Migrations are over-reaching and under-delivering

[philsturgeon]

Trying to make migrations know everything about your database engines is a tricky way to go. Generally you want to build some sort of Schema manipulation tool, then your migrations just run up and down logic and nothing else.

For example: Schema Builder & Migrations from Laravel.

This is the same theory to how CodeIgniter, FuelPHP and Laravel have done it - mostly because its a logical seperated way of working. Kohana never really got migrations right, so going anywhere near their approach (or trying your own array based thing) is going to lead to pain and suffering.

You can't cover all possible combinations of a table structure with an array, and trying just makes for a INCREDIBLY confusing documentation process. Callbacks > Arrays, because you can document a callbacks arguments and the objects and methods that they contain.

[dracony]

But that way you don't get an automatic reversal. The classes that are generatyed have to generate the down method too. Also it's much easier to use these arrays to get the schema of the database at a specified migration. What it lacks atm is the ability to manage indexes, and filling up db with random data for testing, otherwise I find the array approach superior to both manual migrations and command line.

[philsturgeon]

Arrays and callbacks dont make a lot of difference to automatic reversal.

The only thing for that which matters is keeping track of the schema in a way the framework will easily understand. Rails maintains a schema.yml which has field names, types, etc readily accessable so they just make a "change" method and define what you'd like to be new, then it knows how to reverse that for a down.

http://guides.rubyonrails.org/migrations.html

Any of these solutions are drastically better than the extreme array soup that arises from trying to do this the current way. Callbacks make indexes much more flexible (foreign keys, unique, composite, etc).

[dracony]

Why the array soup? Each separate migration would usually be small array with up to 3 members probbly (3.g adding 3 columns or a table). It is much more intuitive because it has a similar format to how you schema looks. Actually I do have all those methods written anyway (for table creation etc), arrays are just a convenient way of calling those. If users will have a problem with arrays It's always very possible just to use the moethods directly.

[philsturgeon]

Maybe, but how do you handle ENUM/SET, default values for fields, allowing them to be nullable or not, defining unique indexes, linking foreign keys to their primary keys, fulltext indexes?

How do you alias "string" to "varchar" or "text" n SQLite (which doesnt have varchar)?

Your syntax is simple, because its missing out 40% of what people need to do with migrations.

[dracony]

Well I do handle default value nullable fields and primary keys. As I already said i still have to add indexes.

Aliasing string to varchar is the concern of the driver that implements the interface.

[philsturgeon]

I wouldnt know that you support defaults and nullables because that is not documented:

http://phpixie.com/modules/database-migration-module/

Like I said, documenting every possible configuration with an array is rough, but you could let the API Docs do a lot of the work for you. People need to know what sort of values can go in, does it take an array or not (in the case of ENUM/SET) is it a boolean? Who knows, because its all one massive array.

I bring all this up because I see you repeating mistakes that Kohana and CodeIgniter made years ago, and who since then have either coded to a much more modern way of doing things or are stuck as they are because they are afraid to change. You coding things in the exact same (broken) way they used to do it is of no help to anybody - the community or yourself.

[philsturgeon]

And yes, the string stuff IS a concern of the driver, but so far your drivers are not concerned. CAN you pass "string" into those arrays and have them automatically switch to "varchar" or "text'? Or are you planning on adding that?

Migrations need to be 100% generic, and work the same on every platform.

[dracony]

Well take a look at the mysql driver, it get's passed basically pieces of that huge array, same will be for the SQLite driver. It can have a type map.

[philsturgeon]

You were talking aout Fat-Free on another issue, so its iinteresting to see their migrations and schema builder work as im suggesting:

$builder = new \DB\SQL\Schema($f3->get('DB'));
$builder->alterTable('users')->renameColumn('status','statusold');

Well take a look at the mysql driver, it get's passed basically pieces of that huge array, same will be for the SQLite driver. It can have a type map.

CAN or does? I don't see a type map. Are you taking about future code?

[dracony]

I mentioned fat-free in the wrong issue )

[sparksp]

What happens if I want to merge two tables? Say I've got users and profile, 1-1 relationship. I want to add the columns to the user table, move all the data over from the profile table and then drop the profile table... can you give an example of how this will be possible with the array structure?

Or splitting a table.

One of the documented examples is dropping a table and renaming another table into its place, what happens if I want to drop a table whose name is greater than the renaming table, will the rename happen before the drop?

[dracony]

There' no magic for splitting/merging stuff. If there were such thing in any framework I would avoid it like fire if I had any real data stored in those. Some thing are better done by hand.

All the actions take place in the order they are specified in the array. You first have to specify the move, then the drop.

[sparksp]

