Databases*
work in progress
SQLite 3
Opening a database
What’s new: open sqlite-schema
To open or create a local file as a sqlite database the open function.
db: open sqlite://sample.s3db
open is a generic function that dispatches on the kind of first argument. In this case the argument is of kind sqlite-schema.
Simple query
To query a database we use a function query. Since we have no tables in the database we just created we will just query for current date.
db .query { select date() }
; returns a table, so we display it
|display
;
; | date() |
; +--------------+
; | 2025-01-07 |
Let’s make another query without actually requring and data in our database.
db .query { select date() as today , 123 + 234 as nice_number }
; returns a table, which we again ...
|display
;
; | today | nice_number |
; +--------------------------+
; | 2025-01-08 | 357 |
you don’t need to worrie about it, but query is again a generic function that dispatches on the kind of first argument. In this case a
sqlite-database
Executing SQL
SQL that doesn’t query for data, but executes changes is called with a exec function. We will first use
this to create a table itself.
db .exec { create table pets ( id int primary_key , name varchar(40) ) }
; returns the database object again
We can use function exec also to insert some data to our table.
db .exec { insert into pets values ( 1 , "Toto" ) }
; SQL also allows use to insert multiple rows at once
db .exec { insert into pets values ( 2 , "Hedwig" ) ,
( 3 , "Nemo" ) , ( 4 , "Hooch" ) }
Querying
We finally have a database with a table and some data in it. So we can do what we usually do with databases, we
query them.
db .query { select * from pets }
|display
;
; | id | name |
; +---------------+
; | 1 | Toto |
; | 2 | Hedwig |
; | 3 | Nemo |
; | 4 | Hooch |
Now let’s query just the names starting with “H”.
db .query { select * from pets where name like "H%" }
;
; | id | name |
; +---------------+
; | 2 | Hedwig |
; | 4 | Hooch |