
package retin;

import java.sql.*;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * ReTIN
 *
 * The Real-Time INdexing technique
 *
 * This program requires PostgreSQL database (8.1+) to be connected in.
 *
 * Go to Commons.java and fill in the connection informations.
 *
 * Edit the ddl.sql script then and copy the necessary informations below
 * (TODO: to automatic in a future version).
 *
 * Copyright (c) 2010 Petr Chmelar
 *
 * All rights reserved (until the first release).
 *
 * @author chmelarp
 */
public class ReTIN {

    // user-eligible database objects -----------------------------------------------------------------------------------------------------------------------------

    /**
     * The RT constant - any RT operation should not exceed this time [ms]
     */
    public static double maxRTimeout = 300;
    
    /**
     * The RT sleep time derived from ... according to the sampling theorem
     */
    public static long sleepTime = 1000;

    /**
     * The block of inserts to be investigated (test the select time), minimal partition size
     */
    public static long batchIndex = 2000;

    /**
     * How many times to wakeup and within batchIndex
     */
    public static long batchWakeupCnt = 5;

    /**
     * How many times to wakeup and within batchIndex
     */
    public static long maximumStdevs = 3;


    /**
     * The schema name
     */
    public static String dbSchema = "climate";

    /**
     * The asynchronous notification name to listen for
     */
    public static String dbNotifyName = "climate_notify";

    // data table rows - user defined EXCEPT rt_id
    public static String dbTable =
        "rt_id timestamp without time zone NOT NULL DEFAULT now()," +
        "datum date NOT NULL," +
        "station integer NOT NULL," +
        "data int4[]";

    // data table constraints - user defined (except the CHECK)... the ## macro stands for id
    public static String dbConstraints[] = { dbSchema +"_data_##_check CHECK ( rt_id >= '?RTF?' AND rt_id < '?RTL?' )",
        dbSchema +"_data_##_pk PRIMARY KEY (datum, station)",
        dbSchema +"_data_##_stations FOREIGN KEY (station) REFERENCES "+ dbSchema +".stations (id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT" };

    public static String dbIndexes[] =  {
        dbSchema +"_data_##_rt_index ON "+ dbSchema +".data_&&(rt_id)",
        // dbSchema +"_data_##_gist ON "+ dbSchema +".data_&& USING gist(data gist__int_ops)",
        dbSchema +"_data_##_gin ON "+ dbSchema +".data_&& USING gin(data)"};

    // real-time queries to perform the speed test (0 index, 1 temp)
    public static String dbQueries[] = {
            "SELECT count(*) FROM "+ dbSchema +".data_index WHERE data @> ARRAY[##]",
            "SELECT count(*) FROM "+ dbSchema +".data_temp WHERE data @> ARRAY[##]",
    };

    // end of user-eligible database objects - pysical table creation ---------------------------------------------------------------------------------------


    // a common functionality (database, logs, ...)
    Commons commons;

    /**
     * Constructor
     */
    public ReTIN() {
        commons = new Commons();
        // LISTEN retin_climate;
        // dbms_alert.register('retin_climate');

        try {
            commons.conn.setAutoCommit(false);
        } catch (SQLException ex) {
            Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        ReTIN retin = new ReTIN();

        if (args.length > 0 && args[0].compareTo("clear") == 0) { // compare string args to "clear"
            retin.clearDB();
        }

/*
        // first, find the previous metadata
        int insert_count_prev = 0;

        try {
            Statement stmt = retin.commons.conn.createStatement();
            ResultSet rset = stmt.executeQuery("SELECT * FROM "+ dbSchema +".data_meta WHERE id=0");

            // if there is no metadata row, insert it
            if (!rset.next()) {
                
                insert_count_prev = 0;
            } else {
                insert_count_prev = rset.getInt("insert_count");
            }

            retin.commons.conn.commit();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
        }
*/
        
        // go to the infinite loop
        retin.maintenance_loop(0);
    } // Main


    /**
     * This is to decide whethet to reindex the database or not
     * @return the exit code
     */
    private void maintenance_loop(int insert_count_prev) { // /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        
        while (true) {
            // pgconn.getNotifications();
            // dbms_alert.waitany(NULL);

            try {
                // ge the metadata
                Statement stmt_meta = commons.conn.createStatement();
                ResultSet rset_meta = stmt_meta.executeQuery("SELECT * FROM "+ dbSchema +".data_meta WHERE id = 0");
                rset_meta.next();

                int insert_count = rset_meta.getInt("insert_count");
                Timestamp rt_first = rset_meta.getTimestamp("rt_first");

                // prepare statements and result sets
                Statement stmt = commons.conn.createStatement();
                ResultSet rset;

                // if there is time to count statistics...
                if (insert_count - insert_count_prev >= batchIndex) { // allways > 0 - no problem for division
                    double insert_millis = rset_meta.getDouble("insert_millis");
                    double insert_stdev = rset_meta.getDouble("insert_stdev");

                    int query_count = rset_meta.getInt("query_count"); // TODO: presunout pod if 200
                    double query_index_millis = rset_meta.getDouble("query_index_millis");
                    double query_index_stdev = rset_meta.getDouble("query_index_stdev");
                    double query_temp_millis = rset_meta.getDouble("query_temp_millis");
                    double query_temp_stdev = rset_meta.getDouble("query_temp_stdev");

/*
                    // set the first update timestamp... if null (at the beginning), find it
                    if (rt_first == null) {
                        stmt = commons.conn.createStatement();
                        rset = stmt.executeQuery("SELECT min(rt_id) FROM "+ dbSchema +".data_temp");
                        if (!rset.next() || rset.getTimestamp(1) == null) {
                            // commons.error(rset, "SELECT min(rt_id) FROM "+ dbSchema +".data_temp");
                            // sleepTime *= 1.1;
                            rt_first = new Timestamp(1900, 1, 1, 0, 0, 0, 0); // DO NOT USE!!!
 *
                            Thread.sleep(sleepTime);
                            continue; // wait until there is something inserted
                        } else {
                            rt_first = rset.getTimestamp(1);
                        }

                        stmt.executeUpdate("UPDATE "+ dbSchema +".data_meta SET rt_first='"+ rt_first.toString() +"' WHERE id = 0");
                        stmt.close();
                    }
*/
                    double[] time_select = new double[dbQueries.length];

                    // SELECT the last record
                    long t1 = System.currentTimeMillis();
                    stmt = commons.conn.createStatement();
                    rset = stmt.executeQuery("SELECT max(rt_id) FROM "+ dbSchema +".data_temp");
                    if (!rset.next()) {
                        commons.error(rset, "SELECT max(rt_id) FROM "+ dbSchema +".data_temp");
                        continue;
                    }
                    long t2 = System.currentTimeMillis();
                    // time_select[-1] = t2 - t1;

                    Timestamp rt_last = rset.getTimestamp(1);
                    rset.close();

                    /* double sum = 0;
                    double sq_sum = 0;
                    for(int i = 0; i < n; ++i) {
                        sum += a[i];
                        sq_sum += a[i] * a[i];
                    } */
                    double mean = insert_millis / insert_count;
                    double stdev = Math.sqrt( (insert_stdev / insert_count) - (mean * mean) );

                    // issue a SERIOUS WARNING if there is no sure about the system to insert realtime
                    if (mean + maximumStdevs*stdev > maxRTimeout) { // QUESTION: proc zde bylo insert_count - insert_count_prev >= maxRTimeout || ???
                        commons.logTime("The system may not suffice the R-T condition (insert)!");
                    }

                    // commit the insert part (don't)
                    rset_meta.close();
                    stmt_meta.close();
                    commons.conn.commit();

                    // adjust the sleep time
                    sleepTime = ((rt_last.getTime() - rt_first.getTime())/insert_count) * batchIndex/batchWakeupCnt;

                    
                    // perform an indexed searches && temp searches ----------------------
                    // RANDOM - using random value to select for
                    int rand = new Integer(getRndVal(1, 15)*100);

                    t1 = System.currentTimeMillis();
                    stmt = commons.conn.createStatement();
                    String select = "SELECT "+ dbSchema +".querycount('"+ dbQueries[0].replace("##", Integer.toString(rand) ) +"')";
                    System.out.println(select);

                    stmt = commons.conn.createStatement();
                    stmt.execute(select);
                    t2 = System.currentTimeMillis();
                    // time_select[2] = t2 - t1;
                    String notice = stmt.getWarnings().getMessage().replaceAll("00:00:", "");
                    time_select[0] = Double.parseDouble(notice)*1000;
                    stmt.close();
                    commons.conn.commit();

                    // now the temp search
                    t1 = System.currentTimeMillis();
                    stmt = commons.conn.createStatement();
                    select = "SELECT "+ dbSchema +".querycount('"+ dbQueries[1].replace("##", Integer.toString(rand) ) +"')";
                    System.out.println(select);

                    stmt = commons.conn.createStatement();
                    stmt.execute(select);
                    t2 = System.currentTimeMillis();
                    // time_select[3] = t2 - t1;
                    notice = stmt.getWarnings().getMessage().replaceAll("00:00:", "");
                    time_select[1] = Double.parseDouble(notice)*1000;
                    stmt.close();
                    
                    // comit queries (don't) ... nezdrzuj, ale bacha na to!
                    commons.conn.commit();


                    // update the query statistics
                    stmt = commons.conn.createStatement();
                    stmt.executeUpdate("UPDATE climate.data_meta SET query_count=(query_count+1), " +
                            " query_index_millis=(query_index_millis+"+ time_select[0] +"), query_index_stdev=(query_index_stdev + ("+ time_select[0]*time_select[0] +")), " +
                            " query_temp_millis=(query_temp_millis+"+ time_select[1] +"), query_temp_stdev=(query_temp_stdev + ("+ time_select[1]*time_select[1] +")) " +
                            " WHERE id=0");
                    stmt.close();
                    commons.conn.commit();


                    // count statistics ---------------------------------------
                    query_count++;
                    query_index_millis += time_select[0];
                    query_index_stdev += time_select[0]*time_select[0];
                    query_temp_millis += time_select[1];
                    query_temp_stdev += time_select[1]*time_select[1];

                    /* double sum = 0;
                    double sq_sum = 0;
                    for(int i = 0; i < n; ++i) {
                        sum += a[i];
                        sq_sum += a[i] * a[i];
                    } */
                    double index_mean = query_index_millis / query_count;
                    double index_stdev = Math.sqrt( (query_index_stdev / query_count) - (index_mean * index_mean) );
                    double temp_mean = query_temp_millis / query_count;
                    double temp_stdev = Math.sqrt( (query_temp_stdev / query_count) - (temp_mean * temp_mean) );

                    // SELECT 3 middle records... count the middle timestamp
                    /*
                    long insert_first_long = rt_first.getTime();
                    long insert_last_long = insert_last.getTime();
                    Timestamp rt_insert_middle = new Timestamp(insert_first_long + (insert_last_long - insert_first_long));

                    long t3 = System.currentTimeMillis();
                    stmt_select = commons.conn.createStatement();
                    String testQuery = "SELECT "+ dbSchema +".query('SELECT * FROM "+ dbSchema +".data_temp WHERE rt_id <= ''"+ rt_insert_middle.toString() +"'' LIMIT 3')";
                    commons.logTime(testQuery);
                    rset_select = stmt_select.executeQuery(testQuery);
                    rset_select.next();

                    long t4 = System.currentTimeMillis();
                    double time_select2 = t4 - t3;

                    rset_select.close();
                    String notice = stmt_select.getWarnings().getMessage().replaceAll("00:00:", "");
                    double time_select2DB = Double.parseDouble(notice);
                    */


                    // if the sequential scan is too slow - reindexing needed OR the index scan is too slow - window shrink needed
                    // because of the index... for a large saw you can multiply the index_mean by the factor up to 2 (to have an optimal average).
                    if (temp_mean >= index_mean || index_mean + maximumStdevs*index_stdev > maxRTimeout) {
                        // COMMIT EVERYTHING... FORGET EVERYTHING
                        commons.conn.commit();

                        // and reindex...
                        this.reindex();
                    }

                    insert_count_prev = 0;
                }

                // Exit the loop nicely
                commons.conn.commit();
                stmt_meta.close();
                
                commons.logTime("Going to sleep for "+  sleepTime +" milliseconds");
                Thread.sleep(sleepTime);

            } catch (InterruptedException ex) {
                Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
                try {
                    commons.conn.rollback();
                } catch (SQLException ex1) {
                    Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex1);
                }
            } catch (SQLException ex) {
                Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
                try {
                    commons.conn.rollback();
                } catch (SQLException ex1) {
                    Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex1);
                }
            }

        }
    }




    /**
     * This is to reindex the indexes
     */
    private void reindex() { // //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        try {

            // get the metadata
            Statement stmt_meta = commons.conn.createStatement();
            ResultSet rset_meta = stmt_meta.executeQuery("SELECT * FROM "+ dbSchema +".data_meta WHERE id=0");
            rset_meta.next();

            int insert_count = rset_meta.getInt("insert_count");
            Timestamp rt_first = rset_meta.getTimestamp("rt_first");

            // count the insert stats
            double insert_millis = rset_meta.getDouble("insert_millis");
            double insert_stdev = rset_meta.getDouble("insert_stdev");
            double mean = insert_millis / insert_count;
            double stdev = Math.sqrt( (insert_stdev / insert_count) - (mean * mean) );

            // count the query stats
            int query_count = rset_meta.getInt("query_count");
            double query_index_millis = rset_meta.getDouble("query_index_millis");
            double query_index_stdev = rset_meta.getDouble("query_index_stdev");
            double query_temp_millis = rset_meta.getDouble("query_temp_millis");
            double query_temp_stdev = rset_meta.getDouble("query_temp_stdev");

            double index_mean = query_index_millis / query_count;
            double imstt = (query_index_stdev / query_count) - (index_mean * index_mean);
            double index_stdev = (imstt <= 0) ? 0 : Math.sqrt( imstt );
            double temp_mean = query_temp_millis / query_count;
            double temp_stdev = Math.sqrt( (query_temp_stdev / query_count) - (temp_mean * temp_mean) );;

            // find out the last unindexed item "1934-03-17"
            Statement stmt = commons.conn.createStatement();
            ResultSet rset = stmt.executeQuery("SELECT max(rt_id) FROM "+ dbSchema +".data_temp");
            rset.next();
            Timestamp rt_last = rset.getTimestamp(1);
            rset.close();

            // find out the next real table partition id
            rset = stmt.executeQuery("SELECT max(id)+1 FROM "+ dbSchema +".data_meta");
            rset.next();
            int id = rset.getInt(1);
            rset.close();

/* BEGIN */
            // create the metadata record for a new physical partition
            int arows = stmt.executeUpdate("UPDATE "+ dbSchema +".data_meta SET id="+ id +", rt_last='"+ rt_last.toString() +"', " +
                    " insert_millis="+ mean +", insert_stdev="+ stdev +", " +
                    " query_index_millis="+ index_mean +", query_index_stdev="+ index_stdev +", " +
                    " query_temp_millis="+ temp_mean +", query_temp_stdev="+ temp_stdev +
                    " WHERE id = 0");

            // create a new temp record
            stmt.executeUpdate("INSERT INTO "+ dbSchema +".data_meta(id, rt_first) VALUES(0, '"+ rt_last +"')");
/* END */

            // OK, here we go - important transaction finished:)
            stmt.close();
            commons.conn.commit();


            // here we perform the data copy
            stmt = commons.conn.createStatement();
            stmt.execute("CREATE TABLE "+ dbSchema +".data_"+ id +" AS SELECT * FROM "+ dbSchema +".data_temp WHERE rt_id < '"+ rt_last +"'");
            stmt.close();
            commons.conn.commit();

            // perform some data modifications...
            String constr =  "ALTER TABLE climate.data_"+ Integer.toString(id) +" ALTER COLUMN rt_id SET NOT NULL";
            // System.out.println(constr);
            stmt = commons.conn.createStatement();
            stmt.execute(constr);
            stmt.close();
            commons.conn.commit();


            // toz a zkusime to naconstraintovat
            for (int i = 0; i < dbConstraints.length; i++) {    // TODO: opravit CHECK constraint
                // this is just for the performance - not absolutely important... may be skipped, BUT the times will never be the same!
                constr = "ALTER TABLE "+ dbSchema +".data_"+ id +" ADD CONSTRAINT "+ dbConstraints[i].replace("##", Integer.toString(id)).replace("?RTF?", rt_first.toString()).replace("?RTL?", rt_last.toString());
                // System.out.println(constr);

                try {
                    stmt = commons.conn.createStatement();
                    stmt.execute(constr);
                    stmt.close();
                    // TODO: correct if failed!
                } catch (SQLException ex) {
                    Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    commons.conn.commit();                    
                }

            }

            // naposled udelame nejake indexy nad touto tabulkou // TODO: zvazit pro experimenty
            for (int i = 0; i < dbIndexes.length; i++) {
                // this is just for the performance - not absolutely important... may be skipped, BUT the times will never be the same!
                String index = "CREATE INDEX "+ dbIndexes[i].replace("##", Integer.toString(id)).replace("&&", Integer.toString(id));
                // System.out.println(index);

                try {
                    stmt = commons.conn.createStatement();
                    stmt.execute(index);
                    stmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    commons.conn.commit();
                }
            }

            // now we have a plenty of time to create temporary indexed table data_itempex

            // indexed window too large, cannot be 99.999% sure to be RT - shrink the window (remove the inheritance from old partitions of data and data_index tables)
            int index_first = 1;
            Timestamp index_rt_first = rt_first;

            if (id > 1) {
                stmt = commons.conn.createStatement();
                rset = stmt.executeQuery("SELECT rt_first FROM "+ dbSchema +".data_meta WHERE id=("+ index_first +")");
                rset.next();
                index_rt_first = rset.getTimestamp(1);  // we're adding one partition, if a shrink needed we must drop two old partitions
                rset.close();

                stmt.close();                
            }
/*          // forgetting
            if (id > 2 && index_mean + maximumStdevs*index_stdev > maxRTimeout) {

                stmt = commons.conn.createStatement();
                rset = stmt.executeQuery("SELECT index_first FROM "+ dbSchema +".data_meta WHERE id=("+ id +" - 1)");
                rset.next();
                index_first = rset.getInt(1) + 2;  // we're adding one partition, if a shrink needed we must drop two old partitions
                rset.close();

                rset = stmt.executeQuery("SELECT rt_first FROM "+ dbSchema +".data_meta WHERE id=("+ index_first +")");
                rset.next();
                index_rt_first = rset.getTimestamp(1);  // we're adding one partition, if a shrink needed we must drop two old partitions
                rset.close();

                stmt.close();

            }
*/
            // update rt_first and index_first
            stmt = commons.conn.createStatement();
            stmt.execute("UPDATE "+ dbSchema +".data_meta SET index_first="+ index_first +" WHERE id="+ id);
            stmt.close();

            commons.conn.commit();

            // Make the table as simple as possible
            String create = "CREATE TABLE "+ dbSchema +".data_itempex ("+ dbTable +")";
            // System.out.println(create);
            stmt = commons.conn.createStatement();
            stmt.execute(create);
            
            // create new inheritances for virtual table climate.data_itempex
            for (int i = 1; i <= id; i++) {
                stmt.execute("ALTER TABLE "+ dbSchema +".data_"+ i +" INHERIT "+ dbSchema +".data_itempex");
            }
            stmt.close();
            commons.conn.commit();

/*
            // well, try to add some constraints to the itempex table
            for (int i = 0; i < dbConstraints.length; i++) {
                // this is just for the performance - not absolutely important... may be skipped, BUT the times will never be the same!
                constr = "ALTER TABLE "+ dbSchema +".data_itempex ADD CONSTRAINT "+ dbConstraints[i].replace("##", "itempex_"+ Integer.toString(id)).replace("?RTF?", index_rt_first.toString()).replace("?RTL?", rt_last.toString());
                // System.out.println(constr);

                try {
                    stmt = commons.conn.createStatement();
                    stmt.execute(constr);
                    stmt.close();
                    commons.conn.commit();
                    // TODO: correct if failed!
                } catch (SQLException ex) {
                    Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    commons.conn.commit();
                }
            }

            // naposled udelame nejake indexy nad touto tabulkou // TODO: zjistit, jestli je potreba - mozna namisto tech vyse
            for (int i = 0; i < dbIndexes.length; i++) {
                // this is just for the performance - not absolutely important... may be skipped, BUT the times will never be the same!
                String index = "CREATE INDEX "+ dbIndexes[i].replace("##", "itempex_"+ Integer.toString(id)).replace("&&", "itempex");
                // System.out.println(index);

                try {
                    stmt = commons.conn.createStatement();
                    stmt.execute(index);
                    stmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    commons.conn.commit();
                }
            }
*/

/* BEGIN */
            // perform the last transaction
            // add the last table to the cluster
            stmt = commons.conn.createStatement();
            stmt.execute("ALTER TABLE "+ dbSchema +".data_"+ id +" INHERIT "+ dbSchema +".data");
            stmt.close();

            // drop inheritances for table climate.data_index
            stmt = commons.conn.createStatement();
            for (int i = 1; i < id; i++) {
                stmt.execute("ALTER TABLE "+ dbSchema +".data_"+ i +" NO INHERIT "+ dbSchema +".data_index");

                // if the data is too old... exclude it from climate.data as well
                if (i < index_first) {
                    stmt.execute("ALTER TABLE "+ dbSchema +".data_"+ i +" NO INHERIT "+ dbSchema +".data");
                }
            }
            stmt.close();

            // here we perform the old index drop down
            try {
                stmt = commons.conn.createStatement();
                // drop the index table
                stmt.execute("DROP TABLE "+ dbSchema +".data_index");
            } catch (SQLException ex) {
                Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
                // TODO: co s tim????
                // commons.conn.rollback();
            }
            stmt.close();

            // rename the itempex table
            try {
                stmt = commons.conn.createStatement();
                stmt.execute("ALTER TABLE  "+ dbSchema +".data_itempex RENAME TO data_index");
            } catch (SQLException ex) {
                Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);

            }
            stmt.close();

            // here we perform the data deletion
            stmt = commons.conn.createStatement();
            stmt.execute("DELETE FROM "+ dbSchema +".data_temp WHERE rt_id < '"+ rt_last.toString() +"'");
            stmt.close();

            // log sucess
            commons.logTime("Created partition table # "+  id +" && reindexing: sucess");

            // done, finally :)
            commons.conn.commit();
