SQLite: lispe_sqlite

back

LispE provides also an SQLite library to handle a SQLite database.

It exposes the following functions:

(deflib sqlite_commit (sqlitev) the SQL command are then processed. It should finish a series of commands initiated with a begin)
(deflib sqlite_begin (sqlitev mode) to enter commit mode: default mode is DEFERRED other modes are: IMMEDIATE and EXCLUSIVE)
(deflib sqlite_create (sqlitev table (() args)) create a table in a database with the arguments tablename,col1,col2:(create sql "table1" "name TEXT PRIMARY KEY"))
(deflib sqlite_open (sqlitev dbname) open a database)
(deflib sqlite_close (sqlitev) close a database)
(deflib sqlite_process (sqlitev command) process a sql command with results handled in a 'loop')
(deflib sqlite_run (sqlitev command) execute a sql command)
(deflib sqlite_execute (sqlitev command) execute a raw sql command.)
(deflib sqlite_insert (sqlitev table (() columns)) insert a line in a table: (insert sql "table1" "name" nm "age" i))

Creating a database

Here is an example of the creation of a database:

; We load our library
(use 'lispe_sqlite)

; we create a SQLite object through which all manipulation
; will occur
(setq mysql (sqlite))

; Opening a database, if it does not exist: create it
; NOTE THAT ALL CALLS NEED A 'mysql' OBJECT
(sqlite_open mysql "test.db")

; We create a table of name: people with two fields: name and age
(sqlite_create mysql "people" "name TEXT PRIMARY KEY" "age INTEGER")

; We close
(sqlite_close mysql)

Filling in a database

Here is an example of how to store data in this database

(use 'lispe_sqlite)

; We create an SQLite object
(setq mysql (sqlite))

; We open a database
(sqlite_open mysql "test.db")

; we insert data in this database 

(sqlite_insert mysql "people" "name" "Pierre" "age" 40)
(sqlite_insert mysql "people" "name" "Jean-Pierre" "age" 43)
(sqlite_insert mysql "people" "name" "Marie" "age" 20)
(sqlite_insert mysql "people" "name" "Jérôme" "age" 34)
(sqlite_insert mysql "people" "name" "Alphonse" "age" 72)
(sqlite_insert mysql "people" "name" "Anne" "age" 28)
(sqlite_insert mysql "people" "name" "Roseline" "age" 61)
(sqlite_insert mysql "people" "name" "Thierry" "age" 50)
(sqlite_insert mysql "people" "name" "Hugo" "age" 17)
(sqlite_insert mysql "people" "name" "Amandine" "age" 38)
(sqlite_insert mysql "people" "name" "Christine" "age" 40)


(sqlite_close mysql)

Querying a database

Each query returns a dictionary:

(use 'lispe_sqlite)

(setq mysql (sqlite))


(sqlite_open mysql "test.db")

; We use sqlite_process to enable a loop on the database

(sqlite_process mysql "select * from people where age >= 30")

; each loop in mysql will return 
; a dictionary corresponding to the query
(loop dico mysql (println dico))

; This is another way to handle a SQLite command
(setq v (sqlite_run mysql "select * from people where age < 40"))

(println v)

(sqlite_close mysql)