David RENÉ / hayamiki_lib

/*
 * Created by PyramIDE.
 * User: フランスのトトロ aka (David RENÉ) 
 * Date: 02/04/2017
 * Time: 19:23
 * © David RENÉ
 */

read data_base/sqlite.anubis
read xlib/database/db_utils.anubis
read hayamiki_lib/types/hayamiki.anubis
transmit hayamiki_lib/controller/hk_controller.anubis

public define String
  referer_to_sql_clause
  (
    HK_Referer    referer,
    String        table_name,
    List(String)  table_columns
  )=
  if referer is
  {
    no_referer                                                     then " (1 = 1) ",
    hk_referer(referer_table, referer_row_id, fk_table, fk_column) then
      if referer_table = table_name then
        "id = " +referer_row_id
      else if fk_column:table_columns then
        fk_column + " = " + referer_row_id
      else
        " (1 = 1) ",
  }
.

public define Maybe(One)
  delete_from_ids
  (
    SQLite3DataBase db,
    String          table_name,    
    List(Int)       ids
  )=
  if length(ids) = 0 then 
    success(unique)
  else
    if sql_query_timeout(db,
          "DELETE FROM "+table_name+
          " WHERE id IN ("+join(", ", to_List_String(ids))+");",
           [],
          "delete_from_ids") is
    {
      error(_)    then failure,
      ok(_, _, _) then success(unique)
    }
  .

public define Maybe(One)
  delete_by_clause
  (
    SQLite3DataBase db,
    String          table_name,    
    String          clause
  )=
  if sql_query_timeout(db,
        "DELETE FROM "+table_name+
        " "+ clause +";",
         [],
        "delete_by_clause") is
  {
    error(_)    then failure,
    ok(_, _, _) then success(unique)
  }.
  
public define Int
  get_count_by_full_clause
  (
    SQLite3DataBase db,
    String          clause
  )=
  if sql_query_timeout(db, clause+";", [], "get_count_by_clause") is
  {
    error(sql_error) then println("get_count_by_full_clause SQL ERROR '"+ sql_error.text + "'\n clause ["+clause+";]");0,
    ok(_, cursor, _) then /*println("get_count_by_full_clause OK ["+clause+";]");*/db_get_Int(cursor)
  }
.

public define Int
  db_get_Int
  (
    SQLite3DataBase   db,
    String            clause,
    List(SQLite3Bind) binds
  )=
  if sql_query_timeout(db, clause+";", binds, "db_get_Int") is
  {
    error(sql_error) then println("db_get_Int SQL ERROR '"+ sql_error.text + "'\n clause ["+clause+";]");0,
    ok(_, cursor, _) then db_get_Int(cursor)
  }
.

public define Int
  db_get_Int
  (
    SQLite3DataBase   db,
    String            clause
  )=
  db_get_Int(db, clause, [])
.

public define List(String)
  db_get_List_String
  (
    SQLite3DataBase   db,
    String            clause,
    List(SQLite3Bind) binds
  )=
  if sql_query_timeout(db, clause+";", binds, "db_get_List_String") is
  {
    error(sql_error) then println("db_get_List_String SQL ERROR '"+ sql_error.text + "'\n clause ["+clause+";]");[],
    ok(_, cursor, _) then db_get_List_String(cursor)
  }
.

public define List(String)
  db_get_List_String
  (
    SQLite3DataBase   db,
    String            clause
  )=
  db_get_List_String(db, clause, [])
.

public define Float
  get_count_float_by_full_clause
  (
    SQLite3DataBase db,
    String          clause
  )=
  if sql_query_timeout(db, clause+";", [], "get_count_by_clause") is
  {
    error(sql_error) then println("get_count_by_full_clause SQL ERROR '"+ sql_error.text + "'\n clause ["+clause+";]");0.0,
    ok(_, cursor, _) then /*println("get_count_by_full_clause OK ["+clause+";]");*/db_get_Float(cursor)
  }
.

