Converting from SQLite3 to PostgreSQL or MySQL

SQLite3 to PostgreSQL

Below is a one liner to import the sqlite3 into a PostgresSQL database. It's a modification from the original one liner from Stack Overflow

cat demo.sqlite.dump | sed -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/' | sed -e 's/PRAGMA foreign_keys=OFF;//' | sed -e 's/unsigned big int/BIGINT/g' | sed -e 's/UNSIGNED BIG INT/BIGINT/g' | sed -e 's/BIG INT/BIGINT/g' | sed -e 's/UNSIGNED INT(10)/BIGINT/' | sed -e 's/BOOLEAN/SMALLINT/g' | sed -e 's/boolean/SMALLINT/g' | sed -e 's/UNSIGNED BIG INT/INTEGER/g' | sed -e 's/INT(3)/INT2/g' | sed -e 's/DATETIME/TIMESTAMP/g' | sed -e 's/NUMERIC/DATE/g' | sed -e 's/0000-00-00 00:00:00/1970-01-01 00:00:00/g' | sed '/DELETE FROM sqlite_sequence;/d' | sed -e 's/INSERT INTO "sqlite_sequence" VALUES(.\([a-z]*\).,\([0-9]*\));/ALTER SEQUENCE \1_id_seq RESTART WITH \2;/g' | psql -d <your database>

Then apply the following change to the DemoAdapter.

diff --git a/include/adapter/DemoAdapter.class.php b/include/adapter/DemoAdapter.class.php
index 11d2d59..2be0742 100644
--- a/include/adapter/DemoAdapter.class.php
+++ b/include/adapter/DemoAdapter.class.php
@@ -90,8 +90,15 @@ class DemoAdapter extends Adapter
 
         try
         {
-            $this->dbh = new PDO("sqlite:$database");
-            $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+            $dsn = 'pgsql:host=localhost;port=5432;dbname=kados;application_name=kados';
+            $username = 'username';
+            $password = 'password';
+            $options = array(
+                PDO::ATTR_PERSISTENT => true,
+                PDO::ATTR_EMULATE_PREPARES => true,
+            );
+            $this->dbh = new PDO($dsn, $username, $password, $options);
+            //$this->dbh->exec('SET search_path TO public');
         }
         catch (PDOException $e)
         {

SQLite3 to MySQL

Have not yet found a working one liner that can transform the demo database to a MySQL equivalent. However, possible with a some guidance from Stack Overflow and manually editing the result.

TODO! Find a one liner to convert from SQLite3 to MySQL.

Then apply the following change to the DemoAdapter.

diff --git a/include/adapter/DemoAdapter.class.php b/include/adapter/DemoAdapter.class.php
index 11d2d59..5454dbd 100644
--- a/include/adapter/DemoAdapter.class.php
+++ b/include/adapter/DemoAdapter.class.php
@@ -90,8 +90,11 @@ class DemoAdapter extends Adapter
 
         try
         {
-            $this->dbh = new PDO("sqlite:$database");
-            $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+            $dsn = 'mysql:host=localhost;port=3306;dname=kados';
+            $username = 'username';
+            $password = 'password';
+            $options = array();
+            $this->dbh = new PDO($dsn, $username, $password, $options);
         }
         catch (PDOException $e)
         {