SQLite

#

# Test creating and dropping an rtree table.
#
do_test rtree-1.1.1 {
  execsql { CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2) }
} {}
do_test rtree-1.1.2a {
  execsql { SELECT name FROM sqlite_master ORDER BY name }
} {t1 t1_node t1_parent t1_rowid}
do_execsql_test rtree-1.1.2b {
  SELECT name FROM pragma_table_list WHERE type='shadow' ORDER BY name;
} {t1_node t1_parent t1_rowid}
do_test rtree-1.1.3 {
  execsql { 
    DROP TABLE t1; 
    SELECT name FROM sqlite_master ORDER BY name;
  }
} {}

               pCol->notNull ? 1 : 0,
               isHidden>=2 || sqlite3ColumnExpr(pTab,pCol)==0 ? 0 :
                          sqlite3ColumnExpr(pTab,pCol)->u.zToken,
               k,
               isHidden);
      }
    }
  }
  break;

  /*
  **   PRAGMA table_list
  **
  ** Return a single row for each table, virtual table, or view in the
  ** entire schema.
  **
  ** schema:     Name of attached database hold this table
  ** name:       Name of the table itself
  ** type:       "table", "view", "virtual", "shadow"
  ** ncol:       Number of columns
  ** wr:         True for a WITHOUT ROWID table
  ** strict:     True for a STRICT table
  */
  case PragTyp_TABLE_LIST: {
    int ii;
    pParse->nMem = 6;
    sqlite3CodeVerifyNamedSchema(pParse, zDb);
    for(ii=0; ii<db->nDb; ii++){
      HashElem *k;
      Hash *pHash;
      if( zDb && sqlite3_stricmp(zDb, db->aDb[ii].zDbSName)!=0 ) continue;
      pHash = &db->aDb[ii].pSchema->tblHash;
      for(k=sqliteHashFirst(pHash); k; k=sqliteHashNext(k) ){
        Table *pTab = sqliteHashData(k);
        const char *zType;
        if( zRight && sqlite3_stricmp(zRight, pTab->zName)!=0 ) continue;
        if( IsView(pTab) ){
          zType = "view";
        }else if( IsVirtual(pTab) ){
          zType = "virtual";
        }else if( pTab->tabFlags & TF_Shadow ){
          zType = "shadow";
        }else{
          zType = "table";
        }
        sqlite3VdbeMultiLoad(v, 1, "sssiii",
           db->aDb[ii].zDbSName,
           pTab->zName,
           zType,
           pTab->nCol,
           (pTab->tabFlags & TF_WithoutRowid)!=0,
           (pTab->tabFlags & TF_Strict)!=0
        );
      }
    }
  }
  break;

#ifdef SQLITE_DEBUG
  case PragTyp_STATS: {
    Index *pIdx;
    HashElem *i;

#define PragTyp_PAGE_SIZE                     31
#define PragTyp_PRAGMA_LIST                   32
#define PragTyp_SECURE_DELETE                 33
#define PragTyp_SHRINK_MEMORY                 34
#define PragTyp_SOFT_HEAP_LIMIT               35
#define PragTyp_SYNCHRONOUS                   36
#define PragTyp_TABLE_INFO                    37
#define PragTyp_TABLE_LIST                    38
#define PragTyp_TEMP_STORE                    39
#define PragTyp_TEMP_STORE_DIRECTORY          40
#define PragTyp_THREADS                       41
#define PragTyp_WAL_AUTOCHECKPOINT            42
#define PragTyp_WAL_CHECKPOINT                43
#define PragTyp_LOCK_STATUS                   44
#define PragTyp_STATS                         45

/* Property flags associated with various pragma. */
#define PragFlg_NeedSchema 0x01 /* Force schema load before running */
#define PragFlg_NoColumns  0x02 /* OP_ResultRow called with zero columns */
#define PragFlg_NoColumns1 0x04 /* zero columns if RHS argument is present */
#define PragFlg_ReadOnly   0x08 /* Read-only HEADER_VALUE */
#define PragFlg_Result0    0x10 /* Acts as query when no argument */

  /*   9 */ "name",       
  /*  10 */ "type",       
  /*  11 */ "notnull",    
  /*  12 */ "dflt_value", 
  /*  13 */ "pk",         
  /*  14 */ "hidden",     
                           /* table_info reuses 8 */
  /*  15 */ "schema",      /* Used by: table_list */
  /*  16 */ "name",       
  /*  17 */ "type",       
  /*  18 */ "ncol",       
  /*  19 */ "wr",         
  /*  20 */ "strict",     
  /*  21 */ "seqno",       /* Used by: index_xinfo */
  /*  22 */ "cid",        
  /*  23 */ "name",       
  /*  24 */ "desc",       
  /*  25 */ "coll",       
  /*  26 */ "key",        
  /*  27 */ "name",        /* Used by: function_list */
  /*  28 */ "builtin",    
  /*  29 */ "type",       
  /*  30 */ "enc",        
  /*  31 */ "narg",       
  /*  32 */ "flags",      
  /*  33 */ "tbl",         /* Used by: stats */
  /*  34 */ "idx",        
  /*  35 */ "wdth",       
  /*  36 */ "hght",       
  /*  37 */ "flgs",       
  /*  38 */ "seq",         /* Used by: index_list */
  /*  39 */ "name",       
  /*  40 */ "unique",     
  /*  41 */ "origin",     
  /*  42 */ "partial",    
  /*  43 */ "table",       /* Used by: foreign_key_check */
  /*  44 */ "rowid",      
  /*  45 */ "parent",     
  /*  46 */ "fkid",       
                           /* index_info reuses 21 */
  /*  47 */ "seq",         /* Used by: database_list */
  /*  48 */ "name",       
  /*  49 */ "file",       
  /*  50 */ "busy",        /* Used by: wal_checkpoint */
  /*  51 */ "log",        
  /*  52 */ "checkpointed",
                           /* collation_list reuses 38 */
  /*  53 */ "database",    /* Used by: lock_status */
  /*  54 */ "status",     
  /*  55 */ "cache_size",  /* Used by: default_cache_size */
                           /* module_list pragma_list reuses 9 */
  /*  56 */ "timeout",     /* Used by: busy_timeout */
};

/* Definitions of all built-in pragmas */
typedef struct PragmaName {
  const char *const zName; /* Name of pragma */
  u8 ePragTyp;             /* PragTyp_XXX value */
  u8 mPragFlg;             /* Zero or more PragFlg_XXX values */

  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_AutoIndex },
#endif
#endif
 {/* zName:     */ "busy_timeout",
  /* ePragTyp:  */ PragTyp_BUSY_TIMEOUT,
  /* ePragFlg:  */ PragFlg_Result0,
  /* ColNames:  */ 56, 1,
  /* iArg:      */ 0 },
#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
 {/* zName:     */ "cache_size",
  /* ePragTyp:  */ PragTyp_CACHE_SIZE,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },

  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_CkptFullFSync },
#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
 {/* zName:     */ "collation_list",
  /* ePragTyp:  */ PragTyp_COLLATION_LIST,
  /* ePragFlg:  */ PragFlg_Result0,
  /* ColNames:  */ 38, 2,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_COMPILEOPTION_DIAGS)
 {/* zName:     */ "compile_options",
  /* ePragTyp:  */ PragTyp_COMPILE_OPTIONS,
  /* ePragFlg:  */ PragFlg_Result0,
  /* ColNames:  */ 0, 0,

  /* ColNames:  */ 0, 0,
  /* iArg:      */ BTREE_DATA_VERSION },
