/**
* Licensed to JumpMind Inc under one or more contributor
* license agreements. See the NOTICE file distributed
* with this work for additional information regarding
* copyright ownership. JumpMind Inc licenses this file
* to you under the GNU General Public License, version 3.0 (GPLv3)
* (the "License"); you may not use this file except in compliance
* with the License.
*
* You should have received a copy of the GNU General Public License,
* version 3.0 (GPLv3) along with this library; if not, see
* <http://www.gnu.org/licenses/>.
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
package org.jumpmind.symmetric.io.data;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;
import java.util.UUID;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;
import org.jumpmind.db.model.Column;
import org.jumpmind.db.model.ForeignKey;
import org.jumpmind.db.model.Reference;
import org.jumpmind.db.model.Table;
import org.jumpmind.db.platform.IDatabasePlatform;
import org.jumpmind.db.sql.DmlStatement;
import org.jumpmind.db.sql.DmlStatement.DmlType;
import org.jumpmind.db.sql.ISqlRowMapper;
import org.jumpmind.db.sql.Row;
import org.jumpmind.db.sql.SqlException;
import org.jumpmind.db.util.BinaryEncoding;
import org.jumpmind.util.AppUtils;
import org.jumpmind.util.FormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Generate data for populating databases.
*/
public class DbFill {
final Logger log = LoggerFactory.getLogger(getClass());
private String catalog;
private String schema;
private IDatabasePlatform platform;
private boolean ignoreMissingTables;
private boolean cascading = false;
private String ignore[] = null;
private String prefixed[] = null;
private int inputLength = 1;
private Random rand = null;
private int interval = 0;
private boolean debug = false;
private boolean verbose = false;
private boolean continueOnError = false;
private boolean print = false;
private String textColumnExpression;
// Weights given to insert, update, and delete commands when
// randomly selecting a command for any given table.
private int[] dmlWeight = {1,0,0};
private Table[] allDbTablesCache = null;
// -1 for no limit
private static final int RANDOM_SELECT_SIZE = 100;
// Must remain 0-2 to choose randomly.
public final static int INSERT = 0;
public final static int UPDATE = 1;
public final static int DELETE = 2;
private boolean firstPass = true;
public DbFill() {
}
public DbFill(IDatabasePlatform platform) {
this.platform = platform;
}
public void fillTables(String... tableNames) {
fillTables(tableNames, null);
}
public void fillTables(String[] tableNames, Map<String,int[]> tableProperties) {
Table[] tables;
if (tableNames.length == 0) {
// If no tableNames are provided look up all tables.
tables = getAllDbTables();
if (ignore != null) {
// Ignore any tables matching an ignorePrefix. (e.g., "sym_")
List<Table> tableList = new ArrayList<Table>(tables.length);
table_loop:
for (Table table : tables) {
for (String ignoreName : ignore) {
if (table.getName().startsWith(ignoreName)) {
if (verbose) {
log.info("Ignore table " + table.getName());
}
continue table_loop;
}
}
for (String prefixedName : prefixed) {
if (!table.getName().startsWith(prefixedName)) {
if (verbose) {
log.info("Non prefixed table (" + prefixedName + ")" + table.getName());
}
continue table_loop;
}
}
tableList.add(table);
}
tables = tableList.toArray(new Table[tableList.size()]);
}
} else {
ArrayList<Table> tableList = new ArrayList<Table>();
for (String tableName : tableNames) {
Table table = platform.readTableFromDatabase(getCatalogToUse(), getSchemaToUse(),
tableName);
if (table != null) {
tableList.add(table);
} else if (!ignoreMissingTables) {
throw new RuntimeException("Cannot find table " + tableName + " in catalog "
+ getCatalogToUse() + " and schema " + getSchemaToUse());
}
}
tables = tableList.toArray(new Table[tableList.size()]);
}
fillTables(tables, tableProperties);
}
/**
* Identify the tables not included in the given list that the initial tables have FK relationships to.
*
* @param tables
*
* @return The table array argument and the tables that the initial table array argument depend on.
*/
public Table[] addFkInsertDependentTables(Table... tables) {
Table[] fkDepTblArray = null;
if (tables != null) {
List<Table> fkDepList = new ArrayList<Table>();
Set<String> tableNames = new HashSet<String>();
for (Table tbl : tables) {
tableNames.add(tbl.getName());
}
for (Table table : tables) {
for (ForeignKey fk : table.getForeignKeys()) {
if (tableNames.add(fk.getForeignTableName())) {
Table tableObj = getDbTable(fk.getForeignTableName());
fkDepList.add(tableObj);
}
}
}
fkDepTblArray = fkDepList.toArray(new Table[fkDepList.size()]);
fkDepTblArray = (Table[])ArrayUtils.addAll(fkDepTblArray, tables);
if (fkDepList.size()>0) {
fkDepTblArray = addFkInsertDependentTables(fkDepTblArray);
}
}
return fkDepTblArray;
}
/**
* Identify the tables not included in the given list that the initial tables have FK relationships to.
*
* @param deleteTables
*
* @return The table array argument and the tables that the initial table array argument depend on.
*/
public Table[] addFkDeleteDependentTables(Table... deleteTables) {
Table[] fkDepTblArray = null;
if (deleteTables != null) {
List<Table> fkDepList = new ArrayList<Table>();
Set<String> deleteTableNames = new HashSet<String>();
for (Table tbl : deleteTables) {
deleteTableNames.add(tbl.getName());
}
Table[] allTables = getAllDbTables();
for (Table table : allTables) {
for (ForeignKey fk : table.getForeignKeys()) {
if (deleteTableNames.contains(fk.getForeignTableName())) {
if (deleteTableNames.add(table.getName())) {
fkDepList.add(table);
}
}
}
}
fkDepTblArray = fkDepList.toArray(new Table[fkDepList.size()]);
fkDepTblArray = (Table[])ArrayUtils.addAll(fkDepTblArray, deleteTables);
if (fkDepList.size()>0) {
fkDepTblArray = addFkDeleteDependentTables(fkDepTblArray);
}
}
return fkDepTblArray;
}
/**
* Once we have an array of table objects we can begin sorting and IUD operations.
*
* @param tables Array of table objects.
*/
private void fillTables(Table[] tables, Map<String,int[]> tableProperties) {
for (int i = 0; i < inputLength; i++) {
makePass(tables, tableProperties);
}
}
/**
* Perform an INSERT, UPDATE, or DELETE on every table in tables.
*
* @param tables Array of tables to perform statement on. Tables must be in
* insert order.
* @param tableProperties Map indicating IUD weights for each table name provided
* in the properties file.
*/
private void makePass(Table[] tables, Map<String,int[]> tableProperties) {
for (Table table : tables) {
// Sleep for the configured time between tables
if (!firstPass) {
AppUtils.sleep(interval);
} else {
firstPass = false;
}
int dmlType = INSERT;
if (tableProperties != null && tableProperties.containsKey(table.getName())) {
dmlType = randomIUD(tableProperties.get(table.getName()));
} else if (dmlWeight != null) {
dmlType = randomIUD(dmlWeight);
}
switch (dmlType) {
case INSERT:
if (verbose) {
log.info("Inserting into table " + table.getName());
}
insertRandomRecord(table);
break;
case UPDATE:
if (verbose) {
log.info("Updating record in table " + table.getName());
}
updateRandomRecord(table);
break;
case DELETE:
if (verbose) {
log.info("Deleting record in table " + table.getName());
}
deleteRandomRecord(table);
break;
}
}
}
/**
* Given a table's IUD weights a random DML statement type is chosen.
*
* @param iudWeight
* @return
*/
private int randomIUD(int[] iudWeight) {
if (iudWeight.length != 3) {
throw new RuntimeException("Incorrect number of IUD weights provided.");
}
int total = iudWeight[0] + iudWeight[1] + iudWeight[2];
if (total == 0) {
return INSERT;
}
int rVal = getRand().nextInt(total);
if (rVal < iudWeight[0]) {
return INSERT;
} else if (rVal < iudWeight[0] + iudWeight[1]) {
return UPDATE;
}
return DELETE;
}
/**
* Select a random row from the table in the connected database. Return null if there are no rows.
*
* TODO: Cache rows.
*
* @param sqlTemplate
* @param table The table to select a row from.
* @return A random row from the table. Null if there are no rows.
*/
private Row selectRandomRow(Table table) {
Row row = null;
// Select all rows and return the primary key columns.
String sql = platform.createDmlStatement(DmlType.SELECT_ALL, table.getCatalog(), table.getSchema(), table.getName(),
table.getPrimaryKeyColumns(), table.getColumns(), null, textColumnExpression).getSql();
final List<Row> rows = new ArrayList<Row>();
platform.getSqlTemplate().query(sql, RANDOM_SELECT_SIZE, new ISqlRowMapper<Object>() {
public Object mapRow(Row row) {
rows.add(row);
return Boolean.TRUE;
}
}, null, null);
if (rows.size() != 0) {
int rowNum = getRand().nextInt(rows.size());
row = rows.get(rowNum);
}
return row;
}
private void updateRandomRecord(Table table) {
DmlStatement updStatement = createUpdateDmlStatement(table);
Row row = createRandomUpdateValues(updStatement, table);
try {
platform.getSqlTemplate().update(updStatement.getSql(), row.toArray(table.getColumnNames()));
if (verbose) {
log.info("Successful update in " + table.getName());
}
} catch (SqlException ex) {
log.info("Failed to process {} with values of {}", updStatement.getSql(),
ArrayUtils.toString(row.toArray(table.getColumnNames())));
if (continueOnError) {
if (debug) {
log.info("", ex);
}
} else {
throw ex;
}
}
}
/**
* Select a random row from the table and update all columns except for primary and foreign keys.
*
* @param sqlTemplate
* @param table
*/
private void insertRandomRecord(Table table) {
DmlStatement insertStatement = createInsertDmlStatement(table);
Row row = createRandomInsertValues(insertStatement, table);
try {
platform.getSqlTemplate().update(insertStatement.getSql(), insertStatement.getValueArray(row.toArray(table.getColumnNames()),
row.toArray(table.getPrimaryKeyColumnNames())));
if (verbose) {
log.info("Successful update in " + table.getName());
}
} catch (SqlException ex) {
log.info("Failed to process {} with values of {}", insertStatement.getSql(),
ArrayUtils.toString(row.toArray(table.getColumnNames())));
if (continueOnError) {
if (debug) {
log.info("", ex);
}
} else {
throw ex;
}
}
}
public String createDynamicRandomInsertSql(Table table) {
DmlStatement insertStatement = createInsertDmlStatement(table);
Row row = createRandomInsertValues(insertStatement, table);
return insertStatement.buildDynamicSql(BinaryEncoding.HEX, row, false, true);
}
public String createDynamicRandomUpdateSql(Table table) {
DmlStatement updStatement = createUpdateDmlStatement(table);
Row row = createRandomUpdateValues(updStatement, table);
return updStatement.buildDynamicSql(BinaryEncoding.HEX, row, false, true);
}
public String createDynamicRandomDeleteSql(Table table) {
DmlStatement deleteStatement = createDeleteDmlStatement(table);
Row row = selectRandomRow(table);
return deleteStatement.buildDynamicDeleteSql(BinaryEncoding.HEX, row, false, true);
}
/**
* Delete a random row in the given table or delete all rows matching selectColumns
* in the given table.
*
* @param table Table to delete from.
* @param selectColumns If provided, the rows that match this criteria are deleted.
*/
private void deleteRandomRecord(Table table) {
DmlStatement deleteStatement = createDeleteDmlStatement(table);
Row row = selectRandomRow(table);
try {
platform.getSqlTemplate().update(deleteStatement.getSql(), row.toArray(table.getColumnNames()));
if (verbose) {
log.info("Successful update in " + table.getName());
}
} catch (SqlException ex) {
log.info("Failed to process {} with values of {}", deleteStatement.getSql(),
ArrayUtils.toString(row.toArray(table.getColumnNames())));
if (continueOnError) {
if (debug) {
log.info("", ex);
}
} else {
throw ex;
}
}
}
private Object generateRandomValueForColumn(Column column) {
Object objectValue = null;
int type = column.getMappedTypeCode();
if (column.isEnum()) {
objectValue = column.getEnumValues()[new Random().nextInt(column.getEnumValues().length)];
} else if (column.isTimestampWithTimezone()) {
objectValue = String.format("%s %s",
FormatUtils.TIMESTAMP_FORMATTER.format(randomDate()),
AppUtils.getTimezoneOffset());
} else if (type == Types.DATE) {
objectValue = DateUtils.truncate(randomDate(), Calendar.DATE);
} else if (type == Types.TIMESTAMP || type == Types.TIME) {
objectValue = randomTimestamp();
} else if (type == Types.INTEGER || type == Types.BIGINT) {
objectValue = randomInt();
} else if (type == Types.SMALLINT) {
objectValue = randomSmallInt(column.getJdbcTypeName().toLowerCase().contains("unsigned"));
} else if (type == Types.FLOAT) {
objectValue = randomFloat();
} else if (type == Types.DOUBLE) {
objectValue = randomDouble();
} else if (type == Types.TINYINT) {
objectValue = randomTinyInt();
} else if (type == Types.NUMERIC || type == Types.DECIMAL
|| type == Types.REAL) {
objectValue = randomBigDecimal(column.getSizeAsInt(), column.getScale());
} else if (type == Types.BOOLEAN || type == Types.BIT) {
objectValue = randomBoolean();
} else if (type == Types.BLOB || type == Types.LONGVARBINARY || type == Types.BINARY
|| type == Types.VARBINARY ||
// SQLServer text type
type == -10) {
objectValue = randomBytes();
} else if (type == Types.ARRAY) {
objectValue = null;
} else if (type == Types.VARCHAR || type == Types.LONGVARCHAR || type == Types.CHAR || type == Types.CLOB) {
int size = 0;
// Assume if the size is 0 there is no max size configured.
if (column.getSizeAsInt() != 0) {
size = column.getSizeAsInt()>50?50:column.getSizeAsInt();
} else {
// No max length so default to 50
size = 50;
}
objectValue = randomString(size);
} else if (type == Types.OTHER) {
if ("UUID".equalsIgnoreCase(column.getJdbcTypeName())) {
objectValue = randomUUID();
}
}
return objectValue;
}
private Object randomSmallInt(boolean unsigned) {
if (unsigned) {
return new Integer(getRand().nextInt(32768));
} else {
// TINYINT (-32768 32767)
return new Integer(getRand().nextInt(65535) - 32768);
}
}
private Object randomFloat() {
return getRand().nextFloat();
}
private Object randomDouble() {
final long places = 1000000000l;
double d = Math.random()*places;
long l = Math.round(d);
return ((double)l)/(double)places+2 + (double)randomInt();
}
private Object randomTinyInt() {
// TINYINT (-128 to 127) or (0 to 255) depending on database platform
return new Integer(getRand().nextInt(127));
}
private String randomString(int maxLength) {
StringBuilder str = new StringBuilder(maxLength);
for (int i = 0; i < maxLength; i++) {
str.append(randomChar());
}
return str.toString();
}
private byte[] randomBytes() {
int length = 10;
byte array[] = new byte[length];
for (int i = 0; i < length; i++) {
array[i] = (byte) getRand().nextInt(256);
}
return array;
}
private boolean randomBoolean() {
return getRand().nextBoolean();
}
private BigDecimal randomBigDecimal(int size, int digits) {
if (size == 0 && digits == 0) {
// set the values to something reasonable
size = 10;
digits = 6;
}
Random rnd = getRand();
StringBuilder str = new StringBuilder();
if (size>0 && rnd.nextBoolean()) {
str.append("-");
}
for (int i=0; i<size; i++) {
if (i == size-digits)
str.append(".");
str.append(rnd.nextInt(10));
}
return new BigDecimal(str.toString());
}
private Character randomChar() {
int rnd = getRand().nextInt(52);
char base = (rnd < 26) ? 'A' : 'a';
return (char) (base + rnd % 26);
}
private Date randomDate() {
// Random date between 1970 and 2020
long l = Math.abs(getRand().nextLong());
long ms = (50L * 365 * 24 * 60 * 60 * 1000);
return new Date(l % ms);
}
private Timestamp randomTimestamp() {
return Timestamp.valueOf(FormatUtils.TIMESTAMP_FORMATTER.format(randomDate()));
}
private Integer randomInt() {
return new Integer(getRand().nextInt(1000000));
}
private String randomUUID() {
return UUID.randomUUID().toString();
}
public String getSchemaToUse() {
if (StringUtils.isBlank(schema)) {
return platform.getDefaultSchema();
} else {
return schema;
}
}
public String getCatalogToUse() {
if (StringUtils.isBlank(catalog)) {
return platform.getDefaultCatalog();
} else {
return catalog;
}
}
protected List<String> getLocalFkRefColumns(Table table) {
List<String> columns = new ArrayList<String>();
for (ForeignKey fk : table.getForeignKeys()) {
for (Reference ref : fk.getReferences()) {
columns.add(ref.getLocalColumnName());
}
}
return columns;
}
protected Table[] getAllDbTables() {
if (allDbTablesCache == null) {
allDbTablesCache = platform.readDatabase(getCatalogToUse(), getSchemaToUse(), null).getTables();
}
return allDbTablesCache;
}
protected Table getDbTable(String tableName) {
if (allDbTablesCache == null) {
allDbTablesCache = platform.readDatabase(getCatalogToUse(), getSchemaToUse(), null).getTables();
}
for (Table table : allDbTablesCache) {
if (table.getName().equalsIgnoreCase(tableName)) {
return table;
}
}
return null;
}
public DmlStatement createInsertDmlStatement(Table table) {
return platform.createDmlStatement(DmlType.INSERT,
table.getCatalog(), table.getSchema(), table.getName(),
table.getPrimaryKeyColumns(), table.getColumns(),
null, textColumnExpression);
}
public DmlStatement createUpdateDmlStatement(Table table) {
return platform.createDmlStatement(DmlType.UPDATE,
table.getCatalog(), table.getSchema(), table.getName(),
table.getPrimaryKeyColumns(), table.getNonPrimaryKeyColumns(),
null, textColumnExpression);
}
public DmlStatement createDeleteDmlStatement(Table table) {
return platform.createDmlStatement(DmlType.DELETE,
table.getCatalog(), table.getSchema(), table.getName(),
table.getPrimaryKeyColumns(), table.getNonPrimaryKeyColumns(),
null, textColumnExpression);
}
private Row createRandomInsertValues(DmlStatement updStatement, Table table) {
Column[] columns = updStatement.getMetaData();
Row row = new Row(columns.length);
for (int i = 0; i < columns.length; i++) {
row.put(columns[i].getName(), generateRandomValueForColumn(columns[i]));
}
return row;
}
private Row createRandomUpdateValues(DmlStatement updStatement, Table table) {
Row row = selectRandomRow(table);
if (row == null) {
log.warn("Unable to update a random record in empty table '"
+ table.getName() + "'.");
return null;
}
Column[] columns = updStatement.getMetaData();
// Get list of local fk reference columns
List<String> localFkRefColumns = getLocalFkRefColumns(table);
for (int i = 0; i < columns.length; i++) {
if (!(columns[i].isPrimaryKey()
|| localFkRefColumns.contains(columns[i].getName()))) {
row.put(columns[i].getName(), generateRandomValueForColumn(columns[i]));
}
}
return row;
}
protected String getTypeValue(String type, String value) {
if (type.equalsIgnoreCase("CHAR")) {
value = "'" + value + "'";
} else if (type.equalsIgnoreCase("VARCHAR")) {
value = "'" + value + "'";
} else if (type.equalsIgnoreCase("LONGVARCHAR")) {
value = "'" + value + "'";
} else if (type.equalsIgnoreCase("DATE")) {
value = "'" + value + "'";
} else if (type.equalsIgnoreCase("TIME")) {
value = "'" + value + "'";
} else if (type.equalsIgnoreCase("TIMESTAMP")) {
value = "'" + value + "'";
} else if (type.equalsIgnoreCase("CLOB")) {
value = "'" + value + "'";
} else if (type.equalsIgnoreCase("BLOB")) {
value = "'" + value + "'";
} else if (type.equalsIgnoreCase("ARRAY")) {
value = "[" + value + "]";
}
return value;
}
public void setPlatform(IDatabasePlatform platform) {
this.platform = platform;
}
public int getRecordCount() {
return inputLength;
}
public void setRecordCount(int recordCount) {
this.inputLength = recordCount;
}
public void setCatalog(String catalog) {
this.catalog = catalog;
}
public void setSchema(String schema) {
this.schema = schema;
}
public boolean isCascading() {
return cascading;
}
public void setCascading(boolean cascading) {
this.cascading = cascading;
}
public String[] getIgnore() {
return ignore;
}
public void setIgnore(String[] ignore) {
this.ignore = ignore;
}
public String[] getPrefixed() {
return prefixed;
}
public void setPrefixed(String[] prefixed) {
this.prefixed = prefixed;
}
public int getInterval() {
return interval;
}
public void setInterval(int interval) {
this.interval = interval;
}
public Random getRand() {
if (rand == null) {
rand = new java.util.Random();
}
return rand;
}
public void setDebug(boolean debug) {
this.debug = debug;
}
public void setVerbose(boolean verbose) {
this.verbose = verbose;
}
public void setDmlWeight(int[] dmlWeight) {
this.dmlWeight = dmlWeight;
}
public void setContinueOnError(boolean continueOnError) {
this.continueOnError = continueOnError;
}
public void setPrint(boolean print) {
this.print = print;
}
public boolean getPrint() {
return print;
}
public int getInsertWeight() {
return dmlWeight[0];
}
public int getUpdateWeight() {
return dmlWeight[1];
}
public int getDeleteWeight() {
return dmlWeight[2];
}
public void setTextColumnExpression(String textColumnExpression) {
this.textColumnExpression = textColumnExpression;
}
public String getTextColumnExpression() {
return textColumnExpression;
}
}