TheBackShed.com

Forum Index : Microcontroller and PC projects : SQL databases on the CMM2 accessible from Basic

Author

Message

matherp
Guru

Joined: 11/12/2012
Location: United Kingdom
Posts: 12194

Posted: 03:37pm 13 Nov 2026

Copy link to clipboard

Print this post

SQL databases in MMBasic on the Colour Maximite 2
New in firmware 7.00.00RC6, for the Generation 1 and the Generation 2. It needs the sql folder of CMM2_Programs_7.00.00RC6.zip on the SD card.

https://github.com/UKTailwind/CMM2V1.5/releases/tag/v7.00.00RC6

What it is
MMBasic programs can now keep their data in an SQL database: a file on the SD card or USB flash drive holding tables of rows, which a program adds to, changes, searches and sorts with statements in SQL, the standard language for databases. A club's members, a collection, a log of readings from a sensor, a game's high scores: anything that is a list of records.

The database engine is SQLite 3.47.0, the same engine that is in phones, web browsers and much else. A database file made on the Colour Maximite 2 opens in SQLite on a PC (DB Browser for SQLite, the sqlite3 shell, Python), and one made on a PC opens on the CMM2.

There is one new command, SQL, and one new function, SQL(. Their best part is that a table's row and an MMBasic structure (TYPE) are treated as the same thing: rows go straight into structures and out of them, matched by the members' names.

SQLite is not in the firmware. It is a module, A:/sql/sql.mod, which SQL OPEN loads and SQL CLOSE unloads, so it costs nothing until a program uses it.

Getting started
Write the 7.00.00RC6 firmware, and copy the sql folder of CMM2_Programs_7.00.00RC6.zip to the root of the SD card. Then run the example on its sample database:

RUN "A:/sql/computers.bas"

A first database

SQL OPEN "A:/data/club.db"
SQL EXEC "CREATE TABLE IF NOT EXISTS member(id INTEGER PRIMARY KEY, name TEXT, born INTEGER)"
SQL EXEC "INSERT INTO member(name, born) VALUES(?, ?)", n$, year%
SQL QUERY "SELECT name, born FROM member WHERE born > ? ORDER BY born", 1950
DO WHILE SQL(NEXT)
PRINT SQL(1), SQL(2)
LOOP
SQL CLOSE

• SQL OPEN opens the database file, and makes it if it is not there.
• SQL EXEC runs statements. Each ? takes the next value after the statement, a number or a string, so there are no quotes to put round a string, and a value with quotes in it does no harm.
• SQL QUERY starts a query. SQL(NEXT) moves to each row in turn, returning 1, then 0 when there are no more, and SQL(n) is column n of the row, as an integer, a float or a string, as the column holds it.
• SQL(SELECT puts all of a query's rows into an array at once, and returns how many. A one dimensional array takes the first column, a two dimensional one (row, column) as many columns as it has:

DIM names$(100)
n = SQL(SELECT "SELECT name FROM member ORDER BY name", names$())

• SQL(COLUMNS), SQL(NAME n), SQL(ISNULL n), SQL(ROWS) (the rows the last INSERT, UPDATE or DELETE changed) and SQL(ID) (the key of the last row inserted) tell the rest.
• SQL CLOSE closes the database. RUN, NEW and CHAIN close it too, and undo any transaction not finished.

Tables and structures

TYPE Member
id AS INTEGER
name AS STRING LENGTH 40
born AS INTEGER
END TYPE
DIM m AS Member, list(100) AS Member

SQL CREATE Member ' a table MEMBER with the type's members as its columns
m.name = "Ann" : m.born = 1961
SQL INSERT m ' a row from m; m.id is now its key
SQL INSERT list() ' a row for each element of the array
SQL QUERY "SELECT * FROM member ORDER BY name"
DO WHILE SQL(FETCH m) ' as SQL(NEXT), the row put into m
PRINT m.name, m.born
LOOP
n = SQL(SELECT "SELECT * FROM member WHERE born > ?", list(), 1950)
SQL EXEC "UPDATE member SET born = :born WHERE id = :id", m

• SQL CREATE makes a table from a TYPE, named after it (or SQL CREATE type, table$). INTEGER members are INTEGER columns, FLOAT members REAL and STRING members TEXT. A member named ID that is an integer is the table's key.
• SQL INSERT adds a row from a structure, or a row for each element of an array of structures, all in one transaction. When ID is 0, the database gives the row a new key and puts it into the member.
• SQL(FETCH, and SQL(SELECT with an array of structures, fill each member from the column of its name, in any case.
• A structure among a statement's values gives its :name parameters from the members of those names.
• Nested structures and array members work too: their columns are named PARENT_CHILD, and NAME_0, NAME_1 and so on.

The example
computers.db, in the sql folder, is a sample database with a table of 18 home computers. Its columns have the names of the members of the type Computer, so each row goes straight into a structure. computers.bas, beside it, is the manual's example:

TYPE Computer
id AS INTEGER
name AS STRING LENGTH 30
maker AS STRING LENGTH 20
year AS INTEGER
cpu AS STRING LENGTH 10
ramkb AS INTEGER
END TYPE
DIM c AS Computer, z80(20) AS Computer
DIM INTEGER i, n

SQL OPEN "A:/sql/computers.db"

PRINT "By year:"
SQL QUERY "SELECT * FROM computer ORDER BY year, name"
DO WHILE SQL(FETCH c)
PRINT c.year, c.name, c.maker
LOOP

n = SQL(SELECT "SELECT * FROM computer WHERE cpu = ? ORDER BY ramkb DESC", z80(), "Z80")
PRINT : PRINT n; " with a Z80:"
FOR i = 0 TO n - 1
PRINT z80(i).name, z80(i).ramkb; "K"
NEXT i

c.id = 0 : c.name = "ZX80" : c.maker = "Sinclair" : c.year = 1980 : c.cpu = "Z80" : c.ramkb = 1
SQL EXEC "DELETE FROM computer WHERE name = ?", c.name
SQL INSERT c
PRINT : PRINT "The ZX80 added as number"; c.id

PRINT : PRINT "Makers:"
SQL QUERY "SELECT maker, count(*) FROM computer GROUP BY maker ORDER BY 2 DESC, 1"
DO WHILE SQL(NEXT)
PRINT SQL(1), SQL(2)
LOOP
SQL CLOSE

It lists the computers in the order they came out, finds the four with a Z80 (filling an array of the type), adds the ZX80 from a structure (SQL INSERT puts its new key, 19, into c.id; the DELETE before it stops a second run adding it twice) and counts each maker's computers.

Transactions
Each statement on its own is a transaction: when it ends, its change is safely on the card. That takes about 20ms. Many changes are far faster inside one transaction, which is written to the card once, at its end:

SQL EXEC "BEGIN"
FOR i = 1 TO 1000
SQL EXEC "INSERT INTO reading(t, value) VALUES(?, ?)", i, v(i)
NEXT i
SQL EXEC "COMMIT"

A thousand inserts take about a third of a second like this, against 20 seconds one at a time. ROLLBACK in place of COMMIT undoes them all. If the power fails during a transaction, the next SQL OPEN finds it unfinished and undoes it, so the database is never left half changed.

Generation 1 and Generation 2
• Generation 2: the engine runs in the 8MB kept for loadable programs, with about 7MB for itself, and BASIC keeps all of its own memory. The one cost: a BASIC program with a database open cannot run a loadable program (.app) until it closes it (the error "SQL CLOSE first"). At the command prompt, running one closes a database that a program left open.
• Generation 1: the engine has 1MB for its work, and with its code it takes about 1.7MB of the 3.7MB that BASIC has, while a database is open. It is fast only with small databases: one of up to about 1MB (roughly 8,000 to 12,000 rows of 100 characters, with an index) works well. With a larger one, finding a row by its key or an index stays quick, but anything that has to look at the whole of a large table (an unindexed sort, GROUP BY, DISTINCT, a join of two big tables) reads the SD card all over again and can take minutes. A loadable program can still run with a database open, in the memory that is left.

From BASIC, on each:

Generation 2 Generation 1
1,000 inserts in one transaction 0.32s 0.38s
One insert committed on its own 17ms 23ms

With a table of 50,000 rows (6.5MB, with a 6MB index):

Generation 2 Generation 1 (1MB)
PRAGMA integrity_check 32s 160s
SELECT count(DISTINCT ...) 34s 460s
CREATE INDEX 21s 23s

The Generation 1's figures for the big table were measured on a Generation 2 given the Generation 1's 1MB: the SD card, not the processor, sets them. Indexes make the difference: put one on the columns a program searches, sorts or joins by.

sql.app at the command prompt
sql.app runs SQL statements on any database, to look into one or set one up without writing a program. Each row is printed as a line, its columns separated by |:

RUN "A:/sql/sql.app", A:/sql/computers.db "SELECT name, year FROM computer"

-h prints the columns' names first, -t the time each statement took, and @ with a file's name runs the statements in the file.

Errors
A statement the database cannot run is an MMBasic error, "SQL: " followed by SQLite's own message (SQL: no such table: member), which ON ERROR SKIP and MM.ERRMSG$ catch. Ctrl-C stops a long statement.

Limits
• One database is open at a time; ATTACH reaches others through it.
• A statement is a string, so at most 255 characters, with up to 20 values after it.
• A structure used with SQL can have at most 64 columns, counting nested members and array elements.
• A large sort writes temporary files (sqlite_....tmp) to the root of the current drive, and removes them after.
• SQL( is a new function: an old program with an array named SQL() must rename it.

The commands and functions

SQL OPEN file$ [, poolKB [, cacheKB]]
SQL CLOSE [QUERY]
SQL EXEC sql$ [, value ...]
SQL QUERY sql$ [, value ...]
SQL CREATE type [, table$]
SQL INSERT var [, table$]

SQL(NEXT) SQL(n) SQL(FETCH var)
SQL(SELECT sql$, array() [, value ...])
SQL(COLUMNS) SQL(NAME n) SQL(ISNULL n) SQL(ROWS) SQL(ID)

poolKB and cacheKB set the engine's memory and its cache, and are not normally needed. The User Manual's new chapter, SQL Databases, has all of this and more.

Licences
SQLite is in the public domain. The module's settings follow usqlite's (Elvin Slavik), under the MIT licence; the module and the SQL command are the firmware's own, in apps/sql and Src/SQL.c of its repository.

Print this page

To reply to this topic, you need to log in.