Re: [sqlite] Index and ORDER BY
Really, there is problem with multi-column indexes. You must use only primary key index for ">=" where clause and "ASC" sorting and "<=" where clause and DESC sorting. 1. I try with primary key: #!/usr/bin/tclsh package require sqlite3 sqlite3 db index_order.db db eval {DROP TABLE IF EXISTS events} db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} db transaction { for {set i 0} {$i<1} {incr i} { set type [expr {$i % 50}] db eval {insert into events values ($i,$type)} } } db close So, "type" is equal ("eid" mod 50). sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 32619722|22 CPU Time: user 0.00 sys 0.00 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY Result: this index is good. 2. And I try with two-columns common order index: #!/usr/bin/tclsh package require sqlite3 sqlite3 db index_order.db db eval {DROP TABLE IF EXISTS events} db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} db transaction { for {set i 0} {$i<1} {incr i} { set type [expr {$i % 50}] db eval {insert into events values ($i,$type)} } } db eval {CREATE INDEX ev_idx ON events(type,eid)} db close sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 32619722|22 CPU Time: user 1.400088 sys 1.696106 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events WITH INDEX ev_idx ORDER BY Result: this index is bad. 3. And I try with two-columns desc order index: #!/usr/bin/tclsh package require sqlite3 sqlite3 db index_order.db db eval {DROP TABLE IF EXISTS events} db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} db transaction { for {set i 0} {$i<1} {incr i} { set type [expr {$i % 50}] db eval {insert into events values ($i,$type)} } } db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)} db close sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 32619722|22 CPU Time: user 0.600037 sys 0.608038 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY And with modified query: sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 9972|22 CPU Time: user 0.00 sys 0.00 sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid ASC LIMIT 1; 22|22 CPU Time: user 0.00 sys 0.004000 sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY eid ASC LIMIT 1; 32619772|22 CPU Time: user 0.284018 sys 0.820051 Result: this index is bad. P.S. Try with primary key index only and write your results. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select with union fails on an attached database for 500 records
Anyone has any clues on this? Best Regards, N.Rajesh Courage is the knowledge of how to fear what ought to be feared and how not to fear what ought not to be feared.> From: [EMAIL PROTECTED]> To: sqlite-users@sqlite.org> Date: Fri, 27 Jun 2008 11:33:53 +> Subject: [sqlite] select with union fails on an attached database for 500 records> > > Hi All,> > We are facing a problem with the select command with union on an attached database and getting an error "SQL logic error or missing database"> > 1.We have an active database connection (for eg a.db which contains a table with following columns)CREATE TABLE Contact_Primary_Info([uid] BIGINT PRIMARY KEY NOT NULL,[phonename] VARCHAR(41) ,[phonenumber] VARCHAR(42));> 2.We have created an in memory database with the attach database command (attach ':memory:' as 'SIM' ) with a similar schema.CREATE TABLE SIM.Contact_SIM ([itemId] BIGINT PRIMARY KEY NOT NULL,[name] VARCHAR(41) ,[number] VARCHAR(42));> where SIM is the attached database.> > 3.Now the usecase we want to get all the records in the Contact_Primary_Info and Contact_SIM table..The following is the query i wrote:> > select phonename,uid from contact_primary_info union select name,itemId from Contact_SIM order by 1 ASC;> > > We created 250 records in the SIM.Contact_SIM table and tried the above query for 10 to 100 records in the table Contact_Primary_Info.> It worked perfectly.When we create 500 records in the Contact_Primary_Info table,we are getting an error on the sqlite3_step command which states > "SQL logic error or missing database"> > > Can anyone please help us on whats going wrong with this?The system we are trying is an embedded device which runs on a ARM11 processor.> > > Best Regards,> N.Rajesh> Courage is the knowledge of how to fear what ought to be feared and how not to fear what ought not to be feared.> _> 2000 Placements last year. Are You next ? Find out> http://ss1.richmedia.in/recurl.asp?pid=499> ___> sqlite-users mailing list> sqlite-users@sqlite.org> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Timely update on all current affairs, sports, events and all thats in News here on MSN videos. http://video.msn.com/?mkt=en-in ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
"flakpit" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > is it legal sql syntax to allow more than one constraint field in > table creation? I need at least these four below to guarantee that > duplicate items do make it into the database but not on the same day. > > item TEXT CONSTRAINT item UNIQUE > units TEXT CONSTRAINT units UNIQUE > shop TEXT CONSTRAINT shop UNIQUE > date TEXT CONSTRAINT date UNIQUE It is possible to have multiple constraints, but declaring each field unique individually won't do what you want. Consider: create table t(a unique, b unique); insert into t values('a', 'b'); -- ok insert into t values('a', 'c'); -- fails: column a is not unique insert into t values('z', 'b'); -- fails: column b is not unique create table t(a, b, unique(a, b)); insert into t values('a', 'b'); -- ok insert into t values('a', 'c'); -- ok insert into t values('z', 'b'); -- ok insert into t values('z', 'b'); -- fails: this pair already exists Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite assert failure in sqlite3FaultEndBenign()
thanks very much! On Mon, Jun 30, 2008 at 4:52 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > > > > > > It is not a bug because sqlite3FaultEndBenign() is used during testing > > only. And the tests in which this function are used only run a single > > thread. > > > > > Recompile with -DSQLITE_OMIT_BUILTIN_TEST=1 to completely disable the > EndBenign() function and its friends. Or omit the -DSQLITE_DEBUG=1 > that you are currently using and the assertion will not fail. Either > way, it should start working for you. > > > > 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] [newbie] linking two tables with index...
Hi. I'm real new to SQLite via the command line, but I have used CoreData quite a bit on OSX. My problem is that I have two tables that I want to link via a unique ID, but I don' t know how to automatically link the ID parameter properly. My tables look like this: create table snapshot( id INTEGER PRIMARY KEY, bumf TEXT ); create table sample( snapshotid INTEGER, type TEXT, used INTEGER ); Now when I insert an entry into the snapshot with: insert into snapshot (bumf) values ('one'); I want to insert a number of entries into the 'sample' table and make sure their 'snapshotid' value is set to the one just created in the previous 'insert into snapshot...' command. I know that this stuff is possible because CoreData does this sort of thing but hides it behind a nice API. How do I create such a relationship with sqlite via the command line ? One more thing... the online references I've found for SQL and SQLite seem very basic and don't talk much about linking tables together and things like many-to-many relationships... Can anyone recommend some more thorough documentation, either electronic or in print ? Thanks for your patience. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] linking two tables with index...
"martin linklater" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi. I'm real new to SQLite via the command line, but I have used > CoreData quite a bit on OSX. My problem is that I have two tables > that I want to link via a unique ID, but I don' t know how to > automatically link the ID parameter properly. My tables look like > this: > > create table snapshot( id INTEGER PRIMARY KEY, bumf TEXT ); > create table sample( snapshotid INTEGER, type TEXT, used INTEGER ); > > Now when I insert an entry into the snapshot with: > > insert into snapshot (bumf) values ('one'); > > I want to insert a number of entries into the 'sample' table and make > sure their 'snapshotid' value is set to the one just created in the > previous 'insert into snapshot...' command. > > I know that this stuff is possible because CoreData does this sort of > thing but hides it behind a nice API. How do I create such a > relationship with sqlite via the command line ? You can't make this happen automatically with SQLite. After you insert a record into snapshot table, retrieve the generated ID with select last_insert_rowid(); (in your own program, use sqlite3_last_insert_rowid API) then specify this ID when inserting into sample. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie questions regarding in-memory database.
Hi, Newbie question regarding in-memory databases. I read in the wiki pages that it is not safe to use same db connection across multiple threads. I am trying to use an in-memory database. My understanding is that opening a connection with ":memory:" creates the database and closing it erases the same. If I were to use one connection per thread will i end up creating distinct memory db's for each thread? is it possible to use a single in-memory db that can be accessed by different threads in a multithreaded environment? Any help doc on this is appreciated. Regards, Karthik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie questions regarding in-memory database.
"Karthik" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Newbie question regarding in-memory databases. I read in the wiki > pages that it is not safe to use same db connection across multiple > threads. It's actually safe with recent SQLite versions. A lot of issues have been eliminated. SQLite know uses a mutex to serialize concurrent API calls from multiple threads. You have to know what you are doing of course. E.g. if one thread starts a transaction, then another thread issues some statements, they go into that transaction. > I am trying to use an in-memory database. My understanding > is that opening a connection with ":memory:" creates the database and > closing it erases the same. If I were to use one connection per > thread will i end up creating distinct memory db's for each thread? Yes. > is it possible to use a single in-memory db that can be accessed by > different threads in a multithreaded environment? The only way is to pass the same db connection between threads. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Three LEFT JOIN issues
1) In the following LEFT JOIN, is it possible to alter the query so that there is no w. prefix at the beginning of each returned column name: SELECT w.* FROM Words AS w LEFT JOIN Words as w2 ON w.Id=w2.Id AND w.Lang=w2.Lang AND w.Revhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Three LEFT JOIN issues
"Csaba" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 1) In the following LEFT JOIN, is it possible to alter > the query so that there is no w. prefix at the > beginning of each returned column name: > > SELECT w.* FROM Words AS w LEFT JOIN Words as w2 >ON w.Id=w2.Id AND w.Lang=w2.Lang AND w.RevWHERE w2.Id IS NULL select * from Words where not exists ( select * from Words w2 where Words.Id = w2.Id and Words.Lang=w2.Lang and Words.Rev 3) Which of the following two queries is more efficient (Ie. is > it better to have the w.Id=527 before or after the WHERE keyword)? I doubt it makes any difference. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] linking two tables with index...
Igor Tandetnik wrote: You can't make this happen automatically with SQLite. Hmm? Of course you can. Just use a trigger: http://sqlite.org/lang_createtrigger.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie questions regarding in-memory database.
Igor Tandetnik wrote: > "Karthik" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] > >> Newbie question regarding in-memory databases. I read in the wiki >> pages that it is not safe to use same db connection across multiple >> threads. >> > > It's actually safe with recent SQLite versions. A lot of issues have > been eliminated. SQLite know uses a mutex to serialize concurrent API > calls from multiple threads. > > You have to know what you are doing of course. E.g. if one thread starts > a transaction, then another thread issues some statements, they go into > that transaction. > > >> I am trying to use an in-memory database. My understanding >> is that opening a connection with ":memory:" creates the database and >> closing it erases the same. If I were to use one connection per >> thread will i end up creating distinct memory db's for each thread? >> > > Yes. > > >> is it possible to use a single in-memory db that can be accessed by >> different threads in a multithreaded environment? >> > > The only way is to pass the same db connection between threads. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Thank you! I did try passing around the connection across threads(before reading the wiki at http://www.sqlite.org/cvstrac/wiki?p=MultiThreading), and things seemed to work without any trouble. Thanks again for clarifying. Another question about in-memory databases. I have a producer-consumer sort of application, different threads keep on updating data in a table and after a scheduled interval, i extract aggregated data from the table and clean up the table, and in the worst cases the producers may flood the db before the cleanup happens, is it possible to set a limit on the database size? how does sqlite behave when database size reaches the threshold(the default/configured one)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
your main issue seems to be that you really need to normalize your db. using your example of pepper 220 gr, when you examine that string it consists of 3 parts, pepper = product description or product name, 220 = weight or volume or measurement of product, followed by the measurement type. All 3 of those properties really dont make a unique item but instead are properties of the item. I think that multiple tables would give you a better db design and fix some of your isses with the constraints. looking at your current create table statement you have columns that belong in a store table, an item table, a purchase history table as well as store_carried table. just imho. Woody --- On Tue, 7/1/08, flakpit <[EMAIL PROTECTED]> wrote: From: flakpit <[EMAIL PROTECTED]> Subject: [sqlite] Multiple constraints per table? To: sqlite-users@sqlite.org Date: Tuesday, July 1, 2008, 1:10 AM This is the way I normally create my shopping database, but this leads to thousands of duplicates. CREATE TABLE shopping(item TEXT,units TEXT,quantity TEXT,category TEXT,shop TEXT,aisle TEXT,price TEXT,total TEXT,date TEXT,note TEXT,record INTEGER PRIMARY KEY AUTOINCREMENT) 'item' is the full retailer's description for this shopping item. If I make this unique, then i eliminate all duplicates and any further entries of this item, so that's no good. item TEXT CONSTRAINT item UNIQUE is it legal sql syntax to allow more than one constraint field in table creation? I need at least these four below to guarantee that duplicate items do make it into the database but not on the same day. item TEXT CONSTRAINT item UNIQUE units TEXT CONSTRAINT units UNIQUE shop TEXT CONSTRAINT shop UNIQUE date TEXT CONSTRAINT date UNIQUE So the below three records would be allowed as the unit weight is different and also there are two different dates and as everyone knows, peppers come in all shapes and sizes (grin) (This is okay) pepper, 120gm, coles, 02/02/2006 pepper, 50gm, coles, 02/02/2006 pepper, 50gm, coles, 04/11/2007 Or would the multiple constraints work globally (if legal syntax) Would it end up with only the single record below because the all constraints operated globally? pepper, 120gm, coles, 02/02/2006 -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18209309.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] [newbie] linking two tables with index...
Mihai Limbasan <[EMAIL PROTECTED]> wrote: >> Igor Tandetnik wrote: >>> You can't make this happen automatically with SQLite. > >> Hmm? Of course you can. Just use a trigger: >> http://sqlite.org/lang_createtrigger.html Can you show an example of a trigger that would make these statements work: insert into snapshot (bumf) values ('one'); insert into sample(type, used) values ('a', 1); insert into sample(type, used) values ('b', 1); so that, at the end, the two new records in sample table would have snapshotid equal to snapshot.id from the record inserted into snapshot table. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie questions regarding in-memory database.
Karthik <[EMAIL PROTECTED]> wrote: > is it possible to set a limit on the database size? PRAGMA max_page_count http://sqlite.org/pragma.html > how does sqlite > behave when database size reaches the threshold(the > default/configured one)? You get an error executing the statement that takes it over threshold. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How does the database file grow?
Hi all, i create and fill database files which reach quite a large size after a while, because i only add data and never remove it. The database files themselves become quite fragmented on the disk. Because I can in many cases calculate in advance the size to which the file will grow, i would like to reserve the disk space before inserting all the data. Can this be done? Thanks, Martin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does the database file grow?
On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote: > Hi all, > > i create and fill database files which reach quite a large size > after a > while, because i only add data and never remove it. > The database files themselves become quite fragmented on the disk. > Because I can in many cases calculate in advance the size to which the > file will grow, i would like to reserve the disk space before > inserting > all the data. Can this be done? Perhaps like this: CREATE TABLE dummy(x); INSERT INTO dummy VALUES(zeroblob(1000)); DROP TABLE dummy; Change the argument of zeroblob to get the size you want. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does the database file grow?
D. Richard Hipp wrote: > On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote: > >> Hi all, >> >> i create and fill database files which reach quite a large size >> after a >> while, because i only add data and never remove it. >> The database files themselves become quite fragmented on the disk. >> Because I can in many cases calculate in advance the size to which the >> file will grow, i would like to reserve the disk space before >> inserting >> all the data. Can this be done? > > > Perhaps like this: > > CREATE TABLE dummy(x); > INSERT INTO dummy VALUES(zeroblob(1000)); > DROP TABLE dummy; > > Change the argument of zeroblob to get the size you want. Would something like this work (on linux)? dd if=/dev/zero of=mydatabase.db bs=1024 count=65536 would create a file that is 64MB in size and zero it out. I'm not use how sqlite would react to being given a preallocated but empty file that exists. -Steve ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepackaged sql statement
The problem with the view is that you can not pass a parameter or variable from the outer select to the views select. On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Henrik Bechmann" <[EMAIL PROTECTED]> > wrote in message news:[EMAIL PROTECTED] > > Thanks Igor! And the SQL statement can be bound to parameters in the > > usual ways? > > No, not in the view. You can, of course, select from the view (as if it > were a table), and _that_ query can be parameterized. > > Igor Tandetnik > > > > > ___ > 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] How does the database file grow?
On Jul 1, 2008, at 12:16 PM, Stephen Woodbridge wrote: > >> On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote: >>> Because I can in many cases calculate in advance the size to which >>> the >>> file will grow, i would like to reserve the disk space before >>> inserting >>> all the data. Can this be done? >> > Would something like this work (on linux)? > > dd if=/dev/zero of=mydatabase.db bs=1024 count=65536 > > would create a file that is 64MB in size and zero it out. I'm not use > how sqlite would react to being given a preallocated but empty file > that > exists. No. SQLite sees a file of zeros as a corrupt database. The internal structure of the file must be correctly initialized. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
>On Jun 30, 2008, at 2:37 PM, Andrea Connell wrote: > >> Any ideas? It's driving me crazy why SQLite is this much slower on >> UNIX >> boxes, while other applications maintain their speed. > >What filesystem are you using on the unix boxes? Are you *sure* you >are not using NFS? > >D. Richard Hipp >[EMAIL PROTECTED] > You know what, I'm not sure but I'd be willing to bet I am using NFS. I'll check around and see if there's anything else I can move it to. Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
I haven't looked closely at this problem but a cursory glance suggests that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause. Try doing the selection ASC and then sorting the output DESC as a seperate action. Alexey Pechnikov wrote: > Really, there is problem with multi-column indexes. You must use only primary > key index for ">=" where clause and "ASC" sorting and "<=" where clause and > DESC sorting. > > > 1. I try with primary key: > > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db close > > So, "type" is equal ("eid" mod 50). > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.00 sys 0.00 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > > Result: this index is good. > > > 2. And I try with two-columns common order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db eval {CREATE INDEX ev_idx ON events(type,eid)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 1.400088 sys 1.696106 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_idx ORDER BY > > > Result: this index is bad. > > > 3. And I try with two-columns desc order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.600037 sys 0.608038 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY > > > And with modified query: > > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 9972|22 > CPU Time: user 0.00 sys 0.00 > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid ASC LIMIT 1; > 22|22 > CPU Time: user 0.00 sys 0.004000 > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY > eid ASC LIMIT 1; > 32619772|22 > CPU Time: user 0.284018 sys 0.820051 > > > > > Result: this index is bad. > > > > P.S. Try with primary key index only and write your results. > ___ > 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] Index and ORDER BY
В сообщении от Tuesday 01 July 2008 19:26:47 John Stanton написал(а): > I haven't looked closely at this problem but a cursory glance suggests > that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause. But primary key index work fine. Why? > Try doing the selection ASC and then sorting the output DESC as a > seperate action. It's impossible for big tables. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
I agree. If I drop indices that use "type", I get my performance back for this query: sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; 16643833|27906245|5972704|0|22|9|4 CPU Time: user 0.001000 sys 0.001000 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY The problem is that indexing the type column gives me a huge performance benefit for other queries in my application. Is there any way I can force sqlite to not use an index for a particular query? Thanks for your help! Jeff On Jul 1, 2008, at 2:14 AM, Alexey Pechnikov wrote: > Really, there is problem with multi-column indexes. You must use > only primary > key index for ">=" where clause and "ASC" sorting and "<=" where > clause and > DESC sorting. > > > 1. I try with primary key: > > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { >for {set i 0} {$i<1} {incr i} { >set type [expr {$i % 50}] >db eval {insert into events values ($i,$type)} >} > } > db close > > So, "type" is equal ("eid" mod 50). > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 > ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.00 sys 0.00 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > > Result: this index is good. > > > 2. And I try with two-columns common order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { >for {set i 0} {$i<1} {incr i} { >set type [expr {$i % 50}] >db eval {insert into events values ($i,$type)} >} > } > db eval {CREATE INDEX ev_idx ON events(type,eid)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 > ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 1.400088 sys 1.696106 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_idx ORDER BY > > > Result: this index is bad. > > > 3. And I try with two-columns desc order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { >for {set i 0} {$i<1} {incr i} { >set type [expr {$i % 50}] >db eval {insert into events values ($i,$type)} >} > } > db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 > ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.600037 sys 0.608038 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY > > > And with modified query: > > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 > ORDER BY > eid DESC LIMIT 1; > 9972|22 > CPU Time: user 0.00 sys 0.00 > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 > ORDER BY > eid ASC LIMIT 1; > 22|22 > CPU Time: user 0.00 sys 0.004000 > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 > ORDER BY > eid ASC LIMIT 1; > 32619772|22 > CPU Time: user 0.284018 sys 0.820051 > > > > > Result: this index is bad. > > > > P.S. Try with primary key index only and write your results. > ___ > 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] Index and ORDER BY
Jeff, try this select instead sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and +type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY Regards-- Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, July 01, 2008 9:44 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Index and ORDER BY I agree. If I drop indices that use "type", I get my performance back for this query: sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; 16643833|27906245|5972704|0|22|9|4 CPU Time: user 0.001000 sys 0.001000 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY The problem is that indexing the type column gives me a huge performance benefit for other queries in my application. Is there any way I can force sqlite to not use an index for a particular query? Thanks for your help! Jeff CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] linking two tables with index...
Igor Tandetnik wrote: > Can you show an example of a trigger that would make these statements > work: > > insert into snapshot (bumf) values ('one'); > insert into sample(type, used) values ('a', 1); > insert into sample(type, used) values ('b', 1); > > so that, at the end, the two new records in sample table would have > snapshotid equal to snapshot.id from the record inserted into snapshot > table. > Sure. I'll use your examples, just making sure to add an id insertion in the first statement since the id column isn't specified as autoincrement. sqlite> .headers on sqlite> DROP TABLE IF EXISTS snapshot; sqlite> CREATE TABLE snapshot(id INTEGER PRIMARY KEY, bumf TEXT); sqlite> DROP TABLE IF EXISTS sample; sqlite> CREATE TABLE sample(snapshotid INTEGER, type TEXT, used INTEGER); sqlite> DROP TABLE IF EXISTS last_snapid; sqlite> CREATE TABLE last_snapid(snapid INTEGER); -- Table will hold a single row with the last inserted snapshot id sqlite> INSERT INTO last_snapid(snapid) VALUES(NULL); -- Ensure the (single) row exists, we'll keep updating it sqlite> SELECT * FROM last_snapid; snapid sqlite> CREATE TRIGGER capture_last_snapid AFTER INSERT ON snapshot BEGIN ...> UPDATE last_snapid SET snapid = new.id WHERE rowid = 1; ...> END; sqlite> CREATE TRIGGER update_sample AFTER INSERT ON sample BEGIN ...> UPDATE sample SET snapshotid = (SELECT snapid FROM last_snapid WHERE rowid = 1) WHERE rowid = NEW.rowid; ...> END; sqlite> INSERT INTO snapshot (id, bumf) VALUES (42, 'one'); sqlite> SELECT * FROM snapshot; id|bumf 42|one sqlite> SELECT * FROM last_snapid; -- To verify capture_last_snapid is working snapid 42 sqlite> INSERT INTO sample(type, used) VALUES ('a', 1); sqlite> INSERT INTO sample(type, used) VALUES ('b', 1); sqlite> SELECT * FROM sample; snapshotid|type|used 42|a|1 42|b|1 sqlite> INSERT INTO snapshot (id, bumf) VALUES (999, 'another one'); sqlite> SELECT * FROM snapshot; id|bumf 42|one 999|another one sqlite> INSERT INTO sample(type, used) VALUES ('c', 3); sqlite> INSERT INTO sample(type, used) VALUES ('d', 54); sqlite> INSERT INTO sample(type, used) VALUES ('e', 168); sqlite> SELECT * FROM sample; snapshotid|type|used 42|a|1 42|b|1 999|c|3 999|d|54 999|e|168 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Terrific! sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND +type=22 ORDER BY eid DESC LIMIT 1; 3261891|4910298|1206924|1|22|9|4 CPU Time: user 0.002000 sys 0.017997 Now I have a workaround - thanks to everyone for the help. I assume that what sqlite is doing under the hood here is doing a linear search of rows in descending order (of the primary key eid) starting at 3261976 to find one where type=22. In the common case, it will find one nearby, and the query will be fast. If it has to search a long way, though, I'd expect it to be rather slow. Of course, I'm much better off than before being that at least common cases are fast, but I'm curious about the general case, where you really want to do an indexed lookup on type. Is it a problem in sqlite that it will only optimize: "WHERE primary_key<=X ORDER BY primary_key DESC" if it's not using an index? Is it supposed to? Thanks, Jeff On Jul 1, 2008, at 9:56 AM, Noah Hart wrote: > Jeff, try this select instead > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 and +type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > > Regards-- Noah > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] > Sent: Tuesday, July 01, 2008 9:44 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Index and ORDER BY > > I agree. If I drop indices that use "type", I get my performance back > for this query: > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22 > ORDER BY eid DESC LIMIT 1; > 16643833|27906245|5972704|0|22|9|4 > CPU Time: user 0.001000 sys 0.001000 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > The problem is that indexing the type column gives me a huge > performance benefit for other queries in my application. Is there any > way I can force sqlite to not use an index for a particular query? > Thanks for your help! > Jeff > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. > If you are not the addressee or authorized to receive this for the > addressee, you must not use, copy, disclose, or take any action > based on this message or any information herein. If you have > received this message in error, please advise the sender immediately > by reply e-mail and delete this message. Thank you for your > cooperation. > > > ___ > 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] [newbie] linking two tables with index...
Mihai Limbasan <[EMAIL PROTECTED]> wrote: > Igor Tandetnik wrote: >> Can you show an example of a trigger that would make these statements >> work: >> >> insert into snapshot (bumf) values ('one'); >> insert into sample(type, used) values ('a', 1); >> insert into sample(type, used) values ('b', 1); >> >> so that, at the end, the two new records in sample table would have >> snapshotid equal to snapshot.id from the record inserted into >> snapshot table. >> > Sure. I'll use your examples, just making sure to add an id insertion > in the first statement since the id column isn't specified as > autoincrement. But it _is_ specified as INTEGER PRIMARY KEY. If I'm inserting a specific Id into snapshot, I'm just going to explicily specify the same id when inserting into sample, and I wouldn't need all the song and dance you show. The whole issue is that the ID in snapshot table is in fact automatically generated. For more details on the relationship between INTEGER PRIMARY KEY and AUTOINCREMENT, see http://sqlite.org/autoinc.html -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
On Jul 1, 2008, at 1:24 PM, [EMAIL PROTECTED] wrote: > > Is it a problem in sqlite that it will only optimize: "WHERE > primary_key<=X ORDER BY primary_key DESC" if it's not using an index? > Is it supposed to? It would be a problem if it where the case. But in every test I have tried, SQLite does in fact use an index on WHERE pk<=X ORDER BY pk DESC. If you can demonstrate a case where it does not, we will fix it. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepackaged sql statement
Alex Katebi wrote: > The problem with the view is that you can not pass a parameter or variable > from the outer select to the views select. Right that is not the purpose of a view. Think of a view as a virtual TABLE. You can not pass a parameter to a table either. You can just query the table or view and you CAN query with a parameter in the select statement. select * from where Maybe it would be help to restate what you are trying to do. -Steve > On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]> > wrote: > >> "Henrik Bechmann" <[EMAIL PROTECTED]> >> wrote in message news:[EMAIL PROTECTED] >>> Thanks Igor! And the SQL statement can be bound to parameters in the >>> usual ways? >> No, not in the view. You can, of course, select from the view (as if it >> were a table), and _that_ query can be parameterized. >> >> Igor Tandetnik >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] linking two tables with index...
Igor Tandetnik wrote: >> Sure. I'll use your examples, just making sure to add an id insertion >> in the first statement since the id column isn't specified as >> autoincrement. >> > > But it _is_ specified as INTEGER PRIMARY KEY. In your example it wasn't. It also wasn't in Martin's original table creation statement. > If I'm inserting a > specific Id into snapshot, I'm just going to explicily specify the same > id when inserting into sample, and I wouldn't need all the song and > dance you show. The whole issue is that the ID in snapshot table is in > fact automatically generated. > > It makes absolutely no difference. I just noted that so you wouldn't think it relied on a specific ID. Here's the whole thing again - I updated the table creation on the snapshot table and removed the id from the insertions on it. The triggers still work perfectly. sqlite> .headers on sqlite> DROP TABLE IF EXISTS snapshot; sqlite> CREATE TABLE snapshot(id INTEGER PRIMARY KEY AUTOINCREMENT, bumf TEXT); sqlite> DROP TABLE IF EXISTS sample; sqlite> CREATE TABLE sample(snapshotid INTEGER, type TEXT, used INTEGER); sqlite> DROP TABLE IF EXISTS last_snapid; sqlite> CREATE TABLE last_snapid(snapid INTEGER); -- Table will hold a single row with the last inserted snapshot id sqlite> INSERT INTO last_snapid(snapid) VALUES(NULL); -- Ensure the (single) row exists, we'll keep updating it sqlite> SELECT * FROM last_snapid; snapid sqlite> CREATE TRIGGER capture_last_snapid AFTER INSERT ON snapshot BEGIN ...> UPDATE last_snapid SET snapid = NEW.id WHERE rowid = 1; ...> END; sqlite> CREATE TRIGGER update_sample AFTER INSERT ON sample BEGIN ...> UPDATE sample SET snapshotid = (SELECT snapid FROM last_snapid WHERE rowid = 1) WHERE rowid = NEW.rowid; ...> END; sqlite> INSERT INTO snapshot (bumf) VALUES ('one'); sqlite> SELECT * FROM snapshot; id|bumf 1|one sqlite> SELECT * FROM last_snapid; -- To verify capture_last_snapid is working snapid 1 sqlite> INSERT INTO sample(type, used) VALUES ('a', 1); sqlite> INSERT INTO sample(type, used) VALUES ('b', 1); sqlite> SELECT * FROM sample; snapshotid|type|used 1|a|1 1|b|1 sqlite> INSERT INTO snapshot (bumf) VALUES ('another one'); sqlite> SELECT * FROM snapshot; id|bumf 1|one 2|another one sqlite> INSERT INTO sample(type, used) VALUES ('c', 3); sqlite> INSERT INTO sample(type, used) VALUES ('d', 54); sqlite> INSERT INTO sample(type, used) VALUES ('e', 168); sqlite> SELECT * FROM sample; snapshotid|type|used 1|a|1 1|b|1 2|c|3 2|d|54 2|e|168 On a sidenote: Are we nitpicking without even checking our facts, or are we trying to help someone? > For more details on the relationship between INTEGER PRIMARY KEY and > AUTOINCREMENT, see http://sqlite.org/autoinc.html > I'm very familiar with it, but thanks for your concern. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] linking two tables with index...
Mihai Limbasan wrote: > In your example it wasn't. Oops, misread what you wrote. What I mean is that it doesn't matter whether the ID is autogenerated or not (of course, if it isn't then the whole issue becomes moot - it in fact *should* be PRIMARY KEY AUTOINCREMENT.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Insert mil.rows to table can take up to a day
Hi All, I have the file which has million rows of insert statement to insert the data to the database like below: insert into tables values(...) (there are million insert statement to insert to the table) (file name is insertTables) Then I ran the following command to insert to table sqlite3 MyDB < insertTables For compeleting to insert 1 million rows it may take up to a day. Is there any way to speech up the insertion in this case. Thanks, JL ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert mil.rows to table can take up to a day
Joanne Pham wrote: > Hi All, > I have the file which has million rows of insert statement to insert the data > to the database like below: > insert into tables values(...) > > (there are million insert statement to insert to the table) > (file name is insertTables) > Then I ran the following command to insert to table > sqlite3 MyDB < insertTables > For compeleting to insert 1 million rows it may take up to a day. Is there > any way to speech up the insertion in this case. > Thanks, > JL > > Hi, Joanne. First of all, have you wrapped the insert sequence in a transaction? That speeds up things considerably. Second, try dropping all indexes on the table prior to insertion and recreate them after insertion is done. Third, make sure no complicated triggers run on insertion into that table. Other than that - are you perchance hosting the database on a flash medium, or generally a slow storage medium? Can you give us more details about the operating system environment and the table schemas? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert mil.rows to table can take up to a day
Thanks a lot Mihai, I will try it. Again thank for quick response. JL - Original Message From: Mihai Limbasan <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Tuesday, July 1, 2008 10:57:31 AM Subject: Re: [sqlite] Insert mil.rows to table can take up to a day Joanne Pham wrote: > Hi All, > I have the file which has million rows of insert statement to insert the data > to the database like below: > insert into tables values(...) > > (there are million insert statement to insert to the table) > (file name is insertTables) > Then I ran the following command to insert to table > sqlite3 MyDB < insertTables > For compeleting to insert 1 million rows it may take up to a day. Is there > any way to speech up the insertion in this case. > Thanks, > JL > > Hi, Joanne. First of all, have you wrapped the insert sequence in a transaction? That speeds up things considerably. Second, try dropping all indexes on the table prior to insertion and recreate them after insertion is done. Third, make sure no complicated triggers run on insertion into that table. Other than that - are you perchance hosting the database on a flash medium, or generally a slow storage medium? Can you give us more details about the operating system environment and the table schemas? ___ 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] Insert mil.rows to table can take up to a day
Thank you so much Mihai. It worked!!! Once again thanks a ton. JL - Original Message From: Mihai Limbasan <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Tuesday, July 1, 2008 10:57:31 AM Subject: Re: [sqlite] Insert mil.rows to table can take up to a day Joanne Pham wrote: > Hi All, > I have the file which has million rows of insert statement to insert the data > to the database like below: > insert into tables values(...) > > (there are million insert statement to insert to the table) > (file name is insertTables) > Then I ran the following command to insert to table > sqlite3 MyDB < insertTables > For compeleting to insert 1 million rows it may take up to a day. Is there > any way to speech up the insertion in this case. > Thanks, > JL > > Hi, Joanne. First of all, have you wrapped the insert sequence in a transaction? That speeds up things considerably. Second, try dropping all indexes on the table prior to insertion and recreate them after insertion is done. Third, make sure no complicated triggers run on insertion into that table. Other than that - are you perchance hosting the database on a flash medium, or generally a slow storage medium? Can you give us more details about the operating system environment and the table schemas? ___ 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] Index and ORDER BY
I'm including a copy of Alexey's relevant message below. Unless I misunderstand, he has a test case that demonstrates that for the table: CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER) the query: SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; runs much faster if there is no index on type. The culprit seems to be the <= in conjunction with the descending ordering. If you change either, the query gets much faster. He tried the using indices events(type,eid) and events(type,eid desc). I also tried your original suggestion of just events(type) and got the same result. Thanks, Jeff D. Richard Hipp wrote: > On Jul 1, 2008, at 1:24 PM, [EMAIL PROTECTED] wrote: > >> Is it a problem in sqlite that it will only optimize: "WHERE >> primary_key<=X ORDER BY primary_key DESC" if it's not using an index? >> Is it supposed to? >> > > It would be a problem if it where the case. But in every test I have > tried, SQLite does in fact use an index on WHERE pk<=X ORDER BY pk > DESC. If you can demonstrate a case where it does not, we will fix it. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Alexey Pechnikov wrote: > Really, there is problem with multi-column indexes. You must use only primary > key index for ">=" where clause and "ASC" sorting and "<=" where clause and > DESC sorting. > > > 1. I try with primary key: > > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db close > > So, "type" is equal ("eid" mod 50). > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.00 sys 0.00 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > > Result: this index is good. > > > 2. And I try with two-columns common order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db eval {CREATE INDEX ev_idx ON events(type,eid)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 1.400088 sys 1.696106 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_idx ORDER BY > > > Result: this index is bad. > > > 3. And I try with two-columns desc order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.600037 sys 0.608038 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY > > > And with modified query: > > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 9972|22 > CPU Time: user 0.00 sys 0.00 > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid ASC LIMIT 1; > 22|22 > CPU Time: user 0.00 sys 0.004000 > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY > eid ASC LIMIT 1; > 32619772|22 > CPU Time: user 0.284018 sys 0.820051 > > > > > Result: this index is bad. > > > > P.S. Try with primary key index only and write your results. > ___ > 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] Insert mil.rows to table can take up to a day
Joanne Pham wrote: > Thank you so much Mihai. > It worked!!! Once again thanks a ton. > JL > Glad to hear that - have fun! Mihai ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rigging up SQLite over LINQ (Entity Framework)
In case anyone stopped reading after the convoluted LINQ query, here's a simplified version of what it's trying to do. It's kindof confusing, but that's what you get when a computer constructs a query I guess: create table a (id integer primary key, bid integer); create table b (id integer primary key, myval varchar); select *, (select count(c) from (select 1 as c from b where b.id = a.bid)) from a; SQL error: no such column: a.bid Is there a way to reference an outer value from an inner sub-query like its attempting to do here? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Robert Simpson Sent: Monday, June 30, 2008 9:10 PM To: 'General Discussion of SQLite Database' Subject: [sqlite] Rigging up SQLite over LINQ (Entity Framework) Just when I thought I was hitting the home stretch, I came across an aggregate query test that I'm not sure how to solve. LINQ generated the following query programmatically: -- Begin auto-generated query SELECT 1 AS [C1], [Project2].[ProductID] AS [ProductID], [Project2].[ProductName] AS [ProductName], [Project2].[QuantityPerUnit] AS [QuantityPerUnit], [Project2].[UnitPrice] AS [UnitPrice], [Project2].[UnitsInStock] AS [UnitsInStock], [Project2].[UnitsOnOrder] AS [UnitsOnOrder], [Project2].[ReorderLevel] AS [ReorderLevel], [Project2].[Discontinued] AS [Discontinued], [Project2].[CategoryID] AS [CategoryID], [Project2].[SupplierID] AS [SupplierID] FROM ( SELECT [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[SupplierID] AS [SupplierID], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[Discontinued] AS [Discontinued], (SELECT Count([Project1].[C1]) AS [A1] FROM ( SELECT 1 AS [C1] FROM[Order Details] AS [Extent2] LEFT OUTER JOIN [Orders] AS [Extent3] ON [Extent2].[OrderID] = [Extent3].[OrderID] LEFT OUTER JOIN [Customers] AS [Extent4] ON [Extent3].[CustomerID] LIKE [Extent4].[CustomerID] INNER JOIN [Suppliers] AS [Extent5] ON [Extent4].[Country] LIKE [Extent5].[Country] WHERE ([Extent1].[SupplierID] = [Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID]) ) AS [Project1]) AS [C1] FROM [Products] AS [Extent1] ) AS [Project2] WHERE [Project2].[C1] > 2 -- End autogenerated query The error SQLite throws is "no such column: Extent1.ProductID" and I believe it's in the inner WHERE clause: WHERE ([Extent1].[SupplierID] = [Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID]) I can of course supply a schema, but I think it may be irrelevant and have more to do with the fact that it's got some inner subqueries referencing an outer object? Since the SQL is autogenerated, I have limited control over it. The actual LINQ query was: var query = from p in db.Products where p.Order_Details.Count(od => od.Orders.Customers.Country == p.Suppliers.Country) > 2 select p; Aside from this little issue involving aggregates, SQLite is working nicely with Microsoft's new Entity Framework. Robert Simpson ___ 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] Performance on HP
Are there any other processes or threads trying to open your db file while you run your tests? On Mon, Jun 23, 2008 at 9:48 AM, Andrea Connell <[EMAIL PROTECTED]> wrote: > > > >> The program took 47 seconds to run, but the results only account for > >> .39 seconds > > > > Most likely all the time is being spent in IO related system calls > > - read(), write() and fsync(). > > > > Dan. > > > Thanks for the idea Dan. How can I confirm this or try reducing the time > spent? I use the same method for reading my input file when I run both > SQLite and our in house system, and the other way only takes 4 seconds > total so I don't think it could be from that. Also, when I run our in > house system and use the profiler the time spent adds up to 100% > > So this must be something within SQLite. I am using a transaction for my > queries. How can I find all of that missing time? > > > If anybody is interested, here is my main chunk of code. ReadLine() > parses the input file and fills the required variables. This method is > shared for both database systems (SQLite and ours). ReadSQLiteComponent > just calls one of the sqlite3_column functions based on the type of the > field, and a similar method is used for our system. > > >std::ifstream inf(argv[1]); > >sqlite3 *db; >sqlite3_stmt *stmt; >sqlite3_stmt *stmt2; >sqlite3_stmt *stmt3; >int rc = sqlite3_open(argv[3], &db); >if( rc ) >{ >printf("Can't open database: %s\n", > sqlite3_errmsg(db)); >sqlite3_close(db); >return -1; >} >sqlite3_prepare(db,"BEGIN TRANSACTION;", 100, &stmt,0); >sqlite3_step(stmt); > >char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? > AND DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY LIKE ? > ;"; >int p = sqlite3_prepare_v2(db,qry,1000,&stmt,0); > char * qry2 = "SELECT * FROM LEVEL2 WHERE PARENT_KEY = ? > AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;"; > int p2 = sqlite3_prepare_v2(db,qry2,1000,&stmt2,0); > char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? > ;"; > int p3 = sqlite3_prepare_v2(db,qry3,1000,&stmt3,0); >if ( p || p2 || p3 ) >{ >printf("Can't create prepared statement: %s\n", > sqlite3_errmsg(db)); >sqlite3_close(db); >return -1; >} > >while (ReadLine(inf)) >{ >sqlite3_bind_text(stmt, 1, cntryid, -1, > SQLITE_TRANSIENT); >sqlite3_bind_int(stmt, 2, searcharea); >sqlite3_bind_text(stmt, 3, addrtype, -1, > SQLITE_TRANSIENT); >int len = strlen(phnkey); >phnkey[len] = '%'; >phnkey[len+1] = '\0'; >sqlite3_bind_text(stmt, 4, phnkey, -1, > SQLITE_TRANSIENT); > >while(sqlite3_step(stmt)==SQLITE_ROW) >{ >for(int i=0; i{ >ReadSQLiteComponent(0,i, stmt); >if (i==51) //LEVEL1.RECORDKEY > > sqlite3_bind_int(stmt2,1,sqlite3_column_int(stmt, i)); >} >sqlite3_bind_text(stmt2,2,prmlow, -1, > SQLITE_TRANSIENT); >sqlite3_bind_text(stmt2,3,prmhigh, -1, > SQLITE_TRANSIENT); >while(sqlite3_step(stmt2)==SQLITE_ROW) >{ >for(int i=0; i i++) >{ >ReadSQLiteComponent(1,i, > stmt2); >if (i==27) > //LEVEL2.RECORDKEY > > sqlite3_bind_int(stmt3,1,sqlite3_column_int(stmt2, i)); >} > > while(sqlite3_step(stmt3)==SQLITE_ROW) >{ >for(int i=0; > i > ReadSQLiteComponent(2,i, stmt3); >q++; >} >sqlite3_reset(stmt3); >} >sqlite3_reset(stmt2); >} >sqlite3_reset(stmt); >} > >sqlite3_prepare(db,"END TRANSACTION;", 100, &stmt,0); >sqlite3_step(stmt); > >sqlite3_finalize(stmt); >sqlite3_finalize(stmt2); >sqlite3_finalize(stmt3);
Re: [sqlite] Index and ORDER BY
On Jul 1, 2008, at 2:17 PM, Jeff Gibson wrote: > I'm including a copy of Alexey's relevant message below. Unless I > misunderstand, he has a test case that demonstrates that for the > table: > > CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER) > > the query: > > SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > > runs much faster if there is no index on type. The culprit seems to > be > the <= in conjunction with the descending ordering. If you change > either, the query gets much faster. He tried the using indices > events(type,eid) and events(type,eid desc). I also tried your > original > suggestion of just events(type) and got the same result. That would be a case of SQLite choosing a suboptimal index, which is very different from ignoring an index all together, which is what your original statement said. I see that if there is an index on events(type) that index is used rather than the primary key. This is because the query optimizer is assuming that type=22 is highly selective. Running ANALYZE might help. But a sure-fire solution is to change the query as follows: SELECT * FROM events WHERE eid<=32619750 AND +type=22 ORDER BY eid DESC LIMIT 1; Note the "+" operator in front of the "type" field in the WHERE clause. This + size makes that term of the WHERE clause an expression, rather than a constraint on a column, and this disqualifies it from use by an index. That forces SQLite to use the other query strategy, which is to use the integer primary key. Note that in this case, the correct index choice depends on the kind of data contained in the table. If there is only a single row out of 20 million for which type=22, but there are hundreds of thousands of rows with eid<=32619750, then the use of the index on event(type) is clearly the better strategy. Only when type=22 is a common occurrence does it become better to use the integer primary key. SQLite does not attempt to keep statistics on table contents, so it has no way of knowing which approach is really better. It makes its best guess. In this case, it happened to guess wrong. But, as I pointed out, a programmer with higher-level knowledge of the table content can steer SQLite toward the better choice with the judicious use of a "+" symbol. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite and updating VIEWs
Hey all, I'm working with a partitioned table setup with a permanent table and a temp table with the same columns and indexes. Every X time we dump all the records in the temp table over to the permanent one. In order to make selection queries easier to manage, I've gone and created a view like so: CREATE TEMPORARY VIEW table_view AS SELECT * FROM table_temp UNION SELECT * FROM table This was all going well, until I realized that updating was going to be very hard (insertion always goes to the temporary table). That seemed easy enough to manage if I use an INSTEAD OF trigger on the view for UPDATE statements. The problem is what I want to do in the trigger, which is this: 1) if the data is in the temporary table, update that 2) if the data is not in the temporary table, copy the data from the permanent table into the temp one, and then update the temp table Sadly, my SQL-fu isn't strong enough to know how to do this, and I'm starting to wonder if it's even possible. If someone could tell me if I can do it, and then provide a pointer as to how to go about it, I'd really appreciate it. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
I see. It turns out that the selectivity of "type" is highly variable - some types are very common and some are quite rare. What made me curious is that when I have an index on type and I look for the first few entries in ascending order, the query is very fast - it seems that it does the indexed search on type and then starts searching the matching rows in ascending order. For the descending order, it seems that it has to find all matching rows and then return the last one (I don't know for sure how it's working, but that seems to fit the performance measurements). Is there any way to have it use the index on type and then search the matching rows in descending order so no sorting is required? My confusion is that it seems to search in descending order when only the primary key is involved, but not when using an index, even if that index has DESC specified. As you say, I have put in the "+type" trick, and that speeds up my common case (where a matching type is nearby so the linear search isn't so bad), so now I'm in the much better situation of just worrying about the hypothetical case where it has to search a long way to find a matching type. Thanks, Jeff D. Richard Hipp wrote: > On Jul 1, 2008, at 2:17 PM, Jeff Gibson wrote: > > >> I'm including a copy of Alexey's relevant message below. Unless I >> misunderstand, he has a test case that demonstrates that for the >> table: >> >> CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER) >> >> the query: >> >> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY >> eid DESC LIMIT 1; >> >> runs much faster if there is no index on type. The culprit seems to >> be >> the <= in conjunction with the descending ordering. If you change >> either, the query gets much faster. He tried the using indices >> events(type,eid) and events(type,eid desc). I also tried your >> original >> suggestion of just events(type) and got the same result. >> > > That would be a case of SQLite choosing a suboptimal index, which is > very different from ignoring an index all together, which is what your > original statement said. I see that if there is an index on > events(type) that index is used rather than the primary key. This is > because the query optimizer is assuming that type=22 is highly > selective. Running ANALYZE might help. But a sure-fire solution is > to change the query as follows: > > SELECT * FROM events > WHERE eid<=32619750 >AND +type=22 > ORDER BY eid DESC > LIMIT 1; > > Note the "+" operator in front of the "type" field in the WHERE > clause. This + size makes that term of the WHERE clause an > expression, rather than a constraint on a column, and this > disqualifies it from use by an index. That forces SQLite to use the > other query strategy, which is to use the integer primary key. > > Note that in this case, the correct index choice depends on the kind > of data contained in the table. If there is only a single row out of > 20 million for which type=22, but there are hundreds of thousands of > rows with eid<=32619750, then the use of the index on event(type) is > clearly the better strategy. Only when type=22 is a common occurrence > does it become better to use the integer primary key. SQLite does not > attempt to keep statistics on table contents, so it has no way of > knowing which approach is really better. It makes its best guess. In > this case, it happened to guess wrong. But, as I pointed out, a > programmer with higher-level knowledge of the table content can steer > SQLite toward the better choice with the judicious use of a "+" symbol. > > > 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] SQLite and updating VIEWs
Shawn Wilsher <[EMAIL PROTECTED]> wrote: > 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 CREATE TRIGGER BEGIN insert into temp_table select * from perm_table where and ; update temp_table set ...; END; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword for index? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and updating VIEWs
On Tue, Jul 1, 2008 at 3:24 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > CREATE TRIGGER > BEGIN >insert into temp_table >select * from perm_table >where 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
Re: [sqlite] SQLite and updating VIEWs
Shawn Wilsher <[EMAIL PROTECTED]> wrote: > 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. 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. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] blob /transaction wierdness
Hi, I'm having some trouble with blobs. I have 4 blobs tht I want to insert into a db, and it works fine when I execute each insert as a single commit. However, when i try to use transactions to input blocks of 255 inserts, blob 4 gets inputted in the position of blob3, blob 3 in position of blob 2 etc etc. Very strange. I've written some code to explain what I mean. The code takes a long time to finish, so if you want to try it, compile it, then execute for a few seconds before ctrl-c'ing it to quit. It should give you enough data to see what I mean. In example 1, the fields are where they should be. In code 2, the fields all get shifted left by 1 column. But the code is identical except for where the "begin" and "commit" statements are located. Anyone know what could be the problem? Im using sqlite v3, th specific version is the one that came with fedora core 8 - 3.4.2. The code is in "c". here are 2 pieces of code - 1 using transactions, 1 without - but otherwise identical. Any help much apreciated. What I'm trying to acheive would be way too slow without transactions. #include #include #include sqlite3* db; sqlite3_stmt *state; void execblobsql(const char* statement,int v1,int v2,int v3,int v4){ char c1[1]; c1[0] = v1; char c2[1]; c2[0] = v2; char c3[1]; c3[0] = v3; char c4[1]; c4[0] = v4; sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,statement,-1,&state,NULL); sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT); sqlite3_step(state); sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); } int main (){ sqlite3_open("./blobs.db", &db); if (db == 0){ printf ("database could not be opened.\n"); return 1; } char *errmsg; sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4 BLOB)",0,0,&errmsg); char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)"; int looper1 = 0; int looper2 = 0; int looper3 = 0; int looper4 = 0; while (looper1 < 256){ while (looper2 < 256){ while (looper3 < 256){ /* sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,stata,-1,&state,NULL);*/ while (looper4 < 256){ execblobsql(stata, looper1, looper2, looper3, looper4); looper4++; } looper3++; looper4=0; /* sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/ } looper2++; looper3=0; looper4=0; } looper1++; looper2=0; looper3=0; looper4=0; } } #include #include #include sqlite3* db; sqlite3_stmt *state; void execblobsql(const char* statement,int v1,int v2,int v3,int v4){ char c1[1]; c1[0] = v1; char c2[1]; c2[0] = v2; char c3[1]; c3[0] = v3; char c4[1]; c4[0] = v4; /*sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,statement,-1,&state,NULL);*/ sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT); sqlite3_step(state); /*sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/ } int main (){ sqlite3_open("./blobs.db", &db); if (db == 0){ printf ("database could not be opened.\n"); return 1; } char *errmsg; sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4 BLOB)",0,0,&errmsg); char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)"; int looper1 = 0; int looper2 = 0; int looper3 = 0; int looper4 = 0; while (looper1 < 256){ while (looper2 < 256){ while (looper3 < 256){ sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,stata,-1,&state,NULL); while (looper4 < 256){ execblobsql(stata, looper1, looper2, looper3, looper4); looper4++; } looper3++; looper4=0; sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); } looper2++; looper3=0; looper4=0; } looper1++; looper2=0; looper3=0; looper4=0; } } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] unresolved external symbol
Hi list. I'm currently making modifications to a project, and want to add SQLite3 to it. I'm using VS6, have the sqlite3.h and sqlite3.c in the same directory, and this is the code: #include "sqlite3.h" bool TestSQLite() { sqlite3 *db; int rc; rc = sqlite3_open("test.sqlite",&db); return true; } but when I build, get the following error Configuration: mfc01 - Win32 Release Compiling... mfc01Dlg.cpp Linking... mfc01Dlg.obj : error LNK2001: unresolved external symbol _sqlite3_open However, if I just compile, everything goes ok. Any help? ___ 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 wrote: > 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. Given a row that you have to work with, your current setup is lacking enough info to know which table the row came from in the view. You might define you view like: CREATE TEMPORARY VIEW table_view AS SELECT *, 'T' as which FROM table_temp UNION SELECT *, 'P' as which FROM table Now in your trigger you can check "which" and know if you need to copy the row to table_temp. -Steve ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
On Tue, 1 Jul 2008, Alexey Pechnikov wrote: > Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" > and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword > for index? The DESC keyword creates the index in descending collation order, rather than ascending order (default). I believe this sort order may not be observed in older versions, but more recent ones do so. Chris Martin UNC-CH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а): > On Tue, 1 Jul 2008, Alexey Pechnikov wrote: > > Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" > > and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" > > keyword for index? > > The DESC keyword creates the index in descending collation order, rather > than ascending order (default). I believe this sort order may not be > observed in older versions, but more recent ones do so. I'm using SQLite 3.5.9 and there are no differents in my tests between DESC and default indeces. I try create index with keywork DESC for optimize DESC sorting but it don't work for me. My tests you can see above. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and updating VIEWs
On Tue, Jul 1, 2008 at 3:37 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > They won't fail - they will successfully insert zero records. It's > perfectly valid to run INSERT ... SELECT and have the SELECT part > produce an empty resultset. It simply does nothing. Sorry, I should have been more explicit in what we are doing. The select will return results because we do not want to delete the data from the temporary table (that involves a write and fsyncing). However, I think I've come up with a solution: CREATE TRIGGER BEGIN INSERT OR REPLACE INTO temp_table SELECT * FROM table_view WHERE 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 <[EMAIL PROTECTED]> wrote: > 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 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. > 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. > 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). UNION has nothing to do with primary key. It only eliminates duplicate records - records with all fields equal. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and updating VIEWs
On Tue, Jul 1, 2008 at 4:05 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Will it? 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] unresolved external symbol
Hello Mauricio, Tuesday, July 1, 2008, 3:43:16 PM, you wrote: MC> Hi list. MC> I'm currently making modifications to a project, and want to add SQLite3 to MC> it. MC> I'm using VS6, have the sqlite3.h and sqlite3.c in the same directory, and MC> this is the code: MC> #include "sqlite3.h" MC> MC> bool TestSQLite() MC> { MC> sqlite3 *db; MC> int rc; MC> rc = sqlite3_open("test.sqlite",&db); MC> return true; MC> } MC> but when I build, get the following error MC> Configuration: mfc01 - Win32 Release MC> Compiling... MC> mfc01Dlg.cpp MC> Linking... MC> mfc01Dlg.obj : error LNK2001: unresolved external symbol _sqlite3_open MC> However, if I just compile, everything goes ok. MC> Any help? MC> ___ MC> sqlite-users mailing list MC> sqlite-users@sqlite.org MC> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users You know, you actually have to build SQLite3.c and the linker has to include it when you link right? It's not enough that they be in the same folder. This is pretty basic and I'm sorry if I dumbed it down too much. I'm just not seeing where you linked it in, in the messages. -- Best regards, Tegmailto:[EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Yes, this has been my experience as well. I've tried 3.5.6 and 3.5.9. Jeff Alexey Pechnikov wrote: > В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а): > >> On Tue, 1 Jul 2008, Alexey Pechnikov wrote: >> >>> Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" >>> and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" >>> keyword for index? >>> >> The DESC keyword creates the index in descending collation order, rather >> than ascending order (default). I believe this sort order may not be >> observed in older versions, but more recent ones do so. >> > > I'm using SQLite 3.5.9 and there are no differents in my tests between DESC > and default indeces. I try create index with keywork DESC for optimize DESC > sorting but it don't work for me. My tests you can see above. > ___ > 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] blob /transaction wierdness
Column numbering for binding starts at 0 Not 1. sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); smlacc1 leador <[EMAIL PROTECTED]> wrote: Hi, I'm having some trouble with blobs. I have 4 blobs tht I want to insert into a db, and it works fine when I execute each insert as a single commit. However, when i try to use transactions to input blocks of 255 inserts, blob 4 gets inputted in the position of blob3, blob 3 in position of blob 2 etc etc. Very strange. I've written some code to explain what I mean. The code takes a long time to finish, so if you want to try it, compile it, then execute for a few seconds before ctrl-c'ing it to quit. It should give you enough data to see what I mean. In example 1, the fields are where they should be. In code 2, the fields all get shifted left by 1 column. But the code is identical except for where the "begin" and "commit" statements are located. Anyone know what could be the problem? Im using sqlite v3, th specific version is the one that came with fedora core 8 - 3.4.2. The code is in "c". here are 2 pieces of code - 1 using transactions, 1 without - but otherwise identical. Any help much apreciated. What I'm trying to acheive would be way too slow without transactions. #include #include #include sqlite3* db; sqlite3_stmt *state; void execblobsql(const char* statement,int v1,int v2,int v3,int v4){ char c1[1]; c1[0] = v1; char c2[1]; c2[0] = v2; char c3[1]; c3[0] = v3; char c4[1]; c4[0] = v4; sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,statement,-1,&state,NULL); sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT); sqlite3_step(state); sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); } int main (){ sqlite3_open("./blobs.db", &db); if (db == 0){ printf ("database could not be opened.\n"); return 1; } char *errmsg; sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4 BLOB)",0,0,&errmsg); char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)"; int looper1 = 0; int looper2 = 0; int looper3 = 0; int looper4 = 0; while (looper1 < 256){ while (looper2 < 256){ while (looper3 < 256){ /* sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,stata,-1,&state,NULL);*/ while (looper4 < 256){ execblobsql(stata, looper1, looper2, looper3, looper4); looper4++; } looper3++; looper4=0; /* sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/ } looper2++; looper3=0; looper4=0; } looper1++; looper2=0; looper3=0; looper4=0; } } #include #include #include sqlite3* db; sqlite3_stmt *state; void execblobsql(const char* statement,int v1,int v2,int v3,int v4){ char c1[1]; c1[0] = v1; char c2[1]; c2[0] = v2; char c3[1]; c3[0] = v3; char c4[1]; c4[0] = v4; /*sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,statement,-1,&state,NULL);*/ sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT); sqlite3_step(state); /*sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/ } int main (){ sqlite3_open("./blobs.db", &db); if (db == 0){ printf ("database could not be opened.\n"); return 1; } char *errmsg; sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4 BLOB)",0,0,&errmsg); char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)"; int looper1 = 0; int looper2 = 0; int looper3 = 0; int looper4 = 0; while (looper1 < 256){ while (looper2 < 256){ while (looper3 < 256){ sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,stata,-1,&state,NULL); while (looper4 < 256){ execblobsql(stata, looper1, looper2, looper3, looper4); looper4++; } looper3++; looper4=0; sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); } looper2++; looper3=0; looper4=0; } looper1++; looper2=0; looper3=0; looper4=0; } } ___ 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] blob /transaction wierdness
Column numbering for binding starts at 0 Not 1. sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); smlacc1 leador <[EMAIL PROTECTED]> wrote: Hi, I'm having some trouble with blobs. I have 4 blobs tht I want to insert into a db, and it works fine when I execute each insert as a single commit. However, when i try to use transactions to input blocks of 255 inserts, blob 4 gets inputted in the position of blob3, blob 3 in position of blob 2 etc etc. Very strange. I've written some code to explain what I mean. The code takes a long time to finish, so if you want to try it, compile it, then execute for a few seconds before ctrl-c'ing it to quit. It should give you enough data to see what I mean. In example 1, the fields are where they should be. In code 2, the fields all get shifted left by 1 column. But the code is identical except for where the "begin" and "commit" statements are located. Anyone know what could be the problem? Im using sqlite v3, th specific version is the one that came with fedora core 8 - 3.4.2. The code is in "c". here are 2 pieces of code - 1 using transactions, 1 without - but otherwise identical. Any help much apreciated. What I'm trying to acheive would be way too slow without transactions. #include #include #include sqlite3* db; sqlite3_stmt *state; void execblobsql(const char* statement,int v1,int v2,int v3,int v4){ char c1[1]; c1[0] = v1; char c2[1]; c2[0] = v2; char c3[1]; c3[0] = v3; char c4[1]; c4[0] = v4; sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,statement,-1,&state,NULL); sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT); sqlite3_step(state); sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); } int main (){ sqlite3_open("./blobs.db", &db); if (db == 0){ printf ("database could not be opened.\n"); return 1; } char *errmsg; sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4 BLOB)",0,0,&errmsg); char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)"; int looper1 = 0; int looper2 = 0; int looper3 = 0; int looper4 = 0; while (looper1 < 256){ while (looper2 < 256){ while (looper3 < 256){ /* sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,stata,-1,&state,NULL);*/ while (looper4 < 256){ execblobsql(stata, looper1, looper2, looper3, looper4); looper4++; } looper3++; looper4=0; /* sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/ } looper2++; looper3=0; looper4=0; } looper1++; looper2=0; looper3=0; looper4=0; } } #include #include #include sqlite3* db; sqlite3_stmt *state; void execblobsql(const char* statement,int v1,int v2,int v3,int v4){ char c1[1]; c1[0] = v1; char c2[1]; c2[0] = v2; char c3[1]; c3[0] = v3; char c4[1]; c4[0] = v4; /*sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,statement,-1,&state,NULL);*/ sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT); sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT); sqlite3_step(state); /*sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/ } int main (){ sqlite3_open("./blobs.db", &db); if (db == 0){ printf ("database could not be opened.\n"); return 1; } char *errmsg; sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4 BLOB)",0,0,&errmsg); char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)"; int looper1 = 0; int looper2 = 0; int looper3 = 0; int looper4 = 0; while (looper1 < 256){ while (looper2 < 256){ while (looper3 < 256){ sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); sqlite3_prepare(db,stata,-1,&state,NULL); while (looper4 < 256){ execblobsql(stata, looper1, looper2, looper3, looper4); looper4++; } looper3++; looper4=0; sqlite3_finalize(state); sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); } looper2++; looper3=0; looper4=0; } looper1++; looper2=0; looper3=0; looper4=0; } } ___ 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] blob /transaction wierdness
Ken <[EMAIL PROTECTED]> wrote: > Column numbering for binding starts at 0 Not 1. > sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); Not true. Parameters in sqlite3_bind_* are numbered from 1. Columns in sqlite3_column_* are numbered from 0. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob /transaction wierdness
smlacc1 leador <[EMAIL PROTECTED]> wrote: > I'm having some trouble with blobs. I have 4 blobs tht I want to > insert into a db, and it works fine when I execute each insert as a > single commit. However, when i try to use transactions to input > blocks of 255 inserts, blob 4 gets inputted in the position of blob3, > blob 3 in position of blob 2 etc etc. Very strange. I've written > some code to explain what I mean. The code takes a long time to > finish, so if you want to try it, compile it, then execute for a few > seconds before ctrl-c'ing it to quit. It should give you enough data > to see what I mean. In example 1, the fields are where they should > be. In code 2, the fields all get shifted left by 1 column. They aren't. It's just that most of your inserts in the inner loops actually fail. The regular pattern of failures makes it look like the values are shifted. Check the return value of sqlite3_step to see for yourself. You must call sqlite3_reset before you can rebind parameters and step again. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob /transaction wierdness
Yup thats what i was thinking, sqlite3_column. My mistake and thanks for catching that! Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ken wrote: > Column numbering for binding starts at 0 Not 1. > sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); Not true. Parameters in sqlite3_bind_* are numbered from 1. Columns in sqlite3_column_* are numbered from 0. Igor Tandetnik ___ 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] Table Level Locking
Hi All, I read the online document regarding "Table Level Locking" as below: At any one time, a single table may have any number of active read-locks or a single active write lock. To read data a table, a connection must first obtain a read-lock. To write to a table, a connection must obtain a write-lock on that table. If a required table lock cannot be obtained, the query fails and SQLITE_LOCKED is returned to the caller So the question that I had is while writing the data to table(write lock) another process can read the data from same table without any problem? Thanks, Joanne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob /transaction wierdness
ok, I'll try that. Thanks. On Tue, Jul 1, 2008 at 6:10 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > smlacc1 leador <[EMAIL PROTECTED]> wrote: > > I'm having some trouble with blobs. I have 4 blobs tht I want to > > insert into a db, and it works fine when I execute each insert as a > > single commit. However, when i try to use transactions to input > > blocks of 255 inserts, blob 4 gets inputted in the position of blob3, > > blob 3 in position of blob 2 etc etc. Very strange. I've written > > some code to explain what I mean. The code takes a long time to > > finish, so if you want to try it, compile it, then execute for a few > > seconds before ctrl-c'ing it to quit. It should give you enough data > > to see what I mean. In example 1, the fields are where they should > > be. In code 2, the fields all get shifted left by 1 column. > > They aren't. It's just that most of your inserts in the inner loops > actually fail. The regular pattern of failures makes it look like the > values are shifted. Check the return value of sqlite3_step to see for > yourself. > > You must call sqlite3_reset before you can rebind parameters and step > again. > > Igor Tandetnik > > > > ___ > 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] Table Level Locking
Table level locking is used among statements for the same connection. File level locking is used among connections. Your case is file level. On Tue, Jul 1, 2008 at 8:00 PM, Joanne Pham <[EMAIL PROTECTED]> wrote: > Hi All, > I read the online document regarding "Table Level Locking" as below: > At any one time, a single table may have any number of active > read-locks or a single active write lock. To read data a table, a connection > must first obtain a read-lock. To write to a table, a > connection must obtain a write-lock on that table. If a required table lock > cannot be obtained, the query fails and SQLITE_LOCKED is > returned to the caller > So the question that I had is while writing the data to table(write lock) > another process can read the data from same table without any problem? > Thanks, > Joanne > > > > ___ > 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] prepackaged sql statement
Hi Steve, This is my problem: create table inet0 (dest, mask, nexthop); create index inet0_idx on inet0(mask, destination); insert into inet0 values(x'0100', x'FF00', x'08080808'); insert into inet0 values(x'0101', x'', x'16161616'); insert into inet0 values(x'01010100', x'FF00', x'24242424'); insert into inet0 values(x'01010100', x'FF00', x''); select * from inet0 where destination=bitand(x'01010101', (select mask from inet0 where destination=bitand(x'01010101, mask) order by mask desc)) order by mask desc; The objective of the above select is to find one or more longest mask nexthop values. I want to make a view for the above select and pass in a destination variable as parameter. Thanks, -Alex On Tue, Jul 1, 2008 at 2:41 PM, Stephen Woodbridge <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > The problem with the view is that you can not pass a parameter or > variable > > from the outer select to the views select. > > Right that is not the purpose of a view. Think of a view as a virtual > TABLE. You can not pass a parameter to a table either. You can just > query the table or view and you CAN query with a parameter in the select > statement. > > select * from where > > Maybe it would be help to restate what you are trying to do. > > -Steve > > > On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]> > > wrote: > > > >> "Henrik Bechmann" <[EMAIL PROTECTED]> > >> wrote in message news:[EMAIL PROTECTED] > >>> Thanks Igor! And the SQL statement can be bound to parameters in the > >>> usual ways? > >> No, not in the view. You can, of course, select from the view (as if it > >> were a table), and _that_ query can be parameterized. > >> > >> Igor Tandetnik > >> > >> > >> > >> > >> ___ > >> 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob /transaction wierdness
Can you update your SQLite to the latest revision? On Tue, Jul 1, 2008 at 3:42 PM, smlacc1 leador <[EMAIL PROTECTED]> wrote: > Hi, > > I'm having some trouble with blobs. I have 4 blobs tht I want to insert > into a db, and it works fine when I execute each insert as a single commit. > However, when i try to use transactions to input blocks of 255 inserts, > blob > 4 gets inputted in the position of blob3, blob 3 in position of blob 2 etc > etc. Very strange. I've written some code to explain what I mean. The > code takes a long time to finish, so if you want to try it, compile it, > then > execute for a few seconds before ctrl-c'ing it to quit. It should give you > enough data to see what I mean. In example 1, the fields are where they > should be. In code 2, the fields all get shifted left by 1 column. But > the > code is identical except for where the "begin" and "commit" statements are > located. Anyone know what could be the problem? Im using sqlite v3, th > specific version is the one that came with fedora core 8 - 3.4.2. The > code > is in "c". > > here are 2 pieces of code - 1 using transactions, 1 without - but otherwise > identical. > > Any help much apreciated. What I'm trying to acheive would be way too slow > without transactions. > > #include > #include > #include > > sqlite3* db; > sqlite3_stmt *state; > > void execblobsql(const char* statement,int v1,int v2,int v3,int v4){ > char c1[1]; > c1[0] = v1; > char c2[1]; > c2[0] = v2; > char c3[1]; > c3[0] = v3; > char c4[1]; > c4[0] = v4; > sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); > sqlite3_prepare(db,statement,-1,&state,NULL); > sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); > sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT); > sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT); > sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT); > > sqlite3_step(state); > sqlite3_finalize(state); > sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); > } > > int main (){ > sqlite3_open("./blobs.db", &db); > if (db == 0){ > printf ("database could not be opened.\n"); > return 1; > } > char *errmsg; > sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4 > BLOB)",0,0,&errmsg); > char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)"; > int looper1 = 0; > int looper2 = 0; > int looper3 = 0; > int looper4 = 0; > > > while (looper1 < 256){ > while (looper2 < 256){ > while (looper3 < 256){ > /* sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); >sqlite3_prepare(db,stata,-1,&state,NULL);*/ >while (looper4 < 256){ > execblobsql(stata, looper1, looper2, looper3, looper4); > looper4++; >} >looper3++; >looper4=0; > /* sqlite3_finalize(state); >sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/ > } > looper2++; > looper3=0; > looper4=0; > } > looper1++; > looper2=0; > looper3=0; > looper4=0; > } > > } > > > #include > #include > #include > > sqlite3* db; > sqlite3_stmt *state; > > void execblobsql(const char* statement,int v1,int v2,int v3,int v4){ > char c1[1]; > c1[0] = v1; > char c2[1]; > c2[0] = v2; > char c3[1]; > c3[0] = v3; > char c4[1]; > c4[0] = v4; > /*sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); > sqlite3_prepare(db,statement,-1,&state,NULL);*/ > sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); > sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT); > sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT); > sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT); > > sqlite3_step(state); > /*sqlite3_finalize(state); > sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/ > } > > int main (){ > sqlite3_open("./blobs.db", &db); > if (db == 0){ > printf ("database could not be opened.\n"); > return 1; > } > char *errmsg; > sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4 > BLOB)",0,0,&errmsg); > char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)"; > int looper1 = 0; > int looper2 = 0; > int looper3 = 0; > int looper4 = 0; > > > while (looper1 < 256){ > while (looper2 < 256){ > while (looper3 < 256){ >sqlite3_exec (db, "BEGIN", NULL, NULL, NULL); >sqlite3_prepare(db,stata,-1,&state,NULL); >while (looper4 < 256){ > execblobsql(stata, looper1, looper2, looper3, looper4); > looper4++; >} >looper3++; >looper4=0; >sqlite3_finalize(state); >sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); > } > looper2++; > looper3=0; > looper4=0; > } > looper1++; > looper2=0; > looper3=0; > looper4=0; > } > > } > ___ > 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/l
Re: [sqlite] blob /transaction wierdness
this worked great. thank you. On Tue, Jul 1, 2008 at 6:10 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > smlacc1 leador <[EMAIL PROTECTED]> wrote: > > I'm having some trouble with blobs. I have 4 blobs tht I want to > > insert into a db, and it works fine when I execute each insert as a > > single commit. However, when i try to use transactions to input > > blocks of 255 inserts, blob 4 gets inputted in the position of blob3, > > blob 3 in position of blob 2 etc etc. Very strange. I've written > > some code to explain what I mean. The code takes a long time to > > finish, so if you want to try it, compile it, then execute for a few > > seconds before ctrl-c'ing it to quit. It should give you enough data > > to see what I mean. In example 1, the fields are where they should > > be. In code 2, the fields all get shifted left by 1 column. > > They aren't. It's just that most of your inserts in the inner loops > actually fail. The regular pattern of failures makes it look like the > values are shifted. Check the return value of sqlite3_step to see for > yourself. > > You must call sqlite3_reset before you can rebind parameters and step > again. > > Igor Tandetnik > > > > ___ > 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] Index and ORDER BY
On Jul 2, 2008, at 2:53 AM, Alexey Pechnikov wrote: > В сообщении от Tuesday 01 July 2008 23:47:50 > [EMAIL PROTECTED] написал(а): >> On Tue, 1 Jul 2008, Alexey Pechnikov wrote: >>> Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" >>> and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" >>> keyword for index? >> >> The DESC keyword creates the index in descending collation order, >> rather >> than ascending order (default). I believe this sort order may not be >> observed in older versions, but more recent ones do so. > > I'm using SQLite 3.5.9 and there are no differents in my tests > between DESC > and default indeces. I try create index with keywork DESC for > optimize DESC > sorting but it don't work for me. My tests you can see above. Have you seen the notes on file-format and "pragma legacy_file_format" in the documentation for CREATE INDEX? http://www.sqlite.org/lang_createindex.html Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rollback/replay journals and durability of the most recent transaction
I've done a little looking into journals, fsyncs, and filesystems recently. One thing I'm trying to understand is whether there was a reason for SQLite choosing to use a rollback journal (of the steps to undo a transaction) rather than a replay journal (of the steps to perform a transaction). In the same way as SQLite uses the presence of a complete (and consistent) journal to indicate that a rollback should be performed, the presence of a complete (and consistent) journal can instead indicate that a replay should be performed. One difference between rollback and replay journals is that with a rollback journal the transaction becomes committed on removal of the journal, but with a replay journal the transaction commits on completion of the journal. This difference means that to ensure durability of a commit with a rollback journal, the parent directory must be synced (after the removal). I can't see that this is done in SQLite, which would mean that durability against filesystem interruption is not obtained for the most recent transaction. (This is not important to me personally, but some may like to know that, on return from a commit statement, the transaction is committed to disk.) The reason that I was considering a replay journal is that for exclusive access mode (at least - it gets more complicated with multiple connections) several transactions could be "committed" to the journal before any need to sync the database file itself. As a possible further optimization, if some "buffer" was kept containing (perhaps locations of) pages modified by the transactions in the journal, then transactions in the journal would not need to be written to the database itself until the journal was so large as to make maintenance of the buffer too costly. This could significantly reduce the frequency of filesystem barrier operations required for consistency, particularly on filesystems with some sort of safe-append behavior (even if it's just that unwritten blocks are zeroed out as on xfs and ntfs). (If durability of each transaction is desired, then writes to the database itself might as well commence after each transaction is committed and synced to the journal, but need not be synced in the database itself until the size of the journal is such that it should be removed.) Thanks, Karl. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
Thank you Igor, the solution below (that you also posted) is what I found in the forums after hours of searching when I should have been asleep. And rather that produce shoddy code, I downloaded a proper sqlite tool to verify that it was all working fine. Thank you for the response, I have learned a lot from you recently:) create table t(a, b, unique(a, b)); insert into t values('a', 'b'); -- ok insert into t values('a', 'c'); -- ok insert into t values('z', 'b'); -- ok insert into t values('z', 'b'); -- fails: this pair already exists -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18230769.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
I understand your solution Woody, but it isn't practical for me, not for my home shopping list program. A full on relational database like that is an awful lot of work and it's only for home use (and any other family I can con into using it and testing it). I'd go for the relational route if I were designing an enterprise wide product, it's only sensible (as you intimated) but Igor's solution (that I also found independantly) will work fine for a small system for now. Thanks for the ideas, will keep proper design in mind if I ever get talked into doing something for a company (something I try mightily to avoid, believe me!!!) -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18230807.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
U, hmm. The tips I gave you were from my pda based shopping program that will be selling for 9.99 soon. Its 6 for one, half dozen for the other. You can design the db so it does the work for you or you code the program to do the work for you. Either way, you will get things to work, it just depends upon how you want to partition your code. Woody from his pda -Original Message- From: flakpit <[EMAIL PROTECTED]> Sent: Tuesday, July 01, 2008 11:00 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multiple constraints per table? I understand your solution Woody, but it isn't practical for me, not for my home shopping list program. A full on relational database like that is an awful lot of work and it's only for home use (and any other family I can con into using it and testing it). I'd go for the relational route if I were designing an enterprise wide product, it's only sensible (as you intimated) but Igor's solution (that I also found independantly) will work fine for a small system for now. Thanks for the ideas, will keep proper design in mind if I ever get talked into doing something for a company (something I try mightily to avoid, believe me!!!) -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18230807.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] Index and ORDER BY
> > I'm using SQLite 3.5.9 and there are no differents in my tests > > between DESC > > and default indeces. I try create index with keywork DESC for > > optimize DESC > > sorting but it don't work for me. My tests you can see above. > > Have you seen the notes on file-format and "pragma legacy_file_format" > in the documentation for CREATE INDEX? > >http://www.sqlite.org/lang_createindex.html > Is it safe to use this pragma? I have seen the notes but I'm not sure ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
Harold Wood & Meyuni Gani wrote: > > U, hmm. The tips I gave you were from my pda based shopping program > that will be selling for 9.99 soon. > Good on you Woody, hope you sell a lot.! If my eyes were up to the challenge of reading my PDA's small screen, i'd buy a copy and save myself work (LOL!) With the amount of junk I've churned out of the years, i've yet to get anyone to buy anything yet (ROFL). Actually, not quite true. One registration out of 6,500 downloads of my address book. -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18231100.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users