Class SqlUtil

java.lang.Object
net.sf.jailer.util.SqlUtil
public class SqlUtil extends Object
Some utility methods.
Author:
Ralf Wisser

  • Field Details

    • JAILER_TABLES

      public static final List<String> JAILER_TABLES
      List of all jailer tables (upper case).

    • JAILER_MH_TABLES

      public static final List<String> JAILER_MH_TABLES
      List of all jailer tables (upper case).

    • SQL_TRUE

      public static final String SQL_TRUE
      Condition formulated in standard SQL that is always fulfilled.
      See Also:

    • SQL_TYPE

      public static final Map<Integer,String> SQL_TYPE
      Maps SQL types from Types to clear text types.

    • PG_NEGATIVE_INFINITY

      public static final long PG_NEGATIVE_INFINITY
      See Also:

    • PG_NEGATIVE_SMALLER_INFINITY

      public static final long PG_NEGATIVE_SMALLER_INFINITY
      See Also:

    • PG_POSITIVE_INFINITY

      public static final long PG_POSITIVE_INFINITY
      See Also:

    • PG_POSITIVE_SMALLER_INFINITY

      public static final long PG_POSITIVE_SMALLER_INFINITY
      See Also:

  • Constructor Details

    • SqlUtil

      public SqlUtil()

  • Method Details

    • reversRestrictionCondition

      public static String reversRestrictionCondition(String condition)
      Change alias A to B and B to A in a SQL-condition.
      Parameters:
      condition - the condition
      Returns:
      condition with reversed aliases

    • replaceAliases

      public static String replaceAliases(String condition, String aliasA, String aliasB)
      Replaces the aliases A and B with given aliases in a SQL-condition.
      Parameters:
      condition - the condition
      aliasA - alias for A
      aliasB - alias for B
      Returns:
      condition with replaced aliases

    • replaceAlias

      public static String replaceAlias(String condition, String alias)
      Replaces the alias T with given alias in a SQL-condition.
      Parameters:
      condition - the condition
      alias - alias for T
      Returns:
      condition with replaced aliases

    • resolvePseudoColumns

      public static String resolvePseudoColumns(String condition, String entityAAlias, String entityBAlias, int today, int birthdayOfSubject, boolean inDeleteMode)
      Resolves the pseudo-columns in a restriction condition.
      Parameters:
      condition - the condition
      entityAAlias - alias for entity table joined with A
      entityBAlias - alias for entity table joined with B
      today - today's day counter
      birthdayOfSubject - birthday of subject
      inDeleteMode - true if processing is in delete mode
      Returns:
      the condition with pseudo-columns resolved

    • resolvePseudoColumns

      public static String resolvePseudoColumns(String condition, String entityAAlias, String entityBAlias, int today, int birthdayOfSubject, String birthdayColumnName, Boolean inDeleteMode)
      Resolves the pseudo-columns in a restriction condition.
      Parameters:
      condition - the condition
      entityAAlias - alias for entity table joined with A
      entityBAlias - alias for entity table joined with B
      today - today's day counter
      birthdayOfSubject - birthday of subject
      birthdayColumnName - name of the column which holds the birthday of an entity ('birthday' or 'orig_birthday')
      inDeleteMode - true if processing is in delete mode, null to leave the pseudo-column unresolved
      Returns:
      the condition with pseudo-columns resolved

    • resolvePseudoColumns

      public static String resolvePseudoColumns(String condition, int today, int birthdayOfSubject, boolean reversed, boolean inDeleteMode)
      Resolves the pseudo-columns in a restriction condition.
      Parameters:
      condition - the condition
      today - today's day counter
      birthdayOfSubject - birthday of subject
      reversed - true if the direction of the association is reversed
      inDeleteMode - true if processing is in delete mode
      Returns:
      the condition with pseudo-columns resolved

    • readTableList

      public static Set<Table> readTableList(CsvFile tableFile, DataModel dataModel, Map<String,String> sourceSchemaMapping)
      Reads a table-list from CSV-file.
      Parameters:
      tableFile - the file containing the list
      dataModel - to get tables from
      sourceSchemaMapping - schema mapping to apply to table names, or null
      Returns:
      set of tables, empty set if file contains no tables

    • mappedSchema

      public static String mappedSchema(Map<String,String> schemaMapping, String tableName)
      Replaces schema of qualified table name according to a schema-map.
      Parameters:
      schemaMapping - the mapping
      tableName - the table name
      Returns:
      table name with replaced schema

    • getColumnType

      public static int getColumnType(DBMS configuration, ResultSet resultSet, ResultSetMetaData resultSetMetaData, int i, Map<Integer,Integer> typeCache) throws SQLException
      Gets type of column from result-set.
      Parameters:
      configuration - the DBMS configuration
      resultSet - result-set
      resultSetMetaData - meta data of the result set
      i - column index
      typeCache - for caching types
      Returns:
      type according to Types
      Throws:
      SQLException

    • getColumnType

      public static int getColumnType(ResultSet resultSet, ResultSetMetaData resultSetMetaData, String columnName, Map<String,Integer> typeCache) throws SQLException
      Gets type of column from result-set.
      Parameters:
      resultSet - result-set
      resultSetMetaData - meta data of the result set
      columnName - column name
      typeCache - for caching types
      Returns:
      type according to Types
      Throws:
      SQLException

    • splitDMLStatement

      public static String splitDMLStatement(String sql, int maxLength)
      Splits a DML statement into several lines with limited length.
      Parameters:
      sql - the DML statement
      maxLength - maximum line length
      Returns:
      DML statement with line breaks

    • toString

      public static String toString(Double content)
      Converts a Double to a plain string representation without scientific notation.
      Parameters:
      content - the value to convert
      Returns:
      plain string representation of the value

    • toString

      public static String toString(BigDecimal content)
      Converts a BigDecimal to a plain string representation without scientific notation.
      Parameters:
      content - the value to convert
      Returns:
      plain string representation of the value

    • normalizeRestrictionCondition

      public static String normalizeRestrictionCondition(String cond)
      Normalizes a restriction condition by reordering the column references into a canonical form.
      Parameters:
      cond - the restriction condition to normalize
      Returns:
      the normalized condition, or the original condition if normalization is not possible

    • removeNonMeaningfulFragments

      public static String removeNonMeaningfulFragments(String sqlStatement)
      Removes all non-meaningful fragments of an SQL statement that might interfere with the SQL parser (comments, literals, etc.).
      Parameters:
      sqlStatement - the SQL statement to process
      Returns:
      the SQL statement with non-meaningful fragments removed

    • removeComments

      public static String removeComments(String statement)
      Removes comments from SQL statement.
      Parameters:
      statement - the statement
      Returns:
      the statement without comments

    • removeCommentsAndLiterals

      public static String removeCommentsAndLiterals(String statement)
      Removes comments and literals from SQL statement.
      Parameters:
      statement - the statement
      Returns:
      the statement without comments and literals

    • removeSubQueries

      public static String removeSubQueries(String sqlSelect)
      Removes sub-queries from SQL statement.
      Parameters:
      sqlSelect - the statement
      Returns:
      the statement without sub-queries

    • columnLabel

      public static String columnLabel(Quoting quoting, Session session, DBMS targetDBMSConfiguration, Table table, String columnLabel)
      Gets the properly quoted column label for use in a SQL statement.
      Parameters:
      quoting - the quoting to use
      session - the current database session
      targetDBMSConfiguration - the target DBMS configuration
      table - the table containing the column
      columnLabel - the column label from the result set
      Returns:
      the properly quoted column name

    • isLetterOrDigit

      public static boolean isLetterOrDigit(char c)
      Checks whether a character is a letter, digit, or underscore as used in SQL identifiers.
      Parameters:
      c - the character to check
      Returns:
      true if the character is alphabetic, a digit, or an underscore

    • createSQLFragmentSearchPattern

      public static String createSQLFragmentSearchPattern(String sql, boolean withFlags)
      Creates a regular expression pattern that matches a SQL fragment, ignoring differences in whitespace and case.
      Parameters:
      sql - the SQL fragment to create a pattern for
      withFlags - true to include case-insensitive and dot-all flags in the pattern
      Returns:
      the regular expression pattern string

    • indexOfDot

      public static int indexOfDot(String fullName)
      Gets the index of the schema-table name separator dot in a fully qualified table name.
      Parameters:
      fullName - the fully qualified table name
      Returns:
      the index of the dot separator, or -1 if not present

    • retrieveEncoding

      public static Charset retrieveEncoding(String scriptFileName) throws FileNotFoundException, IOException
      Retrieves SQL script file encoding.
      Parameters:
      scriptFileName - file name of script
      Returns:
      encoding or null
      Throws:
      FileNotFoundException
      IOException

    • getDefaultCharset

      public static Charset getDefaultCharset()
      Gets the default character set for SQL script files, taking the native encoding system property into account.
      Returns:
      the default Charset