SQLite

SQLite is a database in a library: no server, one file per database, or none at all for a database in memory. tiko ships it as the vendor package vendor/sqlite3, and what you call is SQLite's own C interface, written the way SQLite's documentation writes it.

'' SQLite's own C interface, from the toolchain's vendor package.
#import "vendor/sqlite3"

'' Column text is UTF-8 bytes that SQLite owns; copy it out at once.
function ColumnText( byval st as sqlite3_stmt ptr, byval i as int32 ) _
         as ustring
    return *cast( zstring ptr, sqlite3_column_text( st, i ) )
end function

'' A connection is a sqlite3 ptr; ":memory:" is a private database
'' that lives as long as the connection.
dim db as sqlite3 ptr
if sqlite3_open( ":memory:", @db ) <> SQLITE_OK then
    print "cannot open: "; *sqlite3_errmsg( db )
    end 1
end if

'' sqlite3_exec runs SQL that returns no rows.
dim as string sql = _
    "create table city( name text, country text, people int );" & _
    "insert into city values( 'Lagos', 'Nigeria', 15388000 );" & _
    "insert into city values( 'Osaka', 'Japan', 19060000 );" & _
    "insert into city values( 'Lima', 'Peru', 11283000 );"
if sqlite3_exec( db, sql, nullptr, nullptr, nullptr ) <> SQLITE_OK then
    print "failed: "; *sqlite3_errmsg( db )
    end 1
end if

'' A query is prepared, then stepped through one row at a time.
dim st as sqlite3_stmt ptr
sqlite3_prepare_v2( db, "select name, country, people from city " & _
                        "order by people desc", -1, @st, nullptr )
do while sqlite3_step( st ) = SQLITE_ROW
    print left( ColumnText( st, 0 ) & space( 8 ), 8 ); _
          left( ColumnText( st, 1 ) & space( 10 ), 10 ); _
          sqlite3_column_int64( st, 2 )
loop
sqlite3_finalize( st )

'' A query with one value in its one row.
sqlite3_prepare_v2( db, "select sum( people ) from city", _
                    -1, @st, nullptr )
if sqlite3_step( st ) = SQLITE_ROW then
    print "total people:"; sqlite3_column_int64( st, 0 )
end if
sqlite3_finalize( st )

sqlite3_close( db )

Output

Osaka   Japan      19060000
Lagos   Nigeria    15388000
Lima    Peru       11283000
total people: 45731000

A connection is a sqlite3 ptr, which sqlite3_open fills in through its address, @db. The name :memory: opens a private database that lasts as long as the connection; any other name is a file, created if it does not exist. sqlite3_close closes it.

Almost every function returns a result code: SQLITE_OK for success, and anything else is a failure that sqlite3_errmsg( db ) explains. Nothing raises a runtime error, so an ignored code is an ignored failure.

• sqlite3_exec runs SQL that returns no rows; several statements may be given at once, separated by ;.
• sqlite3_prepare_v2 compiles a query into a sqlite3_stmt ptr. sqlite3_step returns SQLITE_ROW for each row and SQLITE_DONE at the end; sqlite3_column_int64, sqlite3_column_text and their siblings read the row's columns, counting from 0. sqlite3_finalize frees the statement.

sqlite3_column_text returns a pointer to UTF-8 bytes that SQLite owns, valid until the next step. ColumnText copies them into a ustring straight away.

Building the program copies SQLite's shared library beside it, so the two run wherever they travel together. #import "vendor/sqlite3" link static links SQLite into the program itself instead. Other C libraries are reached the same way; see Calling C.

Activities

1. Add a fourth city and print only the cities with more than 12 million people.
2. Change :memory: to cities.db, run the program twice, and explain the second run's output.