public SqlMainDomLock(
ILogger <SqlMainDomLock> logger,
ILoggerFactory loggerFactory,
IOptions <GlobalSettings> globalSettings,
IOptionsMonitor <ConnectionStrings> connectionStrings,
IDbProviderFactoryCreator dbProviderFactoryCreator,
IHostingEnvironment hostingEnvironment,
DatabaseSchemaCreatorFactory databaseSchemaCreatorFactory,
NPocoMapperCollection npocoMappers,
string connectionStringName)
{
// unique id for our appdomain, this is more unique than the appdomain id which is just an INT counter to its safer
_lockId = Guid.NewGuid().ToString();
_logger = logger;
_globalSettings = globalSettings;
_sqlServerSyntax = new SqlServerSyntaxProvider(_globalSettings);
_hostingEnvironment = hostingEnvironment;
_dbFactory = new UmbracoDatabaseFactory(
loggerFactory.CreateLogger <UmbracoDatabaseFactory>(),
loggerFactory,
_globalSettings,
new MapperCollection(() => Enumerable.Empty <BaseMapper>()),
dbProviderFactoryCreator,
databaseSchemaCreatorFactory,
npocoMappers,
connectionStringName);
MainDomKey = MainDomKeyPrefix + "-" + (Environment.MachineName + MainDom.GetMainDomId(_hostingEnvironment)).GenerateHash <SHA1>();
}
public SqlMainDomLock(
ILoggerFactory loggerFactory,
IOptions <GlobalSettings> globalSettings,
IOptionsMonitor <ConnectionStrings> connectionStrings,
IDbProviderFactoryCreator dbProviderFactoryCreator,
IMainDomKeyGenerator mainDomKeyGenerator,
DatabaseSchemaCreatorFactory databaseSchemaCreatorFactory,
NPocoMapperCollection npocoMappers)
{
// unique id for our appdomain, this is more unique than the appdomain id which is just an INT counter to its safer
_lockId = Guid.NewGuid().ToString();
_logger = loggerFactory.CreateLogger <SqlMainDomLock>();
_globalSettings = globalSettings;
_sqlServerSyntax = new SqlServerSyntaxProvider(_globalSettings);
_dbFactory = new UmbracoDatabaseFactory(
loggerFactory.CreateLogger <UmbracoDatabaseFactory>(),
loggerFactory,
_globalSettings,
new MapperCollection(() => Enumerable.Empty <BaseMapper>()),
dbProviderFactoryCreator,
databaseSchemaCreatorFactory,
npocoMappers,
connectionStrings.CurrentValue.UmbracoConnectionString.ConnectionString);
MainDomKey = MainDomKeyPrefix + "-" + mainDomKeyGenerator.GenerateKey();
}
/// <summary>
/// Logic used to perform bulk inserts with SqlServer's BulkCopy
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="sqlSyntaxProvider"></param>
/// <param name="pd"></param>
/// <param name="collection"></param>
/// <returns></returns>
internal static int BulkInsertRecordsSqlServer <T>(Database db, SqlServerSyntaxProvider sqlSyntaxProvider,
Database.PocoData pd, IEnumerable <T> collection)
{
//NOTE: We need to use the original db.Connection here to create the command, but we need to pass in the typed
// connection below to the SqlBulkCopy
using (var cmd = db.CreateCommand(db.Connection, string.Empty))
{
using (var copy = new SqlBulkCopy(
GetTypedConnection <SqlConnection>(db.Connection),
SqlBulkCopyOptions.Default,
GetTypedTransaction <SqlTransaction>(cmd.Transaction))
{
BulkCopyTimeout = 10000,
DestinationTableName = pd.TableInfo.TableName
})
{
//var cols = pd.Columns.Where(x => IncludeColumn(pd, x)).Select(x => x.Value).ToArray();
using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(collection, pd, sqlSyntaxProvider))
{
copy.WriteToServer(bulkReader);
return(bulkReader.RecordsAffected);
}
}
}
}
protected Lazy <ISqlContext> MockSqlContext()
{
var sqlContext = Mock.Of <ISqlContext>();
var syntax = new SqlServerSyntaxProvider(Options.Create(new GlobalSettings()));
Mock.Get(sqlContext).Setup(x => x.SqlSyntax).Returns(syntax);
return(new Lazy <ISqlContext>(() => sqlContext));
}
private MigrationContext GetMigrationContext(out TestDatabase db)
{
ILogger <MigrationContext> logger = Mock.Of <ILogger <MigrationContext> >();
SqlServerSyntaxProvider sqlSyntax = GetSqlSyntax();
db = new TestDatabase(DatabaseType.SqlServer2005, sqlSyntax);
return(new MigrationContext(new TestPlan(), db, logger));
}
/// <summary>
/// Gets an UmbracoDatabase.
/// </summary>
/// <param name="logger">A logger.</param>
/// <returns>An UmbracoDatabase.</returns>
/// <remarks>This is just a void database that has no actual database but pretends to have an open connection
/// that can begin a transaction.</remarks>
public UmbracoDatabase GetUmbracoSqlServerDatabase(ILogger logger)
{
var syntax = new SqlServerSyntaxProvider(); // do NOT try to get the server's version!
var connection = GetDbConnection();
var sqlContext = new SqlContext(syntax, DatabaseType.SqlServer2008, Mock.Of <IPocoDataFactory>());
return(new UmbracoDatabase(connection, sqlContext, logger));
}
/// <summary>
/// Gets an UmbracoDatabase.
/// </summary>
/// <param name="logger">A logger.</param>
/// <returns>An UmbracoDatabase.</returns>
/// <remarks>This is just a void database that has no actual database but pretends to have an open connection
/// that can begin a transaction.</remarks>
public UmbracoDatabase GetUmbracoSqlServerDatabase(ILogger <UmbracoDatabase> logger)
{
var syntax = new SqlServerSyntaxProvider(Options.Create(new GlobalSettings())); // do NOT try to get the server's version!
var connection = GetDbConnection();
var sqlContext = new SqlContext(syntax, DatabaseType.SqlServer2008, Mock.Of <IPocoDataFactory>());
return(new UmbracoDatabase(connection, sqlContext, logger, TestHelper.BulkSqlInsertProvider));
}
public void Setup()
{
var logger = new DebugDiagnosticsLogger();
var path = TestHelper.CurrentAssemblyDirectory;
_sqlCeSyntax = new SqlCeSyntaxProvider();
_sqlServerSyntax = new SqlServerSyntaxProvider();
SetupSqlCe(path, logger);
SetupSqlServer(logger);
}
public void Equals_Method_For_Value_Gets_Escaped()
{
var sqlSyntax = new SqlServerSyntaxProvider(Options.Create(new GlobalSettings()));
Expression <Func <IUser, bool> > predicate = user => user.Username.Equals("*****@*****.**");
var modelToSqlExpressionHelper = new ModelToSqlExpressionVisitor <IUser>(SqlContext.SqlSyntax, Mappers);
var result = modelToSqlExpressionHelper.Visit(predicate);
Debug.Print("Model to Sql ExpressionHelper: \n" + result);
Assert.AreEqual("upper([umbracoUser].[userLogin]) = upper(@0)", result);
Assert.AreEqual("*****@*****.**", modelToSqlExpressionHelper.GetSqlParameters()[0]);
}
public void Format_SqlServer_NonClusteredIndexDefinition_UsingIsClusteredFalse_AddsClusteredDirective()
{
var sqlSyntax = new SqlServerSyntaxProvider();
var indexDefinition = CreateIndexDefinition();
indexDefinition.IndexType = IndexTypes.Clustered;
var actual = sqlSyntax.Format(indexDefinition);
Assert.AreEqual("CREATE CLUSTERED INDEX [IX_A] ON [TheTable] ([A])", actual);
}
public void CreateIndexBuilder_SqlServer_Clustered_CreatesClusteredIndex()
{
var sqlSyntax = new SqlServerSyntaxProvider();
var createExpression = new CreateIndexExpression(DatabaseProviders.SqlServer, new[] { DatabaseProviders.SqlServer }, sqlSyntax)
{
Index = { Name = "IX_A" }
};
var builder = new CreateIndexBuilder(createExpression);
builder.OnTable("TheTable").OnColumn("A").Ascending().WithOptions().Clustered();
Assert.AreEqual("CREATE CLUSTERED INDEX [IX_A] ON [TheTable] ([A])", createExpression.ToString());
}
public void CreateIndexBuilder_SqlServer_Unique_CreatesUniqueNonClusteredIndex_Multi_Columnn()
{
var sqlSyntax = new SqlServerSyntaxProvider();
var createExpression = new CreateIndexExpression(DatabaseProviders.SqlServer, new[] { DatabaseProviders.SqlServer }, sqlSyntax)
{
Index = { Name = "IX_AB" }
};
var builder = new CreateIndexBuilder(createExpression);
builder.OnTable("TheTable").OnColumn("A").Ascending().OnColumn("B").Ascending().WithOptions().Unique();
Assert.AreEqual("CREATE UNIQUE NONCLUSTERED INDEX [IX_AB] ON [TheTable] ([A],[B])", createExpression.ToString());
}
public void Format_SqlServer_NonClusteredIndexDefinition_AddsNonClusteredDirective()
{
SqlServerSyntaxProvider sqlSyntax = GetSqlSyntax();
var indexDefinition = CreateIndexDefinition();
indexDefinition.IndexType = IndexTypes.NonClustered;
var actual = sqlSyntax.Format(indexDefinition);
Assert.AreEqual("CREATE NONCLUSTERED INDEX [IX_A] ON [TheTable] ([A])", actual);
}
public void Can_Bulk_Insert_Native_Sql_Server_Bulk_Inserts()
{
//create the db
var dbSqlServer = new UmbracoDatabase(
"server=.\\SQLExpress;database=YOURDB;user id=YOURUSER;password=YOURPASSWORD",
Constants.DatabaseProviders.SqlServer,
new DebugDiagnosticsLogger());
//drop the table
dbSqlServer.Execute("DROP TABLE [umbracoServer]");
//re-create it
dbSqlServer.Execute(@"CREATE TABLE [umbracoServer](
[id] [int] IDENTITY(1,1) NOT NULL,
[address] [nvarchar](500) NOT NULL,
[computerName] [nvarchar](255) NOT NULL,
[registeredDate] [datetime] NOT NULL CONSTRAINT [DF_umbracoServer_registeredDate] DEFAULT (getdate()),
[lastNotifiedDate] [datetime] NOT NULL,
[isActive] [bit] NOT NULL,
[isMaster] [bit] NOT NULL,
CONSTRAINT [PK_umbracoServer] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)");
var data = new List <ServerRegistrationDto>();
for (var i = 0; i < 1000; i++)
{
data.Add(new ServerRegistrationDto
{
ServerAddress = "address" + i,
ServerIdentity = "computer" + i,
DateRegistered = DateTime.Now,
IsActive = true,
DateAccessed = DateTime.Now
});
}
var sqlServerSyntax = new SqlServerSyntaxProvider();
using (var tr = dbSqlServer.GetTransaction())
{
dbSqlServer.BulkInsertRecords(data, tr, sqlServerSyntax, useNativeSqlPlatformBulkInsert: true);
tr.Complete();
}
// Assert
Assert.That(dbSqlServer.ExecuteScalar <int>("SELECT COUNT(*) FROM umbracoServer"), Is.EqualTo(1000));
}
public void AliasRegexTest()
{
var regex = new SqlServerSyntaxProvider().AliasRegex;
Assert.AreEqual(@"(\[\w+]\.\[\w+])\s+AS\s+(\[\w+])", regex.ToString());
const string sql = "SELECT [table].[column1] AS [alias1], [table].[column2] AS [alias2] FROM [table];";
var matches = regex.Matches(sql);
Assert.AreEqual(2, matches.Count);
Assert.AreEqual("[table].[column1]", matches[0].Groups[1].Value);
Assert.AreEqual("[alias1]", matches[0].Groups[2].Value);
Assert.AreEqual("[table].[column2]", matches[1].Groups[1].Value);
Assert.AreEqual("[alias2]", matches[1].Groups[2].Value);
}
public void CreateIndexBuilder_SqlServer_Clustered_CreatesClusteredIndex()
{
var logger = Mock.Of <ILogger>();
var sqlSyntax = new SqlServerSyntaxProvider();
var db = new TestDatabase(DatabaseType.SqlServer2005, sqlSyntax);
var context = new MigrationContext(db, logger);
var createExpression = new CreateIndexExpression(context)
{
Index = { Name = "IX_A" }
};
new CreateIndexBuilder(createExpression)
.OnTable("TheTable").OnColumn("A").Ascending().WithOptions().Clustered()
.Do();
Assert.AreEqual(1, db.Operations.Count);
Assert.AreEqual("CREATE CLUSTERED INDEX [IX_A] ON [TheTable] ([A])", db.Operations[0].Sql);
}
public virtual void Setup()
{
IServiceCollection container = TestHelper.GetServiceCollection();
TypeLoader typeLoader = TestHelper.GetMockedTypeLoader();
var composition = new UmbracoBuilder(container, Mock.Of <IConfiguration>(), TestHelper.GetMockedTypeLoader());
composition.WithCollectionBuilder <MapperCollectionBuilder>()
.AddCoreMappers();
composition.Services.AddUnique(_ => SqlContext);
IServiceProvider factory = composition.CreateServiceProvider();
var pocoMappers = new NPoco.MapperCollection
{
new NullableDateMapper()
};
var pocoDataFactory = new FluentPocoDataFactory((type, iPocoDataFactory) => new PocoDataBuilder(type, pocoMappers).Init());
var sqlSyntax = new SqlServerSyntaxProvider(Options.Create(new GlobalSettings()));
SqlContext = new SqlContext(sqlSyntax, DatabaseType.SqlServer2012, pocoDataFactory, factory.GetRequiredService <IMapperCollection>());
Mappers = factory.GetRequiredService <IMapperCollection>();
}
public override void Up()
{
//now that the controlId column is renamed and now a string we need to convert
if (Context == null || Context.Database == null)
{
return;
}
//var cpt = SqlSyntaxContext.SqlSyntaxProvider.GetConstraintsPerTable(Context.Database);
//var di = SqlSyntaxContext.SqlSyntaxProvider.GetDefinedIndexes(Context.Database);
if (Context.CurrentDatabaseProvider != DatabaseProviders.SqlServer)
{
Delete.DefaultConstraint().OnTable("cmsMacroProperty").OnColumn("macroPropertyHidden");
}
else
{
//If we are on SQLServer, we need to delete default constraints by name, older versions of umbraco did not name these default constraints
// consistently so we need to look up the constraint name to delete, this only pertains to SQL Server and this issue:
// http://issues.umbraco.org/issue/U4-4133
var sqlServerSyntaxProvider = new SqlServerSyntaxProvider();
var defaultConstraints = sqlServerSyntaxProvider.GetDefaultConstraintsPerColumn(Context.Database).Distinct();
//lookup the constraint we want to delete, normally would be called "DF_cmsMacroProperty_macroPropertyHidden" but
// we cannot be sure with really old versions
var constraint = defaultConstraints
.SingleOrDefault(x => x.Item1 == "cmsMacroProperty" && x.Item2 == "macroPropertyHidden");
if (constraint != null)
{
Execute.Sql(string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]", "cmsMacroProperty", constraint.Item3));
}
}
Delete.Column("macroPropertyHidden").FromTable("cmsMacroProperty");
if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
Delete.ForeignKey().FromTable("cmsMacroProperty").ForeignColumn("macroPropertyType").ToTable("cmsMacroPropertyType").PrimaryColumn("id");
}
else
{
//Before we try to delete this constraint, we'll see if it exists first, some older schemas never had it and some older schema's had this named
// differently than the default.
var keyConstraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct();
var constraint = keyConstraints
.SingleOrDefault(x => x.Item1 == "cmsMacroProperty" && x.Item2 == "macroPropertyType" && x.Item3.InvariantStartsWith("PK_") == false);
if (constraint != null)
{
Delete.ForeignKey(constraint.Item3).OnTable("cmsMacroProperty");
}
}
Alter.Table("cmsMacroProperty").AddColumn("editorAlias").AsString(255).NotNullable().WithDefaultValue("");
//we need to get the data and create the migration scripts before we change the actual schema bits below!
var list = Context.Database.Fetch <dynamic>("SELECT * FROM cmsMacroPropertyType");
foreach (var item in list)
{
var alias = item.macroPropertyTypeAlias;
//check if there's a map created
var newAlias = (string)LegacyParameterEditorAliasConverter.GetNewAliasFromLegacyAlias(alias);
if (newAlias.IsNullOrWhiteSpace() == false)
{
alias = newAlias;
}
//update the table with the alias, the current macroPropertyType will contain the original id
Update.Table("cmsMacroProperty").Set(new { editorAlias = alias }).Where(new { macroPropertyType = item.id });
}
//drop the column now
Delete.Column("macroPropertyType").FromTable("cmsMacroProperty");
//drop the default constraint
Delete.DefaultConstraint().OnTable("cmsMacroProperty").OnColumn("editorAlias");
}
public async Task <bool> AcquireLockAsync(int millisecondsTimeout)
{
if (!_dbFactory.Configured)
{
// if we aren't configured, then we're in an install state, in which case we have no choice but to assume we can acquire
return(true);
}
if (!(_dbFactory.SqlContext.SqlSyntax is SqlServerSyntaxProvider sqlServerSyntaxProvider))
{
throw new NotSupportedException("SqlMainDomLock is only supported for Sql Server");
}
_sqlServerSyntax = sqlServerSyntaxProvider;
_logger.Debug <SqlMainDomLock>("Acquiring lock...");
var db = GetDatabase();
var tempId = Guid.NewGuid().ToString();
try
{
db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
// wait to get a write lock
_sqlServerSyntax.WriteLock(db, TimeSpan.FromMilliseconds(millisecondsTimeout), Constants.Locks.MainDom);
}
catch (Exception ex)
{
if (IsLockTimeoutException(ex))
{
_logger.Error <SqlMainDomLock>(ex, "Sql timeout occurred, could not acquire MainDom.");
_hasError = true;
return(false);
}
// unexpected (will be caught below)
throw;
}
var result = InsertLockRecord(tempId); //we change the row to a random Id to signal other MainDom to shutdown
if (result == RecordPersistenceType.Insert)
{
// if we've inserted, then there was no MainDom so we can instantly acquire
// TODO: see the other TODO, could we just delete the row and that would indicate that we
// are MainDom? then we don't leave any orphan rows behind.
InsertLockRecord(_lockId); // so update with our appdomain id
_logger.Debug <SqlMainDomLock>("Acquired with ID {LockId}", _lockId);
return(true);
}
// if we've updated, this means there is an active MainDom, now we need to wait to
// for the current MainDom to shutdown which also requires releasing our write lock
}
catch (Exception ex)
{
ResetDatabase();
// unexpected
_logger.Error <SqlMainDomLock>(ex, "Unexpected error, cannot acquire MainDom");
_hasError = true;
return(false);
}
finally
{
db?.CompleteTransaction();
}
return(await WaitForExistingAsync(tempId, millisecondsTimeout));
}
public async Task <bool> AcquireLockAsync(int millisecondsTimeout)
{
if (!_dbFactory.Configured)
{
// if we aren't configured then we're in an install state, in which case we have no choice but to assume we can acquire
return(true);
}
if (!(_dbFactory.SqlContext.SqlSyntax is SqlServerSyntaxProvider sqlServerSyntaxProvider))
{
throw new NotSupportedException("SqlMainDomLock is only supported for Sql Server");
}
_sqlServerSyntax = sqlServerSyntaxProvider;
_logger.Debug <SqlMainDomLock>("Acquiring lock...");
var tempId = Guid.NewGuid().ToString();
IUmbracoDatabase db = null;
try
{
db = _dbFactory.CreateDatabase();
_hasTable = db.HasTable(Constants.DatabaseSchema.Tables.KeyValue);
if (!_hasTable)
{
// the Db does not contain the required table, we must be in an install state we have no choice but to assume we can acquire
return(true);
}
db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
// wait to get a write lock
_sqlServerSyntax.WriteLock(db, TimeSpan.FromMilliseconds(millisecondsTimeout), Constants.Locks.MainDom);
}
catch (SqlException ex)
{
if (IsLockTimeoutException(ex))
{
_logger.Error <SqlMainDomLock>(ex, "Sql timeout occurred, could not acquire MainDom.");
_errorDuringAcquiring = true;
return(false);
}
// unexpected (will be caught below)
throw;
}
var result = InsertLockRecord(tempId, db); //we change the row to a random Id to signal other MainDom to shutdown
if (result == RecordPersistenceType.Insert)
{
// if we've inserted, then there was no MainDom so we can instantly acquire
InsertLockRecord(_lockId, db); // so update with our appdomain id
_logger.Debug <SqlMainDomLock, string>("Acquired with ID {LockId}", _lockId);
return(true);
}
// if we've updated, this means there is an active MainDom, now we need to wait to
// for the current MainDom to shutdown which also requires releasing our write lock
}
catch (Exception ex)
{
// unexpected
_logger.Error <SqlMainDomLock>(ex, "Unexpected error, cannot acquire MainDom");
_errorDuringAcquiring = true;
return(false);
}
finally
{
db?.CompleteTransaction();
db?.Dispose();
}
return(await WaitForExistingAsync(tempId, millisecondsTimeout).ConfigureAwait(false));
}