/ Check-in [24ef3844]
Login
Home Timeline Branches Tags Tickets Wiki More...
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Change the name of the parameter table in the CLI to "sqlite_parameters". Doing so involves adding SQLITE_DBCONFIG_WRITABLE_SCHEMA and allowing the creation of tables whose names begin with "sqlite_" and that are indexed while writable_schema is on.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 24ef3844692debc2db7b1d427f5dcf4050481a8c7f71c3b516766b819ecab587
User & Date: drh 2019-03-25 21:35:41
Context
2019-03-25
21:55
Remove a test from the index build logic which is no longer needed and is in fact unreachable. check-in: 9b20ee10 user: drh tags: trunk
21:35
Change the name of the parameter table in the CLI to "sqlite_parameters". Doing so involves adding SQLITE_DBCONFIG_WRITABLE_SCHEMA and allowing the creation of tables whose names begin with "sqlite_" and that are indexed while writable_schema is on. check-in: 24ef3844 user: drh tags: trunk
19:50
Earlier detection of corruption while added to the freelist. check-in: 8c80b004 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c. 

3150
3151
3152
3153
3154
3155
3156

3157
3158
3159
3160
3161
3162
3163

  }
  pDb = &db->aDb[iDb];

  assert( pTab!=0 );
  assert( pParse->nErr==0 );
  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 
       && db->init.busy==0

#if SQLITE_USER_AUTHENTICATION
       && sqlite3UserAuthTable(pTab->zName)==0
#endif
#ifdef SQLITE_ALLOW_SQLITE_MASTER_INDEX
       && sqlite3StrICmp(&pTab->zName[7],"master")!=0
#endif
       && sqlite3StrNICmp(&pTab->zName[7],"altertab_",9)!=0








>








3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
3161
3162
3163
3164

  }
  pDb = &db->aDb[iDb];

  assert( pTab!=0 );
  assert( pParse->nErr==0 );
  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 
       && db->init.busy==0
       && pTblName!=0
#if SQLITE_USER_AUTHENTICATION
       && sqlite3UserAuthTable(pTab->zName)==0
#endif
#ifdef SQLITE_ALLOW_SQLITE_MASTER_INDEX
       && sqlite3StrICmp(&pTab->zName[7],"master")!=0
#endif
       && sqlite3StrNICmp(&pTab->zName[7],"altertab_",9)!=0

Changes to src/main.c. 

839
840
841
842
843
844
845


