World: r3wp

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

it is inconsistent with:


sql ["insert into test values (1, ?, ?)" "Petr" "Krenzelok"] ; in 
this case it returns strings, not words ... imo string and block 
sql queries should be compatible

It should return always string!

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

uhm, there is no parse inside sqlite.r ;-)

either string? statement [statement][first statement]

so it may as well be sqlite library, who is causing it ....

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.

I'm not following this , but in sql ' is used as the quote character 
for literal strings, not "

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

type?/word returns word!

type? returns datatype!

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

I wonder if /direct is usefull at all and if we should have two modes 
.... does mysql driver has two modes?

and if I, and another person, independently run into problems agains 
what naturaly ppl would expect without checking docs and study some 
modes, then there is something wrong. Imo it is the same, as rebol 
shares subobjects by default - nearly EVERY person I know, run at 
some problems because of that. Of course it is by some purpose, but 
then some things are not  of "rebol is simiple" nature ...

It's simply about choice. By default the driver assumes you want 
to be dealing with the full range of REBOL datatypes. If all you 
need are the five datatypes that SQLite supports (Integer, Decimal, 
Binary, Text and Null) then use the /direct refinement. How is this 
"confusing" or "clumsy"? If you don't like the fact that the string! 
form differs from the block form then choose the one you are most 
comfortable with and stick with 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  ) ?

Are there any triggers ?

Or, autoincrementing fields ?

Triggers, Views, auto-increments - yes .... (not sure about auto-increments, 
but iirc yes)

default value - yes ...

Graham, yes to all the above except timestamp/date fields. SQLite 
only supports 5 datatypes: Integer, Decimal, Binary, Null and Text. 
The driver (unless using the /direct refinement to connect) MOLDs 
and LOADs other REBOL types such as date!, pair!, etc into SQLite 
TEXT fields so date is certainly supported at the REBOL level (although 
an "order by date" clause will not give the expected results ... 
I tend to use 'sort/skip SQL "select id,date from t" 2' type constructs 
to achieve the desired result).


Given how common this later operation is (order by date) I'm looking 
at changing the way date is bound. Instead of just MOLDing it, if 
it is transformed to YYYY-MM-DD format then not only can LOAD recognize 
it but it can be sorted (as TEXT) directly by SQLite.

The change to handle date properly is pretty simple in fact (starting 
at line#375):

					unless direct [
						val: either date? val [
							p: reform [val/year val/month val/day]
							all [val/month < 10 insert skip p 5 "0"]
							all [val/day < 10 insert skip p 8 "0"]
							poke p 5 #"/"
							poke p 8 #"/"
							p
						] [mold/all val]
					]

The only question is which delimiters do folks prefer? "-" "/" "." 
or ":"

why not change dates into gregorian values ?

what do the unix people use?

also, this doesn't handle date stamps with time/seconds.

gregorian values
 ... what's the format mask for that?

this is now in unix timestamp 1142917662

oops, I think I meant Julian day numbers.

In Dbase, the date was stored in db in YYYYMMDD, so I vote for the 
date storage change too ... really helps sorting ...

Ashley - the question is, if there should be any delimiter in DB 
:-) You can write simple copy/part at .... if you want .... and load 
will load it into rebol format anyway, no?

If they are stored as numbers, then just as easy to sort!

YYYYMMDD is the short form of the iso date, 
otherwise it should be YYYY-MM-DD, if I remember.

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

what about time??

iirc dbase does not support time datatype ...

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

it's also covered by ISO 8601
YYYY-MM-DD hh:mm:ss
or compact 
YYYYMMDDhhmmss
 I use mostly the compact form in my scripts.

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

but it is easy to convert to - replace time-value "T" " " " :-)

It is easier for the console parser

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.

Worth noting that they are UNIX seconds not UTC seconds.....UNIX 
time does not recognise leap seconds, so it's now seven (I think) 
seconds adrift.
http://en.wikipedia.org/wiki/Unix_time

Indeed, However I think the timezone files can be used to do the 
adjustment if desired, and this may happen in the future.