Is Corona's sqlite compiled with fts3 (full text search) enabled?

[susan7]

I’m trying to use full text search in a sqlite database.  I can run “match” queries against my database locally using the sqlite command line utility, but when I tried to run those same queries against the same database file from within my Corona app, I get “no such module: fts3” in the simulator and in the log when I try it on my device.  Is Corona’s sqlite compiled with fts3 (full text search) enabled?  Do I need to do something in build settings?  I may be out of luck if I can’t get this working.  Thank you.

[krystian6]

What a day.

Exactly today I have got the same issue with my app.

It works fine on ios, but on Android I get this Runtime Error: no such module: fts3.

[susan7]

Thanks - I forgot to mention that I was testing on Android simulator (Windows) and my Android device…hadn’t tried iOS yet, glad to hear it worked for you.  But I hope it’s possible for it to work on both of those platforms…

[rob]

For iOS (and the OS-X Corona Simulator) we use the version of SQLite that’s shipping with the OS.  For Windows, Android and WP8, we build in the standard version of SQLite which does not include the full text extension.

Rob

[krystian6]

Rob: thanks for the explanation.

How can we add it?

It’s difficult to use a database when you cannot perform queries on it.

[rob]

You can’t add support.  Though I suppose with Enterprise you could use a different database engine.   As for how to perform queries, you may have to write more complex queries testing multiple database fields.

Rob

[krystian6]

I will try to compile my own sqlite plugin then.

I’m just surprised query with " LIKE ‘%something%’ "  is not supported.

[rob]

It should be… 

http://stackoverflow.com/questions/7323162/sqlite-like-and

https://www.sqlite.org/lang_expr.html

http://www.techonthenet.com/sqlite/like.php

[krystian6]

Ok… this goes in a wrong direction.

Here’s the query:

select a.id as article\_id, a.link as article\_url, a.title as article\_title, a.category as article\_categories, a.thumbnail as thumbnail, a.timestamp as timestamp, a.bookmark as bookmark from articles a

Here’s the error:

I/Corona &nbsp;(17276): Runtime error I/Corona &nbsp;(17276): no such module: fts3 I/Corona &nbsp;(17276): stack traceback: I/Corona &nbsp;(17276): [C]: in function 'nrows' I/Corona &nbsp;(17276): ?: in function 'getArticles' I/Corona &nbsp;(17276): ?: in function '?' I/Corona &nbsp;(17276): ?: in function 'method' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/platform/resources/init.lua:253: in function 'dispatchEvent' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/subrepos/composer/composer.lua:1477: in function 'gotoScene' I/Corona &nbsp;(17276): ?: in function \<?:249\> I/Corona &nbsp;(17276): ?: in main chunk I/Corona &nbsp;(17276): [C]: in function 'require' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/platform/resources/init.lua:820: in function 'require' I/Corona &nbsp;(17276): ?: in function 'listener' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/subrepos/timer/timer.lua:173: in function 'method' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/platform/resources/init.lua:253: in function \</Users/jenkins/slaveroot/workspace/Templates/label/android/platform/resources/init.lua:221\>

works fine on ios.

[susan7]

Krystian6, is the articles table a virtual table that was created using fts3?  Check the schema…maybe that’s why you get this error, even though you are only performing a select statement.

Regarding the rest, I appreciate the quick reply by Corona staff.  But I am very disappointed that Corona offers no way to implement sqlite’s full text search on Android.  I hope this will be considered as a build option for the future.  As for this app, I don’t have Enterprise, so I guess I’m out of luck…

[krystian6]

What a day.

Exactly today I have got the same issue with my app.

It works fine on ios, but on Android I get this Runtime Error: no such module: fts3.

[susan7]

Thanks - I forgot to mention that I was testing on Android simulator (Windows) and my Android device…hadn’t tried iOS yet, glad to hear it worked for you.  But I hope it’s possible for it to work on both of those platforms…

[rob]

For iOS (and the OS-X Corona Simulator) we use the version of SQLite that’s shipping with the OS.  For Windows, Android and WP8, we build in the standard version of SQLite which does not include the full text extension.

Rob

[krystian6]

Rob: thanks for the explanation.

How can we add it?

It’s difficult to use a database when you cannot perform queries on it.

[rob]

You can’t add support.  Though I suppose with Enterprise you could use a different database engine.   As for how to perform queries, you may have to write more complex queries testing multiple database fields.

Rob

[krystian6]

I will try to compile my own sqlite plugin then.

I’m just surprised query with " LIKE ‘%something%’ "  is not supported.

[rob]

It should be… 

http://stackoverflow.com/questions/7323162/sqlite-like-and

https://www.sqlite.org/lang_expr.html

http://www.techonthenet.com/sqlite/like.php

[krystian6]

Ok… this goes in a wrong direction.

Here’s the query:

select a.id as article\_id, a.link as article\_url, a.title as article\_title, a.category as article\_categories, a.thumbnail as thumbnail, a.timestamp as timestamp, a.bookmark as bookmark from articles a

Here’s the error:

I/Corona &nbsp;(17276): Runtime error I/Corona &nbsp;(17276): no such module: fts3 I/Corona &nbsp;(17276): stack traceback: I/Corona &nbsp;(17276): [C]: in function 'nrows' I/Corona &nbsp;(17276): ?: in function 'getArticles' I/Corona &nbsp;(17276): ?: in function '?' I/Corona &nbsp;(17276): ?: in function 'method' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/platform/resources/init.lua:253: in function 'dispatchEvent' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/subrepos/composer/composer.lua:1477: in function 'gotoScene' I/Corona &nbsp;(17276): ?: in function \<?:249\> I/Corona &nbsp;(17276): ?: in main chunk I/Corona &nbsp;(17276): [C]: in function 'require' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/platform/resources/init.lua:820: in function 'require' I/Corona &nbsp;(17276): ?: in function 'listener' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/subrepos/timer/timer.lua:173: in function 'method' I/Corona &nbsp;(17276): /Users/jenkins/slaveroot/workspace/Templates/label/android/platform/resources/init.lua:253: in function \</Users/jenkins/slaveroot/workspace/Templates/label/android/platform/resources/init.lua:221\>

works fine on ios.

[susan7]

Krystian6, is the articles table a virtual table that was created using fts3?  Check the schema…maybe that’s why you get this error, even though you are only performing a select statement.

Regarding the rest, I appreciate the quick reply by Corona staff.  But I am very disappointed that Corona offers no way to implement sqlite’s full text search on Android.  I hope this will be considered as a build option for the future.  As for this app, I don’t have Enterprise, so I guess I’m out of luck…