846
847
848
849
850
851
852

        { SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, SQLITE_Fts3Tokenizer  },
        { SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, SQLITE_LoadExtension  },
        { SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE,      SQLITE_NoCkptOnClose  },
        { SQLITE_DBCONFIG_ENABLE_QPSG,           SQLITE_EnableQPSG     },
        { SQLITE_DBCONFIG_TRIGGER_EQP,           SQLITE_TriggerEQP     },
        { SQLITE_DBCONFIG_RESET_DATABASE,        SQLITE_ResetDatabase  },
        { SQLITE_DBCONFIG_DEFENSIVE,             SQLITE_Defensive      },


      };
      unsigned int i;
      rc = SQLITE_ERROR; /* IMP: R-42790-23372 */
      for(i=0; i<ArraySize(aFlagOp); i++){
        if( aFlagOp[i].op==op ){
          int onoff = va_arg(ap, int);
          int *pRes = va_arg(ap, int*);








>
>








839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854

        { SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, SQLITE_Fts3Tokenizer  },
        { SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, SQLITE_LoadExtension  },
        { SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE,      SQLITE_NoCkptOnClose  },
        { SQLITE_DBCONFIG_ENABLE_QPSG,           SQLITE_EnableQPSG     },
        { SQLITE_DBCONFIG_TRIGGER_EQP,           SQLITE_TriggerEQP     },
        { SQLITE_DBCONFIG_RESET_DATABASE,        SQLITE_ResetDatabase  },
        { SQLITE_DBCONFIG_DEFENSIVE,             SQLITE_Defensive      },
        { SQLITE_DBCONFIG_WRITABLE_SCHEMA,       SQLITE_WriteSchema|
                                                 SQLITE_NoSchemaError  },
      };
      unsigned int i;
      rc = SQLITE_ERROR; /* IMP: R-42790-23372 */
      for(i=0; i<ArraySize(aFlagOp); i++){
        if( aFlagOp[i].op==op ){
          int onoff = va_arg(ap, int);
          int *pRes = va_arg(ap, int*);

Changes to src/shell.c.in. 

2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754



2755
2756
2757
2758
2759
2760

2761
2762
2763
2764
2765
2766
2767
....
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
....
7138
7139
7140
7141
7142
7143
7144



7145
7146

7147
7148
7149
7150
7151
7152
7153
7154
7155
7156
7157
7158
7159
7160
7161
7162
7163
7164
7165
7166
7167
7168
7169
7170
7171
7172
7173
7174
7175
....
7192
7193
7194
7195
7196
7197
7198
7199
7200
7201
7202
7203
7204
7205
7206
7207
7208
7209
7210
7211
7212
7213
7214
7215
7216
....
7224
7225
7226
7227
7228
7229
7230
7231
7232
7233
7234
7235
7236
7237
7238

#endif
#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
  sqlite3WhereTrace = savedWhereTrace;
#endif
}

/* Name of the TEMP table that holds bind parameter values */
#define BIND_PARAM_TABLE "$Parameters"

/* Create the TEMP table used to store parameter bindings */
static void bind_table_init(ShellState *p){



  sqlite3_exec(p->db,
    "CREATE TABLE IF NOT EXISTS temp.[" BIND_PARAM_TABLE "](\n"
    "  key TEXT PRIMARY KEY,\n"
    "  value ANY\n"
    ") WITHOUT ROWID;",
    0, 0, 0);

}

/*
** Bind parameters on a prepared statement.
**
** Parameter bindings are taken from a TEMP table of the form:
**
................................................................................
  nVar = sqlite3_bind_parameter_count(pStmt);
  if( nVar==0 ) return;  /* Nothing to do */
  if( sqlite3_table_column_metadata(pArg->db, "TEMP", BIND_PARAM_TABLE,
                                    "key", 0, 0, 0, 0, 0)!=SQLITE_OK ){
    return; /* Parameter table does not exist */
  }
  rc = sqlite3_prepare_v2(pArg->db,
          "SELECT value FROM temp.\"" BIND_PARAM_TABLE "\""
          " WHERE key=?1", -1, &pQ, 0);
  if( rc || pQ==0 ) return;
  for(i=1; i<=nVar; i++){
    char zNum[30];
    const char *zVar = sqlite3_bind_parameter_name(pStmt, i);
    if( zVar==0 ){
      sqlite3_snprintf(sizeof(zNum),zNum,"?%d",i);
................................................................................
    open_db(p,0);
    if( nArg<=1 ) goto parameter_syntax_error;

    /* .parameter clear
    ** Clear all bind parameters by dropping the TEMP table that holds them.
    */
    if( nArg==2 && strcmp(azArg[1],"clear")==0 ){



      sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp.[" BIND_PARAM_TABLE "];",
                   0, 0, 0);

    }else

    /* .parameter list
    ** List all bind parameters.
    */
    if( nArg==2 && strcmp(azArg[1],"list")==0 ){
      sqlite3_stmt *pStmt = 0;
      int rx;
      int len = 0;
      rx = sqlite3_prepare_v2(p->db,
             "SELECT max(length(key)) "
             "FROM temp.[" BIND_PARAM_TABLE "];", -1, &pStmt, 0);
      if( rx==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
        len = sqlite3_column_int(pStmt, 0);
        if( len>40 ) len = 40;
      }
      sqlite3_finalize(pStmt);
      pStmt = 0;
      if( len ){
        rx = sqlite3_prepare_v2(p->db,
             "SELECT key, quote(value) "
             "FROM temp.[" BIND_PARAM_TABLE "];", -1, &pStmt, 0);
        while( sqlite3_step(pStmt)==SQLITE_ROW ){
          utf8_printf(p->out, "%-*s %s\n", len, sqlite3_column_text(pStmt,0),
                      sqlite3_column_text(pStmt,1));
        }
        sqlite3_finalize(pStmt);
      }
    }else
................................................................................
      int rx;
      char *zSql;
      sqlite3_stmt *pStmt;
      const char *zKey = azArg[2];
      const char *zValue = azArg[3];
      bind_table_init(p);
      zSql = sqlite3_mprintf(
                  "REPLACE INTO temp.[" BIND_PARAM_TABLE "](key,value)"
                  "VALUES(%Q,%s);", zKey, zValue);
      if( zSql==0 ) shell_out_of_memory();
      pStmt = 0;
      rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
      sqlite3_free(zSql);
      if( rx!=SQLITE_OK ){
        sqlite3_finalize(pStmt);
        pStmt = 0;
        zSql = sqlite3_mprintf(
                   "REPLACE INTO temp.[" BIND_PARAM_TABLE "](key,value)"
                   "VALUES(%Q,%Q);", zKey, zValue);
        if( zSql==0 ) shell_out_of_memory();
        rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
        sqlite3_free(zSql);
        if( rx!=SQLITE_OK ){
          utf8_printf(p->out, "Error: %s\n", sqlite3_errmsg(p->db));
          sqlite3_finalize(pStmt);
................................................................................

    /* .parameter unset NAME
    ** Remove the NAME binding from the parameter binding table, if it
    ** exists.
    */
    if( nArg==3 && strcmp(azArg[1],"unset")==0 ){
      char *zSql = sqlite3_mprintf(
          "DELETE FROM temp.[" BIND_PARAM_TABLE "] WHERE key=%Q", azArg[2]);
      if( zSql==0 ) shell_out_of_memory();
      sqlite3_exec(p->db, zSql, 0, 0, 0);
      sqlite3_free(zSql);
    }else
    /* If no command name matches, show a syntax error */
    parameter_syntax_error:
    showHelp(p->out, "parameter");








|



>
>
>

|




>







 







|







 







>
>
>
|

>











|









|







 







|









|







 







|








2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
....
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
....
7142
7143
7144
7145
7146
7147
7148
7149
7150
7151
7152
7153
7154
7155
7156
7157
7158
7159
7160
7161
7162
7163
7164
7165
7166
7167
7168
7169
7170
7171
7172
7173
7174
7175
7176
7177
7178
7179
7180
7181
7182
7183
....
7200
7201
7202
7203
7204
7205
7206
7207
7208
7209
7210
7211
7212
7213
7214
7215
7216
7217
7218
7219
7220
7221
7222
7223
7224
....
7232
7233
7234
7235
7236
7237
7238
7239
7240
7241
7242
7243
7244
7245
7246

#endif
#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
  sqlite3WhereTrace = savedWhereTrace;
#endif
}

/* Name of the TEMP table that holds bind parameter values */
#define BIND_PARAM_TABLE "sqlite_parameters"

/* Create the TEMP table used to store parameter bindings */
static void bind_table_init(ShellState *p){
  int wrSchema = 0;
  sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, -1, &wrSchema);
  sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, 1, 0);
  sqlite3_exec(p->db,
    "CREATE TABLE IF NOT EXISTS temp." BIND_PARAM_TABLE "(\n"
    "  key TEXT PRIMARY KEY,\n"
    "  value ANY\n"
    ") WITHOUT ROWID;",
    0, 0, 0);
  sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, wrSchema, 0);
}

/*
** Bind parameters on a prepared statement.
**
** Parameter bindings are taken from a TEMP table of the form:
**
................................................................................
  nVar = sqlite3_bind_parameter_count(pStmt);
  if( nVar==0 ) return;  /* Nothing to do */
  if( sqlite3_table_column_metadata(pArg->db, "TEMP", BIND_PARAM_TABLE,
                                    "key", 0, 0, 0, 0, 0)!=SQLITE_OK ){
    return; /* Parameter table does not exist */
  }
  rc = sqlite3_prepare_v2(pArg->db,
          "SELECT value FROM temp." BIND_PARAM_TABLE
          " WHERE key=?1", -1, &pQ, 0);
  if( rc || pQ==0 ) return;
  for(i=1; i<=nVar; i++){
    char zNum[30];
    const char *zVar = sqlite3_bind_parameter_name(pStmt, i);
    if( zVar==0 ){
      sqlite3_snprintf(sizeof(zNum),zNum,"?%d",i);
................................................................................
    open_db(p,0);
    if( nArg<=1 ) goto parameter_syntax_error;

    /* .parameter clear
    ** Clear all bind parameters by dropping the TEMP table that holds them.
    */
    if( nArg==2 && strcmp(azArg[1],"clear")==0 ){
      int wrSchema = 0;
      sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, -1, &wrSchema);
      sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, 1, 0);
      sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp." BIND_PARAM_TABLE ";",
                   0, 0, 0);
      sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, wrSchema, 0);
    }else

    /* .parameter list
    ** List all bind parameters.
    */
    if( nArg==2 && strcmp(azArg[1],"list")==0 ){
      sqlite3_stmt *pStmt = 0;
      int rx;
      int len = 0;
      rx = sqlite3_prepare_v2(p->db,
             "SELECT max(length(key)) "
             "FROM temp." BIND_PARAM_TABLE ";", -1, &pStmt, 0);
      if( rx==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
        len = sqlite3_column_int(pStmt, 0);
        if( len>40 ) len = 40;
      }
      sqlite3_finalize(pStmt);
      pStmt = 0;
      if( len ){
        rx = sqlite3_prepare_v2(p->db,
             "SELECT key, quote(value) "
             "FROM temp." BIND_PARAM_TABLE ";", -1, &pStmt, 0);
        while( sqlite3_step(pStmt)==SQLITE_ROW ){
          utf8_printf(p->out, "%-*s %s\n", len, sqlite3_column_text(pStmt,0),
                      sqlite3_column_text(pStmt,1));
        }
        sqlite3_finalize(pStmt);
      }
    }else
