androiddatabasesqliteandroid-sqlitefts3

How to set a 'DEFAULT' field when using fts3 Sqlite table?

I'm trying to set a default field in my table to current time. When I use a fts3 virtual table, inserting a row doesn't fill the default field to what it should be. Instead, it inserts null.

If I create the same table as normal table, the exact same query works and the field is populated.

Here are the 2 different table structures I'm using:

Normal table that default value does work

CREATE TABLE Emlak_test2 (_id INTEGER PRIMARY KEY,emlak_id TEXT,created_at TEXT DEFAULT (datetime('now', 'localtime')),emlak_sellorrent TEXT,emlak_cat TEXT,emlak_altcat TEXT,emlak_desc TEXT,emlak_living_rooms INTEGER,emlak_rooms INTEGER,emlak_sellprice INTEGER,emlak_temp TEXT,emlak_city TEXT,emlak_state TEXT,emlak_address TEXT,img_p1 TEXT,img_p2 TEXT,img_p3 TEXT,img_p4 TEXT,img_p5 TEXT,musteri_id TEXT);

FTS3 table that the default value does not work

CREATE VIRTUAL TABLE Emlak_test USING fts3 (_id INTEGER PRIMARY KEY,emlak_id TEXT,created_at TEXT DEFAULT (datetime('now', 'localtime')),emlak_sellorrent TEXT,emlak_cat TEXT,emlak_altcat TEXT,emlak_desc TEXT,emlak_living_rooms INTEGER,emlak_rooms INTEGER,emlak_sellprice INTEGER,emlak_temp TEXT,emlak_city TEXT,emlak_state TEXT,emlak_address TEXT,img_p1 TEXT,img_p2 TEXT,img_p3 TEXT,img_p4 TEXT,img_p5 TEXT,musteri_id TEXT);

Now, if I use this query;

insert into table_name default values;

on the first table, I can see that created_at field is populated. On the second table, the field is empty.

I hope you can help me with this.

Thank you!

Solution

  • The documentation says:

    If column names are explicitly provided for the FTS table as part of the CREATE VIRTUAL TABLE statement, then a datatype name may be optionally specified for each column. This is pure syntactic sugar, the supplied typenames are not used by FTS or the SQLite core for any purpose. The same applies to any constraints specified along with an FTS column name – they are parsed but not used or recorded by the system in any way.

    So it is not possible to have default values.

    And,

    it is not possible to create indices or triggers attached to FTS tables.

    So it is not possible to work around this.