Package net.sf.jabref.sql

Source Code of net.sf.jabref.sql.SQLutil

/*
* SQLutil.java
*
* Created on October 4, 2007, 5:28 PM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/

package net.sf.jabref.sql;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.ListIterator;
import java.util.Set;

import net.sf.jabref.BibtexDatabase;
import net.sf.jabref.BibtexEntry;
import net.sf.jabref.BibtexEntryType;
import net.sf.jabref.BibtexFields;
import net.sf.jabref.BibtexString;
import net.sf.jabref.Globals;
import net.sf.jabref.MetaData;
import net.sf.jabref.Util;
import net.sf.jabref.export.FileActions;
import net.sf.jabref.groups.AbstractGroup;
import net.sf.jabref.groups.AllEntriesGroup;
import net.sf.jabref.groups.ExplicitGroup;
import net.sf.jabref.groups.GroupTreeNode;
import net.sf.jabref.groups.KeywordGroup;
import net.sf.jabref.groups.SearchGroup;

/**
*
* @author pattonlk
*/
public class SQLutil {

    public enum DBTYPE {
        MYSQL, DERBY
    }

    private static ArrayList<String> fields = null;
    private static String fieldstr = null;

    /**
     * returns the DBTYPE associated with a DBStrings ServerType
     *
     * @param dbstrings
     *          The DBStrings to query
     * @return The DBTYPE associated withthe dbstrings ServerType
     */
    public static DBTYPE getDBType (DBStrings dbstrings) {

        DBTYPE dbtype = null;
        String srvtype = dbstrings.getServerType();

        if (srvtype.equalsIgnoreCase("mysql")) {
            dbtype = DBTYPE.MYSQL;
        }
        if (srvtype.equalsIgnoreCase("derby")) {
            dbtype = DBTYPE.DERBY;
        }

        return dbtype;
    }

    /**
     * This routine returns the JDBC url corresponding to the DBStrings input.
     *
     * @param dbstrings
     *          The DBStrings to use to make the connection
     * @return The JDBC url corresponding to the input DBStrings
     */
    public static String createJDBCurl (DBStrings dbs) {

        String url = "";
        String servertype = dbs.getServerType();

        if (servertype.equalsIgnoreCase("mysql")) {
            url = "jdbc:" + dbs.getServerType().toLowerCase() + "://"
                          + dbs.getServerHostname() + "/"
                          + dbs.getDatabase();
        }

        if (servertype.equalsIgnoreCase("derby")) {
            url = "jdbc:" + dbs.getServerType().toLowerCase() + ":"
                          + dbs.getDatabase() + ";create=true";
        }

        return url;
    }

    public static String getJDBCdriver (DBStrings dbstrings) {

        String driver = "";
        String servertype = dbstrings.getServerType();

        if (servertype.equalsIgnoreCase("mysql")) {
            driver ="com.mysql.jdbc.Driver";
        }

        if (servertype.equalsIgnoreCase("derby")) {
            driver = "org.apache.derby.jdbc.EmbeddedDriver";
        }

        return driver;
           
    }

    /**
     * This routine accepts the location of a MySQL database specified as a url as
     * well as the username and password for the MySQL user with appropriate access
     * to this database.  The routine returns a valid Connection object if the MySQL
     * database is successfully opened. It returns a null object otherwise.
     */
    public static Connection connectToDB (DBStrings dbstrings)
        throws Exception {

        String url = createJDBCurl(dbstrings);
        String drv = getJDBCdriver(dbstrings);

        Class.forName (drv).newInstance ();
        Connection conn = DriverManager.getConnection (url,
                           dbstrings.getUsername(), dbstrings.getPassword());
      
        return conn;

    }   
  

    /**
     * Utility method for processing DML with proper output
     *
     * @param out
     *          The output (PrintStream or Connection) object to which the DML should be sent
     * @param dml
     *          The DML statements to be processed
     * @return the result of the statement
     */
    private static Object processDMLWithResults ( Object out, String dml)
                            throws SQLException {

        if ( out instanceof PrintStream) {
            // TODO: how to handle the PrintStream case?
            PrintStream fout = (PrintStream) out;
            fout.println(dml);
            return null;
        }

        if ( out instanceof Connection) {
            Connection conn = (Connection) out;
            return execDMLWithResults(conn, dml);
        }

        return null;
    }

    private static String processDMLWithSingleResult ( Connection conn, String query) throws SQLException {
        Object res = execDMLWithResults(conn, query);
        if (res instanceof Statement) {
            Statement st = (Statement)res;
            ResultSet rs = st.getResultSet();
            rs.next();
            String returned = rs.getString(1);
            st.close();
            return returned;
        }
        else return null;
    }

    /**
     * Utility method for executing DML
     *
     * @param conn
     *          The DML Connection object that will execute the SQL
     * @param dml
     *          The DML statements to be executed
     */
    public static Statement execDMLWithResults(Connection conn, String dml) throws SQLException {
        // System.out.println(dml); // remove
        Statement stmnt = conn.createStatement();
        stmnt.executeQuery(dml);
        SQLWarning warn = stmnt.getWarnings();
        if (warn!=null) {
            //TODO handle SQL warnings
            System.out.println(warn.toString());
            System.out.println("("+dml+")");
        }
        return stmnt;
    }


