public ReportMemoryStorage( string fileName, bool readOnly = false ) : base( fileName, readOnly ) { this._memoryStorage = null; this._isMemoryStorage = fileName.Equals("file::memory:"); }
public ReportMemoryStorage( string fileName, bool readOnly = false ) : base( fileName, readOnly ) { this._memoryStorage = null; this._isMemoryStorage = fileName.Equals("file::memory:"); }
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); }
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; }