guile-examples/databases/sqlite3-via-guile-sqlite/example.scm

;;; Code is mostly adapted from
;;; https://notabug.org/guile-sqlite3/guile-sqlite3/src/9405dda61347ff26c57e3be7531dc9dc5a19cf40/tests/basic.scm

(import
 (sqlite3)
 (ice-9 format))

(define (sqlite-exec* db sql key value)
  (let ((stmt (sqlite-prepare db sql)))
    (sqlite-bind stmt key value)
    (sqlite-map display stmt)
    (sqlite-finalize stmt)
    #t))

(define db-name "database.db")


(when (file-exists? db-name)
  (format #t "Removing leftover database ~a~%" db-name)
  (delete-file db-name))


(let ([db (sqlite-open db-name (logior SQLITE_OPEN_CREATE SQLITE_OPEN_READWRITE))])
  (sqlite-db? db)
  (sqlite-exec db "CREATE TABLE project (
    REFERENCE INTEGER PRIMARY KEY,
    name TEXT,
    website TEXT
)")
  ;; Do not forget to close the database.
  (sqlite-close db))


(let ([db (sqlite-open db-name (logior SQLITE_OPEN_CREATE SQLITE_OPEN_READWRITE))])
  (sqlite-db? db)
  (sqlite-exec db
               "INSERT INTO project VALUES (1, 'Guile', '');
                INSERT INTO project VALUES (2, 'Guix', 'gnu.org');")
  ;; Do not forget to close the database.
  (sqlite-close db))


;;; The procedure sqlite-trace is not available in version 0.1.2 -- It
;;; might have been added in version 0.1.3.

;; (let ([db (sqlite-open db-name (logior SQLITE_OPEN_CREATE SQLITE_OPEN_READWRITE))])
;;   (sqlite-trace db
;;                 SQLITE_TRACE_STMT
;;                 (lambda (trace p x)
;;                   (test-assert "trace"
;;                     (string=? (pointer->string
;;                                (sqlite-expanded-sql p))
;;                               "select * from project where 'bla' = 'bla'"))))
;;   (sqlite-exec* db "select * from project where 'bla' = :foo" 'foo "bla")
;;   (sqlite-close db))