    /**
     * Utility method for processing DML with proper output
     *
     * @param out
     *          The output (PrintStream or Connection) object to which the DML should be sent
     * @param dml
     *          The DML statements to be processed
     */
    private static void processDML ( Object out, String dml)
                            throws SQLException {

        if ( out instanceof PrintStream) {
            PrintStream fout = (PrintStream) out;
            fout.println(dml);
        }

        if ( out instanceof Connection) {
            Connection conn = (Connection) out;
            execDML(conn, dml);
        }

    }


    /**
     * Utility method for executing DML
     *
     * @param conn
     *          The DML Connection object that will execute the SQL
     * @param dml
     *          The DML statements to be executed
     */
    public static void execDML(Connection conn, String dml) throws SQLException {
        // System.out.println(dml); // remove
        Statement stmnt = conn.createStatement();
        stmnt.execute(dml);
        SQLWarning warn = stmnt.getWarnings();
        if (warn!=null) {
            //TODO handle SQL warnings
            System.out.println(warn.toString());
            System.out.println("("+dml+")");
        }
        stmnt.close();
    }

   
    public static ArrayList<String> getFields() {
        if (fields == null) {
            refreshFields();
        }
        return fields;
    }


    /**
     * loop through entry types to get required, optional, general and utility
     * fields for this type.
     */
    public static void refreshFields() {

        if (fields==null) {
            fields = new ArrayList<String>();
        } else {
            fields.clear();
        }

        for (BibtexEntryType val : BibtexEntryType.ALL_TYPES.values()) {
            fields = uniqueInsert(fields, val.getRequiredFields());
            fields = uniqueInsert(fields, val.getOptionalFields());
            fields = uniqueInsert(fields, val.getGeneralFields());
            fields = uniqueInsert(fields, val.getUtilityFields());
        }
        //fields = uniqueInsert(fields, new String[] {"owner", "timestamp"});

        // create comma separated list of field names
        fieldstr = "";
        for (int i = 0; i < fields.size(); i++) {
            if (i > 0)
                fieldstr = fieldstr + ", ";
            fieldstr = fieldstr + fields.get(i);
        }

    }


    /**
     * Inserts the elements of a String array into an ArrayList making sure not
     * to duplicate entries in the ArrayList
     *
     * @param list
     *            The ArrayList containing unique entries
     * @param array
     *            The String array to be inserted into the ArrayList
     * @return The updated ArrayList with new unique entries
     */
    private static ArrayList<String> uniqueInsert(ArrayList<String> list,
            String[] array) {

        if (array != null) {
            for (int i = 0; i < array.length; i++) {
                if (!list.contains(array[i]))
                    list.add(array[i]);
            }
        }
        return list;
    }


    /**
     * Accepts the BibtexDatabase and MetaData, generates the DML required to
     * create and populate SQL database tables, and writes this DML to the
     * specified output file.
     *
     * @param database
     *          The BibtexDatabase to export
     * @param metaData
     *          The MetaData object containing the groups information
     * @param keySet
     *          The set of IDs of the entries to export.
     * @param file
     *          The name of the file to which the DML should be written
     */
    public static void exportDatabase(final BibtexDatabase database,
        final MetaData metaData, Set<String> keySet, String file, DBTYPE dbtype )
        throws Exception {

        // open output file
        File outfile = new File(file);
        if (outfile.exists())
            outfile.delete();

        BufferedOutputStream writer = null;
        writer = new BufferedOutputStream( new FileOutputStream( outfile ) );
        PrintStream fout = null;
        fout = new PrintStream( writer );


        exportDatabase_worker(dbtype, database, metaData, keySet, fout);

        fout.close();

    }

    public static Object[] importDatabase(Set<String> keySet,
                                      DBStrings dbStrings)
        throws Exception {

                DBTYPE dbtype = getDBType(dbStrings);

        Object[] result = null;
        Connection conn = null;

        try {

            conn = SQLutil.connectToDB(dbStrings);

            // conn.setAutoCommit(false);

            result = importDatabase_worker(dbtype, keySet, conn);

            /*if (!conn.getAutoCommit()) {
                conn.commit();
                conn.setAutoCommit(true);
            }*/

            conn.close();

        } catch (SQLException ex) {

            throw ex;
        }

        return result;
    }

