Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

3.0 ORM Seems to fail reading Postgres procedure constraints #6150

Closed
tcql opened this issue Mar 23, 2015 · 4 comments

Comments

Projects
None yet
3 participants
@tcql
Copy link

commented Mar 23, 2015

I have an existing postgres schema that I'm trying to access with the ORM. The table has a procedure-enforced unique constraint:

CREATE UNIQUE INDEX users_username_unique
  ON users
  USING btree
  (username_comparator(username) COLLATE pg_catalog."C");

Here are my TableObject and Entity -- obviously super simple; I'm trying to just test out connections and retrieval here:

use Cake\ORM\Table;

class UsersTable extends Table
{
    public function initialize(array $config)
    {
        $this->table('users');
    }
}

use Cake\ORM\Entity;

class User extends Entity
{
}

But when I use find on the TableObject, I get back: Columns used in constraints must be added to the Table schema first. The column "username_comparator(username" was not found. in database/Schema/Table.php:509

@lorenzo lorenzo added this to the 3.0.1 milestone Mar 23, 2015

@lorenzo lorenzo added the defect label Mar 23, 2015

@markstory markstory added the postgres label Mar 23, 2015

@markstory

This comment has been minimized.

Copy link
Member

commented Mar 23, 2015

A big part of the issue here is that we are forced to parse the constraint definition SQL to find columns as Postgres doesn't give us a very good way to find columns that are used by constraints. We'll have to make the postgres schema reflection ignore constraints that don't use columns.

@markstory markstory self-assigned this Mar 23, 2015

@tcql

This comment has been minimized.

Copy link
Author

commented Mar 23, 2015

According to the internet (and my quick testing) something like this should get you all column-based constraints (including multi-column uniques, etc), excluding procedure based constraints:

SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,

rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
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 = 'users'

You could trim that down to remove stuff about constraint usage, and probably filter out check constraints. Does that not work?

@markstory

This comment has been minimized.

Copy link
Member

commented Mar 24, 2015

@tchannel I can give that a shot, our current constraint reflection code is not as robust as I would like.

markstory added a commit that referenced this issue Mar 24, 2015

Describe foreign keys in a way that excludes procedures.
Use information_schema to exclude procedure based foreign keys in schema
reflection. We have no way of managing or re-using procedure based
constraints so we should just exclude them from our reflections.

Refs #6150

markstory added a commit that referenced this issue Mar 24, 2015

Describe foreign keys in a way that excludes procedures.
Use information_schema to exclude procedure based foreign keys in schema
reflection. We have no way of managing or re-using procedure based
constraints so we should just exclude them from our reflections.

Refs #6150
@markstory

This comment has been minimized.

Copy link
Member

commented Mar 24, 2015

Fixed with #6154. Thanks for the improved query @tchannel 😄

@markstory markstory closed this Mar 24, 2015

zoghal added a commit to zoghal/cakephp that referenced this issue Mar 29, 2015

Describe foreign keys in a way that excludes procedures.
Use information_schema to exclude procedure based foreign keys in schema
reflection. We have no way of managing or re-using procedure based
constraints so we should just exclude them from our reflections.

Refs cakephp#6150
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.