World: r3wp

1) why did you name somehow not traditionally functions as connect 
and disconnect?


open and close are already taken and I wanted to avoid pathing (sqlite/open) 
or prefixes (sqlite-open) as the number of words added to the global 
context is relatively few and I wanted them to be meaningful words 
in their own right (same reason why VID doesn't have vid-layout, 
vid-center-face, etc). Connect and disconnect are the commands used 
to login/logout of another user in many databases; so while the context 
is not strictly correct, they are recognisable DB words.

2) why can't we support multiple name-spaces?


You can via the connect/attach refinement (and this lets you transparently 
reference tables across multiple "databases"). Why would you want 
to concurrently access multiple databases that are *not* related 
to each other in any way?

3) can we have opened only one db at a time?


Yes, bar /attach as above. The benefit of this approach is that you 
minimise the number of file locks a single process obtains, and you 
don't have to track state information (i.e. you don't need to tell 
'sql which DB you are referring to).


4) Would it also be possible to extend e.g. 'headings to return non-string 
 values?

No, as it may contain strings like "count(*)"

5) don't agree with exposing functions as you did


I tend to write CONNECT, SQL, etc in upper-case so db references 
embedded in my code stand out. Come up with a better naming scheme 
that everyone likes though and I'll adopt it. ;)

ad 1) I was referring to the context of foreign script reader understanding 
- you used db-open even for rebDB - as I said, it is a detail, but 
makes sometimes things more clearer ...


ad 2) I was imagining simply kind of server product, which can open 
multiple unrelated databases ....


ad 3) kind of disadvantage here to not refer by pointer. We are used 
to it, no? Look at /Command or other schemes - db: open some-db://..... 
conn1: first db conn2: first db ... db2: open some-db://..../other-db

ad 4) OK


ad 5) db- or sqlite- prefix or let it the way it is, I will assign 
it to sqlite: context [] directly in %sqlite.r

you used db-open even for RebDB

 ... that's because it needed to define about 20 low-level SQL statement 
 functions like 'select, 'delete, ''show, 'close, 'insert, etc that 
 conflicted with existing REBOL words and could not be easily renamed 
 (folks expect a SELECT statement, an UPDATE statement, etc or something 
 that resembles it). With the SQLite driver, all these statements 
 are handled by the SQLite library itself so we only need to provide 
 a few high-level accessor functions like SQL and DESCRIBE; these 
 types of words don't conflict with REBOL's lower-level atomic action 
 type words so there isn't the same need to group and isolate them 
 by prefix and/or context.


kind of disadvantage here to not refer by pointer. We are used to 
it, no?

 But is it *needed*? If you *must* work with multiple databases concurrently 
 and *cannot* have them attached, then SQLite is probably not the 
 right solution for you anyway ... it's an embedded C library with 
 limited concurrency and no user rights management after all. ;)

:-)

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