public static string GetUniqueKey(DatabaseInfo.VersionTypeEnum version) {
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return GetUniqueKey2005();
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008 ||
version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return GetUniqueKey2008();
//Fall back to highest compatible version
return GetUniqueKeyAzure();
}
private static string GetSQL(DatabaseInfo.VersionTypeEnum version, SqlOption options)
{
string sql = "";
sql += "SELECT T.object_id, O.type AS ObjectType, ISNULL(CONVERT(varchar,AM.execute_as_principal_id),'CALLER') as ExecuteAs, AF.name AS assembly_name, AM.assembly_class, AM.assembly_id, AM.assembly_method, T.type, CAST(ISNULL(tei.object_id,0) AS bit) AS IsInsert, CAST(ISNULL(teu.object_id,0) AS bit) AS IsUpdate, CAST(ISNULL(ted.object_id,0) AS bit) AS IsDelete, T.parent_id, S.name AS Owner,T.name,is_disabled,is_not_for_replication,is_instead_of_trigger ";
sql += "FROM sys.triggers T ";
sql += "INNER JOIN sys.objects O ON O.object_id = T.parent_id ";
sql += "INNER JOIN sys.schemas S ON S.schema_id = O.schema_id ";
sql += "LEFT JOIN sys.trigger_events AS tei ON tei.object_id = T.object_id and tei.type=1 ";
sql += "LEFT JOIN sys.trigger_events AS teu ON teu.object_id = T.object_id and teu.type=2 ";
sql += "LEFT JOIN sys.trigger_events AS ted ON ted.object_id = T.object_id and ted.type=3 ";
if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10)
{
sql += ",(SELECT null as execute_as_principal_id, null as assembly_class, null as assembly_id, null as assembly_method) AS AM,";
sql += "(SELECT null AS name) AS AF";
}
else
{
sql += "LEFT JOIN sys.assembly_modules AM ON AM.object_id = T.object_id ";
sql += "LEFT JOIN sys.assemblies AF ON AF.assembly_id = AM.assembly_id";
}
sql += " ORDER BY T.parent_id";
return sql;
}
public static string Get(DatabaseInfo.VersionTypeEnum version)
{
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2000) return Get2000();
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return Get2005();
//Fall back to highest compatible version
return Get2008();
}
public static string Get(DatabaseInfo.VersionTypeEnum version, Database databaseSchema)
{
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return Get2005(databaseSchema);
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008) return Get2008(databaseSchema);
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return Get2008R2(databaseSchema);
if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) return GetAzure(databaseSchema);
return "";
}
public static string GetPrimaryKey(DatabaseInfo.VersionTypeEnum version, Table table) {
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2000) return GetPrimaryKey2000(table);
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return GetPrimaryKey2005();
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008 ||
version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2)
return GetPrimaryKey2008();
//Fall back to highest compatible version
return GetPrimaryKeyAzure();
}
public static string GetView(DatabaseInfo.VersionTypeEnum version)
{
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2000 ||
version == DatabaseInfo.VersionTypeEnum.SQLServer2005 ||
version == DatabaseInfo.VersionTypeEnum.SQLServer2008 ||
version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return GetViewSql2008();
//Fall back to highest compatible version
return GetViewSqlAzure();
}
public static string GetTableDetail(DatabaseInfo.VersionTypeEnum version)
{
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2000) return GetTableDetail2000();
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return GetTableDetail2005();
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008 ||
version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return GetTableDetail2008();
//Fall back to highest compatible version
return GetTableDetailAzure();
}
private static string GetSQL(DatabaseInfo.VersionTypeEnum version)
{
string sql = "";
sql += "SELECT ISNULL(CONVERT(varchar,AM.execute_as_principal_id),'CALLER') as ExecuteAs, P.type, AF.name AS assembly_name, AM.assembly_class, AM.assembly_id, AM.assembly_method, P.object_id, S.name as owner, P.name as name, P.create_date, P.modify_date ";
sql += "FROM sys.procedures P ";
sql += "INNER JOIN sys.schemas S ON S.schema_id = P.schema_id ";
if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10)
{
sql +=",(SELECT null as execute_as_principal_id, null as assembly_class, null as assembly_id, null as assembly_method) AS AM,";
sql += "(SELECT null AS name) AS AF";
}
else
{
sql += "LEFT JOIN sys.assembly_modules AM ON AM.object_id = P.object_id ";
sql += "LEFT JOIN sys.assemblies AF ON AF.assembly_id = AM.assembly_id";
}
return sql;
}
public DatabaseInfo Get(Database database)
{
DatabaseInfo item = new DatabaseInfo();
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(DatabaseSQLCommand.GetVersion(database), conn))
{
conn.Open();
item.Server = conn.DataSource;
item.Database = conn.Database;
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
string versionValue = reader["Version"] as string;
try
{
// used to use the decimal as well when Azure was 10.25
var version = new Version(versionValue);
item.VersionNumber = float.Parse(String.Format("{0}.{1}", version.Major, version.Minor), System.Globalization.CultureInfo.InvariantCulture);
int? edition = null;
if (reader.FieldCount > 1 && !reader.IsDBNull(1))
{
int validEdition;
string editionValue = reader[1].ToString();
if (!String.IsNullOrEmpty(editionValue) && int.TryParse(editionValue, out validEdition))
{
edition = validEdition;
}
}
item.SetEdition(edition);
}
catch (Exception notAGoodIdeaToCatchAllErrors)
{
bool useDefaultVersion = false;
//#if DEBUG
// useDefaultVersion = IsKeyPushedDown(System.Windows.Forms.Keys.LShiftKey)
// && IsKeyPushedDown(System.Windows.Forms.Keys.RShiftKey);
//#endif
var exception = new DBDiff.Schema.Misc.SchemaException(
String.Format("Error parsing ProductVersion. ({0})", versionValue ?? "[null]")
, notAGoodIdeaToCatchAllErrors);
if (!useDefaultVersion)
{
throw exception;
}
}
}
}
}
using (SqlCommand command = new SqlCommand(DatabaseSQLCommand.Get(item.Version, database), conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
item.Collation = reader["Collation"].ToString();
item.HasFullTextEnabled = ((int)reader["IsFulltextEnabled"]) == 1;
}
}
}
}
return item;
}