Databases

A file is fine for a few things. For lots of records that you’d like to sort and search, like every score anyone has ever got, a database does the work. My databases are SQLite, and they speak SQL, the language databases have spoken for fifty years. This chapter is about using them from a game. It isn’t a whole SQL course, but there’s enough here for a high-score table, a save game, or a shop full of items.

A high-score table

Here’s a whole one. An SQL block gives a statement a name: USE lines say what it’s given and of what kind, and in the SQL, @name is where each goes. open_database opens the file (!! writable to change it as well as read it), and call_sql runs a statement, with what it’s given after !!.

SCREEN 320, 240
SQL "make"
    CREATE TABLE IF NOT EXISTS scores (name TEXT, points INTEGER)
END SQL
SQL "add"
    USE STRING name
    USE NUMBER points
    INSERT INTO scores (name, points) VALUES (@name, @points)
END SQL
SQL "top"
    USE NUMBER how_many DEFAULT 3
    SELECT name, points FROM scores ORDER BY points DESC LIMIT @how_many
END SQL

db = open_database("scores.db") !! writable
call_sql(db, "make")
call_sql db, "add" !! name("Ada") points(1200)
call_sql db, "add" !! name("Bob") points(900)
call_sql db, "add" !! name("Cy") points(1500)
call_sql db, "add" !! name("Dee") points(300)

PRINT "TOP THREE"
FOR row = call_sql(db, "top")
    PRINT row.name, row.points
NEXT
best = call_sql(db, "top") !! how_many(1)
PRINT "champion:", best(0).name
CLOSE db

TOP THREE
Cy 1500
Ada 1200
Bob 900
champion: Cy

A statement that reads gives back its rows, a list with a map for each row, named by its columns. how_many has a DEFAULT, so it can be left out.

The SQL blocks don’t do anything by themselves. They only give the statement a name, like a BEHAVIOR does, and nothing happens until call_sql runs it.

Crash-tan’s Tip: Always give values with USE and @name, rather than gluing them into the SQL yourself. A player called Bob'); DROP TABLE scores; -- is then just a player with a strange name.

Keeping it: CLOSE

While a database is open, I keep it in memory, and I only write it back to its file when you CLOSE it. That makes it quick, however much you change, but it means that until CLOSE, nothing is saved. A game that stops without closing its database loses what it changed, just like a file it was writing.

Here a score is added and the database closed, then it’s opened again to read: the score is there. Then another is added, without a CLOSE, and the database opened again: that one isn’t.

SCREEN 320, 240
SQL "make"
    CREATE TABLE IF NOT EXISTS scores (name TEXT, points INTEGER)
END SQL
SQL "add"
    USE STRING name
    USE NUMBER points
    INSERT INTO scores (name, points) VALUES (@name, @points)
END SQL
SQL "count"
    SELECT COUNT(*) AS how_many FROM scores
END SQL

db = open_database("scores.db") !! writable
call_sql(db, "make")
call_sql db, "add" !! name("Ada") points(1200)
CLOSE db

db = open_database("scores.db")
PRINT "after CLOSE:", call_sql(db, "count")(0).how_many
CLOSE db

db = open_database("scores.db") !! writable
call_sql db, "add" !! name("Bob") points(900)
again = open_database("scores.db")
PRINT "before CLOSE:", call_sql(again, "count")(0).how_many
CLOSE again
CLOSE db

after CLOSE: 1
before CLOSE: 1

COUNT(*) AS how_many names the count, so it comes back as how_many.

So CLOSE a database when you’re done with a lot of changes: at the end of a level, when the player saves, or when the game is quitting. Without !! writable, a database is only read, and it’s never written back at all.

Where it lives

open_database with !! writable keeps the file where this machine keeps a game’s saves, and makes it the first time, so the scores are there the next time the game starts.

Without !! writable, I find it the way I find an image or a sound, so a game can come with a database: put items.db in its folder, with every item in the shop, and read it with open_database("items.db"). It has to be there already: reading a database that isn’t stops the game. Make it on your own computer with any SQLite tool, or with a little program of your own that fills it. It’s the same file on every machine the game runs on, the web too.

Crash-tan’s Tip: In a game played together, only the server can open a database !! writable. A player’s own machine can read one that came with the game, but anything that has to be kept, like the high scores, belongs to the server.

Changing what’s there

A statement that writes, INSERT, UPDATE or DELETE, doesn’t give back rows. It gives back a map: rows_affected is how many rows it changed, and last_id is the number SQLite gave the last row it added. Every row has one, and a table can show it as a column with INTEGER PRIMARY KEY.

SCREEN 320, 240
SQL "make"
    CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT, price INTEGER)
END SQL
SQL "add"
    USE STRING name
    USE NUMBER price
    INSERT INTO items (name, price) VALUES (@name, @price)
