World: r3wp

0.1.6 available at: http://www.dobeash.com/SQLite/sqlite.r


Now that the driver functionality has stabilized, this build focuses 
on delivering optimal performance. Changes of note include:

	- no-copy directive added

 - Added a new /info refinement to control when column names and widths 
 are obtained
	- main SQL loop rewritten to be approx twice as fast as 0.1.5
	- format / pad optimised (also about twice as fast as well)

 - IMPORT removed (better to use sqlite admin tool for large externel 
 loads / unloads)
	- Example code removed to http://www.dobeash.com/SQLite/demo.r

 - Code reordered and tidied up (directives appear at beggining of 
 context and routines defined on a single line each)

Enjoy!

btw, do you know if it's possible to separate sqlite's engine from 
the rest of the code? (sql, etc) we'd need only the low level stuff, 
file format, indexind etc.

fileformat sucks :-) who needs one extra big binary file? that is 
like ms outlook storage against mozilla's one - give me table per 
file like any other database, plus maybe text storage like rebdb, 
and you win :-)

give me table per file like any other database

 ... many databases actually have a table -> tablespace -> file(s) 
 kind of mapping; and finding a [high performance] RDBMS that uses 
 plain text storage is not that easy.

do you know if it's possible to separate sqlite's engine from the 
rest of the code?

 ... I don't believe so, but I haven't personally checked the C source 
 code; although there are numerous compilation options that could 
 be turned off to reduce the size of the 254KB DLL (or *nix .so) even 
 further.

If someone has an SDK licence could they confirm this strange behaviour 
by replacing the last line of %sqlite.r with:

attempt [delete %test.db]
connect/create %test.db
sql "create table t (c)"
sql "insert into t values (1)"
print mold SQL ["select * from t where c = ?" 1]
wait 2


and encapping it with either enpro or enface. Run both the script 
and the encapped version and compare the output ... I get an empty 
block returned by any statement that makes use of bind variables 
and is encapped. DOing %sqlite.r from an encapped script works fine, 
as does something like:

	do uncompress #{789C...

so my only guess is it's a binding issue of some sort.

are you doing anything strange in the script?

notice that encap does a mold/flat load on the script too

does   do mold/flat load %sqlite.r    still work?

Fixed! It was related to the type?/word "problem" I reported in the 
RAMBO group. Encap mold/flat'ed my switch statement that used #[datatype! 
integer!] etc into integer! etc Thanks for helping me solve two problems 
at once! ;)

as i imagined - that happened to me too once, and I made a ticket 
at the time about using mold/all in encap...

0.1.7 available at: http://www.dobeash.com/SQLite/sqlite.r


Two main fixes are ability to handle INSERTed and SELECTed block 
values, and changes to allow the script to be encapped.

One strange bug remains. Occasionally %demo.r will fail (typically 
selecting rows from Items) due to garbage characters that somehow 
get inserted. I have reduced the problem down to this script:

REBOL []

unless value? 'SQLite [do %sqlite.r]

repeat cnt 10 [
	prin ["^/Run" cnt "..."]
	;	Clean up from previous runs
	error? try [delete %test.db]
	error? try [delete %test.db-journal]
	;	Create Items (1000 rows) records
	prin " create ..."
	CONNECT/flat/create %test.db
	SQL "create table t (c1,c2,c3,c4,c5)"
	prin " insert ..."
	SQL "begin"
;	loop 1000 [
	repeat z 10000 [

;		SQL reduce ["insert into t values (?,?,?,?,?)" 1 "A 1" $1 1 $1 
* 1]

  SQL reduce ["insert into t values (?,?,?,?,?)" 1 reform ["A" 1] $1 
  1 $1 * 1]
	]
	SQL "commit"
	prin " select ..."
	SQL "select * from t"
	DISCONNECT
]

quit


Running the script should cause a failure like the following within 
the first couple of runs:


Run 1 ... create ... insert ... select ...** Syntax Error: Invalid 
tag -- <C
** Near: (line 1) À<C"
>> sqlite/direct?: true
== true
>> sql "select * from t where c2 like '%<%'"
== [1 {À<C^B"} "$1.00" 1 "$1.00"]


Changing the repeat to a loop seems to shift the error, often (but 
not always) making it take more runs to materialize. Replacing the 
reform with a string will often (but not always) allow all runs to 
complete successfully. Changing the number or order of INSERTed values 
also seems to shift the error. I'm not sure whether this is a REBOL 
or SQLite library error, but any help in tracking it down would be 
greatly appreciated.

I copied the cycle to clipboard and then did [ do read clipboard:// 
] first run it was fine, the second one I got ** Syntax Error: Invalid 
string -- "

(first run of do read clipboard:// - all 10 runs were fine)

Good. You've confirmed the error is repeatable (although inconsistent).

To me this looks like either a strange character coding happens or 
that there are some 0x00 chars included, which are interpreted as 
string-end.

I think, the internal memory is mingled, either in sqlite or in rebol, 
probably in the interface by the garbage collector.

I observed already some high memory consumption, until my pc was 
blocked.

I had this already with the earlier versions of sqlitex.r after some 
errors.

I would recommend that you use deliberately some [save].

recommend that you use deliberately some [save]
 ... not sure I understand, what are you suggesting?

Also note that this problem (garbage characters on insert) only seemed 
to materialize on 0.1.7 and the change from type? to type?/word. 
Or at the very least it seems more prevalent now.

[save] I think you can mark your function with a save attribute? 
kind of like you define catch and throw? then it survives garbage 
collection or something like that IIRC ...

You can protect  your memory defined in the interface with [save]
http://www.rebol.com/docs/library.html#Garbage

Yes, in the routine spec.

ah, yes, that's it ....

The high memory consumption and crashing of an process or should 
I say silently vanishing, that I observed with this routine after 
two consecutive runnings, I saw already  sporadic with the former 
releases after some sql errors and trying to resolve a busy lock 
on the db with some tricks, for example repeating a step.

Added [save] to all struct! ... no improvement. ;)

ok, we can at least rule it out :-)

hmm, not sure it is kind of zero char string terminator, as rebol 
suggests, but strange stuff indeed and maybe a rebol bug .... e.g. 
with even new View and  Cyphre's grid I am able to get such strange 
chars in particular cells ....

there must be some leak somewhere ....

or just improper assignment, pointing to some strange location, dunno 
...

now debugger would be usefull ;-)

Bobik is just reporting to me, that without the /direct, sqlite driver 
returns string values as words, how is that?

Ashley, something is wrong maybe .... he just tried to make sqlite 
db in sqliteadmin tool ... he has id (integer) name (text) lastname 
(text) ... he inserts some values, but your driver makes name and 
lastname words, instead of strings ... while sqlite3 returns strings 
correctly ...

/direct corrects the problem, but imo /direct should not be needed? 
Imo our driver should behave consistently with a) other drivers b) 
external sqlite tools?

