SQLite

** "CREATE TABLE ... AS SELECT ..." statement.  The column names of
** the new table will match the result set of the SELECT.
*/
void sqlite3EndTable(
  Parse *pParse,          /* Parse context */
  Token *pCons,           /* The ',' token after the last column defn. */
  Token *pEnd,            /* The ')' before options in the CREATE TABLE */
  u32 tabOpts,            /* Extra table options. Usually 0. */
  Select *pSelect         /* Select from a "CREATE ... AS SELECT" */
){
  Table *p;                 /* The new table */
  sqlite3 *db = pParse->db; /* The database connection */
  int iDb;                  /* Database in which the table lives */
  Index *pIdx;              /* An implied index of the table */

    if( pSelect ){
      sqlite3ErrorMsg(pParse, "");
      return;
    }
    p->tnum = db->init.newTnum;
    if( p->tnum==1 ) p->tabFlags |= TF_Readonly;
  }

  /* Special processing for tables that include the STRICT keyword:
  **
  **   *  Do not allow custom column datatypes.  Every column must have
  **      a datatype that is one of INT, INTEGER, REAL, TEXT, or BLOB.
  **
  **   *  If a PRIMARY KEY is defined, other than the INTEGER PRIMARY KEY,
  **      then all columns of the PRIMARY KEY must have a NOT NULL
  **      constraint.
  */
  if( tabOpts & TF_Strict ){
    int ii;
    p->tabFlags |= TF_Strict;
    for(ii=0; ii<p->nCol; ii++){
      Column *pCol = &p->aCol[ii];
      if( pCol->eCType==COLTYPE_CUSTOM ){
        sqlite3ErrorMsg(pParse,
          "unknown datatype for %s.%s: \"%s\"",
          p->zName, pCol->zCnName, sqlite3ColumnType(pCol, "")
        );
        return;
      }
      if( (pCol->colFlags & COLFLAG_PRIMKEY)!=0
       && p->iPKey!=ii
       && pCol->notNull == OE_None
      ){
        pCol->notNull = OE_Abort;
        p->tabFlags |= TF_HasNotNull;
      }
    }    
  }

  assert( (p->tabFlags & TF_HasPrimaryKey)==0
       || p->iPKey>=0 || sqlite3PrimaryKeyIndex(p)!=0 );
  assert( (p->tabFlags & TF_HasPrimaryKey)!=0
       || (p->iPKey<0 && sqlite3PrimaryKeyIndex(p)==0) );

  /* Special processing for WITHOUT ROWID Tables */

** Name of the default collating sequence
*/
const char sqlite3StrBINARY[] = "BINARY";

/*
** Standard typenames.  These names must match the COLTYPE_* definitions.
** Adjust the SQLITE_N_STDTYPE value if adding or removing entries.
**
**    sqlite3StdType[]            The actual names of the datatypes.
**
**    sqlite3StdTypeLen[]         The length (in bytes) of each entry
**                                in sqlite3StdType[].
**
**    sqlite3StdTypeAffinity[]    The affinity associated with each entry
**                                in sqlite3StdType[].
**
**    sqlite3StdTypeMap[]         The type value (as returned from
**                                sqlite3_column_type() or sqlite3_value_type())
**                                for each entry in sqlite3StdType[].
*/
const unsigned char sqlite3StdTypeLen[] = { 4, 3, 7, 4, 4 };
const char sqlite3StdTypeAffinity[] = {
  SQLITE_AFF_BLOB,
  SQLITE_AFF_INTEGER,
  SQLITE_AFF_INTEGER,
  SQLITE_AFF_REAL,
  SQLITE_AFF_TEXT
};
const char sqlite3StdTypeMap[] = {
  SQLITE_BLOB,
  SQLITE_INTEGER,
  SQLITE_INTEGER,
  SQLITE_FLOAT,
  SQLITE_TEXT
};
const char *sqlite3StdType[] = {
  "BLOB",
  "INT",
  "INTEGER",
  "REAL",
  "TEXT"

    pIdx->zColAff[n] = 0;
  }
 
  return pIdx->zColAff;
}

