package org.intermine.bio.postprocess;

/*
 * Copyright (C) 2002-2014 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 org.intermine.objectstore.ObjectStoreWriter;
import org.intermine.objectstore.intermine.ObjectStoreWriterInterMineImpl;
import org.intermine.sql.Database;

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

/**
 * A task the replace the sequencefeatureoverlappingfeatures table with a view that uses the
 * bioseg type to calculate the overlaps.
 * @author Kim Rutherford
 */
public class OverlapViewTask
{
    protected ObjectStoreWriterInterMineImpl osw;

    /**
     * Construct a new OverlapViewTask that will change the given object store.
     * @param osw an ObjectStore to write to
     */
    public OverlapViewTask(ObjectStoreWriter osw) {
        if (osw instanceof ObjectStoreWriterInterMineImpl) {
            this.osw = (ObjectStoreWriterInterMineImpl) osw;
        } else {
            throw new RuntimeException("the ObjectStoreWriter is not an "
                                       + "ObjectStoreWriterInterMineImpl");
        }
    }

    /**
     * Drop the sequencefeatureoverlappingfeatures table and replace it with a view that
     * uses the bioseg type to calculate the overlaps.
     * @throws SQLException if there is a problem dropping the table or creating the view
     */
    public void createView() throws SQLException {
        Database db = this.osw.getDatabase();
        Connection con = db.getConnection();

        con.setAutoCommit(false);

        String dropSql = "DROP VIEW overlappingfeaturessequencefeature";
        String viewSql =
            "CREATE VIEW overlappingfeaturessequencefeature "
            + " AS SELECT l1.featureid AS overlappingfeatures, "
            + "           l2.featureid AS sequencefeature "
            + "      FROM location l1, location l2 "
            + "     WHERE l1.locatedonid = l2.locatedonid "
            + "       AND l1.featureid != l2.featureid"
            + "       AND bioseg_create(l1.intermine_start, l1.intermine_end) "
            + "              && bioseg_create(l2.intermine_start, l2.intermine_end)";
       
        String viewSqlRange =
                "CREATE VIEW overlappingfeaturessequencefeature "
                + " AS SELECT l1.featureid AS overlappingfeatures, "
                + "           l2.featureid AS sequencefeature "
                + "      FROM location l1, location l2 "
                + "     WHERE l1.locatedonid = l2.locatedonid "
                + "       AND l1.featureid != l2.featureid"
                + "       AND l1.intermine_locrange && l2.intermine_locrange";
        Statement statement = con.createStatement();
        statement.executeUpdate(dropSql);
        statement.close();

        statement = con.createStatement();
        statement.executeUpdate(viewSql);
        statement.close();

        con.commit();
        con.close();
    }
}

@@ -1842,24 +1842,93 @@
      String 
+viewSql;%0A%0A        if (osw.getSchema().useRangeTypes()) %7B%0A            
 viewSql =%0A  
@@ -1929,32 +1929,40 @@
 l =%0A            
+  
+      
 %22CREATE VIEW ove
@@ -1999,32 +1999,48 @@
 e %22%0A            
+      
+          
 + %22 AS SELECT l1
@@ -2068,32 +2068,48 @@
 pingfeatures, %22%0A
+                
             + %22 
@@ -2156,32 +2156,48 @@
 e %22%0A            
+         
+       
 + %22      FROM lo
@@ -2213,32 +2213,48 @@
 , location l2 %22%0A
+                
             + %22 
@@ -2289,32 +2289,48 @@
 2.locatedonid %22%0A
+                
             + %22 
@@ -2373,32 +2373,48 @@
 id%22%0A            
+        
+        
 + %22       AND bi
@@ -2411,28 +2411,24 @@
     AND 
-bioseg_creat
+int4rang
 e(l1.int
@@ -2461,16 +2461,32 @@
 _end) %22%0A
+                
         
@@ -2513,20 +2513,16 @@
  && 
-bioseg_creat
+int4rang
 e(l2
@@ -2566,16 +2566,58 @@
 %0A       
+ %7D else if (osw.getSchema().hasBioSeg()) %7B
 %0A       
@@ -2617,30 +2617,27 @@
         
-String
+   
  viewSql
 Range =%0A
@@ -2632,16 +2632,15 @@
 wSql
-Range =%0A
+ =%0A    
     
@@ -2709,32 +2709,44 @@
                 
+     
+       
 + %22 AS SELECT l1
@@ -2790,32 +2790,44 @@
                 
+     
+       
 + %22           l2
@@ -2866,32 +2866,44 @@
                 
+     
+       
 + %22      FROM lo
@@ -2919,32 +2919,44 @@
 , location l2 %22%0A
+            
                 
@@ -3011,32 +3011,44 @@
                 
+    
+        
 + %22       AND l1
@@ -3083,32 +3083,44 @@
                 
+            
 + %22       AND l1
@@ -3121,56 +3121,449 @@
 AND 
-l1.intermine_locrange && l2.intermine_locrange%22;
+bioseg_create(l1.intermine_start, l1.intermine_end) %22%0A                            + %22            && bioseg_create(l2.intermine_start, l2.intermine_end)%22;%0A        %7D else %7B%0A            throw new IllegalArgumentException(%22Attempt to create overlappingfeatures view but%22%0A                    + %22 database doesn't support Postgres built in ranges (has to be %3E 9.2%22%0A                    + %22 and doesn't have bioseg installed. Aborting.%22);%0A        %7D%0A
 %0A   

Use postgres range type for overlapping features index if available, otherwise bioseg or exception.