github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/atrc.c (about)

     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  }