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;
}
}
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();
}
}