Currently data models are loaded from scripts intended to be loadable
by either sqlplus or psql, and also by our SQLLoader parser. This
means that the SQLLoader class needs to understand the basic syntax
and semantics of both sqlplus and psql. This results in some strange
constraints that must be observed in order to write correct sql files.
Both psql and sqlplus support many more features than the SQLLoader
class supports, so one must be careful to stick to the subset of
features that SQLLoader supports. Also the semantics of sqlplus and
psql have some significant differences, for example psql can't perform
relative includes, whereas sqlplus supports both relative and absolute
includes. But perhaps the most confusing thing is that if scripts are
rarely loaded by hand (e.g. the sql test data model) it is possible
for oracle syntax to slip into psql scripts and for psql syntax to
slip into oracle scripts resulting in files that are parsed correctly
by the SQLLoader class, but by neither sqlplus nor psql.
Also because psql only supports absolute includes the DDLWriter
produces scripts that use absolute includes. This is less than ideal
since the loading of the scripts by hand is brittle since you need to
first cd to the correct directory before sourcing the scripts in
question. Also if you wish to source two scripts that use absolute
includes relative to different working directories then you simply
can't source both of them from the same file since one set of includes
is guaranteed to break.
Probably the most sane way to fix this would be to abandon sqlplus and
psql formats for storing sql scripts and use a very simple format that
clearly delimits JDBC statements and only supports relative includes.
This would greatly simplify the SQLLoader code since right now its
most difficult job is understanding enough of postgres and oracle sql
syntax to determine how to split up a given file into jdbc statements.
Something that it doesn't allways get right. We could then provide a
simple command line version of the tool for loading sql scripts by
hand, as well as options for automatically producing sqlplus style and
psql style scripts from the database independent format.
If we did drop direct support for psql or sqlplus, then a CCM ->
psql/sqlplus syntax convertor would be an absolete must, because there
are DBAs who insist on loading the SQL directly themselves :-( The
only downside I can see of using a CCM format is that development may
be inconvenienced by the need to get a complete system compiling &
starting before you can load the scripts. This could be fairly easily
avoided, however, if the convertor program were separate from CCM
itself (ie maybe a standalone Java tool in ccm-tools RPM), so you
could use it with minimal compile dependencies.