Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | INSERT with named columns for a table with generated columns. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | generated-columns |
| Files: | files | file ages | folders |
| SHA3-256: |
64db39f92d68d1b9f23e48af35e16b96 |
| User & Date: | drh 2019-10-16 22:01:56 |
Context
|
2019-10-17
| ||
| 13:15 | Fix the table_info and table_xinfo pragmas so that they work with virtual columns. Table_info omits virtual columns. Table_xinfo gives them a "hidden" flag of 2, and 3 for STORED columns. (check-in: 069351b8 user: drh tags: generated-columns) | |
|
2019-10-16
| ||
| 22:01 | INSERT with named columns for a table with generated columns. (check-in: 64db39f9 user: drh tags: generated-columns) | |
| 20:05 | ALTER TABLE is able to add a VIRTUAL column. (check-in: 120c6b78 user: drh tags: generated-columns) | |
Changes
Changes to src/expr.c.
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
....
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
|
Parse *pParse, /* Parsing context */
Table *pTab, /* The table containing the value */
int iTabCur, /* The table cursor. Or the PK cursor for WITHOUT ROWID */
int iCol, /* Index of the column to extract */
int regOut /* Extract the value into this register */
){
Vdbe *v = pParse->pVdbe;
assert( v!=0 );
if( pTab==0 ){
sqlite3VdbeAddOp3(v, OP_Column, iTabCur, iCol, regOut);
return;
}
if( iCol<0 || iCol==pTab->iPKey ){
sqlite3VdbeAddOp2(v, OP_Rowid, iTabCur, regOut);
................................................................................
}else{
int op;
int x;
if( IsVirtual(pTab) ){
op = OP_VColumn;
x = iCol;
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
}else if( pTab->aCol[iCol].colFlags & COLFLAG_VIRTUAL ){
int savedSelfTab = pParse->iSelfTab;
pParse->iSelfTab = iTabCur+1;
sqlite3ExprCode(pParse, pTab->aCol[iCol].pDflt, regOut);
pParse->iSelfTab = savedSelfTab;
return;
#endif
}else if( !HasRowid(pTab) ){
x = sqlite3ColumnOfIndex(sqlite3PrimaryKeyIndex(pTab), iCol);
op = OP_Column;
}else{
x = sqlite3ColumnOfTable(pTab,iCol);
|
>
|
>
>
>
|
>
|
|
|
>
>
|
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
....
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
|
Parse *pParse, /* Parsing context */
Table *pTab, /* The table containing the value */
int iTabCur, /* The table cursor. Or the PK cursor for WITHOUT ROWID */
int iCol, /* Index of the column to extract */
int regOut /* Extract the value into this register */
){
Vdbe *v = pParse->pVdbe;
Column *pCol;
assert( v!=0 );
if( pTab==0 ){
sqlite3VdbeAddOp3(v, OP_Column, iTabCur, iCol, regOut);
return;
}
if( iCol<0 || iCol==pTab->iPKey ){
sqlite3VdbeAddOp2(v, OP_Rowid, iTabCur, regOut);
................................................................................
}else{
int op;
int x;
if( IsVirtual(pTab) ){
op = OP_VColumn;
x = iCol;
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
}else if( (pCol = &pTab->aCol[iCol])->colFlags & COLFLAG_VIRTUAL ){
if( pCol->colFlags & COLFLAG_BUSY ){
sqlite3ErrorMsg(pParse, "generated column loop on \"%s\"", pCol->zName);
}else{
int savedSelfTab = pParse->iSelfTab;
pCol->colFlags |= COLFLAG_BUSY;
pParse->iSelfTab = iTabCur+1;
sqlite3ExprCode(pParse, pTab->aCol[iCol].pDflt, regOut);
pParse->iSelfTab = savedSelfTab;
pCol->colFlags &= ~COLFLAG_BUSY;
}
return;
#endif
}else if( !HasRowid(pTab) ){
x = sqlite3ColumnOfIndex(sqlite3PrimaryKeyIndex(pTab), iCol);
op = OP_Column;
}else{
x = sqlite3ColumnOfTable(pTab,iCol);
|
Changes to src/insert.c.
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
....
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
|
** 'A' BLOB
** 'B' TEXT
** 'C' NUMERIC
** 'D' INTEGER
** 'E' REAL
*/
void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){
int i;
char *zColAff = pTab->zColAff;
if( zColAff==0 ){
sqlite3 *db = sqlite3VdbeDb(v);
zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1);
if( !zColAff ){
sqlite3OomFault(db);
return;
}
for(i=0; i<pTab->nCol; i++){
assert( pTab->aCol[i].affinity!=0 );
zColAff[i] = pTab->aCol[i].affinity;
}
do{
zColAff[i--] = 0;
}while( i>=0 && zColAff[i]<=SQLITE_AFF_BLOB );
pTab->zColAff = zColAff;
}
assert( zColAff!=0 );
i = sqlite3Strlen30NN(zColAff);
if( i ){
if( iReg ){
sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);
................................................................................
/* Compute data for all columns of the new entry, beginning
** with the first column.
*/
nHidden = 0;
iRegStore = regRowid+1;
for(i=0; i<pTab->nCol; i++, iRegStore++){
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);
continue;
}
if( pColumn==0 ){
if( pTab->aCol[i].colFlags & COLFLAG_NOINSERT ){
j = -1;
nHidden++;
if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ){
iRegStore--;
continue;
}
}else{
j = i - nHidden;
}
}else{
for(j=0; j<pColumn->nId; j++){
if( pColumn->a[j].idx==i ) break;
}
}
if( j<0 || nColumn==0 || (pColumn && j>=pColumn->nId) ){
sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
}else if( useTempTable ){
sqlite3VdbeAddOp3(v, OP_Column, srcTab, j, iRegStore);
}else if( pSelect ){
if( regFromSelect!=regData ){
sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+j, iRegStore);
}
}else{
sqlite3ExprCode(pParse, pList->a[j].pExpr, iRegStore);
}
}
/* Generate code to check constraints and generate index keys and
** do the insertion.
*/
#ifndef SQLITE_OMIT_VIRTUALTABLE
|
|
|
>
|
|
>
|
|
>
>
>
<
|
<
|
|
>
|
>
>
>
>
|
|
<
<
>
>
>
>
>
>
>
>
>
>
>
<
<
>
|
|
<
<
|
|
|
|
|
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
....
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
|
** 'A' BLOB
** 'B' TEXT
** 'C' NUMERIC
** 'D' INTEGER
** 'E' REAL
*/
void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){
int i, j;
char *zColAff = pTab->zColAff;
if( zColAff==0 ){
sqlite3 *db = sqlite3VdbeDb(v);
zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1);
if( !zColAff ){
sqlite3OomFault(db);
return;
}
for(i=j=0; i<pTab->nCol; i++){
assert( pTab->aCol[i].affinity!=0 );
if( (pTab->aCol[i].colFlags & COLFLAG_VIRTUAL)==0 ){
zColAff[j++] = pTab->aCol[i].affinity;
}
}
do{
zColAff[j--] = 0;
}while( j>=0 && zColAff[j]<=SQLITE_AFF_BLOB );
pTab->zColAff = zColAff;
}
assert( zColAff!=0 );
i = sqlite3Strlen30NN(zColAff);
if( i ){
if( iReg ){
sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);
................................................................................
/* Compute data for all columns of the new entry, beginning
** with the first column.
*/
nHidden = 0;
iRegStore = regRowid+1;
for(i=0; i<pTab->nCol; i++, iRegStore++){
int k;
assert( i>=nHidden );
assert( iRegStore==sqlite3ColumnOfTable(pTab,i)+regRowid+1 );
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);
continue;
}
if( pTab->aCol[i].colFlags & COLFLAG_NOINSERT ){
nHidden++;
if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ){
/* Virtual columns are no stored */
iRegStore--;
}else{
/* Hidden and stored columns get the default value */
sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
}
continue;
}
if( pColumn ){
for(j=0; j<pColumn->nId && pColumn->a[j].idx!=i; j++){}
if( j>=pColumn->nId ){
/* A column not named in the insert column list gets its
** default value */
sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
continue;
}
k = j;
}else if( nColumn==0 ){
sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
continue;
}else{
k = i - nHidden;
}
if( useTempTable ){
sqlite3VdbeAddOp3(v, OP_Column, srcTab, k, iRegStore);
}else if( pSelect ){
if( regFromSelect!=regData ){
sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+k, iRegStore);
}
}else{
sqlite3ExprCode(pParse, pList->a[k].pExpr, iRegStore);
}
}
/* Generate code to check constraints and generate index keys and
** do the insertion.
*/
#ifndef SQLITE_OMIT_VIRTUALTABLE
|
Changes to src/sqliteInt.h.
1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 |
#define COLFLAG_PRIMKEY 0x0001 /* Column is part of the primary key */ #define COLFLAG_HIDDEN 0x0002 /* A hidden column in a virtual table */ #define COLFLAG_HASTYPE 0x0004 /* Type name follows column name */ #define COLFLAG_UNIQUE 0x0008 /* Column def contains "UNIQUE" or "PK" */ #define COLFLAG_SORTERREF 0x0010 /* Use sorter-refs with this column */ #define COLFLAG_VIRTUAL 0x0020 /* GENERATED ALWAYS AS ... VIRTUAL */ #define COLFLAG_STORED 0x0040 /* GENERATED ALWAYS AS ... STORED */ #define COLFLAG_GENERATED 0x0060 /* Combo: _STORED, _VIRTUAL */ #define COLFLAG_NOINSERT 0x0062 /* Combo: _HIDDEN, _STORED, _VIRTUAL */ /* ** A "Collating Sequence" is defined by an instance of the following ** structure. Conceptually, a collating sequence consists of a name and ** a comparison routine that defines the order of that sequence. |
> |
1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 |
#define COLFLAG_PRIMKEY 0x0001 /* Column is part of the primary key */
#define COLFLAG_HIDDEN 0x0002 /* A hidden column in a virtual table */
#define COLFLAG_HASTYPE 0x0004 /* Type name follows column name */
#define COLFLAG_UNIQUE 0x0008 /* Column def contains "UNIQUE" or "PK" */
#define COLFLAG_SORTERREF 0x0010 /* Use sorter-refs with this column */
#define COLFLAG_VIRTUAL 0x0020 /* GENERATED ALWAYS AS ... VIRTUAL */
#define COLFLAG_STORED 0x0040 /* GENERATED ALWAYS AS ... STORED */
#define COLFLAG_BUSY 0x0080 /* Blocks recursion on VIRTUAL columns */
#define COLFLAG_GENERATED 0x0060 /* Combo: _STORED, _VIRTUAL */
#define COLFLAG_NOINSERT 0x0062 /* Combo: _HIDDEN, _STORED, _VIRTUAL */
/*
** A "Collating Sequence" is defined by an instance of the following
** structure. Conceptually, a collating sequence consists of a name and
** a comparison routine that defines the order of that sequence.
|