r3wp [groups: 83 posts: 189283]
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search

World: r3wp

[SQLite] C library embeddable DB .

Anyone had any experience with SQLite (http://www.sqlite.org/), especially 
in conjunction with REBOL? What are the limitations of sqlite3-protocol.r 
if any?

I'm interested to see how folks have dealt with issues such as:

 1) Support for datatypes other than TEXT, INTEGER, NUMBER and BLOB?

 2) All tables in one DB, or one table per DB to improve concurrency?

 3) Multi-user access across a network, given this statement from 
 the SQLite website:

SQLite uses reader/writer locks to control access to the database. 
(Under Win95/98/ME which lacks support for reader/writer locks, a 
probabilistic simulation is used instead.) But use caution: this 
locking mechanism might not work correctly if the database file is 
kept on an NFS filesystem. This is because fcntl() file locking is 
broken on many NFS implementations. You should avoid putting SQLite 
database files on NFS if multiple processes might try to access the 
file at the same time. On Windows, Microsoft's documentation says 
that locking may not work under FAT filesystems if you are not running 
the Share.exe daemon. People who have a lot of experience with Windows 
tell me that file locking of network files is very buggy and is not 
dependable. If what they say is true, sharing an SQLite database 
between two or more Windows machines might cause unexpected problems.
actually I am about to give sqlite a try this week. One of things 
which drive me to this solution is that it allows for sharing.
I know that it is rather primitive (file locking based), but with 
rebd, you are lost, as your data is held in memory.
for larger projects, I would surely use mySQL, install server, and 
be done, but I have small one, but guys want to share the app on 
two or three machines ....
I am a bit worried about that - all tables in one file, because of 
no readability (not plain text, but a binary) and possible file corruption, 
but it seems to me, that it works, or sqlite would not be so highly 
praised ...
dunno of sqlite3 protocol, will try. I also found out, there is btn-sqlite.r 
or something like that (where btn = better than nothing), and it 
uses command line sqlite.exe to get the result :-)
sqlite is certainly used in a lot of projects: http://www.sqlite.org/cvstrac/wiki?p=SqliteUsers
sqlite3-protocol.r has a minor bug whereby locals/cols are not cleared. 
Fix is to add a "clear cols" at the beginning of the ' sqlite-exec 
func. Two other changes I made to this function were:

1) Changing “SQLITE_TEXT [any [attempt [load val: sqlite3/column_text 
stmt j] val]]” so as REBOL values are returned, and

2) Removing the /only clause from "system/words/insert/only tail 
result col" for those that prefer flat data structures (i.e. non-blocked 

Finally, a simple wrapper makes the whole thing more usable:

context [

	db: none

	set 'open-db func [name [file!]] [
		db: open join sqlite://localhost/ name

	set 'close-db does [
		close db

	set 'describe func ['table [word!]] [

  insert db rejoin ["select type, name, sql from sqlite_master where 
  upper(tbl_name) = '" uppercase form table "' order by rootpage"]

	set 'sql function [arg [string! block!]] [statement] [
		case [
			string? arg [insert db arg]
			string? first arg [
				statement: copy first arg
				repeat i -1 + length? arg [
					replace/all statement join ":" i pick arg i + 1
				insert db statement

which lets you do stuff like:

>> open-db %test.db
>> sql "create table t1 (col1 INTEGER, col2 TEXT)"
== []
>> describe t1
== [table t1 "CREATE TABLE t1 (col1 INTEGER, col2 TEXT)"]
>> sql reduce ["insert into t1 values (1,':1')" now/date]
== []
>> sql "select * from t1"
== [1 13-Feb-2006]
>> close-db
ashley - maybe you could post those fix back to rebol.org?
I will once they've settled down ... the SQLITE_TEXT change mentioned 
previously should read:

		val: sqlite3/column_text stmt j
		any [
			all [
				attempt [tmp: load val]
				not block? tmp
				not word? tmp

which should correctly handle TEXT with embedded white-space or illegal 
load chars. The context of funcs can also have the following useful 
func added:

	set 'sql-columns does [
		copy db/locals/cols

and should probably 'copy return db/locals/sqlresult in both the 
'sql and 'describe functions.
what do you mean "once they've settled down"?
My changes, not REBOL.org ;)
so I am here, and so what? How does it relate to RebDB on-disk version? 
btw - looking at sqlite-exec, I don't understand why should I clear 
cols block at the beginning of the function??
sqlite-exec appends column names to the locals/cols block ... but 
does not clear the block for each new query. Run a couple of queries 
and check the contents of locals/cols, you'll see what I mean (all 
this is assuming you are using the %sqlite3-protocol.r script dated 
yes, I am .... so I will simply put "clear cols" at the beginning 
of the function ... thanks a lot ...
btw - how to influence where it stores/creates database? I want it 
to have in /data subdir :-)
I also somehow don't like too much having everything in one file 
:-) (because I expect simple backup of some tables could be done 
simply by copying files ... I wonder, if I would use separate file 
for some tables, if it would be able to join them etc.?
I also noticed there is brand new version of techfell protocol for 
sqlite ... dunno if it is based upon sqlite.exe shell calls, just 
investigating it ...
those guys are really screwing with GPL license ...
1) how to influence where it stores/creates database?

Don't know, I've only been looking at all this for a day and havn't 
worked that out yet either.

2) if I would use separate file for some tables, if it would be able 
to join them

Yes. The ATTACH command lets you "hook up" to multiple databases, 
and you can prefix references with database name.

3) noticed there is brand new version of techfell protocol

Of the *four* sqlite scripts on REBOL.org %sqlite3-protocol.r is 
the one to use if you have a Pro licence.

4) those guys are really screwing with GPL license ...

Who? SQLite is PD as is %sqlite3-protocol.r
I mean Techfell and BTN protocols ...
they base their work on mySQL-protocol.r, which is imo BSD, or not?
the advantage of those protocols is that they call sqlite.exe, so 
even no /Pro users might be satisfied ...
btw - what did you mean in RebDB group in regards to on-disk storage 
for RebDB to go here? You mean that actually on-disk version of RebDB 
is sqlite and we should not reinvent the wheel?
I also hope sqlite will allow "free form" data storage, so I don't 
need to define length of text fields etc :-) That is the feature 
I like about rebdb ....
hmm, I found the section: sqlite-open to-file port/target ..... now 
just to interpret it :-)
hmm, weird systax, but following works:

