ON DELETE / ON UPDATE of inline foreign keys lost with file input (-i)
Nobody has claimed this yet.
Assessment
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Newbie friendliness
- 86/100
Research direction
Start in Ora2Pg.pm around _parse_constraint() at line 3060, then follow how _create_foreign_keys() uses DELETE_RULE and UPDATE_RULE. Reproduce with the t.sql example and the ora2pg file-input command; done means inline CASCADE, SET NULL, and MySQL ON UPDATE CASCADE are preserved while unchanged constraints retain their existing output.
Written by the indexing model from the issue text.
Description
While testing the IDENTITY fix I noticed another one with file input: a foreign key declared inside CREATE TABLE always comes out as ON DELETE NO ACTION, whatever the source says. Oracle, MySQL and MSSQL input all do it, current master (d53a3df):
$ cat t.sql
CREATE TABLE po (id NUMBER NOT NULL, CONSTRAINT po_pk PRIMARY KEY (id));
CREATE TABLE co1 (id NUMBER NOT NULL, pid NUMBER, CONSTRAINT co1_fk FOREIGN KEY (pid) REFERENCES po (id) ON DELETE CASCADE);
CREATE TABLE co2 (id NUMBER NOT NULL, pid NUMBER, CONSTRAINT co2_fk FOREIGN KEY (pid) REFERENCES po (id) ON DELETE SET NULL);
$ ora2pg -c /dev/null -i t.sql -t TABLE -o out.sql
$ grep 'FOREIGN KEY' out.sql
ALTER TABLE co1 ADD CONSTRAINT co1_fk FOREIGN KEY (pid) REFERENCES po(id) ON DELETE NO ACTION INITIALLY IMMEDIATE;
ALTER TABLE co2 ADD CONSTRAINT co2_fk FOREIGN KEY (pid) REFERENCES po(id) ON DELETE NO ACTION INITIALLY IMMEDIATE;
The schema loads fine, so nothing warns you, but deleting a parent now fails instead of cascading:
postgres=# INSERT INTO po VALUES (1);
postgres=# INSERT INTO co1 VALUES (10, 1);
postgres=# DELETE FROM po WHERE id = 1;
ERROR: update or delete on table "po" violates foreign key constraint "co1_fk" on table "co1"
DETAIL: Key (id)=(1) is still referenced from table "co1".
The ALTER TABLE ... ADD CONSTRAINT ... ON DELETE CASCADE form is fine, only the inline one is affected: _parse_constraint() pushes empty strings for DELETE_RULE and UPDATE_RULE (Ora2Pg.pm:3060), so _create_foreign_keys() falls back to NO ACTION.
This fixes it here:
$novalidate = 'NOT VALIDATED' if ($c =~ /NOVALIDATE/);
+ my $delete_rule = '';
+ $delete_rule = uc($1) if ($c =~ /\bON\s+DELETE\s+(CASCADE|SET\s+NULL|SET\s+DEFAULT|RESTRICT|NO\s+ACTION)\b/is);
+ $delete_rule =~ s/\s+/ /g;
+ my $update_rule = '';
+ $update_rule = uc($1) if ($c =~ /\bON\s+UPDATE\s+(CASCADE|SET\s+NULL|SET\s+DEFAULT|RESTRICT|NO\s+ACTION)\b/is);
+ $update_rule =~ s/\s+/ /g;
# CONSTRAINT_NAME,R_CONSTRAINT_NAME,SEARCH_CONDITION,DELETE_RULE,$deferrable,DEFERRED,R_OWNER,TABLE_NAME,OWNER,UPDATE_RULE,VALIDATED
- push(@{$self->{tables}{$tb_name}{foreign_key}}, [ ($c_name,'','','',$deferrable,$deferred,'',$tb_name,'','',$novalidate) ]);
+ push(@{$self->{tables}{$tb_name}{foreign_key}}, [ ($c_name,'','',$delete_rule,$deferrable,$deferred,'',$tb_name,'',$update_rule,$novalidate) ]);
Tested on PG 16 with Oracle, MySQL (-m) and MSSQL (-M) input: CASCADE and SET NULL are kept and behave as in the source, ON UPDATE CASCADE from MySQL too. FKs without a rule still get NO ACTION, DEFERRABLE INITIALLY DEFERRED is unchanged, and the ALTER TABLE form gives the same output as before.
- Dominant language
- Perl
- Stars
- 1.2k
- Forks
- 380
- Avg merge
- 6h 55m
- Merged PRs (30d)
- 1
Getting set up
This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from darold/ora2pg
-
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
-
Difficulty 5/5 Over a week Newbie friendliness 20/100
-
Difficulty 3/5 1-2 days Newbie friendliness 45/100
-
Difficulty 4/5 3-5 days Newbie friendliness 35/100
-
Difficulty 3/5 1-2 days Newbie friendliness 45/100
Similar issues
-
bug
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
RotherOSS/otobo#6286 · 1 comment ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 83/100
LMS-Community/slimserver#1692 · 6 comments ·
Maintainers usually reply within 1 day
-
fix(api): API v3 Product Read ignores request language (lc) for sample products and image URLsPossibly taken @muntasir-396 claimed this 3 days ago. Open
Difficulty 2/5 1-3 hours Newbie friendliness 88/100
openfoodfacts/openfoodfacts-server#15042 · 1 comment ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 75/100
Maintainers usually reply within 3 days