AltME groups: search

I am a bit worried about that - all tables in one file, because of 
no readability (not plain text, but a binary) and possible file corruption, 
but it seems to me, that it works, or sqlite would not be so highly 
praised ...

sqlite is certainly used in a lot of projects: http://www.sqlite.org/cvstrac/wiki?p=SqliteUsers

sqlite3-protocol.r has a minor bug whereby locals/cols are not cleared. 
Fix is to add a "clear cols" at the beginning of the ' sqlite-exec 
func. Two other changes I made to this function were:


1) Changing “SQLITE_TEXT [any [attempt [load val: sqlite3/column_text 
stmt j] val]]” so as REBOL values are returned, and

2) Removing the /only clause from "system/words/insert/only tail 
result col" for those that prefer flat data structures (i.e. non-blocked 
records)

Finally, a simple wrapper makes the whole thing more usable:

context [

	db: none

	set 'open-db func [name [file!]] [
		db: open join sqlite://localhost/ name
	]

	set 'close-db does [
		close db
	]

	set 'describe func ['table [word!]] [

  insert db rejoin ["select type, name, sql from sqlite_master where 
  upper(tbl_name) = '" uppercase form table "' order by rootpage"]
		db/locals/sqlresult
	]

	set 'sql function [arg [string! block!]] [statement] [
		case [
			string? arg [insert db arg]
			string? first arg [
				statement: copy first arg
				repeat i -1 + length? arg [
					replace/all statement join ":" i pick arg i + 1
				]
				insert db statement
			]
		]
		db/locals/sqlresult
	]
]

which lets you do stuff like:

>> open-db %test.db
>> sql "create table t1 (col1 INTEGER, col2 TEXT)"
== []
>> describe t1
== [table t1 "CREATE TABLE t1 (col1 INTEGER, col2 TEXT)"]
>> sql reduce ["insert into t1 values (1,':1')" now/date]
== []
>> sql "select * from t1"
== [1 13-Feb-2006]
>> close-db

sqlite-exec appends column names to the locals/cols block ... but 
does not clear the block for each new query. Run a couple of queries 
and check the contents of locals/cols, you'll see what I mean (all 
this is assuming you are using the %sqlite3-protocol.r script dated 
22-Mar-2005).

yes, I am .... so I will simply put "clear cols" at the beginning 
of the function ... thanks a lot ...

1) how to influence where it stores/creates database?


Don't know, I've only been looking at all this for a day and havn't 
worked that out yet either.


2) if I would use separate file for some tables, if it would be able 
to join them


Yes. The ATTACH command lets you "hook up" to multiple databases, 
and you can prefix references with database name.

3) noticed there is brand new version of techfell protocol


Of the *four* sqlite scripts on REBOL.org %sqlite3-protocol.r is 
the one to use if you have a Pro licence.

4) those guys are really screwing with GPL license ...

Who? SQLite is PD as is %sqlite3-protocol.r

it is somehow a mess ... it does not return error when you try to 
open non-existant db - it creates one ....

Re: RebDB. I'm coming to the conclusion that SQLite is a superset 
of all I wanted from RIF; and it's:

	1) Here today
	2)Lean & mean
	3)One small (256KB) drop in DLL
	4)Works well with REBOL
	5)Public Domain


And, like RebDB, it is pretty flexible about column values (it uses 
column affinity to suggest how values may be coerced – so an INTEGER 
column could contain [1 999 “three” “four”])  which is more REBOLish 
than most other RDBMS's; and all values (TEXT, INTEGER, REAL and 
BLOB) are stored as variable byte length.


My benchmarking of RebDB vs SQLite with three tables; Customers (100), 
Orders (10 per customer) and Items (10 per order) gave the following 
results (measured in Transactions Per Second, with the first for 
RebDB and the second for SQLite):

	select * from Customers 148		120
	select * from Orders	141		11
	select * from Items	 76		 .73
	selective join on Order-Items for one order	8.93	437.23
	join on all Order-Items			.08	4.93
	size of all dat file(s)			410KB		625KB


What you have to remember is that RebDB is 100% memory-based using 
tight loops and REBOL natives where possible, versus SQLite which 
is disk-based and being called via routines.

ok, thanks a lot, maybe repost it in rebdb group, please?

The "create if not exists" is a feature of sqlite not the protcol. 
Think command line:

	sqlite3 new.db

The pathing thing is just an RTFM issue.

A short-term workaround is to do something like:

	old-dir: what-dir
	change-dir new-dir
	open-db %my-db.db
	change-dir old-dir

;)

Yep, wish I had read your message earler. ;)


The 'open func sets port/target to a string of the file name and 
port/path to a string of the path (empty if none). So you just need 
to replace the sqlite-open in the 'open func with:
 
	port/locals/dbid: sqlite-open to-file join port/path port/target

as Anton stated above.

and are you guys sure your join port/path will work? As in low-level 
you are calling a library, which will not understand rebol path anyway, 
unless converted using to-local-file ...

the only one good reason is to have unified aproach to all dbs .... 
that is a good reason, but it should not be limiting ...

so, for me, sqlite is near ideal, I give it a big minus because of 
that, in my opinion, bad design decision .... well, although I can 
imagine that they need to control locking/transacitons on file level, 
so it is easier for them to work with one file only ....

Antont - it can't be easier to fix, as it is a binary file .... I 
really like old unix mail format, which nowadays uses mozilla/netscape 
- plain text files - that is what I call easy to fix ...

As I mentioned near the beginning of this thread, SQLite supports 
multiple database files each containing one or more tables - in fact 
they go so far as recommending that you separate multiple high-access 
tables out into different databases for concurrency reasons. In this 
sense, SQLite "databases" act more like traditional "tablespaces". 
So, if we wanted we could write our REBOL front-end so that it created/accessed 
each table in a database of the same name thus ensuring a one-to-one 
mapping between table names and database names. The advantages of 
this approach are:

	backups (only those tables that change need be backed up)

 external table administration (you can drop a table by deleting its 
 database file)

 concurrency (you spread your file locking across a greater number 
 of physical files)

Disadvantages:


 Administering your database is more cumbersome (you can't use the 
 sqlite3 admin tool to administer all tables in one session)

 Value of sqlite_master is diminished (you can't "select * from sqlite_master" 
 to report on all your tables in one query)

 Query references need to add a database prefix when referring to 
 a table not in their own database

 Name conflicts (all tables in one file means multiple databases can 
 use the same table names - the solution with multiple files would 
 be to segregate at the directory level)

 Multiple database files means you need to zip them prior to some 
 operations such as email attachment, etc


On balance, I actually prefer the one file / one database approach.


