C# DataSchema.DatabaseColumn类代码示例

        public override string WriteDataType(DatabaseColumn column)
        {
            var type = new DataTypeWriter().WriteDataType(column);
            if (column.IsPrimaryKey && (Table.PrimaryKey == null || Table.PrimaryKey.Columns.Count == 1))
            {
                type += " PRIMARY KEY";
                if (column.IsAutoNumber) //must be integer primary key
                {
                    return "INTEGER PRIMARY KEY AUTOINCREMENT";
                }
            }
            if (!column.Nullable) type += " NOT NULL";
            //if there's a default value, and it's not a guid generator or autonumber
            if (!string.IsNullOrEmpty(column.DefaultValue) &&
                !SqlTranslator.IsGuidGenerator(column.DefaultValue) &&
                !column.IsAutoNumber)
            {
                var value = SqlTranslator.Fix(column.DefaultValue);
                //SqlServer (N'string') format
                if (value.StartsWith("(N'", StringComparison.OrdinalIgnoreCase))
                    value = value.Replace("(N'", "('");
                type += " DEFAULT " + value;
            }

            return type;
        }

 /// <summary>
 /// Adds a dataType object to a column.
 /// </summary>
 /// <param name="column">The column.</param>
 public static void AddDataType(DatabaseColumn column)
 {
     if (column == null) return;
     //there is no data type at all
     if (string.IsNullOrEmpty(column.DbDataType)) return;
     //a datatype already assigned
     if (column.DataType != null) return;
     //use upper case
     var dbType = column.DbDataType.ToUpperInvariant();
     //nothing to convert
     if (string.IsNullOrEmpty(dbType)) return;
     var sqlType = SqlType.SqlServer;
     var dataTypeList = new List<DataType>();
     if (column.Table != null)
     {
         //look up the full schema if it exists
         var schema = column.Table.DatabaseSchema;
         if (schema != null)
         {
             var provider = schema.Provider;
             sqlType = ProviderToSqlType.Convert(provider) ?? SqlType.SqlServer;
             dataTypeList = schema.DataTypes;
         }
     }
     //does the schema data types contain this type? if so, assign it.
     var dataType = FindDataType(dbType, dataTypeList, sqlType, column.Length);
     column.DataType = dataType;
 }

        public string WriteDataType(DatabaseColumn column)
        {
            if (column == null) return string.Empty;
            if (string.IsNullOrEmpty(column.DbDataType)) return string.Empty;
            var dataType = column.DbDataTypeStandard();

            dataType = OtherDatabaseTypesToPostgreSql(dataType, column);

            if ((dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase) || dataType == "TIME") &&
                column.DateTimePrecision > 0)
                dataType = dataType + " (" + column.DateTimePrecision + ")";

            //write out datatype definition
            if ((dataType == "VARCHAR" || dataType == "CHAR") && column.Length > 0)
            {
                dataType = dataType + " (" + column.Length + ")";
            }

            if (dataType == "NUMERIC" || dataType == "DECIMAL")
            {
                var scale = column.Scale;
                var precision = column.Precision;

                var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
                if (precision > 0) //Postgresql can have no specified precision
                    dataType = dataType + " (" + precision + writeScale + ")";
            }

            return dataType;
        }

        protected override string WriteDataType(DatabaseColumn column)
        {
            var type = new DataTypeWriter().WriteDataType(column);
            type += (!column.Nullable ? " NOT NULL" : string.Empty);

            var defaultValue = column.DefaultValue;
            if (!string.IsNullOrEmpty(defaultValue))
            {
                defaultValue = FixDefaultValue(defaultValue);
                const string defaultConstraint = " DEFAULT ";

                var dbDataType = column.DbDataType.ToUpperInvariant();
                if (DataTypeConverter.IsVariableString(dbDataType))
                {
                    type += defaultConstraint + "'" + defaultValue + "'";
                }
                else //numeric default
                {
                    type += defaultConstraint + defaultValue;
                }
            }

            //cannot detect the "GENERATED BY DEFAULT" vs "GENERATED ALWAYS" version?
            if (column.IsAutoNumber) type += " GENERATED BY DEFAULT AS IDENTITY";
            if (column.IsPrimaryKey && Table.PrimaryKey.Columns.Count == 1)
                type += " PRIMARY KEY";

            return type;
        }

        public void GivenAlteredColumn()
        {
            //arrange
            DatabaseSchema schema1 = CreateSchema();
            var productsTable = CreateProductsTable();
            schema1.Tables.Add(productsTable);
            var nameColumn = new DatabaseColumn { Name = "Name", DbDataType = "NVARCHAR", Length = 10, Nullable = false };
            productsTable.Columns.Add(nameColumn);

            DatabaseSchema schema2 = CreateSchema();
            var productsTable2 = CreateProductsTable();
            var nameColumn2 = new DatabaseColumn { Name = "Name", DbDataType = "NVARCHAR", Length = 20, Nullable = true };
            productsTable2.Columns.Add(nameColumn2);
            schema2.Tables.Add(productsTable2);

            //act
            var comparison = new CompareSchemas(schema1, schema2);
            var result = comparison.ExecuteResult();

            //assert
            var alterColumn = result.FirstOrDefault(x =>
                                             x.ResultType == ResultType.Change &&
                                             x.SchemaObjectType == SchemaObjectType.Column &&
                                             x.TableName == "Products" &&
                                             x.Name == "Name");
            Assert.IsNotNull(alterColumn);
        }

 public virtual string AddColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
 {
     var tableGenerator = CreateTableGenerator(databaseTable);
     var addColumn = tableGenerator.WriteColumn(databaseColumn).Trim();
     if (string.IsNullOrEmpty(databaseColumn.DefaultValue) && !databaseColumn.Nullable)
     {
         var dt = databaseColumn.DataType;
         if (dt == null || dt.IsString)
         {
             addColumn += " DEFAULT ''"; //empty string
         }
         else if (dt.IsNumeric)
         {
             addColumn += " DEFAULT 0";
         }
         else if (dt.IsDateTime)
         {
             addColumn += " DEFAULT CURRENT_TIMESTAMP";
         }
         //make sure the NOT NULL is AFTER the default
         addColumn = addColumn.Replace(" NOT NULL ", " ") + " NOT NULL";
     }
     return string.Format(CultureInfo.InvariantCulture,
         "ALTER TABLE {0} ADD {1}",
         TableName(databaseTable),
         addColumn) + LineEnding();
 }

 public override string DropColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
 {
     return string.Format(CultureInfo.InvariantCulture,
         "ALTER TABLE {0} DROP COLUMN {1} CASCADE;",
         TableName(databaseTable),
         Escape(databaseColumn.Name));
 }

 private static int GetProviderType(DatabaseColumn column)
 {
     int providerType = -1;
     if (column.DataType != null)
         providerType = column.DataType.ProviderDbType;
     return providerType;
 }

        public override string AlterColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn, DatabaseColumn originalColumn)
        {
            var sb = new StringBuilder();
            var defaultName = "DF_" + databaseTable.Name + "_" + databaseColumn.Name;
            if (originalColumn != null)
            {
                if (originalColumn.DefaultValue != null)
                {
                    //have to drop default contraint
                    var df = FindDefaultConstraint(databaseTable, databaseColumn.Name);
                    if (df != null)
                    {
                        defaultName = df.Name;
                        sb.AppendLine("ALTER TABLE " + TableName(databaseTable)
                                      + " DROP CONSTRAINT " + Escape(defaultName) + ";");
                    }
                }
            }
            //we could check if any of the properties are changed here
            sb.AppendLine(base.AlterColumn(databaseTable, databaseColumn, originalColumn));
            if (databaseColumn.DefaultValue != null)
            {
                //add default contraint
                sb.AppendLine("ALTER TABLE " + TableName(databaseTable) +
                    " ADD CONSTRAINT " + Escape(defaultName) +
                    " DEFAULT " + databaseColumn.DefaultValue +
                    " FOR " + Escape(databaseColumn.Name) + ";");
            }

            return sb.ToString();
        }

 private static bool IsStringColumn(DatabaseColumn column)
 {
     var dataType = column.DbDataType.ToUpperInvariant();
     var isString = (dataType == "NVARCHAR" || dataType == "VARCHAR" || dataType == "CHAR");
     var dt = column.DataType;
     if (dt != null && dt.IsString) isString = true;
     return isString;
 }

        private void ConvertDataTable()
        {
            var columnsKeyMap = new ColumnsKeyMap(ColumnsDataTable);
            var hasIsUnsigned = !string.IsNullOrEmpty(columnsKeyMap.IsUnsignedKey);

            foreach (DataRowView row in ColumnsDataTable.DefaultView)
            {
                var column = new DatabaseColumn();
                column.Name = row[columnsKeyMap.Key].ToString();
                column.TableName = row[columnsKeyMap.TableKey].ToString();

                if (!string.IsNullOrEmpty(columnsKeyMap.SchemaKey))
                    column.SchemaOwner = row[columnsKeyMap.SchemaKey].ToString();
                if (string.Equals("sqlite_default_schema", column.SchemaOwner, StringComparison.OrdinalIgnoreCase))
                    column.SchemaOwner = string.Empty;

                if (!string.IsNullOrEmpty(columnsKeyMap.OrdinalKey))
                    column.Ordinal = Convert.ToInt32(row[columnsKeyMap.OrdinalKey], CultureInfo.CurrentCulture);
                if (!string.IsNullOrEmpty(columnsKeyMap.DatatypeKey))
                    column.DbDataType = row[columnsKeyMap.DatatypeKey].ToString();
                if (hasIsUnsigned && CastToBoolean(row, columnsKeyMap.IsUnsignedKey))
                    column.DbDataType += " unsigned";

                AddNullability(row, columnsKeyMap.NullableKey, column);
                //the length unless it's an OleDb blob or clob
                if (!string.IsNullOrEmpty(columnsKeyMap.LengthKey))
                    column.Length = GetNullableInt(row[columnsKeyMap.LengthKey]);
                if (!string.IsNullOrEmpty(columnsKeyMap.DataLengthKey))
                {
                    //oracle only
                    var dataLength = GetNullableInt(row[columnsKeyMap.DataLengthKey]);
                    //column length already set for char/varchar. For other data types, get data length
                    if (column.Length < 1)
                        column.Length = dataLength;
                }
                if (!string.IsNullOrEmpty(columnsKeyMap.PrecisionKey))
                    column.Precision = GetNullableInt(row[columnsKeyMap.PrecisionKey]);
                if (!string.IsNullOrEmpty(columnsKeyMap.ScaleKey))
                    column.Scale = GetNullableInt(row[columnsKeyMap.ScaleKey]);
                if (columnsKeyMap.DateTimePrecision != null)
                {
                    column.DateTimePrecision = GetNullableInt(row[columnsKeyMap.DateTimePrecision]);
                }

                AddColumnDefault(row, columnsKeyMap.DefaultKey, column);
                if (!string.IsNullOrEmpty(columnsKeyMap.PrimaryKeyKey) && (bool)row[columnsKeyMap.PrimaryKeyKey])
                    column.IsPrimaryKey = true;
                if (!string.IsNullOrEmpty(columnsKeyMap.AutoIncrementKey) && (bool)row[columnsKeyMap.AutoIncrementKey])
                    column.IsAutoNumber = true;
                if (!string.IsNullOrEmpty(columnsKeyMap.UniqueKey) && CastToBoolean(row, columnsKeyMap.UniqueKey))
                    column.IsUniqueKey = true;

                _list.Add(column);
            }

            // Sort columns according to ordinal to get the original order in CREATE TABLE
            _list.Sort((x, y) => x.Ordinal.CompareTo(y.Ordinal));
        }

        private static string OtherDatabaseTypesToPostgreSql(string dataType, DatabaseColumn column)
        {
            //string types
            //character(n) (aka char(n)) character varying(n) aka varchar(n) and text
            if (DataTypeConverter.IsFixedLengthString(dataType))
            {
                return "CHAR";
            }
            if (DataTypeConverter.IsLongString(dataType))
            {
                return "TEXT";
            }
            if (DataTypeConverter.IsVariableString(dataType))
            {
                if (column.Length == -1) return "TEXT";
                return "VARCHAR";
            }

            //numeric types
            if (dataType == "INT") return "INTEGER";
            if (dataType == "INT4") return "INTEGER"; //this is a PostgreSql alias, we'll use standard SQL
            //else if (dataType == "SERIAL") return "INTEGER"; //this is a PostgreSql alias, we'll use standard SQL
            //else if (dataType == "BIGSERIAL") return "BIGINT"; //this is a PostgreSql alias, we'll use standard SQL
            if (dataType == "INT8") return "BIGINT"; //this is a PostgreSql alias, we'll use standard SQL
            if (dataType == "INT2") return "SMALLINT"; //this is a PostgreSql alias, we'll use standard SQL
            if (dataType == "TINYINT") return "SMALLINT"; //this is a MsSql alias, we'll use standard SQL
            if (dataType == "NUMBER")
                return DataTypeConverter.OracleNumberConversion(column.Precision, column.Scale);

            //float and real
            if (dataType == "FLOAT4") return "REAL"; //this is a PostgreSql alias, we'll use standard SQL
            if (dataType == "FLOAT") return "DOUBLE PRECISION";

            //date times
            //SqlServer Timestamp is a binary
            if (DataTypeConverter.IsSqlServerTimestamp(dataType, column))
                return "BYTEA"; //this is just a byte array- functionally you should redesign the table and perhaps use the system extension columns

            if (DataTypeConverter.IsDateTime(dataType))
                return "TIMESTAMP";

            //bytes
            if (DataTypeConverter.IsBlob(dataType, column.Length))
                return "OID";//blobs become object ids
            if (DataTypeConverter.IsBinary(dataType))
            {
                return "BYTEA";
            }

            //there is a native BIT(n) type in Postgresql, but in conversion we probably mean boolean.
            if (dataType == "BIT" && !column.Length.HasValue) return "BOOLEAN";

            //other types
            if (dataType == "XMLTYPE") return "XML";
            if (dataType == "UNIQUEIDENTIFIER") return "UUID";
            return dataType;
        }

 public static bool IsString(DatabaseColumn column, string dataType)
 {
     //all aliases for CHAR and VARCHAR. there's also a LONG VARCHAR
     if (DataTypeConverter.IsVariableString(dataType) ||
         DataTypeConverter.IsFixedLengthString(dataType))
         return true;
     if (column.DataType == null) return false;
     return column.DataType.IsString;
 }

 public static bool IsSqlServerTimestamp(string dataType, DatabaseColumn column)
 {
     if (!dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase))
         return false;
     int providerType = -1;
     if (column.DataType != null)
         providerType = column.DataType.ProviderDbType;
     return (providerType == (int)SqlDbType.Timestamp); //this is just a byte array
 }

 /// <summary>
 /// If a table has a trigger, we assume it's an Oracle trigger/sequence which is translated to identity for the primary key
 /// </summary>
 /// <param name="table">The table.</param>
 /// <param name="column">The column.</param>
 /// <returns></returns>
 public static bool LooksLikeOracleIdentityColumn(DatabaseTable table, DatabaseColumn column)
 {
     if (!column.IsPrimaryKey) return false;
     if (table.Triggers.Count == 0) return false;
     //is there a trigger body which looks like it's using a sequence?
     //if there's a sequence there, it's autogenerating a column - we assume the primary key!
     return table.Triggers.Any(t => t.TriggerBody
                                        .ToUpperInvariant()
                                        .Contains(".NEXTVAL "));
 }