/*
** Make changes to the evolving bytecode to do affinity transformations
** of values that are about to be gathered into a row for table pTab.
**
** For ordinary (legacy, non-strict) tables:
** -----------------------------------------
**
** Compute the affinity string for table pTab, if it has not already been
** computed.  As an optimization, omit trailing SQLITE_AFF_BLOB affinities.
**
** If the affinity string is empty (because it was all SQLITE_AFF_BLOB entries
** which were then optimized out) then this routine becomes a no-op.
**
** Otherwise if iReg>0 then code an OP_Affinity opcode that will set the
** affinities for register iReg and following.  Or if iReg==0,
** then just set the P4 operand of the previous opcode (which should  be
** an OP_MakeRecord) to the affinity string.
**
** A column affinity string has one character per column:
**
**    Character      Column affinity
**    ---------      ---------------
**    'A'            BLOB
**    'B'            TEXT
**    'C'            NUMERIC
**    'D'            INTEGER
**    'E'            REAL
**
** For STRICT tables:
** ------------------
**
** Generate an appropropriate OP_TypeCheck opcode that will verify the
** datatypes against the column definitions in pTab.  If iReg==0, that
** means an OP_MakeRecord opcode has already been generated and should be
** the last opcode generated.  The new OP_TypeCheck needs to be inserted
** before the OP_MakeRecord.  The new OP_TypeCheck should use the same
** register set as the OP_MakeRecord.  If iReg>0 then register iReg is
** the first of a series of registers that will form the new record.
** Apply the type checking to that array of registers.
*/
void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){
  int i, j;
  char *zColAff;
  if( pTab->tabFlags & TF_Strict ){
    if( iReg==0 ){
      /* Move the previous opcode (which should be OP_MakeRecord) forward
      ** by one slot and insert a new OP_TypeCheck where the current
      ** OP_MakeRecord is found */
      VdbeOp *pPrev;
      sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
      pPrev = sqlite3VdbeGetOp(v, -1);
      assert( pPrev!=0 );
      assert( pPrev->opcode==OP_MakeRecord || sqlite3VdbeDb(v)->mallocFailed );
      pPrev->opcode = OP_TypeCheck;
      sqlite3VdbeAddOp3(v, OP_MakeRecord, pPrev->p1, pPrev->p2, pPrev->p3);
    }else{
      /* Insert an isolated OP_Typecheck */
      sqlite3VdbeAddOp2(v, OP_TypeCheck, iReg, pTab->nNVCol);
      sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
    }
    return;
  }
  zColAff = pTab->zColAff;
  if( zColAff==0 ){
    sqlite3 *db = sqlite3VdbeDb(v);
    zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1);
    if( !zColAff ){
      sqlite3OomFault(db);
      return;
    }

  }
  assert( zColAff!=0 );
  i = sqlite3Strlen30NN(zColAff);
  if( i ){
    if( iReg ){
      sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);
    }else{
      assert( sqlite3VdbeGetOp(v, -1)->opcode==OP_MakeRecord
              || sqlite3VdbeDb(v)->mallocFailed );
      sqlite3VdbeChangeP4(v, -1, zColAff, i);
    }
  }
}

