Migrator Got the sqlite provider passing all tests, working on tests now.
Changeset 581e8846c09a…
Parent ed2e87f1c1d8…
by
Changes to 10 files · Browse files at 581e8846c09a Showing diff from parent ed2e87f1c1d8 Diff from another changeset...
-
app/core/Migrator.csproj
-
app/core/Providers/MySqlTransformationProvider.cs
-
app/core/Providers/PostgreSQLTransformationProvider.cs
-
app/core/Providers/SqlServerTransformationProvider.cs
-
app/core/Providers/TransformationProvider.cs
-
test/Migrator.Tests.csproj
-
test/MigratorTest.cs
-
test/Providers/SqlServerTransformationProviderTest.cs
-
test/Providers/TransformationProviderBase.cs
-
test/Providers/TransformationProviderTestAdapter.cs
|
@@ -54,6 +54,7 @@ <Reference Include="System.Data.SQLite.Linq, Version=2.0.38.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=MSIL">
<HintPath>..\..\lib\System.Data.SQLite.Linq.dll</HintPath>
</Reference>
+ <Reference Include="System.Web" />
<Reference Include="System.Xml" />
<Reference Include="log4net">
<HintPath>..\..\lib\log4net.dll</HintPath>
@@ -76,6 +77,8 @@ <ItemGroup>
<Compile Include="AssemblyInfo.cs" />
<Compile Include="Column.cs" />
+ <Compile Include="Table.cs" />
+ <Compile Include="ForeignKey.cs" />
<Compile Include="DuplicatedVersionException.cs" />
<Compile Include="IrreversibleMigrationException.cs" />
<Compile Include="Loggers\ConsoleLogger.cs" />
@@ -97,6 +100,7 @@ <Compile Include="Providers\ProviderFactory.cs" />
<Compile Include="Providers\SqlServerTransformationProvider.cs" />
<Compile Include="Providers\TransformationProvider.cs" />
+ <Compile Include="FormatterExtensions.cs" />
<Compile Include="Tools\SchemaDumper.cs" />
</ItemGroup>
<ItemGroup>
|
||
|
@@ -56,6 +56,11 @@ ExecuteNonQuery(string.Format("CREATE TABLE {0} ({1}) ENGINE = INNODB", name, string.Join(", ", sqlColumns)));
}
+ public override void AddTable(Table table)
+ {
+ throw new NotImplementedException();
+ }
+
public override void RemoveTable(string name)
{
if (TableExists(name))
@@ -228,6 +233,16 @@ return (Column[])columns.ToArray(typeof(Column));
}
+ public override Table GetTable(String table)
+ {
+ throw new NotImplementedException();
+ }
+
+ public override ForeignKey[] GetForeignKeys(String table)
+ {
+ throw new NotImplementedException();
+ }
+
#region Helper methods
private string Quote(string text)
@@ -237,7 +252,7 @@
private string GetSQLForColumn(Column col)
{
- string sqlType = ToSqlType(col.Type, col.Size);
+ string sqlType = ToTextType(col.Type, col.Size);
string sqlNull = col.ColumnProperty == ColumnProperties.Null ? "NULL" : "NOT NULL";
string sqlDefault = "";
string sqlIdentity = "";
@@ -261,7 +276,7 @@ return string.Join(" ", new string[] { col.Name, sqlType, sqlIdentity, sqlNull, sqlDefault });
}
- private string ToSqlType(Type type, int size)
+ private string ToTextType(Type type, int size)
{
if (type == typeof(string))
if (size <= 255)
|
||
|
@@ -56,6 +56,11 @@ AddPrimaryKey(string.Format("PK_{0}", name), name, (string[])pk.ToArray(typeof(string)));
}
+ public override void AddTable(Table table)
+ {
+ throw new NotImplementedException();
+ }
+
public override void RemoveTable(string name)
{
if (TableExists(name))
@@ -226,11 +231,21 @@ throw new NotImplementedException();
}
+ public override Table GetTable(String table)
+ {
+ throw new NotImplementedException();
+ }
+
+ public override ForeignKey[] GetForeignKeys(String table)
+ {
+ throw new NotImplementedException();
+ }
+
#region Helper methods
private string GetSQLForColumn(Column col)
{
- string sqlType = ToSqlType(col.Type, col.Size);
+ string sqlType = ToTextType(col.Type, col.Size);
string sqlNull = col.ColumnProperty == ColumnProperties.Null ? "NULL" : "NOT NULL";
string sqlDefault = "";
string sqlIdentity = "";
@@ -254,7 +269,7 @@ return string.Join(" ", new string[] { string.Format("\"{0}\"", col.Name.ToLower()), sqlType, sqlIdentity, sqlNull, sqlDefault });
}
- private string ToSqlType(Type type, int size)
+ private string ToTextType(Type type, int size)
{
if (type == typeof(string))
if (size <= 255)
|
||
|
|
|
@@ -31,26 +31,6 @@ Connection.Open();
}
- public override void AddTable(string name, params Column[] columns)
- {
- if (TableExists(name))
- {
- Logger.Warn("The table {0} already exists", name);
- return;
- }
-
- string[] sqlColumns = new string[columns.Length];
- int i = 0;
-
- foreach (Column col in columns)
- {
- sqlColumns[i] = GetSQLForColumn(col);
- i++;
- }
-
- ExecuteNonQuery(string.Format("CREATE TABLE {0}{1} ({2})", OWNER, name, string.Join(", ", sqlColumns)));
- }
-
public override void RemoveTable(string name)
{
if (TableExists(name))
@@ -123,11 +103,11 @@ {
// if making a column not null, all null values need to be assigned the default value first
if (props == ColumnProperties.NotNull)
- this.ExecuteNonQuery("UPDATE {0} SET {1} = {2} WHERE {1} IS NULL", table, column, this.ToSqlValue(dataType, defaultValue));
+ this.ExecuteNonQuery("UPDATE {0} SET {1} = {2} WHERE {1} IS NULL", table, column, this.ToValue(dataType, defaultValue));
//
string nullability = props == ColumnProperties.Null ? "Null" : "Not Null";
- this.ExecuteNonQuery("Alter Table {0} Alter Column {1} {2} {3}", table, column, this.ToSqlType(dataType, size), nullability);
+ this.ExecuteNonQuery("Alter Table {0} Alter Column {1} {2} {3}", table, column, this.ToTextType(dataType, size), nullability);
}
}
@@ -163,13 +143,13 @@ return;
}
- string sqlColumn = GetSQLForColumn(new Column(column, type, size, property, defaultValue));
+ string sqlColumn = GetTextFor(new Column(column, type, size, property, defaultValue));
ExecuteNonQuery(string.Format("ALTER TABLE {0} ADD {1}", table, sqlColumn));
}
public override void AlterColumn(string table, string column, Type type, int size)
{
- string sqlColumn = GetSQLForColumn(new Column(column, type, size));
+ string sqlColumn = GetTextFor(new Column(column, type, size));
ExecuteNonQuery(string.Format("ALTER TABLE {0} ALTER COLUMN {1}", table, sqlColumn));
}
@@ -196,97 +176,6 @@ }
}
- #region ConvertColumn
- public override bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc)
- {
- ConvertColumnValidation(table, column, tempColumn);
- this.AddColumn(table, tempColumn, typeof(U));
- return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
- }
-
- public override bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size)
- {
- ConvertColumnValidation(table, column, tempColumn);
- this.AddColumn(table, tempColumn, typeof(U), size);
- return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
- }
-
- public override bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size, ColumnProperties property)
- {
- ConvertColumnValidation(table, column, tempColumn);
- this.AddColumn(table, tempColumn, typeof(U), size, property);
- return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
- }
-
- public override bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, ColumnProperties property)
- {
- ConvertColumnValidation(table, column, tempColumn);
- this.AddColumn(table, tempColumn, typeof(U), property);
- return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
- }
-
- public override bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size, object defaultValue, ColumnProperties property)
- {
- ConvertColumnValidation(table, column, tempColumn);
- this.AddColumn(table, tempColumn, typeof(U), size, property, defaultValue);
- return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
- }
-
- private void ConvertColumnValidation(String table, String column, String tempColumn)
- {
- if (!TableExists(table) || !ColumnExists(table, column) || ColumnExists(table, tempColumn))
- throw new ArgumentException();
- }
-
- private bool DoConvertColumn<T, U>(string table, string column, string tempColumnName, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc)
- {
- Hashtable data = new Hashtable();
- using (IDataReader reader = this.ExecuteQuery(String.Format("SELECT * FROM {0}", table)))
- {
- // Read in and convert all values
- if (reader != null)
- {
- while (reader.Read())
- {
- if (!(reader[column] is DBNull))
- {
- if (!this.CacheConvertedValues || !data.Contains(reader[column]))
- {
- U value = convertFunc.Invoke((T)reader[column], reader);
- data[reader[column]] = value;
- }
- }
- }
- }
- }
-
- try
- {
- // Deal with differences in SQL syntax when working with strings.
- String equalityOp = typeof(T) == typeof(String) ? "LIKE" : "=";
- String insertionSQLTemplate = String.Format("UPDATE {0} SET {1} = {2} WHERE {3} {5} {4}", table, tempColumnName, "{0}", column, "{1}", equalityOp);
-
- // Set values in new table.
- foreach (T key in data.Keys)
- {
- this.ExecuteNonQuery(String.Format(insertionSQLTemplate, this.ToSqlValue(typeof(U), data[key]), this.ToSqlValue(typeof(T), key)));
- }
-
- // Remove old column
- this.RemoveColumn(table, column);
- // Rename new column to old name
- this.RenameColumn(table, tempColumnName, column);
- return true;
- }
- catch (Exception ex)
- {
- if (ColumnExists(table, tempColumnName))
- this.RemoveColumn(table, tempColumnName);
- throw;
- }
- }
- #endregion
-
// Deletes all constraints linked to a column. Sql Server
// doesn't seems to do this.
private void DeleteColumnConstraints(string table, string column)
@@ -326,6 +215,7 @@ }
}
+
public override string[] GetTables()
{
ArrayList tables = new ArrayList();
@@ -341,13 +231,13 @@ return (string[])tables.ToArray(typeof(string));
}
- public override Column[] GetColumns(string table)
+ public override Column[] GetColumns(string tableName)
{
ArrayList columns = new ArrayList();
- var pkey = ColumnsWithConstraint(table, "PRIMARY KEY");
+ var pkey = ColumnsWithConstraint(tableName, "PRIMARY KEY");
- using (IDataReader reader = ExecuteQuery(string.Format("select * from information_schema.columns where table_name = '{0}';", table)))
+ using (IDataReader reader = ExecuteQuery(string.Format("select * from information_schema.columns where table_name = '{0}';", tableName)))
{
while (reader.Read())
{
@@ -356,7 +246,7 @@ var col_default = reader["COLUMN_DEFAULT"].ToString();
var col_type = reader["DATA_TYPE"].ToString();
int? max_length = reader["CHARACTER_MAXIMUM_LENGTH"] as int?;
- Type actualType = FromSqlType(col_type);
+ Type actualType = TypeFromTypeDefinition(col_type);
var colProperties = isNullable ? ColumnProperties.Null : ColumnProperties.NotNull;
if (pkey.Contains(name))
@@ -368,6 +258,68 @@ return (Column[])columns.ToArray(typeof(Column));
}
+ public override Table GetTable(String tableName)
+ {
+ if (!TableExists(tableName)) { return null; }
+
+ Table table = new Table(tableName);
+ table.Columns.AddRange(GetColumns(tableName));
+ table.ForeignKeys.AddRange(GetForeignKeys(tableName));
+ return table;
+ }
+
+ public override ForeignKey[] GetForeignKeys(String tableName)
+ {
+ var foreignKeys= new List<ForeignKey>();
+ if (!TableExists(tableName)) { return foreignKeys.ToArray(); }
+ var QuerySQL = @" -- http://sqlserver2000.databases.aspfaq.com/schema-how-do-i-find-all-the-foreign-keys-in-a-database.html
+ SELECT
+ FK_Table = FK.TABLE_NAME,
+ FK_Column = CU.COLUMN_NAME,
+ PK_Table = PK.TABLE_NAME,
+ PK_Column = PT.COLUMN_NAME,
+ Constraint_Name = C.CONSTRAINT_NAME
+ FROM
+ INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
+ INNER JOIN
+ INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
+ ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
+ INNER JOIN
+ INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
+ ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
+ INNER JOIN
+ INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
+ ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
+ INNER JOIN
+ (
+ SELECT
+ i1.TABLE_NAME, i2.COLUMN_NAME
+ FROM
+ INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
+ INNER JOIN
+ INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
+ ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
+ WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
+ ) PT
+ ON PT.TABLE_NAME = PK.TABLE_NAME
+ -- optional:
+ WHERE FK.TABLE_NAME='{0}'
+ ORDER BY
+ 1,2,3,4
+ ";
+
+ using (IDataReader reader = ExecuteQuery(string.Format(QuerySQL, tableName)))
+ {
+ while (reader.Read())
+ {
+ ForeignKey key = new ForeignKey(reader["Constraint_Name"].ToString(), reader["FK_Column"].ToString(), reader["PK_Table"].ToString(), reader["PK_Column"].ToString());
+ foreignKeys.Add(key);
+ }
+ }
+
+ return foreignKeys.ToArray();
+ }
+
private List<String> ColumnsWithConstraint(String table, String constraintType)
{
List<String> columns = new List<string>();
@@ -398,7 +350,7 @@ List<string> sqlValues = new List<string>();
foreach (var value in values)
{
- sqlValues.Add(this.ToSqlValue(value.GetType(), value));
+ sqlValues.Add(this.ToValue(value.GetType(), value));
}
return ExecuteNonQuery(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, string.Join(", ", columns), string.Join(", ", sqlValues.ToArray())));
@@ -458,201 +410,6 @@ }
#endregion
- #region Helper methods
- private string GetSQLForColumn(Column col)
- {
- // Force boolean values to have a default
- if (col.Type == typeof(bool) && col.ColumnProperty != ColumnProperties.Null && col.DefaultValue == null)
- col.DefaultValue = false;
-
- string sqlDefaultKeyword = "DEFAULT ";
- string sqlDefault = "";
-
- // assign default values, ignore defaults if they have been overloaded for PK/Identity purposes
- if (col.DefaultValue != null)
- sqlDefault = sqlDefaultKeyword + ToSqlValue(col.Type, col.DefaultValue);
-
- string sqlIdentity = "";
- string sqlConstraint = "";
- switch (col.ColumnProperty)
- {
- case ColumnProperties.Null:
- sqlConstraint = "NULL";
- break;
- case ColumnProperties.NotNull:
- sqlConstraint = "NOT NULL";
- break;
- // means a unique ID or a uniquely generated GUID
- case ColumnProperties.Identity:
- sqlConstraint = "NOT NULL"; // Identity columns must also be Not Null
- if (col.Type == typeof(Guid))
- sqlDefault = sqlDefaultKeyword + "NEWID()";
- else
- sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
- break;
- case ColumnProperties.PrimaryKey:
- sqlConstraint = "PRIMARY KEY";
- break;
- // means a unique ID or a uniquely generated GUID as the PK
- case ColumnProperties.PrimaryKeyWithIdentity:
- sqlConstraint = "PRIMARY KEY";
- if (col.Type == typeof(Guid))
- sqlDefault = sqlDefaultKeyword + "NEWID()";
- else
- sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
- break;
- case ColumnProperties.UNIQUE:
- sqlConstraint = "UNIQUE";
- break;
- }
-
- string sqlType = ToSqlType(col.Type, col.Size);
- return string.Join(" ", new string[] { col.Name, sqlType, sqlDefault, sqlIdentity, sqlConstraint });
- }
-
-
-
- /// <summary>
- /// Gets the SQL for a value
- /// </summary>
- /// <param name="type"></param>
- /// <param name="value"></param>
- /// <returns></returns>
- private string ToSqlValue(Type type, object value)
- {
- if (value is SpecialDefaults)
- {
- switch ((SpecialDefaults)value)
- {
- case SpecialDefaults.NewId:
- default:
- return "newid()";
- }
- }
-
- if (type == typeof(string) || (type == typeof(char)) || (type == typeof(DateTime)) || type == typeof(Guid) || type == typeof(Guid?))
- {
- if (value == null)
- return "null";
- return String.Format("'{0}'", value.ToString());
- }
- else if (type == typeof(int) || type == typeof(Int64) || type == typeof(long) || type == typeof(float) || type == typeof(double) || type == typeof(decimal))
- {
- return value.ToString();
- }
- else if (type == typeof(bool))
- {
- Boolean bit = (Boolean)value;
- return bit ? "1" : "0";
- }
- else if (type == typeof(DateTime))
- {
- return ((DateTime)value).ToString("'yyy-MM-dd HH:mm:ss'");
- }
- else if (type == typeof(TimeSpan))
- {
- return ((TimeSpan)value).TotalMilliseconds.ToString();
- }
- else
- {
- throw new NotSupportedException("Type not supported : " + type.Name);
- }
- }
-
- /// <summary>
- /// Gets the SQL for a given system type
- /// </summary>
- /// <param name="type"></param>
- /// <param name="size"></param>
- /// <returns></returns>
- private string ToSqlType(Type type, int size)
- {
- if (type == typeof(string))
- {
- if (size > 0 && size <= 4000) // varchar and nvarchar are limited to 4000 characters in SQL Server
- // Nvarchar is used by active record and allows for internationalized text
- return string.Format("nvarchar({0})", size);
- else
- return "ntext";
- }
- else if (type == typeof(int))
- {
- if (size >= 8)
- return "bigint";
- else
- return "int";
- }
- else if (type == typeof(Int64) || type == typeof(long))
- {
- return "bigint";
- }
- else if (type == typeof(float) || type == typeof(double))
- {
- if (size == 0)
- return "real";
- else
- return string.Format("float({0})", size);
- }
- else if (type == typeof(bool))
- {
- return "bit";
- }
- else if (type == typeof(DateTime))
- {
- return "datetime";
- }
- else if (type == typeof(char))
- {
- return string.Format("nchar({0})", size);
- }
- else if (type == typeof(Guid) || type == typeof(Guid?))
- {
- return "uniqueidentifier";
- }
- else if (type == typeof(byte[]))
- {
- return "image";
- }
- else if (type == typeof(TimeSpan))
- {
- return "bigint";
- }
- else
- {
- throw new NotSupportedException("Type not supported : " + type.Name);
- }
- }
-
-
- private Dictionary<string, Type> _type_conversion_map = new Dictionary<string, Type> {
- {"ntext", typeof(string)},
- {"text", typeof(string)},
- {"nvarchar", typeof(string)},
- {"varchar", typeof(string)},
- {"int", typeof(int)},
- {"bigint", typeof(long)},
- {"real", typeof(float)},
- {"bit", typeof(bool)},
- {"datetime", typeof(DateTime)},
- {"nchar", typeof(char)},
- {"uniqueidentifier", typeof(Guid)},
- {"image", typeof(byte[])},
- };
-
- /// <summary>
- /// Gets the .net type from an SQL type
- /// </summary>
- /// <param name="type"></param>
- /// <returns></returns>
- private Type FromSqlType(String type)
- {
- if (_type_conversion_map.ContainsKey(type))
- return _type_conversion_map[type];
-
- throw new ArgumentException();
- }
- #endregion
-
public override void AddUniqueConstraint(string constraint, string table, params string[] columns)
{
if (ConstraintExists(constraint, table))
|
|
|
|
@@ -22,6 +22,10 @@ using System;
using System.Data;
using System.Collections.Generic;
+using System.Text.RegularExpressions;
+using System.Linq;
+using System.Text;
+using System.Collections;
#endregion
namespace Migrator.Providers
@@ -86,7 +90,72 @@ }
#endregion
- #region Add Column/Table/PK/FK/Constraint
+ #region Tables
+ /// <summary>
+ /// Add a new table
+ /// </summary>
+ /// <param name="name">Table name</param>
+ /// <param name="columns">Columns</param>
+ /// <example>
+ /// Adds the Test table with two columns:
+ /// <code>
+ /// Database.AddTable("Test",
+ /// new Column("Id", typeof(int), ColumnProperties.PrimaryKey),
+ /// new Column("Title", typeof(string), 100)
+ /// );
+ /// </code>
+ /// </example>
+ public virtual void AddTable(string name, params Column[] columns)
+ {
+ AddTable(new Table(name, columns));
+ }
+
+ /// <summary>
+ /// Adds a new table.
+ /// </summary>
+ /// <param name="table">The object representation of a table definition.</param>
+ public virtual void AddTable(Table table)
+ {
+ if (TableExists(table.Name))
+ {
+ Logger.Warn("The table {0} already exists", table.Name);
+ return;
+ }
+
+ string columnSQL = String.Join(", ", table.Columns.Select(column => GetTextFor(column)));
+
+ string fkSQL = String.Join(", ", table.ForeignKeys.Select(fk => GetTextFor(fk)));
+
+ ExecuteNonQuery(string.Format("CREATE TABLE {0} ({1}{2})", table.Name, columnSQL, fkSQL.Trim().Equals(String.Empty) ? "" : ", " + fkSQL));
+
+ }
+
+ /// <summary>
+ /// Remove a table from the database.
+ /// </summary>
+ /// <param name="name">Table name</param>
+ public abstract void RemoveTable(string name);
+
+ /// <summary>
+ /// Rename a Table
+ /// </summary>
+ /// <param name="table">Name of the table to be renames</param>
+ /// <param name="newName">New name of the table</param>
+ public abstract void RenameTable(string table, string newName);
+
+ public abstract Table GetTable(string tableName);
+
+ public abstract string[] GetTables();
+
+ /// <summary>
+ /// Determines if a table exists.
+ /// </summary>
+ /// <param name="table">Table name</param>
+ /// <returns><c>true</c> if the table exists</returns>
+ public abstract bool TableExists(string table);
+ #endregion
+
+ #region Columns
/// <summary>
/// Add a new column to an existing table.
/// </summary>
@@ -156,10 +225,8 @@ /// </summary>
/// <param name="table">Table Name</param>
/// <param name="column">Column Name to alter</param>
- /// <param name="dataType">new typw of the Column</param>
+ /// <param name="type">new type of the Column</param>
/// <param name="size">new size of the Column</param>
- /// <param name="props">New Column Properties</param>
- /// <param name="defaultValue">new Default Value</param>
public abstract void AlterColumn(string table, string column, Type type, int size);
/// <summary>
@@ -189,29 +256,140 @@ }
/// <summary>
+ /// Determines of a column exists.
+ /// </summary>
+ /// <param name="table">Table name</param>
+ /// <param name="column">Column name</param>
+ /// <returns><c>true</c> if the column exists</returns>
+ public abstract bool ColumnExists(string table, string column);
+
+ public virtual Column[] GetColumns(string tableName)
+ {
+ return GetTable(tableName).Columns.ToArray();
+ }
+
+ /// <summary>
+ /// Removes a column from a table
+ /// </summary>
+ /// <param name="table">table containing the column</param>
+ /// <param name="column">column name</param>
+ public abstract void RemoveColumn(string table, string column);
+
+ /// <summary>
+ /// Rename a Column
+ /// </summary>
+ /// <param name="table">Table containing the Column to be Renames</param>
+ /// <param name="column">Column to be renamed</param>
+ /// <param name="newName">New name of the column</param>
+ public abstract void RenameColumn(string table, string column, string newName);
+
+ #region ConvertColumn
+ public virtual bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc)
+ {
+ ConvertColumnValidation(table, column, tempColumn);
+ this.AddColumn(table, tempColumn, typeof(U));
+ return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
+ }
+
+ public virtual bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size)
+ {
+ ConvertColumnValidation(table, column, tempColumn);
+ this.AddColumn(table, tempColumn, typeof(U), size);
+ return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
+ }
+
+ public virtual bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size, ColumnProperties property)
+ {
+ ConvertColumnValidation(table, column, tempColumn);
+ this.AddColumn(table, tempColumn, typeof(U), size, property);
+ return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
+ }
+
+ public virtual bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, ColumnProperties property)
+ {
+ ConvertColumnValidation(table, column, tempColumn);
+ this.AddColumn(table, tempColumn, typeof(U), property);
+ return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
+ }
+
+ public virtual bool ConvertColumn<T, U>(string table, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size, object defaultValue, ColumnProperties property)
+ {
+ ConvertColumnValidation(table, column, tempColumn);
+ this.AddColumn(table, tempColumn, typeof(U), size, property, defaultValue);
+ return this.DoConvertColumn<T, U>(table, column, tempColumn, convertFunc);
+ }
+
+ private void ConvertColumnValidation(String table, String column, String tempColumn)
+ {
+ if (!TableExists(table) || !ColumnExists(table, column) || ColumnExists(table, tempColumn))
+ throw new ArgumentException();
+ }
+
+ private bool DoConvertColumn<T, U>(string table, string column, string tempColumnName, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc)
+ {
+ Hashtable data = new Hashtable();
+ using (IDataReader reader = this.ExecuteQuery(String.Format("SELECT * FROM {0}", table)))
+ {
+ // Read in and convert all values
+ if (reader != null)
+ {
+ while (reader.Read())
+ {
+ if (!(reader[column] is DBNull))
+ {
+ if (!this.CacheConvertedValues || !data.Contains(reader[column]))
+ {
+ U value = convertFunc.Invoke((T)reader[column], reader);
+ data[reader[column]] = value;
+ }
+ }
+ }
+ }
+ }
+
+ try
+ {
+ // Deal with differences in SQL syntax when working with strings.
+ String equalityOp = typeof(T) == typeof(String) ? "LIKE" : "=";
+ String insertionSQLTemplate = String.Format("UPDATE {0} SET {1} = {2} WHERE {3} {5} {4}", table, tempColumnName, "{0}", column, "{1}", equalityOp);
+
+ // Set values in new table.
+ foreach (T key in data.Keys)
+ {
+ this.ExecuteNonQuery(String.Format(insertionSQLTemplate, this.ToValue(typeof(U), data[key]), this.ToValue(typeof(T), key)));
+ }
+
+ // Remove old column
+ this.RemoveColumn(table, column);
+ // Rename new column to old name
+ this.RenameColumn(table, tempColumnName, column);
+ return true;
+ }
+ catch (Exception ex)
+ {
+ System.Diagnostics.Debug.Write(ex.ToString());
+ if (ColumnExists(table, tempColumnName))
+ this.RemoveColumn(table, tempColumnName);
+ throw;
+ }
+ }
+ #endregion
+
+ #endregion
+
+ #region Constraints
+
+ #region Primary Keys
+ /// <summary>
/// Append a primary key to a table.
/// </summary>
/// <param name="name">Constraint name</param>
/// <param name="table">Table name</param>
/// <param name="columns">Primary column names</param>
public abstract void AddPrimaryKey(string name, string table, params string[] columns);
+ #endregion
- /// <summary>
- /// Add a new table
- /// </summary>
- /// <param name="name">Table name</param>
- /// <param name="columns">Columns</param>
- /// <example>
- /// Adds the Test table with two columns:
- /// <code>
- /// Database.AddTable("Test",
- /// new Column("Id", typeof(int), ColumnProperties.PrimaryKey),
- /// new Column("Title", typeof(string), 100)
- /// );
- /// </code>
- /// </example>
- public abstract void AddTable(string name, params Column[] columns);
-
+ #region Foreign Keys
/// <summary>
/// Append a foreign key (relation) between two tables.
/// </summary>
@@ -238,7 +416,6 @@ {
AddForeignKey(name, foreignKeyTable, new string[] { foreignKeyColumn }, entityTable, new string[] { primaryKeyColumn }, cascade);
}
-
/// <summary>
/// Append a foreign key (relation) between two tables.
/// </summary>
@@ -265,6 +442,19 @@ /// <param name="primaryKeyColumns">The Primary Key columns in the Entity table</param>
public abstract void AddForeignKey(string name, string foreignKeyTable, string[] foreignKeyColumns, string entityTableName, string[] primaryKeyColumns);
+ public virtual ForeignKey[] GetForeignKeys(string tableName)
+ {
+ return GetTable(tableName).ForeignKeys.ToArray();
+ }
+
+ /// <summary>
+ /// Removes a constraint.
+ /// </summary>
+ /// <param name="name">Constraint name</param>
+ /// <param name="table">Table owning the constraint</param>
+ public abstract void RemoveForeignKey(string name, string table);
+ #endregion
+
/// <summary>
/// Add unique constraint
/// </summary>
@@ -280,8 +470,17 @@ /// <param name="constraint">Name of the Constraint</param>
public abstract void RemoveConstraint(string constraint, string table);
+ /// <summary>
+ /// Determines if a constraint exists.
+ /// </summary>
+ /// <param name="name">Constraint name</param>
+ /// <param name="table">Table owning the constraint</param>
+ /// <returns><c>true</c> if the constraint exists.</returns>
+ public abstract bool ConstraintExists(string name, string table);
+
#endregion
+ #region Transaction Management
/// <summary>
/// Starts a transaction. Called by the migration mediator.
/// </summary>
@@ -295,46 +494,6 @@ }
/// <summary>
- /// Builds a command using the current transaction
- /// </summary>
- /// <param name="sql"></param>
- /// <returns></returns>
- public IDbCommand BuildCommand(string sql)
- {
- IDbCommand cmd = Connection.CreateCommand();
- cmd.CommandText = sql;
- cmd.CommandType = CommandType.Text;
- if (this.Timeout != null)
- cmd.CommandTimeout = this.Timeout.Value;
- BeginTransaction();
- if (_transaction != null)
- {
- cmd.Transaction = _transaction;
- }
- return cmd;
- }
-
- /// <summary>
- /// Determines of a column exists.
- /// </summary>
- /// <param name="table">Table name</param>
- /// <param name="column">Column name</param>
- /// <returns><c>true</c> if the column exists</returns>
- public abstract bool ColumnExists(string table, string column);
-
- #region Convert Column
- public abstract bool ConvertColumn<T, U>(string table, string column, string tempColumn, ConvertColumnDelegate<T, U> convertFunc);
-
- public abstract bool ConvertColumn<T, U>(string table, string column, string tempColumn, ConvertColumnDelegate<T, U> convertFunc, int size);
-
- public abstract bool ConvertColumn<T, U>(string table, string column, string tempColumn, ConvertColumnDelegate<T, U> convertFunc, int size, ColumnProperties property);
-
- public abstract bool ConvertColumn<T, U>(string table, string column, string tempColumn, ConvertColumnDelegate<T, U> convertFunc, ColumnProperties property);
-
- public abstract bool ConvertColumn<T, U>(string table, string column, string tempColumn, ConvertColumnDelegate<T, U> convertFunc, int size, object defaultValue, ColumnProperties property);
- #endregion
-
- /// <summary>
/// Commit the current transaction. Called by the migrations mediator.
/// </summary>
public void Commit()
@@ -347,6 +506,7 @@ }
catch (Exception ex)
{
+ System.Diagnostics.Debug.Write(ex.ToString());
this.Rollback();
throw;
}
@@ -355,13 +515,19 @@ }
/// <summary>
- /// Determines if a constraint exists.
+ /// Rollback the current migration. Called by the migration mediator.
/// </summary>
- /// <param name="name">Constraint name</param>
- /// <param name="table">Table owning the constraint</param>
- /// <returns><c>true</c> if the constraint exists.</returns>
- public abstract bool ConstraintExists(string name, string table);
+ public virtual void Rollback()
+ {
+ if (_transaction != null && Connection != null && Connection.State == ConnectionState.Open)
+ {
+ _transaction.Rollback();
+ }
+ _transaction = null;
+ }
+ #endregion
+ #region SchemaTable Management
/// <summary>
/// Creates the Schema Info Table
/// </summary>
@@ -397,34 +563,6 @@ if (!VersionRecordExists(AssemblyId))
this.InsertVersionRecord(AssemblyId);
}
-
- /// <summary>
- /// Copies rows from one table to another
- /// </summary>
- /// <param name="fromTable">The name of the table being copied from</param>
- /// <param name="toTable">The table being copied to...</param>
- /// <param name="fromColumns">The names of the columns being copied. These must be in the same order as the names of the matching <see cref="toColumns"/></param>
- /// <param name="toColumns">The names of the columns in the toTable that match the columns in the fromTable</param>
- /// <param name="fromWhere">Where statement that filters the from table.</param>
- public void CopyFromTable(string fromTable, string toTable, string[] fromColumns, string[] toColumns, string fromWhere)
- {
- if (fromColumns.Length != toColumns.Length)
- {
- throw new ArgumentOutOfRangeException("FromColumns and toColumns must have matching numbers.");
- }
-
- string sql = string.Format("Insert Into [{0}] ({1}) Select {2} From [{3}] {4} ",
- toTable,
- string.Join(", ", toColumns),
- string.Join(", ", fromColumns),
- fromTable,
- fromWhere.Length > 1 ? "Where " + fromWhere : "");
-
- this.Logger.Trace("Copying from table [{0}]", sql);
- this.ExecuteNonQuery(sql);
-
- }
-
/// <summary>
/// Get or set the current version of the database.
/// This determines if the migrator should migrate up or down
@@ -439,6 +577,70 @@ set { SetVersion(value, AssemblyId); }
}
+ /// <summary>
+ /// Get the version from the SchemaInfo Table
+ /// </summary>
+ /// <returns>The current version</returns>
+ protected virtual int GetVersion(String assemblyId)
+ {
+ if (assemblyId == null) return 0; // No schema info.
+ if (!TableExists(SCHEMA_INFO_TABLE)) return 0;
+
+ return (int)ExecuteScalar(String.Format("SELECT Version FROM {1} WHERE AssemblyId='{0}'", assemblyId, SCHEMA_INFO_TABLE));
+ }
+
+ /// <summary>
+ /// Set the current version in the SchemaInfo Table
+ /// </summary>
+ /// <param name="version">the new version to set</param>
+ protected virtual void SetVersion(int version, String assemblyId)
+ {
+ if (assemblyId == null)
+ throw new ArgumentException();
+
+ if (!TableExists(SCHEMA_INFO_TABLE))
+ CreateSchemaInfoTable();
+
+ ExecuteNonQuery(string.Format("UPDATE {2} SET Version={0} WHERE AssemblyId='{1}'", version, assemblyId, SCHEMA_INFO_TABLE));
+ }
+
+
+ protected virtual void InsertVersionRecord(String assemblyId)
+ {
+ ExecuteNonQuery(string.Format("INSERT INTO {1} (Version, AssemblyId) VALUES (0, '{0}')", assemblyId, SCHEMA_INFO_TABLE));
+ }
+
+ protected virtual bool VersionRecordExists(String assemblyId)
+ {
+ if (assemblyId == null) return false; // No schema info.
+ if (!TableExists(SCHEMA_INFO_TABLE)) return false;
+
+ int? ver = (int?)ExecuteScalar(String.Format("SELECT Version FROM {1} WHERE AssemblyId='{0}'", assemblyId, SCHEMA_INFO_TABLE));
+ return ver != null;
+ }
+ #endregion
+
+ #region General
+ /// <summary>
+ /// Builds a command using the current transaction
+ /// </summary>
+ /// <param name="sql"></param>
+ /// <returns></returns>
+ public IDbCommand BuildCommand(string sql)
+ {
+ IDbCommand cmd = Connection.CreateCommand();
+ cmd.CommandText = sql;
+ cmd.CommandType = CommandType.Text;
+ if (this.Timeout != null)
+ cmd.CommandTimeout = this.Timeout.Value;
+ BeginTransaction();
+ if (_transaction != null)
+ {
+ cmd.Transaction = _transaction;
+ }
+ return cmd;
+ }
+
protected void EnsureHasConnection()
{
if (Connection.State != ConnectionState.Open)
@@ -504,53 +706,6 @@ return cmd.ExecuteScalar();
}
- public abstract Column[] GetColumns(string table);
-
- public abstract string[] GetTables();
-
- /// <summary>
- /// Get the version from the SchemaInfo Table
- /// </summary>
- /// <returns>The current version</returns>
- protected virtual int GetVersion(String assemblyId)
- {
- if (assemblyId == null) return 0; // No schema info.
- if (!TableExists(SCHEMA_INFO_TABLE)) return 0;
-
- return (int)ExecuteScalar(String.Format("SELECT Version FROM {1} WHERE AssemblyId='{0}'", assemblyId, SCHEMA_INFO_TABLE));
- }
-
- [Obsolete("Use the other Insert variant, this one is not safe!")]
- public virtual int Insert(string table, params string[] columnValues)
- {
- string[] columns = new string[columnValues.Length];
- string[] values = new string[columnValues.Length];
- int i = 0;
-
- foreach (string cs in columnValues)
- {
- columns[i] = cs.Split('=')[0];
- values[i] = cs.Split('=')[1];
- i++;
- }
-
- return ExecuteNonQuery(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, string.Join(", ", columns), string.Join(", ", values)));
- }
-
- /// <summary>
- /// Insert a row into a table
- /// </summary>
- /// <param name="table">The name of the table</param>
- /// <param name="columns">A list of column names</param>
- /// <param name="values">A list of primitive values that can be converted to SQL by the TransformationProvider</param>
- /// <returns></returns>
- public abstract int Insert(string table, string[] columns, params object[] values);
-
- protected virtual void InsertVersionRecord(String assemblyId)
- {
- ExecuteNonQuery(string.Format("INSERT INTO {1} (Version, AssemblyId) VALUES (0, '{0}')", assemblyId, SCHEMA_INFO_TABLE));
- }
-
/// <param name="commandText">Format String</param>
/// <param name="parameters">Arguments to use against the formatted string</param>
/// <returns>Int</returns>
@@ -585,54 +740,61 @@
}
- #region Remove Column/Table/Constraint/FK
+ #endregion
+
+ #region Data Manipulation
/// <summary>
- /// Removes a column from a table
+ /// Copies rows from one table to another
/// </summary>
- /// <param name="table">table containing the column</param>
- /// <param name="column">column name</param>
- public abstract void RemoveColumn(string table, string column);
+ /// <param name="fromTable">The name of the table being copied from</param>
+ /// <param name="toTable">The table being copied to...</param>
+ /// <param name="fromColumns">The names of the columns being copied. These must be in the same order as the names of the matching <see cref="toColumns"/></param>
+ /// <param name="toColumns">The names of the columns in the toTable that match the columns in the fromTable</param>
+ /// <param name="fromWhere">Where statement that filters the from table.</param>
+ public void CopyFromTable(string fromTable, string toTable, string[] fromColumns, string[] toColumns, string fromWhere)
+ {
+ if (fromColumns.Length != toColumns.Length)
+ {
+ throw new ArgumentOutOfRangeException("FromColumns and toColumns must have matching numbers.");
+ }
+
+ string sql = string.Format("Insert Into [{0}] ({1}) Select {2} From [{3}] {4} ",
+ toTable,
+ string.Join(", ", toColumns),
+ string.Join(", ", fromColumns),
+ fromTable,
+ fromWhere.Length > 1 ? "Where " + fromWhere : "");
+
+ this.Logger.Trace("Copying from table [{0}]", sql);
+ this.ExecuteNonQuery(sql);
+
+ }
+
+ [Obsolete("Use the other Insert variant, this one is not safe!")]
+ public virtual int Insert(string table, params string[] columnValues)
+ {
+ string[] columns = new string[columnValues.Length];
+ string[] values = new string[columnValues.Length];
+ int i = 0;
+
+ foreach (string cs in columnValues)
+ {
+ columns[i] = cs.Split('=')[0];
+ values[i] = cs.Split('=')[1];
+ i++;
+ }
+
+ return ExecuteNonQuery(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, string.Join(", ", columns), string.Join(", ", values)));
+ }
/// <summary>
- /// Remove a table from the database.
+ /// Insert a row into a table
/// </summary>
- /// <param name="name">Table name</param>
- public abstract void RemoveTable(string name);
-
- /// <summary>
- /// Rename a Column
- /// </summary>
- /// <param name="table">Table containing the Column to be Renames</param>
- /// <param name="column">Column to be renamed</param>
- /// <param name="newName">New name of the column</param>
- public abstract void RenameColumn(string table, string column, string newName);
-
- /// <summary>
- /// Rename a Table
- /// </summary>
- /// <param name="table">Name of the table to be renames</param>
- /// <param name="newName">New name of the table</param>
- public abstract void RenameTable(string table, string newName);
-
- /// <summary>
- /// Removes a constraint.
- /// </summary>
- /// <param name="name">Constraint name</param>
- /// <param name="table">Table owning the constraint</param>
- public abstract void RemoveForeignKey(string name, string table);
- #endregion
-
- /// <summary>
- /// Rollback the current migration. Called by the migration mediator.
- /// </summary>
- public virtual void Rollback()
- {
- if (_transaction != null && Connection != null && Connection.State == ConnectionState.Open)
- {
- _transaction.Rollback();
- }
- _transaction = null;
- }
+ /// <param name="table">The name of the table</param>
+ /// <param name="columns">A list of column names</param>
+ /// <param name="values">A list of primitive values that can be converted to SQL by the TransformationProvider</param>
+ /// <returns></returns>
+ public abstract int Insert(string table, string[] columns, params object[] values);
public IDataReader Select(string what, string from)
{
@@ -653,43 +815,246 @@ {
return ExecuteScalar(string.Format("SELECT {0} FROM {1} WHERE {2}", what, from, where));
}
-
- /// <summary>
- /// Set the current version in the SchemaInfo Table
- /// </summary>
- /// <param name="version">the new version to set</param>
- protected virtual void SetVersion(int version, String assemblyId)
- {
- if (assemblyId == null)
- throw new ArgumentException();
-
- if (!TableExists(SCHEMA_INFO_TABLE))
- CreateSchemaInfoTable();
-
- ExecuteNonQuery(string.Format("UPDATE {2} SET Version={0} WHERE AssemblyId='{1}'", version, assemblyId, SCHEMA_INFO_TABLE));
- }
-
- /// <summary>
- /// Determines if a table exists.
- /// </summary>
- /// <param name="table">Table name</param>
- /// <returns><c>true</c> if the table exists</returns>
- public abstract bool TableExists(string table);
-
public virtual int Update(string table, params string[] columnValues)
{
return ExecuteNonQuery(string.Format("UPDATE {0} SET {1}", table, string.Join(", ", columnValues)));
}
+ #endregion
- protected virtual bool VersionRecordExists(String assemblyId)
+ #region To SQL Converters
+ protected virtual string GetTextFor(Column col)
{
- if (assemblyId == null) return false; // No schema info.
- if (!TableExists(SCHEMA_INFO_TABLE)) return false;
+ // Force boolean values to have a default
+ if (col.Type == typeof(bool) && col.ColumnProperty != ColumnProperties.Null && col.DefaultValue == null)
+ col.DefaultValue = false;
- int? ver = (int?)ExecuteScalar(String.Format("SELECT Version FROM {1} WHERE AssemblyId='{0}'", assemblyId, SCHEMA_INFO_TABLE));
- return ver != null;
+ string sqlDefaultKeyword = "DEFAULT ";
+ string sqlDefault = "";
+
+ // assign default values, ignore defaults if they have been overloaded for PK/Identity purposes
+ if (col.DefaultValue != null)
+ sqlDefault = sqlDefaultKeyword + ToValue(col.Type, col.DefaultValue);
+
+ string sqlIdentity = "";
+ string sqlConstraint = "";
+ switch (col.ColumnProperty)
+ {
+ case ColumnProperties.Null:
+ sqlConstraint = "NULL";
+ break;
+ case ColumnProperties.NotNull:
+ sqlConstraint = "NOT NULL";
+ break;
+ // means a unique ID or a uniquely generated GUID
+ case ColumnProperties.Identity:
+ sqlConstraint = "NOT NULL"; // Identity columns must also be Not Null
+ if (col.Type == typeof(Guid))
+ sqlDefault = sqlDefaultKeyword + "NEWID()";
+ else
+ sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
+ break;
+ case ColumnProperties.PrimaryKey:
+ sqlConstraint = "PRIMARY KEY";
+ break;
+ // means a unique ID or a uniquely generated GUID as the PK
+ case ColumnProperties.PrimaryKeyWithIdentity:
+ sqlConstraint = "PRIMARY KEY";
+ if (col.Type == typeof(Guid))
+ sqlDefault = sqlDefaultKeyword + "NEWID()";
+ else
+ sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
+ break;
+ case ColumnProperties.UNIQUE:
+ sqlConstraint = "UNIQUE";
+ break;
+ }
+
+ string sqlType = ToTextType(col.Type, col.Size);
+ return string.Join(" ", new string[] { col.Name, sqlType, sqlDefault, sqlIdentity, sqlConstraint });
}
+ protected virtual string GetTextFor(ForeignKey fk)
+ {
+
+ return "CONSTRAINT {key.Name} FOREIGN KEY({constrained}) REFERENCES {key.ReferencedTable}({referenced})".FormatWith(new { key = fk, constrained = String.Join(",", fk.ConstrainedFields), referenced = String.Join(",", fk.ReferencedFields) });
+ }
+
+
+ private Dictionary<Type, Func<object, string>> _standard_type_value_converters = new Dictionary<Type, Func<object, string>> {
+ {typeof(string), value=>{return value == null ? "null" : String.Format("'{0}'", value.ToString());}},
+
+ {typeof(int), value=>{return (value ?? "").ToString();}},
+ {typeof(long),value=>{return (value ?? "").ToString();}}, // This also covers Int64
+
+ {typeof(float),value=>{return (value ?? "").ToString();}},
+ {typeof(double),value=>{return (value ?? "").ToString();}},
+ {typeof(decimal),value=>{return (value ?? "").ToString();}},
+
+ {typeof(bool),value=>{return (Boolean)value? "1" : "0";}},
+ {typeof(DateTime),value=>{return ((DateTime)value).ToString("'yyy-MM-dd HH:mm:ss'");}},
+ {typeof(char),value=>{return value == null ? "null" : String.Format("'{0}'", value.ToString());}},
+ {typeof(Guid),value=>{return value == null ? "null" : String.Format("'{0}'", value.ToString());}},
+ {typeof(Guid?), value=>{return value == null ? "null" : String.Format("'{0}'", value.ToString());}},
+ {typeof(byte[]),value=>{return "image";}},
+
+ {typeof(TimeSpan),value=>{return ((TimeSpan)value).TotalMilliseconds.ToString();;}},
+
+ };
+
+ /// <summary>
+ /// Gets the text for a value
+ /// </summary>
+ /// <param name="type"></param>
+ /// <param name="value"></param>
+ /// <returns></returns>
+ protected virtual string ToValue(Type type, object value)
+ {
+ if (value is SpecialDefaults)
+ {
+ switch ((SpecialDefaults)value)
+ {
+ case SpecialDefaults.NewId:
+ default:
+ return "newid()";
+ }
+ }
+
+ if (_standard_type_value_converters.ContainsKey(type))
+ return _standard_type_value_converters[type].Invoke(value);
+
+ throw new NotSupportedException("Type not supported : " + type.Name);
+ }
+
+ protected virtual string ToTextType(Type type, int size)
+ {
+ return ToTextType(new Column("Temporary_Column_Wrapper") { Type = type, Size = size });
+ }
+
+ private Dictionary<Type, Func<int, string>> _standard_type_to_text_conversion_map = new Dictionary<Type, Func<int, string>> {
+ {typeof(string), size=>{return size > 0 && size <= 4000 ? string.Format("nvarchar({0})", size) : "ntext";}},
+
+ {typeof(int), size=>{return size >= 8 ? "bigint" : "int";}},
+ {typeof(long),size=>{return "bigint";}}, // This also covers Int64
+
+ {typeof(float),size=>{return size == 0 ? "real" : string.Format("float({0})", size);}},
+ {typeof(double),size=>{return size == 0 ? "real" : string.Format("float({0})", size);}},
+ {typeof(decimal),size=>{return size == 0 ? "real" : string.Format("float({0})", size);}},
+
+ {typeof(bool),size=>{return "bit";}},
+ {typeof(DateTime),size=>{return "datetime";}},
+ {typeof(char),size=>{return string.Format("nchar({0})", size);}},
+ {typeof(Guid),size=>{return "uniqueidentifier";}},
+ {typeof(Guid?),size=>{return "uniqueidentifier";}},
+ {typeof(byte[]),size=>{return "image";}},
+ {typeof(TimeSpan),size=>{return "bigint";}},
+
+ };
+
+ /// <summary>
+ /// Gets the text for a given system type
+ /// </summary>
+ /// <param name="column"></param>
+ /// <returns></returns>
+ protected virtual string ToTextType(Column column)
+ {
+ var type = column.Type;
+ var size = column.Size;
+
+ if (_standard_type_to_text_conversion_map.ContainsKey(type))
+ return _standard_type_to_text_conversion_map[type].Invoke(size);
+
+ throw new NotSupportedException("Type not supported : " + type.Name);
+ }
+ #endregion
+
+ #region From SQL Converters
+
+ private Dictionary<string, Type> _standard_text_to_type_conversion_map = new Dictionary<string, Type> {
+ {"ntext", typeof(string)},
+ {"text", typeof(string)},
+ {"nvarchar", typeof(string)},
+ {"varchar", typeof(string)},
+
+ {"int", typeof(int)},
+ {"bigint", typeof(long)},
+
+ {"real", typeof(float)},
+ {"float", typeof(float)},
+
+ {"bit", typeof(bool)},
+ {"datetime", typeof(DateTime)},
+ {"nchar", typeof(char)},
+ {"uniqueidentifier", typeof(Guid)},
+ {"image", typeof(byte[])},
+ };
+
+ /// <summary>
+ /// Gets the .net type from an SQL type
+ /// </summary>
+ /// <param name="type"></param>
+ /// <returns></returns>
+ protected virtual Type TypeFromTypeDefinition(String type)
+ {
+ if(type.IndexOf('(') >=1) // Strip any size indicators
+ type = type.Substring(0, type.IndexOf('('));
+
+ if (_standard_text_to_type_conversion_map.ContainsKey(type))
+ return _standard_text_to_type_conversion_map[type];
+
+ throw new ArgumentException();
+ }
+
+ /// <summary>
+ /// Gets the .net type from an SQL type
+ /// </summary>
+ /// <param name="type"></param>
+ /// <returns></returns>
+ protected virtual int SizeFromTypeDefinition(String type)
+ {
+ var sizeRegex= "\\((?<size>[0-9]+)\\)";
+ var reg = new Regex(sizeRegex);
+ var match = reg.Match(type).Groups["size"];
+
+ if (!match.Success) return 0;
+
+ return int.Parse(match.Value);
+ }
+
+ #endregion
+
+ #region DebugHelpers
+
+ protected internal String DumpTableContents(String table)
+ {
+ StringBuilder builder = new StringBuilder();
+ int i = 0;
+ using (IDataReader reader = ExecuteQuery(string.Format("SELECT * FROM {0}", table)))
+ {
+ if (i == 0)
+ {
+ for (int j = 0; j < reader.FieldCount; j++)
+ {
+ builder.Append(reader.GetName(j).ToString());
+ builder.Append("\t\t|\t\t");
+ }
+ builder.AppendLine();
+ builder.AppendLine("---------------------------------------------------------------"); i++;
+ }
+
+ while (reader.Read())
+ {
+ var fields = new Object[reader.FieldCount];
+ reader.GetValues(fields);
+
+ builder.AppendLine(String.Join("\t\t\t\t", fields));
+ }
+ }
+ System.Diagnostics.Debug.Write(builder.ToString());
+ return builder.ToString();
+ }
+
+ #endregion
+
#region IDisposable Members
public virtual void Dispose()
@@ -700,7 +1065,7 @@ this.Connection.Dispose();
}
}
+ #endregion
- #endregion
}
}
|
|
@@ -44,6 +44,9 @@ <WarningLevel>4</WarningLevel>
</PropertyGroup>
<ItemGroup>
+ <Reference Include="log4net">
+ <HintPath>..\lib\log4net.dll</HintPath>
+ </Reference>
<Reference Include="nunit.framework, Version=2.2.0.0, Culture=neutral, PublicKeyToken=96d09a1eb7f44a77">
<SpecificVersion>False</SpecificVersion>
<HintPath>..\lib\nunit.framework.dll</HintPath>
@@ -58,6 +61,7 @@ </Reference>
</ItemGroup>
<ItemGroup>
+ <Compile Include="AssemblyInfo.cs" />
<Compile Include="MigrationTestCase.cs" />
<Compile Include="MigrationTypeComparerTest.cs" />
<Compile Include="MigratorTest.cs" />
|
||
|
@@ -23,8 +23,8 @@
// Collections qui vont contenir les # de versions
// des migrations invoqu�es apr�s un appel au migrateur.
- private static ArrayList _upCalled = new ArrayList();
- private static ArrayList _downCalled = new ArrayList();
+ public static ArrayList _upCalled = new ArrayList();
+ public static ArrayList _downCalled = new ArrayList();
[SetUp]
public void SetUp()
@@ -144,56 +144,47 @@ providerMock.Expect("Rollback");
else
providerMock.ExpectNoCall("Rollback");
-
- _migrator = new Migrator((TransformationProvider) providerMock.MockInstance, null, true);
-
- // Enl�ve toutes les migrations trouv�e automatiquement
- _migrator.MigrationsTypes.Clear();
- _upCalled.Clear();
- _downCalled.Clear();
-
- _migrator.MigrationsTypes.Add(typeof(FirstMigration));
- _migrator.MigrationsTypes.Add(typeof(SecondMigration));
- _migrator.MigrationsTypes.Add(typeof(ThirdMigration));
- _migrator.MigrationsTypes.Add(typeof(ForthMigration));
- _migrator.MigrationsTypes.Add(typeof(BadMigration));
- _migrator.MigrationsTypes.Add(typeof(SixthMigration));
+
+ _migrator = new Migrator((TransformationProvider)providerMock.MockInstance, typeof(FirstMigration).Assembly, true);
+ _upCalled.Clear();
+ _downCalled.Clear();
}
- private class AbstractTestMigration : Migration
- {
- override public void Up()
- {
- _upCalled.Add(Migrator.GetMigrationVersion(GetType()));
- }
- override public void Down()
- {
- _downCalled.Add(Migrator.GetMigrationVersion(GetType()));
- }
- }
-
- [Migration(1, Ignore=true)]
- private class FirstMigration : AbstractTestMigration {}
- [Migration(2, Ignore=true)]
- private class SecondMigration : AbstractTestMigration {}
- [Migration(3, Ignore=true)]
- private class ThirdMigration : AbstractTestMigration {}
- [Migration(4, Ignore=true)]
- private class ForthMigration : AbstractTestMigration {}
- [Migration(5, Ignore=true)]
- private class BadMigration : AbstractTestMigration {
- override public void Up()
- {
- throw new Exception("oh uh!");
- }
- override public void Down()
- {
- throw new Exception("oh uh!");
- }
- }
- [Migration(6, Ignore=true)]
- private class SixthMigration : AbstractTestMigration {}
+
#endregion
}
+ [Migration(1, Ignore = false)]
+ public class FirstMigration : AbstractTestMigration { }
+ [Migration(2, Ignore = false)]
+ public class SecondMigration : AbstractTestMigration { }
+ [Migration(3, Ignore = false)]
+ public class ThirdMigration : AbstractTestMigration { }
+ [Migration(4, Ignore = false)]
+ public class ForthMigration : AbstractTestMigration { }
+ [Migration(5, Ignore = false)]
+ public class BadMigration : AbstractTestMigration
+ {
+ override public void Up()
+ {
+ throw new Exception("oh uh!");
+ }
+ override public void Down()
+ {
+ throw new Exception("oh uh!");
+ }
+ }
+ [Migration(6, Ignore = false)]
+ public class SixthMigration : AbstractTestMigration { }
+ public class AbstractTestMigration : Migration
+ {
+ override public void Up()
+ {
+ MigratorTest._upCalled.Add(Migrator.GetMigrationVersion(GetType()));
+ }
+ override public void Down()
+ {
+ MigratorTest._downCalled.Add(Migrator.GetMigrationVersion(GetType()));
+ }
+ }
}
|
||
|
@@ -57,8 +57,6 @@ _provider.Rollback();
}
-
-
public void DropTestDatabase()
{
RunSQL(String.Format(@"
|
||
|
|
@@ -138,6 +138,18 @@ Assert.IsFalse(_provider.ColumnExists("Test", "Inactif"));
}
+ [Test]
+ public void RenameColumn()
+ {
+ AddTable();
+ _provider.AddColumn("Test", "Inactif", typeof(bool));
+ Assert.IsTrue(_provider.ColumnExists("Test", "Inactif"));
+
+ _provider.RenameColumn("Test", "Inactif", "Inactive");
+ Assert.IsFalse(_provider.ColumnExists("Test", "Inactif"));
+ Assert.IsTrue(_provider.ColumnExists("Test", "Inactive"));
+ }
+
[Test]
public void HasColumn()
{
@@ -172,6 +184,98 @@ Assert.IsTrue(_provider.TableExists("SchemaInfo"), "No SchemaInfo table created");
}
+ /// <summary>
+ /// Reproduce bug reported by Luke Melia & Daniel Berlinger :
+ /// http://macournoyer.wordpress.com/2006/10/15/migrate-nant-task/#comment-113
+ /// </summary>
+ [Test]
+ public void CommitTwice()
+ {
+ _provider.Commit();
+ Assert.AreEqual(0, _provider.CurrentVersion);
+ _provider.Commit();
+ }
+
+ #region Definition Retriving Tests
+ [Test]
+ public void GetTableDefinition()
+ {
+ var tableName = "GetTableDefinition";
+ var referenceTable = "GetTableDefinition_ReferenceTable";
+
+ AddStandardEntityTable(referenceTable);
+
+ var columns = new Column[]{
+ new Column("Id", typeof(Guid), ColumnProperties.PrimaryKey),
+ new Column("Name", typeof(string), 1024, ColumnProperties.Null),
+ new Column("NTextCol", typeof(string), 4000, ColumnProperties.Null),
+ new Column("TestingNumbers", typeof(int), 0, ColumnProperties.NotNull, 8),
+ new Column("Organization", typeof(Guid), 0, ColumnProperties.NotNull),
+ new Column("DateCreated", typeof(DateTime), 0, ColumnProperties.NotNull),
+ new Column("Parent", typeof(Guid), 0, ColumnProperties.Null)
+ };
+
+
+ var table = new Table(tableName, columns)
+ .Add(new ForeignKey("FK_Testing", "Organization", referenceTable, "Id"));
+
+ _provider.AddTable(table);
+
+ Table retrieved = _provider.GetTable(tableName);
+
+ Assert.AreEqual(columns.Length, retrieved.Columns.Count);
+
+ foreach (var column in columns)
+ {
+ var equilivant = (from found in retrieved.Columns where found.Name.Equals(column.Name) select found).FirstOrDefault();
+ Assert.IsNotNull(equilivant);
+ Assert.AreEqual(column.Type, equilivant.Type, "Column type not the same for " + column.Name);
+ Assert.AreEqual(column.Size, equilivant.Size, "Column size not the same for " + column.Name);
+ Assert.AreEqual(column.ColumnProperty, equilivant.ColumnProperty, "Column properties not the same for " + column.Name);
+ }
+
+ Assert.AreEqual(1, retrieved.ForeignKeys.Count);
+
+ }
+
+ [Test]
+ public void GetForeignKeys()
+ {
+ var tableName = "GetForeignKeys";
+ var referenceTable = "GetForeignKeys_ReferenceTable";
+
+ AddStandardEntityTable(referenceTable);
+
+ var columns = new Column[]{
+ new Column("Id", typeof(Guid), ColumnProperties.PrimaryKey),
+ new Column("Name", typeof(string), 1024, ColumnProperties.Null),
+ new Column("FK_Col", typeof(Guid),ColumnProperties.Null),
+ };
+ var fks = new ForeignKey[]{
+ new ForeignKey("FK_Testing", "FK_Col", referenceTable, "Id")
+ };
+ var table = new Table(tableName, columns, fks);
+
+ _provider.AddTable(table);
+
+ var retrieved = _provider.GetForeignKeys(tableName);
+
+ Assert.AreEqual(fks.Length, retrieved.Length);
+ foreach (var key in retrieved)
+ {
+ var found = (from fk in fks where fk.Name.Equals(key.Name) select fk).FirstOrDefault();
+ Assert.IsNotNull(found);
+ Assert.AreEqual(key.Name, found.Name);
+
+ foreach(var field in key.ConstrainedFields)
+ Assert.IsTrue(found.ConstrainedFields.Contains(field));
+
+ Assert.AreEqual(key.ReferencedTable, found.ReferencedTable);
+ foreach (var field in key.ReferencedFields)
+ Assert.IsTrue(found.ReferencedFields.Contains(field));
+ }
+ }
+
[Test]
public void GetColumns()
{
@@ -186,7 +290,7 @@ new Column("Parent", typeof(Guid), 0, ColumnProperties.Null)
};
_provider.AddTable(tableName, columns);
-
+
var retrieved = _provider.GetColumns(tableName);
Assert.AreEqual(columns.Length, retrieved.Length);
@@ -199,21 +303,15 @@ Assert.AreEqual(column.Size, equilivant.Size, "Column size not the same for " + column.Name);
Assert.AreEqual(column.ColumnProperty, equilivant.ColumnProperty, "Column properties not the same for " + column.Name);
}
-
}
-
- /// <summary>
- /// Reproduce bug reported by Luke Melia & Daniel Berlinger :
- /// http://macournoyer.wordpress.com/2006/10/15/migrate-nant-task/#comment-113
- /// </summary>
- [Test]
- public void CommitTwice()
- {
- _provider.Commit();
- Assert.AreEqual(0, _provider.CurrentVersion);
- _provider.Commit();
- }
-
+ #endregion
+ private void AddStandardEntityTable(String tableName)
+ {
+ _provider.AddTable(new Table(tableName)
+ .Add(new Column("Id", typeof(Guid), ColumnProperties.PrimaryKey))
+ .Add(new Column("Name", typeof(String), 512, ColumnProperties.NotNull))
+ );
+ }
private void DumpTableContents(string table)
{
|
|
|
@@ -97,6 +97,18 @@ }
}
+ public override void AddTable(Table table)
+ {
+ _realProvider.AddTable(table);
+
+ Assert.IsTrue(_realProvider.TableExists(table.Name));
+ foreach (Column c in table.Columns)
+ {
+ Assert.IsTrue(_realProvider.ColumnExists(table.Name, c.Name),
+ string.Format("The column {0}.{1} failed to be created", table.Name, c.Name));
+ }
+ }
+
#region Simple delegate method
public override int CurrentVersion {
get {
@@ -152,6 +164,17 @@ {
return _realProvider.GetColumns(table);
}
+
+ public override Table GetTable(String table)
+ {
+ return _realProvider.GetTable(table);
+ }
+
+ public override ForeignKey[] GetForeignKeys(String table)
+ {
+ return _realProvider.GetForeignKeys(table);
+ }
+
#endregion
|
||
Add a tag
Loading...