Operations on Sucxent++

/*
 * Created on Jan 7, 2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
// This class gathers all the operations that can be perform on Sucxent++
// Hence, all operations on Sucxent++ can be called by running different methods in this class
package dbsucxent;

import dbsucxent.construct.Reconstruct;
import dbsucxent.construct.ReconstructEdge;
import dbsucxent.pathProcessor.NoSuchPathException;
import dbsucxent.pathProcessor.Translator;
import dbsucxent.pathProcessorEdge.EdgeTranslator;


import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.ArrayList;

import javax.swing.tree.DefaultMutableTreeNode;

import dbsucxent.storage.loaders.relational.JdbcOptionsProvider;
import dbsucxent.storage.loaders.relational.MSSQLOptionsProvider;

import dbsucxent.statistics.*; //klarinda 20070528 to get the XML Data statistics


/**
 * @author tzyyming
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 * 
 * 1) set up connection using setConnection() or through the Sucxent constructor
 * 2a) provide the file path and query to run() -- (recomended) 2b) OR load the
 * file using loadDocument(), then provide the query to translateExecute() 2c)
 * OR load the file using loadDocument(), provide the query to translate, then
 * call execute() 3) call either getTree(), getText() or getTable to get the
 * result
 * 
 * Misc: set the optimizing option using setForceOrder(), setLimit() and
 * setJoinType()
 */
public class Sucxent {

