Sep 03
Portable SQL for development between SQLite and MySQL
In case you are developing web applications in Dancer or similar "first local then online" frameworks one common setup is to use SQLite when you run your app local in development mode, and then you take it online into the production environment where it utilises a MySQL server for the database work.
One thing is that the SQL syntax SQLite and MySQL use isn't quite the same!
First,
try to be specidic with your field types. SQLite translates varchar into string but not the other way round. So use varchar(<field length>) in your CREATE sentences. Default values in MySQL are defined with DEFAULT '<value>' and not with brackets like in SQLite.
Second,
use a simple translation function to translate MySQL compatible SQL into SQLite lingo under the SQLite driver. For example, AUTOINCREMENT is AUTO_INCREMENT in the MySQL world:
sub sqlite2mysql {
my $sql = shift;
$sql =~ s/AUTOINCREMENT/AUTO_INCREMENT/;
return $sql;
}
and then do something like this in init_db():
$sql = "create table if not exists `database` (
`id` tinyint,
`value` integer null,
`extra` integer DEFAULT '0',
`some` varchar(255) null,
);";
$sql = sqlite2mysql($sql) if setting('plugins')->{Database}->{driver} eq 'mysql';
$db->do($sql) or die $db->errstr;