CODEWITHSUNDEEP
javaandroidsqldatabasesqlite
intact700
intact700

Reputation: 63

Trying to convert existing SQLite database for use with the Android OS

Hey guys, I have this pre-existing SQLite database that I want to use with my Android application. I have created a sample database from scratch for testing purposes where each primary key is named _id and also adding the table android_metadata. This works great.

So now when I've tried to rename the primary keys of the database I already have, and upload it to the application, it doesn't work.

Can anyone tell me what exactly I have to do to my existing database to get it to work with the Android OS? Like what exactly has to be changed in the database for it to work?

And yes, I have looked at most tutorials, but most of them don't go into detail about what you have to change in the pre-existing database.

Here is the database I am using: http://www.mediafire.com/file/bpbpm19y6kbpjot/database.db

Thanks.

Upvotes: 0

Views: 1517

Answers (4)

gssi
gssi

Reputation: 5071

Again, I have found this document to be very useful: http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

Upvotes: 1

Tim
Tim

Reputation: 5421

Make sure that your existing SQLite database declares integer primary keys using only "INTEGER" (verbatim)--not "int" or "int16" or any of the other possibilities for declaring an integer that SQLite will recognize.

I ran into a related problem when importing a SQLite database in Adobe AIR (which has a common codebase with Goodle and Mozilla and other consortium members, IIRC). My PK had been defined as "int" (verbatim). SQLite treats "INTEGER" primary keys differently than it treats "int" or "INT" or "int16" etc primary keys!

Documented here: http://www.sqlite.org/datatypes.html

An INTEGER primary key is treated by SQLite as a synonym for the RowId. Any other int type is just like a standard column, and with a standard column RowId will not necessarily equal the value in the PK column.

However, Adobe and the other related subgroup of SQLite consortium members did not implement this (documented) behavior--for them any/every integer type used as the PK column is treated as a synonym for the row id-- and their failing to implement this distinction can result in erroneous joins when a pre-existing SQLite database is imported into their implementation(s), if the pre-existing database used anything other than "INTEGER" when declaring its integer-type primary keys.

P.S. I brought this to Adobe's attention and discussed it ad nauseam on the SQLite mailing list and on the Adobe AIR forum. Adobe wrote me that they would document their departure from "standard" SQLite behavior but leave it as is, so I believe Android will also differ from SQLite documented behavior in this regard.

P.P.S. It seems this subgroup of consortium members either did not envision the possibility that a database would be imported (i.e. they assumed the database would always be created anew via their interface) or they simply overlooked this (admittedly wonky) exceptional behavior in SQLite.

P.P.P.S. This table, for example, from the database the OP is using would return spurious results when involved in joins on the [stop_id] column if attached by an implementation of SQLite that did not implement the "standard" INTEGER/int (et al) exceptional behavior but treated any/every int-type when used with the PK as a synonym for the rowid:

  CREATE TABLE mt_stop (
    stop_id  int  NOT NULL PRIMARY KEY ASC,
    stop_lat real NOT NULL CHECK (stop_lat >= -90 AND stop_lat <= 90),
    stop_lon real NOT NULL CHECK (stop_lon >= -180 AND stop_lon <= 180),
    stop_name varchar (120) DEFAULT 'Unknown'
)

Upvotes: 0

Joseph Earl
Joseph Earl

Reputation: 23432

You don't actually need the primary ID column to be named _id -- you can just use something like SELECT my_id as _id, another_field ... in your select statement.

And you can either do as Omokoii said above and set the NO_LOCALIZED_COLLATORS flag, or you can create the android_metadata table and insert the value en-US into it.

As for using an existing DB, perhaps this blog post might help: http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

Upvotes: 0

Arahman
Arahman

Reputation: 434

I usually set the flag NO_LOCALIZED_COLLATORS when calling SQLiteDatabase.openDatabase(). Then you don't need the android_metadata table. As far as I know the _id column also must be of the type INTEGER PRIMARY KEY AUTOINCREMENT.

Upvotes: 0

Related Questions