SQLite

**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.522 2009/03/14 08:37:24 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
*/

  for(n=0; *z; n++, z++){
    if( *z=='"' ){ n++; }
  }
  return n + 2;
}

/*
** This function is a wrapper around sqlite3GetToken() used by 
** isValidDimension(). This function differs from sqlite3GetToken() in
** that:
**
**   * Whitespace is ignored, and
**   * The output variable *peToken is set to 0 if the end of the
**     nul-terminated input string is reached.
*/
static int getTokenNoSpace(unsigned char *z, int *peToken){
  int n = 0;
  while( sqlite3Isspace(z[n]) ) n++;
  if( !z[n] ){
    *peToken = 0;
    return 0;
  }
  return n + sqlite3GetToken(&z[n], peToken);
}

/*
** Parameter z points to a nul-terminated string. Return true if, when
** whitespace is ignored, the contents of this string matches one of 
** the following patterns:
**
**     ""
**     "(number)"
**     "(number,number)"
*/
static int isValidDimension(unsigned char *z){
  int eToken;
  int n = 0;
  n += getTokenNoSpace(&z[n], &eToken);
  if( eToken ){
    if( eToken!=TK_LP ) return 0;
    n += getTokenNoSpace(&z[n], &eToken);
    if( eToken==TK_PLUS || eToken==TK_MINUS ){
      n += getTokenNoSpace(&z[n], &eToken);
    }
    if( eToken!=TK_INTEGER && eToken!=TK_FLOAT ) return 0;
    n += getTokenNoSpace(&z[n], &eToken);
    if( eToken==TK_COMMA ){
      n += getTokenNoSpace(&z[n], &eToken);
      if( eToken==TK_PLUS || eToken==TK_MINUS ){
        n += getTokenNoSpace(&z[n], &eToken);
      }
      if( eToken!=TK_INTEGER && eToken!=TK_FLOAT ) return 0;
      n += getTokenNoSpace(&z[n], &eToken);
    }
    if( eToken!=TK_RP ) return 0;
    getTokenNoSpace(&z[n], &eToken);
  }
  if( eToken ) return 0;
  return 1;
}

/*
** The first parameter is a pointer to an output buffer. The second 
** parameter is a pointer to an integer that contains the offset at
** which to write into the output buffer. This function copies the
** nul-terminated string pointed to by the third parameter, zSignedIdent,
** to the specified offset in the buffer and updates *pIdx to refer
** to the first byte after the last byte written before returning.
** 
** If the string zSignedIdent consists entirely of alpha-numeric
** characters, does not begin with a digit and is not an SQL keyword,
** then it is copied to the output buffer exactly as it is. Otherwise,
** it is quoted using double-quotes.
*/
static void identPut(char *z, int *pIdx, char *zSignedIdent, int isTypename){
  unsigned char *zIdent = (unsigned char*)zSignedIdent;
  int i, j, needQuote;
  i = *pIdx;

  for(j=0; zIdent[j]; j++){
    if( !sqlite3Isalnum(zIdent[j]) && zIdent[j]!='_' ) break;
  }
  needQuote = sqlite3Isdigit(zIdent[0]) || sqlite3KeywordCode(zIdent, j)!=TK_ID;
  if( !needQuote ){
    if( isTypename ){
      /* If this is a type-name, allow a little more flexibility. In SQLite,
      ** a type-name is specified as:
      **
      **   ids [ids] [(number [, number])]
      **
      ** where "ids" is either a quoted string or a simple identifier (in the
      ** above notation, [] means optional). It is a bit tricky to check
      ** for all cases, but it is good to avoid unnecessarily quoting common
      ** typenames like VARCHAR(10).
      */
      needQuote = !isValidDimension(&zIdent[j]);
    }else{
      needQuote = zIdent[j];
    }
  }

  if( needQuote ) z[i++] = '"';
  for(j=0; zIdent[j]; j++){
    z[i++] = zIdent[j];
    if( zIdent[j]=='"' ) z[i++] = '"';
  }
  if( needQuote ) z[i++] = '"';
  z[i] = 0;

  char *zSep, *zSep2, *zEnd, *z;
  Column *pCol;
  n = 0;
  for(pCol = p->aCol, i=0; i<p->nCol; i++, pCol++){
    n += identLength(pCol->zName);
    z = pCol->zType;
    if( z ){
      n += identLength(z);
    }
  }
  n += identLength(p->zName);
  if( n<50 ){
    zSep = "";
    zSep2 = ",";
    zEnd = ")";
  }else{
    zSep = "\n  ";
    zSep2 = ",\n  ";
    zEnd = "\n)";
  }
  n += 35 + 6*p->nCol;
  zStmt = sqlite3Malloc( n );
  if( zStmt==0 ){
    db->mallocFailed = 1;
    return 0;
  }
  sqlite3_snprintf(n, zStmt, "CREATE TABLE ");
  k = sqlite3Strlen30(zStmt);
  identPut(zStmt, &k, p->zName, 0);
  zStmt[k++] = '(';
  for(pCol=p->aCol, i=0; i<p->nCol; i++, pCol++){
    sqlite3_snprintf(n-k, &zStmt[k], zSep);
    k += sqlite3Strlen30(&zStmt[k]);
    zSep = zSep2;
    identPut(zStmt, &k, pCol->zName, 0);
    if( (z = pCol->zType)!=0 ){
      zStmt[k++] = ' ';
      assert( (int)(sqlite3Strlen30(z)+k+1)<=n );
      identPut(zStmt, &k, z, 1);

    }
  }
  sqlite3_snprintf(n-k, &zStmt[k], "%s", zEnd);
  return zStmt;
}

/*

# 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.50 2009/03/14 08:37:24 danielk1977 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 {

  }
} {1 {no such table: t5}}
do_test table-8.8 {
  catchsql {
    CREATE TABLE t5 AS SELECT * FROM no_such_table;
  }
} {1 {no such table: no_such_table}}

do_test table-8.9 {
  execsql {
    CREATE TABLE t10("col.1" [char.3]);
    CREATE TABLE t11 AS SELECT * FROM t10;
    SELECT sql FROM sqlite_master WHERE name = 't11';
  }
} {{CREATE TABLE t11("col.1" "char.3")}}
do_test table-8.10 {
  execsql {
    CREATE TABLE t12(
      a INTEGER,
      b VARCHAR(10),
      c VARCHAR(1,10),
      d VARCHAR(+1,-10),
      e VARCHAR (+1,-10),
      f "VARCHAR (+1,-10, 5)",
      g BIG INTEGER
    );
    CREATE TABLE t13 AS SELECT * FROM t12;
    SELECT sql FROM sqlite_master WHERE name = 't13';
  }
} {{CREATE TABLE t13(
  a INTEGER,
  b VARCHAR(10),
  c VARCHAR(1,10),
  d VARCHAR(+1,-10),
  e VARCHAR (+1,-10),
  f "VARCHAR (+1,-10, 5)",
  g "BIG INTEGER"
)}}

# Make sure we cannot have duplicate column names within a table.
#
do_test table-9.1 {
  catchsql {
    CREATE TABLE t6(a,b,a);
  }