public void ShouldHaveTextImageIfBigTextColumn()
{
var table = new SqlServerTable
{
Name = "test1",
Schema = "dbo2",
Columns = new[]
{
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.NVarChar,
Length = SqlServerConstants.MaxLength
},
Name = "NotNullable",
Nullable = false
},
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "Nullable",
Nullable = true
}
}
};
var converter = new TableTypeConverter();
var tab = converter.Convert(table, null, null);
tab.Text.ShouldContain("TEXTIMAGE_ON [PRIMARY]");
}
public void ShouldContainTableName()
{
var table = new SqlServerTable
{
Name = "test1",
Schema = "dbo2",
Columns = new[]
{
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "NotNullable",
Nullable = false
},
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "Nullable",
Nullable = true
}
}
};
var converter = new TableTypeConverter();
var tab = converter.Convert(table, null, null);
tab.Text.ShouldContain("CREATE TABLE [dbo2].[test1]");
}
public void ShouldSetNotNullableOnColumn()
{
var table = new SqlServerTable
{
Name = "test1",
Schema = "dbo",
Columns = new []
{
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "NotNullable",
Nullable = false
},
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "Nullable",
Nullable = true
}
}
};
var converter = new TableTypeConverter();
var tab = converter.Convert(table, null, null);
tab.Text.ShouldContain("[NotNullable] [int] NOT NULL");
tab.Text.ShouldContain("[Nullable] [int] NULL");
}
/// <summary>
/// Builds the tables.
/// </summary>
/// <returns></returns>
private bool BuildTables()
{
DataTable schemaTables = GetOleDbSchema(OleDbSchemaGuid.Tables, "", _schemaFilter, "", "");
if (schemaTables == null || schemaTables.Rows.Count == 0)
{
return(false);
}
//TODO: Note sure if this is valid. It does not work for Oracle DB's
result.Name = schemaTables.Rows[0]["TABLE_CATALOG"].ToString();
if (result.Name == String.Empty)
{
result.Name = "Unknown";
}
// Build the base schema information
foreach (DataRow dr in schemaTables.Rows)
{
TableType tableType = TableTypeConverter.Convert(dr["TABLE_TYPE"].ToString());
if (tableType == TableType.TABLE || tableType == TableType.VIEW)
{
try
{
result.AddTable(BuildTable(dr));
}
catch (Exception ex)
{
Errors.Add(ex);
}
}
}
return(true);
}
private bool BuildTables()
{
result.Name = _schemaTables.Rows[0]["TABLE_CATALOG"].ToString();
if (result.Name == String.Empty)
{
result.Name = "Unknown";
}
// Build the base schema information
foreach (DataRow dr in _schemaTables.Rows)
{
var tableType = TableTypeConverter.Convert(dr[ColumnTableType].ToString());
if (tableType == TableType.TABLE || tableType == TableType.VIEW)
{
try
{
result.AddTable(BuildTable(dr, tableType));
}
catch (Exception ex)
{
Errors.Add(ex);
}
}
}
return(true);
}
/// <summary>
/// Builds the table.
/// </summary>
/// <param name="dr">The dr.</param>
/// <returns></returns>
private TableSchema BuildTable(DataRow dr)
{
TableSchema table = new TableSchema();
table.Alias = dr["TABLE_NAME"].ToString();
table.Name = dr["TABLE_NAME"].ToString();
table.TableType = TableTypeConverter.Convert(dr["TABLE_TYPE"].ToString());
BuildColumns(table);
return(table);
}
public void ShouldHaveForeignKey()
{
var table = new SqlServerTable
{
Name = "test1",
Schema = "dbo2",
Columns = new[]
{
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.NVarChar,
Length = SqlServerConstants.MaxLength
},
Name = "NotNullable",
Nullable = false
},
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "Nullable",
Nullable = true
}
}
};
table.ForeignKeyConstraints = new[]
{
new ForeignKeyConstraint
{
Name = "fk",
DestinationTableColumnNames = new[] { "abc", "cbed" },
DestinationTableSchema = "dbo",
DestinationTableName = "awe",
SourceTableColumns = table.Columns
}
};
var converter = new TableTypeConverter();
var tab = converter.Convert(table, null, null);
tab.Text.ShouldContain("ALTER TABLE [dbo2].[test1] ADD CONSTRAINT [fk] FOREIGN KEY ([NotNullable], [Nullable]) REFERENCES [dbo].[awe] ([abc], [cbed])");
}
public void ShouldHaveTriggers()
{
var table = new SqlServerTable
{
Name = "test1",
Schema = "dbo2",
Columns = new[]
{
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.NVarChar,
Length = SqlServerConstants.MaxLength
},
Name = "NotNullable",
Nullable = false
},
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "Nullable",
Nullable = true
}
},
Triggers = new []
{
new TableTrigger
{
Schema = "dbo",
Name = "trig1",
Action = TriggerAction.Insert,
TableName = "test1",
TableSchema = "dbo2",
Text = "SELECT 1233"
}
}
};
var converter = new TableTypeConverter();
var tab = converter.Convert(table, null, null);
tab.Text.ShouldContain("SELECT 1233");
}
public void ShouldHaveUniqueConstraint()
{
var table = new SqlServerTable
{
Name = "test1",
Schema = "dbo2",
Columns = new[]
{
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.NVarChar,
Length = SqlServerConstants.MaxLength
},
Name = "NotNullable",
Nullable = false
},
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "Nullable",
Nullable = true
}
},
};
table.UniqueConstraints = new[]
{
new UniqueConstraint
{
Name = "uq",
Clustered = false,
Columns = table.Columns,
FileGroup = "else"
}
};
var converter = new TableTypeConverter();
var tab = converter.Convert(table, null, null);
tab.Text.ShouldContain("ALTER TABLE [dbo2].[test1] ADD CONSTRAINT [uq] UNIQUE NONCLUSTERED ([NotNullable], [Nullable]) ON [else]");
}
public void ShouldHavePermission()
{
var table = new SqlServerTable
{
Name = "test1",
Schema = "dbo2",
Columns = new[]
{
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.NVarChar,
Length = SqlServerConstants.MaxLength
},
Name = "NotNullable",
Nullable = false
},
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "Nullable",
Nullable = true
}
},
PermissionDeclarations = new []
{
new ObjectPermission
{
PermissionName = "SELECT",
StateDescription = "GRANT",
User = "******"
}
}
};
var converter = new TableTypeConverter();
var tab = converter.Convert(table, null, null);
tab.Text.ShouldContain("GRANT SELECT ON [dbo2].[test1] TO [deffff]");
}
public void ShouldHavePrimaryKeyConstraint()
{
var table = new SqlServerTable
{
Name = "test1",
Schema = "dbo2",
Columns = new[]
{
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.NVarChar,
Length = SqlServerConstants.MaxLength
},
Name = "NotNullable",
Nullable = false
},
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "Nullable",
Nullable = true
}
}
};
table.PrimaryKeyConstraint = new PrimaryKeyConstraint
{
Clustered = true,
Name = "pk",
Columns = table.Columns
};
var converter = new TableTypeConverter();
var tab = converter.Convert(table, null, null);
tab.Text.ShouldContain("ALTER TABLE [dbo2].[test1] ADD CONSTRAINT [pk] PRIMARY KEY CLUSTERED ([NotNullable], [Nullable]) ON [PRIMARY]");
}
public void ShouldSetDefaultConstraintOnColumn()
{
var table = new SqlServerTable
{
Name = "test1",
Schema = "dbo",
Columns = new[]
{
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Bit
},
Name = "NotNullable",
Nullable = false,
DefaultConstraint = new DefaultConstraint
{
Name = "df_default",
DefaultVlaue = "(0)"
}
},
new Column
{
DataType = new DataTypeCls
{
DataType = DataType.Int
},
Name = "Nullable",
Nullable = true
}
}
};
var converter = new TableTypeConverter();
var tab = converter.Convert(table, null, null);
tab.Text.ShouldContain("[NotNullable] [bit] NOT NULL CONSTRAINT [df_default] DEFAULT ((0))");
tab.Text.ShouldContain("[Nullable] [int] NULL");
}
