Sometimes for one reason or another you have to work with old SQLite databases. A common task is migrating from V2 to the newer V3 format. You may find that you want to migrate to another DB provider like MySQL. This blog will show you these scenarios.
Download SQLite V2 from http://www.sqlite.org/sqlitedll-2_8_17.zip and place into a folder. My example will be in C:sqlite
Download SQLite V3 from http://www.sqlite.org/2013/sqlite-shell-win32-x86-3080002.zip and place the file sqlite3.exe into the same folder as the earlier one.
Open up a command prompt and change directory to this folder. My command would be cd C:sqlite
Create a temporary SQL dump of V2M
sqlite.exe sqlitev2db.sqlite .dump > temp.sql
Check database is valid
Sometimes there's issues with the ' apostrophes (single quotes) which make the database fail when creating it in another solution like phpMyAdmin. Open up the temp.sql file in your favourite text editor (mine is Notepad++ http://notepad-plus-plus.org/). Its best to replace single quotes like '' (note the 2 characters) or ' and replace with double ones ''. It's also possible to replace using %27 if this is a HTML database like a blog.
I like to use Notepad++ find and replace but any text editor will do this. It's usually found under the Edit or View menu. Alternatively, the keyboard shortcut is almost always Ctrl + H.
Save the new file as db_valid.sql
To make an SQLite 3 database:
sqlite3 database.sqlite3 < db_valid.sql
This will create a new database called database.sqlite3
That's it. All done!
To make a MySQL database you need a server with MySQL and preferably with phpMyAdmin running on it to manage SQL efficiently from a web based interface.
In phpMyAdmin create a new database:
Click on the database and click SQL menu. Open up db_valid.sql in Notepad or your other favourite text editor and copy the contents into the SQL box.
Once this is done you'll have something like this:
The database will now have tables like so:
Clicking a table shows that the entries are in fact there:
Tada. You now have a new MYSQL database.