[sqlite] FTS5 explicitly set delimiter

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

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

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.

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

Great, that's exactly what I need, I'll give it a try.

[sqlite] FTS5 rank configuration option

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?

[sqlite] INSERT DEFAULT literal-value

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 [

[sqlite] INSERT DEFAULT literal-value

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

[sqlite] INSERT DEFAULT literal-value

Maybe there's a technical reason that this functionality wasn't added to SQLite. Does anyone know?

[sqlite] INSERT DEFAULT literal-value

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

[sqlite] INSERT DEFAULT literal-value

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";

[sqlite] INSERT DEFAULT literal-value

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

[sqlite] FTS5 explicitly set delimiter

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

[sqlite] compiling sqlite3 with FTS5 support

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

[sqlite] compiling sqlite3 with FTS5 support

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

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

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

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

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

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

Thanks for fixing it, Dan. Do you know when this will be rolled into an official or preliminary autoconf file? Thanks again.

[sqlite] UPSERT

Will SQLite ever get an UPSERT command similar to other DBMS? Thanks for any info.

[sqlite] UPSERT

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.

[sqlite] UPSERT

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.

[sqlite] UPSERT

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,