public async Task UpdateAsync(params IIdentityEntity[] documents)
{
await _dbConnection.OpenAsync();
try
{
using (var tx = _dbConnection.BeginTransaction(_factory.IsolationLevel))
{
foreach (var document in documents)
{
var content = JsonConvert.SerializeObject(document.Entity, _jsonSettings);
var dialect = SqlDialectFactory.For(_dbConnection);
var updateCmd = $"update [{_factory.TablePrefix}Content] set Content = @Content where Id = @Id;";
await _dbConnection.ExecuteScalarAsync <int>(updateCmd, new { Id = document.Id, Content = content }, tx);
}
tx.Commit();
}
}
finally
{
_dbConnection.Close();
}
}
public async Task <IEnumerable <IndexingTask> > GetIndexingTasksAsync(int afterTaskId, int count)
{
using (var connection = _dbConnectionAccessor.CreateConnection())
{
await connection.OpenAsync();
try
{
var dialect = SqlDialectFactory.For(connection);
var sqlBuilder = dialect.CreateBuilder(_tablePrefix);
sqlBuilder.Select();
sqlBuilder.Table(nameof(IndexingTask));
sqlBuilder.Selector("*");
if (count > 0)
{
sqlBuilder.Take(count.ToString());
}
sqlBuilder.WhereAlso($"{dialect.QuoteForColumnName("Id")} > @Id");
return(await connection.QueryAsync <IndexingTask>(sqlBuilder.ToSqlString(), new { Id = afterTaskId }));
}
catch (Exception e)
{
Logger.LogError(e, "An error occurred while reading indexing tasks");
throw;
}
}
}
public override async Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
{
var dialect = SqlDialectFactory.For(connection);
var type = Index.GetType();
var sql = Updates(type);
await connection.ExecuteAsync(sql, Index, transaction);
// Update the documents list
var reduceIndex = Index as ReduceIndex;
if (reduceIndex != null)
{
var documentTable = CollectionHelper.Current.GetPrefixedName(Store.DocumentTable);
var bridgeTableName = type.Name + "_" + documentTable;
var columnList = $"[{type.Name}Id], [DocumentId]";
var parameterList = $"@Id, @DocumentId";
var bridgeSqlAdd = $"insert into [{_tablePrefix}{bridgeTableName}] ({columnList}) values ({parameterList});";
var bridgeSqlRemove = $"delete from [{_tablePrefix}{bridgeTableName}] where DocumentId = @DocumentId and {type.Name}Id = @Id;";
await connection.ExecuteAsync(bridgeSqlAdd, _addedDocumentIds.Select(x => new { DocumentId = x, Id = Index.Id }), transaction);
await connection.ExecuteAsync(bridgeSqlRemove, _deletedDocumentIds.Select(x => new { DocumentId = x, Id = Index.Id }), transaction);
}
}
public LinearBlockIdGenerator(IConnectionFactory connectionFactory, int range, string tablePrefix)
{
_connectionFactory = connectionFactory;
_dialect = SqlDialectFactory.For(connectionFactory.CreateConnection());
_range = range;
_tablePrefix = tablePrefix;
}
public async Task <IEnumerable <IndexingTask> > GetIndexingTasksAsync(int afterTaskId, int count)
{
await FlushAsync();
var connection = _store.Configuration.ConnectionFactory.CreateConnection();
connection.Open();
var transaction = connection.BeginTransaction(_store.Configuration.IsolationLevel);
try
{
var dialect = SqlDialectFactory.For(connection);
var sqlBuilder = dialect.CreateBuilder(_tablePrefix);
sqlBuilder.Select();
sqlBuilder.Table(nameof(IndexingTask));
sqlBuilder.Selector("*");
sqlBuilder.Take(count);
sqlBuilder.WhereAlso($"{dialect.QuoteForColumnName("Id")} > @Id");
return(await connection.QueryAsync <IndexingTask>(sqlBuilder.ToSqlString(dialect), new { Id = afterTaskId }, transaction));
}
catch (Exception e)
{
Logger.LogError("An error occured while reading indexing tasks: " + e.Message);
throw;
}
finally
{
transaction.Commit();
transaction.Dispose();
connection.Dispose();
}
}
public DbContext(ConnectionStringSettings settings)
{
//this.EntityInfo = new EntityInfo();
this.BatchSize = 100;
DataBase = DbFactory.CreateDataBase(settings);
SqlDialect = SqlDialectFactory.CreateSqlDialect();
}
public async Task <IEnumerable <IndexingTask> > GetIndexingTasksAsync(int afterTaskId, int count)
{
await FlushAsync();
var transaction = await _session.DemandAsync();
try
{
var dialect = SqlDialectFactory.For(transaction.Connection);
var sqlBuilder = dialect.CreateBuilder(_tablePrefix);
sqlBuilder.Select();
sqlBuilder.Table(nameof(IndexingTask));
sqlBuilder.Selector("*");
if (count > 0)
{
sqlBuilder.Take(count.ToString());
}
sqlBuilder.WhereAlso($"{dialect.QuoteForColumnName("Id")} > @Id");
return(await transaction.Connection.QueryAsync <IndexingTask>(sqlBuilder.ToSqlString(), new { Id = afterTaskId }, transaction));
}
catch (Exception e)
{
_session.Cancel();
Logger.LogError(e, "An error occured while reading indexing tasks");
throw;
}
}
public override async Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
{
var dialect = SqlDialectFactory.For(connection);
var type = Index.GetType();
var documentTable = CollectionHelper.Current.GetPrefixedName(Store.DocumentTable);
if (Index is MapIndex)
{
var sql = Inserts(type) + $" {dialect.IdentitySelectString} id";
Index.Id = await connection.ExecuteScalarAsync <int>(sql, Index, transaction);
await connection.ExecuteAsync($"update [{_tablePrefix}{type.Name}] set DocumentId = @mapid where Id = @Id", new { mapid = Index.GetAddedDocuments().Single().Id, Id = Index.Id }, transaction);
}
else
{
var reduceIndex = Index as ReduceIndex;
var sql = Inserts(type) + $"; {dialect.IdentitySelectString} id";
Index.Id = await connection.ExecuteScalarAsync <int>(sql, Index, transaction);
var bridgeTableName = type.Name + "_" + documentTable;
var columnList = $"[{type.Name}Id], [DocumentId]";
var parameterList = $"@Id, @DocumentId";
var bridgeSql = $"insert into [{_tablePrefix}{bridgeTableName}] ({columnList}) values ({parameterList});";
await connection.ExecuteAsync(bridgeSql, _addedDocumentIds.Select(x => new { DocumentId = x, Id = Index.Id }), transaction);
}
}
public SchemaBuilder(IDbConnection connection, IDbTransaction transaction, string tablePrefix)
{
_builder = CommandInterpreterFactory.For(connection);
_dialect = SqlDialectFactory.For(connection);
_tablePrefix = tablePrefix;
Connection = connection;
Transaction = transaction;
}
public override Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
{
var dialect = SqlDialectFactory.For(connection);
var documentTable = CollectionHelper.Current.GetPrefixedName(Store.DocumentTable);
var deleteCmd = $"delete from [{_tablePrefix}{documentTable}] where [Id] = @Id;";
return(connection.ExecuteAsync(deleteCmd, Document, transaction));
}
public SqlVisitor(DataBaseType dbType)
{
this.parameters = new List <DbParameter>();
this.DbType = dbType;
CallIndex = 0;
ParameterIndex = 0;
SqlDialect = SqlDialectFactory.CreateSqlDialect();
}
public SchemaBuilder(ISession session)
{
Transaction = session.Demand();
Connection = Transaction.Connection;
_builder = CommandInterpreterFactory.For(Connection);
_dialect = SqlDialectFactory.For(Connection);
_tablePrefix = session.Store.Configuration.TablePrefix;
}
public DefaultQuery(IDbConnection connection, IDbTransaction transaction, Session session, string tablePrefix)
{
_connection = connection;
_transaction = transaction;
_session = session;
_dialect = SqlDialectFactory.For(connection);
_queryState = new QueryState(_dialect.CreateBuilder(tablePrefix));
}
public override Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
{
var dialect = SqlDialectFactory.For(connection);
var documentTable = CollectionHelper.Current.GetPrefixedName(Store.DocumentTable);
var insertCmd = $"insert into [{_tablePrefix}{documentTable}] ([Id], [Type]) values (@Id, @Type);";
return(connection.ExecuteScalarAsync <int>(insertCmd, Document, transaction));
}
public async Task <object> ExecuteQueryAsync(Query query, IDictionary <string, object> parameters)
{
var sqlQuery = query as SqlQuery;
var templateContext = new TemplateContext();
foreach (var parameter in parameters)
{
templateContext.SetValue(parameter.Key, parameter.Value);
}
var tokenizedQuery = await _liquidTemplateManager.RenderAsync(sqlQuery.Template, templateContext);
var connection = _store.Configuration.ConnectionFactory.CreateConnection();
var dialect = SqlDialectFactory.For(connection);
var results = new List <JObject>();
if (!SqlParser.TryParse(tokenizedQuery, dialect, _store.Configuration.TablePrefix, out var rawQuery, out var rawParameters, out var messages))
{
return(results);
}
if (sqlQuery.ReturnDocuments)
{
IEnumerable <int> documentIds;
using (connection)
{
connection.Open();
documentIds = await connection.QueryAsync <int>(rawQuery, rawParameters);
}
var documents = await _session.GetAsync <object>(documentIds.ToArray());
foreach (var document in documents)
{
results.Add(JObject.FromObject(document));
}
}
else
{
IEnumerable <dynamic> queryResults;
using (connection)
{
connection.Open();
queryResults = await connection.QueryAsync(rawQuery, rawParameters);
}
foreach (var document in queryResults)
{
results.Add(JObject.FromObject(document));
}
}
return(results.ToArray());
}
public DefaultQuery(IDbConnection connection, IDbTransaction transaction, Session session, string tablePrefix, QueryState queryState, object compiledQuery)
{
_queryState = queryState;
_compiledQuery = compiledQuery;
_connection = connection;
_transaction = transaction;
_session = session;
_dialect = SqlDialectFactory.For(connection);
}
public DefaultQuery(DbConnection connection, DbTransaction transaction, Session session, string tablePrefix)
{
_connection = connection;
_transaction = transaction;
_session = session;
_dialect = SqlDialectFactory.For(connection);
_sqlBuilder = new SqlBuilder();
_sqlBuilder.TablePrefix = tablePrefix;
}
public SchemaBuilder(IConfiguration configuration, DbTransaction transaction, bool throwOnError = true)
{
Transaction = transaction;
Connection = Transaction.Connection;
_builder = CommandInterpreterFactory.For(Connection);
Dialect = SqlDialectFactory.For(configuration.ConnectionFactory.DbConnectionType);
TablePrefix = configuration.TablePrefix;
ThrowOnError = throwOnError;
}
public DefaultQuery(IDbConnection connection, IDbTransaction transaction, Session session, string tablePrefix)
{
_documentTable = CollectionHelper.Current.GetPrefixedName(Store.DocumentTable);
_connection = connection;
_transaction = transaction;
_session = session;
_dialect = SqlDialectFactory.For(connection);
_sqlBuilder = _dialect.CreateBuilder(tablePrefix);
}
private async Task FlushAsync()
{
List <IndexingTask> localQueue;
lock (_tasksQueue)
{
localQueue = new List <IndexingTask>(_tasksQueue);
}
if (!localQueue.Any())
{
return;
}
var transaction = await _session.DemandAsync();
var dialect = SqlDialectFactory.For(transaction.Connection);
try
{
var contentItemIds = new HashSet <string>();
// Remove duplicate tasks, only keep the last one
for (var i = localQueue.Count; i > 0; i--)
{
var task = localQueue[i - 1];
if (contentItemIds.Contains(task.ContentItemId))
{
localQueue.RemoveAt(i - 1);
}
else
{
contentItemIds.Add(task.ContentItemId);
}
}
// At this point, content items ids should be unique in _taskQueue
var ids = localQueue.Select(x => x.ContentItemId).ToArray();
var table = $"{_tablePrefix}{nameof(IndexingTask)}";
var deleteCmd = $"delete from {dialect.QuoteForTableName(table)} where {dialect.QuoteForColumnName("ContentItemId")} {dialect.InOperator("@Ids")};";
await transaction.Connection.ExecuteAsync(deleteCmd, new { Ids = ids }, transaction);
var insertCmd = $"insert into {dialect.QuoteForTableName(table)} ({dialect.QuoteForColumnName("CreatedUtc")}, {dialect.QuoteForColumnName("ContentItemId")}, {dialect.QuoteForColumnName("Type")}) values (@CreatedUtc, @ContentItemId, @Type);";
await transaction.Connection.ExecuteAsync(insertCmd, _tasksQueue, transaction);
}
catch (Exception e)
{
_session.Cancel();
Logger.LogError("An error occured while updating indexing tasks", e);
throw;
}
_tasksQueue.Clear();
}
public async Task <IActionResult> Query(AdminQueryViewModel model)
{
if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageSqlQueries))
{
return(Forbid());
}
if (String.IsNullOrWhiteSpace(model.DecodedQuery))
{
return(View(model));
}
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
var stopwatch = new Stopwatch();
stopwatch.Start();
var connection = _store.Configuration.ConnectionFactory.CreateConnection();
var dialect = SqlDialectFactory.For(connection);
var parameters = JsonConvert.DeserializeObject <Dictionary <string, object> >(model.Parameters);
var templateContext = _liquidTemplateManager.Context;
foreach (var parameter in parameters)
{
templateContext.SetValue(parameter.Key, parameter.Value);
}
var tokenizedQuery = await _liquidTemplateManager.RenderAsync(model.DecodedQuery, NullEncoder.Default);
model.FactoryName = _store.Configuration.ConnectionFactory.GetType().FullName;
if (SqlParser.TryParse(tokenizedQuery, dialect, _store.Configuration.TablePrefix, parameters, out var rawQuery, out var messages))
{
model.RawSql = rawQuery;
model.Parameters = JsonConvert.SerializeObject(parameters, Formatting.Indented);
try
{
using (connection)
{
await connection.OpenAsync();
model.Documents = await connection.QueryAsync(rawQuery, parameters);
}
}
catch (Exception e)
{
ModelState.AddModelError("", S["An error occurred while executing the SQL query: {0}", e.Message]);
}
}
public Task InitializeAsync(IStore store, ISchemaBuilder builder)
{
_dialect = SqlDialectFactory.For(store.Configuration.ConnectionFactory.DbConnectionType);
#if NET451
return(Task.FromResult(0));
#else
return(Task.CompletedTask);
#endif
}
public Session(IDocumentStorage storage, Store store)
{
_storage = storage;
_store = store;
_isolationLevel = store.Configuration.IsolationLevel;
_maps = new Dictionary <IndexDescriptor, IList <MapState> >();
_connection = _store.Configuration.ConnectionFactory.CreateConnection();
_dialect = SqlDialectFactory.For(_connection);
}
public async Task <IEnumerable <T> > GetAsync <T>(params int[] ids)
{
if (ids == null)
{
throw new ArgumentNullException("id");
}
var result = new T[ids.Length];
// Create an index to lookup the position of a specific document id
var orderedLookup = new Dictionary <int, int>();
for (var i = 0; i < ids.Length; i++)
{
orderedLookup[ids[i]] = i;
}
await _dbConnection.OpenAsync();
try
{
using (var tx = _dbConnection.BeginTransaction(_factory.IsolationLevel))
{
foreach (var idPages in ids.PagesOf(128))
{
var dialect = SqlDialectFactory.For(_dbConnection);
var selectCmd = $"select Id, Content from [{_factory.TablePrefix}Content] where Id IN @Id;";
var entities = await _dbConnection.QueryAsync <IdString>(selectCmd, new { Id = idPages.ToArray() }, tx);
foreach (var entity in entities)
{
var index = orderedLookup[entity.Id];
if (typeof(T) == typeof(object))
{
result[index] = JsonConvert.DeserializeObject <dynamic>(entity.Content, _jsonSettings);
}
else
{
result[index] = JsonConvert.DeserializeObject <T>(entity.Content, _jsonSettings);
}
}
}
tx.Commit();
}
}
finally
{
_dbConnection.Close();
}
return(result);
}
public async Task DeleteAsync(params IIdentityEntity[] documents)
{
var contentTable = CollectionHelper.Current.GetPrefixedName("Content");
var tx = _session.Demand();
foreach (var documentsPage in documents.PagesOf(128))
{
var dialect = SqlDialectFactory.For(tx.Connection);
var deleteCmd = $"delete from [{_factory.TablePrefix}{contentTable}] where Id IN @Id;";
await tx.Connection.ExecuteScalarAsync <int>(deleteCmd, new { Id = documentsPage.Select(x => x.Id).ToArray() }, tx);
}
}
public async Task DeleteAsync(params IIdentityEntity[] documents)
{
var contentTable = CollectionHelper.Current.GetPrefixedName("Content");
var tx = _session.Demand();
foreach (var documentsPage in documents.PagesOf(128))
{
var dialect = SqlDialectFactory.For(tx.Connection);
var deleteCmd = "delete from " + dialect.QuoteForTableName(_factory.TablePrefix + contentTable) + " where " + dialect.QuoteForColumnName("Id") + dialect.InOperator("@Id") + ";";
await tx.Connection.ExecuteScalarAsync <int>(deleteCmd, new { Id = documentsPage.Select(x => x.Id).ToArray() }, tx);
}
}
public static ICommandInterpreter For(IDbConnection connection)
{
string connectionName = connection.GetType().Name.ToLower();
if (!CommandInterpreters.ContainsKey(connectionName))
{
throw new ArgumentException("Unknown connection name: " + connectionName);
}
var dialect = SqlDialectFactory.For(connection);
return(CommandInterpreters[connectionName](dialect));
}
public async Task <IEnumerable <object> > GetAsync(params IIdentityEntity[] documents)
{
var result = new object[documents.Length];
// Create an index to lookup the position of a specific document id
var orderedLookup = new Dictionary <int, int>();
for (var i = 0; i < documents.Length; i++)
{
orderedLookup[documents[i].Id] = i;
}
await _dbConnection.OpenAsync();
try
{
using (var tx = _dbConnection.BeginTransaction(_factory.IsolationLevel))
{
var typeGroups = documents.GroupBy(x => x.EntityType);
// In case identities are from different types, group queries by type
foreach (var typeGroup in typeGroups)
{
// Limit the IN clause to 128 items at a time
foreach (var documentsPage in typeGroup.PagesOf(128))
{
var ids = documentsPage.Select(x => x.Id).ToArray();
var dialect = SqlDialectFactory.For(_dbConnection);
var op = ids.Length == 1 ? "=" : "IN";
var selectCmd = $"select Id, Content from [{_factory.TablePrefix}Content] where Id {op} @Id;";
var entities = await _dbConnection.QueryAsync <IdString>(selectCmd, new { Id = ids }, tx);
foreach (var entity in entities)
{
var index = orderedLookup[entity.Id];
result[index] = JsonConvert.DeserializeObject(entity.Content, typeGroup.Key, _jsonSettings);
}
}
}
tx.Commit();
}
}
finally
{
_dbConnection.Close();
}
return(result);
}
public async Task UpdateAsync(params IIdentityEntity[] documents)
{
var contentTable = CollectionHelper.Current.GetPrefixedName("Content");
var tx = _session.Demand();
foreach (var document in documents)
{
var content = JsonConvert.SerializeObject(document.Entity, _jsonSettings);
var dialect = SqlDialectFactory.For(tx.Connection);
var updateCmd = "update " + dialect.QuoteForTableName(_factory.TablePrefix + contentTable) + " set " + dialect.QuoteForColumnName("Content") + " = @Content where " + dialect.QuoteForColumnName("Id") + " = @Id;";
await tx.Connection.ExecuteScalarAsync <int>(updateCmd, new { Id = document.Id, Content = content }, tx);
}
}
public async Task CreateAsync(params IIdentityEntity[] documents)
{
var contentTable = CollectionHelper.Current.GetPrefixedName("Content");
var tx = _session.Demand();
foreach (var document in documents)
{
var content = JsonConvert.SerializeObject(document.Entity, _jsonSettings);
var dialect = SqlDialectFactory.For(tx.Connection);
var insertCmd = $"insert into [{_factory.TablePrefix}{contentTable}] ([Id], [Content]) values (@Id, @Content);";
await tx.Connection.ExecuteScalarAsync <int>(insertCmd, new { Id = document.Id, Content = content }, tx);
}
}
