Technical Approach Page

This page describes the work needed to facilitate using both Oracle andPostgreSQL within the Spacewalk application.

• Driver packaging
• spacewalk-setup
• db-control

Thecommon schema approach is the goal for managing the table/index DDL files. The following describes how this can be accomplishedwith a templating orschema generator tool that would be used atbuild time to create the DB specific .sql files from the _common' schema files. The team has _hand'' created the postgres .sql filesas both a fall back and an example of what the end schema needs to be for postgres. Should thecommon schema approach prove to be too complex or unmanageable insome way, we can always manage duplicate schema files.

• /schema/spacewalkContains DDL files.
• /schema/spacewalk/commonCommon schema.
• /schema/spacewalk/common/tablesAlthough we have created ''static'' postgres table DDL (.sql) files, the long term goal is to have most of the table .sql files would go in this directory. The ''common'' schema syntax would be a superset of both DDL grammars. At build time, the ''common'' schema would be used to generate the ''dynamic'' DDL files for each DB in the DB specific directories. Also contains the ''common'' tables.deps file.
• /schema/spacewalk/common/viewsAlthough we have created ''static'' postgres view DDL (.sql) files, the long term goal is to have most of the view .sql files would go in this directory. The ''common'' schema syntax would be a superset of both DDL grammars. At build time, the ''common'' schema would be used to generate the ''dynamic'' DDL files for each DB in the DB specific directories. In order for a view to be common, it must contain a query that works for all databases. Also contains the ''common'' views.deps file.
• /schema/spacewalk/common/dataAlthough we have created ''static'' postgres data loading (.sql) files (inserts), the long term goal is to have most of the data loading .sql files would go in this directory. The ''common'' schema syntax would be a superset of both DML grammars. At build time, the ''common'' schema would be used to generate the ''dynamic'' DDL files for each DB in the DB specific directories. Also contains the ''common'' data.deps file.
• /schema/spacewalk/oracleContains both: generated (dynamic) '''oracle''' specific schema and ''forked'' (static) schema files.
• /schema/spacewalk/oracle/classContains '''oracle''' specific user defined types (such as EVR_T) DDL files.
• /schema/spacewalk/oracle/typesContains '''oracle''' specific user defined types DDL files.
• /schema/spacewalk/oracle/tablesContains '''oracle''' specific ''(forked)'' table DDL files.
• /schema/spacewalk/oracle/tables/commonContains '''common''' table DDL and (.dep) files. Populated at build.
• /schema/spacewalk/oracle/viewsContains '''oracle''' specific ''(forked)'' view DDL files.
• /schema/spacewalk/oracle/views/commonContains '''common''' view DDL and (.dep) files. Populated at build.
• /schema/spacewalk/oracle/dataContains '''oracle''' specific ''(forked)'' data loading (insert) files.
• /schema/spacewalk/oracle/data/commonContains '''common''' data loading (insert) and (.dep) files. Populated at build.
• /schema/spacewalk/oracle/triggersContains '''oracle''' specific ''forked'' trigger creation files.
• /schema/spacewalk/oracle/procsContains '''oracle''' specific ''forked'' stored procedure creation files.
• /schema/spacewalk/oracle/packagesContains '''oracle''' specific ''forked'' package/package body creation files.
• /schema/spacewalk/oracle/synonymsContains '''oracle''' specific ''forked'' synonym creation files (although the plan is to get rid of these).
• /schema/spacewalk/postgresContains both: generated (dynamic) '''postgres''' specific schema and ''forked'' (static) schema files.
• /schema/spacewalk/postgres/classContains '''postgres''' specific user defined types (such as EVR_T) DDL files.
• /schema/spacewalk/postgres/typesContains '''postgres''' specific user defined types DDL files.
• /schema/spacewalk/postgres/tablesContains '''postgres''' specific ''(forked)'' table DDL files.
• /schema/spacewalk/postgres/tables/commonContains '''common''' table DDL and (.dep) files. Populated at build.
• /schema/spacewalk/postgres/viewsContains '''postgres''' specific ''(forked)'' view DDL files.
• /schema/spacewalk/postgres/views/commonContains '''common''' view DDL and (.dep) files. Populated at build.
• /schema/spacewalk/postgres/dataContains '''postgres''' specific ''(forked)'' data loading (insert) files.
• /schema/spacewalk/postgres/data/commonContains '''common''' ''(generated)'' data loading (insert) and (.dep) files. Populated at build.
• /schema/spacewalk/postgres/triggersContains '''postgres''' specific ''forked'' trigger creation files.
• /schema/spacewalk/postgres/procsContains '''postgres''' specific ''forked'' stored procedure creation files.
• /schema/spacewalk/postgres/packagesContains '''postgres''' specific ''forked'' package/package body creation files.

