mondrian.test.loader

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 and 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". Specific databases will represent this their own particular physical type, for example "TINYINT(1)", "BOOLEAN" or "BIT"; see DBLoader.Type.toPhysical(mondrian.spi.Dialect).

Field Summary

Fields

Modifier and Type	Field and Description
static String	BATCH_SIZE_PROP
static String	CREATE_TABLE_INDEX_PROP
static String	CREATE_TABLE_INDEX_SUFFIX_DEFAULT
static String	CREATE_TABLE_PROP
static String	CREATE_TABLE_SUFFIX_DEFAULT
(package private) static String	dateFormatString
(package private) static DateFormat	dateFormatter
(package private) static Pattern	decimalDataTypeRegex
static String	DROP_TABLE_INDEX_PROP
static String	DROP_TABLE_INDEX_SUFFIX_DEFAULT
static String	DROP_TABLE_PROP
static String	DROP_TABLE_ROWS_PROP
static String	DROP_TABLE_ROWS_SUFFIX_DEFAULT
static String	DROP_TABLE_SUFFIX_DEFAULT
(package private) static DBLoader.RowStream	EMPTY_ROW_STREAM
static String	FORCE_PROP
(package private) static DecimalFormat	integerFormatter
static String	JDBC_DRIVER_PROP
static String	JDBC_PASSWORD_PROP
static String	JDBC_URL_PROP
static String	JDBC_USER_PROP
static String	LOAD_TABLE_ROWS_PROP
static String	LOAD_TABLE_ROWS_SUFFIX_DEFAULT
protected static org.apache.log4j.Logger	LOGGER
static String	nl
(package private) static String	oracleDateFormatString
static String	OUTPUT_DIRECTORY_PROP
(package private) static Pattern	varcharDataTypeRegex

Constructor Summary

Constructors

Modifier	Constructor and Description
protected	DBLoader()

Method Summary

Methods

Modifier and Type	Method and 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 quotes
protected 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)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

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

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"

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()

initialize

public void initialize()
                throws Exception

Throws:

Exception

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.

check

protected void check()
              throws Exception

Throws:

Exception

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 - 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

writeDDL

protected void writeDDL(String ddl)
                 throws Exception

Throws:

Exception

executeDDL

protected void executeDDL(String ddl)
                   throws Exception

Throws:

Exception