internal static string CreateDatabaseScript(string databaseName, string dataFileName, string logFileName, SqlVersion sqlVersion)
{
var builder = new SqlDdlBuilder();
builder.AppendSql("create database ");
builder.AppendIdentifier(databaseName);
if (null != dataFileName)
{
Debug.Assert(logFileName != null, "must specify log file with data file");
builder.AppendSql(" on primary ");
builder.AppendFileName(dataFileName);
builder.AppendSql(" log on ");
builder.AppendFileName(logFileName);
}
// Set READ_COMMITTED_SNAPSHOT ON, if SQL Server 2005 and up and not SQLAzure.
if (sqlVersion >= SqlVersion.Sql9)
{
builder.AppendNewLine();
builder.AppendSql("if serverproperty('EngineEdition') <> 5 alter database ");
builder.AppendIdentifier(databaseName);
builder.AppendSql(" set read_committed_snapshot on");
}
return(builder.unencodedStringBuilder.ToString());
}
private static string SetReadCommittedSnapshotScript(string databaseName)
{
SqlDdlBuilder sqlDdlBuilder = new SqlDdlBuilder();
sqlDdlBuilder.AppendSql("alter database ");
sqlDdlBuilder.AppendIdentifier(databaseName);
sqlDdlBuilder.AppendSql(" set read_committed_snapshot on");
return(sqlDdlBuilder.unencodedStringBuilder.ToString());
}
internal static string CreateDatabaseExistsScript(string databaseName, bool useDeprecatedSystemTable)
{
var builder = new SqlDdlBuilder();
builder.AppendSql("SELECT Count(*) FROM ");
AppendSysDatabases(builder, useDeprecatedSystemTable);
builder.AppendSql(" WHERE [name]=");
builder.AppendStringLiteral(databaseName);
return(builder.unencodedStringBuilder.ToString());
}
internal static string CreateDatabaseExistsScript(string databaseName)
{
SqlDdlBuilder sqlDdlBuilder = new SqlDdlBuilder();
sqlDdlBuilder.AppendSql("IF db_id(");
sqlDdlBuilder.AppendStringLiteral(databaseName);
sqlDdlBuilder.AppendSql(") IS NOT NULL SELECT 1 ELSE SELECT Count(*) FROM sys.databases WHERE [name]=");
sqlDdlBuilder.AppendStringLiteral(databaseName);
return(sqlDdlBuilder.unencodedStringBuilder.ToString());
}
private static void AppendSysDatabases(SqlDdlBuilder builder, bool useDeprecatedSystemTable)
{
if (useDeprecatedSystemTable)
{
builder.AppendSql("sysdatabases");
}
else
{
builder.AppendSql("sys.databases");
}
}
internal static string CreateDatabaseScript(string databaseName, string dataFileName, string logFileName)
{
var builder = new SqlDdlBuilder();
builder.AppendSql("create database ");
builder.AppendIdentifier(databaseName);
if (null != dataFileName)
{
Debug.Assert(logFileName != null, "must specify log file with data file");
builder.AppendSql(" on primary ");
builder.AppendFileName(dataFileName);
builder.AppendSql(" log on ");
builder.AppendFileName(logFileName);
}
return(builder.unencodedStringBuilder.ToString());
}
internal static string DropDatabaseScript(string databaseName)
{
SqlDdlBuilder sqlDdlBuilder = new SqlDdlBuilder();
sqlDdlBuilder.AppendSql("drop database ");
sqlDdlBuilder.AppendIdentifier(databaseName);
return(sqlDdlBuilder.unencodedStringBuilder.ToString());
}
internal static string CreateCountDatabasesBasedOnFileNameScript(string databaseFileName, bool useDeprecatedSystemTable)
{
var builder = new SqlDdlBuilder();
builder.AppendSql("SELECT Count(*) FROM ");
if (useDeprecatedSystemTable)
{
builder.AppendSql("sysdatabases");
}
if (!useDeprecatedSystemTable)
{
builder.AppendSql("sys.master_files");
}
builder.AppendSql(" WHERE [");
if (useDeprecatedSystemTable)
{
builder.AppendSql("filename");
}
else
{
builder.AppendSql("physical_name");
}
builder.AppendSql("]=");
builder.AppendStringLiteral(databaseFileName);
return(builder.unencodedStringBuilder.ToString());
}
internal static string CreateGetDatabaseNamesBasedOnFileNameScript(
string databaseFileName,
bool useDeprecatedSystemTable)
{
SqlDdlBuilder builder = new SqlDdlBuilder();
builder.AppendSql("SELECT [d].[name] FROM ");
SqlDdlBuilder.AppendSysDatabases(builder, useDeprecatedSystemTable);
builder.AppendSql(" AS [d] ");
if (!useDeprecatedSystemTable)
{
builder.AppendSql("INNER JOIN sys.master_files AS [f] ON [f].[database_id] = [d].[database_id]");
}
builder.AppendSql(" WHERE [");
if (useDeprecatedSystemTable)
{
builder.AppendSql("filename");
}
else
{
builder.AppendSql("f].[physical_name");
}
builder.AppendSql("]=");
builder.AppendStringLiteral(databaseFileName);
return(builder.unencodedStringBuilder.ToString());
}
internal static string CreateDatabaseScript(
string databaseName,
string dataFileName,
string logFileName)
{
SqlDdlBuilder sqlDdlBuilder = new SqlDdlBuilder();
sqlDdlBuilder.AppendSql("create database ");
sqlDdlBuilder.AppendIdentifier(databaseName);
if (dataFileName != null)
{
sqlDdlBuilder.AppendSql(" on primary ");
sqlDdlBuilder.AppendFileName(dataFileName);
sqlDdlBuilder.AppendSql(" log on ");
sqlDdlBuilder.AppendFileName(logFileName);
}
return(sqlDdlBuilder.unencodedStringBuilder.ToString());
}
internal static string CreateDatabaseExistsScript(string databaseName)
{
// This handles non-SQL Azure servers by using db_id() which will return a non null value for
// a database on the server even if the login doesn't have "view any database" permission.
// On SQL Azure db_id() only works for the current database, which means it will never work here
// because the current database is master. In this case we fall back to the query from sys.databases.
// This doesn't work for non-SQL Azure if the login does not have 'view any database'
// permission, but that case was covered by db_id(). On SQL Azure there are no server class
// securables, which means it is not possible to deny the login 'view any database' so in theory
// if the user can connect to master then the sys.databases query will work.
var builder = new SqlDdlBuilder();
builder.AppendSql("IF db_id(");
builder.AppendStringLiteral(databaseName);
builder.AppendSql(") IS NOT NULL SELECT 1 ELSE SELECT Count(*) FROM sys.databases WHERE [name]=");
builder.AppendStringLiteral(databaseName);
return(builder.unencodedStringBuilder.ToString());
}
internal static string SetDatabaseOptionsScript(SqlVersion sqlVersion, string databaseName)
{
if (sqlVersion < SqlVersion.Sql9)
{
return(string.Empty);
}
SqlDdlBuilder sqlDdlBuilder = new SqlDdlBuilder();
sqlDdlBuilder.AppendSql("if serverproperty('EngineEdition') <> 5 execute sp_executesql ");
sqlDdlBuilder.AppendStringLiteral(SqlDdlBuilder.SetReadCommittedSnapshotScript(databaseName));
return(sqlDdlBuilder.unencodedStringBuilder.ToString());
}
private void AppendCreateSchema(string schema)
{
this.AppendSql("if (schema_id(");
this.AppendStringLiteral(schema);
this.AppendSql(") is null) exec(");
SqlDdlBuilder sqlDdlBuilder = new SqlDdlBuilder();
sqlDdlBuilder.AppendSql("create schema ");
sqlDdlBuilder.AppendIdentifier(schema);
this.AppendStringLiteral(sqlDdlBuilder.unencodedStringBuilder.ToString());
this.AppendSql(");");
this.AppendNewLine();
}
internal static string SetDatabaseOptionsScript(SqlVersion sqlVersion, string databaseName)
{
if (sqlVersion < SqlVersion.Sql9)
{
return(String.Empty);
}
var builder = new SqlDdlBuilder();
// Set READ_COMMITTED_SNAPSHOT ON, if SQL Server 2005 and up, and not SQLAzure.
builder.AppendSql("if serverproperty('EngineEdition') <> 5 execute sp_executesql ");
builder.AppendStringLiteral(SetReadCommittedSnapshotScript(databaseName));
return(builder.unencodedStringBuilder.ToString());
}
private void AppendCreateSchema(string schema)
{
AppendSql("if (schema_id(");
AppendStringLiteral(schema);
AppendSql(") is null) exec(");
// need to create a sub-command and escape it as a string literal as well...
var schemaBuilder = new SqlDdlBuilder();
schemaBuilder.AppendSql("create schema ");
schemaBuilder.AppendIdentifier(schema);
AppendStringLiteral(schemaBuilder.unencodedStringBuilder.ToString());
AppendSql(");");
AppendNewLine();
}
