Re: Am I crazy? My continued quest for column prefixing
i didnt't realize why the use of views should a) be not portable and b) not indexable ? nearly every database supports views, so i could't see a problem there. and the columns listed in a view of course might be indexed in the underlaying table. the only problem i see is that you might not be able to insert data into views. in oracle that's possible, but you had to write an instead of-trigger for each view. don't know if mysql supports tha. but if you want to create a readonly interface to oyur database table with cake, i can see no problem in using views. On 12 Jan., 02:25, Robby Anderson [EMAIL PROTECTED] wrote: After deciding mysql views won't work - well, technically they will, but its a not good solution - not portable (mysql only), not scalable (no indexing) and in general a risky proposition - I'm now on a new hunt: how to use Cake callbacks to support a $columnPrefix field in my models. Why? I have a legacy database to work with where each table's columns have a consistent column name prefix (ie, all tables in user start with usr_) and while Cake easily supports that, I want to use pretty syntax and clean code. Instead of being littered with usr_login and usr_first_name and such, I want login and first_name and other nice clean stuff. Am I crazy? Is this a silly, fruitless quest? Will converting data back and forth kill my performance more than using mysql views, no indexes and all? Is this just worthless, and should I just use my prefixed columns? I dunno, I dunno. But I'm game to find out. (And if you have answers for any of those questions ... I'm game to hear those, too.) So, my first trick was to parse the results of a find and strip them out using everyone's best friend afterFind(). Here are the first generation, brute force results that work on basic models and associations (haven't tested it very deeply yet):http://bin.cakephp.org/saved/27230 If you have comments about ways to optimize this, I'm all ears. Beyond that, here are the other steps as I see them: * Use the prefixStripper() in afterSave() to convert the data returned from saves. * Make beforeFind() and beforeSave() parse all the input parameters, and convert any non-prefixed column names into prefixed column names. These I suspect will be the harder ones to code to catch all the various cases. Again I ask, is this a waste of time, or is this a useful endeavor? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Am I crazy? My continued quest for column prefixing
Thanks, deefens. I didn't think I was totally crazy about the view idea. :) I did some research on the mysql pages, and you _can_ write to mysql views. I also did some research here, and it appears that views aren't officially supported in Cake, and some folks claimed they didn't work, period. That doesn't mean they don't work, though - I did some testing, and simple reads/writes actually work just fine. The constraint seems to be that the view is a single table view (no aliasing) - which is fine for my purposes. For some reason, though, that approach still worries me, and I can't put my finger on it. So, on the code approach angle, I followed up on Nate's idea, and I don't think I'm going to go that low level. For one, I'd like to stay away from DB specific approach, and for two, I'd have to do something funky to catch the model so I can fetch all the associations properly. And besides, after digging around some more in the model and schema class code, the existing methods do such a fantastic job of prepping and organizing the data before and after database access, it seems such a shame to waste all that work. :) So instead, I wrote a few custom methods: __getColumnPrefixData() - which reads the model and its associations and builds a small table of the models, aliases, and prefixes. __columnPrefixer() - which takes the data input and makes sure its formatted for the engine, and then de-formats it before handing it back __columnPrefixerEngine() - which loops through a standard dataset and adds or strips column prefixes I also overrode the Model::save() and Model::saveAll() methods to prefix the inbound data prior to saving (calling the appropriate parent method), and then strip the prefixes from the outbound data returned (for save() - saveAll() doesn't return data). And afterFind() calls the prefixer to strip prefixes from returned data. Modifying beforeFind() was interesting (yay regular expressions!), but again, Cake does such a nice job of standarizing the data format prior to hitting the database, this was actually pretty easy. The conditions had to be looped through to sub occurrences of Model.field and make them Model.prefix_field (with a check to make sure if its already got a prefix, it gets left alone - in fact, all of these methods do that in case back end code calls multiple methods on the same dataset). Fields are order are easy, and I'm going to look at the joins next. I also converted my previous beforeSave() for custom created/modified fields to play nicely with these new methods, and it gets called in the save()/saveAll() methods after the prefixer but before sending the data to the parent method. I still need to look through the delete operations, and make sure that all the various save/update/find operations funnel through the methods, but I'm really happy with this approach, not to mention its been a great Cake learning experience. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Am I crazy? My continued quest for column prefixing
The question of whether it's a waste of time, or is this a useful
endeavor depends largely on your level tolerance for ugly prefixes all
over your code. If you are going to do it, however, I would say
afterFind is not the answer. A far better bet would be to subclass
the MySQL driver and override the resultSet() and fetchResult()
methods. That's going to look something like this:
?php
/*
* app/models/datasources/dbo/dbo_mysql2.php
*/
uses('model/datasources/dbo/dbo_mysql');
class DboMysql2 extends DboMysql {
function resultSet($results) {
// code
}
function fetchResult() {
// more code
}
}
?
Then in your database.php, set 'driver' = 'mysql'. Lucky for you
those two methods are short, because you're on your own for the rest.
Especially the query conditions bit.
On Jan 11, 8:25 pm, Robby Anderson [EMAIL PROTECTED] wrote:
After deciding mysql views won't work - well, technically they will,
but its a not good solution - not portable (mysql only), not scalable
(no indexing) and in general a risky proposition - I'm now on a new
hunt: how to use Cake callbacks to support a $columnPrefix field in my
models. Why? I have a legacy database to work with where each table's
columns have a consistent column name prefix (ie, all tables in user
start with usr_) and while Cake easily supports that, I want to use
pretty syntax and clean code. Instead of being littered with usr_login
and usr_first_name and such, I want login and first_name and other
nice clean stuff.
Am I crazy? Is this a silly, fruitless quest? Will converting data
back and forth kill my performance more than using mysql views, no
indexes and all? Is this just worthless, and should I just use my
prefixed columns? I dunno, I dunno. But I'm game to find out. (And if
you have answers for any of those questions ... I'm game to hear
those, too.)
So, my first trick was to parse the results of a find and strip them
out using everyone's best friend afterFind(). Here are the first
generation, brute force results that work on basic models and
associations (haven't tested it very deeply
yet):http://bin.cakephp.org/saved/27230
If you have comments about ways to optimize this, I'm all ears. Beyond
that, here are the other steps as I see them:
* Use the prefixStripper() in afterSave() to convert the data returned
from saves.
* Make beforeFind() and beforeSave() parse all the input parameters,
and convert any non-prefixed column names into prefixed column names.
These I suspect will be the harder ones to code to catch all the
various cases.
Again I ask, is this a waste of time, or is this a useful endeavor?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups Cake
PHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Am I crazy? My continued quest for column prefixing
Thanks, nate. Thats useful info. The datasource doesn't have direct access to the model, does it? The column prefixes are different by table, so (if I understand it right), if I extend the mySQL driver, I'll have to extend other classes to pass the model name (or at least the column prefix down), or build a table of table - column maps and stash it through Config or something - or, I suppose, look at the table name and pick up the prefix that way, assuming the convention will hold (which sadly, I'm not sure if it will). Hmm. Food for thought. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Am I crazy? My continued quest for column prefixing
The DataSource has direct access to the model all over the place. Look at all the places in DboSouce/DboMysql where it says $model. On Jan 12, 12:35 am, Robby Anderson [EMAIL PROTECTED] wrote: Thanks, nate. Thats useful info. The datasource doesn't have direct access to the model, does it? The column prefixes are different by table, so (if I understand it right), if I extend the mySQL driver, I'll have to extend other classes to pass the model name (or at least the column prefix down), or build a table of table - column maps and stash it through Config or something - or, I suppose, look at the table name and pick up the prefix that way, assuming the convention will hold (which sadly, I'm not sure if it will). Hmm. Food for thought. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
