;;; dbi: Minimal gasket to postgresql, sqlite3 and mysql
;;;
;; Copyright (C) 2007-2016 Matt Welland
;; Redistribution and use in source and binary forms, with or without
;; modification, is permitted.
;;
;; THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS
;; OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
;; WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
;; ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE
;; LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
;; CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
;; OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
;; BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
;; LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
;; (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
;; USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
;; DAMAGE.
;; WARNING: This example is basically useless, I'll rewrite it one of these days ....
(require-library margs dbi)
(define help "help me")
(define remargs (args:get-args
(argv)
(list "-inf")
(list "-h")
args:arg-hash
0))
;; define DBPATH in setup.scm
(include "setup.scm")
(define (ftf:mk-db)
(let* ((fname (conc DBPATH "/ftfplan.db"))
(dbexists (file-exists? fname))
(dbh (if dbexists (dbi:open 'sqlite3 (list (cons 'dbname fname))) #f)))
(if (not dbexists)
(begin
;; (print "fullname: " fullname)
(system (conc "mkdir -p " DBPATH)) ;; create the path
(set! dbh (dbi:open 'sqlite3 (list (cons 'dbname fname))))
(for-each
(lambda (sqry)
;; (print sqry)
(dbi:exec dbh sqry))
;; types: 0 text, 1 jpg, 2 png, 3 svg, 4 spreadsheet, 5 audio, 6 video :: better specs to come...
(list
"CREATE TABLE pics (id INTEGER PRIMARY KEY,name TEXT,dat_id INTEGER,thumb_dat_id INTEGER,created_on INTEGER,owner_id INTEGER);"
"CREATE TABLE dats (id INTEGER PRIMARY KEY,md5sum TEXT,dat BLOB,type INTEGER);"
;; on every modification a new tiddlers entry is created. When displaying the tiddlers do:
;; select where created_on < somedate order by created_on desc limit 1
"CREATE TABLE tiddlers (id INTEGER PRIMARY KEY,wiki_id INTEGER,name TEXT,rev INTEGER,dat_id INTEGER,created_on INTEGER,owner_id INTEGER);"
;; rev and tag only utilized when user sets a tag. All results from a select as above for tiddlers are set to the tag
"CREATE TABLE revs (id INTEGER PRIMARY KEY,tag TEXT);"
;; wikis is here for when postgresql support is added or if a sub wiki is created.
"CREATE TABLE wikis (id INTEGER PRIMARY KEY,key_name TEXT,title TEXT,created_on INTEGER);"))
))
dbh))
(define db (ftf:mk-db))
(dbi:exec db "INSERT INTO pics (name,owner_id) VALUES ('bob',1);")
(dbi:for-each-row (lambda (row)(print "Name: " (vector-ref row 0) ", owner_id: " (vector-ref row 1)))
db
"SELECT name,owner_id FROM pics;")