Re: Re[6]: [sqlite] Accessing Database on Network
On Wed, 2005-08-03 at 08:46 -0500, Henry Miller wrote: > >In my opinion system time stamps etc are not a reliable means of > >comparing 2 files. Many things can change the timestamp of a file, > >without changing the contents, and one (server) os/filesystem can > >report a different file size to another (local) for the same file > >(contents). As I said already, I think having a version number > >embedded in the databse itself is much more relible. > > You should be running NTP (network time protocol) on all computers. > This will keep all your system times to within milliseconds. Unix > systems keep the last modified times tamp separately. Microsoft > Windows sets (resets? I can never remember) the archive bit, which > could be abused to tell you when a file is modified - at the cost of > breaking backups so I can't recommend it. NTP isn't relevant. Set the mtime to whatever you saw on the server using wstat() or utime() or what have you. Don't bother trying "to get close".
[sqlite] Slow Queries, FROM order and indexes
Hello All, I've using SQLite 2.8.16 with a music management application, and I'm trying out why certain queries take a long time and to figure out how SQLite uses my indexes. I've got a master "media" table with a couple of auxiliary tables like "artists", "albums", and "genres". Each table's primary key is id (mediaid, artistid, albumid, and genreid), and the media table contains columns to each of the side tables' primary key (and an index for each one, too). I found that a particular query takes forever when run on a particular database: SELECT DISTINCT albums.albumid, albums.name_lower FROM artists, albums, media WHERE albums.media_count > 0 AND media.albumid=albums.albumid AND media.artistid=artists.artistid ORDER BY artists.name_key, albums.name_key; Well, the largest table (by far) in these queries is always the media table, and I found that by moving the media table to the beginning of the FROM list, it runs almost instantly: SELECT DISTINCT albums.albumid, albums.name_lower FROM media, artists, albums WHERE albums.media_count > 0 AND media.albumid=albums.albumid AND media.artistid=artists.artistid ORDER BY artists.name_key, albums.name_key; Thus, when "media" is first, SQLite scans each record in the media table and tests the smaller tables with the NotExists command which uses the primary key to locate the record immediately. I've been reading up on the virtual machine, op-codes, and performance tips in the wiki for the past few days, and I've got a few questions about what was happening: 1) Shouldn't it be faster to iterate through one of the smaller tables and then use an index on the media table to join with the other small table? 2) It's unclear to me what order my FROM tables should be in. In the first query, did I accidently choose the absolute worst order for the FROM list? So the tables in the FROM clause should appear in the order that tables are introduced, with special consideration to the first table in the list be the primary value we are SELECTing? In the query above, that would mean "FROM albums, media, artists". (Both "FROM media, albums, artists" and "FROM albums, media, artists" run too fast for eye-ball speed compare.) 3) What role, if any, do the ORDER BY columns play in the FROM order? (I would assume none since by the time you've selected a row, you have all of the data for the sorting of that row.) 4) Is there a query optimizer for sqlite? A program that automagically discovered which queries were not in optimal form that could propose a different order or alternate values for certain terms in FROM and WHERE clauses? I'm thinking that the input would be a query and an already existing database (with indexes), and the output would be a (better) optimized query. This would be especially useful for SQLite since everything must be hand-tuned. Thanks! Aaron
Re: [sqlite] how to get last autoincrement value?
Austin Ziegler wrote: On 8/3/05, Khamis Abuelkomboz <[EMAIL PROTECTED]> wrote: Hi I'm using SQLite in my application and am very excited about this little and fast database. How can I retrieve the last created autoincrement value? On MySQL and SQLServer or just the SQL standard I can fire the following select statement: SELECT @@IDENTITY Actually, as far as I know, SELECT @@IDENTITY isn't part of any ANSI SQL standard. It cerrtainly won't work on Oracle ;) That said, you can get the identity value of an autoincremented key with sqlite3_last_insert_rowid (http://sqlite.org/capi3ref.html#sqlite3_last_insert_rowid). It's something of a cheat, but it works and works very well. -austin In SQLite you can also do the same thing directly from SQL if you want. There is a corresponding SQL function called last_insert_rowid(). So you can use; select last_insert_rowid(); This will return the rowid of the last inserted row, which is the key for a table with an integer primary key (SQLite's version of autoincrement keys). It can also be used to get the rowid for tables with other kinds of keys or even no user specified key. HTH Dennis Cote
re: [sqlite] Query locking up SQLite
David Fowler wrote: Query 2: SELECT * FROM table1, table2 WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id) > OR (table1.value LIKE "%different_value%" AND table1.table2_id = table2.id)); This query (and even more complex versions of it) works in MySQL (Haven't tried another DB yet) and I'm trying to migrate to > SQLite, this is really holding me back. Hi David Have you ever tried your statement as following: SELECT * FROM table1, table2 WHERE table1.table2_id = table2.id and (table1.value LIKE "%value%" or table1.value LIKE "%different_value%"); khamis -- Try Code-Navigator on http://www.codenav.com a source code navigating, analysis and developing tool. It supports following languages: * C/C++ * Java * .NET (including CSharp, VB.Net and other .NET components) * Classic Visual Basic * PHP, HTML, XML, ASP, CSS * Tcl/Tk, * Perl * Python * SQL, * m4 Preprocessor * Cobol
Re: [sqlite] how to get last autoincrement value?
On 8/3/05, Khamis Abuelkomboz <[EMAIL PROTECTED]> wrote: > Hi > > I'm using SQLite in my application and am very excited about this little > and fast database. > > How can I retrieve the last created autoincrement value? > On MySQL and SQLServer or just the SQL standard I can fire the following > select statement: > SELECT @@IDENTITY Actually, as far as I know, SELECT @@IDENTITY isn't part of any ANSI SQL standard. It cerrtainly won't work on Oracle ;) That said, you can get the identity value of an autoincremented key with sqlite3_last_insert_rowid (http://sqlite.org/capi3ref.html#sqlite3_last_insert_rowid). It's something of a cheat, but it works and works very well. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
[sqlite] how to get last autoincrement value?
Hi I'm using SQLite in my application and am very excited about this little and fast database. How can I retrieve the last created autoincrement value? On MySQL and SQLServer or just the SQL standard I can fire the following select statement: SELECT @@IDENTITY Is there a similar way to do so in SQLite? Thanks Khamis -- Try Code-Navigator on http://www.codenav.com a source code navigating, analysis and developing tool. It supports following languages: * C/C++ * Java * .NET (including CSharp, VB.Net and other .NET components) * Classic Visual Basic * PHP, HTML, XML, ASP, CSS * Tcl/Tk, * Perl * Python * SQL, * m4 Preprocessor * Cobol
[sqlite] Qn on Transaction across 2 DBs
Hi, I have a requirement where I need to update 2 tables in 2 separate DBs within a transaction. Do I need to attach the second table to the first at the start of the transaction? As I understand SQLite commits a transaction across a single DB only? Thanks!
Re: [sqlite] import function
Jens: Thanks. I did see that suggested elsewhere in this list and I am proceeding that way in order to use any of the shell functionality. Thanks, Dan Jens Miltner wrote: Am 03.08.2005 um 20:50 schrieb Dan Wellisch: Hello: I understand there is the .import function for the sqlite3 command interface, but I need to execute this function from within the C API. What do I use to do this? I need to execute this type of function from within my C++ program, not the command line. You could, e.g., just use the code the sqlite3 shell uses. Search shell.c for "import" and you'll see what it does...
Re: [sqlite] import function
Am 03.08.2005 um 20:50 schrieb Dan Wellisch: Hello: I understand there is the .import function for the sqlite3 command interface, but I need to execute this function from within the C API. What do I use to do this? I need to execute this type of function from within my C++ program, not the command line. You could, e.g., just use the code the sqlite3 shell uses. Search shell.c for "import" and you'll see what it does...
[sqlite] import function
Hello: I understand there is the .import function for the sqlite3 command interface, but I need to execute this function from within the C API. What do I use to do this? I need to execute this type of function from within my C++ program, not the command line. Thanks, Dan
Re: Re[6]: [sqlite] Accessing Database on Network
On 8/3/2005 at 10:34 djm wrote: >Wednesday, August 3, 2005, 4:41:24 AM, you wrote: > >> No, none of those things are guaranteed. If there's even a single >> writer it can be unsafe (consider AFS putting a corrupt journal up >> that clients notice download, and thrash their local copy). > >But Im saying there -wont- be a single writer (except the rare update >process on the server, during which all other clients wont be >reading), and the question is it it always safe then? No. It might be on your particular systems, but we cannot make a general case. It is possible that it will always be safe on (for example) Windows 2000, while not safe on NT or XP! It depends on how the OS handles caching. They are supposed to check on each file access to see if the file changed, but networks are slow, so they might not.Thus you could start reading data that is in the cache, which is out of date. You may get different results from AFS, NFS, Coda, Samba, or Windows networking. Not to mention different versions of each protocol, and implementation. (Windows file sharing uses the same protocol as Samba, but that doesn't mean they handle caching the same!) >> Okay. that's what's important. That update procedure is completely >> unsafe UNLESS you can guarantee that the sqlite database will be >> overwritten atomically. Most operating systems don't have such an >> operation- the closest thing being rename(). > >And this is presumably only important if another client is reading the >file while its being updated. If no client is reading the file on the >server during update (and none are writing anyways), then the os and >the filesystem should be irrelevant, right? No, because we don't know what caching the OS is doing. It is possible for the remote OS to not look at the server at all if it still remembers the part of the file you are asking for after the write! Even writing your own file system may not work if the OS is caching things above the file system. That said, most OSes get this right these days. So if you correctly shut down all your readers, then to a write, then start them back up again, you might consider the problems rare enough to ignore. In fact they may never happen in practice, but in theory they could. What will work is to replace all the places where sqlite reads the file to instead talk to some server on the network you write. This is the same as writing a network file system, but you control ALL the layers, so you know what caching is going on. This is a lot of work to get right, and generally a poor use of time, but it is an option. >In my opinion system time stamps etc are not a reliable means of >comparing 2 files. Many things can change the timestamp of a file, >without changing the contents, and one (server) os/filesystem can >report a different file size to another (local) for the same file >(contents). As I said already, I think having a version number >embedded in the databse itself is much more relible. You should be running NTP (network time protocol) on all computers. This will keep all your system times to within milliseconds. Unix systems keep the last modified times tamp separately. Microsoft Windows sets (resets? I can never remember) the archive bit, which could be abused to tell you when a file is modified - at the cost of breaking backups so I can't recommend it. >> you said "I need to access a database on the network (x), [how do I] >> do this safely (y)". > > I didnt. Youre rephrasing my post, to suit your means, and I think > the point is degenerating in the process. Not really. We are just reading between the lines. Your questions look suspiciously like his rephrasing. Don't be offended if we are answering the question we think you should have asked, not the one you did. The correct answer to your question you should ask is to use something like postgresql/Oracle/(I can think of half a dozen other choices). Sqlite was not designed for what you want to do. Now there may be good reasons you cannot use a different database. That is a whole different issue. We cannot make decisions for you, but we can point out what looks like a bad decision from the parts of the problem we know (we know that we don't know all your issues). Bottom line: what you want to do will probably work without problem. However if you want a strong statement you need to have your lawyers contact your OS vender(s) and get a contract in writing that they will guarantee that this will work. Salesmen will lie, and the documentation may not account for the latest changes, but when you have a contract in writing you can at least get relief if things do not work. Asking management for this forces them to place a value on data if nothing else, which may help drive the decision on what to do.
Re: Re[6]: [sqlite] Accessing Database on Network
On Wed, 2005-08-03 at 10:34 +0200, djm wrote: > Hello, > > Wednesday, August 3, 2005, 4:41:24 AM, you wrote: > > > No, none of those things are guaranteed. If there's even a single > > writer it can be unsafe (consider AFS putting a corrupt journal up > > that clients notice download, and thrash their local copy). > > But Im saying there -wont- be a single writer (except the rare update > process on the server, during which all other clients wont be > reading), and the question is it it always safe then? The answer is "sometimes not". Sometimes, it's even "often not". It really depends on your situation. It depends on what networked filesystem you're using, and what operating system you're using. In the case of UNIX and AFS- if you have all clients turned off - or you do it during AFS replication, then yes. > > Okay. that's what's important. That update procedure is completely > > unsafe UNLESS you can guarantee that the sqlite database will be > > overwritten atomically. Most operating systems don't have such an > > operation- the closest thing being rename(). > > And this is presumably only important if another client is reading the > file while its being updated. If no client is reading the file on the > server during update (and none are writing anyways), then the os and > the filesystem should be irrelevant, right? It depends on what you mean by "no client is reading." If you really mean the machines are turned off [and have no cache to upload], then maybe. If you mean that AND you're doing the update "on the server" (instead of by using another node/client/workstation) then the answer is "probably". > > If you're willing to do that, why not download the database > > periodically? Why not store the database via HTTP or otherwise check > > the mtime of it, and download it anew if the size/mtime doesn't > > match? > > > This'll be a lot safer AND provides a mechanism by which corrupt > > databases can otherwise be transparently updated. > > Downloading a copy of the database is indeed is indeed a good > suggestion, but it doesnt change my original question. > > In my opinion system time stamps etc are not a reliable means of > comparing 2 files. Many things can change the timestamp of a file, > without changing the contents, and one (server) os/filesystem can > report a different file size to another (local) for the same file > (contents). As I said already, I think having a version number > embedded in the databse itself is much more relible. Wrong. The sqlite database is binary. Any size check is going to be in 8-bit bytes and will always be the same- except for platforms that SQLite doesn't run on- any comparison with a system that doesn't have 8-bit bytes will have to be aware of that fact. Changing the timestamp COULD be enough to have clients interrogate it- for example, perform some rsync operations on the file, or check in a prearranged place for a generation number. Either store the version/generation number in a separate file or hack the database to store it in the header. IIRC there are a few unused bytes there. Your OS might have other freshness checks (unix, for example, can use inodes if you always rename()), but even if they don't, there are other mechanisms: * Encoding the generation number/version into the filename * Update some EA (attribute) * Update the file's mtime to some value THAT IT WASN'T before (by recording all used mtimes) You still haven't said what platform you're developing for. > So the question is still is it always safe if 2 clients are > simultaneously reading this version info from the database (or for > that matter, but its not sqlite specific, if 2 clients are > accessing/running the executable file) ? The answer is still one of "usually", "probably" or "maybe". By using the methods I suggest the answer is "yes". > > you said "I need to access a database on the network (x), [how do I] > > do this safely (y)". > > I didnt. Youre rephrasing my post, to suit your means, and I think > the point is degenerating in the process. And as I said already I > wasnt trying to be rude, and Im sorry if it seemed so. But if you > insist on interpreting it as such I suppose I cant stop you. I'm showing you how I read your original post. Is this critically different than what you're saying? If so, how?
Re: [sqlite] Segmentation fault on large selects
Am 02.08.2005 um 19:18 schrieb D. Richard Hipp: On Tue, 2005-08-02 at 09:30 -0400, D. Richard Hipp wrote: On Mon, 2005-08-01 at 22:04 +0200, Jens Miltner wrote: we get an assertion (no crash here, though) in btree.c and the backtrace looks similar to the one scunacc provided, which made me think the two might be related... I am able to reproduce the bug described in ticket #1346. It looks like this may be the same problem. I will let everybody know as soon as I have a fix. Patches to fix ticket #1346 are available at http://www.sqlite.org/cvstrac/chngview?cn=2573 Please try adding these patches and see if they do not fix the problem in the multi-threaded application. Thanks, from a first quick test, this seems to fix our problem with the assertion. BTW, SQLite runs about 2x faster if you compile with -DNDEBUG=1 which disables all of the assert() statements. Yes, I figured that running with debug code is not optimal, but we only link against the debugging build of sqlite in the debug version of our app, which is why we saw the assertions. In the release builds, we link against sqlite built with -DNDEBUG=1 (or actually, configure run without the --enable-debug option, which I assume, will generate the -DNDEBUG=1 C option). Thanks again for the quick fix.
Re: [sqlite] Documentation
Hello L. S., you can create a HTML version by running the TCL scripts which are part of the tarball sources. I have compiled a MS Windows HTML Help document from these. The Help file is part of DISQLite3, a Delphi wrapper of SQLite3. Even if you do not use Delphi, you might still be interested in the HTML Help file if you are using a Windows OS. You can download DISQLite3 from http://www.yunqa.de/delphi/sqlite3/ After extracting the package you will fill find the DISQLite3.chm HTML Help file ready to use. Regars, Ralf >Where is the downloadable documentation? It was supposed to have been in >cluded with the tarball sources, but I don't see it.
Re[6]: [sqlite] Accessing Database on Network
Hello, Wednesday, August 3, 2005, 4:41:24 AM, you wrote: > No, none of those things are guaranteed. If there's even a single > writer it can be unsafe (consider AFS putting a corrupt journal up > that clients notice download, and thrash their local copy). But Im saying there -wont- be a single writer (except the rare update process on the server, during which all other clients wont be reading), and the question is it it always safe then? > Okay. that's what's important. That update procedure is completely > unsafe UNLESS you can guarantee that the sqlite database will be > overwritten atomically. Most operating systems don't have such an > operation- the closest thing being rename(). And this is presumably only important if another client is reading the file while its being updated. If no client is reading the file on the server during update (and none are writing anyways), then the os and the filesystem should be irrelevant, right? > If you're willing to do that, why not download the database > periodically? Why not store the database via HTTP or otherwise check > the mtime of it, and download it anew if the size/mtime doesn't > match? > This'll be a lot safer AND provides a mechanism by which corrupt > databases can otherwise be transparently updated. Downloading a copy of the database is indeed is indeed a good suggestion, but it doesnt change my original question. In my opinion system time stamps etc are not a reliable means of comparing 2 files. Many things can change the timestamp of a file, without changing the contents, and one (server) os/filesystem can report a different file size to another (local) for the same file (contents). As I said already, I think having a version number embedded in the databse itself is much more relible. So the question is still is it always safe if 2 clients are simultaneously reading this version info from the database (or for that matter, but its not sqlite specific, if 2 clients are accessing/running the executable file) ? > you said "I need to access a database on the network (x), [how do I] > do this safely (y)". I didnt. Youre rephrasing my post, to suit your means, and I think the point is degenerating in the process. And as I said already I wasnt trying to be rude, and Im sorry if it seemed so. But if you insist on interpreting it as such I suppose I cant stop you.
