/ Check-in [31c1668d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Tables created with the CREATE TABLE <tbl> AS SELECT ... syntax now inherit column declaration types from the SELECT statement. (CVS 1538)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 31c1668dbc2b84438a5b78b0270f58b37b03791d
User & Date: danielk1977 2004-06-07 10:00:31
Context
2004-06-07
16:27
More work on windows locking. Fix some problems with unix locking. There is still an assertion failure on windows locking in attach2.test. (CVS 1539) check-in: 0c2d169c user: drh tags: trunk
10:00
Tables created with the CREATE TABLE <tbl> AS SELECT ... syntax now inherit column declaration types from the SELECT statement. (CVS 1538) check-in: 31c1668d user: danielk1977 tags: trunk
07:52
Add pragma to set/get text encoding. Also fix an obscure problem where a temp trigger could be accidently dropped. (CVS 1537) check-in: 983221b0 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

    19     19   **     DROP INDEX
    20     20   **     creating ID lists
    21     21   **     BEGIN TRANSACTION
    22     22   **     COMMIT
    23     23   **     ROLLBACK
    24     24   **     PRAGMA
    25     25   **
    26         -** $Id: build.c,v 1.207 2004/06/07 07:52:18 danielk1977 Exp $
           26  +** $Id: build.c,v 1.208 2004/06/07 10:00:31 danielk1977 Exp $
    27     27   */
    28     28   #include "sqliteInt.h"
    29     29   #include <ctype.h>
    30     30   
    31     31   /*
    32     32   ** This routine is called when a new SQL statement is beginning to
    33     33   ** be parsed.  Check to see if the schema for the database needs
................................................................................
   710    710     if( z==0 ) return;
   711    711     for(i=j=0; z[i]; i++){
   712    712       int c = z[i];
   713    713       if( isspace(c) ) continue;
   714    714       z[j++] = c;
   715    715     }
   716    716     z[j] = 0;
   717         -//  pCol->sortOrder = sqlite3CollateType(z, n);
   718    717     pCol->affinity = sqlite3AffinityType(z, n);
   719    718   }
   720    719   
   721    720   /*
   722    721   ** The given token is the default value for the last column added to
   723    722   ** the table currently under construction.  If "minusFlag" is true, it
   724    723   ** means the value token was preceded by a minus sign.
................................................................................
   981    980   static char *createTableStmt(Table *p){
   982    981     int i, k, n;
   983    982     char *zStmt;
   984    983     char *zSep, *zSep2, *zEnd;
   985    984     n = 0;
   986    985     for(i=0; i<p->nCol; i++){
   987    986       n += identLength(p->aCol[i].zName);
          987  +    if( p->aCol[i].zType ){
          988  +      n += (strlen(p->aCol[i].zType) + 1);
          989  +    }
   988    990     }
   989    991     n += identLength(p->zName);
   990    992     if( n<40 ){
   991    993       zSep = "";
   992    994       zSep2 = ",";
   993    995       zEnd = ")";
   994    996     }else{
................................................................................
  1004   1006     identPut(zStmt, &k, p->zName);
  1005   1007     zStmt[k++] = '(';
  1006   1008     for(i=0; i<p->nCol; i++){
  1007   1009       strcpy(&zStmt[k], zSep);
  1008   1010       k += strlen(&zStmt[k]);
  1009   1011       zSep = zSep2;
  1010   1012       identPut(zStmt, &k, p->aCol[i].zName);
         1013  +    if( p->aCol[i].zType ){
         1014  +      zStmt[k++] = ' ';
         1015  +      strcpy(&zStmt[k], p->aCol[i].zType);
         1016  +      k += strlen(p->aCol[i].zType);
         1017  +    }
  1011   1018     }
  1012   1019     strcpy(&zStmt[k], zEnd);
  1013   1020     return zStmt;
  1014   1021   }
  1015   1022   
  1016   1023   /*
  1017   1024   ** This routine is called to report the final ")" that terminates
................................................................................
  1036   1043   void sqlite3EndTable(Parse *pParse, Token *pEnd, Select *pSelect){
  1037   1044     Table *p;
  1038   1045     sqlite *db = pParse->db;
  1039   1046   
  1040   1047     if( (pEnd==0 && pSelect==0) || pParse->nErr || sqlite3_malloc_failed ) return;
  1041   1048     p = pParse->pNewTable;
  1042   1049     if( p==0 ) return;
         1050  +
         1051  +  assert( !db->init.busy || !pSelect );
  1043   1052   
  1044   1053     /* If the table is generated from a SELECT, then construct the
  1045   1054     ** list of columns and the text of the table.
  1046   1055     */
  1047   1056     if( pSelect ){
  1048         -    Table *pSelTab = sqlite3ResultSetOfSelect(pParse, 0, pSelect);
  1049         -    if( pSelTab==0 ) return;
  1050         -    assert( p->aCol==0 );
  1051         -    p->nCol = pSelTab->nCol;
  1052         -    p->aCol = pSelTab->aCol;
  1053         -    pSelTab->nCol = 0;
  1054         -    pSelTab->aCol = 0;
  1055         -    sqlite3DeleteTable(0, pSelTab);
  1056   1057     }
  1057   1058   
  1058   1059     /* If the db->init.busy is 1 it means we are reading the SQL off the
  1059   1060     ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  1060   1061     ** So do not write to the disk again.  Extract the root page number
  1061   1062     ** for the table from the db->init.newTnum field.  (The page number
  1062   1063     ** should have been put there by the sqliteOpenCb routine.)
................................................................................
  1074   1075     */
  1075   1076     if( !db->init.busy ){
  1076   1077       int n;
  1077   1078       Vdbe *v;
  1078   1079   
  1079   1080       v = sqlite3GetVdbe(pParse);
  1080   1081       if( v==0 ) return;
         1082  +
  1081   1083       if( p->pSelect==0 ){
  1082   1084         /* A regular table */
  1083   1085         sqlite3VdbeOp3(v, OP_CreateTable, 0, p->iDb, (char*)&p->tnum, P3_POINTER);
  1084   1086       }else{
  1085   1087         /* A view */
  1086   1088         sqlite3VdbeAddOp(v, OP_Integer, 0, 0);
  1087   1089       }
  1088   1090       p->tnum = 0;
  1089         -    sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
  1090         -    sqlite3VdbeOp3(v, OP_String8, 0, 0, p->pSelect==0?"table":"view", P3_STATIC);
         1091  +
         1092  +    sqlite3VdbeAddOp(v, OP_Close, 0, 0);
         1093  +
         1094  +    /* If this is a CREATE TABLE xx AS SELECT ..., execute the SELECT
         1095  +    ** statement to populate the new table. The root-page number for the
         1096  +    ** new table is on the top of the vdbe stack.
         1097  +    **
         1098  +    ** Once the SELECT has been coded by sqlite3Select(), it is in a
         1099  +    ** suitable state to query for the column names and types to be used
         1100  +    ** by the new table.
         1101  +    */
         1102  +    if( pSelect ){
         1103  +      Table *pSelTab;
         1104  +      sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
         1105  +      sqlite3VdbeAddOp(v, OP_Integer, p->iDb, 0);
         1106  +      sqlite3VdbeAddOp(v, OP_OpenWrite, 1, 0);
         1107  +      pParse->nTab = 2;
         1108  +      sqlite3Select(pParse, pSelect, SRT_Table, 1, 0, 0, 0, 0);
         1109  +      sqlite3VdbeAddOp(v, OP_Close, 1, 0);
         1110  +      if( pParse->nErr==0 ){
         1111  +        pSelTab = sqlite3ResultSetOfSelect(pParse, 0, pSelect);
         1112  +        if( pSelTab==0 ) return;
         1113  +        assert( p->aCol==0 );
         1114  +        p->nCol = pSelTab->nCol;
         1115  +        p->aCol = pSelTab->aCol;
         1116  +        pSelTab->nCol = 0;
         1117  +        pSelTab->aCol = 0;
         1118  +        sqlite3DeleteTable(0, pSelTab);
         1119  +      }
         1120  +    }
         1121  +  
         1122  +    sqlite3OpenMasterTable(v, p->iDb);
         1123  +
         1124  +    sqlite3VdbeOp3(v, OP_String8, 0, 0, p->pSelect==0?"table":"view",P3_STATIC);
  1091   1125       sqlite3VdbeOp3(v, OP_String8, 0, 0, p->zName, 0);
  1092   1126       sqlite3VdbeOp3(v, OP_String8, 0, 0, p->zName, 0);
  1093         -    sqlite3VdbeAddOp(v, OP_Dup, 4, 0);
         1127  +    sqlite3VdbeAddOp(v, OP_Pull, 3, 0);
         1128  +
  1094   1129       if( pSelect ){
  1095   1130         char *z = createTableStmt(p);
  1096   1131         n = z ? strlen(z) : 0;
  1097   1132         sqlite3VdbeAddOp(v, OP_String8, 0, 0);
  1098   1133         sqlite3VdbeChangeP3(v, -1, z, n);
  1099   1134         sqliteFree(z);
  1100   1135       }else{
................................................................................
  1111   1146       }
  1112   1147       sqlite3VdbeOp3(v, OP_MakeRecord, 5, 0, "tttit", P3_STATIC);
  1113   1148       sqlite3VdbeAddOp(v, OP_PutIntKey, 0, 0);
  1114   1149       if( p->iDb!=1 ){
  1115   1150         sqlite3ChangeCookie(db, v, p->iDb);
  1116   1151       }
  1117   1152       sqlite3VdbeAddOp(v, OP_Close, 0, 0);
  1118         -    if( pSelect ){
  1119         -      sqlite3VdbeAddOp(v, OP_Integer, p->iDb, 0);
  1120         -      sqlite3VdbeAddOp(v, OP_OpenWrite, 1, 0);
  1121         -      pParse->nTab = 2;
  1122         -      sqlite3Select(pParse, pSelect, SRT_Table, 1, 0, 0, 0, 0);
  1123         -    }
         1153  +
  1124   1154       sqlite3EndWriteOperation(pParse);
  1125   1155     }
  1126   1156   
  1127   1157     /* Add the table to the in-memory representation of the database.
  1128   1158     */
  1129   1159     if( pParse->explain==0 && pParse->nErr==0 ){
  1130   1160       Table *pOld;

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.183 2004/06/05 00:01:46 drh Exp $
           15  +** $Id: select.c,v 1.184 2004/06/07 10:00:31 danielk1977 Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Allocate a new Select structure and return a pointer to that
    22     22   ** structure.
................................................................................
   608    608     sqlite3VdbeResolveLabel(v, end2);
   609    609     sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
   610    610     sqlite3VdbeResolveLabel(v, end1);
   611    611     sqlite3VdbeAddOp(v, OP_SortReset, 0, 0);
   612    612   }
   613    613   
   614    614   /*
   615         -** Generate code that will tell the VDBE the datatypes of
   616         -** columns in the result set.
   617         -**
   618         -** This routine only generates code if the "PRAGMA show_datatypes=on"
   619         -** has been executed.  The datatypes are reported out in the azCol
   620         -** parameter to the callback function.  The first N azCol[] entries
   621         -** are the names of the columns, and the second N entries are the
   622         -** datatypes for the columns.
   623         -**
   624         -** The "datatype" for a result that is a column of a type is the
   625         -** datatype definition extracted from the CREATE TABLE statement.
   626         -** The datatype for an expression is either TEXT or NUMERIC.  The
   627         -** datatype for a ROWID field is INTEGER.
          615  +** Return a pointer to a string containing the 'declaration type' of the
          616  +** expression pExpr. The string may be treated as static by the caller.
          617  +**
          618  +** If the declaration type is the exact datatype definition extracted from
          619  +** the original CREATE TABLE statement if the expression is a column.
          620  +** 
          621  +** The declaration type for an expression is either TEXT, NUMERIC or ANY.
          622  +** The declaration type for a ROWID field is INTEGER.
          623  +*/
          624  +static const char *columnType(Parse *pParse, SrcList *pTabList, Expr *pExpr){
          625  +  char const *zType = 0;
          626  +  int j;
          627  +  if( pExpr==0 ) return 0;
          628  +  if( pExpr->op==TK_COLUMN && pTabList ){
          629  +    Table *pTab;
          630  +    int iCol = pExpr->iColumn;
          631  +    for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=pExpr->iTable; j++){}
          632  +    assert( j<pTabList->nSrc );
          633  +    pTab = pTabList->a[j].pTab;
          634  +    if( iCol<0 ) iCol = pTab->iPKey;
          635  +    assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
          636  +    if( iCol<0 ){
          637  +      zType = "INTEGER";
          638  +    }else{
          639  +      zType = pTab->aCol[iCol].zType;
          640  +    }
          641  +  }else{
          642  +    switch( sqlite3ExprType(pExpr) ){
          643  +      case SQLITE_AFF_TEXT:     zType = "TEXT";    break;
          644  +      case SQLITE_AFF_NUMERIC:  zType = "NUMERIC"; break;
          645  +      default:                  zType = "ANY";     break;
          646  +    }
          647  +  }
          648  +  return zType;
          649  +}
          650  +
          651  +/*
          652  +** Generate code that will tell the VDBE the declaration types of columns
          653  +** in the result set.
   628    654   */
   629    655   static void generateColumnTypes(
   630    656     Parse *pParse,      /* Parser context */
   631    657     SrcList *pTabList,  /* List of tables */
   632    658     ExprList *pEList    /* Expressions defining the result set */
   633    659   ){
   634    660     Vdbe *v = pParse->pVdbe;
   635         -  int i, j;
          661  +  int i;
   636    662     for(i=0; i<pEList->nExpr; i++){
   637    663       Expr *p = pEList->a[i].pExpr;
   638         -    char *zType = 0;
          664  +    const char *zType = columnType(pParse, pTabList, p);
   639    665       if( p==0 ) continue;
   640         -    if( p->op==TK_COLUMN && pTabList ){
   641         -      Table *pTab;
   642         -      int iCol = p->iColumn;
   643         -      for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=p->iTable; j++){}
   644         -      assert( j<pTabList->nSrc );
   645         -      pTab = pTabList->a[j].pTab;
   646         -      if( iCol<0 ) iCol = pTab->iPKey;
   647         -      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
   648         -      if( iCol<0 ){
   649         -        zType = "INTEGER";
   650         -      }else{
   651         -        zType = pTab->aCol[iCol].zType;
   652         -      }
   653         -    }else{
   654         -      switch( sqlite3ExprType(p) ){
   655         -        case SQLITE_AFF_TEXT:     zType = "TEXT";    break;
   656         -        case SQLITE_AFF_NUMERIC:  zType = "NUMERIC"; break;
   657         -        default:                  zType = "ANY";     break;
   658         -      }
   659         -    }
   660    666       sqlite3VdbeSetColName(v, i+pEList->nExpr, zType, P3_STATIC);
   661    667     }
   662    668   }
   663    669   
   664    670   /*
   665    671   ** Generate code that will tell the VDBE the names of columns
   666    672   ** in the result set.  This information is used to provide the
................................................................................
   775    781     }
   776    782     pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
   777    783     pEList = pSelect->pEList;
   778    784     pTab->nCol = pEList->nExpr;
   779    785     assert( pTab->nCol>0 );
   780    786     pTab->aCol = aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
   781    787     for(i=0; i<pTab->nCol; i++){
   782         -    Expr *p, *pR;
          788  +    Expr *pR;
          789  +    char *zType;
          790  +    Expr *p = pEList->a[i].pExpr;
   783    791       if( pEList->a[i].zName ){
   784    792         aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
   785         -    }else if( (p=pEList->a[i].pExpr)->op==TK_DOT 
          793  +    }else if( p->op==TK_DOT 
   786    794                  && (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){
   787    795         int cnt;
   788    796         sqlite3SetNString(&aCol[i].zName, pR->token.z, pR->token.n, 0);
   789    797         for(j=cnt=0; j<i; j++){
   790    798           if( sqlite3StrICmp(aCol[j].zName, aCol[i].zName)==0 ){
   791    799             int n;
   792    800             char zBuf[30];
................................................................................
   799    807       }else if( p->span.z && p->span.z[0] ){
   800    808         sqlite3SetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
   801    809       }else{
   802    810         char zBuf[30];
   803    811         sprintf(zBuf, "column%d", i+1);
   804    812         pTab->aCol[i].zName = sqliteStrDup(zBuf);
   805    813       }
   806         -    
   807         -    /* Affinity is always NONE, as there is no type name. */
   808         -    pTab->aCol[i].affinity = SQLITE_AFF_NONE;
          814  +
          815  +    zType = sqliteStrDup(columnType(pParse, pSelect->pSrc ,p));
          816  +    pTab->aCol[i].zType = zType;
          817  +    pTab->aCol[i].affinity = SQLITE_AFF_NUMERIC;
          818  +    if( zType ){
          819  +      pTab->aCol[i].affinity = sqlite3AffinityType(zType, strlen(zType));
          820  +    }
   809    821     }
   810    822     pTab->iPKey = -1;
   811    823     return pTab;
   812    824   }
   813    825   
   814    826   /*
   815    827   ** For the given SELECT statement, do three things.

Changes to test/table.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the CREATE TABLE statement.
    13     13   #
    14         -# $Id: table.test,v 1.24 2004/05/24 12:55:55 danielk1977 Exp $
           14  +# $Id: table.test,v 1.25 2004/06/07 10:00:31 danielk1977 Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Create a basic table and verify it is added to sqlite_master
    20     20   #
    21     21   do_test table-1.1 {
................................................................................
   301    301       SELECT * FROM weird;
   302    302     }
   303    303   } {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
   304    304   
   305    305   # Try out the CREATE TABLE AS syntax
   306    306   #
   307    307   do_test table-8.1 {
          308  +breakpoint
   308    309     execsql2 {
   309    310       CREATE TABLE t2 AS SELECT * FROM weird;
   310    311       SELECT * FROM t2;
   311    312     }
   312    313   } {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
   313    314   do_test table-8.1.1 {
   314    315     execsql {
   315    316       SELECT sql FROM sqlite_master WHERE name='t2';
   316    317     }
   317    318   } {{CREATE TABLE t2(
   318         -  'desc',
   319         -  'asc',
   320         -  'explain',
   321         -  '14_vac',
   322         -  fuzzy_dog_12,
   323         -  'begin',
   324         -  'end'
          319  +  'desc' text,
          320  +  'asc' text,
          321  +  'explain' int,
          322  +  '14_vac' boolean,
          323  +  fuzzy_dog_12 varchar(10),
          324  +  'begin' blob,
          325  +  'end' clob
   325    326   )}}
   326    327   do_test table-8.2 {
   327    328     execsql {
   328    329       CREATE TABLE 't3''xyz'(a,b,c);
   329    330       INSERT INTO [t3'xyz] VALUES(1,2,3);
   330    331       SELECT * FROM [t3'xyz];
   331    332     }
................................................................................
   336    337       SELECT * FROM [t4'abc];
   337    338     }
   338    339   } {cnt 1 max(b+c) 5}
   339    340   do_test table-8.3.1 {
   340    341     execsql {
   341    342       SELECT sql FROM sqlite_master WHERE name='t4''abc'
   342    343     }
   343         -} {{CREATE TABLE 't4''abc'(cnt,'max(b+c)')}}
          344  +} {{CREATE TABLE 't4''abc'(cnt NUMERIC,'max(b+c)' NUMERIC)}}
   344    345   do_test table-8.4 {
   345    346     execsql2 {
   346    347       CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3'xyz];
   347    348       SELECT * FROM t5;
   348    349     }
   349    350   } {y'all 1}
   350    351   do_test table-8.5 {