Re: [sqlite] How can a COMMIT attempt result in SQLITE_BUSY ?
Thank's Igor, uhh... I should have read this more carefully. I understand now that the commit may in fact need to wait for some other shared locks... sorry for the noise Marcus > Marcus Grimm wrote: >> I'm wondering how it can happen that after a successfull >> "BEGIN TRANSACTION;" statement and some inserts or update >> statements, that the final COMMIT failes with a busy error code ? >> >> Shouldn't that be impossible because there can be only one running >> transaction ? > > There may be multiple connections to the same database, each of which > could start a transaction. For more details, see > > http://sqlite.org/lockingv3.html > http://sqlite.org/lang_transaction.html > >> Everthing was working perfect until the user started a maintenance >> tool that opens a single connection to the same sqlite database >> while the server application was still running on the same hardware. >> Now it happends that the server was not able to get a >> COMMIT statement through > > You cannot commit any changes while another connection is reading from > the database. > > 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] SQL Query Question
Thanks Igor! Excellent advice and example. It would appear that where my brain freezed is in the use of ">=". For the life of me, I could not think of what would instruct the DB to go beyond the fetch date. Really appreciate it! Cheers! Rick -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Friday, July 03, 2009 9:19 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL Query Question Rick Ratchford wrote: > For example, say I have 15 Dates already extracted by a previous > query. > > I need to now get the 40 records that start at each of those 15 Dates. > > Assuming this is a SORTED dataset in ascending order by Date, I would > need to extract 40 records that start with the record at 2009-03-03, > then 40 records starting with the record at 2008-11-05, and so-forth. > > Can I do this in one SQL statement and have it produce one Recordset > of this result? Or would I have to run 15 different queries? You would be better off with 15 separate queries, like this: select * from myTable where Date >= :anchorDate order by Date asc limit 40; If you insist, you can get everything with a single query - but it will be slow as molasses in winter: select * from dateList dl join myTable t on ( select count(*) from myTable t1 where t1.Date between dl.Date and t.Date ) <= 40 order by dl.Date, t.Date; where dateList is the table (perhaps temporary) containing your 15 dates. 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 can a COMMIT attempt result in SQLITE_BUSY ?
Marcus Grimm wrote: > I'm wondering how it can happen that after a successfull > "BEGIN TRANSACTION;" statement and some inserts or update > statements, that the final COMMIT failes with a busy error code ? > > Shouldn't that be impossible because there can be only one running > transaction ? There may be multiple connections to the same database, each of which could start a transaction. For more details, see http://sqlite.org/lockingv3.html http://sqlite.org/lang_transaction.html > Everthing was working perfect until the user started a maintenance > tool that opens a single connection to the same sqlite database > while the server application was still running on the same hardware. > Now it happends that the server was not able to get a > COMMIT statement through You cannot commit any changes while another connection is reading from the database. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit Rows Retrieved
Rick Ratchford wrote: > Perhaps to solve the larger problem I have in a different post, I > have a question on retrieving records. > > How do you request a fixed number of records starting from a location > in the data based searched for, all in one SQL statement? > > "SELECT Date = '2009-01-01' ...plus the next x number of records" select * from myTable where Date >= '2009-01-01' order by Date limit :x; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Query Question
Rick Ratchford wrote: > For example, say I have 15 Dates already extracted by a previous > query. > > I need to now get the 40 records that start at each of those 15 Dates. > > Assuming this is a SORTED dataset in ascending order by Date, I would > need to extract 40 records that start with the record at 2009-03-03, > then 40 records starting with the record at 2008-11-05, and so-forth. > > Can I do this in one SQL statement and have it produce one Recordset > of this result? Or would I have to run 15 different queries? You would be better off with 15 separate queries, like this: select * from myTable where Date >= :anchorDate order by Date asc limit 40; If you insist, you can get everything with a single query - but it will be slow as molasses in winter: select * from dateList dl join myTable t on ( select count(*) from myTable t1 where t1.Date between dl.Date and t.Date ) <= 40 order by dl.Date, t.Date; where dateList is the table (perhaps temporary) containing your 15 dates. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Limit Rows Retrieved
Perhaps to solve the larger problem I have in a different post, I have a question on retrieving records. How do you request a fixed number of records starting from a location in the data based searched for, all in one SQL statement? "SELECT Date = '2009-01-01' ...plus the next x number of records" If I had such a statement, I believe it would go a long way to solve my bigger problem. Thank you in advance. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Query Question
The recordset/table that holds all the current data could be 1000, 3000, 10,000, ??? records. Total records fetched, however, would be just 15 x 40 = 600. You have 15 dates that mark the start of each 40 record segment. So plainly stated, with my 15 dates referred to as Date1 to Date15: 1. Search for Date1 in the database. 2. Starting from Date1, grab the next 39 records as well. Call this SET1. (only 1 column from each record, called POINTS, is needed). 3. Repeat 1 & 2 for Date2 to Date15, creating a SET2 to SET15. Therefore, I end up with 15 sets of 40 records. My display GRID has 15 rows and 40 columns. Each SET, 1 to 15, will occupy one row of this GRID, with POINTS inserted into the cells. 0 1 2 3 4 5 ... 39 (SET1) 0 1 2 3 4 5 ... 39 (SET2) 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 (SET15) If possible, and if the best way to do this, it would be fine if all 15 sets of 40 were in one recordset. I could then simply run it through a loop and at the end of each 40 records increment the row variable. So I don't if it is better to create 15 recordsets (40 records each), or one recordset (600 records), or whether it is possible to create the 600 record to begin with. I hope this example/question is clear. :-^ Cheers! Rick -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin Sent: Friday, July 03, 2009 7:19 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL Query Question On 4/07/2009 9:01 AM, Simon Slavin wrote: > On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote: > >> Suppose my 15 Dates are: >> >> 2009-03-03 >> 2008-11-05 >> 2008-07-10 >> ... >> ... >> 2007-07-23 >> >> >> Assuming this is a SORTED dataset in ascending order by Date, I would >> need to extract 40 records that start with the record at 2009-03-03, >> then 40 records starting with the record at 2008-11-05, and so-forth. >> >> Can I do this in one SQL statement and have it produce one Recordset >> of this result? Or would I have to run 15 different queries? > > If you have exactly 40 records for each day then you can do it with a > JOIN. Or put all the dates into a long string like > 'x2009-03-03x2008-11-05x ...' and use 'LIKE'. However, if you have > different numbers of entries for each day then I can't think of a way > to do it in one SELECT. > > Oh, hold on, you mean you have one record for each day, and you want > the records for those days and the 39 days after each of those days. > You could make an extremely long SELECT with lots of 'AND' clauses. > But I think you're going to have to do it in software. There's a strong presumption that there are missing days i.e. there is not a row for each possible day, so you can't just do "where date between x and x-plus-40-days" ... How many years of data? 10? That's max 3653 dates. Using Python, you'd run a query to fetch *all* rows in date order. Build a dict mapping date to row index. Then for each of your interesting dates, use the dict to get rowindex, and your up-to-40 required rows are result_set[rowindex:rowindex+40] ___ 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] SQL Query Question
On 4/07/2009 9:01 AM, Simon Slavin wrote: > On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote: > >> Suppose my 15 Dates are: >> >> 2009-03-03 >> 2008-11-05 >> 2008-07-10 >> ... >> ... >> 2007-07-23 >> >> >> Assuming this is a SORTED dataset in ascending order by Date, I >> would need >> to extract 40 records that start with the record at 2009-03-03, then >> 40 >> records starting with the record at 2008-11-05, and so-forth. >> >> Can I do this in one SQL statement and have it produce one Recordset >> of this >> result? Or would I have to run 15 different queries? > > If you have exactly 40 records for each day then you can do it with a > JOIN. Or put all the dates into a long string like > 'x2009-03-03x2008-11-05x ...' and use 'LIKE'. However, if you have > different numbers of entries for each day then I can't think of a way > to do it in one SELECT. > > Oh, hold on, you mean you have one record for each day, and you want > the records for those days and the 39 days after each of those days. > You could make an extremely long SELECT with lots of 'AND' clauses. > But I think you're going to have to do it in software. There's a strong presumption that there are missing days i.e. there is not a row for each possible day, so you can't just do "where date between x and x-plus-40-days" ... How many years of data? 10? That's max 3653 dates. Using Python, you'd run a query to fetch *all* rows in date order. Build a dict mapping date to row index. Then for each of your interesting dates, use the dict to get rowindex, and your up-to-40 required rows are result_set[rowindex:rowindex+40] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Query Question
On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote: > Suppose my 15 Dates are: > > 2009-03-03 > 2008-11-05 > 2008-07-10 > ... > ... > 2007-07-23 > > > Assuming this is a SORTED dataset in ascending order by Date, I > would need > to extract 40 records that start with the record at 2009-03-03, then > 40 > records starting with the record at 2008-11-05, and so-forth. > > Can I do this in one SQL statement and have it produce one Recordset > of this > result? Or would I have to run 15 different queries? If you have exactly 40 records for each day then you can do it with a JOIN. Or put all the dates into a long string like 'x2009-03-03x2008-11-05x ...' and use 'LIKE'. However, if you have different numbers of entries for each day then I can't think of a way to do it in one SELECT. Oh, hold on, you mean you have one record for each day, and you want the records for those days and the 39 days after each of those days. You could make an extremely long SELECT with lots of 'AND' clauses. But I think you're going to have to do it in software. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] referential integrity and INSERT OR REPLACE
nuts. that makes INSERT OR REPLACE worthless if you have tables dependent on one another. Is there any way to manually get a list of records for which there would be a conflict if a given record was inserted? > On Fri, 03 Jul 2009 11:29:14 -0700, James Gregurich > wrote: > > > > >based on my reading of the docs for INSERT OR REPLACE, it will delete > >rows for ANY constraint violation, not just one involving the primary > >key. Is that reading wrong? > > You are right, for UNIQUE constraint violations. > > Indeed it breaks referential integrity, because the new row > can contain a new value for the primary key, without > cascading deletes or updates concerning the old value. > > My conclusion: syntax shortcuts like INSERT OR REPLACE are > evil ;) > > >-James > -- > ( Kees Nuyt > ) > c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL Query Question
Can someone help me with this? Suppose you have a Table/Recordset that has these columns: Date (string) Color (string) Offset (long) I want to extract from this Table/Recordset 40 contiguous records from 15 locations within the dataset, each referenced by the Date. For example, say I have 15 Dates already extracted by a previous query. I need to now get the 40 records that start at each of those 15 Dates. Suppose my 15 Dates are: 2009-03-03 2008-11-05 2008-07-10 ... ... 2007-07-23 Assuming this is a SORTED dataset in ascending order by Date, I would need to extract 40 records that start with the record at 2009-03-03, then 40 records starting with the record at 2008-11-05, and so-forth. Can I do this in one SQL statement and have it produce one Recordset of this result? Or would I have to run 15 different queries? (PS: The columns I wish to return from those records are Date, Points1 only). Thank you! Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] referential integrity and INSERT OR REPLACE
On Fri, 03 Jul 2009 11:29:14 -0700, James Gregurich wrote: > >based on my reading of the docs for INSERT OR REPLACE, it will delete >rows for ANY constraint violation, not just one involving the primary >key. Is that reading wrong? You are right, for UNIQUE constraint violations. Indeed it breaks referential integrity, because the new row can contain a new value for the primary key, without cascading deletes or updates concerning the old value. My conclusion: syntax shortcuts like INSERT OR REPLACE are evil ;) >-James -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] referential integrity and INSERT OR REPLACE
I read on another posting in the archives that it does not. However, I haven't tried it myself. -James > Simon Slavin > Fri, 03 Jul 2009 09:44:22 -0700 > > On 3 Jul 2009, at 3:28am, James Gregurich wrote: > > > How do I maintain referential integrity on a INSERT OR REPLACE given > > it does not call the delete trigger on the offending rows? > > If SQLite decides that it's going to do a REPLACE rather than an > INSERT, does it call the triggers for UPDATE ? I think that would be > a good way for it to work. > > Simon. > ___ > 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] referential integrity and INSERT OR REPLACE
based on my reading of the docs for INSERT OR REPLACE, it will delete rows for ANY constraint violation, not just one involving the primary key. Is that reading wrong? -James > On Thu, 02 Jul 2009 19:28:17 -0700, James Gregurich > wrote: > > > > >question: > > > >How do I maintain referential integrity on a INSERT OR REPLACE given > >it does not call the delete trigger on the offending rows? > > Please correct me if I'm wrong, but considering the two > cases INSERT OR REPLACE handles for the referenced table: > > 1.there was no row with that primary key (PK) > the INSERT part of the statement is used, > any AFTER INSERT trigger is executed > > 2.there already was a row with that PK > the REPLACE part of the statement is used, so > DELETE, then INSERT. > After that, there still is a row with that PK. > There is no reason to trigger cascading deletes > in referring tables, or to forbid the deletion ... > > And considering INSERT OR REPLACE of rows in the referring > table (the one with the foreign key), only the INSERT > trigger has to fire to ensure the FK refers to an existing > PF in the referred table ... > > ... I would say the DELETE TRIGGER doesn't have to fire on > INSERT OR REPLACE. > -- > ( Kees Nuyt > ) > c[_] > ___ > 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] referential integrity and INSERT OR REPLACE
On 3 Jul 2009, at 3:28am, James Gregurich wrote: > How do I maintain referential integrity on a INSERT OR REPLACE given > it does not call the delete trigger on the offending rows? If SQLite decides that it's going to do a REPLACE rather than an INSERT, does it call the triggers for UPDATE ? I think that would be a good way for it to work. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How can a COMMIT attempt result in SQLITE_BUSY ?
Hello List, I'm wondering how it can happen that after a successfull "BEGIN TRANSACTION;" statement and some inserts or update statements, that the final COMMIT failes with a busy error code ? Shouldn't that be impossible because there can be only one running transaction ? I'm using shared cache and read_uncommitted = True inside a server application that opens multible connection to the same DB (similar to what can be found on the wiki pages under SampleCode). Everthing was working perfect until the user started a maintenance tool that opens a single connection to the same sqlite database while the server application was still running on the same hardware. Now it happends that the server was not able to get a COMMIT statement through (i.e. he timeout while trying this via sqlite3_step); with the result that he left a transaction open in one thread and was no longer able to do any writes into the sqlite database. After rebooting the server of course everthing was working back again. I'm able to reproduce this with a modified version of the SampleCode by just starting it two times: usually a COMMIT will timeout after a while. I know some workarounds for this (mainly to really make sure that a COMMIT can't be timed out anymore) but I'm curious if there is an explanation for this. Thank you Marcus Grimm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
On 3/07/2009 7:08 AM, Ed Hawke wrote: > > Out of interest, would I be able to use binding on the run-time defined > fields? > > If I wanted to use: > > select * from A > join B b1 on (A.Column3 = b1.ID) > join C c1 on (b1.Column1 = c1.ID) > join D d1 on (b1.Column2 = d1.ID) > > join B b2 on (A.Column4 = b2.ID) > join C c2 on (b2.Column1 = c2.ID) > join D d2 on (b2.Column2 = d2.ID); > where d2.ID = ? > > Would that work? If we had an explicit literal, e.g. "where d2.ID = 1234", would that work? Of course it would work. Here's the scoop: you can have a binding parameter anywhere you can have a literal. It's that simple. HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
Hello EH Do the "a2-style" (for want of a better way of defining them) names EH exist outside the SQL statement which defines them? Tables and columns have fixed names, which you define when you write your database schema, e.g. CREATE TABLE myTable (id INT, value TEXT); But you can give these names aliases in your SQL statements: e.g. SELECT t1.id AS id1, t1.value AS val1 FROM myTable AS t1 INNER JOIN myTable AS t2 ON t1.id = t2.id; These aliases only exist in the statements that define what they are aliasing. You can't reuse them in other statements without again specifying what they are aliasing. If I've understood you, then what you should read up on is aliases. I hope this helps. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
Ed Hawke wrote: > Fine. > > Do the "a2-style" (for want of a better way of defining them) names > exist outside the SQL statement which defines them? Why does it matter? > i.e. if I execute the example statement that you gave me, then later > execute a statement which references c2 will that work? No. But again, why is this significant? What additional insight does it give you? Anyway, this discussion has veered off-topic and, frankly, become rather boring. I think I'm done with it. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
Fine. Do the "a2-style" (for want of a better way of defining them) names exist outside the SQL statement which defines them? i.e. if I execute the example statement that you gave me, then later execute a statement which references c2 will that work? Will other statements in the same transaction be able to reference c2, or does the reference only persist in the statement in which c2 was defined? Ed Igor Tandetnik wrote: > Ed Hawke wrote: > >> All I meant was that in a database you have defined tables with >> defined column names. These are defined before any SQL statements are >> executed and therefore I would classify them as pre-defined. >> >> Contrast this with the "labels" applied to tables to create a separate >> reference to them within an SQL statement (e.g. a2 in your example). >> Before the SQL statement creating these references is executed then >> they will not be "recognised" by anything (i.e. any references to >> them in other statements will throw up an error). Therefore these are >> only defined when the SQL Statement that defines them is run, and >> therefore I would classify them as run-time defined. >> > > I don't see how this fine distinction is interesting, nor why it's worth > making. While "predefined" column names may exist, in some philosophical > sense, outside the context of a SQL statement, the only way for you to > use them or refer to them is by mentioning them in a SQL statement, at > which point all distinction between "predefined" and "dynamically > generated" names evaporates. > > 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] Nested Inner Join Help
Ed Hawke wrote: > All I meant was that in a database you have defined tables with > defined column names. These are defined before any SQL statements are > executed and therefore I would classify them as pre-defined. > > Contrast this with the "labels" applied to tables to create a separate > reference to them within an SQL statement (e.g. a2 in your example). > Before the SQL statement creating these references is executed then > they will not be "recognised" by anything (i.e. any references to > them in other statements will throw up an error). Therefore these are > only defined when the SQL Statement that defines them is run, and > therefore I would classify them as run-time defined. I don't see how this fine distinction is interesting, nor why it's worth making. While "predefined" column names may exist, in some philosophical sense, outside the context of a SQL statement, the only way for you to use them or refer to them is by mentioning them in a SQL statement, at which point all distinction between "predefined" and "dynamically generated" names evaporates. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
All I meant was that in a database you have defined tables with defined column names. These are defined before any SQL statements are executed and therefore I would classify them as pre-defined. Contrast this with the "labels" applied to tables to create a separate reference to them within an SQL statement (e.g. a2 in your example). Before the SQL statement creating these references is executed then they will not be "recognised" by anything (i.e. any references to them in other statements will throw up an error). Therefore these are only defined when the SQL Statement that defines them is run, and therefore I would classify them as run-time defined. I am aware as I said that these are probably not the correct SQL definitions, they were self-applied definitions to help make clear what I was referring to. I can only apologise that it made it more complicated. Ed Igor Tandetnik wrote: > Ed Hawke wrote: > >> By run-time defined fields I meant column names that SQL would not >> recognise until the query was executed >> > > I don't get the distinction. Could you give an example of column names > that SQL would somehow "recognize" before a query is executed? What do > you mean by "recognize" here, anyway? > > 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] referential integrity and INSERT OR REPLACE
On Thu, 02 Jul 2009 19:28:17 -0700, James Gregurich wrote: > >question: > >How do I maintain referential integrity on a INSERT OR REPLACE given >it does not call the delete trigger on the offending rows? Please correct me if I'm wrong, but considering the two cases INSERT OR REPLACE handles for the referenced table: 1.there was no row with that primary key (PK) the INSERT part of the statement is used, any AFTER INSERT trigger is executed 2.there already was a row with that PK the REPLACE part of the statement is used, so DELETE, then INSERT. After that, there still is a row with that PK. There is no reason to trigger cascading deletes in referring tables, or to forbid the deletion ... And considering INSERT OR REPLACE of rows in the referring table (the one with the foreign key), only the INSERT trigger has to fire to ensure the FK refers to an existing PF in the referred table ... ... I would say the DELETE TRIGGER doesn't have to fire on INSERT OR REPLACE. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to do 5,000,000 "select"s as fast as possible
On Fri, Jul 03, 2009 at 03:02:57PM +0800, knightfeng wrote: > We have to do 5,000,000 "select"s from a database with 4 record > (using C API). Are you sure you really want/need an SQL database for this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] referential integrity and INSERT OR REPLACE
question: How do I maintain referential integrity on a INSERT OR REPLACE given it does not call the delete trigger on the offending rows? thanks, james ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Value returned by sqlite3_column_bytes for strings
Thanks for the reply!
Simon Davies wrote:
> 2009/7/3 chandan :
>
>> Hi all,
>>Consider the following scenario,
>>1. A table contains a column of type "text".
>>2. The value of this column for the first row is say "linux".
>>
>> If we execute the SQL statement: "select name from some_tbl where id = ?"
>> using sqlite3_step() API, then what is the value returned by
>> sqlite3_column_bytes(). Does the count include the '\0' byte (count ==
>> 6). If it does not include the '\0' byte the count should be 5.
>>
>> I executed the following program:
>>
>> /**/
>> #include
>> #include
>> #include
>> #include
>> #include "sqlite3.h"
>>
>> const char *update_db = "update some_tbl set name = ? where id = ?";
>> const char *read_db = "select name from some_tbl where id = ?";
>>
>> int32_t main(int argc, char *argv[])
>> {
>>sqlite3_stmt *stmt;
>>sqlite3 *db;
>>int32_t num_bytes;
>>char buf[100];
>>int32_t ret;
>>
>>if (argc != 2) {
>>fprintf(stderr, "Usage: %s \n", argv[0]);
>>goto out1;
>>}
>>
>>ret = sqlite3_initialize();
>>if (ret != SQLITE_OK) {
>>fprintf(stderr, "Unable to initialize db.\n");
>>goto out1;
>>}
>>
>>ret = sqlite3_open(argv[1], &db);
>>if (ret != SQLITE_OK) {
>>fprintf(stderr, "Unable to open database.\n");
>>goto out2;
>>}
>>
>>stmt = NULL;
>>ret = sqlite3_prepare_v2(db, update_db, strlen(update_db) + 1,
>> &stmt, NULL);
>>if (ret != SQLITE_OK) {
>>fprintf(stderr, "sqlite3_prepare_v2: %s.\n",
>>sqlite3_errmsg(db));
>>goto out3;
>>}
>>
>>ret = sqlite3_bind_text(stmt, 1, "linux", strlen("linux") + 1,
>>SQLITE_TRANSIENT);
>>
>
> This will insert 6 bytes into the db - includes the trailing '\0'
>
>
>>if (ret != SQLITE_OK) {
>>fprintf(stderr, "sqlite3_bind_text: %s.\n",
>>sqlite3_errmsg(db));
>>goto out4;
>>}
>>
>>ret = sqlite3_bind_int64(stmt, 2, 1);
>>if (ret != SQLITE_OK) {
>>fprintf(stderr, "sqlite3_bind_int64: %s.\n",
>>sqlite3_errmsg(db));
>>goto out4;
>>}
>>
>>ret = sqlite3_step(stmt);
>>if (ret != SQLITE_DONE) {
>>fprintf(stderr, "sqlite3_step: %s.\n",
>>sqlite3_errmsg(db));
>>goto out4;
>>}
>>
>>ret = sqlite3_finalize(stmt);
>>if (ret != SQLITE_OK) {
>>fprintf(stderr, "sqlite3_finalize: %s.\n",
>>sqlite3_errmsg(db));
>>}
>>
>>stmt = NULL;
>>ret = sqlite3_prepare_v2(db, read_db, strlen(read_db) + 1, &stmt,
>> NULL);
>>if (ret != SQLITE_OK) {
>>fprintf(stderr, "sqlite3_prepare_v2: %s.\n",
>>sqlite3_errmsg(db));
>>goto out3;
>>}
>>
>>ret = sqlite3_bind_int64(stmt, 1, 1);
>>if (ret != SQLITE_OK) {
>>fprintf(stderr, "sqlite3_bind_int64: %s.\n",
>>sqlite3_errmsg(db));
>>goto out4;
>>}
>>
>>ret = sqlite3_step(stmt);
>>if (ret != SQLITE_ROW) {
>>fprintf(stderr, "sqlite3_step: %s.\n",
>>sqlite3_errmsg(db));
>>goto out4;
>>}
>>
>>num_bytes = sqlite3_column_bytes(stmt, 0);
>>printf("*** num_bytes = %d ***\n", num_bytes);
>>
>>memcpy(buf, sqlite3_column_text(stmt, 0), num_bytes);
>>printf("*** buf = %s ***\n", buf);
>>
>>exit(0);
>>
>> out4:
>>ret = sqlite3_finalize(stmt);
>> out3:
>>ret = sqlite3_close(db);
>> out2:
>>ret = sqlite3_shutdown();
>> out1:
>>exit(1);
>> }
>> /*/
>>
>>
>> The output shows that sqlite3_column_bytes() returns a count value that
>> includes the '\0'. Please correct me if I am arriving at the wrong
>> conclusion.
>>
>
> This is what I would expect given that you are inserting a string that
> includes the '\0'. Your initial statement that the db contains 'linux'
> is wrong; it contains 'linux\0'.
>
> Regards,
> Simon
> ___
> 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] Nested Inner Join Help
On Fri, 03 Jul 2009 00:53:12 +0100, Ed Hawke wrote: >Thank you again Igor. > >By run-time defined fields I meant column names that SQL would not >recognise until the query was executed, and therefore are only defined >when the statement is "run". I am aware that this is probably not the >correct terminology. You can only bind values to predetermined columns. The table names and column names in a sqlite_prepared statement are static and can't be replaced by placeholders. In other words, there are sqlite3_bind_*() functions for all types of values but nothing like sqlite3_bind_tablename() or sqlite3_bind_columnname(). http://www.sqlite.org/c3ref/funclist.html -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to import an empty value(NULL) into database from a file?
On Fri, 03 Jul 2009 11:12:26 +0800, Kermit Mei wrote: >Hello, how can I import an empty value into the database from a file. Using the command line tool to import a csv file, you can't import NULLs. A line like val1,,val3 would translate in val1, empty string, val3 in your table row. >The field may be int or string, if I write "NULL" , then I'll read a >string "NULL" from it. I hope that I can get an empty value (Eg, an >empty string whose size() is zero in Qt). >How can I do ? Note that NULL is not the same as empty string. The empty string case is easy, as show above. Now the NULL case. Assuming 'NULL' is never used as an actual value in the input, you could specify ..,NULL,... in the input file and update the table later, like this: UPDATE mytable SET col2 = NULL WHERE col2 = 'NULL'; >Thanks. > >Kermit -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Value returned by sqlite3_column_bytes for strings
2009/7/3 chandan :
> Hi all,
> Consider the following scenario,
> 1. A table contains a column of type "text".
> 2. The value of this column for the first row is say "linux".
>
> If we execute the SQL statement: "select name from some_tbl where id = ?"
> using sqlite3_step() API, then what is the value returned by
> sqlite3_column_bytes(). Does the count include the '\0' byte (count ==
> 6). If it does not include the '\0' byte the count should be 5.
>
> I executed the following program:
>
> /**/
> #include
> #include
> #include
> #include
> #include "sqlite3.h"
>
> const char *update_db = "update some_tbl set name = ? where id = ?";
> const char *read_db = "select name from some_tbl where id = ?";
>
> int32_t main(int argc, char *argv[])
> {
> sqlite3_stmt *stmt;
> sqlite3 *db;
> int32_t num_bytes;
> char buf[100];
> int32_t ret;
>
> if (argc != 2) {
> fprintf(stderr, "Usage: %s \n", argv[0]);
> goto out1;
> }
>
> ret = sqlite3_initialize();
> if (ret != SQLITE_OK) {
> fprintf(stderr, "Unable to initialize db.\n");
> goto out1;
> }
>
> ret = sqlite3_open(argv[1], &db);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "Unable to open database.\n");
> goto out2;
> }
>
> stmt = NULL;
> ret = sqlite3_prepare_v2(db, update_db, strlen(update_db) + 1,
> &stmt, NULL);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_prepare_v2: %s.\n",
> sqlite3_errmsg(db));
> goto out3;
> }
>
> ret = sqlite3_bind_text(stmt, 1, "linux", strlen("linux") + 1,
> SQLITE_TRANSIENT);
This will insert 6 bytes into the db - includes the trailing '\0'
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_text: %s.\n",
> sqlite3_errmsg(db));
> goto out4;
> }
>
> ret = sqlite3_bind_int64(stmt, 2, 1);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_int64: %s.\n",
> sqlite3_errmsg(db));
> goto out4;
> }
>
> ret = sqlite3_step(stmt);
> if (ret != SQLITE_DONE) {
> fprintf(stderr, "sqlite3_step: %s.\n",
> sqlite3_errmsg(db));
> goto out4;
> }
>
> ret = sqlite3_finalize(stmt);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_finalize: %s.\n",
> sqlite3_errmsg(db));
> }
>
> stmt = NULL;
> ret = sqlite3_prepare_v2(db, read_db, strlen(read_db) + 1, &stmt,
> NULL);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_prepare_v2: %s.\n",
> sqlite3_errmsg(db));
> goto out3;
> }
>
> ret = sqlite3_bind_int64(stmt, 1, 1);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_int64: %s.\n",
> sqlite3_errmsg(db));
> goto out4;
> }
>
> ret = sqlite3_step(stmt);
> if (ret != SQLITE_ROW) {
> fprintf(stderr, "sqlite3_step: %s.\n",
> sqlite3_errmsg(db));
> goto out4;
> }
>
> num_bytes = sqlite3_column_bytes(stmt, 0);
> printf("*** num_bytes = %d ***\n", num_bytes);
>
> memcpy(buf, sqlite3_column_text(stmt, 0), num_bytes);
> printf("*** buf = %s ***\n", buf);
>
> exit(0);
>
> out4:
> ret = sqlite3_finalize(stmt);
> out3:
> ret = sqlite3_close(db);
> out2:
> ret = sqlite3_shutdown();
> out1:
> exit(1);
> }
> /*/
>
>
> The output shows that sqlite3_column_bytes() returns a count value that
> includes the '\0'. Please correct me if I am arriving at the wrong
> conclusion.
This is what I would expect given that you are inserting a string that
includes the '\0'. Your initial statement that the db contains 'linux'
is wrong; it contains 'linux\0'.
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to do 5,000,000 "select"s as fast as possible
Briefly...
sqlite_prepare_v2(db, "select geneName,exonStart,exonEnd from refGene where
chromo=? and txStart <=? and txEnd>=?" ... etc etc)
start loop of 500 records
Use bindings to assign the parameters.
step through it
Reset and clear bindings.
end loop
sqlite3_finalize();
See sqlite_bind_[](), sqlite_reset(), sqlite_clear_bindings() in manual
From: knightfeng
To: sqlite-users
Sent: Friday, 3 July, 2009 5:02:57 PM
Subject: [sqlite] How to do 5,000,000 "select"s as fast as possible
Dear all,
We have to do 5,000,000 "select"s from a database with 4 record
(using C API). We do it as follow:
1. "create table refGene (geneName vchar, geneID vchar, chromo vchar, strand
char(1), txStart number, txEnd number, cdsStart number, cdsEnd number, exonNum
number, exonStart vchar, exonEnd vchar)"
2. insert 4 records.
3. rc = sqlite3_exec(db, "create index indexwig on refGene (chromo, txStart,
txEnd)" , NULL , NULL, &zErrMsg);
4. repeat 5,000,000 {
sprintf(sqlCmd, "select geneName,exonStart,exonEnd from refGene where
chromo=='%s' and txStart <= %d and txEnd>=%d", one.chromo.c_str(),
one.start, one.end);
rc = sqlite3_prepare(db, sqlCmd, strlen(sqlCmd), &stmt, NULL);
rc = sqlite3_step(stmt);
while(rc == SQLITE_ROW)
{
..
rc = sqlite3_step(stmt);
}
rc = sqlite3_finalize(stmt);
}
The 5,000,000 "select"s take about 30 minutes in our machine (3Gb memory, 2 x
1.8G Hz CPU).
Are there some faster ways to use sqlite to do the 5,000,000 "select"s ?
Thanks
Zhixing
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to do 5,000,000 "select"s as fast as possible
Dear all,
We have to do 5,000,000 "select"s from a database with 4 record
(using C API). We do it as follow:
1. "create table refGene (geneName vchar, geneID vchar, chromo vchar, strand
char(1), txStart number, txEnd number, cdsStart number, cdsEnd number, exonNum
number, exonStart vchar, exonEnd vchar)"
2. insert 4 records.
3. rc = sqlite3_exec(db, "create index indexwig on refGene (chromo, txStart,
txEnd)" , NULL , NULL, &zErrMsg);
4. repeat 5,000,000 {
sprintf(sqlCmd, "select geneName,exonStart,exonEnd from refGene where
chromo=='%s' and txStart <= %d and txEnd>=%d", one.chromo.c_str(),
one.start, one.end);
rc = sqlite3_prepare(db, sqlCmd, strlen(sqlCmd), &stmt, NULL);
rc = sqlite3_step(stmt);
while(rc == SQLITE_ROW)
{
..
rc = sqlite3_step(stmt);
}
rc = sqlite3_finalize(stmt);
}
The 5,000,000 "select"s take about 30 minutes in our machine (3Gb memory, 2 x
1.8G Hz CPU).
Are there some faster ways to use sqlite to do the 5,000,000 "select"s ?
Thanks
Zhixing
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
