CODEWITHSUNDEEP
androidsqlsqlite
Ila
Ila

Reputation: 3538

Android app, SQLite, FTS3, create virtual table if not exists

Beginner to SQLite/SQL. Building an Android app with FTS3 enabled, so I need to create virtual tables. I can't use this, because (according to my system log) virtual tables don't use IF NOT EXISTS syntax: 

             // Create the table if it doesn't exist.
            db.transaction(function(tx){
                tx.executeSql( 'CREATE TABLE IF NOT EXISTS User(UserId INTEGER NOT NULL PRIMARY KEY, FirstName TEXT NOT NULL, LastName TEXT NOT NULL)',[],nullHandler,errorHandler);
            },errorHandler,successCallBack);

To confirm that my SQLite plug-in is working as expected, I have been using this instead: 

            // Create the table if it doesn't exist.
            db.transaction(function(tx){
                tx.executeSql( 'DROP TABLE User',[],nullHandler,errorHandler);
                tx.executeSql( 'CREATE VIRTUAL TABLE User USING fts3 (UserId INTEGER NOT NULL PRIMARY KEY, FirstName TEXT NOT NULL, LastName TEXT NOT NULL)',[],nullHandler,errorHandler);
            },errorHandler,successCallBack);

Which works mostly as expected (except that the UserId INTEGER NOT NULL PRIMARY KEY no longer works, returning null every time-- suggestions for a Virtual Table friendly way to add an incrementing integer also very welcome). But I don't want to drop the table every time, I want to keep it. How can I check for the existence of the table User and only create it if it doesn't exist?

Upvotes: 0

Views: 2558

Answers (1)

CL.
CL.

Reputation: 180270

According to the CREATE VIRTUAL TABLE documentation, the correct syntax is:

CREATE VIRTUAL TABLE IF NOT EXISTS User USING FTS4(...)

According to the FTS documentation, you cannot explicitly declare an autoincrementing column, but every table has an implicit column called docid or rowid.

Upvotes: 4

Related Questions