Re: [sqlite] sqlite:Deletion in Joins method

Hi, Thanks for your suggestion Dennis. Now I normalized the database as follows: * CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); * CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist Text); * CREATE TABLE BGM (BgmId

[sqlite] SQLite storage plugin for MySQL

Hi all, The following SQLite related project was created on Cofundos.org (a platform for describing open-source project ideas and pooling resources for their implementation): *SQLite storage plugin for MySQL* by soeren Tags: MySQL C Database SQLite http://Cofundos.org/project.php?id=9

Re: [sqlite] Mac OS X name mangling

On Dec 1, 2007, at 5:08 AM, [EMAIL PROTECTED] wrote: The comment probably should read: Needed to enable pthread recursive mutexes *on Linux*. The _XOPEN_SOURCE define is needed to fix compile-time problems. If you didn't get a compile-time error, it is probably working. Great, that's the

[sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

Starting from version 3.4.2 I receive errors with queries like: SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field or even SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field error is: ORDER BY term number 1 does not match any result column Tables are

[sqlite] SQLite does not support multi-row inserts?

Hello, I ran into a problem when using SQLite from PHP. It appears that SQLite3 does not support multi-row inserts in the form: INSERT INTO (col1, col2) VALUES (1, 2), (3, 4) Is that correct? Will if be implemented in the future? -- Sander Marechal Product Developer [EMAIL PROTECTED]

[sqlite] Single row insert speeds

I've got an application that logs real-time data. Some of the data is periodic (every few secs), other data comes more frequently. Basically, I'm not dealing with bulk inserts, so, I can't queue things up and insert all at once. I'm noticing that my insert times are pretty slow (~5-50 ms on a

RE: [sqlite] Single row insert speeds

What platform are you running on? Most of these sorts of issues that have come up before relate to the fact that SQLite is committing the data to the disc with each insert and has to ensure that the buffer has been written before it can process the next insert. IIRC there is a PRAGMA to switch

Re: [sqlite] Single row insert speeds

you would need to give more info about your db, the data, etc. On my Macbook Pro, I get 1000+ inserts a second for a random 100 byte string insert (that is, less than one per ms). That includes the time to generate the string, and is all in Perl, while I am listening to iTunes, and no funny pragma

Re: [sqlite] Single row insert speeds

P Kishor [EMAIL PROTECTED] wrote: I get 1000+ inserts a second for a random 100 byte string insert ( I get 5+ inserts/sec on my Linux box. Insert speed is not the issue. It is COMMIT speed. At each commit, SQLite waits until all data is on oxide before continuing. That will typically

Re: [sqlite] Single row insert speeds

hi, i am using a commit-thread: my application is starting a transaction with begin and is inserting a lot of data at random times, a thread is doing commit and begin every second (while locking the main app of course :-). this runs very fast and in case of crashes i loose only 1 second of

Re: [sqlite] SQLite does not support multi-row inserts?

--- Sander Marechal [EMAIL PROTECTED] wrote: I ran into a problem when using SQLite from PHP. It appears that SQLite3 does not support multi-row inserts in the form: INSERT INTO (col1, col2) VALUES (1, 2), (3, 4) Is that correct? That's correct. Will if be implemented in the future?

Re: [sqlite] Single row insert speeds

I'm running SQLite 3.1.13 under Fedora 3. Our real app is written in C++. The sample I wrote to debug the insert times is written in C (gcc 3.4.2). Here is the table I've defined: CREATE TABLE sampleTable ( logHost varchar(64) DEFAULT NULL, compId smallint(5) DEFAULT NULL, pid int(10)

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

--- Marco Bambini [EMAIL PROTECTED] wrote: Starting from version 3.4.2 I receive errors with queries like: SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field or even SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field error is: ORDER BY term number

Re: [sqlite] sqlite:Deletion in Joins method

Sreedhar.a wrote: I have only one ALBUM named 'Confession' with id 1 and only one ARTIST named 'Madonna' with id 2 and one BGM named 'rock' with id 5. MUSIC table will have all these details. Now If I delete the Album 'Confession' From table ALBUM. Then it is deleting that record

Re: [sqlite] Single row insert speeds

On 12/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: P Kishor [EMAIL PROTECTED] wrote: I get 1000+ inserts a second for a random 100 byte string insert ( I get 5+ inserts/sec on my Linux box. turned off AutoCommit, turned off rand string generation, and lookee what I get... 200,000

Re: [sqlite] Single row insert speeds

I used the PRAGMA statement and turned off the synchronous option. It made a huge difference. Single inserts were ranging from 5 - 50 ms, now, they're at ~.04 ms. However, I guess there is a tradeoff between the safety of the synchronous operation (in case power is lost) versus the insert

Re: [sqlite] Single row insert speeds

On 12/3/07, Mark Riehl [EMAIL PROTECTED] wrote: I used the PRAGMA statement and turned off the synchronous option. It made a huge difference. Single inserts were ranging from 5 - 50 ms, now, they're at ~.04 ms. However, I guess there is a tradeoff between the safety of the synchronous

Re: [sqlite] Single row insert speeds

A faster disk will give you better performance. A 15,000 rpm disk will give almost three times the performance of a 5,400 rpm one and retain the ACID mode. You could also queue your input and launch periodic Sqlite transactions to empty the queue. Mike Marshall wrote: What platform are

Re: [sqlite] Single row insert speeds

I have a few suggestions for you to try: 1. Use prepared statements. Do not free the statement, reset it and re-use that way you don't incure the parsing overhead for each statement. 2. Use bind variables in conjunction. Redesign to perform more than single row inserts into a

RE: [sqlite] Performance tuning using PRAGMA, other methods

BTW, several PRAGMAS actually increase performance in my embedded app case - maybe 15-30% depending upon transaction activity and the way I structure transaction commits. Specific PRAGMAS that helped include: This is exactly what irritates me about conversations like this - the pragmas

[sqlite] sqlite and CE 4.1

Does sqlite supports ce 4.1? I've tried several wrappers for .net and also system.data.sqlite which supports cf 2 and native code but It doesn't work (the test testce exe crashes after two lines of output). Does sqlite natively supports CE 4.1? Thanks -- AlphaC

[sqlite] maximum number of columns

good day! i am making an api for an sqlite database. 1 api needs the number of columns in the database. i want to restrict the input by putting a specific range (colNum 0 and colNum MAX)..is there any constant in the sqlite3.h that has the value of the maximum number of columns in a table?..

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

I have reported it as a bug - ticket is http://www.sqlite.org/cvstrac/tktview?tn=2822 It appears as though the /src/select.c (Line1499) changed from: if( iCol0 mustComplete ){ to: }else if( mustComplete ){ in version 1.336 of this file -

[sqlite] sqlite tcl binding

I experimeneted withthreads and I get a strange behabiour. it looks like one thread takes over sqlite and does not leave it until it is finished even though I tell it to wait. this is the script I use: package require sqlite3 package require Thread sqlite3 conn test catch {conn eval drop

RE: [sqlite] sqlite:Deletion in Joins method

Dennis wrote: I think you need to add a delete trigger on the music that will delete records in the album, artist, and bgm tables if there are no other records with the same albumId, artitId, or bgmId in the music table (i.e. if this is the last record in the music table that references a

Re: [sqlite] SQLite does not support multi-row inserts?

Joe Wilson wrote: --- Sander Marechal [EMAIL PROTECTED] wrote: I ran into a problem when using SQLite from PHP. It appears that SQLite3 does not support multi-row inserts in the form: INSERT INTO (col1, col2) VALUES (1, 2), (3, 4) Will if be implemented in the future? I doubt it. Too