Anubis / Xlib

/*
 * 
 * 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(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,    //can't find the symbol in the table, because the row is empty
    row(explorer)     then (Int)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 -> 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_DB_id_or_NULL
  (
    String  _name,
    DB_id   _value
  )=
  if _value is
  {
    none          then bind_NULL(_name)
    db_id(value)  then bind_Int(_name, value)
  }
.

    
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).