Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Work toward getting generated columns to work with triggers. Still more work to do in this area. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | generated-columns |
| Files: | files | file ages | folders |
| SHA3-256: |
932a37275d7e932f8237d32c8fc6087e |
| User & Date: | drh 2019-10-19 18:47:27 |
Context
|
2019-10-21
| ||
| 01:04 | Changes to the INSERT logic to make it simpler and faster and so that it works with generated columns and BEFORE triggers. check-in: bc368cb0 user: drh tags: generated-columns | |
|
2019-10-19
| ||
| 18:47 | Work toward getting generated columns to work with triggers. Still more work to do in this area. check-in: 932a3727 user: drh tags: generated-columns | |
| 15:01 | Add testcase macros. check-in: fb9c9bb2 user: drh tags: generated-columns | |
Changes
Changes to src/build.c.
897 897 ** as it appears in the record on disk. The true column number 898 898 ** is the index (0,1,2,...) of the column in the CREATE TABLE statement. 899 899 ** 900 900 ** The storage column number is less than the table column number if 901 901 ** and only there are VIRTUAL columns to the left. 902 902 ** 903 903 ** If SQLITE_OMIT_GENERATED_COLUMNS, this routine is a no-op macro. 904 -** 905 -** This function is the inverse of sqlite3TableColumnToStorage(). 906 904 */ 907 905 i16 sqlite3StorageColumnToTable(Table *pTab, i16 iCol){ 908 906 if( pTab->tabFlags & TF_HasVirtual ){ 909 907 int i; 910 908 for(i=0; i<=iCol; i++){ 911 909 if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ) iCol++; 912 910 } ................................................................................ 915 913 } 916 914 #endif 917 915 918 916 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 919 917 /* Convert a table column number into a storage column number. 920 918 ** 921 919 ** The storage column number (0,1,2,....) is the index of the value 922 -** as it appears in the record on disk. The true column number 923 -** is the index (0,1,2,...) of the column in the CREATE TABLE statement. 920 +** as it appears in the record on disk. Or, if the input column is 921 +** the N-th virtual column (zero-based) then the storage number is 922 +** the number of non-virtual columns in the table plus N. 924 923 ** 925 -** If SQLITE_OMIT_GENERATED_COLUMNS, this routine is a no-op macro. 924 +** The true column number is the index (0,1,2,...) of the column in 925 +** the CREATE TABLE statement. 926 926 ** 927 -** This function is the inverse of sqlite3StorageColumnToTable(). 927 +** If the input column is a VIRTUAL column, then it should not appear 928 +** in storage. But the value sometimes is cached in registers that 929 +** follow the range of registers used to construct storage. This 930 +** avoids computing the same VIRTUAL column multiple times, and provides 931 +** values for use by OP_Param opcodes in triggers. Hence, if the 932 +** input column is a VIRTUAL table, put it after all the other columns. 933 +** 934 +** In the following, N means "normal column", S means STORED, and 935 +** V means VIRTUAL. Suppose the CREATE TABLE has columns like this: 936 +** 937 +** CREATE TABLE ex(N,S,V,N,S,V,N,S,V); 938 +** -- 0 1 2 3 4 5 6 7 8 939 +** 940 +** Then the mapping from this function is as follows: 941 +** 942 +** INPUTS: 0 1 2 3 4 5 6 7 8 943 +** OUTPUTS: 0 1 6 2 3 7 4 5 8 944 +** 945 +** So, in other words, this routine shifts all the virtual columns to 946 +** the end. 947 +** 948 +** If SQLITE_OMIT_GENERATED_COLUMNS then there are no virtual columns and 949 +** this routine is a no-op macro. 928 950 */ 929 951 i16 sqlite3TableColumnToStorage(Table *pTab, i16 iCol){ 930 952 int i; 931 953 i16 n; 932 954 assert( iCol<pTab->nCol ); 933 955 if( (pTab->tabFlags & TF_HasVirtual)==0 ) return iCol; 934 956 for(i=0, n=0; i<iCol; i++){ 935 957 if( (pTab->aCol[i].colFlags & COLFLAG_VIRTUAL)==0 ) n++; 936 958 } 937 - return n; 959 + if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ){ 960 + /* iCol is a virtual column itself */ 961 + return pTab->nNVCol + i - n; 962 + }else{ 963 + /* iCol is a normal or stored column */ 964 + return n; 965 + } 938 966 } 939 967 #endif 940 968 941 969 /* 942 970 ** Begin constructing a new table representation in memory. This is 943 971 ** the first of several action routines that get called in response 944 972 ** to a CREATE TABLE statement. In particular, this routine is called
Changes to src/expr.c.
3600 3600 if( pCol->colFlags & COLFLAG_GENERATED ){ 3601 3601 if( pCol->colFlags & COLFLAG_BUSY ){ 3602 3602 sqlite3ErrorMsg(pParse, "generated column loop on \"%s\"", 3603 3603 pCol->zName); 3604 3604 return 0; 3605 3605 } 3606 3606 pCol->colFlags |= COLFLAG_BUSY; 3607 - if( pCol->colFlags & COLFLAG_VIRTUAL ){ 3608 - target = sqlite3ExprCodeTarget(pParse, pCol->pDflt, target); 3609 - }else{ 3610 - target = iSrc; 3611 - if( pCol->colFlags & COLFLAG_NOTAVAIL ){ 3612 - sqlite3ExprCode(pParse, pCol->pDflt, iSrc); 3613 - } 3607 + if( pCol->colFlags & COLFLAG_NOTAVAIL ){ 3608 + sqlite3ExprCode(pParse, pCol->pDflt, iSrc); 3614 3609 } 3615 3610 pCol->colFlags &= ~(COLFLAG_BUSY|COLFLAG_NOTAVAIL); 3616 - return target; 3611 + return iSrc; 3617 3612 }else 3618 3613 #endif /* SQLITE_OMIT_GENERATED_COLUMNS */ 3619 3614 if( pCol->affinity==SQLITE_AFF_REAL ){ 3620 3615 sqlite3VdbeAddOp2(v, OP_SCopy, iSrc, target); 3621 3616 sqlite3VdbeAddOp1(v, OP_RealAffinity, target); 3622 3617 return target; 3623 3618 }else{ ................................................................................ 4084 4079 ** Then p1 is interpreted as follows: 4085 4080 ** 4086 4081 ** p1==0 -> old.rowid p1==3 -> new.rowid 4087 4082 ** p1==1 -> old.a p1==4 -> new.a 4088 4083 ** p1==2 -> old.b p1==5 -> new.b 4089 4084 */ 4090 4085 Table *pTab = pExpr->y.pTab; 4091 - int p1 = pExpr->iTable * (pTab->nCol+1) + 1 + pExpr->iColumn; 4086 + int iCol = pExpr->iColumn; 4087 + int p1 = pExpr->iTable * (pTab->nCol+1) + 1 4088 + + (iCol>=0 ? sqlite3TableColumnToStorage(pTab, iCol) : -1); 4092 4089 4093 4090 assert( pExpr->iTable==0 || pExpr->iTable==1 ); 4094 - assert( pExpr->iColumn>=-1 && pExpr->iColumn<pTab->nCol ); 4095 - assert( pTab->iPKey<0 || pExpr->iColumn!=pTab->iPKey ); 4091 + assert( iCol>=-1 && iCol<pTab->nCol ); 4092 + assert( pTab->iPKey<0 || iCol!=pTab->iPKey ); 4096 4093 assert( p1>=0 && p1<(pTab->nCol*2+2) ); 4097 4094 4098 4095 sqlite3VdbeAddOp2(v, OP_Param, p1, target); 4099 4096 VdbeComment((v, "r[%d]=%s.%s", target, 4100 4097 (pExpr->iTable ? "new" : "old"), 4101 - (pExpr->iColumn<0 ? "rowid" : pExpr->y.pTab->aCol[pExpr->iColumn].zName) 4098 + (pExpr->iColumn<0 ? "rowid" : pExpr->y.pTab->aCol[iCol].zName) 4102 4099 )); 4103 4100 4104 4101 #ifndef SQLITE_OMIT_FLOATING_POINT 4105 4102 /* If the column has REAL affinity, it may currently be stored as an 4106 4103 ** integer. Use OP_RealAffinity to make sure it is really real. 4107 4104 ** 4108 4105 ** EVIDENCE-OF: R-60985-57662 SQLite will convert the value back to 4109 4106 ** floating point when extracting it from the record. */ 4110 - if( pExpr->iColumn>=0 4111 - && pTab->aCol[pExpr->iColumn].affinity==SQLITE_AFF_REAL 4112 - ){ 4107 + if( iCol>=0 && pTab->aCol[iCol].affinity==SQLITE_AFF_REAL ){ 4113 4108 sqlite3VdbeAddOp1(v, OP_RealAffinity, target); 4114 4109 } 4115 4110 #endif 4116 4111 break; 4117 4112 } 4118 4113 4119 4114 case TK_VECTOR: {
Changes to src/insert.c.
201 201 return 0; 202 202 } 203 203 204 204 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 205 205 /* 206 206 ** All regular columns for table pTab have been puts into registers 207 207 ** starting with iRegStore. The registers that correspond to STORED 208 -** columns have not been initialized. This routine goes back and computes 209 -** the values for STORED columns based on the previously computed normal 210 -** columns. 208 +** or VIRTUAL columns have not yet been initialized. This routine goes 209 +** back and computes the values for those columns based on the previously 210 +** computed normal columns. 211 211 */ 212 -void sqlite3ComputeStoredColumns( 212 +void sqlite3ComputeGeneratedColumns( 213 213 Parse *pParse, /* Parsing context */ 214 214 int iRegStore, /* Register holding the first column */ 215 215 Table *pTab /* The table */ 216 216 ){ 217 217 int i; 218 - /* Because there can be multiple STORED columns that refer to one another, 219 - ** either directly or through VIRTUAL columns, this is a two pass 220 - ** algorithm. On the first pass, mark all STORED columns as NOT-AVAILABLE. 218 + int nv; 219 + /* Because there can be multiple generated columns that refer to one another, 220 + ** this is a two-pass algorithm. On the first pass, mark all generated 221 + ** columns as "not available". 221 222 */ 222 223 for(i=0; i<pTab->nCol; i++){ 223 - if( pTab->aCol[i].colFlags & COLFLAG_STORED ){ 224 + if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){ 224 225 pTab->aCol[i].colFlags |= COLFLAG_NOTAVAIL; 225 226 } 226 227 } 227 228 /* On the second pass, compute the value of each NOT-AVAILABLE column. 228 229 ** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will 229 230 ** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as 230 231 ** they are needed. 231 232 */ 232 233 pParse->iSelfTab = -iRegStore; 233 - for(i=0; i<pTab->nCol; i++, iRegStore++){ 234 + for(i=nv=0; i<pTab->nCol; i++){ 234 235 u32 colFlags = pTab->aCol[i].colFlags; 235 - if( (colFlags & COLFLAG_VIRTUAL)!=0 ){ 236 - /* Virtual columns are not stored */ 237 - iRegStore--; 238 - }else if( (colFlags & COLFLAG_NOTAVAIL)!=0 ){ 239 - /* Stored columns are handled on the second pass */ 240 - sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, iRegStore); 236 + if( (colFlags & COLFLAG_NOTAVAIL)!=0 ){ 237 + assert( colFlags & COLFLAG_GENERATED ); 238 + if( colFlags & COLFLAG_VIRTUAL ){ 239 + /* Virtual columns go at the end */ 240 + assert( pTab->nNVCol+nv == sqlite3TableColumnToStorage(pTab,i) ); 241 + sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, 242 + iRegStore+pTab->nNVCol+nv); 243 + }else{ 244 + /* Stored columns go in column order */ 245 + assert( i-nv == sqlite3TableColumnToStorage(pTab,i) ); 246 + sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, iRegStore+i-nv); 247 + } 241 248 colFlags &= ~COLFLAG_NOTAVAIL; 242 249 } 250 + if( (colFlags & COLFLAG_VIRTUAL)!=0 ) nv++; 243 251 } 244 252 pParse->iSelfTab = 0; 245 253 } 246 254 #endif /* SQLITE_OMIT_GENERATED_COLUMNS */ 247 255 248 256 249 257 #ifndef SQLITE_OMIT_AUTOINCREMENT ................................................................................ 1051 1059 */ 1052 1060 nHidden = 0; 1053 1061 iRegStore = regRowid+1; 1054 1062 for(i=0; i<pTab->nCol; i++, iRegStore++){ 1055 1063 int k; 1056 1064 u32 colFlags; 1057 1065 assert( i>=nHidden ); 1058 - assert( iRegStore==sqlite3TableColumnToStorage(pTab,i)+regRowid+1 ); 1059 1066 if( i==pTab->iPKey ){ 1060 1067 /* The value of the INTEGER PRIMARY KEY column is always a NULL. 1061 1068 ** Whenever this column is read, the rowid will be substituted 1062 1069 ** in its place. Hence, fill this column with a NULL to avoid 1063 1070 ** taking up data space with information that will never be used. 1064 1071 ** As there may be shallow copies of this value, make it a soft-NULL */ 1065 1072 sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore); ................................................................................ 1104 1111 } 1105 1112 }else{ 1106 1113 sqlite3ExprCode(pParse, pList->a[k].pExpr, iRegStore); 1107 1114 } 1108 1115 } 1109 1116 1110 1117 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 1111 - /* Compute the new value for STORED columns after all other 1118 + /* Compute the new value for generated columns after all other 1112 1119 ** columns have already been computed */ 1113 - if( pTab->tabFlags & TF_HasStored ){ 1114 - sqlite3ComputeStoredColumns(pParse, regRowid+1, pTab); 1120 + if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){ 1121 + sqlite3ComputeGeneratedColumns(pParse, regRowid+1, pTab); 1115 1122 } 1116 1123 #endif 1117 1124 1118 1125 /* Generate code to check constraints and generate index keys and 1119 1126 ** do the insertion. 1120 1127 */ 1121 1128 #ifndef SQLITE_OMIT_VIRTUALTABLE ................................................................................ 1450 1457 } 1451 1458 if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){ 1452 1459 onError = OE_Abort; 1453 1460 } 1454 1461 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail 1455 1462 || onError==OE_Ignore || onError==OE_Replace ); 1456 1463 addr1 = 0; 1457 - if( (pTab->tabFlags & TF_HasVirtual)==0 ){ 1458 - iReg = regNewData+1+i; 1459 - }else if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ){ 1460 - iReg = ++pParse->nMem; 1461 - assert( pParse->iSelfTab==0 ); 1462 - pParse->iSelfTab = -regNewData; 1463 - sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, iReg); 1464 - pParse->iSelfTab = 0; 1465 - if( onError==OE_Replace ) onError = OE_Abort; 1466 - }else{ 1467 - testcase( i!=sqlite3TableColumnToStorage(pTab, i) ); 1468 - iReg = sqlite3TableColumnToStorage(pTab, i) + regNewData + 1; 1469 - } 1464 + testcase( i!=sqlite3TableColumnToStorage(pTab, i) ); 1465 + testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ); 1466 + testcase( pTab->aCol[i].colFlags & COLFLAG_STORED ); 1467 + testcase( pTab->aCol[i].colFlags & COLFLAG_GENERATED ); 1468 + iReg = sqlite3TableColumnToStorage(pTab, i) + regNewData + 1; 1470 1469 switch( onError ){ 1471 1470 case OE_Replace: { 1472 1471 assert( onError==OE_Replace ); 1473 1472 addr1 = sqlite3VdbeMakeLabel(pParse); 1474 1473 sqlite3VdbeAddOp2(v, OP_NotNull, iReg, addr1); 1475 1474 VdbeCoverage(v); 1476 1475 sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regNewData+1+i);
Changes to src/sqliteInt.h.
4040 4040 void sqlite3AutoincrementEnd(Parse *pParse); 4041 4041 #else 4042 4042 # define sqlite3AutoincrementBegin(X) 4043 4043 # define sqlite3AutoincrementEnd(X) 4044 4044 #endif 4045 4045 void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int, Upsert*); 4046 4046 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 4047 - void sqlite3ComputeStoredColumns(Parse*, int, Table*); 4047 + void sqlite3ComputeGeneratedColumns(Parse*, int, Table*); 4048 4048 #endif 4049 4049 void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*); 4050 4050 IdList *sqlite3IdListAppend(Parse*, IdList*, Token*); 4051 4051 int sqlite3IdListIndex(IdList*,const char*); 4052 4052 SrcList *sqlite3SrcListEnlarge(Parse*, SrcList*, int, int); 4053 4053 SrcList *sqlite3SrcListAppend(Parse*, SrcList*, Token*, Token*); 4054 4054 SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,
Changes to src/update.c.
689 689 sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k); 690 690 }else{ 691 691 sqlite3VdbeAddOp2(v, OP_Null, 0, k); 692 692 } 693 693 } 694 694 } 695 695 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 696 - if( pTab->tabFlags & TF_HasStored ){ 697 - sqlite3ComputeStoredColumns(pParse, regNew, pTab); 696 + if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){ 697 + sqlite3ComputeGeneratedColumns(pParse, regNew, pTab); 698 698 } 699 699 #endif 700 700 701 701 /* Fire any BEFORE UPDATE triggers. This happens before constraints are 702 702 ** verified. One could argue that this is wrong. 703 703 */ 704 704 if( tmask&TRIGGER_BEFORE ){ ................................................................................ 733 733 if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){ 734 734 if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ) k--; 735 735 }else if( aXRef[i]<0 && i!=pTab->iPKey ){ 736 736 sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k); 737 737 } 738 738 } 739 739 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 740 - if( pTab->tabFlags & TF_HasStored ){ 741 - sqlite3ComputeStoredColumns(pParse, regNew, pTab); 740 + if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){ 741 + sqlite3ComputeGeneratedColumns(pParse, regNew, pTab); 742 742 } 743 743 #endif 744 744 } 745 745 746 746 if( !isView ){ 747 747 /* Do constraint checks. */ 748 748 assert( regOldRowid>0 );