Example directories for upgrade listed to demonstrate concepts. Having common upgrade DDL that is used to generatethe DB specific upgrade DDL (like the table schema) may not be feasible but a worthy goal. Any de duplication will be good.

note: We need some work here.

• /schema/spacewalk/upgrade/spacewalk_04-05
• /schema/spacewalk/upgrade/spacewalk_04-05/common
• /schema/spacewalk/upgrade/spacewalk_04-05/oracle
• /schema/spacewalk/upgrade/spacewalk_04-05/postgres

• Driver setup
  • The/etc/rhn/rhn.conf file needs to have the proper driver specified (see spacewalk-setup).
• Handlingforked queries.
  • Queries are already resolved by name in the Java stack. So, the work to be done here is to provide forforked queries using a namespace prefix. Common queries would retain theplain name as the do today.Forked queries would have their name qualified by a prefixed matching the databasetype property defined in the/etc/rhn/rhn.conf file. An oracle specific query named "listAllChannels" when forked would be qualified as: "oracle:listAllChannels" and "postgres:listAllChannels". This qualification would be transparent to the caller. The underlying query resolution mechanism would first try to resolve as a common query then try the DB specific prefix. The hunting order can be reversed for performance if it turns out there are more forked queries then common (although this would be contrary to our goal).
    • Hibernate queries
    • Datasource queries

• Driver setup (DONE, rhnSQL checks for db backend to instantiate in rhn.conf)
• Handlingforked queries.
  • Queries arenot currently resolved by name in the Python stack. So, the work to be done here is to add this capability into rhnSQL and provide forforked queries using a namespace prefix. An approach could be to add a pythonpackage containing one or more pythonmodules containing a dictionary of named queries.Forked queries would have their name qualified by a prefixed matching the databasetype property defined in the/etc/rhn/rhn.conf file. An oracle specific query named "listAllChannels" when forked would be qualified as: "oracle:listAllChannels" and "postgres:listAllChannels". This qualification would be transparent to the caller. The underlying query resolution mechanism would first try to resolve as a common query then try the DB specific prefix. The hunting order can be reversed for performance if it turns out there are more forked queries then common (although this would be contrary to our goal). Unlike the Java stack,only theforked queries would be added to the dictionaries and the calling code modified to perform the lookup.

• Driver setup
  • Perl DB code must be modified to know to instantiate Oracle or PostgreSQL driver using settings in rhn.conf.
• Handlingforked queries.
  • Queries are alreadymostly resolved by name in the Perl stack. So, the work to be done here is to provide forforked queries using a namespace prefix. Common queries would retain theplain name as the do today.Forked queries would have their name qualified by a prefixed matching the databasetype property defined in the/etc/rhn/rhn.conf file. An oracle specific query named "listAllChannels" when forked would be qualified as: "oracle:listAllChannels" and "postgres:listAllChannels". This qualification would be transparent to the caller. The underlying query resolution mechanism would first try to resolve as a common query then try the DB specific prefix. The hunting order can be reversed for performance if it turns out there are more forked queries then common (although this would be contrary to our goal). Queries embedded in the code will have to be moved to using the lookup mechanism.

||ID||Description ||Component ||Status ||Assigned ||Notes |||| I1 || Modify spacewalk-setup || installer || started || dgoodwin || |||||| I2 || Createpostgres version of db-control || admin || || |||||| I3.1 || Schema generator || schema || prototyped || jortel || most grammar supported |||| I3.2 || Change postgres NUMERIC to BIGINT || schema || || jortel || |||||| I3.3 || Should resize NUMBER(38) to something reasonable ( like NUMBER(9) ) in oracle || schema || || jortel || need to run by community || nice to have |||| I4 || Add support for forked queries: hibernate || java || || jortel || |||||| I5 || Add support for forked queries: datasource || java || || jortel || |||||| I6 || Add support for named queries || python || || || |||||| I7 || Move embedded SQL to named queries || python || || || |||||| I8 || Add support for forked queries: datasource || perl || || shughes :) || |||||| I9 || Move embedded SQL to datasource || perl || || shughes :) || |||||| I10 || RPM Packaging || rpm || || || ||||