     private static Object[] importDatabase_worker (DBTYPE dbtype,
            Set<String> keySet, Connection conn) throws Exception {

         BibtexDatabase database = new BibtexDatabase();

         // Find entry type IDs and their mappings to type names:
         HashMap<String, BibtexEntryType> types = new HashMap<String, BibtexEntryType>();
         Object res = processDMLWithResults(conn,"SELECT entry_types_id,label FROM entry_types;");
         if (res instanceof Statement) {
             Statement statement = (Statement)res;
             ResultSet rs = statement.getResultSet();
             while ( rs.next()) {
                types.put(rs.getString(1), BibtexEntryType.getType(rs.getString(2)));
             }
             statement.close();
         }
         for (Iterator<String> iterator = types.keySet().iterator(); iterator.hasNext();) {
             iterator.next();
         }

          // Read the column names from the entry table:
         res = processDMLWithResults(conn, "SHOW columns FROM entries;");
         ArrayList<String> colNames = new ArrayList<String>();
         if (res instanceof Statement) {
             Statement statement = (Statement)res;
             ResultSet rs = statement.getResultSet();
             boolean started = false;
             while ( rs.next()) {
                if (started)
                    colNames.add(rs.getString(1));
                 else if (rs.getString(1).equals("cite_key"))
                    started = true;
             }
             statement.close();
         }

         // Read the entries and create BibtexEntry instances:
         HashMap<String,BibtexEntry> entries = new HashMap<String, BibtexEntry>();
         res = processDMLWithResults(conn, "SELECT * FROM entries;");
         if (res instanceof Statement) {
             Statement statement = (Statement)res;
             ResultSet rs = statement.getResultSet();
             while ( rs.next()) {
                 String id = rs.getString("entries_id");
                 BibtexEntry entry = new BibtexEntry(Util.createNeutralId(),
                         types.get(rs.getString(3)));
                 entry.setField(BibtexFields.KEY_FIELD, rs.getString("cite_key"));
                 for (Iterator<String> iterator = colNames.iterator(); iterator.hasNext();) {
                     String col = iterator.next();
                     String value = rs.getString(col);
                     if (value != null)
                        entry.setField(col, value);
                     //System.out.println("col: "+col+": "+rs.getString(col));
                 }
                 entries.put(id, entry);
                 database.insertEntry(entry);
             }
             statement.close();
         }

         // Import strings and preamble:
         res = processDMLWithResults(conn, "SELECT * FROM strings;");
         if (res instanceof Statement) {
             Statement statement = (Statement)res;
             ResultSet rs = statement.getResultSet();
             while ( rs.next()) {
                 String label = rs.getString("label"), content = rs.getString("content");
                 if (label.equals("@PREAMBLE")) {
                     database.setPreamble(content);
                 }
                 else {
                     BibtexString string = new BibtexString(Util.createNeutralId(), label, content);
                     database.addString(string);
                 }
             }
             statement.close();
         }

         MetaData metaData = new MetaData();
         metaData.initializeNewDatabase();

         // Read the groups tree:
         importGroupsTree(dbtype, metaData, entries, conn);

         return new Object[] {database, metaData};

     }

    public static void importGroupsTree(DBTYPE dbtype, MetaData metaData, HashMap<String,BibtexEntry> entries,
                                        Connection conn) throws SQLException {
        Object res = processDMLWithResults(conn, "SELECT * FROM groups ORDER BY groups_id;");
        if (res instanceof Statement) {
            Statement statement = (Statement)res;
            ResultSet rs = statement.getResultSet();
            GroupTreeNode rootNode = new GroupTreeNode(new AllEntriesGroup());
            // Create a lookup map for finding the parent to add each group to:
            HashMap<String, GroupTreeNode> groups = new HashMap<String, GroupTreeNode>();
            LinkedHashMap<GroupTreeNode, String> parentIds = new LinkedHashMap<GroupTreeNode, String>();
           
            while ( rs.next()) {
                AbstractGroup group = null;
                String typeId = findGroupTypeName(rs.getString("group_types_id"), conn);
                if (typeId.equals(AllEntriesGroup.ID)) {
                    // register the id of the root node:
                    groups.put(rs.getString("groups_id"), rootNode);
                }
                else if (typeId.equals(ExplicitGroup.ID)) {
                    group = new ExplicitGroup(rs.getString("label"),
                            rs.getInt("hierarchical_context"));
                }
                else if (typeId.equals(KeywordGroup.ID)) {
                    System.out.println("Keyw: "+ rs.getBoolean("case_sensitive"));
                    group = new KeywordGroup(rs.getString("label"),
                            Util.unquote(rs.getString("search_field"), '\\'),
                            Util.unquote(rs.getString("search_expression"), '\\'),
                            rs.getBoolean("case_sensitive"), rs.getBoolean("reg_exp"),
                            rs.getInt("hierarchical_context"));
                }
                else if (typeId.equals(SearchGroup.ID)) {
                    System.out.println("Search: "+ rs.getBoolean("case_sensitive"));
                    group = new SearchGroup(rs.getString("label"),
                            Util.unquote(rs.getString("search_expression"), '\\'),
                            rs.getBoolean("case_sensitive"), rs.getBoolean("reg_exp"),
                            rs.getInt("hierarchical_context"));
                }

                if (group != null) {
                    GroupTreeNode node = new GroupTreeNode(group);
                    parentIds.put(node, rs.getString("parent_id"));
                    groups.put(rs.getString("groups_id"), node);
                }
            }
            statement.close();

            // Ok, we have collected a map of all groups and their parent IDs,
            // and another map of all group IDs and their group nodes.
            // Now we need to build the groups tree:
            for (Iterator<GroupTreeNode> i=parentIds.keySet().iterator(); i.hasNext();) {
                GroupTreeNode node = i.next();
                String parentId = parentIds.get(node);
                // Look up the parent:
                GroupTreeNode parent = groups.get(parentId);
                if (parent == null) {
                    // TODO: missing parent
                }
                else {
                    parent.add(node);
                }
            }

            // If we have explicit groups, set up group membership:
            res = processDMLWithResults(conn, "SELECT * FROM entry_group;");
            if (res instanceof Statement) {
                statement = (Statement)res;
                rs = statement.getResultSet();
                while ( rs.next()) {
                    String entryId = rs.getString("entries_id"),
                            groupId = rs.getString("groups_id");
                    GroupTreeNode node = groups.get(groupId);
                    if ((node != null) && (node.getGroup() instanceof ExplicitGroup)) {
                        ExplicitGroup group = (ExplicitGroup)node.getGroup();
                        group.addEntry(entries.get(entryId));
                    } else {
                        // TODO: unable to find explicit group with the given id
                    }
                }
                statement.close();
            }

            // Finally, set the groups tree for the metadata:
            metaData.setGroups(rootNode);
        }
    }

