cakephp
Handle case specificities
Context : I'm trying to write an OciAdapter to handle migrations inside an Oracle database
Expected behaviour :
- the migration table is created automatically with no change, or a simple overriding
- after having created a migration, getVersionLog returns this migration
- after having run this migration, getVersionLog returns that this migration has been executed
Actual behaviour :
- the migration table is created
- getVersionLog always return an array with empty objects.
For certain SGBDs (Oracle, not to mention it), the case of the SELECTed field name can be different than the one returned.
CREATE TABLE (version ...) will lead to a column named VERSION. and in that case, SELECT version FROM ... will lead, when fetched, to an array whose key is "VERSION"
Note that quoting the column will preserve the case. But quoting columns require to always write it the same manner. If you query your table quoting the columns - i.e., SELECT "version" FROM ... -, then Oracle will give you an "Unknown column" error if you create the table with a "CREATE TABLE (version...) " that creates a column named VERSION.
This issue is here to find a solution that :
- is DB-Agnostic
- avoid rewriting an entire method (
getVersionLog, for instance) just to change ['version'] into ['VERSION'] - keep the code logical (if I write "SELECT version", I expect to be able to do a $result['version'])
PS : Moreover, in the present case (getVersionLog), we have a "select *", so fields are not even explicitely mentioned
For the "version" column of the migration table, I have thought to these solutions :
- quote the column on table creation (so that it is created as "version", lowercase)
- alias the column on fetch : SELECT version "version"
- automatically fix the case of the fetch
- make the fetch return an object with ArrayAccess, i.e make it able to return the row whatever the case of the [] specification is
Don't hesitate to tell me what you think about them
I think that just doing alias for column on fetch would be best as I agree that requiring to use quotes on all usages of the columns (including inserts/updates) would be very annoying, vs the few select clauses we do. (2) and (3) are very similar to doing the alias, but that I think the alias is simpler to follow through what's happening for people new to the code.
I believe that this would affect a hypothetical snowflake adapter if one were to be ever added phinx.
