/ Check-in [120c6b78]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:ALTER TABLE is able to add a VIRTUAL column.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | generated-columns
Files: files | file ages | folders
SHA3-256: 120c6b78cb51532f783014605f1107d40b2e4f54e3852fb1f8f167d0c0b78c69
User & Date: drh 2019-10-16 20:05:56
Context
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
19:31
Simple INSERT and SELECT operations working with VIRTUAL columns. check-in: 7f9f90b1 user: drh tags: generated-columns
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/alter.c.

294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321









322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350


351
352
353
354
355
356
357
#ifndef SQLITE_OMIT_AUTHORIZATION
  /* Invoke the authorization callback. */
  if( sqlite3AuthCheck(pParse, SQLITE_ALTER_TABLE, zDb, pTab->zName, 0) ){
    return;
  }
#endif

  /* If the default value for the new column was specified with a 
  ** literal NULL, then set pDflt to 0. This simplifies checking
  ** for an SQL NULL default below.
  */
  assert( pDflt==0 || pDflt->op==TK_SPAN );
  if( pDflt && pDflt->pLeft->op==TK_NULL ){
    pDflt = 0;
  }

  /* Check that the new column is not specified as PRIMARY KEY or UNIQUE.
  ** If there is a NOT NULL constraint, then the default value for the
  ** column must not be NULL.
  */
  if( pCol->colFlags & COLFLAG_PRIMKEY ){
    sqlite3ErrorMsg(pParse, "Cannot add a PRIMARY KEY column");
    return;
  }
  if( pNew->pIndex ){
    sqlite3ErrorMsg(pParse, "Cannot add a UNIQUE column");
    return;
  }









  if( (db->flags&SQLITE_ForeignKeys) && pNew->pFKey && pDflt ){
    sqlite3ErrorMsg(pParse, 
        "Cannot add a REFERENCES column with non-NULL default value");
    return;
  }
  if( pCol->notNull && !pDflt ){
    sqlite3ErrorMsg(pParse, 
        "Cannot add a NOT NULL column with default value NULL");
    return;
  }

  /* Ensure the default expression is something that sqlite3ValueFromExpr()
  ** can handle (i.e. not CURRENT_TIME etc.)
  */
  if( pDflt ){
    sqlite3_value *pVal = 0;
    int rc;
    rc = sqlite3ValueFromExpr(db, pDflt, SQLITE_UTF8, SQLITE_AFF_BLOB, &pVal);
    assert( rc==SQLITE_OK || rc==SQLITE_NOMEM );
    if( rc!=SQLITE_OK ){
      assert( db->mallocFailed == 1 );
      return;
    }
    if( !pVal ){
      sqlite3ErrorMsg(pParse, "Cannot add a column with non-constant default");
      return;
    }
    sqlite3ValueFree(pVal);
  }



  /* Modify the CREATE TABLE statement. */
  zCol = sqlite3DbStrNDup(db, (char*)pColDef->z, pColDef->n);
  if( zCol ){
    char *zEnd = &zCol[pColDef->n-1];
    u32 savedDbFlags = db->mDbFlags;
    while( zEnd>zCol && (*zEnd==';' || sqlite3Isspace(*zEnd)) ){







<
<
<
<
<
<
<
<













>
>
>
>
>
>
>
>
>
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>







294
295
296
297
298
299
300








301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
#ifndef SQLITE_OMIT_AUTHORIZATION
  /* Invoke the authorization callback. */
  if( sqlite3AuthCheck(pParse, SQLITE_ALTER_TABLE, zDb, pTab->zName, 0) ){
    return;
  }
#endif










  /* Check that the new column is not specified as PRIMARY KEY or UNIQUE.
  ** If there is a NOT NULL constraint, then the default value for the
  ** column must not be NULL.
  */
  if( pCol->colFlags & COLFLAG_PRIMKEY ){
    sqlite3ErrorMsg(pParse, "Cannot add a PRIMARY KEY column");
    return;
  }
  if( pNew->pIndex ){
    sqlite3ErrorMsg(pParse, "Cannot add a UNIQUE column");
    return;
  }
  if( (pCol->colFlags & COLFLAG_GENERATED)==0 ){
    /* If the default value for the new column was specified with a 
    ** literal NULL, then set pDflt to 0. This simplifies checking
    ** for an SQL NULL default below.
    */
    assert( pDflt==0 || pDflt->op==TK_SPAN );
    if( pDflt && pDflt->pLeft->op==TK_NULL ){
      pDflt = 0;
    }
    if( (db->flags&SQLITE_ForeignKeys) && pNew->pFKey && pDflt ){
      sqlite3ErrorMsg(pParse, 
          "Cannot add a REFERENCES column with non-NULL default value");
      return;
    }
    if( pCol->notNull && !pDflt ){
      sqlite3ErrorMsg(pParse, 
          "Cannot add a NOT NULL column with default value NULL");
      return;
    }

    /* Ensure the default expression is something that sqlite3ValueFromExpr()
    ** can handle (i.e. not CURRENT_TIME etc.)
    */
    if( pDflt ){
      sqlite3_value *pVal = 0;
      int rc;
      rc = sqlite3ValueFromExpr(db, pDflt, SQLITE_UTF8, SQLITE_AFF_BLOB, &pVal);
      assert( rc==SQLITE_OK || rc==SQLITE_NOMEM );
      if( rc!=SQLITE_OK ){
        assert( db->mallocFailed == 1 );
        return;
      }
      if( !pVal ){
        sqlite3ErrorMsg(pParse,"Cannot add a column with non-constant default");
        return;
      }
      sqlite3ValueFree(pVal);
    }
  }


  /* Modify the CREATE TABLE statement. */
  zCol = sqlite3DbStrNDup(db, (char*)pColDef->z, pColDef->n);
  if( zCol ){
    char *zEnd = &zCol[pColDef->n-1];
    u32 savedDbFlags = db->mDbFlags;
    while( zEnd>zCol && (*zEnd==';' || sqlite3Isspace(*zEnd)) ){

Changes to src/build.c.

1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
  }
  pTab->nVCol++;
  pCol->colFlags |= eType;
  pCol->pDflt = sqlite3ExprDup(pParse->db, pExpr, 0);
  goto generated_done;

generated_error:
  sqlite3ErrorMsg(pParse, "bad GENERATED ALWAYS AS clause on column \"%s\"",
                  pCol->zName);
generated_done:
  sqlite3ExprDelete(pParse->db, pExpr);
#else
  /* Throw and error for the GENERATED ALWAYS AS clause if the
  ** SQLITE_OMIT_GENERATED_COLUMNS compile-time option is used. */
  sqlite3ErrorMsg(pParse, "GENERATED ALWAYS AS not supported");







|







1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
  }
  pTab->nVCol++;
  pCol->colFlags |= eType;
  pCol->pDflt = sqlite3ExprDup(pParse->db, pExpr, 0);
  goto generated_done;

generated_error:
  sqlite3ErrorMsg(pParse, "incorrect GENERATED ALWAYS AS on column \"%s\"",
                  pCol->zName);
generated_done:
  sqlite3ExprDelete(pParse->db, pExpr);
#else
  /* Throw and error for the GENERATED ALWAYS AS clause if the
  ** SQLITE_OMIT_GENERATED_COLUMNS compile-time option is used. */
  sqlite3ErrorMsg(pParse, "GENERATED ALWAYS AS not supported");

Changes to src/insert.c.

528
529
530
531
532
533
534

535
536
537
538
539
540
541
...
999
1000
1001
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
  SelectDest dest;      /* Destination for SELECT on rhs of INSERT */
  int iDb;              /* Index of database holding TABLE */
  u8 useTempTable = 0;  /* Store SELECT results in intermediate table */
  u8 appendFlag = 0;    /* True if the insert is likely to be an append */
  u8 withoutRowid;      /* 0 for normal table.  1 for WITHOUT ROWID table */
  u8 bIdListInOrder;    /* True if IDLIST is in table order */
  ExprList *pList = 0;  /* List of VALUES() to be inserted  */


  /* Register allocations */
  int regFromSelect = 0;/* Base register for data coming from SELECT */
  int regAutoinc = 0;   /* Register holding the AUTOINCREMENT counter */
  int regRowCount = 0;  /* Memory cell used for the row counter */
  int regIns;           /* Block of regs holding rowid+data being inserted */
  int regRowid;         /* registers holding insert rowid */
................................................................................
    }
    autoIncStep(pParse, regAutoinc, regRowid);

    /* Compute data for all columns of the new entry, beginning
    ** with the first column.
    */
    nHidden = 0;
    for(i=0; i<pTab->nCol; i++){
      int iRegStore = regRowid+1+i;

      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 ){

            continue;
          }
        }else{
          j = i - nHidden;
        }
      }else{
        for(j=0; j<pColumn->nId; j++){







>







 







<
|
>







 







>







528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
....
1000
1001
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
  SelectDest dest;      /* Destination for SELECT on rhs of INSERT */
  int iDb;              /* Index of database holding TABLE */
  u8 useTempTable = 0;  /* Store SELECT results in intermediate table */
  u8 appendFlag = 0;    /* True if the insert is likely to be an append */
  u8 withoutRowid;      /* 0 for normal table.  1 for WITHOUT ROWID table */
  u8 bIdListInOrder;    /* True if IDLIST is in table order */
  ExprList *pList = 0;  /* List of VALUES() to be inserted  */
  int iRegStore;        /* Register in which to store next column */

  /* Register allocations */
  int regFromSelect = 0;/* Base register for data coming from SELECT */
  int regAutoinc = 0;   /* Register holding the AUTOINCREMENT counter */
  int regRowCount = 0;  /* Memory cell used for the row counter */
  int regIns;           /* Block of regs holding rowid+data being inserted */
  int regRowid;         /* registers holding insert rowid */
................................................................................
    }
    autoIncStep(pParse, regAutoinc, regRowid);

    /* 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++){

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_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_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.
**