    /**
     * Look up the group type name from the type ID in the database.
     * @param groupId The database's groups id
     * @param conn The database connection
     * @return The name (JabRef type id) of the group type.
     * @throws SQLException
     */
    public static String findGroupTypeName(String groupId, Connection conn) throws SQLException {
        return processDMLWithSingleResult(conn, "SELECT label FROM group_types WHERE group_types_id=\""+groupId+"\";");
    }

    /**
     * Accepts the BibtexDatabase and MetaData, generates the DML required to
     * create and populate SQL database tables, and writes this DML to the
     * specified SQL database.
     *
     * @param database
     *          The BibtexDatabase to export
     * @param metaData
     *          The MetaData object containing the groups information
     * @param keySet
     *          The set of IDs of the entries to export.
     * @param dbStrings
     *          The necessary database connection information
     */
    public static void exportDatabase(final BibtexDatabase database,
        final MetaData metaData, Set<String> keySet, DBStrings dbStrings)
        throws Exception {

        DBTYPE dbtype = getDBType(dbStrings);

        Connection conn = null;

        try {

            conn = SQLutil.connectToDB(dbStrings);

            // conn.setAutoCommit(false);

            exportDatabase_worker(dbtype, database, metaData, keySet, conn);

            if (!conn.getAutoCommit()) {
                conn.commit();
                conn.setAutoCommit(true);
            }

            conn.close();

        } catch (SQLException ex) {

            if (conn != null) {
                if (!conn.getAutoCommit()) {
                    conn.rollback();
                }
            }

            throw ex;
        }
    }


   /**
     * Worker method for the exportDatabase methods.
     *
     * @param dbtype
     *          The DBTYPE of the database
     * @param database
     *          The BibtexDatabase to export
     * @param metaData
     *          The MetaData object containing the groups information
     * @param keySet
     *            The set of IDs of the entries to export.
     * @param out
     *          The output (PrintStream or Connection) object to which the DML should be written.
     */
    private static void exportDatabase_worker (DBTYPE dbtype,
            final BibtexDatabase database, final MetaData metaData,
            Set<String> keySet, Object out) throws Exception{

        List<BibtexEntry> entries = FileActions.getSortedEntries(database,
            keySet, false);

        // create MySQL tables
        dmlCreateTables(dbtype,out);

        // populate entry_type table
        dmlPopTab_ET(out);

        // populate entries table
        dmlPopTab_FD(entries,out);

        // populate strings table:
        dmlPopTab_ST(database,out);

        GroupTreeNode gtn = metaData.getGroups();

        // populate group_types table
        dmlPopTab_GT(out);


        // populate groups table
        dmlPopTab_GP(gtn,out);
       
    // populate entry_group table
        dmlPopTab_EG(gtn,out);
    }

   
    /**
     * Writes the table creation DML to the specififed file.
     *
     * @param dbtype
     *          Indicates the type of database to be written to
     * @param fout
     *          The output (PrintStream or Connection) object to which the DML should be written
     */
    private static void dmlCreateTables(DBTYPE dbtype, Object out)
                                throws SQLException{

        // make sure fields are initialized
        if (fields==null) {
            refreshFields();
        }

        // build the DML tables specification
        String dml1 = "", dml2 = "";
        switch (dbtype) {
            case MYSQL:

                // drop tables
                processDML(out,"DROP TABLE IF EXISTS entry_types;");
                processDML(out,"DROP TABLE IF EXISTS entries;");
                processDML(out,"DROP TABLE IF EXISTS strings;");
                processDML(out,"DROP TABLE IF EXISTS group_types;");
                processDML(out,"DROP TABLE IF EXISTS groups;");
                processDML(out,"DROP TABLE IF EXISTS entry_group;");

                // generate DML that specifies DB columns corresponding to fields
                dml1 = SQLutil.fieldsAsCols(fields, " VARCHAR(3) DEFAULT NULL");
                dml2 = SQLutil.fieldsAsCols(fields, " TEXT DEFAULT NULL");

                // create tables
                dmlTable_mysql(dml1, dml2, out);

                break;

            case DERBY:

                // drop tables
                if (out instanceof Connection) {

                    Connection conn = (Connection) out;
                    boolean commitNow = conn.getAutoCommit();
                    conn.setAutoCommit(true);

                    //TODO: determine which tables are present, and drop them

                    conn.setAutoCommit(commitNow);

                }

                // generate DML that specifies DB columns corresponding to fields
                dml1 = SQLutil.fieldsAsCols(fields, " VARCHAR(3) DEFAULT NULL");
                dml2 = SQLutil.fieldsAsCols(fields, " LONG VARCHAR DEFAULT NULL");

                // create tables
                dmlTable_derby(dml1, dml2, out);

                break;

            default:
                System.err.println("Error: Do not recognize database enumeration.");
                System.exit(0);
        }

        return;
    }


