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 */
rename_all_columns_of_table(sqlite3 * db,const char * zTab,const char * zColPrefix,sqlite3_str * pConvert,sqlite3_str * pUndo)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 */
rename_all_tables(sqlite3 * db,sqlite3_str * pConvert,sqlite3_str * pUndo)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 */
main(int argc,char ** argv)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 }
151