A proper UPSERT command would be very useful. Here is the simple ON DELETE
example showing the problem using the single REPLACE command, even with
deferred foreign keys.
CREATE TABLE a(
id_a INTEGER PRIMARY KEY AUTOINCREMENT,
data_a);
CREATE TABLE b(
id_b INTEGER PRIMARY KEY AUTOINCREMENT,
id_
Thanks for the info so far.
I attempted to set the PRAGMA defer_foreign_keys = on for the transaction, but
a REPLACE still deletes data in another table with the ON DELETE CASCADE clause.
The problem with REPLACE is: " the REPLACE algorithm deletes pre-existing rows
that are causing the constraint violation prior to inserting or updating the
current row"
When using REPLACE with a table containing a?foreign key clause ON DELETE
CASCADE causes data in other tables to be deleted.
Will SQLite ever get an UPSERT command similar to other DBMS? Thanks for any
info.
Backward compatibility is an understandable concern, but since "DEFAULT" is one
of the 124 official SQLite keywords, you would expect it not to be used for
user-defined objects or else properly quoted.
http://www.sqlite.org/lang_keywords.html
Additionally, adding the capability of using a "DEFAU
Doesn't look like the formatting worked on the last one.
Just an quick example using the sqlite3 CLI:db=/tmp/test.dbsqlite3 $db "create
table t(id integer primary key,ts text default
(datetime('localtime','now')));"for x in "'no_date'" "'invalid_date'" "NULL"
"CURRENT_TIMESTAMP" "DEFAULT"; do?
Thanks for the reply. It would be the case of specifying a default value among
other columns but not wishing to remove it from the inserted fields -- not just
because I'm lazy, but also to reuse that same INSERT statement for other values.
Just an quick example using the sqlite3 CLI:db=/tmp/test.
Maybe there's a technical reason that this functionality wasn't added to
SQLite. Does anyone know?
It would be useful as a convenience, particularly on very?long?tables,?so the
user could omit all names and just use a single DEFAULT in the particular
value. It's also easier to programmatically decide whether to insert a DEFAULT,
NULL,?or an alternate value rather than selectively omitting a p
Is it possible to implement a DEFAULT literal-value in SQLite, which would
allow an individual column to be populated with a DEFAULT, similar to how a
NULL or CURRENT_TIMESTAMP is currently used? Thanks.
for example, from http://www.postgresql.org/docs/9.4/static/sql-insert.html
[ WITH [ RECURS
The documentation says that a persistent rank can be set with
INSERT INTO ft(ft, rank) VALUES('rank', 'bm25(10.0, 5.0)');
However, I can only get it to work on the same database connection as the
query. Subsequent database connections seem to use the standard default
ranking. Is this by design?
Great, that's exactly what I need, I'll give it a try.
I noticed that this update is not included in the official 3.9.2 release. How
is it determined when an update such as this one gets included in a release?
Thanks for any info.
Thanks, I figured that this might be the case. What is the best way to specify
all characters except whitespace as part of a CREATE VIRTUAL TABLE statement?
Should you simply list each of the literal ascii characters such as tokenchars
'!@#$%' and so on. Or is it possible or would it be better t
Is there an easy way to set an FTS5 table so that the only delimiter/separator
is a space and everything else, including all punctuation,?is a token? Some
searching revealed that there is an undocumented?feature as a part of FTS3/4
that allows the actual delimiter to be specified as part of tab
Thanks for fixing it, Dan. Do you know when this will be rolled into an
official or preliminary autoconf file? Thanks again.
This is on v3.9.1. I'm not sure exactly when it stopped working. I was using
the external content functionality with triggers to keep the virtual table
updated as part of a larger program, but found that the referenced example from
the docs exhibits the same issue. INSERT's work fine, but REPLAC
Using the example from the documentation at https://www.sqlite.org/fts5.html
I thought this worked in a previous version.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
I noticed there was talk in the other thread of including FTS5 support in the
amalgamation. That would be great and it seems like that might solve my
problem. Is that correct?
Also, I noticed what may be a couple typos in the FTS5 documentation at
https://www.sqlite.org/fts5.html.
In the extern
Is there a way to build the sqlite3 command line utility with integrated FTS5
support? I have been compiling and installing the latest version by downloading
the latest 'autoconf' file, such as sqlite-autoconf-3081101.tar.gz, and
building and installing that. However, I noticed that this is onl
20 matches
Mail list logo