    Connection _dbConnection;
    JdbcOptionsProvider _urlProvider;
    int _queryDepth;
    String _queryLastElement;
    String _translated = null;
    boolean treeConstructed = true, tableConstructed = true,
            textConstructed = true;
    long _treeConstructTime = 0, _tableConstructTime = 0,
            _textConstructTime = 0;
    long _loadTime = 0, _translateTime = 0, _executeTime = 0;
    public ResultSet _results;
    JDBCTableModel _tableModel;
    DefaultMutableTreeNode _treeNode;
    String _text;
    boolean _forceOrder = false, _limit = false, isThereResult = false;
    int _joinType = 0;
    public static int SUCXENT = 0;
    public static int EDGE = 1;
    int _mode = SUCXENT;
    public static String _dbName,  _usr,  _pass;
    Launcher launcher;
    private long currentRow = 0;
    private long totalRow = 0;
    private long processedRow = 0;
    private int processedPage = 0;
    private int currentPage = 0;
    private long nextRow = 0;
    private Vector pageEndRows = new Vector();
    private int totalPage = 0;
    public ReconstructEdge reconstructEdge;
    public Reconstruct reconstruct;
    private long processedLeaf;
    private boolean newPage = true;
    private String _DBHost = "127.0.0.1";
    //private String _DBHost = "localhost";
    public Sucxent() {
        try {
            launcher = new Launcher();
            _urlProvider = new MSSQLOptionsProvider();
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (Exception ex) {
            System.out.println("A problem occurs while initializing Sucxent:");
            System.out.println(ex.getMessage());
        }
    }

    public void connect(String userName, String pass, String db, int mode)
            throws Exception {
        _mode = mode;
        connect(userName, pass, db);
    }

    // set up a connection
    public void connect(String userName, String pass, String db)
            throws Exception {
        String url = "jdbc:sqlserver://" + _DBHost + ":1433;databaseName=" + db;
        _dbConnection = DriverManager.getConnection(url, userName, pass);

        _dbName = db;
        _usr = userName;
        _pass = pass;

    }

    // disconnect and reconnect to the database
    public void reconnect() throws Exception {
        _results.clearWarnings();
        _results.close();
        _results = null;
        _dbConnection.clearWarnings();
        _dbConnection.commit();
        _dbConnection.close();
        _dbConnection = null;
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url = "jdbc:sqlserver://" + _DBHost + ":1433;databaseName=" + _dbName;
        _dbConnection = DriverManager.getConnection(url, _usr, _pass);

    }

    public void setConnection(Connection dbConnection) {
        _dbConnection = dbConnection;
    }

    public Connection getConnection() {
        return _dbConnection;
    }

    // run: load a Document and execute a query on this document
    public void run(String absoluteFilePath, String path, boolean useTemp)
            throws Exception {
        deleteDocuments();
        loadDocument(absoluteFilePath);
        translateExecute(path, useTemp);
    }

    // translate first then execute
    public void translateExecute(String path, boolean useTemp) throws Exception {
        translate(path, useTemp);
        execute();
    }

    // delete and load documents from connected database
    public void deleteAndLoadDocument(String absoluteFilePath) throws Exception {
        deleteDocuments();
        loadDocument(absoluteFilePath);
    }

    // load documents pointed by absoluteFilePath to the connected database
    public void loadDocument(String absoluteFilePath) throws Exception {
        System.runFinalization();
        System.gc();

        long startTime = System.nanoTime() / 1000000;
        if (_mode == SUCXENT) {
            SucxentShredder shredder = new SucxentShredder(absoluteFilePath,
                    _dbConnection, _dbName);
            shredder.shred();
        } else {
            EdgeShredder shredder = new EdgeShredder(absoluteFilePath,
                    _dbConnection, _dbName);
            shredder.shred();
        }

        _loadTime = System.nanoTime() / 1000000 - startTime;
        System.out.println("Loading Time: " + _loadTime);

    }

    // delete all documents from the connected database
    public void deleteDocuments() throws Exception {
        Statement statement = _dbConnection.createStatement();

        String sqlstats = "TRUNCATE TABLE PathCount_ValueSelectivity; TRUNCATE TABLE TotalPathCount; \n";

        if (_mode == SUCXENT) {
            String sql = "TRUNCATE TABLE attribute; TRUNCATE TABLE pathValue; TRUNCATE TABLE documentRValue; 

TRUNCATE TABLE path; TRUNCATE TABLE document; " + "TRUNCATE TABLE TextContent; TRUNCATE TABLE AttrContent; 
select 1";
            statement.executeQuery(sqlstats + sql);
        } else {
            String sql = "TRUNCATE TABLE attribute; TRUNCATE TABLE edge; TRUNCATE TABLE path;

 TRUNCATE TABLE document; " + "TRUNCATE TABLE TextContent; TRUNCATE TABLE AttrContent; 
select 1";
            statement.executeQuery(sqlstats + sql);
        }

        statement.close();
    }

    // klarinda 20061101 add database
    public void addDatabase(String userName, String pass, String db, int mode)
            throws Exception {
        String url = "jdbc:sqlserver://" + _DBHost + ":1433;databaseName=master";
        _dbConnection = DriverManager.getConnection(url, userName, pass);
        _usr = userName;
        _pass = pass;
        _mode = mode;
        _dbName = db;

        Statement statement = _dbConnection.createStatement();
        String sqlquery = "CREATE Database \"" + db + "\"; select 1;";

        statement.executeQuery(sqlquery);
        statement.close();

        BufferedReader in;
        BufferedReader in2;
        String sqlscript = "USE [" + db + "];\n", str; // erwin 2007-07-04 add

        String sqlscript2 = "";
        ArrayList CreateFuncScript = new ArrayList();
        if (_mode == SUCXENT) {
            in = new BufferedReader(new FileReader("script/SUCXENT.sql"));
            in2 = new BufferedReader(new FileReader("script/SUCXENT-SP.sql"));
            while ((str = in2.readLine()) != null) {
                if (str.trim().equals("GO")) {
                    CreateFuncScript.add(sqlscript2);
                    sqlscript2 = "";
                } else {
                    sqlscript2 = sqlscript2 + str + "\n";
                }
            }
            in2.close();
        } else {
            in = new BufferedReader(new FileReader("script/EDGE.sql"));
        }

        while ((str = in.readLine()) != null) {
            sqlscript = sqlscript + str + "\n";
        }

        in.close();

        statement = _dbConnection.createStatement();
        statement.execute(sqlscript);
        
        for (int i=0; i<CreateFuncScript.size(); i++)
        {
            statement.execute((String)CreateFuncScript.get(i));
        }
        statement.close();

    }

    // klarinda 20061101 delete database
    public void deleteDatabase(String userName, String pass, String db)
            throws Exception {
        String url = "jdbc:sqlserver://" + _DBHost + ":1433;databaseName=master";
        _dbConnection = DriverManager.getConnection(url, userName, pass);
        Statement statement = _dbConnection.createStatement();
        String sqlquery = "DROP Database \"" + db + "\"; select 1;";
        statement.executeQuery(sqlquery);

        statement.close();

        if (_usr != null) { // if connected to other database

            connect(_usr, _pass, _dbName, _mode);
        }

    }


    // perform an SQL query execution on the connected database using the input
    // SQLQuery
    public ResultSet queryDocuments(String SQLQuery) throws SQLException {
        Statement statement = _dbConnection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        return statement.executeQuery(SQLQuery);
    }

    public String translate(String path, boolean useTemp)
            throws NoSuchPathException, SQLException {
        return translate(path, false, useTemp);
    }

    // translate the xpath query input 'path' to SQL query and return it
    // showPath determines whether the showPath option is to be turned on
    public String translate(String path, boolean showPath, boolean useTemp)
            throws NoSuchPathException, SQLException {

        _translated = null;
        _queryDepth = 0;
        _queryLastElement = null;
        Translator ts = null;
        EdgeTranslator ets = null;

        System.runFinalization(); // forcing finalization

        System.gc(); // forcing garbage collection for more accurate timing

        long startTime = System.nanoTime() / 1000000;

        if (_mode == SUCXENT) {
            // added by erwin 2007-07-06
            // check if there is only one document
            int X = getTotalStoredDocument(_dbConnection);

            if (X == 1) {
                // only one documenent
                ts = new Translator(path, _dbConnection, true, showPath,
                        useTemp);
            } else {
                ts = new Translator(path, _dbConnection, false, showPath,
                        useTemp);
            }
            _translated = ts.translate() + optimizingLine();
            _queryDepth = ts.getDepth();
            _queryLastElement = ts.getLastElement();
        } else {
            ets = new EdgeTranslator(path, _dbConnection, true, showPath);
            _translated = ets.translate() + optimizingLine();
        }

        _translateTime = System.nanoTime() / 1000000 - startTime;
        System.out.println("Translate ended, translation time: " + _translateTime);

        return _translated;
    }

    // requery the translated result stored in this class if there exist one
    public void reQuery() throws SQLException {
        if (_translated != null) {
            _results = query(_translated);
        }
    }

    // query the database with SQL statement queryText
    public ResultSet query(String queryText) throws SQLException {
        System.runFinalization();
        System.gc();
        setNewPage(true);

        ResultSet resultSet = query(_dbConnection, queryText);
        return resultSet;
    }

    // added by Erwin 2007-07-06
    public int getTotalStoredDocument(Connection dbcon) throws SQLException {
        Statement stmt = dbcon.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT COUNT(ID) AS TOTAL FROM DOCUMENT ");

        rs.next();

        int total = rs.getInt("TOTAL");
        rs.close();
        stmt.close();

        return total;
    }

    // Querying the database _dbConnection with the SQL query queryText
    public ResultSet query(Connection _dbConnection, String queryText)
            throws SQLException {
        if (_results != null) {
            queryClose();
        }

        if (queryText.equals("Select 'No Rows'")) {
            isThereResult = false;
        } else {
            isThereResult = true;
        }

        Statement statement = _dbConnection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        statement.execute("DBCC DROPCLEANBUFFERS;\nDBCC FREEPROCCACHE;"); // clear

        long startTime = System.nanoTime() / 1000000;
        ResultSet queryResultSet = null;
        try {

            if (queryText.toUpperCase().contains("INSERT INTO PVTEMP")) {
                String[] tempQuery = queryText.split(";");
                statement.executeUpdate("TRUNCATE TABLE PVTemp ");
                statement.executeUpdate(tempQuery[0].trim());
                queryText = tempQuery[1].trim();
            }
            queryResultSet = statement.executeQuery(queryText);
        } catch (SQLException e) {
            System.out.println("*****\tSucxent.query(Conn, queryText) Error in executing query:\n*****\t\t" +
 e.getMessage());
            _results = null;
            throw e;
        }

        if (!tableConstructed && !treeConstructed && !textConstructed) {
            _executeTime = System.nanoTime() / 1000000 - startTime;
        }
        System.out.println("Query ended, Query Time: " + _executeTime);
        return queryResultSet;
    }

    public void queryClose() throws SQLException {
        queryClose(_results);
    }

    // Closing the connection
    public void queryClose(ResultSet results) throws SQLException {
        Statement statement = results.getStatement();
        results.close();
        statement.close();
    }

    public void execute() throws Exception {
        execute(_translated);
    }

    // execute: similar to query method but this method reset some variables in
    // this class as well, to indicate a new start
    public void execute(String queryText) throws Exception {
        treeConstructed = false;
        tableConstructed = false;
        textConstructed = false;
        _tableConstructTime = 0;
        _treeConstructTime = 0;
        _textConstructTime = 0;
        processedRow = 0;
        currentRow = 0;
        totalRow = 0;
        processedPage = 0;
        currentPage = 0;
        nextRow = 0;
        totalPage = 0;

        _results = query(queryText);

    }

    // get the table model for the previously executed query
    public JDBCTableModel getTable(boolean hasEnded) throws SQLException {
        if (tableConstructed) {
            return _tableModel;
        }

        System.runFinalization();
        System.gc();

        long startTime = System.nanoTime() / 1000000;
        _tableModel = new JDBCTableModel(_results, hasEnded);
        tableConstructed = true;
        _tableConstructTime = System.nanoTime() / 1000000 - startTime;

        System.runFinalization();
        System.gc();
        return _tableModel;
    }

    public JDBCTableModel getTable() throws SQLException {
        return getTable(false);
    }

    // get the mutableTreeNode for the previously executed query
    public DefaultMutableTreeNode getTree() throws SQLException, IOException,
            OutOfMemoryException {
        if (treeConstructed) {
            return _treeNode;
        }

        System.runFinalization();
        System.gc();

        DefaultMutableTreeNode returnNode = null;
        long startTime = System.nanoTime() / 1000000;
        if (_mode == SUCXENT) {
            reconstruct = new Reconstruct(_results, _dbConnection,
                    _queryLastElement, _queryDepth, 0, 0);
            returnNode = reconstruct.reconstructTree();
            _treeNode = returnNode;
            setProcessedRow(reconstruct.getRow());
            setTotalRows(reconstruct.getTotalRows());
            if (reconstruct.isOverflow()) {
                throw (new OutOfMemoryException());
            }
        } else {
            reconstructEdge = new ReconstructEdge(_results, _dbConnection);

            returnNode = reconstructEdge.reconstructTree();
            _treeNode = returnNode;

            setProcessedRow(reconstructEdge.getProcessedRows());
            setProcessedLeaf(reconstructEdge.getLeafCount());
        }
        treeConstructed = true;
        _treeConstructTime = System.nanoTime() / 1000000 - startTime;
        System.out.println("Get Tree Ended, Sucxent. Tree Time: " + _treeConstructTime);

        System.runFinalization();
        System.gc();

        return returnNode;
    }

    // klarinda 20061113, get the mutableTreeNode for the document view
    public DefaultMutableTreeNode getDocumentTree() throws SQLException,
            IOException, OutOfMemoryException {
        _queryLastElement = null;
        _queryDepth = 0;

        return getTree();
    }

    // get the XML text for the previously executed query
    public String getText() throws SQLException, IOException {
        if (textConstructed) {
            return _text;
        }

        System.runFinalization();
        System.gc();

        boolean notProcessedBefore = true;
        long startTime = System.nanoTime() / 1000000;
        if (_mode == SUCXENT) {
            reconstruct = new Reconstruct(_results, _dbConnection,
                    _queryLastElement, _queryDepth, currentRow, nextRow);
            _text = reconstruct.reconstructText();
            currentRow = reconstruct.getRow();
            // totalRow = reconstruct.getTotalRows();
            currentPage++;
            if (currentPage > processedPage) {
                notProcessedBefore = true;
                processedPage = currentPage;
                pageEndRows.addElement(new Long(currentRow));
                if (!reconstruct.isOverflow()) {
                    totalPage = currentPage;
                } else {
                    totalPage = -1;
                }
            } else {
                notProcessedBefore = false;
            }
            setProcessedRow(currentRow);
            setTotalRows(reconstruct.getTotalRows());
        } else {
            reconstructEdge = new ReconstructEdge(_results, _dbConnection);
            _text = reconstructEdge.reconstructText();

            setProcessedRow(reconstructEdge.getProcessedRows());
            setProcessedLeaf(reconstructEdge.getLeafCount());
        }
        textConstructed = true;
        if (notProcessedBefore) {
            _textConstructTime = System.nanoTime() / 1000000 - startTime;
        } else {
            _textConstructTime = 0;
        }
        System.out.println("Get Text Ended, Sucxent. Text Time: " + _textConstructTime);

        System.runFinalization();
        System.gc();

        return _text;
    }

    public void setText(String string) {
        _text = string;
        reconstruct = null;
        reconstructEdge = null;
    }

    // get the maximum DocId from the currently connected database
    public int maxDocID() throws Exception {
        if (_mode == EDGE) {
            return 1;
        }
        int docId;
        Statement _stmt = _dbConnection.createStatement();
        ResultSet rs = _stmt.executeQuery("SELECT MAX(Id) FROM Document");

        if (rs.next()) {
            docId = rs.getInt(1);
        } else {
            docId = 0;
        }

        rs.close();
        _stmt.close();
        return docId;
    }

    // check if table model is constructed, should be isTableConstruct()
    public boolean getTableConstructed() {
        return tableConstructed;
    }

    // check if tree model is constructed
    public boolean getTreeConstructed() {
        return treeConstructed;
    }

    // check if the XML text result is constructed
    public boolean getTextConstructed() {
        return textConstructed;
    }

    // set the join type
    public void setJoinType(int joinType) {
        _joinType = joinType;
    }

    // set the force order option
    public void setForceOrder(boolean forceOrder) {
        _forceOrder = forceOrder;
    }

    // set the fast 1 option
    public void setLimit(boolean limit) {
        _limit = limit;
    }

    // adding the optimization line
    public String optimizingLine() {
        String line = "\nOPTION (";
        boolean first = true;

        switch (_joinType) {
            case 1:
                line += "LOOP JOIN";
                first = false;
                break;
            case 2:
                line += "HASH JOIN";
                first = false;
                break;
            case 3:
                line += "MERGE JOIN";
                first = false;
                break;
            default:
        }

        if (_forceOrder) {
            if (first == false) {
                line += ",";
            }

            line += "FORCE ORDER";
            first = false;
        }

        if (_limit) {
            if (first == false) {
                line += ",";
            }

            line += "FAST 1";
            first = false;
        }

        line += (")");

        if (first == true) {
            return "";
        } else {
            return line;
        }
    }

    // get the various timings
    public long getTreeConstructTime() {
        return _treeConstructTime;
    }

    public long getTableConstructTime() {
        return _tableConstructTime;
    }

    public long getTextConstructTime() {
        return _textConstructTime;
    }

    public long getLoadTime() {
        return _loadTime;
    }

    public long getTranslateTime() {
        return _translateTime;
    }

    public long getExecuteTime() {
        return _executeTime;
    }

    // clear all the timings stored in this class
    public void clearAllTime() {
        _treeConstructTime = 0;
        _tableConstructTime = 0;
        _textConstructTime = 0;
        _loadTime = 0;
        _translateTime = 0;
        _executeTime = 0;

    }

    // clear only the construction time
    public void clearConstructTime() {
        _treeConstructTime = 0;
        _tableConstructTime = 0;
        _textConstructTime = 0;
    }

    // return if a translation has already been done
    public String getTranslated() {
        return _translated;
    }

    // get the mode, mode 0 for Sucxent++, mode 1 for Edge
    public int getMode() {
        return _mode;
    }

    public void setMode(int mode) {
        _mode = mode;
    }

    // getting database, username and password
    public String getDbName() {
        return _dbName;
    }

    public String getUsr() {
        return _usr;

    }

    public String getPass() {
        return _pass;
    }

    // check if there is a result returned
    public boolean isResult() {
        return isThereResult;
    }

    // get and set the row in the current resultset that the pointer should
    // point to
    public long getCurrentRow() {
        return currentRow;
    }

    public void setCurrentRow(long current) {
        currentRow = current;
    }

    public void setTotalRows(long total) {
        totalRow = total;
    }

    // get and set the processed row, processed row is the rows that had been
    // processed by reconstruction so far
    public long getProcessedRow() {
        return processedRow;
    }

    public boolean setProcessedRow(long process) {
        if (process > processedRow) {
            processedRow = process;
            return true;
        } else {
            return false;
        }

    }

    // processed leaf is the number of leaf nodes that had been processed by
    // reconstruction so far
    public long getProcessedLeaf() {
        return processedLeaf;
    }

    public void setProcessedLeaf(long process) {
        processedLeaf = process;
    }

    // total rows in the query result
    public long getTotalRows() {
        return totalRow;
    }

    // get the current page
    public int getCurrentPage() {
        return currentPage;
    }

    // number of rows processed by reconstruction so far
    public long getProcessedRows() {
        return processedRow;
    }

    // setting text constructed to false
    public void setTextConstructedFalse() {
        textConstructed = false;
    }

    // set the parameters to the previous or next page
    public boolean setPreviousPage() {
        return setPage(currentPage + 1);
    }

    public boolean setNextPage() {
        return setPage(currentPage - 1);
    }

    // get the number of pages processed so far
    public int getProcessedPage() {
        return processedPage;
    }

    // get the total number of page
    public int getTotalPage() {
        return totalPage;
    }

    // set the parameters to the required page
    public boolean setPage(int page) {
        if (page == currentPage) {
            return false;
        }
        if (page > processedPage + 1) {
            return false;
        }
        if (totalPage > 0 && page > totalPage) {
            return false;
        }

        textConstructed = false;
        if (page == processedPage + 1) {
            currentPage = page - 1;
            nextRow = 0;
            currentRow = ((Long) pageEndRows.lastElement()).longValue();
            newPage = true;
        } else if (page == 1) {
            currentPage = 0;
            currentRow = 0;
            nextRow = ((Long) pageEndRows.firstElement()).longValue();
        } else {
            currentPage = page - 1;
            currentRow = ((Long) pageEndRows.elementAt(currentPage - 1)).longValue();
            nextRow = ((Long) pageEndRows.elementAt(currentPage)).longValue();
        }
        return true;
    }

    public void setNewPage(boolean b) {
        newPage = b;
    }

    public boolean getNewPage() {
        return newPage;
    }

    public void addDatabaseColin(String userName, String pass, String db, int mode, String[] sourceNM)
            throws Exception {
        String url = "jdbc:sqlserver://" + _DBHost + ":1433;databaseName=" + db;
        _dbConnection = DriverManager.getConnection(url, userName, pass);
        _usr = userName;
        _pass = pass;
        _mode = mode;
        _dbName = db;
        String sqlscript = "", str; // erwin 2007-07-04 add

        String sqlscript2 = "";

        for (int i = 0; i < sourceNM.length; i++) {
            BufferedReader in;
            in = new BufferedReader(new FileReader("script/SUCXENT-Colin.sql"));
            while ((str = in.readLine()) != null) {
                str = str.replace("#SRCNAME#", sourceNM[i]);
                sqlscript = sqlscript + str + "\n";
            }
            in.close();

            in = new BufferedReader(new FileReader("script/SUCXENT-SP-Colin.sql"));
            while ((str = in.readLine()) != null) {
                str = str.replace("#SRCNAME#", sourceNM[i]);
                sqlscript2 = sqlscript2 + str + "\n";
            }
            in.close();

        }
        Statement statement = _dbConnection.createStatement();
        statement.execute(sqlscript);
        statement.execute(sqlscript2);
        statement.close();
    }
}