I'm new to CakePHP. Currently using cake 2.5. Also, I'm a newbie in this great community. I hope I make the questions in the correct way ;)
After reading and reading over the Internet and this community I still haven't found a solution for my problem.
The problem:
(If you are in a hurry, read ONLY this section. If you aren't in a hurry, you can find additional information about the exact context of my problem in the above section "Additional Info").
I'm developing a Cake application for reporting purposes which uses 2 databases: a legacy MS SQL Server database which is used as read-only (this is a must) for getting data. And a regular MySQL database that will be used by the Cake application for storing reports, user prefs and some data related with some records of the read-only database. Sorry but I can't post any piece of code or real names of database ítems because a non-disclosure agreement that I signed.
The Key Point:
I have a model with no table (i.e: $useTable = false) using a connection to the read-only database. Lets say ReadOnlyModel.
ReadOnlyModel gets data by running a custom query (this is a must). I achieve that by using $this->query($sql);.
Lets say the hard-coded MS SQL Server query by ReadOnlyModel is:
SELECT
TABLE_A.A_ID, TABLE_A.ONE_STRING,
TABLE_B.PK_COL1, TABLE_B.PK_COL2, TABLE_B.PK_COL3,
TABLE_B.AN_EXTERNAL_ID, TABLE_B.ONE_CODE, TABLE_B.ONE_DATE,
TABLE_B.ONE_NUMBER, TABLE_B.ANOTHER_EXTERNAL_ID,
TABLE_C.OTHER_EXTERNAL_ID
FROM SCHEMA_NAME.dbo.TABLE_A TABLE_A, SCHEMA_NAME.dbo.TABLE_B TABLE_B,
SCHEMA_NAME.dbo.TABLE_C TABLE_C
WHERE TABLE_A.A_ID = TABLE_C.A_ID AND TABLE_A.PK_COL1 = TABLE_B.PK_COL1
AND TABLE_A.PK_COL2 = TABLE_B.PK_COL2
ORDER BY TABLE_B.PK_COL1, TABLE_B.PK_COL2
The query runs ok and is getting correctly all the data. Despite there are three involved tables, for simplicity I'm going to use only one Cake model to encapsulate the data because from the point of view of my Cake application, it doesn't make sense to interact with TABLE_A, or with TABLE_B or with TABLE_C individually. Such "joined records" can be considered as "unique entities" in the Cake application side.
At that point, I need to achieve that this ReadOnlyModel behaves as a conventional and regular Cake model when dealing with common "reading" operations, i.e: I need to be able to use all the Cake functionalities about finding, filtering, instantiating a model by ID and so on.
Is that possible? What would be the right way? I have read the Models and Paginator section of the official Cake book and I don't find the way.
If it isn't possible, does it possible tu run the query and re-structure the retrieved data array in a way than CakePHP expects? For instance: Something like:
Array (
[ReadOnlyModel] => Array (
[id] => id_value1
[field1] => value1
[field2] => value2
[field3] => value3
...
)
[ReadOnlyModel] => Array (
[id] => id_value2
[field1] => value4
[field2] => value5
[field3] => value6
...
)
...
)
And then assign that array to the $data model property. Doing so, would act the Cake core as if the ReadOnlyModel was a normal and regular useTable=true model? Would Paginator works normally and correctly in that case?
If it isn't possible, is there any way to override or hack some of the CakePHP core for getting the above mentioned behaviour?
If not or if that is not the right way, what alternative approaches could I follow? Would it be implementing a custom Datasource a possible way to get success?
Thank you in advance!
PS: Sorry because I know my questions are very general, and no code samples are provided (because of the above mentioned NDA). However, my question is more about coding techniques or specific programming problems regarding with CakePHP 2.5 capabilities and limitations. Sorry because of my english level, too.
Additional info - Clarifiying my context and needs:
(If you aren't in a hurry, you can find below additional details about my system, it context and it requirements and the reasons of many design decissions that I've been forced to have made. Maybe, you can find here answers for your questions or doubts)
There is a really really BIG, MATURE, FULLY-TESTED, HYPER-OPTIMIZED, HIGH-SCALED AND COMPLEX legacy system in production. In fact, is the biggest system I have ever faced. That system will act as a read-only "master", as a huge read-only data provider.
I'm developing a Cake external application that only extracts several data from the "master" system, for reporting purposes mainly.
By requirement, there is no possibility of altering that "master" system in any way at all. So I can't include there my own tables. Because of this, my Cake application will have its own mysql database for storing reports, user preferences and, in 4-5 cases, storing some data related with some records of the "master" system database that are needed in some reporting tasks.
The core of that "master" system is a huge MS SQL Server database which have more than 500 tables. Some of that tables are very large and wide (for instance: about 100K records and 120 columns ). All table names and column names are not "cake-friendly" (i.e: capital letters and underscores everywhere). Besides, some tables have composite primary keys.
That "master" database is highly normalized, so there are tons of relations between tables, lots of levels of many to many relationships. For instance: A hasManyAndBelongsTo B, B hasManyAndBelongsTo C, C hasManyAndBelongsTo D and D hasManyAndBelongsTo E, 4 levels. Besides, each of A, B, C, D and E tables have additional belongsTo/HasMany/hasManyAndBelongsTo relationships with many many other tables.
My Cake application will be used ONLY for data extracting. Its not allowed any kind of operation that could finally introduce any modification in the state of the "master" system. DELETE or UPDATE querys are banned.
The Admin of the "master" system, will allow my Cake application to execute a set of previoysly known querys that I have to detail to him. I will provide him with a collection of query "signatures" that my Cake application will use and then he will made some kind of magic to include that querys in a "white-list" for allowing execute them at the "master" system database. Additionally, he will do a number of per-query optimization tasks in order to speed-up that querys.
So, that "white-list" will be the only connection point between the "master" system and my Cake application.
The Admin of the "master" systems wants me to specify my list of query signatures, according to the next rules:
- 1st: I have to select which tables are needed in each query
- 2nd: I have to select which existing schema columns will be used in each query. The "*" operator will not be allowed as column selector.
- 3rd: I can feel free to use any other kind of SQL stuff need in my querys such as aggregate functions, "on-the-fly" or calculated columns, conditions clause, using literal values, etc.
4th: all the previus elements will be authorized. The rest denied.
E.g:
SELECT A.C1, B.C2 FROM A INNER JOIN B ON A.C3 = B.C4. This signature let me operate ONLY with the A and B tables and with the C1, C2, C3, and C4 columns. Also, it would be possible to use aggregate functions, "on-the-fly" columns, and so on.E.g:
SELECT A.C1, B.C2, A.C1 + B.C2 AS 'NEW_COLUMN' FROM A INNER JOIN B ON A.C3 = B.C4would be a valid query. "NEW_COLUMN" could be used.E.g:
SELECT * FROM A INNER JOIN B ON A.C3 = B.C4will be rejected
Once I have defined my signatures querys, the rest of the database scheme not declared as needed will not be usable for my Cake application. Any future operation trying to access any table or column not declared in the white-list will be denied. And any other query that is not contained in the white-list will be rejected.
Conclussion: I have to limit the "automagically" query creation capability of CakePHP for avoiding that CakePHP could try to execute "unauthorized" queries. So the queries executed against the "master" database have to be hard-coded (although literals or injected values in the WHERE clause will be accepted).
