Re: [sqlite] WAL, threads, shared cache, etc
On Wed, Apr 13, 2011 at 9:05 AM, Mark Hamburg m...@grubmah.com wrote: I have a database for which I essentially want to support three streams of operations: writing, reading fast queries (e.g., lookup a single record), and reading complex queries (e.g., find all of the records matching a particular criterion). I would like to have these run with as little interference as possible between them. I'm on iOS, so I can't use processes (just to avoid the whole don't use threads, use processes! spiel). That last point, however, leads to the issue that the SQLite documentation says very little about threading other than SQLite is threadsafe, don't use threads. So, I wanted to see whether I have the right checklist in mind for implementing this: Mozilla does something similar with it's places.sqlite database. 1. Use WAL mode so that the reads and the writes can proceed essentially in parallel. (Reads can interfere with checkpoints but assuming the system quiesces often and checkpoints then, that shouldn't be an extended problem.) Yes, you want to do this. 2. Use one thread (or on iOS probably one GCD dispatch queue) per stream of work. And this. 3. Open a connection per thread? Yes. 4. Shared cache? Yes or no? You do not want to do this. Doing so will make all your connections have the same cache which sounds good right up until you find out that it means all access to the cache is serialized between all the connections. Hope this helps! Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL for single user database on NFS and Solaris
On Mon, Jan 31, 2011 at 5:44 AM, Dan Kennedy danielk1...@gmail.com wrote: Right. At most a single connection at a time. Unfortunately, that won't work for this issue since we use more than one connection in our process. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL for single user database on NFS and Solaris
On Sun, Jan 30, 2011 at 8:26 PM, Dan Kennedy danielk1...@gmail.com wrote: If you set PRAGMA locking_mode=EXCLUSIVE before reading or writing the WAL-mode database it might work. That would make us only be able to use one database connection though, would it not? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: copying vacuum
On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich j...@kreibi.ch wrote: On a personal level, I don't think it is worth it. In the end, you're still hoping the OS and filesystem will make smart choices about block allocations. An application shouldn't need to be spending a lot of time worrying about this level of filesystem performance. No matter what, you're just hinting and setting up conditions that should allow the filesystem driver to do something smart and fast. It may, or it may not, actually do so. Right, but giving it more hints means it's more likely to do the smart and fast thing. For what it's worth, Taras is working on improving the performance of SQLite in Firefox. He has data that shows that this can dramatically reduce the number of page faults (Taras, please correct me if I'm misrepresenting things) when loading the database. These leads to faster startup times of Firefox. Cheers, Shawn Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] upstreaming Chromium patches for file handle passing support
On Wed, Sep 1, 2010 at 5:57 PM, Paweł Hajdan, Jr. phajdan...@chromium.org wrote: Additionally, browsers seem to be moving to the multi-process architecture (Chrome, Firefox, WebKit2), so I wouldn't be surprised if you get more questions about this in the future, or just more forked copies. FWIW, this won't be a concern for Mozilla. Cheers, Shawn Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?
On Thu, Aug 19, 2010 at 2:56 PM, Simon Slavin slav...@bigfraud.org wrote: It might be worth noting that fragmentation is normally seen as an issue only under Windows which is very sensitive to it however. Other operating systems use different ways of handling disk access, however, real figures from real-world examples may disprove this classic view. Also, many installations of SQLite are on solid state devices where, of course, fragmentation has no effect at all. Really? I can think of at least 350 million installations of SQLite that very likely aren't on an SSD (hint: it's a web browser). Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PATCH] cache preloading
On Mon, Aug 16, 2010 at 5:13 PM, Paweł Hajdan, Jr. phajdan...@chromium.org wrote: Is it something you'd like to include in SQLite? If so, does the patch need any adjustments before that's possible? I'm slightly concerned about licensing here - do we know what license this patch is? Can we get clarification from the chromium team? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mozilla's method
Where did you see this? It's inaccurate, and if I can, I'll remove it. If you want to use a proper asynchronous statement execution which wraps your statement[s] in a transaction, you want to use this: https://developer.mozilla.org/En/Storage#Asynchronously Cheers, Shawn Wilsher Mozilla Developer On Thu, Dec 31, 2009 at 8:53 AM, Bert Nelsen bert.nel...@googlemail.comwrote: Hello, I would like to make writes to my SQLite db faster. I was thinking about the Async method, but I think I remember reading somewhere that it may cause database corruption. Now I read something on the Mozilla pages, and I don't understand what exactely they are doing. Do they bundle everything in transactions only or do they in fact use the Async method? It is not clear to me by reading through their articles. It would be nice if somebody could clear me up on this issue. Here is the article: Lazy writing Mozilla has relaxed the ACID requirements in order to speed up commits. In particular, we have dropped durability. This means that when a commit returns, you are not guaranteed that the commit has gone through. If the power goes out right away, that commit may (or may not) be lost. However, we still support the other (ACI) requirements. This means that the database will not get corrupted. If the power goes out immediately after a commit, the transaction will be like it was rolled back: the database will still be in a consistent state. Higher commit performance is achieved by writing to the database from a separate thread (see storage/src/mozStorageAsyncIO.cpp http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageAsyncIO.cpp which is associated with the storage service in storage/src/mozStorageService.cpp http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageService.cpp ). The main database thread does everything exactly as it did before. However, we have overridden the file operations and everything comes through the AsnycIO module. This file is based on test_async.chttp://www.sqlite.org/cvstrac/rlog?f=sqlite/src/test_async.c from the sqlite distribution. The AsyncIO module packages writes up in messages and puts them on the write thread's message queue. This write thread waits for messages and processes them as fast as it can. This means that writes, locking, and most importantly, disk syncs, only block the AsyncIO thread. Reads are done synchronously, taking into account unwritten data still in the buffer. ___ 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] Mozilla's method
On Thu, Dec 31, 2009 at 9:06 AM, Bert Nelsen bert.nel...@googlemail.comwrote: It's here: https://developer.mozilla.org/en/Storage:Performance Thanks. I've removed the outdated information. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] undefined reference to sqlite3_mutex_held
On Thu, Dec 10, 2009 at 10:18 AM, Daniel Mierswa impu...@impulze.orgwrote: My concolusion is that the TB folks shouldn't assume that the system sqlite library was built with debugging symbols or provide a mechanism to opt out said function call with an easy switch/compiler flag. Well, there is your problem. Building Thunderbird with system SQLite is not supported. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] undefined reference to sqlite3_mutex_held
See https://bugzilla.mozilla.org/show_bug.cgi?id=533171 (this isn't really a SQLite issue). Cheers, Shawn On Wed, Dec 9, 2009 at 3:32 PM, Daniel Mierswa impu...@impulze.org wrote: Hi list, when I try to build the current thunderbird 3.0 release against sqlite-3.6.21 i get an undefined reference to sqlite3_mutex_held, with 3.6.19 that didn't occur. When i compile sqlite3 with -DSQLITE_DEBUG the symbol is exported, now I wonder if this symbol is not supposed to be exported in a release build, in whch case the header should reflect that, or if it's a bug in the current source? Or am I totally off? Thanks for reading. -- Mierswa, Daniel If you still don't like it, that's ok: that's why I'm boss. I simply know better than you do. --- Linus Torvalds, comp.os.linux.advocacy, 1996/07/22 ___ 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] The next release of SQLite....
On Thu, Dec 3, 2009 at 10:26 AM, Tim Romano tim.rom...@yahoo.com wrote: The alternative, NOCASE collation, also does not get me out of the woods. For some reason in Adobe (and in Mozilla) the index is not used on LIKE clauses when the column in question has NOCASE collation, though SQLite3.EXE does use the index in its query plan on the same query. These consortium members might be overriding the LIKE function or compiling statements not with _V2 or doing something else that prevents the optimization. I don't know. Mozilla does override the LIKE function because we need to be able to handle Unicode, which the default implementation does not do. The implementation is here: http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageSQLFunctions.cpp#408 And we'd happily accept patches to fix this issue. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting SQLITE_BUSY within a transaction and how to handle it
On Mon, Mar 16, 2009 at 10:02 PM, Dennis Volodomanov dennis.volodoma...@conceiva.com wrote: Do I need the finalize the statement and re-prepare it again in the second thread before trying to step it? Are you at least reseting the statement? I hit this once before, and reseting the statement fixed the issue for me. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Thread-safety and sqlite3_stmt objects
Hey all, I've been looking online for a bit trying to establish what protections, if any, are associated with sqlite3_stmt objects. It's clearly documented that sqlite3 objects' access is serialized across threads, but I cannot find anything about sqlite3_stmt. I don't actually care either way, but if SQLite protects it internally, I don't want to add additional overhead by protecting it myself. Could someone please clarify this (and maybe add some documentation)? Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thread-safety and sqlite3_stmt objects
On Fri, Mar 6, 2009 at 9:26 PM, Dan danielk1...@gmail.com wrote: Why do you want to use a single sqlite3_stmt* from multiple threads at the same time? Really the only thing I need to access is sqlite3_sql to copy the statement to another thread. We have an asynchronous API that we expose to add-ons and core code in Mozilla. Right now, when a consumer calls executeAsync on a statement, we make a new copy of the statement, transfer the bindings, and pass the new one to the thread that executes the statement. However, profiling shows that creating a statement can often be an expensive operation. I'm looking at just giving the second thread the statement, and if the original thread needs the statement again, I want to clone it off of the original. Note: I'm leaving out some details here that probably aren't important. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS 3 Crash in 3.6.5
Hey all,
I seem to have found a crash that is 100% reproducible in SQLite 3.6.5. I'm
managed to make a reduced test case in a C file that can be found here:
http://files.shawnwilsher.com/2008/11/12/test.c
The file is compiled with the following command:
gcc sqlite3.c test.c -DSQLITE_SECURE_DELETE=1 -DTHREADSAFE=1 -DSQLITE_CORE=1
-DSQLITE_ENABLE_FTS3=1
The program crashes when we try to commit the transaction with the following
stack trace:
Program received signal EXC_BAD_ACCESS, Could not access memory.
Reason: KERN_PROTECTION_FAILURE at address: 0x
0x0005d258 in sqlite3VtabSync (db=0x100168, pzErrmsg=0x101b58) at
sqlite3.c:75690
75690 for(i=0; rc==SQLITE_OK idb-nVTrans aVTrans[i]; i++){
(gdb) bt
#0 0x0005d258 in sqlite3VtabSync (db=0x100168, pzErrmsg=0x101b58) at
sqlite3.c:75690
#1 0x00024d47 in vdbeCommit (db=0x100168, p=0x101ac8) at sqlite3.c:42890
#2 0x0002550e in sqlite3VdbeHalt (p=0x101ac8) at sqlite3.c:43230
#3 0x000291a9 in sqlite3VdbeExec (p=0x101ac8) at sqlite3.c:46357
#4 0x000272b1 in sqlite3Step (p=0x101ac8) at sqlite3.c:44607
#5 0x0002757a in sqlite3_step (pStmt=0x101ac8) at sqlite3.c:44671
#6 0x0006e54e in segdir_max_index (v=0x102108, iLevel=0, pidx=0xbfffed98)
at sqlite3.c:87702
#7 0x000744d4 in segdirNextIndex (v=0x102108, iLevel=0, pidx=0xbfffed98) at
sqlite3.c:90949
#8 0x000754b7 in writeZeroSegment (v=0x102108, pTerms=0x1021c4) at
sqlite3.c:91477
#9 0x0007574d in flushPendingTerms (v=0x102108) at sqlite3.c:91535
#10 0x00075a50 in fulltextSync (pVtab=0x102108) at sqlite3.c:91643
#11 0x0005d20a in sqlite3VtabSync (db=0x100168, pzErrmsg=0x101228) at
sqlite3.c:75695
#12 0x00024d47 in vdbeCommit (db=0x100168, p=0x101198) at sqlite3.c:42890
#13 0x0002550e in sqlite3VdbeHalt (p=0x101198) at sqlite3.c:43230
#14 0x0002c0e3 in sqlite3VdbeExec (p=0x101198) at sqlite3.c:47952
#15 0x000272b1 in sqlite3Step (p=0x101198) at sqlite3.c:44607
#16 0x0002757a in sqlite3_step (pStmt=0x101198) at sqlite3.c:44671
#17 0x0004a9aa in sqlite3_exec (db=0x100168, zSql=0x7ffca COMMIT
TRANSACTION, xCallback=0, pArg=0x0, pzErrMsg=0x0) at sqlite3.c:65582
#18 0x000790b9 in main () at test.c:25
Cheers,
Shawn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2
On Mon, Sep 22, 2008 at 8:23 AM, D. Richard Hipp [EMAIL PROTECTED] wrote: In the two high-profile use cases, the programmers already have the statement using the correct index without an INDEX BY clause. They just want to be alerted if some future schema change alters the index choice, perhaps by deleting one of the indexes that were being used. If the INDEX BY clause becomes a hint, then this function of the clause is removed. And without the impetus of those two high-profile use cases, the functionality will not be added at all. So, I am offering this choice: (1) The ability to select and index with an error if that index won't work and (2) no new capabilities at all. I can see option (1) being useful to Mozilla, so we'd like to see that if possible. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Google Chrome and SQLite3
On Mon, Sep 8, 2008 at 1:30 PM, Rich Rattanni [EMAIL PROTECTED] wrote: So after playing around in my application data directory for google chrome, I noticed file called something-journal. Of course, I knew what that was. So I began opening all kinds of SQLite databases in use by Chrome (had to close chrome due to locks on a few of them). Interesting the things chrome tracks. For instance it actually records, for each site you go to, how many times you manually type it in (or so I assume). I wanted to ask anyone if they have done any cool data mining / reports on their surfing habits, or any neat hacks to Chrome with respect to sqlite? Firefox also uses SQLite to store it's history and bookmarks. There have been some things done with it - the most recent one that comes to mind is this: http://surfmind.com/muzings/?p=154 Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transactions and Threads
Hey all, I'm looking to clarify the behavior of transactions when it comes to threads. When using the same sqlite3 object, and you begin a transaction on one thread, does it also group work that is being done on another thread until you end the transaction? Or is it the case that each thread can have it's own transaction pending on the database? Cheers, Shawn Wilsher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible View bug
On Sat, Aug 9, 2008 at 9:39 PM, Igor Tandetnik [EMAIL PROTECTED] wrote: Your trigger uses a curious syntax for SQL INSERT statement, along the lines of I was hoping it was a bug in my code and not in SQLite. Thanks for spotting my fascinating syntax. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible View bug
Hey all, I think I managed to hit a bug with sqlite and views. I try to insert onto the view (I have a trigger setup to handle it), but sqlite gives me the following error: no such column: from_visit Here is test file that demonstrates the bug: http://files.shawnwilsher.com/2008/8/9/test-bug.c Any advice? Cheers, Shawn Wilsher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and updating VIEWs
Hey all, A month later, I have an updated plan for this with many more details. I'd really appreciate it if folks would take a look and point out any issues that you see. Here's my blog post explaining the current plan with extensive details: http://shawnwilsher.com/archives/169 Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite support stored procedure?
On Tue, Jul 29, 2008 at 11:39 AM, John Stanton [EMAIL PROTECTED] wrote: Adding Javascript to Sqlite as a stored procedure language was a fairly simple operation. Try it if you need stored procedures. Woah - that sounds neat and something interesting to the Mozilla project. Care to elaborate on this? Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] New feature suggestion
Hey all, Over at mozilla, we have an interest to keep other applications from writing to our database. To accomplish this, we open the database with an exclusive lock using the locking_mode pragma. However, this means other processes cannot even read our database. What we'd like to see is a new locking mode that gives you exclusive write access, but allows for any number of readers. This would still have the same performance benefit of using exclusive locking because sqlite will know that the database won't even change, but will allow other readers to look at the data. Let me know what you think (and if this is even feasible). Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.0 coming soon...
I believe there is a typo in Section 1.1 in 1.c. You have filesyste-specific where I think you mean filesystem-specific. Cheers, Shawn On Sat, Jul 12, 2008 at 5:00 PM, D. Richard Hipp [EMAIL PROTECTED] wrote: The current plan is to release SQLite version 3.6.0 on Wednesday, 2008-07-16. Draft documentation on version 3.6.0 is available at http://www.sqlite.org/draft/doc/index.html . Please pay particular attention to: http://www.sqlite.org/draft/doc/35to36.html If you sees any problems with the upcoming release, or finds omissions or errors or ambiguities in the documentation, now would be a very good time to speak up. Thank you for your attention. D. Richard Hipp [EMAIL PROTECTED] ___ 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] Periodical dump of in-memory database into a disk file
You can try to help your issue by running PRAGMA synchronous = OFF; after you open your connection. Cheers, Shawn Wilsher Mozilla Developer On Thu, Jul 10, 2008 at 3:04 PM, X Wang [EMAIL PROTECTED] wrote: Hi, I have a sqlite in-memory databse that I want to periodically dump to a disk file (so I can look into its contents at runtime). Currently I ATTACH an extern file. Periodically I export everything from in-memory databse into the attached extern file and do a final COMMIT. However, this sometimes takes a long time (20 seconds) and totally freeze my multithreaded process(why would this freeze my network I/O threads? Maybe because those threads also do some logging?)... This is on Linux ext3, I also read a bit about Firefox's issue, so I think this is due to fsck. For my issue, actually I do not care too much about data integrity. Is there a way to reduce sqlite's fsck to minimum? Thx ___ 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] Subselect question
Hey all, Quick (and hopefully simple) question regarding subselects in a where clause. Does sqlite cache the values of a subselect so it doesn't have to run the query each time it evaluates a row? Example: SELECT * FROM foo WHERE id NOT IN (SELECT id FROM bar) Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite and updating VIEWs
Hey all, I'm working with a partitioned table setup with a permanent table and a temp table with the same columns and indexes. Every X time we dump all the records in the temp table over to the permanent one. In order to make selection queries easier to manage, I've gone and created a view like so: CREATE TEMPORARY VIEW table_view AS SELECT * FROM table_temp UNION SELECT * FROM table This was all going well, until I realized that updating was going to be very hard (insertion always goes to the temporary table). That seemed easy enough to manage if I use an INSTEAD OF trigger on the view for UPDATE statements. The problem is what I want to do in the trigger, which is this: 1) if the data is in the temporary table, update that 2) if the data is not in the temporary table, copy the data from the permanent table into the temp one, and then update the temp table Sadly, my SQL-fu isn't strong enough to know how to do this, and I'm starting to wonder if it's even possible. If someone could tell me if I can do it, and then provide a pointer as to how to go about it, I'd really appreciate it. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and updating VIEWs
On Tue, Jul 1, 2008 at 3:24 PM, Igor Tandetnik [EMAIL PROTECTED] wrote: CREATE TRIGGER BEGIN insert into temp_table select * from perm_table where the data is currently not in temp table and which record you want copied; update temp_table set ...; END; I had thought of this, but I'm pretty sure this will only work correctly the first time you try to update the view. Subsequent calls will try to copy the data into the temp table, but correctly fail. However, that means the update will never actually run, correct? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and updating VIEWs
On Tue, Jul 1, 2008 at 3:37 PM, Igor Tandetnik [EMAIL PROTECTED] wrote: They won't fail - they will successfully insert zero records. It's perfectly valid to run INSERT ... SELECT and have the SELECT part produce an empty resultset. It simply does nothing. Sorry, I should have been more explicit in what we are doing. The select will return results because we do not want to delete the data from the temporary table (that involves a write and fsyncing). However, I think I've come up with a solution: CREATE TRIGGER BEGIN INSERT OR REPLACE INTO temp_table SELECT * FROM table_view WHERE the data is currently not in temp table AND which record you want copied; UPDATE temp_table SET ...; END; This works since the UNION will select entries from the temp table first, and ignore those in the permanent table that have the same primary key (that is, assuming I understand UNION properly). I'm also making the assumption that it is valid to query the view that the trigger is running on. Is this a sound approach? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and updating VIEWs
On Tue, Jul 1, 2008 at 4:05 PM, Igor Tandetnik [EMAIL PROTECTED] wrote: Will it? the data is currently not in temp table part is supposed to be false when the data is in fact currently in temp table. A WHERE clause that is always false will, naturally, produce no records. Fair. I hadn't thought of doing something like that. because we do not want to delete the data from the temporary table (that involves a write and fsyncing). However, I think I've come up with a solution: CREATE TRIGGER BEGIN INSERT OR REPLACE INTO temp_table REPLACE clause works by deleting a conflicting record then inserting a new one. Since you say you don't want to delete records from temp_table, I don't quite see what you are gaining. We don't want to delete from the permanent table because we are trying to avoid the write and fsync. Our temporary table is in memory (although regardless of that, temp tables don't fsync). UNION has nothing to do with primary key. It only eliminates duplicate records - records with all fields equal. Hrm, that means I have a whole other problem to solve now :( Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA temp_store confusion
Hey all, Over at mozilla we are looking into using more temporary tables, and likely want them all to be in memory as opposed to files. I was looking at http://sqlite.org/pragma.html#pragma_temp_store, and noticed the table, which seems to imply that if TEMP_STORE is either zero or not defined, temporary tables are always written to a file. Is this correct, or is the documentation a bit misleading? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Setting where AUTOINCREMENT starts?
Hey all, I was wondering if we could set the value that an AUTOINCREMENT starts at for temporary tables. Right now we are looking at having to manage it ourselves, but if we could use sqlite to handle it, as long as it starts at the right value, that would be ideal. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA temp_store confusion
On Mon, Jun 30, 2008 at 6:41 PM, D. Richard Hipp [EMAIL PROTECTED] wrote: The default TEMP_STORE is 1, not 0. The default PRAGMA temp_store is 0. TEMP_STORE=1 means that temporary storage defaults to a file but can be overridden by the temp_store pragma. Alright, cool. Temporary storage is never fsync-ed. For that matter, temporary files are always delete-on-close. So in an OS with a good disk cache, little or no real disk I/O ever actually occurs on temp files. Instead, all the data just gets moved in and out of cache blocks in the kernel. This normally works better than storing the temp data in memory since after the temp data is deleted, the disk cache block can be reused by other processes. But writes to the disk can still hurt us with ext3 (and similar file systems) once we call fsync on anything else. We are trying to minimize the number of writes to alleviate this problem. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing SQLite from Javascript in Firefox
What you are looking for is the HTML 5 spec from the WHATWG. This contains a section on using SQL, but it is not yet implemented in Firefox (I think Opera is the only browser that supports it, and only when 9.5 comes out). Cheers, Shawn Wilsher Mozilla Developer On Tue, Jun 10, 2008 at 10:32 AM, Ujval Mysore [EMAIL PROTECTED] wrote: Hi Igor, Can you please help me to understand the point - Only script running in chrome (basically, in FireFox extensions) can access XPCOM components. Excuse for my ignorance. I was able to use SQLite in Internet Explorer using client side ADO. I am looking for a similar solution for Firefox. Is there any way in which I can achieve this without using any Firefox extensions? Thanks, Ujval -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Tuesday, June 10, 2008 5:32 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Accessing SQLite from Javascript in Firefox Ujval Mysore [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Has anyone tried accessing SQLite from Javascript in Firefox? I found an Sqlite helper library for Mozilla at http://codesnippets.joyent.com/posts/show/1030 . But when I try to execute the same, firefox throws the following exception uncaught exception: Permission denied to get property UnnamedClass.classes Any clues what this exception means? Only script running in chrome (basically, in FireFox extensions) can access XPCOM components, including Mozilla Storage ones. Script running on an HTML page cannot, for security reasons. Hence permission denied. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CAUTION - Disclaimer * This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** ___ 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] Version number in sqlite3.pc file
On Thu, Jun 5, 2008 at 11:08 AM, Richard Hipp [EMAIL PROTECTED] wrote: My understanding is that firefox uses the amalgamation, not anything generated from a configure script. The SQLite amalgamation is checked into their source tree. So I am thinking that FF does not care about the sqlite3.pc file. But I am not an expert on FF and might well be wrong about that. I think he might be referring to a configure check when mozilla is compiled with the system sqlite. This is STRONGLY DISCOURAGED, but most linux distros use it anyway. Cheers, Shawn Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite bug on AFP?
On Thu, Jun 5, 2008 at 4:04 PM, D. Richard Hipp [EMAIL PROTECTED] wrote: I think the solution might be as simple as compiling with - DSQLITE_ENABLE_LOCKING_STYLE=1. This option only works on a Mac. It enables some Apple-contributed code that does file locking that works on AFP as well as on other network filesystems that the Mac supports. Would this change out locking works on a normal local file system? At one time it was the case that FF handled all of its own locking such that the SQLite database locking was really unnecessary. Is that still the case? If so, then perhaps the simplest solution here would be to provide a new compile-time option to disable all of the locking logic on all systems. We lock the profile so more than one instance cannot access it, however consumers could access a database anywhere. Additionally, it wouldn't prevent other sqlite consumers from accessing these databases. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Firefox 3 and the SQLite bug
It was mentioned in the bug that opening the file with the O_SYNC flag would no longer require fsyncs. Has this been looked into before by sqlite? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Firefox 3 and the SQLite bug
Thanks! I've posted that information in the bug. Cheers, Shawn On Thu, May 22, 2008 at 4:09 PM, D. Richard Hipp [EMAIL PROTECTED] wrote: On May 22, 2008, at 9:40 AM, Shawn Wilsher wrote: It was mentioned in the bug that opening the file with the O_SYNC flag would no longer require fsyncs. Has this been looked into before by sqlite? I have a prepared a version of SQLite that uses O_SYNC on the main database file and its journal and never calls fsync(). I ran this on SuSE 10.1 x86 and found that preformance was roughly half of what we got using fsync() (with synchronous=FULL). Here are the numbers: O_SYNC: real13m6.918s user 0m14.693s sys 0m22.329s fsync: real 7m5.159s user0m14.745s sys 0m11.049s But versions were compiled with -Os. Gcc version 4.1.0. Of course, your mileage may vary, but based on the magnitude of the difference seen above, I'm thinking that O_SYNC is probably a bad idea. As a point of comparison, the same code compiled with - DSQLITE_NO_SYNC=1 is between 40 and 70 times faster: real0m10.479s user 0m6.736s sys 0m3.732s Oh, what a difference a disk cache makes. D. Richard Hipp [EMAIL PROTECTED] ___ 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] SQLite and Threadsafety (again)
Hey all, I've come to the sad realization that we need to make our sqlite wrapper threadsafe so it can be used on multiple threads without consumers having to worry about threadsafety themselves. So, I wanted to make sure all my assumptions about sqlite data structures are correct so I don't introduce issues before undertaking this task. First, I know that the sqlite3 object can be accessed on multiple threads, but it must only be used by one thread of control at a time. It is also my understanding that this same constraint applies to sqlite3_stmt objects - they can only be used by one thread of control at a time but accessed on multiple ones. What I am not so sure about, however, is if I have to protect the sqlite3 object that owns the statement when I'm calling methods on it such as sqlite3_bind_* interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize. Conservatively, I'm assuming yes on all of the above, but I hope I'm wrong for at least some of those. I would, however, expect to have to protect the sqlite3 object when calling sqlite3_prepare_v2. Clarification on this would be greatly appreciated. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and Threadsafety (again)
The problem with the approach you suggest as that that does not work when PRAGMA locking_mode = EXCLUSIVE, which we use in many places to improve performance. Additionally, it's my understanding that when using the shared cache, that transactions are grouped across connection objects (it's possible I misread a past e-mail though). We use the shared cache for every database connection (although, I've been wondering as of late if it's really worthwhile). It is unfortunate that we'll lose the ability to do multiple reads at the same time, however. I'm open to suggestions on a better way to fix this problem. Cheers, Shawn On Wed, May 21, 2008 at 1:05 PM, Virgilio Alexandre Fornazin [EMAIL PROTECTED] wrote: Not to putting flame in question, but why not use any connection per thread ? At this way you can guarantee: - Correct transaction processing; - Avoid waiting on R/W locks, allowing more than one read to run concurrently; We also use this model with ODBC / ADO database layers. You don't need to take care if your database drivers provides thread safety, handle multiple active result sets (client-side cursors), last insert row id concurrency, etc. We tried to use a single connection per process, but after changed to one connection per thread model, the gains we got avoiding synchronization was bigger than we imaginated. To get this changes working best, we created a database connection pool, that we use to get the connections by their ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections that will not be used by any thread to avoid resource leaking. Is this case, assuming that the unique ID of the database is the file name (SQLite database file name), you can get this behaviour to work transparently for your consumers (I assume you´re not using directly the sqlite3_* calls inside your program, you have some kind of high-level abstraction to use them). -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher Sent: quarta-feira, 21 de maio de 2008 13:08 To: General Discussion of SQLite Database Subject: [sqlite] SQLite and Threadsafety (again) Hey all, I've come to the sad realization that we need to make our sqlite wrapper threadsafe so it can be used on multiple threads without consumers having to worry about threadsafety themselves. So, I wanted to make sure all my assumptions about sqlite data structures are correct so I don't introduce issues before undertaking this task. First, I know that the sqlite3 object can be accessed on multiple threads, but it must only be used by one thread of control at a time. It is also my understanding that this same constraint applies to sqlite3_stmt objects - they can only be used by one thread of control at a time but accessed on multiple ones. What I am not so sure about, however, is if I have to protect the sqlite3 object that owns the statement when I'm calling methods on it such as sqlite3_bind_* interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize. Conservatively, I'm assuming yes on all of the above, but I hope I'm wrong for at least some of those. I would, however, expect to have to protect the sqlite3 object when calling sqlite3_prepare_v2. Clarification on this would be greatly appreciated. Cheers, Shawn Wilsher Mozilla Developer ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On Sat, May 17, 2008 at 2:39 AM, Dan [EMAIL PROTECTED] wrote: And a fun follow-up question. Will sqlite3_transfer_bindings transfer bindings across connection objects if the two statements are for two different connections to the same database? No. It will return SQLITE_MISUSE. Drat. It doesn't look like there's a way to see what's already been bound to a statement either, correct? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On Sat, May 17, 2008 at 10:13 AM, Dan [EMAIL PROTECTED] wrote: How are you going to 'clone' the statement objects to pass to the second database handle? Our wrapper around the statement object already stores the string of the sql statement, so that part is easy. Looks like we'll have to keep track of bound parameters as well now. Cheers, Shawn Wilsher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
And a fun follow-up question. Will sqlite3_transfer_bindings transfer bindings across connection objects if the two statements are for two different connections to the same database? Cheers, Shawn On Tue, May 13, 2008 at 2:05 PM, Shawn Wilsher [EMAIL PROTECTED] wrote: I was looking through the documentation and was wondering why sqlite3_transfer_bindings has been marked as obsolete. It's something that we use currently in our code, and I was looking to use it again for something new. Is there a new way to accomplish the same thing that this function does? What was the rational for removing it. If you need a use case for why Mozilla needs it, I'd be happy to oblige. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_transfer_bindings obsolete?
I was looking through the documentation and was wondering why sqlite3_transfer_bindings has been marked as obsolete. It's something that we use currently in our code, and I was looking to use it again for something new. Is there a new way to accomplish the same thing that this function does? What was the rational for removing it. If you need a use case for why Mozilla needs it, I'd be happy to oblige. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
We most certainly are using sqlite3_prepare_v2. This use case is a bit more exotic. We are currently working on an async database access API (discussion thread [1]). The idea is to have a user prepare and bind parameters on the calling thread, then clone that statement to send it to the background thread that will process the results. Since a statement object can only be accessed on one thread at a time (at least that's my understanding of it), the original statement is still usable on the calling thread (and could be used again immediately even if the other thread is doing work). Cheers, Shawn [1] http://groups.google.com/group/mozilla.dev.planning/browse_thread/thread/045fed0ecba487cc On Tue, May 13, 2008 at 2:18 PM, D. Richard Hipp [EMAIL PROTECTED] wrote: On May 13, 2008, at 2:05 PM, Shawn Wilsher wrote: I was looking through the documentation and was wondering why sqlite3_transfer_bindings has been marked as obsolete. It's something that we use currently in our code, and I was looking to use it again for something new. Is there a new way to accomplish the same thing that this function does? What was the rational for removing it. If you need a use case for why Mozilla needs it, I'd be happy to oblige. We strive to avoid incompatibilities. So even though sqlite3_transfer_bindings() is marked as obsolete, that just means (in the words of the documentation) that we are not going to tell you want it does. :-) It isn't going away. There are tests in the test suite to make sure it works. sqlite3_transfer_bindings() was intended for use with sqlite3_prepare() when sqlite3_step() returns SQLITE_SCHEMA. After the schema error, one creates a new prepared statement from the original SQL, uses sqlite3_transfer_bindings() to move the bindings from the old prepared statement to the new, finalizes the old prepared statement, then retries with the new prepared statement. But all of that was made obsolete by sqlite3_prepare_v2(). Sqlite3_prepare_v2(), you will recall, handles the SQLITE_SCHEMA errors automatically so the use of sqlite3_transfer_bindings() is no longer required. I am curious to know what alternative use Mozilla has found for sqlite3_transfer_bindings(), though. You are using sqlite3_prepare_v2() in place of sqlite3_prepare() I trust. You should be if you are not since applications that use sqlite3_prepare_v2() are less prone to bugs in error handling logic (by virtue of the fact that they can essentially ignore SQLITE_SCHEMA). D. Richard Hipp [EMAIL PROTECTED] ___ 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] Crash in SQLite [@ syncJournal ]
Hey folks, We've started to see a crash in syncJournal that happens in the same place in a previously mentioned e-mail to this list: http://www.mail-archive.com/sqlite-users@sqlite.org/msg29637.html We don't have any steps to reproduce, but we have a bug tracking the issue: https://bugzilla.mozilla.org/show_bug.cgi?id=432148 More details can be found in the bug. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer
On Sat, May 3, 2008 at 12:30 AM, Roger Binns [EMAIL PROTECTED] wrote: I'd also prefer the documentation to be in the wiki so people who use it can update it as they discover various issues. The doc is currently duplicated in http://www.sqlite.org/34to35.html and http://www.sqlite.org/c3ref/vfs.html Additionally, there are some inconsistencies between those documents (method headers aren't the same), and the vfs.html page doesn't give as detailed (or doesn't even talk about) some of the methods that the 34to35.html page does. Cheers, Shawn Wilsher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction across threads
1) If shared, then the second threads insert is part of the transaction and should succeed. 2) No. 3) If the connection is shared between threads, there can only be 1 txn at a time. The second threads attempt to begin a txn will result in an error that indicates a txn is already active. To be clear, when using a shared cache and more than one sqlite3 connection object, only one transaction will exist at a time, correct? However, if it is not using the shared cache, you can have a transaction opened up for each thread? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_LOCKED behavior
Are you using a shared cache? You can get also get SQLITE_LOCKED when using a shared cache. See section 2.2 of http://www.sqlite.org/sharedcache.html. I've not used a shared cache myself. One day I was wondering if I needed to worry about handling SQLITE_LOCKED errors and I came across that page. Are these the only times you can get SQLITE_LOCKED errors? Ah-ha! We are in fact using the shared cache, which probably explains this. Any reason why SQLITE_LOCKED is returned instead of SQLITE_BUSY? With SQLITE_BUSY you can keep retrying until you decide to give up, or until it works, but you can't do that with SQLITE_LOCKED. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_LOCKED behavior
I am not aware of any reason why you cannot retry an SQLITE_LOCKED error after a delay, however. Have you actually tried doing that? Is it giving you trouble? Attempting to retry after it being issues results in SQLITE_MISUSE being returned. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_LOCKED behavior
Did you call sqlite3_reset() before each retry? Ah, I didn't realize I'd have to do that. When I get SQLITE_BUSY returned, I can just retry it, so I made the wrong assumption that that would work in this case as well. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_LOCKED behavior
Did you call sqlite3_reset() before each retry? Doing this fixed the issue. Thanks! Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_LOCKED behavior
Hey all, When using SQLite 3.5.4.1 (a special branch cut for Mozilla, based mostly off of 3.5.4 with some OS/2 fixes), I'm getting SQLITE_LOCKED returned unexpectedly. The documentation seems to indicate that I should only be getting SQLITE_LOCKED if I'm calling sqlite3_exec recursively writing to the same table. However, it seems to me that I'm having that happen when two different threads are trying to write to the same table. I would expect to get SQLITE_BUSY at this point, but perhaps I'm misusing the API or have the wrong expectations. This is happening by using a different sqlite3 database pointers, one for each thread. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] New crashes with SQLite 3.5.7
Hey all, Mozilla has recently upgraded to sqlite 3.5.7, and we've suddenly gotten a lot of crashes. The mozilla bug report is here: https://bugzilla.mozilla.org/show_bug.cgi?id=424163 We haven't looked into it to much, but I figured I'd point it out to so you were aware of it. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New crashes with SQLite 3.5.7
Any additional information you can send, such as the size of the database file at the point of failure, or the exact line on which the problem occurs, will be appreciated. (I know the bug report gives a line-number, but line numbers shift from one amalgamation to another, and I don't know which amalgamation you are using - I want the text of the line on which the problem occurs.) Each individual crash report listed on this page will point you to the proper place: http://crash-stats.mozilla.com/report/list?range_unit=weeksquery_search=signaturequery_type=containssignature=sqlite3BitvecSetquery=sqliterange_value=1 Example from one crash report (beware, really large html file): http://bonsai.mozilla.org/cvsblame.cgi?file=mozilla/db/sqlite3/src/sqlite3.crev=1.14mark=22783#22783 A reproducible test case would, of course, be ideal. That might be a bit difficult to reproduce. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Crashes
I put a pointer to the mozilla bug report here:
I probably should have mentioned our bug report as well. It's bug 408518 [1].
These stack traces don't make any sense to me either. The definition
of sqlite3_enable_shared_cache() in SQLite cvs is:
That, and as far as I can tell it's ever called by any other sqlite code.
int sqlite3_enable_shared_cache(int enable){
sqlite3SharedCacheEnabled = enable;
return SQLITE_OK;
}
sqlite3SharedCacheEnable is a file scoped int.
hmm, are there some threadsafty issues there with setting and reading
that value from (possibly) multiple threads? I don't think it's
related to this (I don't think anything in core code in mozilla
actually toggles those - but add-ons can do it) however.
Stack overflow possibly? Will keep thinking this.
Someone mentioned in the mozilla bug that the new allocator we
switched to may have landed around the time we started seeing this.
I'll look into it further.
Cheers,
Shawn Wilsher
Mozilla Developer
[1] https://bugzilla.mozilla.org/show_bug.cgi?id=408518
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Crashes
Hey all, Over at Mozilla we've been seeing a large amount of crashes in sqlite3_enable_shared_cache. The stack frames don't make a whole lot of sense to me, so I thought I'd inform you and hope that you might have a better idea as to what is going on. If you have any questions, feel free to ask. If I don't know the answer, I'll get the people who should know involved. We'd really like to try and resolve this issue, so insight on this matter would be greatly appreciated. http://tinyurl.com/2393qs We are presently using the latest version of sqlite. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
I have five different copies of the SQLite code on this computer alone, I think. Every Mac has several of them. One of the servers I deploy to has at least 10 copies of it. Every copy of Firefox 3 contains a copy of SQLite. And Firefox 2 ;) Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
Every copy of Firefox 3 contains a copy of SQLite. And Firefox 2 ;) Really? What is it used for? I'm not sure what uses it internally, but it's exposed via mozIStorageService [1], so add-ons can use it as well! Cheers, Shawn [1] http://developer.mozilla.org/en/docs/mozIStorageService ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Unicode support
The problem with ICU is that it's a rather large library, and mozilla already has it's own unicode system. That's we we opted on doing unicode support ourselves (less code duplication, and a smaller binary). Cheers, Shawn Wilsher On Jan 24, 2008 11:35 PM, Dan [EMAIL PROTECTED] wrote: On Jan 25, 2008, at 7:26 AM, Myk Melez wrote: Hi all, I'm working to enable FTS3 in the next version of Firefox [1] so that extenders can take advantage of it, although Firefox itself isn't using it for the next release. Given Firefox's international audience, it would be useful for FTS3 to support Unicode. We currently do this for upper(), lower(), and LIKE by redefining them with sqlite3_create_function [2]. For FTS3 it seems like we'd have to redefine the tokenizer and MATCH. Can that be done using sqlite3_create_function, and what's the status of the international support mentioned in a previous message on this list [3]? Hi Myk, The 'icu' and 'fts3' SQLite extensions can take advantage of the ICU library to provide internationalization if it is available. The ICU extension provides internationalized versions of upper(), lower(), collation sequences and a REGEXP operator. Details are available here: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt Fts3 has an API for creating new tokenizers. See here: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/ README.tokenizers One of the example tokenizers uses the ICU library for localization. See the same document for details. It is built if the SQLITE_ENABLE_ICU macro is defined when fts3 is compiled. Regards, Dan. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Next Version of SQLite
Hey all, I was wondering when you plan on releasing the next version of SQLite. Mozilla is currently using 3.5.4, but that does not include some OS/2 fixes that were checked in after the release of 3.5.4. Instead of patching our local copy of sqlite, I'd like to use a release version, but at the same time do not want to delay this fix to our OS/2 users very long. The specific checkins we are looking at are 4646, 4647, and 4648. See Bug 411780 for details (https://bugzilla.mozilla.org/show_bug.cgi?id=411780). Cheers, Shawn Wilsher - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Next Version of SQLite
On Jan 12, 2008 9:07 PM, D. Richard Hipp [EMAIL PROTECTED] wrote: In case you haven't been watching the timeline (http://www.sqlite.org/cvstrac/timeline) we are in the middle of some major changes. The virtual machine inside of SQLite is being transformed from a stack-based machine into a register-based machine. The whole virtual machine and the code generator is being rewritten. Slowly. Piece by piece. I haven't done an overall line change count yet, but we are looking at some pretty serious code churn. 3.5.4 to 3.5.5 is likely to be the biggest single change in the history of SQLite. Out of curiosity, why so many changes for a point release? If you like, we can set up a special Mozilla branch off of 3.5.4 that includes the OS/2 fixes. That'd be awesome if we could get a 3.5.4.1 type of thing going. I'm very much against patching sqlite locally in our tree (harder to upgrade). Cheers, Shawn Wilsher - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_release_memory Question
Hmm, the documentation (http://sqlite.org/compile.html) doesn't seem
to say anything about that. Is there a cost to pay by enabling those
functions, or no?
Cheers,
Shawn
On Jan 11, 2008 8:42 AM, [EMAIL PROTECTED] wrote:
Shawn Wilsher [EMAIL PROTECTED] wrote:
Hey all,
Over in Mozilla land, we are looking for ways to free up as much
memory as possible on demand. That got me looking into
sqlite3_release_memory. However, the docs say that it tries to free
up to N bytes, but that it could free more or less. My question is,
how do we get it to free as much as possible, or does it do that
automatically regardless of the value you provide to it?
To release as much memory as possible, just call
sqlite3_release_memory(0x7fff);
Or, if you think you might have more than 2GiB of
memory in use:
while( sqlite3_release_memory(0x7fff)0 ){}
Please note, however, that sqlite3_release_memory() is
a no-op unless you compile with -DSQLITE_ENABLE_MEMORY_MANAGEMENT=1.
--
D. Richard Hipp [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] sqlite3_release_memory Question
Hey all, Over in Mozilla land, we are looking for ways to free up as much memory as possible on demand. That got me looking into sqlite3_release_memory. However, the docs say that it tries to free up to N bytes, but that it could free more or less. My question is, how do we get it to free as much as possible, or does it do that automatically regardless of the value you provide to it? For reference purposes, the Mozilla tracker for this is Bug 411894 (https://bugzilla.mozilla.org/show_bug.cgi?id=411894). Cheers, Shawn Wilsher - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Trac Account
Hey all, I was wondering what it takes to an account on Trac. I'm basically the maintainer of the Mozilla Project's SQLite wrapper, and I'd find things to be a bit clearer if bug reports/comments made by me were in fact labeled as such. In addition, I believe that I can get e-mail notifications of changes to tickets, which is a heck of a lot better than bookmarking a ticket and checking back every few days. Is there some policy setup for this that I just haven't found? Cheers, Shawn Wilsher - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
On Dec 14, 2007 10:38 AM, [EMAIL PROTECTED] wrote:
That would be the Serialized Statement Extension, SSE.
The SSE provides the programmer with two new APIs:
int sqlite3_serialize(sqlite3_stmt*, void**, int*);
int sqlite3_deserialize(sqlite3*, void*, int, sqlite3_stmt**);
The first routine takes an SQL statement that was generated by
sqlite3_prepare() and converts it into a form that can be stored
on disk or compiled into a program. The second routine does the
reverse; it takes the serialization of a statement and converts it
back into a working SQL statement that can be used just like any
other statement created by sqlite3_prepare().
You compile SQLite normally on your development workstation, but
for you embedded target you add -DSQLITE_OMIT_PARSER to leave off
the parser. By omitting other optional features (date/time functions,
views, triggers) you can get the size of the library down to the 70KiB
range or less.
On a workstation, you can sqlite3_prepare() statements, then hand
them to sqlite3_serialize(). The results can be hard coded into
C programs to be manually deserialized later, if you like, though
that is a lot of work. A simpler approach is to use the special
sqlite_statement table:
CREATE TABLE sqlite_statement(
id INTEGER PRIMARY KEY,
sql TEXT,
serial BLOB
);
A new API is available that will automatically extract and deserialize
an SQL statement from the sqlite_statement table given its id number:
int sqlite3_fetch_statement(sqlite3*, int id, sqlite3_stmt**);
The idea here is that the SQL statements needed by an application can
be inserted as plain text into the sqlite_statement table. For
example:
INSERT INTO sqlite_statement(sql) VALUES('SELECT * FROM table1');
After many such statements are inserted, they can all be serialized
as follows:
UPDATE sqlite_statement SET serial = sqlite_serialize(sql,id);
Then the complete database can be moved from the development platform
over to the embedded device and the embedded device can use the
sqlite3_fetch_statement() API to extract the statements it needs to
execute.
To be useful, your precompiled statements will normally contain
parameters (ex: INSERT INTO tx VALUES(?,?,?)) and the embedded
application will using sqlite3_bind_xxx() interfaces to attach
values to these parameter prior to invoking sqlite3_step().
The SSE has not been kept current with the base SQLite. But if there
is interest, we could resurrect it easily enough.
I think that Mozilla may find that useful. I think it came up about a
month ago with some places code (new bookmark back-end).
Seth, I think it was you and Mano that were talking about this. Would
this be useful for us?
Cheers,
Shawn
--
D. Richard Hipp [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
