more sqlite, session management issues

[Oliver Bienert <ob...@waldwerk.de>]

Hi all,

found some more bugs I think.

session-create.sql:

Because in sqlite, FOREIGN KEY constraints are parsed but are not 
enforced, a trigger is needed to mimick ON DELETE CASCADE. I added the 
following statement at the end of session-create.sql (Found this hint at 
http://www.justatheory.com/computers/databases/sqlite/):

create trigger fkd_session_id
before DELETE ON rivet_session
for each row begin
    DELETE FROM rivet_session_cache WHERE session_id = OLD.session_id;
end;


dio_Sqlite.tcl:

Sqlite doesn't know about timestamp type or function (See 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions), so I added a 
bit of code before actually running the sql statements (See the code 
snippet enclosed in comment):

    method exec {req} {
        open

        if {[$dbcmd complete $req] == 0} {
        append req ";"
            if {[$dbcmd complete $req] == 0} {
                return -code error "Incomplete SQL"
        }
        }

         ### convert to valid statements for sqlite

         if {[::string match -nocase "delete from rivet_session where 
timestamp 'now'*" $req]} {
        set req "DELETE FROM rivet_session WHERE 
julianday('now','localtime')*86400 - 
julianday(session_update_time)*86400 > 86400;"
         }\
         else {
        regsub -all -- {'now'} $req {datetime('now','localtime')} req
         }

         ### end convert

        set obj [::DIO::SqliteResult #auto -request $req -dbcmd $dbcmd]
        ...
        ...


Oliver


---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org