I mean - in regards to molding/loading string values ....

connect/create %test.db
sql "create table test (id, name, last_name)"
sql {insert into test values (1, "Petr", "Krenzelok")}
sql {select * from test}

; returns [[1 Petr Krenzelok]]   ... sqlite3 does not do that according 
to Bobik's info ... I am not sure I like it - it convert apparent 
string to word, I don't want that and I am not sure I want to use 
/direct for that, that seem like a reverse logic to me ...

ah, now I see ... and I have questions .... Ashley, when the sql 
query is not block, but a string - you send the query as-is? Why 
the difference? with ' it works, with " it does not ... can the functionality 
be made just the same? I think most ppl are used to "quote" string, 
not to 'quote it like that' .... I would prefer driver to return 
an error for quotes, if they can't be used, instead of returning 
string as a word :-)

it is inconsistent with:


sql ["insert into test values (1, ?, ?)" "Petr" "Krenzelok"] ; in 
this case it returns strings, not words ... imo string and block 
sql queries should be compatible

It should return always string!

there is imo inconsistency between block and string format of query 
- if you use quotes with block format, it remains string, if you 
use quotes inside of string query, sqlite returns different values, 
because a word is returned ... imo there is bug in parsing and in 
the insert phase with string query format already ...

uhm, there is no parse inside sqlite.r ;-)

either string? statement [statement][first statement]

so it may as well be sqlite library, who is causing it ....

hehe, type? 'aaaaa'' == word! .... of a value aaaaa'

It could be a memory alignment problem as well. Maybe some kind of 
internal offset that gets screwed up.

I'm not following this , but in sql ' is used as the quote character 
for literal strings, not "

Graham - you may be right, but anyway, I would like to get even ' 
quoted chars returned back as a string to rebol, not a words ...

type?/word returns word!

type? returns datatype!

Pekr, for an explanation of string vs block see the "Using the Driver" 
section of: http://www.dobeash.com/SQLite/DriverGuide/


The various refinements (including /direct) are covered earlier in 
the document under "Database access functions".


Jaime: the type? change relates to a problem with "type? ... switch 
... #[datatype ..." vs "type?/word ... switch ... integer! ..." as 
the first form is not compatible with encap.

Hmm, adding /direct to the example posted previously and changing 
the last part of the INSERT to "... form $1 1 form $1 * 1" seems 
to work properly (100 error-free runs so far). The *only* difference 
then is in this line of the value binding logic:

	unless direct [val: mold/all val]

which if you change it to something like:

	unless direct [p: mold/all val]
	*bind-text sid i p length? p 0


seems to handle more runs before a failure. Thinking that mold/all 
might be the problem I then reran my /direct test with the following 
SQL statement:


 SQL reduce ["insert into t values (?,?,?,?,?)" 1 mold/all reform 
 ["A" 1] mold/all $1 1 mold/all $1 * 1]


which is functionally equivalent to the failing statement ... but 
no failures (after 100 runs). So, the conditions needed to reproduce 
this error [so far] are:

	SQLite library
	INSERT statement using a particular sequence of bind variables
	MOLD/ALL coded / used in a particular manner
	High volume of INSERTs

Now is that an obscure error or what? ;)

Ashley - I am not saying anything. I just want consistent result 
for those two cases - inserting a string into database in the same 
way, should return the same results ....