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
Examples:
Simplified Schema:
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()
{
return array(
'account' => array(self::BELONGS_TO, 'Account', '"accountID"')
);
}
If I do not use the double quotes I receive the following CDbException:
Quote
The relation "account" in active record class "User" is specified with an invalid foreign key "accountID". The foreign key does not point to either joining table.
If I var_dump (or similar) $fke->_table->foreignKeys in CActiveFinder::joinOneMany(), I see that the key does have the extra double quotes:
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 (
SELECT
conname,
CASE WHEN contype='f' THEN
pg_catalog.pg_get_constraintdef(oid)
ELSE
'CHECK (' || consrc || ')'
END AS consrc,
contype,
conrelid AS relid,
NULL AS indkey
FROM
pg_catalog.pg_constraint
WHERE
contype IN ('f', 'c')
UNION ALL
SELECT
pc.relname,
NULL,
CASE WHEN indisprimary THEN
'p'
ELSE
'u'
END,
pi.indrelid,
indkey
FROM
pg_catalog.pg_class pc,
pg_catalog.pg_index pi
WHERE
pc.oid=pi.indexrelid
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
WHERE nspname='public'))
