Hello,
If you are interested, i successfully extended the genfkey tool to make sure
all declared foreign keys have an associated index.
Tables 'temp.idx2' and 'temp.idx' cannot be reused because of the 'il.isunique'
clause...
Thanks Dan for your response in the previous post.
Thanks all for your kindness.
--- /tmp/genfkey.c 2008-10-25 13:24:03.000000000 +0200
+++ genfkey.c 2008-10-25 13:42:09.000000000 +0200
@@ -663,6 +663,23 @@
"fkid, from_tbl, to_tbl, sj(dq(to_col),',') AS cols "
"FROM (SELECT * FROM temp.fkey ORDER BY to_col) "
"GROUP BY fkid, from_tbl;"
+
+ "CREATE TABLE temp.idx3 AS SELECT "
+ "il.tablename AS tablename,"
+ "ii.indexname AS indexname,"
+ "ii.name AS col "
+ "FROM temp.v_idxlist AS il, temp.v_idxinfo AS ii "
+ "WHERE il.database='main' AND ii.indexname = il.name;"
+
+ "CREATE TABLE temp.idx4 AS SELECT "
+ "tablename, indexname, sj(dq(col),',') AS cols "
+ "FROM (SELECT * FROM temp.idx3 ORDER BY col) "
+ "GROUP BY tablename, indexname;"
+
+ "CREATE TABLE temp.fkey3 AS SELECT "
+ "fkid, from_tbl, sj(dq(from_col),',') AS cols "
+ "FROM (SELECT * FROM temp.fkey ORDER BY from_col) "
+ "GROUP BY fkid, from_tbl;"
, 0, 0, pzErr
);
if( rc!=SQLITE_OK ) return rc;
@@ -674,6 +691,14 @@
")", pHasErrors
);
if( rc!=SQLITE_OK ) return rc;
+ rc = detectSchemaProblem(db, "foreign key has no associated index",
+ "SELECT fkid, from_tbl "
+ "FROM temp.fkey3 "
+ "WHERE NOT EXISTS (SELECT 1 "
+ "FROM temp.idx4 WHERE tablename=from_tbl AND fkey3.cols==idx4.cols"
+ ")", pHasErrors
+ );
+ if( rc!=SQLITE_OK ) return rc;
return rc;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
