Agreed. There is a "number" of db tools such as erWin that are
able to generate sql scripts for different target databases, but
then that means you have to maintain/use/design your table stuff
in erWin and not use the native database tools.
Well, to be fair, I'm using phpMyAdmin to model the schema and
generate several varieties of DDL, so it would just be switching
from that to something else (though the alternatives you mention all
cost $$$).
I seem to recall turning on the "ansi sql" options in ms-access
helped the consumption of general ddl commands for jet......
Hmm. I'll have to try that and see what happens.
I don't think there really an ideal solution here because it kind
of means either:
a) use some schema design tool like erWin, or office Viso
I'm already using a variety of this, i.e., phpMyAdmin.
b) stick to 100% ansi sql, but then some databases don't have
particular features
(eg: some systems need triggers to enable cascade updates for
example --
feature sets between databases are not common. When I started
playing with MySql the Referential integrity was a "add-on" by the
inno DB extensions.
No, that's not correct. If you use InnoDB table format, you get RI.
If you use the default MyISAM, you don't. The downside of using
InnoDB is no full-text indexing, which is very easy with MyISAM.
So, even the standards of setting up RI are not always consistent
and compatible between systems
Well, I've always called MySQL a toy database, though it's catching
up these days (version 5 even has triggers!).
c) Build a translator of some type. this is hard to do. Likely
better to have some type of "meta" data that defines the
structures such as a diagramming tool, and then that tool is able
to general ddl for the given platform with the differences taken
into account.
I'm trying to keep the schemas simple. I've concluded that any db
engine that implements CASCADE with triggers that aren't defined in
DDL with standard keywords in the CONSTRAINT definition will just
have to be defined as CASCADE RESTRICT (which is the default if you
don't specify any CASCADE statement). On the other hand, since
simple application templates will be developed to work with these,
the db-specific DDL to define those triggers is not going to be too
hard to write and include somewhere in the project.
But the idea at this point is to be as generic as possible without
sacrificing data integrity.
No question that we tend to lack decent scripting tools for JET
databases and Rogers code is start. The upzise to sql server is
another way to get scripts, but these approahces certainly do not
deal with the differences in feature sets that database engines
have....
Roger's code works backwards for me, as I have to have built the Jet
database before I can use it, whereas what I have is non-Jet DDL
that I want to use to create the Jet database.
Given that the plan is to support FileMaker, too, which has zero
support for executing SQL with its native data format (and thus no
DDL at all), I guess it won't be such a big deal to maintain an
Access MDB with the table structures, instead of distributing DDL
(as with the other databases). Theoretically, it's only the first
build of the database that is going to be difficult. Much of that
can actually be done quite easily in Access by opening a linked
table in design view and copying the structure and pasting it into
an Access table, and then altering the field types appropriately.
Revisions to the schema are not going to be as extensive as the
original schema, so that shouldn't be too hard.
So I don't think it's worth writing a DDL translator. At least, not
for my project.
