[sqlite] TRANSACTION-question
Hi, I do the following (SQLite 308 from C++ source in windows): BEGIN DEFERRED TRANSACTION;DELETE FROM Inbox;INSERT...;COMMIT TRANSACTION; END TRANSACTION; Now, nothing is done? Nothing inserted, nothing deleted from Inbox... Then I tried to execute the same afterwards, but then I got that I am already in a transaction? Is that possible or should I look further? Does a commit end the transaction, or should I end it anyhow? Thx, KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584 pgpCVnosfvaO4.pgp Description: PGP signature
Re: [sqlite] SQLite 3.08 Deadlock in a Linux Multithreaded Multi-Process Application
On Thu, Dec 02, 2004 at 11:33:33PM +0200, Ron Avriel wrote: > Hi, > I'm using SQLite 3.0.8 on Linux in an Apache module, in a > multithreaded multi-process configuration. Occasionally, the database BOTH mulit-threaded and multi-process? Why are you doing that? Is this some sort of special Apache wackiness? (I am not familar with Apache.) > I'm NOT using transactions. To be a bit pedantic, yes you are... You may not be explicitly starting a transaction, but as with any RDBMS worthy of the name, you are using them. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] SQLite 3.08 Deadlock in a Linux Multithreaded Multi-Process Application
Ron Avriel wrote: Is it OK to use the same sqlite3 session in multiple threads but making sure that at anytime it is used by only one thread? That depends on a number of things, including (but not limited to) your Linux kernel version, the particular threading library you are using, and the state of certain environment variables. The answer is often "yes" but depending on the conditions above it can sometimes be "no". Your safest bet is to assume "no". Is there any way to get more information on the deadlock or how to avoid it? The best advice I know of is to not use threads. Use full-blown processes instead. But I'm guessing you will not be heeding that advice, so apart from that, no I don't have any other suggestions. Sorry. For additional information on the brokenness of POSIX advisory locks and of POSIX threads, and in particular how they are especially broken when used together, read the comments in the source file os_unix.c. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] SQLite 3.08 Deadlock in a Linux Multithreaded Multi-Process Application
Hi, I'm using SQLite 3.0.8 on Linux in an Apache module, in a multithreaded multi-process configuration. Occasionally, the database becomes deadlocked - all requests fail with a busy error. Even when I try to access it using sqlite command line I get "Error: database is locked" for every operation. My application is completely idle at that point. My scenario consists of concurrent inserts, updates and deletes from multiple threads from multiple processes to a single table at a high rate. I'm NOT using transactions. I'm using a POOL of sqlite3 sessions for all threads, however at any moment a session is in use by at most a single thread. I couldn't reproduce the deadlock when I used a dedicated session per thread, but I don't think it's a good idea to keep the database open dozens of times. One point maybe worth mentioning is that I saw identical thread IDs on two separate processes. The deadlock occurs both when SQLite is compiled with THREADSAFE and without it (BTW, why is the default Linux setting not thread safe?). The update command is rather complex: "UPDATE Q_DATA SET ID = ?, FLAG = ? WHERE ROWID = (SELECT ROWID FROM Q_DATA WHERE ID ISNULL AND DEQ_TIME < ? ORDER BY DEQ_TIME LIMIT 1)" This update sets a unique id and flag to a ZERO or ONE row with the smallest DEQ_TIME that is smaller than input and that was not chosen already. Later this entry is deleted based on this ID. The insert command is trivial. I also noticed that after the deadlock a database journal file is present. Is it OK to use the same sqlite3 session in multiple threads but making sure that at anytime it is used by only one thread? Is there any way to get more information on the deadlock or how to avoid it? Any help will be greatly appreciated. Ron
Re: [sqlite] Database size
amead wrote: [W]hen I browse the freshly created database (using xxd), I see long stings of zero bytes: It looks like SQLite pads records with zero bytes? Is this necessary? Are there any options I can use to make the database use less disk space? Links that might help: http://www.sqlite.org/sqlite_analyzer-3.0.5.bin.gz Download and run this program against your database to see what the space is being used for. http://www.sqlite.org/cvstrac/getfile/sqlite/src/btree.c The header comment on the btree.c source file explains the file format and the origin of the zeros that you are concerned about. http://www.sqlite.org/php2004/page-001.html Slides from a talk on the architecture of SQLite. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Database size
I have quite a lot of data. Naturally, as an SQLite 3.08 database, my data are significantly bigger than when they were a flat text file. As an aside, I'm very impressed with SQLite. Basic SELECTS in this 1.4 million record database are more or less instantaneous. The performance doing more complicated reads (e.g., with grouping and counts; doing LIKE searches) is extremely good. Anyway, when I browse the freshly created database (using xxd), I see long stings of zero bytes: 00033f0: 4255 4c4c 4354 3036 3631 312d 3337 3332 BULLCT06611-3732 0003400: 0d00 0900 6600 0066 00d1 0135 01a0 ..f..f...5.. 0003410: 0207 026f 02d4 0336 03bc ...o...6 0003420: 0003430: 0003440: 0003450: 0003460: 68b6 4b0a 001f 4d35 2123 ..h.K...M5!# 0003470: 1121 0030 3630 3436 3938 3435 434f 4d4d .!.060469845COMM Line 00033f0 is the end of one record and 0003470 is the start of the next. It looks like SQLite pads records with zero bytes? Is this necessary? Are there any options I can use to make the database use less disk space? Thanks! -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
Re: [sqlite] commas in columns and temporary tables
Taj, > Now, I've got two questions. > The first is that I have a column (company) in a customers table. The > problem is that this column has a comma in it for some rows. For example: > > sqlite> select lastname,business FROM customers WHERE id=449; > Toleser|St Lawrence University, Biology Dept. > > Now, that's all fine and everything for interactive SQL, but when I'm > using this in a program (written in Delphi), the comma messes up the > returned values (since they are comma seperated). So, the Delphi > interface ends up returning: > Toleser|St Lawrence University|Biology Dept. > > That's no good :(. Does anyone have any suggestions on what to do about > this problem? I'm using SQLite 2.8.15. You can do escaping that replaces the comma with something else when writing to the table, then converts it back after you have gotten the info from the comma-separated format. For example, URLs regularly use %XX to escape characters such as space, where XX is the hexadecimal ASCII value. So "space" (ASCII 32) will be "%20". Just remember to escape not only the comma, but also the percentage sign or whatever signals your escape sequences. > As for my question about temporary tables: How long does SQLite keep the > temporary tables around? Only for 1 query? Or until the table hasn't > been modified for X amount of time? Or something I haven't thought of > yet... Can't answer this one, sorry. Ulrik -- Ulrik Petersen, Denmark
Re: [sqlite] commas in columns and temporary tables
Taj Morton wrote: > Hi All, > First off, I've been using SQLite in an open-source POS (inventory) > program and am quite happy with it. Thank you all developers and patch > submitters! > > Now, I've got two questions. > The first is that I have a column (company) in a customers table. The > problem is that this column has a comma in it for some rows. For > example: > > sqlite> select lastname,business FROM customers WHERE id=449; > Toleser|St Lawrence University, Biology Dept. > > Now, that's all fine and everything for interactive SQL, but when I'm > using this in a program (written in Delphi), the comma messes up the > returned values (since they are comma seperated). So, the Delphi > interface ends up returning: > Toleser|St Lawrence University|Biology Dept. > > That's no good :(. Does anyone have any suggestions on what to do > about this problem? I'm using SQLite 2.8.15. If the Delphi interface handles quoted strings in the CSV data then you can quote the columns that may contain commas. Use something like this: SELECT lastname, '"' || bussiness || '"' FROM customers ... The || operator is the concatenation operator. This adds double quotes around all the bussiness name strings. > As for my question about temporary tables: How long does SQLite keep > the temporary tables around? Only for 1 query? Or until the table > hasn't > been modified for X amount of time? Or something I haven't thought of > yet... > Temporary tables are destroyed when the database connection is closed or when the user explicitly drops them. I hope this helps. Dennis Cote
Re: [sqlite] SQL "IS" operator. Was: field=null vs. field isnull
D. Richard Hipp wrote: > I have lately noticed a need for an "IS" operator in SQLite. > IS would work just like "=" for most things. The difference > is that "IS" would compares NULLs as equals. There would, > of course, need to be a corresponding "IS NOT" operator. > > You can already us the IS operator with a right-hand side > of NULL. For example: "x IS NULL" or "x IS NOT NULL". What > I am proposing is to expand IS so that the right-hand side > can be an arbitrary expression. Like this: "x IS 5" or > "x IS NOT y". > > The motivation for this change is so that one can compile > statements that use "?" as the right-hand side of IS and > then insert NULL or a value as appropriate. > > Thoughts? Hi Richard, Your proposed IS operator sounds ok, but it will be a non-standard extension to SQL supported only by SQLite. The case you mentioned as a motivation can be handled using the following expression. case when :param is null then x is null else x = :param end This expression can be used anywhere an expression using your proposed IS operator can. Note, this expresion is simplified by the use of named parameters. The user now only needs to bind a value or NULL to :param once. To use either this expression or an expression using your IS operator, the calling code must know if the value being bound to the parameter is NULL or not. It must then use either, one of the sqlite3_bind_* API calls, or the sqlite3_bind_null API call to set the value of the parameter. I suspect that it is often just as simple to use one of two different SQL queries to handle these cases. I suspect that this IS operator would be more useful when used to compare column values that already exist in tables, such as when joining tables. This is where it really breaks with standard SQL. In standard SQL NULL means the field has no value, so it can't be meaningfully compared to any other value. Your proposed IS operator would treat NULL as an additional value in each range (i.e. integers would have all possible numeric values plus NULL). Again, all these case can be handled with standard expresions combining the existing operators. For example: x is 2 => x = 2 x is not 2 => x != 2 or x is null t1.x is t2.x=> t1.x = t2.x or (t1.x is null and t2.x is null) t1.x is not t2.x=> t1.x != t2.x or (t1.x is null and t2.x is not null) or (t1.x is not null and t2.x is null) The only benefit I can see to adding a new IS operator is that it could perform the checks above (because that is really what it would be doing) in a more effiecient manner. I suspect that it would be implemented with a new VDBE opcode (or two) that would perform all these checks at once, where as the equivalent expressions will compile into many VDBE opcodes (though the code produced is not as bad as the length of the expressions make it look). I question whether users really need or want to perform queries or joins where null values are matched along with non-null values. If they do, this would be a good way to do it. At the end of the day I don't see it as being a very expensive extension to the SQL supported by SQLite, but it should be clearly documented as such. You should also document these equivalent standard expressions so users who want to avoid SQLite specific extensions can easily get the same effect with standard compatible SQL. Just my two cents. Dennis Cote
[sqlite] commas in columns and temporary tables
Hi All, First off, I've been using SQLite in an open-source POS (inventory) program and am quite happy with it. Thank you all developers and patch submitters! Now, I've got two questions. The first is that I have a column (company) in a customers table. The problem is that this column has a comma in it for some rows. For example: sqlite> select lastname,business FROM customers WHERE id=449; Toleser|St Lawrence University, Biology Dept. Now, that's all fine and everything for interactive SQL, but when I'm using this in a program (written in Delphi), the comma messes up the returned values (since they are comma seperated). So, the Delphi interface ends up returning: Toleser|St Lawrence University|Biology Dept. That's no good :(. Does anyone have any suggestions on what to do about this problem? I'm using SQLite 2.8.15. As for my question about temporary tables: How long does SQLite keep the temporary tables around? Only for 1 query? Or until the table hasn't been modified for X amount of time? Or something I haven't thought of yet... Thank, and keep up the good work! -- Taj
[sqlite] [Ann] SQLiteDb updated - on-the-fly encryption, OLEDB Data Binding and more...
Hello sqlite users, we're pleased to announce SQLiteDb (ActiveX dll wrapper written in ATL7) beta release. Help file is not ready yet, but we were forced to release it 'as is' because of current users of SQLiteDb anxious to see the new version at work. If you need any additional info, post a question on the forum (http://www.terrainformatica.com/forum/main.whtm) or contact us. We hope to be able to publish help by Monday, 06.Dec.2004. SQLiteDb has been rewritten from scratch and now is faster and better than before, including features like on-the-fly database encryption, OLEDB data binding, ADO interface compatibility and more. Check it out at http://www.terrainformatica.com/sqlitedb/.
Re: [sqlite] Legal to use sqlite docs in commercial applications ?
George Ionescu wrote: How can I distinguish between *your* documentations and everyone else's ? That's the crux of the problem, isn't it I do not have any easy solutions for you. Great care has been taken to insure that copyright releases are on file that cover all deliverable code in SQLite. Photocopies of those releases are available free of charge to paid support customers or for a small handling fee to other commericial users. But I have not gone to the trouble of obtaining releases for test code or documentation. If it really is important to you to make sure that the documentation is free of copyright, then you (or your lawyers) should contact me at the telephone number below or by email and work out an arrangement whereby I can obtain appropriate releases for you. That will involve some kind of consultation fee, but the fee shouldn't be very big - it will certainly be much less than your lawyers are getting paid. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
