/**
* 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.db.sql;
import static org.apache.commons.lang.StringUtils.isNotBlank;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.codec.binary.Hex;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.NotImplementedException;
import org.jumpmind.db.model.Column;
import org.jumpmind.db.model.Table;
import org.jumpmind.db.model.TypeMap;
import org.jumpmind.db.platform.DatabaseInfo;
import org.jumpmind.db.util.BinaryEncoding;
import org.jumpmind.util.FormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Builds a SQL DML statement
*/
public class DmlStatement {
protected static final Logger log = LoggerFactory.getLogger(DmlStatement.class);
public enum DmlType {
INSERT, UPDATE, DELETE, UPSERT, COUNT, FROM, SELECT, SELECT_ALL, UNKNOWN
};
protected DmlType dmlType;
protected String sql;
protected int[] types;
protected String quote;
protected DatabaseInfo databaseInfo;
protected Column[] keys;
protected Column[] columns;
protected boolean[] nullKeyValues;
protected String textColumnExpression;
public DmlStatement(DmlType type, String catalogName, String schemaName, String tableName,
Column[] keysColumns, Column[] columns, boolean[] nullKeyValues,
DatabaseInfo databaseInfo, boolean useQuotedIdentifiers, String textColumnExpression) {
this.databaseInfo = databaseInfo;
this.columns = columns;
this.textColumnExpression = textColumnExpression;
if (nullKeyValues == null || keysColumns == null
|| nullKeyValues.length != keysColumns.length) {
this.keys = keysColumns;
this.nullKeyValues = keysColumns == null ? null : new boolean[keysColumns.length];
} else {
List<Column> cols = new ArrayList<Column>(keysColumns.length);
// weed out null values
for (int i = 0; i < keysColumns.length; i++) {
if (!nullKeyValues[i]) {
cols.add(keysColumns[i]);
}
}
this.keys = cols.toArray(new Column[cols.size()]);
this.nullKeyValues = nullKeyValues;
}
this.quote = databaseInfo.getDelimiterToken() == null || !useQuotedIdentifiers ? "" :
databaseInfo.getDelimiterToken();
if (type == DmlType.INSERT) {
this.sql = buildInsertSql(Table.getFullyQualifiedTableName(catalogName, schemaName,
tableName, quote, databaseInfo.getCatalogSeparator(), databaseInfo.getSchemaSeparator()), keysColumns, columns);
} else if (type == DmlType.UPDATE) {
this.sql = buildUpdateSql(Table.getFullyQualifiedTableName(catalogName, schemaName,
tableName, quote, databaseInfo.getCatalogSeparator(), databaseInfo.getSchemaSeparator()), keysColumns, columns);
} else if (type == DmlType.DELETE) {
this.sql = buildDeleteSql(Table.getFullyQualifiedTableName(catalogName, schemaName,
tableName, quote, databaseInfo.getCatalogSeparator(), databaseInfo.getSchemaSeparator()), keysColumns);
} else if (type == DmlType.UPSERT) {
this.sql = buildUpsertSql(Table.getFullyQualifiedTableName(catalogName, schemaName,
tableName, quote, databaseInfo.getCatalogSeparator(), databaseInfo.getSchemaSeparator()), keysColumns, columns);
} else if (type == DmlType.COUNT) {
this.sql = buildCountSql(Table.getFullyQualifiedTableName(catalogName, schemaName,
tableName, quote, databaseInfo.getCatalogSeparator(), databaseInfo.getSchemaSeparator()), keysColumns);
} else if (type == DmlType.FROM) {
this.sql = buildFromSql(Table.getFullyQualifiedTableName(catalogName, schemaName,
tableName, quote, databaseInfo.getCatalogSeparator(), databaseInfo.getSchemaSeparator()), keysColumns);
} else if (type == DmlType.SELECT) {
this.sql = buildSelectSql(Table.getFullyQualifiedTableName(catalogName, schemaName,
tableName, quote, databaseInfo.getCatalogSeparator(), databaseInfo.getSchemaSeparator()), keysColumns, columns);
} else if (type == DmlType.SELECT_ALL) {
this.sql = buildSelectSqlAll(Table.getFullyQualifiedTableName(catalogName, schemaName,
tableName, quote, databaseInfo.getCatalogSeparator(), databaseInfo.getSchemaSeparator()), keysColumns, columns);
} else {
throw new NotImplementedException("Unimplemented SQL type: " + type);
}
this.dmlType = type;
this.types = buildTypes(this.keys, columns, databaseInfo.isDateOverridesToTimestamp());
}
protected int[] buildTypes(Column[] keys, Column[] columns, boolean isDateOverrideToTimestamp) {
switch (dmlType) {
case UPDATE:
int[] columnTypes = buildTypes(columns, isDateOverrideToTimestamp);
int[] keyTypes = buildTypes(keys, isDateOverrideToTimestamp);
return ArrayUtils.addAll(columnTypes, keyTypes);
case INSERT:
return buildTypes(columns, isDateOverrideToTimestamp);
case DELETE:
return buildTypes(keys, isDateOverrideToTimestamp);
case COUNT:
return buildTypes(keys, isDateOverrideToTimestamp);
default:
break;
}
return null;
}
protected int getTypeCode(Column column, boolean isDateOverrideToTimestamp) {
int type = column.getMappedTypeCode();
if (type == Types.DATE && isDateOverrideToTimestamp) {
type = Types.TIMESTAMP;
} else if (type == Types.FLOAT || type == Types.DOUBLE || type == Types.REAL) {
type = Types.DECIMAL;
}
return type;
}
protected int[] buildTypes(Column[] columns, boolean isDateOverrideToTimestamp) {
if (columns != null) {
ArrayList<Integer> list = new ArrayList<Integer>(columns.length);
for (int i = 0; i < columns.length; i++) {
if (columns[i] != null) {
list.add(getTypeCode(columns[i], isDateOverrideToTimestamp));
}
}
int[] types = new int[list.size()];
for (int index = 0; index < list.size(); index++) {
types[index] = list.get(index);
}
return types;
} else {
return null;
}
}
protected String buildInsertSql(String tableName, Column[] keys, Column[] columns) {
StringBuilder sql = new StringBuilder("insert into " + tableName + " (");
appendColumns(sql, columns, false);
sql.append(") values (");
appendColumnQuestions(sql, columns);
sql.append(")");
return sql.toString();
}
protected String buildUpsertSql(String tableName, Column[] keyColumns, Column[] columns) {
throw new NotImplementedException("Unimplemented SQL type: " + DmlType.UPSERT);
}
protected String buildUpdateSql(String tableName, Column[] keyColumns, Column[] columns) {
StringBuilder sql = new StringBuilder("update ").append(tableName).append(" set ");
appendColumnsEquals(sql, columns, ", ");
if (keyColumns != null && keyColumns.length > 0) {
sql.append(" where ");
appendColumnsEquals(sql, keyColumns, nullKeyValues, " and ");
}
return sql.toString();
}
protected String buildDeleteSql(String tableName, Column[] keyColumns) {
StringBuilder sql = new StringBuilder("delete from ").append(tableName).append(" where ");
appendColumnsEquals(sql, keyColumns, nullKeyValues, " and ");
return sql.toString();
}
protected String buildFromSql(String tableName, Column[] keyColumns) {
StringBuilder sql = new StringBuilder(" from ").append(tableName).append(" where ");
appendColumnsEquals(sql, keyColumns, nullKeyValues, " and ");
return sql.toString();
}
protected String buildCountSql(String tableName, Column[] keyColumns) {
StringBuilder sql = new StringBuilder("select count(*) from ").append(tableName);
if (keyColumns != null && keyColumns.length > 0) {
sql.append(" where ");
appendColumnsEquals(sql, keyColumns, nullKeyValues, " and ");
}
return sql.toString();
}
protected String buildSelectSql(String tableName, Column[] keyColumns, Column[] columns) {
StringBuilder sql = new StringBuilder("select ");
appendColumns(sql, columns, true);
sql.append(" from ").append(tableName).append(" where ");
appendColumnsEquals(sql, keyColumns, nullKeyValues, " and ");
return sql.toString();
}
protected String buildSelectSqlAll(String tableName, Column[] keyColumns, Column[] columns) {
StringBuilder sql = new StringBuilder("select ");
if (columns != null && columns.length > 0) {
appendColumns(sql, columns, true);
} else {
sql.append("*");
}
sql.append(" from ").append(tableName);
return sql.toString();
}
protected void appendColumnsEquals(StringBuilder sql, Column[] columns, String separator) {
appendColumnsEquals(sql, columns, new boolean[columns.length], separator);
}
protected void appendColumnsEquals(StringBuilder sql, Column[] columns, boolean[] nullColumns,
String separator) {
int existingCount = 0;
if (columns != null) {
for (int i = 0; i < columns.length && i < nullColumns.length; i++) {
if (columns[i] != null) {
if (existingCount++ > 0) {
sql.append(separator);
}
if (!nullColumns[i]) {
appendColumnEquals(sql, columns[i]);
} else {
sql.append(quote).append(columns[i].getName()).append(quote)
.append(" is NULL");
}
}
}
}
}
protected void appendColumnEquals(StringBuilder sql, Column column) {
boolean textType = TypeMap.isTextType(column.getMappedTypeCode());
if (textType && isNotBlank(textColumnExpression)) {
sql.append(quote).append(column.getName()).append(quote).append(" = ").append(textColumnExpression.replace("$(columnName)", "?"));
} else {
sql.append(quote).append(column.getName()).append(quote).append(" = ?");
}
}
protected int appendColumns(StringBuilder sql, Column[] columns, boolean select) {
int existingCount = 0;
if (columns != null) {
for (int i = 0; i < columns.length; i++) {
if (columns[i] != null) {
if (existingCount++ > 0) {
sql.append(", ");
}
appendColumnNameForSql(sql, columns[i], select);
}
}
}
return existingCount;
}
protected void appendColumnNameForSql(StringBuilder sql, Column column, boolean select) {
String columnName = column.getName();
sql.append(quote).append(columnName).append(quote);
}
protected void appendColumnQuestions(StringBuilder sql, Column[] columns) {
if (columns != null) {
for (int i = 0; i < columns.length; i++) {
if (columns[i] != null) {
appendColumnQuestion(sql, columns[i]);
}
}
if (columns.length > 0) {
sql.replace(sql.length() - 1, sql.length(), "");
}
}
}
protected void appendColumnQuestion(StringBuilder sql, Column column) {
boolean textType = TypeMap.isTextType(column.getMappedTypeCode());
if (textType && isNotBlank(textColumnExpression)) {
sql.append(textColumnExpression.replace("$(columnName)", "?")).append(",");
} else {
sql.append("?").append(",");
}
}
public String getColumnsSql(Column[] columns) {
StringBuilder sql = new StringBuilder("select ");
appendColumns(sql, columns, true);
sql.append(getSql());
return sql.toString();
}
public String getSql() {
return sql;
}
public DmlType getDmlType() {
return dmlType;
}
public int[] getTypes() {
return types;
}
public Column[] getColumns() {
return columns;
}
public Column[] getColumnKeyMetaData() {
return (Column[]) ArrayUtils.addAll(columns, keys);
}
public Column[] getMetaData() {
switch (dmlType) {
case UPDATE:
case UPSERT:
return getColumnKeyMetaData();
case INSERT:
return getColumns();
case DELETE:
return getKeys();
default:
break;
}
return null;
}
public Column[] getKeys() {
return keys;
}
@SuppressWarnings("unchecked")
public <T> T[] getValueArray(T[] columnValues, T[] keyValues) {
switch (dmlType) {
case UPDATE:
case UPSERT:
return (T[]) ArrayUtils.addAll(columnValues, keyValues);
case INSERT:
return columnValues;
case DELETE:
return keyValues;
default:
break;
}
return null;
}
public Object[] getValueArray(Map<String, Object> params) {
Object[] args = null;
if (params != null) {
int index = 0;
switch (dmlType) {
case INSERT:
args = new Object[columns.length];
for (Column column : columns) {
args[index++] = params.get(column.getName());
}
break;
case UPDATE:
case UPSERT:
args = new Object[columns.length + keys.length];
for (Column column : columns) {
args[index++] = params.get(column.getName());
}
for (Column column : keys) {
args[index++] = params.get(column.getName());
}
break;
case SELECT:
case DELETE:
args = new Object[keys.length];
for (Column column : keys) {
args[index++] = params.get(column.getName());
}
break;
default:
throw new UnsupportedOperationException();
}
}
return args;
}
public String buildDynamicSql(BinaryEncoding encoding, Row row,
boolean useVariableDates, boolean useJdbcTimestampFormat, Column[] columns) {
final String QUESTION_MARK = "<!QUESTION_MARK!>";
String newSql = sql;
String quote = databaseInfo.getValueQuoteToken();
String binaryQuoteStart = databaseInfo.getBinaryQuoteStart();
String binaryQuoteEnd = databaseInfo.getBinaryQuoteEnd();
String regex = "\\?";
List<Column> columnsToProcess = new ArrayList<Column>();
columnsToProcess.addAll(Arrays.asList(columns));
for (int i = 0; i < columnsToProcess.size(); i++) {
Column column = columnsToProcess.get(i);
String name = column.getName();
int type = column.getMappedTypeCode();
if (row.get(name) != null) {
if (column.isOfTextType()) {
try {
String value = row.getString(name);
value = value.replace("\\", "\\\\");
value = value.replace("$", "\\$");
value = value.replace("'", "''");
value = value.replace("?", QUESTION_MARK);
newSql = newSql.replaceFirst(regex, quote + value + quote);
} catch (RuntimeException ex) {
log.error("Failed to replace ? in {" + sql + "} with " + name + "="
+ row.getString(name));
throw ex;
}
} else if (column.isTimestampWithTimezone()) {
newSql = newSql.replaceFirst(regex, quote + row.getString(name) + quote);
} else if (type == Types.DATE || type == Types.TIMESTAMP || type == Types.TIME) {
Date date = row.getDateTime(name);
if (useVariableDates) {
long diff = date.getTime() - System.currentTimeMillis();
newSql = newSql.replaceFirst(regex, "${curdate" + diff + "}");
} else if (type == Types.TIME) {
newSql = newSql.replaceFirst(regex, (useJdbcTimestampFormat ? "{ts " : "")
+ quote + FormatUtils.TIME_FORMATTER.format(date) + quote
+ (useJdbcTimestampFormat ? "}" : ""));
} else {
newSql = newSql.replaceFirst(regex, (useJdbcTimestampFormat ? "{ts " : "")
+ quote + FormatUtils.TIMESTAMP_FORMATTER.format(date) + quote
+ (useJdbcTimestampFormat ? "}" : ""));
}
} else if (column.isOfBinaryType()) {
byte[] bytes = row.getBytes(name);
if (encoding == BinaryEncoding.NONE) {
newSql = newSql.replaceFirst(regex, quote + row.getString(name));
} else if (encoding == BinaryEncoding.BASE64) {
newSql = newSql.replaceFirst(regex,
quote + new String(Base64.encodeBase64(bytes)) + quote);
} else if (encoding == BinaryEncoding.HEX) {
newSql = newSql.replaceFirst(regex, binaryQuoteStart
+ new String(Hex.encodeHex(bytes)) + binaryQuoteEnd);
}
} else {
newSql = newSql.replaceFirst(regex, row.getString(name));
}
} else {
newSql = newSql.replaceFirst(regex, "null");
}
}
newSql = newSql.replace(QUESTION_MARK, "?");
return newSql + databaseInfo.getSqlCommandDelimiter();
}
public String buildDynamicDeleteSql(BinaryEncoding encoding, Row row,
boolean useVariableDates, boolean useJdbcTimestampFormat) {
return buildDynamicSql(encoding, row, useVariableDates, useJdbcTimestampFormat, keys);
}
public String buildDynamicSql(BinaryEncoding encoding, Row row,
boolean useVariableDates, boolean useJdbcTimestampFormat) {
return buildDynamicSql(encoding, row, useVariableDates, useJdbcTimestampFormat, (Column[]) ArrayUtils.addAll(columns, keys));
}
public boolean isUpsertSupported() {
return false;
}
public String[] getLookupKeyData(Map<String, String> lookupDataMap) {
Column[] lookupColumns = getKeys();
if (lookupColumns != null && lookupColumns.length > 0) {
if (lookupDataMap != null && lookupDataMap.size() > 0) {
String[] keyDataAsArray = new String[lookupColumns.length];
int index = 0;
for (Column keyColumn : lookupColumns) {
keyDataAsArray[index++] = lookupDataMap.get(keyColumn.getName());
}
return keyDataAsArray;
}
}
return null;
}
}