[sqlite] Effect of using WAL on technical support

I'm interested in hearing anyone's experiences of using WAL journal mode on technical support. We often have to copy databases to attach to customer reports and if the someone were to copy the database file while there is an active -wal file then we would very likely be missing the most

Re: [sqlite] Effect of using WAL on technical support

a "pragma wal_checkpoint". I'm testing on Windows 7 with ASP.NET applications. Thanks, Sam On Sun, Apr 10, 2011 at 9:10 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 11 Apr 2011, at 2:04am, Samuel Neff wrote: > > > I'm interested in hearing anyone's e

Re: [sqlite] Effect of using WAL on technical support

, Simon Slavin <slav...@bigfraud.org> wrote: > > On 11 Apr 2011, at 2:48am, Richard Hipp wrote: > > > On Sun, Apr 10, 2011 at 9:36 PM, Samuel Neff <srneff.li...@gmail.com> > wrote: > > > >> I'm sorry, my statement was misleading. I'm referring to immed

[sqlite] Discussing and contributing to new System.Data.SQLite project

Who is maintaining the new System.Data.SQLite project at http://system.data.sqlite.org? Is there a separate mailing list for that project or should questions on that project be sent to the sqlite-users this mailing list? My company is interested in knowing what the plans are going forward and

[sqlite] Integrate page-level locking into main SQLite like BerkleyDB's SQLite ?

The BerkleyDB backed version of SQLite from Oracle supports page level locking. Apparently this is where the real performance gains come from, not swapping out the back-end. http://www.oracle.com/technetwork/database/berkeleydb/downloads/index.html Are there any plans to add page-level locking

Re: [sqlite] Understanding table-level locking in shared-cache mode

Oracle's distribution of BerkleyDB with SQLite has page-level locking instead of database-level locking. If you need the increased concurrency that is an option. http://www.oracle.com/technetwork/database/berkeleydb/overview/index.html Supposedly it's a drop-in replacement for sqlite. I

[sqlite] System.Data.SQLite and Shared Cache

Can the System.Data.SQLite provider add support for Shared Cache? There's some old code in interop.c that's commented out that enables it, but no indication of why it's commented out. There's some discussion of it from five years ago here, with a patch to add support:

Re: [sqlite] How to select Strict Affinity or No Affinity modes?

But the important point is that no matter how much discussion we have, we will never all agree that untyped is better than typed or that typed is better than typed. That's why an option so individual developers can choose is good. We don't have to agree, with an option we can agree to disagree.

Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH

I would like to have strict affinity mode too. In our schemas we use check constraints to enforce strict affinity. Unless you're working in a dynamic typed environment, I can't imagine why you would want to have inconsistent data within a single database field. Also for consistency with

Re: [sqlite] Multi-valued attributes

It's called a many-to-many relationship and you use a cross-reference table to represent the relationship. Say you have table Searches - SearchID FromDate ToDate etc... Users UserID FirstName LastName Then to define what users are associated with what searches, you

Re: [sqlite] Blob incremental i/o via Python

If the images you're storing are larger than the defined page size for the database (which is most likely the case) then you can get better performance and reduced memory consumption by storing the images in the file system and store only paths to the files in the database. This means reading the

Re: [sqlite] Blob incremental i/o via Python

Thanks for the correction. Sam On Thu, Feb 14, 2008 at 6:19 PM, <[EMAIL PROTECTED]> wrote: > "Samuel Neff" <[EMAIL PROTECTED]> wrote: > > If the images you're storing are larger than the defined page size for > the > > database (which is most

Re: [sqlite] Multiple table SELECT help

Try this.. SELECT p.name, sum(s.stat1), sum(s.stat2) FROM stats s JOIN Players p ON p.ID = s.playerID WHERE s.gameID IN (SELECT gameID FROM games ORDER BY gameID DESC LIMIT 5) GROUP BY p.playerID HTH, Sam On Thu, Feb 21, 2008 at 10:25 AM, RBKanso <[EMAIL PROTECTED]> wrote: > > Assume the

Re: [sqlite] Hierarchical Deletion via a Trigger?

