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