Should the table definition be in the model?
That’s a question I ask myself after reading an article about an alternative approach to realize migrations in Rails. With that approach, you define the columns of your table in the model and then you generate the migration files from the model. You no longer have to touch any SQL scripts or to write migration files — if there are changes in the table definition, you make them in the model.
I like this idea, as it allows you to work on a higher level, i.e. you no longer have to deal with foreign keys, association tables etc. That’s automatically done for you. And the more I think about this topic, the more I am convinced that the table definition should be in the model. You can look at the column types of a table as basic “validation rules” used by the database to check whether the data you want to insert are correct. For example, a column type of varchar(255) could be translated to the validation rule “maxLength = 255″. By moving those “validation rules” to the model level, we have all validation rules related to a model in one place. And that’s the logical place where they should be in my opinion.
What do you think about this idea?
yes that s a great idea.
But did you have to handle INDEX ?
perhaps UNIQUE’s one ?
I too really like this idea. It wouldn’t be too easy though, and it could make models a little messy, but it means less file to manage and write.
I especially like the validation tie in.
I am not sure, whether I understood you right: you do changes to the model, and then? Automatically create sql files you execute in the database? Or are the model changes done automatically by the migration script?
Liebe Grüße, Christoph
It would make sense to just make the changes when the migration script is run. Not really much need in creating SQL files, which you then have to run.
Sounds like a great idea - one thing (though maybe i am not thinking about it correctly): if your model is the authorative source of the table definition, and you change it, would the table update automatically? if that happens, might the effect in the table not be a bit unpredictable? it is pretty easy to make (accidental) changes to php file. just a thought.
This concept is really sexy!!
I think this would work ONLY if there was some sort of shell script that you can run to actually migrate the schema changes. This allows you to do one final review of the migrations before committing them to a production database (think of the horror of misspelling a field or accidentally changing the field type… eeeek).
And the validation … sexy too. I had that idea about a year ago when I started to feel convinced that Bake (at that time) was more trouble than it’s worth. (Ie.. a 4 function calculator can do anything but a sweet TI can do it 100x better and more efficiently). I dully named it “scaffolding for models” :)
Does anyone know if something like this is on the road map for CakePHP? Check with the devs … if it’s not I would love to be able to jump on this and contribute since migrations are a big priority in our projects.
This is a good idea.
django is been doing that for a while, the results are great.
declare table property in Model
————-
class PersonModel extends AppModel {
var id = Model::integer(8);
var name = Model::varchar(255);
var age = Model::integer(2);
var profile = $this->hasOne(’Profile’);
}
class ProfileModel extends AppModel {
var person = $this->belongsTo(’Person’);
}
one other benefit is probably, abstraction of database implementation.
the static method in model class automatically at those into a field array to tell the different between a object property and table property..
then later on, you can run those in any database give it mysql, mssql or sqlite.
i can see more benefit like,
tie in validation, cleaner/cleaner association discovery.
or what about, coding on workstation with sqlite, upload to a different server using mysql change only one line in config file…
that was just one possible implementation..
sorry “$this” is wrong probably
self::belongsTo();
self::hasOne();
I don’t think this is the great idea and I will explain you why I think so:
1. When you make an Application logic, most likely you make it in some DB Designer Editor, which if not synchronizing the Database Directly, then export the Database definition as SQL schema.
2. Scaffolding - if there is no Schema in the database, then you need to define all your models before you do scaffold at all.
3. For MySQL is pretty easy to make comparison between 2 schemas /just export them without Data and diff them - you will see the changes, but for example in Oracle, it’s much better to make Diff schema from a tool /like TOAD/ and all differences will appear immediately on the screen /including functions, views indexes etc./
4. I think it’s much easier to make migration script / Appliction which track the changes between 2 schemas. Probably there is something done /I dont use it so far on MySQL but preliminary search trow http://www.mysqldiff.org which is freeware/ which will do migration very easily.
Hope this make sense.
i too am a big dbdeisnger4 user, @home @work.. drop on table, type sync, edit resync..
but it all come down to what is a Model to you ?
to be it is about Data and encapsulating business logic,
so it is also the right place for data definition, data access.
tie in data definition do introduce a bit of manual work, but it open up a door of options for automation…
After thinking this through overnight, I have realized that this solution definitely has its pros and cons.
Pros:
- Development Environment. Since DB schemas change so frequently in dev, it would be nice to have this feature. Much like how Scaffolding works but on the Model level.
- Simple and clear. DB Schemas (manual) are a mess to work with and are very DB-specific. Writing schemas in the Model removes any specific ties to a certain database by generalizing it (Check Model::loadInfo() - returns a Set object). Leave it up to the dbo’s to generate the applicable sql’s.
- Centralized. Working with a group of developers… I limit the other dev’s access to ALTER or DROP. A migration can be run through an auth system to log changes made to the schema.
Cons:
- As Nick pointed out you add another step of the synchronization process: DEV_DB -> Model -> LIVE_DB. Since scripts can’t change the actual Model source code (well, can’t do it gracefully) you will always have to manually modify the Model schema.
- Data Synchronization. Usually I fall into situations where I need to synch certain tables — including their data.
- DBA’s Cring. DBA’s love their CLI and DBDesigners (much as I do). Some people are true purists and encapsulating logic their are familiar with only decreases overall productivity.
…
Just My thoughts :)
Yeah, that’s how Django does it. See http://www.djangoproject.com/documentation/model-api/
@all: Thanks for your comments :)
@wluigi: Good point, I didn’t thought about indexes and at the moment I don’t know how to fit them into this idea…
@Joel: The idea is to create a migration file and to automatically migrate to this version.
@preloader: The idea is to run the migration script after you did some changes on the model. This will generate a migration file and automatically migrate the table to the new version.
@Michael: It would be semi-automatic, i.e. you would have to start the migrate script manually.
@Brandon: Migrations are planned for 1.2, but I don’t know how they will look like. Psychic was experimenting with migrations some time ago in his sandbox: https://trac.cakephp.org/browser/whiteboard/sandbox/psychic
Btw: what happened with your planned cakecollab application/site?
@speedmax: The django example looks interesting. And yes, having the definition in the model helps to abstract the database details and should make it easier to work with different database systems.
@Nik: Yes, you are right, if you use a tool like DBDesigner such an approach doesn’t make much sense. But personally, I don’t use such tools, my tools are paper and pen ;-)
I don’t understand your second point. To use scaffolding, you need tables and models. The only thing that would change, would be the order in which you create them. With the approach described here you would create first the models and then generate the tables, whereas with the current approach you create first the tables and then you generate/create the models.
3. Yes, to compare two schemas you would need one of the approaches you described.
4. By generating migration files and using a migration infrastructure it should be easy to migrate.
@Brandon: Good points.
@chess64: Thanks for the link!
I think it’s a great idea, only *backwards*!
It would make more sense to me if you could create your schema in a dedicated tool (DB Designer for example), and then auto-create your model from that.
DB Designer also has the advantage of being able to update the schema without destroying existing data (as far as it can).
I toyed with the idea of creating a tool that would do this back when I started out with Cake.
Having started with Django and learning CakePHP in my experience having the table definitions a part of the model is another tool that really does speed up productivity quite a bit. I’d also like being to work solely with all my cake files and less through phpmyadmin on my projects.
@Richard, mbastien: Thanks for your comments.
@Richard: Well, I think it depends on the approach you use whether this idea makes sense.
If you work on the table level with a tool like DBDesigner, it is more logical to built first the tables and then the models (maybe a tool like dbdesigner4cake will help in this process).
But if you work on an object level with an UML tool, this order swaps, it is now more logical to create the models first and then to generate the tables. With that you no longer have to deal with details like foreign keys, it is automatically done for you.
@mbastien: Yes, I agree with you. Currently I am using an Ant script to execute the SQL script so that I don’t have to leave my IDE (eclipse).
what would be good ( altho a bit off topic ) is to have something like:
var $validate = array(’field1′=>’auto’,'field2′=>’auto’);
or even
var $validate = ‘auto’; // all
cake reads the validation rules from the db ( type, length, etc ) would add to productivity a little more
the less you write, the better it is.
@othman: Interesting idea (even though it is not compatible with the idea I described in this article *g*).
[...] information in the class declaration, though it does seem to be something that some Cake users are looking at. Having all the fields declared in the class made so much sense and was a convenient point when [...]