Package org.intermine.sql

Class DatabaseUtil

  • public final class DatabaseUtil
extends java.lang.Object
    Collection of commonly used Database utilities
    Author:
    Andrew Varley, Matthew Wakeling

    • Nested Class Summary

      Nested Classes 
      Modifier and Type Class Description
      static class  DatabaseUtil.Type  

    • Method Summary

      All Methods Static Methods Concrete Methods 
      Modifier and Type Method Description
      static void addColumn​(java.sql.Connection con, java.lang.String tableName, java.lang.String columnName, DatabaseUtil.Type type)
      Add a column to an existing database table, if it does not already exist.
      static void addColumn​(Database database, java.lang.String tableName, java.lang.String columnName, DatabaseUtil.Type type)
      Add a column in the table specified in input.
      static void analyse​(Database db, boolean full)
      Analyse given database, perform vacuum full analyse if full parameter true.
      static void analyse​(Database db, ClassDescriptor cld, boolean full)
      Analyse database table for a given class and all associated indirection tables.
      static boolean columnExists​(java.sql.Connection con, java.lang.String tableName, java.lang.String columnName)
      Tests if a column exists in the database
      static void createBagTable​(Database db, java.sql.Connection con, java.lang.String tableName, java.util.Collection<?> bag, java.lang.Class<?> c)
      Create a new table the holds the contents of the given Collection (bag).
      static void createBagValuesTables​(java.sql.Connection con)
      Create the table 'bagvalues' containing the values of the key field objects contained in a bag and an extra values
      static java.lang.String generateSqlCompatibleName​(java.lang.String n)
      Convert any sql keywords to valid names for tables/columns.
      static java.lang.String getColumnName​(FieldDescriptor fd)
      Creates a column name for a field descriptor
      static java.lang.String getIndirectionTableName​(CollectionDescriptor col)
      Creates an indirection table name for a many-to-many collection descriptor
      static java.util.Set<java.lang.String> getIndirectionTableNames​(ClassDescriptor cld)
      Given a ClassDescriptor find names of all related indirection tables.
      static java.lang.String getInwardIndirectionColumnName​(CollectionDescriptor col, int version)
      Creates a column name for the "inward" key of a many-to-many collection descriptor.
      static java.lang.String getOutwardIndirectionColumnName​(CollectionDescriptor col, int version)
      Creates a column name for the "outward" key of a many-to-many collection descriptor.
      static java.lang.String getTableDefinition​(Database db, ClassDescriptor cd)  
      static java.lang.String getTableName​(ClassDescriptor cld)
      Creates a table name for a class descriptor
      static void grant​(Database db, java.lang.String user, java.lang.String perm)
      Grant permission on all tables for given user on specified database.
      static boolean isBagValuesEmpty​(java.sql.Connection con)
      Verify if 'bagvalues' table is empty
      protected static boolean isLegalColumnName​(java.lang.String name)
      Check that a column name provided to us is a legal column name, to prevent SQL injection.
      static java.lang.String objectToString​(java.lang.Object o)
      Generate an SQL compatible representation of an object.
      static void removeAllTables​(java.sql.Connection con)
      Removes every single table from the database given.
      static void removeSequence​(java.sql.Connection con, java.lang.String sequence)
      Remove the sequence from the database given.
      static void removeView​(java.sql.Connection con, java.lang.String view)
      Remove the view from the database given.
      static boolean tableExists​(java.sql.Connection con, java.lang.String tableName)
      Tests if a table exists in the database
      static void updateColumnValue​(java.sql.Connection con, java.lang.String tableName, java.lang.String columnName, java.lang.Object newValue)
      Set the default value in a column for all values.
      static void updateColumnValue​(Database database, java.lang.String tableName, java.lang.String columnName, java.lang.Object newValue)
      Set the default value in a column for all values where the current value is null.
      static boolean verifyColumnType​(java.sql.Connection con, java.lang.String tableName, java.lang.String columnName, int columnType)  

      • Methods inherited from class java.lang.Object

        clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

    • Method Detail

      • tableExists

        public static boolean tableExists​(java.sql.Connection con,
                                  java.lang.String tableName)
                           throws java.sql.SQLException
        Tests if a table exists in the database
        Parameters:
        con - a connection to a database
        tableName - the name of a table to test for
        Returns:
        true if the table exists, false otherwise
        Throws:
        java.sql.SQLException - if an error occurs in the underlying database
        java.lang.NullPointerException - if tableName is null

      • columnExists

        public static boolean columnExists​(java.sql.Connection con,
                                   java.lang.String tableName,
                                   java.lang.String columnName)
                            throws java.sql.SQLException
        Tests if a column exists in the database
        Parameters:
        con - a connection to a database
        tableName - the name of a table containing the column
        columnName - the name of the column to test for
        Returns:
        true if the column exists, false otherwise
        Throws:
        java.sql.SQLException - if an error occurs in the underlying database
        java.lang.NullPointerException - if tableName is null

      • removeAllTables

        public static void removeAllTables​(java.sql.Connection con)
                            throws java.sql.SQLException
        Removes every single table from the database given.
        Parameters:
        con - the Connection to the database
        Throws:
        java.sql.SQLException - if an error occurs in the underlying database

      • removeSequence

        public static void removeSequence​(java.sql.Connection con,
                                  java.lang.String sequence)
                           throws java.sql.SQLException
        Remove the sequence from the database given.
        Parameters:
        con - the Connection to the database
        sequence - the sequence to remove
        Throws:
        java.sql.SQLException - if an error occurs in the underlying database

      • removeView

        public static void removeView​(java.sql.Connection con,
                              java.lang.String view)
                       throws java.sql.SQLException
        Remove the view from the database given.
        Parameters:
        con - the Connection to the database
        view - the view to remove
        Throws:
        java.sql.SQLException - if an error occurs in the underlying database

      • getTableName

        public static java.lang.String getTableName​(ClassDescriptor cld)
        Creates a table name for a class descriptor
        Parameters:
        cld - ClassDescriptor
        Returns:
        a valid table name

      • getColumnName

        public static java.lang.String getColumnName​(FieldDescriptor fd)
        Creates a column name for a field descriptor
        Parameters:
        fd - FieldDescriptor
        Returns:
        a valid column name

      • getIndirectionTableName

        public static java.lang.String getIndirectionTableName​(CollectionDescriptor col)
        Creates an indirection table name for a many-to-many collection descriptor
        Parameters:
        col - CollectionDescriptor
        Returns:
        a valid table name

      • getInwardIndirectionColumnName

        public static java.lang.String getInwardIndirectionColumnName​(CollectionDescriptor col,
                                                              int version)
        Creates a column name for the "inward" key of a many-to-many collection descriptor.
        Parameters:
        col - CollectionDescriptor
        version - the database version number
        Returns:
        a valid column name

      • getOutwardIndirectionColumnName

        public static java.lang.String getOutwardIndirectionColumnName​(CollectionDescriptor col,
                                                               int version)
        Creates a column name for the "outward" key of a many-to-many collection descriptor.
        Parameters:
        col - CollectionDescriptor
        version - the database version number
        Returns:
        a valid column name

      • generateSqlCompatibleName

        public static java.lang.String generateSqlCompatibleName​(java.lang.String n)
        Convert any sql keywords to valid names for tables/columns.
        Parameters:
        n - the string to convert
        Returns:
        a valid sql name

      • objectToString

        public static java.lang.String objectToString​(java.lang.Object o)
        Generate an SQL compatible representation of an object.
        Parameters:
        o - the Object
        Returns:
        a valid SQL String
        Throws:
        java.lang.IllegalArgumentException - if the object is not representable

      • analyse

        public static void analyse​(Database db,
                           boolean full)
                    throws java.sql.SQLException
        Analyse given database, perform vacuum full analyse if full parameter true. WARNING: currently PostgreSQL specific
        Parameters:
        db - the database to analyse
        full - if true perform VACUUM FULL ANALYSE
        Throws:
        java.sql.SQLException - if db problem

      • analyse

        public static void analyse​(Database db,
                           ClassDescriptor cld,
                           boolean full)
                    throws java.sql.SQLException
        Analyse database table for a given class and all associated indirection tables. WARNING: currently PostgreSQL specific
        Parameters:
        db - the database to analyse
        cld - description of class to analyse
        full - if true perform VACUUM FULL ANALYSE
        Throws:
        java.sql.SQLException - if db problem

      • getIndirectionTableNames

        public static java.util.Set<java.lang.String> getIndirectionTableNames​(ClassDescriptor cld)
        Given a ClassDescriptor find names of all related indirection tables.
        Parameters:
        cld - class to find tables for
        Returns:
        a set of all indirection table names

      • grant

        public static void grant​(Database db,
                         java.lang.String user,
                         java.lang.String perm)
                  throws java.sql.SQLException
        Grant permission on all tables for given user on specified database.
        Parameters:
        db - the database to grant permissions on
        user - the username to grant permission to
        perm - permission to grant
        Throws:
        java.sql.SQLException - if db problem

      • createBagTable

        public static void createBagTable​(Database db,
                                  java.sql.Connection con,
                                  java.lang.String tableName,
                                  java.util.Collection<?> bag,
                                  java.lang.Class<?> c)
                           throws java.sql.SQLException
        Create a new table the holds the contents of the given Collection (bag). The "Class c" parameter selects which objects from the bag are put in the new table. eg. if the bag contains Integers and Strings and the parameter is Integer.class then the table will contain only the Integers from the bag. A Class of InterMineObject is handled specially: the new table will contain the IDs of the objects, not the objects themselves. The table will have one column ("value").
        Parameters:
        db - the Database to access
        con - the Connection to use
        tableName - the name to use for the new table
        bag - the Collection to create a table for
        c - the type of objects to put in the new table
        Throws:
        java.sql.SQLException - if there is a database problem

      • createBagValuesTables

        public static void createBagValuesTables​(java.sql.Connection con)
                                  throws java.sql.SQLException
        Create the table 'bagvalues' containing the values of the key field objects contained in a bag and an extra values
        Parameters:
        con - the Connection to use
        Throws:
        java.sql.SQLException - if there is a database problem

      • isBagValuesEmpty

        public static boolean isBagValuesEmpty​(java.sql.Connection con)
                                throws java.sql.SQLException
        Verify if 'bagvalues' table is empty
        Parameters:
        con - the Connection to use
        Returns:
        true if empty
        Throws:
        java.sql.SQLException - if there is a database problem

      • addColumn

        public static void addColumn​(Database database,
                             java.lang.String tableName,
                             java.lang.String columnName,
                             DatabaseUtil.Type type)
                      throws java.sql.SQLException
        Add a column in the table specified in input. A connection is obtained to the database and automatically released after the addition of the column.
        Parameters:
        database - the database to use
        tableName - the table where to add the column
        columnName - the column to add
        type - the type
        Throws:
        java.sql.SQLException - if there is a database problem

      • addColumn

        public static void addColumn​(java.sql.Connection con,
                             java.lang.String tableName,
                             java.lang.String columnName,
                             DatabaseUtil.Type type)
                      throws java.sql.SQLException
        Add a column to an existing database table, if it does not already exist. It is the users responsibility to close the connection after use.
        Parameters:
        con - A connection to the database.
        tableName - The table to add the database too
        columnName - The column to add
        type - The SQL type to add
        Throws:
        java.sql.SQLException - if something goes wrong

      • isLegalColumnName

        protected static boolean isLegalColumnName​(java.lang.String name)
        Check that a column name provided to us is a legal column name, to prevent SQL injection.
        Parameters:
        name - The desired column name.
        Returns:
        Whether or not we should accept it.

      • updateColumnValue

        public static void updateColumnValue​(Database database,
                                     java.lang.String tableName,
                                     java.lang.String columnName,
                                     java.lang.Object newValue)
                              throws java.sql.SQLException
        Set the default value in a column for all values where the current value is null.
        Parameters:
        database - the database to use
        tableName - the table where update the column
        columnName - the column to Update
        newValue - the value to update
        Throws:
        java.sql.SQLException - if there is a database problem

      • updateColumnValue

        public static void updateColumnValue​(java.sql.Connection con,
                                     java.lang.String tableName,
                                     java.lang.String columnName,
                                     java.lang.Object newValue)
                              throws java.sql.SQLException
        Set the default value in a column for all values.
        Parameters:
        con - A connection to the database to use
        tableName - the table where update the column
        columnName - the column to Update
        newValue - the value to update
        Throws:
        java.sql.SQLException - if there is a database problem Note, it is the user's responsibility to ensure the connection given is closed.

      • verifyColumnType

        public static boolean verifyColumnType​(java.sql.Connection con,
                                       java.lang.String tableName,
                                       java.lang.String columnName,
                                       int columnType)
        Parameters:
        con - database connection
        tableName - table name
        columnName - column name
        columnType - column type
        Returns:
        true if column exists

      • getTableDefinition

        public static java.lang.String getTableDefinition​(Database db,
                                                  ClassDescriptor cd)
                                           throws java.lang.ClassNotFoundException
        Parameters:
        db - database
        cd - class descriptor
        Returns:
        SQL to create table
        Throws:
        java.lang.ClassNotFoundException - table isn't in database