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.

[Kurt Welgehausen]

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

Regards

[Chris Fletcher]

Post by Kurt Welgehausen
<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.

[Ron Aaron]

Post by Chris Fletcher

Post by Kurt Welgehausen
<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?

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

Post by Kurt Welgehausen

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

(0,)

Post by Kurt Welgehausen

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

(604010013,)

Post by Kurt Welgehausen

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

(604010015,)


HTH

Martin

----- Original Message -----
From: "Chris Fletcher" <chris-***@public.gmane.org>
To: <sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org>
Sent: Monday, April 03, 2006 11:49 PM
Subject: Re: [sqlite] last modified time or version of sqlite database

Post by Kurt Welgehausen
<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.

[Martin Jenkins]

Ignore this bit - cut and paste error <sigh>

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

Martin

[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

boris-ioIo82A0uPPIb2jZbfQ/***@public.gmane.org

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:chris-***@public.gmane.org]
Sent: Monday, April 03, 2006 3:50 PM
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
Subject: Re: [sqlite] last modified time or version of sqlite database

Post by Kurt Welgehausen
<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.

[Chris Fletcher]

Thanks for your suggestions :)

Chris.