package org.intermine.bio.postprocess;

/*
 * Copyright (C) 2002-2015 FlyMine
 *
 * This code may be freely distributed and modified under the
 * terms of the GNU Lesser General Public Licence.  This should
 * be distributed with the code.  See the LICENSE file for more
 * information or http://www.gnu.org/copyleft/lesser.html.
 *
 */

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

import org.apache.log4j.Logger;
import org.intermine.objectstore.ObjectStoreWriter;
import org.intermine.objectstore.intermine.ObjectStoreWriterInterMineImpl;
import org.intermine.sql.Database;


/**
* Create an index on the location table to be used for range queries. If Postgres is version 9.2 or
* later this will use the built-in int4range type, otherwise it will use BioSeg if installed. If
* neither of these conditions are met an exception will be thrown.
* @author Richard Smith
*
*/
public class CreateLocationOverlapIndex
{
    protected ObjectStoreWriterInterMineImpl osw;
    private static final String RANGE_TYPE = "int4range";
    private static final Logger LOG = Logger.getLogger(CreateLocationOverlapIndex.class);

    /**
     * Construct a new CreateLocationRange
     *
     * @param osw
     *            an ObjectStore to fetch database from
     */
    public CreateLocationOverlapIndex(ObjectStoreWriter osw) {
        if (osw instanceof ObjectStoreWriterInterMineImpl) {
            this.osw = (ObjectStoreWriterInterMineImpl) osw;
        } else {
            throw new RuntimeException("The ObjectStoreWriter is not an "
                                       + "ObjectStoreWriterInterMineImpl");
        }
    }

    /**
     * Create an int4range or bioseg index on the start and end columns of the of the location
     * table. If Postgres is of version 9.3 or later this will be an SPGIST index, otherwise GIST.
     *
     * @throws SQLException
     *             if commands fail
     */
    public void create() throws SQLException {
        Database db = this.osw.getDatabase();

        if (osw.getSchema().useRangeTypes()) {
            Connection con = db.getConnection();
            try {
                // SPGIST indexes are fastest for range queries but only added support for
                // ranges in 9.3, for older versions we need to use GIST
                String indexType = db.isVersionAtLeast("9.3") ? "SPGIST" : "GIST";

                long startTime = System.currentTimeMillis();
                String indexSql = "CREATE INDEX location__int4range "
                        + "ON location USING " + indexType + " (" + RANGE_TYPE
                        + "(intermine_start, intermine_end))";
                LOG.info(indexSql);
                Statement statement = con.createStatement();
                statement.executeUpdate(indexSql);
                statement.close();
                long took = System.currentTimeMillis() - startTime;
                LOG.info("Created " + RANGE_TYPE + " index on location, took: " + took + "ms.");
            } catch (SQLException e) {
                LOG.info("Index location__" + RANGE_TYPE + " index already existed.");
            } finally {
                con.close();
            }
        } else if (osw.getSchema().hasBioSeg()) {
            // We have bioseg but don't support built-in range queries

            Connection con = db.getConnection();
            try {
                con.setAutoCommit(false);

                String indexSql = "CREATE INDEX location__bioseg ON location "
                    + "USING gist (bioseg_create(intermine_start, intermine_end))";

                Statement statement = con.createStatement();
                statement.executeUpdate(indexSql);
                statement.close();

                con.commit();
            } finally {
                con.close();
            }
        } else {
            throw new IllegalArgumentException("Attempt to create index for range queries on"
                    + " location table but database doesn't support Postgres built in ranges (has"
                    + " to be > 9.2) and doesn't have bioseg installed. Aborting.");
        }
    }
}

@@ -355,24 +355,51 @@
 Connection;%0A
+import java.sql.ResultSet;%0A
 import java.
@@ -412,24 +412,24 @@
 LException;%0A
-
 import java.
@@ -2058,24 +2058,26 @@
 atabase();%0A%0A
+%0A%0A
         if (
@@ -2152,32 +2152,33 @@
 etConnection();%0A
+%0A
             try 
@@ -2171,32 +2171,190 @@
           try %7B%0A
+                if (hasIndexAlready(con)) %7B%0A                    // index was already created during this build%0A                    return;%0A                %7D%0A%0A
                 
@@ -3265,76 +3265,138 @@
     
-LOG.info(%22Index location__%22 + RANGE_TYPE + %22 index already existed.%22
+throw new SQLException(%22Failed to create location__%22 + RANGE_TYPE%0A                        + %22. You likely have bad locations. %22, e
 );%0A 
@@ -4393,24 +4393,24 @@
 borting.%22);%0A
-
         %7D%0A  
@@ -4412,11 +4412,571 @@
  %7D%0A    %7D
+%0A%0A    private boolean hasIndexAlready(Connection con) throws SQLException %7B%0A        final String sql = %22SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = %22%0A                + %22c.relnamespace WHERE c.relname = 'location__int4range' %22%0A                + %22AND n.nspname = 'public'%22;%0A        Statement statement = con.createStatement();%0A        ResultSet res = statement.executeQuery(sql);%0A        // true if index is present, false if this query returned no rows%0A        boolean hasIndex = res.next();%0A        statement.close();%0A        return hasIndex;%0A    %7D
 %0A%7D%0A

throw exception if creating overlap indexes fails. This is a fatal error. Refs #1095