/* * * User: David RENE * Date: 11/12/2007 * Time: 01:16 * (c) Calexium * */ read tools/basis.anubis read system/logger.anubis read system/string.anubis read data_base/db_tools.anubis read data_base/sqlite.anubis transmit calexium_lib/database/db_types.anubis transmit calexium_lib/database/db_utils.anubis transmit calexium_lib/database/db_binds.anubis transmit calexium_lib/database/db_update_fields.anubis read calexium_lib/net_services_protocols/logger_client.anubis public define Int one_year_seconds = 365 * 86400. // amount of seconds during 1 year // public define Int one_month_seconds = 31 * 86400. public define Int one_week_seconds = 7 * 86400. // amount of seconds during 1 week public define Int one_day_seconds = 86400. // amount of seconds during 1 day define String __utime_to_string ( UTime t ) = to_decimal(t.seconds) + "." + zero_pad_n(6, t.microseconds ) + " s". define One __logLongQueries ( UTime t0, String sql_query, String msg ) = with t = unow - t0, if t.seconds > 0 then logTrace("", "SQL", logMask("sql_profiling"), "[SQL LONG QUERY] " + __utime_to_string(t) + " from ("+msg+") executing [" + sql_query + "]") else unique. define One __logQueries ( UTime t0, String sql_query, String msg ) = logTrace("", "SQL_TRACE", logMask("sql_trace"), "[SQL] " + __utime_to_string(unow - t0) + " from ("+msg+") executing [" + sql_query + "]"). public define Result(DbError, One) sql_transaction ( Database db, String sql_command, String message ) = // logDebug(debug_log, "Entering into transaction [" + message + "]."); with t0 = unow, if db_do_transaction( db, (One _) |-> if db_query(db, sql_command) is { error(err) then logError("", "DB", db_error(err,message));failure //error in the SQL request ok(_,cursor,_) then success(unique) }, success( (DbError err) |-> logError("", "DB", db_error(err,message))), 600000, // max 600s 200, // retry every 100 ms (DbError _) |-> false ) is { error(err_and_result) then // logDebug(debug_log, "Exiting from transaction [" + message + "] with error."); __logLongQueries(t0, sql_command, message); if err_and_result is (err, mb_result) then error(err), ok(_) then // logDebug(debug_log, "Exiting from transaction [" + message + "]."); __logLongQueries(t0, sql_command, message); ok(unique) }. public define DbQueryResult sql_query_timeout ( Database db, //database handle String sql_query, //sql query itself List(DbBind) initial_bindings, String msg //message to be shown if an error occure ) = //we try with 30 sec of timeout with t0 = unow, if sql_query_timeout(db, sql_query, initial_bindings, 60, 100, (DbError _) |-> false) is { error(sql_error) then logError("", "DB", db_error(sql_error,msg) + " executing [" + sql_query + "]"); __logLongQueries(t0, sql_query, msg); error(sql_error), ok(headers, cursor, reset) then __logLongQueries(t0, sql_query, msg); ok(headers, cursor, reset) }. public define DbQueryResult sql_query_timeout ( Database db, //database handle String sql_query, //sql query itself String msg //message to be shown if an error occure ) = sql_query_timeout(db, sql_query, [], msg). public define Maybe(One -> DbRow) sql_query_timeout ( Database db, //database handle String sql_query, //sql query itself String msg //message to be shown if an error occure ) = if sql_query_timeout(db, sql_query, [], msg) is { error(_) then failure, ok(_,cursor,_) then success(cursor) }. // deprecated. Use one of the previous ones. public define SQLite3QueryResult sql_query_timeout ( SQLite3DataBase db, //database handle String sql_query, //sql query itself List(SQLite3Bind) initial_bindings, String msg //message to be shown if an error occure ) = //we try with 30 sec of timeout //println(" executing [" + sql_query + "]"); with t0 = unow, if sql_query_timeout(db, sql_query, initial_bindings, 120, 100) is { error(sql_error) then logError("", "DB", db_error(sql_error,msg) /*+ " executing [" + sql_query + "]"*/); //println(" executing [" + sql_query + "]"); __logQueries(t0, "[sql_query_timeout Error] "+sql_query, msg); __logLongQueries(t0, sql_query, msg); error(sql_error), ok(headers, cursor, reset) then //__logQueries(t0, sql_query, msg); __logLongQueries(t0, sql_query, msg); ok(headers, cursor, reset) }. public define SQLite3QueryResult sql_query_timeout ( SQLite3DataBase db, //database handle String sql_query, //sql query itself String msg //message to be shown if an error occure ) = sql_query_timeout(db, sql_query, [], msg). public define SQLite3Row sql_query_statement_timeout ( SQLite3Stmt sql_command_stmt, //sql query itself List(SQLite3Bind) initial_bindings, String msg //message to be shown if an error occure ) = //we try with 60 sec of timeout with //println(" executing [" + msg + "]"); with t0 = unow, if sql_query_statement_timeout(sql_command_stmt, initial_bindings, 120, 100, msg) is { error(sql_error) then logError("", "DB", db_error(sql_error,msg) /*+ " executing [" + sql_query + "]"*/); //println(" executing [" + sql_command_stmt + "]"); __logQueries(t0, "[sql_query_statement_timeout Error]", msg); __logLongQueries(t0, "stmt", msg); error(sql_error), no_more_row then //__logQueries(t0, "stmt", msg); __logLongQueries(t0, "stmt", msg); no_more_row row(data) then //__logQueries(t0, "stmt", msg); __logLongQueries(t0, "stmt", msg); row(data) }. // -- Extractors HELPERS --------------- // sqlite3 API public define String db_get_String ( One -> SQLite3Row table_cursor ) = if table_cursor(unique) is { error(sql_error) then logError("", "DB", db_error(sql_error, "db_get_String")); "", no_more_row then "", //can't find the symbol in the table, because the row is empty row(explorer) then text(explorer)(0) }. public define List(String) db_get_string_list ( One -> DbRow table_cursor, List(String) so_far ) = if table_cursor(unique) is { error(sql_error) then logError("", "DB", db_error(sql_error, "db_get_string_list")); reverse(so_far), no_more_row then reverse(so_far), //can't find the symbol in the table, because the row is empty row(explorer) then with s = text(explorer)(0), db_get_string_list(table_cursor, [s . so_far]) }. // sqlite3 API public define List(String) db_get_string_list ( One -> SQLite3Row table_cursor, List(String) so_far ) = if table_cursor(unique) is { error(sql_error) then logError("", "DB", db_error(sql_error, "db_get_string_list")); reverse(so_far), no_more_row then reverse(so_far), //can't find the symbol in the table, because the row is empty row(explorer) then with s = text(explorer)(0), db_get_string_list(table_cursor, [s . so_far]) }. // sqlite3 API public define List(String) db_get_string_list ( One -> SQLite3Row table_cursor ) = if table_cursor(unique) is { error(sql_error) then logError("", "DB", db_error(sql_error, "db_get_string_list")); [], no_more_row then [], //can't find the symbol in the table, because the row is empty row(explorer) then with s = text(explorer)(0), [ s . db_get_string_list(table_cursor)] }. // sqlite3 API public define Int db_get_integer ( One -> SQLite3Row table_cursor ) = if table_cursor(unique) is { error(sql_error) then logError("", "DB", db_error(sql_error, "db_get_integer")); 0, no_more_row then 0, row(explorer) then (Int)db_integer(explorer)(0) }. public define Maybe(Int) db_get_mb_integer ( One -> SQLite3Row table_cursor ) = if table_cursor(unique) is { error(sql_error) then logError("", "DB", db_error(sql_error, "db_get_mb_integer")); failure, no_more_row then failure, row(explorer) then db_integer(explorer)(0) }. // sqlite3 API public define List(Int) db_get_integer_list ( One -> SQLite3Row table_cursor, List(Int) so_far ) = if table_cursor(unique) is { error(sql_error) then logError("", "DB", db_error(sql_error, "db_get_integer_list")); reverse(so_far), no_more_row then reverse(so_far), //can't find the symbol in the table, because the row is empty row(explorer) then with s = (Int)db_integer(explorer)(0), db_get_integer_list(table_cursor, [s . so_far]) }. public define List(Int) db_get_integer_list ( One -> SQLite3Row table_cursor ) = db_get_integer_list(table_cursor, []). // sqlite3 API public define Float db_get_Float ( One -> SQLite3Row table_cursor ) = if table_cursor(unique) is { error(sql_error) then logError("", "DB", db_error(sql_error, "db_get_Float")); 0.0, no_more_row then 0.0, row(explorer) then db_float(explorer)(0) }. public define List(Int) db_get_integer_list ( One -> DbRow table_cursor, List(Int) so_far ) = if table_cursor(unique) is { error(sql_error) then logError("", "DB", db_error(sql_error, "db_get_integer_list")); reverse(so_far), no_more_row then reverse(so_far), //can't find the symbol in the table, because the row is empty row(explorer) then with s = (Int)db_integer(explorer)(0), db_get_integer_list(table_cursor, [s . so_far]) }. public define List(Int) db_get_integer_list ( One -> DbRow table_cursor, ) = db_get_integer_list(table_cursor, []). Help to construct clause and a list of bind according to list of SQLite3_update_field. This is useful when we want to construct a SQL query with only needs fields. // -- Migration HELPERS --------------- public define DbBind bind_String ( String name, String value ) = db_bind(force(sub_string(name, 1, length(name) - 1), name), db_text(value)). public define DbBind bind_ByteArray ( String name, ByteArray value ) = db_bind(force(sub_string(name, 1, length(name) - 1), name), db_blob(value)). public define DbBind bind_Int ( String name, Int value ) = db_bind(force(sub_string(name, 1, length(name) - 1), name), db_integer(value)). public define DbBind bind_NULL ( String name, ) = db_bind(force(sub_string(name, 1, length(name) - 1), name), null).