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