public static void CreateTemporaryTable(this IQuery query, ObjectName tableName, params SqlTableColumn[] columns)
{
var statement = new CreateTableStatement(tableName, columns);
statement.Temporary = true;
Result(query.ExecuteStatement(statement));
}
public override void ExplicitVisit(CreateTableStatement table)
{
if (IsSupportedForCurrentType(table.GetType()))
{
Name = table.SchemaObjectName;
}
}
public override void ExplicitVisit(CreateTableStatement node)
{
PgTableDefinitionVisitor visitor = new PgTableDefinitionVisitor(_buffer);
node.Accept(visitor);
_database.Tables.Add(visitor.Table);
}
public ReverseQuery(IFactory factory, IFilter filter, Expression <Func <TParent, object> > property)
{
var parent = new EntityInfo(typeof(TParent));
var valueType = typeof(TValue);
var valueInfo = new ValueInfo(parent, property.ToPropertyInfo(), valueType);
//this.logger = logger;
this.factory = factory;
this.valueBuilder = new ComplexCommandBuilder(valueInfo.Name, valueInfo.Type);
rootName = "T_" + Guid.NewGuid().ToString().Replace("-", string.Empty);
var selectStatement = new SelectStatement(valueInfo, filter, parent, rootIdAlias);
var createTableStatement = new CreateTableStatement(rootName, selectStatement);
valueBuilder.AddStatement(createTableStatement);
foreach (var parameter in filter.Parameters)
{
valueBuilder.AddParameter(parameter);
}
builder = new ComplexCommandBuilder(parent.Name, parent.Type);
builder.AddStatement(new SelectStatement(parent, rootName, rootIdAlias));
AddChildStatements(builder, parent);
}
protected override object InternalVisit(CreateTableStatement node)
{
var table = Visit <Table>(node.Definition);
table.TableName = Visit <string>(node.SchemaObjectName);
if (Database.ContainsTable(table.TableName))
{
var msg = string.Format("There is already an object named '{0}' in the database", table.TableName);
throw new DuplicateNameException(msg);
}
Database.AddTable(table);
try
{
foreach (var cd in node.Definition.ColumnDefinitions)
{
foreach (var constraint in cd.Constraints)
{
var column = table.GetColumn(cd.ColumnIdentifier.Value);
Visit <Action <Table, Column> >(constraint)?.Invoke(table, column);
}
}
foreach (var constraint in node.Definition.TableConstraints)
{
Visit <Action <Table, Column> >(constraint)?.Invoke(table, null);
}
}
catch
{
Database.RemoveTable(table);
throw;
}
return(new RecordTable(table.TableName, table.Columns, table.Rows));
}
public static void CreateTable(this IQuery query, ObjectName tableName, bool ifNotExists, params SqlTableColumn[] columns)
{
var statement = new CreateTableStatement(tableName, columns);
statement.IfNotExists = ifNotExists;
query.ExecuteStatement(statement);
}
public override void Visit(CreateTableStatement node)
{
sw.WriteLine("<table>");
Visit(node.SchemaObjectName);
foreach (ColumnDefinition coldef in node.Definition.ColumnDefinitions)
Visit(coldef);
sw.WriteLine("</table>");
}
protected override object InternalVisit(CreateTableStatement node)
{
var interpreter = new SQLCreateInterpreter(Database);
var table = interpreter.Visit <RecordTable>(node);
//TODO: we should have result subtypes, SQL returns "Command(s) completed successfully." on this case, and it does not return a reference or data of the created table
return(new SQLExecutionResult(0, null));
}
public void Visit(CreateTableStatement node, IList<TSqlParserToken> TKs)
{
sw.WriteLine("<table>");
Visit(node.SchemaObjectName);
foreach (ColumnDefinition coldef in node.Definition.ColumnDefinitions)
Visit(coldef, TKs);
sw.WriteLine("</table>");
}
public static CreateTableStatement CreateTable(SchemaObjectName tableName, TableDefinition definition)
{
var fragment = new CreateTableStatement();
fragment.SchemaObjectName = tableName;
fragment.Definition = definition;
return(fragment);
}
public override void ExplicitVisit(CreateTableStatement node)
{
_buffer.Append("create table ");
node.SchemaObjectName.Accept(this);
_table.Name = PgTranslatorVisitor.GetTableName(node.SchemaObjectName);
_buffer.AppendLine(" (");
bool needComma = false;
foreach (ColumnDefinition columnDefinition in node.Definition.ColumnDefinitions)
{
if (needComma)
{
_buffer.AppendLine(",");
}
_column = new PgTableColumn();
if (columnDefinition.IdentityOptions != null)
{
_column.IsIdentity = true;
}
columnDefinition.Accept(this);
_table.Columns.Add(_column);
_column = null;
needComma = true;
}
foreach (ConstraintDefinition constraint in node.Definition.TableConstraints)
{
if (needComma)
{
_buffer.AppendLine(",");
}
_buffer.Append("\t");
if (constraint is UniqueConstraintDefinition unique)
{
unique.Accept(new PgExpressionVisitor(_buffer));
}
else if (constraint is ForeignKeyConstraintDefinition foreignKey)
{
foreignKey.Accept(new PgExpressionVisitor(_buffer));
}
else if (constraint is CheckConstraintDefinition check)
{
check.Accept(new PgExpressionVisitor(_buffer));
}
else
{
throw new InvalidOperationException();
}
}
_buffer.AppendLine();
_buffer.AppendLine(");");
_buffer.AppendLine();
}
// end delete functions
// create table functions
public override void EnterCreate_table([NotNull] TSqlParser.Create_tableContext context)
{
base.EnterCreate_table(context);
var createTable = new CreateTableStatement();
createTable.RawStatement = context.GetText();
_ddlStatement = createTable;
Debug.WriteLine(context.GetText());
}
public override void Visit(CreateTableStatement createTableStatement)
{
foreach (var columnDefinition in createTableStatement.Definition.ColumnDefinitions)
{
var value = columnDefinition.ColumnIdentifier.Value;
Analyze(value, columnDefinition.DataType as SqlDataTypeReference);
}
base.Visit(createTableStatement);
}
private static SqlStatement MakeCreateTable(string tableName, IEnumerable <SqlTableColumn> columns, bool ifNotExists,
bool temporary)
{
var objTableName = ObjectName.Parse(tableName);
var tree = new CreateTableStatement(objTableName, columns.ToList());
tree.IfNotExists = ifNotExists;
tree.Temporary = temporary;
return(tree);
}
public override void Visit(CreateTableStatement node)
{
var childCompressionVisitor = new childCompressionVisitor();
node.AcceptChildren(childCompressionVisitor);
if (!childCompressionVisitor.compressionOptionExists)
{
ErrorCallback(RULE_NAME, RULE_TEXT, node.StartLine, node.StartColumn);
}
}
public string Convert(CTable table)
{
string[] parts = { table.Schema.SchemaName, table.TableName };
var createTable = new CreateTableStatement();
///set schema and table name
createTable.SchemaObjectName = new SchemaObjectName();
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = table.Schema.SchemaName
});
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = table.TableName
});
//add columns
createTable.Definition = new TableDefinition();
foreach (var col in table.Column)
{
var dataType = new SqlDataTypeReference {
SqlDataTypeOption = GetSqlDataTypeOption(col.ColumnType)
};
if (col.ColumnLength > 0)
{
dataType.Parameters.Add(new IntegerLiteral {
Value = col.ColumnLength.ToString()
});
}
var column = new ColumnDefinition
{
ColumnIdentifier = new Identifier {
Value = col.ColumnName
},
DataType = dataType
};
createTable.Definition.ColumnDefinitions.Add(column);
}
//generate DDL
var script = new TSqlScript();
var batch = new TSqlBatch();
script.Batches.Add(batch);
batch.Statements.Add(createTable);
var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
dacpacModel.AddObjects(script);
var existing = dacpacModel.GetObject(Table.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
return(existing.GetScript());
}
public override void ExplicitVisit(CreateTableStatement node)
{
foreach (var columnDefinition in node.Definition.ColumnDefinitions)
{
if (columnDefinition.IdentityOptions == null &&
columnDefinition.Constraints.Any(constraint => constraint is UniqueConstraintDefinition && ((UniqueConstraintDefinition)constraint).IsPrimaryKey))
{
this.CreateTableStatements.Add(node);
}
}
}
public void CreateStatementTest()
{
var createTableStatement = new CreateTableStatement
{
TableName = "dummyTable",
ColumnStatementCollection = CreateStatementCollectionMock("dummyColumnDefinition").Object
};
string output = createTableStatement.CreateStatement();
Assert.AreEqual(output, "CREATE TABLE dummyTable (dummyColumnDefinition);");
}
public static EngineResult Evaluate(CreateTableStatement createTable, Scope scope)
{
var databaseName = createTable.SchemaObjectName.DatabaseIdentifier?.Value ?? scope.Env.DefaultDatabase;
var schemaName = createTable.SchemaObjectName.SchemaIdentifier?.Value ?? scope.Env.DefaultSchema;
var tableName = createTable.SchemaObjectName.BaseIdentifier.Value;
var table = new Table {
Name = tableName
};
foreach (var c in createTable.Definition.ColumnDefinitions)
{
table.Columns.Add(new Column
{
Name = new[]
{
databaseName,
schemaName,
tableName,
c.ColumnIdentifier.Value
}.Where(x => x != null).ToArray(),
Type = TranslateDbType(c.DataType)
});
if (c.DefaultConstraint != null)
{
var env = scope.Env;
table.Defaults[c.DefaultConstraint.Column?.Value ?? c.ColumnIdentifier.Value] =
() => Evaluate(c.DefaultConstraint.Expression, NullArgument.It, new Scope(env));
}
if (c.IdentityOptions != null)
{
table.IdentityColumnName = c.ColumnIdentifier.Value;
table.IdentityValue =
c.IdentityOptions.IdentitySeed != null
? Evaluate <int>(c.IdentityOptions.IdentitySeed, NullArgument.It, scope)
: 1;
table.IdentityIncrement =
c.IdentityOptions.IdentityIncrement != null
? Evaluate <int>(c.IdentityOptions.IdentityIncrement, NullArgument.It, scope)
: 1;
}
}
var database = scope.Env.Engine.Databases.GetOrAdd(databaseName, Database.Named);
var schema = database.Schemas.GetOrAdd(schemaName, Schema.Named);
schema.Tables.Declare(table);
return(null);
}
private static bool NamePrimaryKey(CreateTableStatement statement)
{
var columnWithPrimaryKey = statement.Definition.ColumnDefinitions.FirstOrDefault(c => c.Constraints.Any(p => p is UniqueConstraintDefinition));
if (columnWithPrimaryKey == null)
{
return(false);
}
var originalConstraint = columnWithPrimaryKey.Constraints.FirstOrDefault(c => c as UniqueConstraintDefinition != null && ((UniqueConstraintDefinition)c).IsPrimaryKey) as UniqueConstraintDefinition;
if (originalConstraint == null)
{
return(false);
}
var newConstraint = new UniqueConstraintDefinition();
newConstraint.Clustered = originalConstraint.Clustered;
newConstraint.IsPrimaryKey = true;
foreach (var o in originalConstraint.IndexOptions)
{
newConstraint.IndexOptions.Add(o);
}
newConstraint.IndexType = originalConstraint.IndexType;
var referencedColumn = new ColumnWithSortOrder();
referencedColumn.Column = new ColumnReferenceExpression();
referencedColumn.Column.MultiPartIdentifier = new SchemaObjectName();
referencedColumn.Column.MultiPartIdentifier.Identifiers.Add(columnWithPrimaryKey.ColumnIdentifier);
newConstraint.Columns.Add(referencedColumn);
newConstraint.ConstraintIdentifier = new Identifier();
newConstraint.ConstraintIdentifier.Value =
ReplaceTemplateValues(SavedSettings.Get().PrimaryKeyName, statement.SchemaObjectName.BaseIdentifier.Value, referencedColumn.Column.MultiPartIdentifier.Identifiers.Last().Value);
newConstraint.ConstraintIdentifier.QuoteType = QuoteType.SquareBracket;
statement.Definition.TableConstraints.Add(newConstraint);
columnWithPrimaryKey.Constraints.Remove(originalConstraint);
return(true);
}
public override void Visit(CreateTableStatement node)
{
string tableName = node.SchemaObjectName.BaseIdentifier.Value;
string schemaQualifiedTableName = $"{node.SchemaObjectName.SchemaIdentifier.Value}.{tableName}";
string className = GetClassNameForTable(tableName);
ClassDeclarationSyntax classDeclarationSyntax =
CreateSkeletalClass(className, schemaQualifiedTableName)
.AddMembers(node.Definition.ColumnDefinitions.Select(CreatePropertyForTableColumn).ToArray());
FieldDeclarationSyntax field = CreateStaticFieldForClass(className, tableName);
MembersToAdd.Add(field.AddSortingKey(this, tableName));
MembersToAdd.Add(classDeclarationSyntax.AddSortingKey(this, tableName));
base.Visit(node);
}
public override void Visit(CreateTableStatement node)
{
// only apply rule to temp tables
if (!node.SchemaObjectName.BaseIdentifier.Value.Contains("#"))
{
return;
}
var constraintVisitor = new ConstraintVisitor();
node.AcceptChildren(constraintVisitor);
if (constraintVisitor.NamedConstraintExists)
{
errorCallback(RULE_NAME, RULE_TEXT, node.StartLine, node.StartColumn);
}
}
private string ModifyScript(CreateTableStatement statement, string originalScript, string modifiedScript)
{
var oldScriptBlock = originalScript.Substring(statement.StartOffset, statement.FragmentLength);
var comments = GetComments(oldScriptBlock);
var generator = new Sql120ScriptGenerator(SavedSettings.Get().GeneratorOptions);
string newScriptBlock;
generator.GenerateScript(statement, out newScriptBlock);
if (string.IsNullOrEmpty(comments))
modifiedScript = modifiedScript.Replace(oldScriptBlock, newScriptBlock);
else
modifiedScript = modifiedScript.Replace(oldScriptBlock, newScriptBlock + "\r\n--These comments were saved after refactoring this table...\r\n" + comments);
return modifiedScript;
}
void ParseSource()
{
using (StringReader sr = new StringReader(MSource.Text))
{
IList <ParseError> errors;
TSql150Parser parser = new TSql150Parser(true);
TSqlScript script = parser.Parse(sr, out errors) as TSqlScript;
CreateTableStatement cts = null;
foreach (TSqlBatch b in script.Batches)
{
foreach (TSqlStatement statement in b.Statements)
{
if (!(statement is CreateTableStatement))
{
continue;
}
cts = (CreateTableStatement)statement;
break;
}
}
foreach (var i in items)
{
(i.Item1.Panel1.Controls.Find("textbox", true)[0] as TextBox).Text = "";
if (cts != null)
{
//string s = i.Item2.Translate(cts.Definition, i.Item1);
string s = i.Item2.TranslateExt(cts, i.Item1);
(i.Item1.Panel1.Controls.Find("textbox", true)[0] as TextBox).Text = s;
}
else
{
if ((errors != null) && (errors.Count > 0))
{
(i.Item1.Panel1.Controls.Find("textbox", true)[0] as TextBox).Text = errors[0].Message;
}
}
}
}
}
private static TestTable LoadTableFromDDL(string ddl)
{
TSqlFragment sqlF = ScriptDomFacade.Parse(ddl);
CreateTableStatement stmt_CreateTable = (CreateTableStatement)((TSqlScript)sqlF).Batches[0].Statements[0];
string schemaName = stmt_CreateTable.SchemaObjectName.SchemaIdentifier.Dequote();
string tableName = stmt_CreateTable.SchemaObjectName.BaseIdentifier.Dequote();
var table = new TestTable(schemaName, tableName);
foreach (ColumnDefinition col in stmt_CreateTable.Definition.ColumnDefinitions)
{
string columnName = col.ColumnIdentifier.Dequote();
var columnDbType = ProcedureGenerator.ResolveToDbDataType(col.DataType);
table.AddColumn(columnName, columnDbType, true);
}
return(table);
}
public static IQsiTreeNode VisitCreateViewStatement(CreateTableStatement node)
{
QsiDerivedTableNode derivedTableNode;
if (node.ColumnDefs.Any())
{
derivedTableNode = TreeHelper.Create <PDynamicDerivedTableNode>(n =>
{
n.DynamicColumns = new QsiColumnsDeclarationNode();
n.DynamicColumns.Columns.AddRange(node.ColumnDefs.Select(VisitDynamicColumn));
});
}
else
{
derivedTableNode = new PDerivedTableNode();
}
derivedTableNode.Alias.SetValue(new QsiAliasNode
{
Name = IdentifierVisitor.Visit(node.TableName)[^ 1]
public override void Visit(CreateTableStatement node)
{
string tableName = node.SchemaObjectName.BaseIdentifier.Value;
string schemaQualifiedTableName = $"{node.SchemaObjectName.SchemaIdentifier.Value}.{tableName}";
string className = $"{tableName}Table";
ClassDeclarationSyntax classDeclarationSyntax =
ClassDeclaration(className)
.WithModifiers(TokenList(Token(SyntaxKind.InternalKeyword)))
.WithBaseList(
BaseList(
SingletonSeparatedList <BaseTypeSyntax>(
SimpleBaseType(
IdentifierName("Table")))))
.AddMembers(
ConstructorDeclaration(
Identifier(className))
.WithModifiers(
TokenList(
Token(SyntaxKind.InternalKeyword)))
.WithInitializer(
ConstructorInitializer(
SyntaxKind.BaseConstructorInitializer,
ArgumentList(
SingletonSeparatedList(
Argument(
LiteralExpression(
SyntaxKind.StringLiteralExpression,
Literal(schemaQualifiedTableName)))))))
.WithBody(Block()))
.AddMembers(node.Definition.ColumnDefinitions.Select(CreatePropertyForColumn).ToArray());
FieldDeclarationSyntax field = CreateStaticFieldForClass(className, tableName);
MembersToAdd.Add(field.AddSortingKey(this, tableName));
MembersToAdd.Add(classDeclarationSyntax.AddSortingKey(this, tableName));
base.Visit(node);
}
private string ModifyScript(CreateTableStatement statement, string originalScript, string modifiedScript)
{
var oldScriptBlock = originalScript.Substring(statement.StartOffset, statement.FragmentLength);
var comments = GetComments(oldScriptBlock);
var generator = new Sql120ScriptGenerator(SavedSettings.Get().GeneratorOptions);
string newScriptBlock;
generator.GenerateScript(statement, out newScriptBlock);
if (string.IsNullOrEmpty(comments))
{
modifiedScript = modifiedScript.Replace(oldScriptBlock, newScriptBlock);
}
else
{
modifiedScript = modifiedScript.Replace(oldScriptBlock, newScriptBlock + "\r\n--These comments were saved after refactoring this table...\r\n" + comments);
}
return(modifiedScript);
}
public static IQsiDefinitionNode VisitCreateViewStatement(CreateTableStatement statement)
{
var node = new PViewDefinitionNode
{
Identifier = IdentifierVisitor.Visit(statement.TableName)
};
if (statement.ColumnDefs.Any())
{
node.DynamicColumns = new QsiColumnsDeclarationNode();
node.DynamicColumns.Columns.AddRange(statement.ColumnDefs.Select(TableVisitor.VisitDynamicColumn));
}
QsiTableNode tableNode = TreeHelper.Create <QsiTableReferenceNode>(n =>
{
n.Identifier = IdentifierVisitor.Visit(statement.BaseTableName);
PTree.RawNode[n] = statement.BaseTableName;
});
if (statement.WhereClause != null)
{
var derivedTableNode = new QsiDerivedTableNode();
derivedTableNode.Columns.Value = TreeHelper.CreateAllColumnsDeclaration();
derivedTableNode.Where.Value = ExpressionVisitor.VisitWhere(statement.WhereClause);
derivedTableNode.Source.Value = tableNode;
node.Source.Value = derivedTableNode;
}
else
{
node.Source.Value = tableNode;
}
PTree.RawNode[node] = statement;
return(node);
}
public override void Visit(CreateTableStatement node)
{
DDLStatementFound = true;
}
public override void Visit(CreateTableStatement node)
{
Creates.Add(node);
}
protected void VisitWhenMatchedUpdateToken(MergeStatement statement
, IList <string> tempUpdateAliases
, string targetAlias
, string targetTable
, string targetColumnOn
, string sourceAlias
, string sourceTable
, string sourceColumnOn
, bool isTop)
{
int counter = 0;
ExpressionToken tempExpression = new ExpressionToken();
if (statement.WhenMatched != null)
{
foreach (WhenMatchedToken item in statement.WhenMatched)
{
if (!(item is WhenMatchedTokenThenDeleteToken))
{
IList <CTEDefinition> updatedList = new List <CTEDefinition>();
string tempAlias = "tmp_" + counter;
tempExpression = (IsEqualsToken)(statement.On);
if (((WhenMatchedTokenThenUpdateSetToken)item).Set.Count != 0)
{
foreach (BinaryEqualToken setItem in ((WhenMatchedTokenThenUpdateSetToken)item).Set)
{
if (setItem.First is Name)
{
setItem.First = Sql.Name(((Name)setItem.First).LastPart);
}
if (setItem.Second is Name)
{
setItem.Second = Sql.Name(sourceAlias, ((Name)setItem.Second).LastPart);
}
}
}
if (item.AndCondition != null)
{
if (item.AndCondition is BinaryToken)
{
((BinaryToken)item.AndCondition).First = Sql.Name(targetAlias, ((Name)((BinaryToken)item.AndCondition).First).LastPart);
}
else if (item.AndCondition is UnaryToken)
{
((UnaryToken)item.AndCondition).Token = Sql.Name(targetAlias, ((Name)((UnaryToken)item.AndCondition).Token).LastPart);
}
tempExpression = tempExpression.And((ExpressionToken)item.AndCondition);
}
if (isTop)
{
tempExpression = tempExpression.And(Sql.Name(targetAlias, targetColumnOn)
.In(Sql.Select.Output(sourceColumnOn).From(TopAlias)));
}
CreateTableStatement createTempTable =
Sql.CreateTemporaryTable(tempAlias)
.As(Sql.With("updated").As(
Sql.Update(Sql.NameAs(targetTable, targetAlias))
.Set(((WhenMatchedTokenThenUpdateSetToken)item).Set)
.From(Sql.NameAs(sourceTable, sourceAlias))
.Where(tempExpression)
.Output(Sql.Name(targetAlias, targetColumnOn)))
.Select().From("updated").Output(targetColumnOn));
VisitStatement(createTempTable);
State.WriteStatementTerminator();
tempUpdateAliases.Add(tempAlias);
counter++;
State.WriteStatementTerminator();
}
}
}
}
private static bool NamePrimaryKey(CreateTableStatement statement)
{
var columnWithPrimaryKey = statement.Definition.ColumnDefinitions.FirstOrDefault( c => c.Constraints.Any(p => p is UniqueConstraintDefinition));
if (columnWithPrimaryKey == null)
{
return false;
}
var originalConstraint = columnWithPrimaryKey.Constraints.FirstOrDefault( c => c as UniqueConstraintDefinition != null && ((UniqueConstraintDefinition) c).IsPrimaryKey) as UniqueConstraintDefinition;
if (originalConstraint == null)
{
return false;
}
var newConstraint = new UniqueConstraintDefinition();
newConstraint.Clustered = originalConstraint.Clustered;
newConstraint.IsPrimaryKey = true;
foreach (var o in originalConstraint.IndexOptions)
newConstraint.IndexOptions.Add(o);
newConstraint.IndexType = originalConstraint.IndexType;
var referencedColumn = new ColumnWithSortOrder();
referencedColumn.Column = new ColumnReferenceExpression();
referencedColumn.Column.MultiPartIdentifier = new SchemaObjectName();
referencedColumn.Column.MultiPartIdentifier.Identifiers.Add(columnWithPrimaryKey.ColumnIdentifier);
newConstraint.Columns.Add(referencedColumn);
newConstraint.ConstraintIdentifier = new Identifier();
newConstraint.ConstraintIdentifier.Value =
ReplaceTemplateValues(SavedSettings.Get().PrimaryKeyName, statement.SchemaObjectName.BaseIdentifier.Value, referencedColumn.Column.MultiPartIdentifier.Identifiers.Last().Value);
newConstraint.ConstraintIdentifier.QuoteType = QuoteType.SquareBracket;
statement.Definition.TableConstraints.Add(newConstraint);
columnWithPrimaryKey.Constraints.Remove(originalConstraint);
return true;
}
protected override void VisitCreateTableStatement(CreateTableStatement statement)
{
State.Write(Symbols.CREATE);
if (statement.IsTableVariable || statement.IsTemporary)
{
State.Write(Symbols.TEMPORARY);
}
State.Write(Symbols.TABLE);
if (statement.CheckIfNotExists)
{
State.Write(Symbols.IF);
State.Write(Symbols.NOT);
State.Write(Symbols.EXISTS);
}
VisitNameToken(statement.Name);
var separator = "(";
foreach (var column in statement.Columns)
{
State.Write(separator);
separator = ",";
State.Write("\"", column.Name, "\"");
VisitType(column);
if (column.PrimaryKeyDirection.HasValue)
{
State.Write(Symbols.PRIMARY);
State.Write(Symbols.KEY);
State.Write(column.PrimaryKeyDirection.Value == Direction.Asc ? Symbols.ASC : Symbols.DESC);
VisitConflict(column.PrimaryKeyConflict);
if (column.Identity.On)
{
State.Write(Symbols.AUTOINCREMENT);
}
}
if (column.Null.HasValue)
{
if (!column.Null.Value)
{
State.Write(Symbols.NOT);
}
State.Write(Symbols.NULL);
VisitConflict(column.NullConflict);
}
if (column.DefaultValue != null)
{
State.Write(Symbols.DEFAULT);
State.Write(Symbols.OpenParenthesis);
VisitToken(column.DefaultValue);
State.Write(Symbols.CloseParenthesis);
}
}
if (statement.PrimaryKey != null)
{
if (statement.PrimaryKey.Name != null)
{
State.Write(Symbols.Comma);
State.Write(Symbols.CONSTRAINT);
VisitNameToken(statement.PrimaryKey.Name);
}
State.Write(Symbols.PRIMARY);
State.Write(Symbols.KEY);
VisitTokenSetInParenthesis(statement.PrimaryKey.Columns);
VisitConflict(statement.PrimaryKey.Conflict);
}
foreach (var unique in statement.UniqueConstrains)
{
State.Write(Symbols.Comma);
State.Write(Symbols.CONSTRAINT);
VisitNameToken(unique.Name);
State.Write(Symbols.UNIQUE);
VisitTokenSetInParenthesis(unique.Columns);
VisitConflict(unique.Conflict);
}
State.Write(Symbols.CloseParenthesis);
// if indecies are set, create them
if (statement.Indicies.Count > 0)
{
foreach (var createIndexStatement in statement.Indicies)
{
State.WriteStatementTerminator();
createIndexStatement.CheckIfNotExists |= statement.CheckIfNotExists;
VisitCreateIndexStatement(createIndexStatement);
}
}
}
public override void ExplicitVisit(CreateTableStatement fragment)
{
_fragments.Add(fragment);
}
public override void ExplicitVisit(CreateTableStatement table)
{
if (IsSupportedForCurrentType(table.GetType()))
{
Name = table.SchemaObjectName;
}
}
public override void Visit(CreateTableStatement node) { this.action(node); }
public override string TranslateExt(CreateTableStatement createTableStatement, object options)
{
TableDefinition tableDefinition = createTableStatement.Definition;
string optionAllias0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_src", true);
if (r.Length > 0)
{
optionAllias0 = (r[0] as TextBox).Text;
}
}
string optionAlliasDest0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_dest", true);
if (r.Length > 0)
{
optionAlliasDest0 = (r[0] as TextBox).Text;
}
}
bool bOptionInline = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_inline", true);
if (r.Length > 0)
{
bOptionInline = (r[0] as CheckBox).Checked;
}
}
string sColumnSeparator = Environment.NewLine;
if (bOptionInline)
{
sColumnSeparator = null;
}
bool bOptionExplicitNames = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_explicit_pname", true);
if (r.Length > 0)
{
bOptionExplicitNames = (r[0] as CheckBox).Checked;
}
}
bool bOptionValues = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_values", true);
if (r.Length > 0)
{
bOptionValues = (r[0] as CheckBox).Checked;
}
}
bool bOptionDefault = true;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_default", true);
if (r.Length > 0)
{
bOptionDefault = (r[0] as CheckBox).Checked;
}
}
bool bNotAlready = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_notalready", true);
if (r.Length > 0)
{
bNotAlready = (r[0] as CheckBox).Checked;
}
}
string keywordSep = bOptionInline ? " " : Environment.NewLine;
string optionAllias = optionAllias0;
string optionAlliasDest = optionAlliasDest0;
string tableName = TSQLHelper.Identifiers2Value(createTableStatement.SchemaObjectName.Identifiers);
if (!String.IsNullOrEmpty(optionAllias))
{
optionAllias = optionAllias + '.';
}
if (!String.IsNullOrEmpty(optionAlliasDest))
{
optionAlliasDest = optionAlliasDest + '.';
}
string columnIdent = "\t";
if (bOptionInline)
{
columnIdent = null;
}
StringBuilder result = new StringBuilder();
string sep = null;
// insert into
{
result.Append($"insert into {tableName}({sColumnSeparator}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (!bOptionDefault && TSQLHelper.ColumnIsDefault(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
//if (!bOptionInline)
result.Append($"{columnIdent}{sep}{ident}{sColumnSeparator}");
//else result.Append($"{sep}{ident}{sColumnSeparator}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"){Environment.NewLine}");
}
if (bOptionValues)
{
result.Append($"values(");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (!bOptionDefault && TSQLHelper.ColumnIsDefault(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (bOptionExplicitNames)
{
result.Append($"{columnIdent}{sep}{optionAllias}{ident} /*{ident}*/{sColumnSeparator}");
}
else
{
result.Append($"{columnIdent}{sep}{optionAllias}{ident}{sColumnSeparator}");
}
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"){Environment.NewLine}");
}
else
// select
{
result.Append($"select{keywordSep}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (!bOptionDefault && TSQLHelper.ColumnIsDefault(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (bOptionExplicitNames)
{
result.Append($"{columnIdent}{sep}{ident} = {optionAllias}{ident}{sColumnSeparator}");
}
else
{
result.Append($"{columnIdent}{sep}{optionAllias}{ident}{sColumnSeparator}");
}
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"{keywordSep}from {optionAllias0}{Environment.NewLine}");
}
if (bNotAlready)
{
result.Append($"where not exists(select 1 from {tableName} t with (nolock) where");
int i = 0;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
i++;
if (!bOptionDefault && TSQLHelper.ColumnIsDefault(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (i > 1)
{
result.Append($" and");
}
result.Append($" t.{ident} = {optionAllias}{ident}");
}
result.Append($")");
}
result.Append($";");
return(result.ToString());
}
public void ProcessCreateTable(CreateTableStatement TblStmt)
{
bool isTemp = TblStmt.SchemaObjectName.BaseIdentifier.Value.StartsWith("#") ||
TblStmt.SchemaObjectName.BaseIdentifier.Value.StartsWith("@");
if (TblStmt.SchemaObjectName.SchemaIdentifier == null &&
!isTemp
)
{
_smells.SendFeedBack(27, TblStmt);
}
{
foreach (ColumnDefinition colDef in TblStmt.Definition.ColumnDefinitions)
{
_smells.ProcessTsqlFragment(colDef);
}
}
if (isTemp)
{
foreach (ConstraintDefinition constDef in TblStmt.Definition.TableConstraints)
{
if (constDef.ConstraintIdentifier != null) { }
switch (FragmentTypeParser.GetFragmentType(constDef))
{
case "UniqueConstraintDefinition":
UniqueConstraintDefinition unqConst = (UniqueConstraintDefinition)constDef;
if (unqConst.IsPrimaryKey)
{
_smells.SendFeedBack(38, constDef);
}
break;
}
}
foreach (ColumnDefinition colDef in TblStmt.Definition.ColumnDefinitions)
{
if (colDef.DefaultConstraint != null && colDef.DefaultConstraint.ConstraintIdentifier != null)
{
_smells.SendFeedBack(39, colDef);
}
foreach (ConstraintDefinition constDef in colDef.Constraints)
{
if (constDef.ConstraintIdentifier != null) { }
switch (FragmentTypeParser.GetFragmentType(constDef))
{
case "CheckConstraintDefinition":
CheckConstraintDefinition chkConst = (CheckConstraintDefinition)constDef;
if (chkConst.ConstraintIdentifier != null)
{
_smells.SendFeedBack(40, chkConst); ;
}
break;
}
}
}
}
}
CreateTableStatement ParseCreateTableStatement()
{
CreateTableStatement ss = new CreateTableStatement();
ReadNextToken(); //skip "table"
ss.TableName = fCurrentToken.Value.TrimQuotation();
ReadNextToken();
SkipExpected("(");
if (isTableConstraint())
ss.Constraints.Add(parseTableConstraint());
else
ss.Columns.Add(parseColumnDef());
while (!AtEndOfSource && fCurrentToken.Equals(","))
{
ReadNextToken(); // skip ","
if (isTableConstraint())
ss.Constraints.Add(parseTableConstraint());
else
ss.Columns.Add(parseColumnDef());
}
SkipExpected(")");
return ss;
}
public override void Visit(CreateTableStatement node)
{
base.Visit(node);
stmts.Add(node);
}
