package org.intermine.web.task;

/*
 * Copyright (C) 2002-2011 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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.tools.ant.BuildException;
import org.apache.tools.ant.Task;
import org.intermine.api.tracker.util.TrackerUtil;
import org.intermine.objectstore.ObjectStore;
import org.intermine.objectstore.ObjectStoreException;
import org.intermine.objectstore.ObjectStoreWriter;
import org.intermine.objectstore.ObjectStoreWriterFactory;
import org.intermine.objectstore.intermine.ObjectStoreInterMineImpl;
import org.intermine.sql.DatabaseUtil;

/**
 * Task to read an templatetrack XML file of a webapp and saved into the userprofile database.
 *
 * @author dbutano
 */

public class UpdateTrackTableTask extends Task
{
    private String userProfileAlias;

    /**
     * Set the alias of the userprofile object store.
     * @param userProfileAlias the object store alias of the userprofile database
     */
    public void setUserProfileAlias(String userProfileAlias) {
        this.userProfileAlias = userProfileAlias;
    }

    /**
     * Execute the task - read the profiles.
     * @throws BuildException if there is a problem while reading from the file or writing to the
     * profiles.
     */
    public void execute() {

        if (userProfileAlias == null) {
            throw new BuildException("userProfileAlias parameter not set");
        }
        ObjectStoreWriter userProfileOS = null;
        Connection connection = null;
        try {
            userProfileOS =
                ObjectStoreWriterFactory.getObjectStoreWriter(userProfileAlias);
            connection = ((ObjectStoreInterMineImpl) userProfileOS).getDatabase().getConnection();
            if (!verifyTrackColumnType(connection, TrackerUtil.TEMPLATE_TRACKER_TABLE)) {
                Statement stm = connection.createStatement();
                String sql1 = "ALTER TABLE templatetrack ADD COLUMN timestamp_backup bigint";
                stm.executeUpdate(sql1);
                String sql2 = "UPDATE templatetrack SET timestamp_backup=timestamp, timestamp=null";
                stm.executeUpdate(sql2);
                String sql3 = "ALTER TABLE templatetrack DROP COLUMN timestamp";
                stm.executeUpdate(sql3);
                String sql4 = "ALTER TABLE templatetrack ADD COLUMN timestamp timestamp";
                stm.executeUpdate(sql4);
                String sql5= "SELECT timestamp_backup FROM templatetrack";
                ResultSet rs = stm.executeQuery(sql5);
                List<Long> timestampList = new ArrayList<Long>();
                while (rs.next()) {
                    timestampList.add(rs.getLong(1));
                }
                for (Long timestamp_long : timestampList) {
                    Timestamp timestamp = new Timestamp(timestamp_long);
                    String sql6 = "UPDATE templatetrack SET timestamp='" + timestamp.toString() + "' WHERE timestamp_backup=" + timestamp_long;
                    stm.executeUpdate(sql6);
                }
                String sql7 = "UPDATE templatetrack SET timestamp_backup=null";
                stm.executeUpdate(sql7);
                String sql8 = "ALTER TABLE templatetrack DROP COLUMN timestamp_backup";
                stm.executeUpdate(sql8);
            }
            String[] tablesToVerify = {TrackerUtil.LIST_TRACKER_TABLE, TrackerUtil.LOGIN_TRACKER_TABLE,
                                      TrackerUtil.QUERY_TRACKER_TABLE, TrackerUtil.SEARCH_TRACKER_TABLE};

            for (String tableToVerify : tablesToVerify) {
                if (!verifyTrackColumnType(connection, tableToVerify)) {
                    String sql = "DROP TABLE " + tableToVerify;
                }
            }
        } catch (ObjectStoreException ose) {
            ose.printStackTrace();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }  finally {
            ((ObjectStoreInterMineImpl) userProfileOS).releaseConnection(connection);
        }
    }

    private boolean verifyTrackColumnType (Connection con, String tableName) {
        try {
            if (DatabaseUtil.tableExists(con, tableName)) {
                ResultSet res = con.getMetaData().getColumns(null, null,
                                                            tableName, "timestamp");

                while (res.next()) {
                    if (res.getString(3).equals(tableName)
                        && res.getString(4).equals("timestamp")
                        && res.getInt(5) == Types.TIMESTAMP) {
                        return true;
                    }
                    return false;
                }
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return true;
    }
}

@@ -2107,24 +2107,58 @@
 nnection();%0A
+            Statement stm = null;%0A
             
@@ -2247,34 +2247,24 @@
             
-Statement 
 stm = connec
@@ -4131,16 +4131,60 @@
 Verify;%0A
+                    stm.executeUpdate(sql);%0A
         

Fix a bug