This post isn't a question, just something that may be semi-interesting to
someone.
I have two versions of my app: multi-user and single-user: MySQL and SQLite.
So, up
till yesterday, I had 2 versions of the .sql files to build the 67 tables in
the
app's database. It bothered me that the files were substantially the same, but
with
subtle differences. So yesterday I spent a few hours and ironed it out so there
are
only a few cases of differing syntax.
To show the differences here, I'll use the *nix command line utilities "diff"
and
"grep". I use 'diff -u' to get a "unified diff" of the sqlite/system.sql file
versus
the mysql/system.sql. I pipe that to grep to filter out all but the lines that
differed (normally 'diff -u' shows a certain number of common lines before and
after
the actual difference).
Anyway, here it is:
mac:db pmcnett$ diff -u sqlite/system.sql mysql/system.sql | grep '^[+-]'
--- sqlite/system.sql 2010-09-29 10:40:04.000000000 -0700
+++ mysql/system.sql 2010-09-29 10:39:19.000000000 -0700
-create table _module_dependencies (id INTEGER PRIMARY KEY AUTOINCREMENT,
+create table _module_dependencies (id INTEGER PRIMARY KEY auto_increment,
-create table _modules_enabled (id INTEGER PRIMARY KEY AUTOINCREMENT,
+create table _modules_enabled (id INTEGER PRIMARY KEY auto_increment,
-create table _options_enabled (id INTEGER PRIMARY KEY AUTOINCREMENT,
+create table _options_enabled (id INTEGER PRIMARY KEY auto_increment,
-create table _accumulators (id INTEGER PRIMARY KEY AUTOINCREMENT,
+create table _accumulators (id INTEGER PRIMARY KEY auto_increment,
-create table _prefs (id INTEGER PRIMARY KEY AUTOINCREMENT,
+create table _prefs (id INTEGER PRIMARY KEY auto_increment,
-create table _db_updates (id INTEGER PRIMARY KEY AUTOINCREMENT,
+create table _db_updates (id INTEGER PRIMARY KEY auto_increment,
Unfortunately, MySQL uses "AUTO_INCREMENT" while SQLite uses "AUTOINCREMENT",
so I
can't change these into something unified between the two engines. Fortunately,
I
only used auto-incrementing fields in a handful of tables, and my next task is
to
change those to use the UUID keys that are used in every other table, or to
decide to
not use keys at all since these are system-level tables that really don't need
them.
UUID keys are generated using, for example:
from dabo.lib import getRandomUUID
my_uuid = getRandomUUID()
And they look like:
acb1061e-45bb-4ece-86ce-ccfc744bfb32
They are virtually guaranteed to be unique across machines, across, the world:
no one
key will ever be generated again. The most important point about them is that
your
database WILL BE PORTABLE. You can have offline users adding customers to their
copy
of the database, and then when they are online again they can sync up based on
these
keys. You didn't have to issue temporary keys in the offline database, but can
just
use them as-is when syncing up.
One nice thing about all the changes to the table definitions yesterday, things
like
changing the sqlite version from:
create table customers (id CHAR PRIMARY KEY,
name CHAR,
notes CLOB);
to:
create table customers (id CHAR(40) PRIMARY KEY,
name CHAR(64),
notes LONGTEXT);
...didn't require any changes to my bizobj definitions. Not that this surprised
me as
database-agnosticism is one of Dabo's design goals, but it is really cool
nonetheless!
I just thought somebody might find this interesting - it did sort of surprise
me that
I could write equal SQL that worked for 2 different engines. Granted, I use my
DB's
as data stores only, no triggers or referencial integrity enforced at the db
layer.
No stored procedures. Those things would have certainly caused the SQL to drift
apart.
The funny thing is, I remember 3 years ago writing the 'AUTOINCREMENT' for
those
system tables, and I remember thinking "is this going to bite me one day?"
Paul
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/[email protected]