/*
** Return non-zero if the table pTab in database iDb or any of its indices

    return 0;   /* tab2 may not be a view or virtual table */
  }
  if( pDest->nCol!=pSrc->nCol ){
    return 0;   /* Number of columns must be the same in tab1 and tab2 */
  }
  if( pDest->iPKey!=pSrc->iPKey ){
    return 0;   /* Both tables must have the same INTEGER PRIMARY KEY */
  }
  if( (pDest->tabFlags & TF_Strict)!=0 && (pSrc->tabFlags & TF_Strict)==0 ){
    return 0;   /* Cannot feed from a non-strict into a strict table */
  }
  for(i=0; i<pDest->nCol; i++){
    Column *pDestCol = &pDest->aCol[i];
    Column *pSrcCol = &pSrc->aCol[i];
#ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
    if( (db->mDbFlags & DBFLAG_Vacuum)==0 
     && (pDestCol->colFlags | pSrcCol->colFlags) & COLFLAG_HIDDEN 

ifnotexists(A) ::= .              {A = 0;}
ifnotexists(A) ::= IF NOT EXISTS. {A = 1;}
%type temp {int}
%ifndef SQLITE_OMIT_TEMPDB
temp(A) ::= TEMP.  {A = pParse->db->init.busy==0;}
%endif  SQLITE_OMIT_TEMPDB
temp(A) ::= .      {A = 0;}
create_table_args ::= LP columnlist conslist_opt(X) RP(E) table_option_set(F). {
  sqlite3EndTable(pParse,&X,&E,F,0);
}
create_table_args ::= AS select(S). {
  sqlite3EndTable(pParse,0,0,0,S);
  sqlite3SelectDelete(pParse->db, S);
}
%type table_option_set {u32}
%type table_option {u32}
table_option_set(A) ::= .    {A = 0;}
table_option_set(A) ::= table_option(A).
table_option_set(A) ::= table_option_set(X) COMMA table_option(Y). {A = X|Y;}
table_option(A) ::= WITHOUT nm(X). {
  if( X.n==5 && sqlite3_strnicmp(X.z,"rowid",5)==0 ){
    A = TF_WithoutRowid | TF_NoVisibleRowid;
  }else{
    A = 0;
    sqlite3ErrorMsg(pParse, "unknown table option: %.*s", X.n, X.z);
  }
}
table_option(A) ::= nm(X). {
  if( X.n==6 && sqlite3_strnicmp(X.z,"strict",6)==0 ){
    A = TF_Strict;
  }else{
    A = 0;
    sqlite3ErrorMsg(pParse, "unknown table option: %.*s", X.n, X.z);
  }
}
columnlist ::= columnlist COMMA columnname carglist.
columnlist ::= columnname carglist.
columnname(A) ::= nm(A) typetoken(Y). {sqlite3AddColumn(pParse,A,Y);}

      for(x=sqliteHashFirst(pTbls); x; x=sqliteHashNext(x)){
        Table *pTab = sqliteHashData(x);
        Index *pIdx, *pPk;
        Index *pPrior = 0;
        int loopTop;
        int iDataCur, iIdxCur;
        int r1 = -1;
        int bStrict;

        if( pTab->tnum<1 ) continue;  /* Skip VIEWs or VIRTUAL TABLEs */
        if( pObjTab && pObjTab!=pTab ) continue;
        pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab);
        sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenRead, 0,
                                   1, 0, &iDataCur, &iIdxCur);
        /* reg[7] counts the number of entries in the table.

        assert( sqlite3NoTempsInRange(pParse,1,7+j) );
        sqlite3VdbeAddOp2(v, OP_Rewind, iDataCur, 0); VdbeCoverage(v);
        loopTop = sqlite3VdbeAddOp2(v, OP_AddImm, 7, 1);
        if( !isQuick ){
          /* Sanity check on record header decoding */
          sqlite3VdbeAddOp3(v, OP_Column, iDataCur, pTab->nNVCol-1,3);
          sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG);
          VdbeComment((v, "(right-most column)"));
        }
        /* Verify that all NOT NULL columns really are NOT NULL.  At the
        ** same time verify the type of the content of STRICT tables */
        bStrict = (pTab->tabFlags & TF_Strict)!=0;
        for(j=0; j<pTab->nCol; j++){
          char *zErr;
          Column *pCol = pTab->aCol + j;
          int doError, jmp2;
          if( j==pTab->iPKey ) continue;
          if( pCol->notNull==0 && !bStrict ) continue;
          doError = bStrict ? sqlite3VdbeMakeLabel(pParse) : 0;
          sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, j, 3);
          if( sqlite3VdbeGetOp(v,-1)->opcode==OP_Column ){
            sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG);
          }
          if( pCol->notNull ){
            jmp2 = sqlite3VdbeAddOp1(v, OP_NotNull, 3); VdbeCoverage(v);
            zErr = sqlite3MPrintf(db, "NULL value in %s.%s", pTab->zName,
                                pCol->zCnName);
            sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC);
            if( bStrict ){
              sqlite3VdbeGoto(v, doError);
            }else{
              integrityCheckResultRow(v);
            }
            sqlite3VdbeJumpHere(v, jmp2);
          }
          if( pTab->tabFlags & TF_Strict ){
            jmp2 = sqlite3VdbeAddOp3(v, OP_IsNullOrType, 3, 0, 
                                     sqlite3StdTypeMap[pCol->eCType-1]);
            VdbeCoverage(v);
            zErr = sqlite3MPrintf(db, "non-%s value in %s.%s",
                                  sqlite3StdType[pCol->eCType-1],
                                  pTab->zName, pTab->aCol[j].zCnName);
            sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC);
            sqlite3VdbeResolveLabel(v, doError);
            integrityCheckResultRow(v);
            sqlite3VdbeJumpHere(v, jmp2);
          }
        }
        /* Verify CHECK constraints */
        if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
          ExprList *pCheck = sqlite3ExprListDup(db, pTab->pCheck, 0);
          if( db->mallocFailed==0 ){
            int addrCkFault = sqlite3VdbeMakeLabel(pParse);
            int addrCkOk = sqlite3VdbeMakeLabel(pParse);

#define SQLITE_CONSTRAINT_NOTNULL      (SQLITE_CONSTRAINT | (5<<8))
#define SQLITE_CONSTRAINT_PRIMARYKEY   (SQLITE_CONSTRAINT | (6<<8))
#define SQLITE_CONSTRAINT_TRIGGER      (SQLITE_CONSTRAINT | (7<<8))
#define SQLITE_CONSTRAINT_UNIQUE       (SQLITE_CONSTRAINT | (8<<8))
#define SQLITE_CONSTRAINT_VTAB         (SQLITE_CONSTRAINT | (9<<8))
#define SQLITE_CONSTRAINT_ROWID        (SQLITE_CONSTRAINT |(10<<8))
#define SQLITE_CONSTRAINT_PINNED       (SQLITE_CONSTRAINT |(11<<8))
#define SQLITE_CONSTRAINT_DATATYPE     (SQLITE_CONSTRAINT |(12<<8))
#define SQLITE_NOTICE_RECOVER_WAL      (SQLITE_NOTICE | (1<<8))
#define SQLITE_NOTICE_RECOVER_ROLLBACK (SQLITE_NOTICE | (2<<8))
#define SQLITE_WARNING_AUTOINDEX       (SQLITE_WARNING | (1<<8))
#define SQLITE_AUTH_USER               (SQLITE_AUTH | (1<<8))
#define SQLITE_OK_LOAD_PERMANENTLY     (SQLITE_OK | (1<<8))
#define SQLITE_OK_SYMLINK              (SQLITE_OK | (2<<8))

** is only included if the COLFLAG_HASTYPE bit of colFlags is set and the
** collating sequence name is only included if the COLFLAG_HASCOLL bit is
** set.
*/
struct Column {
  char *zCnName;        /* Name of this column */
  unsigned notNull :4;  /* An OE_ code for handling a NOT NULL constraint */
  unsigned eCType :4;   /* One of the standard types */
  char affinity;        /* One of the SQLITE_AFF_... values */
  u8 szEst;             /* Est size of value in this column. sizeof(INT)==1 */
  u8 hName;             /* Column name hash for faster lookup */
  u16 iDflt;            /* 1-based index of DEFAULT.  0 means "none" */
  u16 colFlags;         /* Boolean properties.  See COLFLAG_ defines below */
};

#define TF_NoVisibleRowid 0x00000200 /* No user-visible "rowid" column */
#define TF_OOOHidden      0x00000400 /* Out-of-Order hidden columns */
#define TF_HasNotNull     0x00000800 /* Contains NOT NULL constraints */
#define TF_Shadow         0x00001000 /* True for a shadow table */
#define TF_HasStat4       0x00002000 /* STAT4 info available for this table */
#define TF_Ephemeral      0x00004000 /* An ephemeral table */
#define TF_Eponymous      0x00008000 /* An eponymous virtual table */
#define TF_Strict         0x00010000 /* STRICT mode */

/*
** Allowed values for Table.eTabType
*/
#define TABTYP_NORM      0     /* Ordinary table */
#define TABTYP_VTAB      1     /* Virtual table */
#define TABTYP_VIEW      2     /* A view */

void sqlite3AddColumn(Parse*,Token,Token);
void sqlite3AddNotNull(Parse*, int);
void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int);
void sqlite3AddCheckConstraint(Parse*, Expr*, const char*, const char*);
void sqlite3AddDefaultValue(Parse*,Expr*,const char*,const char*);
void sqlite3AddCollateType(Parse*, Token*);
void sqlite3AddGenerated(Parse*,Expr*,Token*);
void sqlite3EndTable(Parse*,Token*,Token*,u32,Select*);
void sqlite3AddReturning(Parse*,ExprList*);
int sqlite3ParseUri(const char*,const char*,unsigned int*,
                    sqlite3_vfs**,char**,char **);
