You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Jun Li <al...@gmail.com> on 2006/04/04 07:57:43 UTC
How does the xml sorted for foreign-key?
I found that foreign-keys (reference to a same table) doesn't generated in a
particular order.
For example, schema 1:
<table name="EmployeeTypeCodingFieldMap">
<column name="EmployeeTypeCodingFieldMapID" primaryKey="true"
required="true" type="NUMERIC" size="8" autoIncrement="true"/>
<column name="EmployeeTypeFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="CodingFieldFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="IsCompulsory" primaryKey="false" required="true"
type="CHAR" size="1" default="Y" autoIncrement="false"/>
<column name="SortOrder" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="ImpliedByCodingFieldFK" primaryKey="false"
required="false" type="NUMERIC" size="8" autoIncrement="false"/>
<foreign-key foreignTable="CodingField"
name="FK_EmpTypeCodngFldMap_CodngFld">
<reference local="CodingFieldFK" foreign="CodingFieldID"/>
</foreign-key>
<foreign-key foreignTable="CodingField"
name="FK_EmpTypeCFM_ImpByCodingField">
<reference local="ImpliedByCodingFieldFK" foreign="CodingFieldID"/>
</foreign-key>
<foreign-key foreignTable="EmployeeType"
name="FK_EmpTypeCodngFldMap_EmpType">
<reference local="EmployeeTypeFK" foreign="EmployeeTypeID"/>
</foreign-key>
<unique name="PK_EmployeeTypeGLCodeTypeMap">
<unique-column name="EmployeeTypeCodingFieldMapID"/>
</unique>
<index name="IX_EmpTpCodngFldMp_ImpCodngFld">
<index-column name="ImpliedByCodingFieldFK"/>
</index>
<index name="IX_EmpTypeCodingFldMap_EmpType">
<index-column name="EmployeeTypeFK"/>
</index>
<index name="IX_EmpTypeCodngFldMap_CodngFld">
<index-column name="CodingFieldFK"/>
</index>
</table>
schema 2:
<table name="EmployeeTypeCodingFieldMap">
<column name="EmployeeTypeCodingFieldMapID" primaryKey="true"
required="true" type="NUMERIC" size="8" autoIncrement="true"/>
<column name="EmployeeTypeFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="CodingFieldFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="IsCompulsory" primaryKey="false" required="true"
type="CHAR" size="1" default="Y" autoIncrement="false"/>
<column name="SortOrder" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="ImpliedByCodingFieldFK" primaryKey="false"
required="false" type="NUMERIC" size="8" autoIncrement="false"/>
<foreign-key foreignTable="CodingField"
name="FK_EmpTypeCFM_ImpByCodingField">
<reference local="ImpliedByCodingFieldFK" foreign="CodingFieldID"/>
</foreign-key>
<foreign-key foreignTable="CodingField"
name="FK_EmpTypeCodngFldMap_CodngFld">
<reference local="CodingFieldFK" foreign="CodingFieldID"/>
</foreign-key>
<foreign-key foreignTable="EmployeeType"
name="FK_EmpTypeCodngFldMap_EmpType">
<reference local="EmployeeTypeFK" foreign="EmployeeTypeID"/>
</foreign-key>
<unique name="PK_EmployeeTypeGLCodeTypeMap">
<unique-column name="EmployeeTypeCodingFieldMapID"/>
</unique>
<index name="IX_EmpTpCodngFldMp_ImpCodngFld">
<index-column name="ImpliedByCodingFieldFK"/>
</index>
<index name="IX_EmpTypeCodingFldMap_EmpType">
<index-column name="EmployeeTypeFK"/>
</index>
<index name="IX_EmpTypeCodngFldMap_CodngFld">
<index-column name="CodingFieldFK"/>
</index>
</table>
schema 1, 2 generated from different DB but the DBs have exactly the same
structure.
And this issue happened on several tables.
Any idea?
--------------
Cheers,
Jun
Re: How does the xml sorted for foreign-key?
Posted by Jun Li <al...@gmail.com>.
It's on SQL Server 2000 with latest JDBC driver.
On 4/4/06, Jun Li <al...@gmail.com> wrote:
>
> I found that foreign-keys (reference to a same table) doesn't generated
> in a particular order.
>
> For example, schema 1:
>
> <table name="EmployeeTypeCodingFieldMap">
> <column name="EmployeeTypeCodingFieldMapID" primaryKey="true"
> required="true" type="NUMERIC" size="8" autoIncrement="true"/>
> <column name="EmployeeTypeFK" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
> <column name="CodingFieldFK" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
> <column name="IsCompulsory" primaryKey="false" required="true"
> type="CHAR" size="1" default="Y" autoIncrement="false"/>
> <column name="SortOrder" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
> <column name="ImpliedByCodingFieldFK" primaryKey="false"
> required="false" type="NUMERIC" size="8" autoIncrement="false"/>
> <foreign-key foreignTable="CodingField"
> name="FK_EmpTypeCodngFldMap_CodngFld">
> <reference local="CodingFieldFK" foreign="CodingFieldID"/>
> </foreign-key>
> <foreign-key foreignTable="CodingField"
> name="FK_EmpTypeCFM_ImpByCodingField">
> <reference local="ImpliedByCodingFieldFK"
> foreign="CodingFieldID"/>
> </foreign-key>
> <foreign-key foreignTable="EmployeeType"
> name="FK_EmpTypeCodngFldMap_EmpType">
> <reference local="EmployeeTypeFK" foreign="EmployeeTypeID"/>
> </foreign-key>
> <unique name="PK_EmployeeTypeGLCodeTypeMap">
> <unique-column name="EmployeeTypeCodingFieldMapID"/>
> </unique>
> <index name="IX_EmpTpCodngFldMp_ImpCodngFld">
> <index-column name="ImpliedByCodingFieldFK"/>
> </index>
> <index name="IX_EmpTypeCodingFldMap_EmpType">
> <index-column name="EmployeeTypeFK"/>
> </index>
> <index name="IX_EmpTypeCodngFldMap_CodngFld">
> <index-column name="CodingFieldFK"/>
> </index>
> </table>
>
> schema 2:
>
> <table name="EmployeeTypeCodingFieldMap">
> <column name="EmployeeTypeCodingFieldMapID" primaryKey="true"
> required="true" type="NUMERIC" size="8" autoIncrement="true"/>
> <column name="EmployeeTypeFK" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
> <column name="CodingFieldFK" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
> <column name="IsCompulsory" primaryKey="false" required="true"
> type="CHAR" size="1" default="Y" autoIncrement="false"/>
> <column name="SortOrder" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
> <column name="ImpliedByCodingFieldFK" primaryKey="false"
> required="false" type="NUMERIC" size="8" autoIncrement="false"/>
> <foreign-key foreignTable="CodingField"
> name="FK_EmpTypeCFM_ImpByCodingField">
> <reference local="ImpliedByCodingFieldFK"
> foreign="CodingFieldID"/>
> </foreign-key>
> <foreign-key foreignTable="CodingField"
> name="FK_EmpTypeCodngFldMap_CodngFld">
> <reference local="CodingFieldFK" foreign="CodingFieldID"/>
> </foreign-key>
> <foreign-key foreignTable="EmployeeType"
> name="FK_EmpTypeCodngFldMap_EmpType">
> <reference local="EmployeeTypeFK" foreign="EmployeeTypeID"/>
> </foreign-key>
> <unique name="PK_EmployeeTypeGLCodeTypeMap">
> <unique-column name="EmployeeTypeCodingFieldMapID"/>
> </unique>
> <index name="IX_EmpTpCodngFldMp_ImpCodngFld">
> <index-column name="ImpliedByCodingFieldFK"/>
> </index>
> <index name="IX_EmpTypeCodingFldMap_EmpType">
> <index-column name="EmployeeTypeFK"/>
> </index>
> <index name="IX_EmpTypeCodngFldMap_CodngFld">
> <index-column name="CodingFieldFK"/>
> </index>
> </table>
>
> schema 1, 2 generated from different DB but the DBs have exactly the same
> structure.
>
> And this issue happened on several tables.
>
> Any idea?
>
>
>
> --------------
> Cheers,
> Jun
>
--
--------------
Cheers,
Jun
Re: How does the xml sorted for foreign-key?
Posted by Jun Li <al...@gmail.com>.
I found that this is a JDBC driver issue, I mean by using different driver
you might get different order of the elements in xml.
Shouldn't DdlUtils isolate this by sorting the elements in a particular
order?
On 4/5/06, Jun Li <al...@gmail.com> wrote:
>
> This is an issue because we are trying to do a version control on DB by
> keeping the xml schema in CVS. If the xml schema doesn't generate in an
> constant order, the comparing would be difficult.
>
> On 4/4/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> > On 4/4/06, Jun Li <al...@gmail.com> wrote:
>
>
> Why is this an issue for you ? In the database this should be no
> problem as the fks are independent from each other.
>
> Tom
>
>
>
>
> --
> --------------
> Cheers,
>
> Jun
>
--
--------------
Cheers,
Jun
Re: How does the xml sorted for foreign-key?
Posted by Jun Li <al...@gmail.com>.
This is an issue because we are trying to do a version control on DB by
keeping the xml schema in CVS. If the xml schema doesn't generate in an
constant order, the comparing would be difficult.
On 4/4/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> On 4/4/06, Jun Li <al...@gmail.com> wrote:
>
>
> Why is this an issue for you ? In the database this should be no
> problem as the fks are independent from each other.
>
> Tom
>
--
--------------
Cheers,
Jun
Re: How does the xml sorted for foreign-key?
Posted by Thomas Dudziak <to...@gmail.com>.
On 4/4/06, Jun Li <al...@gmail.com> wrote:
> I found that foreign-keys (reference to a same table) doesn't generated in a
> particular order.
<snip>
> And this issue happened on several tables.
>
> Any idea?
DdlUtils uses the order in which the JDBC driver reports them (i.e.
see method readForeignKeys in
http://svn.apache.org/viewcvs.cgi/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/JdbcModelReader.java?view=markup).
Why is this an issue for you ? In the database this should be no
problem as the fks are independent from each other.
Tom