January 16, 2003

xml, sql, java and eiffel (really need some advice..)

update
I am going for the suggestion provided by James (see comments).
Will also help the commons-sql to have an extra use scenario and if it needs fixing, well I can fix it myself :)


Ok here is the story. The system I am currently working on at my employer uses eiffel as a means to do database communications. The schema is defined in 3 xml files,
a physical schema (with oracle/sqlserver specifics), a conceptual schema (the stuff / naming we use in the java code) and a mapping schema to connect the 2 together. The mapping and the physical schema are db specific. The big problem is maintainance. Currently we are doing a lot sqlserver work and this way the oracle schema is out of date, so we end up in a lot of misery. I started a long time ago on a java system that would help us with this (using betwixt) and to add extra flexibility (eg altering tables, recreating indexes, etc). The problem is that the xml has to be in the old format, so it can be parsed via the eiffel backend into a lib file (with the orignal name of libFile.lib).
I really need some advice on tools I can use in java to replace the current way of maintaining the db schemas.

My requirements are :

- The conceptual schema,physical schema and mapping schema should always be maintain ones, use everywhere (it may be in another format, I don't care that much about that).
- I need to create those schemas that eiffel needs to create his libfile (use xml transformation, or betwixt)
- The new parser should be able to alter tables, and drop indexes so we don't have to mess around in the database while doing an upgrade (so killing the manual process..)

Are there any tools out there that can be suitable for the things I want ? (it's currently not an option to switch to OJB or something, so that is not an alternative).
The problem is that with figuring it out yourself that it takes weeks to go through al the solutions, so I though it's best to ask the people who most possibly knwow whats around there and what is best to use, based on experience.

Thanx for your help :)

Btw if you need to see the xml files we currently use, I can send them over..

Posted by mvdb at January 16, 2003 07:04 PM
Comments
You could try using commons-sql. http://jakarta.apache.org/commons/sandbox/sql/index.html It takes a logical XML schema document and can create physical database DDL for a variety of different databases. An added bonus is you get a simple DynaBean API for querying and inserting/updating/deleting data too. If you need to create anything else (reports, source code or whatever) you can then either use XSLT on the source XML or use Java code on the model beans. If you have some other master source of your schema, you can generate commons-sql's XML from it. So it should do the trick I think - though you might need to do some work yourself. Posted by: James Strachan on January 16, 2003 07:18 PM
Post a comment