    /**
     * Generates DML specifying table columns and their datatypes. The output of
     * this routine should be used within a CREATE TABLE statement.
     *
     * @param fields
     *            Contains unique field names
     * @param datatype
     *            Specifies the SQL data type that the fields should take on.
     * @return The DML code to be included in a CREATE TABLE statement.
     */
    private static String fieldsAsCols(ArrayList<String> fields, String datatype) {
        String str = "";
        ListIterator<String> li = fields.listIterator();
        while (li.hasNext()) {
            str = str + li.next() + " " + datatype;
            if (li.hasNext())
                str = str + ", ";
        }
        return str;
    }

    /**
     * Generates DML code necessary to create all tables in a MySQL database,
     * and writes it to appropriate output.
     *
     * @param dml1
     *            Column specifications for fields in entry_type table.
     * @param dml2
     *            Column specifications for fields in entries table.
     * @param out
     *            The output (PrintStream or Connection) object to which the DML should be written.
     * @return DML to create all MySQL tables.
     */
    private static void dmlTable_mysql(String dml1, String dml2, Object out)
            throws SQLException {

        processDML(out,"CREATE TABLE entry_types ( \n"
            + "entry_types_id    INT UNSIGNED  NOT NULL AUTO_INCREMENT, \n"
            + "label       TEXT, \n"
            + dml1
            + ", \n"
            + "PRIMARY KEY (entry_types_id) \n"
            + ");" );
                
        processDML(out,"CREATE TABLE entries ( \n"
            + "entries_id      INTEGER         NOT NULL AUTO_INCREMENT, \n"
      + "jabref_eid      VARCHAR("
      +  Util.getMinimumIntegerDigits()
        + ")   DEFAULT NULL, \n"
            + "entry_types_id  INT UNSIGNED         DEFAULT NULL, \n"
            + "cite_key        VARCHAR(100)     DEFAULT NULL, \n"
            + dml2
            + ",\n"
            + "PRIMARY KEY (entries_id), \n"
      + "INDEX(entry_types_id), \n"
            + "FOREIGN KEY (entry_types_id) REFERENCES entry_types(entry_types_id) \n"
            + ");");

        processDML(out,"CREATE TABLE strings ( \n"
            + "strings_id      INTEGER         NOT NULL AUTO_INCREMENT, \n"
      + "label      VARCHAR(100)  DEFAULT NULL, \n"
        + "content    VARCHAR(200)  DEFAULT NULL, \n"
            + "PRIMARY KEY (strings_id) \n"
            + ");");

        processDML(out,"CREATE TABLE group_types ( \n"
                 + "group_types_id  INTEGER     NOT NULL AUTO_INCREMENT, \n"
                 + "label   VARCHAR(100)    DEFAULT NULL, \n"
                 + "PRIMARY KEY (group_types_id) \n"
                 + ");" );

        processDML(out,"CREATE TABLE groups ( \n"
            + "groups_id       INTEGER         NOT NULL AUTO_INCREMENT, \n"
            + "group_types_id  INTEGER         DEFAULT NULL, \n"
            + "label           VARCHAR(100)    DEFAULT NULL, \n"
            + "parent_id       INTEGER         DEFAULT NULL, \n"
            + "search_field       VARCHAR(100)          DEFAULT NULL, \n"
            + "search_expression  VARCHAR(200)          DEFAULT NULL, \n"
            + "case_sensitive  BOOL          DEFAULT NULL, \n"
            + "reg_exp BOOL DEFAULT NULL, \n"
            + "hierarchical_context INTEGER DEFAULT NULL, \n"
            + "PRIMARY KEY (groups_id) \n"
            + ");");
          
        processDML(out,"CREATE TABLE entry_group ( \n"
            + "entries_id       INTEGER        NOT NULL AUTO_INCREMENT, \n"
            + "groups_id        INTEGER        DEFAULT NULL, \n"
      + "INDEX(entries_id), \n"
      + "INDEX(groups_id), \n"
            + "FOREIGN KEY (entries_id) REFERENCES entries(entries_id), \n"
            + "FOREIGN KEY (groups_id)  REFERENCES groups(groups_id) \n"
            + ");");

        return;

    }

