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.dllor Linux
sql: load/library %libsqlite3.soHere 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
