mySQL to SQLite cheatsheet for iPhone developers

Although I started writing this as a cheat-sheet for myself (after many hours of struggling with this by trial-and-error), I figured it would be helpful for other iPhone developers who’d like to take a mySQL database online and migrate it to their current iPhone project. While other tutorials cover how to read the database into the application from the coding point-of-view, this is just to make sure you get your data uncompromised from your existing mySQL database into a new SQLite DB that your iPhone app can read.

First of all, here’s what I am currently using; I am including versions in case that’s relevant to your situation:

  • MySQL 5.0.81
  • MySQL charset: UTF8-Unicode (utf8)
  • MySQL connection collation: utf8_unicode_ci
  • phpMyAdmin – 2.11.9.5
  • SQLite Database Browser 1.3 (includes 3.3.5 of the SQLite database engine)
  • iPhone Dev Kit – 3.0
  • XCode 3.1.3
  • SQLite 3 framework found at /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS3.0.sdk/usr/lib/libsqlite3.dylib

You can try other solutions for importing the data into a SQLite format, but I’ve had the best luck with Mauricio Piacentini’s SQLite Database Browser. Other options you might want to try include a Firefox plugin.

Here are the steps, using SQLite Database Browser:

  1. phpMyAdmin options for exporting to SQLitelog into phpMyAdmin, select your database and go to Database> Export.
  2. On the export tab, make sure all databases are selected, and the “SQL” radio button is selected.
  3. Select ANSI from the pulldown for export compatibility.
  4. Uncheck all options under Structure and Data, but leave each section checked.
  5. Save as file should be checked, and probably be via ZIP or GZIP, just in case.
  6. Click the Go button to download your file.
  7. Extract the file and open it up in your favorite text editor (I use BBEdit).
  8. Find the first CREATE TABLE line. We need to modify these by hand to simplify the table creation so that SQLite doesn’t get confused. For example, my file came down looking like this after the comment section ended:

    CREATE TABLE dictionary (
    id int(11) NOT NULL auto_increment,
    word varchar(100) NOT NULL,
    adjective tinyint(1) NOT NULL default '0' COMMENT 'is an adjective',
    noun tinyint(1) NOT NULL default '0' COMMENT 'is a noun ',
    intro tinyint(1) NOT NULL default '0' COMMENT 'is an intro',
    PRIMARY KEY  (id),
    UNIQUE KEY words (word)
    );

    You’ll want to edit this to remove pretty much everything but the most basic information and normalize the data types to SQLite 3 Data Types, which are TEXT, NUMERIC, INTEGER, REAL, or BLOB. Your CREATE TABLE should look something like this:

    CREATE TABLE dictionary(
    id INTEGER PRIMARY KEY ASC,
    word TEXT,
    adjective INTEGER,
    noun INTEGER,
    intro TEXT
    );

    I was using id as a primary key so I’ve added “PRIMARY KEY ASC” after “id INTEGER”. (Primary Keys are aliases to row IDs in SQLite, if you have questions read this.)

    However, I’ve noticed that the SQLite Database browser exports SQL in the even more simplified format with no datatype specification as such, which also seemed to work for me and may work just as well for your needs:

    CREATE TABLE dictionary (id, word, adjective, noun, intro);

  9. Continue going through your text file and fix all CREATE TABLE lines as in #8, above.
  10. Check your file text encoding. BBEdit may default to Western (Mac OS Roman), which worked for me, but if you have problems you may need to set it to Unicode, depending on your character set.
  11. Check your file for suspicious characters that might cause the import to choke. Single quotation marks used as apostrophes show up as escaped by themselves; for example you're becomes you''re. This seemed to import fine as long as the other guidelines are followed. Here’s what an example INSERT should look like:

    INSERT INTO dictionary VALUES(305, 'ne''er-do-well', 0, 1, 0);

  12. Check your line endings. BBEdit defaulted to Unix (LF) which worked for me.
  13. Open up SQLite Database Browser, create a new database (.db) file, and import your SQL text file by selecting File> Import> Database From SQL File from the menu.
  14. Check your data by clicking the Browse Data tab and make sure everything came in all right. If not, go back over the steps. Look for weird characters, text and line encoding issues, and syntax issues. The only two commands in your file should be CREATE TABLE and INSERT, although technically it should probably begin with BEGIN TRANSACTION; and end with COMMIT;

Again these steps were derived mostly by trial-and-error, so there may be issues particular to your DB that these guidelines didn’t solve. Please feel free to add any corrections, tips, and questions to the comments area.

Also, I know that with the iPhone OS 3.0, Core Data can take care of a lot of database functionality for you. I’d love to hear how people made the transition, and especially how anyone got a pre-existing SQL database into a Core Data store.

This entry was posted in iPhone Development, Programming and tagged , , , , , , . Bookmark the permalink.

11 Responses to mySQL to SQLite cheatsheet for iPhone developers

  1. Andi says:

    Thank you very much. Keep up the good work.

  2. Chimpo says:

    Nice info.

    Also: CONSTRAINTs / FOREIGN KEYs can be added to the Table creation as seen here, resetting INDICE counts for primary keys won’t work (e.g. “ALTER TABLE “” ALTER COLUMN “id” RESTART WITH 101;”), cration of CACHED tables won’t work and of course non-supported types have to be replaced. For BOOLEAN type one might choose INTEGER while replacing INSERT-values of FALSE with 0 and 1 resp. for TRUE.. etc.

    Cheers and thanks for the heads up!
    Recoding UTF-8 source can be done with native2ascii e.g. “native2ascii -revert infile outfile” for replacing \u…-encoded utf-8 with the native utf-8 encoding w/i sqlite. Or use iconv (“man iconv”) for other cases.

  3. alec says:

    Thanks for that, Chimpo.

  4. thomas fallon says:

    Hey, I just found this post while browsing a bit. It’s was very informative. I’m an iPhone app developer myself, but I’ve never used phpMyAdmin before. You said there were other tutorials out there that had a more code oriented style, would you mind pointing me to any of them?

  5. alec says:

    Thomas,

    It’s been so long since I wrote this post that I couldn’t tell you. I am sure I found them by searching the requisite terms on Google, however.

  6. david says:

    tried it, failed, extremely desperate…

  7. alec says:

    david, what failed?

  8. Mohammed Rafiq says:

    Hi,
    Its good but can u guide me ,how to compare the values of foreign key with primary key.
    Ex:
    Am doing an app and have struck at a point based on sqlite database.
    At first I have to create two tables on same view.In that tables I have to insert values from sqlite database tables.
    In first table i have to get the values from first table.And in second table i have to get the values from second table by comparing with the foreign key.

    table1 table2

    id name id id2 name1
    1 Mohammed 1 1 ABC

    2 Rafiq 2 1 DEF

    3 2 ghi

    in first table i need values Mohammed,Rafiq.
    And when i click on Mohammed i used to get ABC,DEF in second table or if select Rafiq then ghi must display.
    I am sending an app see tht and remodule it searching from a week.
    and the selected data must be displayed in text fields.
    I havent sen any answer for this.If needed I have called values from database to uitable views but not able to compare and diffrentiate .I can post if any one needed.
    if any one gets this post to smrafiqsmd@gmail.com
    Thank u

  9. Pingback: Tweets that mention mySQL to SQLite cheatsheet for iPhone developers | Juggleware Developers' Blog -- Topsy.com

  10. Pingback: MySQL to SQLite migration cheat sheet | Studio Villegas

  11. Pingback: Quelques bons trucs pour XCode » Thiébault Michaël

Leave a Reply

Your email address will not be published. Required fields are marked *