translate XQuery to SQL statements

package dbsucxent.construct;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.tree.DefaultMutableTreeNode;

/**
 * @author Lee Tzyy Ming
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class Reconstruct {

    ResultSet _results, _longResult;
    String _unsplitPaths[];
    String _paths[][];
    boolean _pathIsAttribute[];
    Vector _attribute = new Vector();
    long _deweyOrderSum, _oldDeweyOrderSum;
    long[] _rValue;
    Connection _dbConnection;
    FileOutputStream fileOutputStream;
    StringBuilder stringResult;
    DefaultMutableTreeNode top;
    DefaultMutableTreeNode current;
    public final int TREE = 1,  TEXT = 2,  FILE = 3;
    int _output = 0, _queryDepth, _pathID, _oldPathID;
    long currentRow = 0;
    long endRow = 0;
    long totalRow = 0;
    long nodeCount = 0;
    boolean overflow = false;
    String _queryLastElement, _startElement, rootName = "results";
    private int stringSize = 0;

    // initialize the reconstruction with resultset result, connection dbConnection, the queryDepth
    //	the current row to start recconstruction from and the end row where the reconstruction will stop here
    public Reconstruct(ResultSet results, Connection dbConnection, String queryLastElement, int queryDepth, long current, long end) {
        _results = results;
        _dbConnection = dbConnection;
        _queryLastElement = queryLastElement;
        _queryDepth = queryDepth;
        currentRow = current;
        endRow = end;
    }

    // reconstruct and output the result to a file
    public void reconstructFile(String filename) throws FileNotFoundException, SQLException, IOException {
        _output = FILE;

        if (filename.trim().equals("")) {
            filename = "queryResults";
        }
        File file = new File(filename + ".xml");
        fileOutputStream = new FileOutputStream(file);

        fileOutputStream.write(("<" + rootName + ">").getBytes());
        reconstruct();
        fileOutputStream.write(("</" + rootName + ">").getBytes());

        fileOutputStream.close();

        openFile(filename);
    }

    // reconstruct and output the result as XML text stored in a string
    public String reconstructText() throws FileNotFoundException, SQLException, IOException {
        //timer = new Timer();
        stringResult = new StringBuilder();
        _output = TEXT;
        stringResult.append("<" + rootName + ">");
        reconstruct();
        stringResult.append("</" + rootName + ">");
        return stringResult.toString();
    }

    // reconstruct and output the result as a tree
    public DefaultMutableTreeNode reconstructTree() throws FileNotFoundException, SQLException, IOException {
        _output = TREE;
        DefaultMutableTreeNode _top = new DefaultMutableTreeNode(rootName);
        current = _top;
        reconstruct();
        return _top;
    }

    // given the pathID, this method will return the elements in the path in an array of strings
    private String[] getPath(int pathID) {
        if (_paths[pathID - 1] == null) {
            setPath(pathID);
        }
        return _paths[pathID - 1];
    }

    // check if this pathID is the path to an attribute
    private boolean isPathAttribute(int pathID) {
        if (_paths[pathID - 1] == null) {
            setPath(pathID);
        }
        return _pathIsAttribute[pathID - 1];
    }

    // given a pathId, split the path and obtain useful information from it, only obtain path information when needed
    private void setPath(int pathID) {
        // split the path into its path elements
        _paths[pathID - 1] = _unsplitPaths[pathID - 1].split("\\.");
        int i = 1;
        _paths[pathID - 1][_paths[pathID - 1].length - 1] = _paths[pathID - 1][_paths[pathID - 1].length - 1].trim();

        // remove the # at end of each path element
        for (i = 1; i < _paths[pathID - 1].length; i++) {
            _paths[pathID - 1][i] = _paths[pathID - 1][i].substring(0, (_paths[pathID - 1][i]).length() - 1);
        }
        char lastElement = _paths[pathID - 1][i - 1].charAt(0);

        // if @ is found at starting of last path element, path is an attribute and set the attribute array
        if (lastElement == '@') {
            _pathIsAttribute[pathID - 1] = true;
        } else {
            _pathIsAttribute[pathID - 1] = false;
        }
    }

    // start the reconstruction process
    public void reconstruct() throws FileNotFoundException, SQLException, IOException {
        int newBranchOrder;
        _rValue = getRValue(_dbConnection);
        _unsplitPaths = getPaths(_dbConnection);
        _paths = new String[_unsplitPaths.length][];
        _pathIsAttribute = new boolean[_unsplitPaths.length];


        long maxMem = (Runtime.getRuntime().maxMemory() / 10) * 5;	// set the max memory to use before throw out of memory exception

        _results.last();
        totalRow = _results.getRow();

        // go to the first node to be constructed, either from row 0 or from a specify location currentRow
        if (currentRow == 0) {
            _results.first();
        } else {
            _results.absolute((int) currentRow);
        }

        try {
            _pathID = _results.getInt(2);
        } catch (SQLException e) {
            writeLeaf("No results");
            return;
        }

        // write out the paths from queryDepth to the first leaf node
        int j;
        for (j = _queryDepth; j < getPath(_pathID).length - 1; j++) {
            startElement(getPath(_pathID)[j]);
        }

        _oldDeweyOrderSum = _results.getLong(4);
        _oldPathID = _pathID;
        String startValue = _results.getString(1);

        if (isPathAttribute(_pathID) == false) {
            if (getPath(_pathID).length > j)
 //klarinda 20060804 - add if condition to make sure no
// array out of bound exception (if the 1st result is text w/o parent element)
            {
                startElement(getPath(_pathID)[j]);
            }

            if (startValue != null) {
                writeLeaf(startValue.trim());
            } else {
                writeLeaf(" ");
            }

        } else {
            String attributeName = getPath(_pathID)[j];
            attributeName = attributeName.substring(1, attributeName.length());
            if (startValue != null) {
                writeAttribute(attributeName + " = '" + startValue.trim() + "'");
            } else {
                writeAttribute(attributeName + " = ' '");
            }
        }
        currentRow++;

        // loop through all nodes until we reach the last node
        while (_results.next()) {
            _pathID = _results.getInt(2);

            _deweyOrderSum = _results.getLong(4);

            long deweyDiff = _deweyOrderSum - _oldDeweyOrderSum;

            // find the nearest common ancestor using theorem 3
            for (newBranchOrder = _results.getInt(3) - 2; newBranchOrder >= 0; newBranchOrder--) {
                if (deweyDiff < _rValue[newBranchOrder]) {
                    break;
                }			//klarinda 20060817
            }
            newBranchOrder += 2;

            if (newBranchOrder < _queryDepth - 1) {
                newBranchOrder = _queryDepth - 1;
            }

            int e = (getPath(_oldPathID).length) - 1;
            if (isPathAttribute(_oldPathID) == true) {
                e--;
                newBranchOrder = e; //klarinda 20061109 (because separate attribute table)
            }

            // close path till the common ancestor
            for (; e > newBranchOrder; e--) {
                endElement(getPath(_oldPathID)[e]);
            }

            String value = _results.getString(1);
            int s = newBranchOrder + 1;
            // open path from common ancestor to second node
            for (; s < getPath(_pathID).length - 1; s++) {
                startElement(getPath(_pathID)[s]);
            }

            // write out the leaf value
            if (isPathAttribute(_pathID) == false) {
                if (s < getPath(_pathID).length) {
                    startElement(getPath(_pathID)[s]);
                }
                if (value != null) {
                    writeLeaf(value.trim());
                } else {
                    writeLeaf(" ");
                }
            } else {
                String attributeName = getPath(_pathID)[s];
                attributeName = attributeName.substring(1, attributeName.length());
                if (value != null) {
                    writeAttribute(attributeName + " = '" + value.trim() + "'");
                } else {
                    writeAttribute(attributeName + " = ' '");
                }
            }

            // replace first node with second node and move on to the next node
            _oldDeweyOrderSum = _deweyOrderSum;
            _oldPathID = _pathID;

            currentRow++;

            if (endRow == 0 && (Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()) > maxMem) {
                overflow = true;
                break;
            } else if (endRow != 0 && currentRow == endRow) {
                overflow = true;
                break;
            }

        }

        if (overflow) {
            _results.next();
            if (_results.getInt(3) > _queryDepth) {
                nodeCount = nodeCount - _results.getInt(3) + _queryDepth - 1;
            }
        }


        // last node, therefore close path till queryDepth
        if (isPathAttribute(_oldPathID) == false) {
            for (int i = getPath(_oldPathID).length - 1; i >= _queryDepth; i--) {
                endElement(getPath(_oldPathID)[i]);
            }
        } else {
            for (int i = getPath(_oldPathID).length - 2; i >= _queryDepth; i--) {
                endElement(getPath(_oldPathID)[i]);
            }
        }

    }

    // open an XML file
    public static void openFile(String filename) throws IOException {
        Runtime rt = Runtime.getRuntime();

        String[] cmd = new String[4];
        cmd[0] = "cmd.exe";
        cmd[1] = "/C";
        cmd[2] = "start";
        cmd[3] = filename + ".xml";

        Process p = rt.exec(cmd);
    }

    // write an attribute, stores attribute in a vector an delay its write
    private void writeAttribute(String attribute) {
        nodeCount++;
        _attribute.add(attribute);
    }

    // declare a start element, the writing of start element is delay by 1
    private void startElement(String element) throws IOException {
        nodeCount++;
        writeStartElement();
        _startElement = element;
    }

    // write the previous stored start element and its corresponding attribute if any

    // writing in 3 different types of output depending on which output the user had choosen
    private void writeStartElement() throws IOException {
        String attribute;
        if ((_startElement == null) || (_startElement.length() == 0)) {
            return;
        }
        if (_output == FILE) {
            fileOutputStream.write('<');
            fileOutputStream.write(_startElement.getBytes());
            for (int i = 0; i < _attribute.size(); i++) {
                attribute = "  " + (String) _attribute.elementAt(i);
                fileOutputStream.write(attribute.getBytes());
            }
            fileOutputStream.write('>');
        } else if (_output == TEXT) {

            if (_startElement.length() > 0) {
                stringResult.append("<" + _startElement);
                for (int i = 0; i < _attribute.size(); i++) {
                    stringResult.append("  " + (String) _attribute.elementAt(i));
                }

                stringResult.append(">");
            }
        } else {	// (output == TREE)

            DefaultMutableTreeNode node = new DefaultMutableTreeNode(_startElement);
            current.add(node);

            for (int i = 0; i < _attribute.size(); i++) {
                current = node;
                String Attr = ((String) _attribute.elementAt(i)).trim();
                String[] temp = Attr.split("=");
                DefaultMutableTreeNode attrNode = new DefaultMutableTreeNode("@" + temp[0].trim());
                current.add(attrNode);
                current = attrNode;

                attrNode = new DefaultMutableTreeNode(temp[1].trim());
                current.add(attrNode);
            }


            current = node;
        }
        _attribute.clear();
        _startElement = null;
    }

    // declare an end element and write out this element immediately
    private void endElement(String element) throws IOException {
        writeStartElement();
        if (_output == FILE) {
            fileOutputStream.write('<');
            fileOutputStream.write('/');
            fileOutputStream.write(element.getBytes());
            fileOutputStream.write('>');
        } else if (_output == TEXT) {
            if (element.length() > 0) {
                stringResult.append("</" + element + ">");
            }
        } else {
            current = (DefaultMutableTreeNode) current.getParent();
        }
    }

    // write the leaf value
    private void writeLeaf(String leaf) throws IOException {

        writeStartElement();
        if (_output == FILE) {
            fileOutputStream.write(leaf.getBytes());
        } else if (_output == TEXT) {
            stringResult.append(leaf);
        } else {	// (output == TREE)
            DefaultMutableTreeNode node = new DefaultMutableTreeNode(leaf);
            current.add(node);
        }
    }

    // used previously on SQL server2000, with a separate table to store long data, SQL 2005 uses max type which is faster
    public ResultSet getLongResult(Connection dbConnection) throws SQLException {
        int docId = 1;
        Statement statement = _dbConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        ResultSet results = statement.executeQuery("SELECT LeafValue, DeweyOrderSum FROM TextContent WHERE DocId=" + docId);

        return results;
    }

    // get the list og RValues in an array
    public long[] getRValue(Connection _dbConnection) throws SQLException {
        int docId = 1;
        long[] rvalue;

        Statement statement = _dbConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        ResultSet results = statement.executeQuery("SELECT RValue FROM DocumentRValue WHERE DocId=" + docId);

        results.last();
        rvalue = new long[results.getRow()];
        results.beforeFirst();

        for (int i = 0; results.next(); i++) {
            rvalue[i] = results.getLong(1);
        }

        results.close();
        statement.close();
        results = null;
        statement = null;

        return rvalue;
    }

    // get all the paths and return it in an array of string
    public String[] getPaths(Connection _dbConnection) throws SQLException {
        String[] unsplitPaths;

        if (_dbConnection.getAutoCommit()) {
        }
        _dbConnection.commit();
        Statement statement = _dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY); 	// ms sql server 2005
        ResultSet results = statement.executeQuery("SELECT PathExp FROM Path ORDER BY PathID");
        results.last();
        unsplitPaths = new String[results.getRow()];
        results.beforeFirst();

        for (int i = 0; results.next(); i++) {
            unsplitPaths[i] = results.getString(1);
        }

        results.close();
        statement.close();
        results = null;
        statement = null;

        return unsplitPaths;
    }

    public long getRow() {
        return currentRow;
    }

    public long getTotalRows() {
        return totalRow;
    }

    public boolean isOverflow() {
        return overflow;
    }

    public int getSize() {
        return stringSize;
    }
}