*Project* Anubis *Title* SQLite Alter table function *Copyright* Copyright (c) Cédric Ricard 2007. *Author* Cédric Ricard *Created* 2007 02 17 *Satus* Released *Compatibility* 1.7.10 read tools/basis.anubis read data_base/db_tools.anubis read data_base/alter_table.anubis read system/logger.anubis read system/string.anubis public define Maybe(One) alter_table ( Database db, String tableName, String newTableQuery, Logger log ). --- That's all for the public part ! -------------------------------------------------- define String to_String ( DbEntityType type ) = if type is { table then "table", view then "view", index then "index", trigger then "trigger" }. define One print_list ( List(String) l ) = if l is { [] then print(" <<<\n"), [h . t] then print(h + ", "); print_list(t) }. define Maybe(One) my_sql_query ( Database db, String sql, Logger log ) = if sql_query(db, sql) is { error(sql_error) then logError(log, "SQL ERROR: " + sql_error.text + "\n ...executing query [" + sql + "]"); failure, ok(_,_,_) then success(unique) }. define Maybe(String) get_original_query ( Database db, String dbName, // alias for attached database, 'main' for main database. DbEntityType object_type, String tableName, Logger log ) = if sql_query(db, "SELECT sql FROM " + dbName + ".sqlite_master where (type='"+to_String(object_type)+"') and tbl_name='" + tableName + "'") is { error(sql_error) then logError(log, "alter_table/get_original_query ERROR '" + sql_error.text + "'"); failure, ok(_,table_cursor,_) then if table_cursor(unique) is { error(sql_error) then logError(log, "alter_table/get_original_query ERROR '" + sql_error.text + "'"); failure, no_more_row then failure, //can't find the table in sqlite_master, because the table is new row(current) then success(text(current)(0)) } }. define Bool has_rows ( Database db, String tableName, Logger log ) = if sql_query(db, "SELECT count(*) FROM " + tableName) is { error(sql_error) then logError(log, "has_rows query ERROR '" + sql_error.text + "'"); false, ok(_,table_cursor,_) then if table_cursor(unique) is { error(sql_error) then logError(log, "has_row run ERROR '" + sql_error.text + "'"); false, no_more_row then false, row(current) then db_integer(current)(0) /= 0 } }. define List(String) alter_table_extract_columns_list_sub ( Int -> SQLite3Datum row, Int colIndex ) = if row(colIndex) is text(name) then [name . alter_table_extract_columns_list_sub(row, colIndex+1)] else []. define List(String) alter_table_extract_columns_list ( SQLite3HeadersOrRow -> SQLite3Row table_cursor, Logger log ) = if table_cursor(headers) is { error(sql_error) then logError(log, "alter_table_extract_columns_list ERROR '" + sql_error.text + "'"); [], no_more_row then [], row(headers) then alter_table_extract_columns_list_sub(headers, 0) }. define String alter_table_extract_column_string ( One -> DbRow table_cursor, List(String) oldColumns, Logger log ) = if table_cursor(unique) is { error(sql_error) then logError(log, "alter_table_extract_columns_list ERROR '" + sql_error.text + "'"); "", no_more_row then /*logDebug(log, "Extract columns answers 'no more row'");*/ "", row(row) then if row(1) is db_text(name) then with test = (String name2) |-> logDebug(log, "name: " + name + " / name2: " + name2); if name = name2 then true else false, colString = alter_table_extract_column_string(table_cursor, oldColumns, log), if find_element(oldColumns, test) is { failure then colString, success(_) then logDebug(log, "found: " + name); if is_empty(colString) then name else name + ", " + colString } else "" }. // false = different, true = same define Bool compare_creation_queries ( String tableName, String query1, String query2, ) = if find(tableName, query1, 0) is { failure then false, success(p1) then if find(tableName, query2, 0) is { failure then false, success(p2) then if sub_string(query1, p1, length(query1) - p1) is { failure then false, success(s1) then if sub_string(query2, p2, length(query2) - p2) is { failure then false, success(s2) then s1 = s2 } } } }. public define Maybe(One) alter_table ( Database db, String dbName, // alias for attached database, 'main' for main database. String tableName, String newTableQuery, Logger log ) = with fullTableName = dbName + "." + tableName, if get_original_query(db, dbName, table, tableName, log) is { failure then logInfo(log, "Creating table '" + fullTableName + "'... "); if sql_query(db, newTableQuery) is error(sql_error) then logError(log, "ERROR : " + sql_error.text); failure else logInfo(log, " --> ok."); success(unique), success(originalTableQuery) then if compare_creation_queries(tableName, originalTableQuery, newTableQuery) = false then logInfo(log, "Updating table '" + fullTableName + "' on database... "); println(originalTableQuery); println(newTableQuery); if has_rows(db, fullTableName, log) is { false then if my_sql_query(db, "DROP TABLE " + fullTableName, log) is failure then failure else if my_sql_query(db, newTableQuery, log ) is { failure then failure, success(_) then logInfo(log, " --> ok."); success(unique) }, true then with tempNameTable = tableName + "_temp", forget(sql_query(db, "DROP TABLE " + tempNameTable)); if my_sql_query(db, "CREATE TEMP TABLE " + tempNameTable + " AS SELECT * from " + fullTableName, log) is failure then failure else if my_sql_query(db, "DROP TABLE " + fullTableName, log) is failure then failure else if my_sql_query(db, newTableQuery , log) is failure then failure else if sql_query(db, "SELECT * FROM " + tempNameTable) is { error(sql_error) then logError(log, "alter_table: select * tempTable ERROR '" + sql_error.text + "'"); failure, ok(get_cols,table_cursor2,_) then with oldColumns = get_cols(unique), //print_list(oldColumns); if sql_query(db, "PRAGMA " + dbName + ".table_info(" + tableName + ")") is { error(sql_error) then logError(log, "alter_table: pragma table_info() ERROR '" + sql_error.text + "'"); failure, ok(_,table_cursor3,_) then with newColumnsStr = alter_table_extract_column_string(table_cursor3, oldColumns, log), sql = "INSERT INTO " + fullTableName + " (" + newColumnsStr + ") SELECT " + newColumnsStr + " FROM " + tempNameTable, if sql_query(db, sql) is { error(sql_error) then logError(log, "alter_table ERROR '" + sql_error.text + "'\n\tquery = [" + sql + "]"); failure, ok(_,_,_) then logInfo(log, " --> ok."); success(unique) } } } } else success(unique) }. public define Maybe(One) alter_table ( Database db, String tableName, String newTableQuery, Logger log ) = alter_table(db, "main", tableName, newTableQuery, log). public define Maybe(One) alter_view ( Database db, String dbName, // alias for attached database, 'main' for main database. String viewName, String newViewQuery, Logger log ) = with fullViewName = dbName + "." + viewName, if get_original_query(db, dbName, view, viewName, log) is { failure then logInfo(log, "Creating view '" + fullViewName + "'... "); if sql_query(db, newViewQuery) is error(sql_error) then logError(log, "ERROR : " + sql_error.text); failure else logInfo(log, " --> ok."); success(unique), success(originalViewQuery) then if compare_creation_queries(viewName, originalViewQuery, newViewQuery) = false then logInfo(log, "Updating view '" + fullViewName + "' on database... "); println(originalViewQuery); println(newViewQuery); if my_sql_query(db, "DROP VIEW " + fullViewName, log) is failure then failure else if my_sql_query(db, newViewQuery, log ) is { failure then failure, success(_) then logInfo(log, " --> ok."); success(unique) } else success(unique) }. public define Maybe(One) alter_view ( Database db, String viewName, String newViewQuery, Logger log ) = alter_view(db, "main", viewName, newViewQuery, log).