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()
public static void startCAFImportImpl(ICswResources CswResources, string CAFDatabase, string CAFSchema, string CAFPassword, CswEnumSetupMode SetupMode)
{
CswNbtResources _CswNbtResources = (CswNbtResources)CswResources;
//connect to the CAF database
CswDbVendorOpsOracle CAFConnection = new CswDbVendorOpsOracle("CAFImport", CAFDatabase, CAFSchema, CAFPassword, (CswDataDictionary)_CswNbtResources.DataDictionary, _CswNbtResources.CswLogger, CswEnumPooledConnectionState.Open, "");
string Error = "";
if (false == CAFConnection.IsDbConnectionHealthy(ref Error))
{
throw new CswDniException(CswEnumErrorType.Error, "Check the supplied parameters for the CAF database.", Error);
}
//Run the SQL to generate the table, views, triggers, and other setup operations.
//there is no clean solution for running the contents of .SQL file from inside C#, so please forgive the horrible hacks that follow.
//Assumptions made here:
// the only PL/SQL blocks are the deletes at the top of the script and the triggers at the bottom,
// the / at the end of PL/SQL is always at the beginning of a line,
// triggers always have two lines of spaces before them, except the very first trigger, which has 3
string CAFSql = generateCAFSql(_CswNbtResources);
//add a / before the first trigger and split the file into an array of strings on space-only preceded / chars (breaking off potential PL/SQL blocks)
string[] SQLCommands = Regex.Split(CAFSql
.Replace(");\r\n\r\n\r\ncreate or replace trigger", ");\r\n\r\n\r\n/\r\ncreate or replace trigger")
.Replace("create or replace procedure", "\r\n/\r\ncreate or replace procedure")
.Replace("/*+", "*+"),//Strip slash out of Oracle Hints to prevent splitting the view query
@"\s+/");
foreach (string Command in SQLCommands)
{
//If we stripped a slash out of an Oracle Hint, put it back in
string SQLCommand = Command.Replace("*+", "/*+");
//if the string starts with any of these, it's a PL/SQL block and can be sent as-is
if (SQLCommand.Trim().StartsWith("begin") || SQLCommand.Trim().StartsWith("create or replace trigger") || SQLCommand.Trim().StartsWith("create or replace procedure"))
{
CAFConnection.execArbitraryPlatformNeutralSql(SQLCommand);
}
//otherwise, we need to further split out each command on ; chars
else
{
foreach (string SingleCommand in SQLCommand.Split(';'))
{
if (SingleCommand.Trim() != String.Empty)
{
CAFConnection.execArbitraryPlatformNeutralSql(SingleCommand.Trim());
}
}
}
}//foreach PL/SQL block in CAF.sql
//create the database link, after cleaning up an old one if it exists
_CswNbtResources.execArbitraryPlatformNeutralSql(@"
begin
execute immediate 'drop database link caflink';
exception when others then null;
end;
");
_CswNbtResources.execArbitraryPlatformNeutralSql("create database link caflink connect to " + CAFSchema + " identified by " + CAFPassword + " using '" + CAFDatabase + "'");
//Create custom NodeTypeProps from CAF Properties collections and set up bindings for them
CreateAllCAFProps(_CswNbtResources, SetupMode);
// Enable the CAFImport rule
CswTableUpdate TableUpdate = _CswNbtResources.makeCswTableUpdate("enableCafImportRule", "scheduledrules");
DataTable DataTable = TableUpdate.getTable("where rulename = '" + CswEnumNbtScheduleRuleNames.CAFImport + "'");
if (DataTable.Rows.Count > 0)
{
DataTable.Rows[0]["disabled"] = CswConvert.ToDbVal(false);
TableUpdate.update(DataTable);
}
//create a connection to the schedule service
WSHttpBinding Binding = new WSHttpBinding();
EndpointAddress Endpoint = new EndpointAddress(CswResources.SetupVbls["SchedServiceUri"]);
CswSchedSvcAdminEndPointClient SchedSvcRef = new CswSchedSvcAdminEndPointClient(Binding, Endpoint);
//fetch the CAFImport rule from ScheduleService
CswSchedSvcParams CswSchedSvcParams = new CswSchedSvcParams();
CswSchedSvcParams.CustomerId = _CswNbtResources.AccessId;
CswSchedSvcParams.RuleName = CswEnumNbtScheduleRuleNames.CAFImport;
CswSchedSvcReturn CAFRuleResponse;
try
{
CAFRuleResponse = SchedSvcRef.getRules(CswSchedSvcParams);
}
catch (Exception e)
{
throw new CswDniException(CswEnumErrorType.Error, "Could not connect to schedule service", e.Message);
}
//take the rule that was returned from the last request, set disabled to false, then send it back as an update
CswScheduleLogicDetail CAFImport = CAFRuleResponse.Data[0];
CAFImport.Disabled = false;
CswSchedSvcParams.LogicDetails = new Collection <CswScheduleLogicDetail>();
CswSchedSvcParams.LogicDetails.Add(CAFImport);
CswSchedSvcReturn svcReturn = SchedSvcRef.updateScheduledRules(CswSchedSvcParams);
if (false == svcReturn.Status.Success)
{
throw new CswDniException(svcReturn.Status.Errors[0].Message);
}
}//startCAFImport