/* END */

        } catch (SQLException ex) {
            Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
            try {
                commons.conn.rollback();
                // commons.conn.commit(); // just for debug
            } catch (SQLException ex1) {
                Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex1);
            }
        }

    }

    private int getRndVal(int min, int max) {
        return min + (new Random(System.currentTimeMillis())).nextInt(max-min);
        // return 7;
    }    


    private void clearDB() {
        try {
/*          // if the medatata table is broken do also...
            for (int i = 1; i < 290; i++) {
                Statement stmt = commons.conn.createStatement();
                stmt.execute("INSERT INTO "+ dbSchema +".data_meta(id) VALUES ("+ i +")");
                stmt.close();
                commons.conn.commit();
            }
*/
            // get the metadata
            Statement stmt_meta = commons.conn.createStatement();
            ResultSet rset_meta = stmt_meta.executeQuery("SELECT * FROM " + dbSchema + ".data_meta");

            while (rset_meta.next()) {
                int id = rset_meta.getInt("id");
                try {
                    if (id > 0) {
                        Statement stmt = commons.conn.createStatement();
                        stmt.execute("DROP TABLE "+ dbSchema +".data_"+id);
                        stmt.close();
                    }

                } catch (SQLException ex) {
                    commons.logTime("FAILED: DROP TABLE "+ dbSchema +".data_"+id);
                } finally {
                    commons.conn.commit();
                }
            }

            try {
                Statement stmt = commons.conn.createStatement();
                stmt.execute("DROP TABLE "+ dbSchema +".data_itempex CASCADE");
                stmt.close();
            } catch (SQLException ex) {
                commons.logTime("Already DROPped: DROP TABLE "+ dbSchema +".data_itempex CASCADE");
            } finally {
                commons.conn.commit();
            }

            try {
                Statement stmt = commons.conn.createStatement();
                stmt.execute("TRUNCATE TABLE "+ dbSchema +".data_temp");
                stmt.close();
            } catch (SQLException ex) {
                Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
                commons.logTime("FAILED: TRUNCATE TABLE "+ dbSchema +".data_temp");
            } finally {
                commons.conn.commit();
            }

            try {
                Statement stmt = commons.conn.createStatement();
                stmt.execute("TRUNCATE TABLE "+ dbSchema +".data_meta");
                stmt.executeUpdate("INSERT INTO "+ dbSchema +".data_meta(id) VALUES (0)");
                stmt.close();
            } catch (SQLException ex) {
                Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
                commons.logTime("FAILED: TRUNCATE TABLE "+ dbSchema +".data_meta");
            } finally {
                commons.conn.commit();
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReTIN.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}