    /**
     * Generates DML code necessary to create all tables in a Derby database,
     * and writes it to appropriate output.
     *
     * @param dml1
     *            Column specifications for fields in entry_type table.
     * @param dml2
     *            Column specifications for fields in entries table.
     * @param out
     *            The output (PrintStream or Connection) object to which the DML should be written.
     * @return DML to create all Derby tables.
     */
    private static void dmlTable_derby(String dml1, String dml2, Object out)
            throws SQLException {

        processDML(out,"CREATE TABLE entry_types ( "
            + "entry_types_id INT  NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, "
            + dml1 + ", "
            + "label LONG VARCHAR"
            + ")" );

        processDML(out,"CREATE TABLE entries ( "
            + "entries_id      INTEGER         NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, "
      + "jabref_eid      VARCHAR("
      +  Util.getMinimumIntegerDigits()
        + ")   DEFAULT NULL, "
            + "entry_types_id  INTEGER         DEFAULT NULL, "
            + "cite_key        VARCHAR(100)     DEFAULT NULL, "
            + dml2
            + ")");
         
        processDML(out,"ALTER TABLE entries ADD CONSTRAINT entries_fk "
                     + "FOREIGN KEY (\"entry_types_id\") REFERENCES \"entry_types\" (\"entry_types_id\")");

        processDML(out,"CREATE TABLE group_types ( "
            + "group_types_id INT  NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, "
            + "label LONG VARCHAR"
            + ")" );

        processDML(out,"CREATE TABLE groups ( "
            + "groups_id       INTEGER         NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, "
            + "group_types_id  INTEGER         DEFAULT NULL, "
            + "label           VARCHAR(100)    DEFAULT NULL, "
            + "parent_id       INTEGER         DEFAULT NULL  "
            + "search_field       VARCHAR(100)          DEFAULT NULL, "
            + "search_expression  VARCHAR(200)          DEFAULT NULL, "
            + "case_sensitive  BOOL          DEFAULT NULL, "
            + "reg_exp BOOL DEFAULT NULL"
            + "hierarchical_context INTEGER DEFAULT NULL, "
            + ")");
          
        processDML(out,"CREATE TABLE entry_group ( "
            + "entries_id       INTEGER        NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, "
            + "groups_id        INTEGER        DEFAULT NULL "
            + ")");

        processDML(out,"ALTER TABLE entry_group ADD CONSTRAINT entries_group_fk"
                     + "FOREIGN KEY (\"entries_id\") REFERENCES \"entry_fields\" (\"entries_id\")");

        processDML(out,"ALTER TABLE entry_group ADD CONSTRAINT groups_fk"
                     + "FOREIGN KEY (\"groups_id\") REFERENCES \"groups\" (\"groups_id\")");

        return;

    }

    /**
     * Generates the DML required to populate the group_types table with
     * JabRef data.
     *
     * @param out
     *  The output (PrintSream or Connection) object to which the DML should be written.
     * @throws SQLException
     */
    private static void dmlPopTab_GT( Object out) throws SQLException{
        String[] typeNames = new String[] {
                AllEntriesGroup.ID, ExplicitGroup.ID, KeywordGroup.ID, SearchGroup.ID};
        for (int i = 0; i < typeNames.length; i++) {
            String typeName = typeNames[i];
            String insert = "INSERT INTO group_types (label) VALUES (\""+typeName+"\");";
            // handle DML according to output type
            processDML(out, insert);
        }


    }
     /**
     * Generates the DML required to populate the entry_types table with jabref
     * data.
     *
     * @param out
     *          The output (PrintSream or Connection) object to which the DML should be written.
     */
    private static void dmlPopTab_ET( Object out) throws SQLException{

        String dml = "";
        String insert = "INSERT INTO entry_types (label, "+fieldstr+") VALUES (";

        ArrayList<String> fieldID = new ArrayList<String>();
        for (int i = 0; i < fields.size(); i++)
            fieldID.add(null);

        // loop through entry types
        for (BibtexEntryType val : BibtexEntryType.ALL_TYPES.values()) {

            // set ID for each field corresponding to its relationship to the
            // entry type
            for (int i = 0; i < fieldID.size(); i++) {
                fieldID.set(i, "");
            }
            fieldID = setFieldID(fields, fieldID, val.getRequiredFields(),
                "req");
            fieldID = setFieldID(fields, fieldID, val.getOptionalFields(),
                "opt");
            fieldID = setFieldID(fields, fieldID, val.getGeneralFields(), "gen");
            fieldID = setFieldID(fields, fieldID, val.getUtilityFields(), "uti");

            // build DML insert statement
            dml = insert + "\"" + val.getName().toLowerCase() + "\"";
            for (int i = 0; i < fieldID.size(); i++) {
                dml = dml + ", ";
                if (fieldID.get(i) != "") {
                    dml = dml + "\"" + fieldID.get(i) + "\"";
                } else {
                    dml = dml + "NULL";
                }
            }
            dml = dml + ");";

            // handle DML according to output type
            processDML(out, dml);

        }

        return;

    }


     /**
     * A utility function for facilitating the assignment of a code to each
     * field name that represents the relationship of that field to a specific
     * entry type.
     *
     * @param fields
     *            A list of all fields.
     * @param fieldID
     *            A list for holding the codes.
     * @param fieldstr
     *            A String array containing the fields to be coded.
     * @param ID
     *            The code that should be assigned to the specified fields.
     * @return The updated code list.
     */
    private static ArrayList<String> setFieldID(ArrayList<String> fields,
        ArrayList<String> fieldID, String[] fieldstr, String ID) {
        if (fieldstr != null) {
            for (int i = 0; i < fieldstr.length; i++) {
                fieldID.set(fields.indexOf(fieldstr[i]), ID);
            }
        }
        return fieldID;
    }


