public override void update()
{
CswAuditMetaData CswAuditMetaData = new CswAuditMetaData();
foreach( CswNbtMetaDataNodeType CurrentNodeType in _CswNbtSchemaModTrnsctn.MetaData.getNodeTypes() )
{
if( AuditLevel.NoAudit != CurrentNodeType.AuditLevel )
{
string CurrentAuditLevel = CurrentNodeType.AuditLevel.ToString();
string Where = " where nodetypeid= " + CurrentNodeType.NodeTypeId.ToString() + " and " + CswAuditMetaData.AuditLevelColName + " <> '" + CurrentAuditLevel + "' ";
CswTableUpdate CurrentNodesUpdate = _CswNbtSchemaModTrnsctn.makeCswTableUpdate( "update_nodes_case_27709", "nodes" );
DataTable NodesTable = CurrentNodesUpdate.getTable( Where );
foreach( DataRow CurrentRow in NodesTable.Rows )
{
CurrentRow[CswAuditMetaData.AuditLevelColName] = CurrentAuditLevel;
}
CurrentNodesUpdate.update( NodesTable );
}
}
}//Update()
public override void update()
{
// Populate audit_transaction.auditdate
CswAuditMetaData CswAuditMetaData = new CswAuditMetaData();
// Since we don't know what table goes with what audit transaction, we'll need to go through every auditable table. Fun fun.
string Sql = @"update audit_transactions x
set auditdate = (select min(recordcreated) auditdate
from (
select audittransactionid, recordcreated from blob_data_audit
union select audittransactionid, recordcreated from jct_nodes_props_audit
union select audittransactionid, recordcreated from license_accept_audit
union select audittransactionid, recordcreated from nodes_audit
union select audittransactionid, recordcreated from nodetypes_audit
union select audittransactionid, recordcreated from nodetype_props_audit
union select audittransactionid, recordcreated from nodetype_tabset_audit
union select audittransactionid, recordcreated from node_views_audit
union select audittransactionid, recordcreated from object_class_audit
union select audittransactionid, recordcreated from object_class_props_audit
) m
where m.audittransactionid = x.audittransactionid)
where auditdate is null";
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql( Sql );
} // update()
public override void update()
{
CswAuditMetaData CswAuditMetaData = new CswAuditMetaData();
// part 4, add audittransaction.datetime
_CswNbtSchemaModTrnsctn.addDateColumn(CswAuditMetaData.AuditTransactionTableName, "auditdate", "DateTime of audit transaction", true);
// Inspect shadow tables for missing columns
Int32 n = 1;
foreach (string TableName in _CswNbtSchemaModTrnsctn.CswDataDictionary.getTableNames(IncludeAudit: false))
{
// this function will handle finding missing tables and columns, and creating them
bool audited = _CswNbtSchemaModTrnsctn.makeTableAuditable(TableName);
if (audited)
{
// case 30839
_CswNbtSchemaModTrnsctn.indexColumn(CswAuditMetaData.makeAuditTableName(TableName), "audittransactionid, recordcreated", "audit" + n);
n++;
}
}
} // update()
public static string getAuditLookupFunctionNameForRealTable(string RealTableName)
{
CswAuditMetaData CswAuditMetaData = new CswAuditMetaData();
return(getAuditLookupFunctionName(CswAuditMetaData.makeAuditTableName(RealTableName)));
}
public static string getAbbreviationForRealTable(string RealTableName)
{
CswAuditMetaData CswAuditMetaData = new CswAuditMetaData();
return(getAbbreviation(CswAuditMetaData.makeAuditTableName(RealTableName)));
}
public override void update()
{
foreach (string Abbrev in CswNbtAuditTableAbbreviation.Abbreviations)
{
ICswDataDictionaryReader DataDictionary = _CswNbtSchemaModTrnsctn.CswDataDictionary;
CswAuditMetaData CswAuditMetaData = new CswAuditMetaData();
string AuditTable = CswNbtAuditTableAbbreviation.getAuditTableName(Abbrev);
string RealTable = CswAuditMetaData.makeNameOfAuditedTable(AuditTable);
string AuditTablePk = DataDictionary.getPrimeKeyColumn(AuditTable);
string RealTablePk = DataDictionary.getPrimeKeyColumn(RealTable);
string ObjectType = "CSW_" + Abbrev + @"_OBJ_TYPE";
string TableType = "CSW_" + Abbrev + @"_TABLE_TYPE";
string FuncName = CswNbtAuditTableAbbreviation.getAuditLookupFunctionName(AuditTable);
bool requiresNodeId = CswNbtAuditTableAbbreviation.requiresNodeId(RealTable);
CswCommaDelimitedString Columns = new CswCommaDelimitedString();
CswCommaDelimitedString ObjectTypeColumns = new CswCommaDelimitedString();
foreach (string ColumnName in _CswNbtSchemaModTrnsctn.CswDataDictionary.getColumnNames(RealTable))
{
if (ColumnName != "auditlevel")
{
DataDictionary.setCurrentColumn(RealTable, ColumnName);
string OracleType = CswDbVendorOpsOracle._getOracleDataTypeFromPortableDataType(DataDictionary.PortableDataType, DataDictionary.DataTypeSize);
Columns.Add(ColumnName);
ObjectTypeColumns.Add(ColumnName + " " + OracleType);
}
}
//Columns.Add( "recordcreated" );
ObjectTypeColumns.Add("recordcreated date");
string ObjectTypeSql = @"CREATE OR REPLACE TYPE " + ObjectType + " IS OBJECT (" + ObjectTypeColumns.ToString(false) + ");";
string TableTypeSql = @"CREATE OR REPLACE TYPE " + TableType + " AS TABLE OF " + ObjectType + ";";
string FuncSql = @"create or replace function " + FuncName + @" (AsOfDate in Date";
if (requiresNodeId)
{
FuncSql += ", aNodeId in number";
}
FuncSql += @" ) return " + TableType + @" is
ResultTable " + TableType + @";
begin
with audit1 as (select " + AuditTablePk + @", " + RealTablePk + @", auditeventtype
from " + AuditTable + @" a
where a." + AuditTablePk + @" = (select max(" + AuditTablePk + @")
from " + AuditTable + @" a2
where a2.recordcreated <= AsOfDate
and a2." + RealTablePk + @" = a." + RealTablePk + @"))
select " + ObjectType + @"(" + Columns.ToString(false) + @",recordcreated) BULK COLLECT into ResultTable
from (select " + Columns.ToString(false) + @", recordcreated
from " + AuditTable + @"
where " + AuditTablePk + @" in (select " + AuditTablePk + @" from audit1)
and auditeventtype <> 'PhysicalDelete'
union all
select " + Columns.ToString(false) + @", sysdate as recordcreated
from " + RealTable + @"
where " + RealTablePk + @" not in (select " + RealTablePk + @" from audit1))";
if (requiresNodeId)
{
FuncSql += " where nodeid = aNodeId ";
}
FuncSql += @";
RETURN ResultTable;
EXCEPTION
WHEN OTHERS THEN
ResultTable.DELETE;
RETURN ResultTable;
end " + FuncName + @";";
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(
@"declare
object_not_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(object_not_exists, -04043);
begin
execute immediate 'drop type " + TableType + @" force';
exception
when object_not_exists then null;
end;"
);
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(
@"declare
object_not_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(object_not_exists, -04043);
begin
execute immediate 'drop type " + ObjectType + @" force';
exception
when object_not_exists then null;
end;"
);
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(ObjectTypeSql);
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(TableTypeSql);
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(FuncSql);
} // foreach
} //update()
