SQLITE_ERROR for in memory db query with sql "collate localized"

[Daniel x]

I'm running a Robolectric unit test and creating a temp in-memory DB (by passing a null dbname).

It all works fine apart from one query which has the sql (in part) "...ORDER BY T.'NAME' COLLATE LOCALIZED ASC".

I get the following stack trace (in part) when the query is run:

Caused by: java.lang.RuntimeException: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such collation sequence: LOCALIZED)
at org.robolectric.shadows.ShadowSQLiteProgram.init(ShadowSQLiteProgram.java:29)
at org.robolectric.shadows.ShadowSQLiteStatement.init(ShadowSQLiteStatement.java:19)

at org.robolectric.shadows.ShadowSQLiteDatabase.compileStatement(ShadowSQLiteDatabase.java:482)

So it's the COLLATE LOCALIZED bit that's causing the problem. This statement doesn't cause any problems when running in my app normally against a real db, or even if I switch my app to use a temp in-memory db.

(I've had a fish around the SQLiteDatabase source code and this flag caught my eye NO_LOCALIZED_COLLATORS. However I don't think that is used here, as for the in-memory DBs we go through the create method, which calls openDatabase with the CREATE_IF_NECESSARY flag.)

Any ideas?

Thanks

[Roman Mazur]

Hi Daniel,

Looking at your stacktrace I assume you are using Robolectric 2.2. Could you try the latest 2.3-SNAPSHOT?

Database related code was completely rewritten in Robolectric 2.3. 

Cannot guarantee this specific issue was fixed, but we definitely should loot at what is happening on the latest version.

And a corresponding test case in any form would be really great.

Thanks!

--
You received this message because you are subscribed to the Google Groups "Robolectric" group.
To unsubscribe from this group and stop receiving emails from it, send an email to robolectric...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--

Best regards,
Roman Mazur 

Software engineer at Stanfy (http://stanfy.com.ua)
Skype: roman.mazur.f

LinkedIn: http://ua.linkedin.com/in/romanmazur
Twitter: http://twitter.com/roman_mazur

[Daniel x]

Ok, thanks for the reply, I'll give it a try. Where can I download the 2.3 snapshot with dependencies? (I'm not using maven, and I can't see anything newer than 2.2 when I look in the repo. Do I just download the latest src zip from github?). Might be easier just to install maven! Also, I'm running on Win 7, and I've noticed there are issues with that, is it worth me even trying?

PS. Do you have an estimated release date for 2.3?

Thanks

[Daniel x]

In the meantime, as a workaround for 2.2 I've created my own class and had it extended ShadowSQLiteDatabase and simply overridden rawQueryWithFactory to strip the offending parts from the sql argument - as I'm not trying to test the localised sort (the SQL was generated for me from greenDAO).

For anyone else who wants to use a similar approach here's the steps spelt out for you (all from this help page):

1. Add a reference to your custom shadow class by one of either:

  a. Annotate your test case/class with: "@Config(shadows = com.package.shadows.MyShadowSQLiteDatabase.class)"

  OR

  b. (If you don't have it already) create a file called org.robolectric.Config.properties in the top level src folder of your test project and add the config line to reference your custom shadow class: "shadows=com.package.shadows.MyShadowSQLiteDatabase"

3. Create the custom shadow class and override the relevant method:

@Implements(value = SQLiteDatabase.class, inheritImplementationMethods = true)

public class MyShadowSQLiteDatabase extends ShadowSQLiteDatabase

{

    @Implementation

    public Cursor rawQueryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, String sql, String[] selectionArgs,

            String editTable)

    {

        String sqlWithCollateLocalizedRemoved = sql.replaceAll("COLLATE LOCALIZED ", "");

        return super.rawQueryWithFactory(cursorFactory, sqlWithCollateLocalizedRemoved, selectionArgs, editTable);

    }

}

[Roman Mazur]

You'll find the jars here: https://oss.sonatype.org/content/repositories/snapshots/org/robolectric/robolectric/2.3-SNAPSHOT/

It does have issues on Windows, yet it's not broken completely.

So if it does not take too much of your time, please try the latest version. 

Btw, you'll have to remove your custom shadow since it won't compile (there is no ShadowSQLiteDatabase class in the latest version). 

[Daniel x]

I've just tried this with robolectric-2.3-20140213.155506-121-jar-with-dependencies.jar and no improvement I'm afraid. Very similar exception:

Caused by: com.almworks.sqlite4java.SQLiteException: [1] DB[1] prepare() SELECT ... ORDER BY T.'NAME' COLLATE LOCALIZED ASC [no such collation sequence: LOCALIZED]

Also, I've noticed since changing to 2.3 snapshots I'm now seeing this console warning when I run the tests: "WARNING: [sqlite] SQLite: unknown jar version." Is that something to be worried about?

Thanks

[Daniel x]

Unfortunately I've now tried with the official 2.3 release and still see this problem. As 2.3 removes ShadowSQLiteDatabase I think this is a blocker for me upgrading to 2.3. Any ideas?

[Roman Mazur]

Could you please provide with a description of the error you see with 2.3 (strace, message)? And why the remove of ShadowSQLiteDatabase is a blocker for you?

For more options, visit https://groups.google.com/d/optout.

[Daniel x]

Sorry for the late reply - I must have missed yours.

I don't have the 2.3 stack trace to hand right now, but from memory it was the same as for 2.2, which I have already given in my opening post.

The removal of ShadowSQLiteDatabase in 2.3 is a blocker to me upgrading from 2.2 because my workaround (see my post from Feb 3) uses it to strip the "COLLATE LOCALIZED" from the SQL.

Today however I've just noticed something in the SQLiteDatabase api that caught my eye. In the class overview it states:

Localized Collation - ORDER BY
In addition to SQLite's default BINARY collator, Android supplies two more, LOCALIZED, which changes with the system's current locale, and UNICODE, which is the Unicode Collation Algorithm and not tailored to the current locale.

So the LOCALIZED collator is an addition in Android and not in standard SQLite. And a quick Google shows this has tripped up the SQLite browser project in the same way.

I know 2.3 "Robolectric now uses a real implementation of SQLite" (release notes) so this would explain it. The SQLite browser team worked around it by creating the localization as a fallback.

What options are available to fix this in Robolectric?

Thanks

[Daniel x]

Quick correction, the 2.3 stack was different but with the same error message, I included it in my post on Feb 15.

[Daniel x]

Update:

Someone else also hit this issue (in August) and raised it on the GitHub project - see issue 1230.

And someone else has submitted a fix (in December) via pull request 1419. The fix is the same as the workaround I proposed earlier - stripping the unsupported collations.

[Daniel x]

Someone else submitted a fix for this in January in pull request 1454. They replace COLLATE LOCALIZED with COLLATE NOCASE. It was merged into master on January 12, and so is available in 3.0-SNAPSHOT. Time to give it another go.