Пример #1
0
 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);
     }
 }
Пример #2
0
        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);
                            }
                        }
                    }
                }
            }
Пример #4
0
        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());
        }
Пример #6
0
 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;
         }
     }
 }
Пример #7
0
        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);
        }
Пример #8
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);
        }
Пример #9
0
        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;
            }
        }
Пример #11
0
        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);
        }
Пример #12
0
        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);
 }
Пример #15
0
        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;
        }
Пример #16
0
        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);
            }
        }
Пример #17
0
        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.");
            }
        }
Пример #18
0
        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(")");
            }
        }
Пример #20
0
        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);
        }
Пример #21
0
        private static SqlTypeInfo CharOfMaxLength()
        {
            var dataRef = new SqlDataTypeReference()
            {
                SqlDataTypeOption = SqlDataTypeOption.Char
            };

            dataRef.Parameters.Add(new MaxLiteral());
            return SqlTypeInfo.Create(dataRef);
        }
Пример #22
0
 public override void Visit(SqlDataTypeReference node) { this.action(node); }
Пример #23
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"
            });
        }
        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);
        }
    }
}
Пример #26
0
 public override void ExplicitVisit(SqlDataTypeReference node)
 {
     (new TypeVisitor(this.currentScopeRef.Current)).ExplicitVisit(node);
 }
Пример #27
0
        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);
        }
Пример #28
0
        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);
                 */
            }
        }
Пример #29
0
        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();
            }
        }