Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem in the legacy genfkey code to do with column names that require quoting. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
273ccbf18af5191807678a0a0c09cda8 |
| User & Date: | dan 2009-10-08 13:42:28 |
Context
|
2009-10-08
| ||
| 17:42 | Add test cases to test file "e_fkey.test". check-in: 23e0f61a user: dan tags: trunk | |
| 13:42 | Fix a problem in the legacy genfkey code to do with column names that require quoting. check-in: 273ccbf1 user: dan tags: trunk | |
| 11:57 | Add tests to test/e_fkey.test. check-in: 533703d7 user: dan tags: trunk | |
Changes
Changes to src/shell.c.
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
...
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
|
", '/on_update/', on_update"
", '/name/', 'genfkey' || min(rowid)"
", '/tbl/', dq(from_tbl)"
", '/ref/', dq(to_tbl)"
", '/key_notnull/', sj('new.' || dq(from_col) || ' IS NOT NULL', ' AND ')"
", '/fkey_list/', sj(to_col, ', ')"
", '/rkey_list/', sj(from_col, ', ')"
", '/cond1/', sj(multireplace('new./from/ == /to/'"
", '/from/', dq(from_col)"
", '/to/', dq(to_col)"
"), ' AND ')"
", '/cond2/', sj(multireplace('old./to/ == /from/'"
", '/from/', dq(from_col)"
", '/to/', dq(to_col)"
"), ' AND ')"
", '/update_action/', CASE on_update "
"WHEN 'SET NULL' THEN "
"multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
", '/setlist/', sj(from_col||' = NULL',', ')"
", '/tbl/', dq(from_tbl)"
", '/where/', sj(from_col||' = old.'||dq(to_col),' AND ')"
")"
"WHEN 'CASCADE' THEN "
"multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
", '/setlist/', sj(dq(from_col)||' = new.'||dq(to_col),', ')"
", '/tbl/', dq(from_tbl)"
", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
")"
................................................................................
"ELSE "
" 'SELECT RAISE(ABORT, ''constraint failed'');'"
"END "
", '/delete_action/', CASE on_delete "
"WHEN 'SET NULL' THEN "
"multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
", '/setlist/', sj(from_col||' = NULL',', ')"
", '/tbl/', dq(from_tbl)"
", '/where/', sj(from_col||' = old.'||dq(to_col),' AND ')"
")"
"WHEN 'CASCADE' THEN "
"multireplace('DELETE FROM /tbl/ WHERE /where/;' "
", '/tbl/', dq(from_tbl)"
", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
")"
"ELSE "
|
|
|
|
|
|
|
|
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
...
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
|
", '/on_update/', on_update"
", '/name/', 'genfkey' || min(rowid)"
", '/tbl/', dq(from_tbl)"
", '/ref/', dq(to_tbl)"
", '/key_notnull/', sj('new.' || dq(from_col) || ' IS NOT NULL', ' AND ')"
", '/fkey_list/', sj(dq(to_col), ', ')"
", '/rkey_list/', sj(dq(from_col), ', ')"
", '/cond1/', sj(multireplace('new./from/ == /to/'"
", '/from/', dq(from_col)"
", '/to/', dq(to_col)"
"), ' AND ')"
", '/cond2/', sj(multireplace('old./to/ == /from/'"
", '/from/', dq(from_col)"
", '/to/', dq(to_col)"
"), ' AND ')"
", '/update_action/', CASE on_update "
"WHEN 'SET NULL' THEN "
"multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
", '/setlist/', sj(dq(from_col)||' = NULL',', ')"
", '/tbl/', dq(from_tbl)"
", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
")"
"WHEN 'CASCADE' THEN "
"multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
", '/setlist/', sj(dq(from_col)||' = new.'||dq(to_col),', ')"
", '/tbl/', dq(from_tbl)"
", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
")"
................................................................................
"ELSE "
" 'SELECT RAISE(ABORT, ''constraint failed'');'"
"END "
", '/delete_action/', CASE on_delete "
"WHEN 'SET NULL' THEN "
"multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
", '/setlist/', sj(dq(from_col)||' = NULL',', ')"
", '/tbl/', dq(from_tbl)"
", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
")"
"WHEN 'CASCADE' THEN "
"multireplace('DELETE FROM /tbl/ WHERE /where/;' "
", '/tbl/', dq(from_tbl)"
", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
")"
"ELSE "
|
Changes to tool/genfkey.test.
287 288 289 290 291 292 293 294 |
} {1 {constraint failed}}
do_test genfkey-5.5 {
catchsql {
INSERT INTO "t.3" VALUES(1);
INSERT INTO t13 VALUES(1);
}
} {0 {}}
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 |
} {1 {constraint failed}}
do_test genfkey-5.5 {
catchsql {
INSERT INTO "t.3" VALUES(1);
INSERT INTO t13 VALUES(1);
}
} {0 {}}
# Test also column names that require quoting.
do_test genfkey-6.1 {
execsql {
DROP TABLE "t.3";
DROP TABLE t13;
CREATE TABLE p(
"a.1 first", "b.2 second",
UNIQUE("a.1 first", "b.2 second")
);
CREATE TABLE c(
"c.1 I", "d.2 II",
FOREIGN KEY("c.1 I", "d.2 II")
REFERENCES p("a.1 first", "b.2 second")
ON UPDATE CASCADE ON DELETE CASCADE
);
}
} {}
do_test genfkey-6.2 {
set rc [catch {exec ./sqlite3 test.db .genfkey} msg]
} {0}
do_test genfkey-6.3 {
execsql $msg
execsql {
INSERT INTO p VALUES('A', 'B');
INSERT INTO p VALUES('C', 'D');
INSERT INTO c VALUES('A', 'B');
INSERT INTO c VALUES('C', 'D');
UPDATE p SET "a.1 first" = 'X' WHERE rowid = 1;
DELETE FROM p WHERE rowid = 2;
}
execsql { SELECT * FROM c }
} {X B}
do_test genfkey-6.4 {
execsql {
DROP TABLE p;
DROP TABLE c;
CREATE TABLE parent("a.1", PRIMARY KEY("a.1"));
CREATE TABLE child("b.2", FOREIGN KEY("b.2") REFERENCES parent("a.1"));
}
set rc [catch {exec ./sqlite3 test.db .genfkey} msg]
} {0}
do_test genfkey-6.5 {
execsql $msg
execsql {
INSERT INTO parent VALUES(1);
INSERT INTO child VALUES(1);
}
catchsql { UPDATE parent SET "a.1"=0 }
} {1 {constraint failed}}
do_test genfkey-6.6 {
catchsql { UPDATE child SET "b.2"=7 }
} {1 {constraint failed}}
do_test genfkey-6.7 {
execsql {
SELECT * FROM parent;
SELECT * FROM child;
}
} {1 1}
|