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: |
31c1668dbc2b84438a5b78b0270f58b3 |
| 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
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 {