User authentication is temporarily unavailable. If this situation persists, please inform your Wiki Admin.

Port db to SQLite

Simple Invoices now uses PDO in svn trunk and currently there are mysql and postgres version of the Simple Invoices db.

Want would be great is a SQLite port

Whats needed:

  1. Convert the db - refer shell script below
  2. Rewrite any queries that dont work with sqlite
    1. refer the current code - but use case statement to write alternate queries
    2. ie. 1 for mysql, 1 for postgres and 1 for sqlite

MySQL to SQLite3 (shell script)

A quick way to convert a MySQL DB to SQLite3 is the following shell script (it does not claim to be complete)(in particular, it misses converting booleans from 0/1 to t/f):

  if [ "x$1" == "x" ]; then
     echo "Usage: $0 <dbname>"
  if [ -e "$1.db" ]; then
     echo "$1.db already exists.  I will overwrite it in 15 seconds if you do not press CTRL-C."
     while [ $COUNT -gt 0 ]; do
        echo "$COUNT"
        sleep 1
        COUNT=$((COUNT - 1))
     rm $1.db
  /opt/lampp/bin/mysqldump -u root -p --compact --compatible=ansi --default-character-set=binary $1 |
  grep -v ' KEY "' |
  grep -v ' UNIQUE KEY "' |
  grep -v ' PRIMARY KEY ' |
  sed 's/ unsigned / /g' |
  sed 's/ auto_increment/ primary key autoincrement/gi' |
  sed 's/ smallint([0-9]*) / integer /gi' |
  sed 's/ tinyint([0-9]*) / integer /gi' |
  sed 's/ int([0-9]*) / integer /gi' |
  sed 's/ character set [^ ]* / /gi' |
  sed 's/ enum([^)]*) / varchar(255) /gi' |
  sed 's/ on update [^,]*//gi' |
  perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
  perl -pe '
  if (/^(INSERT.+?)\(/) {
  ' > $1.sql
  cat $1.sql | sqlite3 $1.db > $1.err
  ERRORS=`cat $1.err | wc -l`
  if [ "$ERRORS" == "0" ]; then
     echo "Conversion completed without error. Output file: $1.db"
     rm $1.sql
     rm $1.err
     echo "There were errors during conversion.  Please review $1.err and $1.sql for details."


 | roadmap/sqlite_port.txt · Last modified: 2016/10/11 06:15 (external edit)