Files and databases

A scene can write a file and read it back the next time it runs: some notes, a level you drew, a list of words. For a lot of things of the same kind, such as every score of every game, it can keep a database and ask it questions.

let notes = await loadText("notes.txt").catch(() => "")    // what was written last time, or nothing the first time

async function onKeyDown(key) {
  if (key.length === 1) notes += key
  if (key === "Enter") await saveText("notes.txt", notes)
}

function draw() {
  background("midnightblue")
  text(notes + "|", 20, 60, "white", 24)
  text("Type, then press Enter to save", 20, height - 20, "gray", 16)
}

Click the preview, type a few words and press Enter. Press Restart in the footer, or close the file and open it again, and they are still there.

For one number or one small thing, save and load are simpler: they need no await and no file name. Use a file when you want text in a shape of your own, or something big. Use a database when you want to keep many rows and pick some out.

Writing and reading a file

All four are used with await, because a file takes a moment. To await inside a function of your own, make it an async function.

const level = { name: "Caves", walls: [[0, 0, 200, 20], [80, 120, 20, 200]], start: { x: 40, y: 60 } }
await saveJSON("levels/caves.json", level)

const back = await loadJSON("levels/caves.json")
console.log(back.name, back.walls.length)

loadText and loadJSON look first for a file the scene has written. If there is none, they read the file of that name that sits beside the scene, as they always have. So a scene can come with a level.json beside it and write its own once the player changes something. Where the files are real ones beside the scene (see below), that is the same file, and writing it changes it. A file that is in neither place stops the scene with an error that names it. To carry on with a value of your own, catch it:

const words = await loadText("words.txt").catch(() => "")
const level = await loadJSON("level.json").catch(() => ({ walls: [] }))

Paths

A path is a file's name, with folders in front if you like: "notes.txt", "levels/caves.json". It starts from the scene's own place and stays inside it. A / at the front and any . or .. parts are dropped, so "/notes.txt" and "../notes.txt" are both notes.txt.

In Circuitry a scene writes data, not programs. A path ending in .js, .py, .html or another kind of file that is run is refused, and so is a hidden file (a name starting with .) and one of Circuitry's own documents. The scene stops with an error that names the file. Give the files a scene writes endings such as .txt, .json and .db.

A database

A database keeps rows in tables, like a spreadsheet with named columns, and answers questions written in SQL: the five best scores, every game by one player, how many games there have been. The database here is SQLite, the same one phones and browsers use themselves.

const db = await openDatabase("scores")
await db.run("create table if not exists best (name text, points integer)")

await db.run("insert into best values (?, ?)", "Ann", 12)
await db.run("insert into best values (?, ?)", "Bob", 7)

const rows = await db.query("select name, points from best where points > ? order by points desc", 10)
for (const row of rows) console.log(row.name, row.points)       // Ann 12

openDatabase("scores") is the file scores.db. A name can have a folder and an ending of its own, as in "data/scores.sqlite". Several databases can be open at once. Opening one that is already open gives you the same database again.

Values go in the ? marks

Put a ? in the SQL wherever a value belongs, and give the values after it, in the same order. There must be one value for each ?.

await db.run("insert into best values (?, ?)", player, score)
await db.query("select * from best where name = ? and points >= ?", "Ann", 10)
await db.query("select * from best where name = ? and points >= ?", ["Ann", 10])    // one list does the same

Do not build the SQL by joining text together. A name with a ' in it, such as O'Brien, would break the SQL, and a ? never does.

A value can be a number, text, true or false (kept as 1 and 0), or null for nothing.

One piece of SQL at a time

Give run and query one statement each. The table is made with one run, and each row is put in with another.

create table if not exists is the line to start with: it makes the table the first time and does nothing after that, so the same program runs the first time and every time.

When it is written

You do not save a database. A moment after a change it is written by itself, and it is written when you close() it and when the scene stops. A burst of changes, such as a hundred rows put in one after another, is written once at the end.

If you use begin and commit to make several changes as one, the database is written after the commit. Changes that were never committed are dropped when the database is closed.

When SQL goes wrong

A mistake in the SQL stops the scene with an error that shows the SQL and what SQLite said about it:

Line 4: db.query("select nme from best"): no such column: nme

Top Scores, on the pictures.js page, is a small game that puts every game into a database and shows the five best.

Where files and databases are kept

That depends on where Circuitry is running. The scene is written the same way in each.

• Real files are ones you can see. saveText("notes.txt", …) writes notes.txt beside the scene and openDatabase("scores") makes scores.db there. They show in Circuitry's file browser, and on an iPhone or iPad in the Files app, so you can open, copy and share them. A scene saved in iCloud Drive or in a linked folder keeps its files there too.
• A scene that is not saved yet, on a phone or tablet, uses the folder documents are saved to on the device. Save the scene into a folder of its own to keep its files together.
• Scenes in the same folder share its files. Two scenes that both write notes.txt beside themselves write the same file. In a browser each scene has its own.
• A workflow can read the same database. On an iPhone or iPad, a Database node set to a database on the device, with the same name, in the same folder, opens the same file. Do not change it from a scene and from a workflow at the same moment: a scene writes the whole file each time.
• In a browser nothing is sent anywhere. The files are kept on the computer, tablet or phone you are using, for this scene alone, until Circuitry's data on the device is cleared. Circuitry on another device starts with nothing.
• A scene reaches its own folder and nothing else. It cannot name a file outside it, and it cannot see anything Circuitry itself keeps (what a scene can reach).

Keep them small: a text file can hold 5 million characters and a database 32 MB. A database is written whole a moment after each burst of changes, so change it when something happens (a game ends, a level is saved), not on every frame.

A database needs SQLite, which comes with a scene whose own file says openDatabase (about 700 KB). If the call is tucked away in a file the scene loads with use(), mention openDatabase in the scene too. A scene that opens no database never loads it.

The Python spellings work here too: save_text, save_json, load_text, load_json and open_database.