Class DBLoader
- java.lang.Object
-
- mondrian.test.loader.DBLoader
-
- Direct Known Subclasses:
CsvDBLoader
public abstract class DBLoader extends java.lang.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
Fields Modifier and Type Field Description static java.lang.String
BATCH_SIZE_PROP
static java.lang.String
CREATE_TABLE_INDEX_PROP
static java.lang.String
CREATE_TABLE_INDEX_SUFFIX_DEFAULT
static java.lang.String
CREATE_TABLE_PROP
static java.lang.String
CREATE_TABLE_SUFFIX_DEFAULT
(package private) static java.lang.String
dateFormatString
(package private) static java.text.DateFormat
dateFormatter
(package private) static java.util.regex.Pattern
decimalDataTypeRegex
static java.lang.String
DROP_TABLE_INDEX_PROP
static java.lang.String
DROP_TABLE_INDEX_SUFFIX_DEFAULT
static java.lang.String
DROP_TABLE_PROP
static java.lang.String
DROP_TABLE_ROWS_PROP
static java.lang.String
DROP_TABLE_ROWS_SUFFIX_DEFAULT
static java.lang.String
DROP_TABLE_SUFFIX_DEFAULT
(package private) static DBLoader.RowStream
EMPTY_ROW_STREAM
static java.lang.String
FORCE_PROP
(package private) static java.text.DecimalFormat
integerFormatter
static java.lang.String
JDBC_DRIVER_PROP
static java.lang.String
JDBC_PASSWORD_PROP
static java.lang.String
JDBC_URL_PROP
static java.lang.String
JDBC_USER_PROP
static java.lang.String
LOAD_TABLE_ROWS_PROP
static java.lang.String
LOAD_TABLE_ROWS_SUFFIX_DEFAULT
protected static org.apache.log4j.Logger
LOGGER
static java.lang.String
nl
(package private) static java.lang.String
oracleDateFormatString
static java.lang.String
OUTPUT_DIRECTORY_PROP
(package private) static java.util.regex.Pattern
varcharDataTypeRegex
-
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 java.lang.String
columnValue(DBLoader.Column column, java.lang.Object value)
Convert the columns value to a string based upon its column type.protected java.lang.String
createInsertStatement(DBLoader.Table table, java.lang.Object[] values)
static java.lang.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 java.lang.String
decimalFormat(java.lang.String lengthStr, java.lang.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 java.lang.String
embedQuotes(java.lang.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(java.lang.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()
java.sql.Connection
getConnection()
boolean
getForce()
java.lang.String
getJdbcDriver()
java.lang.String
getJdbcURL()
java.io.File
getOutputDirectory()
java.lang.String
getPassword()
abstract DBLoader.Table[]
getTables()
java.lang.String
getUserName()
void
initialize()
protected void
initializeColumns(DBLoader.Column[] columns)
protected boolean
makeFileWriter(DBLoader.Table table, java.lang.String suffix)
protected java.lang.String
quoteId(java.lang.String name)
Quote the given SQL identifier suitable for the output DBMS.void
setBatchSize(int batchSize)
void
setConnection(java.sql.Connection connection)
void
setForce(boolean force)
void
setJdbcDriver(java.lang.String jdbcDriver)
void
setJdbcURL(java.lang.String jdbcURL)
void
setOutputDirectory(java.io.File outputDirectory)
void
setPassword(java.lang.String password)
void
setUserName(java.lang.String userName)
protected int
writeBatch(java.lang.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(java.lang.String ddl)
-
-
-
Field Detail
-
LOGGER
protected static final org.apache.log4j.Logger LOGGER
-
nl
public static final java.lang.String nl
-
BATCH_SIZE_PROP
public static final java.lang.String BATCH_SIZE_PROP
- See Also:
- Constant Field Values
-
JDBC_DRIVER_PROP
public static final java.lang.String JDBC_DRIVER_PROP
- See Also:
- Constant Field Values
-
JDBC_URL_PROP
public static final java.lang.String JDBC_URL_PROP
- See Also:
- Constant Field Values
-
JDBC_USER_PROP
public static final java.lang.String JDBC_USER_PROP
- See Also:
- Constant Field Values
-
JDBC_PASSWORD_PROP
public static final java.lang.String JDBC_PASSWORD_PROP
- See Also:
- Constant Field Values
-
OUTPUT_DIRECTORY_PROP
public static final java.lang.String OUTPUT_DIRECTORY_PROP
- See Also:
- Constant Field Values
-
FORCE_PROP
public static final java.lang.String FORCE_PROP
- See Also:
- Constant Field Values
-
DROP_TABLE_INDEX_PROP
public static final java.lang.String DROP_TABLE_INDEX_PROP
- See Also:
- Constant Field Values
-
DROP_TABLE_INDEX_SUFFIX_DEFAULT
public static final java.lang.String DROP_TABLE_INDEX_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
CREATE_TABLE_INDEX_PROP
public static final java.lang.String CREATE_TABLE_INDEX_PROP
- See Also:
- Constant Field Values
-
CREATE_TABLE_INDEX_SUFFIX_DEFAULT
public static final java.lang.String CREATE_TABLE_INDEX_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
DROP_TABLE_PROP
public static final java.lang.String DROP_TABLE_PROP
- See Also:
- Constant Field Values
-
DROP_TABLE_SUFFIX_DEFAULT
public static final java.lang.String DROP_TABLE_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
DROP_TABLE_ROWS_PROP
public static final java.lang.String DROP_TABLE_ROWS_PROP
- See Also:
- Constant Field Values
-
DROP_TABLE_ROWS_SUFFIX_DEFAULT
public static final java.lang.String DROP_TABLE_ROWS_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
CREATE_TABLE_PROP
public static final java.lang.String CREATE_TABLE_PROP
- See Also:
- Constant Field Values
-
CREATE_TABLE_SUFFIX_DEFAULT
public static final java.lang.String CREATE_TABLE_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
LOAD_TABLE_ROWS_PROP
public static final java.lang.String LOAD_TABLE_ROWS_PROP
- See Also:
- Constant Field Values
-
LOAD_TABLE_ROWS_SUFFIX_DEFAULT
public static final java.lang.String LOAD_TABLE_ROWS_SUFFIX_DEFAULT
- See Also:
- Constant Field Values
-
decimalDataTypeRegex
static final java.util.regex.Pattern decimalDataTypeRegex
-
varcharDataTypeRegex
static final java.util.regex.Pattern varcharDataTypeRegex
-
integerFormatter
static final java.text.DecimalFormat integerFormatter
-
dateFormatString
static final java.lang.String dateFormatString
- See Also:
- Constant Field Values
-
oracleDateFormatString
static final java.lang.String oracleDateFormatString
- See Also:
- Constant Field Values
-
dateFormatter
static final java.text.DateFormat dateFormatter
-
EMPTY_ROW_STREAM
static final DBLoader.RowStream EMPTY_ROW_STREAM
-
-
Method Detail
-
decimalFormat
public static java.lang.String decimalFormat(java.lang.String lengthStr, java.lang.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 java.lang.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 java.lang.Exception
- Throws:
java.lang.Exception
-
dropTables
public void dropTables(DBLoader.Table[] tables) throws java.lang.Exception
- Throws:
java.lang.Exception
-
dropTable
public void dropTable(DBLoader.Table table) throws java.lang.Exception
- Throws:
java.lang.Exception
-
setOutputDirectory
public void setOutputDirectory(java.io.File outputDirectory)
-
getOutputDirectory
public java.io.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(java.lang.String jdbcDriver)
-
getJdbcDriver
public java.lang.String getJdbcDriver()
-
setJdbcURL
public void setJdbcURL(java.lang.String jdbcURL)
-
getJdbcURL
public java.lang.String getJdbcURL()
-
setUserName
public void setUserName(java.lang.String userName)
-
getUserName
public java.lang.String getUserName()
-
setPassword
public void setPassword(java.lang.String password)
-
getPassword
public java.lang.String getPassword()
-
getConnection
public java.sql.Connection getConnection()
-
initialize
public void initialize() throws java.lang.Exception
- Throws:
java.lang.Exception
-
setConnection
public void setConnection(java.sql.Connection connection)
-
generateStatements
public void generateStatements(DBLoader.Table[] tables) throws java.lang.Exception
- Throws:
java.lang.Exception
-
generateStatements
protected void generateStatements(DBLoader.Table table) throws java.lang.Exception
- Throws:
java.lang.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 java.lang.Exception
- Throws:
java.lang.Exception
-
executeStatements
protected void executeStatements(DBLoader.Table table) throws java.lang.Exception
- Throws:
java.lang.Exception
-
makeFileWriter
protected boolean makeFileWriter(DBLoader.Table table, java.lang.String suffix) throws java.lang.Exception
- Throws:
java.lang.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.
-
check
protected void check() throws java.lang.Exception
- Throws:
java.lang.Exception
-
initializeColumns
protected void initializeColumns(DBLoader.Column[] columns)
-
executeBeforeActions
protected void executeBeforeActions(DBLoader.Table table) throws java.lang.Exception
- Throws:
java.lang.Exception
-
executeAfterActions
protected void executeAfterActions(DBLoader.Table table) throws java.lang.Exception
- Throws:
java.lang.Exception
-
executeDropTableRows
protected boolean executeDropTableRows(DBLoader.Table table) throws java.lang.Exception
- Throws:
java.lang.Exception
-
executeDropTable
protected boolean executeDropTable(DBLoader.Table table)
-
executeCreateTable
protected boolean executeCreateTable(DBLoader.Table table)
-
executeLoadTableRows
protected int executeLoadTableRows(DBLoader.Table table)
-
createInsertStatement
protected java.lang.String createInsertStatement(DBLoader.Table table, java.lang.Object[] values) throws java.lang.Exception
- Throws:
java.lang.Exception
-
quoteId
protected java.lang.String quoteId(java.lang.String name)
Quote the given SQL identifier suitable for the output DBMS.- Parameters:
name
- Identifier- Returns:
- Quoted identifier
-
columnValue
protected java.lang.String columnValue(DBLoader.Column column, java.lang.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 java.lang.String embedQuotes(java.lang.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(java.lang.String[] batch, int batchSize) throws java.io.IOException, java.sql.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:
java.io.IOException
java.sql.SQLException
-
writeDDL
protected void writeDDL(java.lang.String ddl) throws java.lang.Exception
- Throws:
java.lang.Exception
-
executeDDL
protected void executeDDL(java.lang.String ddl) throws java.lang.Exception
- Throws:
java.lang.Exception
-
-