Package org.intermine.objectstore.intermine

Class SqlGenerator

  • public final class SqlGenerator
extends java.lang.Object
    Code to generate an sql statement from a Query object.
    Author:
    Matthew Wakeling, Andrew Varley, Richard Smith

    • Field Detail

      • QUERY_SUBQUERY_FROM

        public static final int QUERY_SUBQUERY_FROM
        subquery in FROM
        See Also:
        Constant Field Values

      • QUERY_SUBQUERY_CONSTRAINT

        public static final int QUERY_SUBQUERY_CONSTRAINT
        subquery in CONSTRAINT
        See Also:
        Constant Field Values

      • NO_ALIASES_ALL_FIELDS

        public static final int NO_ALIASES_ALL_FIELDS
        I DON'T KNOW
        See Also:
        Constant Field Values

      • QUERY_FOR_PRECOMP

        public static final int QUERY_FOR_PRECOMP
        query for precomputing
        See Also:
        Constant Field Values

      • QUERY_SUBQUERY_EXISTS

        public static final int QUERY_SUBQUERY_EXISTS
        subquery exists
        See Also:
        Constant Field Values

      • QUERY_FOR_GOFASTER

        public static final int QUERY_FOR_GOFASTER
        query for go faster
        See Also:
        Constant Field Values

      • QUERY_FOR_COUNTING

        public static final int QUERY_FOR_COUNTING
        subquery only for counting. SELECT COUNT(*) AS ... FROM (subquery)
        See Also:
        Constant Field Values

      • sqlCache

        protected static java.util.Map<DatabaseSchema,​java.util.Map<Query,​org.intermine.objectstore.intermine.SqlGenerator.CacheEntry>> sqlCache

      • tablenamesCache

        protected static java.util.Map<DatabaseSchema,​java.util.Map<Query,​java.util.Set<java.lang.Object>>> tablenamesCache

      • SAFENESS_SAFE

        public static final int SAFENESS_SAFE
        Safeness value indicating a situation safe for ContainsConstraint CONTAINS
        See Also:
        Constant Field Values

      • SAFENESS_ANTISAFE

        public static final int SAFENESS_ANTISAFE
        Safeness value indicating a situation safe for ContainsConstraint DOES NOT CONTAIN
        See Also:
        Constant Field Values

      • SAFENESS_UNSAFE

        public static final int SAFENESS_UNSAFE
        Safeness value indicating a situation unsafe for ContainsConstraint
        See Also:
        Constant Field Values

      • MAX_BAG_INLINE_SIZE

        public static final int MAX_BAG_INLINE_SIZE
        The maximum size a bag in a BagConstraint can be before we consider using a temporary table instead.
        See Also:
        Constant Field Values

    • Method Detail

      • generateQueryForId

        public static java.lang.String generateQueryForId​(java.lang.Integer id,
                                                  java.lang.Class<?> clazz,
                                                  DatabaseSchema schema)
                                           throws ObjectStoreException
        Generates a query to retrieve a single object from the database, by id.
        Parameters:
        id - the id of the object to fetch
        clazz - a Class of the object - if unsure use InterMineObject
        schema - the DatabaseSchema
        Returns:
        a String suitable for passing to an SQL server
        Throws:
        ObjectStoreException - if the given class is not in the model

      • tableNameForId

        public static java.lang.String tableNameForId​(java.lang.Class<?> clazz,
                                              DatabaseSchema schema)
                                       throws ObjectStoreException
        Returns the table name used by the ID fetch query.
        Parameters:
        clazz - the Class of the object
        schema - the DatabaseSchema
        Returns:
        a table name
        Throws:
        ObjectStoreException - if the given class is not in the model

      • registerOffset

        public static void registerOffset​(Query q,
                                  int start,
                                  DatabaseSchema schema,
                                  Database db,
                                  java.lang.Object value,
                                  java.util.Map<java.lang.Object,​java.lang.String> bagTableNames)
        Registers an offset for a given query. This is used later on to speed up queries that use big offsets.
        Parameters:
        q - the Query
        start - the offset
        schema - the DatabaseSchema in which to look up metadata
        db - the Database that the ObjectStore uses
        value - a value, such that adding a WHERE component first_order_field > value with OFFSET 0 is equivalent to the original query with OFFSET offset
        bagTableNames - a Map from BagConstraints to table names, where the table contains the contents of the bag that are relevant for the BagConstraint

      • getOffsetConstraint

        protected static Constraint getOffsetConstraint​(Query q,
                                                QueryOrderable firstOrderBy,
                                                java.lang.Object value,
                                                DatabaseSchema schema)
        Create a constraint to add to the main query to deal with offset - this is based on the first element in the order by (field) and a given value (x). If the order by element cannot have null values this is: 'field > x'. If field can have null values *and* it has not already been constrained as 'NOT NULL' in the main query it is: '(field > x or field IS NULL'.
        Parameters:
        q - the Query
        firstOrderBy - the offset element of the query's order by list
        value - a value, such that adding a WHERE component first_order_field > value with OFFSET 0 is equivalent to the original query with OFFSET offset
        schema - the DatabaseSchema in which to look up metadata
        Returns:
        the constraint(s) to add to the main query

      • generate

        public static java.lang.String generate​(Query q,
                                        int start,
                                        int limit,
                                        DatabaseSchema schema,
                                        Database db,
                                        java.util.Map<java.lang.Object,​java.lang.String> bagTableNames)
                                 throws ObjectStoreException
        Converts a Query object into an SQL String. To produce an SQL query that does not have OFFSET and LIMIT clauses, set start to 0, and limit to Integer.MAX_VALUE.
        Parameters:
        q - the Query to convert
        start - the number of the first row for the query to return, numbered from zero
        limit - the maximum number of rows for the query to return
        schema - the DatabaseSchema in which to look up metadata
        db - the Database that the ObjectStore uses
        bagTableNames - a Map from BagConstraints to table names, where the table contains the contents of the bag that are relevant for the BagConstraint
        Returns:
        a String suitable for passing to an SQL server
        Throws:
        ObjectStoreException - if something goes wrong

      • generate

        public static java.lang.String generate​(Query q,
                                        DatabaseSchema schema,
                                        Database db,
                                        Constraint offsetCon,
                                        int kind,
                                        java.util.Map<java.lang.Object,​java.lang.String> bagTableNames)
                                 throws ObjectStoreException
        Converts a Query object into an SQL String.
        Parameters:
        q - the Query to convert
        schema - the DatabaseSchema in which to look up metadata
        db - the Database that the ObjectStore uses
        offsetCon - an additional constraint for improving the speed of large offsets
        kind - Query type
        bagTableNames - a Map from BagConstraints to table names, where the table contains the contents of the bag that are relevant for the BagConstraint
        Returns:
        a String suitable for passing to an SQL server
        Throws:
        ObjectStoreException - if something goes wrong

      • needsDistinct

        protected static boolean needsDistinct​(Query q)
        Returns true if this query requires a DISTINCT keyword in the generated SQL.
        Parameters:
        q - the Query
        Returns:
        a boolean

      • findTableNames

        public static java.util.Set<java.lang.String> findTableNames​(Query q,
                                                             DatabaseSchema schema)
                                                      throws ObjectStoreException
        Builds a Set of all table names that are touched by a given query.
        Parameters:
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        Returns:
        a Set of table names
        Throws:
        ObjectStoreException - if something goes wrong

      • findTableNames

        public static java.util.Set<java.lang.Object> findTableNames​(Query q,
                                                             DatabaseSchema schema,
                                                             boolean individualOsbs)
                                                      throws ObjectStoreException
        Builds a Set of all table names that are touched by a given query.
        Parameters:
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        individualOsbs - if true, adds individual ObjectStoreBags to the Set, otherwise just adds the table name instead
        Returns:
        a Set of table names
        Throws:
        ObjectStoreException - if something goes wrong

      • buildFromComponent

        protected static void buildFromComponent​(SqlGenerator.State state,
                                         Query q,
                                         DatabaseSchema schema,
                                         java.util.Map<java.lang.Object,​java.lang.String> bagTableNames)
                                  throws ObjectStoreException
        Builds the FROM list for the SQL query.
        Parameters:
        state - the current Sql Query state
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        bagTableNames - a Map from BagConstraint to temporary table name
        Throws:
        ObjectStoreException - if something goes wrong

      • whereHavingSafe

        protected static boolean[] whereHavingSafe​(java.lang.Object o,
                                           Query q)
                                    throws ObjectStoreException
        Returns an array containing two boolean values. The first is whether this object is suitable for use in a WHERE clause, and the second is whether the object is suitable for use in a HAVING clause.
        Parameters:
        o - an Object of some kind
        q - the current Query
        Returns:
        an array of two booleans
        Throws:
        ObjectStoreException - if the object type is not recognised

      • completelyTrue

        protected static boolean completelyTrue​(Constraint con)
                                 throws ObjectStoreException
        Returns true if this constraint is always true, regardless of row values.
        Parameters:
        con - a Constraint
        Returns:
        a boolean
        Throws:
        ObjectStoreException - when a bag contains elements of the wrong type

      • completelyFalse

        protected static boolean completelyFalse​(Constraint con)
                                  throws ObjectStoreException
        Returns true if this constraint is always false, regardless of row values.
        Parameters:
        con - a Constraint
        Returns:
        a boolean
        Throws:
        ObjectStoreException - when a bag contains elements of the wrong type

      • constraintToString

        protected static void constraintToString​(SqlGenerator.State state,
                                         java.lang.StringBuffer buffer,
                                         Constraint c,
                                         Query q,
                                         DatabaseSchema schema,
                                         int safeness,
                                         boolean loseBrackets)
                                  throws ObjectStoreException
        Converts a Constraint object into a String suitable for putting in an SQL query.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the Constraint object
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        safeness - the ContainsConstraint safeness parameter
        loseBrackets - true if an AND ConstraintSet can be represented safely without surrounding parentheses
        Throws:
        ObjectStoreException - if something goes wrong

      • constraintSetToString

        protected static void constraintSetToString​(SqlGenerator.State state,
                                            java.lang.StringBuffer buffer,
                                            ConstraintSet c,
                                            Query q,
                                            DatabaseSchema schema,
                                            int safeness,
                                            boolean loseBrackets)
                                     throws ObjectStoreException
        Converts a ConstraintSet object into a String suitable for putting in an SQL query.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the ConstraintSet object
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        safeness - the ContainsConstraint safeness parameter
        loseBrackets - true if an AND ConstraintSet can be represented safely without surrounding parentheses
        Throws:
        ObjectStoreException - if something goes wrong

      • simpleConstraintToString

        protected static void simpleConstraintToString​(SqlGenerator.State state,
                                               java.lang.StringBuffer buffer,
                                               SimpleConstraint c,
                                               Query q)
                                        throws ObjectStoreException
        Converts a SimpleConstraint object into a String suitable for putting in an SQL query.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the SimpleConstraint object
        q - the Query
        Throws:
        ObjectStoreException - if something goes wrong

      • subqueryConstraintToString

        protected static void subqueryConstraintToString​(SqlGenerator.State state,
                                                 java.lang.StringBuffer buffer,
                                                 SubqueryConstraint c,
                                                 Query q,
                                                 DatabaseSchema schema)
                                          throws ObjectStoreException
        Converts a SubqueryConstraint object into a String suitable for putting in an SQL query.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the SubqueryConstraint object
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        Throws:
        ObjectStoreException - if something goes wrong

      • subqueryExistsConstraintToString

        protected static void subqueryExistsConstraintToString​(SqlGenerator.State state,
                                                       java.lang.StringBuffer buffer,
                                                       SubqueryExistsConstraint c,
                                                       DatabaseSchema schema)
                                                throws ObjectStoreException
        Converts a SubqueryExistsConstraint object into a String suitable for putting in an SQL query.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the SubqueryExistsConstraint object
        schema - the DatabaseSchema in which to look up metadata
        Throws:
        ObjectStoreException - if something goes wrong

      • classConstraintToString

        protected static void classConstraintToString​(SqlGenerator.State state,
                                              java.lang.StringBuffer buffer,
                                              ClassConstraint c,
                                              Query q,
                                              DatabaseSchema schema)
                                       throws ObjectStoreException
        Converts a ClassConstraint object into a String suitable for putting in an SQL query.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the ClassConstraint object
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        Throws:
        ObjectStoreException - if something goes wrong

      • containsConstraintToString

        protected static void containsConstraintToString​(SqlGenerator.State state,
                                                 java.lang.StringBuffer buffer,
                                                 ContainsConstraint c,
                                                 Query q,
                                                 DatabaseSchema schema,
                                                 int safeness,
                                                 boolean loseBrackets)
                                          throws ObjectStoreException
        Converts a ContainsConstraint object into a String suitable for putting in an SQL query.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the ContainsConstraint object
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        safeness - the ContainsConstraint safeness parameter
        loseBrackets - true if an AND ConstraintSet can be represented safely without surrounding parentheses
        Throws:
        ObjectStoreException - if something goes wrong

      • bagConstraintToString

        protected static void bagConstraintToString​(SqlGenerator.State state,
                                            java.lang.StringBuffer buffer,
                                            BagConstraint c,
                                            Query q,
                                            DatabaseSchema schema,
                                            int safeness)
                                     throws ObjectStoreException
        Converts a BagConstraint object into a String suitable for putting on an SQL query.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the BagConstraint object
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        safeness - the constraint context safeness
        Throws:
        ObjectStoreException - if something goes wrong

      • multipleInBagConstraintToString

        protected static void multipleInBagConstraintToString​(SqlGenerator.State state,
                                                      java.lang.StringBuffer buffer,
                                                      MultipleInBagConstraint c,
                                                      Query q,
                                                      int safeness)
                                               throws ObjectStoreException
        Converts a BagConstraint object into a String suitable for putting on an SQL query.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the BagConstraint object
        q - the Query
        safeness - the constraint context safeness
        Throws:
        ObjectStoreException - if something goes wrong

      • overlapConstraintToString

        protected static void overlapConstraintToString​(SqlGenerator.State state,
                                                java.lang.StringBuffer buffer,
                                                OverlapConstraint c,
                                                Query q,
                                                DatabaseSchema schema,
                                                int safeness)
                                         throws ObjectStoreException
        Converts an OverlapConstraint to a String suitable for putting in an SQL query. This will try to use a Postgres range type column first, if not present it will try BioSeg, if not present it will use simple constraints on start and end fields.
        Parameters:
        state - the current SqlGenerator state
        buffer - the StringBuffer to place text into
        c - the OverlapConstraint object
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        safeness - the constraint context safeness
        Throws:
        ObjectStoreException - if something goes wrong

      • objectToString

        public static void objectToString​(java.lang.StringBuffer buffer,
                                  java.lang.Object value)
                           throws ObjectStoreException
        Converts an Object to a String, in a form suitable for SQL.
        Parameters:
        buffer - a StringBuffer to add text to
        value - the Object to convert
        Throws:
        ObjectStoreException - if something goes wrong

      • queryClassToString

        protected static void queryClassToString​(java.lang.StringBuffer buffer,
                                         QueryClass qc,
                                         Query q,
                                         DatabaseSchema schema,
                                         int kind,
                                         SqlGenerator.State state)
                                  throws ObjectStoreException
        Converts a QueryClass to a String.
        Parameters:
        buffer - the StringBuffer to add text to
        qc - the QueryClass to convert
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        kind - the type of the output requested
        state - a State object
        Throws:
        ObjectStoreException - if the model is internally inconsistent

      • queryEvaluableToString

        protected static void queryEvaluableToString​(java.lang.StringBuffer buffer,
                                             QueryEvaluable node,
                                             Query q,
                                             SqlGenerator.State state)
                                      throws ObjectStoreException
        Converts a QueryEvaluable into a String suitable for an SQL query String.
        Parameters:
        buffer - the StringBuffer to add text to
        node - the QueryEvaluable
        q - the Query
        state - a State object
        Throws:
        ObjectStoreException - if something goes wrong

      • buildSelectComponent

        protected static java.lang.String buildSelectComponent​(SqlGenerator.State state,
                                                       Query q,
                                                       DatabaseSchema schema,
                                                       int kind)
                                                throws ObjectStoreException
        Builds a String representing the SELECT component of the Sql query.
        Parameters:
        state - the current Sql Query state
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        kind - the kind of output requested
        Returns:
        a String
        Throws:
        ObjectStoreException - if something goes wrong

      • buildGroupBy

        protected static java.lang.String buildGroupBy​(Query q,
                                               DatabaseSchema schema,
                                               SqlGenerator.State state)
                                        throws ObjectStoreException
        Builds a String representing the GROUP BY component of the Sql query.
        Parameters:
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        state - a State object
        Returns:
        a String
        Throws:
        ObjectStoreException - if something goes wrong

      • buildOrderBy

        protected static java.lang.String buildOrderBy​(SqlGenerator.State state,
                                               Query q,
                                               DatabaseSchema schema,
                                               int kind)
                                        throws ObjectStoreException
        Builds a String representing the ORDER BY component of the Sql query.
        Parameters:
        state - the current Sql Query state
        q - the Query
        schema - the DatabaseSchema in which to look up metadata
        kind - the kind of output requested
        Returns:
        a String
        Throws:
        ObjectStoreException - if something goes wrong