public define Float
  db_get_Float
  (
    SQLite3DataBase   db,
    String            clause,
    List(SQLite3Bind) binds
  )=
  if sql_query_timeout(db, clause+";", binds, "db_get_Float") is
  {
    error(sql_error) then println("db_get_Float SQL ERROR '"+ sql_error.text + "'\n clause ["+clause+";]");0.0,
    ok(_, cursor, _) then db_get_Float(cursor)
  }
.

public define Float
  db_get_Float
  (
    SQLite3DataBase   db,
    String            clause
  )=
  db_get_Float(db, clause, [])
.

public define Int
  get_count_by_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          clause,
    List(SQLite3Bind) binds
  )=
//  with final_clause = if clause = "" then
//                         ""
//                      else
//                        "WHERE "+clause,

  if sql_query_timeout(db, "SELECT COUNT(\""+table_name+".id\") FROM "+table_name+" "+clause+";", binds, "get_count_by_clause") is
  {
    error(sql_error) then println("get_count_by_clause SQL ERROR '"+ sql_error.text + "'\n table_name ["+table_name+"]\n clause ["+clause+";]");0,
    ok(_, cursor, _) then db_get_Int(cursor)
  }
.

public define Int
  get_count_by_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          clause
  )=
  get_count_by_clause(db, table_name, clause, [])
.

public define Int
  get_count
  (
    SQLite3DataBase db,
    String          table_name
  )=
  get_count_by_clause(db, table_name, "")
.


public define List(Int)
  get_ids_by_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          clause
  )=
  with final_clause = if clause = "" then
                         ""
                      else
                        "WHERE "+clause,

  if sql_query_timeout(db, "SELECT \"id\" FROM "+table_name+" "+final_clause+";", [], "get_ids_by_clause") is
  {
    error(sql_error) then println("get_ids_by_clause SQL ERROR '"+ sql_error.text + "'\n table_name ["+table_name+"]\n clause ["+clause+";]");[],
    ok(_, cursor, _) then db_get_List_Int(cursor)
  }
.

public define List(DB_id)
  get_db_ids_by_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          clause
  )=
  with final_clause = if clause = "" then
                         ""
                      else
                        "WHERE "+clause,

  if sql_query_timeout(db, "SELECT \"id\" FROM "+table_name+" "+final_clause+";", [], "get_db_ids_by_clause") is
  {
    error(sql_error) then println("get_db_ids_by_clause SQL ERROR '"+ sql_error.text + "'\n table_name ["+table_name+"]\n clause ["+clause+";]");[],
    ok(_, cursor, _) then db_get_List_DB_id(cursor)
  }
.

public define List(Int)
  get_ids_by_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          id_col,
    String          clause
  )=
  with final_clause = if clause = "" then
                         ""
                      else
                        "WHERE "+clause,

  if sql_query_timeout(db, "SELECT \""+id_col+"\" FROM "+table_name+" "+final_clause+";", [], "get_ids_by_clause") is
  {
    error(sql_error) then println("get_ids_by_clause SQL ERROR '"+ sql_error.text + "'\n table_name ["+table_name+"]\n id_col["+id_col+"]\nclause ["+clause+";]");[],
    ok(_, cursor, _) then db_get_List_Int(cursor)
  }
.

public define List(DB_id)
  get_db_ids_by_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          id_col,
    String          clause
  )=
  with final_clause = if clause = "" then
                         ""
                      else
                        "WHERE "+clause,

  if sql_query_timeout(db, "SELECT \""+id_col+"\" FROM "+table_name+" "+final_clause+";", [], "get_ids_by_clause") is
  {
    error(sql_error) then println("get_db_ids_by_clause SQL ERROR '"+ sql_error.text + "'\n table_name ["+table_name+"]\n id_col["+id_col+"]\nclause ["+clause+";]");[],
    ok(_, cursor, _) then db_get_List_DB_id(cursor)
  }
.

