I am new to this forum and Yii so please forgive me if I am reposting or reveal some ignorance about how Yii works.
I am using Postgres 8.2.3 with PHP 5.2.8. I have a database schema with a number of tables where both the table names and columns have mixed case. In order to preserve case I must surround my tables and columns in double quotes.
When I try to define relations in one of my models I run into trouble. Yii requires I include the double quotes when I specify the foreign key (because the schema stores the foreign keys with the double quotes). The problem is then CDbSchema::quoteColumnName() adds more double quotes!
I am not sure what the best fix is for this… or if I'm just doing something silly. I added quoteColumnName to CPgsqlSchema and modified it to only add double quotes if the given name does not have them but I am not sure this is really a fix.
Any thoughts on this would be appreciated. If you need more info let me know
CREATE TABLE "Account"
"accountID" BIGSERIAL PRIMARY KEY,
"name" VARCHAR(256) NOT NULL
CREATE TABLE "User"
"userID" BIGSERIAL PRIMARY KEY,
"accountID" BIGINT NOT NULL REFERENCES "Account" ON DELETE CASCADE ON UPDATE CASCADE
From the User model:
public function relations()
'account' => array(self::BELONGS_TO, 'Account', '"accountID"')
I'm not sure why it has extra double quotes, but it does and therefore joinOneMany (and perhaps other join functions, I didn't test) cannot find the foreign key. This is what made me try adding the double quotes in relations.
I realize I should have mentioned this at the start. I was a bit tired when I posted previously.
Could you please execute the following SQL and let me know what the result is?
SELECT conname, consrc, contype, indkey FROM (
CASE WHEN contype='f' THEN
'CHECK (' || consrc || ')'
END AS consrc,
conrelid AS relid,
NULL AS indkey
contype IN ('f', 'c')
CASE WHEN indisprimary THEN
AND EXISTS (
SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c
ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)
WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p')
) AS sub
WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='User'
AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace