[sqlite] performance issue

2007-07-17 Thread suresh . bhat
Hi I am using SQLite on MVL OS for ARM processor based embedded platform. I am using SQLite version 3.3.13. We use SQLite APIs for DB operation. I am facing following issue. While testing I observed INSERT and UPDATE command is taking more time than SELECT queries. For example one select query

[sqlite] "Library routine called out of sequence" and user-defined aggregates

2007-07-17 Thread ggeeoo
I'm getting "Library routine called out of sequence" when I try to execute an insertion inside an aggregate that I have created myself. Here's a sample c program: #include #include void MyAggregateFuncStep(sqlite3_context* context,int argc,sqlite3_value** argv) { // Do nothing } void

Re: [sqlite] Re: UPDATE during SELECT

2007-07-17 Thread Larry Lewis
To summarize, if compiling with the THREADSAFE macro set to 1 and sharing a memory database connection between multiple threads, SQLite will handle all read/write synchronization without providing any external locking. Is that basically what you're saying, John? Are there still cases where an

Re: [sqlite] Interrupt SQLite

2007-07-17 Thread drh
"RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is it somehow possible to interrupt an ongoing INSERT operation? I made a > mistake in an index and now got into a very long process that I would like > to stop. I am running this from VBA via the dll from Olaf Schmidt, > dhSQLite.dll. I don't want to

[sqlite] Interrupt SQLite

2007-07-17 Thread RB Smissaert
Is it somehow possible to interrupt an ongoing INSERT operation? I made a mistake in an index and now got into a very long process that I would like to stop. I am running this from VBA via the dll from Olaf Schmidt, dhSQLite.dll. I don't want to kill Excel as I would lose some work. Thanks for any

Re: [sqlite] Replicating table data from sqlite to ms sql server

