Forum OpenACS Q&A: Re: PG 7.2->7.3 upgrade gotcha?
As far as an OpenACS-wide file naming and organization convention, ok, here's one concrete proposal:
- Wherever possible and feasible, database objects must be defined with "create or replace" rather than "create". See below [TODO] for info on Oracle and PostgreSQL limitations.
- All procedural code that lives inside the database must be defined in its own files separate from any DDL ("create table", etc.) statements. These files must be named either "*-pb.sql" (for Oracle package bodies, functions, and procedures; for PostgreSQL functions and procedures) or "*-ph.sql" (for Oracle package headers).
- Oracle package headers may be included with the package bodies in the "*-pb.sql" file, but often should be in their own "*-ph.sql" file (developer's judgment). As PostgreSQL pl/pgsql does not support packages, there should be no Postgres "*-ph.sql" files.
- Triggers should be in their own file named "*-tr.sql".
- Views should be in their own file named "*-vw.sql".
- Stand-alone Oracle functions and procedures, not part of any PL/SQL package, may be in a "*-pb.sql" file, but usually should be in a "*-fn.sql" file instead.
- PostgreSQL functions designed to emulate or be compatible with Oracle package body functions should preferably be in a "*-pb.sql" file rather than a "*-fn.sql" file.
If anyone has counter-suggestions of comments, please chime in. :)
E.g., perhaps "*-fn.sql" should also be allowed in addition to "*-pb.sql" for Oracle and PostgreSQL functions and procedures. In that case, I think you'd add: