> To begin with. Can we live with the fact that we don't use a 100% correct
> presentation of a database, but one that will work 90% of the time?
I you mean various features then I think that we really need to support only
key and vital features. The rest is crap anyway ;-)
> If you
> need something more elaborate you can move to Osage or Castor or something
> else. Good enough will do? :-)
+1
> To go about better foreign keys support we need go through a number of
> steps. First we need a way to make MapBuilders and BasePeer aware of
> combined Keys. To create keys we could create a ForeingKeyMap class that
> holds this information.
>
> ForeignKeyMap fkm1 = tMap.createForeignKey("aForeingTable");
> fkm1.addColumn ("aLocalColumn","aForeignColumn");
> fkm1.addColumn ("anotherLocalColumn","anotherForeignColumn");
>
> To have multiple foreign keys we make multiple calls to
createForeignKey().
>
> For starters we could leave the isForeignKey() method in the ColumnMap and
> write an implementation that tries to find that column in the
ForeignKeyMaps
> for that table. This method should then be marked deprecated while we are
> working to remove it from all the code.
>
> After this we need a way to represent this in torque. I'll be willing to
> add the functionality in newtorque. The xml can look something like this
>
> <table name="table1">
> <column name="col1" type="int">
> <column name="col2" type="int">
> <column name="col3" type="int">
> <column name="col4" type="int">
> <foreign-key table="ForeignTable">
> <column local="col1" foreign="foreign1">
> <column local="col2" foreign="foreign2">
> </foreing-key>
> </table>
>
+1 to everything.
>
> The two most prominent parts where isForeignKey() is used is in BasePeer
and
> torque. Torque shouldn't be to big a problem to fix.
>
> In BasePeer.createQueryString() information about the foreign keys is used
> to create simple joins if necessary. This is doable. In
> BasePeer.doDelete() the isForeignKey() method is also called. It seems as
> if multiple deletes is done here, I'm not sure. Maybe somebody can shed
> some light on this method.
I've looked at those and could not grasp at once what they are used there
for. I would also appreciate someone explaining it. Anyway, whatever is
being done there ca be either reimplemented or removed (if this
functionality is not needed there. may be the case for delete..)
> When the above mentioned changes are implemented Foreign Keys should be
ok.
>
> Primary Keys represent a whole new challenge altogether.
My opinion is that primary and unique keys should be dealt with similar to
foreign keys. XML schema would look smth. like:
<table name="table1">
<column name="col1" type="int">
<column name="col2" type="int">
<column name="col3" type="int">
<column name="col4" type="int">
<primary-key>
<column name="col1">
<column name="col2">
</primary-key>
<unique-key>
<column name="col2">
<column name="col3">
</unique-key>
</table>
or (the way I would peronally prefer):
<table name="table1">
<column name="col1" type="int">
<column name="col2" type="int">
<column name="col3" type="int">
<column name="col4" type="int">
<unique-key primary="true">
<column name="col1">
<column name="col2">
</unique-key>
<unique-key>
<column name="col2">
<column name="col3">
</unique-key>
</table>
I prefer the last one because PK is essentially a UK "of special
significance".
The corresponding classes would be:
UniqueKey and
PrimaryKey extends UniqueKey
> The biggest issue
> lies with IDBroker and BaseObject. These two classes relies on the fact
> that a table has *one* column that acts as the id.
Actually if you look at BaseObject there are traces of intent to have a more
abstract id (historians, is that true?). I think reimplementing it in this
manner is doable, although there are certainly things to think about...
As far as IDBroker is concerned I think that it is not supposed to produce
any PK but just a unique integer ID. I do not think anyone would use it with
multiple-column PK (what sense would it make?). So my hope here is that
there is nothing to be changed here...
> I don't really have any ideas of how we would implement this. As far as
I'm
> concerned we just let users know that we require each table to have one
> column designated as the id column. This is good db design anyway.
That's correct for entity tables, but not for cross-ref tables. Cross-ref
tables do not have their own ID (you can create one, but that's a _bad_
design). The x-ref tables are the whole reason to support multiple-column
PKs.
We need suport for UKs because FK can only reference a UK or PK. I think
it's a good idea to validate this.
> If we go for the above changes should we maybe consider moving to
something
> like Peer version 2. Could be an excuse for a nice clean break to JDBC2 as
> well. Just a thought - any comments suggestions?
We probably do not need to change that much to make a rewrite reasonable.
That's just opinion.
fedor.
PS. another problem slightly related to this one. If you noticed, the tables
in the generated SQL files are created in the same order they are in XML.
Problem here is that very often there are FKs which reference tables created
later in the script and do not exist yet. So the script will not really
work. We need to address it at some point. We can either create tables in
the order such that they do not reference non-existent tables (problem here
is that there can easily be circular references), or create all tables first
and only create FKs after tables. I also want to get back to my proposal to
break XML schema into one file per table for the following reasons: 1)
change tracking is more comprehensive 2) it will not dictate order of table
creation and some smart algorithm can be used which would take into account
dependencies/changes 3) there will be one-to-one correspondence between XML
and java files.
dixi.
_______________________________________________________
Say Bye to Slow Internet!
http://www.home.com/xinbox/signup.html
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
