Re: [sqlite] last modified time or version of sqlite database

[Chris Fletcher]

Thanks for your suggestions :)

Chris.

[sqlite] last modified time or version of sqlite database

[Chris Fletcher]

I am generating web pages from an sqlite database.

I would like to generate an ETag header so I need some handle on when
the database has changed, either a last modified time or a revision
number of the data.

Is there an easy way to get this out of SQLite?

In this case the db is very simple so I can easily add a new column (or
in fact use an existing column) but I wondered if there was a more
general solution.

Thanks,

Chris.

Re: [sqlite] last modified time or version of sqlite database

[Kurt Welgehausen]

http://sqlite.org/capi3ref.html#sqlite3_total_changes

Regards

Re: [sqlite] last modified time or version of sqlite database

[Chris Fletcher]

http://sqlite.org/capi3ref.html#sqlite3_total_changes


Thanks - but this seems to give the number of changes during the  
lifetime of a db session. With CGI the sessions will be short lived.  
On a new session I want to know when the db was last modified (or  
some other indication of changes).


Regards,

Chris.

RE: [sqlite] last modified time or version of sqlite database

[Boris Popov]

Umm, how about a last modified at file attribute on your db file?

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-Original Message-
From: Chris Fletcher [mailto:[EMAIL PROTECTED] 
Sent: Monday, April 03, 2006 3:50 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] last modified time or version of sqlite database

 http://sqlite.org/capi3ref.html#sqlite3_total_changes

Thanks - but this seems to give the number of changes during the  
lifetime of a db session. With CGI the sessions will be short lived.  
On a new session I want to know when the db was last modified (or  
some other indication of changes).

Regards,

Chris.


smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] last modified time or version of sqlite database

[Ron Aaron]

On Mon, April 3, 2006 15:49, Chris Fletcher wrote:
 http://sqlite.org/capi3ref.html#sqlite3_total_changes

 Thanks - but this seems to give the number of changes during the
 lifetime of a db session. With CGI the sessions will be short lived.
 On a new session I want to know when the db was last modified (or
 some other indication of changes).

Why not use a trigger on insert/update etc to update a table with one row and
column, the time of last modification?  Alternatively, ensure your INSERTs etc
update that table when they are done modifying?

-- 
RonWare.ORG - Precision Crafted Software   http://ronware.org/
Reva Forth for Linux and Windows  http://ronware.org/reva/

Re: [sqlite] last modified time or version of sqlite database

[Martin Jenkins]

Near the bottom of http://sqlite.org/pragma.html you'll find:

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = integer ;
The pragmas [...] user_version are used to set or get the [...] 
user-version, [which is a] 32-bit signed integers stored in the database 
header. [...] The user-version is not used internally by SQLite. It may be 
used by applications for any purpose.


A quick python session:


import apsw
con=apsw.Connection(aaa)
con=apsw.Connection(temp)
con=apsw.Connection(temp)
csr=con.cursor()
csr.execute(PRAGMA user_version).next()

(0,)

csr.execute(PRAGMA user_version=0604010013)
csr.execute(PRAGMA user_version).next()

(604010013,)

csr.execute(PRAGMA user_version=0604010015)
csr.execute(PRAGMA user_version).next()

(604010015,)


HTH

Martin

- Original Message - 
From: Chris Fletcher [EMAIL PROTECTED]

To: sqlite-users@sqlite.org
Sent: Monday, April 03, 2006 11:49 PM
Subject: Re: [sqlite] last modified time or version of sqlite database



http://sqlite.org/capi3ref.html#sqlite3_total_changes


Thanks - but this seems to give the number of changes during the  lifetime 
of a db session. With CGI the sessions will be short lived.  On a new 
session I want to know when the db was last modified (or  some other 
indication of changes).


Regards,

Chris.

Re: [sqlite] last modified time or version of sqlite database

[Martin Jenkins]

Ignore this bit - cut and paste error sigh


con=apsw.Connection(aaa)
con=apsw.Connection(temp)


Martin