[sqlite] Suggestion for syntax enhancement for virtual tables
For creating temporary virtual tables, currently you need to do: CREATE VIRTUAL TABLE temp.t ... Can this syntax be made to work too (similar to creating regular tables)? CREATE VIRTUAL TEMP TABLE t ... or CREATE TEMP VIRTUAL TABLE t ... -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger not working with empty table [reproducible]
On Wed, 31 Dec 2014 22:18:02 -0500 Igor Tandetnik wrote: > Well, the WHEN condition on the trigger is always false when the table > is empty. What else did you expect? > > Perhaps you meant something like > > WHEN new.last_price NOT IN (SELECT last_price ...) Ops, you're right ;). Sorry about that. Thank you very much! -- Linux 3.18.1: Diseased Newt http://www.youtube.com/DanielFragaBR http://exchangewar.info Bitcoin: 12H6661yoLDUZaYPdah6urZS5WiXwTAUgL ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger not working with empty table [reproducible]
On 12/31/2014 9:48 PM, Dâniel Fraga wrote: 3) create a TRIGGER using INSTEAD OF: CREATE TRIGGER [BTC_BRL-TRIGGER] INSTEAD OF INSERT ON [BTC_BRL-VIEW] WHEN (SELECT last_price FROM [BTC_BRL-VIEW] WHERE last_price != new.last_price) BEGIN INSERT INTO [BTC_BRL] values(new.timestamp,new.last_price); END; 4) finally try to INSERT: INSERT INTO [BTC_BRL-VIEW] values(1419991328,925.47); The INSERT will execute, but the table [BTC_BRL] remains empty. Well, the WHEN condition on the trigger is always false when the table is empty. What else did you expect? Perhaps you meant something like WHEN new.last_price NOT IN (SELECT last_price ...) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger not working with empty table [reproducible]
I found a solution to a problem I reported here: http://stackoverflow.com/questions/27711987/sqlite-insert-only-if-price-is-different-from-last-row/ And I notice an SQLite abnormal behaviour (I'm using version 3.8.7.4). Here's how you can reproduce it: 1) create a TABLE and leave it empty: CREATE TABLE IF NOT EXISTS [BTC_BRL](timestamp integer, last_price real); 2) create a VIEW with the last row: CREATE VIEW [BTC_BRL-VIEW] AS SELECT timestamp,last_price FROM [BTC_BRL] order by rowid desc limit 1; 3) create a TRIGGER using INSTEAD OF: CREATE TRIGGER [BTC_BRL-TRIGGER] INSTEAD OF INSERT ON [BTC_BRL-VIEW] WHEN (SELECT last_price FROM [BTC_BRL-VIEW] WHERE last_price != new.last_price) BEGIN INSERT INTO [BTC_BRL] values(new.timestamp,new.last_price); END; 4) finally try to INSERT: INSERT INTO [BTC_BRL-VIEW] values(1419991328,925.47); The INSERT will execute, but the table [BTC_BRL] remains empty. The INSERT will only work when we INSERT at least an entry into table [BTC_BRL] directly. Can someone confirm this? Thank you! -- Linux 3.18.1: Diseased Newt http://www.youtube.com/DanielFragaBR http://exchangewar.info Bitcoin: 12H6661yoLDUZaYPdah6urZS5WiXwTAUgL ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
