Class UpdateHandler

  • All Implemented Interfaces:
    TableTypeConstants

    @PublicAPI
    public class UpdateHandler
    extends ErrorHandler
    implements TableTypeConstants
    Provides methods for handling structure tables, e.g.:
    • creating tables
    • updating and inserting rows
    • dropping tables
    Both fix columns (the ones which have "cd_" prefix in their names) and additional columns can be set using the class.

    WARNING!
    For optimization reasons logs are buffered in UpdateHandler from JChem 5.4.
    When you use transactions saveUpdateLogs() must be called in order to flush buffers before you commit!
    (close() method performs a call to saveUpdateLogs(), no extra calls are needed.)

    Example for insert/update:

        int id;          // cd_id value of compound
        // Additional columns:
        String name;
        Float stock;
        String comments;
        ...
        UpdateHandler uh = new UpdateHandler(conh,
                (isInsertion? UdateHandler.INSERT : UpdateHandler.UPDATE),
                structureTableName, "name, stock, comments");
        try {
            uh.setStructure(molfile);
            if(!isInsertion) {
                uh.setID(id);
            }
            uh.setValueForAdditionalColumn(1, name);
            uh.setValueForAdditionalColumn(2, stock);
            uh.setValueForAdditionalColumn(3, comments);
            uh.execute();
        } finally {
            uh.close();
        }
        ...
     
    • Field Detail

      • INSERT

        public static final int INSERT
        Row insertion with automatic cd_id value.
        See Also:
        Constant Field Values
      • INSERT_WITH_ID

        public static final int INSERT_WITH_ID
        Row insertion with cd_id value set by the application.
        See Also:
        Constant Field Values
      • DUPLICATE_FILTERING_ON

        public static final int DUPLICATE_FILTERING_ON
        Constant for 'duplicate filtering' option on import. This value switches on the option.
        See Also:
        Constant Field Values
      • DUPLICATE_FILTERING_OFF

        public static final int DUPLICATE_FILTERING_OFF
        Constant for 'duplicate filtering' option on import. This value switches off the option.
        See Also:
        Constant Field Values
      • DUPLICATE_FILTERING_TABLE_OPTION

        public static final int DUPLICATE_FILTERING_TABLE_OPTION
        Constant for 'duplicate filtering' option on import. This value sets this option as table option.
        See Also:
        Constant Field Values
      • QUERY_TABLE_ENUMERATION_LIMIT

        public static final int QUERY_TABLE_ENUMERATION_LIMIT
        Upper limit for enumerating queries when inserting into QUERY tables
        See Also:
        Constant Field Values
    • Constructor Detail

      • UpdateHandler

        public UpdateHandler()
        Zero-arg constructor
      • UpdateHandler

        public UpdateHandler​(ConnectionHandler conh,
                             int mode,
                             String tableName,
                             String additionalColumns)
                      throws SQLException
        Constructor.
        Parameters:
        conh - ConnectionHandler opened to the database, containing the name of the JChem property table name.
        mode - possible values UPDATE, INSERT, INSERT_WITH_ID. Not all RDBMS-s support INSERT_WITH_ID if cd_id is assigned to be auto incremented,
        tableName - name of structure table to be modified
        additionalColumns - specify columns that are not created automatically by JChemManager.
        Throws:
        SQLException - if read the table metadata encounters a problem.
      • UpdateHandler

        public UpdateHandler​(ConnectionHandler conh,
                             int mode,
                             String tableName,
                             String additionalColumns,
                             String indexedTable,
                             String indexedColumn)
                      throws SQLException
        Constructor. FOR INTERNAL USE ONLY.
        Parameters:
        conh - ConnectionHandler opened to the database, containing the name of the JChem property table name of the property table.
        mode - possible values UPDATE, INSERT, INSERT_WITH_ID. Not all RDBMS-s support INSERT_WITH_ID if cd_id is assigned to be auto incremented,
        tableName - name of structure table to be modified
        additionalColumns - specify columns that are not created automatically by JChemManager.
        indexedTable - the name of indexed table when used with JChem Cartridge for regular structure tables. Must be null for JChem structure tables.
        indexedColumn - the name of indexed column when used with JChem Cartridge for regular structure tables. Must be null for JChem structure tables.
        Throws:
        SQLException - if an error occurs
        Since:
        JChem JChem 3.0
    • Method Detail

      • init

        public void init​(ConnectionHandler conHandler,
                         int mode,
                         String tableName,
                         String additionalColumns)
                  throws SQLException
        Initializes the object. Useful if UpdateHandler is applied as a JavaBean. It should be called immediately after constructing the object.
        Parameters:
        conHandler - connection handler object.
        mode - possible values UPDATE, INSERT, INSERT_WITH_ID. Not all RDBMS-s support INSERT_WITH_ID if cd_id is assigned to be auto incremented,
        tableName - name of structure table to be modified
        additionalColumns - specify columns that are not created automatically by JChemManager.
        Throws:
        SQLException - if an error occurs
        See Also:
        ConnectionHandler
      • getMolecule

        public Molecule getMolecule()
        Returns the Molecule that was used during insert or update.
        Returns:
        the structure specified in setValuesForFixColumns( ... ) in standardized form, as it was inserted into the database.
        NOTE: Should be called only after execute().
        Since:
        JChem 2.2.1
      • setROWID

        public void setROWID​(String rowID)
        Sets values for all fix columns in cartridge mode if the mode is INSERT_WITH_ID.
        FOR INTERNAL USE ONLY.
        Parameters:
        rowID - the ROWID if the original line in the non-jchem table.
      • setStructure

        public void setStructure​(String source)
        Sets the source of the input molecule.
        Parameters:
        source - the source of the structure (molfile, SMILES, MRV, etc.)
        Since:
        JChem 3.2
        See Also:
        setStructure(byte[])
      • setStructure

        public void setStructure​(byte[] source)
        Sets the source of the input molecule.
        Parameters:
        source - the source of the structure (molfile, SMILES, MRV, etc.)
        Since:
        JChem 3.2
        See Also:
        setStructure(String)
      • setID

        public void setID​(int id)
        Sets the cd_id value for the structure. Effective in UPDATE or INSERT_WITH_ID mode.
        Parameters:
        id - the cd_id value
        Since:
        JChem 3.2
      • setIgnoreChemicalTermsExceptions

        public void setIgnoreChemicalTermsExceptions​(boolean ignore)
        Specifies whether to ignore exceptions during the calculation of Chemical Terms columns.
        Parameters:
        ignore - if set to true, no exceptions will be thrown if a calculation for a Chemical Terms column fails. The affected column will have a value of NULL in the database. The default value is false.
        Since:
        JChem 3.2.6
      • setLoggingEnabled

        public void setLoggingEnabled​(boolean value)
        Enables / disables logging structures during import.
        Parameters:
        value - If true (default value), logging is enabled.
      • isLoggingEnabled

        public boolean isLoggingEnabled()
        Shows whether logging is enabled during import.
        Returns:
        True if logging is enabled.
      • checkChemTermColValue

        public static Object checkChemTermColValue​(Object result)
      • setStructureNameField

        public void setStructureNameField​(String nameField)
        Set the name of that column where the structure name is stored.
        Parameters:
        nameField - Name of the column.
      • getStructureNameField

        public String getStructureNameField()
        Get the name of that column which stores the structure names.
        Returns:
        Name of the column.
      • setValueForAdditionalColumn

        public void setValueForAdditionalColumn​(int index,
                                                Object value,
                                                int sqlType)
                                         throws SQLException
        Sets values for an additional column. Call setValuesForFixColumns before invoking this method.
        Parameters:
        index - the index value of the additional column as listed in the constructor starting at 1.
        value - the value of the column in the row. It can also be null if the column is declared as NULL.
        sqlType - the type of the column as defined in java.sql.Types
        Throws:
        SQLException - if an error occurs
      • setValueForAdditionalColumn

        public void setValueForAdditionalColumn​(int index,
                                                Object value)
                                         throws SQLException
        Sets values for all additional columns Type is defined automatically.
        Parameters:
        index - the index value of the additional column as listed in the constructor starting at 1.
        value - the value of the column in the row.
        Throws:
        SQLException - if an error occurs
      • setEmptyStructuresAllowed

        public void setEmptyStructuresAllowed​(boolean allowed)
        Sets if empty structures are allowed, default is false
        Parameters:
        allowed - set to true to enable empty structures
      • setSetChiralFlag

        public void setSetChiralFlag​(boolean setChiralFlag)
        Sets if chiral flag should be set to true during import. Useful when importing MDL files with missing chiral flag.
        Parameters:
        setChiralFlag - if set to true, chiral flag is set to true for imported molecules. The default setting is false. since 2.3
      • getSetChiralFlag

        public boolean getSetChiralFlag()
        Getter for property setChiralFlag.
        Returns:
        the value of property setChiralFlag
        See Also:
        setSetChiralFlag(boolean)
      • execute

        public int execute​(boolean returnLastId)
                    throws SQLException
        Executes the update or insert operation.
        Stays in the same transaction if Connection.getAutoCommit()==false
        Parameters:
        returnLastId - if true, then gets the last inserted ID and returns it.
        NOTE: works with ORACLE, DB2, MySQL, MSSQLServer, InterBase, PostgreSQL and MSAccess
        Returns:
        the last inserted id, or zero if returnLastId is false or the operation is update. If duplicateFiltering is on, returns the cd_id of the already existing structure in the database as a negative value. (regardless of returnLastId).
        Throws:
        SQLException - if an error occurs
      • close

        public void close()
                   throws SQLException
        Closes the handler (required to call). Closes the prepared statements applied and writes the changes to the log table (required for correct cache update).
        Throws:
        SQLException - if an error occurs
      • saveUpdateLogs

        public void saveUpdateLogs()
                            throws SQLException
        Saves the current - possibly buffered - update log(s). Must be called before committing updates in transaction!
        Throws:
        SQLException - if an error occurs
      • deleteRows

        public static void deleteRows​(ConnectionHandler conh,
                                      String tableName,
                                      String where)
                               throws SQLException
        Deletes the specified rows from a structure table. Also deletes the appropriate rows from the corresponding Molecular Descriptor tables, if present and needed. (Referential integrity is used to take care of that for most RDBMSs). This method is best suited to delete large number of rows. The IDs of the deleted structures will not be logged individually, therefore the cache update may take some time after the operation. For deleting a small amount of records using deleteRow(chemaxon.util.ConnectionHandler, String, int) is recommended.
        Parameters:
        conh - connection to the database
        tableName - name of structure table to be modified
        where - the WHERE part of the SQL statement deleting the rows. E.g. "WHERE cd_id IN (23, 247, 786) ". If null or empty string all rows will be deleted.
        Throws:
        SQLException - if an error occurs
        See Also:
        deleteRow(chemaxon.util.ConnectionHandler, String, int)
      • deleteRows

        public static void deleteRows​(ConnectionHandler conh,
                                      String tableName,
                                      String where,
                                      boolean enableLogging)
                               throws SQLException
        Deletes the specified rows from a structure table. Also deletes the appropriate rows from the corresponding Molecular Descriptor tables, if present and needed. (Referential integrity is used to take care of that for most RDBMSs). This method is best suited to delete large number of rows. The IDs of the deleted structures will not be logged individually, therefore the cache update may take some time after the operation. For deleting a small amount of records using deleteRow(chemaxon.util.ConnectionHandler, String, int) is recommended.
        Parameters:
        conh - connection to the database
        tableName - name of structure table to be modified
        where - the WHERE part of the SQL statement deleting the rows. E.g. "WHERE cd_id IN (23, 247, 786) ". If null or empty string all rows will be deleted.
        enableLogging - set to true if logging the deletion is enabled
        Throws:
        SQLException - if an error occurs
        See Also:
        deleteRow(chemaxon.util.ConnectionHandler, String, int)
      • deleteRow

        public static void deleteRow​(ConnectionHandler conh,
                                     String tableName,
                                     int cd_id)
                              throws SQLException
        Deletes the specified rows from a structure table. Also deletes the appropriate rows from the corresponding Molecular Descriptor tables, if present and needed. (Referential integrity is used to take care of that for most RDBMSs). Use this method to delete relatively small amount of records. The ID of each structure is logged in the update log table providing efficient cache update after the operation. For deleting a large amount of records using deleteRows(chemaxon.util.ConnectionHandler, String, String) is recommended.
        Parameters:
        conh - connection to the database
        tableName - the name of the structure table
        cd_id - the cd_id value of the row to be deleted (primary key)
        Throws:
        SQLException - if an error occurs
        Since:
        5.0
        See Also:
        deleteRows(chemaxon.util.ConnectionHandler, String, String)
      • deleteRow

        public static void deleteRow​(ConnectionHandler conh,
                                     String tableName,
                                     int cd_id,
                                     boolean enableLogging)
                              throws SQLException
        Deletes the specified rows from a structure table. Also deletes the appropriate rows from the corresponding Molecular Descriptor tables, if present and needed. (Referential integrity is used to take care of that for most RDBMSs). Use this method to delete relatively small amount of records. if enableLoggingThe ID of each structure is logged in the update log table providing efficient cache update after the operation. For deleting a large amount of records using deleteRows(chemaxon.util.ConnectionHandler, String, String) is recommended.
        Parameters:
        conh - connection to the database
        tableName - the name of the structure table
        cd_id - the cd_id value of the row to be deleted (primary key)
        enableLogging - set to true if logging the deletion is enabled
        Throws:
        SQLException - if an error occurs
        Since:
        5.0
        See Also:
        deleteRows(chemaxon.util.ConnectionHandler, String, String)
      • getStructureTables

        public static String[] getStructureTables​(ConnectionHandler conh)
                                           throws SQLException
        Collects the names of all structures tables in the database (from the records of the JChemProperties table).
        Parameters:
        conh - connection to the database
        Returns:
        the names of all structure tables.
        Throws:
        SQLException - if an error occurs
      • isStructureTable

        public static boolean isStructureTable​(ConnectionHandler conh,
                                               String tableName)
                                        throws SQLException
        Checks if a structure table exists.
        Parameters:
        conh - connection handler to the database
        tableName - name of structure table
        Returns:
        true if the table exists
        Throws:
        SQLException - if an error occurs
      • dropStructureTable

        public static void dropStructureTable​(ConnectionHandler conh,
                                              String tableName)
                                       throws SQLException
        Drops a structure table.
        Parameters:
        conh - connection to the database
        tableName - name of structure table
        Throws:
        SQLException - if an error occurs
      • recalculateTable

        public static boolean recalculateTable​(ConnectionHandler ch,
                                               String tableName,
                                               boolean changeStandardization,
                                               String standardizerConfig,
                                               Map<String,​String> chemTermColCfg,
                                               Boolean tautomerDuplicateFiltering,
                                               ProgressWriter pw)
                                        throws UpdateHandlerException
        Recalculates generated values in a structure table in normal mode.
        Parameters:
        ch - ConnectionHandler opened to the database
        tableName - the name of the structure table to be regenerated
        changeStandardization - changes default standardization if true
        standardizerConfig - new standardizer configuration, is set to
        chemTermColCfg - colname-to-chemterm map for columns autocalculated based on Chemical Terms. Specify null if there is no change in the column definitions.
        tautomerDuplicateFiltering - set to Boolean.TRUE to enable considering tautomers during duplicate filtering. Set to null to keep the current setting (default).
        pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
        Returns:
        whether any error occurred during the regeneration process (true, if not).
        Throws:
        UpdateHandlerException - if an error occurs during processing.
        Since:
        JChem 5.0
      • recalculateTable

        public static boolean recalculateTable​(ConnectionHandler ch,
                                               String tableName,
                                               boolean changeStandardization,
                                               String standardizerConfig,
                                               Map<String,​String> chemTermColCfg,
                                               Boolean tautomerDuplicateFiltering,
                                               ProgressWriter pw,
                                               int regenerationMode)
                                        throws UpdateHandlerException
        Recalculates generated values in a structure table.
        Parameters:
        ch - ConnectionHandler opened to the database
        tableName - the name of the structure table to be regenerated
        changeStandardization - changes default standardization if true
        standardizerConfig - new standardizer configuration, is set to
        chemTermColCfg - colname-to-chemterm map for columns autocalculated based on Chemical Terms. Specify null if there is no change in the column definitions.
        tautomerDuplicateFiltering - set to Boolean.TRUE to enable considering tautomers during duplicate filtering. Set to null to keep the current setting (default).
        pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
        regenerationMode - Normal, precalculation or regenerate not precalculated rows (see: RegenerationConstants)
        Returns:
        whether any error occurred during the regeneration process (true, if not).
        Throws:
        UpdateHandlerException - if an error occurs during processing.
        Since:
        JChem 5.0
      • recalculateCTColumns

        public static boolean recalculateCTColumns​(ConnectionHandler ch,
                                                   String tableName,
                                                   String[] columns,
                                                   ProgressWriter pw)
                                            throws UpdateHandlerException
        Recalculates chemical terms in a structure table in normal mode.
        Parameters:
        ch - ConnectionHandler opened to the database
        tableName - the name of the structure table to be regenerated
        columns - array of chemical terms column names
        pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
        Returns:
        whether any error occurred during the regeneration process (true, if not).
        Throws:
        UpdateHandlerException - if an error occurs during processing.
        Since:
        JChem 5.1.2
      • recalculateCTColumns

        public static boolean recalculateCTColumns​(ConnectionHandler ch,
                                                   String tableName,
                                                   String[] columns,
                                                   ProgressWriter pw,
                                                   int regenerationMode)
                                            throws UpdateHandlerException
        Recalculates chemical terms in a structure table.
        Parameters:
        ch - ConnectionHandler opened to the database
        tableName - the name of the structure table to be regenerated
        columns - array of chemical terms column names
        pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
        regenerationMode - Normal, precalculation or regenerate not precalculated rows (see: RegenerationConstants)
        Returns:
        whether any error occurred during the regeneration process (true, if not).
        Throws:
        UpdateHandlerException - if an error occurs during processing.
        Since:
        JChem 5.1.2
      • recalculateMDTables

        public static boolean recalculateMDTables​(ConnectionHandler ch,
                                                  String tableName,
                                                  ProgressWriter pw)
                                           throws UpdateHandlerException,
                                                  SQLException
        Recalculates molecular descriptors for a structure table / for all structure tables.
        Parameters:
        ch - ConnectionHandler opened to the database
        tableName - the name of the structure table to be regenerated. If null, ALL structure tables are regenerated.
        pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
        Returns:
        whether any error occurred during the regeneration process (true, if not).
        Throws:
        UpdateHandlerException - if an error occurs during processing.
        SQLException - if an error occurs
        Since:
        JChem 5.3.2
      • recalculateWithoutCTColumns

        public static boolean recalculateWithoutCTColumns​(ConnectionHandler ch,
                                                          String tableName,
                                                          boolean changeStandardization,
                                                          String standardizerConfig,
                                                          Boolean tautomerDuplicateFiltering,
                                                          ProgressWriter pw)
                                                   throws UpdateHandlerException
        Recalculates generated values in a structure table but skips Chemical Terms calculations in normal mode.
        Parameters:
        ch - ConnectionHandler opened to the database
        tableName - the name of the structure table to be regenerated
        changeStandardization - changes default standardization if true
        standardizerConfig - new standardizer configuration, is set to
        tautomerDuplicateFiltering - set to Boolean.TRUE to enable considering tautomers during duplicate filtering. Set to null to keep the current setting (default).
        pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
        Returns:
        whether any error occurred during the regeneration process (true, if not).
        Throws:
        UpdateHandlerException - if an error occurs during processing.
        Since:
        JChem 5.2.4
      • recalculateWithoutCTColumns

        public static boolean recalculateWithoutCTColumns​(ConnectionHandler ch,
                                                          String tableName,
                                                          boolean changeStandardization,
                                                          String standardizerConfig,
                                                          Boolean tautomerDuplicateFiltering,
                                                          ProgressWriter pw,
                                                          int regenerationMode)
                                                   throws UpdateHandlerException
        Recalculates generated values in a structure table but skips Chemical Terms calculations.
        Parameters:
        ch - ConnectionHandler opened to the database
        tableName - the name of the structure table to be regenerated
        changeStandardization - changes default standardization if true
        standardizerConfig - new standardizer configuration, is set to
        tautomerDuplicateFiltering - set to Boolean.TRUE to enable considering tautomers during duplicate filtering. Set to null to keep the current setting (default).
        pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
        regenerationMode - Normal, precalculation or regenerate not precalculated rows (see: RegenerationConstants)
        Returns:
        whether any error occurred during the regeneration process (true, if not).
        Throws:
        UpdateHandlerException - if an error occurs during processing.
        Since:
        JChem 5.2.4
      • setRowData

        public void setRowData​(UpdateHandler.RowData rowData)
                        throws SQLException
        Specifies pre-calculated row data values. Subtitutes setValueForFixColumns() and setValueForAdditionalColumn calls. Useful if the data is calculated in a multi-thread environment.
        Parameters:
        rowData - the RowData object
        Throws:
        SQLException - if an error occurs
        Since:
        JChem 3.2
        See Also:
        createRowData(String)
      • setChemTermCalc

        public void setChemTermCalc​(boolean isCalcNeeded)
        Enable or disable the chemical terms calculation during the update
        Parameters:
        isCalcNeeded - boolean