8000 Add `onUpdate` and `matchType` to the output of `analyze`/`read_schema` by kvch · Pull Request #654 · xataio/pgroll · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Add onUpdate and matchType to the output of analyze/read_schema #654

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.

Sign up for GitHub

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

Merged
merged 8 commits into from
Feb 4, 2025
Merged

Add onUpdate and matchType to the output of analyze/read_schema #654

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
8 commits
Select commit Hold shift + click to select a range
7b73e78
expose more fk info in analyze
kvch Feb 4, 2025
0fb02a7
expose more info in aalyze
kvch Feb 4, 2025
54e1163
adjust tests
kvch Feb 4, 2025
2c5f1b5
format
kvch Feb 4, 2025
0dba3a9
add more test
kvch Feb 4, 2025
9240add
one more
kvch Feb 4, 2025
4a26268
rm comma
kvch Feb 4, 2025
d471528
more test
kvch Feb 4, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion pkg/schema/schema.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -133,7 +133,7 @@ type ForeignKey struct {
OnUpdate string `json:"onUpdate"`

// MatchType is the match type of the foreign key
MatchType string `json:"match_type"`
MatchType string `json:"matchType"`
}

// CheckConstraint represents a check constraint on a table
Expand Down
28 changes: 22 additions & 6 deletions pkg/state/init.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -236,10 +236,16 @@ BEGIN
uc_constraint.conrelid = t.oid
AND uc_constraint.contype = 'u' GROUP BY uc_constraint.oid, uc_constraint.conname) AS uc_details), 'foreignKeys', (
SELECT
COALESCE(json_object_agg(fk_details.conname, json_build_object('name', fk_details.conname, 'columns', fk_details.columns, 'referencedTable', fk_details.referencedTable, 'referencedColumns', fk_details.referencedColumns, 'onDelete', fk_details.onDelete)), '{}'::json)
COALESCE(json_object_agg(fk_details.conname, json_build_object('name', fk_details.conname, 'columns', fk_details.columns, 'referencedTable', fk_details.referencedTable, 'referencedColumns', fk_details.referencedColumns, 'matchType', fk_details.matchType, 'onDelete', fk_details.onDelete, 'onUpdate', fk_details.onUpdate)), '{}'::json)
FROM (
SELECT
fk_info.conname AS conname, fk_info.columns AS columns, fk_info.relname AS referencedTable, array_agg(ref_attr.attname ORDER BY ref_attr.attname) AS referencedColumns, CASE WHEN fk_info.confdeltype = 'a' THEN
fk_info.conname AS conname, fk_info.columns AS columns, fk_info.relname AS referencedTable, array_agg(ref_attr.attname ORDER BY ref_attr.attname) AS referencedColumns, CASE WHEN fk_info.confmatchtype = 'f' THEN
'FULL'
WHEN fk_info.confmatchtype = 'p' THEN
'PARTIAL'
WHEN fk_info.confmatchtype = 's' THEN
'SIMPLE'
END AS matchType, CASE WHEN fk_info.confdeltype = 'a' THEN
'NO ACTION'
WHEN fk_info.confdeltype = 'r' THEN
'RESTRICT'
Expand All @@ -249,18 +255,28 @@ BEGIN
'SET DEFAULT'
WHEN fk_info.confdeltype = 'n' THEN
'SET NULL'
END AS onDelete FROM (
END AS onDelete, CASE WHEN fk_info.confupdtype = 'a' THEN
'NO ACTION'
WHEN fk_info.confupdtype = 'r' THEN
'RESTRICT'
WHEN fk_info.confupdtype = 'c' THEN
'CASCADE'
WHEN fk_info.confupdtype = 'd' THEN
'SET DEFAULT'
WHEN fk_info.confupdtype = 'n' THEN
'SET NULL'
END AS onUpdate FROM (
SELECT
fk_constraint.conname, fk_constraint.conrelid, fk_constraint.confrelid, fk_constraint.confkey, fk_cl.relname, fk_constraint.confdeltype, array_agg(fk_attr.attname ORDER BY fk_attr.attname) AS columns FROM pg_constraint AS fk_constraint
fk_constraint.conname, fk_constraint.conrelid, fk_constraint.confrelid, fk_constraint.confkey, fk_cl.relname, fk_constraint.confmatchtype, fk_constraint.confdeltype, fk_constraint.confupdtype, array_agg(fk_attr.attname ORDER BY fk_attr.attname) AS columns FROM pg_constraint AS fk_constraint
INNER JOIN pg_class fk_cl ON fk_constraint.confrelid = fk_cl.oid -- join the referenced table
INNER JOIN pg_attribute fk_attr ON fk_attr.attrelid = fk_constraint.conrelid
AND fk_attr.attnum = ANY (fk_constraint.conkey) -- join the columns of the referencing table
WHERE
fk_constraint.conrelid = t.oid
AND fk_constraint.contype = 'f' GROUP BY fk_constraint.conrelid, fk_constraint.conname, fk_constraint.confrelid, fk_cl.relname, fk_constraint.confkey, fk_constraint.confdeltype) AS fk_info
AND fk_constraint.contype = 'f' GROUP BY fk_constraint.conrelid, fk_constraint.conname, fk_constraint.confrelid, fk_cl.relname, fk_constraint.confkey, fk_constraint.confmatchtype, fk_constraint.confdeltype, fk_constraint.confupdtype) AS fk_info
INNER JOIN pg_attribute ref_attr ON ref_attr.attrelid = fk_info.confrelid
AND ref_attr.attnum = ANY (fk_info.confkey) -- join the columns of the referenced table
GROUP BY fk_info.conname, fk_info.conrelid, fk_info.columns, fk_info.confrelid, fk_info.confdeltype, fk_info.relname) AS fk_details))), '{}'::json)
GROUP BY fk_info.conname, fk_info.conrelid, fk_info.columns, fk_info.confrelid, fk_info.confmatchtype, fk_info.confdeltype, fk_info.confupdtype, fk_info.relname) AS fk_details))), '{}'::json)
FROM pg_class AS t
INNER JOIN pg_namespace AS ns ON t.relnamespace = ns.oid
LEFT JOIN pg_description AS descr ON t.oid = descr.objoid
Expand Down
205 changes: 205 additions & 0 deletions pkg/state/state_test.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -550,7 +550,9 @@ func TestReadSchema(t *testing.T) {
Columns: []string{"fk"},
ReferencedTable: "table1",
ReferencedColumns: []string{"id"},
MatchType: "SIMPLE",
OnDelete: "NO ACTION",
OnUpdate: "NO ACTION",
},
},
CheckConstraints: map[string]*schema.CheckConstraint{},
Expand Down Expand Up @@ -608,7 +610,129 @@ func TestReadSchema(t *testing.T) {
Columns: []string{"fk"},
ReferencedTable: "table1",
ReferencedColumns: []string{"id"},
MatchType: "SIMPLE",
OnDelete: "CASCADE",
OnUpdate: "NO ACTION",
},
},
CheckConstraints: map[string]*schema.CheckConstraint{},
UniqueConstraints: map[string]*schema.UniqueConstraint{},
},
},
},
},
{
name: "foreign key with ON DELETE CASCADE ON UPDATE CASCADE",
createStmt: "CREATE TABLE public.table1 (id int PRIMARY KEY); CREATE TABLE public.table2 (fk int NOT NULL, CONSTRAINT fk_fkey FOREIGN KEY (fk) REFERENCES public.table1 (id) ON DELETE CASCADE ON UPDATE CASCADE)",
wantSchema: &schema.Schema{
Name: "public",
Tables: map[string]*schema.Table{
"table1": {
Name: "table1",
Columns: map[string]*schema.Column{
"id": {
Name: "id",
Type: "integer",
Nullable: false,
Unique: true,
PostgresType: "base",
},
},
PrimaryKey: []string{"id"},
Indexes: map[string]*schema.Index{
"table1_pkey": {
Name: "table1_pkey",
Unique: true,
Columns: []string{"id"},
Method: string(migrations.OpCreateIndexMethodBtree),
Definition: "CREATE UNIQUE INDEX table1_pkey ON public.table1 USING btree (id)",
},
},
CheckConstraints: map[string]*schema.CheckConstraint{},
UniqueConstraints: map[string]*schema.UniqueConstraint{},
ForeignKeys: map[string]*schema.ForeignKey{},
},
"table2": {
Name: "table2",
Columns: map[string]*schema.Column{
"fk": {
Name: "fk",
Type: "integer",
Nullable: false,
PostgresType: "base",
},
},
PrimaryKey: []string{},
Indexes: map[string]*schema.Index{},
ForeignKeys: map[string]*schema.ForeignKey{
"fk_fkey": {
Name: "fk_fkey",
Columns: []string{"fk"},
ReferencedTable: "table1",
ReferencedColumns: []string{"id"},
MatchType: "SIMPLE",
OnDelete: "CASCADE",
OnUpdate: "CASCADE",
},
},
CheckConstraints: map[string]*schema.CheckConstraint{},
UniqueConstraints: map[string]*schema.UniqueConstraint{},
},
},
},
},
{
name: "foreign key with MATCH full ON DELETE CASCADE",
createStmt: "CREATE TABLE public.table1 (id int PRIMARY KEY); CREATE TABLE public.table2 (fk int NOT NULL, CONSTRAINT fk_fkey FOREIGN KEY (fk) REFERENCES public.table1 (id) MATCH FULL ON DELETE CASCADE)",
wantSchema: &schema.Schema{
Name: "public",
Tables: map[string]*schema.Table{
"table1": {
Name: "table1",
Columns: map[string]*schema.Column{
"id": {
Name: "id",
Type: "integer",
Nullable: false,
Unique: true,
PostgresType: "base",
},
},
PrimaryKey: []string{"id"},
Indexes: map[string]*schema.Index{
"table1_pkey": {
Name: "table1_pkey",
Unique: true,
Columns: []string{"id"},
Method: string(migrations.OpCreateIndexMethodBtree),
Definition: "CREATE UNIQUE INDEX table1_pkey ON public.table1 USING btree (id)",
},
},
CheckConstraints: map[string]*schema.CheckConstraint{},
UniqueConstraints: map[string]*schema.UniqueConstraint{},
ForeignKeys: map[string]*schema.ForeignKey{},
},
"table2": {
Name: "table2",
Columns: map[string]*schema.Column{
"fk": {
Name: "fk",
Type: "integer",
Nullable: false,
PostgresType: "base",
},
},
PrimaryKey: []string{},
Indexes: map[string]*schema.Index{},
ForeignKeys: map[string]*schema.ForeignKey{
"fk_fkey": {
Name: "fk_fkey",
Columns: []string{"fk"},
ReferencedTable: "table1",
ReferencedColumns: []string{"id"},
MatchType: "FULL",
OnDelete: "CASCADE",
OnUpdate: "NO ACTION",
},
},
CheckConstraints: map[string]*schema.CheckConstraint{},
Expand Down Expand Up @@ -837,7 +961,88 @@ func TestReadSchema(t *testing.T) {
Columns: []string{"customer_id", "product_id"},
ReferencedTable: "products",
ReferencedColumns: []string{"customer_id", "product_id"},
MatchType: "SIMPLE",
OnDelete: "NO ACTION",
OnUpdate: "NO ACTION",
},
},
CheckConstraints: map[string]*schema.CheckConstraint{},
UniqueConstraints: map[string]*schema.UniqueConstraint{},
},
},
},
},
{
name: "multicolumn foreign key constraint with on update action",
createStmt: `CREATE TABLE products(
customer_id INT NOT NULL,
product_id INT NOT NULL,
PRIMARY KEY(customer_id, product_id));

CREATE TABLE orders(
customer_id INT NOT NULL,
product_id INT NOT NULL,
CONSTRAINT fk_customer_product FOREIGN KEY (customer_id, product_id) REFERENCES products (customer_id, product_id) ON UPDATE CASCADE);`,
wantSchema: &schema.Schema{
Name: "public",
Tables: map[string]*schema.Table{
"products": {
Name: "products",
Columns: map[string]*schema.Column{
"customer_id": {
Name: "customer_id",
Type: "integer",
Nullable: false,
PostgresType: "base",
},
"product_id": {
Name: "product_id",
Type: "integer",
Nullable: false,
PostgresType: "base",
},
},
PrimaryKey: []string{"customer_id", "product_id"},
Indexes: map[string]*schema.Index{
"products_pkey": {
Name: "products_pkey",
Unique: true,
Columns: []string{"customer_id", "product_id"},
Method: string(migrations.OpCreateIndexMethodBtree),
Definition: "CREATE UNIQUE INDEX products_pkey ON public.products USING btree (customer_id, product_id)",
},
},
ForeignKeys: map[string]*schema.ForeignKey{},
CheckConstraints: map[string]*schema.CheckConstraint{},
UniqueConstraints: map[string]*schema.UniqueConstraint{},
},
"orders": {
Name: "orders",
Columns: map[string]*schema.Column{
"customer_id": {
Name: "customer_id",
Type: "integer",
Nullable: false,
PostgresType: "base",
},
"product_id": {
Name: "product_id",
Type: "integer",
Nullable: false,
PostgresType: "base",
},
},
PrimaryKey: []string{},
Indexes: map[string]*schema.Index{},
ForeignKeys: map[string]*schema.ForeignKey{
"fk_customer_product": {
Name: "fk_customer_product",
Columns: []string{"customer_id", "product_id"},
ReferencedTable: "products",
ReferencedColumns: []string{"customer_id", "product_id"},
MatchType: "SIMPLE",
OnDelete: "NO ACTION",
OnUpdate: "CASCADE",
},
},
CheckConstraints: map[string]*schema.CheckConstraint{},
Expand Down
0