Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add support for CREATE TABLE AS. (CVS 377) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
78a50971e9adc8739e7888201c79465a |
User & Date: | drh 2002-02-18 18:30:32.000 |
Context
2002-02-18
| ||
22:49 | Bug fix: CREATE TABLE, followed by DROP TABLE within the same transaction is working now. (CVS 378) (check-in: 553579f936 user: drh tags: trunk) | |
18:30 | Add support for CREATE TABLE AS. (CVS 377) (check-in: 78a50971e9 user: drh tags: trunk) | |
13:56 | Allow general expressions in the VALUES clause of an INSERT statement. (CVS 376) (check-in: ec1f3fae6f user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
21 22 23 24 25 26 27 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** | | | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** ** $Id: build.c,v 1.75 2002/02/18 18:30:32 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called after a single SQL statement has been ** parsed and we want to execute the VDBE code to implement |
︙ | ︙ | |||
645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 | */ static void changeCookie(sqlite *db){ if( db->next_cookie==db->schema_cookie ){ db->next_cookie = db->schema_cookie + sqliteRandomByte() + 1; db->flags |= SQLITE_InternChanges; } } /* ** This routine is called to report the final ")" that terminates ** a CREATE TABLE statement. ** ** The table structure that other action routines have been building ** is added to the internal hash tables, assuming no errors have ** occurred. ** ** An entry for the table is made in the master table on disk, ** unless this is a temporary table or initFlag==1. When initFlag==1, ** it means we are reading the sqlite_master table because we just ** connected to the database or because the sqlite_master table has ** recently changes, so the entry for this table already exists in ** the sqlite_master table. We do not want to create it again. */ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | > > > > > > > > > > > > > | 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 | */ static void changeCookie(sqlite *db){ if( db->next_cookie==db->schema_cookie ){ db->next_cookie = db->schema_cookie + sqliteRandomByte() + 1; db->flags |= SQLITE_InternChanges; } } /* ** Measure the number of characters needed to output the given ** identifier. The number returned includes any quotes used ** but does not include the null terminator. */ static int identLength(const char *z){ int n; for(n=2; *z; n++, z++){ if( *z=='\'' ){ n++; } } return n; } /* ** Write an identifier onto the end of the given string. Add ** quote characters as needed. */ static void identPut(char *z, int *pIdx, char *zIdent){ int i, j; i = *pIdx; z[i++] = '\''; for(j=0; zIdent[j]; j++){ z[i++] = zIdent[j]; if( zIdent[j]=='\'' ) z[i++] = '\''; } z[i++] = '\''; z[i] = 0; *pIdx = i; } /* ** Generate a CREATE TABLE statement appropriate for the given ** table. Memory to hold the text of the statement is obtained ** from sqliteMalloc() and must be freed by the calling function. */ static char *createTableStmt(Table *p){ int i, k, n; char *zStmt; char *zSep, *zSep2, *zEnd; n = 0; for(i=0; i<p->nCol; i++){ n += identLength(p->aCol[i].zName); } n += identLength(p->zName); if( n<40 ){ zSep = ""; zSep2 = ","; zEnd = ")"; }else{ zSep = "\n "; zSep2 = ",\n "; zEnd = "\n)"; } n += 25 + 6*p->nCol; zStmt = sqliteMalloc( n ); if( zStmt==0 ) return 0; assert( !p->isTemp ); strcpy(zStmt, "CREATE TABLE "); k = strlen(zStmt); identPut(zStmt, &k, p->zName); zStmt[k++] = '('; for(i=0; i<p->nCol; i++){ strcpy(&zStmt[k], zSep); k += strlen(&zStmt[k]); zSep = zSep2; identPut(zStmt, &k, p->aCol[i].zName); } strcpy(&zStmt[k], zEnd); return zStmt; } /* ** This routine is called to report the final ")" that terminates ** a CREATE TABLE statement. ** ** The table structure that other action routines have been building ** is added to the internal hash tables, assuming no errors have ** occurred. ** ** An entry for the table is made in the master table on disk, ** unless this is a temporary table or initFlag==1. When initFlag==1, ** it means we are reading the sqlite_master table because we just ** connected to the database or because the sqlite_master table has ** recently changes, so the entry for this table already exists in ** the sqlite_master table. We do not want to create it again. ** ** If the pSelect argument is not NULL, it means that this routine ** was called to create a table generated from a ** "CREATE TABLE ... AS SELECT ..." statement. The column names of ** the new table will match the result set of the SELECT. */ void sqliteEndTable(Parse *pParse, Token *pEnd, Select *pSelect){ Table *p; sqlite *db = pParse->db; if( (pEnd==0 && pSelect==0) || pParse->nErr || sqlite_malloc_failed ) return; p = pParse->pNewTable; if( p==0 ) return; /* Add the table to the in-memory representation of the database. */ assert( pParse->nameClash==0 || pParse->initFlag==1 ); if( pParse->explain==0 && pParse->nameClash==0 ){ Table *pOld; pOld = sqliteHashInsert(&db->tblHash, p->zName, strlen(p->zName)+1, p); if( pOld ){ assert( p==pOld ); /* Malloc must have failed inside HashInsert() */ return; } pParse->pNewTable = 0; db->nTable++; db->flags |= SQLITE_InternChanges; } /* If the table is generated from a SELECT, then construct the ** list of columns and the text of the table. */ if( pSelect ){ Table *pSelTab = sqliteResultSetOfSelect(pParse, 0, pSelect); assert( p->aCol==0 ); p->nCol = pSelTab->nCol; p->aCol = pSelTab->aCol; pSelTab->nCol = 0; pSelTab->aCol = 0; sqliteDeleteTable(0, pSelTab); } /* If the initFlag is 1 it means we are reading the SQL off the ** "sqlite_master" table on the disk. So do not write to the disk ** again. Extract the root page number for the table from the ** pParse->newTnum field. (The page number should have been put ** there by the sqliteOpenCb routine.) */ |
︙ | ︙ | |||
706 707 708 709 710 711 712 | */ if( !pParse->initFlag ){ int n, addr; Vdbe *v; v = sqliteGetVdbe(pParse); if( v==0 ) return; | | > > < | < < < | > > > | > > > > > > > > > > > > | 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 | */ if( !pParse->initFlag ){ int n, addr; Vdbe *v; v = sqliteGetVdbe(pParse); if( v==0 ) return; addr = sqliteVdbeAddOp(v, OP_CreateTable, 0, p->isTemp); sqliteVdbeChangeP3(v, addr, (char *)&p->tnum, P3_POINTER); p->tnum = 0; if( !p->isTemp ){ sqliteVdbeAddOp(v, OP_NewRecno, 0, 0); sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeChangeP3(v, -1, "table", P3_STATIC); sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeChangeP3(v, -1, p->zName, P3_STATIC); sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeChangeP3(v, -1, p->zName, P3_STATIC); sqliteVdbeAddOp(v, OP_Dup, 4, 0); sqliteVdbeAddOp(v, OP_String, 0, 0); if( pSelect ){ char *z = createTableStmt(p); n = z ? strlen(z) : 0; sqliteVdbeChangeP3(v, -1, z, n); sqliteFree(z); }else{ assert( pEnd!=0 ); n = Addr(pEnd->z) - Addr(pParse->sFirstToken.z) + 1; sqliteVdbeChangeP3(v, -1, pParse->sFirstToken.z, n); } sqliteVdbeAddOp(v, OP_MakeRecord, 5, 0); sqliteVdbeAddOp(v, OP_PutIntKey, 0, 0); changeCookie(db); sqliteVdbeAddOp(v, OP_SetCookie, db->next_cookie, 0); sqliteVdbeAddOp(v, OP_Close, 0, 0); } if( pSelect ){ int op = p->isTemp ? OP_OpenWrAux : OP_OpenWrite; sqliteVdbeAddOp(v, op, 1, 0); pParse->nTab = 2; sqliteSelect(pParse, pSelect, SRT_Table, 1); } sqliteEndWriteOperation(pParse); } } /* ** Given a token, look up a table with that name. If not found, leave ** an error for the parser to find and return NULL. |
︙ | ︙ |
Changes to src/main.c.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** Main file for the SQLite library. The routines in this file ** implement the programmer interface to the library. Routines in ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** Main file for the SQLite library. The routines in this file ** implement the programmer interface to the library. Routines in ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** ** $Id: main.c,v 1.59 2002/02/18 18:30:33 drh Exp $ */ #include "sqliteInt.h" #include "os.h" /* ** This is the callback routine for the code that initializes the ** database. See sqliteInit() below for additional information. |
︙ | ︙ | |||
390 391 392 393 394 395 396 397 398 399 400 401 402 403 | break; } case ' ': case '\t': case '\n': case '\f': { break; } case '\'': { isComplete = 0; zSql++; while( *zSql && *zSql!='\'' ){ zSql++; } if( *zSql==0 ) return 0; break; | > > > > > > > | 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 | break; } case ' ': case '\t': case '\n': case '\f': { break; } case '[': { isComplete = 0; zSql++; while( *zSql && *zSql!=']' ){ zSql++; } if( *zSql==0 ) return 0; break; } case '\'': { isComplete = 0; zSql++; while( *zSql && *zSql!='\'' ){ zSql++; } if( *zSql==0 ) return 0; break; |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** This file contains SQLite's grammar for SQL. Process this file ** using the lemon parser generator to generate C code that runs ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** This file contains SQLite's grammar for SQL. Process this file ** using the lemon parser generator to generate C code that runs ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** ** @(#) $Id: parse.y,v 1.51 2002/02/18 18:30:33 drh Exp $ */ %token_prefix TK_ %token_type {Token} %default_type {Token} %extra_argument {Parse *pParse} %syntax_error { sqliteSetString(&pParse->zErrMsg,"syntax error",0); |
︙ | ︙ | |||
64 65 66 67 68 69 70 | cmd ::= COMMIT trans_opt. {sqliteCommitTransaction(pParse);} cmd ::= END trans_opt. {sqliteCommitTransaction(pParse);} cmd ::= ROLLBACK trans_opt. {sqliteRollbackTransaction(pParse);} ///////////////////// The CREATE TABLE statement //////////////////////////// // cmd ::= create_table create_table_args. | | | > | | > > > > > | 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | cmd ::= COMMIT trans_opt. {sqliteCommitTransaction(pParse);} cmd ::= END trans_opt. {sqliteCommitTransaction(pParse);} cmd ::= ROLLBACK trans_opt. {sqliteRollbackTransaction(pParse);} ///////////////////// The CREATE TABLE statement //////////////////////////// // cmd ::= create_table create_table_args. create_table ::= CREATE(X) temp(T) TABLE ids(Y). { sqliteStartTable(pParse,&X,&Y,T); } %type temp {int} temp(A) ::= TEMP. {A = 1;} temp(A) ::= . {A = 0;} create_table_args ::= LP columnlist conslist_opt RP(X). { sqliteEndTable(pParse,&X,0); } create_table_args ::= AS select(S). { sqliteEndTable(pParse,0,S); sqliteSelectDelete(S); } columnlist ::= columnlist COMMA column. columnlist ::= column. // About the only information used for a column is the name of the // column. The type is always just "text". But the code will accept // an elaborate typename. Perhaps someday we'll do something with it. // |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.87 2002/02/18 18:30:33 drh Exp $ */ #include "sqlite.h" #include "hash.h" #include "vdbe.h" #include "parse.h" #include "btree.h" #include <stdio.h> |
︙ | ︙ | |||
562 563 564 565 566 567 568 569 570 571 572 573 574 | Expr *sqliteExprFunction(ExprList*, Token*); void sqliteExprDelete(Expr*); ExprList *sqliteExprListAppend(ExprList*,Expr*,Token*); void sqliteExprListDelete(ExprList*); void sqlitePragma(Parse*,Token*,Token*,int); void sqliteCommitInternalChanges(sqlite*); void sqliteRollbackInternalChanges(sqlite*); void sqliteStartTable(Parse*,Token*,Token*,int); void sqliteAddColumn(Parse*,Token*); void sqliteAddNotNull(Parse*, int); void sqliteAddPrimaryKey(Parse*, IdList*, int); void sqliteAddColumnType(Parse*,Token*,Token*); void sqliteAddDefaultValue(Parse*,Token*,int); | > | | 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 | Expr *sqliteExprFunction(ExprList*, Token*); void sqliteExprDelete(Expr*); ExprList *sqliteExprListAppend(ExprList*,Expr*,Token*); void sqliteExprListDelete(ExprList*); void sqlitePragma(Parse*,Token*,Token*,int); void sqliteCommitInternalChanges(sqlite*); void sqliteRollbackInternalChanges(sqlite*); Table *sqliteResultSetOfSelect(Parse*,char*,Select*); void sqliteStartTable(Parse*,Token*,Token*,int); void sqliteAddColumn(Parse*,Token*); void sqliteAddNotNull(Parse*, int); void sqliteAddPrimaryKey(Parse*, IdList*, int); void sqliteAddColumnType(Parse*,Token*,Token*); void sqliteAddDefaultValue(Parse*,Token*,int); void sqliteEndTable(Parse*,Token*,Select*); void sqliteDropTable(Parse*, Token*); void sqliteDeleteTable(sqlite*, Table*); void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int); IdList *sqliteIdListAppend(IdList*, Token*); void sqliteIdListAddAlias(IdList*, Token*); void sqliteIdListDelete(IdList*); void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*); |
︙ | ︙ |
Changes to test/table.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE TABLE statement. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE TABLE statement. # # $Id: table.test,v 1.14 2002/02/18 18:30:33 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a basic table and verify it is added to sqlite_master # do_test table-1.1 { |
︙ | ︙ | |||
297 298 299 300 301 302 303 304 305 | } } {a b 9 0 xyz hi y'all} do_test table-7.3 { execsql2 { SELECT * FROM weird; } } {desc a asc b explain 9 vacuum 0 delimiters xyz begin hi end y'all} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | } } {a b 9 0 xyz hi y'all} do_test table-7.3 { execsql2 { SELECT * FROM weird; } } {desc a asc b explain 9 vacuum 0 delimiters xyz begin hi end y'all} # Try out the CREATE TABLE AS syntax # do_test table-8.1 { execsql2 { CREATE TABLE t2 AS SELECT * FROM weird; SELECT * FROM t2; } } {desc a asc b explain 9 vacuum 0 delimiters xyz begin hi end y'all} do_test table-8.2 { execsql { CREATE TABLE 't3''xyz'(a,b,c); INSERT INTO [t3'xyz] VALUES(1,2,3); SELECT * FROM [t3'xyz]; } } {1 2 3} do_test table-8.3 { execsql2 { CREATE TABLE [t4'abc] AS SELECT count(*), min(a), max(b+c) FROM [t3'xyz]; SELECT * FROM [t4'abc]; } } {count(*) 1 min(a) 1 max(b+c) 5} do_test table-8.4 { execsql2 { CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3'xyz]; SELECT * FROM t5; } } {y'all 1} do_test table-8.5 { db close sqlite db test.db execsql2 { SELECT * FROM [t4'abc]; } } {count(*) 1 min(a) 1 max(b+c) 5} do_test table-8.6 { execsql2 { SELECT * FROM t2; } } {desc a asc b explain 9 vacuum 0 delimiters xyz begin hi end y'all} do_test table-8.7 { catchsql { SELECT * FROM t5; } } {1 {no such table: t5}} finish_test |
Changes to www/changes.tcl.
︙ | ︙ | |||
21 22 23 24 25 26 27 28 29 30 31 32 33 34 | <li>Allow identifiers to be quoted in square brackets, for compatibility with MS-Access.</li> <li>Added support for sub-queries in the FROM clause of a SELECT.</li> <li>More efficient implementation of sqliteFileExists() under Windows. (by Joel Luscy)</li> <li>The VALUES clause of an INSERT can now contain expressions, including scalar SELECT clauses.</li> } chng {2002 Feb 14 (2.3.2)} { <li>Bug fix: There was an incorrect assert() in pager.c. The real code was all correct (as far as is known) so everything should work OK if you compile with -DNDEBUG=1. But without disability the assertions, there could be a fault.</li> | > | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | <li>Allow identifiers to be quoted in square brackets, for compatibility with MS-Access.</li> <li>Added support for sub-queries in the FROM clause of a SELECT.</li> <li>More efficient implementation of sqliteFileExists() under Windows. (by Joel Luscy)</li> <li>The VALUES clause of an INSERT can now contain expressions, including scalar SELECT clauses.</li> <li>Added support for CREATE TABLE AS SELECT</li> } chng {2002 Feb 14 (2.3.2)} { <li>Bug fix: There was an incorrect assert() in pager.c. The real code was all correct (as far as is known) so everything should work OK if you compile with -DNDEBUG=1. But without disability the assertions, there could be a fault.</li> |
︙ | ︙ |
Changes to www/index.tcl.
1 2 3 | # # Run this TCL script to generate HTML for the index.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this TCL script to generate HTML for the index.html file. # set rcsid {$Id: index.tcl,v 1.54 2002/02/18 18:30:33 drh Exp $} puts {<html> <head><title>SQLite: An SQL Database Engine In A C Library</title></head> <body bgcolor=white> <h1 align=center>SQLite: An SQL Database Engine In A C Library</h1> <p align=center>} puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>" |
︙ | ︙ | |||
40 41 42 43 44 45 46 | puts {<h2>Features</h2> <p><ul> <li>Implements a large subset of SQL92.</li> <li>A complete database (with multiple tables and indices) is stored in a single disk file.</li> <li>Atomic commit and rollback protect data integrity.</li> | | | 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | puts {<h2>Features</h2> <p><ul> <li>Implements a large subset of SQL92.</li> <li>A complete database (with multiple tables and indices) is stored in a single disk file.</li> <li>Atomic commit and rollback protect data integrity.</li> <li>Small memory footprint: less than 20K lines of C code.</li> <li><a href="speed.html">Four times faster</a> than PostgreSQL. Twice as fast as SQLite 1.0.</li> <li>Very simple <a href="c_interface.html">C/C++ interface</a> requires the use of only three functions and one opaque structure.</li> <li><a href="tclsqlite.html">TCL bindings</a> included.</li> <li>A TCL-based test suite provides near 100% code coverage.</li> |
︙ | ︙ |
Changes to www/lang.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: lang.tcl,v 1.24 2002/02/18 18:30:33 drh Exp $} puts {<html> <head> <title>Query Language Understood By SQLite</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
241 242 243 244 245 246 247 248 | Section {CREATE TABLE} {createtable} Syntax {sql-command} { CREATE [TEMP | TEMPORARY] TABLE <table-name> ( <column-def> [, <column-def>]* [, <constraint>]* ) } {column-def} { | > > | | 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 | Section {CREATE TABLE} {createtable} Syntax {sql-command} { CREATE [TEMP | TEMPORARY] TABLE <table-name> ( <column-def> [, <column-def>]* [, <constraint>]* ) } {sql-command} { CREATE [TEMP | TEMPORARY] TABLE <table-name> AS <select-statement> } {column-def} { <name> [<type>] [<column-constraint>]* } {type} { <typename> | <typename> ( <number> ) | <typename> ( <number> , <number> ) } {column-constraint} { NOT NULL [ <conflict-clause> ] | PRIMARY KEY [<sort-order>] [ <conflict-clause> ] | |
︙ | ︙ | |||
270 271 272 273 274 275 276 | followed by the name of a new table and a parenthesized list of column definitions and constraints. The table name can be either an identifier or a string. The only reserved table name is "<b>sqlite_master</b>" which is the name of the table that records the database schema.</p> <p>Each column definition is the name of the column followed by the datatype for that column, then one or more optional column constraints. | | | | 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 | followed by the name of a new table and a parenthesized list of column definitions and constraints. The table name can be either an identifier or a string. The only reserved table name is "<b>sqlite_master</b>" which is the name of the table that records the database schema.</p> <p>Each column definition is the name of the column followed by the datatype for that column, then one or more optional column constraints. The datatype for the column is (usually) ignored and may be omitted. All information is stored as null-terminated strings. The UNIQUE constraint causes an index to be created on the specified columns. This index must contain unique keys. The DEFAULT constraint specifies a default value to use when doing an INSERT. </p> <p>Specifying a PRIMARY KEY normally just creates a UNIQUE index |
︙ | ︙ | |||
321 322 323 324 325 326 327 328 329 330 331 | <p>There are no arbitrary limits on the number of columns or on the number of constraints in a table. The total amount of data in a single row is limited to about 1 megabytes. (This limit can be increased to 16MB by changing a single #define in the source code and recompiling.)</p> <p>The exact text of each CREATE TABLE statement is stored in the <b>sqlite_master</b> table. Everytime the database is opened, all CREATE TABLE statements are read from the <b>sqlite_master</b> table and used to regenerate | > > > > | > > > > | 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 | <p>There are no arbitrary limits on the number of columns or on the number of constraints in a table. The total amount of data in a single row is limited to about 1 megabytes. (This limit can be increased to 16MB by changing a single #define in the source code and recompiling.)</p> <p>The CREATE TABLE AS form defines the table to be the result set of a query. The names of the table columns are the names of the columns in the result.</p> <p>The exact text of each CREATE TABLE statement is stored in the <b>sqlite_master</b> table. Everytime the database is opened, all CREATE TABLE statements are read from the <b>sqlite_master</b> table and used to regenerate SQLite's internal representation of the table layout. If the original command was a CREATE TABLE AS then then an equivalent CREATE TABLE statement is synthesized and store in <b>sqlite_master</b> in place of the original command. </p> } Section DELETE delete Syntax {sql-statement} { DELETE FROM <table-name> [WHERE <expression>] } |
︙ | ︙ |