/**
* Generate "CREATE" SQL statement for the view.
*
* @param orReplace if true, then include the OR REPLACE clause
* @param force if true, then include the FORCE clause
* @return the SQL statement
*/
public String getCreateSQL(boolean orReplace, boolean force) {
StatementBuilder buff = new StatementBuilder("CREATE ");
if (orReplace) {
buff.append("OR REPLACE ");
}
if (force) {
buff.append("FORCE ");
}
buff.append("VIEW ");
buff.append(getSQL());
if (comment != null) {
buff.append(" COMMENT ").append(StringUtils.quoteStringSQL(comment));
}
if (columns.length > 0) {
buff.append('(');
for (Column c : columns) {
buff.appendExceptFirst(", ");
buff.append(c.getSQL());
}
buff.append(')');
} else if (columnNames != null) {
buff.append('(');
for (String n : columnNames) {
buff.appendExceptFirst(", ");
buff.append(n);
}
buff.append(')');
}
return buff.append(" AS\n").append(querySQL).toString();
}
/**
* Get the list of columns as a string.
*
* @return the list of columns
*/
private String getColumnListSQL() {
StatementBuilder buff = new StatementBuilder();
for (IndexColumn c : indexColumns) {
buff.appendExceptFirst(", ");
buff.append(c.getSQL());
}
return buff.toString();
}
public String getSQL() {
StatementBuilder buff = new StatementBuilder("(");
buff.append(this.left.getSQL()).append(" IN(");
for (Expression e : this.valueList) {
buff.appendExceptFirst(", ");
buff.append(e.getSQL());
}
return buff.append("))").toString();
}
@Override
public String getSQL() {
StatementBuilder buff = new StatementBuilder();
buff.append(Parser.quoteIdentifier(userAggregate.getName())).append('(');
for (Expression e : args) {
buff.appendExceptFirst(", ");
buff.append(e.getSQL());
}
return buff.append(')').toString();
}
@Override
public String getPlanSQL() {
StatementBuilder buff = new StatementBuilder("INSERT INTO ");
buff.append(table.getSQL()).append('(');
for (Column c : columns) {
buff.appendExceptFirst(", ");
buff.append(c.getSQL());
}
buff.append(")\n");
if (insertFromSelect) {
buff.append("DIRECT ");
}
if (sortedInsertMode) {
buff.append("SORTED ");
}
if (list.size() > 0) {
buff.append("VALUES ");
int row = 0;
if (list.size() > 1) {
buff.append('\n');
}
for (Expression[] expr : list) {
if (row++ > 0) {
buff.append(",\n");
}
buff.append('(');
buff.resetCount();
for (Expression e : expr) {
buff.appendExceptFirst(", ");
if (e == null) {
buff.append("DEFAULT");
} else {
buff.append(e.getSQL());
}
}
buff.append(')');
}
} else {
buff.append(query.getPlanSQL());
}
return buff.toString();
}
/**
* Convert an int array to the Java source code that represents this array. Null will be converted
* to 'null'.
*
* @param array the int array
* @return the Java source code (including new int[]{})
*/
public static String quoteJavaIntArray(int[] array) {
if (array == null) {
return "null";
}
StatementBuilder buff = new StatementBuilder("new int[]{");
for (int a : array) {
buff.appendExceptFirst(", ");
buff.append(a);
}
return buff.append('}').toString();
}
@Override
public String getSQL() {
StatementBuilder buff = new StatementBuilder("(");
for (Expression e : list) {
buff.appendExceptFirst(", ");
buff.append(e.getSQL());
}
if (list.length == 1) {
buff.append(',');
}
return buff.append(')').toString();
}
private String getMethodSignature(Method m) {
StatementBuilder buff = new StatementBuilder(m.getName());
buff.append('(');
for (Class<?> p : m.getParameterTypes()) {
buff.appendExceptFirst(", ");
if (p.isArray()) {
buff.append(p.getComponentType().getName()).append("[]");
} else {
buff.append(p.getName());
}
}
return buff.append(')').toString();
}
void merge(Db db, Object obj) {
if (primaryKeyColumnNames == null || primaryKeyColumnNames.size() == 0) {
throw new IllegalStateException(
"No primary key columns defined "
+ "for table "
+ obj.getClass()
+ " - no update possible");
}
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff = new StatementBuilder("MERGE INTO ");
buff.append(db.getDialect().getTableName(schemaName, tableName)).append(" (");
buff.resetCount();
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append(field.columnName);
}
buff.append(") KEY(");
buff.resetCount();
for (FieldDefinition field : fields) {
if (field.isPrimaryKey) {
buff.appendExceptFirst(", ");
buff.append(field.columnName);
}
}
buff.append(") ");
buff.resetCount();
buff.append("VALUES (");
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append('?');
Object value = getValue(obj, field);
stat.addParameter(value);
}
buff.append(')');
stat.setSQL(buff.toString());
StatementLogger.merge(stat.getSQL());
stat.executeUpdate();
}
public String getSQL() {
StatementBuilder buff = new StatementBuilder();
// TODO always append the schema once FUNCTIONS_IN_SCHEMA is enabled
if (functionAlias.getDatabase().getSettings().functionsInSchema
|| !functionAlias.getSchema().getName().equals(Constants.SCHEMA_MAIN)) {
buff.append(Parser.quoteIdentifier(functionAlias.getSchema().getName())).append('.');
}
buff.append(Parser.quoteIdentifier(functionAlias.getName())).append('(');
for (Expression e : args) {
buff.appendExceptFirst(", ");
buff.append(e.getSQL());
}
return buff.append(')').toString();
}
@Override
public String toString() {
StatementBuilder buff = new StatementBuilder("( /* key:");
buff.append(getKey());
if (version != 0) {
buff.append(" v:" + version);
}
buff.append(" */ ");
for (Value v : data) {
buff.appendExceptFirst(", ");
buff.append(v == null ? "null" : v.getTraceSQL());
}
return buff.append(')').toString();
}
long insert(Db db, Object obj, boolean returnKey) {
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff = new StatementBuilder("INSERT INTO ");
buff.append(db.getDialect().getTableName(schemaName, tableName)).append('(');
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append(field.columnName);
}
buff.append(") VALUES(");
buff.resetCount();
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append('?');
Object value = getValue(obj, field);
stat.addParameter(value);
}
buff.append(')');
stat.setSQL(buff.toString());
StatementLogger.insert(stat.getSQL());
if (returnKey) {
return stat.executeInsert();
}
return stat.executeUpdate();
}
/**
* Combine an array of strings to one array using the given separator character. A backslash and
* the separator character and escaped using a backslash.
*
* @param list the string array
* @param separatorChar the separator character
* @return the combined string
*/
public static String arrayCombine(String[] list, char separatorChar) {
StatementBuilder buff = new StatementBuilder();
for (String s : list) {
buff.appendExceptFirst(String.valueOf(separatorChar));
if (s == null) {
s = "";
}
for (int j = 0, length = s.length(); j < length; j++) {
char c = s.charAt(j);
if (c == '\\' || c == separatorChar) {
buff.append('\\');
}
buff.append(c);
}
}
return buff.toString();
}
private String getQuery(Object[] row) throws SQLException {
StatementBuilder buff = new StatementBuilder();
if (schema != null) {
buff.append(StringUtils.quoteIdentifier(schema)).append('.');
}
buff.append(StringUtils.quoteIdentifier(table)).append(" WHERE ");
for (int columnIndex : keys) {
buff.appendExceptFirst(" AND ");
buff.append(StringUtils.quoteIdentifier(columns[columnIndex]));
Object o = row[columnIndex];
if (o == null) {
buff.append(" IS NULL");
} else {
buff.append('=').append(FullText.quoteSQL(o, columnTypes[columnIndex]));
}
}
return buff.toString();
}
void update(Db db, Object obj) {
if (primaryKeyColumnNames == null || primaryKeyColumnNames.size() == 0) {
throw new IllegalStateException(
"No primary key columns defined "
+ "for table "
+ obj.getClass()
+ " - no update possible");
}
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff = new StatementBuilder("UPDATE ");
buff.append(db.getDialect().getTableName(schemaName, tableName)).append(" SET ");
buff.resetCount();
for (FieldDefinition field : fields) {
if (!field.isPrimaryKey) {
buff.appendExceptFirst(", ");
buff.append(field.columnName);
buff.append(" = ?");
Object value = getValue(obj, field);
stat.addParameter(value);
}
}
Object alias = ClassUtils.newObject(obj.getClass());
Query<Object> query = Query.from(db, alias);
boolean firstCondition = true;
for (FieldDefinition field : fields) {
if (field.isPrimaryKey) {
Object aliasValue = field.getValue(alias);
Object value = field.getValue(obj);
if (!firstCondition) {
query.addConditionToken(ConditionAndOr.AND);
}
firstCondition = false;
query.addConditionToken(new Condition<Object>(aliasValue, value, CompareType.EQUAL));
}
}
stat.setSQL(buff.toString());
query.appendWhere(stat);
StatementLogger.update(stat.getSQL());
stat.executeUpdate();
}
/**
* Add a row to the index.
*
* @param row the row
* @param commitIndex whether to commit the changes to the Lucene index
*/
protected void insert(Object[] row, boolean commitIndex) throws SQLException {
/*## LUCENE2 ##
String query = getQuery(row);
Document doc = new Document();
doc.add(new Field(LUCENE_FIELD_QUERY, query,
Field.Store.YES, Field.Index.UN_TOKENIZED));
long time = System.currentTimeMillis();
doc.add(new Field(LUCENE_FIELD_MODIFIED,
DateTools.timeToString(time, DateTools.Resolution.SECOND),
Field.Store.YES, Field.Index.UN_TOKENIZED));
StatementBuilder buff = new StatementBuilder();
for (int index : indexColumns) {
String columnName = columns[index];
String data = asString(row[index], columnTypes[index]);
// column names that start with _ must be escaped to avoid conflicts
// with internal field names (_DATA, _QUERY, _modified)
if (columnName.startsWith(LUCENE_FIELD_COLUMN_PREFIX)) {
columnName = LUCENE_FIELD_COLUMN_PREFIX + columnName;
}
doc.add(new Field(columnName, data,
Field.Store.NO, Field.Index.TOKENIZED));
buff.appendExceptFirst(" ");
buff.append(data);
}
Field.Store storeText = STORE_DOCUMENT_TEXT_IN_INDEX ?
Field.Store.YES : Field.Store.NO;
doc.add(new Field(LUCENE_FIELD_DATA, buff.toString(), storeText,
Field.Index.TOKENIZED));
try {
indexAccess.modifier.addDocument(doc);
} catch (IOException e) {
throw convertException(e);
}
//*/
// ## LUCENE3 ##
String query = getQuery(row);
Document doc = new Document();
doc.add(new Field(LUCENE_FIELD_QUERY, query, Field.Store.YES, Field.Index.NOT_ANALYZED));
long time = System.currentTimeMillis();
doc.add(
new Field(
LUCENE_FIELD_MODIFIED,
DateTools.timeToString(time, DateTools.Resolution.SECOND),
Field.Store.YES,
Field.Index.NOT_ANALYZED));
StatementBuilder buff = new StatementBuilder();
for (int index : indexColumns) {
String columnName = columns[index];
String data = asString(row[index], columnTypes[index]);
// column names that start with _
// must be escaped to avoid conflicts
// with internal field names (_DATA, _QUERY, _modified)
if (columnName.startsWith(LUCENE_FIELD_COLUMN_PREFIX)) {
columnName = LUCENE_FIELD_COLUMN_PREFIX + columnName;
}
doc.add(new Field(columnName, data, Field.Store.NO, Field.Index.ANALYZED));
buff.appendExceptFirst(" ");
buff.append(data);
}
Field.Store storeText = STORE_DOCUMENT_TEXT_IN_INDEX ? Field.Store.YES : Field.Store.NO;
doc.add(new Field(LUCENE_FIELD_DATA, buff.toString(), storeText, Field.Index.ANALYZED));
try {
indexAccess.writer.addDocument(doc);
if (commitIndex) {
commitIndex();
}
} catch (IOException e) {
throw convertException(e);
}
// */
}
public String getPlanSQL() {
// can not use the field sqlStatement because the parameter
// indexes may be incorrect: ? may be in fact ?2 for a subquery
// but indexes may be set manually as well
Expression[] exprList = expressions.toArray(new Expression[expressions.size()]);
StatementBuilder buff = new StatementBuilder("SELECT");
if (distinct) {
buff.append(" DISTINCT");
}
for (int i = 0; i < visibleColumnCount; i++) {
buff.appendExceptFirst(",");
buff.append('\n');
buff.append(StringUtils.indent(exprList[i].getSQL(), 4, false));
}
buff.append("\nFROM ");
TableFilter filter = topTableFilter;
if (filter != null) {
buff.resetCount();
int i = 0;
do {
buff.appendExceptFirst("\n");
buff.append(filter.getPlanSQL(i++ > 0));
filter = filter.getJoin();
} while (filter != null);
} else {
buff.resetCount();
int i = 0;
for (TableFilter f : topFilters) {
do {
buff.appendExceptFirst("\n");
buff.append(f.getPlanSQL(i++ > 0));
f = f.getJoin();
} while (f != null);
}
}
if (condition != null) {
buff.append("\nWHERE ").append(StringUtils.unEnclose(condition.getSQL()));
}
if (groupIndex != null) {
buff.append("\nGROUP BY ");
buff.resetCount();
for (int gi : groupIndex) {
Expression g = exprList[gi];
g = g.getNonAliasExpression();
buff.appendExceptFirst(", ");
buff.append(StringUtils.unEnclose(g.getSQL()));
}
}
if (group != null) {
buff.append("\nGROUP BY ");
buff.resetCount();
for (Expression g : group) {
buff.appendExceptFirst(", ");
buff.append(StringUtils.unEnclose(g.getSQL()));
}
}
if (having != null) {
// could be set in addGlobalCondition
// in this case the query is not run directly, just getPlanSQL is
// called
Expression h = having;
buff.append("\nHAVING ").append(StringUtils.unEnclose(h.getSQL()));
} else if (havingIndex >= 0) {
Expression h = exprList[havingIndex];
buff.append("\nHAVING ").append(StringUtils.unEnclose(h.getSQL()));
}
if (sort != null) {
buff.append("\nORDER BY ").append(sort.getSQL(exprList, visibleColumnCount));
}
if (orderList != null) {
buff.append("\nORDER BY ");
buff.resetCount();
for (SelectOrderBy o : orderList) {
buff.appendExceptFirst(", ");
buff.append(StringUtils.unEnclose(o.getSQL()));
}
}
if (limitExpr != null) {
buff.append("\nLIMIT ").append(StringUtils.unEnclose(limitExpr.getSQL()));
if (offsetExpr != null) {
buff.append(" OFFSET ").append(StringUtils.unEnclose(offsetExpr.getSQL()));
}
}
if (sampleSize != 0) {
buff.append("\nSAMPLE_SIZE ").append(sampleSize);
}
if (isForUpdate) {
buff.append("\nFOR UPDATE");
}
if (isQuickAggregateQuery) {
buff.append("\n/* direct lookup */");
}
if (isDistinctQuery) {
buff.append("\n/* distinct */");
}
if (sortUsingIndex) {
buff.append("\n/* index sorted */");
}
if (isGroupQuery) {
if (isGroupSortedQuery) {
buff.append("\n/* group sorted */");
}
}
// buff.append("\n/* cost: " + cost + " */");
return buff.toString();
}
TableDefinition<T> createTableIfRequired(Db db) {
if (!createTableIfRequired) {
// skip table and index creation
// but still check for upgrades
db.upgradeTable(this);
return this;
}
SQLDialect dialect = db.getDialect();
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff;
if (memoryTable && dialect.supportsMemoryTables()) {
buff = new StatementBuilder("CREATE MEMORY TABLE IF NOT EXISTS ");
} else {
buff = new StatementBuilder("CREATE TABLE IF NOT EXISTS ");
}
buff.append(dialect.getTableName(schemaName, tableName)).append('(');
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append(field.columnName).append(' ').append(field.dataType);
if (field.maxLength > 0) {
buff.append('(').append(field.maxLength).append(')');
}
if (field.isAutoIncrement) {
buff.append(" AUTO_INCREMENT");
}
if (!field.allowNull) {
buff.append(" NOT NULL");
}
// default values
if (!field.isAutoIncrement && !field.isPrimaryKey) {
String dv = field.defaultValue;
if (!StringUtils.isNullOrEmpty(dv)) {
if (ModelUtils.isProperlyFormattedDefaultValue(dv)
&& ModelUtils.isValidDefaultValue(field.field.getType(), dv)) {
buff.append(" DEFAULT " + dv);
}
}
}
}
// primary key
if (primaryKeyColumnNames != null && primaryKeyColumnNames.size() > 0) {
buff.append(", PRIMARY KEY(");
buff.resetCount();
for (String n : primaryKeyColumnNames) {
buff.appendExceptFirst(", ");
buff.append(n);
}
buff.append(')');
}
buff.append(')');
stat.setSQL(buff.toString());
StatementLogger.create(stat.getSQL());
stat.executeUpdate();
// create indexes
for (IndexDefinition index : indexes) {
String sql = db.getDialect().getCreateIndex(schemaName, tableName, index);
stat.setSQL(sql);
StatementLogger.create(stat.getSQL());
stat.executeUpdate();
}
// tables are created using IF NOT EXISTS
// but we may still need to upgrade
db.upgradeTable(this);
return this;
}