db: open sqlite://localhost//C/rebol/view/sqlite.db
it is somehow a mess ... it does not return error when you try to 
open non-existant db - it creates one ....
well, simple things should be simple ... so how to put the damned 
thing to open db at your path? :-)
Re: RebDB. I'm coming to the conclusion that SQLite is a superset 
of all I wanted from RIF; and it's:

	1) Here today
	2)Lean & mean
	3)One small (256KB) drop in DLL
	4)Works well with REBOL
	5)Public Domain

And, like RebDB, it is pretty flexible about column values (it uses 
column affinity to suggest how values may be coerced – so an INTEGER 
column could contain [1 999 “three” “four”])  which is more REBOLish 
than most other RDBMS's; and all values (TEXT, INTEGER, REAL and 
BLOB) are stored as variable byte length.

My benchmarking of RebDB vs SQLite with three tables; Customers (100), 
Orders (10 per customer) and Items (10 per order) gave the following 
results (measured in Transactions Per Second, with the first for 
RebDB and the second for SQLite):

	select * from Customers 148		120
	select * from Orders	141		11
	select * from Items	 76		 .73
	selective join on Order-Items for one order	8.93	437.23
	join on all Order-Items			.08	4.93
	size of all dat file(s)			410KB		625KB

What you have to remember is that RebDB is 100% memory-based using 
tight loops and REBOL natives where possible, versus SQLite which 
is disk-based and being called via routines.
ok, thanks a lot, maybe repost it in rebdb group, please?
ok, as for your benchmark, also note, that inserts etc. are slow 
as hell. Because it reopens the file each time ... IIRC there is 
some trick as transaction? for that?
insert ...
hmm, but scheme for sqlite is still kind of cumbersome - non ability 
to open at certain path, no error if you open non existant db - it 
mistakenly automatically creates one, then of course your query fails 
but I will somehow sort it out ....
The "create if not exists" is a feature of sqlite not the protcol. 
Think command line:

	sqlite3 new.db

The pathing thing is just an RTFM issue.
I tried to briefly look into API and not found path anywere mentioned 
... I will try looking better ;-)
A short-term workaround is to do something like:

	old-dir: what-dir
	change-dir new-dir
	open-db %my-db.db
	change-dir old-dir

or maybe it is better to use sqlite.exe - after all who needs scheme 
for file access here, right?
ok, finally for lunch .... :-)
I decided to revert back to sqlite3 protocol, not the one Cal Dixon 
turned into scheme. The scheme simply does not fit here ...
I would be glad, if we would work from original sqlite3.r version, 
Ashley .... it is simplified wrapper, not messing with scheme code 
... first we can make this one better, then properly wrap scheme, 
although using scheme for local files is not of much use anyway ...
oh, I found the solution for the path problem. As I expected, the 
library needs path in filesystem friendly way. Just add to-local-files 
into sqlite-open function, so it should read as: sqlite3/open to-string 
to-local-file name tmp: make struct!
but with scheme the design is broken anyway, as scheme does not allow 
some chars, which are legitimate with filesystem, as "!", so ....
I prefer Cal's version as it adds one word to the global context:


as opposed to:


and tightens up some of the code; so I'll use it as the base to optimize 
from. One of the things I want to add is automatic type conversion 
so you can store and retrieve REBOL values without having to worry 
about the fact that they may be stored as TEXT in SQLite.
re:solution for the path problem. Was that for Cal's version? Problem 
occurs earlier than that in the 'open function with this line:

	port/locals/dbid: sqlite-open to-file port/target

asport/target contains only the file name regardless of what path 
you specify!
probably needs   to-file join port/path port/target