World: r3wp

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

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

Value Binding: Is something like this possible as well?

	SQL ["select * from ? where col_1 = ?" mytable 1]

IMO it would be very handy, because those statements can be prepared 
and assigned to rebol words, for simple access in an application. 
It the SQL statement changes, I only have to do it at one place.

Not sure, havn't tried yet.

I got an error in the 'sql func ...


** Script Error: length? expected series argument of type: series 
port tuple bitset struct
** Where: switch
** Near: *bind-text sid i val length?

the database is opened with /direct refinement.

The call is:
	sql ["select * from person where guid = ?" guid1]


Where I know, that the dataset with this guid exists, because I have 
just got it from another selsct.
The dataset contains only strings, some of them empty.


Well, this is it: ["h-o-h.org_20060326_182311691_1224" "Urte" "Hermann" 
"Urmeli" "" "" "" "" "" "" "" "" "" "" "Opera ID: 359" "" "" ""]

And I am using the right guid.

Any ideas?

Uuups, seems I fed the wrong data ... 

guid1 in the previous post was not the full guid as a thought, but 
the first character of said guid.

So, the error in one small message: 

>> sql ["select * from person where guid = ?" #"a"]

** Script Error: length? expected series argument of type: series 
port tuple bitset struct
** Where: switch
** Near: *bind-text sid i val length?

If you're using CONNECT/direct then char is not an available datatype 
(and you would have had the same problem trying to insert a char). 
What does the raw data look like? I suspect it is: {#"a"}

No, actually it is something like "abcdef", I had one 'first too 
many in my code. That was the problem.

It''s just that I stumbled about the error, and didn't know what 
to make out of it. At least at first.

Is it somehow possible to encrypt the content of databases?

Ok, there seem to be some companies that offer special SQLite DLLs. 
Ashley, maybe you can have a look to see if the DLL interface is 
compatible? I think there shouldn't be big changes necessary. Take 
a look at: www.sqlcrypt.com

It's just adding two APIs to SQLite.

I'm going to check it out.

You could always encrypt selective columns client-side (e.g. sql 
reduce ["insert into t values (?,?)" key encrypt string])

Yes, but for querying I need to encrypt the data as well to be able 
to select it. Don't know if this is a big performance hit.

If it's only TEXT types you need to encrypt then we could always 
add a /secure refinement to CONNECT that would force encrypt / decrypt 
on all TEXT bind variables. Performance wouldn't be too bad as values 
would only be encrypted [once] on INSERT and SELECT, the actual query 
(which could potentially hit millions of rows) would be comparing 
encrypted strings and would only need to decrypt strings that form 
part of the result set. Very similiar to the overhead that MOLD/LOAD 
currently incur when not using the /direct refinement.