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