END SQL
SQL "sale"
    USE NUMBER off
    UPDATE items SET price = price - @off WHERE price > @off
END SQL
SQL "sell"
    USE NUMBER id
    DELETE FROM items WHERE id = @id
END SQL
SQL "shop"
    SELECT id, name, price FROM items ORDER BY id
END SQL

db = open_database("shop.db") !! writable
call_sql(db, "make")
call_sql db, "add" !! name("sword") price(50)
call_sql db, "add" !! name("shield") price(30)
potion = call_sql(db, "add") !! name("potion") price(5)
PRINT "the potion is number", potion.last_id

sale = call_sql(db, "sale") !! off(10)
PRINT sale.rows_affected, "things are cheaper"
call_sql db, "sell" !! id(potion.last_id)

FOR item = call_sql(db, "shop")
    PRINT item.id, item.name, item.price
NEXT
CLOSE db

the potion is number 3
2 things are cheaper
1 sword 40
2 shield 20

The potion was too cheap for the sale, so only two things changed, and then it was sold.

What a statement is given

A USE line is USE NUMBER or USE STRING, then the name. With a DEFAULT, it can be left out; without one, it has to be given every time, and if it isn’t, I stop and say which one is missing.

SQL "add"
    USE STRING name
    USE NUMBER points
    INSERT INTO scores (name, points) VALUES (@name, @points)
END SQL
SQL "make"
    CREATE TABLE scores (name TEXT, points INTEGER)
END SQL
db = open_database("scores.db") !! writable
call_sql(db, "make")
call_sql db, "add" !! name("Ada")

Given its points too, it adds the row:

SCREEN 320, 240
SQL "add"
    USE STRING name
    USE NUMBER points
    INSERT INTO scores (name, points) VALUES (@name, @points)
END SQL
SQL "make"
    CREATE TABLE scores (name TEXT, points INTEGER)
END SQL
db = open_database("scores.db") !! writable
call_sql(db, "make")
added = call_sql(db, "add") !! name("Ada") points(1200)
PRINT added.rows_affected, "row added"
CLOSE db

A block holds one statement. Two in one block is a mistake I stop at, so making three tables takes three blocks:

SQL "make"
    CREATE TABLE scores (name TEXT, points INTEGER);
    CREATE TABLE players (name TEXT)
END SQL
db = open_database("game.db") !! writable
call_sql(db, "make")

A block for each:

SCREEN 320, 240
SQL "make scores"
    CREATE TABLE scores (name TEXT, points INTEGER)
END SQL
SQL "make players"
    CREATE TABLE players (name TEXT)
END SQL
db = open_database("game.db") !! writable
call_sql(db, "make scores")
call_sql(db, "make players")
PRINT "two tables made"
CLOSE db

Empty cells: NULLS

A cell in a database can be empty: SQL calls that NULL. Crash BASIC has no empty number, so when a statement reads an empty cell, I stop, and say which column it was in. That’s what you want, most of the time: an empty score is usually a mistake somewhere else.

When an empty cell is fine, a NULLS line says what it should read as. Here a player who hasn’t finished a race yet has no time:

SCREEN 320, 240
SQL "make"
    CREATE TABLE races (name TEXT, seconds REAL)
END SQL
SQL "add"
    USE STRING name
    INSERT INTO races (name) VALUES (@name)
END SQL
SQL "finish"
    USE STRING name
    USE NUMBER seconds
    UPDATE races SET seconds = @seconds WHERE name = @name
END SQL
SQL "board"
    NULLS seconds = 0
    SELECT name, seconds FROM races ORDER BY name
END SQL

db = open_database("races.db") !! writable
call_sql(db, "make")
call_sql db, "add" !! name("Ada")
call_sql db, "add" !! name("Bob")
call_sql db, "finish" !! name("Ada") seconds(61.5)
FOR racer = call_sql(db, "board")
    IF racer.seconds = 0
        PRINT racer.name, "is still racing"
    ELSE
        PRINT racer.name, "took", racer.seconds
    END IF
NEXT
CLOSE db

Ada took 61.5
Bob is still racing

Without the NULLS line, reading Bob’s time stops the game.

A NULLS value is a number of 0 or more, or a string: 0 for a time nobody could really take, "" for a name. A negative one is a mistake I stop at. For one, let the SQL choose it: COALESCE(seconds, -1) is seconds, or -1 when it’s empty.

SCREEN 320, 240
SQL "make"
    CREATE TABLE races (name TEXT, seconds REAL)
END SQL
SQL "add"
    USE STRING name
    INSERT INTO races (name) VALUES (@name)
END SQL
SQL "board"
    SELECT name, COALESCE(seconds, -1) AS seconds FROM races
END SQL

db = open_database("races.db") !! writable
call_sql(db, "make")
call_sql db, "add" !! name("Bob")
PRINT call_sql(db, "board")(0).seconds
CLOSE db

-1