Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?
Hi List, I want to make this query faster, for this, i created indexes for Entity_Id and grpuid and also used a pragmas : PRAGMA journal_mode = MEMORY; PRAGMA synchronous = OFF; PRAGMA temp_store = MEMORY; PRAGMA count_changes = OFF; PRAGMA cache_size = 12000; I also do Begin Transaction and End Transaction, but still i havn't achieved the speed the way i like. My Questions : 1. Am i missing out on anything? 2. Entity_ID is a Primary Integer Key, so is there any need to create an Index for it,as per my understanding,Indexing increases the database size and also the INSERT gets slower Thanks in Advance, Atul Igor Tandetnik wrote: Atul_Vaidya wrote: Hi, I have three tables, 1. Table Entity_xdata containing following fields Entity_id|Layer|grpuid| 2. Table, group_xdata_pipe containing following fields grpuid|LNV| 3. Table group_id_vs_reggappname containing following fields grpuid|reggappname| Now, I need to Fire a query to SQlite where in I get the list of all the distinct LNVs. Currently I achieve it by following two query commands as follows SELECT DISTINCT Entity_xData.grpuid from Entity_xdata INNER JOIN group_id_vs_regappname ON(Entity_xdata.grpuid = group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname = 'CPD1') I get the grpuids using this command and then i use the grpuids that i get from this query, as an input to my next query, something like this ... SELECT DISTINCT Line_Number_View FROM (SELECT grpuid,line_number_view FROM group_xdata_pipe WHERE grpuid = '%s' ORDER BY grpuid ASC)ORDER BY Line_Number_View ASC,query_result[x]) My question is Is it possible to combine both these querries into one ? SELECT DISTINCT Line_Number_View FROM group_xdata_pipe WHERE grpuid IN ( SELECT Entity_xData.grpuid FROM Entity_xdata INNER JOIN group_id_vs_regappname ON ( Entity_xdata.grpuid = group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname = 'CPD1') ) ORDER BY Line_Number_View ASC; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/how-to-Select-using-results-from-other-selects-in-a-single-SQlite-querry-statement---tp25149885p25271121.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] Making Update fast
Did you tried using the pragmas refer sqlite documentation for pragmas http://www.sqlite.org/pragma.html Atul souvik.datta wrote: Update tableName set Flag=1 where Filename=FileName; The updates taking huge amount of time. I tried to wrap these updates within transactions (50 updates within one transaction) but that is actually causing the a Read thread (the one which is trying to read the Metadata info ) to starve. If I am not using the transaction , the read thread response is much better but then the updates are running real slow. Can some suggest how I can improve this? Thanks and Regards, Souvik Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/Making-Update-fast-tp25269409p25271497.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] Making Update fast
souvik.datta wrote: Update tableName set Flag=1 where Filename=FileName; Check index presence on Filename column and still make updates in 1 large transaction. - Best Regards. Max Kosenko. -- View this message in context: http://www.nabble.com/Making-Update-fast-tp25269409p25273296.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] Problems encountered on upgrade from SQLite2 to -3
1. *re OID vs ROWID:* I don't understand your statement that the name of a result column is undefined. The documentation clearly states A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers: *ROWID*, *OID*, or *_ROWID_*. If I use a column name (as defined by my Create Table) in a select statement, Rexx variables are set (using RexxSQL here) in the form x.columnname.n -- except if the column name used is OID, in which case the Rexx variable set is x.ROWID.n instead of the expected x.OID.n No mention is made in the documentation of this quirk, which behavior is different from that of SQLite version 2. 2. *re integer vs string:* Version 3 differs from version 2 here. Version 2 was declared to be typeless and Select Where column = 1 behaved identically with Select Where column = '1'. Version 3 behavior is different in that the two previous examples produce different results, a compatibility issue. Version 3 documentation states that SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. Apparently this is not done in the case of Select Where column = '1' and a particular row has been set with a value of unquoted 1. (Columns were defined with no type.) The v3 document (in discussing conversions) uses the expression affinity is applied without defining what that means. Does it mean conversion to? -R. D. Richard Hipp wrote: On Sep 1, 2009, at 4:38 PM, Rod Dav4is wrote: Aren't these problems considered worth fixing ? I do not consider them to be problems. Rod Dav4is wrote: 1. *OID vs ROWID*: Specification of the OID field name (in SELECT) did not set Rexx variables X.OID.n, but instead set variables x.ROWID.n The name of a result column is undefined unless you use the AS clause. We try to be reasonably consistent, but there are no promises. There are especially no promises when moving form 2.8 to 3.6 2. *Quotes in SELECT*: Specification of Field='3' failed to find hits; Field=3 (i.e. without quotes) was required. This is a feature, not a bug. SQLite 3.x distinguishes between integers and strings and does not consider them equal to one another. You might have some rows where Field='3' and different rows where Field=3 and SQLite will distinguish between them. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ 538 ancestral collateral families, mostly 17°-19° century New England European roots. Total population: 136,000+ Annex: http://www.gencircles.com/users/dav4is/ email: dav...@yahoo.com A Democrat, a Republican and a giraffe walk into a bar. The bartender looks up from his want ads and says, What is this, a joke? -unknown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
Nicolas Williams wrote: On Wed, Sep 02, 2009 at 05:44:38PM -0400, Shaun Seckman (Firaxis) wrote: I'm just curious how difficult it would be to add support for booleans in SQLite. This would most likely involve adding a new type affinity as well as adding true and false keywords to the lexer. There's much more that could be done but I'm just looking for rudimentary support. ... I would just like to reduce some memory overhead as well as use those keywords instead of creating integer fields using 0 and 1. SQLite3 uses a variable length encoding for integers on disk, so you will gain no space on disk by having a native boolean type. And according to http://www.sqlite.org/fileformat.html#record_format, there's even a special optimization for the integers 0 and 1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3
2. *re integer vs string:* Version 3 differs from version 2 here. Version 2 was declared to be typeless and Select Where column = 1 behaved identically with Select Where column = '1'. Version 3 behavior is different in that the two previous examples produce different results, a compatibility issue. Version 3 also differs from version 2 in that all functions are named starting with 'sqlite3_' instead of 'sqlite_'. Doesn't this make another compatibility issue to complain about? Why didn't you mention it at all? AFAIK, SQLite 3 never tried to say that it is fully backward-compatible with SQLite 2. The v3 document (in discussing conversions) uses the expression affinity is applied without defining what that means. Does it mean conversion to? Read about affinities and how are they applied here: http://www.sqlite.org/datatype3.html. Pavel On Thu, Sep 3, 2009 at 6:31 AM, Rod Dav4isdav...@yahoo.com wrote: 1. *re OID vs ROWID:* I don't understand your statement that the name of a result column is undefined. The documentation clearly states A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers: *ROWID*, *OID*, or *_ROWID_*. If I use a column name (as defined by my Create Table) in a select statement, Rexx variables are set (using RexxSQL here) in the form x.columnname.n -- except if the column name used is OID, in which case the Rexx variable set is x.ROWID.n instead of the expected x.OID.n No mention is made in the documentation of this quirk, which behavior is different from that of SQLite version 2. 2. *re integer vs string:* Version 3 differs from version 2 here. Version 2 was declared to be typeless and Select Where column = 1 behaved identically with Select Where column = '1'. Version 3 behavior is different in that the two previous examples produce different results, a compatibility issue. Version 3 documentation states that SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. Apparently this is not done in the case of Select Where column = '1' and a particular row has been set with a value of unquoted 1. (Columns were defined with no type.) The v3 document (in discussing conversions) uses the expression affinity is applied without defining what that means. Does it mean conversion to? -R. D. Richard Hipp wrote: On Sep 1, 2009, at 4:38 PM, Rod Dav4is wrote: Aren't these problems considered worth fixing ? I do not consider them to be problems. Rod Dav4is wrote: 1. *OID vs ROWID*: Specification of the OID field name (in SELECT) did not set Rexx variables X.OID.n, but instead set variables x.ROWID.n The name of a result column is undefined unless you use the AS clause. We try to be reasonably consistent, but there are no promises. There are especially no promises when moving form 2.8 to 3.6 2. *Quotes in SELECT*: Specification of Field='3' failed to find hits; Field=3 (i.e. without quotes) was required. This is a feature, not a bug. SQLite 3.x distinguishes between integers and strings and does not consider them equal to one another. You might have some rows where Field='3' and different rows where Field=3 and SQLite will distinguish between them. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ 538 ancestral collateral families, mostly 17°-19° century New England European roots. Total population: 136,000+ Annex: http://www.gencircles.com/users/dav4is/ email: dav...@yahoo.com A Democrat, a Republican and a giraffe walk into a bar. The bartender looks up from his want ads and says, What is this, a joke? -unknown ___ 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] Problems encountered on upgrade from SQLite2 to -3
2. *Quotes in SELECT*: Specification of Field='3' failed to find hits; Field=3 (i.e. without quotes) was required. This is a feature, not a bug. SQLite 3.x distinguishes between integers and strings and does not consider them equal to one another. You might have some rows where Field='3' and different rows where Field=3 and SQLite will distinguish between them. BTW, I've re-read documentation about this, felt that something wrong here, tried it out and saw that your (Rod) complaint is wrong in this particular case: sqlite create table t (n integer, t text); sqlite insert into t values (1, '3'); sqlite select * from t; 1|3 sqlite select * from t where n = 1; 1|3 sqlite select * from t where n = '1'; 1|3 sqlite select * from t where t = '3'; 1|3 sqlite select * from t where t = 3; 1|3 sqlite select * from t where +n = '1'; sqlite Pavel On Tue, Sep 1, 2009 at 4:48 PM, D. Richard Hippd...@hwaci.com wrote: On Sep 1, 2009, at 4:38 PM, Rod Dav4is wrote: Aren't these problems considered worth fixing ? I do not consider them to be problems. Rod Dav4is wrote: 1. *OID vs ROWID*: Specification of the OID field name (in SELECT) did not set Rexx variables X.OID.n, but instead set variables x.ROWID.n The name of a result column is undefined unless you use the AS clause. We try to be reasonably consistent, but there are no promises. There are especially no promises when moving form 2.8 to 3.6 2. *Quotes in SELECT*: Specification of Field='3' failed to find hits; Field=3 (i.e. without quotes) was required. This is a feature, not a bug. SQLite 3.x distinguishes between integers and strings and does not consider them equal to one another. You might have some rows where Field='3' and different rows where Field=3 and SQLite will distinguish between them. D. Richard Hipp d...@hwaci.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] Problems encountered on upgrade from SQLite2 to -3
Pavel Ivanov wrote: 2. *Quotes in SELECT*: Specification of Field='3' failed to find hits; Field=3 (i.e. without quotes) was required. This is a feature, not a bug. SQLite 3.x distinguishes between integers and strings and does not consider them equal to one another. You might have some rows where Field='3' and different rows where Field=3 and SQLite will distinguish between them. BTW, I've re-read documentation about this, felt that something wrong here, tried it out and saw that your (Rod) complaint is wrong in this particular case: sqlite create table t (n integer, t text); sqlite insert into t values (1, '3'); sqlite select * from t; 1|3 sqlite select * from t where n = 1; 1|3 sqlite select * from t where n = '1'; 1|3 sqlite select * from t where t = '3'; 1|3 sqlite select * from t where t = 3; 1|3 sqlite select * from t where +n = '1'; sqlite Pavel I know what I saw: I had to change some of my selects, removing single quotes from literals, in order for selects to have the same result as on version 2. Perhaps the fact that my column definitions declared no typing has an effect here? -R. -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ 538 ancestral collateral families, mostly 17°-19° century New England European roots. Total population: 136,000+ Annex: http://www.gencircles.com/users/dav4is/ email: dav...@yahoo.com A Democrat, a Republican and a giraffe walk into a bar. The bartender looks up from his want ads and says, What is this, a joke? -unknown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3
Perhaps the fact that my column definitions declared no typing has an effect here? Yes, that means that your columns have no affinity, all data stored in it as you give and no conversions done during insertions and comparisons: sqlite create table t (n, t); sqlite insert into t values (1, '3'); sqlite select * from t where n = 1; 1|3 sqlite select * from t where n = '1'; sqlite Everything is well described at http://www.sqlite.org/datatype3.html and link there goes even from http://www.sqlite.org/version3.html which I presume you've already seen. Pavel On Thu, Sep 3, 2009 at 8:44 AM, Rod Dav4isdav...@yahoo.com wrote: Pavel Ivanov wrote: 2. *Quotes in SELECT*: Specification of Field='3' failed to find hits; Field=3 (i.e. without quotes) was required. This is a feature, not a bug. SQLite 3.x distinguishes between integers and strings and does not consider them equal to one another. You might have some rows where Field='3' and different rows where Field=3 and SQLite will distinguish between them. BTW, I've re-read documentation about this, felt that something wrong here, tried it out and saw that your (Rod) complaint is wrong in this particular case: sqlite create table t (n integer, t text); sqlite insert into t values (1, '3'); sqlite select * from t; 1|3 sqlite select * from t where n = 1; 1|3 sqlite select * from t where n = '1'; 1|3 sqlite select * from t where t = '3'; 1|3 sqlite select * from t where t = 3; 1|3 sqlite select * from t where +n = '1'; sqlite Pavel I know what I saw: I had to change some of my selects, removing single quotes from literals, in order for selects to have the same result as on version 2. Perhaps the fact that my column definitions declared no typing has an effect here? -R. -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ 538 ancestral collateral families, mostly 17°-19° century New England European roots. Total population: 136,000+ Annex: http://www.gencircles.com/users/dav4is/ email: dav...@yahoo.com A Democrat, a Republican and a giraffe walk into a bar. The bartender looks up from his want ads and says, What is this, a joke? -unknown ___ 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] Problems encountered on upgrade from SQLite2 to -3
Pavel Ivanov wrote: 2. *re integer vs string:* Version 3 differs from version 2 here. Version 2 was declared to be typeless and Select Where column = 1 behaved identically with Select Where column = '1'. Version 3 behavior is different in that the two previous examples produce different results, a compatibility issue. Version 3 also differs from version 2 in that all functions are named starting with 'sqlite3_' instead of 'sqlite_'. Doesn't this make another compatibility issue to complain about? Why didn't you mention it at all? AFAIK, SQLite 3 never tried to say that it is fully backward-compatible with SQLite 2. I only reported problems that I had actually encountered in migrating to version 3. I never had occasion to use the function names you mentioned. *re backward compatibility:* OTOH, it doesn't say that it is NOT backward compatible w/r/t the syntax and semantics of the language. As a user contemplating an upgrade, I would expect such differences to be prominently discussed. The v3 document (in discussing conversions) uses the expression affinity is applied without defining what that means. Does it mean conversion to? Read about affinities and how are they applied here: http://www.sqlite.org/datatype3.html. Yes, that is where I looked to find what applied affinity meant, but never found a crisp definition. If it means converted to, it should say that. Pavel On Thu, Sep 3, 2009 at 6:31 AM, Rod Dav4isdav...@yahoo.com wrote: 1. *re OID vs ROWID:* I don't understand your statement that the name of a result column is undefined. The documentation clearly states A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers: *ROWID*, *OID*, or *_ROWID_*. If I use a column name (as defined by my Create Table) in a select statement, Rexx variables are set (using RexxSQL here) in the form x.columnname.n -- except if the column name used is OID, in which case the Rexx variable set is x.ROWID.n instead of the expected x.OID.n No mention is made in the documentation of this quirk, which behavior is different from that of SQLite version 2. 2. *re integer vs string:* Version 3 differs from version 2 here. Version 2 was declared to be typeless and Select Where column = 1 behaved identically with Select Where column = '1'. Version 3 behavior is different in that the two previous examples produce different results, a compatibility issue. Version 3 documentation states that SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. Apparently this is not done in the case of Select Where column = '1' and a particular row has been set with a value of unquoted 1. (Columns were defined with no type.) The v3 document (in discussing conversions) uses the expression affinity is applied without defining what that means. Does it mean conversion to? -R. D. Richard Hipp wrote: On Sep 1, 2009, at 4:38 PM, Rod Dav4is wrote: Aren't these problems considered worth fixing ? I do not consider them to be problems. Rod Dav4is wrote: 1. *OID vs ROWID*: Specification of the OID field name (in SELECT) did not set Rexx variables X.OID.n, but instead set variables x.ROWID.n The name of a result column is undefined unless you use the AS clause. We try to be reasonably consistent, but there are no promises. There are especially no promises when moving form 2.8 to 3.6 2. *Quotes in SELECT*: Specification of Field='3' failed to find hits; Field=3 (i.e. without quotes) was required. This is a feature, not a bug. SQLite 3.x distinguishes between integers and strings and does not consider them equal to one another. You might have some rows where Field='3' and different rows where Field=3 and SQLite will distinguish between them. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ 538 ancestral collateral families, mostly 17°-19° century New England European roots. Total population: 136,000+ Annex: http://www.gencircles.com/users/dav4is/ email: dav...@yahoo.com A Democrat, a Republican and a giraffe walk into a bar. The bartender looks up from his want ads and says, What is this, a joke? -unknown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3
Pavel Ivanov wrote: Perhaps the fact that my column definitions declared no typing has an effect here? Yes, that means that your columns have no affinity, all data stored in it as you give and no conversions done during insertions and comparisons: sqlite create table t (n, t); sqlite insert into t values (1, '3'); sqlite select * from t where n = 1; 1|3 sqlite select * from t where n = '1'; sqlite Everything is well described at http://www.sqlite.org/datatype3.html and link there goes even from http://www.sqlite.org/version3.html which I presume you've already seen. Pavel Yes, I know how it works. But that seems to contradict the documentation. The first field of the record inserted should have a type of numeric, as types are associated with the data not with the column declaration. So the phrase Where n = '1' should fall into the first bullet case, i.e. * When two expressions are compared, if expression A has INTEGER or REAL or NUMERIC affinity and expression B does not, then NUMERIC affinity is applied to the value of expression B before the comparison takes place. (Reading conversion is applied for affinity is applied.) -R. -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ 538 ancestral collateral families, mostly 17°-19° century New England European roots. Total population: 136,000+ Annex: http://www.gencircles.com/users/dav4is/ email: dav...@yahoo.com A Democrat, a Republican and a giraffe walk into a bar. The bartender looks up from his want ads and says, What is this, a joke? -unknown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3
Yes, I know how it works. But that seems to contradict the documentation. The first field of the record inserted should have a type of numeric, as types are associated with the data not with the column declaration. So the phrase Where n = '1' should fall into the first bullet case Rod, type != affinity. While in my example value in the field n in the inserted record has type numeric it has no affinity (because column has no type declared) thus it doesn't fall into first bullet. Affinity is associated with columns, not with the data inserted. Thus in my earlier example (when column n had type integer) even though column n had integer affinity expression +n has no affinity (though integer type) and thus comparison +n = '1' is always false because no conversions applied. (Reading conversion is applied for affinity is applied.) These are not equivalent actions. When we are talking about conversion of text into number affinity is applied means that conversion is made if possible otherwise the value keeps its text type, but conversion is applied means that conversion is made in any case i.e. if text doesn't contain number it will be converted to 0. Pavel On Thu, Sep 3, 2009 at 9:33 AM, Rod Dav4isdav...@yahoo.com wrote: Pavel Ivanov wrote: Perhaps the fact that my column definitions declared no typing has an effect here? Yes, that means that your columns have no affinity, all data stored in it as you give and no conversions done during insertions and comparisons: sqlite create table t (n, t); sqlite insert into t values (1, '3'); sqlite select * from t where n = 1; 1|3 sqlite select * from t where n = '1'; sqlite Everything is well described at http://www.sqlite.org/datatype3.html and link there goes even from http://www.sqlite.org/version3.html which I presume you've already seen. Pavel Yes, I know how it works. But that seems to contradict the documentation. The first field of the record inserted should have a type of numeric, as types are associated with the data not with the column declaration. So the phrase Where n = '1' should fall into the first bullet case, i.e. * When two expressions are compared, if expression A has INTEGER or REAL or NUMERIC affinity and expression B does not, then NUMERIC affinity is applied to the value of expression B before the comparison takes place. (Reading conversion is applied for affinity is applied.) -R. -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ 538 ancestral collateral families, mostly 17°-19° century New England European roots. Total population: 136,000+ Annex: http://www.gencircles.com/users/dav4is/ email: dav...@yahoo.com A Democrat, a Republican and a giraffe walk into a bar. The bartender looks up from his want ads and says, What is this, a joke? -unknown ___ 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] Problems encountered on upgrade from SQLite2 to -3
Umm, At 05:16 03/09/2009, you wrote: ´¯¯¯ Thanks for reminding me: A thing's value is generally proportional to its cost. And the attitude of its support team figures in there, too. -R. Whether _you_ consider them problems or not, they were certainly problems for me, migrating a working application to version 3 and having it fall over in subtle ways because of these undocumented two vs three differences. They cost me several hours of unnecessary analysis time. You should bill DRH for this. Or, ask for your money back. Seriously, esp. since the product is still under warranty. `--- What a wonderful example of arrogant attitude! What a brilliant understanding you demonstrate of what open (and _free_) software means! DRH, other developpers and hundreds of contributors really should have frozen SQLite design from the day You Mighty wrote the first line of Your Mighty code, just to avoid You Mighty the horrible frustration of having to change Your Mighty code at all. And this of course until the sun goes nova: think of Your Mighty descendants! What have you contributed so far to allow yourself such superior position? Have you ever shown the faintest step of commitment that so many here constantly bring to SQLite, openly or silently? Are you the author of one definitive Guide for migrating SQLite v2x to v3x applications that will be available in the Wiki for anyone to benefit? Have you made any proposal to improve existing documentation or code? Please, make us the favor to switch to Oracle or any other utterly expensive RDBMS and go whine elsewhere for lack of support/reactivity (esp. Oracle). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3
*re applied affinity:* If that is what is meant, then the document should say it, instead of leaving it to the reader's imagination. Since column typing was superfluous in version2, it seems that the version3 adoption of typing, as defined, would perhaps be an upgrade compatibility issue, no? -R. Pavel Ivanov wrote in part: These are not equivalent actions. When we are talking about conversion of text into number affinity is applied means that conversion is made if possible otherwise the value keeps its text type, but conversion is applied means that conversion is made in any case i.e. if text doesn't contain number it will be converted to 0. Pavel -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ 538 ancestral collateral families, mostly 17°-19° century New England European roots. Total population: 136,000+ Annex: http://www.gencircles.com/users/dav4is/ email: dav...@yahoo.com A Democrat, a Republican and a giraffe walk into a bar. The bartender looks up from his want ads and says, What is this, a joke? -unknown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3
Whoa! All I did was report two problems that I encountered when upgrading from version2 to version3! I was told that my problems were not problems at all; In fact one of them was a feature! That sounds kinda arrogant to me. As far as freezing the language, I made no such statement or request. But I did imply that a migration document would have been useful. I certainly don't feel qualified to write such a document because I'm pretty sure that my puny application hardly represents an exhaustive test of SQLite version compatibly -- and software testing is something that I happen to know quite a bit about. *re documentation suggestions:* Well, in fact I have. I suggested that a definition of exactly what is meant by applied affinity is needed. -R. Jean-Christophe Deschamps wrote: Umm, At 05:16 03/09/2009, you wrote: ´¯¯¯ Thanks for reminding me: A thing's value is generally proportional to its cost. And the attitude of its support team figures in there, too. -R. Whether _you_ consider them problems or not, they were certainly problems for me, migrating a working application to version 3 and having it fall over in subtle ways because of these undocumented two vs three differences. They cost me several hours of unnecessary analysis time. You should bill DRH for this. Or, ask for your money back. Seriously, esp. since the product is still under warranty. `--- What a wonderful example of arrogant attitude! What a brilliant understanding you demonstrate of what open (and _free_) software means! DRH, other developpers and hundreds of contributors really should have frozen SQLite design from the day You Mighty wrote the first line of Your Mighty code, just to avoid You Mighty the horrible frustration of having to change Your Mighty code at all. And this of course until the sun goes nova: think of Your Mighty descendants! What have you contributed so far to allow yourself such superior position? Have you ever shown the faintest step of commitment that so many here constantly bring to SQLite, openly or silently? Are you the author of one definitive Guide for migrating SQLite v2x to v3x applications that will be available in the Wiki for anyone to benefit? Have you made any proposal to improve existing documentation or code? Please, make us the favor to switch to Oracle or any other utterly expensive RDBMS and go whine elsewhere for lack of support/reactivity (esp. Oracle). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ 538 ancestral collateral families, mostly 17°-19° century New England European roots. Total population: 136,000+ Annex: http://www.gencircles.com/users/dav4is/ email: dav...@yahoo.com A Democrat, a Republican and a giraffe walk into a bar. The bartender looks up from his want ads and says, What is this, a joke? -unknown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3
On Sep 3, 2009, at 10:43 AM, Rod Dav4is wrote: *re applied affinity:* If that is what is meant, then the document should say it, instead of leaving it to the reader's imagination. Since column typing was superfluous in version2, it seems that the version3 adoption of typing, as defined, would perhaps be an upgrade compatibility issue, no? I might be wrong, but I'm guessing you'll find the people here will help you more if you take the chip off of your shoulder and ask nicely. You see, the fact is that most people ported their applications from SQLite2 to SQLite3 back in 2004. A dare say that most current readers of this mailing list didn't discover SQLite until after SQLite3 was already well established, and hence have no memory of what SQLite2 did or how it was different from SQLite3. So porting from SQLite2 to SQLite3 is not a topic that is a high priority to people here. And hence, they tend to respond unsupportively when addressing a complaint by a user who is clearly miffed that SQLite3 does not work exactly the way SQLite2 used to work. I suggest a do-over. Rod, I suggest you re-register for this mailing list under a different name, then log on and send a request that is worded something like this: Hi! I'm porting an older application from SQLite2 to SQLite3 and am running into a couple of compatibility issues. [explain the two problems here.] Can somebody suggest ways of either (1) getting SQLite3 to work more like SQLite2 used to work, or (2) how I can change my code to work the way SQLite3 expects? Thanks! Note that the sample request in the previous paragraph does not contain an impatient claim that SQLite3 is broken and needs fixing. And in particular, it does not contain such a claim coming from someone who does not understand how SQLite3 works. I think if you try my do-over suggestion you will find the people here will be nice, friendly, and much, much more helpful. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3
At the risk of throwing gasoline on a fire... I didn't react badly to Rod's original post. Yes, it could have been worded more diplomatically, but why so thin-skinned about it? He reported some issues. The response was that they weren't issues. Can you see how that might be construed as unhelpful? As for: You see, the fact is that most people ported their applications from SQLite2 to SQLite3 back in 2004. while that may very well be true, it sure didn't help Rod, did it? What he asked for was a migration guide (actually, he just asked for some clarifying language in the documentation). Just because most people don't need a migration guide because they already ported doesn't mean that a user who hasn't ported doesn't need a migration guide. That's arguing from the specific to the general, one of the classic logical fallicies. - Original Message - From: D. Richard Hipp d...@hwaci.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Thursday, September 03, 2009 8:05 AM Subject: Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3 On Sep 3, 2009, at 10:43 AM, Rod Dav4is wrote: *re applied affinity:* If that is what is meant, then the document should say it, instead of leaving it to the reader's imagination. Since column typing was superfluous in version2, it seems that the version3 adoption of typing, as defined, would perhaps be an upgrade compatibility issue, no? I might be wrong, but I'm guessing you'll find the people here will help you more if you take the chip off of your shoulder and ask nicely. You see, the fact is that most people ported their applications from SQLite2 to SQLite3 back in 2004. A dare say that most current readers of this mailing list didn't discover SQLite until after SQLite3 was already well established, and hence have no memory of what SQLite2 did or how it was different from SQLite3. So porting from SQLite2 to SQLite3 is not a topic that is a high priority to people here. And hence, they tend to respond unsupportively when addressing a complaint by a user who is clearly miffed that SQLite3 does not work exactly the way SQLite2 used to work. I suggest a do-over. Rod, I suggest you re-register for this mailing list under a different name, then log on and send a request that is worded something like this: Hi! I'm porting an older application from SQLite2 to SQLite3 and am running into a couple of compatibility issues. [explain the two problems here.] Can somebody suggest ways of either (1) getting SQLite3 to work more like SQLite2 used to work, or (2) how I can change my code to work the way SQLite3 expects? Thanks! Note that the sample request in the previous paragraph does not contain an impatient claim that SQLite3 is broken and needs fixing. And in particular, it does not contain such a claim coming from someone who does not understand how SQLite3 works. I think if you try my do-over suggestion you will find the people here will be nice, friendly, and much, much more helpful. D. Richard Hipp d...@hwaci.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] Problems encountered on upgrade from SQLite2 to -3
On Thu, Sep 3, 2009 at 10:30 AM, Jim Showalterj...@jimandlisa.com wrote: At the risk of throwing gasoline on a fire... I didn't react badly to Rod's original post. The original post was fine. It was the third post that was inflammatory. Yes, it could have been worded more diplomatically, but why so thin-skinned about it? a! so it *could* have been worded more diplomatically, so it *should* have been worded more diplomatically. The subsequent dialog could have been -- Rod: blah blah Someone else: I take umbrage at that Rod: Sorry, if I came off as disgruntled. I really appreciate all this free software that usually works great. Was frustrated with the snafus I encountered. I hope someone can help me. Someone else: Oh, no problem. Happens to all of us. Let's see now... He reported some issues. The response was that they weren't issues. Can you see how that might be construed as unhelpful? Not at all. DRH's response was matter of fact and looking forward. Pavel responded with 4 very helpful posts. As for: You see, the fact is that most people ported their applications from SQLite2 to SQLite3 back in 2004. while that may very well be true, it sure didn't help Rod, did it? What he asked for was a migration guide (actually, he just asked for some clarifying language in the documentation). Just because most people don't need a migration guide because they already ported doesn't mean that a user who hasn't ported doesn't need a migration guide. That's arguing from the specific to the general, one of the classic logical fallicies. Very true. But this free and open software. If a migration guide to fit Rod's specifics doesn't exist, then Rod can ask questions and create one and put it back into public domain so others can benefit. I guess it all boils down to the attitude one displays. No one grudges anyone their moments of venting frustration as long as those displays come with also a wry smile and some self-deprecation. But, DRH already commented on that. - Original Message - From: D. Richard Hipp d...@hwaci.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Thursday, September 03, 2009 8:05 AM Subject: Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3 On Sep 3, 2009, at 10:43 AM, Rod Dav4is wrote: *re applied affinity:* If that is what is meant, then the document should say it, instead of leaving it to the reader's imagination. Since column typing was superfluous in version2, it seems that the version3 adoption of typing, as defined, would perhaps be an upgrade compatibility issue, no? I might be wrong, but I'm guessing you'll find the people here will help you more if you take the chip off of your shoulder and ask nicely. You see, the fact is that most people ported their applications from SQLite2 to SQLite3 back in 2004. A dare say that most current readers of this mailing list didn't discover SQLite until after SQLite3 was already well established, and hence have no memory of what SQLite2 did or how it was different from SQLite3. So porting from SQLite2 to SQLite3 is not a topic that is a high priority to people here. And hence, they tend to respond unsupportively when addressing a complaint by a user who is clearly miffed that SQLite3 does not work exactly the way SQLite2 used to work. I suggest a do-over. Rod, I suggest you re-register for this mailing list under a different name, then log on and send a request that is worded something like this: Hi! I'm porting an older application from SQLite2 to SQLite3 and am running into a couple of compatibility issues. [explain the two problems here.] Can somebody suggest ways of either (1) getting SQLite3 to work more like SQLite2 used to work, or (2) how I can change my code to work the way SQLite3 expects? Thanks! Note that the sample request in the previous paragraph does not contain an impatient claim that SQLite3 is broken and needs fixing. And in particular, it does not contain such a claim coming from someone who does not understand how SQLite3 works. I think if you try my do-over suggestion you will find the people here will be nice, friendly, and much, much more helpful. D. Richard Hipp d...@hwaci.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 -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are
[sqlite] Defining user groups in SQLite table
I have a database table that holds about 20,000 codes. Each code can be used by several different user groups. I could add a column to the database table for each user group to indicate which codes that particular group has access to. But, I have over 100 different groups! Can anyone suggest another way of doing this? So, group 1 has access to code1, code2, code3, code4, code5; group 2 has access to code1, code2, code3; group 3 has access to code 2, code3, code4. etc. Thanks in advance for any suggestions! -- View this message in context: http://www.nabble.com/Defining-user-groups-in-SQLite-table-tp25281126p25281126.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] Defining user groups in SQLite table
On Thu, Sep 03, 2009 at 10:57:13AM -0700, karenebs scratched on the wall: I have a database table that holds about 20,000 codes. Each code can be used by several different user groups. I could add a column to the database table for each user group to indicate which codes that particular group has access to. But, I have over 100 different groups! Can anyone suggest another way of doing this? So, group 1 has access to code1, code2, code3, code4, code5; group 2 has access to code1, code2, code3; group 3 has access to code 2, code3, code4. etc. Thanks in advance for any suggestions! This is a classic Many-to-Many relationship. You're basically putting people (codes) into groups. People can belong to more than one group, groups have more than one person. The standard way of doing this is to have a GROUPID in your Groups table and a CODEID in your Codes table. You then add a third table, known as a bridge or link table, that represents a membership. It has at least two columns: GROUPID and CODEID. Put a unique index across both columns and you're good to go (you may want a second index with the columns in the other order). To add a code to a group, just add a new membership with the correct ID values. To query, join your codes and groups across the bridge table. You can add other columns to the link table if you want, such as a starting date or ending date for the membership, a membership type, etc. Depending on your needs, you many need to search and cleanup codes with zero groups or groups with zero codes. You are likely to get all kinds of examples if you Google sql many to many or sql bridge table. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Defining user groups in SQLite table
table groups ( group_id, group_descri ) table access ( rela_group, rela_code) --- El jue 3-sep-09, karenebs ka...@echobasesoftware.com escribió: De: karenebs ka...@echobasesoftware.com Asunto: [sqlite] Defining user groups in SQLite table Para: sqlite-users@sqlite.org Fecha: jueves, 3 de septiembre de 2009, 2:57 pm I have a database table that holds about 20,000 codes. Each code can be used by several different user groups. I could add a column to the database table for each user group to indicate which codes that particular group has access to. But, I have over 100 different groups! Can anyone suggest another way of doing this? So, group 1 has access to code1, code2, code3, code4, code5; group 2 has access to code1, code2, code3; group 3 has access to code 2, code3, code4. etc. Thanks in advance for any suggestions! -- View this message in context: http://www.nabble.com/Defining-user-groups-in-SQLite-table-tp25281126p25281126.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 Yahoo! Cocina Encontra las mejores recetas con Yahoo! Cocina. http://ar.mujer.yahoo.com/cocina/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite to Access
Hi All - We have a new utility in our environment using SQLite and I was wondering if there was a way to dump all the database into Access so we can create reports on the databases? Thanks, Ryan Pighin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite to Access
The ODBC connector for SQLite will allow you to connect Access to SQLite without having to import/export: http://www.ch-werner.de/sqliteodbc/ Regards, - Robert On Thu, Sep 3, 2009 at 2:33 PM, Pighin, Ryanpigh...@rvh.on.ca wrote: Hi All - We have a new utility in our environment using SQLite and I was wondering if there was a way to dump all the database into Access so we can create reports on the databases? Thanks, Ryan Pighin ___ 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] Defining user groups in SQLite table
On Thu, Sep 03, 2009 at 10:57:13AM -0700, karenebs wrote: I have a database table that holds about 20,000 codes. Each code can be used by several different user groups. I could add a column to the database table for each user group to indicate which codes that particular group has access to. But, I have over 100 different groups! Can anyone suggest another way of doing this? So, group 1 has access to code1, code2, code3, code4, code5; group 2 has access to code1, code2, code3; group 3 has access to code 2, code3, code4. etc. Thanks in advance for any suggestions! Group memberships are best modelled as separate objects. You need three tables then: one for users, one for groups, and one for group memberships: CREATE TABLE users (userid INTEGER PRIMARY KEY, username TEXT, ...); CREATE TABLE groups (groupid INTEGER PRIMARY KEY, groupname TEXT, ...); CREATE TABLE group_memberships (groupid INTEGER, userid INTEGER, UNIQUE (groupid, userid)); You could sprinkle FOREIGN KEY and add ON CONFLICT IGNORE: CREATE TABLE group_memberships (groupid INTEGER, userid INTEGER, UNIQUE (groupid, userid) ON CONFLICT IGNORE, FOREIGN KEY (groupid) REFERENCES groups (groupid), FOREIGN KEY (userid) REFERENCES users (userid)); You could even sprinkle ON DELETE/UPDATE/INSERT clauses: CREATE TABLE group_memberships (groupid INTEGER, userid INTEGER, UNIQUE (groupid, userid) ON CONFLICT IGNORE, FOREIGN KEY (groupid) REFERENCES groups (groupid) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (userid) REFERENCES users (userid) ON DELETE CASCADE ON UPDATE CASCADE); This makes queries for memberships and mebers, both very fast. And you can always use group_concat() to get a single string with all groups that a user is a member of or all users that are members of a group. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?
Atul_Vaidya atul.vai...@prototechsolutions.com wrote: I want to make this query faster, for this, i created indexes for Entity_Id Entity_Id doesn't appear anywhere in the query. How is an index on it supposed to help? and grpuid Which of them? There are three tables, all having a column named grpuid. Run the query with EXPLAIN QUERY PLAN in front. The output will tell you which indexes, if any, SQLite uses for each join. and also used a pragmas : PRAGMA journal_mode = MEMORY; PRAGMA synchronous = OFF; PRAGMA temp_store = MEMORY; PRAGMA count_changes = OFF; PRAGMA cache_size = 12000; These pragmas are mostly used when you are writing to the database. They do little to speed up SELECTs. I also do Begin Transaction and End Transaction Same thing - this doesn't help SELECTs. 2. Entity_ID is a Primary Integer Key Then there's already an index on it. No reason to create a second one. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite to Access
Well, Pighin, Ryan wrote: Hi All - We have a new utility in our environment using SQLite and I was wondering if there was a way to dump all the database into Access so we can create reports on the databases? Why not attach the SQLite database into Access and create your reports? That way changes in the database will be reflected in the reports without having to dump it again. Use an SQLite ODBC driver (for example, the one at www.ch-werner.de/sqliteodbc/ ) and use the linked tables command to attach the SQLite database tables in. Guy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] confused about column affinity
Creating a new thread for this topic... I'm confused by affinity. Here is my munging of Pavel's examples from another thread: --- sql.txt --- .echo on create table d (i); create table n (i numeric); create table i (i integer); insert into d values (1); insert into n values (1); insert into i values (1); select * from d where i=1; select * from n where i=1; select * from i where i=1; select * from d where i='1'; select * from n where i='1'; select * from i where i='1'; .echo off SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .read sql.txt create table d (i); create table n (i numeric); create table i (i integer); insert into d values (1); insert into n values (1); insert into i values (1); select * from d where i=1; 1 select * from n where i=1; 1 select * from i where i=1; 1 select * from d where i='1'; select * from n where i='1'; 1 select * from i where i='1'; 1 .echo off sqlite Based on the documentation (http://www.sqlite.org/datatype3.html) column affinity does not limit the types of data that can be stored, it merely prefers types based on documented coercion rules. The default affinity is NONE if left unspecified. INTEGER affinity behaves like NUMERIC affinity except that it will store a REAL value as an INTEGER if there is no fractional component and it fits into an INTEGER container. Therefore table d should have the default affinity NONE and not coerce data from one storage class to another, so the first insert should put an INTEGER in table d. The second insert should put an INTEGER in table n, and the third insert should also put an INTEGER in table i, both based on coercion rules. So I don't understand why *any* of the last 3 selects should return a value at all. If there is some type conversion going on in the comparisons, why did the fourth select return no results? RW PS: The documentation in section 2.1 (see url above) at first led me to believe that the default affinity is NUMERIC (see the 5th bullet). Perhaps it would be more clear to have the first bullet be If no datatype string is provided, then it is assigned affinity NONE. Also, one might expect that specifying an affinity of NONE would result in an affinity of NONE, but such a table behaves like table n above, i.e. NUMERIC affinity. So section 2.1 correctly describes what strings are parsed for affinity determination, though not without some user surprise. SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table none (i NONE); sqlite insert into none values(1); sqlite select * from none where i='1'; 1 sqlite Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
You can convert a V1 database into a V4 database by opening it, setting the legacy PRAGMA to false, and the VACUUMing the database. You can convert back in a similar way. In fact, be cautious of that. If you have a build that defaults to V1, make sure you turn the legacy PRAGMA off before you VACUUM a V4 database, or it will convert it back to V1 (I think...). http://www.sqlite.org/compile.html#default_file_format http://www.sqlite.org/pragma.html#pragma_legacy_file_format If this is true, the documentation doesn't even hint at the feature: This flag only affects newly created databases. It has no effect on databases that already exist. I would test it, but When the pragma is issued with no argument, it returns the setting of the flag. This pragma does not tell which file format the current database is using. It tells what format will be used by any newly created databases. One way to test it would be to try opening an up-converted database in a version previous to 3.3.0, which I don't have handy. RW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
I would test it, but When the pragma is issued with no argument, it returns the setting of the flag. This pragma does not tell which file format the current database is using. It tells what format will be used by any newly created databases. Hmm. I can't get the pragma to return a value at all. SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite pragma default_file_format; sqlite create table test (i); sqlite pragma default_file_format; sqlite insert into test values(1); sqlite pragma default_file_format; sqlite SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite pragma default_file_format; sqlite pragma default_file_format=false; sqlite pragma default_file_format; sqlite pragma default_file_format=true; sqlite pragma default_file_format; sqlite RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
Wilson, Ronald rwils...@harris.com wrote: Creating a new thread for this topic... I'm confused by affinity. Here is my munging of Pavel's examples from another thread: --- sql.txt --- .echo on create table d (i); create table n (i numeric); create table i (i integer); insert into d values (1); insert into n values (1); insert into i values (1); select * from d where i=1; select * from n where i=1; select * from i where i=1; select * from d where i='1'; select * from n where i='1'; select * from i where i='1'; .echo off SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .read sql.txt create table d (i); create table n (i numeric); create table i (i integer); insert into d values (1); insert into n values (1); insert into i values (1); select * from d where i=1; 1 select * from n where i=1; 1 select * from i where i=1; 1 select * from d where i='1'; select * from n where i='1'; 1 select * from i where i='1'; 1 .echo off sqlite Based on the documentation (http://www.sqlite.org/datatype3.html) column affinity does not limit the types of data that can be stored, it merely prefers types based on documented coercion rules. So I don't understand why *any* of the last 3 selects should return a value at all. The answer is in the document you cite, section 3 Comparison Expressions: quote SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the nominal affinity assigned to the expressions on either side of the binary operator. Affinities are assigned to expressions in the following cases: - An expression that is a simple reference to a column value has the same affinity as the column it refers to. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions. - An expression of the form CAST(expr TO type) is assigned an affinity as if it were a reference to a column declared with type type Conversions are applied before the comparison as described below. In the following bullet points, the two operands are refered to as expression A and expression B. Expressions A and B may appear as either the left or right operands - the following statements are true when considering both A opB and B opA. - When two expressions are compared, if expression A has INTEGER or REAL or NUMERIC affinity and expression B does not, then NUMERIC affinity is applied to the value of expression B before the comparison takes place. - When two expressions are compared, if expression A has been assigned an affinity and expression B has not, then the affinity of expression A is applied to the value of expression B before the comparison takes place. - Otherwise, if neither of the above applies, no conversions occur. The results are compared as is. If a string is compared to a number, the number will always be less than the string. /quote Expression '1' has no affinity. d.i has no affinity either, so in d.i='1' no conversion takes place; since d.i has a value of type integer, it is never equal to a string. On the other hand, n.i and i.i have NUMERIC and INTEGER affinity, correspondingly. So '1' is coerced to NUMERIC value of 1, and the equality test is satisfied. If there is some type conversion going on in the comparisons, why did the fourth select return no results? No affinity, no conversion. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
On Sep 3, 2009, at 3:30 PM, Wilson, Ronald wrote: SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .read sql.txt create table d (i); create table n (i numeric); create table i (i integer); insert into d values (1); insert into n values (1); insert into i values (1); select * from d where i=1; 1 select * from n where i=1; 1 select * from i where i=1; 1 select * from d where i='1'; select * from n where i='1'; 1 select * from i where i='1'; 1 The default affinity is NONE if left unspecified. INTEGER affinity behaves like NUMERIC affinity except that it will store a REAL value as an INTEGER if there is no fractional component and it fits into an INTEGER container. Therefore table d should have the default affinity NONE and not coerce data from one storage class to another, so the first insert should put an INTEGER in table d. The second insert should put an INTEGER in table n, and the third insert should also put an INTEGER in table i, both based on coercion rules. So I don't understand why *any* of the last 3 selects should return a value at all. If there is some type conversion going on in the comparisons, why did the fourth select return no results? In the statement SELECT * FROM n WHERE i='1', in the expression i='1', the i has integer affinity. Hence the '1' on the other side of the = is coerced into a 1. See section 3 Comparison Expressions of the same document: http://www.sqlite.org/datatype3.html This all seems really complicated. But we did it that way because it causes SQLite to mimic the behavior of other statically typed database engines (ex: MySQL, PostgreSQL, Oracle, etc.) and hence maximizes compatibility. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
Sigh, I thought I had read all the applicable sections. Thanks Igor. Though, now I'm curious what your opinion on this is: SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table none (i NONE); sqlite insert into none values(1); sqlite select * from none where i='1'; 1 sqlite RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
Hi, Ronald! You seem to mix terms affinity, data type and declared type of column again. The default affinity is NONE if left unspecified. Mixed statement which is right and wrong in some sense. If you look closer to 2.1 of cited document you'll see that the default affinity is NUMERIC (item # 5). But if you do not specify type of the column in CREATE TABLE statement then affinity will be NONE (item # 3). So I don't understand why *any* of the last 3 selects should return a value at all. If there is some type conversion going on in the comparisons, why did the fourth select return no results? Igor and DRH already answered this so I'm not printing the same. PS: The documentation in section 2.1 (see url above) at first led me to believe that the default affinity is NUMERIC (see the 5th bullet). Perhaps it would be more clear to have the first bullet be If no datatype string is provided, then it is assigned affinity NONE. This is exactly what 3rd bullet says. Also, one might expect that specifying an affinity of NONE would result in an affinity of NONE, but such a table behaves like table n above, i.e. NUMERIC affinity. So section 2.1 correctly describes what strings are parsed for affinity determination, though not without some user surprise. You cannot specify affinity NONE - it's not specified anywhere. In your example you're specified column datatype NONE. And according to the same rules it will fall into 5th bullet and assign affinity NUMERIC. Pavel On Thu, Sep 3, 2009 at 3:30 PM, Wilson, Ronaldrwils...@harris.com wrote: Creating a new thread for this topic... I'm confused by affinity. Here is my munging of Pavel's examples from another thread: --- sql.txt --- .echo on create table d (i); create table n (i numeric); create table i (i integer); insert into d values (1); insert into n values (1); insert into i values (1); select * from d where i=1; select * from n where i=1; select * from i where i=1; select * from d where i='1'; select * from n where i='1'; select * from i where i='1'; .echo off SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .read sql.txt create table d (i); create table n (i numeric); create table i (i integer); insert into d values (1); insert into n values (1); insert into i values (1); select * from d where i=1; 1 select * from n where i=1; 1 select * from i where i=1; 1 select * from d where i='1'; select * from n where i='1'; 1 select * from i where i='1'; 1 .echo off sqlite Based on the documentation (http://www.sqlite.org/datatype3.html) column affinity does not limit the types of data that can be stored, it merely prefers types based on documented coercion rules. The default affinity is NONE if left unspecified. INTEGER affinity behaves like NUMERIC affinity except that it will store a REAL value as an INTEGER if there is no fractional component and it fits into an INTEGER container. Therefore table d should have the default affinity NONE and not coerce data from one storage class to another, so the first insert should put an INTEGER in table d. The second insert should put an INTEGER in table n, and the third insert should also put an INTEGER in table i, both based on coercion rules. So I don't understand why *any* of the last 3 selects should return a value at all. If there is some type conversion going on in the comparisons, why did the fourth select return no results? RW PS: The documentation in section 2.1 (see url above) at first led me to believe that the default affinity is NUMERIC (see the 5th bullet). Perhaps it would be more clear to have the first bullet be If no datatype string is provided, then it is assigned affinity NONE. Also, one might expect that specifying an affinity of NONE would result in an affinity of NONE, but such a table behaves like table n above, i.e. NUMERIC affinity. So section 2.1 correctly describes what strings are parsed for affinity determination, though not without some user surprise. SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table none (i NONE); sqlite insert into none values(1); sqlite select * from none where i='1'; 1 sqlite Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.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] Booleans in SQLite
On Thu, Sep 03, 2009 at 03:57:14PM -0400, Wilson, Ronald wrote: Hmm. I can't get the pragma to return a value at all. SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite pragma default_file_format; Wrong pragma. Try: sqlite pragma legacy_file_format; 1 sqlite pragma legacy_file_format=0; sqlite pragma legacy_file_format; 0 sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
Yeah it seems confusing but once you get your head around it you're ok. The documentation is all there, and I'm sure there are a million threads on the archive about the same thing. If I had the time I'd write an 'affinity for dummies' section for the documentation. In the end, I think a lot of us have to go through the same bewilderment before the light goes on, no matter how milked down the documentation gets. RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com !!! CHANGE OF EMAIL ADDRESS EFFECTIVE JUNE 2009 !!! Please update your contact list to reflect my new Harris email address (ronald.wil...@harris.com) as soon as possible. HARRIS CORPORATION | RF Communications Division assuredcommunications(tm) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of D. Richard Hipp Sent: Thursday, September 03, 2009 4:04 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] confused about column affinity On Sep 3, 2009, at 3:30 PM, Wilson, Ronald wrote: SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .read sql.txt create table d (i); create table n (i numeric); create table i (i integer); insert into d values (1); insert into n values (1); insert into i values (1); select * from d where i=1; 1 select * from n where i=1; 1 select * from i where i=1; 1 select * from d where i='1'; select * from n where i='1'; 1 select * from i where i='1'; 1 The default affinity is NONE if left unspecified. INTEGER affinity behaves like NUMERIC affinity except that it will store a REAL value as an INTEGER if there is no fractional component and it fits into an INTEGER container. Therefore table d should have the default affinity NONE and not coerce data from one storage class to another, so the first insert should put an INTEGER in table d. The second insert should put an INTEGER in table n, and the third insert should also put an INTEGER in table i, both based on coercion rules. So I don't understand why *any* of the last 3 selects should return a value at all. If there is some type conversion going on in the comparisons, why did the fourth select return no results? In the statement SELECT * FROM n WHERE i='1', in the expression i='1', the i has integer affinity. Hence the '1' on the other side of the = is coerced into a 1. See section 3 Comparison Expressions of the same document: http://www.sqlite.org/datatype3.html This all seems really complicated. But we did it that way because it causes SQLite to mimic the behavior of other statically typed database engines (ex: MySQL, PostgreSQL, Oracle, etc.) and hence maximizes compatibility. D. Richard Hipp d...@hwaci.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] confused about column affinity
Wilson, Ronald rwils...@harris.com wrote: Sigh, I thought I had read all the applicable sections. Thanks Igor. Though, now I'm curious what your opinion on this is: SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table none (i NONE); sqlite insert into none values(1); sqlite select * from none where i='1'; 1 sqlite I don't have an opinion on this. Should I? Is there a question in there somewhere? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
Though, now I'm curious what your opinion on this is: I don't have an opinion on this. Should I? Is there a question in there somewhere? Igor Tandetnik The documentation refers to the column affinities TEXT, NUMERIC, INTEGER, REAL, and NONE, but NONE is not identified as a column affinity by the SQL parser, which treats it as NUMERIC. I thought that might lead to some user surprise. That is my opinion. Roughly, my question was, Do you have an opinion on that? You answered my question adequately. Thank you. RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
Wrong pragma. Try: sqlite pragma legacy_file_format; 1 sqlite pragma legacy_file_format=0; sqlite pragma legacy_file_format; 0 sqlite Thanks. I'm going to stop talking for a few days now. Enough gaffs for one day. Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
Wilson, Ronald rwils...@harris.com wrote: Though, now I'm curious what your opinion on this is: I don't have an opinion on this. Should I? Is there a question in there somewhere? The documentation refers to the column affinities TEXT, NUMERIC, INTEGER, REAL, and NONE, but NONE is not identified as a column affinity by the SQL parser, which treats it as NUMERIC. Column affinity is not part of the syntax - declared type is. The affinity is inferred from the type. Column affinity is a semantic, rather than a syntactic, concept, so it is meaningless to state whether or not a parser identifies one. It is true that a declared type of NONE is not treated in any way specially by SQLite, and results in a column with NUMERIC affinity - just like a declared type of, say, BLAH would. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
On Thu, Sep 03, 2009 at 04:21:28PM -0400, Wilson, Ronald wrote: Wrong pragma. Try: Thanks. I'm going to stop talking for a few days now. Enough gaffs for one day. Heh. But, actually, why doesn't SQLite3 produce an error when unknown pragmas are used? Wouldn't that be the right thing to do? I would thinks so. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
Heh. But, actually, why doesn't SQLite3 produce an error when unknown pragmas are used? Wouldn't that be the right thing to do? I would thinks so. I think the docs say unknown pragmas are treated like no-ops. (yeah, I read the docs.) RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
The documentation refers to the column affinities TEXT, NUMERIC, INTEGER, REAL, and NONE, but NONE is not identified as a column affinity by the SQL parser, which treats it as NUMERIC. Column affinity is not part of the syntax - declared type is. The affinity is inferred from the type. Column affinity is a semantic, rather than a syntactic, concept, so it is meaningless to state whether or not a parser identifies one. Thanks for the reply. I really appreciate your fast and frequent feedback, though sometimes your obsession for correctness gets in the way of a simple answer. I think it's fine to gently nudge people to ask well-formed questions, but my question was quite clear despite the semantic/syntactic mismatch. Nevertheless thank you for making me a better person. Seriously. RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.17
On 3 Sep 2009, at 1:38am, P Kishor wrote: well, I think the problem is with the sqlite3 command line tool. I agree. I just checked it with OS X 10.6, which comes with SQLite version 3.6.12, and got the same problem: you can't type accented characters into the sqlite3 tool. So I guess the sqlite3 tool uses a version of readline (or whatever) which doesn't do this properly. However, the SQLite database system does handle unicode characters just fine. I can type accented characters in the Terminal.app using bash and no fancy setting just fine. Yes, OS X 10.6 seems to have a version of bash which reads the accented characters fine without any changes to the environment. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Defining user groups in SQLite table
On 3 Sep 2009, at 6:57pm, karenebs wrote: I have a database table that holds about 20,000 codes. Each code can be used by several different user groups. I could add a column to the database table for each user group to indicate which codes that particular group has access to. But, I have over 100 different groups! Can anyone suggest another way of doing this? So, group 1 has access to code1, code2, code3, code4, code5; group 2 has access to code1, code2, code3; group 3 has access to code 2, code3, code4. etc. It's a classic many-to-many relational model. So the classic way to do it is to create another table listing your groups, and to have a many-to-many table between the two of them, showing which groups can use which codes. If the only thing you care about groups is the group names, and there's no point in keeping any more information than that about your groups, you don't have to do it like that. You can instead add another column to your 'codes' table which is just a long string that lists all the groups that can use that code: group1 group4 group7 group21 Alternatively you can create a groups table, and in that table list all the codes that group has access to: group7 code3 code4 code5 code30 code31 Which of these three things you do depends on which is the most convenient and useful for your purposes. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] shared library location on Linux question
I'm trying to run a .NET program on Linux using MONO that makes use of SQLite and nHibernate. I've got the managed code only System.Data.SQLite.dll and use the sqlite3.dll on Vista and my program and tests run fine. However, I don't know where to put the sqlite-3.6.17.so shared library, or if there are other paths or settings I need to configure to use SQLite on Linux. I'm getting the following exception and any advice would be greatly appreciated. Thanks in advance, Dan SchemaExport: sqlite3_open_v2 System.EntryPointNotFoundException: sqlite3_open_v2 at (wrapper managed-to-native) System.Data.SQLite.UnsafeNativeMethods:sqlite3_open_v2 (byte[],intptr,int,intptr) at System.Data.SQLite.SQLite3.Open (System.String strFilename, SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) [0x0] at System.Data.SQLite.SQLiteConnection.Open () [0x0] at NHibernate.Connection.DriverConnectionProvider.GetConnection () [0x0] at NHibernate.Tool.hbm2ddl.SchemaExport.Execute (Boolean script, Boolean export, Boolean justDrop, Boolean format) [0x0] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.17
On Thu, Sep 03, 2009 at 03:27:38PM +0100, Simon Slavin wrote: On 3 Sep 2009, at 1:38am, P Kishor wrote: well, I think the problem is with the sqlite3 command line tool. I agree. I just checked it with OS X 10.6, which comes with SQLite version 3.6.12, and got the same problem: you can't type accented characters into the sqlite3 tool. So I guess the sqlite3 tool uses a version of readline (or whatever) which doesn't do this properly. However, the SQLite database system does handle unicode characters just fine. It's almost certainly readline. I tried SQLite3 on Solaris without readline and it handles non-ASCII UTF-8 input just fine. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Surprisingly slow performance
I'm having a performance problem with sqlite, where a particular select operation (involving a join) seems to be taking much longer than I'd expect (by an order of magnitude). I've been through several iterations of searching for information on the web, adjusting the table structures, and modifying the relevant query, but to no avail. Here's the basic sequence of events: - I populate a collection of tables from a large file (inside a transaction which is then committed) - I then need to add further rows to the tables (from another large file), but for each insert, first locate an existing row and store it's rowid in the new row. It's locating the existing row that is causing a huge problem - altering the runtime of the second phase from 2 seconds to 70 or 80 seconds. (If I skip the select, and insert a dummy value, everything is blazingly fast) Here's the slow select: SELECT id, heading, lon, lat from positioned, ways on ways.id = positioned.rowid WHERE ident=? AND type=4 AND airport=?; Relevant table definitions: create table positioned (type INT, ident char(8), name char(32), lon float, lat float, elev float, bucket int); create table ways (id INTEGER primary key, heading float, length float, width float, surface int, airport int); Other relevant things: - this is sqlite 3.4.0, as shipped with Leopard. - I'm using rowids to implement support for inheritance - 'positioned' is the base class, and 'ways' is a derived class. All the tables corresponding to my derived classes have an integer primary key which is defined to match the rowid of the base row in 'positioned'. Hence I'm very frequently doing joins on positioned.rowid = sometable.id. Perhaps there's a more efficient or standard idiom to express this? - There's indexes defined on ways (airport) and positioned (type) and (ident) - and some other columns. I have a slight worry that querying an indexed table which is being continually INSERT-ed into might be a potential problem, but I experimented with disabling certain indexes with no change in performance (well, sometimes it got worse) 'ident' is not unique, but matching on it should reduce the potential result set down to fifty or sixty rows at most (before any join needs to take place). A given combination of (ident, airport) *should* be globally unique - there should be exactly one result row unless the input data is malformed (which is unlikely, but possible). I've deliberately made the ident term the first one, after reading in various places that this helps the SQLite query planner. - both positioned and ways have tens of thousands of rows; the entire DB will easily fit in memory. (And, at the time I'm doing these operations, the tables have just been populated, so I'd expect all the DB pages to be in memory ... but perhaps I'm wrong about that) - in the slow phase, I'm inside a single transaction; the sequence of operations is the problematic select, followed by an insert; repeated several thousand times. Hopefully that's all clear - if not, I can supply an example database file, or an EXPLAIN output of the select operation. Regards, James ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
On Thu, Sep 03, 2009 at 03:47:50PM -0400, Wilson, Ronald scratched on the wall: You can convert a V1 database into a V4 database by opening it, setting the legacy PRAGMA to false, and the VACUUMing the database. If this is true, the documentation doesn't even hint at the feature: This flag only affects newly created databases. It has no effect on databases that already exist. I agree that the docs are a bit light on this point. I would have never guessed about the VACUUM thing, if it wasn't for the fact I ran across this line in the 3.2.8 to 3.3.0 section of http://www.sqlite.org/formatchng.html: Once a database file is created, its format is fixed. So a database file created by SQLite 3.2.8 and merely modified by version 3.3.0 or later will retain the old format. Except, the VACUUM command recreates the database so running VACUUM on 3.3.0 or later will change the file format to the latest edition. That is, the way VACUUM essentially works is that it copies everything to a temp database, creates a brand-new-from-scratch database, and copies everything back. When it creates the new database, that database takes on the current defaults. If you stop and think about it, it isn't that big of a surprise. You can use VACUUM to change the page size and a bunch of other fixed parameters. However, it might be nice if the docs were a bit more forthcoming about this point. One way to test it would be to try opening an up-converted database in a version previous to 3.3.0, which I don't have handy. The other way (which is what I did) is to just dump the first ~50 bytes of the file and see what version is set. See 44..47 here: http://www.sqlite.org/fileformat.html#database_header. It would be kind of nice if there was some PRAGMA command that dumped all this type of info for the current main database. I don't remember if sqlite3_analyzer does or not. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared library location on Linux question
Subject: [sqlite] shared library location on Linux question I'm trying to run a .NET program on Linux using MONO that makes use of SQLite and nHibernate. I've got the managed code only System.Data.SQLite.dll and use the sqlite3.dll on Vista and my program and tests run fine. However, I don't know where to put the sqlite- 3.6.17.so shared library, or if there are other paths or settings I need to configure to use SQLite on Linux. I'm getting the following exception and any advice would be greatly appreciated. Thanks in advance, Dan SchemaExport: sqlite3_open_v2 System.EntryPointNotFoundException: sqlite3_open_v2 at (wrapper managed-to-native) System.Data.SQLite.UnsafeNativeMethods:sqlite3_open_v2 (byte[],intptr,int,intptr) at System.Data.SQLite.SQLite3.Open (System.String strFilename, SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) [0x0] at System.Data.SQLite.SQLiteConnection.Open () [0x0] at NHibernate.Connection.DriverConnectionProvider.GetConnection () [0x0] at NHibernate.Tool.hbm2ddl.SchemaExport.Execute (Boolean script, Boolean export, Boolean justDrop, Boolean format) [0x0] Try: export LD_LIBRARY_PATH=/some/path/to/where/sqlite3/lib/resides Otherwise it expects the library to be in the typical places such as /usr/lib. -Allan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
Jay A. Kreibich wrote: Integer values between -128 and 127 use only a single byte of storage above and beyond the header size that all values have. Not quite. Values between 0 127 use 1 byte of storage. Negative values use the full 9 bytes in my experience. (I'm setting aside the integer 0 and integer 1 optimizations outlined in http://www.sqlite.org/fileformat.html#record_format with that statement.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
On Sep 3, 2009, at 7:25 PM, Mark Spiegel wrote: Jay A. Kreibich wrote: Integer values between -128 and 127 use only a single byte of storage above and beyond the header size that all values have. Not quite. Values between 0 127 use 1 byte of storage. Negative values use the full 9 bytes in my experience. (I'm setting aside the integer 0 and integer 1 optimizations outlined in http://www.sqlite.org/fileformat.html#record_format with that statement.) You are both right and both wrong. There are two different integer representations used in SQLite. (1) varint or variable length integer is an encoding of 64-bit signed integers into between 1 and 9 bytes. Negative values use the full 9 bytes as do large positive values. But small non-negative integers use just one or two bytes. Varints are used in places where integers are expected to be small and non-negative, such as record sizes in the btree (usually less than 100 bytes) and also for rowids. (2) When you store an integer into a column (a column other than the rowid) it is stored as a 0-, 1-, 2-, 3-, 4-, 6-, or 8-byte signed integer. The smallest possible representation is used, depending on the magnitude of the integer. The size used is actually recorded in a separate varint (the type varint) that also determines that the value stored is an integer and not (say) a string or blob or floating point number or NULL. A type varint of 1 means store a 1-byte integer. A type varint of 2 means store a 2-byte integer. And so forth. A type varint of 8 (I think) means the value is exactly 0 so don't store anything. 9 means the value is exactly 1. And so forth. Notice that the type varints are all small integers and are thus themselves represented by a single byte. Every value stored has a type varint. Additional data is stored as necessary. A zero-byte string or blob stores uses no space beyond its type varint. A NULL uses no space beyond its type varint. A numeric 0 or 1 uses no space beyond its type varint. An integer between -127 and +127 uses 1 additional byte beyond its varint. A 1MB blob uses a million bytes of additional space beyond its type varint. And so forth. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
D. Richard Hipp wrote: You are both right and both wrong. There are two different integer representations used in SQLite. (1) varint or variable length integer is an encoding of 64-bit signed integers into between 1 and 9 bytes. Negative values use the full 9 bytes as do large positive values. But small non-negative integers use just one or two bytes. Varints are used in places where integers are expected to be small and non-negative, such as record sizes in the btree (usually less than 100 bytes) and also for rowids. (2) When you store an integer into a column (a column other than the rowid) it is stored as a 0-, 1-, 2-, 3-, 4-, 6-, or 8-byte signed integer. The smallest possible representation is used, depending on the magnitude of the integer. The size used is actually recorded in a separate varint (the type varint) that also determines that the value stored is an integer and not (say) a string or blob or floating point number or NULL. A type varint of 1 means store a 1-byte integer. A type varint of 2 means store a 2-byte integer. And so forth. A type varint of 8 (I think) means the value is exactly 0 so don't store anything. 9 means the value is exactly 1. And so forth. Notice that the type varints are all small integers and are thus themselves represented by a single byte. Every value stored has a type varint. Additional data is stored as necessary. A zero-byte string or blob stores uses no space beyond its type varint. A NULL uses no space beyond its type varint. A numeric 0 or 1 uses no space beyond its type varint. An integer between -127 and +127 uses 1 additional byte beyond its varint. A 1MB blob uses a million bytes of additional space beyond its type varint. And so forth. That makes sense. Thank you for clarifying. One further question. It seems when we profile, that a lot of time is spent encoding and decoding varints. Are there really that many multi-byte varints in use in the system? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
On Sep 3, 2009, at 8:25 PM, Mark Spiegel wrote: D. Richard Hipp wrote: You are both right and both wrong. There are two different integer representations used in SQLite. (1) varint or variable length integer is an encoding of 64-bit signed integers into between 1 and 9 bytes. ... (2) When you store an integer into a column (a column other than the rowid) it is stored as a 0-, 1-, 2-, 3-, 4-, 6-, or 8-byte signed integer. ... That makes sense. Thank you for clarifying. One further question. It seems when we profile, that a lot of time is spent encoding and decoding varints. Are there really that many multi-byte varints in use in the system? Most varints are type varints and type varints are almost always a single byte (the only exceptions being for large blobs or strings). Varints are also used to store the total number of bytes in a row (also usually one byte). Most varints are a single byte. We, too, have profiled, and we agree that a lot of time is spent decoding varints. As you have already observed, the common case of a single-byte varint is usually handled by by a macro and so never calls the sqlite3GetVarint() decoder function. And sqlite3GetVarint() is very carefully coded to be fast even when it is called. The varint decoder is one of the more carefully scrutinized parts of SQLite. I'm scanning through some profiling output now and I'm seeing that some varints are almost always a single byte (only 60 multibyte varints out of 474350, in one example) while others are multibyte about half the time. I'm not seeing any cases where more then half the varints are multibyte. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
D. Richard Hipp wrote: Most varints are type varints and type varints are almost always a single byte (the only exceptions being for large blobs or strings). Varints are also used to store the total number of bytes in a row (also usually one byte). Most varints are a single byte. We, too, have profiled, and we agree that a lot of time is spent decoding varints. As you have already observed, the common case of a single-byte varint is usually handled by by a macro and so never calls the sqlite3GetVarint() decoder function. And sqlite3GetVarint() is very carefully coded to be fast even when it is called. The varint decoder is one of the more carefully scrutinized parts of SQLite. I'm scanning through some profiling output now and I'm seeing that some varints are almost always a single byte (only 60 multibyte varints out of 474350, in one example) while others are multibyte about half the time. I'm not seeing any cases where more then half the varints are multibyte. Right. I observed that in the single byte case, the macro prevents the calling of the varint funtions. The last time I profiled this was after Shane worked it over last year. I believe the actual functions (not code generated by the macro) accounted for about 6% of the time spent in SQLite during our performance test suite. Obviously, that is going to be highly variable depending on the type of data contained, and the types of operations performed, etc. Clearly the varints in some cases, will be almost exclusively single byte. What I'm more curious about is their overall usage. Are we looking at 60/474350 for the entire database (in your example) or just one particular use within the database? FWIW, I experimented with several different encoding schemes that preserved the single byte properties and was quickly able to cut the time consumed in our profiling test in half. Unfortunately, they all broke compatibility. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
D. Richard Hipp wrote: Most varints are type varints and type varints are almost always a single byte (the only exceptions being for large blobs or strings). Varints are also used to store the total number of bytes in a row (also usually one byte). Most varints are a single byte. We, too, have profiled, and we agree that a lot of time is spent decoding varints. As you have already observed, the common case of a single-byte varint is usually handled by by a macro and so never calls the sqlite3GetVarint() decoder function. And sqlite3GetVarint() is very carefully coded to be fast even when it is called. The varint decoder is one of the more carefully scrutinized parts of SQLite. I'm scanning through some profiling output now and I'm seeing that some varints are almost always a single byte (only 60 multibyte varints out of 474350, in one example) while others are multibyte about half the time. I'm not seeing any cases where more then half the varints are multibyte. Sorry I missed the obvious. Multi-byte about half the time. Read the numbers, skipped the words. My grade school teachers would not be surprised. Question already answered. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
