Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Simple INSERT and SELECT operations working with VIRTUAL columns. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | generated-columns |
| Files: | files | file ages | folders |
| SHA3-256: |
7f9f90b1b885fa9905b296f2e0fcc9b2 |
| User & Date: | drh 2019-10-16 19:31:46 |
Context
|
2019-10-16
| ||
| 20:05 | ALTER TABLE is able to add a VIRTUAL column. (check-in: 120c6b78 user: drh tags: generated-columns) | |
| 19:31 | Simple INSERT and SELECT operations working with VIRTUAL columns. (check-in: 7f9f90b1 user: drh tags: generated-columns) | |
| 12:18 | Initial experimental code for generated column support. Non-functional. (check-in: 11d472c1 user: drh tags: generated-columns) | |
Changes
Changes to src/expr.c.
3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 |
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, iCol);
pParse->iSelfTab = savedSelfTab;
return;
#endif
}else if( !HasRowid(pTab) ){
x = sqlite3ColumnOfIndex(sqlite3PrimaryKeyIndex(pTab), iCol);
op = OP_Column;
}else{
|
| |
3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 |
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{
|
Changes to src/insert.c.
33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 ... 669 670 671 672 673 674 675 676 677 678 679 680 681 682 ... 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 .... 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 .... 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 |
Vdbe *v;
assert( !IsVirtual(pTab) );
v = sqlite3GetVdbe(pParse);
assert( opcode==OP_OpenWrite || opcode==OP_OpenRead );
sqlite3TableLock(pParse, iDb, pTab->tnum,
(opcode==OP_OpenWrite)?1:0, pTab->zName);
if( HasRowid(pTab) ){
sqlite3VdbeAddOp4Int(v, opcode, iCur, pTab->tnum, iDb, pTab->nCol);
VdbeComment((v, "%s", pTab->zName));
}else{
Index *pPk = sqlite3PrimaryKeyIndex(pTab);
assert( pPk!=0 );
assert( pPk->tnum==pTab->tnum );
sqlite3VdbeAddOp3(v, opcode, iCur, pPk->tnum, iDb);
sqlite3VdbeSetP4KeyInfo(pParse, pPk);
................................................................................
for(j=0; j<pTab->nCol; j++){
if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
pColumn->a[i].idx = j;
if( i!=j ) bIdListInOrder = 0;
if( j==pTab->iPKey ){
ipkColumn = i; assert( !withoutRowid );
}
break;
}
}
if( j>=pTab->nCol ){
if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){
ipkColumn = i;
bIdListInOrder = 0;
................................................................................
ipkColumn = pTab->iPKey;
}
/* Make sure the number of columns in the source data matches the number
** of columns to be inserted into the table.
*/
for(i=0; i<pTab->nCol; i++){
nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0);
}
if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){
sqlite3ErrorMsg(pParse,
"table %S has %d columns but %d values were supplied",
pTabList, 0, pTab->nCol-nHidden, nColumn);
goto insert_cleanup;
}
................................................................................
** 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( IsHiddenColumn(&pTab->aCol[i]) ){
j = -1;
nHidden++;
}else{
j = i - nHidden;
}
}else{
for(j=0; j<pColumn->nId; j++){
if( pColumn->a[j].idx==i ) break;
}
................................................................................
VdbeComment((v, "Do IPK REPLACE"));
sqlite3VdbeJumpHere(v, ipkBottom);
}
/* Generate the table record */
if( HasRowid(pTab) ){
int regRec = aRegIdx[ix];
sqlite3VdbeAddOp3(v, OP_MakeRecord, regNewData+1, pTab->nCol, regRec);
sqlite3SetMakeRecordP5(v, pTab);
if( !bAffinityDone ){
sqlite3TableAffinity(v, pTab, 0);
}
}
*pbMayReplace = seenReplace;
|
| > > > > > > > > > | | > > > | > |
33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 ... 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 ... 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 .... 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 .... 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 |
Vdbe *v;
assert( !IsVirtual(pTab) );
v = sqlite3GetVdbe(pParse);
assert( opcode==OP_OpenWrite || opcode==OP_OpenRead );
sqlite3TableLock(pParse, iDb, pTab->tnum,
(opcode==OP_OpenWrite)?1:0, pTab->zName);
if( HasRowid(pTab) ){
sqlite3VdbeAddOp4Int(v, opcode, iCur, pTab->tnum, iDb,
pTab->nCol - pTab->nVCol);
VdbeComment((v, "%s", pTab->zName));
}else{
Index *pPk = sqlite3PrimaryKeyIndex(pTab);
assert( pPk!=0 );
assert( pPk->tnum==pTab->tnum );
sqlite3VdbeAddOp3(v, opcode, iCur, pPk->tnum, iDb);
sqlite3VdbeSetP4KeyInfo(pParse, pPk);
................................................................................
for(j=0; j<pTab->nCol; j++){
if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
pColumn->a[i].idx = j;
if( i!=j ) bIdListInOrder = 0;
if( j==pTab->iPKey ){
ipkColumn = i; assert( !withoutRowid );
}
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
if( pTab->aCol[j].colFlags & (COLFLAG_STORED|COLFLAG_VIRTUAL) ){
sqlite3ErrorMsg(pParse,
"cannot INSERT into generated column \"%s\"",
pTab->aCol[j].zName);
goto insert_cleanup;
}
#endif
break;
}
}
if( j>=pTab->nCol ){
if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){
ipkColumn = i;
bIdListInOrder = 0;
................................................................................
ipkColumn = pTab->iPKey;
}
/* Make sure the number of columns in the source data matches the number
** of columns to be inserted into the table.
*/
for(i=0; i<pTab->nCol; i++){
if( pTab->aCol[i].colFlags & COLFLAG_NOINSERT ) nHidden++;
}
if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){
sqlite3ErrorMsg(pParse,
"table %S has %d columns but %d values were supplied",
pTabList, 0, pTab->nCol-nHidden, nColumn);
goto insert_cleanup;
}
................................................................................
** 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 ){
continue;
}
}else{
j = i - nHidden;
}
}else{
for(j=0; j<pColumn->nId; j++){
if( pColumn->a[j].idx==i ) break;
}
................................................................................
VdbeComment((v, "Do IPK REPLACE"));
sqlite3VdbeJumpHere(v, ipkBottom);
}
/* Generate the table record */
if( HasRowid(pTab) ){
int regRec = aRegIdx[ix];
sqlite3VdbeAddOp3(v, OP_MakeRecord, regNewData+1,
pTab->nCol-pTab->nVCol, regRec);
sqlite3SetMakeRecordP5(v, pTab);
if( !bAffinityDone ){
sqlite3TableAffinity(v, pTab, 0);
}
}
*pbMayReplace = seenReplace;
|
Changes to src/parse.y.
345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 |
ccons ::= CHECK LP expr(X) RP. {sqlite3AddCheckConstraint(pParse,X);}
ccons ::= REFERENCES nm(T) eidlist_opt(TA) refargs(R).
{sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D). {sqlite3DeferForeignKey(pParse,D);}
ccons ::= COLLATE ids(C). {sqlite3AddCollateType(pParse, &C);}
ccons ::= GENERATED ALWAYS AS generated.
ccons ::= AS generated.
generated ::= LP expr(E) RP.
{sqlite3AddGenerated(pParse,E,0);}
generated ::= LP expr(E) RP ID(TYPE).
{sqlite3AddGenerated(pParse,E,&TYPE);}
// The optional AUTOINCREMENT keyword
%type autoinc {int}
autoinc(X) ::= . {X = 0;}
autoinc(X) ::= AUTOINCR. {X = 1;}
// The next group of rules parses the arguments to a REFERENCES clause
|
< | | < |
345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 |
ccons ::= CHECK LP expr(X) RP. {sqlite3AddCheckConstraint(pParse,X);}
ccons ::= REFERENCES nm(T) eidlist_opt(TA) refargs(R).
{sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D). {sqlite3DeferForeignKey(pParse,D);}
ccons ::= COLLATE ids(C). {sqlite3AddCollateType(pParse, &C);}
ccons ::= GENERATED ALWAYS AS generated.
ccons ::= AS generated.
generated ::= LP expr(E) RP. {sqlite3AddGenerated(pParse,E,0);}
generated ::= LP expr(E) RP ID(TYPE). {sqlite3AddGenerated(pParse,E,&TYPE);}
// The optional AUTOINCREMENT keyword
%type autoinc {int}
autoinc(X) ::= . {X = 0;}
autoinc(X) ::= AUTOINCR. {X = 1;}
// The next group of rules parses the arguments to a REFERENCES clause
|
Changes to src/pragma.c.
1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 |
}
assert( pParse->nMem>=8+j );
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->nCol-1, 3);
sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG);
}
/* Verify that all NOT NULL columns really are NOT NULL */
for(j=0; j<pTab->nCol; j++){
char *zErr;
int jmp2;
if( j==pTab->iPKey ) continue;
|
| |
1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 |
}
assert( pParse->nMem>=8+j );
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->nCol-pTab->nVCol-1,3);
sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG);
}
/* Verify that all NOT NULL columns really are NOT NULL */
for(j=0; j<pTab->nCol; j++){
char *zErr;
int jmp2;
if( j==pTab->iPKey ) continue;
|
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 */ /* ** 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. ** ** If CollSeq.xCmp is NULL, it means that the |
> |
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_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.
**
** If CollSeq.xCmp is NULL, it means that the
|