I don't agree with the XML anaology. As I understand it, recursion in SQL is referring to self-referencing tables that create a parent/child relationship. This generally does not apply to XML since XML is hierarchical but usually not recursive (i.e., the children are not the same elements as

Re: [sqlite] Detecting database table changes

Use triggers to populate some table such as Changes or History or LatestChange or something. Then you can just query this one table for updates. HTH, Sam On Fri, Feb 22, 2008 at 4:28 PM, John Karp <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm using SQLite to maintain a database that is used

Re: [sqlite] Detecting database table changes

, 2008 at 12:05 PM, John Karp <[EMAIL PROTECTED]> wrote: > Okay, thanks. That sounds workable. > > Am I right to understand from the documentation that I have to create > three triggers (insert, update, and delete) for each table concerned? > > Thanks again, > John >

Re: [sqlite] Concatenating values from multiple varchar fields

While '||' is the standard, unfortunately MSSQL uses '+' for string concatentation so people that are used to using '+' are most likely coming from MSSQL and thus using '||' does not make for portable SQL (MSSQL does not support '||', at least not in version 2005). In my applications I have a

Re: [sqlite] Optimization Question for SQLite Experts

Here's two suggestions. First the simple suggestion is instead of this.. for (z=0;z

Re: [sqlite] Optimization Question for SQLite Experts

shouldn't leafID be the primary key of your LEAVES table and thus already indexed? What does your create table statement look like? I'd expect CREATE TABLE Leaves (LeafID INTEGER PRIMARY KEY AUTOINCREMENT, ... other columns ... ) As far as the create index failing, no idea there, sorry.. Sam

Re: [sqlite] How To concatenate two fields in one

SQLite uses '||' as the concatenation operator (which is correct, MSSQL is really wrong to accept '+' and not use '||'). HTH, Sam On Fri, Feb 29, 2008 at 11:52 AM, Alessio Forconi <[EMAIL PROTECTED]> wrote: > > What I would like to achieve is the same as writing in SQL Server > > SELECT

Re: [sqlite] [newbie] SQLite and VB.Net?

The System.Data.SQLite wrapper that others have mentioned is wonderful. I would go the ADO.NET route 'cause it'll fit into your .NET application much better. The overhead is minimal compared to the normal cost of running database queries (in any database). However, if you really want to skip

Re: [sqlite] [newbie] SQLite and VB.Net?

2, 2008 at 10:36 PM, Gilles Ganault <[EMAIL PROTECTED]> wrote: > On Sun, 2 Mar 2008 21:07:03 -0500, "Samuel Neff" > <[EMAIL PROTECTED]> wrote: > > I would go the ADO.NET route 'cause it'll fit into your .NET application > much > >better. The over

Re: [sqlite] insert of string with line breaks

How are you verifying that the db only received one line from the description? using sqlite3.exe or some other tool, or AIR? SQLite itself does not have issues with storing line breaks, but it's possible the AIR wrapper is losing them somewhere or more likely it's a display/formatting issue.

[sqlite] Any way to get more information than "constraint failed" when a constraint fails?

Is there any way to get more information from SQLite when a constraint fails, particularly which field caused the constraint to fail? Ideally the error message should list the field name that caused the constraint to fail, the bad value, and even the constraint itself. This is what I get...

Re: [sqlite] Count(1)

Scott, Is it really a full table scan or just an index scan (at least in the case where no data is needed from the table as in the original sample that had no join or where clause). Thanks, Sam On Thu, Apr 3, 2008 at 4:12 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > A little bit more info:

Re: [sqlite] Any way to get more information than "constraint failed" when a constraint fails?

AIL PROTECTED]> wrote: > > > Samuel Neff wrote: > > I first reported this when I started working with SQLite. > > > > http://www.sqlite.org/cvstrac/tktview?tn=2258 > > There was an earlier ticket with more details about why this happens: > > http://www

Re: [sqlite] SQLite in DotNet environment and virtual tables

While System.Data.SQLite does not support virtual tables, it does support custom funcions written in .NET and is open source, so that should get you a long way towards writing virtual tables in .NET (but still will require some C interop programming). http://sqlite.phxsoftware.com/ HTH, Sam

Re: [sqlite] Cannot get amalgamation built from CVS to compile

