Forum OpenACS Q&A: define PL/SQL and DML in separate files

Posted by Andrew Piskorski on
This is a good example of why all PL/SQL and plpgsql code should be defined, with 'create or replace', in their own SQL files, separate from the 'create table', etc. DML statements. Then all you have to do is re-source all those stock SQL files into the database, and all your stored functions and procedures are nicely redefined.

In my own work with Oracle, way back when I adopted a naming convention of "*-ph.sql" and "*-pb.sql" to indicate "package header" and "package body" files, since most Oracle PL/SQL code should be in PL/SQL packages. PostgreSQL doesn't have plpgsql packages, but at least when I tweaked the OpenACS Static Pages package, I used the same "*-pb.sql" naming convention for Postgres, to line up with Oracle.

(Note that it is also often useful to have the Oracle PL/SQL package header and body in two separate files, but this is much less important than having the PL/SQL definitions separate from the DML.)

The same goes for views and triggers, incidentally - they should be separate from the DML so you can easily recreate them just by sourcing the file. The general principle here is that stuff which you can simply "create or replace" (stored procedure code, views, triggers) should always be in a separate file from stuff which you can only "create" (tables). This makes the logistics of maintenance and upgrades substantially easier, and there's no drawback. Any more detailed break out of stuff, like PL/SQL package bodies and headers in separate, is just icing on the cake.

Maybe we could come up with some OpenACS recomended standard for this sort of thing and move all the packages towards using it.

Posted by Malte Sussdorff on
Andrew, after trying to upgrade one of our sites last night, I'm all yours..