     /**
     * Generates the DML required to populate the entries table with jabref
     * data and writes it to the output PrintStream.
     *
     * @param entries
     *          The BibtexEntries to export    
     * @param out
     *          The output (PrintStream or Connection) object to which the DML should be written.
     */
    private static void dmlPopTab_FD(List<BibtexEntry> entries, Object out)
                            throws SQLException {

        String dml = "";
        String val = "";
        String insert = "INSERT INTO entries (jabref_eid, entry_types_id, cite_key, "
            + fieldstr
            + ") VALUES (";

        // loop throught the entries that are to be exported
        for (BibtexEntry entry : entries) {

            // build DML insert statement
            dml = insert
            + "\"" + entry.getId() + "\""
            + ", (SELECT entry_types_id FROM entry_types WHERE label=\""
            + entry.getType().getName().toLowerCase() + "\"), \""
                  + entry.getCiteKey() + "\"";

            for (int i = 0; i < fields.size(); i++) {
                dml = dml + ", ";
                val = entry.getField(fields.get(i));
                if (val != null) {
                    //escape slashes and quotes for MySQL
                  val = val.replace("\\", "\\\\");
                  val = val.replace("\"", "\\\"");
                  val = val.replace("\'", "\\\'");
                  val = val.replace("`", "\\`");
                  dml = dml + "\"" + val + "\"";
                } else {
                    dml = dml + "NULL";
                }
            }
            dml = dml + ");";

            // handle DML according to output type
            processDML(out, dml);

        }

        return;

    }

    private static void dmlPopTab_ST(BibtexDatabase database, Object out)
                            throws SQLException {

        String insert = "INSERT INTO strings (label, content) VALUES (";

        // Insert preamble as a string:
        if (database.getPreamble() != null) {
            String dml = insert + "\"@PREAMBLE\", "
                    + "\""+Util.quote(database.getPreamble(), "\"", '\\')+"\""
                    + ");";
            processDML(out, dml);
        }

        Set<String> keys = database.getStringKeySet();
        for (Iterator<String> iterator = keys.iterator(); iterator.hasNext();) {
            String key = iterator.next();
            BibtexString string = database.getString(key);
            String dml = insert + "\""+Util.quote(string.getName(), "\"", '\\')+"\", "
                    + "\""+Util.quote(string.getContent(), "\"", '\\')+"\""
                    + ");";
            processDML(out, dml);
        }
    }

     /**
     * Generates the DML required to populate the groups table with jabref
     * data, and writes this DML to the output file.
     *
     * @param cursor
     *            The current GroupTreeNode in the GroupsTree
     * @param out
     *            The output (PrintStream or Connection) object to which the DML should be written.
     */
  private static int dmlPopTab_GP (GroupTreeNode cursor, Object out)
                        throws Exception {
        int cnt = dmlPopTab_GP_worker(cursor, 1, 1, out);
        return cnt;
    }

    /**
     * Recursive worker method for the dmlPopTab_GP methods.
     *
     * @param cursor
     *            The current GroupTreeNode in the GroupsTree
     * @param parentID
     *            The integer ID associated with the cursors's parent node
     * @param ID
     *            The integer value to associate with the cursor
     * @param out
     *            The output (PrintStream or Connection) object to which the DML should be written.
     */
  private static int dmlPopTab_GP_worker (GroupTreeNode cursor, int parentID,
            int ID, Object out) throws SQLException{

        AbstractGroup group = cursor.getGroup();
        String searchField = null, searchExpr = null, caseSensitive = null, reg_exp = null;
        int hierContext = group.getHierarchicalContext();
        if (group instanceof KeywordGroup) {
            searchField = ((KeywordGroup)group).getSearchField();
            searchExpr = ((KeywordGroup)group).getSearchExpression();
            caseSensitive = ((KeywordGroup)group).isCaseSensitive() ? "1" : "0";
            reg_exp = ((KeywordGroup)group).isRegExp() ? "1" : "0";
        }
        else if (group instanceof SearchGroup) {
            searchExpr = ((SearchGroup)group).getSearchExpression();
            caseSensitive = ((SearchGroup)group).isCaseSensitive() ? "1" : "0";
            reg_exp = ((SearchGroup)group).isRegExp() ? "1" : "0";
        }

        // Protect all quotes in the group descriptions:
        if (searchField != null)
            searchField = Util.quote(searchField, "\"", '\\');
        if (searchExpr != null)
            searchExpr = Util.quote(searchExpr, "\"", '\\');

        // handle DML according to output type
        processDML(out, "INSERT INTO groups (groups_id, label, parent_id, group_types_id, search_field, "
            +"search_expression, case_sensitive, reg_exp, hierarchical_context) "
              + "VALUES (" + ID + ", \"" + cursor.getGroup().getName()
              + "\", " + parentID
                      +", (SELECT group_types_id FROM group_types where label=\""+group.getTypeId()+"\")"
                      +", "+(searchField != null ? "\""+searchField+"\"" : "NULL")
                      +", "+(searchExpr != null ? "\""+searchExpr+"\"" : "NULL")
                      +", "+(caseSensitive != null ? "\""+caseSensitive+"\"" : "NULL")
                      +", "+(reg_exp != null ? "\""+reg_exp+"\"" : "NULL")
                      +", "+hierContext
                      + ");");

    // recurse on child nodes (depth-first traversal)
      int myID = ID;
      for (Enumeration<GroupTreeNode> e = cursor.children(); e.hasMoreElements();)
      ID = dmlPopTab_GP_worker(e.nextElement(),myID,++ID,out);
      return ID;
  }


