There's a design issue David and I have been discussion over the email
for about a day. I would like to hear what you think.

It concerns the dependencies between database objects and what is the
best way to represent them in Daversy.

Until now, the only database objects Daversy has supported were tables
and columns. As was planned I started working on adding support to
other objects, and I started out with indexes. The relationship
between tables and columns is quite clear : it's one of containment. A
table clearly contains the columns. The relationship between an index
and a table is not so clear-cut. On the one hand, an index cannot
exist without the table that it indexes. On the other, removing the
index doesn't change anything in the logic of the database design -
just makes it slower. Another argument that supports the second claim
is the fact that while columns, primary keys and foreign keys can be
stated within the CREATE TABLE statement, the indexes must be created
with separate SQL commands, leading me to believe that the designers
of SQL did not consider the index to be an integral part of a table.

This whole issue of dependency management relates to the way we
generate SQL scripts from a state. We need to create the objects in an
order that will not violate their dependencies. That is not a hard
task, what we need is just a topological sort implementation. The
problem is that some object types can be viewed as being contained
within other (as in the table-index case) and so the sorting might
need to be done on top-level objects only.

There's also another problem, the implementation of the database
providers. Ideally each database provider will contain very little
code so that Daversy could be adapted to many. The original concept
was to create an ICommandGenerator itnterface and each provider will
have classes that implement this interface for each database object
type they support. And so in the Oracle provider we have
CTableCommandGenerator, CColumnCommandGenerator and
CIndexCommandGenerator. The assumption about those objects is that
they generate complete SQL commands, such that CColumnCommandGenerator
can generate ALTER statements to add / remove / change columns in a
particular table. This assumption led to some duplicity between the
table command generator and the column command generator - both have
to handle column names and data types. That duplicity was naturally
refactored into a common method but it still feels a bit like a
cludge. If we consider the indexe to be a part of the column, it will
require CTableCommandGenerator to call CIndexCommandGenerator
directly. In itself that is not a bad thing, but that makes the
provider aware of the hierarchy of database objects and that logic
will have to be duplicated in all the providers. I don't feel very
comfortable with that. Looking at this from yet another angle, a
database provider is already more or less aware of that hierarchy in
its Extract method. It adds columns to tables and tables to the state,
which makes it aware of the fact that a table contains columns. It
could also add indexes to the table and be aware that tables contain
indexes.

I'd love to hear any thoughts you might have on the subject. If you're
not familiar with the design of Daversy, you can download
https://www.svn-hosting.com/trac/Daversy/attachment/wiki/Downloads/Daversy.chm
and read the documents in the Daversy Documentation folder.