public override void Visit(SqlDataTypeReference node) { if (TypesThatRequireLength.Any(option => Equals(option, node.SqlDataTypeOption) && node.Parameters.Count < 1)) { ErrorCallback(RULE_NAME, RULE_TEXT, node.StartLine, node.StartColumn + node.FragmentLength); } }
public static Field GetField(this SqlDataTypeReference sqlDataTypeReference, string name, bool isNullable, ILogger logger, SchemaFile file) { var type = sqlDataTypeReference.GetFieldType(logger, file); switch (type) { case FieldType.String: return(new StringField() { Name = name, Type = type, IsNullable = isNullable, Length = sqlDataTypeReference.GetStringLength(logger), }); case FieldType.Decimal: case FieldType.Numeric: return(new DecimalField() { Name = name, Type = type, IsNullable = isNullable, Precision = sqlDataTypeReference.GetPrecision(logger), Scale = sqlDataTypeReference.GetScale(logger), }); default: return(new DefaultField() { Name = name, Type = type, IsNullable = isNullable, }); } }
private void Analyze(string value, SqlDataTypeReference dataType) { if (dataType == null) { return; } if (_variableNamePattern.IsMatch(value)) { if (dataType.SqlDataTypeOption == SqlDataTypeOption.Char || dataType.SqlDataTypeOption == SqlDataTypeOption.VarChar) { if (dataType.Parameters.Count > 0 && dataType.Parameters[0].LiteralType == LiteralType.Integer) { int size = Int32.Parse((dataType.Parameters[0] as IntegerLiteral).Value); if (size < _newDataTypeName.MaxDataSize) { int startTokenIndex = dataType.FirstTokenIndex; int endTokenIndex = dataType.LastTokenIndex; _collector.CollectTokenToUpdate(startTokenIndex, endTokenIndex); } } } } }
public static SqlDataTypeReference DataType(SqlDataTypeOption option, IEnumerable <Literal> parameters) { var fragment = new SqlDataTypeReference(); fragment.SqlDataTypeOption = option; fragment.Parameters.AddRange(parameters); return(fragment); }
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 Visit(SqlDataTypeReference node) { for (var i = 0; i < TypesThatRequireLength.Length; i++) { var option = TypesThatRequireLength[i]; if (Equals(option, node.SqlDataTypeOption) && node.Parameters.Count < 1) { ErrorCallback(RULE_NAME, RULE_TEXT, node.StartLine, node.StartColumn + node.FragmentLength); break; } } }
public static int GetScale(this SqlDataTypeReference sqlDataTypeReference, ILogger logger) { if (sqlDataTypeReference.Parameters.Any()) { var parameter = sqlDataTypeReference.Parameters.Last(); switch (parameter.LiteralType) { case LiteralType.Integer: return(int.Parse(parameter.Value)); } } // The default scale is 0 return(0); }
public static int GetPrecision(this SqlDataTypeReference sqlDataTypeReference, ILogger logger) { if (sqlDataTypeReference.Parameters.Any()) { var parameter = sqlDataTypeReference.Parameters.First(); switch (parameter.LiteralType) { case LiteralType.Integer: return(int.Parse(parameter.Value)); } } // The default precision is 18 return(18); }
public void ProcessSqlDataTypeReference(SqlDataTypeReference DataType) { if (DataType.SqlDataTypeOption == SqlDataTypeOption.Table) { } switch (DataType.SqlDataTypeOption) { case SqlDataTypeOption.Table: break; case SqlDataTypeOption.Text: case SqlDataTypeOption.NText: case SqlDataTypeOption.Image: _smells.SendFeedBack(47, DataType); break; } }
private ScalarExpression GetColumnOrCastColumn(ColumnDescriptor column, string tableName) { var columnReference = new ColumnReferenceExpression(); columnReference.MultiPartIdentifier = MultiPartIdentifierBuilder.Get(tableName, column.Name.GetName()); if (column.UnderlyingType == "xml") { var cast = new CastCall(); var type = new SqlDataTypeReference(); type.SqlDataTypeOption = SqlDataTypeOption.NVarChar; type.Parameters.Add(new MaxLiteral()); cast.DataType = type; cast.Parameter = columnReference; return(cast); } return(columnReference); }
public static int GetStringLength(this SqlDataTypeReference sqlDataTypeReference, ILogger logger) { if (sqlDataTypeReference.Parameters.Any()) { var parameter = sqlDataTypeReference.Parameters.First(); switch (parameter.LiteralType) { case LiteralType.Integer: return(int.Parse(parameter.Value)); case LiteralType.Max: return(8000); } } // TODO : when it's variable declaration it will trim it at 1 otherwise at 30 characters // https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#arguments // length - An optional integer that specifies the length of the target data type. The default value is 30. return(30); }
public override void Analyze(SqlFileContext context) { ColumnDefinition node = (ColumnDefinition)context.Fragment; SqlDataTypeReference sdtr = node.DataType as SqlDataTypeReference; if (sdtr == null) { return; } if (sdtr.SqlDataTypeOption != SqlDataTypeOption.VarBinary && sdtr.SqlDataTypeOption != SqlDataTypeOption.VarChar && sdtr.SqlDataTypeOption != SqlDataTypeOption.NVarChar) { return; } if (sdtr.Parameters.Count > 0) { Region region = sdtr.CreateRegion(); int size = Int32.Parse(sdtr.Parameters[0].Value); if (size <= context.Policy.GetProperty(SmallSizeThreshold)) { // The data type for column '{0}' was defined as {1} of a // small size ({2}) which may incur additional storage and // performance costs. Declare this data type as a fixed size // or ignore the warning in cases performance is not a concern. context.Logger.Log(this, RuleUtilities.BuildResult(ResultKind.Warning, context, region, nameof(RuleResources.SQL2009_Default), node.ColumnIdentifier.Value, sdtr.SqlDataTypeOption.ToString(), size.ToString())); } } return; }
public override void ExplicitVisit(SqlDataTypeReference fragment) { _fragments.Add(fragment); }
private ScalarExpression GetColumnOrCastColumn(ColumnDescriptor column, string tableName) { var columnReference = new ColumnReferenceExpression(); columnReference.MultiPartIdentifier = MultiPartIdentifierBuilder.Get(tableName, column.Name.GetName()); if (column.UnderlyingType == "xml") { var cast = new CastCall(); var type = new SqlDataTypeReference(); type.SqlDataTypeOption = SqlDataTypeOption.NVarChar; type.Parameters.Add(new MaxLiteral()); cast.DataType = type; cast.Parameter = columnReference; return cast; } return columnReference; }
public static DbType ResolveToDbDataType(DataTypeReference dataType) { if (dataType.Name.Count == 1) { string typeName = dataType.Name[0].Value; if (string.Equals("INT", typeName, StringComparison.OrdinalIgnoreCase)) { //return typeof(int); return(DbType.Int32); } if (string.Equals("BIT", typeName, StringComparison.OrdinalIgnoreCase)) { //return typeof(bool); return(DbType.Boolean); } if (string.Equals("NVARCHAR", typeName, StringComparison.OrdinalIgnoreCase)) { SqlDataTypeReference sqlDataType = (SqlDataTypeReference)dataType; string maxLen = sqlDataType.Parameters[0].Value; //dataType.p //return typeof(string); return(DbType.String); } if (string.Equals("SMALLINT", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.Int16); } if (string.Equals("DATETIME", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.DateTime); } if (string.Equals("BIGINT", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.Int64); } if (string.Equals("TINYINT", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.Byte); } if (string.Equals("UNIQUEIDENTIFIER", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.Guid); } if (string.Equals("VARBINARY", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.Binary); } if (string.Equals("DECIMAL", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.Decimal); } if (string.Equals("DATETIMEOFFSET", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.DateTimeOffset); } if (string.Equals("NCHAR", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.String); } if (string.Equals("XML", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.Xml); } if (string.Equals("DATE", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.Date); } if (string.Equals("TIME", typeName, StringComparison.OrdinalIgnoreCase)) { return(DbType.Time); } throw new NotImplementedException("typeName:" + typeName); } else { throw new NotImplementedException("Name.Count:" + dataType.Name.Count); } }
public static FieldType GetFieldType(this SqlDataTypeReference sqlDataTypeReference) { switch (sqlDataTypeReference.SqlDataTypeOption) { case SqlDataTypeOption.BigInt: return(FieldType.BigInt); case SqlDataTypeOption.Int: return(FieldType.Int); case SqlDataTypeOption.SmallInt: return(FieldType.SmallInt); case SqlDataTypeOption.TinyInt: return(FieldType.TinyInt); case SqlDataTypeOption.Bit: return(FieldType.Bit); case SqlDataTypeOption.Numeric: case SqlDataTypeOption.Decimal: return(FieldType.Decimal); case SqlDataTypeOption.Money: return(FieldType.Money); case SqlDataTypeOption.SmallMoney: return(FieldType.SmallMoney); case SqlDataTypeOption.Float: return(FieldType.Float); case SqlDataTypeOption.Real: return(FieldType.Real); case SqlDataTypeOption.DateTime: return(FieldType.DateTime); case SqlDataTypeOption.SmallDateTime: return(FieldType.SmallDateTime); case SqlDataTypeOption.Char: case SqlDataTypeOption.VarChar: case SqlDataTypeOption.NChar: case SqlDataTypeOption.NVarChar: return(FieldType.String); case SqlDataTypeOption.Text: case SqlDataTypeOption.NText: return(FieldType.Text); case SqlDataTypeOption.Binary: return(FieldType.Binary); case SqlDataTypeOption.VarBinary: return(FieldType.VarBinary); case SqlDataTypeOption.Image: return(FieldType.Image); case SqlDataTypeOption.Table: return(FieldType.Table); case SqlDataTypeOption.Timestamp: return(FieldType.Timestamp); case SqlDataTypeOption.UniqueIdentifier: return(FieldType.UniqueIdentifier); case SqlDataTypeOption.Date: return(FieldType.Date); case SqlDataTypeOption.Time: return(FieldType.Time); case SqlDataTypeOption.DateTime2: return(FieldType.DateTime2); case SqlDataTypeOption.DateTimeOffset: return(FieldType.DateTimeOffset); case SqlDataTypeOption.Sql_Variant: return(FieldType.SqlVariant); default: throw new ArgumentException($"{sqlDataTypeReference.SqlDataTypeOption} data type option is not supported."); } }
public CreateProcedureStatement Convert(CStoredProcedure storedProcedure) { //build body string[] parts = { storedProcedure.Schema.SchemaName, storedProcedure.StoredProcedureName }; var createStoredProcedure = new CreateProcedureStatement(); ///set schema and table name /// var schemaObjectName = new SchemaObjectName(); schemaObjectName.Identifiers.Add(new Identifier { Value = storedProcedure.Schema.SchemaName }); schemaObjectName.Identifiers.Add(new Identifier { Value = storedProcedure.StoredProcedureName }); createStoredProcedure.ProcedureReference = new ProcedureReference(); createStoredProcedure.ProcedureReference.Name = schemaObjectName; //add parameters foreach (var param in storedProcedure.Parameter) { if (param.ParameterTypeIsUserDefined) { var dataType = new UserDataTypeReference(); dataType.Name = new SchemaObjectName(); dataType.Name.Identifiers.Add(new Identifier { Value = param.ParameterTypeRaw }); if (param.ParameterLength > 0) { dataType.Parameters.Add(new IntegerLiteral { Value = param.ParameterLength.ToString() }); } createStoredProcedure.Parameters.Add(new ProcedureParameter { VariableName = new Identifier { Value = $"@{param.ParameterName}" }, DataType = dataType, Value = param.DefaultToNull ? new NullLiteral() : null, Modifier = param.ParameterTypeRaw.ToLower() == "sysname" ? ParameterModifier.None: ParameterModifier.ReadOnly //todo }); } else { var dataType = new SqlDataTypeReference(); var parameterName = param.ParameterName; if (param.IsCollection) { parameterName += "_Collection";// temp solution for comma separate collection parameters } if (param.ParameterTypeRaw == "enum") { dataType.SqlDataTypeOption = SqlDataTypeOption.Int; //todo: review this } else { dataType.SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(param.ParameterTypeRaw); } if (param.ParameterLength > 0) { dataType.Parameters.Add(new IntegerLiteral { Value = param.ParameterLength.ToString() }); } createStoredProcedure.Parameters.Add(new ProcedureParameter { VariableName = new Identifier { Value = $"@{parameterName}" }, DataType = dataType, Value = param.DefaultToNull ? new NullLiteral() : null }); } } var parser = new TSql120Parser(false); createStoredProcedure.StatementList = new StatementList(); if (!string.IsNullOrEmpty(storedProcedure.StoredProcedureBody)) { IList <ParseError> errors; var script2 = parser.Parse(new StringReader(storedProcedure.StoredProcedureBody), out errors) as TSqlScript; if (errors.Count > 0) { RaiseSqlErrorsException(errors, storedProcedure.StoredProcedureBody); } foreach (var batch2 in script2.Batches) { foreach (var statement in batch2.Statements) { createStoredProcedure.StatementList.Statements.Add(statement); } } } return(createStoredProcedure); }
public override void ExplicitVisit(SqlDataTypeReference node) { bool skipParameters = false; string typeName = node.Name.BaseIdentifier.Value.ToLower(); switch (node.SqlDataTypeOption) { case SqlDataTypeOption.SmallInt: if (_column.IsIdentity) { typeName = "smallserial"; } break; case SqlDataTypeOption.Int: if (_column.IsIdentity) { typeName = "serial"; } break; case SqlDataTypeOption.BigInt: if (_column.IsIdentity) { typeName = "bigserial"; } break; case SqlDataTypeOption.Bit: typeName = "bool"; break; case SqlDataTypeOption.Float: typeName = "double precision"; skipParameters = true; break; case SqlDataTypeOption.TinyInt: typeName = "smallint"; if (_column.IsIdentity) { typeName = "smallserial"; } break; case SqlDataTypeOption.DateTime: case SqlDataTypeOption.DateTime2: typeName = "timestamp"; skipParameters = true; break; case SqlDataTypeOption.Image: typeName = "bytea"; break; case SqlDataTypeOption.NText: typeName = "text"; break; case SqlDataTypeOption.Time: skipParameters = true; break; case SqlDataTypeOption.UniqueIdentifier: typeName = "uuid"; break; case SqlDataTypeOption.VarBinary: if (node.Parameters.Where(t => t is MaxLiteral).Any()) { typeName = "bytea"; skipParameters = true; } else { typeName = "bytea"; } break; case SqlDataTypeOption.NVarChar: case SqlDataTypeOption.VarChar: if (node.Parameters.Where(t => t is MaxLiteral).Any()) { typeName = "text"; skipParameters = true; } else { typeName = "varchar"; } break; case SqlDataTypeOption.Char: case SqlDataTypeOption.NChar: typeName = "varchar"; break; } _column.DataType = typeName; _buffer.AppendFormat("{0}", typeName); if (node.Parameters.Count > 0 && !skipParameters) { _buffer.AppendFormat("("); _buffer.Append(string.Join(", ", node.Parameters.Select(t => t.Value).ToArray())); _buffer.Append(")"); } }
public string Convert(CTableType tableType) { string[] parts = { tableType.Schema.SchemaName, tableType.TableName }; var createTypeTable = new CreateTypeTableStatement(); ///set schema and table name createTypeTable.Name = new SchemaObjectName(); createTypeTable.Name.Identifiers.Add(new Identifier { Value = tableType.Schema.SchemaName }); createTypeTable.Name.Identifiers.Add(new Identifier { Value = tableType.TableName }); //add columns createTypeTable.Definition = new TableDefinition(); foreach (var col in tableType.Column) { var dataType = new SqlDataTypeReference { SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(col.ColumnTypeRaw) }; if (DoesNeedLength(col.ColumnSqlDbType)) { 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 }; if (col.IsIdentity) { column.IdentityOptions = new IdentityOptions { IdentitySeed = new IntegerLiteral { Value = "1000" }, IdentityIncrement = new IntegerLiteral { Value = "1" } } } ; column.Constraints.Add(new NullableConstraintDefinition { Nullable = col.IsNullable }); if (col.IsUnique) { column.Constraints.Add(new UniqueConstraintDefinition()); } if (col.IsIndexed) { column.Index = new IndexDefinition { Name = new Identifier { Value = $"IX_{col.ColumnName}" }, IndexType = new IndexType { IndexTypeKind = IndexTypeKind.NonClustered } } } ; createTypeTable.Definition.ColumnDefinitions.Add(column); } /* * //generate DDL * var script = new TSqlScript(); * var batch = new TSqlBatch(); * script.Batches.Add(batch); * batch.Statements.Add(createTypeTable); * 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(); */ string scriptOut; var scriptGen = new Sql120ScriptGenerator(); scriptGen.GenerateScript(createTypeTable, out scriptOut); return(scriptOut); }
private static SqlTypeInfo CharOfMaxLength() { var dataRef = new SqlDataTypeReference() { SqlDataTypeOption = SqlDataTypeOption.Char }; dataRef.Parameters.Add(new MaxLiteral()); return SqlTypeInfo.Create(dataRef); }
public override void Visit(SqlDataTypeReference node) { this.action(node); }
private ScalarExpression GetDefaultValue(SqlDataTypeReference type) { switch (type.SqlDataTypeOption) { case SqlDataTypeOption.SmallInt: case SqlDataTypeOption.TinyInt: case SqlDataTypeOption.Bit: case SqlDataTypeOption.Decimal: case SqlDataTypeOption.Numeric: case SqlDataTypeOption.Money: case SqlDataTypeOption.SmallMoney: case SqlDataTypeOption.Float: case SqlDataTypeOption.Real: case SqlDataTypeOption.BigInt: case SqlDataTypeOption.Int: return(new IntegerLiteral { Value = "0" }); case SqlDataTypeOption.Char: case SqlDataTypeOption.VarChar: case SqlDataTypeOption.Text: case SqlDataTypeOption.NChar: case SqlDataTypeOption.NVarChar: case SqlDataTypeOption.NText: return(new StringLiteral { Value = "" }); case SqlDataTypeOption.Binary: case SqlDataTypeOption.VarBinary: case SqlDataTypeOption.Image: return(new BinaryLiteral { Value = "0" }); case SqlDataTypeOption.UniqueIdentifier: return(new StringLiteral { Value = Guid.NewGuid().ToString() }); case SqlDataTypeOption.DateTime: case SqlDataTypeOption.SmallDateTime: case SqlDataTypeOption.Date: case SqlDataTypeOption.DateTime2: return(new StringLiteral { Value = "1980-04-01" }); //yes i did do that case SqlDataTypeOption.Time: return(new StringLiteral { Value = "11:59:59" }); //yes i did do that } return(new StringLiteral { Value = "0" }); }
private ScalarExpression GetDefaultValue(SqlDataTypeReference type) { switch (type.SqlDataTypeOption) { case SqlDataTypeOption.SmallInt: case SqlDataTypeOption.TinyInt: case SqlDataTypeOption.Bit: case SqlDataTypeOption.Decimal: case SqlDataTypeOption.Numeric: case SqlDataTypeOption.Money: case SqlDataTypeOption.SmallMoney: case SqlDataTypeOption.Float: case SqlDataTypeOption.Real: case SqlDataTypeOption.BigInt: case SqlDataTypeOption.Int: return new IntegerLiteral {Value = "0"}; case SqlDataTypeOption.Char: case SqlDataTypeOption.VarChar: case SqlDataTypeOption.Text: case SqlDataTypeOption.NChar: case SqlDataTypeOption.NVarChar: case SqlDataTypeOption.NText: return new StringLiteral {Value = ""}; case SqlDataTypeOption.Binary: case SqlDataTypeOption.VarBinary: case SqlDataTypeOption.Image: return new BinaryLiteral {Value = "0"}; case SqlDataTypeOption.UniqueIdentifier: return new StringLiteral {Value = Guid.NewGuid().ToString()}; case SqlDataTypeOption.DateTime: case SqlDataTypeOption.SmallDateTime: case SqlDataTypeOption.Date: case SqlDataTypeOption.DateTime2: return new StringLiteral {Value = "1980-04-01"}; //yes i did do that case SqlDataTypeOption.Time: return new StringLiteral {Value = "11:59:59"}; //yes i did do that } return new StringLiteral {Value = "0"}; }
public CreateTableStatement Convert(CTable table) { var parser = new TSql120Parser(false); 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) { if (col.ColumnType == DbType.Object) { continue; } var dataType = new SqlDataTypeReference { SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(col.ColumnTypeRaw) }; if (col.ColumnLength > 0) { dataType.Parameters.Add(new IntegerLiteral { Value = col.ColumnLength.ToString() }); } var column = new ColumnDefinition { ColumnIdentifier = new Identifier { Value = col.ColumnName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, table.ConvertToSnakeCase) }, DataType = dataType }; if (!string.IsNullOrEmpty(col.DefaultValue)) { IList <ParseError> errors; //var defaultValueText = "CONVERT([char](32), REPLACE(CONVERT([char](36),NEWID()),'-',''))"; var scriptDefault = parser.ParseExpression(new StringReader(col.DefaultValue), out errors); column.DefaultConstraint = new DefaultConstraintDefinition { Expression = scriptDefault }; } if (col.IsIdentity) { column.IdentityOptions = new IdentityOptions { IdentitySeed = new IntegerLiteral { Value = col.ColumnType == System.Data.DbType.Byte ? "1" : "1000" }, IdentityIncrement = new IntegerLiteral { Value = "1" } } } ; column.Constraints.Add(new NullableConstraintDefinition { Nullable = col.IsNullable }); if (col.IsUnique) { column.Constraints.Add(new UniqueConstraintDefinition()); } if (col.IsIndexed) { column.Index = new IndexDefinition { Name = new Identifier { Value = $"IX_{col.ColumnName}" }, IndexType = new IndexType { IndexTypeKind = IndexTypeKind.NonClustered } } } ; createTable.Definition.ColumnDefinitions.Add(column); } //add PK's var pks = table.GetPrimaryKeyColumns(); if (pks.Count > 0) { var primaryKeyConstraint = new UniqueConstraintDefinition { IsPrimaryKey = true }; primaryKeyConstraint.Clustered = true; // todo: use metadata foreach (var pk in table.GetPrimaryKeyColumns()) { var columnIdentifier = new MultiPartIdentifier(); columnIdentifier.Identifiers.Add(new Identifier { Value = pk.ColumnName }); var columnRefExpression = new ColumnReferenceExpression { MultiPartIdentifier = columnIdentifier }; var columnWithSortOrder = new ColumnWithSortOrder { Column = columnRefExpression }; primaryKeyConstraint.Columns.Add(columnWithSortOrder); } var pkConstraintName = $"PK_{table.Schema.SchemaName}_{table.TableName}_{pks.First().ColumnName}"; primaryKeyConstraint.ConstraintIdentifier = new Identifier { Value = pkConstraintName }; createTable.Definition.TableConstraints.Add(primaryKeyConstraint); } //add foreign keys foreach (var col in table.Column) { if (col.ForeignKeyColumn == null || col.ForeignKeyColumn.Count == 0) { continue; } foreach (var fk in col.ForeignKeyColumn) { var fkConstraintName = $"FK_{table.TableName}_{fk.Table.TableName}"; var foreignKeyConstraint = new ForeignKeyConstraintDefinition(); foreignKeyConstraint.ConstraintIdentifier = new Identifier { Value = fkConstraintName }; foreignKeyConstraint.Columns.Add(new Identifier { Value = col.ColumnName }); foreignKeyConstraint.ReferenceTableName = new SchemaObjectName(); foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier { Value = fk.Table.Schema.SchemaName }); foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier { Value = fk.Table.TableName }); foreignKeyConstraint.ReferencedTableColumns.Add(new Identifier { Value = fk.ColumnName }); createTable.Definition.TableConstraints.Add(foreignKeyConstraint); } } return(createTable); } } }
public override void ExplicitVisit(SqlDataTypeReference node) { (new TypeVisitor(this.currentScopeRef.Current)).ExplicitVisit(node); }
private static SqlTypeInfo NCharOfLength(int len) { var dataRef = new SqlDataTypeReference() { SqlDataTypeOption = SqlDataTypeOption.NChar }; dataRef.Parameters.Add(new IntegerLiteral() { Value = len.ToString() }); return SqlTypeInfo.Create(dataRef); }
public override void ExplicitVisit(SqlDataTypeReference node) { var name = node.Name; var parameters = node.Parameters; SqlName typename = SqlName.From(node.Name); var sqlDataTypeOption = node.SqlDataTypeOption; short length = 0; bool isMax = false; byte precision = 0; byte scale = 0; string collationName = null; foreach (var parameter in parameters) { { if (parameter is MaxLiteral) { isMax = true; continue; } } { if (parameter is IntegerLiteral lengthLiteral) { short.TryParse(lengthLiteral.Value, out length); continue; } } // TODO: float(1,2) // TODO: collationName } if (sqlDataTypeOption != ModelSystemDataType.None) { // happyness var scalarType = new ModelSematicScalarType(); scalarType.Name = typename; scalarType.MaxLength = (isMax) ? ((short)-1) : length; scalarType.Precision = precision; scalarType.Scale = scale; scalarType.SystemDataType = sqlDataTypeOption; scalarType.CollationName = collationName; node.Analyse.ResultType = new SqlCodeType(scalarType); } else { if (System.Diagnostics.Debugger.IsAttached) { System.Diagnostics.Debugger.Break(); } var resolved = this.scope.ResolveObject(typename, null); if (resolved != null) { if (resolved is ModelSematicType modelType) { node.Analyse.ResultType = new SqlCodeType(modelType); } } /* * var sqlName = SqlName.Parse(name.BaseIdentifier.Value, ObjectLevel.Object); * var sqlSysName = SqlName.Root.Child("sys", ObjectLevel.Schema).Child(sqlName.Name, ObjectLevel.Object); * var sqlCodeType = this.currentScopeRef.Current.ResolveObject(sqlSysName, null) as ISqlCodeType; * SetAnalyseSqlCodeType(node.Analyse, sqlCodeType); */ } }
protected override object InternalVisit(SqlDataTypeReference node) { /* * INFO(Richo): Based on the following table * https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings */ switch (node.SqlDataTypeOption) { case SqlDataTypeOption.Int: return(typeof(Int32)); case SqlDataTypeOption.BigInt: return(typeof(Int64)); case SqlDataTypeOption.SmallInt: return(typeof(Int16)); case SqlDataTypeOption.TinyInt: return(typeof(Byte)); case SqlDataTypeOption.Decimal: case SqlDataTypeOption.Numeric: case SqlDataTypeOption.Money: case SqlDataTypeOption.SmallMoney: return(typeof(Decimal)); case SqlDataTypeOption.Text: case SqlDataTypeOption.NChar: case SqlDataTypeOption.NVarChar: case SqlDataTypeOption.NText: case SqlDataTypeOption.VarChar: case SqlDataTypeOption.Char: //TODO: size limit? unicode limit? //TODO: if this has more than 1 space, it should be a string return(typeof(String)); case SqlDataTypeOption.Bit: return(typeof(Boolean)); case SqlDataTypeOption.Float: return(typeof(Double)); case SqlDataTypeOption.Real: return(typeof(Single)); case SqlDataTypeOption.Date: case SqlDataTypeOption.DateTime: case SqlDataTypeOption.DateTime2: case SqlDataTypeOption.SmallDateTime: return(typeof(DateTime)); case SqlDataTypeOption.Time: return(typeof(TimeSpan)); case SqlDataTypeOption.DateTimeOffset: return(typeof(DateTimeOffset)); case SqlDataTypeOption.Binary: case SqlDataTypeOption.VarBinary: case SqlDataTypeOption.Image: case SqlDataTypeOption.Rowversion: case SqlDataTypeOption.Timestamp: return(typeof(Byte[])); case SqlDataTypeOption.Sql_Variant: return(typeof(Object)); case SqlDataTypeOption.UniqueIdentifier: return(typeof(Guid)); case SqlDataTypeOption.None: case SqlDataTypeOption.Cursor: case SqlDataTypeOption.Table: default: throw new NotSupportedException(); } }