Re: [sqlite] CHECK constraints
MS SQL 2000 wil alsol insert (5, NULL) Unless you additionally constrain the fields with not null, but that's a violation the not null constraint, not the x y constraint.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Well, since you put it that way. May I go one step farther and request that this new Dynamic Type also adhere to Bankers Rounding commonly implemented as BCD in other so equipped databases. Please, no. I'll handle rounding in my application, I don't need it on the database. Not all database applications are banking applications. Brad
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
No, but I would bet somewhere near the 90%+ range are commercial applications, requiring the management of fixed point bankers (European or English) rounding numeric data. I've been writing commercial applications for about 20 years now, including 10 years on payroll applications for the Marine Corps and insurance apps for civilians, and I've never used banking rounding. Why should the many suffer for the benefit of the few? Well, first prove it's a many to few relationship, a fact not currently in evidence. Second, why should the database care at all, when there are plenty of libraries out there that can handle it just fine. BCD ain't rocket science. I learned the concept in the Military many years ago. If by BCD you mean Binary Coded Decimal, or Cobol's Comp-3, (which is what *my* military experience remembers it as), I fail to see any connection, other than they did it that way back when..., between the storage format and a method for rounding. Many database engines are beginning to support the data type in some manner, with few calling it BCD. A fact that is completely irrelevant when discussing mathematical operations on those data types. Brad
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? Absolutely not.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
If it should do division the same way for every operation then you must require the result to be able to represent every possible division result. IE the result must be floating point. If you're writing the inner loop for a quake engine there are very good reasons for not wanting to pay I'm not writing a quake look in SQL. No one with any sense would. All this change does is make SQLite even more data type agnostic than it was in the previous version. floating point operations. If you're creating a very large database why should you pay for 80 bits (an IEEE float) of storage when 8 will do just fine? So don't make the field 10 bytes long, make it only 8. SQLite won't care a bit, and will give you the value in whatever format you want. Brad
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
floating point operations. If you're creating a very large database why should you pay for 80 bits (an IEEE float) of storage when 8 will do just fine? So don't make the field 10 bytes long, make it only 8. SQLite won't care a bit, and will give you the value in whatever format you want. Then it's not type agnostic any more. You now have an 8 byte numeric and a 10 byte numeric. Which is no different than integer and real. From SQLite's standpoint it is agnostic. SQLite neither knows nor cares what is actually stored in the column; that's up to your application to deal with. Whether those 8 bytes represent a generic number, a real value, an integer, a floating point value, date or text is for your application to determine, not the DB engine. At least from SQLite's perspective.
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
The problem with the grade-school rule is that, assuming the last digit is uniformly distributed, you'll be rounding up 5 times out of 9 and rounding down 4 times out of 9. No, if the last digit is uniformly distributed, then 0 is as likely as any other. You round down on 0, 1, 2, 3 4 and round up on 5, 6, 7, 8 9. The fact that rounding down on 0 is the same as the unrounded number isn't significant.
Re: [sqlite] SQLite as a Windows kernel tool
Look I'm certain you mean well, but the rest of us are pretty busy using one of the best small footprint databases on the planet. That means we are way too busy to nit-pic a good product to pieces, just because it won't compile clean using Mickeysoft's latest and greatest. It's not a nit-pic[sic] when one's compiler (and I saw no mention of the compiler vendor in the original poster's note, but I don't have the start of the thread) issues a warning about potentially unsafe behaviour. There's a reason those warnings are issued. Such issues can cause hard to find problems in programs. Code review is a valuable tool in software development. I believe the original poster's intent was to note that if an implicit conversion is correct for that section of code, then what is the down-side of changing those to explicit casts? I'm aware that the time spent chasing them down and analyzing each one is a major task, but I get the impression that the OP doesn't feel qualified to make those changes, otherwise he would have tackled it. I believe he intended to bring the subject to the attention of DRH, in case these are actual problems, not to whine about the code base. us all, and won't come off as such an irritating whiner. As opposed to...? Brad
Re: [sqlite] .import null values
sqlite create table t (a, b integer, c real); this is a great explanation, but, why does 1,2,3 return as text,integer,integer and not integer,integer,integer? I suspect that it's because no type was specified for field 'a', so everything is interpreted as text. Brad
Re: [sqlite] Survey: NULLs and GROUP BY
CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER); INSERT INTO t1 VALUES(1,2,1); INSERT INTO t1 VALUES(NULL,2,2); INSERT INTO t1 VALUES(1,NULL,4); INSERT INTO t1 VALUES(NULL,NULL,8); INSERT INTO t1 SELECT * FROM t1; SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3; Microsoft SQL Server 2000 (only difference is I used a temp table): (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (4 row(s) affected) a b --- --- --- 1 2 2 NULL 2 4 1 NULL 8 NULLNULL 16 (4 row(s) affected)
Re: [sqlite] Unlucky number for the ROUND function
From: Bob Dankert Using an older version which I compiled, I get 9.9, though it seems it should round up to 10.0? This may be dependent upon the math library linked into the application by the compiler used to build SQLite. Some libraries appear to now be using so-called banking rounding (though there are other names), where a 5 digit is rounded up or down depending upon the digit immediately to its left. If that digit is odd, it rounds one way, if even, the other. It looks like in this case, 9.95 would round to 9.9, while 9.85 would likely round to 9.9 as well. Try rounding 9.85 and see what you get. Brad
Re: [sqlite] proper SQL DELETE syntax
DELETE from timeline where timeline.name=del_timelines.name and timeline.last_change=del_timelines.last_change; 'Twere me, I'd do it like this: delete timeline from del_timelines wheretimeline.name = del_timelines.name and timeline.lastchange = del_timelines.last_change Brad
Re: [sqlite] proper SQL DELETE syntax
'Twere me, I'd do it like this: delete timeline from del_timelines wheretimeline.name = del_timelines.name and timeline.lastchange = del_timelines.last_change I guess you mean... from timeline,del_timeline ... No, I meant what I wrote, except for the difference in lastchange to last_change. But regardless, this form of the DELETE command does not (seem) to work in SQLite. Perhaps it is not part of the SQL standard. That's quite possible. I didn't test it using SQLite, but that form *does* work on MS SQL Server.
Re: [sqlite] Insert all rows from old table into new table but in sorted order
You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the you misinterpreted my problem, I want to add all rows of old table into new table but with sorted order I don't want to fire another query (select * from newtable order by desc no ) to give sorted rows, I want to insert all rows in sorted order into new table. As the original responder said You can't do that. The records may or may not be inserted into the target table in the physical order you specified on the insert query. The physical order in the target table depends on how the SQL engine decides to write them. The order in which rows are returned from a query that doesn't specify order is undefined, meaning they may be in the order they are in the table, or some other order that is the result of the SQL engine's optimization. In other SQL engine's, you can force the physical order of the rows by using what MS SQL Server calls a clustered index, which isn't really an index at all, but rather a physical ordering of the rows in a table. I haven't seen anything about SQLite supporting clustered indexes, but you might check the www.sqlite.org website to see if it does or if there are any plans to include it in the future.
Re: [sqlite] SQLite.NET in-memory database creation
Stupid question, but how can i create an in-memory database with SQLite.NET provider? When i use Data Source=:memory: i get an exception that the specified file isn't found. If you are using the ADOSQLiteDotNet provider from SourceForge, add New=True to your connection string.
Re: [sqlite] SQLite.NET in-memory database creation
Stupid question, but how can i create an in-memory database with SQLite.NET provider? When i use Data Source=:memory: i get an exception that the specified file isn't found. If you are using the ADOSQLiteDotNet provider from SourceForge, add New=True to your connection string. To clarify, my connection strings for in-memory databases look like this: Data Source=:memory:;Version=3;New=True
Re: [sqlite] Error : Library routine called out of sequence
I am facing one weird problem using SQLite. Many times I get an error Library routine called out of sequence while executing query Create table SOURCEFEED (SOURCEFEED,STATUS) values ( ABC,0) , I'm not getting the reason behind this. Why am I getting this error? My application is a multithread application and executes query from a worker thread. Are you trying to create a table or insert values into an existing table? I've never seen anything in SQL that suggests that you can do what you appear to be trying to do with that query, i.e. insert rows into a table while creating it.
Re: [sqlite] a couple notes on cross-compiling sqlite3 for the PPC
p.s. as a side note, the whole point of this exercise is to build a *really* small footprint DB for an embedded system for which space is at a premium, and sqlite came highly recommended. If you look on SourceForge, there is a project called adodotnetsqlite, http://sourceforge.net/projects/adodotnetsqlite/, and the programmers there have mentioned a version of SQLite compiled for handheld platforms. I know that they use a version of that for the .NET provider they are writing.
RE: [sqlite] a couple notes on cross-compiling sqlite3 for the PPC
ok, i just took a quick look there and, personally, there's just a wee bit too much .vcl and .vcp and .net and so forth for my comfort level. :-P i'll take a shot at the full sqlite and see where that takes me, but i'll keep the alternative in mind. thanks. Understood, I was refering to the fact that he was compiling for the platform. I was thinking you might get some pointers from how he does it.
Re: [sqlite] preventing text to integer conversion of bindvariables in perl
Simple illustration: sqlite3 test 'create table t ( k text unique, v text);' perl -e 'use DBI; $db = DBI-connect( dbi:SQLite:dbname=test ); $db-do( qq[REPLACE INTO t VALUES(?,?);], undef, key, 0325763213);' sqlite3 test 'select * from t;' returns: key|325763213 It looks like perl is making this conversion for you. SQLite does not do this. As a work-around, consider prepending a single 'x' character to every v column entry then strip of the 'x' before you use it. Knowing next to nothing about Perl, and thus feeling eminently qualified to comment on it, what would happen if you wrapped the number in single quotation marks instead of double ones, or put single ones inside the double ones, e.g. '0325763213' or '0325763213'?
Re: [sqlite] Index with two columns
Hello, please, is there any way to make SQLite use an index on two columns when I want to select all rows which have some combination of the two columns? SELECT * FROM PointFeature WHERE DsetId=203 AND SectId IN (4,400); I can't answer the question you asked, but I will point out one thing. Many SQL engines attempt to optimize index use in queries. For instance, in MS SQL Server, your query might *not* use the second column in the index if using the index would take perform more poorly than not using it. Using an index is not always faster than doing a table scan. If there are only a few rows where DsetId is equal to 203, the table scan of that subset could very well be faster than using an index to look up the values. In our MS SQL Server environment, we usually don't even bother to create indexes on more than one field if there are going to be less than a few hundred rows in a subset of a query like that. It takes the server longer to do the index lookup than it would the table scan.
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
So who out there needs a value of K larger than 2000? What is the largest K that anybody is using? Who would object if I inserted a limit on K that was in the range of 1000 or 2000? I have never, in SQLite or any other SQL DB product I've worked with, created or used a table with more than a couple of hundred fields. The largest table I currently work with has less than 100, and that's too many, mostly because of poor design.
RE: [sqlite] Version 3.1.3 is a headache
The last one is really annoying and I can't believe the auto-tests could have missed it ... create table a (id, x); create table b (id, y); insert into a values (1,1); insert into b values (1,2); select * from a inner join b; column names returned: id,x,id,y You'll get the same result when you execute that query in SQL Server 2000. I suspect you'll get the same result from a lot of SQL DB systems. How am I supposed to use such column names? The same way every other programmer does, by using alias on the fields that you are interested in, and leaving the others out of it.
RE: [sqlite] Importing a CSV file
That should have been obvious if you'd attempted to select something from the table with headers turned on in the command line utility. Thank you so much, and sorry for sending this stupid question. No apologies necessary, except for me. Re-reading that passage, it looks much harsher than I meant it. Sorry about that. I merely meant it as a suggestion for *next* time something like that happens. Sometimes things ar just too obvious to be seen (in french, we say they are on one's nose). In English, right in front of one's nose. :) In fact, as the table was empty, I did not know how to start the troubleshooting. I tried to dump the table, but sqlite keeps the exact formatting of the sql instruction : In the command line utility, the .header option controls the display of the column names when you do a select. I didn't notice your problem right off either, so I just created the table and selected from it to see the extra column.
Re: [sqlite] Importing a CSV file
CREATE TABLE All_Chr_CxCb_atleast1 ( TCIDTEXT, STRAND TEXT, CHROMOSOME TEXT, START_POS INTEGER, STOP_POSINTEGER, REPRESENT_POS INTEGER, TKIDINTEGER, REPRESENT TEXT, SYMBOL1 TEXT, SYMBOL2 TEXT, LOCUSLINK TEXT, GO, BLOB, CAA INTEGER, You've got an extra comma here after the GO field. That makes the parser think your trying to create field named GO, followed by one named BLOB, then one named CAA. Remove the comma. That should have been obvious if you'd attempted to select something from the table with headers turned on in the command line utility.
Re: [sqlite] SQL Question
CREATE TABLE teams (id,name);
CREATE TABLE games (id, date, team1_id, team2_id, result);
team1_id and team2_id refer to the id in the teams table.
1. What query would be best suited to get an output so that the
output would contain the teams names (not only the id) and the
dates and results?
select team1.name, team2.name, games.date, games.result
fromgames
inner join teams team1 on games.team1_id = team1.id
inner join teams team2 on games.team2_id = team2.id;
2. What query would be best if I want to search for games that
two specific teams had played. For example, I want all the games
that the teams with the names 'bla' and 'blub' played against
each other. Is there any way to do that?
select team1.name, team2.name, games.date, games.result
fromgames
inner join teams team1 on games.team1_id = team1.id
and team1.name in ('bla', 'blub')
inner join teams team2 on games.team2_id = team2.id
and team2.name in ('bla', 'blub');
I didn't check spelling or stuff, but the syntax should be mostly correct.
RE: [sqlite] feature request
Reading the 'unsupported features page' there's a feature request about allowing the + and - join syntax like oracle has. It would allow more easy readable and writable queries although I know it's not quite standard. SQLite used to have this (I read) but has been removed. I think I'd not find such a feature useful. Adding non-SQL standard functionality to SQLite is a good idea when it provides some clearly needed functionality, or when it supports the design goals of the product. However, simply adding support for a different *notation* for already existing functionality, i.e. joins, would seem, IMNECTHO, to be not worth the effort. For one thing, it adds code without adding any new real functionality. That increases the size of SQLite, thus going *against* one of the product's goals.
Re: [sqlite] Getting a list of most recent queries
Hi there, Is it possible to get a list of say, the last 10 executed SQL commands? This would be very usefull for debugging purposes for example. The FAQ doesn't answer the question. If you are using the command line utility in a DOS shell in Windows, the up arrow key will show you every command you've executed. I'm sure there's a limit, but I don't know what it would be. If you are talking about SQLite in general, I believe the answer is only if you save 'the last 10 executed SQL commands' you've performed. It shouldn't be difficult to set up a table to hold those, and a wrapper function that updates that table every time a query is run.
Re: [sqlite] Getting a list of most recent queries
If you are talking about SQLite in general, I believe the answer is only if you save 'the last 10 executed SQL commands' you've performed. It shouldn't be difficult to set up a table to hold those, and a wrapper function that updates that table every time a query is run. Well, I'm using the SQLite ADO.NET provider, which has the actual calls and such nicely wrapped up. But anyways, thanks for the help. That still falls under the SQLite in general classification. I'm using an ADO.NET provider as well, and there's nothing native to it. You can still set up a wrapper function and a table, though.
RE: [sqlite] SQLite3 and version 2.1 DBs
I have built sqlite3.lib from the source tarball, and when I try to create a statement in my 2.1 DB (created via SQLite Two point one? Is that version not over three years old, or did you mistype? What GUI tools do people use to manage their 3.x databases if that's the case? Haven't found the need to use a GUI tool yet, however, this one has been posted on the list a couple of times. I don't know if it's been updated to the version 3 DB format, http://www.kraslabs.com/sqlite/ It only says sqlite.dll version 2.7.2 or higher on the web site, so it might not work with 3.
Re: RE(1): [sqlite] SQLite Advocacy
11. By using SQLite you eliminate a possible source of virus infection. SQL server has been targeted in at least one major virus outbreak. Completely and totally false. Applications that use SQLite can be corrupted and infected by viruses just like any other executable file on the file system. If you install MS SQL server you run a network based service that is vulnerable to attack. If you run Sqlite you don't run any service and thus are invulnerable (to network service based infections). Not relevant. No reference to network service based infections was made in the original post. Yes, any executable can be infected, but that's a meaningless statement since you can't have any database without executable code. Then saying that SQLite won't be a source of virus infections is also meaningless, since you can't have any [SQLite] database without executable code, which executable code can be infected.
Re: [sqlite] Finding duplicate records
I'm new to SQL, and SQLite, and I find myself needing to identify duplicate records in a SQLite table (there are about 2K duplicates in a 36K row table). Any suggestions or magic SQL queries appreciated :-) Here are a couple of strategies I've used successfully for finding and potentially removing duplicate records. It assumes a primary key field that is *separate* from the fields being checked for duplication. This example also assumes that I only want to check for duplication on two of the fields in the table: field1 and field2. Assume a table such as follows, that might included duplicate records. create table checkedtable ( priKeyinteger primary key, field1char(10), field2char(10), other possible fields ); and a temporary table to hold the duplicates create temp table dups ( field1, field2, reccount, keyvalue ); The first method is probably the simplest: insert into dups (field1, field2, keyvalue) select field1, field2, max(priKey) from checkedtable group by field1, field2 I don't know if this one works on SQLite, as I've never had reason to try it. (I've learned a fair bit about DB design in the last couple of years before starting with SQLite, so duplication that needs correction is less of a problem now :) It does work on SQL Server 2000, though. The second method is one that I used before I understood that GROUP BY could be used for more than counting. :-) Get all the records that have duplicate information and put them into the temp table: insert into dups (field1, field2, reccount) select field1, field2, count(priKey) from checkedtable group by field1, field2 having count(priKey) 1 Now, get the key for one of them: update dups set keyvalue = checkedtable.priKey fromcheckedtable where checkedtable.field1 = checkedtable.field1 and checkedtable.field2 = checkedtable.field2 Using either of these methods, the temporary table now contains key values that point to one of each set of duplicated records in the original table. It can be used to delete them, archive them, change them, whatever, by joining back to the original. Note that the code presented here is off the top of my head, and not tested, so there may be syntactic errors that I didn't catch while writing it. With any luck, there aren't any logic errors. Hopefully, it will give you a starting point. Brad
Re: [sqlite] Database corruption and recovery
Alex Chudnovsky said: I am using ADO .NET wrapper (http://sourceforge.net/projects/adodotnetsqlite/) and I am not sure if I can just drop in new .DLL -- the wrapper was not updated for a while now :( FWIW, there is active discussion going on the forums. I remember discussion when the wrapper changed to allow dropping in the SQLite dll. He did that specifically so he wouldn't have to rebuild the wrapper when SQLite changed, aside from major functionality changes. I feel moderately bad about that, because I have recommended that particular wrapper in the past. To be honest, I'm not a giant fan of ADO.NET. It seems to have taken us away from the database-independent that we got with ADO and given us a differnt object to use for each different database we connect to. The use of the provider specific objects might have that effect, but one can use the more generic ODBC and OLEDB provider objects if one wishes to do so. The specific ones are just optimized (one hopes!) for the particular DB. Since they all implement the same interface, I've taken to just coding for the interface in my program, and choosing which underlying object to use at run time based on what I'm connecting to.
RE: [sqlite] How do I create a field with default current_timestamp
Create table TemperatureData ( Tempt real, TimeStamp default CURRENT_TIMESTAMP); Insert into TempertaureData values( 40.2, NULL); Try specifying the Tempt field and it's value only. SQLite may be interpreting the NULL as a supplied value and thus not inserting the default value. Insert into TemperatureData (tempt) values(86.0); Don't have SQLite on the machine here, or I would check it myself.
Re: [sqlite] Is there any way to enable recursive triggers?
CREATE TRIGGER deep BEFORE DELETE ON list BEGIN DELETE FROM list WHERE id=old.next; END; This trigger is guaranteed to terminate because it will eventually run out of entries to delete. But given any recursion limit, we can always construct a case where the trigger will want to run longer than the limit. So do we put recursion limits on UPDATE and INSERT triggers only and let DELETE triggers run as long as they like? That works as far as I know, but it seems kind of arbitrary. Under what circumstances might one *want* to update or insert recursively? What circumstances that can't be handled by a properly constructed query or set of queries? I can see the delete, but even that can be handled another way. I'm aware of the advantages of triggers, and the absence of stored procedures makes them even more valuable, but I'm curious. Surely somebody else has been down this road before me and can offer a little guidance? FWIW, I'm told by our DBA that SQL Server 2000 has a setting that allows or disallows recursive trigger execution. When disallowed, triggers apparently just don't recursively call themselves, even if they are designed to do so, i.e. they won't generate an error. When allowing recursion, he's not sure whether there's a limit, some internal checking that attempts to discern when reasonableness has been exceeded or if it will just merrily recurse away until stopped. And he's not willing to test it. :-)
Re: [sqlite] New to Sqlite
My initial question is this: I have a set of SQL statements in a file that builds my desired database. In reading the command line options for sqlite3 it appeared the .read command would read this file and execute the statements in the file specified. Yet when I do that, I see the statements come flying across my command window but there's nothing to indicate success or failure After running the script, can you select from the tables created? There should be nothing in them, of course, but you should be able to execute a select * from table; without generating an error. IME, the SQLITE3.EXE program reports errors when it can't create the tables, or encounters problems, so no message should mean success. and the database file I specified stays at zero length. So obviously that doesn't work. Not so obvious to me. Are you looking at the file size while the SQLITE?.exe program still has the file open, or *after* exiting the SQLITE command line back to your shell? Can anyone please tell me if there is any way to perform the batch processing of the SQL statements in my file? Unless I'm misunderstanding what you've done, what you've done works for me. It's how I check the validity of my creation, and other, scripts. Brad
Re: [sqlite] VACUUM function problem
I have deleted some objects in my database. Now I have objects at id (INTEGER PRIMARY KEY) =1,3,4,5,7,8,9,16,17,20 . id=2,6,10,.. are empty. I want to defrag the database so that I have objects continuously at id=1,2,3,4,5,6,7,... The first question I'd have to ask is why you want to do that? If those primary keys are referred to by foreign keys in child tables, you'd have to change all those values as well. With a large number of child tables, this seems like more work than it's work. Any mistakes and you destroy your database's referential integrity. Is the vacuum function not the right function? I don't believe so. I think your only recourse is to define another table with the same structure, then select all the records from the old one into the new one, delete the old one, then do the same back again to the old name. Unless there's some pragma or something that allows one to rename a table.
Re: [sqlite] coding style
create view myview as select t1.a a from t1 inner join t2 on t1.a=t2.a; create table problem as select * from myview; Change this last line to: create table problem as select a as a from myview; That creates the problem table with just a as the field name.
Re: [sqlite] field=null vs. field isnull
i've read the 'null handling' section and still not found the answer to this question: why should 'select * from tbl where field=null' be any different from 'select * from tbl where field isnull' The short answer is because it's different. A longer answer would reference the SQL-92 standard, talking about how any comparison involving null will resolve to false, including null = null. Null is defined to be not equal to anything, including null. You *must* use is null to check for null, that's the way the SQL language is implemented, and it's the way that all SQL-92 compliant implementations are supposed to behave.
RE: [sqlite] SQL IS operator.
You can already us the IS operator with a right-hand side of NULL. For example: x IS NULL or x IS NOT NULL. What I am proposing is to expand IS so that the right-hand side can be an arbitrary expression. Like this: x IS 5 or x IS NOT y. The motivation for this change is so that one can compile statements that use ? as the right-hand side of IS and then insert NULL or a value as appropriate. In the last 5 years coding against SQL Server, I've *never* needed such an operator. I've gotten so used to coding SQL statements to properly check for null before checking for a value that I don't even notice the effort anymore. That said, I like the idea. There are somethings that would be much easier. Is there any precedent for it in the SQL-92 standard, or is it something completely new?
Re: [sqlite] Update multiple columns from subquery
Peter Bartholdsson wrote: Is there some way to write this query in SQLite? UPDATE groups SET (fileCount, size) = (SELECT count(id), sum(size) FROM files where groupId = 15) WHERE groupId = 15; Sadly, no. SQLite only allows a subquery to return a single value, not a tuple. I haven't seen too many others that would do that either, so no great loss. However, will SQLite do this? update groups setfilecount = result.count, size = result.size from ( select count(id), sum(size) from files where groupid = 15 ) as result where groupid = 15; Alternative, if SQLite won't do that, select the files info into a temporary table, and use the temporary table in the FROM clause above. create temp table result as select count(id), sum(size) from files where groupid = 15; update groups setfilecount = result.count, size = result.size from result where groups.groupid = 15 andresult.groupid = 15; It's not one statement, but it should be executable with one call, right?
Re: [sqlite] Why this SQL does not work?
select ID, (select SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID from B [...] It mentions, among others: Variable subqueries Subqueries must be static. They are evaluated only once. They may not, therefore, refer to variables in the main query. I'm not the original poster.. but I'm trying to come up to speed on SQL/sqlite and I'm having some trouble understanding this... What is an example of a static subquery? The above is an example of a static sub-query. It's a query within a query. And how would one re-write this to eliminate the subquery? select B.ID, A.SERVER_ID from B inner join A on B.GROUP_ID = A.ID or select B.ED, A.SERVER_ID fromA, B where A.ID = B.GROUP_ID I think.
[sqlite] Case Sensitivity
Is there a setting that I can use when opening a database, or when creating it, or whatever, so that queries against textual fields in the tables are *not* case sensitive? Or is the fact that SQLite is typeless going to get in the way of this? For instance, I want to be able to do: select lastname, firstname, mi from participant where lastname='smith' and firstname = 'john' and get the same result set as I would from: select lastname, firstname, mi from participant where lower(lastname)='smith' and lower(firstname) = 'john' We have no control over how the clients send us the names, and we don't attempt to correct the casing of names, so they could be in any combination of case.
Re: [sqlite] Case Sensitivity
definitions. There is already a built-in NOCASE collation that works for US-Latin characters. You'll need to do something more advanced for I am using US-Latin characters. international characters sets, however. If you need i18n support, you can still use the built-in NOCASE collation as an implementation guide (it isn't hard) or to experiment with the COLLATE clause on table definitions. I thought I saw mention of that somewhere, but I couldn't find any occurances of NOCASE in the syntax document. I was hoping for some database wide setting (using 3.x). Is there not one? I presume using the collate clause is something on the order of: create table participant ( partkeyinteger primary keynot null, lastname varchar(40)not null collate nocase, firstname varchar(40)null collate nocase );
Re: [sqlite] Case Sensitivity
Brass Tilde wrote: I was hoping for some database wide setting (using 3.x). Is there not one? The default collating sequences is called BINARY. You can redefine BINARY to be the same as NOCASE if you want. Then when tables use the default collating sequence, they will use NOCASE instead of BINARY. I presume that would be accomplished using the same sqlite3_create_collation() API that I'd use to create an entirely new one? The wrapper I'm using doesn't appear to expose that API (or I haven't stumbled across it yet). What effect does redefining the BINARY sequence have on numeric data in the tables? I presume using the collate clause is something on the order of: That is correct. I suspect I will be using the COLLATE clause. It's easier for me to have multiple copies of the creation SQL statement for the different backend platforms, isolating the non-standard SQL statements to that one place, than it is to have different operations for the different backends (i.e. find works one way for one DB, and another way for another).
[sqlite] Unexpected results from SQLite3.exe
Running Win2K, with all current patches, I use the SQLite3.exe program to create a database named test.db using the .read command. The file test.sql contains the following table definition: create table Author ( AuthorKey integer primary key not null, LastName varchar (40)not null, FirstName varchar (20)null, Middle char(5) null ); create index IX_Author_1 on Author(LastName, FirstName); C:\dbtestsqlite3.exe test.db SQLite version 3.0.4 Enter .help for instructions sqlite .read test.sql Now, I execute a few inserts one at a time, and after each one do a select last_insert_rowid() from the table to get the last key I inserted. It works fine the first time, but every subsequent time, I get as many rows (all with the same value, the last rowid) returned as there are records in the table. I later added two rows at once, and once again got as many rows returned as there are records. A transcript is below. sqlite insert into author (AuthorKey, LastName, FirstName, Middle) values (null, Smith, John, Q. ); sqlite select last_insert_rowid() from Author; 1 sqlite insert into author (AuthorKey, LastName, FirstName, Middle) values (null, Jones, James, J. ); sqlite select last_insert_rowid() from Author; 2 2 sqlite insert into author (AuthorKey, LastName, FirstName, Middle) values (null, Doe, Jane, F. ); sqlite select last_insert_rowid() from Author; 3 3 3 sqlite insert into author (AuthorKey, LastName, FirstName, Middle) values (null, Babe, D., Licious ); sqlite insert into author (AuthorKey, LastName, FirstName, Middle) values (null, Horndog, M., F. ); sqlite select last_insert_rowid() from Author; 5 5 5 5 5 Is this just the SQLite3.exe doing this, or is it the library itself? I don't use the library directly, rather I use a wrapper written by someone else, so I can't test that directly myself. The wrapper I'm using doesn't return more than one value (though I don't know if that's because the library doesn't, or because something in the wrapper is filtering it.)
Re: [sqlite] Unexpected results from SQLite3.exe
Brass Tilde wrote: sqlite select last_insert_rowid() from Author; 5 5 5 5 5 There are five rows in the table Author. One row of the result set is generated for each row in the table. Thus you have 5 rows of output. This is as designed. I wondered about that. Thanks for confirming. You probably meant to say: select last_insert_rowid() That is, without the FROM clause. And as long as I do that immediately after inserting to the table, with no intervening inserts to other tables, I suspect that will work. Fortunately, I have the luxury of writing a single user, single threaded app. Thanks again.
Re: [sqlite] Concerns about checkin 1879
More sophisticated programs that want more control can still have it, even with check-in [1879]. If thread A is trying to COMMIT and thread B is trying to UPDATE, you will get a busy handler callback from thread A and an SQLITE_BUSY reply from thread B. But the SQLITE_BUSY return from thread B did not clear any locks. There is nothing that prevents the program from rolling back thread A then reissuing the UPDATE statement of thread B. OK, maybe I'm just not getting something here, but why on earth would I want to roll back a commit in order to allow an update? Shouldn't it be the other way around? If thread A has completed it's update, and is now in the process of committing that change, why does that *not* take precedence over a thread that is just now starting it's update?
Re: [sqlite] Database locked
I am trying to import a csv file (ip-to-country to be especific) to a table on sqlite db. Version 2.8: COPY tablename FROM 'filename' USING DELIMITERS ','; You weren't a Clipper/XBase programmer at one time, were you? :-
Re: [sqlite] OT: Reply-To Munging Considered Useful
Better: Somebody please write me a simple, secure, mail handler to replace qmail/ezmlm that lets each user decide for themselves whether they want a Reply-To back to the mailing list or unmunged headers. I'll be happy to supply volunteers with a detailed specification of what I am looking for in a mail system. Funny you should mention that. I've been looking for one of those myself, and had half decided to write my own. Problem is, I don't know anything about mail processing, so it's going to be a long road. If you don't mind though, I'd like to see those specs. Even though I can't promise you anything, I'm interested in seeing other folks' ideas. If you have them already put together, of course; I don't expect you to create them for me when I can't deliver what you're looking for.
RE: [sqlite] Create Table Issue.?
CREATE TABLE [SPMBlock] ( [SPMId] [int] identity(1,1) NOT NULL , Does identity do the same thing as it does under MS SQL Server? I thought the way to get an identity field in SQLite was to define it as integer primary key. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Re: Life of a cursor
After that, it's of no use to keep the database open, so it closes it. And sends the results obtained back to the client. Wow! This is the first I hear that. Wouldn't that be an expensive operation? (opening, closing, reopening...) For an embedded database, such as SQLite, yes it would be an expensive, and unnecessary, operation. The open/use/close method is well suited for a client/server engine, however. Many client/server RDBMS engines, such as MS SQL, and I'm pretty sure Oracle, use so-called connection pooling, where the connections on the server aren't actually deallocated when the client closes them, but are instead maintained in a pool on the server. The next client that opens a connection may get a new one, or it may get a reference to a previously used one. If your application holds a connection open even if it has no further use for it, or won't need it for some extended period of time, then that connection's resources aren't available for some other application, which means the server has to allocate more resources for a new one, using up time and memory on the server. The server side resources for the connection being held open aren't actually being used for anything, so they are wasted. Imagine every application that connects to the server doing that. The theory is that allocating and deallocating the resources used by a connection object on the server are actually more time consuming than simply reinitializing the resources associated with an existing object, so not having to create a new one *every* time a client connects saves time on the server and makes the applications that much scaleable. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Question about expected query result??
On MS SQL Server 2000, one can pass a field name to the COUNT function, and though I haven't yet seen any difference in the results between the two, the queries run faster with COUNT(field) than with COUNT(*). COUNT(fieldname) provides the count of rows where the data in 'fieldname' is non-null. COUNT(*) provides the total count of rows: Ah. I guess that pretty much explains that. I seldom do counts without a WHERE clause of some sort that would preclude counting nulls in the first place. Thanks. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] suggestion
I'm new to sqlite but as far as I can tell it does not support ALTER TABLE command. I could find that useful in future versions. That's right. ALTER TABLE would be very helpful. Are there any reasons it is not implemented yet ? What do you mean by ALTER TABLE? The ability to add new columns or constraints? That can be implemented by renaming the original table, creating a new table with the new structure, then importing the contents of the original table into the new one. In fact, with the exception of adding a new column to the *end*, this is precisely how MS SQL 2000 implements adding a new column to a table. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] 'ALL' Not Supported?
I've searched around quite a bit for an answer on this but I haven't found anything yet. As far as I understand the following is a valid SQL-92 query: Have you tried it on some other SQL-92 compliant DB and had it work? I've never seen all used in quite that way before. Admittedly, I've only used SQL Server, Oracle, MSDE, Access and now SQLite, so maybe that's just me. sqlite select model from (select model,price from laptop where hdsize = all (select l.hdsize from laptop l)) MaxSize where MaxSize.price 2500; SQL error: near all: syntax error If you check out the relevant section of the SQL as Understood By SQLite, http://www.sqlite.org/lang.html#select, you'll see that the use of the all keyword is somewhat different from what you've used. Select top 1 hdsize Fromlaptop Where price 2500 Order byhdsize desc -- save value from above query Select model Fromlaptop Where hdsize = savedvalue Yeah, you could combine those two, but how much time do you want to spend coming up with a geeky way to do? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] OK to drop support for legacy file formats?
On 6 Feb 2004, at 14:05, D. Richard Hipp wrote: If you use a modern version of SQLite (version 2.6.0 through 2.8.11) to open an older database file (version 2.1.0 through 2.5.6) the library will automatically rebuild all the indices in the database in order to correct a design flaw in the older database files. I am proposing to drop support for this auto-update feature. I can see both sides of this. On the one hand I'm a *big* supporter of keeping SQLite small, but on the other hand I have a project with currently over 50,000 sqlite databases on a large (terabyte) filesystem. Upgrading each one with a utility (and the associated downtime) would be a bit of a nightmare. Upgrading them on a per-access basis like the current implementation would do is a bit more agreeable to me. My understanding is that SQLite has had this auto-update feature since version 2.6.0. If I understand correctly, you should only have a problem if you are *now* using a version prior to that, and go from that version directly to 2.8.12 or later. If you've kept your version of the db engine current, or at least have upgraded to 2.6.0 or later, then all of your databases should now have their indices updated by now. Have I misinterpreted something? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] OK to drop support for legacy file formats?
If you use a modern version of SQLite (version 2.6.0 through 2.8.11) I am proposing to drop support for this auto-update feature. Beginning with 2.8.12, if you attempt to open a database file If the opinion of someone who's just started using the product, and has no intention of using the older versions of which you speak, I shan't miss the feature either. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
