Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Support for STRICT tables. A table with the STRICT option only allows a few core datatypes on columns and enforces those types rigidly. Also, PRIMARY KEY columns must be NOT NULL. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
f9c1d3441b87ee296542faa724410d16 |
User & Date: | drh 2021-08-20 15:44:48.339 |
Context
2021-08-20
| ||
19:51 | Improvement to error handling in Lemon. No impact on SQLite. Forum post 2f468f43cbc48d7f (check-in: 18cc2f8574 user: drh tags: trunk) | |
15:44 | Support for STRICT tables. A table with the STRICT option only allows a few core datatypes on columns and enforces those types rigidly. Also, PRIMARY KEY columns must be NOT NULL. (check-in: f9c1d3441b user: drh tags: trunk) | |
08:05 | Improved error messages for the SQLITE_CONSTRAINT_DATATYPE error. (Closed-Leaf check-in: 39abca0147 user: drh tags: strict-tables) | |
2021-08-18
| ||
22:26 | Fix ON CONFLICT clause processing for NOT NULL constraints of PRIMARY KEY columns on WITHOUT ROWID tables. Ticket [f2be158c57aaa8c6] (check-in: 13abba0941 user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
2552 2553 2554 2555 2556 2557 2558 | ** "CREATE TABLE ... AS SELECT ..." statement. The column names of ** the new table will match the result set of the SELECT. */ void sqlite3EndTable( Parse *pParse, /* Parse context */ Token *pCons, /* The ',' token after the last column defn. */ Token *pEnd, /* The ')' before options in the CREATE TABLE */ | | | 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 | ** "CREATE TABLE ... AS SELECT ..." statement. The column names of ** the new table will match the result set of the SELECT. */ void sqlite3EndTable( Parse *pParse, /* Parse context */ Token *pCons, /* The ',' token after the last column defn. */ Token *pEnd, /* The ')' before options in the CREATE TABLE */ u32 tabOpts, /* Extra table options. Usually 0. */ Select *pSelect /* Select from a "CREATE ... AS SELECT" */ ){ Table *p; /* The new table */ sqlite3 *db = pParse->db; /* The database connection */ int iDb; /* Database in which the table lives */ Index *pIdx; /* An implied index of the table */ |
︙ | ︙ | |||
2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 | if( pSelect ){ sqlite3ErrorMsg(pParse, ""); return; } p->tnum = db->init.newTnum; if( p->tnum==1 ) p->tabFlags |= TF_Readonly; } assert( (p->tabFlags & TF_HasPrimaryKey)==0 || p->iPKey>=0 || sqlite3PrimaryKeyIndex(p)!=0 ); assert( (p->tabFlags & TF_HasPrimaryKey)!=0 || (p->iPKey<0 && sqlite3PrimaryKeyIndex(p)==0) ); /* Special processing for WITHOUT ROWID Tables */ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 | if( pSelect ){ sqlite3ErrorMsg(pParse, ""); return; } p->tnum = db->init.newTnum; if( p->tnum==1 ) p->tabFlags |= TF_Readonly; } /* Special processing for tables that include the STRICT keyword: ** ** * Do not allow custom column datatypes. Every column must have ** a datatype that is one of INT, INTEGER, REAL, TEXT, or BLOB. ** ** * If a PRIMARY KEY is defined, other than the INTEGER PRIMARY KEY, ** then all columns of the PRIMARY KEY must have a NOT NULL ** constraint. */ if( tabOpts & TF_Strict ){ int ii; p->tabFlags |= TF_Strict; for(ii=0; ii<p->nCol; ii++){ Column *pCol = &p->aCol[ii]; if( pCol->eCType==COLTYPE_CUSTOM ){ sqlite3ErrorMsg(pParse, "unknown datatype for %s.%s: \"%s\"", p->zName, pCol->zCnName, sqlite3ColumnType(pCol, "") ); return; } if( (pCol->colFlags & COLFLAG_PRIMKEY)!=0 && p->iPKey!=ii && pCol->notNull == OE_None ){ pCol->notNull = OE_Abort; p->tabFlags |= TF_HasNotNull; } } } assert( (p->tabFlags & TF_HasPrimaryKey)==0 || p->iPKey>=0 || sqlite3PrimaryKeyIndex(p)!=0 ); assert( (p->tabFlags & TF_HasPrimaryKey)!=0 || (p->iPKey<0 && sqlite3PrimaryKeyIndex(p)==0) ); /* Special processing for WITHOUT ROWID Tables */ |
︙ | ︙ |
Changes to src/global.c.
︙ | ︙ | |||
347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 | ** Name of the default collating sequence */ const char sqlite3StrBINARY[] = "BINARY"; /* ** Standard typenames. These names must match the COLTYPE_* definitions. ** Adjust the SQLITE_N_STDTYPE value if adding or removing entries. */ const unsigned char sqlite3StdTypeLen[] = { 4, 3, 7, 4, 4 }; const char sqlite3StdTypeAffinity[] = { SQLITE_AFF_BLOB, SQLITE_AFF_INTEGER, SQLITE_AFF_INTEGER, SQLITE_AFF_REAL, SQLITE_AFF_TEXT }; const char *sqlite3StdType[] = { "BLOB", "INT", "INTEGER", "REAL", "TEXT" | > > > > > > > > > > > > > > > > > > > | 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 | ** Name of the default collating sequence */ const char sqlite3StrBINARY[] = "BINARY"; /* ** Standard typenames. These names must match the COLTYPE_* definitions. ** Adjust the SQLITE_N_STDTYPE value if adding or removing entries. ** ** sqlite3StdType[] The actual names of the datatypes. ** ** sqlite3StdTypeLen[] The length (in bytes) of each entry ** in sqlite3StdType[]. ** ** sqlite3StdTypeAffinity[] The affinity associated with each entry ** in sqlite3StdType[]. ** ** sqlite3StdTypeMap[] The type value (as returned from ** sqlite3_column_type() or sqlite3_value_type()) ** for each entry in sqlite3StdType[]. */ const unsigned char sqlite3StdTypeLen[] = { 4, 3, 7, 4, 4 }; const char sqlite3StdTypeAffinity[] = { SQLITE_AFF_BLOB, SQLITE_AFF_INTEGER, SQLITE_AFF_INTEGER, SQLITE_AFF_REAL, SQLITE_AFF_TEXT }; const char sqlite3StdTypeMap[] = { SQLITE_BLOB, SQLITE_INTEGER, SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT }; const char *sqlite3StdType[] = { "BLOB", "INT", "INTEGER", "REAL", "TEXT" |
︙ | ︙ |
Changes to src/insert.c.
︙ | ︙ | |||
106 107 108 109 110 111 112 113 114 115 | pIdx->zColAff[n] = 0; } return pIdx->zColAff; } /* ** Compute the affinity string for table pTab, if it has not already been ** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities. ** | > > > > > > | > > | | | | | | | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | 106 107 108 109 110 111 112 113 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 140 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 | pIdx->zColAff[n] = 0; } return pIdx->zColAff; } /* ** Make changes to the evolving bytecode to do affinity transformations ** of values that are about to be gathered into a row for table pTab. ** ** For ordinary (legacy, non-strict) tables: ** ----------------------------------------- ** ** Compute the affinity string for table pTab, if it has not already been ** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities. ** ** If the affinity string is empty (because it was all SQLITE_AFF_BLOB entries ** which were then optimized out) then this routine becomes a no-op. ** ** Otherwise if iReg>0 then code an OP_Affinity opcode that will set the ** affinities for register iReg and following. Or if iReg==0, ** then just set the P4 operand of the previous opcode (which should be ** an OP_MakeRecord) to the affinity string. ** ** A column affinity string has one character per column: ** ** Character Column affinity ** --------- --------------- ** 'A' BLOB ** 'B' TEXT ** 'C' NUMERIC ** 'D' INTEGER ** 'E' REAL ** ** For STRICT tables: ** ------------------ ** ** Generate an appropropriate OP_TypeCheck opcode that will verify the ** datatypes against the column definitions in pTab. If iReg==0, that ** means an OP_MakeRecord opcode has already been generated and should be ** the last opcode generated. The new OP_TypeCheck needs to be inserted ** before the OP_MakeRecord. The new OP_TypeCheck should use the same ** register set as the OP_MakeRecord. If iReg>0 then register iReg is ** the first of a series of registers that will form the new record. ** Apply the type checking to that array of registers. */ void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){ int i, j; char *zColAff; if( pTab->tabFlags & TF_Strict ){ if( iReg==0 ){ /* Move the previous opcode (which should be OP_MakeRecord) forward ** by one slot and insert a new OP_TypeCheck where the current ** OP_MakeRecord is found */ VdbeOp *pPrev; sqlite3VdbeAppendP4(v, pTab, P4_TABLE); pPrev = sqlite3VdbeGetOp(v, -1); assert( pPrev!=0 ); assert( pPrev->opcode==OP_MakeRecord || sqlite3VdbeDb(v)->mallocFailed ); pPrev->opcode = OP_TypeCheck; sqlite3VdbeAddOp3(v, OP_MakeRecord, pPrev->p1, pPrev->p2, pPrev->p3); }else{ /* Insert an isolated OP_Typecheck */ sqlite3VdbeAddOp2(v, OP_TypeCheck, iReg, pTab->nNVCol); sqlite3VdbeAppendP4(v, pTab, P4_TABLE); } return; } zColAff = pTab->zColAff; if( zColAff==0 ){ sqlite3 *db = sqlite3VdbeDb(v); zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1); if( !zColAff ){ sqlite3OomFault(db); return; } |
︙ | ︙ | |||
153 154 155 156 157 158 159 160 161 162 163 164 165 166 | } assert( zColAff!=0 ); i = sqlite3Strlen30NN(zColAff); if( i ){ if( iReg ){ sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i); }else{ sqlite3VdbeChangeP4(v, -1, zColAff, i); } } } /* ** Return non-zero if the table pTab in database iDb or any of its indices | > > | 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 | } assert( zColAff!=0 ); i = sqlite3Strlen30NN(zColAff); if( i ){ if( iReg ){ sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i); }else{ assert( sqlite3VdbeGetOp(v, -1)->opcode==OP_MakeRecord || sqlite3VdbeDb(v)->mallocFailed ); sqlite3VdbeChangeP4(v, -1, zColAff, i); } } } /* ** Return non-zero if the table pTab in database iDb or any of its indices |
︙ | ︙ | |||
2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 | return 0; /* tab2 may not be a view or virtual table */ } if( pDest->nCol!=pSrc->nCol ){ return 0; /* Number of columns must be the same in tab1 and tab2 */ } if( pDest->iPKey!=pSrc->iPKey ){ return 0; /* Both tables must have the same INTEGER PRIMARY KEY */ } for(i=0; i<pDest->nCol; i++){ Column *pDestCol = &pDest->aCol[i]; Column *pSrcCol = &pSrc->aCol[i]; #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS if( (db->mDbFlags & DBFLAG_Vacuum)==0 && (pDestCol->colFlags | pSrcCol->colFlags) & COLFLAG_HIDDEN | > > > | 2832 2833 2834 2835 2836 2837 2838 2839 2840 2841 2842 2843 2844 2845 2846 2847 2848 | return 0; /* tab2 may not be a view or virtual table */ } if( pDest->nCol!=pSrc->nCol ){ return 0; /* Number of columns must be the same in tab1 and tab2 */ } if( pDest->iPKey!=pSrc->iPKey ){ return 0; /* Both tables must have the same INTEGER PRIMARY KEY */ } if( (pDest->tabFlags & TF_Strict)!=0 && (pSrc->tabFlags & TF_Strict)==0 ){ return 0; /* Cannot feed from a non-strict into a strict table */ } for(i=0; i<pDest->nCol; i++){ Column *pDestCol = &pDest->aCol[i]; Column *pSrcCol = &pSrc->aCol[i]; #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS if( (db->mDbFlags & DBFLAG_Vacuum)==0 && (pDestCol->colFlags | pSrcCol->colFlags) & COLFLAG_HIDDEN |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
192 193 194 195 196 197 198 | ifnotexists(A) ::= . {A = 0;} ifnotexists(A) ::= IF NOT EXISTS. {A = 1;} %type temp {int} %ifndef SQLITE_OMIT_TEMPDB temp(A) ::= TEMP. {A = pParse->db->init.busy==0;} %endif SQLITE_OMIT_TEMPDB temp(A) ::= . {A = 0;} | | | > | > > | > > > > > > > > | 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 | ifnotexists(A) ::= . {A = 0;} ifnotexists(A) ::= IF NOT EXISTS. {A = 1;} %type temp {int} %ifndef SQLITE_OMIT_TEMPDB temp(A) ::= TEMP. {A = pParse->db->init.busy==0;} %endif SQLITE_OMIT_TEMPDB temp(A) ::= . {A = 0;} create_table_args ::= LP columnlist conslist_opt(X) RP(E) table_option_set(F). { sqlite3EndTable(pParse,&X,&E,F,0); } create_table_args ::= AS select(S). { sqlite3EndTable(pParse,0,0,0,S); sqlite3SelectDelete(pParse->db, S); } %type table_option_set {u32} %type table_option {u32} table_option_set(A) ::= . {A = 0;} table_option_set(A) ::= table_option(A). table_option_set(A) ::= table_option_set(X) COMMA table_option(Y). {A = X|Y;} table_option(A) ::= WITHOUT nm(X). { if( X.n==5 && sqlite3_strnicmp(X.z,"rowid",5)==0 ){ A = TF_WithoutRowid | TF_NoVisibleRowid; }else{ A = 0; sqlite3ErrorMsg(pParse, "unknown table option: %.*s", X.n, X.z); } } table_option(A) ::= nm(X). { if( X.n==6 && sqlite3_strnicmp(X.z,"strict",6)==0 ){ A = TF_Strict; }else{ A = 0; sqlite3ErrorMsg(pParse, "unknown table option: %.*s", X.n, X.z); } } columnlist ::= columnlist COMMA columnname carglist. columnlist ::= columnname carglist. columnname(A) ::= nm(A) typetoken(Y). {sqlite3AddColumn(pParse,A,Y);} |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 | for(x=sqliteHashFirst(pTbls); x; x=sqliteHashNext(x)){ Table *pTab = sqliteHashData(x); Index *pIdx, *pPk; Index *pPrior = 0; int loopTop; int iDataCur, iIdxCur; int r1 = -1; if( pTab->tnum<1 ) continue; /* Skip VIEWs or VIRTUAL TABLEs */ if( pObjTab && pObjTab!=pTab ) continue; pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab); sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenRead, 0, 1, 0, &iDataCur, &iIdxCur); /* reg[7] counts the number of entries in the table. | > | 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 | for(x=sqliteHashFirst(pTbls); x; x=sqliteHashNext(x)){ Table *pTab = sqliteHashData(x); Index *pIdx, *pPk; Index *pPrior = 0; int loopTop; int iDataCur, iIdxCur; int r1 = -1; int bStrict; if( pTab->tnum<1 ) continue; /* Skip VIEWs or VIRTUAL TABLEs */ if( pObjTab && pObjTab!=pTab ) continue; pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab); sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenRead, 0, 1, 0, &iDataCur, &iIdxCur); /* reg[7] counts the number of entries in the table. |
︙ | ︙ | |||
1670 1671 1672 1673 1674 1675 1676 1677 | assert( sqlite3NoTempsInRange(pParse,1,7+j) ); sqlite3VdbeAddOp2(v, OP_Rewind, iDataCur, 0); VdbeCoverage(v); loopTop = sqlite3VdbeAddOp2(v, OP_AddImm, 7, 1); if( !isQuick ){ /* Sanity check on record header decoding */ sqlite3VdbeAddOp3(v, OP_Column, iDataCur, pTab->nNVCol-1,3); sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG); } | > | > > > | | > > | | > > > > > > > > > > > > > > > | | > | | > | 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 | assert( sqlite3NoTempsInRange(pParse,1,7+j) ); sqlite3VdbeAddOp2(v, OP_Rewind, iDataCur, 0); VdbeCoverage(v); loopTop = sqlite3VdbeAddOp2(v, OP_AddImm, 7, 1); if( !isQuick ){ /* Sanity check on record header decoding */ sqlite3VdbeAddOp3(v, OP_Column, iDataCur, pTab->nNVCol-1,3); sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG); VdbeComment((v, "(right-most column)")); } /* Verify that all NOT NULL columns really are NOT NULL. At the ** same time verify the type of the content of STRICT tables */ bStrict = (pTab->tabFlags & TF_Strict)!=0; for(j=0; j<pTab->nCol; j++){ char *zErr; Column *pCol = pTab->aCol + j; int doError, jmp2; if( j==pTab->iPKey ) continue; if( pCol->notNull==0 && !bStrict ) continue; doError = bStrict ? sqlite3VdbeMakeLabel(pParse) : 0; sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, j, 3); if( sqlite3VdbeGetOp(v,-1)->opcode==OP_Column ){ sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG); } if( pCol->notNull ){ jmp2 = sqlite3VdbeAddOp1(v, OP_NotNull, 3); VdbeCoverage(v); zErr = sqlite3MPrintf(db, "NULL value in %s.%s", pTab->zName, pCol->zCnName); sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC); if( bStrict ){ sqlite3VdbeGoto(v, doError); }else{ integrityCheckResultRow(v); } sqlite3VdbeJumpHere(v, jmp2); } if( pTab->tabFlags & TF_Strict ){ jmp2 = sqlite3VdbeAddOp3(v, OP_IsNullOrType, 3, 0, sqlite3StdTypeMap[pCol->eCType-1]); VdbeCoverage(v); zErr = sqlite3MPrintf(db, "non-%s value in %s.%s", sqlite3StdType[pCol->eCType-1], pTab->zName, pTab->aCol[j].zCnName); sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC); sqlite3VdbeResolveLabel(v, doError); integrityCheckResultRow(v); sqlite3VdbeJumpHere(v, jmp2); } } /* Verify CHECK constraints */ if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){ ExprList *pCheck = sqlite3ExprListDup(db, pTab->pCheck, 0); if( db->mallocFailed==0 ){ int addrCkFault = sqlite3VdbeMakeLabel(pParse); int addrCkOk = sqlite3VdbeMakeLabel(pParse); |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
557 558 559 560 561 562 563 564 565 566 567 568 569 570 | #define SQLITE_CONSTRAINT_NOTNULL (SQLITE_CONSTRAINT | (5<<8)) #define SQLITE_CONSTRAINT_PRIMARYKEY (SQLITE_CONSTRAINT | (6<<8)) #define SQLITE_CONSTRAINT_TRIGGER (SQLITE_CONSTRAINT | (7<<8)) #define SQLITE_CONSTRAINT_UNIQUE (SQLITE_CONSTRAINT | (8<<8)) #define SQLITE_CONSTRAINT_VTAB (SQLITE_CONSTRAINT | (9<<8)) #define SQLITE_CONSTRAINT_ROWID (SQLITE_CONSTRAINT |(10<<8)) #define SQLITE_CONSTRAINT_PINNED (SQLITE_CONSTRAINT |(11<<8)) #define SQLITE_NOTICE_RECOVER_WAL (SQLITE_NOTICE | (1<<8)) #define SQLITE_NOTICE_RECOVER_ROLLBACK (SQLITE_NOTICE | (2<<8)) #define SQLITE_WARNING_AUTOINDEX (SQLITE_WARNING | (1<<8)) #define SQLITE_AUTH_USER (SQLITE_AUTH | (1<<8)) #define SQLITE_OK_LOAD_PERMANENTLY (SQLITE_OK | (1<<8)) #define SQLITE_OK_SYMLINK (SQLITE_OK | (2<<8)) | > | 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 | #define SQLITE_CONSTRAINT_NOTNULL (SQLITE_CONSTRAINT | (5<<8)) #define SQLITE_CONSTRAINT_PRIMARYKEY (SQLITE_CONSTRAINT | (6<<8)) #define SQLITE_CONSTRAINT_TRIGGER (SQLITE_CONSTRAINT | (7<<8)) #define SQLITE_CONSTRAINT_UNIQUE (SQLITE_CONSTRAINT | (8<<8)) #define SQLITE_CONSTRAINT_VTAB (SQLITE_CONSTRAINT | (9<<8)) #define SQLITE_CONSTRAINT_ROWID (SQLITE_CONSTRAINT |(10<<8)) #define SQLITE_CONSTRAINT_PINNED (SQLITE_CONSTRAINT |(11<<8)) #define SQLITE_CONSTRAINT_DATATYPE (SQLITE_CONSTRAINT |(12<<8)) #define SQLITE_NOTICE_RECOVER_WAL (SQLITE_NOTICE | (1<<8)) #define SQLITE_NOTICE_RECOVER_ROLLBACK (SQLITE_NOTICE | (2<<8)) #define SQLITE_WARNING_AUTOINDEX (SQLITE_WARNING | (1<<8)) #define SQLITE_AUTH_USER (SQLITE_AUTH | (1<<8)) #define SQLITE_OK_LOAD_PERMANENTLY (SQLITE_OK | (1<<8)) #define SQLITE_OK_SYMLINK (SQLITE_OK | (2<<8)) |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2035 2036 2037 2038 2039 2040 2041 | ** is only included if the COLFLAG_HASTYPE bit of colFlags is set and the ** collating sequence name is only included if the COLFLAG_HASCOLL bit is ** set. */ struct Column { char *zCnName; /* Name of this column */ unsigned notNull :4; /* An OE_ code for handling a NOT NULL constraint */ | | | 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 | ** is only included if the COLFLAG_HASTYPE bit of colFlags is set and the ** collating sequence name is only included if the COLFLAG_HASCOLL bit is ** set. */ struct Column { char *zCnName; /* Name of this column */ unsigned notNull :4; /* An OE_ code for handling a NOT NULL constraint */ unsigned eCType :4; /* One of the standard types */ char affinity; /* One of the SQLITE_AFF_... values */ u8 szEst; /* Est size of value in this column. sizeof(INT)==1 */ u8 hName; /* Column name hash for faster lookup */ u16 iDflt; /* 1-based index of DEFAULT. 0 means "none" */ u16 colFlags; /* Boolean properties. See COLFLAG_ defines below */ }; |
︙ | ︙ | |||
2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 | #define TF_NoVisibleRowid 0x00000200 /* No user-visible "rowid" column */ #define TF_OOOHidden 0x00000400 /* Out-of-Order hidden columns */ #define TF_HasNotNull 0x00000800 /* Contains NOT NULL constraints */ #define TF_Shadow 0x00001000 /* True for a shadow table */ #define TF_HasStat4 0x00002000 /* STAT4 info available for this table */ #define TF_Ephemeral 0x00004000 /* An ephemeral table */ #define TF_Eponymous 0x00008000 /* An eponymous virtual table */ /* ** Allowed values for Table.eTabType */ #define TABTYP_NORM 0 /* Ordinary table */ #define TABTYP_VTAB 1 /* Virtual table */ #define TABTYP_VIEW 2 /* A view */ | > | 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 | #define TF_NoVisibleRowid 0x00000200 /* No user-visible "rowid" column */ #define TF_OOOHidden 0x00000400 /* Out-of-Order hidden columns */ #define TF_HasNotNull 0x00000800 /* Contains NOT NULL constraints */ #define TF_Shadow 0x00001000 /* True for a shadow table */ #define TF_HasStat4 0x00002000 /* STAT4 info available for this table */ #define TF_Ephemeral 0x00004000 /* An ephemeral table */ #define TF_Eponymous 0x00008000 /* An eponymous virtual table */ #define TF_Strict 0x00010000 /* STRICT mode */ /* ** Allowed values for Table.eTabType */ #define TABTYP_NORM 0 /* Ordinary table */ #define TABTYP_VTAB 1 /* Virtual table */ #define TABTYP_VIEW 2 /* A view */ |
︙ | ︙ | |||
4431 4432 4433 4434 4435 4436 4437 | void sqlite3AddColumn(Parse*,Token,Token); void sqlite3AddNotNull(Parse*, int); void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int); void sqlite3AddCheckConstraint(Parse*, Expr*, const char*, const char*); void sqlite3AddDefaultValue(Parse*,Expr*,const char*,const char*); void sqlite3AddCollateType(Parse*, Token*); void sqlite3AddGenerated(Parse*,Expr*,Token*); | | | 4432 4433 4434 4435 4436 4437 4438 4439 4440 4441 4442 4443 4444 4445 4446 | void sqlite3AddColumn(Parse*,Token,Token); void sqlite3AddNotNull(Parse*, int); void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int); void sqlite3AddCheckConstraint(Parse*, Expr*, const char*, const char*); void sqlite3AddDefaultValue(Parse*,Expr*,const char*,const char*); void sqlite3AddCollateType(Parse*, Token*); void sqlite3AddGenerated(Parse*,Expr*,Token*); void sqlite3EndTable(Parse*,Token*,Token*,u32,Select*); void sqlite3AddReturning(Parse*,ExprList*); int sqlite3ParseUri(const char*,const char*,unsigned int*, sqlite3_vfs**,char**,char **); #define sqlite3CodecQueryParameters(A,B,C) 0 Btree *sqlite3DbNameToBtree(sqlite3*,const char*); #ifdef SQLITE_UNTESTABLE |
︙ | ︙ | |||
4836 4837 4838 4839 4840 4841 4842 4843 4844 4845 4846 4847 4848 4849 | int sqlite3ValueFromExpr(sqlite3 *, Expr *, u8, u8, sqlite3_value **); void sqlite3ValueApplyAffinity(sqlite3_value *, u8, u8); #ifndef SQLITE_AMALGAMATION extern const unsigned char sqlite3OpcodeProperty[]; extern const char sqlite3StrBINARY[]; extern const unsigned char sqlite3StdTypeLen[]; extern const char sqlite3StdTypeAffinity[]; extern const char *sqlite3StdType[]; extern const unsigned char sqlite3UpperToLower[]; extern const unsigned char *sqlite3aLTb; extern const unsigned char *sqlite3aEQb; extern const unsigned char *sqlite3aGTb; extern const unsigned char sqlite3CtypeMap[]; extern SQLITE_WSD struct Sqlite3Config sqlite3Config; | > | 4837 4838 4839 4840 4841 4842 4843 4844 4845 4846 4847 4848 4849 4850 4851 | int sqlite3ValueFromExpr(sqlite3 *, Expr *, u8, u8, sqlite3_value **); void sqlite3ValueApplyAffinity(sqlite3_value *, u8, u8); #ifndef SQLITE_AMALGAMATION extern const unsigned char sqlite3OpcodeProperty[]; extern const char sqlite3StrBINARY[]; extern const unsigned char sqlite3StdTypeLen[]; extern const char sqlite3StdTypeAffinity[]; extern const char sqlite3StdTypeMap[]; extern const char *sqlite3StdType[]; extern const unsigned char sqlite3UpperToLower[]; extern const unsigned char *sqlite3aLTb; extern const unsigned char *sqlite3aEQb; extern const unsigned char *sqlite3aGTb; extern const unsigned char sqlite3CtypeMap[]; extern SQLITE_WSD struct Sqlite3Config sqlite3Config; |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
667 668 669 670 671 672 673 674 675 676 677 678 679 680 | return out2PrereleaseWithClear(pOut); }else{ pOut->flags = MEM_Int; return pOut; } } /* ** Execute as much of a VDBE program as we can. ** This is the core of sqlite3_step(). */ int sqlite3VdbeExec( Vdbe *p /* The VDBE */ | > > > > > > > > > > > > > | 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 | return out2PrereleaseWithClear(pOut); }else{ pOut->flags = MEM_Int; return pOut; } } /* ** Return the symbolic name for the data type of a pMem */ static const char *vdbeMemTypeName(Mem *pMem){ static const char *azTypes[] = { /* SQLITE_INTEGER */ "INT", /* SQLITE_FLOAT */ "REAL", /* SQLITE_TEXT */ "TEXT", /* SQLITE_BLOB */ "BLOB", /* SQLITE_NULL */ "NULL" }; return azTypes[sqlite3_value_type(pMem)-1]; } /* ** Execute as much of a VDBE program as we can. ** This is the core of sqlite3_step(). */ int sqlite3VdbeExec( Vdbe *p /* The VDBE */ |
︙ | ︙ | |||
2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 | pIn1 = &aMem[pOp->p1]; VdbeBranchTaken( (pIn1->flags & MEM_Null)!=0, 2); if( (pIn1->flags & MEM_Null)!=0 ){ goto jump_to_p2; } break; } /* Opcode: ZeroOrNull P1 P2 P3 * * ** Synopsis: r[P2] = 0 OR NULL ** ** If all both registers P1 and P3 are NOT NULL, then store a zero in ** register P2. If either registers P1 or P3 are NULL then put ** a NULL in register P2. | > > > > > > > > > > > > > > > > | 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 | pIn1 = &aMem[pOp->p1]; VdbeBranchTaken( (pIn1->flags & MEM_Null)!=0, 2); if( (pIn1->flags & MEM_Null)!=0 ){ goto jump_to_p2; } break; } /* Opcode: IsNullOrType P1 P2 P3 * * ** Synopsis: if typeof(r[P1]) IN (P3,5) goto P2 ** ** Jump to P2 if the value in register P1 is NULL or has a datatype P3. ** P3 is an integer which should be one of SQLITE_INTEGER, SQLITE_FLOAT, ** SQLITE_BLOB, SQLITE_NULL, or SQLITE_TEXT. */ case OP_IsNullOrType: { /* jump, in1 */ int doTheJump; pIn1 = &aMem[pOp->p1]; doTheJump = (pIn1->flags & MEM_Null)!=0 || sqlite3_value_type(pIn1)==pOp->p3; VdbeBranchTaken( doTheJump, 2); if( doTheJump ) goto jump_to_p2; break; } /* Opcode: ZeroOrNull P1 P2 P3 * * ** Synopsis: r[P2] = 0 OR NULL ** ** If all both registers P1 and P3 are NOT NULL, then store a zero in ** register P2. If either registers P1 or P3 are NULL then put ** a NULL in register P2. |
︙ | ︙ | |||
2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 2876 2877 2878 | pOp = &aOp[aOp[0].p3-1]; break; }else{ rc = SQLITE_CORRUPT_BKPT; goto abort_due_to_error; } } /* Opcode: Affinity P1 P2 * P4 * ** Synopsis: affinity(r[P1@P2]) ** ** Apply affinities to a range of P2 registers starting with P1. ** ** P4 is a string that is P2 characters long. The N-th character of the | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 2894 2895 2896 2897 2898 2899 2900 2901 2902 2903 2904 2905 2906 2907 2908 2909 2910 2911 2912 2913 2914 2915 2916 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 2997 2998 | pOp = &aOp[aOp[0].p3-1]; break; }else{ rc = SQLITE_CORRUPT_BKPT; goto abort_due_to_error; } } /* Opcode: TypeCheck P1 P2 * P4 * ** Synopsis: typecheck(r[P1@P2]) ** ** Apply affinities to the range of P2 registers beginning with P1. ** Take the affinities from the Table object in P4. If any value ** cannot be coerced into the correct type, then raise an error. ** ** This opcode is similar to OP_Affinity except that this opcode ** forces the register type to the Table column type. This is used ** to implement "strict affinity". ** ** Preconditions: ** ** <ul> ** <li> P2 should be the number of non-virtual columns in the ** table of P4. ** <li> Table P4 should be a STRICT table. ** </ul> ** ** If any precondition is false, an assertion fault occurs. */ case OP_TypeCheck: { Table *pTab; Column *aCol; int i; assert( pOp->p4type==P4_TABLE ); pTab = pOp->p4.pTab; assert( pTab->tabFlags & TF_Strict ); assert( pTab->nNVCol==pOp->p2 ); aCol = pTab->aCol; pIn1 = &aMem[pOp->p1]; for(i=0; i<pTab->nCol; i++){ if( aCol[i].colFlags & COLFLAG_VIRTUAL ) continue; assert( pIn1 < &aMem[pOp->p1+pOp->p2] ); applyAffinity(pIn1, aCol[i].affinity, encoding); if( (pIn1->flags & MEM_Null)==0 ){ switch( aCol[i].eCType ){ case COLTYPE_BLOB: { if( (pIn1->flags & MEM_Blob)==0 ) goto vdbe_type_error; break; } case COLTYPE_INTEGER: case COLTYPE_INT: { if( (pIn1->flags & MEM_Int)==0 ) goto vdbe_type_error; break; } case COLTYPE_TEXT: { if( (pIn1->flags & MEM_Str)==0 ) goto vdbe_type_error; break; } default: { assert( aCol[i].eCType==COLTYPE_REAL ); if( pIn1->flags & MEM_Int ){ /* When applying REAL affinity, if the result is still an MEM_Int ** that will fit in 6 bytes, then change the type to MEM_IntReal ** so that we keep the high-resolution integer value but know that ** the type really wants to be REAL. */ testcase( pIn1->u.i==140737488355328LL ); testcase( pIn1->u.i==140737488355327LL ); testcase( pIn1->u.i==-140737488355328LL ); testcase( pIn1->u.i==-140737488355329LL ); if( pIn1->u.i<=140737488355327LL && pIn1->u.i>=-140737488355328LL){ pIn1->flags |= MEM_IntReal; pIn1->flags &= ~MEM_Int; }else{ pIn1->u.r = (double)pIn1->u.i; pIn1->flags |= MEM_Real; pIn1->flags &= ~MEM_Int; } }else if( (pIn1->flags & MEM_Real)==0 ){ goto vdbe_type_error; } break; } } } REGISTER_TRACE((int)(pIn1-aMem), pIn1); pIn1++; } assert( pIn1 == &aMem[pOp->p1+pOp->p2] ); break; vdbe_type_error: sqlite3VdbeError(p, "cannot store %s value in %s column %s.%s", vdbeMemTypeName(pIn1), sqlite3StdType[aCol[i].eCType-1], pTab->zName, aCol[i].zCnName); rc = SQLITE_CONSTRAINT_DATATYPE; goto abort_due_to_error; } /* Opcode: Affinity P1 P2 * P4 * ** Synopsis: affinity(r[P1@P2]) ** ** Apply affinities to a range of P2 registers starting with P1. ** ** P4 is a string that is P2 characters long. The N-th character of the |
︙ | ︙ |
Added test/strict1.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 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 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 | # 2021-08-18 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file implements regression tests for SQLite library. The # focus of this file is testing STRICT tables. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix strict1 # STRICT tables have on a limited number of allowed datatypes. # do_catchsql_test strict1-1.1 { CREATE TABLE t1(a) STRICT; } {1 {unknown datatype for t1.a: ""}} do_catchsql_test strict1-1.2 { CREATE TABLE t1(a PRIMARY KEY) STRICT, WITHOUT ROWID; } {1 {unknown datatype for t1.a: ""}} do_catchsql_test strict1-1.3 { CREATE TABLE t1(a PRIMARY KEY) WITHOUT ROWID, STRICT; } {1 {unknown datatype for t1.a: ""}} do_catchsql_test strict1-1.4 { CREATE TABLE t1(a BANJO PRIMARY KEY) WITHOUT ROWID, STRICT; } {1 {unknown datatype for t1.a: "BANJO"}} do_catchsql_test strict1-1.5 { CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f DATE) strict; } {1 {unknown datatype for t1.f: "DATE"}} do_catchsql_test strict1-1.6 { CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f TEXT(50)) WITHOUT ROWID, STRICT; } {1 {unknown datatype for t1.f: "TEXT(50)"}} do_execsql_test strict1-2.0 { CREATE TABLE t1( a INT, b INTEGER, c BLOB, d TEXT, e REAL ) STRICT; } {} do_catchsql_test strict1-2.1 { INSERT INTO t1(a) VALUES('xyz'); } {1 {cannot store TEXT value in INT column t1.a}} do_catchsql_test strict1-2.2 { INSERT INTO t1(b) VALUES('xyz'); } {1 {cannot store TEXT value in INTEGER column t1.b}} do_catchsql_test strict1-2.3 { INSERT INTO t1(c) VALUES('xyz'); } {1 {cannot store TEXT value in BLOB column t1.c}} do_catchsql_test strict1-2.4 { INSERT INTO t1(d) VALUES(x'3142536475'); } {1 {cannot store BLOB value in TEXT column t1.d}} do_catchsql_test strict1-2.5 { INSERT INTO t1(e) VALUES('xyz'); } {1 {cannot store TEXT value in REAL column t1.e}} do_execsql_test strict1-3.1 { INSERT INTO t1(a, b) VALUES(1,2),('3','4'),(5.0, 6.0),(null,null); SELECT a, b, '|' FROM t1; } {1 2 | 3 4 | 5 6 | {} {} |} do_catchsql_test strict1-3.2 { INSERT INTO t1(a) VALUES(1.2); } {1 {cannot store REAL value in INT column t1.a}} do_catchsql_test strict1-3.3 { INSERT INTO t1(a) VALUES(x'313233'); } {1 {cannot store BLOB value in INT column t1.a}} do_catchsql_test strict1-3.4 { INSERT INTO t1(b) VALUES(1.2); } {1 {cannot store REAL value in INTEGER column t1.b}} do_catchsql_test strict1-3.5 { INSERT INTO t1(b) VALUES(x'313233'); } {1 {cannot store BLOB value in INTEGER column t1.b}} do_execsql_test strict1-4.1 { DELETE FROM t1; INSERT INTO t1(c) VALUES(x'313233'), (NULL); SELECT typeof(c), c FROM t1; } {blob 123 null {}} do_catchsql_test strict1-4.2 { INSERT INTO t1(c) VALUES('456'); } {1 {cannot store TEXT value in BLOB column t1.c}} do_execsql_test strict1-5.1 { DELETE FROM t1; INSERT INTO t1(d) VALUES('xyz'),(4),(5.5),(NULL); SELECT typeof(d), d FROM t1; } {text xyz text 4 text 5.5 null {}} do_catchsql_test strict1-5.2 { INSERT INTO t1(d) VALUES(x'4567'); } {1 {cannot store BLOB value in TEXT column t1.d}} do_execsql_test strict1-6.1 { DELETE FROM t1; INSERT INTO t1(e) VALUES(1),(2.5),('3'),('4.5'),(6.0),(NULL); SELECT typeof(e), e FROM t1; } {real 1.0 real 2.5 real 3.0 real 4.5 real 6.0 null {}} do_catchsql_test strict1-6.2 { INSERT INTO t1(e) VALUES('xyz'); } {1 {cannot store TEXT value in REAL column t1.e}} do_catchsql_test strict1-6.3 { INSERT INTO t1(e) VALUES(x'3456'); } {1 {cannot store BLOB value in REAL column t1.e}} finish_test |
Added test/strict2.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 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 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 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 140 141 142 143 | # 2021-08-19 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file implements regression tests for SQLite library. The # focus of this file is testing STRICT tables. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix strict2 # PRAGMA integrity_check on a STRICT table should verify that # all of the values are of the correct type. # do_execsql_test strict2-1.1 { CREATE TABLE t1( a INT, b INTEGER, c TEXT, d REAL, e BLOB ) STRICT; CREATE TABLE t1nn( a INT NOT NULL, b INTEGER NOT NULL, c TEXT NOT NULL, d REAL NOT NULL, e BLOB NOT NULL ) STRICT; CREATE TABLE t2(a,b,c,d,e); INSERT INTO t1(a,b,c,d,e) VALUES(1,1,'one',1.0,x'b1'),(2,2,'two',2.25,x'b2b2b2'); PRAGMA writable_schema=on; UPDATE sqlite_schema SET rootpage=(SELECT rootpage FROM sqlite_schema WHERE name='t1'); } {} db close sqlite3 db test.db do_execsql_test strict2-1.2 { PRAGMA quick_check('t1'); } {ok} do_execsql_test strict2-1.3 { UPDATE t2 SET a=2.5 WHERE b=2; PRAGMA quick_check('t1'); } {{non-INT value in t1.a}} do_execsql_test strict2-1.4 { UPDATE t2 SET a='xyz' WHERE b=2; PRAGMA quick_check('t1'); } {{non-INT value in t1.a}} do_execsql_test strict2-1.5 { UPDATE t2 SET a=x'445566' WHERE b=2; PRAGMA quick_check('t1'); } {{non-INT value in t1.a}} do_execsql_test strict2-1.6 { UPDATE t2 SET a=2.5 WHERE b=2; PRAGMA quick_check('t1nn'); } {{non-INT value in t1nn.a}} do_execsql_test strict2-1.7 { UPDATE t2 SET a='xyz' WHERE b=2; PRAGMA quick_check('t1nn'); } {{non-INT value in t1nn.a}} do_execsql_test strict2-1.8 { UPDATE t2 SET a=x'445566' WHERE b=2; PRAGMA quick_check('t1nn'); } {{non-INT value in t1nn.a}} do_execsql_test strict2-1.13 { UPDATE t2 SET a=2 WHERE b=2; UPDATE t2 SET b=2.5 WHERE a=2; PRAGMA quick_check('t1'); } {{non-INTEGER value in t1.b}} do_execsql_test strict2-1.14 { UPDATE t2 SET b='two' WHERE a=2; PRAGMA quick_check('t1'); } {{non-INTEGER value in t1.b}} do_execsql_test strict2-1.15 { UPDATE t2 SET b=x'b0b1b2b3b4' WHERE a=2; PRAGMA quick_check('t1'); } {{non-INTEGER value in t1.b}} do_execsql_test strict2-1.16 { UPDATE t2 SET b=NULL WHERE a=2; PRAGMA quick_check('t1'); } {ok} do_execsql_test strict2-1.17 { UPDATE t2 SET b=2.5 WHERE a=2; PRAGMA quick_check('t1nn'); } {{non-INTEGER value in t1nn.b}} do_execsql_test strict2-1.18 { UPDATE t2 SET b=NULL WHERE a=2; PRAGMA quick_check('t1nn'); } {{NULL value in t1nn.b}} do_execsql_test strict2-1.23 { UPDATE t2 SET b=2 WHERE a=2; UPDATE t2 SET c=9 WHERE a=2; PRAGMA quick_check('t1'); } {{non-TEXT value in t1.c}} do_execsql_test strict2-1.24 { UPDATE t2 SET c=9.5 WHERE a=2; PRAGMA quick_check('t1'); } {{non-TEXT value in t1.c}} do_execsql_test strict2-1.25 { UPDATE t2 SET c=x'b0b1b2b3b4' WHERE a=2; PRAGMA quick_check('t1'); } {{non-TEXT value in t1.c}} do_execsql_test strict2-1.33 { UPDATE t2 SET c='two' WHERE a=2; UPDATE t2 SET d=9 WHERE a=2; PRAGMA quick_check('t1'); } {ok} do_execsql_test strict2-1.34 { UPDATE t2 SET d='nine' WHERE a=2; PRAGMA quick_check('t1'); } {{non-REAL value in t1.d}} do_execsql_test strict2-1.35 { UPDATE t2 SET d=x'b0b1b2b3b4' WHERE a=2; PRAGMA quick_check('t1'); } {{non-REAL value in t1.d}} do_execsql_test strict2-1.43 { UPDATE t2 SET d=2.5 WHERE a=2; UPDATE t2 SET e=9 WHERE a=2; PRAGMA quick_check('t1'); } {{non-BLOB value in t1.e}} do_execsql_test strict2-1.44 { UPDATE t2 SET e=9.5 WHERE a=2; PRAGMA quick_check('t1'); } {{non-BLOB value in t1.e}} do_execsql_test strict2-1.45 { UPDATE t2 SET e='hello' WHERE a=2; PRAGMA quick_check('t1'); } {{non-BLOB value in t1.e}} finish_test |