guile-sqlite3/tests/basic.test

;;;; basic.test ---      -*- mode: scheme; coding: utf-8; -*-
;;;;
;;;;   Copyright (C) 2011 Detlev Zundel <dzu@denx.de>
;;;;
;;;; This library is free software; you can redistribute it and/or
;;;; modify it under the terms of the GNU Lesser General Public
;;;; License as published by the Free Software Foundation; either
;;;; version 3 of the License, or (at your option) any later version.
;;;;
;;;; This library is distributed in the hope that it will be useful,
;;;; but WITHOUT ANY WARRANTY; without even the implied warranty of
;;;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
;;;; Lesser General Public License for more details.
;;;;
;;;; You should have received a copy of the GNU Lesser General Public
;;;; License along with this library; if not, write to the Free Software
;;;; Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

(define-module (tests basic-test)
  #:use-module (ice-9 format)
  #:use-module (sqlite3))

(define (sqlite-exec db sql)
  (let ((stmt (sqlite-prepare db sql)))
    (sqlite-map display stmt)))

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

;; Cleanup database so we can check creation
(define db-name "tests/simple.db")
(if (file-exists? db-name)
    (begin
      (format #t "Removing leftover database ~a~%" db-name)
      (delete-file db-name)))

(format #t "Creating test database ~a:" db-name)
(define db (sqlite-open db-name (logior SQLITE_OPEN_CREATE
                                        SQLITE_OPEN_READWRITE)))
(format #t "~40tOk~%")

(format #t "Creating table 'project':")
(sqlite-exec
 db
 "create table project (
      reference integer primary key,
      name   text,
      website   text
  )")
(format #t "~40tOk~%")

(format #t "Inserting a dataset:")
(sqlite-exec db "insert into project values (1, 'Guile', '')")
(format #t "~40tOk~%")

(let* ((s "SELECT * FROM project")
       (stmt (sqlite-prepare db s #:cache? #t)))
  (unless (eq? stmt (sqlite-prepare db s #:cache? #t))
    (error "caching is not caching much!")))

(format #t "Reading dataset: ")
(sqlite-exec db "select * from project")
(format #t "~40tOk~%")

(format #t "Reading dataset with named parameter: ")
(sqlite-exec* db "select * from project where 'bla' = :foo" ":foo" "bla")
(format #t "~40tOk~%")

(format #t "Reading dataset with named parameter, v2: ")
(sqlite-exec* db "select * from project where 'bla' = :foo" 'foo "bla")
(format #t "~40tOk~%")

(format #t "Inserting a dataset using sqlite-bind: ")
(sqlite-exec db "CREATE TABLE foos (dbid INTEGER PRIMARY KEY, name TEXT)")
(let ((stmt (sqlite-prepare db "INSERT INTO foos(name) VALUES(?)")))
  (sqlite-bind stmt 1 "myfoo")
  (sqlite-step stmt)
  (sqlite-finalize stmt))
(sqlite-exec db "DROP TABLE IF EXISTS foos")
(format #t "~40tOk~%")

(sqlite-close db)

(format #t "Removing database ~a" db-name)
(delete-file db-name)
(format #t "~40tOk~%")

(exit 0)