#define sqlite3CodecQueryParameters(A,B,C) 0
Btree *sqlite3DbNameToBtree(sqlite3*,const char*);

#ifdef SQLITE_UNTESTABLE

int sqlite3ValueFromExpr(sqlite3 *, Expr *, u8, u8, sqlite3_value **);
void sqlite3ValueApplyAffinity(sqlite3_value *, u8, u8);
#ifndef SQLITE_AMALGAMATION
extern const unsigned char sqlite3OpcodeProperty[];
extern const char sqlite3StrBINARY[];
extern const unsigned char sqlite3StdTypeLen[];
extern const char sqlite3StdTypeAffinity[];
extern const char sqlite3StdTypeMap[];
extern const char *sqlite3StdType[];
extern const unsigned char sqlite3UpperToLower[];
extern const unsigned char *sqlite3aLTb;
extern const unsigned char *sqlite3aEQb;
extern const unsigned char *sqlite3aGTb;
extern const unsigned char sqlite3CtypeMap[];
extern SQLITE_WSD struct Sqlite3Config sqlite3Config;

    return out2PrereleaseWithClear(pOut);
  }else{
    pOut->flags = MEM_Int;
    return pOut;
  }
}

/*
** Return the symbolic name for the data type of a pMem
*/
static const char *vdbeMemTypeName(Mem *pMem){
  static const char *azTypes[] = {
      /* SQLITE_INTEGER */ "INT",
      /* SQLITE_FLOAT   */ "REAL",
      /* SQLITE_TEXT    */ "TEXT",
      /* SQLITE_BLOB    */ "BLOB",
      /* SQLITE_NULL    */ "NULL"
  };
  return azTypes[sqlite3_value_type(pMem)-1];
}