public define List(DB_id)
  get_db_ids_by_full_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          id_col,
    String          full_clause,
    List(SQLite3Bind) binds
  )=

  if sql_query_timeout(db, "SELECT \""+id_col+"\" FROM "+table_name+" "+full_clause+";", binds, "get_db_ids_by_full_clause") is
  {
    error(sql_error) then println("get_db_ids_by_full_clause SQL ERROR '"+ sql_error.text + "'\n table_name ["+table_name+"]\n id_col["+id_col+"]\nfull_clause ["+full_clause+";]");[],
    ok(_, cursor, _) then db_get_List_DB_id(cursor)
  }
.

public define List(DB_id)
  get_db_ids_by_full_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          id_col,
    String          full_clause
  )=
  get_db_ids_by_full_clause(db, table_name, id_col, full_clause, [])
.

public define Maybe(DB_id)
  get_db_id_by_full_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          id_col,
    String          full_clause,
    List(SQLite3Bind) binds
  )=
  with the_list = get_db_ids_by_full_clause(db, table_name, id_col, full_clause, binds),
  println("list size "+length(the_list));
  if the_list is
  {
    []      then failure,
    [h . _] then success(h)
  }
.
  
public define Maybe(Int)
  get_mb_id_by_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          id_col,    
    String          clause
  )=
  with final_clause = if clause = "" then
                         ""
                      else
                        "WHERE "+clause,

  if sql_query_timeout(db, "SELECT \""+id_col+"\" FROM "+table_name+" "+final_clause+";", [], "get_mb_id_by_clause") is
  {
    error(_)         then failure,
    ok(_, cursor, _) then db_get_mb_Int(cursor)
  }
.

public define Maybe(Int)
  get_mb_id_by_clause
  (
    SQLite3DataBase db,
    String          table_name,  
    String          clause
  )=
  get_mb_id_by_clause(db, table_name, "id", clause)
.

public define DB_id
  get_DB_id_by_clause
  (
    SQLite3DataBase db,
    String          table_name,  
    String          clause
  )=
  if get_mb_id_by_clause(db, table_name, "id", clause) is
  {
    failure     then none,
    success(id) then db_id(id)
  }
.

public define Maybe(One -> SQLite3Row)
  get_cursor_by_clause
  (
    SQLite3DataBase   db,
    String            table_name,
    String            fields,
    String            _join,
    String            _where,
    String            _order,
    String            _limit
  )=
  with where = if _where = "" then "" else " WHERE "+_where,
       join  = if _join  = "" then "" else " JOIN "+_join,

  if sql_query_timeout(db, "SELECT "+fields+" FROM "+table_name+join+where+" "+_order+" "+_limit+";", [], "get_cursor_by_clause") is
  {
    error(_)         then failure,
    ok(_, cursor, _) then success(cursor)
  }
.

public define (String, String, String, String) -> Maybe(One -> SQLite3Row)
  call_back_get_cursor_by_clause
  (
    SQLite3DataBase   db,
    String            table_name,
    String            fields
  )=
  with head = "SELECT "+fields+" FROM "+table_name,
  (
    String            _join,
    String            _where,
    String            _order,
    String            _limit
  )|->
  with where = if _where = "" then "" else " WHERE "+_where,
       join  = if _join  = "" then "" else " JOIN "+_join,

  if sql_query_timeout(db, head + join + where + " "+ _order + " " + _limit + ";", [], "get_cursor_by_clause") is
  {
    error(_)         then failure,
    ok(_, cursor, _) then success(cursor)
  }
.
 
 /***** JSON *****/

public define Maybe(JsonValue)
  extract_cursor_to_json
  (
    SQLite3DataBase                             db,
    List(((Int -> SQLite3Datum) -> JsonMember)) extractors,
    One -> SQLite3Row                           table_cursor
  ) =
  //get next cursor
  if table_cursor(unique) is
  {
    error(sql_error)  then println(db_error(sql_error, "extract_cursor_to_json")); failure,
    no_more_row       then failure,
    row(cursor)       then
      success(
        json_object(
          map(((Int -> SQLite3Datum) -> JsonMember extractor) |-> extractor(cursor) , extractors)
        ))
  }
.

