Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -21,11 +21,11 @@ ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** -** $Id: build.c,v 1.207 2004/06/07 07:52:18 danielk1977 Exp $ +** $Id: build.c,v 1.208 2004/06/07 10:00:31 danielk1977 Exp $ */ #include "sqliteInt.h" #include /* @@ -712,11 +712,10 @@ int c = z[i]; if( isspace(c) ) continue; z[j++] = c; } z[j] = 0; -// pCol->sortOrder = sqlite3CollateType(z, n); pCol->affinity = sqlite3AffinityType(z, n); } /* ** The given token is the default value for the last column added to @@ -983,10 +982,13 @@ char *zStmt; char *zSep, *zSep2, *zEnd; n = 0; for(i=0; inCol; i++){ n += identLength(p->aCol[i].zName); + if( p->aCol[i].zType ){ + n += (strlen(p->aCol[i].zType) + 1); + } } n += identLength(p->zName); if( n<40 ){ zSep = ""; zSep2 = ","; @@ -1006,10 +1008,15 @@ for(i=0; inCol; i++){ strcpy(&zStmt[k], zSep); k += strlen(&zStmt[k]); zSep = zSep2; identPut(zStmt, &k, p->aCol[i].zName); + if( p->aCol[i].zType ){ + zStmt[k++] = ' '; + strcpy(&zStmt[k], p->aCol[i].zType); + k += strlen(p->aCol[i].zType); + } } strcpy(&zStmt[k], zEnd); return zStmt; } @@ -1038,23 +1045,17 @@ sqlite *db = pParse->db; if( (pEnd==0 && pSelect==0) || pParse->nErr || sqlite3_malloc_failed ) return; p = pParse->pNewTable; if( p==0 ) return; + + assert( !db->init.busy || !pSelect ); /* If the table is generated from a SELECT, then construct the ** list of columns and the text of the table. */ if( pSelect ){ - Table *pSelTab = sqlite3ResultSetOfSelect(pParse, 0, pSelect); - if( pSelTab==0 ) return; - assert( p->aCol==0 ); - p->nCol = pSelTab->nCol; - p->aCol = pSelTab->aCol; - pSelTab->nCol = 0; - pSelTab->aCol = 0; - sqlite3DeleteTable(0, pSelTab); } /* If the db->init.busy is 1 it means we are reading the SQL off the ** "sqlite_master" or "sqlite_temp_master" table on the disk. ** So do not write to the disk again. Extract the root page number @@ -1076,23 +1077,57 @@ int n; Vdbe *v; v = sqlite3GetVdbe(pParse); if( v==0 ) return; + if( p->pSelect==0 ){ /* A regular table */ sqlite3VdbeOp3(v, OP_CreateTable, 0, p->iDb, (char*)&p->tnum, P3_POINTER); }else{ /* A view */ sqlite3VdbeAddOp(v, OP_Integer, 0, 0); } p->tnum = 0; - sqlite3VdbeAddOp(v, OP_Pull, 1, 0); - sqlite3VdbeOp3(v, OP_String8, 0, 0, p->pSelect==0?"table":"view", P3_STATIC); + + sqlite3VdbeAddOp(v, OP_Close, 0, 0); + + /* If this is a CREATE TABLE xx AS SELECT ..., execute the SELECT + ** statement to populate the new table. The root-page number for the + ** new table is on the top of the vdbe stack. + ** + ** Once the SELECT has been coded by sqlite3Select(), it is in a + ** suitable state to query for the column names and types to be used + ** by the new table. + */ + if( pSelect ){ + Table *pSelTab; + sqlite3VdbeAddOp(v, OP_Dup, 0, 0); + sqlite3VdbeAddOp(v, OP_Integer, p->iDb, 0); + sqlite3VdbeAddOp(v, OP_OpenWrite, 1, 0); + pParse->nTab = 2; + sqlite3Select(pParse, pSelect, SRT_Table, 1, 0, 0, 0, 0); + sqlite3VdbeAddOp(v, OP_Close, 1, 0); + if( pParse->nErr==0 ){ + pSelTab = sqlite3ResultSetOfSelect(pParse, 0, pSelect); + if( pSelTab==0 ) return; + assert( p->aCol==0 ); + p->nCol = pSelTab->nCol; + p->aCol = pSelTab->aCol; + pSelTab->nCol = 0; + pSelTab->aCol = 0; + sqlite3DeleteTable(0, pSelTab); + } + } + + sqlite3OpenMasterTable(v, p->iDb); + + sqlite3VdbeOp3(v, OP_String8, 0, 0, p->pSelect==0?"table":"view",P3_STATIC); sqlite3VdbeOp3(v, OP_String8, 0, 0, p->zName, 0); sqlite3VdbeOp3(v, OP_String8, 0, 0, p->zName, 0); - sqlite3VdbeAddOp(v, OP_Dup, 4, 0); + sqlite3VdbeAddOp(v, OP_Pull, 3, 0); + if( pSelect ){ char *z = createTableStmt(p); n = z ? strlen(z) : 0; sqlite3VdbeAddOp(v, OP_String8, 0, 0); sqlite3VdbeChangeP3(v, -1, z, n); @@ -1113,16 +1148,11 @@ sqlite3VdbeAddOp(v, OP_PutIntKey, 0, 0); if( p->iDb!=1 ){ sqlite3ChangeCookie(db, v, p->iDb); } sqlite3VdbeAddOp(v, OP_Close, 0, 0); - if( pSelect ){ - sqlite3VdbeAddOp(v, OP_Integer, p->iDb, 0); - sqlite3VdbeAddOp(v, OP_OpenWrite, 1, 0); - pParse->nTab = 2; - sqlite3Select(pParse, pSelect, SRT_Table, 1, 0, 0, 0, 0); - } + sqlite3EndWriteOperation(pParse); } /* Add the table to the in-memory representation of the database. */ Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -10,11 +10,11 @@ ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** -** $Id: select.c,v 1.183 2004/06/05 00:01:46 drh Exp $ +** $Id: select.c,v 1.184 2004/06/07 10:00:31 danielk1977 Exp $ */ #include "sqliteInt.h" /* @@ -610,55 +610,61 @@ sqlite3VdbeResolveLabel(v, end1); sqlite3VdbeAddOp(v, OP_SortReset, 0, 0); } /* -** Generate code that will tell the VDBE the datatypes of -** columns in the result set. -** -** This routine only generates code if the "PRAGMA show_datatypes=on" -** has been executed. The datatypes are reported out in the azCol -** parameter to the callback function. The first N azCol[] entries -** are the names of the columns, and the second N entries are the -** datatypes for the columns. -** -** The "datatype" for a result that is a column of a type is the -** datatype definition extracted from the CREATE TABLE statement. -** The datatype for an expression is either TEXT or NUMERIC. The -** datatype for a ROWID field is INTEGER. +** Return a pointer to a string containing the 'declaration type' of the +** expression pExpr. The string may be treated as static by the caller. +** +** If the declaration type is the exact datatype definition extracted from +** the original CREATE TABLE statement if the expression is a column. +** +** The declaration type for an expression is either TEXT, NUMERIC or ANY. +** The declaration type for a ROWID field is INTEGER. +*/ +static const char *columnType(Parse *pParse, SrcList *pTabList, Expr *pExpr){ + char const *zType = 0; + int j; + if( pExpr==0 ) return 0; + if( pExpr->op==TK_COLUMN && pTabList ){ + Table *pTab; + int iCol = pExpr->iColumn; + for(j=0; jnSrc && pTabList->a[j].iCursor!=pExpr->iTable; j++){} + assert( jnSrc ); + pTab = pTabList->a[j].pTab; + if( iCol<0 ) iCol = pTab->iPKey; + assert( iCol==-1 || (iCol>=0 && iColnCol) ); + if( iCol<0 ){ + zType = "INTEGER"; + }else{ + zType = pTab->aCol[iCol].zType; + } + }else{ + switch( sqlite3ExprType(pExpr) ){ + case SQLITE_AFF_TEXT: zType = "TEXT"; break; + case SQLITE_AFF_NUMERIC: zType = "NUMERIC"; break; + default: zType = "ANY"; break; + } + } + return zType; +} + +/* +** Generate code that will tell the VDBE the declaration types of columns +** in the result set. */ static void generateColumnTypes( Parse *pParse, /* Parser context */ SrcList *pTabList, /* List of tables */ ExprList *pEList /* Expressions defining the result set */ ){ Vdbe *v = pParse->pVdbe; - int i, j; + int i; for(i=0; inExpr; i++){ Expr *p = pEList->a[i].pExpr; - char *zType = 0; + const char *zType = columnType(pParse, pTabList, p); if( p==0 ) continue; - if( p->op==TK_COLUMN && pTabList ){ - Table *pTab; - int iCol = p->iColumn; - for(j=0; jnSrc && pTabList->a[j].iCursor!=p->iTable; j++){} - assert( jnSrc ); - pTab = pTabList->a[j].pTab; - if( iCol<0 ) iCol = pTab->iPKey; - assert( iCol==-1 || (iCol>=0 && iColnCol) ); - if( iCol<0 ){ - zType = "INTEGER"; - }else{ - zType = pTab->aCol[iCol].zType; - } - }else{ - switch( sqlite3ExprType(p) ){ - case SQLITE_AFF_TEXT: zType = "TEXT"; break; - case SQLITE_AFF_NUMERIC: zType = "NUMERIC"; break; - default: zType = "ANY"; break; - } - } sqlite3VdbeSetColName(v, i+pEList->nExpr, zType, P3_STATIC); } } /* @@ -777,14 +783,16 @@ pEList = pSelect->pEList; pTab->nCol = pEList->nExpr; assert( pTab->nCol>0 ); pTab->aCol = aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol ); for(i=0; inCol; i++){ - Expr *p, *pR; + Expr *pR; + char *zType; + Expr *p = pEList->a[i].pExpr; if( pEList->a[i].zName ){ aCol[i].zName = sqliteStrDup(pEList->a[i].zName); - }else if( (p=pEList->a[i].pExpr)->op==TK_DOT + }else if( p->op==TK_DOT && (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){ int cnt; sqlite3SetNString(&aCol[i].zName, pR->token.z, pR->token.n, 0); for(j=cnt=0; jaCol[i].zName = sqliteStrDup(zBuf); } - - /* Affinity is always NONE, as there is no type name. */ - pTab->aCol[i].affinity = SQLITE_AFF_NONE; + + zType = sqliteStrDup(columnType(pParse, pSelect->pSrc ,p)); + pTab->aCol[i].zType = zType; + pTab->aCol[i].affinity = SQLITE_AFF_NUMERIC; + if( zType ){ + pTab->aCol[i].affinity = sqlite3AffinityType(zType, strlen(zType)); + } } pTab->iPKey = -1; return pTab; } Index: test/table.test ================================================================== --- test/table.test +++ test/table.test @@ -9,11 +9,11 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE TABLE statement. # -# $Id: table.test,v 1.24 2004/05/24 12:55:55 danielk1977 Exp $ +# $Id: table.test,v 1.25 2004/06/07 10:00:31 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a basic table and verify it is added to sqlite_master @@ -303,10 +303,11 @@ } {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} # Try out the CREATE TABLE AS syntax # do_test table-8.1 { +breakpoint execsql2 { CREATE TABLE t2 AS SELECT * FROM weird; SELECT * FROM t2; } } {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} @@ -313,17 +314,17 @@ do_test table-8.1.1 { execsql { SELECT sql FROM sqlite_master WHERE name='t2'; } } {{CREATE TABLE t2( - 'desc', - 'asc', - 'explain', - '14_vac', - fuzzy_dog_12, - 'begin', - 'end' + 'desc' text, + 'asc' text, + 'explain' int, + '14_vac' boolean, + fuzzy_dog_12 varchar(10), + 'begin' blob, + 'end' clob )}} do_test table-8.2 { execsql { CREATE TABLE 't3''xyz'(a,b,c); INSERT INTO [t3'xyz] VALUES(1,2,3); @@ -338,11 +339,11 @@ } {cnt 1 max(b+c) 5} do_test table-8.3.1 { execsql { SELECT sql FROM sqlite_master WHERE name='t4''abc' } -} {{CREATE TABLE 't4''abc'(cnt,'max(b+c)')}} +} {{CREATE TABLE 't4''abc'(cnt NUMERIC,'max(b+c)' NUMERIC)}} do_test table-8.4 { execsql2 { CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3'xyz]; SELECT * FROM t5; }