/*
** Execute as much of a VDBE program as we can.
** This is the core of sqlite3_step().  
*/
int sqlite3VdbeExec(
  Vdbe *p                    /* The VDBE */

  pIn1 = &aMem[pOp->p1];
  VdbeBranchTaken( (pIn1->flags & MEM_Null)!=0, 2);
  if( (pIn1->flags & MEM_Null)!=0 ){
    goto jump_to_p2;
  }
  break;
}

/* Opcode: IsNullOrType P1 P2 P3 * *
** Synopsis: if typeof(r[P1]) IN (P3,5) goto P2
**
** Jump to P2 if the value in register P1 is NULL or has a datatype P3.
** P3 is an integer which should be one of SQLITE_INTEGER, SQLITE_FLOAT,
** SQLITE_BLOB, SQLITE_NULL, or SQLITE_TEXT.
*/
case OP_IsNullOrType: {      /* jump, in1 */
  int doTheJump;
  pIn1 = &aMem[pOp->p1];
  doTheJump = (pIn1->flags & MEM_Null)!=0 || sqlite3_value_type(pIn1)==pOp->p3;
  VdbeBranchTaken( doTheJump, 2);
  if( doTheJump ) goto jump_to_p2;
  break;
}

/* Opcode: ZeroOrNull P1 P2 P3 * *
** Synopsis: r[P2] = 0 OR NULL
**
** If all both registers P1 and P3 are NOT NULL, then store a zero in
** register P2.  If either registers P1 or P3 are NULL then put
** a NULL in register P2.

    pOp = &aOp[aOp[0].p3-1];
    break;
  }else{
    rc = SQLITE_CORRUPT_BKPT;
    goto abort_due_to_error;
  }
}

/* Opcode: TypeCheck P1 P2 * P4 *
** Synopsis: typecheck(r[P1@P2])
**
** Apply affinities to the range of P2 registers beginning with P1.
** Take the affinities from the Table object in P4.  If any value
** cannot be coerced into the correct type, then raise an error.
**
** This opcode is similar to OP_Affinity except that this opcode
** forces the register type to the Table column type.  This is used
** to implement "strict affinity".
**
** Preconditions:
**
** <ul>
** <li> P2 should be the number of non-virtual columns in the
**      table of P4.
** <li> Table P4 should be a STRICT table.
** </ul>
**
** If any precondition is false, an assertion fault occurs.
*/
case OP_TypeCheck: {
  Table *pTab;
  Column *aCol;
  int i;

  assert( pOp->p4type==P4_TABLE );
  pTab = pOp->p4.pTab;
  assert( pTab->tabFlags & TF_Strict );
  assert( pTab->nNVCol==pOp->p2 );
  aCol = pTab->aCol;
  pIn1 = &aMem[pOp->p1];
  for(i=0; i<pTab->nCol; i++){
    if( aCol[i].colFlags & COLFLAG_VIRTUAL ) continue;
    assert( pIn1 < &aMem[pOp->p1+pOp->p2] );
    applyAffinity(pIn1, aCol[i].affinity, encoding);
    if( (pIn1->flags & MEM_Null)==0 ){
      switch( aCol[i].eCType ){
        case COLTYPE_BLOB: {
          if( (pIn1->flags & MEM_Blob)==0 ) goto vdbe_type_error;
          break;
        }
        case COLTYPE_INTEGER:
        case COLTYPE_INT: {
          if( (pIn1->flags & MEM_Int)==0 ) goto vdbe_type_error;
          break;
        }
        case COLTYPE_TEXT: {
          if( (pIn1->flags & MEM_Str)==0 ) goto vdbe_type_error;
          break;
        }
        default: {
          assert( aCol[i].eCType==COLTYPE_REAL );
          if( pIn1->flags & MEM_Int ){
            /* When applying REAL affinity, if the result is still an MEM_Int
            ** that will fit in 6 bytes, then change the type to MEM_IntReal
            ** so that we keep the high-resolution integer value but know that
            ** the type really wants to be REAL. */
            testcase( pIn1->u.i==140737488355328LL );
            testcase( pIn1->u.i==140737488355327LL );
            testcase( pIn1->u.i==-140737488355328LL );
            testcase( pIn1->u.i==-140737488355329LL );
            if( pIn1->u.i<=140737488355327LL && pIn1->u.i>=-140737488355328LL){
              pIn1->flags |= MEM_IntReal;
              pIn1->flags &= ~MEM_Int;
            }else{
              pIn1->u.r = (double)pIn1->u.i;
              pIn1->flags |= MEM_Real;
              pIn1->flags &= ~MEM_Int;
            }
          }else if( (pIn1->flags & MEM_Real)==0 ){
            goto vdbe_type_error;
          }
          break;
        }
      }
    }
    REGISTER_TRACE((int)(pIn1-aMem), pIn1);
    pIn1++;
  }
  assert( pIn1 == &aMem[pOp->p1+pOp->p2] );
  break;

vdbe_type_error:
  sqlite3VdbeError(p, "cannot store %s value in %s column %s.%s",
     vdbeMemTypeName(pIn1), sqlite3StdType[aCol[i].eCType-1],
     pTab->zName, aCol[i].zCnName);
  rc = SQLITE_CONSTRAINT_DATATYPE;
  goto abort_due_to_error;
}