public define List(JsonValue)
  get_all_to_json
  (
    SQLite3DataBase   db,
    List(((Int -> SQLite3Datum) -> JsonMember)) extractors,
    One -> SQLite3Row table_cursor,
    List(JsonValue)   so_far
  ) =
  if extract_cursor_to_json(db, extractors, table_cursor) is
  {
    failure       then reverse(so_far),
    success(data) then get_all_to_json(db, extractors, table_cursor, [data . so_far])
  }
.

  
 /***** Type $T ******/

define (One -> SQLite3Row, List($T)) ->List($T)
  _get_all
  (
    (One -> SQLite3Row table_cursor) -> Maybe($T) extractor
  )=
  (
    One -> SQLite3Row cursor,
    List($T)          so_far
  )|-loop->
  if extractor(cursor) is
  {
    failure       then reverse(so_far),
    success(data) then loop(cursor, [data . so_far])
  }
.

public define (One -> SQLite3Row table_cursor) -> List($T)
  get_all
  (
    (One -> SQLite3Row table_cursor) -> Maybe($T)       extractor
  )=
  with real_get = _get_all(extractor),
  (
    One -> SQLite3Row cursor
  ) |->
  real_get(cursor, [])
.

public define List($T)
  get_all_by_full_clause
  (
    SQLite3DataBase db,
    String          table_name,
    String          full_clause,
    (One -> SQLite3Row table_cursor) -> Maybe($T)       extractor
  )=
  
  if sql_query_timeout(db, "SELECT * FROM "+table_name+" "+full_clause+";", [], "get_all_by_full_clause") is
  {
    error(sql_error) then println("get_all_by_full_clause SQL ERROR '"+ sql_error.text + "'\n table_name ["+table_name+"]\n full_clause ["+full_clause+";]");[],
    ok(_, cursor, _) then _get_all(extractor)(cursor, [])
  }
.

public define List($T)
  get_all_by_full_clause
  (
    SQLite3DataBase db,
    String          full_clause,
    (One -> SQLite3Row table_cursor) -> Maybe($T)       extractor
  )=
  
  if sql_query_timeout(db, full_clause+";", [], "get_all_by_full_clause") is
  {
    error(sql_error) then println("get_all_by_full_clause SQL ERROR '"+ sql_error.text + "'\n full_clause ["+full_clause+";]");[],
    ok(_, cursor, _) then _get_all(extractor)(cursor, [])
  }
.

public define (String, String, String, String) -> List($T)
  call_back_get_all_by_clause
  (
    SQLite3DataBase   db,
    String            table_name,
    String            fields,
    (One -> SQLite3Row table_cursor) -> Maybe($T)       extractor
    //(One -> SQLite3Row table_cursor) -> List($T) list_extractor    
  )=
  with head = "SELECT "+fields+" FROM "+table_name,   //Construct the head of the SQL query
  with list_extractor = get_all(extractor),           //construct the call_back for the list of the type $T with the extractor
  (
    String            _join,
    String            _where,
    String            _order,
    String            _limit
  )|->
  with where = if _where = "" then "" else " WHERE "+_where,
       join  = if _join  = "" then "" else " JOIN "+_join,
       order = if _order = "" then "" else " ORDER BY "+_order,
 
  with final_clause = head + join + where + " "+ order + " " + _limit + ";",
  //println("call_back_get_all_by_clause Final clause ["+final_clause+"]");
  
  if sql_query_timeout(db, final_clause, [], "get_cursor_by_clause") is
  {
    error(_)         then [],
    ok(_, cursor, _) then list_extractor(cursor)
  }
.

public define (String, String, String, String) -> Maybe($T)
  call_back_get_one_by_clause
  (
    SQLite3DataBase   db,
    String            table_name,
    String            fields,
    (One -> SQLite3Row table_cursor) -> Maybe($T)       extractor
    //(One -> SQLite3Row table_cursor) -> List($T) list_extractor    
  )=
  with head = "SELECT "+fields+" FROM "+table_name,   //Construct the head of the SQL query
  (
    String            _join,
    String            _where,
    String            _order,
    String            _limit
  )|->
  with where = if _where = "" then "" else " WHERE "+_where,
       join  = if _join  = "" then "" else " JOIN "+_join,
       order = if _order = "" then "" else " ORDER BY "+_order,

  with final_clause = head + join + where + " "+ order + " " + _limit + ";",
  //println("call_back_get_one_by_clause Final clause ["+final_clause+"]");
  
  if sql_query_timeout(db, final_clause, [], "get_cursor_by_clause") is
  {
    error(_)         then failure,
    ok(_, cursor, _) then extractor(cursor)
  }