I did not ask for "magic", I'd like the tools to be able to do each step myself within a migration. You can already add columns, drop tables, but there's no way I can process any data (that I can see). Another example is if I decide to change the format of the data stored in a column, I would not be able to do this with a PHPixie migration.

[philsturgeon]

Right, I completely forgot to mention this in the first post.

1.) Migrations should not assume that you are only changing SCHEMA, and not changing data at all 2.) Migrations should not assume you are only working with the database.

I should be able to use a migration to update the structure of XML files on the server, or anything.

http://pastie.org/7385258

How would I make a migration like this? When I have added my new field, how can I populate that value?

Without the ability to do that, your migrations are fundamentally broken. No magic required, just some up() and down() methods. You can support a change() method too, so migrations can easily do either - but if they CANT do it, then you're wrong.

[dracony]

There has been a proposal for it here: http://phpixie.com/forum/discussion/comment/106#Comment_106 i'll be implementing it in a way similar to this one

[sparksp]

That looks more like database seeding that what we're talking about. Most of my applications don't have data in them that I can define up front, but rather user entered data.

[dracony]

That was the answer to "When I have added my new field, how can I populate that value?"

[sparksp]

But that assumes you know the IDs of everything that you want to populate, or that you want to populate everything with the same value. Any thoughts on how you'd allow people to populate based on the row itself, or values from another table?

[dracony]

The simplest solution of course is to allow custom coded ups and downs while leaving the schema to manage the usual stuff.

[philsturgeon]

Well you COULD let upsert support callbacks, but this "adding generic logic inside an array" business is confusing, bizarre and non-standard. Again, you're assuming this is going to be database logic.

Generic SQL queries need to be run (not just hard coded data), XML files should be allowed to be moderated, and the migrations definitely don't need "PDO" drivers. Schema manipulation is a generic library, migrations are generic "change management" classes, so the change management class can do some schema manipulation (or not) as it sees fit. Do you see my point?

[dracony]

Basically what you're sauing is that migrations should be a system of taking classes and running ups and downs in those classes and more or less do nothing else. I ment this to be a datbase migration system which it is. I strongly believe that if someone wishes to have a class for running up and down he can just make it himself. Perhaps if I ever see a demend for it I'll split the schema and migrations into two separate systems.

But you really miss out on the whole philosophy here: it was ment to be a framework to do simple things in a simple way. If I wanted to make yet another Doctrine (bad example but you get what I mean) I'd just use that one.

That's basically what IMHO PHP lacked, something simple and straightforward. And though you can argue that I could use Slim and the like for simplicity, I actually couldn't, because I'd still have to use some DB class, some ORM, some view rendering etc. My experience with using slim was esentially reassembling symfony in the process)))

PHPixie was ment as something that you download to quickly make simple things happen, it's never going to be feature-rich, as it's not the point.

[philsturgeon]

Sure I get that, but your "arrays do everything" approach is not just "not feature rich", its fundamentally flawed and not actually useful for anything.

Migrations run ups and downs, thats what they do. Then you can change some schema and run some SQL This currently does not allow you to even run SQL, so its not even doing your original intention of "doing simple things in a simple way". Migrations are already very simple, and your attempt to simplify them further with a single array is only over-complicating the matter and breaking most of their use case.

I'm not suggesting Doctrine, but FuelPHP and Laravel both already handle migrations considerably better than this.

If you're trying to make a basic framework that doesn't do things that most other frameworks do then you should point out the differences on your homepage, instead of just having large breasted cartoon fairies on there. It's pretty hard to take this seriously otherwise.

[dracony]

Well her breasts arent that large)

[zaun]

Just my 2 cents, take it for what it's worth:

I'm used to a system that works similar to how the current migration module works, that is dealing with scheme changes separately from data migrations. The missing bit from the module is the ability to run a pre/post script. The implementation is very different as its designed for SQL server and in a custom proprietary language but the gist is:

1) Run any pre-scripts (direct sql script or a batch file of sorts) 2) Run the scheme migration (defined in the array here which seems easy enough) 3) Run any post-scripts (again direct sql or a little script) 4) Goto #1 for each version between current & desired.

I haven't really been dealing with a lot of data in PHPixie as of yet but when that times comes the pre/post scripts would be more important as those deal with moving data around before and after the scheme change.

The XML format we use is much more complex in that it supports more than just tables. It can deal with stored procedures, views, and enumeration tables, indexes, pk/fk relationships, etc. but that may or may not be overkill for PHPixie and those things could be added in the future too.

One thing I do like about PHPixie is the ease of use. I was able to just start coding in about 30 min. where when I tried others platforms there were bootstrap scripts and 1000 classes and nothing was nice and simple. One of the nice things about the current migration module is its simplicity - just define the scheme and go. Features can always be added just keep things nice and simple :)