World: r3wp

clear buffer

 is also an optimization as it defaults to 32K values (make block! 
 1032 * 32) and I don't won't to reallocate it each SQL call. The 
 following benchmarks (Transactions Per Second) give some backround 
 to the design decisions I made:

	buffer				1744718
	copy buffer			282
	copy/deep buffer		76

	clear buffer			1144733
	buffer: copy []			824352
	buffer: make block! 32768	387


So the approach I took optimizes for large result sets by allocating 
a large buffer once up-front and then just referencing it thereafter.

0.1.4 of the driver available at:http://www.dobeash.com/SQLite/sqlite.r


Plus new documentation now available at: http://www.dobeash.com/SQLite/

Enjoy!

Well, OK, I'm just not sure it's worth it. On the other hand, I don't 
expect a high volume access on my databases ...

I think it *is* worth it, because it gives the choice to copy or 
not to the user, instead of deciding beforehand for the user.  It 
being a somewhat unexpected optimization, however, means that it 
should be documented clearly, which Ashley is doing.

just would like to say, that I don't like the last line:
any [system/script/parent/header halt]

which throws an error if you run the script as a part of bigger context
I would use:
any [error? try [system/script/parent/header] halt]

run the script as a part of bigger context

 Not sure I understand what the issue is; do you have a small example?

not small, but imagine, that I can include your code to other script, 
which for example require sqlite

and I don't want to halt the code

and I'm using modified attempt, which will print out all errors without 
halting the script as well so I can see, what's going on - and your 
code is not clear it throws error when system/script is not defined

>> rss/run %sqlite
!!! ERRROR:
make object! [
    code: 312
    type: 'script
    id: 'cannot-use
    arg1: 'path
    arg2: 'none!
    arg3: none
    near: [any [system/script/parent/header halt]]
    where: 'attempt
]

which is because system/script/parent is none

but maybe it's my fault and I should set the system/script/parent 
to something:-)

There seems to be quite a few different ways of using / running a 
script.

Can anyone please look at http://sqlite.org/capi3ref.html#sqlite3_create_collation
and help to explain me, how I can add other collation?

it seems to me it requires pointer to some routine?

Oldes, if I 'do %sqlite.r it works fine (the parent is set correctly). 
How is your rss/run func actually DOing the %sqlite.r script? Sounds 
like you are loading the script into a context and DOing it inline?

He might be doing this :
	context load %script

The code of my rss (RebolSourceSafe) is here: http://oldes.multimedia.cz/rss/projects/rss/latest/rss.r

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)