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