Customizable ActiveRecord property - Table Column mapping
Posted 24 November 2009 - 05:22 PM
I just wonder if it possible to make Yii AR feature configurable how to map AR Object Properties with Database table column names.
We has many application with with numerous tables and it's just too hard to convert all tables to the current Yii AR conventions.
Name the table with module prefix and table name:
ex: core_users <- this can be easily mapped with the model's tableName
But we like to name our columns with prefix and underscore like
this way is lot easier to write complex sqls.
But in AR object is so ugly to work attribute names like $usr_cr_date we prefer $crDate
As I see in source CDbColumnSchema - name property is used for mapping and rawName for sql commands. So this would be an easy task if we can somehow tell the corresponding CDBSChema implemetation how to fetch name property from database.
Maybe we can define columnprefix in model like tableName, and pass a configuration descriptor to the CDBSchema which tells how to generate the CDbColumnSchema name property from the table descriptor (like remove prefixes, rip off underscores, uppercase words first chars etc.)
That way AR model - db mapping could be more flexible.
Posted 18 May 2011 - 06:56 AM
This is not actually an answer, but rather an addition to the previous comment.
Besides the "prefix" issue of Silver, there are also two others (which are cause of bother to me):
- Accentuated (non ASCII) characters
- spaces in column names
Column names are usually chosen to be meaningful. This can actually, sometimes, mean giving a column the actual name of what it contains, such as "firstname". Now, "firstname" is "Prénom" in French (notice the acute over the 'e'). So, with CActiveRecord, you end-up with an object with member $user->Prénom ...
Now, I'm not sure that PHP is too happy with non-ASCII names (variables, members, functions, ...) and I am sure that, further along the chain, they cause havoc with zii.widgets.CDetailView (generated an application exception).
Even though I do not use column names with white-space in them, there is nothing in SQL which would prevent you from doing so, and somebody may find it meaningful to call a column `arrival time` rather than `arrivalTime`.
How would PHP cope with a $user->arrival time member?
So, all that being said, I had one remark and one question:
The remark: instead of making columns members (via __get/__set) and exploiting it in other classes, wouldn't it have been safer to have just recorded them in an associative array with the keys being the column names?
The request: is there a way (without dirtily and messily changing the core Yii code) of interfering on the column-name to class member mapping so as to "remap" column names which could cause trouble (such as ones with white-spaces or non-ASCII characters)?
Thanks in advance for answering both remark and question.
Posted 18 May 2011 - 07:59 PM
I basically create a new class (ActiveRecord) which extends CActiveRecord and overloads the following member functions:
- init (so as to do the remapping when an active record is created by "new")
- instanciate and afterFind (so as to do the remapping when an active record is created by a "class::model()->findXxx(...)")
Then, any sub-class of "ActiveRecord" which wants to do remapping just has to declare a member function called "protected function ARreMap()" which returns an array of the mappings as follows: array('column name' => 'name to be used for the active record members').
It works very well... The only problem is that in the code generated by gii, the actual table column names appear, and they have to be changed for the name defined in the remapping... The next step would probably be to generate a tool (or a variant of gii) to automate the modification of the gii generated files...
Anyway, if anybody likes this code, please let me know...
Number of downloads: 63
Posted 19 May 2011 - 04:30 AM
having done that little "messing around" with Active Records yesterday (see previous post), it seems to me, a posteriori, that CActiveRecord, and all its "paraphernalia" are extremely complex, rather "fat" (loads of data structures, with quite a bit of it redundant, like having the data base table "tableSchema" and a copy of its columns ("CDbColumnSchema") both in the meta-data), doing data-base accesses (to build the meta-data) even before any actual data access and trying to do quite a few things dynamically (the meta-data building, and hence the attributes, and hence the public members of the class), but still has to resort (it's not a designe issue: there's no choice) to some explicit, static, coding related to the table structure (e.g. in the "rules()", "attributeLabels()" and "search()" overloaded member functions).
And, but I'm a "newbie" to Yii as the label on the left of the post shows, I am not that sure that all this complexity and "weight" actually brings the "magic" that other parts of Yii bring.
I would like some feed-back from users more experienced with Yii on that aspect...
At this point in time, I am quite keen on designing a "CPassiveRecord" class, clearly inspired by some aspects (and code) of the "CActiveRecord" class but:
- lighter weight
- keeping actual data-base column names and values in an associative array (with potential renaming of columns to answer Silver's initial request in post #1) instead of class members (or do you call them "properties" here?)
- with less dynamic behaviour, since, anyway, the code generator (an extension of gii) will always have to put some non-dynamic elements in the code...
The reason why I initially got into the desire of renaming data-base columns in CActiveRecord is because I am attempting to transfer to Yii an existing application, with an already existing and largely populated data-base, and, yes, some column, names have accentuated characters, and, yes, that caused me trouble with the "standard" ActiveRecord/gii generated controller/gii generated CRUD code...
Posted 30 May 2011 - 02:19 AM
Anyway, if anybody likes this code, please let me know...
I am keen to hear something about your progress. I don't see a nice solution to use yii with legacy databases.
It would be great to have some single point to override the mapping. Thus one would have a very thin layer between class properties and actual table columns.
Maybe somebody of the official developer team could comment on that?