Class DBLoader
- java.lang.Object
-
- mondrian.test.loader.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
-
-
Nested Class Summary
Nested Classes Modifier and Type Class Description static class
DBLoader.Column
static interface
DBLoader.Row
static class
DBLoader.RowDefault
static interface
DBLoader.RowStream
The RowStream interface allows one to load large sets of rows by streaming them in, one does not have to have all of the row data in memory.class
DBLoader.Table
static class
DBLoader.Type
Represents a logical type, such as "BOOLEAN".
-
Field Summary
-
Constructor Summary
Constructors Modifier Constructor Description protected
DBLoader()
-
Method Summary
All Methods Static Methods Instance Methods Abstract Methods Concrete Methods Modifier and Type Method Description protected void
check()
void
clear()
Undoes all of the database table creations performed when the load method was called.void
close()
Releases resources.protected void
closeFileWriter()
protected String
columnValue(DBLoader.Column column, Object value)
Convert the columns value to a string based upon its column type.protected String
createInsertStatement(DBLoader.Table table, Object[] values)
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.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.void
dropTable(DBLoader.Table table)
void
dropTables(DBLoader.Table[] tables)
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 quotesprotected void
executeAfterActions(DBLoader.Table table)
protected void
executeBeforeActions(DBLoader.Table table)
protected boolean
executeCreateTable(DBLoader.Table table)
protected void
executeDDL(String ddl)
protected boolean
executeDropTable(DBLoader.Table table)
protected boolean
executeDropTableRows(DBLoader.Table table)
protected int
executeLoadTableRows(DBLoader.Table table)
protected void
executeStatements(DBLoader.Table table)
void
executeStatements(DBLoader.Table[] tables)
protected void
generateAfterActions(DBLoader.Table table)
protected void
generateBeforeActions(DBLoader.Table table)
protected void
generateCreateTable(DBLoader.Table table)
protected void
generateDropTable(DBLoader.Table table)
protected void
generateDropTableRows(DBLoader.Table table)
protected void
generateStatements(DBLoader.Table table)
void
generateStatements(DBLoader.Table[] tables)
int
getBatchSize()
Connection
getConnection()
boolean
getForce()
String
getJdbcDriver()
String
getJdbcURL()
File
getOutputDirectory()
String
getPassword()
abstract DBLoader.Table[]
getTables()
String
getUserName()
void
initialize()
protected void
initializeColumns(DBLoader.Column[] columns)
protected boolean
makeFileWriter(DBLoader.Table table, String suffix)
protected String
quoteId(String name)
Quote the given SQL identifier suitable for the output DBMS.void
setBatchSize(int batchSize)
void
setConnection(Connection connection)
void
setForce(boolean force)
void
setJdbcDriver(String jdbcDriver)
void
setJdbcURL(String jdbcURL)
void
setOutputDirectory(File outputDirectory)
void
setPassword(String password)
void
setUserName(String userName)
protected int
writeBatch(String[] batch, int batchSize)
If we are outputting to JDBC, Execute the given set of SQL statements Otherwise, output the statements to a file.protected void
writeDDL(String ddl)
-
-
-
Field Detail
-
LOGGER
protected static final org.apache.log4j.Logger LOGGER
-
nl
public static final String nl
-
BATCH_SIZE_PROP
public static final String BATCH_SIZE_PROP
- See Also:
- Constant Field Values
-
JDBC_DRIVER_PROP
public static final String JDBC_DRIVER_PROP
- See Also:
- Constant Field Values
-
JDBC_URL_PROP
public static final String JDBC_URL_PROP
- See Also:
- Constant Field Values
-
JDBC_USER_PROP
public static final String JDBC_USER_PROP
- See Also:
- Constant Field Values
-
JDBC_PASSWORD_PROP
public static final String JDBC_PASSWORD_PROP
- See Also:
- Constant Field Values
-
OUTPUT_DIRECTORY_PROP
public static final String OUTPUT_DIRECTORY_PROP
- See Also:
- Constant Field Values
-
FORCE_PROP
public static final String FORCE_PROP
- See Also:
- Constant Field Values
-
DROP_TABLE_INDEX_PROP
public static final String DROP_TABLE_INDEX_PROP
- See Also:
- Constant Field Values
-
DROP_TABLE_INDEX_SUFFIX_DEFAULT
public static final String DROP_TABLE_INDEX_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
CREATE_TABLE_INDEX_PROP
public static final String CREATE_TABLE_INDEX_PROP
- See Also:
- Constant Field Values
-
CREATE_TABLE_INDEX_SUFFIX_DEFAULT
public static final String CREATE_TABLE_INDEX_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
DROP_TABLE_PROP
public static final String DROP_TABLE_PROP
- See Also:
- Constant Field Values
-
DROP_TABLE_SUFFIX_DEFAULT
public static final String DROP_TABLE_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
DROP_TABLE_ROWS_PROP
public static final String DROP_TABLE_ROWS_PROP
- See Also:
- Constant Field Values
-
DROP_TABLE_ROWS_SUFFIX_DEFAULT
public static final String DROP_TABLE_ROWS_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
CREATE_TABLE_PROP
public static final String CREATE_TABLE_PROP
- See Also:
- Constant Field Values
-
CREATE_TABLE_SUFFIX_DEFAULT
public static final String CREATE_TABLE_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
LOAD_TABLE_ROWS_PROP
public static final String LOAD_TABLE_ROWS_PROP
- See Also:
- Constant Field Values
-
LOAD_TABLE_ROWS_SUFFIX_DEFAULT
public static final String LOAD_TABLE_ROWS_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
decimalDataTypeRegex
static final Pattern decimalDataTypeRegex
-
varcharDataTypeRegex
static final Pattern varcharDataTypeRegex
-
integerFormatter
static final DecimalFormat integerFormatter
-
dateFormatString
static final String dateFormatString
- See Also:
- Constant Field Values
-
oracleDateFormatString
static final String oracleDateFormatString
- See Also:
- Constant Field Values
-
dateFormatter
static final DateFormat dateFormatter
-
EMPTY_ROW_STREAM
static final DBLoader.RowStream EMPTY_ROW_STREAM
-
-
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 formatplacesStr
- 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 formatplaces
- int: number of decimal places- Returns:
- number format, ie. length = 6, places = 2 => "###0.00"
-
getTables
public abstract DBLoader.Table[] getTables() throws Exception
- Throws:
Exception
-
dropTables
public void dropTables(DBLoader.Table[] tables) throws Exception
- Throws:
Exception
-
dropTable
public void dropTable(DBLoader.Table table) throws Exception
- Throws:
Exception
-
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)
-
generateStatements
public void generateStatements(DBLoader.Table[] tables) throws Exception
- Throws:
Exception
-
generateStatements
protected void generateStatements(DBLoader.Table table) throws Exception
- Throws:
Exception
-
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)
-
executeStatements
public void executeStatements(DBLoader.Table[] tables) throws Exception
- Throws:
Exception
-
executeStatements
protected void executeStatements(DBLoader.Table table) throws Exception
- Throws:
Exception
-
makeFileWriter
protected boolean makeFileWriter(DBLoader.Table table, String suffix) throws Exception
- Throws:
Exception
-
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)
-
executeBeforeActions
protected void executeBeforeActions(DBLoader.Table table) throws Exception
- Throws:
Exception
-
executeAfterActions
protected void executeAfterActions(DBLoader.Table table) throws Exception
- Throws:
Exception
-
executeDropTableRows
protected boolean executeDropTableRows(DBLoader.Table table) throws Exception
- Throws:
Exception
-
executeDropTable
protected boolean executeDropTable(DBLoader.Table table)
-
executeCreateTable
protected boolean executeCreateTable(DBLoader.Table table)
-
executeLoadTableRows
protected int executeLoadTableRows(DBLoader.Table table)
-
createInsertStatement
protected String createInsertStatement(DBLoader.Table table, Object[] values) throws Exception
- Throws:
Exception
-
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
- Columnvalue
- 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 executebatchSize
- # SQL statements to execute- Returns:
- # SQL statements executed
- Throws:
IOException
SQLException
-
-