Eclipse Community Forums: EMF "Technology" (Ecore Tools, EMFatic, etc) » [Teneo] Primary key and Index with same columns

Help

Home

Home » Modeling » EMF "Technology" (Ecore Tools, EMFatic, etc) » [Teneo] Primary key and Index with same columns

Show: Today's Messages :: Show Polls :: Message Navigator

[Teneo] Primary key and Index with same columns [message #90476]

Wed, 18 July 2007 16:45

Alain Picard

Messages: 266
Registered: July 2009

Senior Member

I have generated a schema for one of my model and I am ending up with
many case like this:

Keyname Type Field
PRIMARY PRIMARY element_e_id
FKA34FEB2FDDEAA974 INDEX element_e_id

which gets me a warning of:
PRIMARY and INDEX keys should not both be set for column `element_e_id`

Also, I saw a posting regarding the naming of indexes. Can this now be
specified and if so, what are the advantages (other than readability)?

Thanks.
Alain

Report message to a moderator

Re: [Teneo] Primary key and Index with same columns [message #90490 is a reply to message #90476]

Wed, 18 July 2007 17:24

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

Hi Alain,
I am not sure were this warning is coming from, I would also say that both a primary and normal
index on one column are not required. Can you show the relevant part of the mapping file?

There is an option in Teneo: ADD_INDEX_FOR_FOREIGN_KEY, which adds indexes for foreign keys but it
should not do this for primary keys and the default is false, so I don't think this is it.

There are two ways to set foreign key names:
- set the option SET_FOREIGN_KEY_NAME to true
- use a @ForeignKey(name="myfk") annotation on the ereference

The main advantage is readability, also apparently there were cases were hibernate created fk names
which were too long, with this option/annotation you can prevent/workaround that.

gr. Martin

Alain Picard wrote:
> I have generated a schema for one of my model and I am ending up with
> many case like this:
>
> Keyname Type Field
> PRIMARY PRIMARY element_e_id
> FKA34FEB2FDDEAA974 INDEX element_e_id
>
> which gets me a warning of:
> PRIMARY and INDEX keys should not both be set for column `element_e_id`
>
> Also, I saw a posting regarding the naming of indexes. Can this now be
> specified and if so, what are the advantages (other than readability)?
>
> Thanks.
> Alain

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org

Report message to a moderator

Re: [Teneo] Primary key and Index with same columns [message #90504 is a reply to message #90490]

Wed, 18 July 2007 18:05

Alain Picard

Messages: 266
Registered: July 2009

Senior Member

Martin,

BTW, the warning is given by our phpMyAdmin tool when browsing the tables.

As far as options are concerned there is only one:
teneo.mapping.inheritance=JOINED

The only places with relations to this table (Annotation) are the following:
<class name="KDM_MetaModel.core.impl.ElementImpl" entity-name="Element"
abstract="true" lazy="false" table="`element`">
<meta attribute="eclassName">Element</meta>
<meta attribute="epackage">http://kdm.omg.org/core</meta>
<id type="long" name="e_id" column="e_id"
access=" org.eclipse.emf.teneo.hibernate.mapping.identifier.Identifie rPropertyHandler ">
<meta attribute="syntheticId">true</meta>
<generator class="native"/>
</id>
<version name="e_version" column="e_version"
access=" org.eclipse.emf.teneo.hibernate.mapping.property.VersionProp ertyHandler ">
<meta attribute="syntheticVersion">true</meta>
</version>
<bag name="attribute" lazy="true" cascade="all,delete-orphan">
<key update="true">
<column name="`element_attribute_e_id`" not-null="false"
unique="false"/>
</key>
<one-to-many entity-name="Attribute"/>
</bag>
<bag name="annotation" lazy="true" cascade="all,delete-orphan">
<key update="true">
<column name="`element_annotation_e_id`" not-null="false"
unique="false"/>
</key>
<one-to-many entity-name="Annotation"/>
</bag>
</class>

and:
<joined-subclass name="KDM_MetaModel.kdm.impl.AnnotationImpl"
entity-name="Annotation" abstract="false" lazy="false" extends="Element"
table="`annotation`">
<meta attribute="eclassName">Annotation</meta>
<meta attribute="epackage">http://kdm.omg.org/kdm</meta>
<key>
<column name="`element_e_id`"/>
</key>
<property name="text" lazy="false" insert="true" update="true"
not-null="false" unique="false" type="java.lang.String">
<column not-null="false" unique="false" name="`text`"/>
</property>
</joined-subclass>

Given the size and nature of the model, I'll also send you a complete
version via email, in case that brings up any more clues.

Thanks
Alain

Report message to a moderator

Re: [Teneo] Primary key and Index with same columns [message #90520 is a reply to message #90504]

Wed, 18 July 2007 18:44

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

Afaics the element_e_id is the foreign key from the joined subclass table to the parent class table.
Because it is a foreign key it will probably automatically have an index (depends on the database)
and this field is also the primary key of the joined subclass table. This would explain the index
and pk warning.
Can you check the table definition to confirm this?

If this is the case then this seems logical, hibernate controls this so it is not something Teneo
can change (if even change is required in this case).

gr. Martin

Alain Picard wrote:
> Martin,
>
> BTW, the warning is given by our phpMyAdmin tool when browsing the tables.
>
> As far as options are concerned there is only one:
> teneo.mapping.inheritance=JOINED
>
>
> The only places with relations to this table (Annotation) are the
> following:
> <class name="KDM_MetaModel.core.impl.ElementImpl"
> entity-name="Element" abstract="true" lazy="false" table="`element`">
> <meta attribute="eclassName">Element</meta>
> <meta attribute="epackage">http://kdm.omg.org/core</meta>
> <id type="long" name="e_id" column="e_id"
> access=" org.eclipse.emf.teneo.hibernate.mapping.identifier.Identifie rPropertyHandler ">
>
> <meta attribute="syntheticId">true</meta>
> <generator class="native"/>
> </id>
> <version name="e_version" column="e_version"
> access=" org.eclipse.emf.teneo.hibernate.mapping.property.VersionProp ertyHandler ">
>
> <meta attribute="syntheticVersion">true</meta>
> </version>
> <bag name="attribute" lazy="true" cascade="all,delete-orphan">
> <key update="true">
> <column name="`element_attribute_e_id`" not-null="false"
> unique="false"/>
> </key>
> <one-to-many entity-name="Attribute"/>
> </bag>
> <bag name="annotation" lazy="true" cascade="all,delete-orphan">
> <key update="true">
> <column name="`element_annotation_e_id`"
> not-null="false" unique="false"/>
> </key>
> <one-to-many entity-name="Annotation"/>
> </bag>
> </class>
>
>
> and:
> <joined-subclass name="KDM_MetaModel.kdm.impl.AnnotationImpl"
> entity-name="Annotation" abstract="false" lazy="false" extends="Element"
> table="`annotation`">
> <meta attribute="eclassName">Annotation</meta>
> <meta attribute="epackage">http://kdm.omg.org/kdm</meta>
> <key>
> <column name="`element_e_id`"/>
> </key>
> <property name="text" lazy="false" insert="true" update="true"
> not-null="false" unique="false" type="java.lang.String">
> <column not-null="false" unique="false" name="`text`"/>
> </property>
> </joined-subclass>
>
>
>
> Given the size and nature of the model, I'll also send you a complete
> version via email, in case that brings up any more clues.
>
> Thanks
> Alain

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org

Report message to a moderator

Re: [Teneo] Primary key and Index with same columns [message #90535 is a reply to message #90520]

Wed, 18 July 2007 18:53

Alain Picard

Messages: 266
Registered: July 2009

Senior Member

Martin,

Here is the table definition:
CREATE TABLE `annotation` (
`element_e_id` bigint(20) NOT NULL,
`text` varchar(255) default NULL,
`element_annotation_e_id` bigint(20) default NULL,
`econtainer_class` varchar(255) default NULL,
`e_container` varchar(255) default NULL,
`e_container_featureid` int(11) default NULL,
PRIMARY KEY (`element_e_id`),
KEY `FKA34FEB2FDDEAA974` (`element_e_id`),
KEY `FKA34FEB2FE9E381DE` (`element_annotation_e_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `annotation`
ADD CONSTRAINT `FKA34FEB2FE9E381DE` FOREIGN KEY
(`element_annotation_e_id`) REFERENCES `element` (`e_id`),
ADD CONSTRAINT `FKA34FEB2FDDEAA974` FOREIGN KEY (`element_e_id`)
REFERENCES `element` (`e_id`);

BTW, In your opinion how well will a model like the one i sent you work
in practice. This creates 300 tables with a joined_table strategy and
will get to the point of containing 100's of thousands of records at least.

Regards
Alain

Report message to a moderator

Re: [Teneo] Primary key and Index with same columns [message #90569 is a reply to message #90535]

Wed, 18 July 2007 20:34

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

Alain,
Yes the table def shows what I mean, afaics this is not so much of a problem.

Regarding performance I can not say that much (it always depends), my own experience is with less
deep inheritance structures.
The joined-subclasses can be fetched in one select together with all the parent classes so that
should be manageable with larger data sets.

Btw, you can try to flatten the structure by declaring the Element type a mappedsuperclass but then
you can't really do polymorphic queries on Element level.

For me most of the time the amount of queries is a performance problem and not so much the
complexity of one query with many joins, for example show 500 rows in a view with a need to do one
query extra per row to get extra info out-of the db resulting in 500 extra queries/view.

gr. Martin

Alain Picard wrote:
> Martin,
>
> Here is the table definition:
> CREATE TABLE `annotation` (
> `element_e_id` bigint(20) NOT NULL,
> `text` varchar(255) default NULL,
> `element_annotation_e_id` bigint(20) default NULL,
> `econtainer_class` varchar(255) default NULL,
> `e_container` varchar(255) default NULL,
> `e_container_featureid` int(11) default NULL,
> PRIMARY KEY (`element_e_id`),
> KEY `FKA34FEB2FDDEAA974` (`element_e_id`),
> KEY `FKA34FEB2FE9E381DE` (`element_annotation_e_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> ALTER TABLE `annotation`
> ADD CONSTRAINT `FKA34FEB2FE9E381DE` FOREIGN KEY
> (`element_annotation_e_id`) REFERENCES `element` (`e_id`),
> ADD CONSTRAINT `FKA34FEB2FDDEAA974` FOREIGN KEY (`element_e_id`)
> REFERENCES `element` (`e_id`);
>
> BTW, In your opinion how well will a model like the one i sent you work
> in practice. This creates 300 tables with a joined_table strategy and
> will get to the point of containing 100's of thousands of records at least.
>
> Regards
> Alain

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org

Report message to a moderator