Re: [sqlite] Detecting database table changes
Use triggers to populate some table such as Changes or History or LatestChange or something. Then you can just query this one table for updates. HTH, Sam On Fri, Feb 22, 2008 at 4:28 PM, John Karp <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm using SQLite to maintain a database that is used by several > independent processes. One process is a 'viewer', and it is important > that it always be presenting the latest data. Currently, I am using > Linux's inotify feature to detect any changes to the file. This works > fairly well, except it has no way of knowing which particular tables > have changed. Is there some internal table-level timestamp or > versioning that I could access? > > Or will I need to create an auxiliary table that contains table > timestamps? Is there another, more elegant possibility I'm not > thinking of? > > Thanks in advance, > John > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Contrib down?
Clicking any of the download links is generating an error:
ERROR: attempt to write a readonly database
attempt to write a readonly database
while executing
"db eval {UPDATE file SET cnt=cnt+1 WHERE rowid=$Q(get)}"
invoked from within
"if {[info exists Q(get)]} {
db eval {UPDATE file SET cnt=cnt+1 WHERE rowid=$Q(get)}
content-type application/binary
reply-content [db one {SELEC..."
invoked from within
"::tws::eval [read $fd [file size $argv1]]"
invoked from within
"reply-content [::tws::eval [read $fd [file size $argv1]]]"
invoked from within
"evalfile main.tcl"
(What I'm actually looking for is an example of how to do loadable
functions on Windows, so if anyone has a more direct example of that,
that would be great.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing size of timestamps
Use the Sqlite method, a floating point number. Inbuilt functions support that technique. Rich Rattanni wrote: > All: > > I was wondering if there was any way to reduce the 'cost' of storing a > timestamp on entries in a SQLite database. I performed a hexdump of > the file and it showed me the timestamp is stored as a 19-byte ASCII > string. One quick thing I thought of was to store the unix timestamp > in each field, then when I wanted an actual date use > datetime(mytimeField,'unixepoch','localtime') to convert it back. > This would save me 9 bytes per record, but I would (greedily) like to > save more... any thoughts? > > -- > TIA, > Richard Rattanni > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Detecting database table changes
Hi all, I'm using SQLite to maintain a database that is used by several independent processes. One process is a 'viewer', and it is important that it always be presenting the latest data. Currently, I am using Linux's inotify feature to detect any changes to the file. This works fairly well, except it has no way of knowing which particular tables have changed. Is there some internal table-level timestamp or versioning that I could access? Or will I need to create an auxiliary table that contains table timestamps? Is there another, more elegant possibility I'm not thinking of? Thanks in advance, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing size of timestamps
Rich Rattanni wrote: > > I was wondering if there was any way to reduce the 'cost' of storing a > timestamp on entries in a SQLite database. I performed a hexdump of > the file and it showed me the timestamp is stored as a 19-byte ASCII > string. One quick thing I thought of was to store the unix timestamp > in each field, then when I wanted an actual date use > datetime(mytimeField,'unixepoch','localtime') to convert it back. > This would save me 9 bytes per record, but I would (greedily) like to > save more... any thoughts? > Storing a unix timestamp should save you 14 bytes per record. The unix timestamp is an integer value with a value that fits into 32 bits (at least until 2038). Integers are stored in a variable size format in SQLite that should use only 5 bytes per 32 bit timestamp. Storing a unix timestamp gives you resolution to one second. If you need finer resolution you can save a double precision floating point julian day number which uses 8 bytes per record. For lower storage you will probably need to go to a delta encoding where you store the time difference between records rather than the absolute time of the record. These values are harder to use, but can save considerable space and work quite well if you have lots of data stored at fairly regular intervals. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
Nicolas Williams wrote: > > Ah, good to know. Would there be interest in supporting that in SQLite? > I think it'd be very useful (particularly given the populatiry of XML). I don't know how Richard feels about it, but it is very useful for the common case of hierarchical bill of materials processing. I'm not sure how useful it would be for XML unless you were trying to store a parse tree of an XML document in your database. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
Samuel Neff wrote: > As I understand it, recursion in SQL > is referring to self-referencing tables that create a parent/child > relationship. Actually the WITH RECURSIVE clause in SQL:1999 handles multiple mutually recursive tables as well as self referential tables. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction log writing performance
[EMAIL PROTECTED] wrote: > "Brian Smith" <[EMAIL PROTECTED]> wrote: > > When SQLite writes to the log file, it > > > > (1) writes all the data, > > (2) fsyncs, then > > (3) updates the page count in the header, and finally > > (4) fsyncs again. > > > > Isn't it possible to change SQLite so that the steps 3 and 4 are > > unnecessary? > But without steps 3 and 4 and on some filesystems, a power > failure that occurs while the journal is being written can > result in database corruption. > It is true that a sufficiently strong checksum might detect > the corruption in the middle. SQLite does do some > checksumming to try to detect this sort of problem when you > specify PRAGMA synchronous=NORMAL. But checksums are not 100%. Thanks for the quick reply. I agree that checksums are not 100%. But, some checksums are close enough that the advantage of doing a single fsync can outweigh the advantage of doing two fsyncs--not just from a performance standpoint, but from a reliability standpoint, because the failure to commit the data in the event of a power failure is still data loss, especially when the SQLite database is the primary storage. The two-sync process says "In the event of a power failure (or network interruption for NFS), I am (up to 30%) less likely to save your data, but if I *do* save it, you can be nearly 100% sure that it isn't corrupted." A mechanism based on checksums is saying "In the event of a power failure or network interruption (for NFS), I am (up to X%) less likely to save your data, but if I *do* save it, you can be Y% sure that it isn't corrupted." If X is small (say 1-3%) then how large does Y need to be before the tradeoff is worthwhile? > I suspect that most modern journalling filesystems are > SAFE_APPEND, but I'll continue to assume the worst > until I know for sure. > Marco Bambini <[EMAIL PROTECTED]> wrote: > > So, is safe so to set PRAGMA synchronous=NORMAL; under MacOS X? > > I am told probably not, though Apple has never issued a > definitive statement on the question. The SAFE_APPEND attribute or synchronous=NORMAL is not really helpful for anybody developing software for other people to use on their own systems, because we cannot reliably detect filesystem characteristics at runtime, and we cannot restrict people to running only on particular filesysetms. A mechanism that eliminates steps #3 and #4 without caring about the particular filesystem would be much more useful, assuming it can reliably detect errors. But, I recognize that such a change may improve performance of small transactions, but only at the expense of very large ones. Anyway, I actually don't care so much about raw performance of individual commits as much as the lock contention; reducing the commit time reduces the time that the exclusive lock is held. Regards, Brian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unrecognized "Z" UTC time zone signifier
Wilson, Ron P wrote: > Heh. He implemented it with a goto statement! It is also interesting that the vast majority of the Zulu people do not live in the "Zulu timezone." - Brian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unrecognized "Z" UTC time zone signifier
Heh. He implemented it with a goto statement! While I personally have no problem with this, especially when it is the most efficient solution, we have a client who demands all source code comply with MISRA and I don't relish the day we have to defend SQlite and our parser (lemon generated). Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ralf Junker Sent: Friday, February 22, 2008 9:09 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Unrecognized "Z" UTC time zone signifier Aristotle Pagaltzis wrote: >* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2008-02-21 13:45]: >> Ralf Junker <[EMAIL PROTECTED]> wrote: >> > SQLite does not recognize "Z" as the zero offset time zone >> > specifier. >> >> If we start accepting any symbolic timezone names, seems like >> we would then need to start accepting them all. >Not hardly. FWIW, the IETF recommendation for timestamps in >any new internet standards is to use the format specified in >RFCÂ 3339, which is based on codified experience. For time zones, >it prescribes that they be given as either a numeric offset or >`Z` a shortcut for `+00`; no provision is made for other symbolic >names as those only cause trouble. So you should have no trouble >refusing requests to support those. Richard did it, and it works like a charm: http://www.sqlite.org/cvstrac/chngview?cn=4805 Many thanks! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing size of timestamps
Rich Rattanni wrote: > All: > > I was wondering if there was any way to reduce the 'cost' of storing a > timestamp on entries in a SQLite database. I performed a hexdump of > the file and it showed me the timestamp is stored as a 19-byte ASCII > string. One quick thing I thought of was to store the unix timestamp > in each field, then when I wanted an actual date use > datetime(mytimeField,'unixepoch','localtime') to convert it back. > This would save me 9 bytes per record, but I would (greedily) like to > save more... any thoughts? > There is the old fashioned way of storing the date time stamp as an Int64 showing the number of milliseconds since an arbitrary point in time. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reducing size of timestamps
All: I was wondering if there was any way to reduce the 'cost' of storing a timestamp on entries in a SQLite database. I performed a hexdump of the file and it showed me the timestamp is stored as a 19-byte ASCII string. One quick thing I thought of was to store the unix timestamp in each field, then when I wanted an actual date use datetime(mytimeField,'unixepoch','localtime') to convert it back. This would save me 9 bytes per record, but I would (greedily) like to save more... any thoughts? -- TIA, Richard Rattanni ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
On Fri, Feb 22, 2008 at 11:51:08AM -0500, Samuel Neff wrote: > I don't agree with the XML anaology. As I understand it, recursion in SQL > is referring to self-referencing tables that create a parent/child > relationship. This generally does not apply to XML since XML is > hierarchical but usually not recursive (i.e., the children are not the same > elements as the parents). Certainly it's possible and does happen, but for > the most part the relationship between XML elements can be easily mapped to > relational tables and does not require recursive SQL to process. You've clearly not used xml2rfc. The schema for writing Internet-Drafts and RFCs in XML certainly does have recursive elements (e.g., the and elements). > Oracle has had recursive SQL support for a while and it's very helpful when > appropriately used. MSSQL adds hierarchical data types in 2008 to support > similar functionality. Yes. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trying to locate older version of SQLite
Thank you Dennis. Martin from this mailing list sent me an email detailing the same thing. This ticket is closed :) Shibu Narayanan Consultant, PrimeSourcing Division, Investment Banking Group Tel.Office: 91-80-2208-6270 or 91-80-6659-6270 e-mail: [EMAIL PROTECTED] The answer is 42. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Friday, February 22, 2008 9:40 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] trying to locate older version of SQLite [EMAIL PROTECTED] wrote: > > I have a website on a shared hosting which has PHP 5 installed on it. I > ran a test program and created a SQLite database from it. When I opened > the database file, it has as its first line "This file contains an > SQLite 2.1 database". I am trying to download the 2.1 version of SQLite > from the internet(google) but unable to get any windows binary for it. > The SQLite.org website does not carry binaries of older versions. > > I also tried to search in the sqlite-users mailing list, but the site > does not have a search option, and the messages are stored month wise, > so I do not know whether this has been answered before on this mailing > list. > > Where can I get it? Any help would be greatly appreciated. > Older version of SQLite are aviable from the website, but there are no direct links on the web pages. You need to manually edit the links to get the file you need. The 2.1 version of the database file implies that it was created with a 2.X.Y version of SQLite. You should get the latest version which is 2.8.17 (I believe). If you go the download page http://www.sqlite.org/download.html and the right click on the link to download the latest Windows binary file, then select Copy Link Location (at least using Firefox, in IE the command is Copy Shortcut). Now open a new tab or window and paste the link into the address bar. You can edit the link and replace the version number with the version you want to download. In your case you need to change http://www.sqlite.org/sqlitedll-3_5_6.zip to http://www.sqlite.org/sqlitedll-2_8_17.zip and then press enter to start the download. This process can be used to get previous versions of the DLL and the command line shell program (and probably all other files as well). HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users DISCLAIMER: This message contains privileged and confidential information and is intended only for an individual named. If you are not the intended recipient, you should not disseminate, distribute, store, print, copy or deliver this message. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required, please request a hard-copy version. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
I don't agree with the XML anaology. As I understand it, recursion in SQL is referring to self-referencing tables that create a parent/child relationship. This generally does not apply to XML since XML is hierarchical but usually not recursive (i.e., the children are not the same elements as the parents). Certainly it's possible and does happen, but for the most part the relationship between XML elements can be easily mapped to relational tables and does not require recursive SQL to process. Oracle has had recursive SQL support for a while and it's very helpful when appropriately used. MSSQL adds hierarchical data types in 2008 to support similar functionality. HTH, Sam On Fri, Feb 22, 2008 at 11:32 AM, Nicolas Williams <[EMAIL PROTECTED]> wrote: > On Fri, Feb 22, 2008 at 09:24:06AM -0700, Dennis Cote wrote: > > As Nicolas said, SQL:1999 defines a standard method of doing this. It > > uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been > > widely implemented, but I believe that IBM's DB2 supports this > mechanism. > > Ah, good to know. Would there be interest in supporting that in SQLite? > I think it'd be very useful (particularly given the populatiry of XML). > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
Nicolas Williams wrote: > On Fri, Feb 22, 2008 at 09:24:06AM -0700, Dennis Cote wrote: > >> As Nicolas said, SQL:1999 defines a standard method of doing this. It >> uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been >> widely implemented, but I believe that IBM's DB2 supports this mechanism. >> > > Ah, good to know. Would there be interest in supporting that in SQLite? > I think it'd be very useful (particularly given the populatiry of XML). I could be mistaken, however, I believe that recursive triggers or recursive selects would be highly useful as a substitute for enforcing referential integrity, especially cascading deletes. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction log writing performance
Marco Bambini <[EMAIL PROTECTED]> wrote: > > So, is safe so to set PRAGMA synchronous=NORMAL; under MacOS X? > I am told probably not, though Apple has never issued a definitive statement on the question. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
On Fri, Feb 22, 2008 at 09:24:06AM -0700, Dennis Cote wrote: > As Nicolas said, SQL:1999 defines a standard method of doing this. It > uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been > widely implemented, but I believe that IBM's DB2 supports this mechanism. Ah, good to know. Would there be interest in supporting that in SQLite? I think it'd be very useful (particularly given the populatiry of XML). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
John Elrick wrote: > > A recursive trigger would handle this issue nicely...a way of SELECTing > a hierarchy would also be nice, but I believe there is nothing > standardized for that particular operation. > As Nicolas said, SQL:1999 defines a standard method of doing this. It uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been widely implemented, but I believe that IBM's DB2 supports this mechanism. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
On Fri, Feb 22, 2008 at 11:14:41AM -0500, John Elrick wrote: > > IIUC ANSI SQL has a WITH keyword and support for recursive queries. > > > > SQLite doesn't support this, of course. But it could (whether it will > > is another story). > > A recursive trigger would handle this issue nicely...a way of SELECTing > a hierarchy would also be nice, but I believe there is nothing > standardized for that particular operation. I'm not familiar with the specs, so I can't tell you for sure, but the results for my searches seem to indicate that WITH recursive queries are part of ANSI/ISO SQL. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction log writing performance
On Feb 22, 2008, at 4:57 PM, [EMAIL PROTECTED] wrote: > That depends on your filesystem. On many modern file > systems you can safely omit 3 and 4. And if the > xDeviceCharacteristics() method of the VFS implementation > for a particular filesystem reports SQLITE_IOCAP_SAFE_APPEND, > then SQLite skips steps 3 and 4. Steps 3 and 4 are > also skipped if you set > >PRAGMA synchronous=NORMAL; > > instead of the default > >PRAGMA synchronous=FULL; > > It has been reported to us that by omitting steps 3 and > 4 you get about a 30% speed improvement on MacOS X. So, is safe so to set PRAGMA synchronous=NORMAL; under MacOS X? It would be really nice to know under which modern file system it is safe to skip 3 and 4. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
John Elrick wrote: > I'm a bit stumped and was curious if anyone had an elegant solution for > this problem. Assuming the following simplified example, my goal is to > cascade the deletes until all parent/child relations have been deleted. > The trigger removes the first level, but stops there (I believe this > behavior is documented). I can think of a delete query which would also > remove the first level, but am having a brain lock on any single query > which would walk a chain of arbitrary length. > > Am I missing something obvious? No. > If not, does anyone have any brilliant ideas? > See http://article.gmane.org/gmane.comp.db.sqlite.general/17286/match=managing+trees+database for a method I use to manage trees in SQLite that works very well. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
Nicolas Williams wrote: > On Fri, Feb 22, 2008 at 10:46:00AM -0500, Igor Tandetnik wrote: > >> John Elrick <[EMAIL PROTECTED]> wrote: >> >>> I'm a bit stumped and was curious if anyone had an elegant solution >>> for this problem. Assuming the following simplified example, my goal >>> is to cascade the deletes until all parent/child relations have been >>> deleted. The trigger removes the first level, but stops there (I >>> believe this behavior is documented). I can think of a delete query >>> which would also remove the first level, but am having a brain lock >>> on any single query which would walk a chain of arbitrary length. >>> >> It's impossible in pure SQL, unless the DBMS supports special syntax for >> recursive queries, and/or recursive triggers. SQLite supports neither. >> You would have to code the recursion in your host application. >> > > IIUC ANSI SQL has a WITH keyword and support for recursive queries. > > SQLite doesn't support this, of course. But it could (whether it will > is another story). > A recursive trigger would handle this issue nicely...a way of SELECTing a hierarchy would also be nice, but I believe there is nothing standardized for that particular operation. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trying to locate older version of SQLite
[EMAIL PROTECTED] wrote: > > I have a website on a shared hosting which has PHP 5 installed on it. I > ran a test program and created a SQLite database from it. When I opened > the database file, it has as its first line "This file contains an > SQLite 2.1 database". I am trying to download the 2.1 version of SQLite > from the internet(google) but unable to get any windows binary for it. > The SQLite.org website does not carry binaries of older versions. > > I also tried to search in the sqlite-users mailing list, but the site > does not have a search option, and the messages are stored month wise, > so I do not know whether this has been answered before on this mailing > list. > > Where can I get it? Any help would be greatly appreciated. > Older version of SQLite are aviable from the website, but there are no direct links on the web pages. You need to manually edit the links to get the file you need. The 2.1 version of the database file implies that it was created with a 2.X.Y version of SQLite. You should get the latest version which is 2.8.17 (I believe). If you go the download page http://www.sqlite.org/download.html and the right click on the link to download the latest Windows binary file, then select Copy Link Location (at least using Firefox, in IE the command is Copy Shortcut). Now open a new tab or window and paste the link into the address bar. You can edit the link and replace the version number with the version you want to download. In your case you need to change http://www.sqlite.org/sqlitedll-3_5_6.zip to http://www.sqlite.org/sqlitedll-2_8_17.zip and then press enter to start the download. This process can be used to get previous versions of the DLL and the command line shell program (and probably all other files as well). HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
On Fri, Feb 22, 2008 at 10:46:00AM -0500, Igor Tandetnik wrote: > John Elrick <[EMAIL PROTECTED]> wrote: > > I'm a bit stumped and was curious if anyone had an elegant solution > > for this problem. Assuming the following simplified example, my goal > > is to cascade the deletes until all parent/child relations have been > > deleted. The trigger removes the first level, but stops there (I > > believe this behavior is documented). I can think of a delete query > > which would also remove the first level, but am having a brain lock > > on any single query which would walk a chain of arbitrary length. > > It's impossible in pure SQL, unless the DBMS supports special syntax for > recursive queries, and/or recursive triggers. SQLite supports neither. > You would have to code the recursion in your host application. IIUC ANSI SQL has a WITH keyword and support for recursive queries. SQLite doesn't support this, of course. But it could (whether it will is another story). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction log writing performance
"Brian Smith" <[EMAIL PROTECTED]> wrote: > When SQLite writes to the log file, it > > (1) writes all the data, > (2) fsyncs, then > (3) updates the page count in the header, and finally > (4) fsyncs again. > > Isn't it possible to change SQLite so that the steps 3 > and 4 are unnecessary? > That depends on your filesystem. On many modern file systems you can safely omit 3 and 4. And if the xDeviceCharacteristics() method of the VFS implementation for a particular filesystem reports SQLITE_IOCAP_SAFE_APPEND, then SQLite skips steps 3 and 4. Steps 3 and 4 are also skipped if you set PRAGMA synchronous=NORMAL; instead of the default PRAGMA synchronous=FULL; It has been reported to us that by omitting steps 3 and 4 you get about a 30% speed improvement on MacOS X. But without steps 3 and 4 and on some filesystems, a power failure that occurs while the journal is being written can result in database corruption. The damage happens like this: The journal is written to the disk out-of-order so that later parts of the journal are written before some earlier parts. And the power failure occurs before the entire journal is written. So you are left with something like this: Where "." indicates valid data, and "*" indicates random trash that just happen to be on the disk - not information that was written by SQLite. Once power is restore, the next SQLite process to open the file would see the "hot" journal, notice that both the header and the "end mark" are valid, but the stuff in the middle is not. That invalid stuff in the middle will get played back into the database, corrupting it. It is true that a sufficiently strong checksum might detect the corruption in the middle. SQLite does do some checksumming to try to detect this sort of problem when you specify PRAGMA synchronous=NORMAL. But checksums are not 100%. The SAFE_APPEND attribute means that the filesystem does not extend the size of the file until the content has been safely written to oxide. For filesystems that support SAFE_APPEND, there is no possibility of getting corrupt data in the file during a power failure. I suspect that most modern journalling filesystems are SAFE_APPEND, but I'll continue to assume the worst until I know for sure. Early versions of SQLite2 always assumed SAFE_APPEND. Then we got some reports from the field of corruption following power loss that was ultimately traced back to the scenario described above. That's why we added the extra fsyncs and checksums. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
Igor Tandetnik wrote: > John Elrick <[EMAIL PROTECTED]> wrote: > >> I'm a bit stumped and was curious if anyone had an elegant solution >> for this problem. Assuming the following simplified example, my goal >> is to cascade the deletes until all parent/child relations have been >> deleted. The trigger removes the first level, but stops there (I >> believe this behavior is documented). I can think of a delete query >> which would also remove the first level, but am having a brain lock >> on any single query which would walk a chain of arbitrary length. >> > > It's impossible in pure SQL, unless the DBMS supports special syntax for > recursive queries, and/or recursive triggers. SQLite supports neither. > You would have to code the recursion in your host application. > I was afraid that was the case. Thank you Igor. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
John Elrick <[EMAIL PROTECTED]> wrote: > I'm a bit stumped and was curious if anyone had an elegant solution > for this problem. Assuming the following simplified example, my goal > is to cascade the deletes until all parent/child relations have been > deleted. The trigger removes the first level, but stops there (I > believe this behavior is documented). I can think of a delete query > which would also remove the first level, but am having a brain lock > on any single query which would walk a chain of arbitrary length. It's impossible in pure SQL, unless the DBMS supports special syntax for recursive queries, and/or recursive triggers. SQLite supports neither. You would have to code the recursion in your host application. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transaction log writing performance
When SQLite writes to the log file, it (1) writes all the data, (2) fsyncs, then (3) updates the page count in the header, and finally (4) fsyncs again. Isn't it possible to change SQLite so that the steps 3 and 4 are unnecessary? In particular, if SQLite had an end of file marker for the transaction log, and if the page checksums were more reliable, then the page count in the header would be unnecessary. In particular, once the reliability offered by the checksum is as high as the reliability offered by the write ordering, then the choice between the two depends only on performance. And, as the gap between CPU performance and disk sync performance widens, it stands to reason that checksumming will be increasingly faster for more and more transactions. Is there some flaw with this idea? I did read this in the SQLite source code: ** This is not a real checksum. It is really just the sum of the ** random initial value and the page number. We experimented with ** a checksum of the entire data, but that was found to be too slow. >From this, we can see that the "checksum" currently used by SQLite is not >reliable at detecting errors on its own; a stronger checksum would be needed. >I read RFC 3385 [1], which describes the checksums used by the iSCSI protocol. >They provide evidence that their choice, CRC-32C, is very reliable at >detecting errors, while being fast and simple to implement. I haven't done my >own measurements yet, but I find it hard to believe that for typical >transactions, computing CRC-32s is going to be slower than the extra >seek+write+fsync that is required. When you did your testing, how many pages >were required to be touched before checksumming approached the slowness of >seek+write+fsync? Besides my interest in this as a SQLite user, I also am interested in this I am building a simple persistent log file system, where the entries are very small (100 bytes each) and I would like to avoid any requirement to align data on sector boundaries, I would like to avoid doing multiple fsyncs if possible, and I would like to write to the file in an append-only fashion. Also, the CouchDB developers recently raised a similar issue [2]. FWIW, I am more interested in the safety of this approach (vs. write ordering) than the performance, as my application almost never writes more than one sector per transaction. Thanks, Brian [1] http://tools.ietf.org/html/rfc3385 [2] http://damienkatz.net/2008/02/faster_couchdb.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read/write loops
Colin Yes transactions are initiate by "Begin" and end with a "commit" Can you provide a more code example. As its not clear to me at what point your getting the error. The default behavoir of a Begin transaction does not immediately lock the database for write. Take a look at "BEGIN IMMEDIATE" as it will attempt to lock at that point. The "begin" does not lock until 1. a cache spill to disk or 2 a commit. Colin Darcey <[EMAIL PROTECTED]> wrote: Ken, I am not sure how to use this facility, looks like one responds by sending another email. When you write "use transactions", do you mean "BEGIN TRANSACTION" / "COMMIT TRANSACTION". I am using these SQL commands to achieve transaction control. The first write is accepted, but the second one received the error each time I test. Colin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Hierarchical Deletion via a Trigger?
I'm a bit stumped and was curious if anyone had an elegant solution for this problem. Assuming the following simplified example, my goal is to cascade the deletes until all parent/child relations have been deleted. The trigger removes the first level, but stops there (I believe this behavior is documented). I can think of a delete query which would also remove the first level, but am having a brain lock on any single query which would walk a chain of arbitrary length. Am I missing something obvious? If not, does anyone have any brilliant ideas? John Elrick Fenestra Technologies Example...it can be assumed that the actual hierarchy will run from 0..n children for any given node. CREATE TABLE FOO ( PARENT_ID INTEGER, CHILD_ID INTEGER ); CREATE TRIGGER FOO_AD1 AFTER DELETE ON FOO BEGIN DELETE FROM FOO WHERE PARENT_ID = old.CHILD_ID; END; INSERT INTO FOO VALUES (1,2); INSERT INTO FOO VALUES (2,3); INSERT INTO FOO VALUES (2,4); INSERT INTO FOO VALUES (3,5); COMMIT; SELECT * FROM FOO => [["PARENT_ID", "CHILD_ID"], ["1", "2"], ["2", "3"], ["2", "4"], ["3", "5"]] DELETE FROM FOO WHERE PARENT_ID = 1 The result SELECT * FROM FOO => [["PARENT_ID", "CHILD_ID"], ["3", "5"]] The desired result SELECT * FROM FOO => [["PARENT_ID", "CHILD_ID"]] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please test lastest CVS using WinCE
>> Is there a recommended way to run the tests on CE? I created a >> VisualStudio project for sqlite3 but ran into some problems: >> >> 1) I couldn't see a way to generate sqlite3.h. I ended up using cygwin >> with configure then make to get me sqlite3.h, parse.h, opcodes.h and >> keywordhash.h and included those. Should I just inspect the configure >> script to see how these are generated and try and incorporate that into >> a build step in VisualStudio? > Use the pre-parsed zip files. It should work out of the box. Thanks for the help Nuno. I use the amalgamated version in my own application, and it works great, but Richard was asking for someone to test a version in CVS, so I didn't think there would be a ZIPed or amalgamated version for that. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unrecognized "Z" UTC time zone signifier
Aristotle Pagaltzis wrote: >* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2008-02-21 13:45]: >> Ralf Junker <[EMAIL PROTECTED]> wrote: >> > SQLite does not recognize "Z" as the zero offset time zone >> > specifier. >> >> If we start accepting any symbolic timezone names, seems like >> we would then need to start accepting them all. >Not hardly. FWIW, the IETF recommendation for timestamps in >any new internet standards is to use the format specified in >RFCÂ 3339, which is based on codified experience. For time zones, >it prescribes that they be given as either a numeric offset or >`Z` a shortcut for `+00`; no provision is made for other symbolic >names as those only cause trouble. So you should have no trouble >refusing requests to support those. Richard did it, and it works like a charm: http://www.sqlite.org/cvstrac/chngview?cn=4805 Many thanks! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please test lastest CVS using WinCE
On Thu, Feb 21, 2008 at 4:17 PM, Fin Springs <[EMAIL PROTECTED]> wrote: > > Can somebody with the ability to compile and test for wince > > please test check-in [4802] for me. > > Is there a recommended way to run the tests on CE? I created a > VisualStudio project for sqlite3 but ran into some problems: > > 1) I couldn't see a way to generate sqlite3.h. I ended up using cygwin > with configure then make to get me sqlite3.h, parse.h, opcodes.h and > keywordhash.h and included those. Should I just inspect the configure > script to see how these are generated and try and incorporate that into > a build step in VisualStudio? Use the pre-parsed zip files. It should work out of the box. > 2) All the test files include tcl.h which CE (at least by default) > doesn't have, so I'm not sure how to run the unit tests. I haven't yet > gone through them to see if they use TCL or not though, to see if I can > just ifdef out the include. There were people who managed to port TCL/TK to Windows CE, but if you plan to run the unit tests you need to build tcl/tk yourself, because you need the development files (the headers and libraries). The easiest way I can see is by using cegcc (cegcc.sf.net), because it works a bit like cygwin, meaning it emulates a lot of the posix functionality. The same project also includes a "mingw32-like" cross compiler for WinCE, but that may mean some tweaking. Many years ago someone managed to run the test suite on the sqlite-wince.sf.net port, so it is possible (at the time the cegcc project didn't exist yet, so now it should be easier). Also, to run the test suite you may find something like PocketConsole may be of help (to have a command prompt on CE). Off course nothing prevents you from making a small GUI front-end (or redirect output to a file). > 3) I'm not familiar with the extensions (FTx and ICU) so I'm not sure > whether they need to be compiled in for testing, or if all possible > combinations need to be built and then tested. This are optional, and I believe they don't make part of the standard tests (and I don't know of a ICU port for WinCE). > 4) What flavours of CE would you like the tests to be run on? Consumer > devices these days typically run either Pocket PC 2003 or Windows Mobile > 5 which are basically fixed configurations of Windows CE 4.2 and 5 > respectively, plus some apps (Pocket Word etc). Plain-old Windows CE > devices can have pretty much any subset of CE's features on them, as > it's up to the OEM to decide which components to include when they > configure the platform for their device. The SQLite CE port only works out-of-the-box for 4.x and latter, so I believe this two should the ones to be tested (I'm not fully sure about 4.0). There is bug in sqlite when opening files with Unicode characters with the UTF-8 version of sqlite3_open - ticket 2479 [1]. I believe that is because the OEM removed the UTF-8 character mapping from the OS build. The ticket has a possible workaround. Regards, ~Nuno Lucas [1] http://www.sqlite.org/cvstrac/tktview?tn=2479 > Cheers, > Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ & sqlite3
thank u very much, i'll try :) Dennis Cote wrote: > > vl.pavlov wrote: >> hello & thanks 4 reply >> >> ok, i think i understand, >> i would like that you, if not prob., write the whole solution once with >> index on words >> > > Here it is again. > > create table words (word text primary key, number integer); > > insert or replace into words values(:word, > coalesce((select number from words where word = :word) + 1, 1)); > > I have changed the field name above from the original count to number to > avoid confusion with the count() function in SQL. > > The primary key constraint on the word column creates a unique index on > the words with the word field in ascending order. That index is used to > quickly locate a word in the table, or determine that the word is > missing from the table. > > The :word is a parameter to the insert statement that you bind to the > word you want to insert. If you are generating the SQL as text then > simply replace that identifier with a literal string containing the word > to be inserted. > > HTH > Dennis Cote > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15633039.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