/* Opcode: Affinity P1 P2 * P4 *
** Synopsis: affinity(r[P1@P2])
**
** Apply affinities to a range of P2 registers starting with P1.
**
** P4 is a string that is P2 characters long. The N-th character of the

# 2021-08-18
#
# 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 STRICT tables.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix strict1

# STRICT tables have on a limited number of allowed datatypes.
#
do_catchsql_test strict1-1.1 {
  CREATE TABLE t1(a) STRICT;
} {1 {unknown datatype for t1.a: ""}}
do_catchsql_test strict1-1.2 {
  CREATE TABLE t1(a PRIMARY KEY) STRICT, WITHOUT ROWID;
} {1 {unknown datatype for t1.a: ""}}
do_catchsql_test strict1-1.3 {
  CREATE TABLE t1(a PRIMARY KEY) WITHOUT ROWID, STRICT;
} {1 {unknown datatype for t1.a: ""}}
do_catchsql_test strict1-1.4 {
  CREATE TABLE t1(a BANJO PRIMARY KEY) WITHOUT ROWID, STRICT;
} {1 {unknown datatype for t1.a: "BANJO"}}
do_catchsql_test strict1-1.5 {
  CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f DATE) strict;
} {1 {unknown datatype for t1.f: "DATE"}}
do_catchsql_test strict1-1.6 {
  CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f TEXT(50)) WITHOUT ROWID, STRICT;
} {1 {unknown datatype for t1.f: "TEXT(50)"}}

do_execsql_test strict1-2.0 {
  CREATE TABLE t1(
    a INT,
    b INTEGER,
    c BLOB,
    d TEXT,
    e REAL
  ) STRICT;
} {}
do_catchsql_test strict1-2.1 {
  INSERT INTO t1(a) VALUES('xyz');
} {1 {cannot store TEXT value in INT column t1.a}}
do_catchsql_test strict1-2.2 {
  INSERT INTO t1(b) VALUES('xyz');
} {1 {cannot store TEXT value in INTEGER column t1.b}}
do_catchsql_test strict1-2.3 {
  INSERT INTO t1(c) VALUES('xyz');
} {1 {cannot store TEXT value in BLOB column t1.c}}
do_catchsql_test strict1-2.4 {
  INSERT INTO t1(d) VALUES(x'3142536475');
} {1 {cannot store BLOB value in TEXT column t1.d}}
do_catchsql_test strict1-2.5 {
  INSERT INTO t1(e) VALUES('xyz');
} {1 {cannot store TEXT value in REAL column t1.e}}

do_execsql_test strict1-3.1 {
  INSERT INTO t1(a, b) VALUES(1,2),('3','4'),(5.0, 6.0),(null,null);
  SELECT a, b, '|' FROM t1;
} {1 2 | 3 4 | 5 6 | {} {} |}
do_catchsql_test strict1-3.2 {
  INSERT INTO t1(a) VALUES(1.2);
} {1 {cannot store REAL value in INT column t1.a}}
do_catchsql_test strict1-3.3 {
  INSERT INTO t1(a) VALUES(x'313233');
} {1 {cannot store BLOB value in INT column t1.a}}
do_catchsql_test strict1-3.4 {
  INSERT INTO t1(b) VALUES(1.2);
} {1 {cannot store REAL value in INTEGER column t1.b}}
do_catchsql_test strict1-3.5 {
  INSERT INTO t1(b) VALUES(x'313233');
} {1 {cannot store BLOB value in INTEGER column t1.b}}

do_execsql_test strict1-4.1 {
  DELETE FROM t1;
  INSERT INTO t1(c) VALUES(x'313233'), (NULL);
  SELECT typeof(c), c FROM t1;
} {blob 123 null {}}
do_catchsql_test strict1-4.2 {
  INSERT INTO t1(c) VALUES('456');
} {1 {cannot store TEXT value in BLOB column t1.c}}

do_execsql_test strict1-5.1 {
  DELETE FROM t1;
  INSERT INTO t1(d) VALUES('xyz'),(4),(5.5),(NULL);
  SELECT typeof(d), d FROM t1;
} {text xyz text 4 text 5.5 null {}}
do_catchsql_test strict1-5.2 {
  INSERT INTO t1(d) VALUES(x'4567');
} {1 {cannot store BLOB value in TEXT column t1.d}}

do_execsql_test strict1-6.1 {
  DELETE FROM t1;
  INSERT INTO t1(e) VALUES(1),(2.5),('3'),('4.5'),(6.0),(NULL);
  SELECT typeof(e), e FROM t1;
} {real 1.0 real 2.5 real 3.0 real 4.5 real 6.0 null {}}
do_catchsql_test strict1-6.2 {
  INSERT INTO t1(e) VALUES('xyz');
} {1 {cannot store TEXT value in REAL column t1.e}}
do_catchsql_test strict1-6.3 {
  INSERT INTO t1(e) VALUES(x'3456');
} {1 {cannot store BLOB value in REAL column t1.e}}

finish_test

# 2021-08-19
#
# 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 STRICT tables.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix strict2

# PRAGMA integrity_check on a STRICT table should verify that
# all of the values are of the correct type.
#
do_execsql_test strict2-1.1 {
  CREATE TABLE t1(
    a INT,
    b INTEGER,
    c TEXT,
    d REAL,
    e BLOB
  ) STRICT;
  CREATE TABLE t1nn(
    a INT NOT NULL,
    b INTEGER NOT NULL,
    c TEXT NOT NULL,
    d REAL NOT NULL,
    e BLOB NOT NULL
  ) STRICT;
  CREATE TABLE t2(a,b,c,d,e);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,1,'one',1.0,x'b1'),(2,2,'two',2.25,x'b2b2b2');
  PRAGMA writable_schema=on;
  UPDATE sqlite_schema SET rootpage=(SELECT rootpage FROM sqlite_schema WHERE name='t1');
} {}
db close
sqlite3 db test.db
do_execsql_test strict2-1.2 {
  PRAGMA quick_check('t1');
} {ok}
do_execsql_test strict2-1.3 {
  UPDATE t2 SET a=2.5 WHERE b=2;
  PRAGMA quick_check('t1');
} {{non-INT value in t1.a}}
do_execsql_test strict2-1.4 {
  UPDATE t2 SET a='xyz' WHERE b=2;
  PRAGMA quick_check('t1');
} {{non-INT value in t1.a}}
do_execsql_test strict2-1.5 {
  UPDATE t2 SET a=x'445566' WHERE b=2;
  PRAGMA quick_check('t1');
} {{non-INT value in t1.a}}
do_execsql_test strict2-1.6 {
  UPDATE t2 SET a=2.5 WHERE b=2;
  PRAGMA quick_check('t1nn');
} {{non-INT value in t1nn.a}}
do_execsql_test strict2-1.7 {
  UPDATE t2 SET a='xyz' WHERE b=2;
  PRAGMA quick_check('t1nn');
} {{non-INT value in t1nn.a}}
do_execsql_test strict2-1.8 {
  UPDATE t2 SET a=x'445566' WHERE b=2;
  PRAGMA quick_check('t1nn');
} {{non-INT value in t1nn.a}}

do_execsql_test strict2-1.13 {
  UPDATE t2 SET a=2 WHERE b=2;
  UPDATE t2 SET b=2.5 WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-INTEGER value in t1.b}}
do_execsql_test strict2-1.14 {
  UPDATE t2 SET b='two' WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-INTEGER value in t1.b}}
do_execsql_test strict2-1.15 {
  UPDATE t2 SET b=x'b0b1b2b3b4' WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-INTEGER value in t1.b}}
do_execsql_test strict2-1.16 {
  UPDATE t2 SET b=NULL WHERE a=2;
  PRAGMA quick_check('t1');
} {ok}
do_execsql_test strict2-1.17 {
  UPDATE t2 SET b=2.5 WHERE a=2;
  PRAGMA quick_check('t1nn');
} {{non-INTEGER value in t1nn.b}}
do_execsql_test strict2-1.18 {
  UPDATE t2 SET b=NULL WHERE a=2;
  PRAGMA quick_check('t1nn');
} {{NULL value in t1nn.b}}

do_execsql_test strict2-1.23 {
  UPDATE t2 SET b=2 WHERE a=2;
  UPDATE t2 SET c=9 WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-TEXT value in t1.c}}
do_execsql_test strict2-1.24 {
  UPDATE t2 SET c=9.5 WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-TEXT value in t1.c}}
do_execsql_test strict2-1.25 {
  UPDATE t2 SET c=x'b0b1b2b3b4' WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-TEXT value in t1.c}}

do_execsql_test strict2-1.33 {
  UPDATE t2 SET c='two' WHERE a=2;
  UPDATE t2 SET d=9 WHERE a=2;
  PRAGMA quick_check('t1');
} {ok}
do_execsql_test strict2-1.34 {
  UPDATE t2 SET d='nine' WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-REAL value in t1.d}}
do_execsql_test strict2-1.35 {
  UPDATE t2 SET d=x'b0b1b2b3b4' WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-REAL value in t1.d}}

do_execsql_test strict2-1.43 {
  UPDATE t2 SET d=2.5 WHERE a=2;
  UPDATE t2 SET e=9 WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-BLOB value in t1.e}}
do_execsql_test strict2-1.44 {
  UPDATE t2 SET e=9.5 WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-BLOB value in t1.e}}
do_execsql_test strict2-1.45 {
  UPDATE t2 SET e='hello' WHERE a=2;
  PRAGMA quick_check('t1');
} {{non-BLOB value in t1.e}}



finish_test