{"id":49,"date":"2012-04-04T15:09:14","date_gmt":"2012-04-04T20:09:14","guid":{"rendered":"http:\/\/blogs.law.harvard.edu\/acts\/?p=49"},"modified":"2012-06-06T12:06:39","modified_gmt":"2012-06-06T16:06:39","slug":"yii-migrations-dealing-with-auto_increment-for-oracle-and-mysql","status":"publish","type":"post","link":"https:\/\/archive.blogs.harvard.edu\/acts\/2012\/04\/04\/yii-migrations-dealing-with-auto_increment-for-oracle-and-mysql\/","title":{"rendered":"Yii Migrations: Dealing with auto_increment for Oracle and MySQL"},"content":{"rendered":"<p>Oracle deals with auto_increment by using sequences and triggers:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\ncreate sequence test_seq\r\nstart with 1\r\nincrement by 1\r\nnomaxvalue;\r\n\r\ncreate trigger test_trigger\r\nbefore insert on test\r\nfor each row\r\nbegin\r\nselect test_seq.nextval into :new.id from dual;\r\nend;\r\n\/\r\n<\/pre>\n<p>And MySQL is just an auto_increment keyword on the end of a column declaration:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TABLE animals (\r\nid MEDIUMINT NOT NULL AUTO_INCREMENT,\r\nname CHAR(30) NOT NULL,\r\nPRIMARY KEY (id)\r\n)\r\n<\/pre>\n<div>So I created a simple Autoincrement class:<\/div>\n<p><a href=\"https:\/\/github.com\/jazahn\/Quizmo\/blob\/master\/quizmo\/protected\/migrations\/Autoincrement.php\">\/protected\/migrations\/Autoincrement.php<\/a><\/p>\n<div><\/div>\n<div>Which I included in the migration file for a schema. \u00a0This class will handle checking the driver and executing the appropriate sql.<\/div>\n<pre class=\"brush: php; title: ; notranslate\" title=\"\">\r\nAutoincrement::up(&#039;USERS&#039;, Yii::app()-&gt;db-&gt;driverName);\r\nAutoincrement::down(&#039;USERS&#039;, Yii::app()-&gt;db-&gt;driverName);\r\n\r\n<\/pre>\n<p>What needs to be remembered here is to add a &#8220;sequenceName&#8221; to the model class, that is of the form &#8220;TABLENAME_SEQ&#8221;.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle deals with auto_increment by using sequences and triggers: create sequence test_seq start with 1 increment by 1 nomaxvalue; create trigger test_trigger before insert on test for each row begin select test_seq.nextval into :new.id from dual; end; \/ And MySQL is just an auto_increment keyword on the end of a column declaration: CREATE TABLE animals [&hellip;]<\/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,63949,63951,3159,4034,63947,63948,63943],"class_list":["post-49","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-auto_increment","tag-migration","tag-mysql","tag-oracle","tag-sequences","tag-triggers","tag-yii-2"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/posts\/49","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=49"}],"version-history":[{"count":15,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/posts\/49\/revisions"}],"predecessor-version":[{"id":186,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/posts\/49\/revisions\/186"}],"wp:attachment":[{"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/media?parent=49"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/categories?post=49"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/acts\/wp-json\/wp\/v2\/tags?post=49"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}