/ Check-in [7f9f90b1]
Login

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: 7f9f90b1b885fa9905b296f2e0fcc9b2341019b42fc839722a93cf60e49a9252
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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