.

     relation_table
     id = id of that relation
     source_id = id of the first record in relation
     target_id = id of the second record in relation
     
     for example we search the all record targeted by source id
     
     relation_id | source_id | target_id
     -----------------------------------
     0           | 1         | 1
     1           | 1         | 2
     2           | 1         | 4
     3           | 0         | 2
     
     if we want to get all relation from source matching with id 1 we will get a list of ids [1, 2, 4]
     

public define List($T)
  get_relation
  (
    SQLite3DataBase db,
    String  relation_table,
    String  relation_source_id_col,
    String  relation_target_id_col,
    String  relation_condition,       //only the right part atfer = in SQL format. This means the SQL query will be constructed with "relation_source_id_col = " relation_condition
    String  foreign_order,
    (SQLite3DataBase db, String clause) -> List($T)       get_all_by_clause_to_list_type
  )=
  //get 
  with ids = get_ids_by_clause(db, relation_table, relation_target_id_col, relation_source_id_col+" = "+relation_condition),
  if length(ids) = 0 then 
    []
  else
    with the_clause = " id IN ("+join(", ", to_List_String(ids))+") "+foreign_order,
    get_all_by_clause_to_list_type(db, the_clause)
.

  /** Get relation by foreign table
     relation_table_to_f_table 
     get the relation from source_id 
     id = id of that relation
     source_id = id of the first record in relation
     f_table   = foreign table
     f_id      = id in foreign table for second leg of the relation
   */
public define List($T)
  get_relation_to_f_table
  (
    SQLite3DataBase db,
    String  relation_table,
    String  foreign_table_name,       //col f_table
    String  relation_condition,       //only the right part atfer = in SQL format. This means the SQL query will be constructed with "relation_source_id_col = " relation_condition
    String  foreign_order,
    (SQLite3DataBase db, String clause) -> List($T)       get_all_by_clause_to_list_type
  )=
  //get 
  with ids = get_ids_by_clause(db, relation_table, "f_id", "f_table = '"+foreign_table_name+"' AND fk_id = "+relation_condition),
  if length(ids) = 0 then 
    []
  else
    with the_clause = " id IN ("+join(", ", to_List_String(ids))+") "+foreign_order,
    get_all_by_clause_to_list_type(db, the_clause)
.

public define List($T)
  get_relation_from_f_table
  (
    SQLite3DataBase db,
    String  relation_table,
    String  foreign_table_name,       //col f_table
    String  relation_condition,       //only the right part atfer = in SQL format. This means the SQL query will be constructed with "relation_source_id_col = " relation_condition
    String  foreign_order,
    (SQLite3DataBase db, String clause) -> List($T)       get_all_by_clause_to_list_type
  )=
  //get 
  with ids = get_ids_by_clause(db, relation_table, "fk_id", "f_table = '"+foreign_table_name+"' AND f_id = "+relation_condition),
  if length(ids) = 0 then 
    []
  else
    with the_clause = " id IN ("+join(", ", to_List_String(ids))+") "+foreign_order,
    get_all_by_clause_to_list_type(db, the_clause)
.

public define Maybe($T)
  get_relation
  (
    SQLite3DataBase db,
    String  relation_table,
    String  relation_source_id_col,
    String  relation_target_id_col,
    String  relation_condition,       //only the right part atfer = in SQL format. This means the SQL query will be constructed with "relation_source_id_col = " relation_condition
    (SQLite3DataBase db, String clause) -> Maybe($T)       get_by_clause_to_mb_type
  )=
  //get 
  if get_mb_id_by_clause(db, relation_table, relation_target_id_col, relation_source_id_col+" = "+relation_condition) is
  {
    failure     then failure
    success(id) then get_by_clause_to_mb_type(db, "id = "+id)      
  }
