[sqlite] INSERT OR IGNORE safety?
I wonder if I'm on safe side when I use, say: INSERT OR IGNORE INTO foo (foo) VALUES (?1); INSERT INTO bar (foo) SELECT f.rowid FROM foo f WHERE f.foo = ?1; (within a transaction) against the following schema: CREATE TABLE foo (foo TEXT NOT NULL); CREATE INDEX foo-unique ON foo (foo); CREATE TABLE bar (foo INTEGER NOT NULL REFERENCES foo); I mean, if the first INSERT fails for the reason other than the UNIQUE constraint set, the second one will fail too, and it's the success of the second one that I was interested in the first place. JFTR, the other possible ways to implement the first INSERT that I'm aware of, are: INSERT INTO foo (foo) SELECT ?1 WHERE NOT EXISTS (SELECT 1 FROM foo f WHERE f.foo = ?1); INSERT INTO foo (foo) SELECT ?1 EXCEPT SELECT f.foo FROM foo f; -- FSF associate member #7257 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE safety?
Ivan Shmakov wrote: I wonder if I'm on safe side when I use, say: INSERT OR IGNORE INTO foo (foo) VALUES (?1); I mean, if the first INSERT fails for the reason other than the UNIQUE constraint set http://sqlite.org/lang_conflict.html says: | The ON CONFLICT clause applies to UNIQUE and NOT NULL constraints (and | to PRIMARY KEY constraints which for the purposes of this section are | the same thing as UNIQUE constraints). The ON CONFLICT algorithm does | not apply to FOREIGN KEY constraints. If it fails for any other reason, you get a normal error. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE safety?
Clemens Ladisch writes: Ivan Shmakov wrote: I wonder if I'm on safe side when I use, say: INSERT OR IGNORE INTO foo (foo) VALUES (?1); I mean, if the first INSERT fails for the reason other than the UNIQUE constraint set http://sqlite.org/lang_conflict.html says: | The ON CONFLICT clause applies to UNIQUE and NOT NULL constraints […] If it fails for any other reason, you get a normal error. The point is that I have a NOT NULL constraint as well: CREATE TABLE foo (foo TEXT NOT NULL); (There was a missing UNIQUE in CREATE INDEX, BTW.) However, as it's clear to me now, the second INSERT as specified won't raise an error should there be no ?1 in foo. Thus, I need something like the following instead: INSERT OR IGNORE INTO foo (foo) VALUES (?1); INSERT INTO bar (foo) VALUES ((SELECT f.rowid FROM foo f WHERE f.foo = ?1)); Or is there a better way to ensure that the inner SELECT either returns a single row, or fails? TIA. -- FSF associate member #7257 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] natrual join (not natural)
Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/11/12 18:25, Mark Brand wrote: You mentioned that this extra AS might help avoid ambiguities and errors. The only example of this you mentioned was where aliases names with spaces are not quoted. Do you have cases of this in mind that wouldn't be syntax errors anyway? In other words, I'm looking for evidence that missing AS really is in the same category of trailing semicolons, etc. Table originally has a column named price. For various reasons they add a new column named price new. Since you can't use bindings in queries they will either have been written out by hand or composed (eg sprintf). If the latter code doesn't quote the names then the query includes price new which selects the wrong column and overwrites the value returned for new. There is a probability of it being detected which depends on other names in the query/tables and what the consuming code uses. But a lint that warns about an implicit AS would have a 100% chance of catching this problem, if is a relevant problem for that code base. Your example seems to be about column aliases rather than table aliases, but I grant that examples can be crafted where warning at the absence of AS in a table alias is helpful. But one can also craft cases where warning at the *presence* of AS is helpful. Consider this: CREATE TABLE price ( id PRIMARY KEY, p INT ) CREATE TABLE price as ( id PRIMARY KEY, p INT ) SELECT * FROM price as p But the programmer intended: SELECT * FROM price as p and will query the wrong table for years and years until the company goes bankrupt. Examples of this kind can be produced to argue either way, so they cannot decide the matter. Even if we disagree about this, it should be clear that a lint check for (INNER|LEFT) JOIN without ON constraint is vastly more valuable. Mark ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Assertion fault with nested select statement
Hello, I think I've found a bug in SQLite3, I get an assertion fault (or segmentation fault in release build) when running a somewhat complicated nested select statement: % sqlite3 -init sqlite3-bug.sql bla.sqlite3 -- Loading resources from sqlite3-bug.sql sqlite3: sqlite3.c:65666: sqlite3VdbeExec: Assertion `u.an.pC!=0' failed. zsh: abort sqlite3 -init sqlite3-bug.sql bla.sqlite3 It is 100% reproduceable. I stripped down my statement and schema I used to create a small example. See bottom of this mail for the content of sqlite3-bug.sql. I'm running 64-bit linux. Regards, Alex GDB backtrace: Program received signal SIGABRT, Aborted. 0x77535c15 in raise () from /lib64/libc.so.6 (gdb) bt #0 0x77535c15 in raise () from /lib64/libc.so.6 #1 0x7753708b in abort () from /lib64/libc.so.6 #2 0x7752ec7e in __assert_fail_base () from /lib64/libc.so.6 #3 0x7752ed22 in __assert_fail () from /lib64/libc.so.6 #4 0x77b997e1 in sqlite3VdbeExec () from /usr/lib64/libsqlite3.so.0 #5 0x77b70927 in sqlite3_step () from /usr/lib64/libsqlite3.so.0 #6 0x004052c4 in shell_exec.constprop.7 () #7 0x00405d49 in process_input () #8 0x00402783 in main () Compiled using: ./configure --prefix=/usr --build=x86_64-pc-linux-gnu --host=x86_64-pc-linux-gnu --mandir=/usr/share/man --infodir=/usr/share/info --datadir=/usr/share --sysconfdir=/etc --localstatedir=/var/lib --libdir=/usr/lib64 --disable-dependency-tracking --disable-static --enable-dynamic-extensions --enable-readline --enable-threadsafe % sqlite3 -version 3.7.14.1 2012-10-04 19:37:12 091570e46d04e84b67228e0bdbcd6e1fb60c6bdb % gcc --version gcc (Gentoo 4.6.3 p1.6, pie-0.5.2) 4.6.3 % uname -a Linux inspiron 3.4.11-tuxonice #1 SMP PREEMPT Sat Sep 29 18:58:19 CEST 2012 x86_64 Intel(R) Core(TM)2 Duo CPU P7450 @ 2.13GHz GenuineIntel GNU/Linux % cat ~/.sqliterc -- SQLite3 command line configuration .timer ON .headers ON .modecolumn .width 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 -- vim: ft=sql % cat sqlite3-bug.sql -- schema CREATE TABLE AAA ( aaa_id INTEGER PRIMARY KEY AUTOINCREMENT ); CREATE TABLE RRR ( rrr_id INTEGER PRIMARY KEY AUTOINCREMENT, rrr_dateINTEGER NOT NULL, rrr_aaa INTEGER ); CREATE TABLE TTT ( ttt_id INTEGER PRIMARY KEY AUTOINCREMENT, target_aaa INTEGER NOT NULL, source_aaa INTEGER NOT NULL ); -- insert insert into AAA (aaa_id) values (2); insert into TTT (ttt_id, target_aaa, source_aaa) values (4469, 2, 2); insert into TTT (ttt_id, target_aaa, source_aaa) values (4476, 2, 1); insert into RRR (rrr_id, rrr_date, rrr_aaa) values (0, 0, NULL); insert into RRR (rrr_id, rrr_date, rrr_aaa) values (2, 4312, 2); -- segfault SELECT i.aaa_id, (SELECT sum(CASE WHEN (t.source_aaa == i.aaa_id) THEN 1 ELSE 0 END) FROM TTT t ) AS segfault FROM (SELECT curr.rrr_aaa as aaa_id FROM RRR curr -- you also can comment out the next line -- it causes segfault to happen after one row is outputted INNER JOIN AAA a ON (curr.rrr_aaa = aaa_id) LEFT JOIN RRR r ON (r.rrr_id 0 AND r.rrr_date curr.rrr_date) GROUP BY curr.rrr_id HAVING r.rrr_date IS NULL ) i; .quit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
Alan Cox, on 10/31/2012 05:54 AM wrote: I don't want to flame on this topic, but you are not right here. As far as I can see, a big chunk of Linux storage and file system developers are/were employed by the gold-plated storage manufacturers, starting from FusionIO, SGI and Oracle. You know, RedHat from recent times also stepped to this market, at least I saw their advertisement on SDC 2012. So, you can add here all RedHat employees. Booleans generally should be reserved for logic operators. Most of the Linux companies work on both low and high end storage. The two are not mutually exclusive nor do they divide neatly by market. Many big clouds use cheap low end drives by the crate, some high end desktops are using SAS although given you can get six 2.5 hotplug drives in a 5.25 bay I'm not sure personally there is much point Those doesn't contradict the point that high performance storage vendors are also funding Linux kernel storage development. Send patches with benchmarks demonstrating it is useful. It's really quite simple. Code talks. How about that recently preliminary infrastructure to send ORDERED commands instead of queue draining was deleted from the kernel, because there's no difference where to drain the queue, on the kernel or the storage side? Vlad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On Thu, Nov 1, 2012 at 8:38 PM, Howard Chu h...@symas.com wrote: Alan Cox wrote: How about that recently preliminary infrastructure to send ORDERED commands instead of queue draining was deleted from the kernel, because there's no difference where to drain the queue, on the kernel or the storage side? Send patches. Isn't any type of kernel-side ordering an exercise in futility, since a) the kernel has no knowledge of the disk's actual geometry b) most drives will internally re-order requests anyway c) cheap drives won't support barriers Even assuming the drives honored all your requests without lying, how would you really want this behavior exposed? From the userland perspective, there are very few apps that care. Probably only transactional databases, really. As a DB author, I'm not sure I'd be keen on this as an open() or fcntl() option. Databases that really care would be on dedicated filesystems and/or devices, so per-file control would be tedious. You would most likely want to say all writes to this string of devices should be order-preserving and forget about it. With that guarantee, a careful writer can have perfectly intact data structures all the time, without ever slowing down for a fsync. SQLite cares. SQLite is an in-process, transaction, zero-configuration database that is estimated to be used by over 1 million distinct applications and to be have over 2 billion deployments. SQLite uses ordinary disk files in ordinary directories, often selected by the end-user. There is no system administrator with SQLite, so there is no opportunity to use a dedicated filesystem with special mount options. SQLite uses fsync() as a write barrier to assure consistency following a power loss. In addition, we do everything we can to maximize the amount of time after the fsync() before we actually do another write where order matters, in the hopes that the writes will still be ordered on platforms where fsync() is ignored for whatever reason. Even so, we believe we could get a significant performance boost and reliability improvement if we had a reliable write barrier. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/**project/http://www.openldap.org/project/ __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
Isn't any type of kernel-side ordering an exercise in futility, since a) the kernel has no knowledge of the disk's actual geometry b) most drives will internally re-order requests anyway They will but only as permitted by the commands queued, so you have some control depending upon the interface capabilities. c) cheap drives won't support barriers Barriers are pretty much universal as you need them for power off ! Even assuming the drives honored all your requests without lying, how would you really want this behavior exposed? From the userland perspective, there are very few apps that care. Probably only transactional databases, really. And file systems internally sometimes. A file system is after all a transactional database of sorts. Alan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE safety?
On 2 Nov 2012, at 8:58am, Ivan Shmakov oneing...@gmail.com wrote: INSERT OR IGNORE INTO foo (foo) VALUES (?1); INSERT INTO bar (foo) VALUES ((SELECT f.rowid FROM foo f WHERE f.foo = ?1)); Or is there a better way to ensure that the inner SELECT either returns a single row, or fails? What do you mean by 'fails' ? Returning zero rows from a SELECT is not failure: it's successfully reporting that there are no such rows in the table. And there may be rows in the table even if the first INSERT failed: the rows may have already been inserted. If what you mean is that some syntax error or conflict prevented the first INSERT from working, the correct way to do it is to look at the result returned from that INSERT and see whether it is SQLITE_OK. Howwever, if you just want to know whether there are any relevant rows in the table ... Solution 1: Before doing the second INSERT command do SELECT count(*) FROM foo WHERE foo.foo = ?1 and look at the number it returns. Solution 2: Call sqlite3_last_insert_rowid(sqlite3*) both before and after the first INSERT and see whether the value changes. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SUGGESTION: now as alias for strftime('%s','now')
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin slav...@bigfraud.org:
On 1 Nov 2012, at 7:55pm, Григорий Григоренко grigore...@mail.ru wrote:
it is a common practice to store datetime values as UNIX time UTC.
Maybe, Sqlite should have some shortcut for evaluating current moment?
Please read
http://www.sqlite.org/lang_datefunc.html
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
functions that modify or format date values.
It's about having useful shortcut for getting current moment that doesn't have
(string) parameters and so can be easily remembered and typed.
Compare:
MS SQL: CURRENT_TIMESTAMP
PostgreSQL: now()
Oracle: sysdate
To:
Sqlite: strftime('%s','now')
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SUGGESTION: now as alias for strftime('%s','now')
Григорий Григоренко grigore...@mail.ru wrote: Compare: MS SQL: CURRENT_TIMESTAMP SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause. Does this satisfy your requirements? http://sqlite.org/lang_createtable.html#tablecoldef -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite on Windows Phone 8
Hi Visual Studio 2012 for Windows Phone offers installation of SQLite for Windows Phone 8 as an extension. To enable programming against this extension a wrapper component or library needs to be installed. I am using sqlite-net installed via NuGet. But when compiling SQLite.cs, the compiler complains that the namespace Community.CsharpSqlite is not found. The same procedure works just fine in Visual Studio 2012 for Windows 8. For Windows Phone a DLL is needed that includes the Community.CsharpSqlite namespace. I have three questions: 1. Has anyone allready build this DLL (Community.CsharpSqlite.WP.dll), that is needed for sqlite-net? 2. How do I compile the DLL myself? 3. Are there other ways to utilize the SQLite for Windows Phone 8 extension? I eagerly awaits your comments and answers. Peter Naldal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SUGGESTION: now as alias for strftime('%s','now')
CREATE TABLE t(id,time);
INSERT INTO t VALUES(1,CURRENT_DATE);
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
INSERT INTO t VALUES(3,datetime('now'));
INSERT INTO t VALUES(4,date('now'));
SELECT * FROM t;
1|2012-11-02
2|2012-11-02 14:10:15
3|2012-11-02 14:10:15
4|2012-11-02
Perhaps the documentation needs to be better? Apparently you couldn't find
this info...
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Friday, November 02, 2012 8:08 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: now as alias for strftime('%s','now')
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin slav...@bigfraud.org:
On 1 Nov 2012, at 7:55pm, Григорий Григоренко grigore...@mail.ru wrote:
it is a common practice to store datetime values as UNIX time UTC.
Maybe, Sqlite should have some shortcut for evaluating current moment?
Please read
http://www.sqlite.org/lang_datefunc.html
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
functions that modify or format date values.
It's about having useful shortcut for getting current moment that doesn't have
(string) parameters and so can be easily remembered and typed.
Compare:
MS SQL: CURRENT_TIMESTAMP
PostgreSQL: now()
Oracle: sysdate
To:
Sqlite: strftime('%s','now')
Simon.
___
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
Re: [sqlite] SUGGESTION: now as alias for strftime('%s','now')
On 2 November 2012 13:08, Григорий Григоренко grigore...@mail.ru wrote:
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin slav...@bigfraud.org:
Please read
http://www.sqlite.org/lang_datefunc.html
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
functions that modify or format date values.
It's about having useful shortcut for getting current moment that doesn't
have (string) parameters and so can be easily remembered and typed.
Compare:
MS SQL: CURRENT_TIMESTAMP
PostgreSQL: now()
Oracle: sysdate
To:
Sqlite: strftime('%s','now')
datetime() will give current date and time
likewise date(), time() etc
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SUGGESTION: now as alias for strftime('%s','now')
Igor Tandetnik wrote: SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause. http://sqlite.org/lang_createtable.html#tablecoldef SQLite does in fact accept CURRENT_TIMESTAMP anywhere: http://www.sqlite.org/lang_expr.html Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE safety?
Simon Slavin writes: On 2 Nov 2012, at 8:58am, Ivan Shmakov wrote: INSERT OR IGNORE INTO foo (foo) VALUES (?1); INSERT INTO bar (foo) VALUES ((SELECT f.rowid FROM foo f WHERE f.foo = ?1)); Or is there a better way to ensure that the inner SELECT either returns a single row, or fails? What do you mean by 'fails'? Returning zero rows from a SELECT is not failure: it's successfully reporting that there are no such rows in the table. Which, as long as the task below is considered, indicates a “contradiction” (of a kind.) And there may be rows in the table even if the first INSERT failed: the rows may have already been inserted. Yes. The end result for the command sequence I'm looking for is simple: • foo has a (foo → ?1) record — it doesn't matter if it was INSERT'ed just now, or was added at some point before; • bar has a (foo → id) record, where ‘id’ is the ROWID of the aforementioned foo record. AIUI, the command sequence above does just that. If what you mean is that some syntax error or conflict prevented the first INSERT from working, the correct way to do it is to look at the result returned from that INSERT and see whether it is SQLITE_OK. ACK, thanks. However, I'm not really interested in the result of the first INSERT, and, in this case, these commands may actually be a part of a trigger (where, I believe, I cannot look at the result returned all that easily.) […] -- FSF associate member #7257 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE safety?
Ivan Shmakov writes: Simon Slavin writes: On 2 Nov 2012, at 8:58am, Ivan Shmakov wrote: INSERT OR IGNORE INTO foo (foo) VALUES (?1); INSERT INTO bar (foo) VALUES ((SELECT f.rowid FROM foo f WHERE f.foo = ?1)); […] The end result for the command sequence I'm looking for is simple: • foo has a (foo → ?1) record — it doesn't matter if it was INSERT'ed just now, or was added at some point before; • bar has a (foo → id) record, where ‘id’ is the ROWID of the aforementioned foo record. Correction: “… has a /new/ (foo → id) record…” AIUI, the command sequence above does just that. […] -- FSF associate member #7257 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE safety?
On 2 Nov 2012, at 4:36pm, Igor Tandetnik itandet...@mvps.org wrote: If a statement inside a trigger fails, then the trigger's execution as a whole fails, and then the statement that caused the trigger to run in the first place fails and reports the original error. Thus, if the two INSERT's are in the trigger, and the first one fails, the second one doesn't even run. For clarity: if a statement inside a trigger fails, then the trigger's execution as a whole fails /immediately/. Execution stops with the first failure. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE safety?
Igor Tandetnik writes:
[…]
Note that ending up in the IGNORE branch of INSERT OR IGNORE
statement doesn't constitute failure, but normal execution. The same
is true for INSERT ... SELECT statement where SELECT returns zero
rows so nothing is actually inserted.
Thus was my question: how do I ensure that a SELECT … INSERT
statement results in exactly one row being inserted?
(Additional constraint: it should be possible to use the code in
a trigger.) The best thing I was able to imagine is to replace:
INSERT INTO bar (foo)
SELECT f.rowid FROM foo f WHERE f.foo = ?1;
with:
INSERT INTO bar (foo)
VALUES ((SELECT f.rowid FROM foo f WHERE f.foo = ?1));
… The task I'm solving seems unlike an unusual one. Suppose
that, e. g., I'm recording a history of clients. Each client
has a name, and is served an arbitrary (but positive) number of
times, at particular dates. Or, for the 0'th approximation:
CREATE TABLE history (
client-name TEXTNOT NULL,
-- as in: CAST (strftime ('%s', 'now') AS INTEGER), or now ()
timestamp INTEGER NOT NULL
);
The redundancy of the schema above could, however, be reduced as
follows:
CREATE TABLE client (
name TEXTNOT NULL
);
CREATE UNIQUE INDEX client-unique
ON client (name);
CREATE TABLE history-1 (
clientINTEGER NOT NULL REFERENCES client,
timestamp INTEGER NOT NULL
);
Now, I wish to preserve the simplicity of access of the first
variant. Thus, I create a VIEW:
CREATE VIEW history
AS SELECT c.name AS client-name,
h.timestamp
FROM history-1 h
INNER JOIN client c
ON (c.rowid = h.client);
… and a TRIGGER:
CREATE TRIGGER history-insert
INSTEAD OF INSERT ON history FOR EACH ROW BEGIN
INSERT OR IGNORE INTO client (name)
VALUES (new.client-name);
INSERT INTO history-1 (client, timestamp)
VALUES ((SELECT c.rowid
FROM client.c
WHERE c.name = new.client-name),
new.timestamp);
END;
This way, an application can SELECT from and INSERT into this
new history VIEW with just the same simplicity as was possible
with the former history TABLE. Naturally, this trigger
requires that a single INSERT to the history VIEW results in
exactly a single INSERT to the underlying history-1 TABLE.
(Which is what brought my question above.)
To note is that, with little CASE trickery, it's possible to
support “hybrid” (id, value) views (c.rowid AS client-id in
the VIEW above), and NULL non-constraints, just as well.
--
FSF associate member #7257 np. ml_hygm.xm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE safety?
Ivan Shmakov oneing...@gmail.com wrote: Igor Tandetnik writes: Note that ending up in the IGNORE branch of INSERT OR IGNORE statement doesn't constitute failure, but normal execution. The same is true for INSERT ... SELECT statement where SELECT returns zero rows so nothing is actually inserted. Thus was my question: how do I ensure that a SELECT … INSERT statement results in exactly one row being inserted? In your hypothetical example of two INSERT statements in a trigger, that is what would indeed happen, assuming both statements succeed. If either of the two statements fails, the trigger as a whole would fail and any changes it made rolled back. (Additional constraint: it should be possible to use the code in a trigger.) The best thing I was able to imagine is to replace: INSERT INTO bar (foo) SELECT f.rowid FROM foo f WHERE f.foo = ?1; with: INSERT INTO bar (foo) VALUES ((SELECT f.rowid FROM foo f WHERE f.foo = ?1)); If the nested SELECT returns an empty set, then this statement attempts to insert NULL into bar.foo, which would fail due to NOT NULL constraint. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows Phone 8
Peter Naldal wrote: Visual Studio 2012 for Windows Phone offers installation of SQLite for Windows Phone 8 as an extension. To enable programming against this extension a wrapper component or library needs to be installed. I am using sqlite-net installed via NuGet. When using the SQLite for Windows Phone extension SDK, an additional component may be required to use it from managed code (C#). The sqlite-net project seems to be a popular choice. But when compiling SQLite.cs, the compiler complains that the namespace Community.CsharpSqlite is not found. The same procedure works just fine in Visual Studio 2012 for Windows 8. For Windows Phone a DLL is needed that includes the Community.CsharpSqlite namespace. I have three questions: 1. Has anyone allready build this DLL (Community.CsharpSqlite.WP.dll), that is needed for sqlite-net? 2. How do I compile the DLL myself? Unfortunately, I'm not familiar with the implementation of the sqlite-net project. Perhaps somebody else on the mailing list can comment on these questions? 3. Are there other ways to utilize the SQLite for Windows Phone 8 extension? I'm not sure. If P/Invoke is allowed on Windows Phone 8, that could be used. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE safety?
Igor Tandetnik writes: Ivan Shmakov oneing...@gmail.com wrote: Igor Tandetnik writes: Note that ending up in the IGNORE branch of INSERT OR IGNORE statement doesn't constitute failure, but normal execution. The same is true for INSERT ... SELECT statement where SELECT returns zero rows so nothing is actually inserted. Thus was my question: how do I ensure that a SELECT … INSERT statement results in exactly one row being inserted? In your hypothetical example of two INSERT statements in a trigger, that is what would indeed happen, assuming both statements succeed. If either of the two statements fails, the trigger as a whole would fail and any changes it made rolled back. Yes. The point is that, thanks to OR IGNORE, the first one succeeds even if the record being INSERT'ed already exists in the table. (The ON CONFLICT clause is non-standard, and I was initially reluctant to use it in my code. However, the example being discussed has made its benefits obvious to me.) (Additional constraint: it should be possible to use the code in a trigger.) The best thing I was able to imagine is to replace: INSERT INTO bar (foo) SELECT f.rowid FROM foo f WHERE f.foo = ?1; with: INSERT INTO bar (foo) VALUES ((SELECT f.rowid FROM foo f WHERE f.foo = ?1)); If the nested SELECT returns an empty set, then this statement attempts to insert NULL into bar.foo, which would fail due to NOT NULL constraint. … Exactly as intended. -- FSF associate member #7257 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compiling SQLite3 with MSVC 2010
Hi, ALL, Is anybody trying to compile SQLite with MSVC 2010? I am getting a lot of warnings. Is there any interest in fixing those? If not what is the policy of using SQLite3 code? I'm using 3.7.14 release. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite3 with MSVC 2010
On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot ikoro...@gmail.com wrote: Hi, ALL, Is anybody trying to compile SQLite with MSVC 2010? Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were performed using MSVC 2010. I am getting a lot of warnings. Is there any interest in fixing those? No. See http://www.sqlite.org/testing.html#staticanalysis for an explanation. If not what is the policy of using SQLite3 code? I'm using 3.7.14 release. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite3 with MSVC 2010
Richard, On Fri, Nov 2, 2012 at 4:14 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot ikoro...@gmail.com wrote: Hi, ALL, Is anybody trying to compile SQLite with MSVC 2010? Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were performed using MSVC 2010. I am getting a lot of warnings. Is there any interest in fixing those? No. See http://www.sqlite.org/testing.html#staticanalysis for an explanation. I just read this link. Interesting information. IIUC, all those warnings are harmless and they do not appear on other platforms. Which means that either gcc is more forgiving or that I am trying to compile my application with some very strange configuration. Or maybe it's C++11 that throws the compilation off of track? I'm just trying to understand why those warnings appear and why nobody else see them on other platforms. Thank you. If not what is the policy of using SQLite3 code? I'm using 3.7.14 release. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ 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
Re: [sqlite] Compiling SQLite3 with MSVC 2010
On Fri, Nov 2, 2012 at 7:25 PM, Igor Korot ikoro...@gmail.com wrote: Richard, On Fri, Nov 2, 2012 at 4:14 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot ikoro...@gmail.com wrote: Hi, ALL, Is anybody trying to compile SQLite with MSVC 2010? Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were performed using MSVC 2010. I am getting a lot of warnings. Is there any interest in fixing those? No. See http://www.sqlite.org/testing.html#staticanalysis for an explanation. I just read this link. Interesting information. IIUC, all those warnings are harmless and they do not appear on other platforms. Which means that either gcc is more forgiving or that I am trying to compile my application with some very strange configuration. Or maybe it's C++11 that throws the compilation off of track? SQLite is written in C, not C++. If you are trying to compile it as C++, then yes you will get a lot of warnings. And there is no guarantee that the result will work, because that is not a configuration that we test. I'm just trying to understand why those warnings appear and why nobody else see them on other platforms. Thank you. If not what is the policy of using SQLite3 code? I'm using 3.7.14 release. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ 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 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite3 with MSVC 2010
Igor Korot wrote: I just read this link. Interesting information. IIUC, all those warnings are harmless and they do not appear on other platforms. Which means that either gcc is more forgiving or that I am trying to compile my application with some very strange configuration. Or maybe it's C++11 that throws the compilation off of track? Out of curiosity, what warning level and compiler options are you using? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite3 with MSVC 2010
Richard, On Fri, Nov 2, 2012 at 4:32 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Nov 2, 2012 at 7:25 PM, Igor Korot ikoro...@gmail.com wrote: Richard, On Fri, Nov 2, 2012 at 4:14 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot ikoro...@gmail.com wrote: Hi, ALL, Is anybody trying to compile SQLite with MSVC 2010? Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were performed using MSVC 2010. I am getting a lot of warnings. Is there any interest in fixing those? No. See http://www.sqlite.org/testing.html#staticanalysis for an explanation. I just read this link. Interesting information. IIUC, all those warnings are harmless and they do not appear on other platforms. Which means that either gcc is more forgiving or that I am trying to compile my application with some very strange configuration. Or maybe it's C++11 that throws the compilation off of track? SQLite is written in C, not C++. If you are trying to compile it as C++, then yes you will get a lot of warnings. And there is no guarantee that the result will work, because that is not a configuration that we test. Yes, I understand that. But C program should compile fine whether you use C or C++ compiler. Is it not? Basically I'm trying to embed SQLite into C++ application and following advice just embedded the code in the project. Are you saying I shouldn't do that and use pre-compiled binaries? Thank you. I'm just trying to understand why those warnings appear and why nobody else see them on other platforms. Thank you. If not what is the policy of using SQLite3 code? I'm using 3.7.14 release. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ 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 -- D. Richard Hipp d...@sqlite.org ___ 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
Re: [sqlite] Compiling SQLite3 with MSVC 2010
Hi, Joe, On Fri, Nov 2, 2012 at 4:34 PM, Joe Mistachkin sql...@mistachkin.com wrote: Igor Korot wrote: I just read this link. Interesting information. IIUC, all those warnings are harmless and they do not appear on other platforms. Which means that either gcc is more forgiving or that I am trying to compile my application with some very strange configuration. Or maybe it's C++11 that throws the compilation off of track? Out of curiosity, what warning level and compiler options are you using? Warning Level: /W4 Trying to compile it in the debug mode with WIN32, UNICODE, Multi-threaded Debug DLL (/MDd). Everything else is probably not important. If it is please do let me know. Thank you. -- Joe Mistachkin ___ 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
Re: [sqlite] Compiling SQLite3 with MSVC 2010
On 2 Nov 2012, at 11:52pm, Igor Korot ikoro...@gmail.com wrote: But C program should compile fine whether you use C or C++ compiler. Is it not? I am repeatedly told that you must tell a C compiler that .c files are C files, and that .cpp files are C++ files. Compiling a C file as if it's C++ doesn't work. Fortunately, all C++ compilers I know can compile C too. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite3 with MSVC 2010
Fortunately, all C++ compilers I know can compile C too. Likely because C++ is rarely anything more than syntactic sugar on standard C ... --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite3 with MSVC 2010
Igor Korot wrote: Warning Level: /W4 Trying to compile it in the debug mode with WIN32, UNICODE, Multi-threaded Debug DLL (/MDd). The /W4 option produces a lot of compiler warnings, mostly related to perceived 64-bit portability issues. These warnings are harmless and can be safely ignored. Also, it is possible to adjust the warning level for a single source code file within a project using Visual Studio. In this case, the warning level for the sqlite3.c file should normally be set to /W3. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite3 with MSVC 2010
Igor Korot ikoro...@gmail.com wrote: But C program should compile fine whether you use C or C++ compiler. Is it not? No. A valid C program doesn't have to be a valid C++ program. As a simplest example, C allows implicit conversion from void* to any other pointer type, while C++ does not. So it's common to see things like int* p = malloc(100 * sizeof(int)); which is valid C but invalid C++. Basically I'm trying to embed SQLite into C++ application and following advice just embedded the code in the project. Normally, a file with .c extension would be processed by a C compiler, unless explicitly configured otherwise. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
