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); } }