*Project*                                   Anubis
   
 *Title*                            SQLite Foreign Key functions
   
 *Copyright*                     Copyright (c) Cédric Ricard 2007. 

   
   
 *Author*       Cédric Ricard

 *Created*      2007 12 21
 *Satus*        Released
 *Compatibility* 1.8.4

read tools/basis.anubis
read data_base/sqlite.anubis
read data_base/db_tools.anubis
read system/logger.anubis


  
define Maybe(One)
  my_sql_query
  (
    Database        db,
    String sql,
    Logger log
  ) =
  if sql_query(db, sql) is 
  {
    error(sql_error)   then logError(log, "SQL ERROR: " + sql_error.text + "\n   ...executing query [" + sql + "]"); failure,
    ok(_,_,_)             then success(unique)
  }.
  
public define Maybe(One)
  alter_trigger
  (
    Database        db,
    String          trigger_name,
    String          sql,
    Logger          log
  ) =
  if sql_query(db, "SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = " + db_make_sql_string(trigger_name))  is
  {
    error(sql_error)   then logError(log, "SQL ERROR: " + sql_error.text + "\n   ...looking for trigger named [" + trigger_name + "]"); failure,
    ok(_,cursor,_) then
      if cursor(unique) is
      {
        error(sql_error)      then logError(log, "alter_trigger ERROR '" + sql_error.text + "'"); failure,
        no_more_row           then my_sql_query(db, sql, log),
        row(current)          then 
          with old_sql = text(current)(0),
          if old_sql /= sql then
            logDebug(log, "Old trigger was:\n" + old_sql + "\n");
            logDebug(log, "New trigger is:\n" + sql + "\n");
            logInfo(log, "Updating trigger '" + trigger_name + "'...");
            forget(my_sql_query(db, "DROP TRIGGER [" + trigger_name + "]", log));
            if my_sql_query(db, sql, log) is success(_) then logInfo (log, "  --> ok."); success(unique)
                                                        else logError(log, "  --> error!"); failure
          else
            success(unique)
      }
  }.


public type FK_Null:
  null,
  not_null.
  
public type FK_Cascade:
  delete_in_cascade,
  block_if_children.
  
public define Maybe(One)
  make_foreign_key
  (
    Database        db,
    String          table_name,
    String          field_name,
    String          foreign_table_name,
    String          foreign_field_name,
    FK_Null         fk_null,
    FK_Cascade      fk_cascade,
    Logger          log
  ) =
  with constrait_name = table_name + "__" + field_name + "__" + foreign_table_name,
       sql_create =
              "CREATE TRIGGER [fki__" + constrait_name + "] \n"
            + "BEFORE INSERT ON [" + table_name + "] \n"
            + "FOR EACH ROW BEGIN \n"
            + "    SELECT RAISE(ROLLBACK, 'insert on table \"" + table_name + "\" violates foreign key constraint \"fki__" + constrait_name + "\"') \n"
            + "    WHERE  " + (if fk_null is null then " NEW.[" + field_name + "] IS NOT NULL AND \n" else "\n")
            + "           (SELECT [" + foreign_field_name + "] FROM [" + foreign_table_name + "] WHERE [" + foreign_field_name + "] = NEW.[" + field_name + "]) IS NULL; \n"
            + "END",
       sql_update =
              "CREATE TRIGGER [fku__" + constrait_name + "] \n"
            + "BEFORE UPDATE ON [" + table_name + "] \n"
            + "FOR EACH ROW BEGIN \n"
            + "    SELECT RAISE(ROLLBACK, 'update on table \"" + table_name + "\" violates foreign key constraint \"fku__" + constrait_name + "\"') \n"
            + "    WHERE  " + (if fk_null is null then " NEW.[" + field_name + "] IS NOT NULL AND \n" else "\n")
            + "           (SELECT [" + foreign_field_name + "] FROM [" + foreign_table_name + "] WHERE [" + foreign_field_name + "] = NEW.[" + field_name + "]) IS NULL; \n"
            + "END",
       sql_delete =
              "CREATE TRIGGER [fkd__" + constrait_name + "] \n"
            + "BEFORE DELETE ON [" + foreign_table_name + "] \n"
            + "FOR EACH ROW BEGIN \n"
            + if fk_cascade is {
                                  delete_in_cascade then 
                                    "    DELETE from [" + table_name + "] WHERE [" + field_name + "] = OLD.[" + foreign_field_name + "];\n",
                                  block_if_children then
                                    "    SELECT RAISE(ROLLBACK, 'delete on table \"" + foreign_table_name + "\" violates foreign key constraint \"fku__" + constrait_name + "\"') \n"
                                    + "    WHERE  (SELECT [" + field_name + "] FROM [" + table_name + "] WHERE [" + field_name + "] = OLD.[" + foreign_field_name + "]) IS NOT NULL; \n"
                                }
            + "END",
  if alter_trigger(db, "fki__" + constrait_name, sql_create, log)  is failure 
    then failure 
  else if alter_trigger(db, "fku__" + constrait_name, sql_update, log)  is failure 
    then failure 
  else if alter_trigger(db, "fkd__" + constrait_name, sql_delete, log)  is failure 
    then failure 
  else 
    success(unique).


  
public define Maybe(One)
  make_foreign_key
  (
    Database        db,
    String table_name,
    String field_name,
    String foreign_table_name,
    String foreign_field_name,
    Bool   field_can_be_null,
    Bool   delete, //delete_in_cascade ?
    Logger log
  ) =
  make_foreign_key
  (
    db,
    table_name,
    field_name,
    foreign_table_name,
    foreign_field_name,
    if field_can_be_null then null else not_null,
    if delete then delete_in_cascade else block_if_children,
    log
  ).