/// <summary> /// Get a deep copy of data /// </summary> /// <returns></returns> public NormalizeInfo Clone() { NormalizeInfo result = (NormalizeInfo)MemberwiseClone(); result.Fields = new List <NormalizeFieldInfo>(Fields.Count); result.ChildDirectories = new List <NormalizeInfo>(ChildDirectories.Count); foreach (NormalizeInfo childDirectory in ChildDirectories) { result.ChildDirectories.Add(childDirectory.Clone()); } foreach (NormalizeFieldInfo field in Fields) { result.Fields.Add(field.Clone()); } return(result); }
private long? SaveMemoryQueryData( long queryId, NormalizeInfo dbStructure, DataTable queryData, long sessionId, DataTable currentTable ) { if (!this._isMemoryStorage) { return 0L; } ReportTable table = null; Dictionary<int, int> rowOrderDict = new Dictionary<int, int>(); Dictionary<string, int> currentDict = new Dictionary<string, int>(); Dictionary<string, int> insertDict = new Dictionary<string, int>(); Dictionary<string, int> updateDict = new Dictionary<string, int>(); HashSet<string> duplicateRows = new HashSet<string>(); HashSet<string> updatedRows = new HashSet<string>(); //List<RowUpdateInput> rowsToUpdate = new List<RowUpdateInput>(); //List<RowUpdateOrderInput> rowsToUpdateOrder = new List<RowUpdateOrderInput>(); List<ITableRow> rows = new List<ITableRow>(); List<ITableRow> rowsToUpdateList = new List<ITableRow>(); List<string> rowsToDelete = new List<string>(); bool needInsertCommand = false; int currentRowOrder = 0; int queryRowOrder = 0; //long rowsDeleted = 0L; //long rowsDuplicatedDeleted = 0L; long rowsInserted = 0L; //long rowsUnchanged = 0L; long rowsUpdated = 0L; if (currentTable != null && currentTable.Rows != null) { for (int i = 0; i < currentTable.Rows.Count; i++) { DataRow row = currentTable.Rows[i]; string hash = row[CommonRowFiller.RowHashFieldName].ToString(); if (!currentDict.ContainsKey(hash)) { currentDict.Add(hash, i); } else { duplicateRows.Add(hash); } } } this.PrepareTables(dbStructure); // Log.DebugFormat( // @"SaveQueryData:BeginLock:QueryId:'{0}';sessionId:'{1}'", // queryId, // sessionId // ); lock (this._tablesLock) { if (!this._tables.TryGetValue(dbStructure, out table)) { Log.DebugFormat("SaveQueryData:EndLock(TryGetValue):QueryId:'{0}';sessionId:'{1}'", queryId, sessionId ); return null; } } // Log.DebugFormat( // @"SaveQueryData:EndLock:QueryId:'{0}';sessionId:'{1}'", // queryId, // sessionId // ); foreach (DataRow queryRow in queryData.Rows) { string hash = GetHashFromDataRow(queryRow); bool hashExists = currentDict.ContainsKey(hash); if (hashExists && !duplicateRows.Contains(hash)) { updateDict.Add(hash, currentRowOrder); updatedRows.Add(hash); currentDict.Remove(hash); rowOrderDict.Add(currentRowOrder, queryRowOrder); currentRowOrder++; } else { bool needInsert = !insertDict.ContainsKey(hash) && !updatedRows.Contains(hash); if (needInsert) { insertDict.Add(hash, currentRowOrder); rowOrderDict.Add(currentRowOrder, queryRowOrder); currentRowOrder++; } } queryRowOrder++; } foreach (string hash in currentDict.Keys) { if (!updatedRows.Contains(hash) && !duplicateRows.Contains(hash)) { rowsToDelete.Add(hash); } } // Log.DebugFormat( // @"SaveQueryData:BeginLock:QueryId:'{0}';sessionId:'{1}'", // queryId, // sessionId // ); lock (this._tablesLock) { foreach (KeyValuePair<string, int> keyPair in insertDict) { string hash = keyPair.Key; int rowOrder = keyPair.Value; DataRow row = queryData.Rows[rowOrderDict[rowOrder]]; ITableRow tableRow; this.ProcessRowForWrite(row, dbStructure, out tableRow, false); if (rowsToDelete.Count > 0L) { string hashOld = rowsToDelete[0]; CommonRowFiller.ModifyTableRow(tableRow, queryId, sessionId, rowOrder, hash); tableRow.Values[CommonRowFiller.RowHashFieldName + "Old"] = hashOld; rowsToUpdateList.Add(tableRow); rowsToDelete.RemoveAt(0); } else { needInsertCommand = true; CommonRowFiller.ModifyTableRow(tableRow, queryId, sessionId, rowOrder, hash); tableRow.Values[CommonRowFiller.RowHashFieldName + "Old"] = -1; rows.Add(tableRow); } } foreach (KeyValuePair<string, int> keyPair in updateDict) { string hash = keyPair.Key; int rowOrder = keyPair.Value; DataRow row = queryData.Rows[rowOrderDict[rowOrder]]; ITableRow tableRow; this.ProcessRowForWrite(row, dbStructure, out tableRow, false); CommonRowFiller.ModifyTableRow(tableRow, queryId, sessionId, rowOrder, hash); tableRow.Values[CommonRowFiller.RowHashFieldName + "Old"] = hash; rowsToUpdateList.Add(tableRow); } rowsUpdated += table.ReplaceRowsTrans(rowsToUpdateList); if (needInsertCommand) { rowsInserted += table.ReplaceRowsTrans(rows); } } // Log.DebugFormat( // @"SaveQueryData:EndLock:QueryId:'{0}';sessionId:'{1}'", // queryId, // sessionId // ); Log.DebugFormat("rowsInserted:{0};rowsUpdated:{1}", rowsInserted, rowsUpdated ); return rowsInserted + rowsUpdated; }
public NormalizeInfo GetDbStucture( TemplateNodeQueryInfo templateNodeQueryInfo, Int64 recordSet, InstanceInfo instance, DataTable dataTable = null ) { List<QueryInfo> queries = Model.GetQueryByTemplateNodeQueryInfo(templateNodeQueryInfo); QueryInfo query = queries.FirstOrDefault(x => x.Source == instance.Type || x.Source == QuerySource.SQLite); string tableName = this.GetTableName(templateNodeQueryInfo, recordSet); string tableIndexes = string.Empty; if (query != null) { tableIndexes = query.QueryIndexFileds; } lock (this._dbStructures) { if (!this._dbStructures.ContainsKey(tableName)) { NormalizeInfo definedRecordSet = query.GetDbStructureForRecordSet(recordSet); if (definedRecordSet != null) { var clone = definedRecordSet.Clone(); clone.TableName = tableName; this._dbStructures.Add(tableName, clone); } else { if (dataTable == null) { NormalizeInfo result = new NormalizeInfo(); NormalizeFieldInfo field = new NormalizeFieldInfo(); result.TableName = tableName; result.Fields = new List<NormalizeFieldInfo>(); field.Name = "*"; field.Type = SqlDbType.NVarChar; result.Fields.Add(field); result.TableIndexFileds = tableIndexes; return result; } NormalizeInfo dbStructure = new NormalizeInfo { TableName = tableName, IsAutoGenerated = true, Fields = new List<NormalizeFieldInfo>(), ChildDirectories = new List<NormalizeInfo>(), RecordSet = recordSet, QueryName = query.Name, TableIndexFileds = tableIndexes }; foreach (DataColumn column in dataTable.Columns) { NormalizeFieldInfo field = new NormalizeFieldInfo(); field.Name = column.ColumnName; field.Type = column.DataType.ToSqlDbType(); field.IsUnique = true; dbStructure.Fields.Add(field); } this._dbStructures.Add(tableName, dbStructure); } } } NormalizeInfo structure = this._dbStructures[tableName]; return structure; }
protected override long? SaveQueryData( long queryId, NormalizeInfo dbStructure, DataTable queryData, long sessionId ) { long rowsInserted = 0L; long rowsUpdated = 0L; long rowsDeleted = 0L; long rowsUpdatedRowOrder = 0L; long rowsTotal = 0L; long? rowsInMemory = 0L; this.PrepareTables(dbStructure); if (this._memoryStorage == null) { this._memoryStorage = new ReportMemoryStorage("file::memory:"); } DataTable currentData = this.ReadResult(dbStructure, queryId, true); this._memoryStorage.ClearData(queryId, dbStructure, sessionId); rowsInMemory = this._memoryStorage.SaveMemoryQueryData(queryId, dbStructure, queryData, sessionId, currentData); this.AttachDatabase("file:memdb1?mode=memory&cache=shared", "temp_report"); string rowHashF = (CommonRowFiller.RowHashFieldName).AsDbName(); string rowHashOldF = (CommonRowFiller.RowHashFieldName + "Old").AsDbName(); string queryIdF = QueryDirectory.TableName.AsFk().AsDbName(); string sessionIdF = MetaResultTable.SessionIdFieldName.AsDbName(); string masterFieldsList = this.GetFieldsString(dbStructure, string.Empty); // Log.DebugFormat("masterFieldsList:'{0}'", // masterFieldsList // ); string memoryFieldsList = this._memoryStorage.GetFieldsString(dbStructure, "tR"); // Log.DebugFormat("memoryFieldsList:'{0}'", // memoryFieldsList // ); memoryFieldsList = memoryFieldsList.Replace(",tR." + rowHashOldF, string.Empty); // Log.DebugFormat("memoryFieldsList:'{0}'", // memoryFieldsList // ); string insertMemoryFieldsList = memoryFieldsList; // restore key fields memoryFieldsList = memoryFieldsList.Replace(",tR." + rowHashF, ",dest." + rowHashF); memoryFieldsList = memoryFieldsList.Replace(",tR." + queryIdF, ",dest." + queryIdF); memoryFieldsList = memoryFieldsList.Replace(",tR." + sessionIdF, ",dest." + sessionIdF); // remove date fields // memoryFieldsList = memoryFieldsList.Replace(",temp_report." + dbStructure.TableName + "." + TableDefinition.DateCreated.AsDbName(), ""); // memoryFieldsList = memoryFieldsList.Replace(",temp_report." + dbStructure.TableName + "." + TableDefinition.DateUpdated.AsDbName(), ""); // masterFieldsList = masterFieldsList.Replace("," + TableDefinition.DateCreated.AsDbName(), ""); // masterFieldsList = masterFieldsList.Replace("," + TableDefinition.DateUpdated.AsDbName(), ""); string deleteQuery = string.Format( " DELETE FROM {0}" + " WHERE" + " {1} NOT IN (" + " SELECT" + " tR.{2}" + " FROM" + " [temp_report].{0} tR" + " WHERE" + " tR.{3} = {4}" + " AND tR.{5} = {6}" + " )" + " AND {3} = {4}" + " AND {5} = {6}" + ";", dbStructure.TableName.AsDbName(), rowHashF, rowHashOldF, queryIdF, queryId, sessionIdF, sessionId ); // Log.DebugFormat("deleteStr:'{0}'", // deleteStr // ); string updateQuery = string.Format( " REPLACE INTO {0}" + " (" + " {1}" + " )" + " SELECT" + " {2}" + " FROM" + " [temp_report].{0} tR" + " INNER JOIN {0} dest ON" + " tR.{3} = dest.{4}" + " AND tR.{4} != dest.{4}" + " WHERE" + " tR.{5} = {6}" + " AND tR.{7} = {8}" + " ;", dbStructure.TableName.AsDbName(), masterFieldsList, memoryFieldsList, rowHashOldF, rowHashF, queryIdF, queryId, sessionIdF, sessionId ); // Log.DebugFormat("updateStr:'{0}'", // updateStr // ); string insertQuery = string.Format( " INSERT INTO {0}" + " (" + " {1}" + " )" + " SELECT" + " {2}" + " FROM" + " [temp_report].{0} tR" + " WHERE" + " tR.{3} = -1" + " AND tR.{4} = {5}" + " AND tR.{6} = {7}" + " AND tR.{8} NOT IN (" + " SELECT" + " {8}" + " FROM" + " {0}" + " WHERE" + " {4} = {5}" + " AND {6} = {7}" + " )" + ";", dbStructure.TableName.AsDbName(), masterFieldsList, insertMemoryFieldsList, rowHashOldF, queryIdF, queryId, sessionIdF, sessionId, rowHashF ); // Log.DebugFormat("insertStr:'{0}'", // insertStr // ); string updateRowOrderQuery = string.Format( " UPDATE {0} SET" + " {1} = (" + " SELECT" + " tR.{1}" + " FROM" + " [temp_report].{0} tR" + " WHERE" + " tR.{2} = {3}" + " AND tR.{4} = {5}" + " AND tR.{6} = {0}.{6}" + " AND tR.{1} != {0}.{1}" + " )" + " WHERE" + " EXISTS (" + " SELECT" + " 1" + " FROM" + " [temp_report].{0} tR" + " WHERE" + " tR.{2} = {3}" + " AND tR.{4} = {5}" + " AND tR.{6} = {0}.{6}" + " AND tR.{1} != {0}.{1}" + " )" + ";", dbStructure.TableName.AsDbName(), CommonRowFiller.RowOrderFieldName.AsDbName(), queryIdF, queryId, sessionIdF, sessionId, rowHashF ); // Log.DebugFormat("strUpdateRowOrder:'{0}'", // strUpdateRowOrder // ); string countRowsQuery = string.Format( " SELECT" + " COUNT(*)" + " FROM" + " {0}" + " WHERE" + " {1} = {2}" + " AND {3} = {4};", dbStructure.TableName.AsDbName(), queryIdF, queryId, sessionIdF, sessionId ); // Log.DebugFormat("countRowsQuery:'{0}'", // countRowsQuery // ); // operations rowsDeleted = this.ExecuteNonQuery(deleteQuery); rowsUpdated = this.ExecuteNonQuery(updateQuery); rowsInserted = this.ExecuteNonQuery(insertQuery); rowsUpdatedRowOrder = this.ExecuteNonQuery(updateRowOrderQuery); rowsTotal = this.ExecuteScalar<long>(countRowsQuery); Log.DebugFormat( @"rowsInserted:{0};rowsUpdated:{1};rowsDeleted:{2};rowsUpdatedRowOrder:{3};totalRows:{4}", rowsInserted, rowsUpdated, rowsDeleted, rowsUpdatedRowOrder, rowsTotal ); if (rowsTotal != rowsInMemory) { Log.ErrorFormat( @"Rows added to in-memory table:'{0}'. Rows formed by merge:'{1}'.", rowsInMemory, rowsTotal ); } return rowsTotal; }