Re: [sqlite] OR, IN: which is faster?
On Feb 1, 2007, at 8:19 AM, Ion Silvestru wrote: If we have a query where we compare a column to a set of values, then which is faster: OR or IN? Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR... IN: (mycol IN "a", "b", "c" ...) IN is much faster - OR disables any use of indexes for column mycol. Cheers, Peter. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] OR, IN: which is faster?
If we have a query where we compare a column to a set of values, then which is faster: OR or IN? Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR... IN: (mycol IN "a", "b", "c" ...) Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How To Use ATTACH?
I know how to use ATTACH with sqlite3.exe but I am having problems using it in C++. I am using a wrapper and what I am trying to do is illustrated by: CppSQLite3DB db; db.open("Stocks.db"); db.execDML("ATTACH 'Options.db' AS OPT;"); sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN "; sSQL += "(SELECT Stocks.rowid FROM Stocks, Options "; sSQL += "WHERE Stocks.sStockSymbol = Options.sStockSymbol); "; db.execDML(sSQL.c_str()); but this gives me an error saying table Options (in database file Options.db) is not available. How do I get ATTACH to work in C++? Thanks, Roger - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Obtaining randomness on win32
On Wed, 2007-01-31 at 20:04 -0600, Jay Sprenkle wrote: > On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > > Please pick a pseudo algorithm of your choice and give us the ability to > > > seed it? > > > > > > > You already have this capability. Use the redefinably I/O feature > > to insert your own random number seeder in place of the one that > > comes built in. > > > I don't follow that. You mean write a user defined function? I think you'll need to define SQLITE_ENABLE_REDEF_IO when compiling and then do something like: int myRandomSeedFunction(char *zOutputBuffer){ ... } sqlite3_os_switch()->xRandomSeed = myRandomSeedFunction; The comments in os.h have the details. Dan. > > -- > The PixAddixImage Collector suite: > http://groups-beta.google.com/group/pixaddix > > SqliteImporter and SqliteReplicator: Command line utilities for Sqlite > http://www.reddawn.net/~jsprenkl/Sqlite > > Cthulhu Bucks! > http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to lock sqlite DB when access it
I want to lock a DB when I access it, in case of two processes write/read it at the same time. Forgive my poor english. Thank you.
Re: [sqlite] Obtaining randomness on win32
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > > Please pick a pseudo algorithm of your choice and give us the ability to > > > seed it? > > > > > > > You already have this capability. Use the redefinably I/O feature > > to insert your own random number seeder in place of the one that > > comes built in. > > > I don't follow that. You mean write a user defined function? > Compile with -DSQLITE_ENABLE_REDEF_IO=1. Then there will be a global variable named sqlite3Os.xRandomSeed which is a pointer to the function that seeds the random number generator. Before starting up SQLite for the first time, write a pointer to whatever seeding function you want to use into the variable above and that is what SQLite will use to seed its PRNG. Not sure how to write the seeding function? There are examples in os_unix.c, os_win.c, and os_os2.c. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
> I wonder what the reason was to limit the number of table joins to 32. http://www.sqlite.org/cvstrac/chngview?cn=3622 Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] update inner join syntax
Thanks Richard, it worked perfectly. Roger [EMAIL PROTECTED] wrote: UPDATE stocks SET bOption=1 WHERE rowid IN (SELECT stocks.rowid FROM Stocks, Options WHERE Stocks.sStockSymbol = Options.sStockSymbol); -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite core function question
Hi Jeff, I've encountered some functions that apparently aren't supported by SQLite So have I, such as replacing occurrences of an inner string. so I've created my own I've yet to figure out/try that. Is there a library somewhere of prebuilt functions we can add? Is there and mechanism for standardizing added functions so that databases used in one system are more likely to work on another, because the same functions are there? Also, several of my queries have a basic int() wrapper, that also seems to be unsupported. You could use: SELECT ROUND(x-0.499); or better yet, I expected this to work, using the modulo operator %: SELECT x - x % 1; But it just returns zero. Further testing indicates that % ignores decimal parts of the operands. So this seems to work: SELECT x % 1e100; As long as x is less than 1e100. Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Preserving sort order of joined subquery
I should also mention that I have been able to achieve what I want using a temp table with an autoincrement field. First I do: insert into temp_table (id) select id from table_a order by x; Then I use this table in the join and sort on the autoincrement field: select * from temp_table join table_b on sub.id = table_b.id order by temp_table.num; This works, but I would like to avoid this step :) cheers, -steve - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Obtaining randomness on win32
On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Please pick a pseudo algorithm of your choice and give us the ability to > seed it? > You already have this capability. Use the redefinably I/O feature to insert your own random number seeder in place of the one that comes built in. I don't follow that. You mean write a user defined function? -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com
Re: [sqlite] Abuse of the SQLite website
On 1/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Last night, a single user (or, at least, a single IP address) in China that self-identified as running windows98 and Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz 24980 times and sqlite-source-3_3_12.zip 25044 times over about a 5 hour period, sucking up significant bandwidth in the process. I've seen this type of thing before and have on occasion banned specific IP addresses from the website using iptables -A INPUT -s -j DROP I created a script that scanned my site logs for such things and it automates dropping them into the iptables bit bucket. I'm sure you could come up with something workable fairly quickly
Re: [sqlite] sqlite core function question
From: <[EMAIL PROTECTED]> I'm not sure what "int()" does. Maybe you are looking for round(). Or perhaps cast(expr AS int) will serve your needs. Sorry, I should have been clearer. INT just forces the result to be an integer. So, your "cast" example is probably what I need. Thanks for the quick response. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] What is the sqlite concatenation operator?
Hi James, What is the sqlite concatenation operator? Ohh, ooh, I actually know this one. Pick me, pick me... ;-) The concatenation operator (for joining strings) is a double bar: || eg: SELECT 'hi ' || 'there' gives: 'hi there' See: http://www.sqlite.org/lang_expr.html where it says, in part: The || operator is "concatenate" - it joins together the two strings of its operands. Hope this helps, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite core function question
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote: > I'm currently converting some Access tables/views to SQLite. > I've encountered some functions that apparently aren't > supported by SQLite, so I've created my own (a power function > and an "IIF" function). Also, several of my queries have a > basic int() wrapper, that also seems to be unsupported. Like > the others, I've just added my own, but I wonder if I'm missing > something. The "expression" page doesn't seem to document > an "int" function, but I wonder if there is some other equivalent? > I'm not sure what "int()" does. Maybe you are looking for round(). Or perhaps cast(expr AS int) will serve your needs. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating a view on an ATTACHed database
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote: > > Out of curiosity though, is there a reason why ... a view > [across multiple attached databases] can't be stored permanently? When you open a database and first try to use it, SQLite scans the SQLITE_MASTER table and parses the schema. Views are stored in sqlite_master like all other parts of the schema. IIRC, the parser would get upset if it tried to parse a view that referenced a table that did not yet exist. For that reason, a view cannot reference a table in a different database. I might have fixed the parser at some point so that it will accept a view definition that includes undefined tables, but the restriction on views not referencing tables in other databases seems a reasonable restriction so I left that in. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_interrupt from another thread
Roger Binns <[EMAIL PROTECTED]> wrote: > > We had an earlier discussion about calling sqlite3_interrupt from > another thread, which wasn't possible at the time: > > It looks like the code is fixed for 3.3.12 (and possibly earlier). I > just wanted to double check that it is now officially safe to call from > another thread. The main documentation doesn't mention anything either way. > Go to http://www.sqlite.org/cvstrac/search and do a search for sqlite3_interrupt in tickets and in check-ins. The result is http://www.sqlite.org/cvstrac/search?s=sqlite3_interrupt&t=1&c=1 From this we see that that sqlite3_interrupt() can be called from a separate thread as of check-in [3336] on 2006-07-26 associated with ticket #1897. That was first delivered in version 3.3.7. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite core function question
I'm currently converting some Access tables/views to SQLite. I've encountered some functions that apparently aren't supported by SQLite, so I've created my own (a power function and an "IIF" function). Also, several of my queries have a basic int() wrapper, that also seems to be unsupported. Like the others, I've just added my own, but I wonder if I'm missing something. The "expression" page doesn't seem to document an "int" function, but I wonder if there is some other equivalent? Thanks, Jeff
Re: [sqlite] Creating a view on an ATTACHed database
From: <[EMAIL PROTECTED]> "Jeff Godfrey" <[EMAIL PROTECTED]> wrote: So, is it not possible to create a view across a "main" and an "attached" database? If I recall, you can create a TEMP VIEW across attached databases. Thanks for the tip. Adding TEMP is all it took to get things working. Out of curiosity though, is there a reason why such a view can't be stored permanently? Obviously, it can't be "used" until the other table(s) are attached, but being able to store it would seem to make things a bit tidier... I may be way off base - just wondering... Again - thanks. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_interrupt from another thread
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We had an earlier discussion about calling sqlite3_interrupt from another thread, which wasn't possible at the time: http://article.gmane.org/gmane.comp.db.sqlite.general/20427 It looks like the code is fixed for 3.3.12 (and possibly earlier). I just wanted to double check that it is now officially safe to call from another thread. The main documentation doesn't mention anything either way. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFwUICmOOfHg372QQRAnJnAKDXpjxOhbXtMak9EVoDjXBPfmG7fACfcwUl nOWwJ6XrTmXzNYVg/PqFyv8= =1+/+ -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
I code in VB and I think I stay out of altering the C source code. > just do a single REPLACE command with a SELECT on 2 or more subqueries on > sub-sets of the tables (more efficient). Will try that one. > This stands to reason since you're only doing a single lookup per > sub-table instead of the 6 lookups per sub-table you did with the > UPDATE command. Maybe, but the speed is actually less as my figure of 5 to 6 times faster was faulty due to me not noticing the error caused by the > 32 table joins. I would say it is about 2 to 3 times faster. Still worth it, plus a nicer looking SQL. I wonder what the reason was to limit the number of table joins to 32. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 01 February 2007 00:42 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > There is one important problem though that I just discovered. > Just found out that the maximum number of tables in a join is 32! > So, with my base table that is only 31 to add. Let's do some grepping... #define BMS (sizeof(Bitmask)*8) ... /* The number of tables in the FROM clause is limited by the number of ** bits in a Bitmask */ if( pTabList->nSrc>BMS ){ sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS); return 0; } ... You could try changing src/sqliteInt.h: -typedef unsigned int Bitmask; +typedef u64 Bitmask; and then recompiling sqlite. If all goes well, you should be able to join up to 64 tables. Never tried it. It might work, or might not. Alternatively, you can either perform 2 consecutive REPLACE commands with half the tables in each update (less efficient), or just do a single REPLACE command with a SELECT on 2 or more subqueries on sub-sets of the tables (more efficient). > Actually make that about 5 to 6 times as fast. This stands to reason since you're only doing a single lookup per sub-table instead of the 6 lookups per sub-table you did with the UPDATE command. Never Miss an Email Stay connected with Yahoo! Mail on your mobile. Get started! http://mobile.yahoo.com/services?promote=mail - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] update inner join syntax
Roger Miskowicz <[EMAIL PROTECTED]> wrote: > I am trying to do an update inner join, and have tried the following > without success. Would someone please tell me the proper syntax for > sqlite3? > > > UPDATE Stocks, Stocks-- SQL error: near ",": syntax error > INNER JOIN Options > ON Stocks.sStockSymbol = Options.sStockSymbol > SET Stocks.bOption = 1; > > UPDATE Stocks > SET bOption = 1 > INNER JOIN Options-- SQL error: near "INNER": syntax error > ON Stocks.sStockSymbol = Options.sStockSymbol; > > UPDATE Stocks > SET bOption = 1 > FROM Stocks, Options-- SQL error: near "FROM": syntax error > WHERE Stocks.sStockSymbol = Options.sStockSymbol; > > UPDATE Stocks > SET bOption = 1 -- SQL error: near "FROM": syntax error > FROM Stocks INNER JOIN Options > ON Stocks.sStockSymbol = Options.sStockSymbol; > UPDATE stocks SET bOption=1 WHERE rowid IN (SELECT stocks.rowid FROM Stocks, Options WHERE Stocks.sStockSymbol = Options.sStockSymbol); -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
--- RB Smissaert <[EMAIL PROTECTED]> wrote: > There is one important problem though that I just discovered. > Just found out that the maximum number of tables in a join is 32! > So, with my base table that is only 31 to add. Let's do some grepping... #define BMS (sizeof(Bitmask)*8) ... /* The number of tables in the FROM clause is limited by the number of ** bits in a Bitmask */ if( pTabList->nSrc>BMS ){ sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS); return 0; } ... You could try changing src/sqliteInt.h: -typedef unsigned int Bitmask; +typedef u64 Bitmask; and then recompiling sqlite. If all goes well, you should be able to join up to 64 tables. Never tried it. It might work, or might not. Alternatively, you can either perform 2 consecutive REPLACE commands with half the tables in each update (less efficient), or just do a single REPLACE command with a SELECT on 2 or more subqueries on sub-sets of the tables (more efficient). > Actually make that about 5 to 6 times as fast. This stands to reason since you're only doing a single lookup per sub-table instead of the 6 lookups per sub-table you did with the UPDATE command. Never Miss an Email Stay connected with Yahoo! Mail on your mobile. Get started! http://mobile.yahoo.com/services?promote=mail - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote: > Is cast documented on the sqlite website? I couldn't find it. > http://www.sqlite.org/lang_expr.html -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] update inner join syntax
I am trying to do an update inner join, and have tried the following without success. Would someone please tell me the proper syntax for sqlite3? UPDATE Stocks, Stocks-- SQL error: near ",": syntax error INNER JOIN Options ON Stocks.sStockSymbol = Options.sStockSymbol SET Stocks.bOption = 1; UPDATE Stocks SET bOption = 1 INNER JOIN Options-- SQL error: near "INNER": syntax error ON Stocks.sStockSymbol = Options.sStockSymbol; UPDATE Stocks SET bOption = 1 FROM Stocks, Options-- SQL error: near "FROM": syntax error WHERE Stocks.sStockSymbol = Options.sStockSymbol; UPDATE Stocks SET bOption = 1 -- SQL error: near "FROM": syntax error FROM Stocks INNER JOIN Options ON Stocks.sStockSymbol = Options.sStockSymbol; Thanks, Roger - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Equivalent syntax?
Thanks. Unfortunately my background is sybase and that's anything but standard :-( -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 6:52 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Equivalent syntax? On Wed, 31 Jan 2007 17:30:29 -0500, you wrote: >BTW, what is the concatenation operator? Standard SQL: string || string -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Equivalent syntax?
Thanks, somehow I had missed it. -Original Message- From: Nicolas Williams [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 6:43 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Equivalent syntax? On Wed, Jan 31, 2007 at 06:31:20PM -0500, Anderson, James H (IT) wrote: > Is cast documented on the sqlite website? I couldn't find it. http://www.sqlite.org/ Click on 'syntax', click on 'expression', arrive at: http://www.sqlite.org/lang_expr.html - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On Wed, 31 Jan 2007 17:30:29 -0500, you wrote: >BTW, what is the concatenation operator? Standard SQL: string || string -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On Wed, 31 Jan 2007 18:31:20 -0500, you wrote: >Is cast documented on the sqlite website? I couldn't find it. http://www.sqlite.org/lang_expr.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote: Is cast documented on the sqlite website? I couldn't find it. .. http://www.sqlite.org/lang_expr.html -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On Wed, Jan 31, 2007 at 06:31:20PM -0500, Anderson, James H (IT) wrote: > Is cast documented on the sqlite website? I couldn't find it. http://www.sqlite.org/ Click on 'syntax', click on 'expression', arrive at: http://www.sqlite.org/lang_expr.html - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] What is the sqlite concatenation operator?
Thanks, jim NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.
RE: [sqlite] Equivalent syntax?
Is cast documented on the sqlite website? I couldn't find it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 5:51 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Equivalent syntax? "Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote: > In the case shown, for example, > > convert(char(3), NULL) CDRefIndustry, > > It creates a char(3) column, sets it to null, and names it > CDRefIndustry. > Dennis Cote's guess was mostly right then. A strict equivalent in SQLite (and in standard SQL) would be: cast(NULL AS char(3)) CDRefIndustry But the cast is not really necessary in SQLite. You could get by with just this: NULL CDRefIndustry So, tell me James, what is MorganStanley doing with SQLite? ;-) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNIQUE constraint on column
"Shane Harrelson" <[EMAIL PROTECTED]> wrote: > > I have two tables, an "Objects" table with a foreign key into a second > "Strings" table which is composed of unique values. It is a many to > one relationship, that is, several Objects may reference the same > String. When an Object is added, its associated String is added to > the Strings table. If the String already exists in the Strings > table, I'd like the new Object to reference the existing copy. > > Currently, I've implemented it as so (leaving out error handling, etc.): > > begin transaction > insert into Strings (value) VALUES ( 'foo') > if string insert result is SQLITE_OK >get rowid of last insert (sqlite3_last_insert_rowid) > else if result is SQLITE_CONSTRAINT >select rowid from Strings where value = 'foo' > end if > if rowid >insert into Objects (string_id) VALUES (rowid) > end if > if no error >commit transaction > else >rollback transaction > end if > > With my dataset, there is about a 10% chance of the string being a > duplicate -- that is about 1 in 10 string inserts hit the UNIQUE > constraint violation. > > I've tested "viloating" the internals of the VBDE and pulling the > rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT > result is returned and it is measurably (5-10%) faster then doing the > subsequent SELECT. > What you are doing is the most efficient way that I can think of right off hand. If "violating" the internals is something you want to do that's fine - just remember that it is likely to break in irrepaiable ways in some future point release. No tears. Notice that an sqlite3_last_constraint_rowid() function doesn't really work because an insert might fail due to multiple constraint violations all on different rows. In your schema, perhaps, there can be no more than one constraint violated at a time, but it is easy enough to construct a schema where multiple rows can violate a constraint, so the sqlite3_last_constraint_rowid() idea does not generalize well. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Equivalent syntax?
Experimenting :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 5:51 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Equivalent syntax? "Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote: > In the case shown, for example, > > convert(char(3), NULL) CDRefIndustry, > > It creates a char(3) column, sets it to null, and names it > CDRefIndustry. > Dennis Cote's guess was mostly right then. A strict equivalent in SQLite (and in standard SQL) would be: cast(NULL AS char(3)) CDRefIndustry But the cast is not really necessary in SQLite. You could get by with just this: NULL CDRefIndustry So, tell me James, what is MorganStanley doing with SQLite? ;-) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Preserving sort order of joined subquery
Hello all :) Imagine the following query: select * from ( select id from table_a order by x ) sub join table_b on sub.id = table_b.id; I would like the order that is defined by the results subquery "sub" to be preserved after the result of the join. What I think I need is a function that will number the results in the subquery so I can sort on it in the outer query: select * from ( select id, rownum() as rownum from table_a order by x ) sub join table_b on sub.id = table_b.id order by sub.rownum; I've looked at creating a user defined function or aggregate, but it seems like the function/aggregate's lifetime is either per-row or per grouping so I will not be able to count each row of the result. Any ideas on how I can do this? cheers, -steve - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Equivalent syntax?
OK, thanks, I'll try that. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor Sent: Wednesday, January 31, 2007 5:34 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Equivalent syntax? On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote: > In the case shown, for example, > > convert(char(3), NULL) CDRefIndustry, > > It creates a char(3) column, sets it to null, and names it > CDRefIndustry. Since SQLite has no datatypes, char(3) doesn't mean anything to it. How about SELECT null AS CDRefIndustry or SELECT '' AS CDRefIndustry > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 31, 2007 5:14 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Equivalent syntax? > > "Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote: > > What's the equivalent sqlite syntax for sybase convert function? > > Can you describe what the convert function in sybase does? That > might help us to find the equivalent function in SQLite for you. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote: > In the case shown, for example, > > convert(char(3), NULL) CDRefIndustry, > > It creates a char(3) column, sets it to null, and names it > CDRefIndustry. > Dennis Cote's guess was mostly right then. A strict equivalent in SQLite (and in standard SQL) would be: cast(NULL AS char(3)) CDRefIndustry But the cast is not really necessary in SQLite. You could get by with just this: NULL CDRefIndustry So, tell me James, what is MorganStanley doing with SQLite? ;-) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Preserving sort order of joined subquery
Hello all :) Imagine the following query: select * from ( select id from table_a order by x, y, z ) sub join table_b on sub.id = table_b.id I would like the order that is defined by the results subquery "sub" to be preserved after the result of the join. I do realize I could move the "order by" clause to the outer query, but lets say the subquery - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNIQUE constraint on column
On Wed, Jan 31, 2007 at 05:23:29PM -0500, Shane Harrelson wrote: > > I have two tables, an "Objects" table with a foreign key into a second > "Strings" table which is composed of unique values. It is a many to > one relationship, that is, several Objects may reference the same > String. When an Object is added, its associated String is added to > the Strings table. If the String already exists in the Strings > table, I'd like the new Object to reference the existing copy. > > Currently, I've implemented it as so (leaving out error handling, etc.): > > begin transaction > insert into Strings (value) VALUES ( 'foo') > if string insert result is SQLITE_OK Sounds like you should want to use INSERT OR IGNORE ... INTO Strings and then SELECT the rowid of the string for use in INSERTing INTO Object. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating a view on an ATTACHed database
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote: > > So, is it not possible to create a view across a "main" and > an "attached" database? > If I recall, you can create a TEMP VIEW across attached databases. But you can't create a persistent view because such a view would not make sense to a processes that opened only one database without opening the other attached databases. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Abuse of the SQLite website
Peter James wrote: On 1/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Thoughts anyone? Are there less drastic measures that might be taken to prevent this kind of abuse? A couple of people here mentioned CAPTCHA's. This is sort of the standard for preventing automated abuse (intentional or unintentional), and there are lots of example implementations out there, maybe even in whatever "custom software" you use. :-) http://en.wikipedia.org/wiki/Captcha Even if you don't go the CAPTCHA route, just forcing an HTTP POST to begin a download will probably filter out a large proportion of errant traffic or web bots. I see your robots.txt file is in order for the downloads area, but of course that's just a gentleman's agreement... I would be really careful about using these. A significant number of people are visually impaired -- I work directly with one person who is (he uses powerful magnifiers to read normal sized 10-12 point text) and a second person who sits across from me in the office is blind. I don't know how they deal with captcha verification, I will have to ask. Notice what the wikipedia text has to say on accessibility issues. A maptcha is probably a better solution but if you don't understand you are looking at a math problem, you are also blocked out. Bob Cochran - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote: In the case shown, for example, convert(char(3), NULL) CDRefIndustry, It creates a char(3) column, sets it to null, and names it CDRefIndustry. Since SQLite has no datatypes, char(3) doesn't mean anything to it. How about SELECT null AS CDRefIndustry or SELECT '' AS CDRefIndustry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 5:14 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Equivalent syntax? "Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote: > What's the equivalent sqlite syntax for sybase convert function? Can you describe what the convert function in sybase does? That might help us to find the equivalent function in SQLite for you. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote: What's the equivalent sqlite syntax for sybase convert function? For example, select distinct date, CDId, CDName, CDTicket, tradeId, tapsAccount, CDBook, coalesce(CDFid,'') CDFid, CDStatus, CDTradeDate, CDExpDate, CDNotional, CDCurr, CDSellBuy, CDType, CDExerType, CDEntity, CDCusip, CDSetlType, CDCredInit, CDSingleEntry, CDMaterialType, CDEffDate, CDPremFreq, CDPaymentType, CDUpfrontSetl, CDPublicInfo, CDCollReq, CDSpreadCurve, CDPremium, CDOptType, CDAccrue, CDRefPrice, CDPremiumAmnt, CDLastCoupLength, CDWhoDelivers, CDCollateralText, CDFactorReason, CDDefStartProt, CDDefEndProt, CDDefProtType, convert(char(80), NULL) CDComment, convert(varchar(100), NULL) CDEvent, convert(char(11), NULL) CDCurveType, convert(char(11), NULL) CrvShName, convert(varchar(90), NULL) CDRefEntity, convert(char(3), NULL) CDRefIndustry, convert(char(3), NULL) CDRefCountry, convert(char(4), NULL) CDRefSNP, convert(char(4),NULL) CDRefMoody, convert(char(4),NULL) CDRefMSRating, CDRefId into TMP_credDerivOrig from credDerivOrig_C1 Are you looking for something like ifnull or nullif? NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Equivalent syntax?
I don't see the cast function listed in the Core Functions section of the web page... BTW, what is the concatenation operator? -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 5:21 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Equivalent syntax? Anderson, James H (IT) wrote: > What's the equivalent sqlite syntax for sybase convert function? For > example, > > select distinct > date, CDId, CDName, CDTicket, > tradeId, tapsAccount, CDBook, coalesce(CDFid,'') CDFid, > CDStatus, CDTradeDate, CDExpDate, CDNotional, > CDCurr, CDSellBuy, CDType, CDExerType, > CDEntity, CDCusip, CDSetlType, CDCredInit, > CDSingleEntry, CDMaterialType, CDEffDate, CDPremFreq, > CDPaymentType, CDUpfrontSetl, CDPublicInfo, CDCollReq, > CDSpreadCurve, CDPremium, CDOptType, CDAccrue, > CDRefPrice, CDPremiumAmnt, CDLastCoupLength, CDWhoDelivers, > CDCollateralText, CDFactorReason, CDDefStartProt, CDDefEndProt, > CDDefProtType, > convert(char(80), NULL) CDComment, > convert(varchar(100), NULL) CDEvent, > convert(char(11), NULL) CDCurveType, > convert(char(11), NULL) CrvShName, > convert(varchar(90), NULL) CDRefEntity, > convert(char(3), NULL) CDRefIndustry, > convert(char(3), NULL) CDRefCountry, > convert(char(4), NULL) CDRefSNP, > convert(char(4),NULL) CDRefMoody, > convert(char(4),NULL) CDRefMSRating, > CDRefId > into TMP_credDerivOrig > from credDerivOrig_C1 > > Based on context I would say the cast function is the rough equivalent. But, since all text types in SQLite are equivalent there is no need to convert them from one type to another (i.e. from varchar(100) to char(80)). I suspect this query would translate to SQLite by simply removing the convert(...) clauses. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNIQUE constraint on column
On 1/31/07, Dennis Cote <[EMAIL PROTECTED]> wrote: Shane Harrelson wrote: > On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> >> The official way to find the conflicting entry is to do a query. >> >> SELECT rowid FROM table WHERE uniquecolumn=?; >> >> > > Thank you for the reply. I assumed this was most likely the case, and > as I said in my original email, I was hoping to avoid having to do a > seperate select query for the sake of speed... especially since the > value I needed was so tantalizing close in the VDBE struct. > Shane, Why do you want the rowid of the conflicting row? I only ask because I suspect there may be a better way to do what you want to do. Dennis Cote I have two tables, an "Objects" table with a foreign key into a second "Strings" table which is composed of unique values. It is a many to one relationship, that is, several Objects may reference the same String. When an Object is added, its associated String is added to the Strings table. If the String already exists in the Strings table, I'd like the new Object to reference the existing copy. Currently, I've implemented it as so (leaving out error handling, etc.): begin transaction insert into Strings (value) VALUES ( 'foo') if string insert result is SQLITE_OK get rowid of last insert (sqlite3_last_insert_rowid) else if result is SQLITE_CONSTRAINT select rowid from Strings where value = 'foo' end if if rowid insert into Objects (string_id) VALUES (rowid) end if if no error commit transaction else rollback transaction end if With my dataset, there is about a 10% chance of the string being a duplicate -- that is about 1 in 10 string inserts hit the UNIQUE constraint violation. I've tested "viloating" the internals of the VBDE and pulling the rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT result is returned and it is measurably (5-10%) faster then doing the subsequent SELECT. Any help or suggestions with how to do this better would be appreciated. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Equivalent syntax?
In the case shown, for example, convert(char(3), NULL) CDRefIndustry, It creates a char(3) column, sets it to null, and names it CDRefIndustry. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 5:14 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Equivalent syntax? "Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote: > What's the equivalent sqlite syntax for sybase convert function? Can you describe what the convert function in sybase does? That might help us to find the equivalent function in SQLite for you. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
Anderson, James H (IT) wrote: What's the equivalent sqlite syntax for sybase convert function? For example, select distinct date, CDId, CDName, CDTicket, tradeId, tapsAccount, CDBook, coalesce(CDFid,'') CDFid, CDStatus, CDTradeDate, CDExpDate, CDNotional, CDCurr, CDSellBuy, CDType, CDExerType, CDEntity, CDCusip, CDSetlType, CDCredInit, CDSingleEntry, CDMaterialType, CDEffDate, CDPremFreq, CDPaymentType, CDUpfrontSetl, CDPublicInfo, CDCollReq, CDSpreadCurve, CDPremium, CDOptType, CDAccrue, CDRefPrice, CDPremiumAmnt, CDLastCoupLength, CDWhoDelivers, CDCollateralText, CDFactorReason, CDDefStartProt, CDDefEndProt, CDDefProtType, convert(char(80), NULL) CDComment, convert(varchar(100), NULL) CDEvent, convert(char(11), NULL) CDCurveType, convert(char(11), NULL) CrvShName, convert(varchar(90), NULL) CDRefEntity, convert(char(3), NULL) CDRefIndustry, convert(char(3), NULL) CDRefCountry, convert(char(4), NULL) CDRefSNP, convert(char(4),NULL) CDRefMoody, convert(char(4),NULL) CDRefMSRating, CDRefId into TMP_credDerivOrig from credDerivOrig_C1 Based on context I would say the cast function is the rough equivalent. But, since all text types in SQLite are equivalent there is no need to convert them from one type to another (i.e. from varchar(100) to char(80)). I suspect this query would translate to SQLite by simply removing the convert(...) clauses. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Creating a view on an ATTACHed database
Hi All, I have an open sqlite3 database (name = dbSerial), to which I've attached a 2nd database (name = dbParent). Now, I'm trying to create a view by joining a view from dbSerial with another view from dbParent. Attempting to create the view generates the following error: Error: view [tcoverage] cannot reference objects in database dbParent Specifically, here's my (contrived) view creation code... SELECT c.zone, t.zone FROM precoverage AS c INNER JOIN dbParent.target AS t ON (c.zone = t.zone) So, is it not possible to create a view across a "main" and an "attached" database? If not, what's my best option (copy the necessary data to a single (in memory?) database?)... Thanks for any pointers. Jeff
Re: [sqlite] Equivalent syntax?
"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote: > What's the equivalent sqlite syntax for sybase convert function? Can you describe what the convert function in sybase does? That might help us to find the equivalent function in SQLite for you. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Equivalent syntax?
What's the equivalent sqlite syntax for sybase convert function? For example, select distinct date, CDId, CDName, CDTicket, tradeId, tapsAccount, CDBook, coalesce(CDFid,'') CDFid, CDStatus, CDTradeDate, CDExpDate, CDNotional, CDCurr, CDSellBuy, CDType, CDExerType, CDEntity, CDCusip, CDSetlType, CDCredInit, CDSingleEntry, CDMaterialType, CDEffDate, CDPremFreq, CDPaymentType, CDUpfrontSetl, CDPublicInfo, CDCollReq, CDSpreadCurve, CDPremium, CDOptType, CDAccrue, CDRefPrice, CDPremiumAmnt, CDLastCoupLength, CDWhoDelivers, CDCollateralText, CDFactorReason, CDDefStartProt, CDDefEndProt, CDDefProtType, convert(char(80), NULL) CDComment, convert(varchar(100), NULL) CDEvent, convert(char(11), NULL) CDCurveType, convert(char(11), NULL) CrvShName, convert(varchar(90), NULL) CDRefEntity, convert(char(3), NULL) CDRefIndustry, convert(char(3), NULL) CDRefCountry, convert(char(4), NULL) CDRefSNP, convert(char(4),NULL) CDRefMoody, convert(char(4),NULL) CDRefMSRating, CDRefId into TMP_credDerivOrig from credDerivOrig_C1 Thanks, jim NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.
Re: [sqlite] NULL always greater?
Dennis, Thanks for the timely reply. max(coalesce(col1, 0), coalesce(col2, 0)) is a lot cleaner than the max( case when col1 is null then 0 else col1 end, case when col2 is null then 0 else col2 end ) solution I came up with. Though the performance seems to be about the same. The "coalesce" word hasn't been in my vocabulary, so it has no meaning for me. I'll have to read-up :-)) -Clark - Original Message From: Dennis Cote <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 31, 2007 11:40:02 AM Subject: Re: [sqlite] NULL always greater? Clark Christensen wrote: > I've read through numerous discussions here about comparing values with null, > and how SQLite functions work with null values, and I thought I understood. > > Now it seems appropriate to use the max(col1, col2) function to find the > latest of two dates (integer Unix times), and some rows will contain null in > one column or the other. But, max() always returns null when one of its args > is null. That just seems backwards :-)) > > FWIW, I'm on 3.3.12 on both Windows and Linux. > > Any help is appreciated. > > Clark, You must reassign the value used for the comparison if it is null. The coalesce function will return the first non null value in its arguments, and this may be all you need. If you only want the value from col2 if col1 is null then simply use coalesce(col1, col2) You will only get a null result if both columns are null. If you really wan the max of the two columns you can use coalesc to convert nulls into zeros for the max function. max(coalesce(col1, 0), coalesce(col2, 0)) This will give a result of zero if both columns are null. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
There is one important problem though that I just discovered. Just found out that the maximum number of tables in a join is 32! So, with my base table that is only 31 to add. This trouble doesn't of course apply to the old UPDATE method. So, I think after all I need the old way of doing it or what I could do is see how many tables are to be added and pick the method accordingly. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:54 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Actually make that about 5 to 6 times as fast. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:39 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400, 410); > insert into e2 values(5, 500, 510); > > create table e3(id primary key, a, b); > insert into e3 values(3, 23.1, 230.1); > insert into e3 values(4, 24.1, 240.1); > insert into e3 values(5, 25.1, 250.1); > > select * from t1 order by id; > > replace into t1(id, e2_a, e2_b, e3_a, e3_b) > select t1.id, e2.a, e2.b, e3.a, e3.b > from t1, e2, e3 > where t1.id = e2.id and t1.id = e3.id; > > select * from t
Re: [sqlite] UNIQUE constraint on column
Hi Shane, On Wed, 31 Jan 2007 09:29:24 -0500, you wrote: >On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> "Shane Harrelson" <[EMAIL PROTECTED]> wrote: >> > when i try to insert a row into a table that has a UNIQUE constraint >> > on a column, and I get the SQLITE_CONSTRAINT result code because i'm >> > inserting a duplicate value, is there anyway to determine the rowid of >> > the conflict? >> > >> > looking at the internals of the VDBE, i found that the rowid of the >> > conflicting row is pushed on top of the VDBE stack. >> > >> > if i'm willing to violate the interface, i can dereference the rowid >> > from the internals of the VDBE struct. i'd rather not do this... is >> > there a more formal mechanism for getting this value without having do >> > to do a separate "select" query? something like >> > sqlite3_last_insert_rowid() -- ie. sqlite3_last_conflict_rowid()? >> > >> >> The official way to find the conflicting entry is to do a query. >> >> SELECT rowid FROM table WHERE uniquecolumn=?; >> >> -- >> D. Richard Hipp <[EMAIL PROTECTED]> >> > >Thank you for the reply. I assumed this was most likely the case, and >as I said in my original email, I was hoping to avoid having to do a >seperate select query for the sake of speed... especially since the >value I needed was so tantalizing close in the VDBE struct. The SELECT will be quite fast, chances are the required pages of the unique index will still be in the cache. In my opinion it is always better to write portable code, so I would prefer to rely on generic SQL than on yet another implementation specific API. Just my 2 cents. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNIQUE constraint on column
Shane Harrelson wrote: On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: The official way to find the conflicting entry is to do a query. SELECT rowid FROM table WHERE uniquecolumn=?; Thank you for the reply. I assumed this was most likely the case, and as I said in my original email, I was hoping to avoid having to do a seperate select query for the sake of speed... especially since the value I needed was so tantalizing close in the VDBE struct. Shane, Why do you want the rowid of the conflicting row? I only ask because I suspect there may be a better way to do what you want to do. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] NULL always greater?
Clark Christensen wrote: I've read through numerous discussions here about comparing values with null, and how SQLite functions work with null values, and I thought I understood. Now it seems appropriate to use the max(col1, col2) function to find the latest of two dates (integer Unix times), and some rows will contain null in one column or the other. But, max() always returns null when one of its args is null. That just seems backwards :-)) FWIW, I'm on 3.3.12 on both Windows and Linux. Any help is appreciated. Clark, You must reassign the value used for the comparison if it is null. The coalesce function will return the first non null value in its arguments, and this may be all you need. If you only want the value from col2 if col1 is null then simply use coalesce(col1, col2) You will only get a null result if both columns are null. If you really wan the max of the two columns you can use coalesc to convert nulls into zeros for the max function. max(coalesce(col1, 0), coalesce(col2, 0)) This will give a result of zero if both columns are null. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
Actually make that about 5 to 6 times as fast. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:39 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400, 410); > insert into e2 values(5, 500, 510); > > create table e3(id primary key, a, b); > insert into e3 values(3, 23.1, 230.1); > insert into e3 values(4, 24.1, 240.1); > insert into e3 values(5, 25.1, 250.1); > > select * from t1 order by id; > > replace into t1(id, e2_a, e2_b, e3_a, e3_b) > select t1.id, e2.a, e2.b, e3.a, e3.b > from t1, e2, e3 > where t1.id = e2.id and t1.id = e3.id; > > select * from t1 order by id; > > id e2_ae2_be3_ae3_b > 3 30 31 23 230 > 4 40 41 24 240 > 5 50 51 25 250 > > id e2_ae2_be3_ae3_b > 3 300 310 23.1230.1 > 4 400 410 24.1240.1 > 5 500 510 25.1250.1 > > > > > > RBS > > > > -Original Message- > > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > > Sent: 29 January 2007 23:52 > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] Limit statement size? > > > > RB
RE: [sqlite] Limit statement size?
Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400, 410); > insert into e2 values(5, 500, 510); > > create table e3(id primary key, a, b); > insert into e3 values(3, 23.1, 230.1); > insert into e3 values(4, 24.1, 240.1); > insert into e3 values(5, 25.1, 250.1); > > select * from t1 order by id; > > replace into t1(id, e2_a, e2_b, e3_a, e3_b) > select t1.id, e2.a, e2.b, e3.a, e3.b > from t1, e2, e3 > where t1.id = e2.id and t1.id = e3.id; > > select * from t1 order by id; > > id e2_ae2_be3_ae3_b > 3 30 31 23 230 > 4 40 41 24 240 > 5 50 51 25 250 > > id e2_ae2_be3_ae3_b > 3 300 310 23.1230.1 > 4 400 410 24.1240.1 > 5 500 510 25.1250.1 > > > > > > RBS > > > > -Original Message- > > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > > Sent: 29 January 2007 23:52 > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] Limit statement size? > > > > RB Smissaert wrote: > > > Had a go at this, but sofar I haven't been able yet to get it to work. > > > I get no error, but A3Test115_J remains just at it is. > > > I couldn't find much information about INSERT OR REPLACE in the SQ
Re: [sqlite] Does SQLite support user-defined data-types ?
You need to revise your Java interface or maybe find another. What are you using? Jerome CORRENOZ wrote: Now, I can use user-dfined types by declaring them with SQL create tables. Fine ! But when I try to use the following code to get an object, I get a ClassCastException: Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery( "SELECT * FROM EMP"); while (rset.next()) { Dept dept = (Dept)rset.getObject("DEPT"); } Is it normal or is it due to the fact that SQLite doesn't support SQL3 commands, mainly the getTypeMap() command that maps a Java class with a SQL type ? Notice that my Java class implements SQLData but it seems having no effect. Thanks for your answer, Jerome John Stanton wrote: Sqlite is flexible in typing. You can create user defined types just by declaring them in your SQL which creates tables. Thereafter the API will return the declared type and the actual type so that you can process it appropriately. Jerome CORRENOZ wrote: Hi, I'm starting with SQLite and I would like to know if it is possible to create user-defined data-types through the following SQL command: create type MyType ... ? In fact, I need it to map a database with an object-oriented model, so that I could manage the objects in a ResultSet like it follows: Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery( "SELECT * FROM EMP"); while (rset.next()) { Dept dept = (Dept)rset.getObject("DEPT"); System.out.println("Lieu du dept : " + dept.getLieu()); } Is there a way to do it with SQLite ? Regards, Jerome - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[2]: [sqlite] adding years,months, days with decimals using datetime function
I faced the same problem recently (before I joined this newsgroup). I backed off from SQL to C++ level, which was very uncomfortable. It would be very handy if you implement the same decimal-point parsing for years too ;) Best Regards, Ivailo Karamanolev On Wednesday, January 31, 2007, 6:04:14 PM, [EMAIL PROTECTED] wrote: > "info" <[EMAIL PROTECTED]> wrote: >> Hi, >> >> If I use the expression datetime('2000-01-01','1.5 months'), SQLite returns >> 2000-02-16 00:00:00. This means that it added one month plus half a month. >> Makes sense. >> >> If I use the expression datetime('2000-01-01','1.5 days'), I get 2000-01-02 >> 12:00:00. Again this makes sense: 1.5 days is equal to 1 day plus 12 hours. >> >> But with the expression datetime('2000-01-01','1.5 years') the result is >> 2001-01-01 00:00:00. Which means, SQLite added only 1 year and not an extra >> 6 months. >> >> Can anyone explain why using decimals works for months and days and not for >> years? >> > Because nobody has ever written the code to do that. :-) > -- > D. Richard Hipp <[EMAIL PROTECTED]> > - > To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] adding years,months, days with decimals using datetime function
So, it is not a bug but an unadvertised feature. :-) What will happen most likely? That working with decimals for years will also be added in the future, or that working with decimals for months and days will be removed? Rick van der Lans -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Verzonden: woensdag 31 januari 2007 17:04 Aan: sqlite-users@sqlite.org Onderwerp: Re: [sqlite] adding years,months, days with decimals using datetime function "info" <[EMAIL PROTECTED]> wrote: > Hi, > > If I use the expression datetime('2000-01-01','1.5 months'), SQLite returns > 2000-02-16 00:00:00. This means that it added one month plus half a month. > Makes sense. > > If I use the expression datetime('2000-01-01','1.5 days'), I get 2000-01-02 > 12:00:00. Again this makes sense: 1.5 days is equal to 1 day plus 12 hours. > > But with the expression datetime('2000-01-01','1.5 years') the result is > 2001-01-01 00:00:00. Which means, SQLite added only 1 year and not an extra > 6 months. > > Can anyone explain why using decimals works for months and days and not for > years? > Because nobody has ever written the code to do that. :-) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can't build 3.3.12 on my Unix
<[EMAIL PROTECTED]> wrote on 01/30/2007 03:24:51 PM: > [EMAIL PROTECTED] wrote: > > > > I probably should ask my question this way: What is the *safe* method for > > 32 bit machines to build Sqlite 3.3x? > > > > "Safe" is relative. I know of a few companies that use the technique > I outlined earlier. But I have never personally tested a 32-bit build > so I cannot say what problems might come up. I would expect to find > problems if you try to insert an integer that cannot be represented in > only 32-bits, for example. But as long as you stick to smaller > integers I'm guessing everything will likely work ok. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > Good News! I dropped the xlong.h, and put the defined the x64 stuff as long and unsigned long, and it compiled 1 2 3: >From sqlite3.h: #ifdef SQLITE_INT64_TYPE typedef SQLITE_INT64_TYPE sqlite_int64; typedef unsigned SQLITE_INT64_TYPE sqlite_uint64; #elif defined(_MSC_VER) || defined(__BORLANDC__) typedef __int64 sqlite_int64; typedef unsigned __int64 sqlite_uint64; #else typedef long sqlite_int64; typedef unsigned long sqlite_uint64; typedef long i64; typedef unsigned long u64; #endif I will begin testing to see what that does to the capabilities of the databaseBut maybe later I will get GCC version 4 built, and i can try again with the original source. Thanks again! - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] adding years,months, days with decimals using datetime function
"info" <[EMAIL PROTECTED]> wrote: > Hi, > > If I use the expression datetime('2000-01-01','1.5 months'), SQLite returns > 2000-02-16 00:00:00. This means that it added one month plus half a month. > Makes sense. > > If I use the expression datetime('2000-01-01','1.5 days'), I get 2000-01-02 > 12:00:00. Again this makes sense: 1.5 days is equal to 1 day plus 12 hours. > > But with the expression datetime('2000-01-01','1.5 years') the result is > 2001-01-01 00:00:00. Which means, SQLite added only 1 year and not an extra > 6 months. > > Can anyone explain why using decimals works for months and days and not for > years? > Because nobody has ever written the code to do that. :-) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite read-only
Ion Silvestru <[EMAIL PROTECTED]> wrote: > Hi, > > Is there a possibility to open a SQLite database read-only so that > database file will not be locked? This request is for updating > reasons, to overwrite the database file with a newer version, while > users have opened the database only for read. > No, not really. If a database were opened read-only without a read-lock and it was trying to read while another database was writing, then the reader might see partially updated information, which is incorrect. That said, you can play games with the user-defined I/O layer to make it do anything you want. But on your own head be it. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does julianday work according to the manual?
"info" <[EMAIL PROTECTED]> wrote: > Hi, > > The manual states that the function julianday returns the number of days > since noon in Greenwich on November 24, 4714 B.C. That would imply that the > statement: > > Select julianday('-4714-11-24 12:00:00'); > > Should return 0.0. But it doesn't, it returns -365.0 > > Does this mean, that the manual should say "since noon in Greenwich on > November 24, 4713 B.C.? Or am I missing something? > The year -4713 and 4714 b.c. are the same year. When using historical notation (eg: "b.c.") you skip the zero year and go straight from 1 a.d. to 1 b.c. When using astronomical notation (eg: -4713) there is a 0 year, which corresponds to 1 b.c. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] NULL always greater?
Aah, perfect. Thanks for the pointer. -Clark - Original Message From: Dan Kennedy <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Tuesday, January 30, 2007 10:49:34 PM Subject: Re: [sqlite] NULL always greater? The basic rule is that the result of any comparison involving a NULL value (including comparing against another NULL) is NULL. See the following for the details: http://www.sqlite.org/nulls.html Dan. On Tue, 2007-01-30 at 16:41 -0800, Clark Christensen wrote: > I've read through numerous discussions here about comparing values with null, > and how SQLite functions work with null values, and I thought I understood. > > Now it seems appropriate to use the max(col1, col2) function to find the > latest of two dates (integer Unix times), and some rows will contain null in > one column or the other. But, max() always returns null when one of its args > is null. That just seems backwards :-)) > > FWIW, I'm on 3.3.12 on both Windows and Linux. > > Any help is appreciated. > > Thanks! > > -Clark > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
> Your INSERT OR REPLACE statement is in error. Yes, you are right. In the end it all came down to a simple mistake on my side. Sorry if I have wasted anybody's time. Got this all working now and will now see if it is faster than my old method. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 03:49 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Your INSERT OR REPLACE statement is in error. You have fewer columns in your SELECT clause than are specified in your INSERT column name list. You should have seen an error like this in SQLite version 3.3.12: SQL error: X values for Y columns Assuming PATIENT_ID is the sole unique key for A3TestB67_J and your SQL column counts match, the REPLACE should work. --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does julianday work according to the manual?
On Wednesday, January 31, 2007 Rick van der Lans wrote: > The manual states that the function julianday returns the number of days > since noon in Greenwich on November 24, 4714 B.C. That would imply that the > statement: > Select julianday('-4714-11-24 12:00:00'); > Should return 0.0. But it doesn't, it returns -365.0 > Does this mean, that the manual should say "since noon in Greenwich on > November 24, 4713 B.C.? Or am I missing something? sqlite> Select julianday('-0001-11-24 12:00:00'); 1721022.0 sqlite> Select julianday('-11-24 12:00:00'); 1721388.0 sqlite> Select julianday('0001-11-24 12:00:00'); 1721753.0 There is no year 0. The calendar goes from -1 BCE to 1 CE. -11-24 is 1 BCE. So, if you want November 24, 4714 B.C. you need to say sqlite> Select julianday('-4713-11-24 12:00:00'); 0.0 e -- Doug Currie Londonderry, NH, USA - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] adding years,months, days with decimals using datetime function
Hi, If I use the expression datetime('2000-01-01','1.5 months'), SQLite returns 2000-02-16 00:00:00. This means that it added one month plus half a month. Makes sense. If I use the expression datetime('2000-01-01','1.5 days'), I get 2000-01-02 12:00:00. Again this makes sense: 1.5 days is equal to 1 day plus 12 hours. But with the expression datetime('2000-01-01','1.5 years') the result is 2001-01-01 00:00:00. Which means, SQLite added only 1 year and not an extra 6 months. Can anyone explain why using decimals works for months and days and not for years? Rick van der Lans - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite read-only
Hi, Is there a possibility to open a SQLite database read-only so that database file will not be locked? This request is for updating reasons, to overwrite the database file with a newer version, while users have opened the database only for read. Thanks in advance. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does julianday work according to the manual?
Wikipedia also disagrees with the 24. Nov of 4714 B.C. as start of julian days... http://en.wikipedia.org/wiki/Julian_day (Footnote from that page..) ^ This equals November 24, 4714 BC in the proleptic Gregorian calendar. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does julianday work according to the manual?
info schrieb: Hi, The manual states that the function julianday returns the number of days since noon in Greenwich on November 24, 4714 B.C. That would imply that the statement: Select julianday('-4714-11-24 12:00:00'); Should return 0.0. But it doesn't, it returns -365.0 Does this mean, that the manual should say "since noon in Greenwich on November 24, 4713 B.C.? Or am I missing something? Wikipedia also disagrees with the 24. Nov of 4714 B.C. as start of julian days... http://en.wikipedia.org/wiki/Julian_day Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Where can I find doc on user defined types?
Thanks, jim NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.
Re: [sqlite] UNIQUE constraint on column
On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Shane Harrelson" <[EMAIL PROTECTED]> wrote: > when i try to insert a row into a table that has a UNIQUE constraint > on a column, and I get the SQLITE_CONSTRAINT result code because i'm > inserting a duplicate value, is there anyway to determine the rowid of > the conflict? > > looking at the internals of the VDBE, i found that the rowid of the > conflicting row is pushed on top of the VDBE stack. > > if i'm willing to violate the interface, i can dereference the rowid > from the internals of the VDBE struct. i'd rather not do this... is > there a more formal mechanism for getting this value without having do > to do a separate "select" query? something like > sqlite3_last_insert_rowid() -- ie. sqlite3_last_conflict_rowid()? > The official way to find the conflicting entry is to do a query. SELECT rowid FROM table WHERE uniquecolumn=?; -- D. Richard Hipp <[EMAIL PROTECTED]> Thank you for the reply. I assumed this was most likely the case, and as I said in my original email, I was hoping to avoid having to do a seperate select query for the sake of speed... especially since the value I needed was so tantalizing close in the VDBE struct. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Does julianday work according to the manual?
Hi, The manual states that the function julianday returns the number of days since noon in Greenwich on November 24, 4714 B.C. That would imply that the statement: Select julianday('-4714-11-24 12:00:00'); Should return 0.0. But it doesn't, it returns -365.0 Does this mean, that the manual should say "since noon in Greenwich on November 24, 4713 B.C.? Or am I missing something? Rick van der Lans - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNIQUE constraint on column
"Shane Harrelson" <[EMAIL PROTECTED]> wrote: > when i try to insert a row into a table that has a UNIQUE constraint > on a column, and I get the SQLITE_CONSTRAINT result code because i'm > inserting a duplicate value, is there anyway to determine the rowid of > the conflict? > > looking at the internals of the VDBE, i found that the rowid of the > conflicting row is pushed on top of the VDBE stack. > > if i'm willing to violate the interface, i can dereference the rowid > from the internals of the VDBE struct. i'd rather not do this... is > there a more formal mechanism for getting this value without having do > to do a separate "select" query? something like > sqlite3_last_insert_rowid() -- ie. sqlite3_last_conflict_rowid()? > The official way to find the conflicting entry is to do a query. SELECT rowid FROM table WHERE uniquecolumn=?; -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Obtaining randomness on win32
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > On 1/29/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > The pseudo-random number generator (PRNG) in SQLite is becoming more > > important so it seem good to make sure it is well seeded. > > > I'd like to be able to seed the random number generator so I can have > repeatable > sequences of random numbers (for testing, etc.). The only really random > numbers > are generated from hardware, and that's problematic. You can't make it cross > platform, or even cross processor, without a lot of switches/if > statements/etc. > Please pick a pseudo algorithm of your choice and give us the ability to > seed it? > You already have this capability. Use the redefinably I/O feature to insert your own random number seeder in place of the one that comes built in. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Newbie Question
Stocks is a table in SOC and Options is a table in Options (Opt). - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UNIQUE constraint on column
when i try to insert a row into a table that has a UNIQUE constraint on a column, and I get the SQLITE_CONSTRAINT result code because i'm inserting a duplicate value, is there anyway to determine the rowid of the conflict? looking at the internals of the VDBE, i found that the rowid of the conflicting row is pushed on top of the VDBE stack. if i'm willing to violate the interface, i can dereference the rowid from the internals of the VDBE struct. i'd rather not do this... is there a more formal mechanism for getting this value without having do to do a separate "select" query? something like sqlite3_last_insert_rowid() -- ie. sqlite3_last_conflict_rowid()? thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Obtaining randomness on win32
On 1/29/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: The pseudo-random number generator (PRNG) in SQLite is becoming more important so it seem good to make sure it is well seeded. I'd like to be able to seed the random number generator so I can have repeatable sequences of random numbers (for testing, etc.). The only really random numbers are generated from hardware, and that's problematic. You can't make it cross platform, or even cross processor, without a lot of switches/if statements/etc. Please pick a pseudo algorithm of your choice and give us the ability to seed it? On Unix this is easy - just open /dev/urandom and read out as much randomness as you need. But I do not know how to do this on win32 and wince. The current implementation seeds the random number generator on these platforms by grabbing a copy of the current system time. See the sqlite3WinRandomSeed() function in os_win.c for details. This is not a very good method for seeding a PRNG. Can someone with more knowledge of win32 and wince please suggest a better method for seeding the PRNG on those platforms? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - -- -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com
[sqlite] Re: Hardware problems at www.sqlite.org
Recent outages on www.sqlite.org are due to hardware problems and are unrelated to the recent download abuse. Our ISP will be swapping out servers soon (which will result in another outage of an hour to two, of course.) Thank you for your patience. If you ever need access to www.sqlite.org and you find that it is unavailable, remember that there is a mirror site at http://www.hwaci.com/sw/sqlite The mirror does not support the wiki or trouble tickets or the timeline, but it does hold all of the static pages which include most of the documentation and the source code. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Abuse of the SQLite website
hello drh, list I used to think that only in my country they can forbid knives and forks because someone can misuse them, but obviously it is true for other countries too. Regards, Emily - Спортни залагания! bg.sportingbet.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does SQLite support user-defined data-types ?
Now, I can use user-dfined types by declaring them with SQL create tables. Fine ! But when I try to use the following code to get an object, I get a ClassCastException: Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery( "SELECT * FROM EMP"); while (rset.next()) { Dept dept = (Dept)rset.getObject("DEPT"); } Is it normal or is it due to the fact that SQLite doesn't support SQL3 commands, mainly the getTypeMap() command that maps a Java class with a SQL type ? Notice that my Java class implements SQLData but it seems having no effect. Thanks for your answer, Jerome John Stanton wrote: Sqlite is flexible in typing. You can create user defined types just by declaring them in your SQL which creates tables. Thereafter the API will return the declared type and the actual type so that you can process it appropriately. Jerome CORRENOZ wrote: Hi, I'm starting with SQLite and I would like to know if it is possible to create user-defined data-types through the following SQL command: create type MyType ... ? In fact, I need it to map a database with an object-oriented model, so that I could manage the objects in a ResultSet like it follows: Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery( "SELECT * FROM EMP"); while (rset.next()) { Dept dept = (Dept)rset.getObject("DEPT"); System.out.println("Lieu du dept : " + dept.getLieu()); } Is there a way to do it with SQLite ? Regards, Jerome - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -