//

 // Copyright (c) 2004, International Decision Systems

 // all rights reserved

 //

 package net.sourceforge.addam.ddlgen.oracle;

 import java.io.*;

 import java.sql.*;

 import java.util.List;

 import java.util.ArrayList;

 import net.sourceforge.addam.ddlgen.Filter;

 import net.sourceforge.addam.ddlgen.Generator;

 /**

  * Uses DBMS_METADATA to generate the script to recreate the object.

  *

  * @author TIM3

  * @since Oct 12, 2005

  */

 public abstract class DBMSMetadataGenerator implements Generator {

     public String objectsSQL() {

         return "select object_name from all_objects where owner=? and object_type=?";

     }

     public abstract String objectType();

     public List<String> getObjects(String catalog, String schema, Filter filter,

                                    Connection connection, DatabaseMetaData metadata) throws Exception {

         return getObjects(catalog, schema, objectType(), filter, connection, metadata, objectsSQL(), schema, objectType());

     }

     protected List<String> getObjects(String catalog, String schema, String objectType, Filter filter,

                                    Connection connection, DatabaseMetaData metadata, String objectsSQL, String... args) throws Exception {

         List<String> objects = new ArrayList<String>();

         PreparedStatement statement = null;

         ResultSet resultSet = null;

         try {

             statement = connection.prepareStatement(objectsSQL);

             int index=1;

             for (String arg : args) {

                 statement.setString(index++, arg);

             }

             resultSet = statement.executeQuery();

             while (resultSet.next()) {

                 String dbObject = resultSet.getString(1);

                 if (filter.includes(dbObject)) {

                     objects.add(dbObject);

                 }

             }

         } finally {

             if (resultSet != null) resultSet.close();

             if (statement != null) statement.close();

         }

         return objects;

     }

     public void generate(String catalog, String schema, String object,

                          String vendor, Connection connection, DatabaseMetaData metadata, BufferedWriter writer)

             throws Exception {

         generate(catalog, schema, objectType(), object, vendor, connection, metadata, writer);

     }

     protected void generate(String catalog, String schema, String objectType, String object,

                          String vendor, Connection connection, DatabaseMetaData metadata, BufferedWriter writer)

             throws Exception {

         if (metadata.storesUpperCaseIdentifiers()) {

             schema = (schema == null) ? null : schema.toUpperCase();

             object = (object == null) ? null : object.toUpperCase();

         } else if (metadata.storesLowerCaseIdentifiers()) {

             schema = (schema == null) ? null : schema.toLowerCase();

             object = (object == null) ? null : object.toLowerCase();

         }

         PreparedStatement statement = null;

         ResultSet resultSet = null;

         try {

             setTransformParam(connection,"PRETTY",true);

             setTransformParam(connection,"SQLTERMINATOR",true);

             statement = connection.prepareStatement("select dbms_metadata.get_ddl(?,?,?) from dual");

             statement.setString(1,objectType);

             statement.setString(2,object);

             statement.setString(3,schema);

             resultSet = statement.executeQuery();

             while (resultSet.next()) {

                 Clob clob = resultSet.getClob(1);

                 Reader clobReader = clob.getCharacterStream();

                 BufferedReader reader = new BufferedReader(clobReader);

                 for (String line = reader.readLine(); line != null; line = reader.readLine() ) {

                     line = line.replace("\"" + schema + "\".","");

                     writeLine(line,writer);

                 }

             }

             // put everything back the way it was

             setDefaultTransformParams(connection);

         } finally {

             if (resultSet != null) resultSet.close();

             if (statement != null) statement.close();

         }

     }

     protected void writeLine(String line, BufferedWriter writer) throws IOException {

         writer.write(line);

         writer.newLine();

         writer.flush();

     }

     protected void setTransformParam(Connection connection, String param, boolean value) throws SQLException {

         PreparedStatement statement = null;

         try {

             statement = connection.prepareCall("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,?,?)}");

             statement.setString(1,param);

             statement.setBoolean(2,value);

             statement.execute();

         } finally {

             if (statement != null) statement.close();

         }

     }

     protected void setDefaultTransformParams(Connection connection) throws SQLException {

         PreparedStatement statement = null;

         try {

             statement = connection.prepareCall("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT',true)}");

             statement.execute();

         } finally {

             if (statement != null) statement.close();

         }

     }

 }