public SqlScriptGenerator Get(SqlVersion version)
{
// TODO : upgrade ScriptDom package version to get access to newer syntax?
SqlScriptGeneratorOptions options = new SqlScriptGeneratorOptions
{
SqlVersion = version,
KeywordCasing = KeywordCasing.Uppercase
};
switch (version)
{
case SqlVersion.Sql80:
return(new Sql80ScriptGenerator(options));
case SqlVersion.Sql90:
return(new Sql90ScriptGenerator(options));
case SqlVersion.Sql100:
return(new Sql100ScriptGenerator(options));
case SqlVersion.Sql110:
return(new Sql110ScriptGenerator(options));
case SqlVersion.Sql120:
default:
return(new Sql120ScriptGenerator(options));
}
}
internal static string GetVersionHint(SqlVersion version, ServerType serverType)
{
if (serverType == ServerType.Cloud)
{
Debug.Assert(version >= SqlVersion.Sql11);
return(SqlProviderManifest.TokenAzure11);
}
switch (version)
{
case SqlVersion.Sql8:
return(SqlProviderManifest.TokenSql8);
case SqlVersion.Sql9:
return(SqlProviderManifest.TokenSql9);
case SqlVersion.Sql10:
return(SqlProviderManifest.TokenSql10);
case SqlVersion.Sql11:
return(SqlProviderManifest.TokenSql11);
default:
throw new ArgumentException(Strings.UnableToDetermineStoreVersion);
}
}
public static string GetVersionHint(SqlVersion version, ServerType serverType)
{
if (serverType == ServerType.Cloud)
{
return(SqlProviderManifest.TokenAzure11);
}
switch (version)
{
case SqlVersion.Sql8:
return(SqlProviderManifest.TokenSql8);
case SqlVersion.Sql9:
return(SqlProviderManifest.TokenSql9);
case SqlVersion.Sql10:
return(SqlProviderManifest.TokenSql10);
case SqlVersion.Sql11:
return(SqlProviderManifest.TokenSql11);
default:
throw new ArgumentException("Could not determine storage version; a valid storage connection or a version hint is required.");
}
}
public AnalyzerCommandLineResult Parse(string[] args)
{
SqlVersion sqlVersion = SqlVersion.Sql130;
var commandLine = new CommandLineParser(commandLineDefinition).Parse(args);
if (commandLine.IsValid)
{
var initialQuotedIdentifiers = commandLine.IsOptionDefined(InitialQuotedIdentifiersKey);
if (commandLine.IsSettingDefined(SqlVersionKey))
{
var versionString = commandLine.GetSettingValue(SqlVersionKey);
if (!Enum.TryParse(versionString, out sqlVersion))
{
Logger.Error($"Sql version '{versionString}' not recognized.");
Logger.Info($"Available values: {Enum.GetNames(typeof(SqlVersion))}");
}
}
return(new AnalyzerCommandLineResult(
true,
sqlVersion,
commandLine.GetSettingValue(InputFileKey),
commandLine.GetSettingValue(ConfigFileKey),
initialQuotedIdentifiers,
commandLine.IsOptionDefined(VerboseKey),
commandLine.IsOptionDefined(DisplayHelpKey),
commandLineDefinition.GetHelpText()));
}
else
{
return(new AnalyzerCommandLineResult(false, sqlVersion, string.Empty, string.Empty, false, false, true, commandLineDefinition.GetHelpText()));
}
}
/// <summary>
/// Detect and memorize the connected SQL Server version
/// </summary>
private void DetectServerVersion()
{
string sqlCmd = string.Empty;
string result;
/*
* Find the query to use to get the version
*/
WriteLog("Detecting Sql Server version...");
XPathDocument vd = new XPathDocument(_appPath + "General.VersionDetect.xml");
XPathNavigator nav = vd.CreateNavigator();
XPathNodeIterator nodeIterator = nav.Select("/databases/database[@name='" + databaseServerName + "']/query");
while (nodeIterator.MoveNext())
{
sqlCmd = nodeIterator.Current.GetAttribute("value", string.Empty);
}
/*
* Execute the query
*/
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
try
{
result = (string)cmd.ExecuteScalar();
}
catch (Exception)
{
result = string.Empty;
}
finally
{
cmd.Dispose();
}
if (result == null)
{
result = string.Empty;
}
result = result.ToLower();
/*
* Perform matching against the xml version file
*/
WriteLog("Loading VersionsInfo module...");
vd = new XPathDocument(_appPath + "SqlServer.VersionsInfo.xml");
nav = vd.CreateNavigator();
nodeIterator = nav.Select("/databases/database[@name='" + databaseServerName + "']/version");
while (nodeIterator.MoveNext())
{
if (result.StartsWith(nodeIterator.Current.GetAttribute("identifier", string.Empty)))
{
version = (SqlVersion)Enum.Parse(version.GetType(), nodeIterator.Current.GetAttribute("id", string.Empty), false);
versionDescription = nodeIterator.Current.GetAttribute("name", string.Empty);
interfaceFile = nodeIterator.Current.GetAttribute("interface", string.Empty);
}
}
WriteLog(String.Format("Detected Sql Server Version: {0}.", versionDescription));
}
private void SetupAnalyzer(SqlVersion sqlVersion, bool initialQuotedIdentifiers)
{
var registrations = RegisterServices();
Logger.Info("Plugins:");
var pluginLoader = IocContainer.Instance.Resolve <IPluginLoaderService>();
var pluginLoadResults = pluginLoader.LoadPlugins();
foreach (var pluginLoadResult in pluginLoadResults)
{
if (pluginLoadResult.SuccessfullyLoaded)
{
Logger.Info($" Assembly: {pluginLoadResult.AssemblyFullName}");
Logger.Info($" Name: {pluginLoadResult.PluginServices.Information.Name}, Description: {pluginLoadResult.PluginServices.Information.Description}");
var ruleTypes = pluginLoadResult.PluginServices.GetRuleTypes();
Logger.Info($" {ruleTypes.Count} Ruletypes: {string.Join(", ", ruleTypes)}");
registrations.RegisterRuleTypes(ruleTypes);
var configControlTypes = pluginLoadResult.PluginServices.GetConfigControlTypes();
registrations.RegisterConfigControlTypes(configControlTypes);
}
foreach (var loadMessage in pluginLoadResult.LoadMessages)
{
Logger.Info($" Message:{loadMessage.Severity} {loadMessage.Message}");
}
}
if (!pluginLoadResults.Any(x => x.SuccessfullyLoaded))
{
Logger.Warn("No rule plugins found in base directory.");
}
IocContainer.Instance.VerifyResolutions();
}
internal static string GetVersionHint(SqlVersion version, ServerType serverType)
{
if (serverType == ServerType.Cloud)
{
Debug.Assert(version >= SqlVersion.Sql11);
return SqlProviderManifest.TokenAzure11;
}
switch (version)
{
case SqlVersion.Sql8:
return SqlProviderManifest.TokenSql8;
case SqlVersion.Sql9:
return SqlProviderManifest.TokenSql9;
case SqlVersion.Sql10:
return SqlProviderManifest.TokenSql10;
case SqlVersion.Sql11:
return SqlProviderManifest.TokenSql11;
default:
throw new ArgumentException(Strings.UnableToDetermineStoreVersion);
}
}
private static void VerifySqlType(SqlVersion sqlVersion, Column column, SqlParameterInfo expectedParamInfo, string expectedSqlString)
{
var sqlType = column.GetSqlType();
VerifySqlParamInfo(sqlVersion, sqlType, expectedParamInfo);
Assert.AreEqual(expectedSqlString, sqlType.GetDataTypeSql(sqlVersion));
}
/// <summary>
/// 创建T-SQL语法解析器
/// </summary>
public static TSqlParser Create(SqlVersion tsqlParserVersion, bool initialQuotedIdentifiers = true)
{
switch (tsqlParserVersion)
{
case SqlVersion.Sql90:
return(new TSql90Parser(initialQuotedIdentifiers));
case SqlVersion.Sql80:
return(new TSql80Parser(initialQuotedIdentifiers));
case SqlVersion.Sql100:
return(new TSql100Parser(initialQuotedIdentifiers));
case SqlVersion.Sql110:
return(new TSql110Parser(initialQuotedIdentifiers));
case SqlVersion.Sql120:
return(new TSql120Parser(initialQuotedIdentifiers));
case SqlVersion.Sql130:
return(new TSql130Parser(initialQuotedIdentifiers));
case SqlVersion.Sql140:
return(new TSql140Parser(initialQuotedIdentifiers));
default:
throw new ArgumentException(
string.Format(CultureInfo.CurrentCulture, "The value ({0}) provided for type ({1}) is unknown",
new object[] { tsqlParserVersion, "TSqlParserVersion" }), nameof(tsqlParserVersion));
}
}
internal static bool UseGeneratedValuesVariable(DbInsertCommandTree tree, SqlVersion sqlVersion)
{
bool flag1 = false;
if (sqlVersion > SqlVersion.Sql8 && tree.Returning != null)
{
HashSet <EdmMember> edmMemberSet = new HashSet <EdmMember>(tree.SetClauses.Cast <DbSetClause>().Select <DbSetClause, EdmMember>((Func <DbSetClause, EdmMember>)(s => ((DbPropertyExpression)s.Property).Property)));
bool flag2 = false;
foreach (EdmMember keyMember in ((DbScanExpression)tree.Target.Expression).Target.ElementType.KeyMembers)
{
if (!edmMemberSet.Contains(keyMember))
{
if (flag2)
{
flag1 = true;
break;
}
flag2 = true;
if (!DmlSqlGenerator.IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
flag1 = true;
break;
}
}
}
}
return(flag1);
}
internal static bool IsPreKatmai(SqlVersion sqlVersion)
{
if (sqlVersion != SqlVersion.Sql8)
{
return(sqlVersion == SqlVersion.Sql9);
}
return(true);
}
private void RegisterSqlParser(SqlVersion sqlVersion, bool initialQuotedIdentifiers)
{
Logger.Info($"Register requested parser: SqlVersion={sqlVersion}, InitialQuotedIdentifiers={initialQuotedIdentifiers}.");
var aSqlParser = new TSql90Parser(initialQuotedIdentifiers);
var requestedParser = aSqlParser.Create(sqlVersion, initialQuotedIdentifiers);
IocContainer.Instance.RegisterInstance(requestedParser, Reuse.Singleton);
}
protected override string DbCreateDatabaseScript(string providerManifestToken, StoreItemCollection storeItemCollection)
{
EntityUtil.CheckArgumentNull(providerManifestToken, "providerManifestToken");
EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
SqlVersion version = SqlVersionUtils.GetSqlVersion(providerManifestToken);
return(CreateObjectsScript(version, storeItemCollection));
}
// <summary>
// Initializes a new instance of the <see cref="SqlProviderManifest" /> class.
// </summary>
// <param name="manifestToken"> A token used to infer the capabilities of the store. </param>
public SqlProviderManifest(string manifestToken)
: base(GetProviderManifest())
{
// GetSqlVersion will throw ArgumentException if manifestToken is null, empty, or not recognized.
_version = SqlVersionUtils.GetSqlVersion(manifestToken);
Initialize();
}
// <summary>
// Initializes a new instance of the <see cref="SqlProviderManifest" /> class.
// </summary>
// <param name="manifestToken"> A token used to infer the capabilities of the store. </param>
public SqlProviderManifest(string manifestToken)
: base(GetProviderManifest())
{
// GetSqlVersion will throw ArgumentException if manifestToken is null, empty, or not recognized.
_version = SqlVersionUtils.GetSqlVersion(manifestToken);
Initialize();
}
/// <summary>
/// Gets a sql auth connection context.
/// </summary>
/// <param name="cmdlet">The cmdlet requesting the context</param>
/// <param name="serverName">The name of the server to connect to</param>
/// <param name="manageUrl">The manage url of the server</param>
/// <param name="credentials">The credentials to connect to the server</param>
/// <param name="sessionActivityId">The session activity ID</param>
/// <param name="managementServiceUri">The URI for management service</param>
/// <returns>The connection context</returns>
public static IServerDataServiceContext GetContext(
PSCmdlet cmdlet,
string serverName,
Uri manageUrl,
SqlAuthenticationCredentials credentials,
Guid sessionActivityId,
Uri managementServiceUri)
{
Version version;
// If a version was specified (by tests) us it.
if (sqlVersion == SqlVersion.v2)
{
version = new Version(11, 0);
}
else if (sqlVersion == SqlVersion.v12)
{
version = new Version(12, 0);
}
else // If no version specified, determine the version by querying the server.
{
version = GetVersion(manageUrl, credentials);
}
sqlVersion = SqlVersion.None;
IServerDataServiceContext context = null;
if (version.Major >= 12)
{
context = new TSqlConnectionContext(
sessionActivityId,
manageUrl.Host,
credentials,
serverName);
}
else
{
context = ServerDataServiceSqlAuth.Create(
managementServiceUri,
sessionActivityId,
credentials,
serverName);
// Retrieve $metadata to verify model version compatibility
XDocument metadata = ((ServerDataServiceSqlAuth)context).RetrieveMetadata();
XDocument filteredMetadata = DataConnectionUtility.FilterMetadataDocument(metadata);
string metadataHash = DataConnectionUtility.GetDocumentHash(filteredMetadata);
if (!((ServerDataServiceSqlAuth)context).metadataHashes.Any(knownHash => metadataHash == knownHash))
{
cmdlet.WriteWarning(Resources.WarningModelOutOfDate);
}
((ServerDataServiceSqlAuth)context).MergeOption = MergeOption.PreserveChanges;
}
return(context);
}
public void Can_Return_Version()
{
var installed = new SqlInstalledVersion(1, 2, 3, DateTime.UtcNow);
var expected = new SqlVersion(1, 2, 3);
var actual = installed.GetVersion();
actual.ShouldBeEquivalentTo(expected);
}
internal sealed override string GetDataTypeSql(SqlVersion sqlVersion)
{
SqlParameterInfo paramInfo = GetSqlParameterInfo(sqlVersion);
Debug.Assert(paramInfo.Size.HasValue);
var size = paramInfo.Size.GetValueOrDefault();
return(size == -1 ? GetSqlDataType() + "(MAX)" : string.Format(CultureInfo.InvariantCulture, GetSqlDataType() + "({0})", size));
}
/// <summary>
/// Create the database and the database objects.
/// If initial catalog is not specified, but AttachDBFilename is specified, we generate a random database name based on the AttachDBFilename.
/// Note: this causes pollution of the db, as when the connection string is later used, the mdf will get attached under a different name.
/// However if we try to replicate the name under which it would be attached, the following scenario would fail:
/// The file does not exist, but registered with database.
/// The user calls: If (DatabaseExists) DeleteDatabase
/// CreateDatabase
/// For further details on the behavior when AttachDBFilename is specified see Dev10# 188936
/// </summary>
protected override void DbCreateDatabase(DbConnection connection, int?commandTimeout, StoreItemCollection storeItemCollection)
{
EntityUtil.CheckArgumentNull(connection, "connection");
EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);
string databaseName, dataFileName, logFileName;
GetOrGenerateDatabaseNameAndGetFileNames(sqlConnection, out databaseName, out dataFileName, out logFileName);
string createDatabaseScript = SqlDdlBuilder.CreateDatabaseScript(databaseName, dataFileName, logFileName);
SqlVersion sqlVersion = GetSqlVersion(storeItemCollection);
string createObjectsScript = CreateObjectsScript(sqlVersion, storeItemCollection);
UsingMasterConnection(sqlConnection, conn =>
{
// create database
CreateCommand(conn, createDatabaseScript, commandTimeout).ExecuteNonQuery();
});
// Create database already succeeded. If there is a failure from this point on, the user should be informed.
try
{
// Clear connection pool for the database connection since after the 'create database' call, a previously
// invalid connection may now be valid.
SqlConnection.ClearPool(sqlConnection);
UsingConnection(sqlConnection, conn =>
{
// create database objects
CreateCommand(conn, createObjectsScript, commandTimeout).ExecuteNonQuery();
});
}
catch (Exception e)
{
if (EntityUtil.IsCatchableExceptionType(e))
{
// Try to drop the database
try
{
DropDatabase(sqlConnection, commandTimeout, databaseName);
}
catch (Exception ie)
{
// The creation of the database succeeded, the creation of the database objects failed, and the dropping of the database failed.
if (EntityUtil.IsCatchableExceptionType(ie))
{
throw new InvalidOperationException(Strings.SqlProvider_IncompleteCreateDatabase, new AggregateException(Strings.SqlProvider_IncompleteCreateDatabaseAggregate, e, ie));
}
throw;
}
// The creation of the database succeeded, the creation of the database objects failed, the database was dropped, no reason to wrap the exception
throw;
}
throw;
}
}
public void RegisterServices(SqlVersion sqlVersion, bool initialQuotedIdentifiers)
{
RegisterSqlParser(sqlVersion, initialQuotedIdentifiers);
IocContainer.Instance.Register <IRuleServices, RuleServices>(Reuse.Singleton);
IocContainer.Instance.Register <IAnalyzerServices, AnalyzerServices>(Reuse.Singleton);
IocContainer.Instance.Register <IConfigurationServices, ConfigurationServices>(Reuse.Singleton);
IocContainer.Instance.Register <IStorageServices, StorageServices>(Reuse.Singleton);
IocContainer.Instance.Register <IPluginLoaderService, PluginLoaderService>(Reuse.Singleton);
}
/// <summary>
/// Gets a sql auth connection context.
/// </summary>
/// <param name="cmdlet">The cmdlet requesting the context</param>
/// <param name="serverName">The name of the server to connect to</param>
/// <param name="manageUrl">The manage url of the server</param>
/// <param name="credentials">The credentials to connect to the server</param>
/// <param name="sessionActivityId">The session activity ID</param>
/// <param name="managementServiceUri">The URI for management service</param>
/// <returns>The connection context</returns>
public static IServerDataServiceContext GetContext(
PSCmdlet cmdlet,
string serverName,
Uri manageUrl,
SqlAuthenticationCredentials credentials,
Guid sessionActivityId,
Uri managementServiceUri)
{
Version version;
// If a version was specified (by tests) us it.
if (sqlVersion == SqlVersion.v2)
{
version = new Version(11, 0);
}
else if (sqlVersion == SqlVersion.v12)
{
version = new Version(12, 0);
}
else // If no version specified, determine the version by querying the server.
{
version = GetVersion(manageUrl, credentials);
}
sqlVersion = SqlVersion.None;
IServerDataServiceContext context = null;
if (version.Major >= 12)
{
context = new TSqlConnectionContext(
sessionActivityId,
manageUrl.Host,
credentials,
serverName);
}
else
{
context = ServerDataServiceSqlAuth.Create(
managementServiceUri,
sessionActivityId,
credentials,
serverName);
// Retrieve $metadata to verify model version compatibility
XDocument metadata = ((ServerDataServiceSqlAuth)context).RetrieveMetadata();
XDocument filteredMetadata = DataConnectionUtility.FilterMetadataDocument(metadata);
string metadataHash = DataConnectionUtility.GetDocumentHash(filteredMetadata);
if (!((ServerDataServiceSqlAuth)context).metadataHashes.Any(knownHash => metadataHash == knownHash))
{
cmdlet.WriteWarning(Resources.WarningModelOutOfDate);
}
((ServerDataServiceSqlAuth)context).MergeOption = MergeOption.PreserveChanges;
}
return context;
}
public void Returns_Null_If_No_Version_Installed()
{
var validContext = GetValidContext();
SqlVersion version = null;
version = validContext.GetInstalledVersion();
Assert.IsNull(version);
}
internal static string GenerateUpdateSql(DbUpdateCommandTree tree, SqlVersion sqlVersion, out List<SqlParameter> parameters)
{
const string dummySetParameter = "@p";
StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, sqlVersion);
if (tree.SetClauses.Count == 0)
{
commandText.AppendLine("declare " + dummySetParameter + " int");
}
// update [schemaName].[tableName]
commandText.Append("update ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// set c1 = ..., c2 = ..., ...
bool first = true;
commandText.Append("set ");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Property.Accept(translator);
commandText.Append(" = ");
setClause.Value.Accept(translator);
}
if (first)
{
// If first is still true, it indicates there were no set
// clauses. Introduce a fake set clause so that:
// - we acquire the appropriate locks
// - server-gen columns (e.g. timestamp) get recomputed
//
// We use the following pattern:
//
// update Foo
// set @p = 0
// where ...
commandText.Append(dummySetParameter + " = 0");
}
commandText.AppendLine();
// where c1 = ..., c2 = ...
commandText.Append("where ");
tree.Predicate.Accept(translator);
commandText.AppendLine();
// generate returning sql
GenerateReturningSql(commandText, tree, null, translator, tree.Returning, false);
parameters = translator.Parameters;
return commandText.ToString();
}
/// <summary>
/// Determines whether the database for the given connection exists.
/// There are three cases:
/// 1. Initial Catalog = X, AttachDBFilename = null: (SELECT Count(*) FROM sys.databases WHERE [name]= X) > 0
/// 2. Initial Catalog = X, AttachDBFilename = F: if (SELECT Count(*) FROM sys.databases WHERE [name]= X) > true,
/// if not, try to open the connection and then return (SELECT Count(*) FROM sys.databases WHERE [name]= X) > 0
/// 3. Initial Catalog = null, AttachDBFilename = F: Try to open the connection. If that succeeds the result is true, otherwise
/// if the there are no databases corresponding to the given file return false, otherwise throw.
///
/// Note: We open the connection to cover the scenario when the mdf exists, but is not attached.
/// Given that opening the connection would auto-attach it, it would not be appropriate to return false in this case.
/// Also note that checking for the existence of the file does not work for a remote server. (Dev11 #290487)
/// For further details on the behavior when AttachDBFilename is specified see Dev10# 188936
/// </summary>
protected override bool DbDatabaseExists(DbConnection connection, int?commandTimeout, StoreItemCollection storeItemCollection)
{
EntityUtil.CheckArgumentNull(connection, "connection");
EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);
var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
if (string.IsNullOrEmpty(connectionBuilder.InitialCatalog) && string.IsNullOrEmpty(connectionBuilder.AttachDBFilename))
{
throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_MissingInitialCatalog);
}
if (!string.IsNullOrEmpty(connectionBuilder.InitialCatalog))
{
if (CheckDatabaseExists(sqlConnection, commandTimeout, connectionBuilder.InitialCatalog))
{
//Avoid further processing
return(true);
}
}
if (!string.IsNullOrEmpty(connectionBuilder.AttachDBFilename))
{
try
{
UsingConnection(sqlConnection, (SqlConnection con) => { });
return(true);
}
catch (SqlException e)
{
if (!string.IsNullOrEmpty(connectionBuilder.InitialCatalog))
{
return(CheckDatabaseExists(sqlConnection, commandTimeout, connectionBuilder.InitialCatalog));
}
// Initial catalog not specified
string fileName = GetMdfFileName(connectionBuilder.AttachDBFilename);
bool databaseDoesNotExistInSysTables = false;
UsingMasterConnection(sqlConnection, conn =>
{
SqlVersion sqlVersion = SqlVersionUtils.GetSqlVersion(conn);
string databaseExistsScript = SqlDdlBuilder.CreateCountDatabasesBasedOnFileNameScript(fileName, useDeprecatedSystemTable: sqlVersion == SqlVersion.Sql8);
int result = (int)CreateCommand(conn, databaseExistsScript, commandTimeout).ExecuteScalar();
databaseDoesNotExistInSysTables = (result == 0);
});
if (databaseDoesNotExistInSysTables)
{
return(false);
}
throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_CannotTellIfDatabaseExists, e);
}
}
// CheckDatabaseExists returned false and no AttachDBFilename is specified
return(false);
}
private static void VerifySqlParamInfo(SqlVersion sqlVersion, SqlType sqlType, SqlParameterInfo expected)
{
var actual = sqlType.GetSqlParameterInfo(sqlVersion);
Assert.AreEqual(expected.SqlDbType, actual.SqlDbType);
Assert.AreEqual(expected.Size, actual.Size);
Assert.AreEqual(expected.Precision, actual.Precision);
Assert.AreEqual(expected.Scale, actual.Scale);
Assert.AreEqual(expected.UdtTypeName, actual.UdtTypeName);
}
internal static void GenerateConst(IndentedStringBuilder sqlBuilder, SqlVersion sqlVersion, Column column, object value)
{
if (value == null)
{
sqlBuilder.Append(NULL);
return;
}
sqlBuilder.Append(column.GetSqlType().GetTSqlLiteral(value, sqlVersion));
}
private SqlGenerator(SqlVersion sqlVersion)
{
SqlVersion = sqlVersion;
SqlBuilder = new IndentedStringBuilder();
_expressionGenerator = new ExpressionGenerator()
{
SqlVersion = this.SqlVersion,
SqlBuilder = this.SqlBuilder,
};
}
public string FormatSql(TSqlScript tsqlScript, SqlVersion version)
{
String script;
_generatorFactory
.Get(version)
.GenerateScript(tsqlScript, out script);
return(script);
}
/// <summary>
/// Get a <see cref="RepositoryVersion" /> instance from <see cref="SqlVersion" />
/// </summary>
public static RepositoryVersion GetRepositoryVersion(SqlVersion sv)
{
if (sv == null || string.IsNullOrEmpty(sv.Version))
{
return(null);
}
else
{
return(new RepositoryVersion(sv.Version));
}
}
internal sealed override string GetTSqlLiteral(object value, SqlVersion sqlVersion)
{
if (value == null)
{
return(NULL);
}
var dbValue = GetDbValue((TEnum)value);
return(dbValue.HasValue ? GetTSqlLiteral(dbValue.GetValueOrDefault(), sqlVersion) : NULL);
}
private static void VerifyDbExpression(SqlVersion sqlVersion, DbExpression expr, string expectedSql, out ExpressionGenerator generator)
{
generator = new ExpressionGenerator()
{
SqlVersion = sqlVersion,
SqlBuilder = new IndentedStringBuilder(),
ModelAliasManager = new ModelAliasManagerMock()
};
expr.Accept(generator);
Assert.AreEqual(expectedSql, generator.SqlBuilder.ToString());
}
public void Can_Set_Version_After_Init()
{
var validContext = GetValidContext();
var expectedVersion = new SqlVersion(0, 0, 0);
validContext.Init();
var currentVersion = validContext.GetInstalledVersion().GetVersion();
currentVersion.ShouldBeEquivalentTo(expectedVersion);
}
private static SqlVersion GetSqlVersion(StoreItemCollection storeItemCollection)
{
SqlProviderManifest sqlManifest = (storeItemCollection.StoreProviderManifest as SqlProviderManifest);
if (sqlManifest == null)
{
throw EntityUtil.Argument(System.Data.Entity.Strings.Mapping_Provider_WrongManifestType(typeof(SqlProviderManifest)));
}
SqlVersion sqlVersion = sqlManifest.SqlVersion;
return(sqlVersion);
}
/// <summary>
/// Initialize a new expression translator populating the given string builder
/// with command text. Command text builder and command tree must not be null.
/// </summary>
/// <param name="commandText">Command text with which to populate commands</param>
/// <param name="commandTree">Command tree generating SQL</param>
/// <param name="preserveMemberValues">Indicates whether the translator should preserve
/// member values while compiling t-SQL (only needed for server generation)</param>
internal ExpressionTranslator(StringBuilder commandText, DbModificationCommandTree commandTree,
bool preserveMemberValues, SqlVersion version)
{
Debug.Assert(null != commandText);
Debug.Assert(null != commandTree);
_commandText = commandText;
_commandTree = commandTree;
_version = version;
_parameters = new List <SqlParameter>();
_memberValues = preserveMemberValues ? new Dictionary <EdmMember, SqlParameter>() :
null;
}
public SQLScripter(SqlVersion sqlVersion, SqlScriptGeneratorOptions options, bool quotedIdentifier, String inputScript)
{
switch (sqlVersion)
{
case SqlVersion.Sql80:
SQLScripter100(options, quotedIdentifier, inputScript);
break;
case SqlVersion.Sql90:
SQLScripter100(options, quotedIdentifier, inputScript);
break;
case SqlVersion.Sql100:
SQLScripter100(options, quotedIdentifier, inputScript);
break;
}
}
public SQLParser(SqlVersion sqlVersion, bool quotedIdentifier, string inputScript)
{
switch (sqlVersion)
{
case SqlVersion.Sql80:
SQLParser80 (quotedIdentifier, inputScript);
break;
case SqlVersion.Sql90:
SQLParser90 (quotedIdentifier, inputScript);
break;
case SqlVersion.Sql100:
SQLParser100 (quotedIdentifier, inputScript);
break;
}
}
internal static string GetVersionHint(SqlVersion version)
{
switch (version)
{
case SqlVersion.Sql8:
return SqlProviderManifest.TokenSql8;
case SqlVersion.Sql9:
return SqlProviderManifest.TokenSql9;
case SqlVersion.Sql10:
return SqlProviderManifest.TokenSql10;
default:
throw new ArgumentException(Strings.UnableToDetermineStoreVersion);
}
}
internal static string GenerateDeleteSql(DbDeleteCommandTree tree, SqlVersion sqlVersion, out List<SqlParameter> parameters)
{
var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
var translator = new ExpressionTranslator(commandText, tree, false, sqlVersion);
// delete [schemaName].[tableName]
commandText.Append("delete ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where c1 = ... AND c2 = ...
commandText.Append("where ");
tree.Predicate.Accept(translator);
parameters = translator.Parameters;
return commandText.ToString();
}
/// <summary>
/// Initialize a new expression translator populating the given string builder
/// with command text. Command text builder and command tree must not be null.
/// </summary>
/// <param name="commandText"> Command text with which to populate commands </param>
/// <param name="commandTree"> Command tree generating SQL </param>
/// <param name="preserveMemberValues"> Indicates whether the translator should preserve member values while compiling t-SQL (only needed for server generation) </param>
internal ExpressionTranslator(
StringBuilder commandText, DbModificationCommandTree commandTree,
bool preserveMemberValues, SqlVersion version)
{
Debug.Assert(null != commandText);
Debug.Assert(null != commandTree);
_commandText = commandText;
_commandTree = commandTree;
_version = version;
_parameters = new List<SqlParameter>();
_memberValues = preserveMemberValues
? new Dictionary<EdmMember, SqlParameter>()
: null;
}
/// <summary>
/// Determine whether we should use a generated values variable to return server generated values.
/// This is true when we're attempting to insert a row where the primary key is server generated
/// but is not an integer type (and therefore can't be used with scope_identity()). It is also true
/// where there is a compound server generated key.
/// </summary>
private static bool UseGeneratedValuesVariable(DbInsertCommandTree tree, SqlVersion sqlVersion)
{
var useGeneratedValuesVariable = false;
if (sqlVersion > SqlVersion.Sql8
&& tree.Returning != null)
{
// Figure out which columns have values
var columnsWithValues =
new HashSet<EdmMember>(tree.SetClauses.Cast<DbSetClause>().Select(s => ((DbPropertyExpression)s.Property).Property));
// Only SQL Server 2005+ support an output clause for inserts
var firstKeyFound = false;
foreach (var keyMember in ((DbScanExpression)tree.Target.Expression).Target.ElementType.KeyMembers)
{
if (!columnsWithValues.Contains(keyMember))
{
if (firstKeyFound)
{
// compound server gen key
useGeneratedValuesVariable = true;
break;
}
else
{
firstKeyFound = true;
if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
// unsupported type
useGeneratedValuesVariable = true;
break;
}
}
}
}
}
return useGeneratedValuesVariable;
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, SqlVersion sqlVersion, out List<SqlParameter> parameters)
{
var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
var translator = new ExpressionTranslator(
commandText, tree,
null != tree.Returning, sqlVersion);
var useGeneratedValuesVariable = UseGeneratedValuesVariable(tree, sqlVersion);
var tableType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType;
if (useGeneratedValuesVariable)
{
// manufacture the variable, e.g. "declare @generated_values table(id uniqueidentifier)"
commandText
.Append("declare ")
.Append(s_generatedValuesVariableName)
.Append(" table(");
var first = true;
foreach (var column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
var columnType = SqlGenerator.GenerateSqlForStoreType(sqlVersion, column.TypeUsage);
if (columnType == "rowversion"
|| columnType == "timestamp")
{
// rowversion and timestamp are intrinsically read-only. use binary to gather server generated
// values for these types.
columnType = "binary(8)";
}
commandText
.Append(GenerateMemberTSql(column))
.Append(" ")
.Append(columnType);
Facet collationFacet;
if (column.TypeUsage.Facets.TryGetValue(DbProviderManifest.CollationFacetName, false, out collationFacet))
{
var collation = collationFacet.Value as string;
if (!string.IsNullOrEmpty(collation))
{
commandText.Append(" collate ").Append(collation);
}
}
}
Debug.Assert(!first, "if useGeneratedValuesVariable is true, it implies some columns do not have values");
commandText.AppendLine(")");
}
// insert [schemaName].[tableName]
commandText.Append("insert ");
tree.Target.Expression.Accept(translator);
if (0 < tree.SetClauses.Count)
{
// (c1, c2, c3, ...)
commandText.Append("(");
var first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
}
else
{
commandText.AppendLine();
}
if (useGeneratedValuesVariable)
{
// output inserted.id into @generated_values
commandText.Append("output ");
var first = true;
foreach (var column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
commandText.Append("inserted.");
commandText.Append(GenerateMemberTSql(column));
}
commandText
.Append(" into ")
.AppendLine(s_generatedValuesVariableName);
}
if (0 < tree.SetClauses.Count)
{
// values c1, c2, ...
var first = true;
commandText.Append("values (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
}
else
{
// default values
commandText.AppendLine("default values");
}
// generate returning sql
GenerateReturningSql(commandText, tree, tableType, translator, tree.Returning, useGeneratedValuesVariable);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static bool IsPreKatmai(SqlVersion sqlVersion)
{
return sqlVersion == SqlVersion.Sql8 || sqlVersion == SqlVersion.Sql9;
}
/// <summary>
/// Creates a SqlParameter given a name, type, and direction
/// </summary>
internal static SqlParameter CreateSqlParameter(string name, TypeUsage type, ParameterMode mode, object value, bool preventTruncation, SqlVersion version) {
int? size;
byte? precision;
byte? scale;
string udtTypeName;
value = EnsureSqlParameterValue(value);
SqlParameter result = new SqlParameter(name, value);
// .Direction
ParameterDirection direction = MetadataHelper.ParameterModeToParameterDirection(mode);
if (result.Direction != direction) {
result.Direction = direction;
}
// .Size, .Precision, .Scale and .SqlDbType
// output parameters are handled differently (we need to ensure there is space for return
// values where the user has not given a specific Size/MaxLength)
bool isOutParam = mode != ParameterMode.In;
SqlDbType sqlDbType = GetSqlDbType(type, isOutParam, version, out size, out precision, out scale, out udtTypeName);
if (result.SqlDbType != sqlDbType) {
result.SqlDbType = sqlDbType;
}
if (sqlDbType == SqlDbType.Udt)
{
result.UdtTypeName = udtTypeName;
}
// Note that we overwrite 'facet' parameters where either the value is different or
// there is an output parameter. This is because output parameters in SqlClient have their
// facets clobbered if they are implicitly set (e.g. if the Size was implicitly set
// by setting the value)
if (size.HasValue)
{
// size.HasValue is always true for Output parameters
if ((isOutParam || result.Size != size.Value))
{
if (preventTruncation && size.Value != -1)
{
// To prevent truncation, set the Size of the parameter to the larger of either
// the declared length or the actual length for the parameter. This allows SQL
// Server to complain if a value is too long while preventing cache misses for
// values within the range.
result.Size = Math.Max(result.Size, size.Value);
}
else
{
result.Size = size.Value;
}
}
}
else
{
PrimitiveTypeKind typeKind = MetadataHelper.GetPrimitiveTypeKind(type);
if (typeKind == PrimitiveTypeKind.String)
{
result.Size = GetDefaultStringMaxLength(version, sqlDbType);
}
else if(typeKind == PrimitiveTypeKind.Binary)
{
result.Size = GetDefaultBinaryMaxLength(version);
}
}
if (precision.HasValue && (isOutParam || result.Precision != precision.Value)) {
result.Precision = precision.Value;
}
if (scale.HasValue && (isOutParam || result.Scale != scale.Value)) {
result.Scale = scale.Value;
}
// .IsNullable
bool isNullable = TypeSemantics.IsNullable(type);
if (isOutParam || isNullable != result.IsNullable) {
result.IsNullable = isNullable;
}
return result;
}
/// <summary>
/// Determines SqlDbType for the given primitive type. Extracts facet
/// information as well.
/// </summary>
private static SqlDbType GetSqlDbType(TypeUsage type, bool isOutParam, SqlVersion version, out int? size, out byte? precision, out byte? scale, out string udtName) {
// only supported for primitive type
PrimitiveTypeKind primitiveTypeKind = MetadataHelper.GetPrimitiveTypeKind(type);
size = default(int?);
precision = default(byte?);
scale = default(byte?);
udtName = default(string);
//
switch (primitiveTypeKind) {
case PrimitiveTypeKind.Binary:
// for output parameters, ensure there is space...
size = GetParameterSize(type, isOutParam);
return GetBinaryDbType(type);
case PrimitiveTypeKind.Boolean:
return SqlDbType.Bit;
case PrimitiveTypeKind.Byte:
return SqlDbType.TinyInt;
case PrimitiveTypeKind.Time:
if (!SqlVersionUtils.IsPreKatmai(version)) {
precision = GetKatmaiDateTimePrecision(type, isOutParam);
}
return SqlDbType.Time;
case PrimitiveTypeKind.DateTimeOffset:
if (!SqlVersionUtils.IsPreKatmai(version)) {
precision = GetKatmaiDateTimePrecision(type, isOutParam);
}
return SqlDbType.DateTimeOffset;
case PrimitiveTypeKind.DateTime:
//For katmai pick the type with max precision which is datetime2
if (!SqlVersionUtils.IsPreKatmai(version)) {
precision = GetKatmaiDateTimePrecision(type, isOutParam);
return SqlDbType.DateTime2;
}
else {
return SqlDbType.DateTime;
}
case PrimitiveTypeKind.Decimal:
precision = GetParameterPrecision(type, null);
scale = GetScale(type);
return SqlDbType.Decimal;
case PrimitiveTypeKind.Double:
return SqlDbType.Float;
case PrimitiveTypeKind.Geography:
{
udtName = "geography";
return SqlDbType.Udt;
}
case PrimitiveTypeKind.Geometry:
{
udtName = "geometry";
return SqlDbType.Udt;
}
case PrimitiveTypeKind.Guid:
return SqlDbType.UniqueIdentifier;
case PrimitiveTypeKind.Int16:
return SqlDbType.SmallInt;
case PrimitiveTypeKind.Int32:
return SqlDbType.Int;
case PrimitiveTypeKind.Int64:
return SqlDbType.BigInt;
case PrimitiveTypeKind.SByte:
return SqlDbType.SmallInt;
case PrimitiveTypeKind.Single:
return SqlDbType.Real;
case PrimitiveTypeKind.String:
size = GetParameterSize(type, isOutParam);
return GetStringDbType(type);
default:
Debug.Fail("unknown PrimitiveTypeKind " + primitiveTypeKind);
return SqlDbType.Variant;
}
}
private static int GetDefaultStringMaxLength(SqlVersion version, SqlDbType type)
{
int result;
if (version < SqlVersion.Sql9)
{
if (type == SqlDbType.NChar || type == SqlDbType.NVarChar)
{
result = 4000;
}
else
{
result = 8000;
}
}
else
{
result = -1;
}
return result;
}
private static string CreateObjectsScript(SqlVersion version, StoreItemCollection storeItemCollection)
{
return SqlDdlBuilder.CreateObjectsScript(storeItemCollection, createSchemas: version != SqlVersion.Sql8);
}
private static int GetDefaultBinaryMaxLength(SqlVersion version)
{
int result;
if (version < SqlVersion.Sql9)
{
result = 8000;
}
else
{
result = -1;
}
return result;
}
private static string BuildSqlForDateTimeOffset(DateTimeOffset dateTimeOffset, SqlVersion sqlVersion)
{
var builder = new StringBuilder();
var sqlGenerator = new SqlGenerator(sqlVersion);
var functionExpression = EdmFunctions.CreateDateTimeOffset(
DbExpression.FromInt32(dateTimeOffset.Year),
DbExpression.FromInt32(dateTimeOffset.Month),
DbExpression.FromInt32(dateTimeOffset.Day),
DbExpression.FromInt32(dateTimeOffset.Hour),
DbExpression.FromInt32(dateTimeOffset.Minute),
DbExpression.FromInt32(dateTimeOffset.Second),
DbExpression.FromInt32(Convert.ToInt32(dateTimeOffset.Offset.TotalMinutes)));
var sqlFragment = SqlFunctionCallHandler.GenerateFunctionCallSql(
sqlGenerator, functionExpression);
using (var sqlWriter = new SqlWriter(builder))
{
sqlFragment.WriteSql(sqlWriter, sqlGenerator);
}
return builder.ToString();
}
/// <summary>
/// Detect and memorize the connected SQL Server version
/// </summary>
private void DetectServerVersion()
{
string sqlCmd = string.Empty;
string result;
/*
* Find the query to use to get the version
*/
WriteLog("Detecting Sql Server version...");
XPathDocument vd = new XPathDocument(_appPath + "General.VersionDetect.xml");
XPathNavigator nav = vd.CreateNavigator();
XPathNodeIterator nodeIterator = nav.Select("/databases/database[@name='" + databaseServerName + "']/query");
while (nodeIterator.MoveNext())
{
sqlCmd = nodeIterator.Current.GetAttribute("value", string.Empty);
}
/*
* Execute the query
*/
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
try
{
result = (string)cmd.ExecuteScalar();
}
catch (Exception)
{
result = string.Empty;
}
finally
{
cmd.Dispose();
}
if (result == null) result = string.Empty;
result = result.ToLower();
/*
* Perform matching against the xml version file
*/
WriteLog("Loading VersionsInfo module...");
vd = new XPathDocument(_appPath + "SqlServer.VersionsInfo.xml");
nav = vd.CreateNavigator();
nodeIterator = nav.Select("/databases/database[@name='" + databaseServerName + "']/version");
while (nodeIterator.MoveNext())
{
string fromXML = nodeIterator.Current.GetAttribute("identifier", string.Empty);
string fromID = nodeIterator.Current.GetAttribute("id", string.Empty);
if (result.StartsWith(fromXML) )
{
version = (SqlVersion)Enum.Parse(version.GetType(), fromID, false);
versionDescription = nodeIterator.Current.GetAttribute("name", string.Empty);
interfaceFile = nodeIterator.Current.GetAttribute("interface", string.Empty);
}
}
WriteLog(String.Format("Detected Sql Server Version: {0}.", versionDescription));
}