Pekr's other comments in relation to schema implementation also have 
merit (I've agreed with Pekr twice today - a new record!); I see 
the value of an ftp schema, an http schema, etc; but what value in 
a sqlite schema? Given that the entire schema can be written in a 
much more concise fashion as an anonymous context that exports a 
couple of key access functions to the global context; I can't see 
what the functional differences between the two implementations would 
be?


So, bar any good reasons to the contrary, these are the features 
of the implementation I am currently working on (a rough design spec 
if you like):

	Implemented as an anonymous context

 "Database" is a directory (which is specified when a database is 
 opened with 'open-db)

 Each table resides in a "tablespace" (aka SQLite database file) of 
 the same name
	File is automatically opened on first reference

 The /blocked refinement of 'db-open specifies that rows will be returned 
 in their own block (default is a single block of values)

 Non-numeric values (which SQLite stores natively as INTEGER and REAL) 
 will be subject to 'mold/all on insert and 'load on retrieval

 The /native refinement of 'open-db will turn this behaviour off (see 
 comments below)

 SQLite binding will be supported allowing statements such as ["insert 
 into table values (?,?,?)" 1 [bob-:-mail-:-com] "Some text"] and ["select 
 * from table where email = ?" [bob-:-mail-:-com]]


Whether to store values (including string!) as molded values in SQLite 
is an interesting question; on the one hand it gives you transparent 
storage and access to REBOL values – but at the performance cost 
of having to mold and load every TEXT value returned; and the storage 
cost of the overhead of a molded representation. On the other hand, 
if I only want to store numbers and strings anyway then I don't want 
this overhead. I think the only practical solution is a /native type 
option as detailed above.

OK, need to leave to work, just a note - I think we can forget having 
one-table-per-file, as it is simply a hack with sqlite. Version prior 
to 3.0 even had problems with transactions in that regard and even 
3.0 has stupid compile limitation (like we have with callbacks) to 
something like 16 separate table-files, so ...

as for your new version - looking forward to it. Maybe we could vote 
a bit for what is default behavior and fo naming conventions .... 
when I saw /blocked, I first thought something about blocking copy, 
waiting behavior ....

e.g. Bobik prefers 'blocked mode as a default, as he can directly 
pass it to grid (Henrik and Cyphre's too IIRC)

and somehow - I liked the ability of RebDB to define dbs in a simple 
way - you just named columns and did not have to care about types 
even ....

as for visual tools - just suggest me one. I run thru installation 
of most of them. I did not find ANYTHING in a quality of mySQL Admin 
and mySQL Query. Thwo of them I liked are commercial, so imo sqlite, 
as a database of the year, is really badly supported here ....

I've given up on the one table per database idea as the default attachment 
limit is 10 files. On the positive side, the ATTACH command seems 
to work properly under 3.0+ and table names unique to an attached 
database do not have to be prefixed in queries. ;) My 'connect function:

>> help connect
USAGE:
    CONNECT database /create /attach databases /blocked /direct

DESCRIPTION:
     Open a SQLite database.
     CONNECT is a function value.

ARGUMENTS:
     database -- (Type: file url)

REFINEMENTS:
     /create -- Create database if non-existent

     /attach -- Attach up to 10 databases (of type file!) to this connection
         databases -- Database files (Type: block)
     /blocked -- Return each row as a block
     /direct -- Do not mold/load REBOL values

lets you do this:


 connect/attach %/c/app/data/system.db [%/c/app/data/users.db %/c/app/data/reports.db]
	sql "select * from a-system-table"
	sql "select * from a-user-table"
	sql "select * from a-report-table"


which could be quite useful in many situations. The default, however, 
is now one database.


BTW, my rewrite (even after adding a lot more functionality) is about 
twice as fast as the original protocol version.

I have no opinion on molded values issue .... maybe we could have 
something like /custom, with a dialect, or not so complicated, just 
/molded refinement, where you define which columns to mold - if that 
makes sense and would actually speed anything up?

<<SQLite "databases" act more like traditional "tablespaces">>
That's a nice flexible approach.

It may add an apparent unnecessary level of complexity for small 
databases, but the tablespace approach is intended to scale almost 
unlimitedly.

Think to when REBOL has taken over the world, and we have tables 
that exceed a single disk drive. Tablespaces exist (in part) to handle 
that sort of issue.

It's a data space that contains tables.

A tablespace can be split across disk volumes (or servers or machines)
And a table is defined as residing in one or more tablespaces.


The unit of back up is a tablespace -- or a database (which consists 
of 1 or more tablespaces)

It adds a lot of flexibility for large systems. But can be overkill 
for smaller ones....Where you probably just have one tablespace that 
lives in one folder.

ok, please inform us here, if you do some changes/fixes :-) Thanks 
a lot ...

Ashley - maybe I vote for functions having sql- or sqlite- prefix 
.... sql then could become sql-query .... because ... standalone 
"disconnect" in rebol script is a bit strangely looking .... in rebol 
you can disconnect from many things ... it does not look obvisous 
at first sight .... what do others think?

I would prefer setting /blocked or not, /direct or not, as a result 
fo query, so maybe those refinements should be available with /sql 
function instead?

Bumped the version to 0.1.2 with three enhancements:

1) Automatic NULL -> none! conversion (and vice versa)

2) 'sql now handles a SQLITE_BUSY return code by retrying the failing 
sqlite3_step 5 times at one second intervals

3) About value (number of retries) can be set with a new connect/timeout 
refinement

can we have opened only one db at a time? And if we want to open 
other, do we have to attach, no other chance?

I also don't agree with exposing functions as you did it, without 
prefix of sqlite or db .... that aproach is flat ... and unless it 
is not forbidden by sqlite itself, I don't see a reason to limit 
ourselves to have just one openened database .... I will shortly 
try it ...

It seems you want cursors.

Then you can separate preparing of an sql statement and fetching 
from the cursor.

This would allow to reset an already compiled statement and to redo 
 do it without a new compilation.
Or even to cache compiled statements as Caché does it.

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

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)

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.

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?

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

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?

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.

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?

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!

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.

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.

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

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

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.

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.

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

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.

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

/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?

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

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

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.

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

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

But they are not the same way ...

	SQL "insert into t values ('text')
	SQL {insert into t values ('"text"')}

map to:


 SQL ["insert into t values (?)" "text"]	; with /direct refinement

 SQL ["insert into t values (?)" "text"]	; without /direct refinement


