AryaWu/sqlite

1/*2** This program generates a script that stresses the ALTER TABLE statement.3** Compile like this:4**5**      gcc -g -c sqlite3.c6**      gcc -g -o atrc atrc.c sqlite3.o -ldl -lpthread7**8** Run the program this way:9**10**      ./atrc DATABASE | ./sqlite3 DATABASE11**12** This program "atrc" generates a script that can be fed into an ordinary13** command-line shell.  The script performs many ALTER TABLE statements,14** runs ".schema --indent" and "PRAGMA integrity_check;", does more15** ALTER TABLE statements to restore the original schema, and then16** runs "PRAGMA integrity_check" again.  Every table and column has its17** name changed.  The entire script is contained within BEGIN...ROLLBACK18** 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 rename25** every column in table zTable to a generic name composed from26** zColPrefix and a sequential number.  The generated text is27** appended pConvert.  If pUndo is not NULL, then SQL text that28** will undo the change is appended to pUndo.29**30** The table to be converted must be in the "main" schema.31*/32int 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, cnt54    );55    if( pUndo ){56      sqlite3_str_appendf(pUndo,57        "ALTER TABLE \"%w\" RENAME COLUMN \"%w%d\" TO \"%w\";\n",58        zTab, zColPrefix, cnt, zCol59      );60    }61  }62  sqlite3_finalize(pStmt);63  return SQLITE_OK; 64}65 66/* Rename all tables and their columns in the main database67*/68int 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, zTab94      );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, zNewTab100    );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 transaction111**   (2) Rename all tables and columns to use generic names.112**   (3) Print the schema after this rename113**   (4) Run pragma integrity_check114**   (5) Do more ALTER TABLE statements to change the names back115**   (6) Run pragma integrity_check again116**   (7) Rollback the transaction117*/118int 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}151