/*
 * Created by PyramIDE.
 * User: ricard
 * Date: 03/12/2008
 * Time: 13:07
 * 
 */

read tools/basis.anubis
read system/logger.anubis
read data_base/sqlite.anubis
read data_base/alter_table.anubis //for sqlite3 version of alter_table
read data_base/db_types.anubis    //FK...
read data_base/sqlite_foreign_key.anubis

read calexium_lib/database/db_utils.anubis
read calexium_lib/net_services_protocols/logger_service.anubis

 read ezmailbox_constants.anubis


public type DbTable:
  table(String name, String create_query),
  table(String db_name, String name, String create_query),
  view(String name, String create_query).
  

public define Bool
  is_table_exists
  (
    SQLite3DataBase db,
    String          dbName,
    String          table_name
  )=
  if sql_query_timeout(db, "SELECT * FROM " + dbName + ".sqlite_master WHERE type ='table' AND tbl_name =:table_name", [bind_String(":table_name", table_name)], "is_table_exists("+table_name+")") is
  {
    error(_)         then false,  //error in the SQL request
    ok(_, cursor, _) then
      if cursor(unique) is 
      {
        error(_)      then false,
        no_more_row   then false,
        row(current)  then true
      }
  }.

public define Bool
  is_table_exists
  (
    SQLite3DataBase db,
    String          table_name
  )=
  is_table_exists(db, "main", table_name).

public define Bool
  is_empty_database
  (
    SQLite3DataBase db,
    String          dbName,
  )=
  if sql_query_timeout(db, "SELECT * FROM " + dbName + ".sqlite_master", [], "is_empty_database("+dbName+")") is
  {
    error(_)         then true,  //error in the SQL request
    ok(_, cursor, _) then
      if cursor(unique) is 
      {
        error(_)      then true,
        no_more_row   then true,
        row(current)  then false
      }
  }.

public define Bool
  is_empty_database
  (
    SQLite3DataBase db,
  )=
  is_empty_database(db, "main").

public define Maybe(One)
  drop_all_triggers
  (
    SQLite3DataBase db,
    Logger          log
  ) =
  if sqlite3_query(db, "SELECT name FROM sqlite_master WHERE type = 'trigger'", []) is
  {
    error(err) then logError(log, db_error(err, "drop_all_triggers")); failure,
    ok(_, cursor, _) then 
      with drop_trigger = (String name) |->   if sqlite3_query(db, "DROP TRIGGER " + name, []) is
                                              {
                                                error(err2) then logError(log, db_error(err2, "Failed to drop trigger '" + name + "'")),
                                                ok(_, cursor, _) then unique
                                              },
      map_forget(drop_trigger, db_get_string_list(cursor, []));
      success(unique)
  }.

public define Maybe(One)
  create_tables
  (
    SQLite3DataBase       db,
    Logger                logger,
    List(DbTable)         tables,
    String                version_string
  ) = 
  if tables is 
  {
    [] then success(unique),
    [h . t] then
      if h is
      {
        table(table_name, create_string) then
          if alter_table(db, table_name, create_string, logger) is
          {
            failure     then logError(logger, "Version "+version_string+": Can't create '"+table_name+"' table"); failure,
            success(_)  then create_tables(db, logger, t, version_string)
          },
        table(db_name, table_name, create_string) then
          if alter_table(db, db_name, table_name, create_string, logger) is
          {
            failure     then logError(logger, "Version "+version_string+": Can't create '"+db_name+"."+table_name+"' table"); failure,
            success(_)  then create_tables(db, logger, t, version_string)
          },
        view(view_name, create_string) then
          if alter_view(db, view_name, create_string, logger) is
          {
            failure     then logError(logger, "Version "+version_string+": Can't create '"+view_name+"' view"); failure,
            success(_)  then create_tables(db, logger, t, version_string)
          },
      }
  }.


public define Maybe(One)
  create_indexes
  (
    SQLite3DataBase db,
    Logger          logger,
    List(String)    indexes,
    String          version_string
  )=
  if indexes is
  {
    []      then success(unique),
    [h . t] then
      if sql_query_timeout(db, h, [], "Version "+version_string+": create indexes") is
      {
        error(_)   then failure,
        ok(_,_,_)  then create_indexes(db, logger, t, version_string)
      }
  }.


public define One
  make_foreign_key
  (
    SQLite3DataBase db,
    String table_name,
    String field_name,
    String foreign_table_name,
    String foreign_field_name,
    Bool   fk_null,
    Bool   fk_cascade,
    Logger log
  ) =
  if (Maybe(One))make_foreign_key(db, table_name, field_name, foreign_table_name, foreign_field_name, fk_null, fk_cascade, log) is 
  {
    failure     then 
      logError(log, "Can't create foreign key constraint between '"+table_name+"' and '"+foreign_table_name+"' tables"),
    success(_)  then 
      unique
  }.
    
public define Maybe(One)
  make_foreign_keys
  (
    SQLite3DataBase db,
    Logger          logger,
    List((String, String, String, String, FK_Null, FK_Cascade)) relations,
    String          version_string
  )=
  if relations is
  {
    [] then success(unique),
    [h . t] then
      if h is (table_name, field_name, foreign_table_name, foreign_field_name, fk_null, fk_cascade) then
      if (Maybe(One))make_foreign_key(db, table_name, field_name, foreign_table_name, foreign_field_name, fk_null, fk_cascade, logger) is 
      {
        failure     then 
          logError(logger, "Version "+version_string+": Can't create foreign key constraint between '"+table_name+"' and '"+foreign_table_name+"' tables"); failure,
        success(_)  then 
          make_foreign_keys(db, logger, t, version_string)
      }
  }.