On Tue, May 6, 2008 at 2:42 PM, Matthew L. Creech <[EMAIL PROTECTED]> wrote: > > > By default things like HAVE_GMTIME_R aren't defined, so you'd have to > add those to your CPPFLAGS or something if you wanted to build a > generic amalgamation with those features included. The datatypes that >

Re: [sqlite] Cannot get amalgamation built from CVS to compile

Matthew, Thanks! After deleting everthing and re-checking out from cvs, using the pre-build makefile worked great. Best regards, Sam On Tue, May 6, 2008 at 4:46 PM, Matthew L. Creech <[EMAIL PROTECTED]> wrote: > > If you want to create a generic amalgamation (without pre-defined > features

Re: [sqlite] sorting records in random order

This query runs slow: SELECT id FROM data ORDER BY random(); but this equivalent query runs very fast: SELECT id FROM (SELECT id, random() r FROM data) ORDER BY r; HTH, Sam On Wed, May 7, 2008 at 2:37 PM, Barbara Weinberg <[EMAIL PROTECTED]> wrote: > Hi > I was wondering whether anyone had

Re: [sqlite] sorting records in random order

On Wed, May 7, 2008 at 5:25 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > I couldn't see how these would be different so I fired up the explain > command. As I expected, these two produce identical code (except for the > integer id assigned to the ephemeral table used for the sort). I don't >

Re: [sqlite] Cannot get amalgamation built from CVS to compile

Matthey, Thanks for making this change. We got latest from CVS today and configure/make worked great. One other issue we're having and are not sure about is we get a compiler error on sqlite3_profile and sqlite3_trace. We need to remove these two lines from the def file included with the

Re: [sqlite] Mailing list

On Tue, May 13, 2008 at 3:13 PM, <[EMAIL PROTECTED]> wrote: > > I think mailing lists worked fine 20 years ago but does it have any > advantage today ? :) > Rado > > That's just an opinion.. personally I prefer mailing lists because I can see all seven lists I subscribe to in one place and check

Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

On Tue, May 13, 2008 at 7:51 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > for version 3.6.0 we are considering a behavior change in which a call > to sqlite3_close() will silently and automatically call > sqlite3_finalize() on all outstanding prepared statements. > > D. Richard Hipp > [EMAIL

[sqlite] Locking causing exponentially degraded peformance with multiple processes

We're running into a lot of very slow queries and db locks when running with multiple processes accessing the same database. As a test we created a small application that has only two threads and a small single table database. RunRead reads all data from the one table (100 records). RunWrite

Re: [sqlite] Locking causing exponentially degraded peformance with multiple processes

ing BEGIN IMMEDIATE you lose any chance of concurrency. > > Samuel Neff wrote: > > We're running into a lot of very slow queries and db locks when running > with > > multiple processes accessing the same database. As a test we created a > > small application that has only

[sqlite] Corrupted sqlite_sequence table

I have a corrupt sqlite_sequence table. It has table names in the "seq" field. here is a trimmed version of data in sqlite_sequence: -- Loading resources from C:\Documents and Settings\sam/.sqliterc SQLite version 3.5.7 Enter ".help" for instructions sqlite> .width 50 50 sqlite> select * from

Re: [sqlite] Corrupted sqlite_sequence table

AIL PROTECTED]> wrote: > > On May 28, 2008, at 7:21 PM, Samuel Neff wrote: > > > It happens every time. I can send you a db and the update scripts, > > but I'll > > need you to keep it confidential (not signed affidavit or anything > > like > > that, just understa

Re: [sqlite] Corrupted sqlite_sequence table

great, thanks! On Mon, Jun 2, 2008 at 11:55 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Samuel Neff wrote: > > Were you able to successfully reproduce the corruption using the scripts > and > > databases I sent? We're having a lot more trouble with this problem and >

[sqlite] "database disk image is malformed" error but on restart of application everything is fine

We see an error "database disk image is malformed" in our logs sometimes and if we restart our application then it can continue fine--the db is not corrupt. We can open the db in sqlite3.exe CLI and running "PRAGMA integrity_check;" returns OK. What can cause this error if the db is (thankfully)

Re: [sqlite] simultaneous transactions

