John DeSoi wrote:
Russ,
On Jun 10, 2005, at 2:34 PM, Russ Brown wrote:
This certainly is a far more complex problem than I originally thought
it was. I'd like any solution to be able to work in any version
control system, and to be applicable to any database engine (my
employer is a MySQL house, while I personally prefer Postgres for my
personal stuff, so I'm in favour of it being completely portable).
Another important design criteria for me would be ease of use. This is
a version-control stage on top of the existing version control work
that a developer needs to do, and if it's a real hassle they aren't
going to bother to do it properly. It therefore needs to be able to do
things as automatically as possible, for example by connecting to a
database and generating the manifest file directly.
I think what this amounts to is basically doing the task as it has
traditionally been accomplished (and already suggested in the thread).
There is a baseline schema and any group of developers needs to share a
version controlled file to transform the schema into a new version. This
could involve schema and data modifications.
As you say, unless there is a huge win for developers no one will take
the time to learn some other specification method (i.e. some type of XML
manifest). I think the best approach is a tool that takes the baseline
schema, new schema, and current transform script and then generates the
SQL needed for the next revision. The developer would simply need to
review the generated code, adjust it (if necessary), and then commit it
as the next revision.
Ah, I don't think I explained clearly enough how I meant for the XML
manifest to be used. In my mind, I don't see that the developer should
need to manually edit or create the file at all, except perhaps for
resolving conflicts when doing a merge.
The idea I have is that the manifest is first created by feeding a raw
SQL schema into some program, which creates the manifest. The file is
then updated by either feeding another schema into the program along
with the generated manifest, or by having the program connect to the
database directly and getting at the information that way. Either way,
the manifest file gets updated by the program, not the developer.
The program is also used to compare between two different manifest files
(e.g. two revisions of the same file) and produces the SQL required to
make the migration.
This can only work if the file containing the database state is more
descriptive than a raw SQL schema dump is, as we've already identified
that there is no way of differentiating between a DROP/CREATE and an ALTER.
If developer interaction can be kept to a minimum, the system should
make it easier for DBAs to keep track of the changes that developers are
making in development, and make it more unlikely that changes will be
left out of rollouts.
--
Russ.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