#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
 {/* zName:     */ "database_list",
  /* ePragTyp:  */ PragTyp_DATABASE_LIST,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0,
  /* ColNames:  */ 47, 3,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && !defined(SQLITE_OMIT_DEPRECATED)
 {/* zName:     */ "default_cache_size",
  /* ePragTyp:  */ PragTyp_DEFAULT_CACHE_SIZE,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1,
  /* ColNames:  */ 55, 1,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
#if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
 {/* zName:     */ "defer_foreign_keys",
  /* ePragTyp:  */ PragTyp_FLAG,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,

  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
 {/* zName:     */ "foreign_key_check",
  /* ePragTyp:  */ PragTyp_FOREIGN_KEY_CHECK,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_Result1|PragFlg_SchemaOpt,
  /* ColNames:  */ 43, 4,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FOREIGN_KEY)
 {/* zName:     */ "foreign_key_list",
  /* ePragTyp:  */ PragTyp_FOREIGN_KEY_LIST,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
  /* ColNames:  */ 0, 8,

  /* iArg:      */ SQLITE_FullFSync },
#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
#if !defined(SQLITE_OMIT_INTROSPECTION_PRAGMAS)
 {/* zName:     */ "function_list",
  /* ePragTyp:  */ PragTyp_FUNCTION_LIST,
  /* ePragFlg:  */ PragFlg_Result0,
  /* ColNames:  */ 27, 6,
  /* iArg:      */ 0 },
#endif
#endif
 {/* zName:     */ "hard_heap_limit",
  /* ePragTyp:  */ PragTyp_HARD_HEAP_LIMIT,
  /* ePragFlg:  */ PragFlg_Result0,
  /* ColNames:  */ 0, 0,

  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
 {/* zName:     */ "index_info",
  /* ePragTyp:  */ PragTyp_INDEX_INFO,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
  /* ColNames:  */ 21, 3,
  /* iArg:      */ 0 },
 {/* zName:     */ "index_list",
  /* ePragTyp:  */ PragTyp_INDEX_LIST,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
  /* ColNames:  */ 38, 5,
  /* iArg:      */ 0 },
 {/* zName:     */ "index_xinfo",
  /* ePragTyp:  */ PragTyp_INDEX_INFO,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
  /* ColNames:  */ 21, 6,
  /* iArg:      */ 1 },
#endif
#if !defined(SQLITE_OMIT_INTEGRITY_CHECK)
 {/* zName:     */ "integrity_check",
  /* ePragTyp:  */ PragTyp_INTEGRITY_CHECK,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_Result1|PragFlg_SchemaOpt,
  /* ColNames:  */ 0, 0,

  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#endif
#if defined(SQLITE_DEBUG) || defined(SQLITE_TEST)
 {/* zName:     */ "lock_status",
  /* ePragTyp:  */ PragTyp_LOCK_STATUS,
  /* ePragFlg:  */ PragFlg_Result0,
  /* ColNames:  */ 53, 2,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
 {/* zName:     */ "locking_mode",
  /* ePragTyp:  */ PragTyp_LOCKING_MODE,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_SchemaReq,
  /* ColNames:  */ 0, 0,

  /* iArg:      */ SQLITE_SqlTrace },
#endif
#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG)
 {/* zName:     */ "stats",
  /* ePragTyp:  */ PragTyp_STATS,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq,
  /* ColNames:  */ 33, 5,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
 {/* zName:     */ "synchronous",
  /* ePragTyp:  */ PragTyp_SYNCHRONOUS,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
 {/* zName:     */ "table_info",
  /* ePragTyp:  */ PragTyp_TABLE_INFO,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
  /* ColNames:  */ 8, 6,
  /* iArg:      */ 0 },
 {/* zName:     */ "table_list",
  /* ePragTyp:  */ PragTyp_TABLE_LIST,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1,
  /* ColNames:  */ 15, 6,
  /* iArg:      */ 1 },
 {/* zName:     */ "table_xinfo",
  /* ePragTyp:  */ PragTyp_TABLE_INFO,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
  /* ColNames:  */ 8, 7,
  /* iArg:      */ 1 },
#endif
#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)

  /* ePragTyp:  */ PragTyp_WAL_AUTOCHECKPOINT,
  /* ePragFlg:  */ 0,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
 {/* zName:     */ "wal_checkpoint",
  /* ePragTyp:  */ PragTyp_WAL_CHECKPOINT,
  /* ePragFlg:  */ PragFlg_NeedSchema,
  /* ColNames:  */ 50, 3,
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
 {/* zName:     */ "writable_schema",
  /* ePragTyp:  */ PragTyp_FLAG,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_WriteSchema|SQLITE_NoSchemaError },
#endif
};
/* Number of pragmas: 68 on by default, 78 total. */

  CREATE TABLE t1(
    a INT,
    b INTEGER,
    c BLOB,
    d TEXT,
    e REAL
  ) STRICT;
  SELECT strict FROM pragma_table_list('t1');
} {1}
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);

do_test view-1.1.110 {
  db config enable_view on
  catchsql {
    SELECT * FROM v1 ORDER BY a;
    SELECT * FROM v1temp ORDER BY a;
  }
} {0 {1 2 4 5 7 8 1 2 4 5 7 8}}
do_execsql_test view-1.1.120 {
  SELECT name, type FROM pragma_table_list('v1');
} {v1 view}
do_test view-1.2 {
  catchsql {
    ROLLBACK;
    SELECT * FROM v1 ORDER BY a;
  }
} {1 {no such table: v1}}
do_test view-1.3 {

} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}

integrity_check without_rowid1-1.0ic

do_execsql_test_if_vtab without_rowid1-1.0ixi {
  SELECT name, key FROM pragma_index_xinfo('t1');
} {c 1 a 1 b 0 d 0}
do_execsql_test_if_vtab without_rowid1-1.0tl {
  SELECT wr FROM pragma_table_list('t1');
} {1}

do_execsql_test without_rowid1-1.1 {
  SELECT *, '|' FROM t1 ORDER BY +c, a;
} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}

do_execsql_test without_rowid1-1.2 {
  SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;

  NAME: table_xinfo
  TYPE: TABLE_INFO
  FLAG: NeedSchema Result1 SchemaOpt
  ARG:  1
  COLS: cid name type notnull dflt_value pk hidden
  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)

  NAME: table_list
  TYPE: TABLE_LIST
  FLAG: NeedSchema Result1 SchemaOpt
  COLS: schema name type ncol wr strict
  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)

  NAME: stats
  FLAG: NeedSchema Result0 SchemaReq
  COLS: tbl idx wdth hght flgs
  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG)

  NAME: index_info
  TYPE: INDEX_INFO