Dear all,
The CDbSchema does not says anything about unique keys.
But I need to know which columns are unique to display the values of theses columns as a comprehensive key for the user.
I read a little the code and found that variables $isUniqueKey have to be added into CDbColumnSchema.php, and $uniqueKey into CDbTableSchema.php and the C*Schema.php
For CPgsqlSchema, the SQL in findConstraints() can return the unique keys, changing contype IN (‘f’, ‘c’, ‘u’) and cloning findPrimaryKey().
This is the diff for CPgsqlSchema.
198c198
< SELECT conname, consrc, contype, indkey, conkey FROM (
---
> SELECT conname, consrc, contype, indkey FROM (
208,209c208
< NULL AS indkey,
< conkey
---
> NULL AS indkey
213c212
< contype IN ('f', 'c', 'u')
---
> contype IN ('f', 'c')
224,225c223
< indkey,
< NULL AS conkey
---
> indkey
250,251d247
< else if($row['contype']==='u' && $row['conkey'] !== null) // unique key
< $this->findUniqueKey($table,$row['conname'],$row['conkey']);
292,327d287
< * Collects unique key information.
< * @param CPgsqlTableSchema the table metadata
< * @param string pgsql unique key index list
< */
< protected function findUniqueKey($table,$ukname,$indices)
< {
< $indices=substr($indices,1,-1);
< $sql=<<<EOD
< SELECT attnum, attname FROM pg_catalog.pg_attribute WHERE
< attrelid=(
< SELECT oid FROM pg_catalog.pg_class WHERE relname=:table AND relnamespace=(
< SELECT oid FROM pg_catalog.pg_namespace WHERE nspname=:schema
< )
< )
< AND attnum IN ({$indices})
< EOD;
< $command=$this->getDbConnection()->createCommand($sql);
< $command->bindValue(':table',$table->name);
< $command->bindValue(':schema',$table->schemaName);
< $uniques=array();
< foreach($command->queryAll() as $row)
< {
< $name=$row['attname'];
< if(isset($table->columns[$name]))
< {
< $table->columns[$name]->isUniqueKey=true;
< $uniques[]=$name;
< }
< }
< if($table->uniqueKey===null)
< $table->uniqueKey=array($ukname=>$uniques);
< else
< $table->uniqueKey[$ukname]=$uniques;
< }
<
< /**
It works, but an over reading could be great.
In another project, I used this simple query :
SELECT DISTINCT
tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM
information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE
tc.table_name = :table AND constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE');
Maybe it could help.