/*
Copyright (C) 2000-2010  Ministere de la culture et de la communication (France), AJLSM
See LICENCE file
 */
package fr.gouv.culture.sdx.utils.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import fr.gouv.culture.sdx.exception.SDXException;
import fr.gouv.culture.sdx.exception.SDXExceptionCode;

/**
 * An Oracle implementation of the SDX Database interface.
 * 
 * Tested with Oracle 10g under Windows. Should work with many Oracle versions.
 */
public class ORACLEDatabase extends JDBCDatabase {

    /**
     * Returns the CREATE TABLE statement.
     * 
     * For Oracle, we do not use a sequence, we define a PRIMARY KEY on the three
     * fields (ID, NAME, VALUE). We force NOT NULL because the three fields are
     * part of the PRIMARY KEY, and thus Oracle forces them to be not null anyway.
     */
    protected String getTableCreationQuery() {
        return "CREATE TABLE " + getTableName() + " ( " + FIELD_ID + " VARCHAR(255) NOT NULL, " + FIELD_PROPERTY_NAME + " VARCHAR(255) NOT NULL, " + FIELD_PROPERTY_VALUE + " VARCHAR(255) NOT NULL, "
                + "PRIMARY KEY (" + FIELD_ID + ", " + FIELD_PROPERTY_NAME + ", " + FIELD_PROPERTY_VALUE + "))";
    }

    /**
     * For performance reasons, we create an index on the ID field.
     */
    protected String getCreateIndiciesQuery(String fieldName) {
        return "CREATE INDEX " + getTableName() + "_" + fieldName + " ON " + getTableName() + " (" + fieldName + ")";
    }

    /**
     * Initialise l'entrepot. Si la table n'existe pas, elle est creee.
     * 
     * On surcharge cette methode car Oracle reagit bizarrement au
     * DatabaseMetaData. On fait simplement une requete.
     */
    public void init() throws SDXException {

        // La connexion et le resultset
        DatabaseConnection sqlDbConn = getConnection();
        Connection conn = null;
        ResultSet rs = null;
        try {
            // On essaie de recuperer un enregistrement
            conn = sqlDbConn.getConnection();
            Statement st = conn.createStatement();
            rs = st.executeQuery("SELECT * FROM " + getTableName() + " WHERE " + FIELD_ID + " = '_____'");
        } 
        catch (SQLException e) {
            // S'il y a exception, c'est probablement parce que la table n'existe pas
            // On va la creer, si ce n'etait pas le probleme alors tant pis
            createTable(conn);
        }
        finally {
            // On detruit nos objets de connexion
            if (rs != null) {
                try {
                    rs.close();
                }
                catch (SQLException e) {
                    String[] args = new String[2];
                    args[0] = getId();
                    args[1] = e.getMessage();
                    throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_CLOSE_RESULT_SET, args, e);
                }
            }
            if ( sqlDbConn != null ) {
                sqlDbConn.commit();
                releaseConnection(sqlDbConn);
            }
        }
    }

    /**
     * Creates the index on the ID field.
     */
    protected void createIndicies(Connection conn) throws SDXException {
        PreparedStatement st = null;
        try {
            st = conn.prepareStatement(getCreateIndiciesQuery(FIELD_ID));
            st.clearParameters();
            st.execute();
        }
        catch (SQLException e) {
            String[] args = new String[2];
            args[0] = this.getTableName();
            args[1] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_CREATE_INDEX, args, e);
        }
        finally {
            try {
                if ( st != null ) st.close();
            }
            catch (SQLException e) {}
        }
    }

    /**
     * We slightly modify the default behaviour to insert a dummy property when
     * an entity has no property defined. In the normal code, an empty string is
     * used, but the Oracle JDBC driver inserts null instead of empty spaces, thus
     * violating the primary key constraints (no null) on the three fields.
     */
    public synchronized void save(DatabaseEntity ent) throws SDXException {
        // Let the super method handle null entity
        if ( ent == null ) super.save(ent);
        else {
            // If not properties, then we add en "empty" one
            Property[] props = ent.getProperties();
            if ( props == null || props.length == 0 ) {
                ent.addProperty("_", "_");
            }
            // And we let the super method handle it
            super.save(ent);
        }
    }
}
