{"id":47,"date":"2012-05-13T07:48:00","date_gmt":"2012-05-13T12:48:00","guid":{"rendered":"http:\/\/blogs.law.harvard.edu\/acts\/?p=47"},"modified":"2012-07-01T00:45:33","modified_gmt":"2012-07-01T04:45:33","slug":"abstracting-schemas-for-mysql-and-oracle-with-yii-migrations","status":"publish","type":"post","link":"https:\/\/archive.blogs.harvard.edu\/acts\/2012\/05\/13\/abstracting-schemas-for-mysql-and-oracle-with-yii-migrations\/","title":{"rendered":"Abstracting schemas for MySQL and Oracle with Yii Migrations"},"content":{"rendered":"

Yii migrations are an interesting way to keep track of your database changes. \u00a0I was originally looking for a tool that would abstract the schema SQL — with something like the Yii ActiveRecord — so I wouldn’t have to write duplicate code for Oracle and MySQL. \u00a0I mean, that’s kind of the point of going with the PDO abstraction.<\/p>\n

So more than a few forum question\/answers led me to Yii migration:<\/p>\n

http:\/\/www.yiiframework.com\/doc\/guide\/1.1\/en\/database.migration<\/a><\/p>\n

I seriously have that page open 3 times in my tabs and I have read it top to bottom probably 20 times. \u00a0I keep going back to it over and over hoping it will have more information. \u00a0It’s just a little short on answers to all of my questions.<\/p>\n

As I said, all I wanted was a way to abstract the schema, so the original premise of “migrations” was a little different than what I was looking for, but it being the only non-custom option, I went forward with it. \u00a0The point of migrations is to make sure the state of your database matches the revision of your code. \u00a0So it’s sort of like its own hokey versioning system. \u00a0As such, it doesn’t function as simply as I might like it. \u00a0It’s not easy to focus on one migration if it’s not the last migration. \u00a0Because the point of migrations is that you’re moving forward and editing a past migration would be like editing revision X without it effecting all of the future revisions. \u00a0So it makes sense for what it is, it’s just awkward. \u00a0For one the file structure HAS to include the timestamp. \u00a0And it has to include that timestamp at the front of the filename, so autocompleting these migration filenames is a pain in the ass.<\/p>\n

One important thing they don’t talk about in the documentation is that the list of what’s been migrated is added automatically to your database via the “tbl_migrations” — and please remember the “s as it will insert it as lowercase, which could be annoying for Oracle users, though Yii seems to handle it “appropriately”.<\/p>\n

The only major hurdle here is dealing with autoincrement. The way this was dealt with was I created an Autoincrement class that I plopped in the migrations directory:
\nprotected\/migrations\/Autoincrement.php<\/a><\/p>\n

This meant that I could just create the tables like so:<\/p>\n

\r\nclass m120402_194059_Quizes extends CDbMigration\r\n{\r\n\tpublic function up()\r\n\t{\r\n\r\n\t\t$this->createTable('QUIZES', array(\r\n\t\t\t'ID' => 'pk',\r\n\t\t\t'COLLECTION_ID' => 'integer NOT NULL',\r\n\t\t\t'TITLE' => 'string NOT NULL',\r\n\t\t\t'DESCRIPTION' => 'string',\r\n\t\t\t'VISIBILITY' => "integer NOT NULL",\r\n\t\t\t'STATE' => 'string',\r\n\t\t\t'SHOW_FEEDBACK' => 'integer',\r\n\t\t\t'START_DATE' => 'datetime',\r\n\t\t\t'END_DATE' => 'datetime',\r\n\t\t\t'DATE_MODIFIED' => 'datetime',\r\n\t\t\t'DELETED' => "integer NOT NULL",\r\n\t\t));\t\r\n\r\n\t\tAutoincrement::up('QUIZES', Yii::app()->db->driverName);\r\n\r\n\r\n\t}\r\n\r\n\tpublic function down()\r\n\t{\r\n\r\n\t\tAutoincrement::down('QUIZES', Yii::app()->db->driverName);\r\n\r\n\t\t$this->dropTable('QUIZES');\r\n\r\n\t}\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Yii migrations are an interesting way to keep track of your database changes. \u00a0I was originally looking for a tool that would abstract the schema SQL — with something like the Yii ActiveRecord — so I wouldn’t have to write duplicate code for Oracle and MySQL. \u00a0I mean, that’s kind of the point of going […]<\/p>\n","protected":false},"author":4571,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[63924,1841,63941,63942,3163,63928,63937],"tags":[63965,63951,3159,4034,63964,63943],"class_list":["post-47","post","type-post","status-publish","format-standard","hentry","category-atg","category-databases","category-mysql-databases","category-oracle-databases","category-php","category-quizmo-atg","category-yii","tag-abstraction","tag-migration","tag-mysql","tag-oracle","tag-schema","tag-yii-2"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/posts\/47","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/users\/4571"}],"replies":[{"embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/comments?post=47"}],"version-history":[{"count":4,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/posts\/47\/revisions"}],"predecessor-version":[{"id":230,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/posts\/47\/revisions\/230"}],"wp:attachment":[{"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/media?parent=47"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/categories?post=47"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/tags?post=47"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}