/* * * 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 read calexium_lib/net_services_protocols/logger_service.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))), 60000, // max 60s 100, // 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, 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, "stmt", 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 --------------- 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(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 -> 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]) }. 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. public define (List(SQLite3Bind), String) make_clause_and_binds ( List(SQLite3_update_field) fields, List(SQLite3Bind) so_far, List(String) clause )= if fields is { [] then (so_far, join(", ",clause)), [ h . t ] then if h is field(column, bind) then make_clause_and_binds(t, [bind . so_far], [column+" = :v_"+column . clause]) }. extended version of bind for SQLite3. public define SQLite3Bind bind_Int_or_NULL ( String name, Int value )= if value = 0 then bind_NULL(name) else bind_Int(name, value). public define SQLite3Bind bind_String_or_NULL ( String name, String value )= if value = "" then bind_NULL(name) else bind_String(name, value). public define SQLite3Bind bind_Datetime ( String name, DB_datetime dt )= bind_String(name, datetime(dt)). public define SQLite3Bind bind_Date ( String name, DB_date d )= bind_String(name, date(d)). public define SQLite3Bind bind_Bool ( String name, Bool value )= bind_Int(name, to_DBInt(value)). public define SQLite3Bind bind_BLOB ( String name, $V value )= bind_ByteArray(name, serialize(value)). // -- 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).