php-crud-api
php-crud-api copied to clipboard
mevdschee
Make use of schema
Hi Maurits,
We have a database (MSSQL) that has several different schema's. Is there a way to use your api.php on this database? For what I see now is that the code does not make use of a schema variable/parameter. So only the (few) tables in the dbo schema are accessible. Do you consider adding schema functionality?
Regards,
Hugo Voerman
I guess this relates to multi-tenant schema solution as described here (the other two are supported):
http://stackoverflow.com/questions/8340721/multiple-schemas-versus-enormous-tables
The script does support multiple databases (but not multiple schema's), maybe allowing a dot in the table name (or database name) would be a good way to enable this. What do you think?
I think allowing a dot in the 'table' name as a qualifier for [schema].[table] would be great. without the dot (in case of MSSQL) the api could/should fallback to the dbo schema (for backwards compatibility). Do you know of any timing on this issue? I really like the script, but I really need the multiple schema support.
Sorry, I guess I'm to new to this, or does PR mean Problem Report?
Please tell me what to do? I'm also looking in the code and as you support multiple tables using a ',' in the GET (for instance) I really think the '.' should be used. I rewrote //, 'reflect_table' => 'SELECT table_schema+'.'+TABLE_NAME FROM "INFORMATION_SCHEMA"."TABLES" WHERE table_schema+'.'+TABLE_NAME LIKE ? AND "TABLE_CATALOG" = ?'
to trick it a little bit and saw (using lots of print_r's :) that it gets a bit further. Would this be a path to explore?
Okay, sorry about that. A PR is a "Pull Request", which means that you suggest the code that needs to be altered. You are right about the reflection queries that (all) need to be adjusted, but not in the way you suggest (you need to add "table_schema" as a condition) and that is not all that has to change. Other things are the parsing of the input parameter and the adjustment of the actual queries.
Changing reflect_pk to SELECT "COLUMN_NAME", * FROM "INFORMATION_SCHEMA"."TABLE_CONSTRAINTS" tc, "INFORMATION_SCHEMA"."KEY_COLUMN_USAGE" ku WHERE tc."CONSTRAINT_TYPE" = 'PRIMARY KEY' AND tc."CONSTRAINT_NAME" = ku."CONSTRAINT_NAME" AND tc.constraint_schema = ku.constraint_schema AND ku.constraint_schema+'.'+ku."TABLE_NAME" = ? AND ku."TABLE_CATALOG" = ?
also seems to do its job. The only thing is that I actually don't like the ku.constraint_schema+'.'+ku."TABLE_NAME" construction very much.
Sorry, i posted this after you sent the comment above. I'll look into the PR thing, hopefully I can figure it out.
Good to hear it does the job for you. I'll leave this ticket open as a reference for other people to upvote the functionality.