World: r3wp

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

Changes of note include:


 - New /format refinement of CONNECT that formats output like MySQL 
 (can be turned on/off via SQLIte/format?: true|false)
	- Widths block (SQLite/widths) added to supporrt above

 - DESCRIBE, TABLES and INDEXES functions rewritten (and simplified)
	- Added an EXPLAIN function

 - CONNECT rewritten (note that the attach refinement has been replaced 
 by the function accepting a block! of file names instead)
	- DATABASE function added

 - Experimental IMPORT function added (but not exported to global 
 context - see example for how it is used)

 - Error trapping / reporting more informative (especially if a library 
 call error occurs)
	- Example block updated (do example)

Documentation will be updated as time permits.

It's good that you make sid now a permanent menber.

why /flat was removed? /raw now seems to combine non mold/all and 
non block, what if I want molded and flat? :-)

ah, I am dumb ... that is distribution of funcionality between sql 
and connect which confused me ...

imo that should be somehow consolidated - why connect uses /flat 
to not blockify, and /direct to not mold/all, but sql function uses 
/raw for both of functionalities?

I like those features being separated as in connect, du not understand 
why 'sql has /direct for not molding, and /raw combining /direct 
and /flat ... but it does lack /flat itself :-)

ok, maybe it is just a question of finding some kind of equilibrium 
about where such refinements fits the best ...

perhaps include in sqlite a XML ouput would be a good idea !

Hi Ashley,

trouble is, I will always have to copy, because where's the sense 
in getting data that may be changed next second by the next call? 
On the other hand, adding a /no-copy refinement, with 
either no-copy [clear buffer][buffer: copy []]

Would give me a fast way to securely get my data, and I guess it 
should not slow down those who want no-copy behaviour in any ingful 
way..

/no-copy is a reasonable compromise, and fits with the /flat refinement 
in that the driver defaults to the expected "safe" behaviour. I'll 
add that to the next build.


XML output: I'm adding HTML output to the next build; is XML needed 
in addition to that? And if so, what does a "standard" table definition 
look like under XML (please provide a short example).


SQL refinements: the idea is that in most cases you specify all the 
refinements you need with CONNECT and just use SQL without any; except 
in the following two special cases:


1. SQL/direct: where you used CONNECT (without the /direct refinement), 
but need to access a *specific* table that does not need its TEXT 
column values MOLDed (e.g. sqlite_master)


2. SQL/raw: as above but flattens data. Used primary to access system 
tables / data, this ensures that values are always returned in a 
consistent format; lets you write code like:

	to file! third DATABASE


without having to worry about the format of the returned block(s).


Grammatical question. Do folks prefer I use the word INDEXES or INDICES 
to refer to more than one INDEX?

SQL/raw: For the sake of clarity, I've renamed this /flat and changed 
all "SQL/raw" calls to "SQL/flat/direct".

indexes is more readable for me, non english man, but indices ir 
probably correct, no?

the driver is already rather big, I am not sure if we should polute 
it with something it does not clearly belong there - why html outpu? 
What kind and html - I don't understand what should it do?

XML makes more sense, but dunno what kind of XML - just columns wrapped 
in <colname>value</colname>?

HTML table is much more suitable than XML, but I also think, this 
should not be part of it, it's not so difficult for anybody, to convert 
rebol block to something else. But Ashley is the author, it's up 
to him:-)

Great Ashley!

To understand where I come from: I have worked in Software Quality 
Assurance as a student... You can't get rid of ithat way of thinking 
;-) 

I've learnt, that over-optimization in a component often just doesn't 
pay on the application level. Apart from it being a major source 
of hard to find errors.

Yes, /no-copy is a good way. Simplicity and safety first.

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 ...