2007-07-17 Thread Asif Lodhi
Hi maitong, On 7/16/07, maitong uy <[EMAIL PROTECTED]> wrote: The scenario would be the sqlite database is managed using CGI C, resides in Linux environment, and accessed through the web. Then the sql server would be replicating whatever changes would occur in the sqlite database (both sqlite

Re: [sqlite] Re: UPDATE during SELECT

2007-07-17 Thread John Stanton
We implement pthread read/write locks around Sqlite in a multi-threaded environment and disable the fcntl file locking and ignore busy logic. It has the downside of losing some concurrency compared to the Sqlite pending and reserved lock strategy, but we have not suffered a performance hit

[sqlite] Re: Re: UPDATE during SELECT

2007-07-17 Thread Igor Tandetnik
Larry Lewis wrote: For a multi-threaded application using an in-memory database (":memory:"), would you recommend: a) an external mutex to synchronize exclusive access to the database -- probably the safest b) an external read-write lock to allow concurrent reads but only one write (parallel

Re: [sqlite] autoincrement and fts2?

2007-07-17 Thread Scott Hess
[Forwarding gist of an offline conversation with Joe.] Looks about like what my patch looks like. Needs to additionally handle %_segments.rowid (same problem, but you need to insert more than 16 docs to see it). I'm also tossing in some test cases. My patch should be ready this afternoon.

[sqlite] malloc failure in sqlite3_prepare

2007-07-17 Thread Rich Rattanni
All: I am writing a program that reads large blob data (~15 MB) from a sqlite database, then writes the data out to the sound card. After this time, some results are calculated and inserted into the same database. In my code, I have 1 database handle from which I do a prepare to extract the

Re: [sqlite] Re: UPDATE during SELECT

2007-07-17 Thread Larry Lewis
Igor, Thanks for your help. I've tested the first case successfully. For a multi-threaded application using an in-memory database (":memory:"), would you recommend: a) an external mutex to synchronize exclusive access to the database -- probably the safest b) an external read-write lock

[sqlite] Re: Enum user defined functions from code

2007-07-17 Thread Igor Tandetnik
Andre du Plessis <[EMAIL PROTECTED]> wrote: Is there any way to get the list of registered user defined functions from code or SQL, and their parameters? None that I know of. Note that you don't specify the number of parameters when registering a custom function, so SQLite doesn't know it

Re: [sqlite] autoincrement and fts2?

2007-07-17 Thread Scott Hess
You can, and I'm working on a patch to do this to see how it might look. There's the question of how to handle existing tables. -scott On 7/17/07, Chris Wedgwood <[EMAIL PROTECTED]> wrote: On Tue, Jul 17, 2007 at 09:37:43AM -0700, Scott Hess wrote: > Summary: In sqlite 3.4, running vacuum

Re: [sqlite] Enum user defined functions from code

2007-07-17 Thread drh
"Andre du Plessis" <[EMAIL PROTECTED]> wrote: > > Is there any way to get the list of registered user defined functions > from code or SQL, and their parameters? > No. -- D. Richard Hipp <[EMAIL PROTECTED]> - To

Re: [sqlite] autoincrement and fts2?

2007-07-17 Thread Joe Wilson
Scott, I've attached a possible patch to the ticket. It seems to work, but I may have missed some something. Tell me what you think. --- Scott Hess <[EMAIL PROTECTED]> wrote: > I've updated the bug with an example of how this breaks fts tables > (fts1 or fts2). I'm thinking on the problem. >

RE: [sqlite] Sharing an in-memory database between applications

2007-07-17 Thread Lodewijk Duymaer van Twist
Hi Rob, This maybe a dumb idea but have you tried a software RAM disk? Or maybe even a physical RAM disk. http://www.tomshardware.com/2005/09/07/can_gigabyte/index.html Kind Regards, Lodewijk -Original Message- From: Rob Richardson [mailto:[EMAIL PROTECTED] Sent: Friday, July 13,

[sqlite] Enum user defined functions from code

2007-07-17 Thread Andre du Plessis
Hi all Is there any way to get the list of registered user defined functions from code or SQL, and their parameters? I need to provide our users with some GUI's to generate SQL and I would like to make the user defined functions available, I know as it is user defined functions I should

Re: [sqlite] autoincrement and fts2?

2007-07-17 Thread Scott Hess
I've updated the bug with an example of how this breaks fts tables (fts1 or fts2). I'm thinking on the problem. http://www.sqlite.org/cvstrac/tktview?tn=2510 Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can break the table if you've done any deletions. I'll try to add more

Re: [sqlite] autoincrement and fts2?

2007-07-17 Thread Scott Hess
WTH! Wow, this is a very unexpected change. I must have not been paying attention at some point. -scott On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote: >>The standard way to have non-TEXT information associated with rows in >>an fts table would be a separate table which joins with the

[sqlite] Re: UPDATE during SELECT

2007-07-17 Thread Igor Tandetnik
Larry Lewis wrote: If I am stepping through the results of a SELECT and want to UPDATE values in the currently selected row prior to completion of the SELECT query, will this work? Yes, in the recent enough SQLite version. What if there is already a pending writer lock on the database from

Re: [sqlite] autoincrement and fts2?

2007-07-17 Thread Ralf Junker
>>The standard way to have non-TEXT information associated with rows in >>an fts table would be a separate table which joins with the fts table >>on rowid. > >I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, I >believe that it will be affected by VACUUM change of

Re: [sqlite] Re: Finding record position

2007-07-17 Thread Colin Manning
select count(*) from pb where name < 'John Smith'; Thx... duh guess I should have thought of that. -- No virus found in this outgoing message. Checked by AVG. Version: 7.5.476 / Virus Database: 269.10.6/902 - Release Date: 15/07/2007 14:21

RE: [sqlite] Does Transaction object roll back automatically on exceptions?

2007-07-17 Thread Samuel R. Neff
Note that by default the ADO.NET wrapper executes transactions in immediate mode which is not desirable for read-only data. To start a deferred transaction, you need to use the SQLite.NET-specific overload BeginTransaction(true) which is not available if using the DbProvider object model. Best

Re: [sqlite] Finding record position

2007-07-17 Thread drh
Colin Manning <[EMAIL PROTECTED]> wrote: > Hi > > Assume I have a simple table for a phone directory - names, addresses, and > phone numbers etc. > > Then assume this is a vast table with many millions of records, and that > there are indices on name, phone number, etc. > > Then assume I'm

Re: [sqlite] Finding record position

2007-07-17 Thread Bharath Booshan L
On 7/17/07 5:13 PM, "Colin Manning" <[EMAIL PROTECTED]> wrote: > > Not a problem. I can use "SELECT * FROM pb ORDER BY name LIMIT x,y" etc as > my user pages up and down the list, or drags a scrollbar. > > Next, assume the user wants to jump to a specific record in the list, or to > (say)

Re: [sqlite] Finding record position

2007-07-17 Thread John Stanton
First, think about using FTS2 for your text lookup instead of using LIKE and having to perform row scans. Think about changing your logic to just read from the database instead of blowing out your memory by holding the database on disk, then in disk cache and again in application memory. If

[sqlite] Re: Finding record position

2007-07-17 Thread Igor Tandetnik
Colin Manning <[EMAIL PROTECTED]> wrote: Then assume I'm writing an app that displays the entire directory in a window, ordered by name/number etc using a scrollbar to move up and down. So I might have: CREATE TABLE pb (id INT, name VARCHAR(40),...); CREATE INDEX pb_idx_name ON pb(name); .. Not

Re: [sqlite] Finding record position

2007-07-17 Thread Veikko Mäkinen
Colin Manning wrote: Hi Assume I have a simple table for a phone directory - names, addresses, and phone numbers etc. Next, assume the user wants to jump to a specific record in the list, or to (say) the first entry for a specific name. How do you do this with sqlite, without forcing the

[sqlite] Finding record position

2007-07-17 Thread Colin Manning
Hi Assume I have a simple table for a phone directory - names, addresses, and phone numbers etc. Then assume this is a vast table with many millions of records, and that there are indices on name, phone number, etc. Then assume I'm writing an app that displays the entire directory in a

Re: [sqlite] autoincrement and fts2?

2007-07-17 Thread Ralf Junker
>The rowid is the standard SQLite rowid, so it does provide an INTEGER >PRIMARY KEY AUTOINCREMENT column. > >The standard way to have non-TEXT information associated with rows in >an fts table would be a separate table which joins with the fts table >on rowid. I have not tested this, but if the

Re: [sqlite] autoincrement and fts2?

2007-07-17 Thread Scott Hess
On 7/16/07, Adam Megacz <[EMAIL PROTECTED]> wrote: Is there any way to use a INTEGER PRIMARY KEY AUTOINCREMENT on a table that has FTS2? Specifying it in the obvious manner looks like it works, but the column just ends up with nulls in it. In fts tables all columns other than rowid are of

RE: [sqlite] Does Transaction object roll back automatically on exceptions?

2007-07-17 Thread Dan Kennedy
On Mon, 2007-07-16 at 16:47 -0400, Ahmed Sulaiman wrote: > Hi all, > > Is it necessary to run a "SELECT" command in between a transaction? I > have few places in my code where I have a command that reads some data > from a table, and I wonder if I should begin/commit a transaction? Is > there

[sqlite] autoincrement and fts2?

2007-07-17 Thread Adam Megacz
Is there any way to use a INTEGER PRIMARY KEY AUTOINCREMENT on a table that has FTS2? Specifying it in the obvious manner looks like it works, but the column just ends up with nulls in it. - a -- PGP/GPG: 5C9F F366 C9CF 2145 E770 B1B8 EFB1 462D A146 C380