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()
示例#2
0
        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