using BEGIN IMMEDIATE would prevent this situation from happening, right? Process 2 would get the lock error when it tries to begin the transaction and thus never obtain a reserved lock which prevented process 1 from promoting to an exclusive lock for commit. Sam

Re: [sqlite] quick transaction question

If I launch two sqlite3.exe processes to the same database and do "begin exclusive" in one and "begin" in the second I do not get a busy/locked error in the second (not until you run some other sql like select or insert). What situation can cause "begin" to get a busy/locked error? (plain begin,

Re: [sqlite] The APress SQLite Book

I loved the book. It had a very good introduction to SQL and even as an experienced SQL developer I learned a few things from that, the coverage of SQLite was a wonderful introduction, and it covered some advanced topics well. SQLite changes so much that a lot of things don't apply any more, so

Re: [sqlite] Table Adapter Update Question

Robert, I looked at CoreLab's provider recently and didn't get the impression that they used your code at all. It's separate DLL's, very different classes, is missing some functionality that you provide (like custom functions written in .NET) but has other functionality (like robust connection

Re: [sqlite] Simple problem ?

you can do it with a subquery, like select o.date, (select sum(credit - debit) from bank i where i.rowid < o.rowid) from bank o order by rowid; but it would be _much_ more efficient to handle it in your host application as you loop through the data. Notice that I used rowid instead of date

Re: [sqlite] Clear Screen Command

I've wished there was a ".clear" command often. And I'm sure it's much easier to implement than output coloring. :-) Sam - We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in the Washington D.C. Contact

Re: [sqlite] code formatting in list messages

I think it's a problem with [EMAIL PROTECTED]'s e-mail client. All the messages from him have this problem and it looks like only messages from him have this problem. I see other messages in the list with C code and no escaping. Looking at the raw message I received via the sqlite mailing list

[sqlite] Inconsistent error: "temporary storage cannot be changed from within a transaction"

We're inconsistently getting this error: PRAGMA temp_store = MEMORY ---> System.Data.SQLite.SQLiteException: SQLite error temporary storage cannot be changed from within a transaction We now realize that we are in fact issuing the PRAGMA inside a transaction, but the weird thing is it works

Re: [sqlite] Inconsistent error: "temporary storage cannot be changed from within a transaction"

On Thu, Jul 17, 2008 at 12:53 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > The error only occurs if the temp_store value really is trying to > change. If the new temp_store value is the same as the old, no error > is generated. Are you sure you had not already issued the PRAGMA once >

[sqlite] Any equivalent to MSSQL's UPDATE..FROM.. clause ?

I'm trying to update records in one table based on joined data in another table. MSSQL has support for a "FROM" clause within an UPDATE statement which makes this type of thing very easy. Is there any equivalent in SQLite? The only way I've found to achive the same results is to use a subselect

Re: [sqlite] Any equivalent to MSSQL's UPDATE..FROM.. clause ?

Thanks, I didn't think REPLACE would work here but you're right, it does do exactly what I need. Best regards, Sam - We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in the Washington D.C. Contact [EMAIL

Re: [sqlite] Force the use of a specified index?

If neither index individually offers a performance boost, it's possible a single multi-column index might be better. Sam On Thu, Sep 17, 2009 at 3:43 PM, Bart Smissaert wrote: > Have tried INDEXED BY and it does indeed work and force the use of the > specified index. >

[sqlite] "natrual" join (not "natural")

We wrote a query and intended to use a "natural" join but had a typo and wrote "natrual" join instead. We were surprised this query was processed without error and performed a cross join. Example: SQLite version 3.7.6.3 Enter ".help" for instructions Enter SQL statements terminated with a ";"

Re: [sqlite] "natrual" join (not "natural")

org> wrote: > On Wed, Oct 31, 2012 at 9:54 AM, Samuel Neff <srneff.li...@gmail.com> > wrote: > > > We wrote a query and intended to use a "natural" join but had a typo and > > wrote "natrual" join instead. We were surprised this query was p

[sqlite] LIMIT expression in subquery cannot reference outer query tables

When using LIMIT in a subquery it seems the expression cannot access the outer query's tables. Other complex expressions are supported in LIMIT, so I was curious if this is a defined limitation or something else? Example: sqlite> create table test (f int); sqlite> insert into test values (1);