Re: [sqlite] Is this a bug? Can't tell from docs....
On Fri, Nov 18, 2011 at 05:30:20PM -0800, Yang Zhang scratched on the wall: I just got bit by some peculiar behavior in sqlite where id int primary key is different from: id integer primary key In particular, sqlite will generate values for the latter but not the former: Well, yes... They're different types. FLOAT and FLOATING POINT have very different behaviors as well (although not for the reasons you might guess). I couldn't find in http://www.sqlite.org/autoinc.html To quote: If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. [...] When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. The docs are quite specific that this is a column of type... and not just any column with an integer storage class. The phrase INTEGER PRIMARY KEY is also a link, which takes you to a section of the CREATE TABLE docs that includes this: Other integer type names like INT or BIGINT or SHORT INTEGER or UNSIGNED INTEGER causes the primary key column to behave as an ordinary table column... or http://www.sqlite.org/datatype3.html any mention of this peculiardistinguishing behavior. Again, the phrase INTEGER PRIMARY KEY appears on this page as a link to the above information that clearly states the column must be defined as an INTEGER. You seem to be assuming the defined type INT and the type INTEGER should be equivalent. Anyway, if this is intentional (as I'm guessing), I wouldn't have been able to tell from the docs - perhaps this would warrant special mention? Almost every use of the phrase INTEGER PRIMARY KEY in the documentation is a link that brings you right to this information. It seems like it would be difficult to make this any more accessible without copying the information to several different places on the website, thus bulking up the docs so much people don't bother to read them and making it much more difficult to maintain. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] can you select series with SQL?
Say we have a table table1 with unique integer field ID. Now we have the following data: ID 1 2 3 4 11 12 13 14 If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can you select series with SQL?
ID 1 2 3 4 11 12 13 14 If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? Sure: select id from yourtable where id = 14 limit 4 order by id desc; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can you select series with SQL?
Bart Smissaert bart.smissa...@gmail.com wrote: If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? select * from MyTable t1 where (select count(*) from MyTable t2 where t2.ID between t1.ID and 14) == 14 - t1.ID + 1; Personally, I'd just run a query like select * from MyTable where ID = 14 order by ID desc; and step through it until the next ID is non-consecutive. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple one
sqlite drop trigger if exists mytrigger; sqlite create trigger mytrigger after insert on result ... begin ... select 'Test2'; ... end; sqlite insert into result values(0,0,0); sqlite Explain the path of select here? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can you select series with SQL?
so the consecutive numbers, going down from 14? Sorry I interpreted the question in a dumb way. Igor is certainly right (as usual). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple one
Matt Young youngsan...@gmail.com wrote: sqlite drop trigger if exists mytrigger; sqlite create trigger mytrigger after insert on result ... begin ... select 'Test2'; ... end; sqlite insert into result values(0,0,0); sqlite Explain the path of select here? What do you mean, path of select? This select isn't going anywhere. The trigger is completely pointless and has no effect whatsoever, if that's what you are asking. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple one
On 20 Nov 2011, at 5:04am, Matt Young wrote: sqlite drop trigger if exists mytrigger; sqlite create trigger mytrigger after insert on result ... begin ... select 'Test2'; ... end; sqlite insert into result values(0,0,0); sqlite Explain the path of select here? Well, it was listed as an initial option o the trigger, so I didn't quite get it either. You generally use TRIGGERs to make changes to the database. They're for things like making sure an author record exists for every book you have. Or for keeping account balances updated. That stuff. Although it's not a violation of the syntax, a SELECT in a TRIGGER doesn't report its results to anything. The only reason I can think of for doing one is it uses a function that has some weird side-effect. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple one
Well, it was listed as an initial option o the trigger, so I didn't quite get it either. On Sat, Nov 19, 2011 at 7:02 PM, Igor Tandetnik itandet...@mvps.org wrote: Matt Young youngsan...@gmail.com wrote: sqlite drop trigger if exists mytrigger; sqlite create trigger mytrigger after insert on result ... begin ... select 'Test2'; ... end; sqlite insert into result values(0,0,0); sqlite Explain the path of select here? What do you mean, path of select? This select isn't going anywhere. The trigger is completely pointless and has no effect whatsoever, if that's what you are asking. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Checking the integrity of FOREIGN KEYs
So does PRAGMA integrity_check (or any other function of SQLite) check to see that FOREIGN KEY records are there ? Or can it in future versions ? Please ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
