// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// Settings
var sqlSettings = new SqlSettings();
Configuration.Bind("SQL", sqlSettings);
services.AddSingleton <SqlSettings>(sqlSettings);
var settings = new Settings();
Configuration.Bind("Configuration", settings);
services.AddSingleton <Settings>(settings);
var pusherSettings = new PusherSettings();
Configuration.Bind("Pusher", pusherSettings);
services.AddSingleton <PusherSettings>(pusherSettings);
// Repositories
services.AddSingleton <IChoreRepository, ChoreRepository>();
// Clients
services.AddSingleton <SqlClient>();
// ServiceBus
services.AddSingleton <ServiceBusClient>();
services.AddHostedService <EndpointInitializer>();
// Message Handlers
services.AddSingleton <CreateChoreHandler>();
services.AddSingleton <DeleteChoreHandler>();
services.AddControllers();
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// Settings
var settings = new SqlSettings();
Configuration.Bind("SQL", settings);
services.AddSingleton <SqlSettings>(settings);
var config = new Settings();
Configuration.Bind("Configuration", config);
services.AddSingleton <ISettings>(config);
// Repositories
services.AddSingleton <IProfileRepository, ProfileRepository>();
services.AddSingleton <IHouseRepository, HouseRepository>();
// Clients
services.AddSingleton <SqlClient>();
// Handlers
services.AddSingleton <CreateProfileHandler>();
// ServiceBus
services.AddSingleton <ServiceBusClient>();
services.AddHostedService <EndpointIntializer>();
services.AddControllers();
}
private static DataAccessResponse CreateDatabaseOnMaster(SqlSettings sqlSettings, string databaseName, RetryPolicy retryPolicy)
{
//Connect to 'master'
ReliableSqlConnection sqlConnectionMaster = new ReliableSqlConnection(
Helpers.SqlConnectionStrings.GenerateConnectionString(sqlSettings, "master"),
retryPolicy);
//Create Database:
var sqlStatement = new StringBuilder();
sqlStatement.Append("IF NOT EXISTS ");
sqlStatement.Append("(SELECT 1 FROM sys.databases WHERE name = N'");
sqlStatement.Append(databaseName);
sqlStatement.Append("') ");
sqlStatement.Append("BEGIN ");
sqlStatement.Append("CREATE DATABASE ");
sqlStatement.Append(databaseName);
sqlStatement.Append(" END");
SqlCommand sqlCommand = sqlConnectionMaster.CreateCommand();
sqlCommand.CommandText = sqlStatement.ToString();
sqlCommand.Connection.OpenWithRetry();
int result = sqlCommand.ExecuteNonQueryWithRetry(); // returns Int indicating number of rows affected
sqlCommand.Connection.Close();
return(new DataAccessResponse {
isSuccess = true
});
}
public SqlCommandStore(EngineConfiguration config)
: base(config)
{
_settings = config.SqlSettings;
_settings.ResolveConnectionString();
_providerFactory = DbProviderFactories.GetFactory(_settings.ProviderName);
_statements = _settings.Statements ?? ProviderStatements[_settings.ProviderName];
}
public SqlCommandStore(EngineConfiguration config)
: base(config)
{
_settings = config.SqlSettings;
_settings.ResolveConnectionString();
_providerFactory = DbProviderFactories.GetFactory(_settings.ProviderName);
_statements = _settings.Statements ?? ProviderStatements[_settings.ProviderName];
}
public static void AddSqlDb(this IServiceCollection services, SqlSettings settings)
{
services.AddDbContext <DataContext>(options =>
options.UseSqlServer(settings.ConnectionString), ServiceLifetime.Transient);
services.AddDefaultIdentity <ApplicationUser>()
.AddEntityFrameworkStores <DataContext>();
}
public DrawContext(DbContextOptions <DrawContext> options, SqlSettings settings) : base(options)
{
_settings = settings;
if (_settings.CommandTimeout != null)
{
this.Database.SetCommandTimeout(_settings.CommandTimeout);
}
}
public SqlDeduplicationSession(string messageId, SqlSettings settings, bool isDuplicate, DbConnection connection, DbTransaction transaction)
{
this.messageId = messageId;
this.settings = settings;
this.connection = connection;
this.transaction = transaction;
IsDuplicate = isDuplicate;
}
public void Initstatement_is_idempotent(SqlSettings settings)
{
var config = new EngineConfiguration();
config.JournalStorage = StorageType.Sql;
config.SqlSettings = settings;
config.SqlSettings.TableName = "test-" + Guid.NewGuid();
config.CreateCommandStore();
config.CreateCommandStore();
}
public static IServiceCollection AddFundingDataZoneDBSettings(IServiceCollection builder, IConfiguration config)
{
ISqlSettings sqlSettings = new SqlSettings();
config.Bind("FDZSqlStorageSettings", sqlSettings);
builder.AddSingleton(sqlSettings);
builder.AddScoped <ISqlConnectionFactory, SqlConnectionFactory>();
builder.AddScoped <IPublishingAreaRepository, PublishingAreaRepository>();
return(builder);
}
public AsgardDatabase(SqlSettings settings)
: base(settings.BifrostConnection, settings.AutomaticallyCreateDatabase)
{
DbObjects = new List <IDatabaseObject>()
{
new DocumentStateTable(),
new QueueItemTable(),
new ConsumerTable(),
new PushVerifyDocumentToQueueProcedure()
};
if (settings.DestroyBifrostDatabase)
{
Destroy();
}
}
public static bool DatabaseExists(SqlSettings sqlSettings, AppSettings appSettings)
{
bool exists = false;
string SqlStatement =
"IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'" + appSettings.databaseName + "') SELECT 'true' ELSE SELECT 'false'";
SqlCommand sqlCommand = new SqlCommand(SqlStatement.ToString(), new SqlConnection(Helpers.SqlConnectionStrings.GenerateConnectionString(sqlSettings, "master")));
sqlCommand.Connection.Open();
exists = Convert.ToBoolean(sqlCommand.ExecuteScalar());
sqlCommand.Connection.Close();
return(exists);
}
public static bool TableExists(string databaseName, string tableName, SqlSettings sqlSettings)
{
bool exists = false;
string SqlStatement =
"IF OBJECT_ID ('dbo." + tableName + "') IS NOT NULL SELECT 'true' ELSE SELECT 'false'";
SqlCommand sqlCommand = new SqlCommand(SqlStatement.ToString(), new SqlConnection(Helpers.SqlConnectionStrings.GenerateConnectionString(sqlSettings, databaseName)));
sqlCommand.Connection.Open();
exists = Convert.ToBoolean(sqlCommand.ExecuteScalar());
sqlCommand.Connection.Close();
return(exists);
}
private void LogSettings()
{
if (!_hostingEnvironment.IsDevelopment())
{
return;
}
JwtSettings jwtSettings = Configuration.GetSettings <JwtSettings>();
_logger.LogInformation($"{nameof(jwtSettings.Issuer)}: {jwtSettings.Issuer}");
_logger.LogInformation($"{nameof(jwtSettings.ExpiryMinutes)}: {jwtSettings.ExpiryMinutes}");
_logger.LogInformation($"{nameof(jwtSettings.Key)}: {jwtSettings.Key}");
SqlSettings sqlSettings = Configuration.GetSettings <SqlSettings>();
_logger.LogInformation($"{nameof(sqlSettings.ConnectionString)}: {sqlSettings.ConnectionString}");
}
public IServiceProvider ConfigureServices(IServiceCollection services)
{
/*services.AddMvc()
* .AddControllersAsServices();*/
services.AddCustomMvc();
services.AddSwaggerDocs();
services.AddControllers().AddNewtonsoftJson();
services.AddJwt();
services.AddRedis();
services.AddCors(options =>
{
options.AddPolicy("CorsPolicy", cors =>
cors.AllowAnyOrigin()
.AllowAnyMethod()
.AllowAnyHeader());
});
var sqlOptions = new SqlSettings();
Configuration.GetSection("sql").Bind(sqlOptions);
services.AddDbContext <IdentityContext>(options =>
options.UseSqlServer(sqlOptions.ConnectionString)
);
services.AddSingleton <IJwtHandler, JwtHandler>();
var builder = new ContainerBuilder();
builder.RegisterAssemblyTypes(Assembly.GetEntryAssembly())
.AsImplementedInterfaces();
builder.Populate(services);
builder.AddRabbitMq();
//builder.RegisterContext<IdentityContext>();
builder.RegisterType <PasswordHasher <User> >().As <IPasswordHasher <User> >();
builder.AddSqlRepository <RefreshToken, IdentityContext>();
builder.AddSqlRepository <User, IdentityContext>();
Container = builder.Build();
return(new AutofacServiceProvider(Container));
}
public void Can_Crawl_Blog()
{
var dbSettings = new SqlSettings();
var database = new AsgardDatabase(dbSettings);
var queueService = new SqlQueueService(database, new QueueItemMapper());
var documentStateService = new SqlDocumentStateService(database, new DocumentStateMapper());
var linkScrapers = new ILinkScraper[] { new AgilityPackHrefLinkScraper() };
var pageConverters = new IPageConverter[] { new WebPageConverter() };
var downloadHandler = new HttpClientDownloadHandler();
var pageHandlers = new IPageHandler[]
{
new BinaryPageHandler(), new NotAuthorizedPageHandler(), new NotFoundPageHandler(), new WebPageHandler()
};
var pageService = new ParallelPageService(linkScrapers, pageConverters, downloadHandler, pageHandlers);
var stateService = new SqlStateService(new StateDatabase(new CommonDatabaseSettings()
{
CommonConnection = dbSettings.BifrostConnection
}));
var connector = new WebCrawlerConnector(queueService, documentStateService, pageService, stateService);
var fetched = connector.ExecuteFetch(new WebConnectorJobConfiguration()
{
StartUrl = "http://blog.cwa.me.uk",
JobName = "Test_cwablog",
NumberOfPagesPerExecution = 10,
Credential = null,
DefaultVerifyFrequency = new Frequency()
{
Days = 1
},
Depth = 2,
LinkFilter = new LinkFilter()
{
StayOnHost = true
},
PageFilter = new PageFilter()
{
ExcludeBinaryPages = true
}
});
}
public void TruncateJournal(SqlSettings settings)
{
var config = new EngineConfiguration();
config.SqlSettings = settings;
settings.TableName = "test-" + Guid.NewGuid();
var commandStore = new SqlCommandStore(config);
commandStore.Initialize();
var formatter = new BinaryFormatter();
var writer = new SqlJournalWriter(formatter, commandStore);
Enumerable.Range(1, 50).ToList().ForEach(i =>
{
writer.Write(JournalEntry.Create((ulong)i, DateTime.Now.AddSeconds(i), new AppendNumberCommand(i)));
});
writer.Dispose();
commandStore.Truncate(40);
Assert.AreEqual(10, commandStore.CommandEntries().Count());
}
private static DataAccessResponse RunSqlScripts(SqlSettings sqlSettings, AppSettings appSettings, RetryPolicy retryPolicy)
{
//Connect to '<databaseName>'
ReliableSqlConnection sqlConnection = new ReliableSqlConnection(
Helpers.SqlConnectionStrings.GenerateConnectionString(sqlSettings, appSettings.databaseName),
retryPolicy);
//List of script folders to be run (in order)
var scriptsOrder = new List <string>();
scriptsOrder.Add("Pre");
scriptsOrder.Add("Tables");
scriptsOrder.Add("Post");
scriptsOrder.Add("Seeds");
//Loop through all scripts within each folder and run them against the database connection:
foreach (string folder in scriptsOrder)
{
var files = Directory.GetFiles(Directory.GetCurrentDirectory() + "/Sql/Scripts/" + folder);
foreach (string file in files)
{
Console.WriteLine("Running script: '" + file + "'");
var fileData = System.IO.File.ReadAllText(file);
var split = SplitSqlStatements(fileData);
foreach (var s in split)
{
//REPLACE [schemaname] with schemaName/tenantId/accountID:
var script = s.Replace("#schema#", appSettings.schemaName);
Helpers.SqlExecution.ExecuteNonQueryStatement(script, sqlConnection);
}
}
}
return(new DataAccessResponse {
isSuccess = true
});
}
protected override void Load(ContainerBuilder builder)
{
var settings = new SqlSettings();
builder.RegisterInstance(settings);
//Mappers
builder.RegisterType <QueueItemMapper>().As <IMapper <QueueItem, QueueItemModel> >()
.SingleInstance();
builder.RegisterType <DocumentStateMapper>().As <IMapper <DocumentState, DocumentStateModel> >()
.SingleInstance();
//Database
builder.RegisterType <AsgardDatabase>()
.SingleInstance();
//Services
builder.RegisterType <SqlQueueService>().As <IQueueService>().SingleInstance();
builder.RegisterType <SqlDocumentStateService>().As <IDocumentStateService>().SingleInstance();
builder.RegisterType <SqlConsumerService>().As <IConsumerService>().SingleInstance();
builder.RegisterType <SqlWorkTaskService>().As <IWorkTaskService>().SingleInstance();
builder.RegisterType <SqlStateService>().As <IStateService>().SingleInstance();
builder.RegisterType <SqlProcessInformationService>().As <IProcessInformationService>().SingleInstance();
builder.RegisterType <SqlJobService>().As <IJobService>().SingleInstance();
//Common database components
builder.RegisterInstance(new CommonDatabaseSettings()
{
CommonConnection = settings.BifrostConnection,
DestroyCommonDatabase = settings.DestroyBifrostDatabase
}).SingleInstance();
builder.RegisterType <WorkTaskDatabase>().AsSelf().SingleInstance();
builder.RegisterType <StateDatabase>().AsSelf().SingleInstance();
builder.RegisterType <ProcessInformationDatabase>().AsSelf().SingleInstance();
builder.RegisterType <JobDatabase>().AsSelf().SingleInstance();
builder.RegisterType <DefaultJobConfigurationMapper>().As <IJobConfigurationMapper>().SingleInstance();
}
public static string GenerateConnectionString(SqlSettings sqlSettings, string databaseName)
{
StringBuilder connectionString = new StringBuilder();
//SQL Linux/Docker Connection String: --------------
connectionString.Append("Server=");
connectionString.Append(sqlSettings.server);
connectionString.Append(";Database=");
connectionString.Append(databaseName);
connectionString.Append(";User="******";Password="******"Server=");
* connectionString.Append(sqlSettings.server);
*
* connectionString.Append(";Database=");
* connectionString.Append(databaseName);
*
* connectionString.Append(";User Id=");
* connectionString.Append(sqlSettings.userName);
*
* connectionString.Append(";Password="******";MultipleActiveResultSets=true");
* connectionString.Append(";Trusted_Connection=False;Encrypt=True;Persist Security Info=True;"); //<-- Adding Persist Security Info=True; resolved SQL connectivity errors when making multiple calls
* connectionString.Append("Connection Timeout=45;"); //<-- Tripled the default timeout
*/
return(connectionString.ToString());
}
public static SqlSettings GetSqlSettings()
{
//Get configuration from Docker/Compose (via .env and appsettings.json)
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
.AddEnvironmentVariables(); //<-- Allows for Docker Env Variables
IConfigurationRoot configuration = builder.Build();
var _server = configuration["Sql:Server"];
var _userName = configuration["Sql:UserName"];
var _password = configuration["Sql:Password"];
var sqlSettings = new SqlSettings {
server = _server,
userName = _userName,
password = _password
};
return(sqlSettings);
}
public IServiceProvider ConfigureServices(IServiceCollection services)
{
services.AddCustomMvc();
services.AddSwaggerDocs();
services.AddControllers().AddNewtonsoftJson();
services.AddJwt();
services.AddRedis();
services.AddCors(options =>
{
options.AddPolicy("CorsPolicy", cors =>
cors.AllowAnyOrigin()
.AllowAnyMethod()
.AllowAnyHeader());
});
var sqlOptions = new SqlSettings();
Configuration.GetSection("sql").Bind(sqlOptions);
services.AddDbContext <PeopleContext>(options =>
options.UseSqlServer(sqlOptions.ConnectionString)
);
//services.RegisterServiceForwarder<IProductsService>("products-service");
var builder = new ContainerBuilder();
builder.RegisterAssemblyTypes(Assembly.GetEntryAssembly())
.AsImplementedInterfaces();
builder.Populate(services);
builder.AddDispatchers();
builder.AddRabbitMq();
builder.AddSqlRepository <Person, PeopleContext>();
builder.AddSqlRepository <Course, PeopleContext>();
Container = builder.Build();
return(new AutofacServiceProvider(Container));
}
public void SqlCommandStoreWriteReadEntries(SqlSettings settings)
{
var config = new EngineConfiguration();
config.SqlSettings = settings;
settings.TableName = "test-" + Guid.NewGuid();
var commandStore = new SqlCommandStore(config);
commandStore.Initialize();
var formatter = new BinaryFormatter();
var writer = new SqlJournalWriter(formatter, commandStore);
writer.Write(JournalEntry.Create(1UL, DateTime.Now, new ModelCreated(typeof(TestModel))));
writer.Write(JournalEntry.Create(2UL, DateTime.Now.AddSeconds(1), new AppendNumberCommand(42)));
writer.Write(JournalEntry.Create(3UL, DateTime.Now.AddSeconds(2), new AppendNumberCommand(64)));
foreach (var entry in commandStore.GetJournalEntriesFrom(1))
{
Trace.WriteLine(entry);
}
writer.Dispose();
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// Settings
var settings = new SqlSettings();
Configuration.Bind("SQL", settings);
services.AddSingleton <SqlSettings>(settings);
var config = new Settings();
Configuration.Bind("Configuration", config);
services.AddSingleton <Settings>(config);
// Repositories
services.AddSingleton <ITaskRepository, TaskRepository>();
// Clients
services.AddSingleton <SqlClient>();
// ServiceBus
string endpointName;
#if DEBUG
endpointName = $"Tak.TaskWorker.{Environment.MachineName}";
#else
endpointName = "Tak.TaskWorker";
#endif
services.AddSingleton <CreateTaskHandler>();
services.AddSingleton <DeleteTaskHandler>();
services.AddSingleton <IQueueClient>(new QueueClient(config.ServiceBus, endpointName));
services.AddSingleton <ServiceBusClient>();
services.AddHostedService <EndpointInitializer>();
services.AddControllers();
}
public static DataAccessResponse InitializeDatabase(SqlSettings sqlSettings, AppSettings appSettings)
{
var retryPolicy = Helpers.RetryPolicies.GetRetryPolicy();
var createDatabaseResult = CreateDatabaseOnMaster(sqlSettings, appSettings.databaseName, retryPolicy);
if (createDatabaseResult.isSuccess)
{
var runSqlScriptsResult = RunSqlScripts(sqlSettings, appSettings, retryPolicy);
return(new DataAccessResponse {
isSuccess = true,
errorMessage = ""
});
}
else
{
return(new DataAccessResponse {
isSuccess = false,
errorMessage = "Failed to create database '" + appSettings.databaseName + "'"
});
}
}
public void SqlProviderIntegrationTest(SqlSettings settings)
{
var config = new EngineConfiguration();
config.JournalStorage = StorageType.Sql;
config.SqlSettings = settings;
config.SqlSettings.TableName = "test-" + Guid.NewGuid();
config.SnapshotPath = Guid.NewGuid().ToString();
config.TruncateJournalOnSnapshot = true;
var engine = Engine.For <TestModel>(config);
int initial = engine.Execute(new DelegateQuery <TestModel, int>(m => m.CommandsExecuted));
engine.Execute(new TestCommandWithoutResult());
int actual = engine.Execute(new TestCommandWithResult());
Assert.AreEqual(initial + 2, actual);
Config.Engines.CloseAll();
engine = Engine.For <TestModel>(config);
actual = engine.Execute(new TestCommandWithResult());
Assert.AreEqual(initial + 3, actual);
Config.Engines.All.First().CreateSnapshot();
var store = config.CreateCommandStore();
Assert.AreEqual(0, store.CommandEntries().Count(), "journal should be empty after snapshot");
Config.Engines.CloseAll();
engine = Engine.For <TestModel>(config);
int commandsExecuted = engine.Execute(new DelegateQuery <TestModel, int>(m => m.CommandsExecuted));
Assert.AreEqual(commandsExecuted, actual, "state should be same after close and reload");
Config.Engines.CloseAll();
//cleanup
Directory.Delete(config.SnapshotPath, true);
}
public SqlToDoDataService(IOptions <SqlSettings> options)
{
sqlOptions = options.Value;
}
public LibraryContext(DbContextOptions <LibraryContext> options, SqlSettings sqlSettings)
: base(options)
{
_sqlSettings = sqlSettings;
}
public override IEnumerable <IEditorScript> GetScripts(ITextSource text, int offset, bool readOnly)
{
var item = GetCurrentItem(text, offset);
if (item != null)
{
var generator = new ScriptMenuGenerator()
{
Conn = _conn,
ConnData = _connData,
Items = new[] { item }
};
var extras = new List <IEditorScript>();
if (!readOnly)
{
extras.Add(new EditorScriptExecute()
{
Name = "Transform: Criteria to Where Clause",
Execute = () =>
{
var doc = text as IDocument;
if (doc != null)
{
try
{
var segment = GetCurrentQuerySegment(text, offset);
var queryItem = _conn.AmlContext.FromXml(text.CreateReader(segment.Offset, segment.Length)).AssertItem();
queryItem.QueryType().Set("ignore");
queryItem.Where().Remove();
var settings = new SqlSettings(_conn)
{
RenderOption = SqlRenderOption.WhereClause
};
var elem = XElement.Load(text.CreateReader(segment.Offset, segment.Length));
var whereClause = elem.Attribute("where")?.Value ?? "";
if (whereClause != "")
{
whereClause += " and ";
}
whereClause += queryItem.ToQueryItem().ToArasSql(settings);
elem.SetAttributeValue("where", whereClause);
foreach (var child in elem.Elements().ToArray())
{
child.Remove();
}
doc.Replace(segment.Offset, segment.Length, elem.ToString());
}
catch (Exception ex)
{
// Do nothing
}
}
return(Task.FromResult(true));
}
});
}
return(generator.GetScripts().Concat(extras));
}
return(Enumerable.Empty <IEditorScript>());
}
public RefreshTokenContext(DbContextOptions <RefreshTokenContext> options, IOptions <SqlSettings> sqlSettings) : base(options)
{
_settings = sqlSettings.Value;
}
public UserRepository(SqlSettings settings)
{
this.settings = settings;
}