Ar Or Dao For Large Amounts Of Data

Hi guys

I have a business system that I want to convert to Yii. However, this system has monster size reports (crunching data accumulated over several years) with sql statements nested inside other sql statements (some more than half a page long).

How should I go about doing this in Yii? To replicate these sql statements using AR could be a nightmare. But if I use DAO, then I loose all the RBAC or ACL; and I thus have no way to filter the records according to the user’s permissions.

What about an Access Control layer between the application and the DB? That way, both AR and DAO should be able to use it. And it would also enable you to use Row Level Access Control. Preferably, some external report applications - like Jasper - should also be able to use it.

Some people recommend using DB views, where you can filter the records passed to AR or DAO (or stored procedures since MySql’s views can’t receive parameters).

Any advice please.

Thanx

Hi Gerhard,

I don’t understand this.

Is it possible to have RBAC control over AR operation?

Hi softark.

I don’t know. I don’t use Yii’s RBAC. I guess you can manipulate model filtering with business rules. I know you can do it with the controller’s accessRules() function via an expression.

I build my own RBAC because I need Row Level Access Control (which leans more towards ACL).

There are many ways to try this such as with "relational query criteria", "default scope", "parameterized named scopes", "behaviors with beforeFind()", etc. but defaultScope() works the best, because the other options are not always activated in all circumstances e.g. beforeFind() does not work with related models when using relational query.

However, some of these technologies - such as default scope - are only available in AR. DAO does not use AR models. So, if you use DAO to build complex sql statements, then you loose ‘default scope’ and your Row Level Access Control.

I see.

So you are saying that we can implement access controlling of db records much easier with AR than with DAO, aren’t you? If I’m not misunderstood, yes, I agree with you.

Yes that is correct, but:

  • I think AR can be difficult to create complex sql statements,

  • AR eats lots of memory when used for complex sql (all frameworks’ AR, not just Yii AR),

  • I don’t think external reporting programs can use Yii AR, so you have to duplicate your access control procedures somewhere else.

On the other hand:

AR can connect to multiple databases on separate machines, because each model can use a separate connection.

But, DAO sql can only use one connection at a time:


$sql = "SELECT * FROM ... "

$connection = Yii::app()->db;

$command = $connection->createCommand($sql);

$results = $command->queryAll();

So, you have to query the different machines separately and then merge the results.

What I did not try yet is using DAO with CModels.

  1. Can that be used with multiple machines simultaneously?

  2. Does CModel also use lots of memory - like AR?