Ah, important points about views, etc. in Postgres. I wasn't aware of
those limitations.
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".
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:
- 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.
