public async Task Test_Version_PropName()
{
var p = new
{
Id = new Guid("cf9fad7a-9775-28b9-7693-11e6ea3b1484"),
Name = "John",
BirthDate = new DateTime(1975, 03, 17),
Version = Environment.TickCount
};
using (var ts = DbContext.CreateTransactionScope())
{
await db.QueryAsync("create table #Person (Id uniqueidentifier primary key, Name nvarchar(50), BirthDate datetime2, Version timestamp);", sqlTextOnly : true);
var query = Helpers.QB.Upsert(p, tableName: "#Person",
excludedInsertProperties: n => (n == "Version"),
includedUpdateProperties: n => (n != "Version" && n != "Id")
);
var batch = QueryBatch.Create();
batch.AddQuery(query);
await db.CommitQueryBatchAsync(batch);
dynamic row = (await db.QueryAsync("select * from #Person;")).Single();
Assert.IsTrue(row.Id == p.Id && row.Name == p.Name && row.BirthDate == p.BirthDate);
await db.QueryAsync("drop table #Person;");
}
} // Test_Version_PropName()
public void A_query_with_GOs_produces_multiple_Query_objects()
{
var batch = QueryBatch.Parse(@"-- test query...
select 1
GO
select foo
from bar
go
insert into table 1 (name, desc)
values('a name like gogo', 'i want to GO now.')
GO
select 3
GO
GO
GO
GO
");
Assert.That(batch.Queries.Count, Is.EqualTo(4), "Empty queries should be ignored");
Assert.That(batch.Queries[0].Sql, Is.EqualTo(@"-- test query...
select 1"));
Assert.That(batch.Queries[1].Sql, Is.EqualTo("select foo\r\nfrom bar"));
Assert.That(batch.Queries[2].Sql, Is.EqualTo(@"insert into table 1 (name, desc)
values('a name like gogo', 'i want to GO now.')" ));
Assert.That(batch.Queries[3].Sql, Is.EqualTo("select 3"));
}
private object Enqueue(IQuery query)
{
if (_currentBatch == null)
{
_currentBatch = new QueryBatch(_context);
}
return(_currentBatch.Add(query));
}
public async Task TestExecuteAsync()
{
var queryBatch = QueryBatch.Create();
queryBatch.AddQuery("declare @foo table(id int not null); insert @foo values (@id), (@id), (@id);", new { id = 37 });
var val = await db.CommitQueryBatchAsync(queryBatch);
Assert.AreEqual(val, 3);
}
/// <summary>Initializes a new instance of the <see cref="ViewTableForm"/> class.</summary>
/// <param name="services">The services.</param>
/// <param name="settings">The settings.</param>
public ViewTableForm(IApplicationServices services, IApplicationSettings settings)
: this()
{
_services = services;
_settings = settings;
_batch = new QueryBatch();
TableName = string.Empty;
Text = Resources.ViewData;
dataGridViewResult.DefaultCellStyle.NullValue = _settings.NullText;
dataGridViewResult.DataBindingComplete += DataGridViewResultDataBindingComplete;
_services.Settings.DatabaseConnectionReset += SettingsDatabaseConnectionReset;
_services.SystemMessagePosted += ServicesSystemMessagePosted;
}
public void Batch_indicators_on_a_line_alone_cause_issues()
{
var batch = QueryBatch.Parse(@"-- issue...
insert into table 1 (name, desc)
values('foo', 'if the
go
is on a line by itself we have a problem...')");
Assert.That(batch.Queries.Count, Is.EqualTo(1), "This fails for now...");
// Basically there is no easy workaround short of writing an actual SQL
// query parser... allow 'turn off batches' will do...
// A good comprimise would token parse taking string (and escaping) into account
// but even that would add up...
}
/// <summary>Fills the list with the batch result sets.</summary>
/// <param name="batch">The query batch.</param>
public void Fill(QueryBatch batch)
{
_batch = batch;
lstBatches.Items.Clear();
if (batch == null)
{
return;
}
for (int setIndex = 0; setIndex < batch.Queries.Count; setIndex++)
{
var query = batch.Queries[setIndex];
if (query.Result != null && query.Result.Tables.Count > 0)
{
string setName = string.Format("Result Set {0} ({1} tables)", setIndex, query.Result.Tables.Count);
lstBatches.Items.Add(setName);
}
}
lstBatches.SelectedIndex = 0;
}
/// <summary>Fills the list with the batch result sets.</summary>
/// <param name="batch">The query batch.</param>
public void Fill(QueryBatch batch)
{
_batch = batch;
lstBatches.Items.Clear();
if (batch == null)
{
return;
}
for (int setIndex = 0; setIndex < batch.Queries.Count; setIndex++)
{
var query = batch.Queries[setIndex];
if (query.Result != null && query.Result.Tables.Count > 0)
{
string setName = string.Format("Result Set {0} ({1} tables)", setIndex, query.Result.Tables.Count);
lstBatches.Items.Add(setName);
}
}
lstBatches.SelectedIndex = 0;
}
/// <summary>Fills the list with the named batch results.</summary>
/// <param name="batch">The batch.</param>
public void Fill(QueryBatch batch)
{
batchQuerySelectControl1.Fill(batch);
}
void UpdateBatch(QueryBatch queryBatch)
{
Profiler.BeginSample("UpdateBatch");
var queryCount = queryBatch.queryIds.Count;
Profiler.BeginSample("Get hitcollision entities");
var hitCollEntityArray = m_colliderGroup.GetEntityArraySt();
var hitCollDataArray = m_colliderGroup.GetComponentDataArraySt <HitCollisionData>();
var hitColliders = new NativeList <Entity>(hitCollEntityArray.Length, Allocator.TempJob);
var hitColliderData = new NativeList <HitCollisionData>(hitCollEntityArray.Length, Allocator.TempJob);
var hitColliderFlags = new NativeList <uint>(hitCollEntityArray.Length, Allocator.TempJob);
for (int i = 0; i < hitCollEntityArray.Length; i++)
{
var hitCollisionOwner =
EntityManager.GetComponentData <HitCollisionOwnerData>(hitCollDataArray[i].hitCollisionOwner);
if (hitCollisionOwner.collisionEnabled == 0)
{
continue;
}
queryBatch.boundsHistory.Add(EntityManager.GetBuffer <HitCollisionData.BoundsHistory>(hitCollEntityArray[i]));
hitColliderData.Add(hitCollDataArray[i]);
hitColliders.Add(hitCollEntityArray[i]);
hitColliderFlags.Add(hitCollisionOwner.colliderFlags);
}
Profiler.EndSample();
// Environment test
Profiler.BeginSample("Environment test");
var envTestCommands = new NativeArray <RaycastCommand>(queryCount, Allocator.TempJob);
var envTestResults = new NativeArray <RaycastHit>(queryCount, Allocator.TempJob);
for (int nQuery = 0; nQuery < queryCount; nQuery++)
{
var queryId = queryBatch.queryIds[nQuery];
var queryData = m_queries[queryId];
// Start environment test
var query = queryData.query;
envTestCommands[nQuery] = new RaycastCommand(query.origin, query.direction, query.distance, m_environmentMask);
}
var envTestHandle = RaycastCommand.ScheduleBatch(envTestCommands, envTestResults, 10);
envTestHandle.Complete();
Profiler.EndSample();
Profiler.BeginSample("Handle environment test");
for (int nQuery = 0; nQuery < queryCount; nQuery++)
{
var queryId = queryBatch.queryIds[nQuery];
var queryData = m_queries[queryId];
var result = envTestResults[nQuery];
var impact = result.collider != null;
// query distance is adjusted so followup tests only are done before environment hit point
if (impact)
{
queryData.query.distance = result.distance;
// Set environment as default hit. Will be overwritten if HitCollision is hit
queryData.result.hit = 1;
queryData.result.hitPoint = result.point;
queryData.result.hitNormal = result.normal;
if (result.collider.gameObject.layer == m_hitCollisionLayer)
{
var hitCollision = result.collider.GetComponent <HitCollision>();
if (hitCollision != null)
{
queryData.result.hitCollisionOwner = hitCollision.owner;
}
}
}
}
Profiler.EndSample();
// Start broadphase tests
Profiler.BeginSample("Broadphase test");
var broadphaseHandels = new NativeArray <JobHandle>(queryCount, Allocator.Temp);
for (int nQuery = 0; nQuery < queryCount; nQuery++)
{
var queryId = queryBatch.queryIds[nQuery];
var queryData = m_queries[queryId];
var query = queryData.query;
queryData.broadPhaseBounds = new NativeArray <sphere>(hitColliderData.Length, Allocator.TempJob);
for (int i = 0; i < hitColliderData.Length; i++)
{
// Get bounds for tick
var histIndex = hitColliderData[i].GetHistoryIndex(query.hitCollisionTestTick);
var boundSphere = primlib.sphere(queryBatch.boundsHistory[i][histIndex].pos,
hitColliderData[i].boundsRadius);
queryData.broadPhaseBounds[i] = boundSphere;
}
queryData.broadTestJob = new BroadPhaseSphereCastJob(hitColliders, hitColliderData,
hitColliderFlags, queryData.broadPhaseBounds, query.ExcludeOwner, Entity.Null, query.mask,
new ray(query.origin, query.direction), query.distance, query.radius);
broadphaseHandels[nQuery] = queryData.broadTestJob.Schedule();
}
var broadphaseHandle = JobHandle.CombineDependencies(broadphaseHandels);
broadphaseHandels.Dispose();
broadphaseHandle.Complete();
Profiler.EndSample();
// Start narrow tests
Profiler.BeginSample("Narrow test");
// TODO (mogensh) find out how to combine jobs without "write to same native" issue
//var handles = new NativeArray<JobHandle>(queryCount, Allocator.TempJob);
for (int nQuery = 0; nQuery < queryCount; nQuery++)
{
var queryId = queryBatch.queryIds[nQuery];
var queryData = m_queries[queryId];
var query = queryData.query;
var broadPhaseResult = queryData.broadTestJob.result;
// Start narrow tests
queryData.narrowTestJobs.Clear();
//var narrowTestHandles = new NativeArray<JobHandle>(broadPhaseResult.Length, Allocator.Temp);
for (var i = 0; i < broadPhaseResult.Length; i++)
{
var entity = broadPhaseResult[i];
var ray = new ray(query.origin, query.direction);
queryData.narrowTestJobs.Add(new SphereCastSingleJob(EntityManager, entity, ray,
query.distance, query.radius, query.hitCollisionTestTick));
// narrowTestHandles[i] = queryData.narrowTestJobs[i].Schedule();
var handle = queryData.narrowTestJobs[i].Schedule();
handle.Complete();
}
//handles[nQuery] = JobHandle.CombineDependencies(narrowTestHandles);
// narrowTestHandles.Dispose();
}
// var handle = JobHandle.CombineDependencies(handles);
// handles.Dispose();
// handle.Complete();
Profiler.EndSample();
// Find closest
Profiler.BeginSample("Find closest");
for (int nQuery = 0; nQuery < queryBatch.queryIds.Count; nQuery++)
{
var queryId = queryBatch.queryIds[nQuery];
var queryData = m_queries[queryId];
var query = queryData.query;
var closestIndex = -1;
var closestDist = float.MaxValue;
for (int i = 0; i < queryData.narrowTestJobs.Count; i++)
{
var result = queryData.narrowTestJobs[i].result[0];
var hit = result.hit == 1;
if (hit)
{
var dist = math.distance(query.origin, result.primCenter);
if (dist < closestDist)
{
closestDist = dist;
closestIndex = i;
}
}
}
if (closestIndex != -1)
{
var result = queryData.narrowTestJobs[closestIndex].result[0];
queryData.result.hit = 1;
queryData.result.hitPoint = result.primCenter;
queryData.result.hitNormal = -queryData.query.direction; // TODO (mogensh) find correct normal
var hitCollisionData = EntityManager.GetComponentData <HitCollisionData>(
queryData.narrowTestJobs[closestIndex].hitCollObject);
queryData.result.hitCollisionOwner = hitCollisionData.hitCollisionOwner;
}
// TODO (mogensh) keep native arrays for next query
queryData.broadTestJob.Dispose();
for (int i = 0; i < queryData.narrowTestJobs.Count; i++)
{
queryData.narrowTestJobs[i].Dispose();
}
}
Profiler.EndSample();
for (int nQuery = 0; nQuery < queryBatch.queryIds.Count; nQuery++)
{
var queryId = queryBatch.queryIds[nQuery];
var queryData = m_queries[queryId];
queryData.broadPhaseBounds.Dispose();
}
envTestCommands.Dispose();
envTestResults.Dispose();
hitColliders.Dispose();
hitColliderData.Dispose();
hitColliderFlags.Dispose();
Profiler.EndSample();
}
private void ExecuteBatch()
{
_currentBatch.Execute(_mapTokenToResult);
_currentBatch = null;
}
public async Task SanityTestBatchedQueries()
{
{
var batch1 = QueryBatch.Create();
for (int i = 0; i < 50; ++i)
{
batch1.AddQuery("select [Answer] = 2;");
}
int result1 = await db.CommitQueryBatchAsync(batch1);
Console.WriteLine(result1);
Assert.IsTrue(result1 == -1);
// -1
// No rows were changed per batch; 1 batch only (default batch size is 50).
var batch2 = QueryBatch.Create();
for (int i = 0; i < 65; ++i)
{
batch2.AddQuery("select [Answer] = 2;");
}
int result2 = await db.CommitQueryBatchAsync(batch2);
Console.WriteLine(result2);
Assert.IsTrue(result2 == -1);
// -1
// No rows were changed per batch; 1 batch only (short 2nd batch of 15 queries merged into the 1st batch)
// short batch is 50/3 = 16 queries or less
}
{
var batch3 = QueryBatch.Create();
for (int i = 0; i < 70; ++i)
{
batch3.AddQuery("select [Answer] = 2;");
}
int result3 = await db.CommitQueryBatchAsync(batch3);
Console.WriteLine(result3);
Assert.IsTrue(result3 == -2);
// -2
// No rows were changed per batch; 2 batches:
// 1st batch of 50 queries and 2nd batch of 20 queries
// last batch is larger than short batch - triggers an additional db call
}
{
var batch1 = QueryBatch.Create();
var batch2 = QueryBatch.Create();
for (int i = 0; i < 40; ++i)
{
batch1.AddQuery("select [Answer] = 1;");
batch2.AddQuery("select [Answer] = 2;");
}
batch2.Append(batch1); // adding batch1 queries to batch2
int result = await db.CommitQueryBatchAsync(QueryBatch.Create(new[] { batch1, batch2 }));
Console.WriteLine(result);
Assert.IsTrue(result == -3);
// -3
// No rows were changed per batch; 3 batches:
// 1st batch of 50 queries; 2nd batch of 50 queries; 3rd batch of 20 queries
// last batch is larger than short batch - triggers an additional db call
}
{
var batch = QueryBatch.Create();
for (int i = 0; i < 43; ++i)
{
batch.AddQuery("select [Answer] = 1;");
}
int result = await db.CommitQueryBatchAsync(queryBatch : batch, batchSize : 10);
Console.WriteLine(result);
Assert.IsTrue(result == -4);
// -4
// 3 batches with 10 queries and last batch with 13 queries
}
{
var dbTemp = DbContext.Create(db.ConnectionString);
var batch = QueryBatch.Create();
for (int i = 0; i < 43; ++i)
{
batch.AddQuery("select [Answer] = 1;");
}
dbTemp.BatchSize = 10;
int result = await dbTemp.CommitQueryBatchAsync(batch);
Console.WriteLine(result);
Assert.IsTrue(result == -4);
// -4
// 3 batches with 10 queries and last batch with 13 queries
}
{
var queryBatch = QueryBatch.Create();
var queryInfo = QueryInfo.Create("declare @foo table(id int not null); insert @foo values (@id), (@id), (@id);", new { id = 37 });
queryBatch.AddQuery(queryInfo);
int result = await db.CommitQueryBatchAsync(queryBatch);
Console.WriteLine(result);
Assert.IsTrue(result == 3);
}
}
/// <summary>The get.</summary>
/// <param name="viewOrTableName">The view or table name.</param>
/// <returns></returns>
public DataTable Get(string viewOrTableName)
{
DbDataAdapter adapter = null;
DbCommand cmd = null;
DataTable dt = null;
QueryBatch batch = new QueryBatch();
Query query = new Query("SELECT * FROM " + viewOrTableName);
if (string.IsNullOrEmpty(viewOrTableName))
{
return(null);
}
if (_dataTables.ContainsKey(viewOrTableName))
{
return(_dataTables[viewOrTableName]);
}
try
{
if (_dbConnection == null || _dbConnection.State != ConnectionState.Open)
{
_dbConnection = Services.Settings.GetOpenConnection();
}
query.Result = new DataSet(viewOrTableName + " View");
batch.Clear();
batch.Add(query);
adapter = Services.Settings.ProviderFactory.CreateDataAdapter();
cmd = _dbConnection.CreateCommand();
cmd.CommandText = query.Sql;
cmd.CommandType = CommandType.Text;
adapter.SelectCommand = cmd;
adapter.Fill(query.Result);
}
// catch (Exception exp)
// {
// throw;
// }
finally
{
if (adapter != null)
{
adapter.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
if (query.Result.Tables.Count > 0)
{
dt = query.Result.Tables[0];
_dataTables[viewOrTableName] = dt;
}
return(dt);
}
public void A_single_query_produces_1_batch_with_1_Query()
{
var batch = QueryBatch.Parse("select 1");
Assert.That(batch.Queries.Count, Is.EqualTo(1));
}
public void No_single_query_produces_empty_batch()
{
var batch = QueryBatch.Parse("");
Assert.That(batch.Queries.Count, Is.EqualTo(0));
}
/// <summary>Fills the list with the named batch results.</summary>
/// <param name="batch">The batch.</param>
public void Fill(QueryBatch batch)
{
batchQuerySelectControl1.Fill(batch);
}
