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+unsubscribe@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+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
No comments:
Post a Comment