SQLite
Check-in [ed64a55a22]
Not logged in
Overview
Comment:Add the "atrc" test program. "Atrc" is short for "ALTER TABLE RENAME COLUMN". See the header comment on the program itself for further information.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256:ed64a55a2242408ac0b889b330cf91d38a954399b75c6ebe71232613b864c70b
User & Date: drh 2018-08-11 20:46:54
Context
2018-08-13
13:43
Fix issues with ALTER TABLE RENAME COLUMN associated with OOM errors. check-in: 0b28dd5c2e user: drh tags: alter-table-rename-column
2018-08-11
20:46
Add the "atrc" test program. "Atrc" is short for "ALTER TABLE RENAME COLUMN". See the header comment on the program itself for further information. check-in: ed64a55a22 user: drh tags: alter-table-rename-column
20:38
If the new column name in an ALTER TABLE RENAME COLUMN statement is quoted, then also use quotes for the column name in the edited SQL statements. check-in: ca5184a25f user: dan tags: alter-table-rename-column
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added test/atrc.c.













































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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 problem 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_master 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; 
}