Customizable ActiveRecord property - Table Column mapping

Archives Feature Requests
1

Hello,

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.

Our 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

usr_id

usr_login

usr_cr_date

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.

Regards,

Silver

2

Hello Y’all!

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):

[list=1]

[*]Accentuated (non ASCII) characters

[*]spaces in column names

[/list]

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.

Dom

3

After quite a bit of searching around, I came up with a relatively clean scheme for remapping data-base column names before they become ActiveRecord members…

I basically create a new class ([font="Lucida Console"]ActiveRecord[/font]) which extends CActiveRecord and overloads the following member functions:

  • [font="Lucida Console"]init[font="Arial"] (so as to do the remapping when an active record is created by "[font="Lucida Console"]new[font="Arial"]")[/font][/font][/font][/font]
  • [font="Lucida Console"]instanciate[font="Arial"] and [/font]afterFind[font="Arial"] (so as to do the remapping when an active record is created by a "[font="Lucida Console"]class::model()->findXxx(…)[font="Arial"]")[/font][/font][/font][/font]

[font=“Lucida Console”][font=“Arial”][font=“Lucida Console”][font=“Arial”]I store the remapping information as an extra property of the class’ associated “[font=“Lucida Console”]CActiveRecordMetaData[/font]”.

Then, any sub-class of “[/font][/font][/font][/font][font=“Lucida Console”]ActiveRecord[/font][font=“Lucida Console”][font=“Arial”][font=“Lucida Console”][font=“Arial”]” which wants to do remapping just has to declare a member function called “[font=“Lucida Console”]protected function ARreMap()[/font]” 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…

Cheers,

Dom

[/font][/font][/font][/font]

4

CActiveRecord unduly complex and heavy? - Please, comment.

Hi,

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 “[font=“Lucida Console”]tableSchema[/font]” and a copy of its columns ("[font=“Lucida Console”]CDbColumnSchema[/font]") 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 “[font=“Lucida Console”]rules()[/font]”, “[font=“Lucida Console”]attributeLabels()[/font]” and “[font=“Lucida Console”]search()[/font]” 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 "[font="Lucida Console"]CPassiveRecord[/font]" class, clearly inspired by some aspects (and code) of the "[font="Lucida Console"]CActiveRecord[/font]" 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…

Once again, have I gone completely overboard? Am I throwing the baby with the bath-water? Have I grossly missed the beauty and magic of ActivRecords?

Please, comment.

PS/background info:

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…

5

Hi Dom,

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?

Best Regards

Sebastian

6

Great work Dom

Small adjustment is required for [font="Courier New"]ActiveRecord::_internalARreMap()[/font]

[font="Courier New"]$MetaD =& $this->getMetaData();[/font] should be changed to [font="Courier New"]$MetaD = $this->getMetaData();[/font]