World: r3wp

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?

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

Great! :-)

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.

... or you could explicitly name all columns you want to display 
play in your select statement

	select * from persons; 

becomes

	select firstname, lastname from person;

the concatenation operator || does not work. If I do:

	SELECT firstname || lastname AS name from person;

I still get blocks of 2 values, not one.

Replace the column-text block in the SQL function with:

	[(
		either direct [
			[*column-text (sid) idx]
		][
			[
				s: v: *column-text (sid) idx
				while [s: find s {""}] [change/part s "" 2]
				load v
			]
		]
	)]

I've added this to the next build.

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


Two main fixes are date bind value handling and concatenated string 
handling (both of which were posted previously as code snippets).

0.1.9 posted with improved concatenation handling logic. In addition 
to handling strings you can now also do:

	SQL "select name||100 from customers"

and

	SQL {select id||'"-'"||name from customers}


Most REBOL types are supported (in concatenation type operations) 
although blocks may cause problems.

thanks Ashley. I already found out, that it works when using direct. 
Now going to check the new versions.

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

Direct: definite speed gain as MOLD / LOAD is bypassed for each and 
every value bound / retrieved. /flat (if your data structure can 
use it) is also faster and uses less memory - although the gain is 
more noticeable with larger numbers of rows.

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

DATABASE function?

initial sync...

Right, that's it! Don't know why I haven't seen it ;-)