public string insert(Web.Core.DB.Transaction _transaction, ITableSpec table, Dictionary <string, string> data)
{
using (var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock (transaction) {
using (DbCommand command = transaction.sqlconnection.CreateCommand()) {
List <string> updates = new List <string>();
List <string> updatesPlaceholders = new List <string>();
ParamsHolder paramsholder = new ParamsHolder();
foreach (KeyValuePair <string, string> kvp in data)
{
updates.Add(this.traits.escapeIdentifier(kvp.Key));
updatesPlaceholders.Add(this.traits.markParam(paramsholder.Add(kvp.Value)));
}
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "INSERT INTO " + table.compile(this.traits) + " (" + String.Join(", ", updates.ToArray()) + ") VALUES (" + String.Join(", ", updatesPlaceholders.ToArray()) + ")";
foreach (KeyValuePair <string, string> kvp in paramsholder.data)
{
command.AddParameter(kvp.Key, kvp.Value);
}
command.ExecuteNonQuery();
if (data.ContainsKey(table.idName))
{
return(data[table.idName]);
}
return(this.traits.LastInsertId(command, table).ToString());
}
}
}
}
/// <summary>
/// Tables that are in AV and where records are normally owned by them.
/// As far as we know they are never deleted and (although this might not have been fully analysed), deletion might fail because we might be using them.
/// Generally used for reference data.
/// </summary>
public bool OwnedByAVUnlessNegativeNoDeletes(ITableSpec table, dynamic oldRecordFromTarget, dynamic newRecordFromSource, Operation operation)
{
var primaryKeys = Keys.GetPrimaryKeys((IDictionary <string, object>)(oldRecordFromTarget ?? newRecordFromSource), table);
if (primaryKeys.First() < 0)
{
if (operation == Operation.Delete)
{
// Migrate will identify these for deletion as they are not on the source system
return(false);
}
else
{
// If Migrate is trying to insert or update them then are assertion that only we create negative ids is wrong
_log.Error($"AVMS somehow has record in {table.Name} for {primaryKeys}");
return(false);
}
}
else
{
if (operation == Operation.Delete)
{
// If Migrate is trying to delete its own records then the assumption it never does is wrong
_log.Error($"AVMS unexpectedly trying to delete {table.Name} for {primaryKeys}");
return(false);
}
else
{
return(true);
}
}
}
public void AnonymiseProviderSiteHistory(ITableSpec tableSpec, dynamic oldRecord, dynamic newRecord)
{
if (_migrateConfig.AnonymiseData)
{
newRecord.Comment = "[Training provider history]";
}
}
public void update(Web.Core.DB.Transaction _transaction, ITableSpec table, string id, Dictionary <string, string> data)
{
using (var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock (transaction) {
using (DbCommand command = transaction.sqlconnection.CreateCommand()) {
List <string> updates = new List <string>();
ParamsHolder paramsholder = new ParamsHolder();
foreach (KeyValuePair <string, string> kvp in data)
{
updates.Add(this.traits.escapeIdentifier(kvp.Key) + " = " + this.traits.markParam(paramsholder.Add(kvp.Value)));
}
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "UPDATE " + table.compile(traits) + " set " + String.Join(", ", updates.ToArray()) + " where " + table.getIdSpec().compile(this.traits) + " = " + this.traits.markParam("id");
command.AddParameter("id", id);
foreach (KeyValuePair <string, string> kvp in paramsholder.data)
{
command.AddParameter(kvp.Key, kvp.Value);
}
// throw new CriticalException(command.CommandText + "; parameters: " + string.Join(", ", (from DbParameter parameter in command.Parameters select parameter.ParameterName + "='" + parameter.Value.ToString() + "'").ToArray()));
command.ExecuteNonQuery();
}
}
}
}
public void DoPossibleDelete(IMutateTarget mutateTarget, ITableSpec table, dynamic targetRecord)
{
if (table.CanMutate(table, targetRecord, null, Operation.Delete))
{
mutateTarget.Delete(targetRecord);
}
}
public List <string> LoadIdsByConditions(ITableSpec table, Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, ColumnSpec idSpec, bool allowHugeLists)
{
using (DbConnection connection = this.createConnection()) {
using (DbCommand command = connection.CreateCommand()) {
return(this._LoadIdsByConditions(command, table, conditions, diapasone, joins, sorts, idSpec, allowHugeLists));
}
}
}
public List <Dictionary <string, string> > LoadByIds(ITableSpec table, List <string> ids)
{
using (DbConnection connection = this.createConnection()) {
using (DbCommand command = connection.CreateCommand()) {
return(this._LoadByIds(command, table, ids, false));
}
}
}
public void DoPossibleInsert(IMutateTarget mutateTarget, ITableSpec table, dynamic sourceRecord)
{
if (table.CanMutate(table, null, sourceRecord, Operation.Insert))
{
table.Transform(table, null, sourceRecord);
mutateTarget.Insert(sourceRecord);
}
}
private List <Dictionary <string, string> > _LoadByIds(DbCommand command, ITableSpec table, List <string> ids, bool forUpdate)
{
using (var logger = this.CreateCommandExecutionLogger()) {
command.CommandType = System.Data.CommandType.Text;
ParamsHolder paramsHolder = new ParamsHolder();
List <string> placeholder = new List <string>();
foreach (string id in ids)
{
placeholder.Add(this.traits.markParam(paramsHolder.Add(id)));
}
command.CommandText = logger.commandText = "SELECT * FROM " + table.compile(this.traits) + " WHERE " + table.getIdSpec().compile(this.traits) + " IN (" + string.Join(", ", placeholder.ToArray()) + ")" + (forUpdate ? " FOR UPDATE" : "");
//command.Prepare();
foreach (KeyValuePair <string, string> kvp in paramsHolder.data)
{
command.AddParameter(kvp.Key, kvp.Value);
}
Dictionary <string, Dictionary <string, string> > rawResult = new Dictionary <string, Dictionary <string, string> >();
using (DbDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Dictionary <string, string> row = new Dictionary <string, string>();
for (int i = 0; i < reader.FieldCount; i++)
{
//throw new CriticalException("Name: " + reader.GetName(i));
object value = reader.GetValue(i);
string sValue;
if (value is DateTime)
{
sValue = ((DateTime)value).Ticks.ToString();
}
else if (value is TimeSpan)
{
sValue = ((TimeSpan)value).Ticks.ToString();
}
else
{
sValue = value.ToString();
}
row.Add(reader.GetName(i), sValue);
}
rawResult.Add(row[table.idName], row);
}
}
List <Dictionary <string, string> > result = new List <Dictionary <string, string> >();
foreach (string id in ids)
{
if (rawResult.ContainsKey(id))
{
result.Add(rawResult[id]);
}
}
return(result);
}
}
public void AnonymiseVacancy(ITableSpec tableSpec, dynamic oldRecord, dynamic newRecord)
{
if (_migrateConfig.AnonymiseData)
{
var anon = _avmsSyncRepository.GetAnonymousDetails((int)newRecord.VacancyId);
newRecord.ContactName = $"{anon.GivenName} {anon.Surname}";
}
}
public static Dictionary<string, string> LoadById(this IDBConnection connection, ITableSpec table, string id)
{
List<Dictionary<string, string>> rows = connection.LoadByIds(table, new List<string> { id });
if(rows.Count < 1) {
throw new NotFoundInDBException(table, id);
}
if(rows.Count > 1) {
throw new CriticalException(rows.Count + " objects with specified id");
}
return rows[0];
}
public long LastInsertId(DbCommand command, ITableSpec table)
{
string sequenceName = table.name + "_" + table.idName + "_seq";
using(DbCommand newCommand = command.Connection.CreateCommand()) {
if(command.Transaction != null) {
newCommand.Transaction = command.Transaction;
}
newCommand.CommandType = System.Data.CommandType.Text;
newCommand.CommandText = "SELECT CURRVAL('" + this.escapeIdentifier(sequenceName) + "')";
return (long)newCommand.ExecuteScalar();
}
}
public bool CanMutateVacancyOwnerRelationship(ITableSpec tableSpec, dynamic oldRecordFromTarget, dynamic newRecordFromSource, Operation operation)
{
// Deletes could potentially be a problem, but since using a VacancyOwnerRelationship in a Vacancy results in editing of the EmployerDescription and setting of EditedInRaa, all is good
if (oldRecordFromTarget != null && oldRecordFromTarget.EditedInRaa)
{
_log.Info($"Ignored {operation} to VacancyOwnerRelationship from AVMS with VacancyOwnerRelationshipId = {oldRecordFromTarget.VacancyOwnerRelationshipId} because EditedInRaa=1");
return(false);
}
return(true);
}
public void AnonymiseProviderSite(ITableSpec tableSpec, dynamic oldRecordFromTarget, dynamic newRecord)
{
if (_migrateConfig.AnonymiseData)
{
int id = newRecord.ProviderSiteID;
var anon1 = _avmsSyncRepository.GetAnonymousDetails(id);
var anon2 = _avmsSyncRepository.GetAnonymousDetails(id + 1);
newRecord.ContactDetailsAsARecruitmentAgency = $"{anon1.GivenName} {anon1.Surname} on {anon1.TelephoneNumber}";
newRecord.ContactDetailsForEmployer = $"{anon1.GivenName} on {anon1.TelephoneNumber}";
newRecord.ContactDetailsForCandidate = $"{anon1.GivenName} on {anon2.TelephoneNumber}";
}
}
public void lockTable(Web.Core.DB.Transaction _transaction, ITableSpec table)
{
using (var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock (transaction) {
using (DbCommand command = transaction.sqlconnection.CreateCommand()) {
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "LOCK TABLE " + table.compile(this.traits);
command.ExecuteNonQuery();
}
}
}
}
public long LastInsertId(DbCommand command, ITableSpec table)
{
string sequenceName = table.name + "_" + table.idName + "_seq";
using (DbCommand newCommand = command.Connection.CreateCommand()) {
if (command.Transaction != null)
{
newCommand.Transaction = command.Transaction;
}
newCommand.CommandType = System.Data.CommandType.Text;
newCommand.CommandText = "SELECT CURRVAL('" + this.escapeIdentifier(sequenceName) + "')";
return((long)newCommand.ExecuteScalar());
}
}
public void delete(Web.Core.DB.Transaction _transaction, ITableSpec table, string id)
{
using (var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock (transaction) {
using (DbCommand command = transaction.sqlconnection.CreateCommand()) {
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "DELETE FROM " + table.compile(traits) + " where " + table.getIdSpec().compile(this.traits) + " = " + this.traits.markParam("id");
command.AddParameter("id", id);
command.ExecuteNonQuery();
}
}
}
}
public void delete(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, string id)
{
using(var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock(transaction) {
using(DbCommand command = transaction.sqlconnection.CreateCommand()) {
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "DELETE FROM " + table.compile(traits) + " where " + table.getIdSpec().compile(this.traits) + " = " + this.traits.markParam("id");
command.AddParameter("id", id);
command.ExecuteNonQuery();
}
}
}
}
public void DoSlidingComparision(ITableSpec table, IEnumerable <dynamic> sourceRecords, IEnumerable <dynamic> targetRecords, IDictionary <IKeys, Operation> operationById, IMutateTarget mutateTarget)
{
// TODO: Don't completely ignore the operation claimed by the change tracking
// And don't completely ignore change tracking where no record could be found on the source
using (var sourceRecordEnumerator = sourceRecords.GetEnumerator())
using (var targetRecordEnumerator = targetRecords.GetEnumerator())
{
var sourceRecord = sourceRecordEnumerator.MoveNext() ? sourceRecordEnumerator.Current : null;
var targetRecord = targetRecordEnumerator.MoveNext() ? targetRecordEnumerator.Current : null;
while (sourceRecord != null || targetRecord != null)
{
Keys sourcePrimaryKeys = (sourceRecord != null) ? Keys.GetPrimaryKeys(sourceRecord, table) : Keys.MaxValue;
Keys targetPrimaryKeys = (targetRecord != null) ? Keys.GetPrimaryKeys(targetRecord, table) : Keys.MaxValue;
//_log.Debug($"({sourcePrimaryKeys}) ({targetPrimaryKeys})");
var cmp = targetPrimaryKeys.CompareTo(sourcePrimaryKeys);
if (cmp < 0)
{
// Record is missing from source. Keep advancing target until back in sync.
DoPossibleDelete(mutateTarget, table, targetRecord);
targetRecord = targetRecordEnumerator.MoveNext() ? targetRecordEnumerator.Current : null;
continue;
}
else if (cmp == 0)
{
// Record in both source and target
DoPossibleUpdate(mutateTarget, table, sourceRecord, targetRecord);
sourceRecord = sourceRecordEnumerator.MoveNext() ? sourceRecordEnumerator.Current : null;
targetRecord = targetRecordEnumerator.MoveNext() ? targetRecordEnumerator.Current : null;
continue;
}
else if (cmp > 0)
{
// Record is missing from target. Keep inserting from source and advancing it until back in sync.
DoPossibleInsert(mutateTarget, table, sourceRecord);
sourceRecord = sourceRecordEnumerator.MoveNext() ? sourceRecordEnumerator.Current : null;
continue;
}
else
{
throw new Exception("Impossible");
}
}
}
}
// This is based on the anonymisation script used to create the static anonymised database
// The following were anonymised there, but not here because they are not being migrated:
// Candidate, Stakeholder, VacancySearch, ApplicationUnsuccessfulReasonType, CAFFields, NASSupportContact, Application, AdditionalAnswer
public void AnonymisePerson(ITableSpec tableSpec, dynamic oldRecordFromTarget, dynamic newRecord)
{
if (_migrateConfig.AnonymiseData)
{
int id = newRecord.PersonId;
var anon = _avmsSyncRepository.GetAnonymousDetails(id);
newRecord.Title = (object)_avmsSyncRepository.GetPersonTitleTypeIdsByTitleFullName().GetValueOrDefault(anon.TitleWithoutDot, 6); // Need to manually box (possible Dapper bug)
newRecord.OtherTitle = (newRecord.Title == 6) ? anon.TitleWithoutDot : "";
newRecord.FirstName = anon.GivenName;
newRecord.MiddleNames = (id % 3 == 0) ? "" : anon.MothersMaiden; // TODO - better
newRecord.Surname = anon.Surname;
newRecord.LandlineNumber = anon.TelephoneNumber + "1"; // TODO
newRecord.MobileNumber = anon.TelephoneNumber;
newRecord.Email = anon.EmailAddress;
}
}
public bool CanMutateVacancy(ITableSpec tableSpec, dynamic oldRecordFromTarget, dynamic newRecordFromSource, Operation operation)
{
if (operation == Operation.Delete)
{
// dbo.Application records (which include VacancyId) are not being deleted to deleting Vacancy will fail sometimes
// Also it is probably desirable to keep Vacancy records for the moment, HOWEVER note that the child records are being deleted to
// handle delete/re-add scenarios so the information will be incomplete.
return(false);
}
if (oldRecordFromTarget != null && oldRecordFromTarget.EditedInRaa)
{
_log.Info($"Ignored change to Vacancy from AVMS with VacancyId = {oldRecordFromTarget.VacancyId} because EditedInRaa=1");
return(false);
}
return(true);
}
public void TransformVacancy(ITableSpec tableSpec, dynamic oldRecord, dynamic newRecord)
{
if (oldRecord == null)
{
newRecord.VacancyGuid = (object)Guid.NewGuid(); // Need to manually box object (possible Dapper bug)
}
else
{
newRecord.VacancyGuid = oldRecord.VacancyGuid;
}
// TODO: Well, this is great, but it still won't be set for uploaded vacancies.
var vacancyTypeId = VacancyTypeUnknown;
var apprenticeshipType = newRecord.ApprenticeshipType;
if (apprenticeshipType != ApprenticeshipTypeUnknown)
{
vacancyTypeId = apprenticeshipType == ApprenticeshipTypeTraineeship ? VacancyTypeTraineeship : VacancyTypeApprenticeship;
}
newRecord.VacancyTypeId = (object)vacancyTypeId;
//newRecord.OtherImportantInformation = string.Join(" ", newRecord.OtherImportantInformation, newRecord.RealityCheck); // TODO: This must be in vacancytextfield instead
// The old values in this field would not be recognised by our system (although they will probably have timed out)
newRecord.BeingSupportedBy = null;
newRecord.LockedForSupportUntil = null;
// Required by unsuccessful candidates report and now correctly set in RAA so keeping
//newRecord.ContractOwnerID = null;
//newRecord.OriginalContractOwnerId = null;
// Believed to be supported by FAA, so don't blank (TODO: Check)
// newRecord.EmployerAnonymousName = null;
// newRecord.VacancyManagerAnonymous = false;
// Vacancy source
newRecord.VacancySourceId = (object)1;
AnonymiseVacancy(tableSpec, oldRecord, newRecord);
}
public void DoChangesForTable(ITableSpec table, ISnapshotSyncContext syncContext, IMutateTarget mutateTarget)
{
try
{
var changes = syncContext.GetChangesForTable(table);
var changesOfInterest = new Dictionary <IKeys, Operation>();
foreach (var change in changes)
{
switch (change.Operation)
{
case Operation.Insert:
case Operation.Update:
case Operation.Delete:
changesOfInterest.Add(change.PrimaryKeys, change.Operation);
break;
default:
throw new Exception($"Unknown change {change.Operation}");
}
}
_log.Debug($"Change tracking reports {changesOfInterest.Count(c => c.Value == Operation.Insert)} inserts, {changesOfInterest.Count(c => c.Value == Operation.Update)} updates, {changesOfInterest.Count(c => c.Value == Operation.Delete)} deletes");
if (changesOfInterest.Any())
{
var sourceRecords = syncContext.GetSourceRecordsAsync(table, changesOfInterest.Keys);
var targetRecords = syncContext.GetTargetRecords(table, changesOfInterest.Keys);
_log.Debug($"Loaded {sourceRecords.Result.Count()} from source, {targetRecords.Count()} from target");
DoSlidingComparision(table, sourceRecords.Result, targetRecords, changesOfInterest, mutateTarget);
}
}
finally
{
mutateTarget.FlushInsertsAndUpdates();
}
}
public void AnonymiseEmployerContact(ITableSpec tableSpec, dynamic oldRecordFromTarget, dynamic newRecord)
{
if (_migrateConfig.AnonymiseData)
{
int id = newRecord.EmployerContactId;
var anon1 = _avmsSyncRepository.GetAnonymousDetails(id);
var anon2 = _avmsSyncRepository.GetAnonymousDetails(id + 1);
// Don't think we're using this, so might as well anonymise it
newRecord.AddressLine1 = anon1.StreetAddress;
newRecord.AddressLine2 = "";
newRecord.AddressLine3 = "";
newRecord.AddressLine4 = "";
newRecord.AddressLine5 = null;
newRecord.Town = anon1.City;
// TODO: CountyId
newRecord.PostCode = anon1.ZipCode;
// TODO: LocalAuthorityId
newRecord.FaxNumber = anon1.TelephoneNumber; // TODO
newRecord.AlternatePhoneNumber = anon2.TelephoneNumber;
}
}
public void DoInitial(ITableSpec table, ITransactionlessSyncContext syncContext, IMutateTarget mutateTarget, long maxFirstId)
{
int batchSize = (int)(_migrateConfig.RecordBatchSize * table.BatchSizeMultiplier);
try
{
long startFirstId = (table.Name == "Personxxxx") ? 4290000 : 0;
while (startFirstId < maxFirstId)
{
long endFirstId = Math.Min(startFirstId + batchSize - 1, maxFirstId);
_log.Debug($"Processing {startFirstId} to {endFirstId} on {table.Name} ({startFirstId / Math.Max(maxFirstId / 100, 1)}% done)");
var sourceRecords = syncContext.GetSourceRecordsAsync(table, startFirstId, endFirstId);
var targetRecords = syncContext.GetTargetRecords(table, startFirstId, endFirstId);
DoSlidingComparision(table, sourceRecords.Result, targetRecords, null, mutateTarget);
startFirstId = endFirstId + 1;
}
}
finally
{
try
{
// Where records are deleted and then reinserted in is important that they are deleted before attempting inserts...
mutateTarget.FlushDeletes(); // TODO: Ideally pass parameter through to prevent WARNings and ERRORs.
}
catch (Exception)
{
// ...but when they are on a child table then the parent will need to be deleted first.
_log.Info("Some deletes failed. These will be retried once the parent tables have been processed.");
}
mutateTarget.FlushInsertsAndUpdates();
}
}
public long GetCountByConditions(ITableSpec table, Web.Core.DB.conditions.AbstractCondition conditions, params JoinSpec[] joins)
{
using (var logger = this.CreateCommandExecutionLogger()) {
using (DbConnection connection = this.createConnection()) {
using (DbCommand command = connection.CreateCommand()) {
command.CommandType = System.Data.CommandType.Text;
var conditionsCompiled = ConditionCompiler.Compile(conditions, this.traits);
string queryConditions = "";
if (conditionsCompiled.Key != "")
{
queryConditions = "WHERE " + conditionsCompiled.Key;
}
ParamsHolder paramsHolder = conditionsCompiled.Value;
string queryJoins = "";
{
if (joins.Length > 0)
{
throw new NotImplementedException();
}
}
command.CommandText = logger.commandText = "SELECT COUNT(*) " + "FROM " + table.compile(this.traits) + " " + queryJoins + " " + queryConditions;
foreach (KeyValuePair <string, string> kvp in paramsHolder.data)
{
command.AddParameter(kvp.Key, kvp.Value);
}
object rawCount = command.ExecuteScalar();
long count = (long)rawCount;
return(count);
}
}
}
}
public List<string> LoadIdsByConditions(ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, ColumnSpec idSpec, bool allowHugeLists)
{
using(DbConnection connection = this.createConnection()) {
using(DbCommand command = connection.CreateCommand()) {
return this._LoadIdsByConditions(command, table, conditions, diapasone, joins, sorts, idSpec, allowHugeLists);
}
}
}
public List<Dictionary<string, string>> LoadByIds(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, List<string> ids)
{
Transaction transaction = (Transaction)_transaction;
lock(transaction) {
using(DbCommand command = transaction.sqlconnection.CreateCommand()) {
command.Transaction = transaction.sqltransaction;
return this._LoadByIds(command, table, ids, true);
}
}
}
public List<Dictionary<string, string>> LoadByIds(ITableSpec table, List<string> ids)
{
using(DbConnection connection = this.createConnection()) {
using(DbCommand command = connection.CreateCommand()) {
return this._LoadByIds(command, table, ids, false);
}
}
}
public JoinSpec(ColumnSpec mainColumn, ITableSpec additionalTable, string alias)
{
this.mainColumn = mainColumn;
this.additionalTableRaw = additionalTable;
this.additionalTable = new TableSpec(additionalTable, alias);
}
public long GetCountByConditions(ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, params JoinSpec[] joins)
{
using(var logger = this.CreateCommandExecutionLogger()) {
using(DbConnection connection = this.createConnection()) {
using(DbCommand command = connection.CreateCommand()) {
command.CommandType = System.Data.CommandType.Text;
var conditionsCompiled = ConditionCompiler.Compile(conditions, this.traits);
string queryConditions = "";
if(conditionsCompiled.Key != "") queryConditions = "WHERE " + conditionsCompiled.Key;
ParamsHolder paramsHolder = conditionsCompiled.Value;
string queryJoins = "";
{
if(joins.Length > 0) {
throw new NotImplementedException();
}
}
command.CommandText = logger.commandText = "SELECT COUNT(*) " + "FROM " + table.compile(this.traits) + " " + queryJoins + " " + queryConditions;
foreach(KeyValuePair<string, string> kvp in paramsHolder.data) {
command.AddParameter(kvp.Key, kvp.Value);
}
object rawCount = command.ExecuteScalar();
long count = (long)rawCount;
return count;
}
}
}
}
private List<string> _LoadIdsByConditions(DbCommand command, ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, ColumnSpec idSpec, bool allowHugeLists)
{
using(var logger = this.CreateCommandExecutionLogger()) {
command.CommandType = System.Data.CommandType.Text;
var conditionsCompiled = ConditionCompiler.Compile(conditions, this.traits);
string queryConditions = "";
if(conditionsCompiled.Key != "") queryConditions = "WHERE " + conditionsCompiled.Key;
ParamsHolder paramsHolder = conditionsCompiled.Value;
StringBuilder queryJoins = new StringBuilder();
{
foreach(var join in joins) {
queryJoins.Append(" JOIN ");
queryJoins.Append(join.additionalTableRaw.compile(this.traits));
queryJoins.Append(" ");
queryJoins.Append(join.additionalTable.compile(this.traits));
queryJoins.Append(" ON ");
queryJoins.Append(join.mainColumn.compile(this.traits));
queryJoins.Append(" = ");
queryJoins.Append(join.additionalTable.getIdSpec().compile(this.traits));
}
}
string querySorts = "";
if(sorts.Length > 0) {
List<string> sortParts = new List<string>();
foreach(SortSpec sortSpec in sorts) {
if(sortSpec.ascending) {
sortParts.Add(sortSpec.column.compile(this.traits) + " ASC");
} else {
sortParts.Add(sortSpec.column.compile(this.traits) + " DESC");
}
}
querySorts = "ORDER BY " + string.Join(", ", sortParts.ToArray());
}
string queryMain = "FROM " + table.compile(this.traits) + " " + queryJoins + " " + queryConditions;
foreach(KeyValuePair<string, string> kvp in paramsHolder.data) {
command.AddParameter(kvp.Key, kvp.Value);
}
if(!diapasone.total.HasValue) {
command.CommandText = logger.commandText = "SELECT COUNT(*) " + queryMain;
object rawCount;
//try {
rawCount = command.ExecuteScalar();
//} catch(Npgsql.NpgsqlException e) {
//throw new FLocalException("Error while trying to execute " + command.CommandText + ": " + e.Message);
//}
long count = (long)rawCount;
if(count < 1) {
diapasone.total = 0;
} else {
diapasone.total = count;
}
}
if(diapasone.total.Value < 1) {
return new List<string>();
} else {
if(diapasone.total.Value > 1000 && diapasone.count < 0 && !allowHugeLists) {
throw new CriticalException("huge list");
}
string queryLimits = "";
if(diapasone.count >= 0) {
queryLimits = "LIMIT " + diapasone.count + " OFFSET " + diapasone.start;
}
command.CommandText = logger.commandText = "SELECT " + idSpec.compile(this.traits) + " " + queryMain + " " + querySorts + " " + queryLimits;
List<string> result = new List<string>();
using(DbDataReader reader = command.ExecuteReader()) {
while(reader.Read()) {
result.Add(reader.GetValue(0).ToString());
}
}
return result;
}
}
}
public void update(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, string id, Dictionary<string, string> data)
{
using(var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock(transaction) {
using(DbCommand command = transaction.sqlconnection.CreateCommand()) {
List<string> updates = new List<string>();
ParamsHolder paramsholder = new ParamsHolder();
foreach(KeyValuePair<string, string> kvp in data) {
updates.Add(this.traits.escapeIdentifier(kvp.Key) + " = " + this.traits.markParam(paramsholder.Add(kvp.Value)));
}
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "UPDATE " + table.compile(traits) + " set " + String.Join(", ", updates.ToArray()) + " where " + table.getIdSpec().compile(this.traits) + " = " + this.traits.markParam("id");
command.AddParameter("id", id);
foreach(KeyValuePair<string, string> kvp in paramsholder.data) {
command.AddParameter(kvp.Key, kvp.Value);
}
// throw new CriticalException(command.CommandText + "; parameters: " + string.Join(", ", (from DbParameter parameter in command.Parameters select parameter.ParameterName + "='" + parameter.Value.ToString() + "'").ToArray()));
command.ExecuteNonQuery();
}
}
}
}
public void TransformAttachedDocument(ITableSpec tableSpec, dynamic oldRecord, dynamic newRecord)
{
//newRecord.Attachment = new byte[0]; // These are rather large and causing timeouts
}
public JoinSpec(ColumnSpec mainColumn, ITableSpec additionalTable, string alias)
{
this.mainColumn = mainColumn;
this.additionalTableRaw = additionalTable;
this.additionalTable = new TableSpec(additionalTable, alias);
}
public NotFoundInDBException(ITableSpec tableSpec, string id)
: this(tableSpec.getIdSpec(), id)
{
}
public static string compile(this ITableSpec table, IDBTraits traits)
{
return(traits.escapeIdentifier(table.name));
}
public TableSpec(ITableSpec raw, string name)
{
this._name = name;
this._idName = raw.idName;
}
public List<string> LoadIdsByConditions(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, bool allowHugeLists)
{
Transaction transaction = (Transaction)_transaction;
lock(transaction) {
using(DbCommand command = transaction.sqlconnection.CreateCommand()) {
command.Transaction = transaction.sqltransaction;
return this._LoadIdsByConditions(command, table, conditions, diapasone, joins, sorts, table.getIdSpec(), allowHugeLists);
}
}
}
public static List<string> LoadIdsByConditions(this IDBConnection connection, ITableSpec table, AbstractCondition conditions, Diapasone diapasone, ColumnSpec idSpec, params SortSpec[] sorts)
{
return connection.LoadIdsByConditions(table, conditions, diapasone, new JoinSpec[0], sorts, idSpec, false);
}
public void lockTable(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table)
{
using(var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock(transaction) {
using(DbCommand command = transaction.sqlconnection.CreateCommand()) {
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "LOCK TABLE " + table.compile(this.traits);
command.ExecuteNonQuery();
}
}
}
}
private void DoPossibleUpdate(IMutateTarget mutateTarget, ITableSpec table, dynamic sourceRecord, dynamic targetRecord)
{
table.Transform(table, targetRecord, sourceRecord);
int changesLeftToLog = _migrateConfig.MaxNumberOfChangesToDetailPerTable.GetValueOrDefault(int.MaxValue) - mutateTarget.NumberOfUpdates;
var sourceRecordDict = (IDictionary <string, object>)sourceRecord;
var targetRecordDict = (IDictionary <string, object>)targetRecord;
List <string> changes = null;
foreach (var sourceCol in sourceRecordDict)
{
var targetColValue = targetRecordDict[sourceCol.Key];
bool equal;
if (sourceCol.Value == null || targetColValue == null)
{
equal = (sourceCol.Value == null && targetColValue == null);
}
else if (sourceCol.Value.GetType() == typeof(byte[]))
{
var s = (byte[])sourceCol.Value;
var t = (byte[])targetColValue;
equal = s.SequenceEqual(t);
}
else
{
equal = sourceCol.Value.Equals(targetColValue);
}
if (!equal)
{
if (changes == null)
{
changes = new List <string>();
if (changesLeftToLog <= 0)
{
break;
}
}
changes.Add($"{sourceCol.Key} '{(targetColValue == null ? "<null>" : targetColValue.ToString().Truncate(100, "..."))}' => '{(sourceCol.Value == null ? "<null>" : sourceCol.Value.ToString().Truncate(100, "..."))}'");
}
}
if (changes != null && table.CanMutate(table, targetRecord, sourceRecord, Operation.Update))
{
if (changesLeftToLog == 0)
{
_log.Info($"{table.Name}: More records have changes but these will not be reported");
}
else if (changesLeftToLog > 0)
{
_log.Info($"{table.Name} {Keys.GetPrimaryKeys(sourceRecord, table)}: {string.Join(", ", changes)}");
}
mutateTarget.Update(sourceRecord);
}
else
{
mutateTarget.NoChange(sourceRecord);
}
}
private List<Dictionary<string, string>> _LoadByIds(DbCommand command, ITableSpec table, List<string> ids, bool forUpdate)
{
using(var logger = this.CreateCommandExecutionLogger()) {
command.CommandType = System.Data.CommandType.Text;
ParamsHolder paramsHolder = new ParamsHolder();
List<string> placeholder = new List<string>();
foreach(string id in ids) {
placeholder.Add(this.traits.markParam(paramsHolder.Add(id)));
}
command.CommandText = logger.commandText = "SELECT * FROM " + table.compile(this.traits) + " WHERE " + table.getIdSpec().compile(this.traits) + " IN (" + string.Join(", ", placeholder.ToArray()) + ")" + (forUpdate ? " FOR UPDATE" : "");
//command.Prepare();
foreach(KeyValuePair<string, string> kvp in paramsHolder.data) {
command.AddParameter(kvp.Key, kvp.Value);
}
Dictionary<string, Dictionary<string, string>> rawResult = new Dictionary<string, Dictionary<string, string>>();
using(DbDataReader reader = command.ExecuteReader()) {
while(reader.Read()) {
Dictionary<string, string> row = new Dictionary<string, string>();
for(int i=0; i<reader.FieldCount; i++) {
//throw new CriticalException("Name: " + reader.GetName(i));
object value = reader.GetValue(i);
string sValue;
if(value is DateTime) {
sValue = ((DateTime)value).Ticks.ToString();
} else if(value is TimeSpan) {
sValue = ((TimeSpan)value).Ticks.ToString();
} else {
sValue = value.ToString();
}
row.Add(reader.GetName(i), sValue);
}
rawResult.Add(row[table.idName], row);
}
}
List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
foreach(string id in ids) {
if(rawResult.ContainsKey(id)) {
result.Add(rawResult[id]);
}
}
return result;
}
}
public static List<string> LoadIdsByConditions(this IDBConnection connection, ITableSpec table, AbstractCondition conditions, Diapasone diapasone, params JoinSpec[] joins)
{
return connection.LoadIdsByConditions(table, conditions, diapasone, joins, new SortSpec[] { new SortSpec(table.getIdSpec(), true) });
}
public void TransformProvider(ITableSpec tableSpec, dynamic oldRecord, dynamic newRecord)
{
newRecord.UPIN = null; // We can't always populate this, so always set it to null to prove it isn't used.
}
public string insert(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, Dictionary<string, string> data)
{
using(var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock(transaction) {
using(DbCommand command = transaction.sqlconnection.CreateCommand()) {
List<string> updates = new List<string>();
List<string> updatesPlaceholders = new List<string>();
ParamsHolder paramsholder = new ParamsHolder();
foreach(KeyValuePair<string, string> kvp in data) {
updates.Add(this.traits.escapeIdentifier(kvp.Key));
updatesPlaceholders.Add(this.traits.markParam(paramsholder.Add(kvp.Value)));
}
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "INSERT INTO " + table.compile(this.traits) + " (" + String.Join(", ", updates.ToArray()) + ") VALUES (" + String.Join(", ", updatesPlaceholders.ToArray()) + ")";
foreach(KeyValuePair<string, string> kvp in paramsholder.data) {
command.AddParameter(kvp.Key, kvp.Value);
}
command.ExecuteNonQuery();
if(data.ContainsKey(table.idName)) return data[table.idName];
return this.traits.LastInsertId(command, table).ToString();
}
}
}
}
public TableSpec(ITableSpec raw, string name)
{
this._name = name;
this._idName = raw.idName;
}
public static List<string> LoadIdsByConditions(this IDBConnection connection, ITableSpec table, AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts)
{
return connection.LoadIdsByConditions(table, conditions, diapasone, joins, sorts, table.getIdSpec(), false);
}
public NotFoundInDBException(ITableSpec tableSpec, string id) : this(tableSpec.getIdSpec(), id)
{
}
public ColumnSpec(ITableSpec table, string name)
{
this.table = table;
this.name = name;
}