On 09/29/2010 02:03 PM, Paul McNett wrote: > 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?" > I have fully db agnositc scripts for my database migrations. This is accomplished through SqlAlchemy and SqlAlchemy-migrate. They are extraordinarily powerful tools and I would highly recommend them. They also allow me to perform a database update within my application during startup time.
Regards, Nate _______________________________________________ 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]
