Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Improved conflict-target matching logic. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | upsert |
| Files: | files | file ages | folders |
| SHA3-256: |
98d32ba661f4ba662b639994b74352b6 |
| User & Date: | drh 2018-04-13 13:44:48 |
Context
|
2018-04-13
| ||
| 14:27 | Get the conflict-target clause parsing working correctly, with test cases. This change involves an enhancement to sqlite3ExprCompare() which needs to be reviewed on trunk prior to merging. check-in: 5bf70425 user: drh tags: upsert | |
| 13:44 | Improved conflict-target matching logic. check-in: 98d32ba6 user: drh tags: upsert | |
| 13:06 | Back off of the extended upsert syntax that allows multiple ON CONFLICT clauses. The syntax now is exactly as in PostgreSQL and MySQL. Add support for WHERE clauses on the conflict-target phrase, for partial indexes. check-in: 2c1b1987 user: drh tags: upsert | |
Changes
Changes to src/upsert.c.
82 83 84 85 86 87 88 89 90 91 92 93 94 95 ... 113 114 115 116 117 118 119 120 121 122 123 124 125 126 ... 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 |
Upsert *pUpsert /* The ON CONFLICT clauses */
){
NameContext sNC;
Table *pTab;
Index *pIdx;
ExprList *pTarget;
Expr *pTerm;
int rc;
assert( pTabList->nSrc==1 );
assert( pTabList->a[0].pTab!=0 );
assert( pUpsert!=0 );
assert( pUpsert->pUpsertTarget!=0 );
................................................................................
&& (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
&& (pTerm->iColumn==XN_ROWID || pTerm->iColumn==pTab->iPKey)
){
/* The conflict-target is the rowid of the primary table */
assert( pUpsert->pUpsertIdx==0 );
return SQLITE_OK;
}
/* Check for matches against other indexes */
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
int ii, jj, nn;
if( !IsUniqueIndex(pIdx) ) continue;
if( pTarget->nExpr!=pIdx->nKeyCol ) continue;
if( pIdx->pPartIdxWhere ){
................................................................................
if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere,
pIdx->pPartIdxWhere, pTabList->a[0].iCursor)!=0 ){
continue;
}
}
nn = pIdx->nKeyCol;
for(ii=0; ii<nn; ii++){
if( pIdx->aiColumn[ii]!=XN_EXPR ){
for(jj=0; jj<nn; jj++){
if( pTarget->a[jj].pExpr->op!=TK_COLUMN ) continue;
if( pTarget->a[jj].pExpr->iColumn!=pIdx->aiColumn[ii] ) continue;
break;
}
}else{
Expr *pExpr;
assert( pIdx->aColExpr!=0 );
assert( pIdx->aColExpr->nExpr>ii );
pExpr = pIdx->aColExpr->a[ii].pExpr;
for(jj=0; jj<nn; jj++){
if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr, -1)==0 ){
break;
}
}
}
if( jj<nn ) break;
}
if( ii>=nn ) continue;
pUpsert->pUpsertIdx = pIdx;
return SQLITE_OK;
}
sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
"PRIMARY KEY or UNIQUE constraint");
return SQLITE_ERROR;
}
#endif /* SQLITE_OMIT_UPSERT */
|
> > > > > > > > > > > > > < < < < < < < | > > > > > > | | < > > > | | > > > < > > > | > |
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 ... 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 ... 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 |
Upsert *pUpsert /* The ON CONFLICT clauses */
){
NameContext sNC;
Table *pTab;
Index *pIdx;
ExprList *pTarget;
Expr *pTerm;
Expr sCol[2];
int rc;
assert( pTabList->nSrc==1 );
assert( pTabList->a[0].pTab!=0 );
assert( pUpsert!=0 );
assert( pUpsert->pUpsertTarget!=0 );
................................................................................
&& (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
&& (pTerm->iColumn==XN_ROWID || pTerm->iColumn==pTab->iPKey)
){
/* The conflict-target is the rowid of the primary table */
assert( pUpsert->pUpsertIdx==0 );
return SQLITE_OK;
}
/* Initialize sCol[0..1] to be an expression parse tree for a
** single column of an index. The sCol[0] node will be the TK_COLLATE
** operator and sCol[1] will be the TK_COLUMN operator. Code below
** will populate the specific collation and column number values
** prior to comparing against the conflict-target expression.
*/
memset(sCol, 0, sizeof(sCol));
sCol[0].op = TK_COLLATE;
sCol[0].pLeft = &sCol[1];
sCol[1].op = TK_COLUMN;
sCol[1].iTable = pTabList->a[0].iCursor;
/* Check for matches against other indexes */
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
int ii, jj, nn;
if( !IsUniqueIndex(pIdx) ) continue;
if( pTarget->nExpr!=pIdx->nKeyCol ) continue;
if( pIdx->pPartIdxWhere ){
................................................................................
if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere,
pIdx->pPartIdxWhere, pTabList->a[0].iCursor)!=0 ){
continue;
}
}
nn = pIdx->nKeyCol;
for(ii=0; ii<nn; ii++){
Expr *pExpr;
if( pIdx->aiColumn[ii]==XN_EXPR ){
assert( pIdx->aColExpr!=0 );
assert( pIdx->aColExpr->nExpr>ii );
pExpr = pIdx->aColExpr->a[ii].pExpr;
}else{
sCol[1].iColumn = pIdx->aiColumn[ii];
sCol[0].u.zToken = (char*)pIdx->azColl[ii];
pExpr = &sCol[0];
}
for(jj=0; jj<nn; jj++){
if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr,
pTabList->a[0].iCursor)<2
){
break; /* Column ii of the index matches column jj of target */
}
}
if( jj>=nn ){
/* The target contains no match for column jj of the index */
break;
}
}
if( ii<nn ){
/* Column ii of the index did not match any term of the conflict target.
** Continue the search with the next index. */
continue;
}
pUpsert->pUpsertIdx = pIdx;
return SQLITE_OK;
}
sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
"PRIMARY KEY or UNIQUE constraint");
return SQLITE_ERROR;
}
#endif /* SQLITE_OMIT_UPSERT */
|
Changes to test/upsert1.test.
35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
SELECT * FROM t1;
} {1 {no such column: x}}
do_catchsql_test upsert1-120 {
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_catchsql_test upsert1-130 {
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
if 0 {
do_catchsql_test upsert1-200 {
DROP INDEX t1x1;
DELETE FROM t1;
CREATE UNIQUE INDEX t1x1 ON t1(a||b);
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||b) DO NOTHING;
SELECT * FROM t1;
} {0 {5 6}}
}
finish_test
|
> < | > > > > > | |
35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
SELECT * FROM t1;
} {1 {no such column: x}}
do_catchsql_test upsert1-120 {
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
breakpoint
do_catchsql_test upsert1-130 {
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_catchsql_test upsert1-200 {
DROP INDEX t1x1;
DELETE FROM t1;
CREATE UNIQUE INDEX t1x1 ON t1(a||b);
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||b) DO NOTHING;
SELECT * FROM t1;
} {0 {5 6 0}}
do_catchsql_test upsert1-210 {
DELETE FROM t1;
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||+b) DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
finish_test
|