Monday 19 December 2011

SQLite

SQLite is SQL database engine small and ready to use. There are many Rebol scripts that handle SQLite, if you don't know this light SQL engine, you should visit:
http://www.sqlite.org/

The first script  I'll show you  is the following:
http://www.rebol.org/view-script.r?script=btn-sqlite.r

you have to put sqlite executable in the same directory of the script (Windows) or in /usr/bin/ (Linux). It's slow but it works this way:

>> do %btn-sqlite.r
>> db: open btn://localhost/test.db3
>> insert db "CREATE TABLE t1 (a int, b text, c text)"
== true
>> repeat i 25 [
[ insert db [{INSERT INTO t1 VALUES (?, ?, ?)} i (join "cool" i) (join "cool"
(25 + 1 - i))]
[ ]
== true
>> insert db "SELECT * FROM t1"
== true
>> probe db/locals/columns
["a" "b" "c"]
== ["a" "b" "c"]
>> res: copy/part db 10
== [["1" "cool1" "cool25"] ["2" "cool2" "cool24"] ["3" "cool3" "cool23"] ["4" "coo
l4" "cool22"] ["5" "cool5" "cool21"] ["6" "cool6"...
>> probe res
[["1" "cool1" "cool25"] ["2" "cool2" "cool24"] ["3" "cool3" "cool23"] ["4" "cool4"
"cool22"] ["5" "cool5" "cool21"] ["6" "cool6" "cool20"] ["7" "cool7" "cool19"] ["
8" "cool8" "cool18"] ["9" "cool9" "cool17"] ["10" "cool10" "cool16"]]
== [["1" "cool1" "cool25"] ["2" "cool2" "cool24"] ["3" "cool3" "cool23"] ["4" "coo
l4" "cool22"] ["5" "cool5" "cool21"] ["6" "cool6"...
>> probe length? res
10
== 10
>> insert db "DROP TABLE t1"
== true
>> close db


The second script is a little bit complex, you have to download the DLL or the Linux library in the script folder, then you can download the following script:
http://www.rebol.org/view-script.r?script=sqlite3.r

modify the script with the correct path to your sql3 library
Windows
sql: load/library %sqlite3.dll
or Linux
sql: load/library %libsqlite3.so
Here how it works:

>> db: sqlite-open %test.db
== 16121296
>> sqlite-exec db "CREATE TABLE t1 (a int , b text , c text);"
== []
>> sqlite-exec db "CREATE TABLE t2 (a int , b text , c text);"
== []
>> ; Testing of 1000 inserts one transaction at a time.
>> t: now/time/precise
== 12:14:23.765
>> repeat i 1000 [ sqlite-exec db reduce [{INSERT INTO t1 VALUES (?,"cool1","cool1");} i]
== []
>> delta: now/time/precise - t
== 0:02:22.422
>> print join "elapsed time = " delta
elapsed time = 0:02:22.422
>> ; Testing of 1000 inserts in one global transaction.
>> t: now/time/precise
== 12:26:49.687
>> sqlite-exec db "begin transaction;"
== []
>> repeat i 1000 [ sqlite-exec db reduce [{INSERT INTO t2 VALUES (?,"cool2","cool2");} i]
== []
>> sqlite-exec db "commit transaction;"
== []
>> delta: now/time/precise - t
== 0:00:00.281
>> print join "elapsed time = " delta
elapsed time = 0:00:00.281
>> ; Select now all data from both tables.
>> ;Just go through "res" block if you want to see the results.

>> res: copy []
== []
>> t: now/time/precise
== 12:29:49.875
>> repeat i 1000 [
insert tail res sqlite-exec/names db reduce ["SELECT * FROM t1 WHERE a=?;" i]
insert tail res sqlite-exec/names db reduce ["SELECT * FROM t2 WHERE a=?;" i]
]
== []
>> delta: now/time/precise - t
== 0:00:04.828
>> print join "elapsed time = " delta
elapsed time = 0:00:04.828
>> sqlite-close db


Then you can attach more databases in a single database, Robert Paluch, alias BobikCZ, shows us how to do it:

do %sqlite3.r ;; load sqlite driver
db: sqlite-open %myfirstdb.db ;; open first db file
sqlite-exec db {attach database 'myseconddb.db' as myseconddb} ;; attach my second db file
res: sqlite-exec db {select * from myseconddb.mytable} ;;resulting select etc..
;; there can be use also joins of tables


If you need to use SQLite over internet, you can with this script:
http://www.rebol.org/view-script.r?script=techfell-protocol.r
Here how it works:

db: open techfell://user:password@webhost.com
insert db "CREATE TABLE t1 (a int, b text, c text)"
repeat i 25 [
insert db [{INSERT INTO t1 VALUES (?, ?, ?)} i (join "cool" i) (join "cool" (25 + 1 - i))]
]
insert db "SELECT * FROM t1"
probe db/locals/columns
res: copy/part db 10
probe res
probe length? res
insert db "DROP TABLE t1"
close db

2 comments:

  1. For an alternate interface to sqlite that works a little more like the rebol/command database interfaces you can try my old script: http://www.rebol.org/view-script.r?script=sqlite3-protocol.r

    ReplyDelete
  2. Bluehost is ultimately one of the best website hosting provider for any hosting services you require.

    ReplyDelete