[android-developers] Re: SQLiteDatabase.execSQL() not behaving as expected
It is definitely not a one to one mapping, especially when it comes
down to handling transactions (as execSQL does an implicit one, etc)
and doing C/Java mappings along with SQLIte's auto type conversions
(NULL to int, etc). There's also no way to include in C functions
either.
One thing to note is that the flash drive on most Android devices is
abysmally slow and SQLite is constantly doing fsyncs (especially on
ext3 devices). execSQL(PRAGMA synchronous=OFF); can give up to a 4x
speed boost depending on the device, YMMV.
John
On Jan 27, 9:33 am, Robert Hawkey rhaw...@gmail.com wrote:
Yes you would be right if I was using MATCH for the SELECT from the
temporary SearchResults table; however, I'm not doing that. I just
select all the rows from that table as it is not an FTS3 table. Yes
you're also correct, neither iOS nor Android seem to support FTS4
tables at the moment. My tables are FTS3 tables.
I suspect it must have something to do with the internal
implementation of execSQL(). It must not truly be a 1:1 mapping of
the C sqlite3_exec() function in the SQLite3 library, I suspect
they're doing something else. I suspect this because the
documentation says not to include multiple statements separated
with ;'s when calling execSQL(), whereas you can do that with
sqlite3_exec().
Rob
On Jan 26, 6:52 pm, John jsp...@gmail.com wrote:
Oh and from the same session:
sqlite sqlite CREATE VIRTUAL TABLE pages USING fts4(title, body);
SQL error: no such module: fts4SQL error: near sqlite: syntax error
sqlite
On Jan 26, 3:15 pm, Robert Hawkey rhaw...@gmail.com wrote:
SQlite supports FTS3 and FTS4 (full text searching). I've seen
documentation (don't have it handy) that FTS3 is enabled in the build
of SQLite shipped in the API level I'm using so MATCH is valid. I
know MATCH does work because I do at least get one return value.
Also, when I perform the work via rawQueries I get the full results.
Rob
On Jan 26, 2:23 pm, Mark Murphy mmur...@commonsware.com wrote:
I have never used MATCH in SQLite. The LIKE operator uses %, not *, as
the wildcard.
http://sqlite.org/lang_expr.html
On Tue, Jan 24, 2012 at 10:45 AM, Robert Hawkey rhaw...@gmail.com
wrote:
Hi everyone,
I have an app I wrote for the iOS that makes extremely heavy use of a
large database, I am now porting that app to the Android platform.
I have a great deal of operations that follow this pattern:
1 db.execSQL(CREATE TEMPORARY TABLE SearchResults(Name text););
2 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
ProductNames WHERE NameLower MATCH ' + term + *';);
3 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
BrandNames WHERE NameLower MATCH ' + term + *';);
...
Cursor cursor = db.rawQuery(SELECT Name FROM SearchResults +
myCount, null);
cursor.moveToFirst();
if (!cursor.isAfterLast())
{
Debug.log(DB, - Adding: + cursor.getString(0));
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
On iOS using the SQLite3 C API and sqlite3_exec() all of these
statements work perfectly fine. However, on Android they are
exhibiting strange behaviour. I seem to only ever gets one row, it
seems from the very first INSERT (the line that starts with 2 above).
My goal here is to wrap all of the above commands in a begin and end
transaction so that I can prevent multiple transactions from being
created, also I use a temporary in memory table rather than a
rawQuery() with just the selects because that prevents the bridge from
the database layer to the Java layer from happening until the very end
which seems to result in much better performance.
When I rewrite the above logic to look like this:
Cursor cursor = db.rawQuery(SELECT Name FROM Table1 WHERE NameLower
MATCH ' + term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
cursor = db.rawQuery(SELECT Name FROM Table2 WHERE NameLower MATCH '
+ term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
...
It works perfectly returning all the proper results, however this is
extremely slow.
Could anyone explain to me why the execSQL() calls above would not
work as I expect them too (and how they work on iOS)?
Thanks!
Rob
--
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to
android-developers@googlegroups.com
To unsubscribe from this group, send email to
[android-developers] Re: SQLiteDatabase.execSQL() not behaving as expected
Yes you would be right if I was using MATCH for the SELECT from the
temporary SearchResults table; however, I'm not doing that. I just
select all the rows from that table as it is not an FTS3 table. Yes
you're also correct, neither iOS nor Android seem to support FTS4
tables at the moment. My tables are FTS3 tables.
I suspect it must have something to do with the internal
implementation of execSQL(). It must not truly be a 1:1 mapping of
the C sqlite3_exec() function in the SQLite3 library, I suspect
they're doing something else. I suspect this because the
documentation says not to include multiple statements separated
with ;'s when calling execSQL(), whereas you can do that with
sqlite3_exec().
Rob
On Jan 26, 6:52 pm, John jsp...@gmail.com wrote:
Oh and from the same session:
sqlite sqlite CREATE VIRTUAL TABLE pages USING fts4(title, body);
SQL error: no such module: fts4SQL error: near sqlite: syntax error
sqlite
On Jan 26, 3:15 pm, Robert Hawkey rhaw...@gmail.com wrote:
SQlite supports FTS3 and FTS4 (full text searching). I've seen
documentation (don't have it handy) that FTS3 is enabled in the build
of SQLite shipped in the API level I'm using so MATCH is valid. I
know MATCH does work because I do at least get one return value.
Also, when I perform the work via rawQueries I get the full results.
Rob
On Jan 26, 2:23 pm, Mark Murphy mmur...@commonsware.com wrote:
I have never used MATCH in SQLite. The LIKE operator uses %, not *, as
the wildcard.
http://sqlite.org/lang_expr.html
On Tue, Jan 24, 2012 at 10:45 AM, Robert Hawkey rhaw...@gmail.com wrote:
Hi everyone,
I have an app I wrote for the iOS that makes extremely heavy use of a
large database, I am now porting that app to the Android platform.
I have a great deal of operations that follow this pattern:
1 db.execSQL(CREATE TEMPORARY TABLE SearchResults(Name text););
2 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
ProductNames WHERE NameLower MATCH ' + term + *';);
3 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
BrandNames WHERE NameLower MATCH ' + term + *';);
...
Cursor cursor = db.rawQuery(SELECT Name FROM SearchResults +
myCount, null);
cursor.moveToFirst();
if (!cursor.isAfterLast())
{
Debug.log(DB, - Adding: + cursor.getString(0));
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
On iOS using the SQLite3 C API and sqlite3_exec() all of these
statements work perfectly fine. However, on Android they are
exhibiting strange behaviour. I seem to only ever gets one row, it
seems from the very first INSERT (the line that starts with 2 above).
My goal here is to wrap all of the above commands in a begin and end
transaction so that I can prevent multiple transactions from being
created, also I use a temporary in memory table rather than a
rawQuery() with just the selects because that prevents the bridge from
the database layer to the Java layer from happening until the very end
which seems to result in much better performance.
When I rewrite the above logic to look like this:
Cursor cursor = db.rawQuery(SELECT Name FROM Table1 WHERE NameLower
MATCH ' + term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
cursor = db.rawQuery(SELECT Name FROM Table2 WHERE NameLower MATCH '
+ term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
...
It works perfectly returning all the proper results, however this is
extremely slow.
Could anyone explain to me why the execSQL() calls above would not
work as I expect them too (and how they work on iOS)?
Thanks!
Rob
--
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
--
Mark Murphy (a Commons
Guy)http://commonsware.com|http://github.com/commonsguyhttp://commonsware.com/blog|http://twitter.com/commonsguy
Android Training in DC:http://marakana.com/training/android/
--
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
[android-developers] Re: SQLiteDatabase.execSQL() not behaving as expected
SQlite supports FTS3 and FTS4 (full text searching). I've seen
documentation (don't have it handy) that FTS3 is enabled in the build
of SQLite shipped in the API level I'm using so MATCH is valid. I
know MATCH does work because I do at least get one return value.
Also, when I perform the work via rawQueries I get the full results.
Rob
On Jan 26, 2:23 pm, Mark Murphy mmur...@commonsware.com wrote:
I have never used MATCH in SQLite. The LIKE operator uses %, not *, as
the wildcard.
http://sqlite.org/lang_expr.html
On Tue, Jan 24, 2012 at 10:45 AM, Robert Hawkey rhaw...@gmail.com wrote:
Hi everyone,
I have an app I wrote for the iOS that makes extremely heavy use of a
large database, I am now porting that app to the Android platform.
I have a great deal of operations that follow this pattern:
1 db.execSQL(CREATE TEMPORARY TABLE SearchResults(Name text););
2 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
ProductNames WHERE NameLower MATCH ' + term + *';);
3 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
BrandNames WHERE NameLower MATCH ' + term + *';);
...
Cursor cursor = db.rawQuery(SELECT Name FROM SearchResults +
myCount, null);
cursor.moveToFirst();
if (!cursor.isAfterLast())
{
Debug.log(DB, - Adding: + cursor.getString(0));
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
On iOS using the SQLite3 C API and sqlite3_exec() all of these
statements work perfectly fine. However, on Android they are
exhibiting strange behaviour. I seem to only ever gets one row, it
seems from the very first INSERT (the line that starts with 2 above).
My goal here is to wrap all of the above commands in a begin and end
transaction so that I can prevent multiple transactions from being
created, also I use a temporary in memory table rather than a
rawQuery() with just the selects because that prevents the bridge from
the database layer to the Java layer from happening until the very end
which seems to result in much better performance.
When I rewrite the above logic to look like this:
Cursor cursor = db.rawQuery(SELECT Name FROM Table1 WHERE NameLower
MATCH ' + term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
cursor = db.rawQuery(SELECT Name FROM Table2 WHERE NameLower MATCH '
+ term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
...
It works perfectly returning all the proper results, however this is
extremely slow.
Could anyone explain to me why the execSQL() calls above would not
work as I expect them too (and how they work on iOS)?
Thanks!
Rob
--
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
--
Mark Murphy (a Commons
Guy)http://commonsware.com|http://github.com/commonsguyhttp://commonsware.com/blog|http://twitter.com/commonsguy
Android Training in DC:http://marakana.com/training/android/
--
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
[android-developers] Re: SQLiteDatabase.execSQL() not behaving as expected
It's because of the use of the TEMPORARY TABLE clause I think.
# MATCH clause variance
http://www.sqlite.org/fts3.html
# iOS sqlite support
http://stackoverflow.com/questions/3142575/iphone-ios-2-0-to-ios-4-0-sqlite-compatibility
# Android sqlite support
http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android
#sqlite session from a 2.1 emulator:
# sqlite3
SQLite version 3.5.9
Enter .help for instructions
sqlite CREATE VIRTUAL TABLE data USING fts3(Name text);
sqlite CREATE TEMPORARY TABLE SearchResults(Name text);
sqlite insert into SearchResults(Name) values ('foobar');
sqlite insert into data(Name) values ('foobar');
sqlite select Name from data where Name MATCH 'foo*';
foobar
sqlite select Name from SearchResults where Name MATCH 'foo*';
SQL error: unable to use function MATCH in the requested context
sqlite
I would guess that there is a difference between sqlite 3.5.9
(Android) and 3.6.12 (iOS)(but you haven't said what versions work/
don't work).
On Jan 26, 3:15 pm, Robert Hawkey rhaw...@gmail.com wrote:
SQlite supports FTS3 and FTS4 (full text searching). I've seen
documentation (don't have it handy) that FTS3 is enabled in the build
of SQLite shipped in the API level I'm using so MATCH is valid. I
know MATCH does work because I do at least get one return value.
Also, when I perform the work via rawQueries I get the full results.
Rob
On Jan 26, 2:23 pm, Mark Murphy mmur...@commonsware.com wrote:
I have never used MATCH in SQLite. The LIKE operator uses %, not *, as
the wildcard.
http://sqlite.org/lang_expr.html
On Tue, Jan 24, 2012 at 10:45 AM, Robert Hawkey rhaw...@gmail.com wrote:
Hi everyone,
I have an app I wrote for the iOS that makes extremely heavy use of a
large database, I am now porting that app to the Android platform.
I have a great deal of operations that follow this pattern:
1 db.execSQL(CREATE TEMPORARY TABLE SearchResults(Name text););
2 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
ProductNames WHERE NameLower MATCH ' + term + *';);
3 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
BrandNames WHERE NameLower MATCH ' + term + *';);
...
Cursor cursor = db.rawQuery(SELECT Name FROM SearchResults +
myCount, null);
cursor.moveToFirst();
if (!cursor.isAfterLast())
{
Debug.log(DB, - Adding: + cursor.getString(0));
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
On iOS using the SQLite3 C API and sqlite3_exec() all of these
statements work perfectly fine. However, on Android they are
exhibiting strange behaviour. I seem to only ever gets one row, it
seems from the very first INSERT (the line that starts with 2 above).
My goal here is to wrap all of the above commands in a begin and end
transaction so that I can prevent multiple transactions from being
created, also I use a temporary in memory table rather than a
rawQuery() with just the selects because that prevents the bridge from
the database layer to the Java layer from happening until the very end
which seems to result in much better performance.
When I rewrite the above logic to look like this:
Cursor cursor = db.rawQuery(SELECT Name FROM Table1 WHERE NameLower
MATCH ' + term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
cursor = db.rawQuery(SELECT Name FROM Table2 WHERE NameLower MATCH '
+ term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
...
It works perfectly returning all the proper results, however this is
extremely slow.
Could anyone explain to me why the execSQL() calls above would not
work as I expect them too (and how they work on iOS)?
Thanks!
Rob
--
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
--
Mark Murphy (a Commons
Guy)http://commonsware.com|http://github.com/commonsguyhttp://commonsware.com/blog|http://twitter.com/commonsguy
Android Training in DC:http://marakana.com/training/android/
--
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
[android-developers] Re: SQLiteDatabase.execSQL() not behaving as expected
Oh and from the same session:
sqlite sqlite CREATE VIRTUAL TABLE pages USING fts4(title, body);
SQL error: no such module: fts4SQL error: near sqlite: syntax error
sqlite
On Jan 26, 3:15 pm, Robert Hawkey rhaw...@gmail.com wrote:
SQlite supports FTS3 and FTS4 (full text searching). I've seen
documentation (don't have it handy) that FTS3 is enabled in the build
of SQLite shipped in the API level I'm using so MATCH is valid. I
know MATCH does work because I do at least get one return value.
Also, when I perform the work via rawQueries I get the full results.
Rob
On Jan 26, 2:23 pm, Mark Murphy mmur...@commonsware.com wrote:
I have never used MATCH in SQLite. The LIKE operator uses %, not *, as
the wildcard.
http://sqlite.org/lang_expr.html
On Tue, Jan 24, 2012 at 10:45 AM, Robert Hawkey rhaw...@gmail.com wrote:
Hi everyone,
I have an app I wrote for the iOS that makes extremely heavy use of a
large database, I am now porting that app to the Android platform.
I have a great deal of operations that follow this pattern:
1 db.execSQL(CREATE TEMPORARY TABLE SearchResults(Name text););
2 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
ProductNames WHERE NameLower MATCH ' + term + *';);
3 db.execSQL(INSERT INTO SearchResults (Name) SELECT Name FROM
BrandNames WHERE NameLower MATCH ' + term + *';);
...
Cursor cursor = db.rawQuery(SELECT Name FROM SearchResults +
myCount, null);
cursor.moveToFirst();
if (!cursor.isAfterLast())
{
Debug.log(DB, - Adding: + cursor.getString(0));
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
On iOS using the SQLite3 C API and sqlite3_exec() all of these
statements work perfectly fine. However, on Android they are
exhibiting strange behaviour. I seem to only ever gets one row, it
seems from the very first INSERT (the line that starts with 2 above).
My goal here is to wrap all of the above commands in a begin and end
transaction so that I can prevent multiple transactions from being
created, also I use a temporary in memory table rather than a
rawQuery() with just the selects because that prevents the bridge from
the database layer to the Java layer from happening until the very end
which seems to result in much better performance.
When I rewrite the above logic to look like this:
Cursor cursor = db.rawQuery(SELECT Name FROM Table1 WHERE NameLower
MATCH ' + term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
cursor = db.rawQuery(SELECT Name FROM Table2 WHERE NameLower MATCH '
+ term + *', null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
...
It works perfectly returning all the proper results, however this is
extremely slow.
Could anyone explain to me why the execSQL() calls above would not
work as I expect them too (and how they work on iOS)?
Thanks!
Rob
--
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
--
Mark Murphy (a Commons
Guy)http://commonsware.com|http://github.com/commonsguyhttp://commonsware.com/blog|http://twitter.com/commonsguy
Android Training in DC:http://marakana.com/training/android/
--
You received this message because you are subscribed to the Google
Groups Android Developers group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
[android-developers] Re: SQLiteDatabase.execSQL() not behaving as expected
if it is slow, you can run it under loadermanager like CursorLoader that will run cursor query in background thread. -- You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