The first approach in each case is saying, "I want this value to 
be stored as a SQLite TEXT value which will not be LOADed upon retrieval"; 
while the second is saying, "I want this value to be stored as a 
MOLDed SQLite TEXT value which will be LOADed upon retrieval back 
into a REBOL string value (as opposed to the word 'text)".


A string! statement is strictly literal, it is passed onto SQLite 
with no parsing or conversion. If you want to bind values, use the 
block form ... that's what it's there for!

But simply put - string is a string and in rebol I expect a string 
without the compromises or clumsy /direct refinement .... no wonder 
person I know remains with sqlite3 driver just because of that ...

ok, not sure now and I will retest, but the problem also is, that 
the string form allows you to use "text here" in quotes instead of 
'text here'  ...but - once you query your db, driver returns it as 
two separate words because of space - it simply ruins the block, 
because you get two elements instead of one. Bobik told me, that 
sqlite3 does not do that, so I assume it is a difference of how returned 
data is being processed. But I will do some tests using both drivers, 
because he defined his dbs using some external tool (sqlite admin 
 or so ...)

You'll find the exact same behaviour with the other sqlite3 scripts 
... none of them attempt to parse a literal statement string. The 
other scripts basically default to /direct which means that any non-numeric 
/ binary values are converted to TEXT (i.e. you can insert a REBOL 
date! but it comes back as a string!). If you want that behaviour 
then just use /direct. It's not that complex, really ...

Does sqlite support timestamp/date fields ?  Is there a way to set 
a default value for a field ( so that on a sql insert, you don't 
have to explicitly mention that column  ) ?

hmm, DBase tools have it like I said - YYYYMMDD, and it is question 
of date mask (which can be set upon locale, which rebol does not 
support :-), if you use dot, slash, whatever as a separator ...

I meant I'd like to see a full timestamp datatype support.

If it's not for human communication, the space between date und time 
is a T
so now /precise is 2006-03-21T11:06:48.232+01

In unix the time is expressed in seconds and microseconds since midnight 
(0 hour), January 1, 1970.  The resolution of the system clock is 
hardware dependent, and the time may be updated continuously or in 
``ticks.'' 

The following structures are defined in <sys/time.h> as:

struct timeval {
        long    tv_sec;         /* seconds since Jan. 1, 1970 */
        long    tv_usec;        /* and microseconds */
};

struct timezone {
        int     tz_minuteswest; /* of Greenwich */

        int     tz_dsttime;     /* type of dst correction to apply */
};


The timezone structure indicates the local time zone (measured in 
minutes of time westward from Greenwich), and a flag that, if nonzero, 
indicates that Daylight Saving time applies locally during the appropriate 
part of the year.

Pekr, "the question is, if there should be any delimiter in DB". 
There has to be, otherwise LOAD will treat "20060101" as an integer 
not a date. Remember that SQLite has no concept of "column types" 
so the MOLDed values themselves have to carry / represent the REBOL 
type when LOADed. What we are trying to do with date is to use an 
alternate representation that REBOL will still recognize as a date 
but that also happens to sort correctly; YYYY-MM-DD achieves both 
those objectives, it's just a cosmetic question as to what delimiter 
"looks" better if someone looks at the raw data (prior to being LOADed 
into REBOL values) or uses the format directly in their statements 
(e.g. "select * from t where date = '2006-01-01'").


Graham, "If they are stored as numbers, then just as easy to sort!" 
Yes, but as per above we lose the fact that they are dates. If they 
are stored as integer then we'll get them back as integers.


Graham, "what about time?" REBOL time values (in HH:MM:SS format) 
are already supported, and nothing prevents you from using now / 
now/precise except that these values will not be sorted correctly 
[with an "order by" clause that is].

Mind you, I can always extend the date handling logic (as posted 
previously) to check to see whether the date has a time component; 
something like:

	if val/time [...]


Should we be checking for a zone component as well? The shortest 
possible timestamp that REBOL will recognize is:

	type? load "2006-01-01/13:30"

But that excludfes both seconds and zone.

I was thinking that if you wanted a log, you might need timestamps 
...

Didn't played around with SQLite yet. But I expect that a result 
set is returned. Isn't it than possible to use Rebol to sort the 
result set? It can handle all datatypes.

Is the linking between SQLite and RebGUI already implemented? In 
that if I get back a result set, that I can use it directly to fill 
a list or drop-down list? Or load a result set into a form?

Yes, just use CONNECT/flat and the driver will return all values 
in a single block which RebGUI can use directly in lists and tables 
(no conversion required).

Next question, before I start using SQLit and RebGUI now. How do 
you handle the identification of table rows? Do you display the record 
ID or can the record ID be a hidden entry?

I display the record ID (which maps to a unique entity ID such as 
patient#, order#, ect) and which the end-user is usually interested 
in seeing anyway (as it ties in to their other systems). If you want 
to hide the column in RebGUI then just give it a width specification 
of 0 and ensure it is not the last column.

BTW, is there a speed gain when using direct? I only save strings 
atm, anyway.

Is it possible to check, wether a database is already connected?

Although, if you are not connected to any database, it raises an 
error just like any other function. 

I think it would be helpful to have a function to just check, maybe 
like ...


either none? database [ ... you hev to connect ....][ ... check whether 
the right database is corrected ...]