Re: [sqlite] WAL, threads, shared cache, etc

[Shawn Wilsher]

On Wed, Apr 13, 2011 at 9:05 AM, Mark Hamburg  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

[Shawn Wilsher]

On Mon, Jan 31, 2011 at 5:44 AM, Dan Kennedy  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

[Shawn Wilsher]

On Sun, Jan 30, 2011 at 8:26 PM, Dan Kennedy  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] upstreaming Chromium patches for file handle passing support

[Shawn Wilsher]

On Wed, Sep 1, 2010 at 5:57 PM, Paweł Hajdan, Jr.
 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] Feature request: copying vacuum

[Shawn Wilsher]

On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich  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] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

[Shawn Wilsher]

On Thu, Aug 19, 2010 at 2:56 PM, Simon Slavin  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

[Shawn Wilsher]

On Mon, Aug 16, 2010 at 5:13 PM, Paweł Hajdan, Jr.
 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

[Shawn Wilsher]

On Thu, Dec 31, 2009 at 9:06 AM, Bert Nelsen wrote:

> 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] Mozilla's method

[Shawn Wilsher]

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.com>wrote:

> 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.c<http://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] undefined reference to sqlite3_mutex_held

[Shawn Wilsher]

On Thu, Dec 10, 2009 at 10:18 AM, Daniel Mierswa wrote:

> 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

[Shawn Wilsher]

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

[Shawn Wilsher]

On Thu, Dec 3, 2009 at 10:26 AM, Tim Romano  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

[Shawn Wilsher]

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

Re: [sqlite] Thread-safety and sqlite3_stmt objects

[Shawn Wilsher]

On Fri, Mar 6, 2009 at 9:26 PM, Dan  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] Thread-safety and sqlite3_stmt objects

[Shawn Wilsher]

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

[sqlite] FTS 3 Crash in 3.6.5

[Shawn Wilsher]

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 && inVTrans && 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

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

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?

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

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

Re: [sqlite] SQLite and updating VIEWs

[Shawn Wilsher]

On Tue, Jul 1, 2008 at 4:05 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Will it?  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

Re: [sqlite] SQLite and updating VIEWs

[Shawn Wilsher]

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 
  AND ;

  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

[Shawn Wilsher]

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  and
>   ;
>
>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

[sqlite] SQLite and updating VIEWs

[Shawn Wilsher]

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] PRAGMA temp_store confusion

[Shawn Wilsher]

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

[sqlite] Setting where AUTOINCREMENT starts?

[Shawn Wilsher]

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

[sqlite] PRAGMA temp_store confusion

[Shawn Wilsher]

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

Re: [sqlite] Accessing SQLite from Javascript in Firefox

[Shawn Wilsher]

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] SQLite bug on AFP?

[Shawn Wilsher]

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] Version number in sqlite3.pc file

[Shawn Wilsher]

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] Firefox 3 and the SQLite "bug"

[Shawn Wilsher]

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

Re: [sqlite] Firefox 3 and the SQLite "bug"

[Shawn Wilsher]

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] SQLite and Threadsafety (again)

[Shawn Wilsher]

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

[sqlite] SQLite and Threadsafety (again)

[Shawn Wilsher]

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] sqlite3_transfer_bindings obsolete?

[Shawn Wilsher]

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?

[Shawn Wilsher]

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?

[Shawn Wilsher]

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

Re: [sqlite] sqlite3_transfer_bindings obsolete?

[Shawn Wilsher]

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] sqlite3_transfer_bindings obsolete?

[Shawn Wilsher]

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] Crash in SQLite [@ syncJournal ]

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

> 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

[Shawn Wilsher]

>  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

Re: [sqlite] SQLITE_LOCKED behavior

[Shawn Wilsher]

>  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

[Shawn Wilsher]

>  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

[Shawn Wilsher]

>  Are you using a shared cache?  You can get also get SQLITE_LOCKED when
>  using a shared cache.  See section 2.2 of
>  .
>
>  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

[sqlite] SQLITE_LOCKED behavior

[Shawn Wilsher]

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

Re: [sqlite] New crashes with SQLite 3.5.7

[Shawn Wilsher]

>  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=weeks_search=signature_type=contains=sqlite3BitvecSet=sqlite_value=1

Example from one crash report (beware, really large html file):
http://bonsai.mozilla.org/cvsblame.cgi?file=mozilla/db/sqlite3/src/sqlite3.c=1.14=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

[sqlite] New crashes with SQLite 3.5.7

[Shawn Wilsher]

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] SQLite Crashes

[Shawn Wilsher]

>  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

[Shawn Wilsher]

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?

[Shawn Wilsher]

> > > 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] Most widely deployed?

[Shawn Wilsher]

>  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] FTS3 Unicode support

[Shawn Wilsher]

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]
-

Re: [sqlite] Next Version of SQLite

[Shawn Wilsher]

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]
-

[sqlite] Next Version of SQLite

[Shawn Wilsher]

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] sqlite3_release_memory Question

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

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

[Shawn Wilsher]

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]
-