Hi,
I need (and have attempted to add) limited support for foreign key
constraints mapping to virtual tables. It seems this is not possible
currently in sqlite, or at least, it gives me "foreign key mismatch" when I
try.
My use-case is this:
create virtual table vtbl using vtblXYZ;
create table x(i integer references vtbl(id));
insert into x(i) values(5);
where vtblXYZ is declared:
sqlite3_declare_vtab(db, "CREATE TABLE a(id INTEGER PRIMARY KEY,data TEXT
...
The patch below provides this support, but is limited to mapping to integer
primary keys on the virtual table, and it works as far as I know and am able
to test. My question is, is there a reason why virtual tables
can't/shouldn't be used in this way. Is my approach valid or is it likely
to back-fire!?
Many thanks for your input.
Andy
(This patch is based off version 3.7.10, sorry that its so old!)
Index: sqlite3.c
===================================================================
--- sqlite3.c (revision 9938)
+++ sqlite3.c (working copy)
@@ -83132,7 +83132,7 @@ SQLITE_PRIVATE void sqlite3AddPrimaryKey
Table *pTab = pParse->pNewTable;
char *zType = 0;
int iCol = -1, i;
- if( pTab==0 || IN_DECLARE_VTAB ) goto primary_key_exit;
+ if( pTab==0 ) goto primary_key_exit;
if( pTab->tabFlags & TF_HasPrimaryKey ){
sqlite3ErrorMsg(pParse,
"table \"%s\" has more than one primary key", pTab->zName);
@@ -83169,7 +83169,7 @@ SQLITE_PRIVATE void sqlite3AddPrimaryKey
sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
"INTEGER PRIMARY KEY");
#endif
- }else{
+ }else if( !IN_DECLARE_VTAB ){
Index *p;
p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0,
sortOrder, 0);
if( p ){
@@ -89121,10 +89121,45 @@ static void fkLookupParent(
sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk, regTemp);
}
- sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
- sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp);
- sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
- sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
+ if (pTab->tabFlags & TF_Virtual) {
+ /* For a INTEGER PRIMARY KEY in a virtual table, we need to build
+ ** an explicit "EXISTS ( SELECT 1 FROM parent WHERE pkey=fkey )"
+ ** expression. */
+ sqlite3* db = pParse->db;
+ Expr* pPIK = sqlite3Expr(db, TK_ID, pFKey->aCol[0].zCol ?: "oid");
/* use ROWID if zCol null */
+ Expr* pFIK = sqlite3Expr(db, TK_REGISTER, 0);
+ if( pFIK ){
+ pFIK->iTable = regTemp;
+ }
+
+ Token tFrom;
+ tFrom.z = pTab->zName;
+ tFrom.n = sqlite3Strlen30(tFrom.z);
+
+ Select* pSelect = sqlite3SelectNew(pParse,
+ sqlite3ExprListAppend(pParse, 0, sqlite3PExpr(pParse,
TK_INTEGER, 0, 0, &sqlite3IntTokens[1])), /* SELECT 1 */
+ sqlite3SrcListAppend(db, 0, &tFrom, 0), /* FROM parent */
+ sqlite3PExpr(pParse, TK_EQ, pPIK, pFIK, 0), /* WHERE pkey=fkey
*/
+ 0, 0, 0, 0,
+ sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[1]),
/* LIMIT 1 */
+ 0
+ );
+
+ SelectDest dest;
+ int regTemp2 = sqlite3GetTempReg(pParse);
+ sqlite3SelectDestInit(&dest, SRT_Exists, regTemp2);
+ sqlite3VdbeAddOp2(v, OP_Integer, 0, regTemp2); /* initialise target
register */
+ sqlite3Select(pParse, pSelect, &dest);
+ sqlite3VdbeAddOp3(v, OP_If, regTemp2, iOkNoCur, 0); /* if found,
then ok */
+
+ sqlite3SelectDelete(db, pSelect);
+ sqlite3ReleaseTempReg(pParse, regTemp2);
+ }else{
+ sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
+ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp);
+ sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
+ sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
+ }
sqlite3VdbeJumpHere(v, iMustBeInt);
sqlite3ReleaseTempReg(pParse, regTemp);
}else{
@@ -89192,6 +89227,7 @@ static void fkLookupParent(
sqlite3VdbeResolveLabel(v, iOk);
sqlite3VdbeAddOp1(v, OP_Close, iCur);
+ sqlite3VdbeResolveLabel(v, iOkNoCur);
}
/*
@@ -104518,6 +104553,13 @@ SQLITE_API int sqlite3_declare_vtab(sqli
&& (pParse->pNewTable->tabFlags & TF_Virtual)==0
){
if( !pTab->aCol ){
+ if( (pParse->pNewTable->tabFlags & TF_HasPrimaryKey) &&
+ pParse->pNewTable->iPKey >= 0 ){
+ assert( !(pTab->tabFlags & TF_HasPrimaryKey) );
+ pTab->tabFlags |= TF_HasPrimaryKey;
+ pTab->iPKey = pParse->pNewTable->iPKey;
+ pTab->keyConf = pParse->pNewTable->keyConf;
+ }
pTab->aCol = pParse->pNewTable->aCol;
pTab->nCol = pParse->pNewTable->nCol;
pParse->pNewTable->nCol = 0;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
