ru.curs.celesta.dbutils.adaptors.ddl.MsSqlDdlGenerator Maven / Gradle / Ivy
The newest version!
package ru.curs.celesta.dbutils.adaptors.ddl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import ru.curs.celesta.CelestaException;
import ru.curs.celesta.DBType;
import ru.curs.celesta.dbutils.adaptors.DBAdaptor;
import ru.curs.celesta.dbutils.adaptors.column.ColumnDefinerFactory;
import ru.curs.celesta.dbutils.meta.DbColumnInfo;
import ru.curs.celesta.dbutils.meta.DbIndexInfo;
import ru.curs.celesta.event.TriggerQuery;
import ru.curs.celesta.event.TriggerType;
import ru.curs.celesta.score.AbstractView;
import ru.curs.celesta.score.BasicTable;
import ru.curs.celesta.score.Column;
import ru.curs.celesta.score.Count;
import ru.curs.celesta.score.DateTimeColumn;
import ru.curs.celesta.score.Expr;
import ru.curs.celesta.score.Grain;
import ru.curs.celesta.score.Index;
import ru.curs.celesta.score.MaterializedView;
import ru.curs.celesta.score.ParameterizedView;
import ru.curs.celesta.score.SQLGenerator;
import ru.curs.celesta.score.Sum;
import ru.curs.celesta.score.TableElement;
import ru.curs.celesta.score.VersionedElement;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;
import static ru.curs.celesta.dbutils.adaptors.constants.CommonConstants.ALTER_TABLE;
import static ru.curs.celesta.dbutils.adaptors.function.CommonFunctions.getFieldList;
/**
* Class for SQL generation of data definition of MsSql.
*/
public final class MsSqlDdlGenerator extends DdlGenerator {
private static final Logger LOGGER = LoggerFactory.getLogger(MsSqlDdlGenerator.class);
public MsSqlDdlGenerator(DBAdaptor dmlAdaptor) {
super(dmlAdaptor);
}
@Override
List dropParameterizedView(String schemaName, String viewName, Connection conn) {
String sql = String.format("DROP FUNCTION %s", tableString(schemaName, viewName));
return Collections.singletonList(sql);
}
@Override
List dropIndex(Grain g, DbIndexInfo dBIndexInfo) {
String sql = String.format(
"DROP INDEX %s ON %s",
dBIndexInfo.getIndexName(),
tableString(g.getName(), dBIndexInfo.getTableName())
);
return Collections.singletonList(sql);
}
@Override
String dropTriggerSql(TriggerQuery query) {
String sql = String.format(
"drop trigger %s",
tableString(query.getSchema(), query.getName())
);
return sql;
}
@Override
DBType getType() {
return DBType.MSSQL;
}
@Override
List updateVersioningTrigger(Connection conn, TableElement t) {
List result = new ArrayList<>();
// First of all, we are about to check if trigger exists
try {
TriggerQuery query = new TriggerQuery()
.withSchema(t.getGrain().getName())
.withTableName(t.getName())
.withName(t.getName() + "_upd");
boolean triggerExists = this.triggerExists(conn, query);
if (t instanceof VersionedElement) {
VersionedElement ve = (VersionedElement) t;
if (ve.isVersioned()) {
if (!triggerExists) {
result.add(createVersioningTrigger(t));
this.rememberTrigger(query);
}
} else {
if (triggerExists) {
// DROP TRIGGER
result.add(dropTrigger(query));
}
}
}
} catch (CelestaException e) {
throw new CelestaException("Could not update version check trigger on %s.%s: %s", t.getGrain().getName(),
t.getName(), e.getMessage());
}
return result;
}
private String createVersioningTrigger(TableElement t) {
StringBuilder sb = new StringBuilder();
sb.append(
String.format("create trigger \"%s\".\"%s_upd\" on \"%s\".\"%s\" for update as begin%n",
t.getGrain().getName(), t.getName(), t.getGrain().getName(), t.getName()));
sb.append(generateTsqlForVersioningTrigger(t));
sb.append("end\n");
// CREATE TRIGGER
LOGGER.trace("{}", sb);
return sb.toString();
}
@Override
public String dropPk(TableElement t, String pkName) {
String sql = String.format("alter table %s.%s drop constraint \"%s\"", t.getGrain().getQuotedName(),
t.getQuotedName(), pkName);
return sql;
}
private String generateTsqlForVersioningTrigger(TableElement t) {
StringBuilder sb = new StringBuilder();
sb.append("IF exists (select * from inserted inner join deleted on \n");
addPKJoin(sb, "inserted", "deleted", t);
sb.append("where inserted.recversion <> deleted.recversion) BEGIN\n");
sb.append(" RAISERROR ('record version check failure', 16, 1);\n");
sb.append("END\n");
sb.append(String.format("update \"%s\".\"%s\" set recversion = recversion + 1 where%n",
t.getGrain().getName(), t.getName()));
sb.append("exists (select * from inserted where \n");
addPKJoin(sb, "inserted", String.format("\"%s\".\"%s\"", t.getGrain().getName(), t.getName()),
t);
sb.append(");\n");
return sb.toString();
}
//TODO:Must be defined in single place
private void addPKJoin(StringBuilder sb, String left, String right, TableElement t) {
boolean needAnd = false;
for (String s : t.getPrimaryKey().keySet()) {
if (needAnd) {
sb.append(" AND ");
}
sb.append(String.format(" %s.\"%s\" = %s.\"%s\"%n", left, s, right, s));
needAnd = true;
}
}
@Override
List updateColumn(Connection conn, Column c, DbColumnInfo actual) {
@SuppressWarnings("unchecked")
final Class> cClass = (Class>) c.getClass();
List result = new ArrayList<>();
String sql;
if (!"".equals(actual.getDefaultValue())) {
sql = String.format(
ALTER_TABLE + tableString(c.getParentTable().getGrain().getName(), c.getParentTable().getName())
+ " drop constraint \"def_%s_%s\"", c.getParentTable().getName(), c.getName());
result.add(sql);
}
String def = ColumnDefinerFactory.getColumnDefiner(getType(), cClass).getMainDefinition(c);
sql = String.format(ALTER_TABLE + tableString(c.getParentTable().getGrain().getName(),
c.getParentTable().getName()) + " alter column %s", def);
result.add(sql);
def = ColumnDefinerFactory.getColumnDefiner(getType(), cClass).getDefaultDefinition(c);
if (!"".equals(def)) {
sql = String.format(ALTER_TABLE + tableString(c.getParentTable().getGrain().getName(),
c.getParentTable().getName()) + " add %s for %s",
def, c.getQuotedName());
result.add(sql);
}
return result;
}
@Override
List createIndex(Index index) {
String fieldList = getFieldList(index.getColumns().keySet());
String sql = String.format("CREATE INDEX %s ON "
+ tableString(index.getTable().getGrain().getName(), index.getTable().getName())
+ " (%s)", index.getQuotedName(), fieldList);
return Collections.singletonList(sql);
}
@Override
public SQLGenerator getViewSQLGenerator() {
return new SQLGenerator() {
@Override
protected String concat() {
return " + ";
}
@Override
protected String preamble(AbstractView view) {
return String.format("create view %s as", viewName(view));
}
@Override
protected String boolLiteral(boolean val) {
return val ? "1" : "0";
}
@Override
protected String paramLiteral(String paramName) {
return "@" + paramName;
}
};
}
@Override
List createParameterizedView(ParameterizedView pv) {
SQLGenerator gen = getViewSQLGenerator();
StringWriter sw = new StringWriter();
PrintWriter bw = new PrintWriter(sw);
try {
pv.selectScript(bw, gen);
} catch (IOException e) {
throw new CelestaException(e);
}
bw.flush();
String inParams = pv.getParameters()
.entrySet().stream()
.map(e ->
"@" + e.getKey() + " "
+ ColumnDefinerFactory.getColumnDefiner(getType(),
CELESTA_TYPES_COLUMN_CLASSES.get(e.getValue().getType().getCelestaType())
).dbFieldType()
).collect(Collectors.joining(", "));
String selectSql = sw.toString();
String sql = String.format(
"CREATE FUNCTION " + tableString(pv.getGrain().getName(), pv.getName()) + "(%s)%n"
+ " RETURNS TABLE%n"
+ " AS%n"
+ " RETURN %s",
inParams, selectSql);
return Collections.singletonList(sql);
}
@Override
Optional dropAutoIncrement(Connection conn, TableElement t) {
String sql = String.format("delete from " + t.getGrain().getScore().getSysSchemaName()
+ ".sequences where grainid = '%s' and tablename = '%s';%n",
t.getGrain().getName(), t.getName());
return Optional.of(sql);
}
@Override
String truncDate(String dateStr) {
return "cast(floor(cast(" + dateStr + " as float)) as datetime)";
}
@Override
public List dropTableTriggersForMaterializedViews(Connection conn, BasicTable t) {
List result = new ArrayList<>();
List mvList = t.getGrain().getElements(MaterializedView.class).values().stream()
.filter(mv -> mv.getRefTable().getTable().equals(t))
.collect(Collectors.toList());
TriggerQuery query = new TriggerQuery()
.withSchema(t.getGrain().getName())
.withTableName(t.getName());
for (MaterializedView mv : mvList) {
String insertTriggerName = mv.getTriggerName(TriggerType.POST_INSERT);
String deleteTriggerName = mv.getTriggerName(TriggerType.POST_DELETE);
query.withName(insertTriggerName);
if (this.triggerExists(conn, query)) {
result.add(dropTrigger(query));
}
query.withName(deleteTriggerName);
if (this.triggerExists(conn, query)) {
result.add(dropTrigger(query));
}
}
if (!mvList.isEmpty()) {
// Set excessive 'rec_version' trigger to zero.
query.withName(t.getName() + "_upd");
if (this.triggerExists(conn, query)) {
result.add(dropTrigger(query));
}
if (t instanceof VersionedElement && ((VersionedElement) t).isVersioned()) {
result.add(createVersioningTrigger(t));
this.rememberTrigger(query);
}
}
return result;
}
@Override
public List createTableTriggersForMaterializedViews(BasicTable t) {
List result = new ArrayList<>();
List mvList = t.getGrain().getElements(MaterializedView.class).values().stream()
.filter(mv -> mv.getRefTable().getTable().equals(t))
.collect(Collectors.toList());
if (mvList.isEmpty()) {
return result;
}
StringBuilder afterUpdateTriggerTsql = new StringBuilder();
if (t instanceof VersionedElement && ((VersionedElement) t).isVersioned()) {
afterUpdateTriggerTsql.append(generateTsqlForVersioningTrigger(t)).append("\n");
}
TriggerQuery query = new TriggerQuery()
.withSchema(t.getGrain().getName())
.withTableName(t.getName());
String fullTableName = tableString(t.getGrain().getName(), t.getName());
for (MaterializedView mv : mvList) {
createTableTriggerForMv(t, result, fullTableName, afterUpdateTriggerTsql, query, mv);
}
StringBuilder sb = new StringBuilder();
final String sqlPrefix =
(t instanceof VersionedElement && ((VersionedElement) t).isVersioned()) ? "alter" : "create";
String updateTriggerName = String.format("%s_upd", t.getName());
sb.append(
String.format("%s trigger \"%s\".\"%s\" on \"%s\".\"%s\" for update as begin%n",
sqlPrefix, t.getGrain().getName(), updateTriggerName, t.getGrain().getName(), t.getName()));
sb.append(afterUpdateTriggerTsql);
sb.append("end\n");
result.add(sb.toString());
this.rememberTrigger(query.withName(updateTriggerName));
return result;
}
private void createTableTriggerForMv(BasicTable t, List result, String fullTableName,
StringBuilder afterUpdateTriggerTsql,
TriggerQuery query, MaterializedView mv) {
String fullMvName = tableString(mv.getGrain().getName(), mv.getName());
String insertTriggerName = mv.getTriggerName(TriggerType.POST_INSERT);
String deleteTriggerName = mv.getTriggerName(TriggerType.POST_DELETE);
String mvColumns = mv.getColumns().keySet().stream()
.filter(alias -> !MaterializedView.SURROGATE_COUNT.equals(alias))
.collect(Collectors.joining(", "))
.concat(", " + MaterializedView.SURROGATE_COUNT);
String aggregateColumns = mv.getColumns().keySet().stream()
.filter(alias -> !MaterializedView.SURROGATE_COUNT.equals(alias))
.map(alias -> "aggregate." + alias)
.collect(Collectors.joining(", "))
.concat(", " + MaterializedView.SURROGATE_COUNT);
String rowConditionTemplate = mv.getColumns().keySet().stream()
.filter(mv::isGroupByColumn)
.map(alias -> "mv." + alias + " = %1$s." + alias + " ")
.collect(Collectors.joining(" AND "));
String rowConditionForExistsTemplate = mv.getColumns().keySet().stream()
.filter(mv::isGroupByColumn)
.map(alias -> {
Column colRef = mv.getColumnRef(alias);
if (DateTimeColumn.CELESTA_TYPE.equals(colRef.getCelestaType())) {
return "mv." + alias + " = cast(floor(cast(%1$s."
+ mv.getColumnRef(alias).getName() + " as float)) as datetime)";
}
return "mv." + alias + " = %1$s." + mv.getColumnRef(alias).getName() + " ";
})
.collect(Collectors.joining(" AND "));
String setStatementTemplate = mv.getAggregateColumns().keySet().stream()
.map(alias -> {
StringBuilder sb = new StringBuilder();
sb.append("mv.").append(alias)
.append(" = mv.").append(alias)
.append(" %1$s aggregate.").append(alias);
return sb.toString();
}).collect(Collectors.joining(", "))
.concat(", mv.").concat(MaterializedView.SURROGATE_COUNT).concat(" = ")
.concat("mv.").concat(MaterializedView.SURROGATE_COUNT).concat(" %1$s aggregate.")
.concat(MaterializedView.SURROGATE_COUNT);
String tableGroupByColumns = mv.getColumns().values().stream()
.filter(v -> mv.isGroupByColumn(v.getName()))
.map(v -> {
if (DateTimeColumn.CELESTA_TYPE.equals(v.getCelestaType())) {
return "cast(floor(cast(\"" + v.getName() + "\" as float)) as datetime)";
}
return "\"" + mv.getColumnRef(v.getName()).getName() + "\"";
}
).collect(Collectors.joining(", "));
String selectPartOfScript = mv.getColumns().keySet().stream()
.filter(alias -> !MaterializedView.SURROGATE_COUNT.equals(alias))
.map(alias -> {
Column colRef = mv.getColumnRef(alias);
Map aggrCols = mv.getAggregateColumns();
if (aggrCols.containsKey(alias)) {
if (colRef == null) {
if (aggrCols.get(alias) instanceof Count) {
return "COUNT(*) as \"" + alias + "\"";
}
return "";
} else if (aggrCols.get(alias) instanceof Sum) {
return "SUM(\"" + colRef.getName() + "\") as \"" + alias + "\"";
} else {
return "";
}
}
if (DateTimeColumn.CELESTA_TYPE.equals(colRef.getCelestaType())) {
return "cast(floor(cast(\"" + colRef.getName() + "\" as float)) as datetime) "
+ "as \"" + alias + "\"";
}
return "\"" + colRef.getName() + "\" as " + "\"" + alias + "\"";
})
.filter(str -> !str.isEmpty())
.collect(Collectors.joining(", "))
.concat(", COUNT(*) AS " + MaterializedView.SURROGATE_COUNT);
StringBuilder insertSqlBuilder = new StringBuilder("MERGE INTO %s WITH (HOLDLOCK) AS mv \n")
.append("USING (SELECT %s FROM inserted GROUP BY %s) AS aggregate ON %s \n")
.append("WHEN MATCHED THEN \n ")
.append("UPDATE SET %s \n")
.append("WHEN NOT MATCHED THEN \n")
.append("INSERT (%s) VALUES (%s); \n");
String insertSql = String.format(insertSqlBuilder.toString(), fullMvName,
selectPartOfScript, tableGroupByColumns, String.format(rowConditionTemplate, "aggregate"),
String.format(setStatementTemplate, "+"), mvColumns, aggregateColumns);
String deleteMatchedCondTemplate = mv.getAggregateColumns().keySet().stream()
.map(alias -> "mv." + alias + " %1$s aggregate." + alias)
.collect(Collectors.joining(" %2$s "));
String existsSql = "EXISTS(SELECT * FROM " + fullTableName + " AS t WHERE "
+ String.format(rowConditionForExistsTemplate, "t") + ")";
StringBuilder deleteSqlBuilder = new StringBuilder("MERGE INTO %s WITH (HOLDLOCK) AS mv \n")
.append("USING (SELECT %s FROM deleted GROUP BY %s) AS aggregate ON %s \n")
.append("WHEN MATCHED AND %s THEN DELETE\n ")
.append("WHEN MATCHED AND (%s) THEN \n")
.append("UPDATE SET %s; \n");
String deleteSql = String.format(deleteSqlBuilder.toString(), fullMvName,
selectPartOfScript, tableGroupByColumns, String.format(rowConditionTemplate, "aggregate"),
String.format(deleteMatchedCondTemplate, "=", "AND").concat(" AND NOT " + existsSql),
String.format(deleteMatchedCondTemplate, "<>", "OR")
.concat(" OR (" + String.format(deleteMatchedCondTemplate, "=", "AND")
.concat(" AND " + existsSql + ")")),
String.format(setStatementTemplate, "-"));
String sql;
//INSERT
sql = String.format("create trigger \"%s\".\"%s\" "
+ "on %s after insert as begin %n"
+ MaterializedView.CHECKSUM_COMMENT_TEMPLATE
+ "%n %s %n END;",
t.getGrain().getName(), insertTriggerName, fullTableName, mv.getChecksum(), insertSql);
LOGGER.trace(sql);
result.add(sql);
this.rememberTrigger(query.withName(insertTriggerName));
//UPDATE
//Инструкции для update-триггера нужно собирать и использовать после прогона главного цикла метода
afterUpdateTriggerTsql.append(String.format("%n%s%n %n%s%n", deleteSql, insertSql));
//DELETE
sql = String.format("create trigger \"%s\".\"%s\" on %s after delete as begin %n %s %n END;",
t.getGrain().getName(), deleteTriggerName, fullTableName, deleteSql);
LOGGER.trace(sql);
result.add(sql);
this.rememberTrigger(query.withName(deleteTriggerName));
}
}