Re: [sqlite] SQL query, finding out which row a result is in
On Mon, Apr 23, 2007 at 09:26:53AM -0400, Stephen Oberholtzer wrote: On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible. The arbitrary statement can order the results any way it wants. Let's say your resultset consists of 3 columns: memberid, lastname, firstname. Then you can do this: create temp table _results (rownumber int AUTOINCREMENT, memberid int, lastname text, firstname text); insert into _results (memberid, lastname, firstname) select memberid, lastname, firstname from MainTable where whatever; select * from _results where memberid=12345; While this isn't a whole lot more efficient than just pulling everything into the application and doing it that way, it will work out a bit better if you need to do this for multiple member IDs. This is the closest to what I was looking for. Thanks to all for the suggestions. Regards, Gilles - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Submitting patches?
On 4/23/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: (drh makes a mental note to improve the formatting of the contributed code section. The original table format seems a bit cluttered...) As long as you may be making a change, may I request a feature if it's not too much trouble? It would nice to be able to revise the description and/or file without resetting the date and counter (or at least add a new date onto the old). I like seeing the cumulative total but I'd like to do some minor fixes in the description. If it's too much like turning it into a CMS though, it's not worth it. Thanks. Liam
Re: [sqlite] Fortran 95 Language Bindings
Arjen, Thank you for attentive maintenance and development on this valuable interface. On 4/23/07, Arjen Markus <[EMAIL PROTECTED]> wrote: Liam Healy wrote: > Arjen, > > I've taken another look at these bindings, and at my project. For a > variety > of reasons, most not related to the sqlite3 Fortran bindings, I have > decided > to proceed in a different direction. However, on closer examination > of the > Fortran bindings and other language bindings to sqlite3 (C and Common > Lisp > via CLSQL), I notice that there is no equivalent of > sqlite3_get_table. This > would make my use of a Fortran interface considerably more difficult. At > first I thought there was no equivalent of sqlite3_exec but on closer > examination it appears that sqlite3_do performs that role. > Liam, I added the subroutine sqlite3_get_table to the Fortran bindings. You will need to use the CVS repository to get it (I have not released a new version of the flibs stuff yet), but it works as simple as you might expect - see csvdata.f90 in the tests/sqlite directory. Regards, Arjen - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
Marco Bambini wrote: Database is uniformly distributed, I created it ad hoc just for my test (sqlite 3.3.12): Marco, Another way to think of this is that if your database contained random numbers in the range 1-100 for both a and b, then an index on either of those values would allow sqlite to ignore all but the requested value, or 99% of the entries. It would only have to examine 1% of the records and would run in perhaps 2% of the time of a full table scan. If your data had even more distinct values, things would be even faster. Ultimately, if each data value was unique, then one index lookup would find the matching record, and the lookup time would only be about 2/300,000 or 0.0007% of the time for a full table scan. Indexes are not a magical cure all, they only speed up lookups if you enough different values to let them to reduce the search space to a small enough portion of the entire database to pay for their overhead. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
Marco Bambini wrote: I know that I can use the ANALYZE command or that I can index both columns. I was making some tests and I found that with one index the query is slower that without any index, so I just trying to understand the reason... I do not want to run it faster, I already know that it is possible. Database is uniformly distributed, I created it ad hoc just for my test (sqlite 3.3.12): CREATE TABLE table1 (a INTEGER, b INTEGER) 150,000 rows are inserted with: INSERT INTO table1 (a,b) VALUES (5,10) 200 rows are inserted with: INSERT INTO table1 (a,b) VALUES (5,11) 150,000 rows are inserted with: INSERT INTO table1 (a,b) VALUES (4,11) And the query was: SELECT * FROM table1 WHERE a=5 AND b=11; New benchmarks: WITHOUT INDEX: 0.281 secs WITH TWO INDEXes: 0.463 secs WITH TWO INDEXes and the ANALYZE command: 0.480 secs INDEXes are: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); Marco, You have a kind of pathological case here. Your data is not uniformly distributed. You have only three kinds of records. 150,200 records match the index on a for a = 5, and 150,200 records match the index on b for b=11. So it doesn't matter which single index sqlite chooses, it will have to scan through 150,200 records comparing the un-indexed field's value. To do this it must extract the rowid from the index and then locate that row in the main table, and then compare the value of the un-indexed field. Scanning an index for 150,200 records and then looking up 150,200 records in the main table is simply more work than scanning the entire table of 300,000 records once. This case does not benefit from indexing, and in fact it is slowed down on both lookups and inserts. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recommended wrapper for Python?
At 21:14 23/04/2007 +0100, Martin Jenkins wrote: pysqlite implements Python's DBAPI and was integrated into Python. There is another wrapper, APSW, which is thinner and closer to SQLite's C API. Choose whichever you feel most comfortable with. If you choose pysqlite be aware that it will start transactions behind your back and can produce the wrong error message if you start your own transactions. APSW works with Python 2.5, is usually a bit faster than pysqlite and is more frequently updated - it's not tied to the DBAPI. Thanks for the clarification. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
I know that I can use the ANALYZE command or that I can index both columns. I was making some tests and I found that with one index the query is slower that without any index, so I just trying to understand the reason... I do not want to run it faster, I already know that it is possible. Database is uniformly distributed, I created it ad hoc just for my test (sqlite 3.3.12): CREATE TABLE table1 (a INTEGER, b INTEGER) 150,000 rows are inserted with: INSERT INTO table1 (a,b) VALUES (5,10) 200 rows are inserted with: INSERT INTO table1 (a,b) VALUES (5,11) 150,000 rows are inserted with: INSERT INTO table1 (a,b) VALUES (4,11) And the query was: SELECT * FROM table1 WHERE a=5 AND b=11; New benchmarks: WITHOUT INDEX: 0.281 secs WITH TWO INDEXes: 0.463 secs WITH TWO INDEXes and the ANALYZE command: 0.480 secs INDEXes are: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); --- Marco Bambini On Apr 23, 2007, at 9:36 PM, [EMAIL PROTECTED] wrote: Marco Bambini <[EMAIL PROTECTED]> wrote: Yes, I know that it is faster ... I just wonder why with one index the query is slower that without any index... Probably because most of the entries in your table match the term being indexed. In your case, this likely means that a large fraction of the table entries have a=5. When searching from an index, SQLite first finds the index entry, then has to do a binary search for the table entry. The table entry lookup is O(logN) where N is the number of entries in the table. If the number of rows in the result set is proportional to N, then the total runtime is O(NlogN). On the other hand, the total runtime of a full table scan (which is what happens if you omit the index) is O(N). N -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] TRANSACTION
Thanks for the input and confirming my analysis. I am implementing a
remote procedure call capability and keep a library of SQL transactions
to be executed by a remote client.
Dennis Cote wrote:
John Stanton wrote:
I am not sure how to proceed with handling multiple SQL statements.
Perhaps someone has some experiences they would be kind enough to share.
I want to store multi-statement SQL to implement an entire transaction
in the form -
BEGIN
statement
statement
...
COMMIT
I can see that sqlite3_prepare has the capability of stepping through
a multi statement string but it looks like each statement becomes a
seperate vdbe object so if I had five statements I would have five
sqlite3_stmt ptrs and would need to step through them in sequence.
Does anyone know if I see it correctly? Is there some way I have not
seen to compile them all into one vdbe object?
John,
You see things correctly. You can't put multiple statements in one VDBE
object (unless you use a trigger).
But I don't think you need to store multiple prepared statements unless
you want to do so for speed.
Simply store the string that contains all the statements, and scan
through them using sqlite3_prpeare in a loop. You can include the begin
and commit statements as well. You are done after executing the last
statement, for which sqlite3_prepare will return a null tail pointer.
You will only ever have one statement prepared to execute at any point.
char* sql_block =
sqlite3_stmt* s;
char* sql= sql_block;
do {
sqlite3_prepare_v2(db, sql, -1, , );
sqlite3_finalize(s);
} while (sql != NULL);
A trigger block can contain multiple SQL statements, so you might be
able to use a trigger to do what you want if you execute a trigger in a
transaction.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Recommended wrapper for Python?
Gilles Ganault wrote: At 14:40 23/04/2007 +0200, Stef Mientki wrote: I've no experience whatsover, but if I see the list, the top one is the best choice, because it'll be integrated in the standard Python. Thanks. I didn't know SQLite was part of Python 2.5. pysqlite implements Python's DBAPI and was integrated into Python. There is another wrapper, APSW, which is thinner and closer to SQLite's C API. Choose whichever you feel most comfortable with. If you choose pysqlite be aware that it will start transactions behind your back and can produce the wrong error message if you start your own transactions. APSW works with Python 2.5, is usually a bit faster than pysqlite and is more frequently updated - it's not tied to the DBAPI. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: TRANSACTION
Thanks Igor, much obliged. That fits my application quite elegantly. Igor Tandetnik wrote: John Stanton <[EMAIL PROTECTED]> wrote: I want to store multi-statement SQL to implement an entire transaction in the form - BEGIN statement statement ... COMMIT I can see that sqlite3_prepare has the capability of stepping through a multi statement string but it looks like each statement becomes a seperate vdbe object so if I had five statements I would have five sqlite3_stmt ptrs and would need to step through them in sequence. Does anyone know if I see it correctly? Yes you do. Just run an outer loop until sqlite3_prepare says there are no more queries left. Is there some way I have not seen to compile them all into one vdbe object? No. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
Marco Bambini <[EMAIL PROTECTED]> wrote: > Yes, I know that it is faster ... I just wonder why with one index > the query is slower that without any index... Probably because most of the entries in your table match the term being indexed. In your case, this likely means that a large fraction of the table entries have a=5. When searching from an index, SQLite first finds the index entry, then has to do a binary search for the table entry. The table entry lookup is O(logN) where N is the number of entries in the table. If the number of rows in the result set is proportional to N, then the total runtime is O(NlogN). On the other hand, the total runtime of a full table scan (which is what happens if you omit the index) is O(N). N - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
Marco Bambini wrote: As a performance test I created a db with 300,000 records, table is: CREATE TABLE table1 (a INTEGER, b INTEGER) a query like: SELECT * FROM table1 WHERE a=5 AND b=11; takes 0.281 secs. if I add two indexes: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); the same query is about two times slower, it takes 0.463 secs. (I know that only one index is used by the query). I repeated the test several times and results are confirmed... Anyone have an explanation? Marco, I suspect that sqlite is arbitrarily choosing the wrong index for your data. Can you retry the test after running an ANALYZE command? Are the a and b values in your sample code uniformly distributed? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] TRANSACTION
John Stanton wrote:
I am not sure how to proceed with handling multiple SQL statements.
Perhaps someone has some experiences they would be kind enough to share.
I want to store multi-statement SQL to implement an entire transaction
in the form -
BEGIN
statement
statement
...
COMMIT
I can see that sqlite3_prepare has the capability of stepping through
a multi statement string but it looks like each statement becomes a
seperate vdbe object so if I had five statements I would have five
sqlite3_stmt ptrs and would need to step through them in sequence.
Does anyone know if I see it correctly? Is there some way I have not
seen to compile them all into one vdbe object?
John,
You see things correctly. You can't put multiple statements in one VDBE
object (unless you use a trigger).
But I don't think you need to store multiple prepared statements unless
you want to do so for speed.
Simply store the string that contains all the statements, and scan
through them using sqlite3_prpeare in a loop. You can include the begin
and commit statements as well. You are done after executing the last
statement, for which sqlite3_prepare will return a null tail pointer.
You will only ever have one statement prepared to execute at any point.
char* sql_block =
sqlite3_stmt* s;
char* sql= sql_block;
do {
sqlite3_prepare_v2(db, sql, -1, , );
sqlite3_finalize(s);
} while (sql != NULL);
A trigger block can contain multiple SQL statements, so you might be
able to use a trigger to do what you want if you execute a trigger in a
transaction.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: TRANSACTION
John Stanton <[EMAIL PROTECTED]> wrote: I want to store multi-statement SQL to implement an entire transaction in the form - BEGIN statement statement ... COMMIT I can see that sqlite3_prepare has the capability of stepping through a multi statement string but it looks like each statement becomes a seperate vdbe object so if I had five statements I would have five sqlite3_stmt ptrs and would need to step through them in sequence. Does anyone know if I see it correctly? Yes you do. Just run an outer loop until sqlite3_prepare says there are no more queries left. Is there some way I have not seen to compile them all into one vdbe object? No. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
Yes, I know that it is faster ... I just wonder why with one index the query is slower that without any index... --- Marco Bambini On Apr 23, 2007, at 6:31 PM, P Kishor wrote: On 4/23/07, Marco Bambini <[EMAIL PROTECTED]> wrote: As a performance test I created a db with 300,000 records, table is: CREATE TABLE table1 (a INTEGER, b INTEGER) a query like: SELECT * FROM table1 WHERE a=5 AND b=11; takes 0.281 secs. if I add two indexes: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); the same query is about two times slower, it takes 0.463 secs. (I know that only one index is used by the query). Try CREATE INDEX index_ab ON table1 (a, b); and test. I repeated the test several times and results are confirmed... Anyone have an explanation? --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recommended wrapper for Python?
At 14:40 23/04/2007 +0200, Stef Mientki wrote: I've no experience whatsover, but if I see the list, the top one is the best choice, because it'll be integrated in the standard Python. Thanks. I didn't know SQLite was part of Python 2.5. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] TRANSACTION
I am not sure how to proceed with handling multiple SQL statements. Perhaps someone has some experiences they would be kind enough to share. I want to store multi-statement SQL to implement an entire transaction in the form - BEGIN statement statement ... COMMIT I can see that sqlite3_prepare has the capability of stepping through a multi statement string but it looks like each statement becomes a seperate vdbe object so if I had five statements I would have five sqlite3_stmt ptrs and would need to step through them in sequence. Does anyone know if I see it correctly? Is there some way I have not seen to compile them all into one vdbe object? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Submitting patches?
Stephen Oberholtzer wrote: What's the best way to submit patches for SQLite? I looked around on the website and didn't find anything relevant; Google wasn't much help because all I got were pages for *other* projects that used SQLite to maintain their patch databases. Stephen, See the Contributed Code section here http://www.sqlite.org/copyright.html HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Submitting patches?
"Stephen Oberholtzer" <[EMAIL PROTECTED]> wrote: > What's the best way to submit patches for SQLite? I looked around on > the website and didn't find anything relevant; Google wasn't much help > because all I got were pages for *other* projects that used SQLite to > maintain their patch databases. > For bug fixes you can attach patches to a ticket. Write a ticket at http://www.sqlite.org/cvstrac/tktnew For an enhancement, you first have to convince me that the patch (1) does not break anything and (2) is useful enough to justify whatever effort will be required on my part to maintain the change for decades into the future. This is no small hurdle. Assuming you get across the acceptance gate, you will also need to execute a public domain dedication of your changes. See http://www.sqlite.org/copyright.html If your patch is really an add-ons for SQLite, then you and skip the complications above and self-publish in the contributors section. See http://www.sqlite.org/contrib Write to me privately for a userid and password that will enable you to upload. (drh makes a mental note to improve the formatting of the contributed code section. The original table format seems a bit cluttered...) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQL query, finding out which row a result is in
Hi Roy, If your statement "X" is represented below by "select ... Order by ..." Then would the following give you what you're looking for?? create temp table Xtab as (select Order by ); select ROWID from xTab where MemberID=4567373; (Without some "order by" clause, by the way, the rowid won't make sense -- don't rely on any default ordering) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Submitting patches?
What's the best way to submit patches for SQLite? I looked around on the website and didn't find anything relevant; Google wasn't much help because all I got were pages for *other* projects that used SQLite to maintain their patch databases. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
On 4/23/07, Marco Bambini <[EMAIL PROTECTED]> wrote: As a performance test I created a db with 300,000 records, table is: CREATE TABLE table1 (a INTEGER, b INTEGER) a query like: SELECT * FROM table1 WHERE a=5 AND b=11; takes 0.281 secs. if I add two indexes: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); the same query is about two times slower, it takes 0.463 secs. (I know that only one index is used by the query). Try CREATE INDEX index_ab ON table1 (a, b); and test. I repeated the test several times and results are confirmed... Anyone have an explanation? --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query, finding out which row a result is in
You don't have to read into a memory array. How about just running through your selection with an sqlite3_step and counting the rows? Gilles Roy wrote: On Sun, Apr 22, 2007 at 05:33:43PM -0500, P Kishor wrote: On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible. The arbitrary statement can order the results any way it wants. what do you mean by "which row"? Do you want to know the position of the required row within your result set, sort of like i = 0 foreach row if currentrow's memberid == 4567373 print i get out of the loop else i++ That is what I want to do. I want to know where the memberid is in the list (imagine the list was a waiting list or something). Is there not a way to just get the row number back? Is seems inefficient to have to allocate all of the memory to hold all of the results and then iterate through them. Thanks, Gilles - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] An explanation?
As a performance test I created a db with 300,000 records, table is: CREATE TABLE table1 (a INTEGER, b INTEGER) a query like: SELECT * FROM table1 WHERE a=5 AND b=11; takes 0.281 secs. if I add two indexes: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); the same query is about two times slower, it takes 0.463 secs. (I know that only one index is used by the query). I repeated the test several times and results are confirmed... Anyone have an explanation? --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] INSTEAD OF Trigger Question
Thank you! It work's fine.. Sylko -Ursprüngliche Nachricht- Von: Stephen Oberholtzer [mailto:[EMAIL PROTECTED] Gesendet: Montag, 23. April 2007 15:42 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] INSTEAD OF Trigger Question On 4/23/07, Sylko Zschiedrich <[EMAIL PROTECTED]> wrote: > Hi all, > > i have a question to "instead of triggers" on views. > > Following schema: > h> > That's my current implementation. But with this I can't update the View > to 'null' values because the coalesce statement will return the > old.values. > > How can I handle that? > Can I "detect" the columns to update? Actually, you don't need to. the OLD and NEW pseudotables don't contain just the data affected by the update; it includes the *complete* row before the update (in OLD) and after the update (in NEW). I have attached a sqlite3 script demonstrating this; just run it with sqlite3 < sqlite-view-update.sql to see the results. A final warning: there is a gross inefficiency in the way updates on views are handled. If your view is big -- i.e. "select count(*) from myview" reports more than about 100K rows -- your update is going to take inexplicably long. I'm pondering a patch for that. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSTEAD OF Trigger Question
On 4/23/07, Sylko Zschiedrich <[EMAIL PROTECTED]> wrote: Hi all, i have a question to "instead of triggers" on views. Following schema: h> That's my current implementation. But with this I can't update the View to 'null' values because the coalesce statement will return the old.values. How can I handle that? Can I "detect" the columns to update? Actually, you don't need to. the OLD and NEW pseudotables don't contain just the data affected by the update; it includes the *complete* row before the update (in OLD) and after the update (in NEW). I have attached a sqlite3 script demonstrating this; just run it with sqlite3 < sqlite-view-update.sql to see the results. A final warning: there is a gross inefficiency in the way updates on views are handled. If your view is big -- i.e. "select count(*) from myview" reports more than about 100K rows -- your update is going to take inexplicably long. I'm pondering a patch for that. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE .headers on .mode columns -- create a table create table footbl(key INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, num INTEGER NOT NULL, str TEXT NOT NULL, float REAL NOT NULL ); create table footbl_history( oldkey int, oldnum int, oldstr text, oldfloat real, newkey int, newnum int, newstr text, newfloat real ); -- create a seemingly useless view create view foo as select key, num, str, float from footbl; create trigger foo_update instead of update on foo begin insert into footbl_history values ( old.key, old.num, old.str, old.float, new.key, new.num, new.str, new.float); update footbl set key=new.key, num=new.num, str=new.str, float=new.float where key=old.key; end; insert into footbl (num,str,float) values (1, 'one', 1.0); insert into footbl (num,str,float) values (2, 'three', 2.0); insert into footbl (num,str,float) values (3, 'two', 3.0); -- oops! Oh wait! update foo set str='two' where num=2; update foo set str='three' where num=3; .headers off select 'View:'; .headers on select * from foo; .headers off select ''; select 'History:'; .headers on select * from footbl_history; - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query, finding out which row a result is in
On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible. The arbitrary statement can order the results any way it wants. Let's say your resultset consists of 3 columns: memberid, lastname, firstname. Then you can do this: create temp table _results (rownumber int AUTOINCREMENT, memberid int, lastname text, firstname text); insert into _results (memberid, lastname, firstname) select memberid, lastname, firstname from MainTable where whatever; select * from _results where memberid=12345; While this isn't a whole lot more efficient than just pulling everything into the application and doing it that way, it will work out a bit better if you need to do this for multiple member IDs. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recommended wrapper for Python?
Gilles Ganault wrote: Hello I browsed through the archives at Gname, but most threads regarding wrappers for Python date back to 2005. There are several wrappers listed in the wiki (http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers), so I'd like some feedback about which you would recommend to use SQLite from Python (2.4). I've no experience whatsover, but if I see the list, the top one is the best choice, because it'll be integrated in the standard Python. cheers, Stef Thank you GG. - To unsubscribe, send email to [EMAIL PROTECTED] - Kamer van Koophandel - handelsregister 41055629 / Netherlands Chamber of Commerce - trade register 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Recommended wrapper for Python?
Hello I browsed through the archives at Gname, but most threads regarding wrappers for Python date back to 2005. There are several wrappers listed in the wiki (http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers), so I'd like some feedback about which you would recommend to use SQLite from Python (2.4). Thank you GG. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query, finding out which row a result is in
> You could do this: > > SELECT COUNT(*) from X where memberid < 4567373 That assumes that you are sorting by memberid, of course... Hugh - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query, finding out which row a result is in
> That is what I want to do. I want to know where the memberid is in > the list (imagine the list was a waiting list or something). Is there > not a way to just get the row number back? Is seems inefficient to > have to allocate all of the memory to hold all of the results and > then iterate through them. You could do this: SELECT COUNT(*) from X where memberid < 4567373 Hugh - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] INSTEAD OF Trigger Question
Hi all, i have a question to "instead of triggers" on views. Following schema: -- View -- CREATE VIEW DepartmentHasApp as select UID_Application as UID_Application, UID_Org as UID_Department, XTouchedas XTouched, ... from BaseTreeHasApp where exists (select 1 from basetree y where y.uid_org = BaseTreeHasApp.UID_Org and y.uid_orgroot = 'VDepartment'); -- Trigger - CREATE TRIGGER DepartmentHasApp_Update Instead of Update on DepartmentHasApp begin update BaseTreeHasApp set UID_Application = coalesce(new.UID_Application, old.UID_Application), UID_Org = coalesce(new.UID_Department, old.UID_Department), XTouched = coalesce(new.XTouched, old.XTouched), ... where UID_Application = coalesce(new.UID_Application, old.UID_Application) and UID_Org = coalesce(new.UID_Department, old.UID_Department); end; -- That's my current implementation. But with this I can't update the View to 'null' values because the coalesce statement will return the old.values. How can I handle that? Can I "detect" the columns to update? Thanks Sylko - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite with hugetlbpage on linux
Hi All, Could somebody please answer my questions? - Does sqlite work with hugetlbpage (http://lxr.linux.no/source/Documentation/vm/hugetlbpage.txt) feature on linux? - If not, is anybody working on this? Thanks in advance, Prasanth. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Fortran 95 Language Bindings
Liam Healy wrote: Arjen, I've taken another look at these bindings, and at my project. For a variety of reasons, most not related to the sqlite3 Fortran bindings, I have decided to proceed in a different direction. However, on closer examination of the Fortran bindings and other language bindings to sqlite3 (C and Common Lisp via CLSQL), I notice that there is no equivalent of sqlite3_get_table. This would make my use of a Fortran interface considerably more difficult. At first I thought there was no equivalent of sqlite3_exec but on closer examination it appears that sqlite3_do performs that role. Liam, I added the subroutine sqlite3_get_table to the Fortran bindings. You will need to use the CVS repository to get it (I have not released a new version of the flibs stuff yet), but it works as simple as you might expect - see csvdata.f90 in the tests/sqlite directory. Regards, Arjen - To unsubscribe, send email to [EMAIL PROTECTED] -
