Class DBLoader

  • Direct Known Subclasses:
    CsvDBLoader

    public abstract class DBLoader
    extends Object
    This is an abstract base class for the creation and load of one or more database tables with data. Optionally, if a table already exists it can be dropped or its rows deleted.

    Within this class is the Table sub-class. There is an abstract method the returns an array of Table instances. A Table instance is used to define a database table and its content (its rows or data). A concrete subclass of this class can define how the array of Tables are created (CSV files, XML files, by reading another database, etc.) and then using this class load thoses Tables into a database. In addition, rather than loading a database, this class can be used to generate files containing the sql that can be used to load the database.

    To use this class the following must be specified:

    • JDBC Driver: This is used both for the generation of the SQL (using the SqlQuery.Dialect) and loading the database itself.
    • JDBC URL: How to connect to the database.
    • User Name: The database user name (optional).
    • Password: The database password (optional).
    • Output Directory: If specified, then rather than creating and loading the tables in the database, files containing database specific SQL statements are generated. The names of the files are based upon the table name plus a suffix. Each table has four files for the different SQL operations: drop the table, drop the rows in the table, create the table and load the rows in the table. The suffixes have default values which can be overriden the the value of System properties:
      • The property "mondrian.test.loader.drop.table.suffix" can be used to define the drop table suffix. The default value is "drop.sql".
      • The property "mondrian.test.loader.drop.table.rows.suffix" can be used to define the drop rows table suffix. The default value is "droprows.sql".
      • The property "mondrian.test.loader.create.table.suffix" can be used to define the create table suffix. The default value is "create.sql".
      • The property "mondrian.test.loader.table.rows.suffix" can be used to define the create table suffix. The default value is "loadrows.sql".
    • Force: If files are being generated and if they already exist, setting the force flag to true instructs this class to over-write the existing file. If the force flag is false and a file already exists and exception is thrown.

    Each Table object created has a Controller object with four boolean instance variables that control what actions are taken when the Table's executeStatements method is called. Those instance variables are:

    • DropTable: If true, the table is dropped from the database (Default value is true).
    • DropRows: If true, the rows in the table are dropped (Default value is true).
    • CreateTable: If true, the table is created in the database (Default value is true).
    • LoadRows: If true, the rows are loaded into the table (Default value is true).

    The Table.Controller must also have its RowStream object defined. A RowStream produces a set of Row objects (see the Row interface below) which in turn has an array of Objects that represent the values of a row in the Table. The default RowStream is an emtpy RowStrean, no rows. The user must implement the RowStrean interface. One such implementation might be a RowStrean containing a list of rows. In this case, all of the rows would be in memory. Another implementation might read each row's data from a file or another database. In this case, the row data is not in memory allowing one to load much larger tables.

    Each column must have one of the following SQL data type definitions:

    • INTEGER
    • DECIMAL(*,*)
    • SMALLINT
    • VARCHAR(*)
    • REAL
    • BOOLEAN
    • BIGINT
    • DATE
    • TIMESTAMP

    NOTE: Much of the code appearing in this class came from the MondrianFoodMartLoader class.

    Author:
    Richard M. Emberson
    • Constructor Detail

      • DBLoader

        protected DBLoader()
    • Method Detail

      • decimalFormat

        public static String decimalFormat​(String lengthStr,
                                           String placesStr)
        Generate an appropriate number format string for doubles etc to be used to include a number in an SQL insert statement. Calls decimalFormat(int length, int places) to do the work.
        Parameters:
        lengthStr - String representing integer: number of digits to format
        placesStr - String representing integer: number of decimal places
        Returns:
        number format, ie. length = 6, places = 2 => "####.##"
      • decimalFormat

        public static String decimalFormat​(int length,
                                           int places)
        Generate an appropriate number format string for doubles etc to be used to include a number in an SQL insert statement.
        Parameters:
        length - int: number of digits to format
        places - int: number of decimal places
        Returns:
        number format, ie. length = 6, places = 2 => "###0.00"
      • setOutputDirectory

        public void setOutputDirectory​(File outputDirectory)
      • getOutputDirectory

        public File getOutputDirectory()
      • setForce

        public void setForce​(boolean force)
      • getForce

        public boolean getForce()
      • setBatchSize

        public void setBatchSize​(int batchSize)
      • getBatchSize

        public int getBatchSize()
      • setJdbcDriver

        public void setJdbcDriver​(String jdbcDriver)
      • getJdbcDriver

        public String getJdbcDriver()
      • setJdbcURL

        public void setJdbcURL​(String jdbcURL)
      • getJdbcURL

        public String getJdbcURL()
      • setUserName

        public void setUserName​(String userName)
      • getUserName

        public String getUserName()
      • setPassword

        public void setPassword​(String password)
      • getPassword

        public String getPassword()
      • getConnection

        public Connection getConnection()
      • setConnection

        public void setConnection​(Connection connection)
      • generateBeforeActions

        protected void generateBeforeActions​(DBLoader.Table table)
      • generateDropTable

        protected void generateDropTable​(DBLoader.Table table)
      • generateDropTableRows

        protected void generateDropTableRows​(DBLoader.Table table)
      • generateCreateTable

        protected void generateCreateTable​(DBLoader.Table table)
      • generateAfterActions

        protected void generateAfterActions​(DBLoader.Table table)
      • closeFileWriter

        protected void closeFileWriter()
      • clear

        public void clear()
        Undoes all of the database table creations performed when the load method was called.
      • close

        public void close()
        Releases resources.

        Call this method when the load process is finished and the connection is no longer going to be used.

      • initializeColumns

        protected void initializeColumns​(DBLoader.Column[] columns)
      • executeDropTable

        protected boolean executeDropTable​(DBLoader.Table table)
      • executeCreateTable

        protected boolean executeCreateTable​(DBLoader.Table table)
      • executeLoadTableRows

        protected int executeLoadTableRows​(DBLoader.Table table)
      • quoteId

        protected String quoteId​(String name)
        Quote the given SQL identifier suitable for the output DBMS.
        Parameters:
        name - Identifier
        Returns:
        Quoted identifier
      • columnValue

        protected String columnValue​(DBLoader.Column column,
                                     Object value)
        Convert the columns value to a string based upon its column type.
        Parameters:
        column - Column
        value - Column value
        Returns:
        Column value as a SQL string
      • embedQuotes

        protected String embedQuotes​(String original)
        Generate an appropriate string to use in an SQL insert statement for a VARCHAR colummn, taking into account NULL strings and strings with embedded quotes
        Parameters:
        original - String to transform
        Returns:
        NULL if null string, otherwise massaged string with doubled quotes for SQL
      • writeBatch

        protected int writeBatch​(String[] batch,
                                 int batchSize)
                          throws IOException,
                                 SQLException
        If we are outputting to JDBC, Execute the given set of SQL statements Otherwise, output the statements to a file.
        Parameters:
        batch - SQL statements to execute
        batchSize - # SQL statements to execute
        Returns:
        # SQL statements executed
        Throws:
        IOException
        SQLException