public void UpdateQueryTest(Common.Enums.FhirVersion fhirVersion, string resourceName, string resourceId)
{
//Setup
var IOperationOutcomeSupportMock = IOperationOutcomeSupport_MockFactory.Get();
var IFhirResourceNameSupportMock = IFhirResourceNameSupport_MockFactory.Get(resourceName);
var IFhirResourceIdSupportMock = IFhirResourceIdSupport_MockFactory.Get(resourceId);
FhirUriFactory FhirUriFactory = GetFhirUriFactory(resourceName);
ValidateQueryService ValidateQueryService = new ValidateQueryService(IOperationOutcomeSupportMock.Object, IFhirResourceNameSupportMock.Object, IFhirResourceIdSupportMock.Object, FhirUriFactory);
var UpdateQuery = new UpdateQuery(
HttpVerb.PUT,
fhirVersion,
new Uri($"{TestData.BaseUrlServer}/{resourceName}/{resourceId}"),
new Dictionary <string, Microsoft.Extensions.Primitives.StringValues>(),
new Dictionary <string, Microsoft.Extensions.Primitives.StringValues>(),
resourceName,
resourceId,
new Common.FhirTools.FhirResource(fhirVersion));
//Act
bool Result = ValidateQueryService.IsValid(UpdateQuery, out Common.FhirTools.FhirResource? IsNotValidOperationOutCome);
//Assert
Assert.True(Result);
Assert.Null(IsNotValidOperationOutCome);
}
private static BaseQuery BuildUpdateQuery(ITrackedModel <BaseModel> trackedModel)
{
var rootSchemaName = trackedModel.Model.GetSchemaName();
var values = trackedModel.GetChanges();
if (!values.Any())
{
return(null);
}
var updateQuery = new UpdateQuery()
{
RootSchemaName = rootSchemaName,
ColumnValues = new ColumnValues()
{
Items = ConvertValuesToColumnExpressions(trackedModel.Type, values)
},
Filters = new Filters()
{
FilterType = FilterType.FilterGroup,
Items = new Dictionary <string, Filter>()
{
{ "PrimaryFilter", GeneratePrimaryFilter(trackedModel) }
},
}
};
return(updateQuery);
}
public override string UpdateQuery(UpdateQuery query)
{
StringBuilder str = new StringBuilder();
str.Append("UPDATE ");
if (query.limit > 0)
{
str.AppendFormat("TOP {0} ", query.limit);
}
str.Append(ModifiersQueryPart(query, true, true));
str.Append("\n");
str.Append(query.tables.ToString());
str.Append("\n");
str.Append(query.setClause.ToString());
str.Append("\n");
str.Append(query.ConditionalQueryPart());
return(str.ToString());
}
public void GenerateQuery()
{
var Mappings = new MappingSource(new IMapping[] {
new BaseClass1Mapping(),
new ConcreteClass1Mapping(),
new ConcreteClass2Mapping(),
new ConcreteClass3Mapping(),
new IInterface1Mapping(),
new IInterface2Mapping()
},
new MockDatabaseMapping(),
new QueryProviderManager(new[] { new SQLServerQueryProvider(Configuration, ObjectPool) }, Logger),
Canister.Builder.Bootstrapper.Resolve <ILogger>(),
ObjectPool);
var TestObject = new UpdateQuery <ConcreteClass1>(Mappings, ObjectPool);
var Result = TestObject.GenerateQueries(new ConcreteClass1 {
ID = 10, BaseClassValue1 = 1, Value1 = 2
})[0];
Assert.Equal(CommandType.Text, Result.DatabaseCommandType);
Assert.Equal(3, Result.Parameters.Length);
Assert.Equal(10, Result.Parameters[0].InternalValue);
Assert.Equal("ID", Result.Parameters[0].ID);
Assert.Equal(1, Result.Parameters[2].InternalValue);
Assert.Equal("BaseClassValue1", Result.Parameters[2].ID);
Assert.Equal(2, Result.Parameters[1].InternalValue);
Assert.Equal("Value1", Result.Parameters[1].ID);
Assert.Equal("UPDATE [dbo].[BaseClass1_]\r\nSET [dbo].[BaseClass1_].[BaseClassValue1_]=@BaseClassValue1\r\nFROM [dbo].[BaseClass1_]\r\nINNER JOIN [dbo].[IInterface1_] ON [dbo].[BaseClass1_].[IInterface1_ID_]=[dbo].[IInterface1_].[ID_]\r\nWHERE [dbo].[IInterface1_].[ID_]=@ID;\r\n\r\nUPDATE [dbo].[ConcreteClass1_]\r\nSET [dbo].[ConcreteClass1_].[Value1_]=@Value1\r\nFROM [dbo].[ConcreteClass1_]\r\nINNER JOIN [dbo].[BaseClass1_] ON [dbo].[ConcreteClass1_].[BaseClass1_ID_]=[dbo].[BaseClass1_].[ID_]\r\nINNER JOIN [dbo].[IInterface1_] ON [dbo].[BaseClass1_].[IInterface1_ID_]=[dbo].[IInterface1_].[ID_]\r\nWHERE [dbo].[IInterface1_].[ID_]=@ID;\r\n", Result.QueryString);
Assert.Equal(QueryType.Update, Result.QueryType);
}
//[ExpectedException(typeof(InvalidQueryException))]
public void InvalidUpdate()
{
UpdateQuery query = new UpdateQuery();
//RenderUpdate(query);
Assert.Throws <InvalidQueryException>(() => RenderUpdate(query));
}
public bool Update(Expression <Func <T, dynamic> > fieldUpdate, Expression <Func <T, bool> > whereClause, object source)
{
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
bool result = false;
var Query = new UpdateQuery(Entity);
cmd.CommandText = Query.GetQueryWithParameter(fieldUpdate, whereClause, source);
Query.SetParameters(cmd);
try
{
if (cmd.ExecuteNonQuery() > 0)
{
result = true;
}
}
catch (Exception ex)
{
throw new System.Exception(ex.Message);
}
return(result);
}
public void GenerateQueryWithMapPropertiesWithValue()
{
var Mappings = new MappingSource(new IMapping[] {
new AllReferencesAndIDMappingWithDatabase(),
new MapPropertiesMapping()
},
new MockDatabaseMapping(),
new QueryProviderManager(new[] { new SQLServerQueryProvider(Configuration, ObjectPool) }, Logger),
Canister.Builder.Bootstrapper.Resolve <ILogger>(),
ObjectPool);
Mappings.Mappings[typeof(MapProperties)].MapProperties.First().Setup(Mappings);
Mappings.Mappings[typeof(MapProperties)].MapProperties.First().SetColumnInfo(Mappings);
var TestObject = new UpdateQuery <MapProperties>(Mappings, ObjectPool);
var Result = TestObject.GenerateQueries(new MapProperties {
ID = 10, BoolValue = true, MappedClass = new AllReferencesAndID {
ID = 1
}
})[0];
Assert.Equal(CommandType.Text, Result.DatabaseCommandType);
Assert.Equal(2, Result.Parameters.Length);
Assert.Equal(10, Result.Parameters[0].InternalValue);
Assert.Equal("ID", Result.Parameters[0].ID);
Assert.True((bool)Result.Parameters[1].InternalValue);
Assert.Equal("BoolValue", Result.Parameters[1].ID);
Assert.Equal("UPDATE [dbo].[MapProperties_]\r\nSET [dbo].[MapProperties_].[BoolValue_]=@BoolValue\r\nFROM [dbo].[MapProperties_]\r\nWHERE [dbo].[MapProperties_].[ID_]=@ID;\r\n", Result.QueryString);
Assert.Equal(QueryType.Update, Result.QueryType);
}
public bool IsValid(out string invalidLine)
{
var currentStep = 0;
var stepFound = -1;
invalidLine = string.Empty;
foreach (var line in UpdateQuery.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
{
if (line.StartsWith("_GO_"))
{
currentStep++;
string trimmedLine = line.TrimFirstOccurence("_GO_").Replace(" ", "").Replace("-", "");
if (!trimmedLine.StartsWith("step"))
{
invalidLine = line;
return(false);
}
trimmedLine = trimmedLine.TrimFirstOccurence("step");
if (!int.TryParse(trimmedLine, out stepFound) || stepFound != currentStep)
{
invalidLine = line;
return(false);
}
}
}
return(true);
}
private async Task DoUpdate()
{
//update must wait for any inserts to complete (to avoid updates on records that haven't been inserted yet)
if (_createRecordsTask != null)
{
var result = await _createRecordsTask;
WriteDataTicks += result;
}
if (_updateRecordsTask != null)
{
var result = await _updateRecordsTask;
WriteDataTicks += result;
}
var updateQueries = new List <UpdateQuery>();
foreach (var row in _updateRows)
{
var updateQuery = new UpdateQuery(
_targetTable.Name,
_targetTable.Columns.Where(c => c.DeltaType != TableColumn.EDeltaType.SurrogateKey).Select(c => new QueryColumn(c, row[_targetTable.GetOrdinal(c.Name)])).ToList(),
_targetTable.Columns.Where(c => c.DeltaType == TableColumn.EDeltaType.SurrogateKey).Select(c => new Filter(c, Filter.ECompare.IsEqual, row[_targetTable.GetOrdinal(c.Name)])).ToList()
);
updateQueries.Add(updateQuery);
}
_updateRecordsTask = TaskTimer.Start(() => _targetConnection.ExecuteUpdate(_targetTable, updateQueries, _cancellationToken)); //this has no await to ensure processing continues.
_updateRows = new TableCache();
}
/* [Test]
* [Ignore("")]
* public void Performace1()
* {
* SelectQuery query = new SelectQuery();
* query.Columns.Add(new SelectColumn("indexed"));
* query.FromClause.BaseTable = FromTerm.Table("LargeTable");
* query.OrderByTerms.Add(new OrderByTerm("indexed", null, OrderByDirection.Ascending));
*
* const int iterations = 10, pageSize = 10, maxPages = 100;
* int count = RenderRowCount(query);
* Random random = new Random(10);
* DateTime startTime = DateTime.Now;
* for (int i = 0; i < iterations; i++)
* {
* int pageIndex = random.Next(maxPages);
* string sql = Renderer.RenderPage(pageIndex, pageSize, count, query);
* RunSql(sql);
* }
*
* TimeSpan span = DateTime.Now - startTime;
* Console.WriteLine("Paging elapsed time: {0} ms.", span.TotalMilliseconds);
*
* random = new Random(10);
* startTime = DateTime.Now;
* for (int i = 0; i < iterations; i++)
* {
* int pageIndex = random.Next(maxPages);
* query.Top = (pageIndex + 1) * pageSize;
* string sql = Renderer.RenderSelect(query);
* RunSql(sql);
* }
* span = DateTime.Now - startTime;
* Console.WriteLine("Sorting elapsed time: {0} ms.", span.TotalMilliseconds);
* }
*/
protected void RenderUpdate(UpdateQuery query)
{
string sql = Renderer.RenderUpdate(query);
Console.WriteLine(sql);
RunSql(sql);
}
public async Task <ActionResult <R4Model.Resource> > Put(string resourceName, string resourceId, [FromBody] R4Model.Resource resource)
{
if (resource == null)
{
return(BadRequest());
}
var command = new UpdateQuery(
HttpVerb.PUT,
_ControllerFhirVersion,
this.Request.GetUrl(),
QueryHelpers.ParseQuery(this.Request.QueryString.ToString()),
new Dictionary <string, StringValues>(this.Request.Headers),
resourceName,
resourceId,
new Common.FhirTools.FhirResource(_ControllerFhirVersion)
{
R4 = resource
}
);
var UpdateCommandHandler = this.IFhirApiQueryHandlerFactory.GetUpdateCommand();
FhirApiResult Result = await UpdateCommandHandler.Handle(command);
return(Result.PrepareResponse <R4Model.Resource>(this));
}
/// <summary>
/// Deletes the item, based on the item values that contain a deltaType = NaturalKey
/// </summary>
/// <returns>The delete.</returns>
/// <param name="connection">Connection.</param>
/// <param name="item">Item to delete</param>
/// <param name="cancellationToken">Cancellation token.</param>
public async Task ExecuteUpdate(Connection connection, T item, CancellationToken cancellationToken)
{
var filters = new List <Filter>();
var updateColumns = new List <QueryColumn>();
foreach (var mapping in TableMappings)
{
var column = Table.Columns[mapping.Position];
var value = DataType.TryParse(column.DataType, mapping.PropertyInfo.GetValue(item));
if (mapping.IsKey)
{
var filter = new Filter(column, Filter.ECompare.IsEqual, value);
filters.Add(filter);
}
else
{
var updateColumn = new QueryColumn(column, value);
updateColumns.Add(updateColumn);
}
}
var updateQuery = new UpdateQuery(Table.Name, updateColumns, filters);
await connection.ExecuteUpdate(Table, new List <UpdateQuery>() { updateQuery }, cancellationToken);
}
private IExecuteItemResponse ExecuteQuery(UpdateQuery updateQuery)
{
var response = new ExecuteItemResponse()
{
Success = false,
RowsAffected = 0,
ErrorMessage = string.Empty
};
if (!TryParsePrimaryValue(updateQuery, out var primaryValue))
{
response.ErrorMessage = "Cannot find primary value from filter";
return(response);
}
if (!TryGetEntity(updateQuery.RootSchemaName, primaryValue, out var entity, out var errorMessage))
{
response.ErrorMessage = errorMessage;
return(response);
}
SetColumnValues(entity, updateQuery.ColumnValues.Items);
try {
response.Success = entity.Save(false);
response.RowsAffected = 1;
} catch (Exception e) {
response.ErrorMessage = e.Message;
}
return(response);
}
/// <summary>
/// Updates an Entity in the database, provided that none of the Primary
/// keys were modified.
/// </summary>
/// <remarks>This method utilizes the <see cref="PreparedNonQuery"/> to speed things along.</remarks>
/// <param name="obj">The <see cref="TEntity"/> object to update in the dataset</param>
/// <returns>true if any records in the database were affected; false otherwise.</returns>
public bool Update(TEntity obj)
{
if (UpdateQuery == null)
{
using (var updateQuery = new UpdateQueryBuilder(EntityTable.TableName, Context))
{
// Generate the SQL
foreach (var attribute in EntityTable.Columns)
{
// Keys go in the WHERE statement, not the SET statement
if (EntityTable.PrimaryKeys.Contains(attribute.Key))
{
PropertyInfo info = attribute.Value.Property;
updateQuery.Where(attribute.Key, Comparison.Equals, new SqlLiteral($"@{attribute.Key}"));
}
else
{
updateQuery.Set(attribute.Key, new SqlLiteral($"@{attribute.Key}"));
}
}
UpdateQuery = new PreparedNonQuery(updateQuery.BuildCommand());
}
}
lock (UpdateQuery)
{
// Update parameters and execute the SQL Command
UpdateQuery.SetParameters(obj, EntityTable);
return(UpdateQuery.Execute() == 1);
}
}
protected void Execute <T>(UpdateQuery <T> query, UpdateExecutor <T> executor = null)
where T : DataRecord
{
var item = query.CreateWork(executor);
Client.EnqueueWork(item);
}
private void Dispose(bool disposing)
{
AccessSemaphore?.Dispose();
InsertQuery?.Dispose();
UpdateQuery?.Dispose();
DeleteQuery?.Dispose();
SelectHashQuery?.Dispose();
}
public void ToString_NullTableName()
{
//Arrange
var q = new UpdateQuery();
//Act
q.ToString();
}
public void Update()
{
UpdateQuery query = new UpdateQuery("products");
query.Terms.Add(new UpdateTerm("price", SqlExpression.Number(12.1)));
query.Terms.Add(new UpdateTerm("quantaty", SqlExpression.Field("quantaty")));
query.WhereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId"), SqlExpression.Number(1), CompareOperator.Equal));
RenderUpdate(query);
}
public string RealTimeUpdateQuery()
{
UpdateQuery query = new UpdateQuery(RealTimeData);
query.AddField(new Field(nameof(ReceivedOn), packet.ReceivedOn));
query.AddFieldRange(packet.Fields);
query.AddFilters(packet.FilterFields);
return(query.ToQuery());
}
public void logicalDelete(ActiveRecord item)
{
UpdateQuery query = new UpdateQuery(item.GetType());
query.addKeyValue("estado", "0");
query.addWhere("id", item.id.ToString());
Console.WriteLine(query.build());
query.exec();
}
public virtual async Task UpdateAudit(TransformWriterResult writerResult, CancellationToken cancellationToken)
{
var picoTable = new PocoTable <TransformWriterResult>();
writerResult.IsCurrent = true;
writerResult.IsPrevious = false;
writerResult.IsPreviousSuccess = false;
//when the runstatuss is finished or finished with errors, set the previous success record to false.
if (writerResult.RunStatus == TransformWriterResult.ERunStatus.Finished || writerResult.RunStatus == TransformWriterResult.ERunStatus.FinishedErrors)
{
var updateLatestColumn = new List <QueryColumn>()
{
new QueryColumn(new TableColumn("IsCurrent", ETypeCode.Boolean), false),
new QueryColumn(new TableColumn("IsPreviousSuccess", ETypeCode.Boolean), false)
};
var updateLatestFilters = new List <Filter>()
{
new Filter(new TableColumn("HubKey", ETypeCode.Int64), Filter.ECompare.IsEqual, writerResult.HubKey),
new Filter(new TableColumn("ReferenceKey", ETypeCode.Int64), Filter.ECompare.IsEqual, writerResult.ReferenceKey),
new Filter(new TableColumn("IsPreviousSuccess", ETypeCode.Boolean), Filter.ECompare.IsEqual, true),
};
var updateIsLatest = new UpdateQuery(picoTable.Table.Name, updateLatestColumn, updateLatestFilters);
await ExecuteUpdate(picoTable.Table, new List <UpdateQuery>() { updateIsLatest }, CancellationToken.None);
writerResult.IsPreviousSuccess = true;
}
//when finished, mark the previous result to false.
if (writerResult.IsFinished)
{
var updateLatestColumn = new List <QueryColumn>()
{
new QueryColumn(new TableColumn("IsCurrent", ETypeCode.Boolean), false),
new QueryColumn(new TableColumn("IsPrevious", ETypeCode.Boolean), false)
};
var updateLatestFilters = new List <Filter>()
{
new Filter(new TableColumn("HubKey", ETypeCode.Int64), Filter.ECompare.IsEqual, writerResult.HubKey),
new Filter(new TableColumn("ReferenceKey", ETypeCode.Int64), Filter.ECompare.IsEqual, writerResult.ReferenceKey),
new Filter(new TableColumn("IsPrevious", ETypeCode.Boolean), Filter.ECompare.IsEqual, true),
};
var updateIsLatest = new UpdateQuery(picoTable.Table.Name, updateLatestColumn, updateLatestFilters);
await ExecuteUpdate(picoTable.Table, new List <UpdateQuery>() { updateIsLatest }, CancellationToken.None);
writerResult.IsCurrent = false;
writerResult.IsPrevious = true;
}
await picoTable.ExecuteUpdate(this, writerResult, cancellationToken);
}
public void Edit(DVD dvd)
{
UpdateQuery.UpdateDVD(dvd.DvdID, dvd.Title, dvd.ReleaseYear, dvd.Director, dvd.Rating, dvd.Notes);
var found = _Dvds.FirstOrDefault(d => d.DvdID == dvd.DvdID);
if (found != null)
{
found = dvd;
}
}
public void Update(StudentStatusDo studentStatus)
{
if (studentStatus == null)
{
throw new Exception("An update with null value can't be made!");
}
var query = UpdateQuery.Create("studentStatuses", studentStatus);
database.Execute(query);
}
public void ToString_EmptyTableName()
{
//Arrange
var q = new UpdateQuery()
{
TableName = ""
};
//Act
q.ToString();
}
public void ToString_NullValues()
{
//Arrange
var q = new UpdateQuery()
{
TableName = "t"
};
//Act
q.ToString();
}
public void UpdateProduct(Shop shop, AProduct product, UpdateQuery query)
{
List <AProduct> products = new List <AProduct>();
products.Add(product);
List <UpdateQuery> queries = new List <UpdateQuery>();
queries.Add(query);
UpdateProduct(shop, products, queries);
}
public static async Task BanUser(User user)
{
Monitor.Enter(coherenceLock);
UpdateQuery <User> query = new UpdateQuery <User>();
query.Where("user_id", user.ID);
query.Set("role", UserRole.Banned);
await Program.MySql().ExecuteNonQuery(query);
Monitor.Exit(coherenceLock);
}
/// <summary>
/// Renders the whole UPDATE statement using ANSI SQL standard
/// </summary>
/// <param name="query"></param>
/// <returns>The rendered SQL string</returns>
public virtual string UpdateStatement(UpdateQuery query)
{
query.Validate();
StringBuilder builder = new StringBuilder();
Update(builder, query.TableName);
UpdateTerms(builder, query.Terms);
Where(builder, query.WhereClause);
WhereClause(builder, query.WhereClause);
return(builder.ToString());
}
public int update(ActiveRecord item)
{
UpdateQuery query = new UpdateQuery(item.GetType());
foreach (KeyValuePair <string, string> properties in getProperties(item))
{
query.addKeyValue(properties.Key, properties.Value);
}
query.addWhere("id", item.id.ToString());
Console.WriteLine(query.build());
return(query.exec());
}
public static async Task AddCredits(User user, int amount)
{
Monitor.Enter(coherenceLock);
UpdateQuery <User> query = new UpdateQuery <User>();
query.Where("user_id", user.ID);
query.Set("credits", user.Credits + amount);
await Program.MySql().ExecuteNonQuery(query);
Monitor.Exit(coherenceLock);
}
public void NonAtomicFalse()
{
var sequenceQuery = Substitute.For<ISequenceQuery<string>>();
var query = new UpdateQuery<string>(
sequenceQuery,
s => "woot",
false);
var term = query.GenerateTerm(queryConverter);
var nonAtomicArgs = term.optargs.Where(kv => kv.key == "non_atomic");
Assert.That(nonAtomicArgs.Count(), Is.EqualTo(0));
}
public void NonAtomicTrue()
{
var sequenceQuery = Substitute.For<ISequenceQuery<string>>();
var query = new UpdateQuery<string>(
sequenceQuery,
s => "woot",
true);
var term = query.GenerateTerm(queryConverter);
var nonAtomicArgs = term.optargs.Where(kv => kv.key == "non_atomic");
Assert.That(nonAtomicArgs.Count(), Is.EqualTo(1));
var nonAtomicArg = nonAtomicArgs.Single();
Assert.That(nonAtomicArg.val, Is.Not.Null);
Assert.That(nonAtomicArg.val.type, Is.EqualTo(Term.TermType.DATUM));
Assert.That(nonAtomicArg.val.datum, Is.Not.Null);
Assert.That(nonAtomicArg.val.datum.type, Is.EqualTo(Datum.DatumType.R_BOOL));
Assert.That(nonAtomicArg.val.datum.r_bool, Is.True);
}
