StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is OP, I was researching and experimenting further and came to conclusion that there is <code>FTS3</code> to blame. Since I need the data to be <a href="http://www.sqlite.org/fts3.html" rel="nofollow noreferrer">searchable by fulltext</a> I was creating <code>VIRTUAL TABLE USING FTS3</code> and then the parameters binding failed.</p> <p>As I do not want to query shadow table (<code>Msg_content</code>) directly, my solution is to split data into 2 related tables:</p> <pre><code>db.execSQL("CREATE TABLE " + Msg._TABLE_NAME + " (" + Msg._ID + PRIMARY_KEY_AUTOINC + Msg.PRIORITY + " TEXT," + Msg.RECEIVED + " INTEGER," + Msg.MOBILE_STATUS + " INTEGER DEFAULT 0," + Msg.READ + " SHORT DEFAULT 0," + Msg.FLASH + " SHORT DEFAULT 0" + ");"); db.execSQL("CREATE VIRTUAL TABLE " + MsgText._TABLE_NAME + " USING FTS3 (" + MsgText._ID + PRIMARY_KEY + MsgText.TITLE + " TEXT," + MsgText.CONTENT + " TEXT," + MsgText.KEYWORDS + " TEXT," + "FOREIGN KEY(" + MsgText._ID + ") " + "REFERENCES " + Msg._TABLE_NAME + "(" + Msg._ID + ") " + ");"); </code></pre> <p>Then I created View to use by queries:</p> <pre><code>db.execSQL("CREATE VIEW IF NOT EXISTS " + View.MSG_CONTENT + " AS SELECT " + Msg._TABLE_NAME + "." + Msg._ID + ", " + Msg._TABLE_NAME + "." + Msg.READ + ", " + Msg._TABLE_NAME + "." + Msg.FLASH + ", " + (...) MsgText._TABLE_NAME + "." + MsgText.TITLE + ", " + MsgText._TABLE_NAME + "." + MsgText.CONTENT + " FROM " + Msg._TABLE_NAME + ", " + MsgText._TABLE_NAME + " WHERE " + Msg._TABLE_NAME + "." + Msg._ID + "=" + MsgText._TABLE_NAME + "." + MsgText._ID); </code></pre> <p>This works very well for me as I can query data using parameters and do fulltext search when needed. Query performance is the same as when using just one table.</p> <p>I hope this helps someone else who might bump into the same issue.</p> <p>Cheers,<br> PeS</p> <p>P.S. Checked Meta and <a href="https://meta.stackexchange.com/questions/12513/should-i-not-answer-my-own-questions">it is OK to reply to self</a>, apparently.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload