/ Check-in [ed64a55a]
Login

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

Overview
Comment:Add the "atrc" test program. "Atrc" is short for "ALTER TABLE RENAME COLUMN". See the header comment on the program itself for further information.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256: ed64a55a2242408ac0b889b330cf91d38a954399b75c6ebe71232613b864c70b
User & Date: drh 2018-08-11 20:46:54
Context
2018-08-13
13:43
Fix issues with ALTER TABLE RENAME COLUMN associated with OOM errors. check-in: 0b28dd5c user: drh tags: alter-table-rename-column
2018-08-11
20:46
Add the "atrc" test program. "Atrc" is short for "ALTER TABLE RENAME COLUMN". See the header comment on the program itself for further information. check-in: ed64a55a user: drh tags: alter-table-rename-column
20:38
If the new column name in an ALTER TABLE RENAME COLUMN statement is quoted, then also use quotes for the column name in the edited SQL statements. check-in: ca5184a2 user: dan tags: alter-table-rename-column
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Added test/atrc.c.

            1  +/*
            2  +** This program generates a script that stresses the ALTER TABLE statement.
            3  +** Compile like this:
            4  +**
            5  +**      gcc -g -c sqlite3.c
            6  +**      gcc -g -o atrc atrc.c sqlite3.o -ldl -lpthread
            7  +**
            8  +** Run the problem this way:
            9  +**
           10  +**      ./atrc DATABASE | ./sqlite3 DATABASE
           11  +**
           12  +** This program "atrc" generates a script that can be fed into an ordinary
           13  +** command-line shell.  The script performs many ALTER TABLE statements,
           14  +** runs ".schema --indent" and "PRAGMA integrity_check;", does more
           15  +** ALTER TABLE statements to restore the original schema, and then
           16  +** runs "PRAGMA integrity_check" again.  Every table and column has its
           17  +** name changed.  The entire script is contained within BEGIN...ROLLBACK
           18  +** so that no changes are ever actually made to the database.
           19  +*/
           20  +#include "sqlite3.h"
           21  +#include <stdio.h>
           22  +
           23  +/*
           24  +** Generate the text of ALTER TABLE statements that will rename
           25  +** every column in table zTable to a generic name composed from
           26  +** zColPrefix and a sequential number.  The generated text is
           27  +** appended pConvert.  If pUndo is not NULL, then SQL text that
           28  +** will undo the change is appended to pUndo.
           29  +**
           30  +** The table to be converted must be in the "main" schema.
           31  +*/
           32  +int rename_all_columns_of_table(
           33  +  sqlite3 *db,                   /* Database connection */
           34  +  const char *zTab,              /* Table whose columns should all be renamed */
           35  +  const char *zColPrefix,        /* Prefix for new column names */
           36  +  sqlite3_str *pConvert,         /* Append ALTER TABLE statements here */
           37  +  sqlite3_str *pUndo             /* SQL to undo the change, if not NULL */
           38  +){
           39  +  sqlite3_stmt *pStmt;
           40  +  int rc;
           41  +  int cnt = 0;
           42  +
           43  +  rc = sqlite3_prepare_v2(db,
           44  +         "SELECT name FROM pragma_table_info(?1);",
           45  +         -1, &pStmt, 0);
           46  +  if( rc ) return rc;
           47  +  sqlite3_bind_text(pStmt, 1, zTab, -1, SQLITE_STATIC);
           48  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
           49  +    const char *zCol = (const char*)sqlite3_column_text(pStmt, 0);
           50  +    cnt++;
           51  +    sqlite3_str_appendf(pConvert,
           52  +      "ALTER TABLE \"%w\" RENAME COLUMN \"%w\" TO \"%w%d\";\n",
           53  +      zTab, zCol, zColPrefix, cnt
           54  +    );
           55  +    if( pUndo ){
           56  +      sqlite3_str_appendf(pUndo,
           57  +        "ALTER TABLE \"%w\" RENAME COLUMN \"%w%d\" TO \"%w\";\n",
           58  +        zTab, zColPrefix, cnt, zCol
           59  +      );
           60  +    }
           61  +  }
           62  +  sqlite3_finalize(pStmt);
           63  +  return SQLITE_OK; 
           64  +}
           65  +
           66  +/* Rename all tables and their columns in the main database
           67  +*/
           68  +int rename_all_tables(
           69  +  sqlite3 *db,              /* Database connection */
           70  +  sqlite3_str *pConvert,    /* Append SQL to do the rename here */
           71  +  sqlite3_str *pUndo        /* Append SQL to undo the rename here */
           72  +){
           73  +  sqlite3_stmt *pStmt;
           74  +  int rc;
           75  +  int cnt = 0;
           76  +
           77  +  rc = sqlite3_prepare_v2(db,
           78  +         "SELECT name FROM sqlite_master WHERE type='table'"
           79  +         " AND name NOT LIKE 'sqlite_%';",
           80  +         -1, &pStmt, 0);
           81  +  if( rc ) return rc;
           82  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
           83  +    const char *zTab = (const char*)sqlite3_column_text(pStmt, 0);
           84  +    char *zNewTab;
           85  +    char zPrefix[2];
           86  +
           87  +    zPrefix[0] = (cnt%26) + 'a';
           88  +    zPrefix[1] = 0;
           89  +    zNewTab = sqlite3_mprintf("tx%d", ++cnt);
           90  +    if( pUndo ){
           91  +      sqlite3_str_appendf(pUndo,
           92  +        "ALTER TABLE \"%s\" RENAME TO \"%w\";\n",
           93  +        zNewTab, zTab
           94  +      );
           95  +    }
           96  +    rename_all_columns_of_table(db, zTab, zPrefix, pConvert, pUndo);
           97  +    sqlite3_str_appendf(pConvert,
           98  +      "ALTER TABLE \"%w\" RENAME TO \"%s\";\n",
           99  +      zTab, zNewTab
          100  +    );
          101  +    sqlite3_free(zNewTab);
          102  +  }
          103  +  sqlite3_finalize(pStmt);
          104  +  return SQLITE_OK;
          105  +}
          106  +
          107  +/*
          108  +** Generate a script that does this:
          109  +**
          110  +**   (1) Start a transaction
          111  +**   (2) Rename all tables and columns to use generic names.
          112  +**   (3) Print the schema after this rename
          113  +**   (4) Run pragma integrity_check
          114  +**   (5) Do more ALTER TABLE statements to change the names back
          115  +**   (6) Run pragma integrity_check again
          116  +**   (7) Rollback the transaction
          117  +*/
          118  +int main(int argc, char **argv){
          119  +  sqlite3 *db;
          120  +  int rc;
          121  +  sqlite3_str *pConvert;
          122  +  sqlite3_str *pUndo;
          123  +  char *zDbName;
          124  +  char *zSql1, *zSql2;
          125  +  if( argc!=2 ){
          126  +    fprintf(stderr, "Usage: %s DATABASE\n", argv[0]);
          127  +  }
          128  +  zDbName = argv[1];
          129  +  rc = sqlite3_open(zDbName, &db);
          130  +  if( rc ){
          131  +    fprintf(stderr, "sqlite3_open() returns %d\n", rc);
          132  +    return 1;
          133  +  }
          134  +  pConvert = sqlite3_str_new(db);
          135  +  pUndo = sqlite3_str_new(db);
          136  +  rename_all_tables(db, pConvert, pUndo);
          137  +  zSql1 = sqlite3_str_finish(pConvert);
          138  +  zSql2 = sqlite3_str_finish(pUndo);
          139  +  sqlite3_close(db);
          140  +  printf("BEGIN;\n");
          141  +  printf("%s", zSql1);
          142  +  sqlite3_free(zSql1);
          143  +  printf(".schema --indent\n");
          144  +  printf("PRAGMA integrity_check;\n");
          145  +  printf("%s", zSql2);
          146  +  sqlite3_free(zSql2);
          147  +  printf("PRAGMA integrity_check;\n");
          148  +  printf("ROLLBACK;\n");
          149  +  return 0; 
          150  +}