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 program 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_schema 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 }