Converting MySql data base to SQLite data base

Hello Again, 

my goal here is to make a SQLite data base from an existing MySql db i have.

I was digging around the net, I found many tools that can do this mysql to SQLite migration. You can find it here in http://www.sqlite.org/cvstrac/wiki?p=ConverterTools .

Also, David provided a howto in http://code2design.com/tutorial/convert_mysql_tables_to_sqlite_tables, you may refer to that link to see how to do it manually, and i can tell you it is just a pain in the `back`. Or you can copy and paste the following shell script in your cmd.

First, disable all the AUTO_INCREMENT in your MySql data base, as stated by David

Second, in SQLite "auto_increment" is a given if the key is a PRIMARY KEY. So we can delete the "auto_increment" part of the MySQL query.

Second, let's move to the shell Script.
The shell script source is here in http://forums.mysql.com/read.php?145,68269,92627 but i made some modification to it and here is the result :
1- Open you cmd (Under Linux of course) then tape the following :

                                                                                                         

 ***@***:~$ sudo /bin/sh

[sudo] password for ***:

 #                                                           

                                                                                                        

Now, copy the following into the cmd then press Enter key.

mysqldump --user=root --password=rootpass --compact --compatible=ansi --default-character-set=binary db_name |
grep -v ' KEY "' |
grep -v ' UNIQUE KEY "' |
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '
if (/^(INSERT.+?)\(/) {
$a=$1;
s/\\'\''/'\'\''/g;
s/\\n/\n/g;
s/\),\(/\);\n$a\(/g;
}
' |
sqlite3 output.db

Explanation :

The mysqldump client can be used to dump a database or a collection of
databases for backup or for transferring the data to another SQL server
(not necessarily a MySQL server). The dump contains SQL statements to
create the table and/or populate the table.

In the first line, you will need to specify the db user, the password if there is any else remove that option, and other option, refer here for more details about mysqldump http://linuxcommand.org/man_pages/mysqldump1.html. And finally we put the db name that we want to convert. (db_name in this case).
The next lines will edit the sql file to remove lines that contains KEY and UNIQUE KEY and other fixes (I ain't that expert handling command lines in linux :) )
the last line here is where we are going to create our sqlite file, in this case we named the file output.db but you u can name it what ever you want.

Now, if every thing went alright, you will have the file output.db created under the directory /bin with root rights. You will have to move that file and change its rights.

 # exit                         

 ***@***:~$ sudo mv /bin/output.db output.db

 ***@***:~$ chmod 755 output.db

I think we are done here. Now you can open your SQLite file using any tool you want, like Sqliteman or any other.

In the next tutorial, I am going to demonstrate how to use this SQLite data base in android. (Coming SOON).
Feel free to leave your comments.