Meta

I ran into an issue with a minor difference between Oracle and MySQL. Apparently MySQL is better about returning rows in the order they were inserted than Oracle. Now if you want to let me know it’s wrong to assume results are returned in any specific order, I know! Neither gives any guarantee on the order of results without an ORDER BY, but Oracle is semi-random.

Yii migrations are an interesting way to keep track of your database changes. I 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. I mean, that’s kind of the point of going with the PDO abstraction.

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

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. The point of migrations is to make sure the state of your database matches the revision of your code. So it’s sort of like its own hokey versioning system. As such, it doesn’t function as simply as I might like it. It’s not easy to focus on one migration if it’s not the last migration. Because 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. So it makes sense for what it is, it’s just awkward. For one the file structure HAS to include the timestamp. And it has to include that timestamp at the front of the filename, so autocompleting these migration filenames is a pain in the ass.

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”.

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:protected/migrations/Autoincrement.php

Totally annoying. Now I’m not one who has a ton of experience with foreign keys as most places seem to have relations with tables but never explicitly link them as such.

So a FK just doesn’t let you add a record when the record it’s linking to doesn’t exist and won’t let you delete a record when there is some other record linking to it.

The problem is I don’t think Yii thought about these when they did fixtures. They built in a way to deal with it, but it’s a little annoying.

Let’s ignore the fact that all of my tables are in caps because I want this to work in Oracle without having to throw single quotes around everything all the time. Let’s take a look at the fixtures of a section of my tables in a project I’m working on right now.

Without these scripts, each table will just DELETE FROM MYTABLE when resetTable is called. The problem with this is if you DELETE FROM COLLECTIONS without deleting the QUIZZES first, you get an integrity constraint violation. So a table init file needs to truncate all tables that are “children” of that table in the order of smallest to largest. COLLECTIONS.init.php looks like this: