Re: [sqlite] Sampling Data
Could you provide some example data? I'm particularly interested in the values of the Minute field. -R. npearson99 wrote: > I had another question before about moving averages via sql statement and the > response I got was great. > > Now I'm trying to "sample" the data. I guess it would be a form of > smoothing but I'm not sure what to call it. > > I want to do something like this: > > row1 = (item[0] + item[1] + item[2] + item[3] )/4) > row2 = (item[4] + item[5] + item[6] + item[7] )/4) > row3 = (item[8] + item[9] + item[10] + item[11] )/4) > > I'm using some pseudo code here, I hope this make sense. > > Example Table: > tableID > Minute > Watt > > I'm trying to sum average the watt column by minute. > > Thanks in advance. > > > -- 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] Moving Average?
A very simple and useful Weighted Moving Average can be had without keeping a number of samples on hand. WMA = (WMA + NewSample) / 2 I.e. the new WMA is the average of the current WMA and the value of the new sample. This weights successive samples by decreasing powers of 2: Current sample weight=1/2, previous sample weight=1/4, next previous=1/8, etc. The WMA tracks changes well. Other weightings can be had by applying coefficients to the elements: WMA = (A*WMA + B*NewSample) / (A+B) It can be seen that the original strategy is an instance of the general formula with coefficients of 1 for A and B. -R. npearson99 wrote: > I'm trying to calculate a moving average in sqlite through sqliteman. > > I'm just trying to get some of the analytic functions to work, but I keep > getting a syntax error. I got this from a sql wiki page, so I hope > something like this will work. > > select sum(salary) over > (partition by months order by minutes rows between unbounded preceding and > current row) > from tblSalaryData > > I get an error by partition. I know this doesn't give me the moving > average, I'm just trying to get the "over" statement part to register. > > Any ideas? > > Thanks! > -- 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] How long should Insert take?
Well, That certainly did the trick. Much obliged! Rate now > 2000 inserts/second including transaction processing and 3390 inserts. -R. Igor Tandetnik wrote: > Rod Dav4is <dav...@yahoo.com> wrote: > >> *Insert rate*: 7/sec. >> > > Run batches of inserts within a single transaction. It's not the insert > itself that's slow, it's the process of committing a transaction. By not > starting an explicit transaction, you commit an implict one on every > statement. > > Igor Tandetnik > > > > ___ > 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] How long should Insert take?
Thanks. I'll give it a try. Igor Tandetnik wrote: > Rod Dav4is <dav...@yahoo.com> wrote: > >> *Insert rate*: 7/sec. >> > > Run batches of inserts within a single transaction. It's not the insert > itself that's slow, it's the process of committing a transaction. By not > starting an explicit transaction, you commit an implict one on every > statement. > > Igor Tandetnik > > > > ___ > 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] How long should Insert take?
The environment:
Compaq Presario CQ60 w/Pentium dual core 2GHz+2GHz, 2GB RAM
Windows Vista Home Premium w/SP2, fully updated & patched
SQLite/3 (3.6.17)
My db 17.5Mb (small-medium size, I would say)
The table: CREATE TABLE INDI (ref text , indi text)
No indices or other constraints on INDI
Data to insert (typical) "DAVI37" , "@I1@"
Insertion code (Rexx, RexxSQL, error checking code omitted)
SetINDI: Procedure Expose D.
SQLCA.=;Call SQLCommand ,'Delete from INDI'
say 'Begin SetINDI' time('R')
say 'count=' D.0INDI.0
Do f_i=1 to D.0INDI.0
IF f_I//100 = 0 Then say f_i time('R')
f_indi=D.0INDI.f_i
f_refn=Strip(D.0INDI.f_indi.1Refn,'Trailing','*')
f_CMD='Insert into INDI values (' quote(f_refn) ',' quote(f_indi) ')'
SQLCA.=;Call SQLCommand , f_CMD
End
*Insert rate*: 7/sec.
This seems rather slow to me. I expected something perhaps an order of
magnitude faster.
The time is not spent in the Rexx overhead. Commenting out the SQL
Insert statement the loop runs in an eyeblink.
Am I expecting too much? I think that SQLite2 did this much faster.
-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
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
*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
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
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 Dav4is<dav...@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. >>>>> >>>>>
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
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
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. P Kishor wrote: > On Wed, Sep 2, 2009 at 3:54 PM, Rod Dav4is<dav...@yahoo.com> wrote: > >> 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. > > > > > >> 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 >> >> > > > > -- 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
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. 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
Aren't these problems considered worth fixing ? 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 >2. *Quotes in SELECT*: Specification of Field='3' failed to find > hits; Field=3 (i.e. without quotes) was required. > > -- 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] Problems encountered on upgrade from SQLite2 to -3
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 2. *Quotes in SELECT*: Specification of Field='3' failed to find hits; Field=3 (i.e. without quotes) was required. -- 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] A problem with versions of SQLite
I'm breaking in a new PC, with Vista fighting me every inch of the way! *My immediate problem*: I have a rather large v2.1 SQLite db (~26MB) and I seem to have lost my copy of SQLite 2.1. Can someone help me out: 1. Provide a pre-compiled 2,1 (for Windows), or 2. Provide an easy conversion to the current 3.6. Perhaps the version 3.6 db manager will automatically convert my 2.1 to the new 3.6 format (or is that too much to hope for?). All help and advice welcomed! -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] Unusual use of the LIKE operator (long)
Very nice: Identify a date format. *Anybody else? *-R.* * Andrew Finkenstadt wrote in part: 8>< and we select the date_format column based on the likeness of the date_pattern column, run it through to_date, catch any exceptions, and return the DATE column. --a - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unusual use of the LIKE operator (long)
I'm not claiming to have invented anything new, as I was using similar techniques some 30 years ago in another life (in the employ of a large blue company which shall remain nameless :-[ ). Not in SQL, you understand. A pretty thorough search didn't turn up any such use in online SQL documentation or forums so I thought to mention it just as an "unusual usage", to perhaps stimulate others to consider the possibilities. *Q*: In what ways have others used the //'string' LIKE column-name// form? *Q*: Is there a name for this usage in SQL? *Andrew*: How does your date table work? -R. Andrew Finkenstadt wrote: We've used this exact technique in a table called "ANY_DATE_FORMAT" since 1997... admittedly in Oracle, but the principle still applies. It's great for coalescing the number of states necessary to implement parsers. :) --andy On 8/18/07, Rod Dav4is <[EMAIL PROTECTED]> wrote: Conventional usage is as follows: ... WHERE column-name LIKE "string" Which, of course, selects rows where the values in the named column match the string, which can, and usually does, have wildcards. I have been using an inverted arrangement: ... WHERE "string" LIKE column-name This allows the wildcards to be in the named column of the database. What possible use could this be? It's hard to even get your head around it. -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.com/~dav4is/ 480 ancestral & collateral families, mostly 17°-19° century New England & European roots. Total population: 117,600+ Annex: http://www.gencircles.com/users/dav4is/ email: [EMAIL PROTECTED] Two roads diverged in a wood, and I... I took the one less traveled by, and that has made all the difference. -Robert Frost - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Unusual use of the LIKE operator (long)
Conventional usage is as follows: ... WHERE column-name LIKE "string" Which, of course, selects rows where the values in the named column match the string, which can, and usually does, have wildcards. I have been using an inverted arrangement: ... WHERE "string" LIKE column-name This allows the wildcards to be in the named column of the database. What possible use could this be? It's hard to even get your head around it. _*Example*_ I have an application that wants to apply changes to images, using the fantastic ImageMagick program. Generally, these are simple size reductions, and the same IM options can be used in broad categories -- with a few exceptions. I have two groups of images, named M.##.tif and HG.##.tif. The IM options are to be different for the two categories, M and HG, but the same within those categories -- with those few exceptions. This could easily get out of hand and become a programming nightmare! Here are the few SQL records (table:OPTIONS) that do this: ___key__priority_ _options_ M.23.% 0options for image M.23 M.70.% 0options for image M.70 HG.01.%0options for image HG.01 M.%9default options for category M HG.% 9default options for category HG The search goes like this: ... WHERE "file-name" LIKE key ORDER BY priority LIMIT 1 I use the LIMIT 1 because each options field has the __complete__ set of options for that case, but a different LIMIT (e.g. 2) could be used to accommodate //additional/ /options for specific images. One can easily see how this can also be extended to provide different options for other image types, perhaps .jpg or .gif (M.%.GIF 8 options). _*Other Applications*_ These are some other applications where I have used table column names in the right argument of the LIKE operator. _*Decision Tables*_ The condition stub is put into the database, and the "don't care" entries are easily represented with LIKE wildcards. This doesn't save having to make those tests, but the operation using SQL is simple to implement. _*Finite State Machine*_ The state transition tuples are represented in SQL, again with don't care positions coded as LIKE wildcards. -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.com/~dav4is/ 480 ancestral & collateral families, mostly 17°-19° century New England & European roots. Total population: 117,600+ Annex: http://www.gencircles.com/users/dav4is/ email: [EMAIL PROTECTED] Two roads diverged in a wood, and I... I took the one less traveled by, and that has made all the difference. -Robert Frost - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] like-op
Low, indeed! It amazes me how ordinary citizens are always ready to jump in with inane remarks like that. As it turns out, LIKE is a lot smarter than I thought, e.g. this works: WHERE FLAGS LIKE '% % 1 %' As long as there are at least 3 blanks in each FLAGS value. I suspect that it would also find records where the 4th word was '1', but that will not be possible in my situation. -R. David Morel wrote: Le mer 15/09/2004 à 00:43, Peter a écrit : Rod Dav4is wrote: 'm reminded of the guy who wanted to buy a loaf of bread and was told how to build a bakery. ;O) Don't hesitate to contact customer services to arrange for your 30day no quibble refund. :-Þ THAT whas low. Actually, you could search the list archive, somebody already wrote such an extension, with astonishing results in terms of speed. I don't remember, I think it was 6 or 8 months ago. -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.com/~dav4is/ 391 ancestral & collateral families, mostly 17th - 19th century New England & European roots, total population: 89,400+ Annex: http://www.gencircles.com/users/dav4is/ email: [EMAIL PROTECTED]
Re: [sqlite] like-op
1. Hardly a solution for those who, like me, don't have C, are not into compiling Windows programs, etc. 2. This would make for a bastard version of LIKE or GLOB, which really grates on my nerves. I'm reminded of the guy who wanted to buy a loaf of bread and was told how to build a bakery. ;O) So, I guess the bottom line answer is No. -R. D. Richard Hipp wrote: Rod Dav4is wrote: Have you given any thought to adding a GREP option to the like-ops? I have a situation where I'd like to select records based on the value of the 3rd word in a particular column. Unless there's a way to do it with LIKE or GLOB? You can use the sqlite3_create_function() API to provide your own implementation of LIKE and/or GLOB that does whatever you want.
[sqlite] like-op
Have you given any thought to adding a GREP option to the like-ops? I have a situation where I'd like to select records based on the value of the 3rd word in a particular column. Unless there's a way to do it with LIKE or GLOB? -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.com/~dav4is/ 391 ancestral & collateral families, mostly 17th - 19th century New England & European roots, total population: 89,400+ Annex: http://www.gencircles.com/users/dav4is/ email: [EMAIL PROTECTED]
[sqlite] TEST
Is this how I should send messages to the list? Your %^&@# verbose instructions tell everything but how to do that! -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.com/~dav4is/ 391 ancestral & collateral families, mostly 17th - 19th century New England & European roots, total population: 89,400+ Annex: http://www.gencircles.com/users/dav4is/ email: [EMAIL PROTECTED]
