package fr.ifremer.quadrige3.core.dao.technical.jdbc;
   
   /*-
    * #%L
    * Quadrige3 Core :: Shared
    * %%
    * Copyright (C) 2017 - 2018 Ifremer
    * %%
    * This program is free software: you can redistribute it and/or modify
   * it under the terms of the GNU Affero General Public License as published by
   * the Free Software Foundation, either version 3 of the License, or
   * (at your option) any later version.
   * 
   * This program is distributed in the hope that it will be useful,
   * but WITHOUT ANY WARRANTY; without even the implied warranty of
   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   * GNU General Public License for more details.
   * 
   * You should have received a copy of the GNU Affero General Public License
   * along with this program.  If not, see <http://www.gnu.org/licenses/>.
   * #L%
   */
  import java.sql.CallableStatement;
  import java.sql.Connection;
  import java.sql.SQLException;
  
  public class OracleStatements {
  
      private static final String DISABLE_ALL_CONSTRAINTS_SQL = "BEGIN\n"+
              "  FOR c IN\n"+
              "  (SELECT c.owner, c.table_name, c.constraint_name\n"+
              "   FROM user_constraints c, user_tables t\n"+
              "   WHERE c.table_name = t.table_name\n"+
              "   AND c.status = 'ENABLED'\n" +
              "   AND c.constraint_type IN ('P', 'R')\n"+
              "   AND NOT (t.iot_type IS NOT NULL AND c.constraint_type = 'P')\n"+
              "   ORDER BY c.constraint_type DESC)\n"+
              "  LOOP\n"+
              "    dbms_utility.exec_ddl_statement('alter table \"' || c.owner || '\".\"' || c.table_name || '\" disable constraint ' || c.constraint_name);\n"+
              "  END LOOP;\n"+
              "END;";
  
      private static final String ENABLE_ALL_CONSTRAINTS_SQL = "BEGIN\n" +
              "  FOR c IN\n" +
              "  (SELECT c.owner, c.table_name, c.constraint_name\n" +
              "   FROM user_constraints c, user_tables t\n" +
              "   WHERE c.table_name = t.table_name\n" +
              "   AND c.status = 'DISABLED'\n" +
              "   AND c.constraint_type IN ('P', 'R')\n" +
              "   ORDER BY c.constraint_type)\n" +
              "  LOOP\n" +
              "    dbms_utility.exec_ddl_statement('alter table \"' || c.owner || '\".\"' || c.table_name || '\" enable constraint ' || c.constraint_name);\n" +
              "  END LOOP;\n" +
              "END;";
  
      //Disable all delete triggers (need to load database for tests
      private static final String DISABLE_DELETE_TRIGGERS_SQL = "DECLARE\n" +
              "  CURSOR cursorTriggerNames is\n" +
              "    select\n" +
              "      TRIGGER_NAME\n" +
              "    from\n" +
              "      user_triggers\n" +
              "    where\n" +
              "      trigger_name like 'TBD_%'\n" +
              "      and STATUS = 'ENABLED';\n" +
              "  cur integer;\n" +
              "  rc  integer;\n" +
              "  triggerName VARCHAR2(100);\n" +
              "  sqlQuery VARCHAR2(2000);\n" +
              "BEGIN\n" +
              "  cur := DBMS_SQL.OPEN_CURSOR;\n" +
              "  DBMS_OUTPUT.PUT_LINE('Disabling delete triggers...');\n" +
              "  for cursorTriggerName in cursorTriggerNames loop\n" +
              "      triggerName := cursorTriggerName.TRIGGER_NAME;\n" +
              "      DBMS_OUTPUT.PUT_LINE('Disable trigger '''|| triggerName ||'''');\n" +
              "      sqlQuery := 'ALTER TRIGGER '||triggerName||' DISABLE';\n" +
              "      DBMS_SQL.PARSE(cur, sqlQuery, DBMS_SQL.NATIVE);\n" +
              "      rc := DBMS_SQL.EXECUTE(cur);\n" +
              "    end loop;\n" +
              "  DBMS_OUTPUT.PUT_LINE('Disabling delete triggers... [OK]');\n" +
              "  COMMIT;\n" +
              "  DBMS_SQL.CLOSE_CURSOR(cur);\n" +
              "END;";
  
      // Enable all delete triggers (need to load database for tests)
      private static final String ENABLE_DELETE_TRIGGERS_SQL = "DECLARE\n" +
              "  CURSOR cursorTriggerNames is\n" +
              "    select\n" +
              "      TRIGGER_NAME\n" +
              "    from\n" +
              "      user_triggers\n" +
              "    where\n" +
              "      trigger_name like 'TBD_%'\n" +
              "      and STATUS = 'DISABLED';\n" +
              "  cur integer;\n" +
              "  rc  integer;\n" +
              "  triggerName VARCHAR2(100);\n" +
              "  sqlQuery VARCHAR2(2000);\n" +
              "BEGIN\n" +
             "  cur := DBMS_SQL.OPEN_CURSOR;\n" +
             "  DBMS_OUTPUT.PUT_LINE('Enabling delete triggers...');\n" +
             "  for cursorTriggerName in cursorTriggerNames loop\n" +
             "      triggerName := cursorTriggerName.TRIGGER_NAME;\n" +
             "      DBMS_OUTPUT.PUT_LINE('Enable trigger '''|| triggerName ||'''');\n" +
             "      sqlQuery := 'ALTER TRIGGER '||triggerName||' ENABLE';\n" +
             "      DBMS_SQL.PARSE(cur, sqlQuery, DBMS_SQL.NATIVE);\n" +
             "      rc := DBMS_SQL.EXECUTE(cur);\n" +
             "    end loop;\n" +
             "  DBMS_OUTPUT.PUT_LINE('Enabling delete triggers... [OK]');\n" +
             "  COMMIT;\n" +
             "  DBMS_SQL.CLOSE_CURSOR(cur);\n" +
             "END;";
 
     public static void setIntegrityConstraints(Connection connection, boolean enableIntegrityConstraints) throws SQLException{
 
         // Disable
         if (!enableIntegrityConstraints) {
             executeProcedure(connection, DISABLE_ALL_CONSTRAINTS_SQL);
         }
 
         // Enable
         else {
             executeProcedure(connection, ENABLE_ALL_CONSTRAINTS_SQL);
         }
     }
 
     public static void setEnableDeleteTriggers(Connection connection, boolean enableDeleteTriggers) throws SQLException{
 
         // Disable
         if (!enableDeleteTriggers) {
             executeProcedure(connection, DISABLE_DELETE_TRIGGERS_SQL);
         }
 
         // Enable
         else {
             executeProcedure(connection, ENABLE_DELETE_TRIGGERS_SQL);
         }
     }
 
     private static void executeProcedure(Connection connection, String procedure) throws SQLException {
         CallableStatement stat = connection.prepareCall(procedure);
         stat.executeUpdate();
     }
 }