    /**
     * Generates the DML required to populate the entry_group table with jabref
     * data, and writes the DML to the PrintStream.
     *
     * @param cursor
     *            The current GroupTreeNode in the GroupsTree
     * @param out
     *            The output (PrintStream or Connection) object to which the DML should be written.
     */
  private static int dmlPopTab_EG(GroupTreeNode cursor, Object fout)
                        throws SQLException{

            int cnt = dmlPopTab_EG_worker(cursor, 1, 1, fout);
            return cnt;
    }

    /**
     * Recursive worker method for the dmlPopTab_EG methods.
     *
     * @param cursor
     *            The current GroupTreeNode in the GroupsTree
     * @param parentID
     *            The integer ID associated with the cursors's parent node
     * @param ID
     *            The integer value to associate with the cursor
     * @param out
     *            The output (PrintStream or Connection) object to which the DML should be written.
     */

  private static int dmlPopTab_EG_worker(GroupTreeNode cursor, int parentID, int ID,
      Object out) throws SQLException{

    // if this group contains entries...
    if ( cursor.getGroup() instanceof ExplicitGroup) {

      // build INSERT statement for each entry belonging to this group
      ExplicitGroup grp = (ExplicitGroup)cursor.getGroup();
     
      for (BibtexEntry be : grp.getEntries()){

                // handle DML according to output type
                processDML(out, "INSERT INTO entry_group (entries_id, groups_id) "
               + "VALUES ("
               + "(SELECT entries_id FROM entries WHERE jabref_eid="
               + "\"" + be.getId() + "\""
               + "), "
               + "(SELECT groups_id FROM groups WHERE groups_id="
               + "\"" + ID + "\")"
               + ");");
      }
    }

    // recurse on child nodes (depth-first traversal)
      int myID = ID;
      for (Enumeration<GroupTreeNode> e = cursor.children(); e.hasMoreElements();)
      ID = dmlPopTab_EG_worker(e.nextElement(),myID,++ID,out);

      return ID;
  }

    /**
     * Processes a SQLException, and returns a more user-friendly message
     *
     * @param ex
     *            The SQLException raised
     * @param dbtype
     *            DBTYPE specifying the type of database that raised the exception
     */

    public static String getExceptionMessage (Exception ex, DBTYPE dbtype) {
       
        String errorMessage = null;

        switch (dbtype) {
            case MYSQL:
                errorMessage = getExceptionMessage_MySQL(ex);
                break;
            case DERBY:
                errorMessage = getExceptionMessage_MySQL(ex);
                break;
            default:
                errorMessage = Globals.lang("Could not determine exception message.");
                break;
        }

        return errorMessage;

    }

    /**
     * Handles work for getExceptionMessage when dbtype is MYSQL
     *
     * @param ex
     *            The SQLException raised
     */
    public static String getExceptionMessage_MySQL (Exception ex) {
     
        String msg = null;

        // handle case where exception is SQL related
        if (ex instanceof SQLException) {

            SQLException sqlex = (SQLException) ex;

            // desc  : Unkown DB
            // code  : 1049
            // state : 42000
            // msg   : Unkown database 'database_name'
            // type  : SQLException
            // tested with MySQL

            if (sqlex.getSQLState().equals("42000")) {
                msg = Globals.lang(sqlex.getMessage());
            }


            // desc  : command denied
            // code  : 1142
            // state : 42000
            // msg   : * command denied to user 'username'@'hostname' for table 'table_name'
            // tested with MySQL

            if (sqlex.getSQLState().equals("42000")) {
                msg = Globals.lang("User does not have sufficient privileges.\n");
                msg = msg + Globals.lang("(" + sqlex.getMessage() + ")");
            }


            // desc  : Invalid username and/or password
            // code  : 1045
            // state : 28000
            // msg   : Access denied for user 'username'@'hostname' (using password: ...)
            // type  : SQLException
            // tested with MySQL

            if (sqlex.getSQLState().equals("28000")) {
                msg = Globals.lang(sqlex.getMessage());
            }


            // desc  : Cannot connect to SQL server
            // code  : 0
            // state : 08S01
            // msg   : Communications link failure due to underlying exception
            // type  : java.net.UnknownHostException
            // tested with MySQL

            if (sqlex.getSQLState().equals("08S01")) {
                msg = Globals.lang("Cannot connect to SQL server at the specified host.");
            }

            // for debugging...
            if (false) {
                System.out.println("-------------------------------------");
                System.out.println(sqlex.getErrorCode());
                System.out.println(sqlex.getSQLState());
                System.out.println(sqlex.getMessage());
                System.out.println("-------------------------------------");
            }

        }
       
        // handle case where exception is non-SQL related
        if (msg == null) {

            if (ex.getMessage()==null) {
                msg = ex.toString();
            } else {
                msg = ex.getMessage();
            }

        }


        return msg;

    }

}
TOP

Related Classes of net.sf.jabref.sql.SQLutil

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.