World: r3wp

:-)

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

thanks a lot :-)

It's designed to create the database in a Rebol's root dir? (if the 
path is not fully specified) I would expect to create the database 
in the folder where I'm (and where is the SQLite)

hehe, forget that ....

not sure ... it was really strange ... it created dbs at path of 
dll or so, dunno now ...

but I think that it is easy by simply setting db-path: your-dir-here 
... and then connect join db-path %my-db-name.db

While testing I started a script using sqlite several times, at about 
the 17th call I get this error:

** Script Error: Library error: Max Callbacks
** Where: context
** Near: sqlite-trace: make routine! [
    db [integer!]
    clb [callback! [int string!]]
    ptr [integer!]
] SQLite3lib

I'm using the following version:

	Title:		"SQLite driver"
	Owner:		"Ashley G. Trüter"
	Version:	0.1.3
	Date:		22-Feb-2006

And another question ... I have a table which conains string data 
...

sql {select * from person where firstname = "Ingo"}


does not find the row (tried with singel quotes, too). What am I 
doing wrong? It goes without saying, that the data is actually there, 
and I can find it using the following SQL

sql {select * from person where firstname like "%ingo%"}

REBOL supports a maximum of 16 callbacks; so to avoid this error 
don't do %sqlite.r more than once within a script (and there is no 
sensible reason to do so anyway).


As for strings, remember that the driver mold's them; so they are 
actually stored as "string" (inclusive of quotes). You can reference 
them in one of two ways:

	sql {select * from table where col = '"string"'}

or

	sql ["select * from table where col = ?" "string"]


The second form is preferred as the binding is handled for you by 
the driver. I'm in the process of writing a SQLite Driver Guide (that 
covers this and other tricks) but it's a week or two away at the 
moment.

Thanks Ashley,

I found out the first point by myself, and just added a check whether 
'sql has already been set before doing sqlite.r.

But so far I had no idea about the molding of strings, I could have 
tried for months, I guess ;-)

Hi Ashlsy, I found a bug in sqlite.r. 'sql reuses its own return 
value on subsequent calls. so ...

>> all: sql "select * from persons"
== [["Ivo" "Hohmann" ...] ....]
>> me: sql {select * from persons where firstname = '"ingo"'}
== [["Ingo" "Hohmann" ...] ...]
>> all =? me
== true

Deliberate design that. The last line of 'sql is simply:

	buffer

not:

	copy/deep buffer


This is important when dealing with a large number of values as you 
want to pass a reference not double the amount of memory used with 
a redundant copy/deep! I'll add this "gotcha" to the documentation 
I'm writing.

Actually, there is no need to copy/deep buffer.
Just change 
	clear buffer
to
	buffer: copy []

there is no problem with integer, decimal, and none values regarding 
sharing.
Blob data is debased, which implicitly creates a new string.

Strings are normally loaded, which creates a new string. only when 
you use /raw, you are dependend on the sqlite.dll having a sane interface 
and not reusing the returned string data. You could add this as a 
possible gotcha.

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!