.

public define Maybe($T)
  get_relation_to_f_table
  (
    SQLite3DataBase db,
    String  relation_table,           //table where is located the relation
    String  foreign_table_name,       //table name as filter for concerned relation
    String  relation_condition,       //only the right part atfer = in SQL format. This means the SQL query will be constructed with "relation_source_id_col = " relation_condition
    (SQLite3DataBase db, String clause) -> Maybe($T)       get_by_clause_to_mb_type
  )=

  if get_mb_id_by_clause(db, relation_table, "f_id", "f_table = '"+foreign_table_name+"' AND fk_id = "+relation_condition) is
  {
    failure     then failure
    success(id) then get_by_clause_to_mb_type(db, "id = "+id)      
  }
.

public define Maybe($T)
  get_relation_from_f_table
  (
    SQLite3DataBase db,
    String  relation_table,           //table where is located the relation
    String  foreign_table_name,       //table name as filter for concerned relation
    String  relation_condition,       //only the right part atfer = in SQL format. This means the SQL query will be constructed with "relation_source_id_col = " relation_condition
    (SQLite3DataBase db, String clause) -> Maybe($T)       get_by_clause_to_mb_type
  )=
  //get 
  if get_mb_id_by_clause(db, relation_table, "fk_id", "f_table = '"+foreign_table_name+"' AND f_id = "+relation_condition) is
  {
    failure     then failure
    success(id) then get_by_clause_to_mb_type(db, "id = "+id)      
  }
.

public define Maybe(Int)
  insert_or_update
  (
    SQLite3DataBase             db,
    String                      table_name,
    DB_id                       db_id,
    List(SQLite3_update_field)  fields
  )=
  if db_id is
  {
    none then
      since make_insert_clause_and_binds(fields) is (binds, insert_columns, insert_values),
      with insert_query = "INSERT INTO "+table_name+" "+insert_columns+" VALUES "+insert_values+";",
      if sql_query_timeout(db, insert_query, binds, table_name+" (INSERT)") is
      {
        error(sql_error)  then 
          println("insert on table '"+table_name+"' SQL ERROR '"+ sql_error.text + "'\n sql_insert ["+insert_query+"]\n binds "+dump_Binds(binds));
          failure,
        ok(_, cursor, _)  then 
          if sql_query_scalar(db, "SELECT last_insert_rowid()") is
          {
            error(_)  then  failure,
            ok(datum) then  
              with result1 = if datum is integer(n) then n else 0,
              println(table_name+" inserted id ["+result1+"]");
              success(result1)
          }
//    SQLite3DataBase        db, 
//    String                 sql_command
//  )        
//        success(unique)
      },
    db_id(idx) then
      since make_update_clause_and_binds(fields) is (binds, set_string),
      with update_query = "UPDATE "+table_name+" SET "+set_string +" WHERE id = "+idx+";",
      if sql_query_timeout(db, update_query, binds, table_name+" (UPDATE)") is
      {
        error(sql_error) then 
          println("update on table '"+table_name+"' id ["+idx+"] SQL ERROR '"+ sql_error.text + "'\n sql_insert ["+update_query+"]\n binds "+dump_Binds(binds));
          //logError(debug_log, db_error(sql_error, table_name));
          failure,
        ok(_, _, _)      then success(idx)
      }
  }
.

public define Maybe(One)
  update_with_clause
  (
    SQLite3DataBase             db,
    String                      table_name,
    String                      clause,
    List(SQLite3_update_field)  fields
  )=

  since make_update_clause_and_binds(fields) is (binds, set_string),
  if sql_query_timeout(db, "UPDATE "+table_name+" SET "+set_string +" "+clause+";", binds, table_name+" (UPDATE)") is
  {
    error(sql_error) then 
      //logError(debug_log, db_error(sql_error, table_name));
      failure,
    ok(_, _, _)      then success(unique)
  }
.