................................................................................
      int rx;
      char *zSql;
      sqlite3_stmt *pStmt;
      const char *zKey = azArg[2];
      const char *zValue = azArg[3];
      bind_table_init(p);
      zSql = sqlite3_mprintf(
                  "REPLACE INTO temp." BIND_PARAM_TABLE "(key,value)"
                  "VALUES(%Q,%s);", zKey, zValue);
      if( zSql==0 ) shell_out_of_memory();
      pStmt = 0;
      rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
      sqlite3_free(zSql);
      if( rx!=SQLITE_OK ){
        sqlite3_finalize(pStmt);
        pStmt = 0;
        zSql = sqlite3_mprintf(
                   "REPLACE INTO temp." BIND_PARAM_TABLE "(key,value)"
                   "VALUES(%Q,%Q);", zKey, zValue);
        if( zSql==0 ) shell_out_of_memory();
        rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
        sqlite3_free(zSql);
        if( rx!=SQLITE_OK ){
          utf8_printf(p->out, "Error: %s\n", sqlite3_errmsg(p->db));
          sqlite3_finalize(pStmt);
................................................................................

    /* .parameter unset NAME
    ** Remove the NAME binding from the parameter binding table, if it
    ** exists.
    */
    if( nArg==3 && strcmp(azArg[1],"unset")==0 ){
      char *zSql = sqlite3_mprintf(
          "DELETE FROM temp." BIND_PARAM_TABLE " WHERE key=%Q", azArg[2]);
      if( zSql==0 ) shell_out_of_memory();
      sqlite3_exec(p->db, zSql, 0, 0, 0);
      sqlite3_free(zSql);
    }else
    /* If no command name matches, show a syntax error */
    parameter_syntax_error:
    showHelp(p->out, "parameter");

Changes to src/sqlite.h.in. 

2195
2196
2197
2198
2199
2200
2201











2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214

2215
2216
2217
2218
2219
2220
2221
2222

** features include but are not limited to the following:
** <ul>
** <li> The [PRAGMA writable_schema=ON] statement.
** <li> Writes to the [sqlite_dbpage] virtual table.
** <li> Direct writes to [shadow tables].
** </ul>
** </dd>











** </dl>
*/
#define SQLITE_DBCONFIG_MAINDBNAME            1000 /* const char* */
#define SQLITE_DBCONFIG_LOOKASIDE             1001 /* void* int int */
#define SQLITE_DBCONFIG_ENABLE_FKEY           1002 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_TRIGGER        1003 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER 1004 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION 1005 /* int int* */
#define SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE      1006 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_QPSG           1007 /* int int* */
#define SQLITE_DBCONFIG_TRIGGER_EQP           1008 /* int int* */
#define SQLITE_DBCONFIG_RESET_DATABASE        1009 /* int int* */
#define SQLITE_DBCONFIG_DEFENSIVE             1010 /* int int* */

#define SQLITE_DBCONFIG_MAX                   1010 /* Largest DBCONFIG */

/*
** CAPI3REF: Enable Or Disable Extended Result Codes
** METHOD: sqlite3
**
** ^The sqlite3_extended_result_codes() routine enables or disables the
** [extended result codes] feature of SQLite. ^The extended result








>
>
>
>
>
>
>
>
>
>
>













>
|








2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234

** features include but are not limited to the following:
** <ul>
** <li> The [PRAGMA writable_schema=ON] statement.
** <li> Writes to the [sqlite_dbpage] virtual table.
** <li> Direct writes to [shadow tables].
** </ul>
** </dd>
**
** [[SQLITE_DBCONFIG_WRITABLE_SCHEMA]] <dt>SQLITE_DBCONFIG_WRITABLE_SCHEMA</dt>
** <dd>The SQLITE_DBCONFIG_WRITABLE_SCHEMA option activates or deactivates the
** "writable_schema" flag. This has the same effect and is logically equivalent
** to setting [PRAGMA writable_schema=ON] or [PRAGMA writable_schema=OFF].
** The first argument to this setting is an integer which is 0 to disable 
** the writable_schema, positive to enable writable_schema, or negative to
** leave the setting unchanged. The second parameter is a pointer to an
** integer into which is written 0 or 1 to indicate whether the writable_schema
** is enabled or disabled following this call.
** </dd>
** </dl>
*/
#define SQLITE_DBCONFIG_MAINDBNAME            1000 /* const char* */
#define SQLITE_DBCONFIG_LOOKASIDE             1001 /* void* int int */
#define SQLITE_DBCONFIG_ENABLE_FKEY           1002 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_TRIGGER        1003 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER 1004 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION 1005 /* int int* */
#define SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE      1006 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_QPSG           1007 /* int int* */
#define SQLITE_DBCONFIG_TRIGGER_EQP           1008 /* int int* */
#define SQLITE_DBCONFIG_RESET_DATABASE        1009 /* int int* */
#define SQLITE_DBCONFIG_DEFENSIVE             1010 /* int int* */
#define SQLITE_DBCONFIG_WRITABLE_SCHEMA       1011 /* int int* */
#define SQLITE_DBCONFIG_MAX                   1011 /* Largest DBCONFIG */

/*
** CAPI3REF: Enable Or Disable Extended Result Codes
** METHOD: sqlite3
**
** ^The sqlite3_extended_result_codes() routine enables or disables the
** [extended result codes] feature of SQLite. ^The extended result

This page was generated in about 0.029s by Fossil 2.11 [a92d5a514d] 2019-10-23 00:46:53