World: r3wp

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 
records)

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"]
		db/locals/sqlresult
	]

	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
			]
		]
		db/locals/sqlresult
	]
]

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:

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


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 
22-Mar-2005).

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?

begin
insert ...
...
end

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:

	set-tracing

as opposed to:

	sqlite-close
	sqlite-error
	sqlite-exec
	sqlite-open
	sqlite3
	SQLITE_BLOB
	SQLITE_DONE
	SQLITE_FLOAT
	SQLITE_INTEGER
	SQLITE_NULL
	SQLITE_OK
	SQLITE_ROW
	SQLITE_TEXT


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

Yep, wish I had read your message earler. ;)


The 'open func sets port/target to a string of the file name and 
port/path to a string of the path (empty if none). So you just need 
to replace the sqlite-open in the 'open func with:
 
	port/locals/dbid: sqlite-open to-file join port/path port/target

as Anton stated above.

Oops, that should be:


 port/locals/dbid: sqlite-open to-file either port/path [join port/path 
 port/target] [port/target]


as port/path contains none! when no path is provided. On that note, 
anyone know why port/path and port/target are set to string! not 
file! ?

I would add this too 

  if not integer? port/locals/dbid  ......     [make error! port/locals/dbid]
as you see the source of errors earlier.

Ashley, don't know why, but they are string! for http and ftp schemes, 
and file! for file and directory schemes.