World: r3wp

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.

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

is datestamp also going to store the timezone ?

It simply MOLDs whatever now / now/precise returns. There is no "datestamp" 
type in REBOL per se (as date! covers both the short and long forms).

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

My synapse chat program also stores timestamps and needs the timezone 
so that other users not in NZ can translate the timestamp to local 
time.

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.

Agreed. It's just that almost every datatype apart from date (pair! 
is also problematic) happens to sort correctly under SQLite as is; 
and changing date's TEXT representation so that it sorts correctly 
within SQLite is fairly easy. I'd rather write:

	result: SQL "select id,date from t order by date"


than:

	result: sort/skip SQL "select id,date from t"  2

as it's both easier to maintain and more efficient.

Ok, that makes sense.

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?