Home » Modeling » EMF » [CDO] How to resolve 'adjusted' table and column names
| | |
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521416 is a reply to message #521334] |
Wed, 17 March 2010 09:38   |
 Bjoern Sundin
Messages: 47
Registered: July 2009 |
Member |
|
|
Hi Stefan and thanks for your answer.
I recon it could be a workable solution to use annotations in the ecore to override table and column names. I have some question
marks about such a solution though:
1) I attempted to override the TimeStamp_C863 table name to force it to use the name TimeStamp by adding the following annotation:
<eClassifiers xsi:type="ecore:EClass" name="TimeStamp">
<eAnnotations source="http://www.eclipse.org/CDO/DBStore">
<details key="tableName" value="TimeStamp"/>
</eAnnotations>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="day" eType="ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="hours" eType="ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="minutes" eType="ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="seconds" eType="ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
</eClassifiers>
This fails and the table name anyway becomes TimeStamp_C863. If I manually create a table TimeStamp in the database there is no
problem. I could also verify that the annotation works, as I successfully named the table to e.g. TimeSquare in generating a new
database.
2) In order to be sure what names are used one would basically have to add an annotaion for each class/attibute/reference in the
ecore model. But one of the reasons using the framework is to be able to swiftly generate a database from an existing model. I do
not have a feeling that that is so swift anymore then. If one have no insight into the code there is also no possibility to find out
what the DBStore considers a reserved word.
3) The DBStore doesn't (yet) provide any CrossReferenceResolver for unresolved (not loaded) referencers. Afaik the only possible way
to resolve those referencers without having to load a whole huge database into memory is to use CDOQuery as I described in my
earlier post. Maybe there is another way, though, that I haven't detected yet?
4) If I use annotation to override the table name from e.g. TimeStamp to TimeSquare, the reference list tables still use the class
name e.g. MyEvent_TimeStamp_List (Yes, I didn't add any annotation yet to the EReference in MyEvent also named timestamp...).
Wouldn't it then be consistent to use MyEvent_TimeSquare_List also here? Or if the tabelname got changed to TimeStamp_C863 the
reference table would be MyEvent_TimeStamp_C863_List?
I noticed the SQL92_RESERVED_WORDS array in the DBAdapter class and that it is called from the H2Adapter and the HSQLDBAdapter over
the method getReservedWords(). If the name is contained in the SQL92_RESERVED_WORDS array, forceNamesWithID is set true and a suffix
is added to the name. The H2 documentation, however, defines just a subset of those words
(http://www.h2database.com/html/advanced.html#compatibility) and it is specified that a reserved word still can be used if
surrounded with double quotes. According to my experiences using Teneo this is the approach of the HibernateStore (Martin, you may
correct me if I'm wrong?).
Wouldn't it then be more consistent and also more transparent towards the user of the framework, if the DBStore, instead of silently
adding a suffix id to some table and column names, would handle surrounded quotes instead?
Cheers
Bjoern
"Stefan Winkler" <stefan.winkler-et@fernuni-hagen.de> schrieb im Newsbeitrag news:hnq9sh$ae8$1@build.eclipse.org...
> Hi Bj
|
|
|
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521427 is a reply to message #521416] |
Wed, 17 March 2010 10:12 |
|
This is a multi-part message in MIME format.
--------------020201050104020904020304
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8bit
Am 17.03.2010 15:21, schrieb Bjoern Sundin:
> Hi Stefan and thanks for your answer.
>
> I recon it could be a workable solution to use annotations in the ecore to override table and column names. I have some question
> marks about such a solution though:
>
> 1) I attempted to override the TimeStamp_C863 table name to force it to use the name TimeStamp by adding the following annotation:
> <eClassifiers xsi:type="ecore:EClass" name="TimeStamp">
> <eAnnotations source="http://www.eclipse.org/CDO/DBStore">
> <details key="tableName" value="TimeStamp"/>
> </eAnnotations>
> <eStructuralFeatures xsi:type="ecore:EAttribute" name="day" eType="ecore:EDataType
> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
> <eStructuralFeatures xsi:type="ecore:EAttribute" name="hours" eType="ecore:EDataType
> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
> <eStructuralFeatures xsi:type="ecore:EAttribute" name="minutes" eType="ecore:EDataType
> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
> <eStructuralFeatures xsi:type="ecore:EAttribute" name="seconds" eType="ecore:EDataType
> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
> </eClassifiers>
> This fails and the table name anyway becomes TimeStamp_C863. If I manually create a table TimeStamp in the database there is no problem.
That's odd, and probably a sign that the array of reserved words in the
IDBAdapter you're using (H2Adapter?) does not match with reality.
> I could also verify that the annotation works, as I successfully named the table to e.g. TimeSquare in generating a new
> database.
>
Yes, the IDBAdapter always has the last word in specifying the final
name. If it assumes a name is a reserved word it will mangle it.
> 2) In order to be sure what names are used one would basically have to add an annotaion for each class/attibute/reference in the
> ecore model. But one of the reasons using the framework is to be able to swiftly generate a database from an existing model. I do
> not have a feeling that that is so swift anymore then.
So, what are you suggesting? Note, that you must satisfy three criteria
at the same time:
1) Make it "swiftly"
2) Make it produce valid names for any concrete DB
3) Make it predictable
> If one have no insight into the code there is also no possibility to find out
> what the DBStore considers a reserved word.
>
That's why we produce open source software. You do have all insights
that are needed ;-)
The H2Adapter uses SQL92 keywords:
| *public *String[] getReservedWords()
{
*return *getSQL92ReservedWords();
}|
And those list "TIMESTAMP" as a reserved word. Can you point us to a
list of the correct H2 keywords?
> 3) The DBStore doesn't (yet) provide any CrossReferenceResolver for unresolved (not loaded) referencers. Afaik the only possible way
> to resolve those referencers without having to load a whole huge database into memory is to use CDOQuery as I described in my
> earlier post. Maybe there is another way, though, that I haven't detected yet?
>
No, but we have https://bugs.eclipse.org/bugs/show_bug.cgi?id=300149 for it.
> 4) If I use annotation to override the table name from e.g. TimeStamp to TimeSquare, the reference list tables still use the class
> name e.g. MyEvent_TimeStamp_List
Sounds odd. Can you file a bugzilla?
> (Yes, I didn't add any annotation yet to the EReference in MyEvent also named timestamp...).
>
I'm not sure if this is possible at all. Stefan, do you know that?
> Wouldn't it then be consistent to use MyEvent_TimeSquare_List also here? Or if the tabelname got changed to TimeStamp_C863 the
> reference table would be MyEvent_TimeStamp_C863_List?
>
I guess that one is harder, but not unreasonable ;-)
We're planning a major refactoring of the DBStore some time later this
year. A better approach to naming strategies should be on the list then...
> I noticed the SQL92_RESERVED_WORDS array in the DBAdapter class and that it is called from the H2Adapter and the HSQLDBAdapter over
> the method getReservedWords(). If the name is contained in the SQL92_RESERVED_WORDS array, forceNamesWithID is set true and a suffix
> is added to the name. The H2 documentation, however, defines just a subset of those words
> (http://www.h2database.com/html/advanced.html#compatibility) and it is specified that a reserved word still can be used if
> surrounded with double quotes. According to my experiences using Teneo this is the approach of the HibernateStore (Martin, you may
> correct me if I'm wrong?).
>
It strikes me that double quotes are an excellent idea.
> Wouldn't it then be more consistent and also more transparent towards the user of the framework, if the DBStore, instead of silently
> adding a suffix id to some table and column names, would handle surrounded quotes instead?
>
Yes.
Cheers
/Eike
----
http://thegordian.blogspot.com
http://twitter.com/eikestepper
> Cheers
> Bjoern
>
> "Stefan Winkler"<stefan.winkler-et@fernuni-hagen.de> schrieb im Newsbeitrag news:hnq9sh$ae8$1@build.eclipse.org...
>
>> Hi Bj
Cheers
/Eike
----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper
|
|
|
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521448 is a reply to message #521427] |
Wed, 17 March 2010 15:50 |
Bjoern Sundin
Messages: 47
Registered: July 2009 |
Member |
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_01D4_01CAC5F1.FC47BAF0
Content-Type: text/plain;
charset="ISO-8859-15"
Content-Transfer-Encoding: quoted-printable
I added some comments inline.
Cheers
Bjoern
"Eike Stepper" <stepper@esc-net.de> schrieb im Newsbeitrag =
news:hnqq5m$oua$2@build.eclipse.org...
Am 17.03.2010 15:21, schrieb Bjoern Sundin:=20
Hi Stefan and thanks for your answer.
I recon it could be a workable solution to use annotations in the ecore =
to override table and column names. I have some question=20
marks about such a solution though:
1) I attempted to override the TimeStamp_C863 table name to force it to =
use the name TimeStamp by adding the following annotation:
<eClassifiers xsi:type=3D"ecore:EClass" name=3D"TimeStamp">
<eAnnotations source=3D"http://www.eclipse.org/CDO/DBStore">
<details key=3D"tableName" value=3D"TimeStamp"/>
</eAnnotations>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"day" =
eType=3D"ecore:EDataType=20
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"hours" eType=3D"ecore:EDataType=20
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"minutes" eType=3D"ecore:EDataType=20
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"seconds" eType=3D"ecore:EDataType=20
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
</eClassifiers>
This fails and the table name anyway becomes TimeStamp_C863. If I =
manually create a table TimeStamp in the database there is no problem. =
That's odd, and probably a sign that the array of reserved words in the =
IDBAdapter you're using (H2Adapter?) does not match with reality.
Afaics the DBAdapter defines the same reserved words for both the =
H2Adapter and the HSQLDBAdapter. And it is true, I do try to =
deliberately use some DBAdapter reserved words as table/column names =
(which also works good using Derby and MySQL).
I could also verify that the annotation works, as I successfully named =
the table to e.g. TimeSquare in generating a new=20
database.
Yes, the IDBAdapter always has the last word in specifying the final =
name. If it assumes a name is a reserved word it will mangle it.
2) In order to be sure what names are used one would basically have to =
add an annotaion for each class/attibute/reference in the=20
ecore model. But one of the reasons using the framework is to be able to =
swiftly generate a database from an existing model. I do=20
not have a feeling that that is so swift anymore then. So, what are you =
suggesting? Note, that you must satisfy three criteria at the same time:
1) Make it "swiftly"
2) Make it produce valid names for any concrete DB
3) Make it predictable
Please, don't missunderstand me, I appreciate the framework very much =
and merely want to give point to a, in my opinion, more user friendly =
approach. You saw my proposal about double quotes at the end of my last =
post. Didn't intend to sound crude.
If one have no insight into the code there is also no possibility to =
find out=20
what the DBStore considers a reserved word.
That's why we produce open source software. You do have all insights =
that are needed ;-)
The H2Adapter uses SQL92 keywords:
public String[] getReservedWords()
{
return getSQL92ReservedWords();
} =20
And those list "TIMESTAMP" as a reserved word. Can you point us to a =
list of the correct H2 keywords?
http://www.h2database.com/html/advanced.html#compatibility
3) The DBStore doesn't (yet) provide any CrossReferenceResolver for =
unresolved (not loaded) referencers. Afaik the only possible way=20
to resolve those referencers without having to load a whole huge =
database into memory is to use CDOQuery as I described in my=20
earlier post. Maybe there is another way, though, that I haven't =
detected yet?
No, but we have https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D300149 =
for it.
I also mailed you some code a couple of days ago in order to share my =
attempt how to handle not loaded cross referencers =
(CrossReferenceUtil.java)
4) If I use annotation to override the table name from e.g. TimeStamp to =
TimeSquare, the reference list tables still use the class=20
name e.g. MyEvent_TimeStamp_List Sounds odd. Can you file a bugzilla?
I think I'll wait with that until I see where we are heading to in this =
discussion
(Yes, I didn't add any annotation yet to the EReference in MyEvent also =
named timestamp...).=20
I'm not sure if this is possible at all. Stefan, do you know that?
Wouldn't it then be consistent to use MyEvent_TimeSquare_List also here? =
Or if the tabelname got changed to TimeStamp_C863 the=20
reference table would be MyEvent_TimeStamp_C863_List?
I guess that one is harder, but not unreasonable ;-)
We're planning a major refactoring of the DBStore some time later this =
year. A better approach to naming strategies should be on the list =
then...
I see the naming as it is now (without adding suffixes) as a very good =
and straight forward approach! :-)
I noticed the SQL92_RESERVED_WORDS array in the DBAdapter class and that =
it is called from the H2Adapter and the HSQLDBAdapter over=20
the method getReservedWords(). If the name is contained in the =
SQL92_RESERVED_WORDS array, forceNamesWithID is set true and a suffix=20
is added to the name. The H2 documentation, however, defines just a =
subset of those words=20
(http://www.h2database.com/html/advanced.html#compatibility) and it is =
specified that a reserved word still can be used if=20
surrounded with double quotes. According to my experiences using Teneo =
this is the approach of the HibernateStore (Martin, you may=20
correct me if I'm wrong?).
It strikes me that double quotes are an excellent idea.
Whew! ;-)
Wouldn't it then be more consistent and also more transparent towards =
the user of the framework, if the DBStore, instead of silently=20
adding a suffix id to some table and column names, would handle =
surrounded quotes instead?
Yes.
Cheers
/Eike
----
http://thegordian.blogspot.com
http://twitter.com/eikestepper
Cheers
Bjoern
"Stefan Winkler" <stefan.winkler-et@fernuni-hagen.de> schrieb im =
Newsbeitrag news:hnq9sh$ae8$1@build.eclipse.org...
Hi Bj=F6rn,
using SQL-Queries is always problematic and should only be used if you
are aware of circumstances like the one you mentioned.
There is no way to query for the database table and feature names. The
designated way to go is to use table and column name annotations (see
DBAnnotationTest for details on the API) and make sure for yourself that
they don't collide.
Cheers,
Stefan
Am 17.03.2010 09:03, schrieb Bjoern Sundin:
Tailing CDO/Net4j 3.0 HEAD I encountered today a new problem with =
the database table names and columns in certain cases. In my
application I have an database cross reference resolver class that =
resolves all (loaded and not yet loaded) objects referencing
another object e.g. targeted for deletion. At startup the ePackages are =
registered and references/referencers are analysed and=20
the
relations cached. I can then submit an EObject to get all referencers =
resolved.
In order to query the database I generate an SQL statement and then use =
a CDOQuery to get the result. Until now I could relay on
that the table name and column name were same as the instance class name =
resp. the feature name. See example below:
EClass referencer;
EReference reference;
....
String referencerName =3D referencer.getInstanceClass().getSimpleName();
String referenceName =3D reference.getName();
String table =3D referencerName;
String column =3D referenceName;
if (reference.isMany()) {
table =3D referencerName + "_" + referenceName + "_list";
column =3D "CDO_VALUE";
}
// Create query
String id =3D String.valueOf(CDOIDUtil.getLong(cdoId));
String queryString =3D "SELECT * FROM " + table + " WHERE " + column + =
"=3D:fk";
CDOQuery query =3D view.createQuery("sql", queryString);
query.setParameter("fk", id);
Now I have seen that when using H2, HSQLDB or MySQL this does not always =
work anymore. For Derby I haven't been able to test due=20
to
bug 306000.
A. Example of an 'adjusted' table name:
I have a model class named TimeStamp. The ecore definition looks like =
this:
<eClassifiers xsi:type=3D"ecore:EClass" name=3D"TimeStamp">
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"day" =
eType=3D"ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"hours" =
eType=3D"ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"minutes" =
eType=3D"ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"seconds" =
eType=3D"ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
</eClassifiers>
The table name using MySQL is 'TIMESTAMP_C1190'
The table name using H2 or HSQLDB is TIMESTAMP_C863'
B. Example of an 'adjusted' column name:
I have a model class StepTelegramContent holding a EJavaObject with the =
feature name 'value'. The ecore definition:
<eClassifiers xsi:type=3D"ecore:EClass" name=3D"StepTelegramContent">
<eStructuralFeatures xsi:type=3D"ecore:EReference" name=3D"target" =
eType=3D"ecore:EClass=20
nodeprojectdata.ecore#//DatapointReference"
resolveProxies=3D"false"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"value" =
eType=3D"ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EJavaObject"/>
<eStructuralFeatures xsi:type=3D"ecore:EReference" =
name=3D"nameValuePairs" upperBound=3D"-1"
eType=3D"ecore:EClass nodeprojectdata.ecore#//NameValuePair" =
containment=3D"true"/>
</eClassifiers>
The column name of the 'value' feature using MySQL is 'VALUE'
The column name of the 'value' feature using H2 or HSQLDB is =
'VALUE_F924'
Another example of a column name is my model class Operation. Defined in =
ecore as follows:
<eClassifiers xsi:type=3D"ecore:EClass" name=3D"Operation">
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"version" =
eType=3D"ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"name" =
lowerBound=3D"1" eType=3D"ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EString"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"force" =
lowerBound=3D"1" eType=3D"ecore:EDataType
http://www.eclipse.org/emf/2002/Ecore#//EBoolean"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"alarmOriented" lowerBound=3D"1"
eType=3D"ecore:EDataType =
http://www.eclipse.org/emf/2002/Ecore#//EBoolean"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"usePrio" =
lowerBound=3D"1"
eType=3D"ecore:EDataType =
http://www.eclipse.org/emf/2002/Ecore#//EBoolean"/>
<eStructuralFeatures xsi:type=3D"ecore:EReference" name=3D"type" =
lowerBound=3D"1" eType=3D"ecore:EClass
.../../ch.bergauer.rcp.amset.system.attributes/model/systema ttributes.ecor=
e#//OperationType"
resolveProxies=3D"false"/>
<eStructuralFeatures xsi:type=3D"ecore:EReference" name=3D"result" =
eType=3D"#//DatapointReference"
resolveProxies=3D"false"/>
<eStructuralFeatures xsi:type=3D"ecore:EReference" name=3D"operands" =
upperBound=3D"-1"
eType=3D"#//Operand" containment=3D"true" =
resolveProxies=3D"false"/>
</eClassifiers>
The EReference named 'result' is referencing another EObject =
'DatapointReference'.
In the MySQL database the 'result' feature has column name 'RESULT'
In the HSQLDB or H2 databases the column name is 'RESULT_F11163'
In earlier versions of the CDO/Net4j framework this was not a problem.
In order to be able to do a direct query to the database using CDOQuery, =
I would need to be able to detect/resolve the=20
table/column
name that actually is in the database. Obvioulsy it is not anymore =
enought to be aware of the instance class name or the feature
name as the name might differ in the datastore, also depending on which =
database is being used.
Cheers
Bjoern
=20
------=_NextPart_000_01D4_01CAC5F1.FC47BAF0
Content-Type: text/html;
charset="ISO-8859-15"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE></TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3DISO-8859-15">
<META content=3D"MSHTML 6.00.5730.13" name=3DGENERATOR></HEAD>
<BODY text=3D#000000 bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I added some comments =
inline.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Cheers</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Bjoern</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Eike Stepper" <<A=20
href=3D"mailto:stepper@esc-net.de">stepper@esc-net.de</A>> schrieb =
im=20
Newsbeitrag <A=20
=
href=3D"news:hnqq5m$oua$2@build.eclipse.org">news:hnqq5m$oua$2@build.ecli=
pse.org</A>...</DIV>Am=20
17.03.2010 15:21, schrieb Bjoern Sundin:=20
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">Hi Stefan and thanks for your answer.
I recon it could be a workable solution to use annotations in the ecore =
to override table and column names. I have some question=20
marks about such a solution though:
1) I attempted to override the TimeStamp_C863 table name to force it to =
use the name TimeStamp by adding the following annotation:
<eClassifiers xsi:type=3D"ecore:EClass" name=3D"TimeStamp">
<eAnnotations source=3D<A class=3Dmoz-txt-link-rfc2396E =
href=3D"http://www.eclipse.org/CDO/DBStore">"http://www.eclipse.org/CDO/D=
BStore"</A>>
<details key=3D"tableName" value=3D"TimeStamp"/>
</eAnnotations>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"day" eType=3D"ecore:EDataType=20
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt">http://www.eclipse.=
org/emf/2002/Ecore#//EInt</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"hours" eType=3D"ecore:EDataType=20
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt">http://www.eclipse.=
org/emf/2002/Ecore#//EInt</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"minutes" eType=3D"ecore:EDataType=20
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt">http://www.eclipse.=
org/emf/2002/Ecore#//EInt</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"seconds" eType=3D"ecore:EDataType=20
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt">http://www.eclipse.=
org/emf/2002/Ecore#//EInt</A>"/>
</eClassifiers>
This fails and the table name anyway becomes TimeStamp_C863. If I =
manually create a table TimeStamp in the database there is no problem. =
</PRE></BLOCKQUOTE>
<DIV>That's odd, and probably a sign that the array of reserved words =
in the=20
IDBAdapter you're using (H2Adapter?) does not match with=20
reality.</DIV></BLOCKQUOTE><FONT face=3DArial size=3D2>Afaics the =
DBAdapter defines=20
the same reserved words for both the H2Adapter and the HSQLDBAdapter. =
And it is=20
true, I do try to deliberately use some DBAdapter reserved words as =
table/column=20
names (which also works good using Derby and MySQL).</FONT>
<DIV dir=3Dltr><FONT face=3DArial size=3D2></FONT> </DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">I could also verify that the annotation =
works, as I successfully named the table to e.g. TimeSquare in =
generating a new=20
database.
</PRE></BLOCKQUOTE>Yes, the IDBAdapter always has the last word in=20
specifying the final name. If it assumes a name is a reserved word it =
will=20
mangle it.<BR><BR>
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">2) In order to be sure what names are used =
one would basically have to add an annotaion for each =
class/attibute/reference in the=20
ecore model. But one of the reasons using the framework is to be able to =
swiftly generate a database from an existing model. I do=20
not have a feeling that that is so swift anymore then. =
</PRE></BLOCKQUOTE>
<DIV>So, what are you suggesting? Note, that you must satisfy three =
criteria=20
at the same time:<BR>1) Make it "swiftly"<BR>2) Make it produce valid =
names=20
for any concrete DB<BR>3) Make it predictable<BR></DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Please, don't missunderstand =
me, I=20
appreciate the framework very much and merely want to give point to a, =
in my=20
opinion, more user friendly approach. You saw my proposal =
about double=20
quotes at the end of my last post. Didn't intend to sound =
crude.</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">If one have no insight into the code there =
is also no possibility to find out=20
what the DBStore considers a reserved word.
</PRE></BLOCKQUOTE>That's why we produce open source software. You do =
have=20
all insights that are needed ;-)<BR><BR>The H2Adapter uses SQL92=20
keywords:<BR><BR>
<STYLE type=3Dtext/css>CODE {
FONT-SIZE: 10pt; MARGIN: 0px; FONT-FAMILY: Courier New, Courier
}
</STYLE>
<!-- =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D --><!-- =3D Java Sourcecode to HTML automatically =
converted code =3D --><!-- =3D Java2Html Converter 5.0 [2006-02-26] by =
Markus Gebhard markus@jave.de =3D --><!-- =3D Further =
information: http://www.java2html.de =3D -->
<DIV class=3Djava align=3Dleft>
<TABLE cellSpacing=3D0 cellPadding=3D3 bgColor=3D#ffffff border=3D0>
<TBODY>
<TR><!-- start source code -->
<TD vAlign=3Dtop noWrap align=3Dleft><CODE><FONT=20
color=3D#ffffff> </FONT><FONT=20
color=3D#7f0055><B>public </B></FONT><FONT=20
color=3D#000000>String</FONT><FONT =
color=3D#000000>[] </FONT><FONT=20
color=3D#000000>getReservedWords</FONT><FONT=20
color=3D#000000>()</FONT><BR><FONT =
color=3D#ffffff> </FONT><FONT=20
color=3D#000000>{</FONT><BR><FONT=20
color=3D#ffffff> </FONT ><FONT=20
color=3D#7f0055><B>return </B></FONT><FONT=20
color=3D#000000>getSQL92ReservedWords</FONT><FONT=20
color=3D#000000>()</FONT><FONT color=3D#000000>;</FONT><BR><FONT =
color=3D#ffffff> </FONT><FONT =
color=3D#000000>}</FONT></CODE> </TD><!-- end source code =
--></TR></TBODY></TABLE></DIV><!-- =3D END of automatically =
generated HTML code =3D --><!-- =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D -->
<DIV><BR>And those list "TIMESTAMP" as a reserved word. Can you point =
us to a=20
list of the correct H2 keywords?</DIV>
<DIV> </DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2></FONT><A =
class=3Dmoz-txt-link-freetext=20
href=3D"http://www.h2database.com/html/advanced.html#compatibility">http:=
//www.h2database.com/html/advanced.html#compatibility</A><BR ></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">3) The DBStore doesn't (yet) provide any =
CrossReferenceResolver for unresolved (not loaded) referencers. Afaik =
the only possible way=20
to resolve those referencers without having to load a whole huge =
database into memory is to use CDOQuery as I described in my=20
earlier post. Maybe there is another way, though, that I haven't =
detected yet?
</PRE></BLOCKQUOTE>
<DIV>No, but we have <A class=3Dmoz-txt-link-freetext=20
=
href=3D"https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D300149">https://b=
ugs.eclipse.org/bugs/show_bug.cgi?id=3D300149</A>=20
for it.</DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>I also mailed you some =
code a couple=20
of days ago in order to share my attempt how to handle not loaded =
cross=20
referencers (CrossReferenceUtil.java)</FONT><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">4) If I use annotation to override the =
table name from e.g. TimeStamp to TimeSquare, the reference list tables =
still use the class=20
name e.g. MyEvent_TimeStamp_List </PRE></BLOCKQUOTE>
<DIV>Sounds odd. Can you file a bugzilla?</DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>I think I'll wait with =
that until I=20
see where we are heading to in this discussion</FONT><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">(Yes, I didn't add any annotation yet to =
the EReference in MyEvent also named timestamp...).=20
</PRE></BLOCKQUOTE>I'm not sure if this is possible at all. Stefan, do =
you=20
know that?<BR><BR>
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">Wouldn't it then be consistent to use =
MyEvent_TimeSquare_List also here? Or if the tabelname got changed to =
TimeStamp_C863 the=20
reference table would be MyEvent_TimeStamp_C863_List?
</PRE></BLOCKQUOTE>
<DIV>I guess that one is harder, but not unreasonable ;-)<BR><BR>We're =
planning a major refactoring of the DBStore some time later this year. =
A=20
better approach to naming strategies should be on the list=20
then...</DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>I see the naming as it is now =
(without=20
adding suffixes) as a very good and straight forward approach!=20
:-)</FONT><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">I noticed the SQL92_RESERVED_WORDS array in =
the DBAdapter class and that it is called from the H2Adapter and the =
HSQLDBAdapter over=20
the method getReservedWords(). If the name is contained in the =
SQL92_RESERVED_WORDS array, forceNamesWithID is set true and a suffix=20
is added to the name. The H2 documentation, however, defines just a =
subset of those words=20
(<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.h2database.com/html/advanced.html#compatibility">http:=
//www.h2database.com/html/advanced.html#compatibility</A>) and it is =
specified that a reserved word still can be used if=20
surrounded with double quotes. According to my experiences using Teneo =
this is the approach of the HibernateStore (Martin, you may=20
correct me if I'm wrong?).
</PRE></BLOCKQUOTE>
<DIV>It strikes me that double quotes are an excellent=20
idea.<BR></DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Whew! ;-)</FONT><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">Wouldn't it then be more consistent and =
also more transparent towards the user of the framework, if the DBStore, =
instead of silently=20
adding a suffix id to some table and column names, would handle =
surrounded quotes instead?
</PRE></BLOCKQUOTE>Yes.<BR><BR>Cheers<BR>/Eike<BR><BR >----<BR><A=20
class=3Dmoz-txt-link-freetext=20
=
href=3D"http://thegordian.blogspot.com">http://thegordian.blogspot.com</A=
><BR><A=20
class=3Dmoz-txt-link-freetext=20
=
href=3D"http://twitter.com/eikestepper">http://twitter.com/eikestepper</A=
><BR><BR><BR>
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">Cheers
Bjoern
"Stefan Winkler" <A class=3Dmoz-txt-link-rfc2396E =
href=3D"mailto:stefan.winkler-et@fernuni-hagen.de"><stefan.winkler-et@=
fernuni-hagen.de></A> schrieb im Newsbeitrag <A =
class=3Dmoz-txt-link-freetext =
href=3D"news:hnq9sh$ae8$1@build.eclipse.org">news:hnq9sh$ae8$1@build.ecli=
pse.org</A>...
</PRE>
<BLOCKQUOTE type=3D"cite"><PRE wrap=3D"">Hi Bj=F6rn,
using SQL-Queries is always problematic and should only be used if you
are aware of circumstances like the one you mentioned.
There is no way to query for the database table and feature names. The
designated way to go is to use table and column name annotations (see
DBAnnotationTest for details on the API) and make sure for yourself that
they don't collide.
Cheers,
Stefan
Am 17.03.2010 09:03, schrieb Bjoern Sundin:
</PRE>
<BLOCKQUOTE type=3D"cite"><PRE wrap=3D"">Tailing CDO/Net4j 3.0 =
HEAD I encountered today a new problem with the database table names and =
columns in certain cases. In my
application I have an database cross reference resolver class that =
resolves all (loaded and not yet loaded) objects referencing
another object e.g. targeted for deletion. At startup the ePackages are =
registered and references/referencers are analysed and=20
the
relations cached. I can then submit an EObject to get all referencers =
resolved.
In order to query the database I generate an SQL statement and then use =
a CDOQuery to get the result. Until now I could relay on
that the table name and column name were same as the instance class name =
resp. the feature name. See example below:
EClass referencer;
EReference reference;
....
String referencerName =3D referencer.getInstanceClass().getSimpleName();
String referenceName =3D reference.getName();
String table =3D referencerName;
String column =3D referenceName;
if (reference.isMany()) {
table =3D referencerName + "_" + referenceName + "_list";
column =3D "CDO_VALUE";
}
// Create query
String id =3D String.valueOf(CDOIDUtil.getLong(cdoId));
String queryString =3D "SELECT * FROM " + table + " WHERE " + column + =
"=3D:fk";
CDOQuery query =3D view.createQuery("sql", queryString);
query.setParameter("fk", id);
Now I have seen that when using H2, HSQLDB or MySQL this does not always =
work anymore. For Derby I haven't been able to test due=20
to
bug 306000.
A. Example of an 'adjusted' table name:
I have a model class named TimeStamp. The ecore definition looks like =
this:
<eClassifiers xsi:type=3D"ecore:EClass" name=3D"TimeStamp">
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"day" =
eType=3D"ecore:EDataType
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt">http://www.eclipse.=
org/emf/2002/Ecore#//EInt</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"hours" =
eType=3D"ecore:EDataType
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt">http://www.eclipse.=
org/emf/2002/Ecore#//EInt</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"minutes" eType=3D"ecore:EDataType
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt">http://www.eclipse.=
org/emf/2002/Ecore#//EInt</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"seconds" eType=3D"ecore:EDataType
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt">http://www.eclipse.=
org/emf/2002/Ecore#//EInt</A>"/>
</eClassifiers>
The table name using MySQL is 'TIMESTAMP_C1190'
The table name using H2 or HSQLDB is TIMESTAMP_C863'
B. Example of an 'adjusted' column name:
I have a model class StepTelegramContent holding a EJavaObject with the =
feature name 'value'. The ecore definition:
<eClassifiers xsi:type=3D"ecore:EClass" =
name=3D"StepTelegramContent">
<eStructuralFeatures xsi:type=3D"ecore:EReference" =
name=3D"target" eType=3D"ecore:EClass=20
nodeprojectdata.ecore#//DatapointReference"
resolveProxies=3D"false"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"value" =
eType=3D"ecore:EDataType
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EJavaObject">http://www.e=
clipse.org/emf/2002/Ecore#//EJavaObject</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EReference" =
name=3D"nameValuePairs" upperBound=3D"-1"
eType=3D"ecore:EClass nodeprojectdata.ecore#//NameValuePair" =
containment=3D"true"/>
</eClassifiers>
The column name of the 'value' feature using MySQL is 'VALUE'
The column name of the 'value' feature using H2 or HSQLDB is =
'VALUE_F924'
Another example of a column name is my model class Operation. Defined in =
ecore as follows:
<eClassifiers xsi:type=3D"ecore:EClass" name=3D"Operation">
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"version" eType=3D"ecore:EDataType
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt">http://www.eclipse.=
org/emf/2002/Ecore#//EInt</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"name" =
lowerBound=3D"1" eType=3D"ecore:EDataType
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EString">http://www.eclip=
se.org/emf/2002/Ecore#//EString</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"force" =
lowerBound=3D"1" eType=3D"ecore:EDataType
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EBoolean">http://www.ecli=
pse.org/emf/2002/Ecore#//EBoolean</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"alarmOriented" lowerBound=3D"1"
eType=3D"ecore:EDataType <A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EBoolean">http://www.ecli=
pse.org/emf/2002/Ecore#//EBoolean</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"usePrio" lowerBound=3D"1"
eType=3D"ecore:EDataType <A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EBoolean">http://www.ecli=
pse.org/emf/2002/Ecore#//EBoolean</A>"/>
<eStructuralFeatures xsi:type=3D"ecore:EReference" name=3D"type" =
lowerBound=3D"1" eType=3D"ecore:EClass
.../../ch.bergauer.rcp.amset.system.attributes/model/systema ttributes.ecor=
e#//OperationType"
resolveProxies=3D"false"/>
<eStructuralFeatures xsi:type=3D"ecore:EReference" =
name=3D"result" eType=3D"#//DatapointReference"
resolveProxies=3D"false"/>
<eStructuralFeatures xsi:type=3D"ecore:EReference" =
name=3D"operands" upperBound=3D"-1"
eType=3D"#//Operand" containment=3D"true" =
resolveProxies=3D"false"/>
</eClassifiers>
The EReference named 'result' is referencing another EObject =
'DatapointReference'.
In the MySQL database the 'result' feature has column name 'RESULT'
In the HSQLDB or H2 databases the column name is 'RESULT_F11163'
In earlier versions of the CDO/Net4j framework this was not a problem.
In order to be able to do a direct query to the database using CDOQuery, =
I would need to be able to detect/resolve the=20
table/column
name that actually is in the database. Obvioulsy it is not anymore =
enought to be aware of the instance class name or the feature
name as the name might differ in the datastore, also depending on which =
database is being used.
Cheers
Bjoern
</PRE></BLOCKQUOTE></BLOCKQUOTE><PRE wrap=3D"">
</PRE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_01D4_01CAC5F1.FC47BAF0--
|
|
|
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521474 is a reply to message #521448] |
Wed, 17 March 2010 17:13 |
|
This is a multi-part message in MIME format.
--------------050201050405050708050103
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8bit
Am 17.03.2010 16:50, schrieb Bjoern Sundin:
> I added some comments inline.
> Cheers
> Bjoern
>
> "Eike Stepper" <stepper@esc-net.de <mailto:stepper@esc-net.de>>
> schrieb im Newsbeitrag news:hnqq5m$oua$2@build.eclipse.org...
> Am 17.03.2010 15:21, schrieb Bjoern Sundin:
>> Hi Stefan and thanks for your answer.
>>
>> I recon it could be a workable solution to use annotations in the ecore to override table and column names. I have some question
>> marks about such a solution though:
>>
>> 1) I attempted to override the TimeStamp_C863 table name to force it to use the name TimeStamp by adding the following annotation:
>> <eClassifiers xsi:type="ecore:EClass" name="TimeStamp">
>> <eAnnotations source="http://www.eclipse.org/CDO/DBStore">
>> <details key="tableName" value="TimeStamp"/>
>> </eAnnotations>
>> <eStructuralFeatures xsi:type="ecore:EAttribute" name="day" eType="ecore:EDataType
>> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
>> <eStructuralFeatures xsi:type="ecore:EAttribute" name="hours" eType="ecore:EDataType
>> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
>> <eStructuralFeatures xsi:type="ecore:EAttribute" name="minutes" eType="ecore:EDataType
>> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
>> <eStructuralFeatures xsi:type="ecore:EAttribute" name="seconds" eType="ecore:EDataType
>> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
>> </eClassifiers>
>> This fails and the table name anyway becomes TimeStamp_C863. If I manually create a table TimeStamp in the database there is no problem.
> That's odd, and probably a sign that the array of reserved words
> in the IDBAdapter you're using (H2Adapter?) does not match with
> reality.
>
> Afaics the DBAdapter defines the same reserved words for both the
> H2Adapter and the HSQLDBAdapter. And it is true, I do try to
> deliberately use some DBAdapter reserved words as table/column names
> (which also works good using Derby and MySQL).
Are you willing to contribute a patch to give H2Adapter its own, correct
set of reserved words?
>> I could also verify that the annotation works, as I successfully named the table to e.g. TimeSquare in generating a new
>> database.
>>
> Yes, the IDBAdapter always has the last word in specifying the
> final name. If it assumes a name is a reserved word it will mangle it.
>
>> 2) In order to be sure what names are used one would basically have to add an annotaion for each class/attibute/reference in the
>> ecore model. But one of the reasons using the framework is to be able to swiftly generate a database from an existing model. I do
>> not have a feeling that that is so swift anymore then.
> So, what are you suggesting? Note, that you must satisfy three
> criteria at the same time:
> 1) Make it "swiftly"
> 2) Make it produce valid names for any concrete DB
> 3) Make it predictable
>
> Please, don't missunderstand me, I appreciate the framework very much
> and merely want to give point to a, in my opinion, more user friendly
> approach. You saw my proposal about double quotes at the end of my
> last post. Didn't intend to sound crude.
I really didn't feel offended! It's just a crazy time with M6 and
EclipseCon preparation plus a deadline by my sponsor. I'm a bit short on
words these days and I was just trying to make you propose a good
solution. I know that there are some, but I hoped that you take the time
to define them :P
>> If one have no insight into the code there is also no possibility to find out
>> what the DBStore considers a reserved word.
>>
> That's why we produce open source software. You do have all
> insights that are needed ;-)
>
> The H2Adapter uses SQL92 keywords:
>
> | *public *String[] getReservedWords()
> {
> *return *getSQL92ReservedWords();
> }|
>
>
> And those list "TIMESTAMP" as a reserved word. Can you point us to
> a list of the correct H2 keywords?
>
> http://www.h2database.com/html/advanced.html#compatibility
>
>> 3) The DBStore doesn't (yet) provide any CrossReferenceResolver for unresolved (not loaded) referencers. Afaik the only possible way
>> to resolve those referencers without having to load a whole huge database into memory is to use CDOQuery as I described in my
>> earlier post. Maybe there is another way, though, that I haven't detected yet?
>>
> No, but we have
> https://bugs.eclipse.org/bugs/show_bug.cgi?id=300149 for it.
>
> I also mailed you some code a couple of days ago in order to share my
> attempt how to handle not loaded cross referencers
> (CrossReferenceUtil.java)
>
>> 4) If I use annotation to override the table name from e.g. TimeStamp to TimeSquare, the reference list tables still use the class
>> name e.g. MyEvent_TimeStamp_List
> Sounds odd. Can you file a bugzilla?
>
> I think I'll wait with that until I see where we are heading to in
> this discussion
That depends mainly on you, maybe on Stefan (but I know that he's busy,
too, these days and mostly reactive to severe bugs).
Cheers
/Eike
----
http://thegordian.blogspot.com
http://twitter.com/eikestepper
>> (Yes, I didn't add any annotation yet to the EReference in MyEvent also named timestamp...).
>>
> I'm not sure if this is possible at all. Stefan, do you know that?
>
>> Wouldn't it then be consistent to use MyEvent_TimeSquare_List also here? Or if the tabelname got changed to TimeStamp_C863 the
>> reference table would be MyEvent_TimeStamp_C863_List?
>>
> I guess that one is harder, but not unreasonable ;-)
>
> We're planning a major refactoring of the DBStore some time later
> this year. A better approach to naming strategies should be on the
> list then...
>
> I see the naming as it is now (without adding suffixes) as a very good
> and straight forward approach! :-)
>
>> I noticed the SQL92_RESERVED_WORDS array in the DBAdapter class and that it is called from the H2Adapter and the HSQLDBAdapter over
>> the method getReservedWords(). If the name is contained in the SQL92_RESERVED_WORDS array, forceNamesWithID is set true and a suffix
>> is added to the name. The H2 documentation, however, defines just a subset of those words
>> (http://www.h2database.com/html/advanced.html#compatibility) and it is specified that a reserved word still can be used if
>> surrounded with double quotes. According to my experiences using Teneo this is the approach of the HibernateStore (Martin, you may
>> correct me if I'm wrong?).
>>
> It strikes me that double quotes are an excellent idea.
>
> Whew! ;-)
>
>> Wouldn't it then be more consistent and also more transparent towards the user of the framework, if the DBStore, instead of silently
>> adding a suffix id to some table and column names, would handle surrounded quotes instead?
>>
> Yes.
>
> Cheers
> /Eike
>
> ----
> http://thegordian.blogspot.com
> http://twitter.com/eikestepper
>
>
>> Cheers
>> Bjoern
>>
>> "Stefan Winkler"<stefan.winkler-et@fernuni-hagen.de> schrieb im Newsbeitragnews:hnq9sh$ae8$1@build.eclipse.org...
>>
>>> Hi Bj
Cheers
/Eike
----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper
|
|
|
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521624 is a reply to message #521474] |
Thu, 18 March 2010 10:37 |
Bjoern Sundin
Messages: 47
Registered: July 2009 |
Member |
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_022E_01CAC68F.596896D0
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_022F_01CAC68F.596896D0"
------=_NextPart_001_022F_01CAC68F.596896D0
Content-Type: text/plain;
charset="ISO-8859-15"
Content-Transfer-Encoding: quoted-printable
"Eike Stepper" <stepper@esc-net.de> schrieb im Newsbeitrag =
news:hnr2jf$3bf$1@build.eclipse.org...
Am 17.03.2010 16:50, schrieb Bjoern Sundin:=20
I added some comments inline.
Cheers
Bjoern
"Eike Stepper" <stepper@esc-net.de> schrieb im Newsbeitrag =
news:hnqq5m$oua$2@build.eclipse.org...
Am 17.03.2010 15:21, schrieb Bjoern Sundin:=20
Hi Stefan and thanks for your answer.
I recon it could be a workable solution to use annotations in the ecore =
to override table and column names. I have some question=20
marks about such a solution though:
1) I attempted to override the TimeStamp_C863 table name to force it to =
use the name TimeStamp by adding the following annotation:
<eClassifiers xsi:type=3D"ecore:EClass" name=3D"TimeStamp">
<eAnnotations source=3D"http://www.eclipse.org/CDO/DBStore">
<details key=3D"tableName" value=3D"TimeStamp"/>
</eAnnotations>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" name=3D"day" =
eType=3D"ecore:EDataType=20
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"hours" eType=3D"ecore:EDataType=20
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"minutes" eType=3D"ecore:EDataType=20
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"seconds" eType=3D"ecore:EDataType=20
http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
</eClassifiers>
This fails and the table name anyway becomes TimeStamp_C863. If I =
manually create a table TimeStamp in the database there is no problem. =
That's odd, and probably a sign that the array of reserved words in the =
IDBAdapter you're using (H2Adapter?) does not match with reality.
Afaics the DBAdapter defines the same reserved words for both the =
H2Adapter and the HSQLDBAdapter. And it is true, I do try to =
deliberately use some DBAdapter reserved words as table/column names =
(which also works good using Derby and MySQL).
Are you willing to contribute a patch to give H2Adapter its own, =
correct set of reserved words?
Yes, I can do that. However, before I do that I would shortly want to =
discuss the concept of naming tables and columns. I think it might =
become a burden to maintain a hardcoded list of reserved words. Using =
another version of one of the the supported databases, some words might =
have been added or removed. A more generic solution would be handier =
(see patch.txt).=20
It It is easy to modify the getName(String name, String suffix, int =
maxLength) method in AbstractMappingStrategy.java to surround the name =
with a double quote.I could test this using H2 and HSQLDB and there are =
no problems using reserved words as table and/or column names. It is =
also possible to send of a query using CDOQuery without surrounding the =
table/column name with double quotes. So the double quotes do not need =
to be transparent to the user of the framework, which I think is good. =
This is also applicable for Derby, according to the documentation =
( http://db.apache.org/derby/docs/10.1/ref/rrefkeywords29722.h tml). I =
could not test it with Derby yet, though, being blocked on bug 306000. =
Also PostgreSQL recomment the double quote solution for reserved words =
( http://www.postgresql.org/docs/8.4/interactive/sql-keywords- appendix.htm=
l). Remains handling MySQL, according to the documentation it is a =
possible solution, howevre instead of double quotes one has to use a =
grave accent ( ` ). By letting each DBAdapter define it's own quotation =
character this could be an easy adjustment. See also attached quite =
inofficial patch. If you want, I can add an bug/enhancement request and =
submit the patch as suggestion?
I could also verify that the annotation works, as I successfully named =
the table to e.g. TimeSquare in generating a new=20
database.
Yes, the IDBAdapter always has the last word in specifying the final =
name. If it assumes a name is a reserved word it will mangle it.
2) In order to be sure what names are used one would basically have to =
add an annotaion for each class/attibute/reference in the=20
ecore model. But one of the reasons using the framework is to be able to =
swiftly generate a database from an existing model. I do=20
not have a feeling that that is so swift anymore then. So, what are you =
suggesting? Note, that you must satisfy three criteria at the same time:
1) Make it "swiftly"
2) Make it produce valid names for any concrete DB
3) Make it predictable
Please, don't missunderstand me, I appreciate the framework very =
much and merely want to give point to a, in my opinion, more user =
friendly approach. You saw my proposal about double quotes at the end of =
my last post. Didn't intend to sound crude.
I really didn't feel offended! It's just a crazy time with M6 and =
EclipseCon preparation plus a deadline by my sponsor. I'm a bit short on =
words these days and I was just trying to make you propose a good =
solution. I know that there are some, but I hoped that you take the time =
to define them :P
Yes, I'll try to contribute too, not only wail. ;-)=20
At the moment I am too quite busy. I should present a result but there =
are several blockers to be handled before my app is running stable =
again.
If one have no insight into the code there is also no possibility to =
find out=20
what the DBStore considers a reserved word.
That's why we produce open source software. You do have all insights =
that are needed ;-)
The H2Adapter uses SQL92 keywords:
public String[] getReservedWords()
{
return getSQL92ReservedWords();
} =20
And those list "TIMESTAMP" as a reserved word. Can you point us to =
a list of the correct H2 keywords?
http://www.h2database.com/html/advanced.html#compatibility
3) The DBStore doesn't (yet) provide any CrossReferenceResolver for =
unresolved (not loaded) referencers. Afaik the only possible way=20
to resolve those referencers without having to load a whole huge =
database into memory is to use CDOQuery as I described in my=20
earlier post. Maybe there is another way, though, that I haven't =
detected yet?
No, but we have https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D300149 =
for it.
I have now adjusted the CrossReferenceUtil.java to take eventual table =
or column name annotations into consideration too. This also works good =
in my application if I annotate all critical class and feature names. I =
think I attach the class to bug 300149 as an initial suggestion.
I also mailed you some code a couple of days ago in order to share =
my attempt how to handle not loaded cross referencers =
(CrossReferenceUtil.java)
4) If I use annotation to override the table name from e.g. TimeStamp to =
TimeSquare, the reference list tables still use the class=20
name e.g. MyEvent_TimeStamp_List Sounds odd. Can you file a bugzilla?
I think I'll wait with that until I see where we are heading to in =
this discussion
That depends mainly on you, maybe on Stefan (but I know that he's =
busy, too, these days and mostly reactive to severe bugs).
Well, I'm not sure if it is a bug. I might have been a bit in a hurry =
testing this. So, until I do encounter that this is an actual problem, =
I'd wait filing a bug.=20
Cheers
Bjoern
------=_NextPart_001_022F_01CAC68F.596896D0
Content-Type: text/html;
charset="ISO-8859-15"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE></TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3DISO-8859-15">
<META content=3D"MSHTML 6.00.5730.13" name=3DGENERATOR></HEAD>
<BODY text=3D#000000 bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Eike Stepper" <<A=20
href=3D"mailto:stepper@esc-net.de">stepper@esc-net.de</A>> schrieb =
im=20
Newsbeitrag <A=20
=
href=3D"news:hnr2jf$3bf$1@build.eclipse.org">news:hnr2jf$3bf$1@build.ecli=
pse.org</A>...</DIV>Am=20
17.03.2010 16:50, schrieb Bjoern Sundin:=20
<BLOCKQUOTE cite=3Dmid:hnqtol$iig$1@build.eclipse.org type=3D"cite">
<META content=3D"MSHTML 6.00.5730.13" name=3DGENERATOR>
<DIV><FONT face=3DArial size=3D2>I added some comments =
inline.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Cheers</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Bjoern</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Eike Stepper" <<A href=3D"mailto:stepper@esc-net.de"=20
moz-do-not-send=3D"true">stepper@esc-net.de</A>> schrieb im =
Newsbeitrag=20
<A href=3D"news:hnqq5m$oua$2@build.eclipse.org"=20
=
moz-do-not-send=3D"true">news:hnqq5m$oua$2@build.eclipse.org</A>...</DIV>=
Am=20
17.03.2010 15:21, schrieb Bjoern Sundin:=20
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">Hi Stefan and thanks for your answer.
I recon it could be a workable solution to use annotations in the ecore =
to override table and column names. I have some question=20
marks about such a solution though:
1) I attempted to override the TimeStamp_C863 table name to force it to =
use the name TimeStamp by adding the following annotation:
<eClassifiers xsi:type=3D"ecore:EClass" name=3D"TimeStamp">
<eAnnotations source=3D<A class=3Dmoz-txt-link-rfc2396E =
href=3D"http://www.eclipse.org/CDO/DBStore" =
moz-do-not-send=3D"true">"http://www.eclipse.org/CDO/DBStore"</A>>
<details key=3D"tableName" value=3D"TimeStamp"/>
</eAnnotations>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"day" eType=3D"ecore:EDataType=20
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt" =
moz-do-not-send=3D"true">http://www.eclipse.org/emf/2002/Ecore#//EInt</A>=
"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"hours" eType=3D"ecore:EDataType=20
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt" =
moz-do-not-send=3D"true">http://www.eclipse.org/emf/2002/Ecore#//EInt</A>=
"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"minutes" eType=3D"ecore:EDataType=20
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt" =
moz-do-not-send=3D"true">http://www.eclipse.org/emf/2002/Ecore#//EInt</A>=
"/>
<eStructuralFeatures xsi:type=3D"ecore:EAttribute" =
name=3D"seconds" eType=3D"ecore:EDataType=20
<A class=3Dmoz-txt-link-freetext =
href=3D"http://www.eclipse.org/emf/2002/Ecore#//EInt" =
moz-do-not-send=3D"true">http://www.eclipse.org/emf/2002/Ecore#//EInt</A>=
"/>
</eClassifiers>
This fails and the table name anyway becomes TimeStamp_C863. If I =
manually create a table TimeStamp in the database there is no problem. =
</PRE></BLOCKQUOTE>
<DIV>That's odd, and probably a sign that the array of reserved =
words in=20
the IDBAdapter you're using (H2Adapter?) does not match with=20
reality.</DIV></BLOCKQUOTE><FONT face=3DArial size=3D2>Afaics the =
DBAdapter=20
defines the same reserved words for both the H2Adapter and the=20
HSQLDBAdapter. And it is true, I do try to deliberately use some =
DBAdapter=20
reserved words as table/column names (which also works =
good using Derby=20
and MySQL).</FONT></BLOCKQUOTE>Are you willing to contribute a patch =
to give=20
H2Adapter its own, correct set of reserved words?<BR><BR></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Yes, I can do that. However, =
before I do=20
that I would shortly want to discuss the concept of naming =
tables and=20
columns. I think it might become a burden to maintain a =
hardcoded=20
list of reserved words. Using another version of one of the the=20
supported databases, some words might have been added or=20
removed. A more generic solution would be handier (see=20
patch.txt). </FONT></DIV>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>It It is easy to modify the =
getName(String=20
name, String suffix, int maxLength) method in =
AbstractMappingStrategy.java=20
to surround the name with a double quote.I could test this using H2 =
and=20
HSQLDB and there are no problems using reserved words as table and/or =
column=20
names. It is also possible to send of a query using CDOQuery without =
surrounding=20
the table/column name with double quotes. So the double quotes do =
not need=20
to be transparent to the user of the framework, which I think is =
good. This=20
is also applicable for Derby, according to the documentation (<A=20
href=3D" http://db.apache.org/derby/docs/10.1/ref/rrefkeywords29722.h tml">=
http://db.apache.org/derby/docs/10.1/ref/rrefkeywords29722.h tml</A>).=20
I could not test it with Derby yet, though, being blocked =
on bug=20
306000. Also PostgreSQL recomment the double quote solution for =
reserved=20
words (<A=20
href=3D" http://www.postgresql.org/docs/8.4/interactive/sql-keywords- appen=
dix.html"> http://www.postgresql.org/docs/8.4/interactive/sql-keywords- app=
endix.html</A>).=20
Remains handling MySQL, according to the documentation it is a =
possible=20
solution, howevre instead of double quotes one has to use a grave accent =
( `=20
). By letting each DBAdapter define it's own quotation character =
this could=20
be an easy adjustment. See also attached quite inofficial patch. If =
you=20
want, I can add an bug/enhancement request and submit the patch as=20
suggestion?</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqtol$iig$1@build.eclipse.org type=3D"cite">
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">I could also verify that the annotation =
works, as I successfully named the table to e.g. TimeSquare in =
generating a new=20
database.
</PRE></BLOCKQUOTE>Yes, the IDBAdapter always has the last word in=20
specifying the final name. If it assumes a name is a reserved word =
it will=20
mangle it.<BR><BR>
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">2) In order to be sure what names are used =
one would basically have to add an annotaion for each =
class/attibute/reference in the=20
ecore model. But one of the reasons using the framework is to be able to =
swiftly generate a database from an existing model. I do=20
not have a feeling that that is so swift anymore then. =
</PRE></BLOCKQUOTE>
<DIV>So, what are you suggesting? Note, that you must satisfy =
three=20
criteria at the same time:<BR>1) Make it "swiftly"<BR>2) Make it =
produce=20
valid names for any concrete DB<BR>3) Make it=20
predictable<BR></DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Please, don't =
missunderstand me, I=20
appreciate the framework very much and merely want to give point to =
a, in my=20
opinion, more user friendly approach. You saw my proposal =
about=20
double quotes at the end of my last post. Didn't intend to sound=20
crude.</FONT></DIV></BLOCKQUOTE>
<DIV>I really didn't feel offended! It's just a crazy time with M6 and =
EclipseCon preparation plus a deadline by my sponsor. I'm a bit short =
on words=20
these days and I was just trying to make you propose a good solution. =
I know=20
that there are some, but I hoped that you take the time to define them =
:P</DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Yes, I'll try to contribute =
too, not only=20
wail. ;-) </FONT></DIV>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>At the moment I am too quite =
busy. I should=20
present a result but there are several blockers to be handled before my =
app is=20
running stable again.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqtol$iig$1@build.eclipse.org type=3D"cite">
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">If one have no insight into the code there =
is also no possibility to find out=20
what the DBStore considers a reserved word.
</PRE></BLOCKQUOTE>That's why we produce open source software. You do=20
have all insights that are needed ;-)<BR><BR>The H2Adapter uses =
SQL92=20
keywords:<BR><BR>
<STYLE type=3Dtext/css>CODE {
FONT-SIZE: 10pt; MARGIN: 0px; FONT-FAMILY: Courier New, Courier
}
</STYLE>
<!-- =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D --><!-- =3D Java Sourcecode to HTML automatically =
converted code =3D --><!-- =3D Java2Html Converter 5.0 [2006-02-26] by =
Markus Gebhard markus@jave.de =3D --><!-- =3D Further =
information: http://www.java2html.de =3D -->
<DIV class=3Djava align=3Dleft>
<TABLE cellSpacing=3D0 cellPadding=3D3 bgColor=3D#ffffff =
border=3D0>
<TBODY>
<TR><!-- start source code -->
<TD vAlign=3Dtop noWrap align=3Dleft><CODE><FONT=20
color=3D#ffffff> </FONT><FONT=20
color=3D#7f0055><B>public </B></FONT><FONT=20
color=3D#000000>String</FONT><FONT =
color=3D#000000>[] </FONT><FONT=20
color=3D#000000>getReservedWords</FONT><FONT=20
color=3D#000000>()</FONT><BR><FONT=20
color=3D#ffffff> </FONT><FONT=20
color=3D#000000>{</FONT><BR><FONT=20
color=3D#ffffff> </FONT ><FONT=20
color=3D#7f0055><B>return </B></FONT><FONT=20
color=3D#000000>getSQL92ReservedWords</FONT><FONT=20
color=3D#000000>()</FONT><FONT =
color=3D#000000>;</FONT><BR><FONT=20
color=3D#ffffff> </FONT><FONT =
color=3D#000000>}</FONT></CODE>=20
</TD><!-- end source code --></TR></TBODY></TABLE></DIV><!-- =
=3D END of automatically generated HTML code =3D --><!-- =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D -->
<DIV><BR>And those list "TIMESTAMP" as a reserved word. Can you =
point us=20
to a list of the correct H2 keywords?</DIV>
<DIV> </DIV></BLOCKQUOTE>
<DIV dir=3Dltr><A class=3Dmoz-txt-link-freetext=20
href=3D"http://www.h2database.com/html/advanced.html#compatibility"=20
=
moz-do-not-send=3D"true">http://www.h2database.com/html/advanced.html#com=
patibility</A><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">3) The DBStore doesn't (yet) provide any =
CrossReferenceResolver for unresolved (not loaded) referencers. Afaik =
the only possible way=20
to resolve those referencers without having to load a whole huge =
database into memory is to use CDOQuery as I described in my=20
earlier post. Maybe there is another way, though, that I haven't =
detected yet?
</PRE></BLOCKQUOTE>
<DIV>No, but we have <A class=3Dmoz-txt-link-freetext=20
href=3D"https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D300149"=20
=
moz-do-not-send=3D"true">https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D=
300149</A>=20
for it.</DIV>
<DIV> </DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE >
<DIV dir=3Dltr><FONT face=3DArial size=3D2>I have now adjusted the=20
CrossReferenceUtil.java to take eventual table or column name =
annotations into=20
consideration too. This also works good in my application if I annotate =
all=20
critical class and feature names. I think I attach the class to bug =
300149 as an=20
initial suggestion.</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqtol$iig$1@build.eclipse.org type=3D"cite">
<DIV dir=3Dltr><FONT face=3DArial size=3D2>I also mailed you =
some code a=20
couple of days ago in order to share my attempt how to handle =
not=20
loaded cross referencers (CrossReferenceUtil.java)</FONT><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">4) If I use annotation to override the =
table name from e.g. TimeStamp to TimeSquare, the reference list tables =
still use the class=20
name e.g. MyEvent_TimeStamp_List </PRE></BLOCKQUOTE>
<DIV>Sounds odd. Can you file a bugzilla?</DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>I think I'll wait =
with that until=20
I see where we are heading to in this=20
discussion</FONT><BR></DIV></BLOCKQUOTE>
<DIV>That depends mainly on you, maybe on Stefan (but I know that he's =
busy,=20
too, these days and mostly reactive to severe =
bugs).<BR></DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Well, I'm not sure if it is a =
bug. I might=20
have been a bit in a hurry testing this. So, until I do encounter that =
this is=20
an actual problem, I'd wait filing a bug. </FONT></DIV>
<DIV dir=3Dltr><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Cheers</FONT></DIV>
<DIV dir=3Dltr><FONT face=3DArial =
size=3D2>Bjoern</FONT></DIV></BODY></HTML>
------=_NextPart_001_022F_01CAC68F.596896D0--
------=_NextPart_000_022E_01CAC68F.596896D0
Content-Type: text/plain;
name="patch.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="patch.txt"
### Eclipse Workspace Patch 1.0
#P org.eclipse.emf.cdo.server.db
Index: =
src/org/eclipse/emf/cdo/server/internal/db/mapping/AbstractM appingStrateg=
y.java
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
RCS file: =
/cvsroot/modeling/org.eclipse.emf/org.eclipse.emf.cdo/plugin s/org.eclipse=
..emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/ db/mapping/Abs=
tractMappingStrategy.java,v
retrieving revision 1.24
diff -u -r1.24 AbstractMappingStrategy.java
--- =
src/org/eclipse/emf/cdo/server/internal/db/mapping/AbstractM appingStrateg=
y.java 14 Mar 2010 19:34:11 -0000 1.24
+++ =
src/org/eclipse/emf/cdo/server/internal/db/mapping/AbstractM appingStrateg=
y.java 18 Mar 2010 10:20:26 -0000
@@ -335,10 +335,6 @@
private String getName(String name, String suffix, int maxLength)
{
boolean forceNamesWithID =3D isForceNamesWithID();
- if (store.getDBAdapter().isReservedWord(name))
- {
- forceNamesWithID =3D true;
- }
=20
if (name.length() > maxLength || forceNamesWithID)
{
@@ -347,7 +343,11 @@
name =3D name.substring(0, length) + suffix;
}
=20
- return name;
+ char quote =3D store.getDBAdapter().getQuotationCharacter();
+
+ name =3D quote + name + quote;
+
+ return name.toUpperCase();
}
=20
// -- factories for mapping of classes, values, lists =
------------------
#P org.eclipse.net4j.db
Index: src/org/eclipse/net4j/db/IDBAdapter.java
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
RCS file: =
/cvsroot/modeling/org.eclipse.emf/org.eclipse.emf.net4j/plug ins/org.eclip=
se.net4j.db/src/org/eclipse/net4j/db/IDBAdapter.java,v
retrieving revision 1.19
diff -u -r1.19 IDBAdapter.java
--- src/org/eclipse/net4j/db/IDBAdapter.java 13 Mar 2010 17:09:59 -0000 =
1.19
+++ src/org/eclipse/net4j/db/IDBAdapter.java 18 Mar 2010 10:20:27 -0000
@@ -46,9 +46,7 @@
=20
public boolean dropTable(IDBTable table, Statement statement);
=20
- public String[] getReservedWords();
-
- public boolean isReservedWord(String word);
+ public char getQuotationCharacter();
=20
/**
* @since 2.0
Index: src/org/eclipse/net4j/spi/db/DBAdapter.java
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
RCS file: =
/cvsroot/modeling/org.eclipse.emf/org.eclipse.emf.net4j/plug ins/org.eclip=
se.net4j.db/src/org/eclipse/net4j/spi/db/DBAdapter.java,v
retrieving revision 1.11
diff -u -r1.11 DBAdapter.java
--- src/org/eclipse/net4j/spi/db/DBAdapter.java 13 Mar 2010 17:09:59 =
-0000 1.11
+++ src/org/eclipse/net4j/spi/db/DBAdapter.java 18 Mar 2010 10:20:27 =
-0000
@@ -40,46 +40,10 @@
{
private static final ContextTracer TRACER =3D new =
ContextTracer(OM.DEBUG_SQL, DBAdapter.class);
=20
- private static final String[] SQL92_RESERVED_WORDS =3D { "ABSOLUTE", =
"ACTION", "ADD", "AFTER", "ALL", "ALLOCATE", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
- "ALTER", "AND", "ANY", "ARE", "ARRAY", "AS", "ASC", "ASENSITIVE", =
"ASSERTION", "ASYMMETRIC", "AT", "ATOMIC", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ //$NON-NLS-12$
- "AUTHORIZATION", "AVG", "BEFORE", "BEGIN", "BETWEEN", "BIGINT", =
"BINARY", "BIT", "BIT_LENGTH", "BLOB", "BOOLEAN", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "BOTH", "BREADTH", "BY", "CALL", "CALLED", "CASCADE", "CASCADED", =
"CASE", "CAST", "CATALOG", "CHAR", "CHARACTER", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "CHARACTER_LENGTH", "CHAR_LENGTH", "CHECK", "CLOB", "CLOSE", =
"COALESCE", "COLLATE", "COLLATION", "COLUMN", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$
- "COMMIT", "CONDITION", "CONNECT", "CONNECTION", "CONSTRAINT", =
"CONSTRAINTS", "CONSTRUCTOR", "CONTAINS", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$
- "CONTINUE", "CONVERT", "CORRESPONDING", "COUNT", "CREATE", =
"CROSS", "CUBE", "CURRENT", "CURRENT_DATE", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$
- "CURRENT_DEFAULT_TRANSFORM_GROUP", "CURRENT_PATH", =
"CURRENT_ROLE", "CURRENT_TIME", "CURRENT_TIMESTAMP", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
- "CURRENT_TRANSFORM_GROUP_FOR_TYPE", "CURRENT_USER", "CURSOR", =
"CYCLE", "DATA", "DATE", "DAY", "DEALLOCATE", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$
- "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DEFERRABLE", "DEFERRED", =
"DELETE", "DEPTH", "DEREF", "DESC", "DESCRIBE", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "DESCRIPTOR", "DETERMINISTIC", "DIAGNOSTICS", "DISCONNECT", =
"DISTINCT", "DO", "DOMAIN", "DOUBLE", "DROP", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$
- "DYNAMIC", "EACH", "ELEMENT", "ELSE", "ELSEIF", "END", "EQUALS", =
"ESCAPE", "EXCEPT", "EXCEPTION", "EXEC", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "EXECUTE", "EXISTS", "EXIT", "EXTERNAL", "EXTRACT", "FALSE", =
"FETCH", "FILTER", "FIRST", "FLOAT", "FOR", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "FOREIGN", "FOUND", "FREE", "FROM", "FULL", "FUNCTION", =
"GENERAL", "GET", "GLOBAL", "GO", "GOTO", "GRANT", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "GROUP", "GROUPING", "HANDLER", "HAVING", "HOLD", "HOUR", =
"IDENTITY", "IF", "IMMEDIATE", "IN", "INDICATOR", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "INITIALLY", "INNER", "INOUT", "INPUT", "INSENSITIVE", "INSERT", =
"INT", "INTEGER", "INTERSECT", "INTERVAL", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "INTO", "IS", "ISOLATION", "ITERATE", "JOIN", "KEY", "LANGUAGE", =
"LARGE", "LAST", "LATERAL", "LEADING", "LEAVE", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "LEFT", "LEVEL", "LIKE", "LOCAL", "LOCALTIME", "LOCALTIMESTAMP", =
"LOCATOR", "LOOP", "LOWER", "MAP", "MATCH", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "MAX", "MEMBER", "MERGE", "METHOD", "MIN", "MINUTE", "MODIFIES", =
"MODULE", "MONTH", "MULTISET", "NAMES", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "NATIONAL", "NATURAL", "NCHAR", "NCLOB", "NEW", "NEXT", "NO", =
"NONE", "NOT", "NULL", "NULLIF", "NUMERIC", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ //$NON-NLS-12$
- "OBJECT", "OCTET_LENGTH", "OF", "OLD", "ON", "ONLY", "OPEN", =
"OPTION", "OR", "ORDER", "ORDINALITY", "OUT", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "OUTER", "OUTPUT", "OVER", "OVERLAPS", "PAD", "PARAMETER", =
"PARTIAL", "PARTITION", "PATH", "POSITION", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "PRECISION", "PREPARE", "PRESERVE", "PRIMARY", "PRIOR", =
"PRIVILEGES", "PROCEDURE", "PUBLIC", "RANGE", "READ", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "READS", "REAL", "RECURSIVE", "REF", "REFERENCES", "REFERENCING", =
"RELATIVE", "RELEASE", "REPEAT", "RESIGNAL", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "RESTRICT", "RESULT", "RETURN", "RETURNS", "REVOKE", "RIGHT", =
"ROLE", "ROLLBACK", "ROLLUP", "ROUTINE", "ROW", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "ROWS", "SAVEPOINT", "SCHEMA", "SCOPE", "SCROLL", "SEARCH", =
"SECOND", "SECTION", "SELECT", "SENSITIVE", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "SESSION", "SESSION_USER", "SET", "SETS", "SIGNAL", "SIMILAR", =
"SIZE", "SMALLINT", "SOME", "SPACE", "SPECIFIC", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "SPECIFICTYPE", "SQL", "SQLCODE", "SQLERROR", "SQLEXCEPTION", =
"SQLSTATE", "SQLWARNING", "START", "STATE", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$
- "STATIC", "SUBMULTISET", "SUBSTRING", "SUM", "SYMMETRIC", =
"SYSTEM", "SYSTEM_USER", "TABLE", "TABLESAMPLE", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$
- "TEMPORARY", "THEN", "TIME", "TIMESTAMP", "TIMEZONE_HOUR", =
"TIMEZONE_MINUTE", "TO", "TRAILING", "TRANSACTION", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$
- "TRANSLATE", "TRANSLATION", "TREAT", "TRIGGER", "TRIM", "TRUE", =
"UNDER", "UNDO", "UNION", "UNIQUE", "UNKNOWN", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "UNNEST", "UNTIL", "UPDATE", "UPPER", "USAGE", "USER", "USING", =
"VALUE", "VALUES", "VARCHAR", "VARYING", "VIEW", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "WHEN", "WHENEVER", "WHERE", "WHILE", "WINDOW", "WITH", "WITHIN", =
"WITHOUT", "WORK", "WRITE", "YEAR", "ZONE" }; //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
-
private String name;
=20
private String version;
=20
- private Set<String> reservedWords;
-
public DBAdapter(String name, String version)
{
this.name =3D name;
@@ -375,26 +339,6 @@
throw new IllegalArgumentException("Unknown type: " + type); =
//$NON-NLS-1$
}
=20
- public String[] getSQL92ReservedWords()
- {
- return SQL92_RESERVED_WORDS;
- }
-
- public boolean isReservedWord(String word)
- {
- if (reservedWords =3D=3D null)
- {
- reservedWords =3D new HashSet<String>();
- for (String reservedWord : getReservedWords())
- {
- reservedWords.add(reservedWord.toUpperCase());
- }
- }
-
- word =3D word.toUpperCase();
- return reservedWords.contains(word);
- }
-
/**
* @since 2.0
*/
#P org.eclipse.net4j.db.derby
Index: src/org/eclipse/net4j/db/derby/DerbyAdapter.java
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
RCS file: =
/cvsroot/modeling/org.eclipse.emf/org.eclipse.emf.net4j/plug ins/org.eclip=
se.net4j.db.derby/src/org/eclipse/net4j/db/derby/DerbyAdapte r.java,v
retrieving revision 1.7
diff -u -r1.7 DerbyAdapter.java
--- src/org/eclipse/net4j/db/derby/DerbyAdapter.java 13 Mar 2010 =
17:10:02 -0000 1.7
+++ src/org/eclipse/net4j/db/derby/DerbyAdapter.java 18 Mar 2010 =
10:20:28 -0000
@@ -22,27 +22,6 @@
{
public static final String VERSION =3D "10.3.2.1"; //$NON-NLS-1$
=20
- private static final String[] RESERVED_WORDS =3D { "ADD", "ALL", =
"ALLOCATE", "ALTER", "AND", "ANY", "ARE", "AS", "ASC", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$
- "ASSERTION", "AT", "AUTHORIZATION", "AVG", "BEGIN", "BETWEEN", =
"BIGINT", "BIT", "BOOLEAN", "BOTH", "BY", "CALL", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "CASCADE", "CASCADED", "CASE", "CAST", "CHAR", "CHARACTER", =
"CHECK", "CLOSE", "COALESCE", "COLLATE", "COLLATION", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "COLUMN", "COMMIT", "CONNECT", "CONNECTION", "CONSTRAINT", =
"CONSTRAINTS", "CONTINUE", "CONVERT", "CORRESPONDING", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$
- "CREATE", "CURRENT", "CURRENT_DATE", "CURRENT_TIME", =
"CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR", "DEALLOCATE", =
//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ =
//$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
- "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DEFERRABLE", "DEFERRED", =
"DELETE", "DESC", "DESCRIBE", "DIAGNOSTICS", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "DISCONNECT", "DISTINCT", "DOUBLE", "DROP", "ELSE", "END", =
"END-EXEC", "ESCAPE", "EXCEPT", "EXCEPTION", "EXEC", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "EXECUTE", "EXISTS", "EXPLAIN", "EXTERNAL", "FALSE", "FETCH", =
"FIRST", "FLOAT", "FOR", "FOREIGN", "FOUND", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "FROM", "FULL", "FUNCTION", "GET", "GETCURRENTCONNECTION", =
"GLOBAL", "GO", "GOTO", "GRANT", "GROUP", "HAVING", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "HOUR", "IDENTITY", "IMMEDIATE", "IN", "INDICATOR", "INITIALLY", =
"INNER", "INOUT", "INPUT", "INSENSITIVE", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "INSERT", "INT", "INTEGER", "INTERSECT", "INTO", "IS", =
"ISOLATION", "JOIN", "KEY", "LAST", "LEFT", "LIKE", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "LOWER", "LTRIM", "MATCH", "MAX", "MIN", "MINUTE", "NATIONAL", =
"NATURAL", "NCHAR", "NVARCHAR", "NEXT", "NO", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "NOT", "NULL", "NULLIF", "NUMERIC", "OF", "ON", "ONLY", "OPEN", =
"OPTION", "OR", "ORDER", "OUTER", "OUTPUT", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ //$NON-NLS-12$ =
//$NON-NLS-13$
- "OVERLAPS", "PAD", "PARTIAL", "PREPARE", "PRESERVE", "PRIMARY", =
"PRIOR", "PRIVILEGES", "PROCEDURE", "PUBLIC", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "READ", "REAL", "REFERENCES", "RELATIVE", "RESTRICT", "REVOKE", =
"RIGHT", "ROLLBACK", "ROWS", "RTRIM", "SCHEMA", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "SCROLL", "SECOND", "SELECT", "SESSION_USER", "SET", "SMALLINT", =
"SOME", "SPACE", "SQL", "SQLCODE", "SQLERROR", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$
- "SQLSTATE", "SUBSTR", "SUBSTRING", "SUM", "SYSTEM_USER", "TABLE", =
"TEMPORARY", "TIMEZONE_HOUR", //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ =
//$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
- "TIMEZONE_MINUTE", "TO", "TRANSACTION", "TRANSLATE", =
"TRANSLATION", "TRIM", "TRUE", "UNION", "UNIQUE", "UNKNOWN", =
//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ =
//$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "UPDATE", "UPPER", "USER", "USING", "VALUES", "VARCHAR", =
"VARYING", "VIEW", "WHENEVER", "WHERE", "WITH", "WORK", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "WRITE", "XML", "XMLEXISTS", "XMLPARSE", "XMLQUERY", =
"XMLSERIALIZE", "YEAR" }; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ =
//$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
-
public DerbyAdapter(String name)
{
super(name, VERSION);
@@ -68,8 +47,8 @@
return super.getTypeName(field);
}
=20
- public String[] getReservedWords()
+ public char getQuotationCharacter()
{
- return RESERVED_WORDS;
+ return '\"';
}
}
#P org.eclipse.net4j.db.h2
Index: src/org/eclipse/net4j/db/h2/H2Adapter.java
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
RCS file: =
/cvsroot/modeling/org.eclipse.emf/org.eclipse.emf.net4j/plug ins/org.eclip=
se.net4j.db.h2/src/org/eclipse/net4j/db/h2/H2Adapter.java,v
retrieving revision 1.3
diff -u -r1.3 H2Adapter.java
--- src/org/eclipse/net4j/db/h2/H2Adapter.java 13 Mar 2010 17:09:56 =
-0000 1.3
+++ src/org/eclipse/net4j/db/h2/H2Adapter.java 18 Mar 2010 10:20:29 =
-0000
@@ -71,8 +71,8 @@
return super.getTypeName(field);
}
=20
- public String[] getReservedWords()
+ public char getQuotationCharacter()
{
- return getSQL92ReservedWords();
+ return '\"';
}
}
#P org.eclipse.net4j.db.hsqldb
Index: src/org/eclipse/net4j/db/hsqldb/HSQLDBAdapter.java
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
RCS file: =
/cvsroot/modeling/org.eclipse.emf/org.eclipse.emf.net4j/plug ins/org.eclip=
se.net4j.db.hsqldb/src/org/eclipse/net4j/db/hsqldb/HSQLDBAda pter.java,v
retrieving revision 1.7
diff -u -r1.7 HSQLDBAdapter.java
--- src/org/eclipse/net4j/db/hsqldb/HSQLDBAdapter.java 13 Mar 2010 =
17:09:54 -0000 1.7
+++ src/org/eclipse/net4j/db/hsqldb/HSQLDBAdapter.java 18 Mar 2010 =
10:20:30 -0000
@@ -91,8 +91,8 @@
return super.getTypeName(field);
}
=20
- public String[] getReservedWords()
+ public char getQuotationCharacter()
{
- return getSQL92ReservedWords();
+ return '\"';
}
}
#P org.eclipse.net4j.db.mysql
Index: src/org/eclipse/net4j/db/mysql/MYSQLAdapter.java
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
RCS file: =
/cvsroot/modeling/org.eclipse.emf/org.eclipse.emf.net4j/plug ins/org.eclip=
se.net4j.db.mysql/src/org/eclipse/net4j/db/mysql/MYSQLAdapte r.java,v
retrieving revision 1.8
diff -u -r1.8 MYSQLAdapter.java
--- src/org/eclipse/net4j/db/mysql/MYSQLAdapter.java 13 Mar 2010 =
17:09:57 -0000 1.8
+++ src/org/eclipse/net4j/db/mysql/MYSQLAdapter.java 18 Mar 2010 =
10:20:31 -0000
@@ -32,30 +32,6 @@
=20
public static final String VERSION =3D "5.1.5"; //$NON-NLS-1$
=20
- private static final String[] RESERVED_WORDS =3D { "ACTION", "ADD", =
"ALL", "ALTER", "ANALYZE", "AND", "AS", "ASC", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$
- "ASENSITIVE", "BEFORE", "BETWEEN", "BIGINT", "BINARY", "BIT", =
"BLOB", "BOTH", "BY", "CALL", "CASCADE", "CASE", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$ //$NON-NLS-11$ =
//$NON-NLS-12$
- "CHANGE", "CHAR", "CHARACTER", "CHECK", "COLLATE", "COLUMN", =
"CONDITION", "CONSTRAINT", "CONTINUE", "CONVERT", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
- "CREATE", "CROSS", "CURRENT_DATE", "CURRENT_TIME", =
"CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR", "DATABASE", //$NON-NLS-1$ =
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ =
//$NON-NLS-7$ //$NON-NLS-8$
- "DATABASES", "DATE", "DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE", =
"DAY_SECOND", "DEC", "DECIMAL", "DECLARE", //$NON-NLS-1$ //$NON-NLS-2$ =
//$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ =
//$NON-NLS-8$ //$NON-NL
|
|
|
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521645 is a reply to message #521624] |
Thu, 18 March 2010 11:53 |
|
This is a multi-part message in MIME format.
--------------080107090400020601020300
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
Am 18.03.2010 11:37, schrieb Bjoern Sundin:
>
> "Are you willing to contribute a patch to give H2Adapter its own,
> correct set of reserved words?
>
> Yes, I can do that. However, before I do that I would shortly want to
> discuss the concept of naming tables and columns. I think it might
> become a burden to maintain a hardcoded list of reserved words. Using
> another version of one of the the supported databases, some words
> might have been added or removed. A more generic solution would be
> handier (see patch.txt).
> It It is easy to modify the getName(String name, String suffix, int
> maxLength) method in AbstractMappingStrategy.java to surround the
> name with a double quote.I could test this using H2 and HSQLDB and
> there are no problems using reserved words as table and/or column
> names. It is also possible to send of a query using CDOQuery without
> surrounding the table/column name with double quotes. So the double
> quotes do not need to be transparent to the user of the framework,
> which I think is good. This is also applicable for Derby, according to
> the documentation
> ( http://db.apache.org/derby/docs/10.1/ref/rrefkeywords29722.h tml). I
> could not test it with Derby yet, though, being blocked on bug 306000.
> Also PostgreSQL recomment the double quote solution for reserved words
> ( http://www.postgresql.org/docs/8.4/interactive/sql-keywords- appendix.html).
> Remains handling MySQL, according to the documentation it is a
> possible solution, howevre instead of double quotes one has to use a
> grave accent ( ` ). By letting each DBAdapter define it's own
> quotation character this could be an easy adjustment. See also
> attached quite inofficial patch.
As inofficial as it can be in a public newsgroup :P
> If you want, I can add an bug/enhancement request and submit the patch
> as suggestion?
That'd be great!
>>> I could also verify that the annotation works, as I successfully named the table to e.g. TimeSquare in generating a new
>>> database.
>>>
>> Yes, the IDBAdapter always has the last word in specifying
>> the final name. If it assumes a name is a reserved word it
>> will mangle it.
>>
>>> 2) In order to be sure what names are used one would basically have to add an annotaion for each class/attibute/reference in the
>>> ecore model. But one of the reasons using the framework is to be able to swiftly generate a database from an existing model. I do
>>> not have a feeling that that is so swift anymore then.
>> So, what are you suggesting? Note, that you must satisfy
>> three criteria at the same time:
>> 1) Make it "swiftly"
>> 2) Make it produce valid names for any concrete DB
>> 3) Make it predictable
>>
>> Please, don't missunderstand me, I appreciate the framework very
>> much and merely want to give point to a, in my opinion, more user
>> friendly approach. You saw my proposal about double quotes at the
>> end of my last post. Didn't intend to sound crude.
> I really didn't feel offended! It's just a crazy time with M6 and
> EclipseCon preparation plus a deadline by my sponsor. I'm a bit
> short on words these days and I was just trying to make you
> propose a good solution. I know that there are some, but I hoped
> that you take the time to define them :P
>
> Yes, I'll try to contribute too, not only wail. ;-)
> At the moment I am too quite busy. I should present a result but there
> are several blockers to be handled before my app is running stable again.
>
>>> If one have no insight into the code there is also no possibility to find out
>>> what the DBStore considers a reserved word.
>>>
>> That's why we produce open source software. You do have all
>> insights that are needed ;-)
>>
>> The H2Adapter uses SQL92 keywords:
>>
>> | *public *String[] getReservedWords()
>> {
>> *return *getSQL92ReservedWords();
>> }|
>>
>>
>> And those list "TIMESTAMP" as a reserved word. Can you point
>> us to a list of the correct H2 keywords?
>>
>> http://www.h2database.com/html/advanced.html#compatibility
>>
>>> 3) The DBStore doesn't (yet) provide any CrossReferenceResolver for unresolved (not loaded) referencers. Afaik the only possible way
>>> to resolve those referencers without having to load a whole huge database into memory is to use CDOQuery as I described in my
>>> earlier post. Maybe there is another way, though, that I haven't detected yet?
>>>
>> No, but we have
>> https://bugs.eclipse.org/bugs/show_bug.cgi?id=300149 for it.
>>
> I have now adjusted the CrossReferenceUtil.java
Where is that?
> to take eventual table or column name annotations into consideration
> too. This also works good in my application if I annotate all critical
> class and feature names. I think I attach the class to bug 300149 as
> an initial suggestion.
If you contribute patches,
Please confirm the following:
1) The number of lines that you changed is smaller than 250. (if applicable)
2) You are the only author of these changed lines.
3) You apply the EPL to these changed lines.
>> I also mailed you some code a couple of days ago in order to
>> share my attempt how to handle not loaded cross referencers
>> (CrossReferenceUtil.java)
>>
>>> 4) If I use annotation to override the table name from e.g. TimeStamp to TimeSquare, the reference list tables still use the class
>>> name e.g. MyEvent_TimeStamp_List
>> Sounds odd. Can you file a bugzilla?
>>
>> I think I'll wait with that until I see where we are heading
>> to in this discussion
> That depends mainly on you, maybe on Stefan (but I know that he's
> busy, too, these days and mostly reactive to severe bugs).
>
> Well, I'm not sure if it is a bug. I might have been a bit in a hurry
> testing this. So, until I do encounter that this is an actual problem,
> I'd wait filing a bug.
Ok.
Cheers
/Eike
----
http://thegordian.blogspot.com
http://twitter.com/eikestepper
> Cheers
> Bjoern
--------------080107090400020601020300
Content-Type: text/html; charset=ISO-8859-15
Content-Transfer-Encoding: 8bit
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-15"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Am 18.03.2010 11:37, schrieb Bjoern Sundin:
<blockquote cite="mid:hnsvoq$dnh$1@build.eclipse.org" type="cite">
<title></title>
<meta http-equiv="Content-Type"
content="text/html; charset=ISO-8859-15">
<meta content="MSHTML 6.00.5730.13" name="GENERATOR">
<div><br>
</div>
<blockquote dir="ltr"
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;">
<div>"Are you willing to contribute a patch to give H2Adapter its
own, correct set of reserved words?<br>
</div>
<br>
</blockquote>
<div dir="ltr"><font size="2" face="Arial">Yes, I can do that.
However, before I do that I would shortly want to discuss the concept
of naming tables
Cheers
/Eike
----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper
|
|
|
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521668 is a reply to message #521624] |
Thu, 18 March 2010 12:56 |
Stefan Winkler
Messages: 307
Registered: July 2009
Location: Germany |
Senior Member |
|
|
Björn,
as Eike already said, I'm a bit short on time (I understand we all are
;-)). I'm currently fixing CDO-Bugs during my free week-ends. (300600 is
at the top of my list).
Just to give an idea, SQLQueries and annotations (and I think H2
support) had been contributed by some people in order to solve their
specific issues. Therefore, some of the features there might not be as
complete as one might like. But it is good you found them.
Just to summarize: we should
(1) have a Bug for using double-quotes instead of mangling table names.
(2) have a Bug for using annotated names in reference table/column names
(1) should make the problems of incorrect H2 keyword list go away as
well as the problem of an annotated name colliding with a reserved word
(the latter would have better thrown an exception instead of silently
mangling the name ...)
(2) should make the whole naming stuff more 'swift'. However, there is
still a problem with too long names. Particularly if you use package or
class prefixes, names could get too long and be mangled again. As a
long-term solution, we could maybe parse the SQL in the store,
transparently add double quotes or replace identifiers with their
database-mapped equivalents. But this would be a bit more work, I think.
Did I forget something?
Cheers,
Stefan
Am 18.03.2010 11:37, schrieb Bjoern Sundin:
>
>
> "Eike Stepper" <stepper@esc-net.de <mailto:stepper@esc-net.de>>
> schrieb im Newsbeitrag news:hnr2jf$3bf$1@build.eclipse.org...
> Am 17.03.2010 16:50, schrieb Bjoern Sundin:
>> I added some comments inline.
>>
>> Cheers
>> Bjoern
>>
>> "Eike Stepper" <stepper@esc-net.de
>> <mailto:stepper@esc-net.de>> schrieb im Newsbeitrag
>> news:hnqq5m$oua$2@build.eclipse.org...
>> Am 17.03.2010 15:21, schrieb Bjoern Sundin:
>>> Hi Stefan and thanks for your answer.
>>>
>>> I recon it could be a workable solution to use annotations in the ecore to override table and column names. I have some question
>>> marks about such a solution though:
>>>
>>> 1) I attempted to override the TimeStamp_C863 table name to force it to use the name TimeStamp by adding the following annotation:
>>> <eClassifiers xsi:type="ecore:EClass" name="TimeStamp">
>>> <eAnnotations source="http://www.eclipse.org/CDO/DBStore">
>>> <details key="tableName" value="TimeStamp"/>
>>> </eAnnotations>
>>> <eStructuralFeatures xsi:type="ecore:EAttribute" name="day" eType="ecore:EDataType
>>> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
>>> <eStructuralFeatures xsi:type="ecore:EAttribute" name="hours" eType="ecore:EDataType
>>> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
>>> <eStructuralFeatures xsi:type="ecore:EAttribute" name="minutes" eType="ecore:EDataType
>>> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
>>> <eStructuralFeatures xsi:type="ecore:EAttribute" name="seconds" eType="ecore:EDataType
>>> http://www.eclipse.org/emf/2002/Ecore#//EInt"/>
>>> </eClassifiers>
>>> This fails and the table name anyway becomes TimeStamp_C863. If I manually create a table TimeStamp in the database there is no problem.
>> That's odd, and probably a sign that the array of reserved
>> words in the IDBAdapter you're using (H2Adapter?) does not
>> match with reality.
>>
>> Afaics the DBAdapter defines the same reserved words for both the
>> H2Adapter and the HSQLDBAdapter. And it is true, I do try to
>> deliberately use some DBAdapter reserved words as table/column
>> names (which also works good using Derby and MySQL).
> Are you willing to contribute a patch to give H2Adapter its own,
> correct set of reserved words?
>
> Yes, I can do that. However, before I do that I would shortly want to
> discuss the concept of naming tables and columns. I think it might
> become a burden to maintain a hardcoded list of reserved words. Using
> another version of one of the the supported databases, some words
> might have been added or removed. A more generic solution would be
> handier (see patch.txt).
> It It is easy to modify the getName(String name, String suffix, int
> maxLength) method in AbstractMappingStrategy.java to surround the
> name with a double quote.I could test this using H2 and HSQLDB and
> there are no problems using reserved words as table and/or column
> names. It is also possible to send of a query using CDOQuery without
> surrounding the table/column name with double quotes. So the double
> quotes do not need to be transparent to the user of the framework,
> which I think is good. This is also applicable for Derby, according to
> the documentation
> ( http://db.apache.org/derby/docs/10.1/ref/rrefkeywords29722.h tml). I
> could not test it with Derby yet, though, being blocked on bug 306000.
> Also PostgreSQL recomment the double quote solution for reserved words
> ( http://www.postgresql.org/docs/8.4/interactive/sql-keywords- appendix.html).
> Remains handling MySQL, according to the documentation it is a
> possible solution, howevre instead of double quotes one has to use a
> grave accent ( ` ). By letting each DBAdapter define it's own
> quotation character this could be an easy adjustment. See also
> attached quite inofficial patch. If you want, I can add an
> bug/enhancement request and submit the patch as suggestion?
>
>>> I could also verify that the annotation works, as I successfully named the table to e.g. TimeSquare in generating a new
>>> database.
>>>
>> Yes, the IDBAdapter always has the last word in specifying
>> the final name. If it assumes a name is a reserved word it
>> will mangle it.
>>
>>> 2) In order to be sure what names are used one would basically have to add an annotaion for each class/attibute/reference in the
>>> ecore model. But one of the reasons using the framework is to be able to swiftly generate a database from an existing model. I do
>>> not have a feeling that that is so swift anymore then.
>> So, what are you suggesting? Note, that you must satisfy
>> three criteria at the same time:
>> 1) Make it "swiftly"
>> 2) Make it produce valid names for any concrete DB
>> 3) Make it predictable
>>
>> Please, don't missunderstand me, I appreciate the framework very
>> much and merely want to give point to a, in my opinion, more user
>> friendly approach. You saw my proposal about double quotes at the
>> end of my last post. Didn't intend to sound crude.
> I really didn't feel offended! It's just a crazy time with M6 and
> EclipseCon preparation plus a deadline by my sponsor. I'm a bit
> short on words these days and I was just trying to make you
> propose a good solution. I know that there are some, but I hoped
> that you take the time to define them :P
>
> Yes, I'll try to contribute too, not only wail. ;-)
> At the moment I am too quite busy. I should present a result but there
> are several blockers to be handled before my app is running stable again.
>
>
>>> If one have no insight into the code there is also no possibility to find out
>>> what the DBStore considers a reserved word.
>>>
>> That's why we produce open source software. You do have all
>> insights that are needed ;-)
>>
>> The H2Adapter uses SQL92 keywords:
>>
>> | *public *String[] getReservedWords()
>> {
>> *return *getSQL92ReservedWords();
>> }|
>>
>>
>> And those list "TIMESTAMP" as a reserved word. Can you point
>> us to a list of the correct H2 keywords?
>>
>>
>> http://www.h2database.com/html/advanced.html#compatibility
>>
>>> 3) The DBStore doesn't (yet) provide any CrossReferenceResolver for unresolved (not loaded) referencers. Afaik the only possible way
>>> to resolve those referencers without having to load a whole huge database into memory is to use CDOQuery as I described in my
>>> earlier post. Maybe there is another way, though, that I haven't detected yet?
>>>
>> No, but we have
>> https://bugs.eclipse.org/bugs/show_bug.cgi?id=300149 for it.
>>
>>
> I have now adjusted the CrossReferenceUtil.java to take eventual table
> or column name annotations into consideration too. This also works
> good in my application if I annotate all critical class and feature
> names. I think I attach the class to bug 300149 as an initial suggestion.
>
>> I also mailed you some code a couple of days ago in order to
>> share my attempt how to handle not loaded cross referencers
>> (CrossReferenceUtil.java)
>>
>>> 4) If I use annotation to override the table name from e.g. TimeStamp to TimeSquare, the reference list tables still use the class
>>> name e.g. MyEvent_TimeStamp_List
>> Sounds odd. Can you file a bugzilla?
>>
>> I think I'll wait with that until I see where we are heading
>> to in this discussion
> That depends mainly on you, maybe on Stefan (but I know that he's
> busy, too, these days and mostly reactive to severe bugs).
>
> Well, I'm not sure if it is a bug. I might have been a bit in a hurry
> testing this. So, until I do encounter that this is an actual problem,
> I'd wait filing a bug.
>
> Cheers
> Bjoern
|
|
|
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521673 is a reply to message #521645] |
Thu, 18 March 2010 13:18 |
Bjoern Sundin
Messages: 47
Registered: July 2009 |
Member |
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_025A_01CAC6A5.E5374AB0
Content-Type: text/plain;
charset="ISO-8859-15"
Content-Transfer-Encoding: quoted-printable
Ok, I created bug 306356 for this issue.
The CrossReferenceUtil.java is a class I made for my application. It is =
not in the framework but I attached it to bug 300149 as initial =
suggestion
Cheers
Bjoern
"Eike Stepper" <stepper@esc-net.de> schrieb im Newsbeitrag =
news:hnt475$lq9$1@build.eclipse.org...
Am 18.03.2010 11:37, schrieb Bjoern Sundin:=20
"Are you willing to contribute a patch to give H2Adapter its own, =
correct set of reserved words?
Yes, I can do that. However, before I do that I would shortly want =
to discuss the concept of naming tables and columns. I think it might =
become a burden to maintain a hardcoded list of reserved words. Using =
another version of one of the the supported databases, some words might =
have been added or removed. A more generic solution would be handier =
(see patch.txt).=20
It It is easy to modify the getName(String name, String suffix, int =
maxLength) method in AbstractMappingStrategy.java to surround the name =
with a double quote.I could test this using H2 and HSQLDB and there are =
no problems using reserved words as table and/or column names. It is =
also possible to send of a query using CDOQuery without surrounding the =
table/column name with double quotes. So the double quotes do not need =
to be transparent to the user of the framework, which I think is good. =
This is also applicable for Derby, according to the documentation =
( http://db.apache.org/derby/docs/10.1/ref/rrefkeywords29722.h tml). I =
could not test it with Derby yet, though, being blocked on bug 306000. =
Also PostgreSQL recomment the double quote solution for reserved words =
( http://www.postgresql.org/docs/8.4/interactive/sql-keywords- appendix.htm=
l). Remains handling MySQL, according to the documentation it is a =
possible solution, howevre instead of double quotes one has to use a =
grave accent ( ` ). By letting each DBAdapter define it's own quotation =
character this could be an easy adjustment. See also attached quite =
inofficial patch.=20
As inofficial as it can be in a public newsgroup :P
If you want, I can add an bug/enhancement request and submit the =
patch as suggestion?
That'd be great!
I could also verify that the annotation works, as I successfully named =
the table to e.g. TimeSquare in generating a new=20
database.
Yes, the IDBAdapter always has the last word in specifying the final =
name. If it assumes a name is a reserved word it will mangle it.
2) In order to be sure what names are used one would basically have to =
add an annotaion for each class/attibute/reference in the=20
ecore model. But one of the reasons using the framework is to be able to =
swiftly generate a database from an existing model. I do=20
not have a feeling that that is so swift anymore then. So, what are you =
suggesting? Note, that you must satisfy three criteria at the same time:
1) Make it "swiftly"
2) Make it produce valid names for any concrete DB
3) Make it predictable
Please, don't missunderstand me, I appreciate the framework very =
much and merely want to give point to a, in my opinion, more user =
friendly approach. You saw my proposal about double quotes at the end of =
my last post. Didn't intend to sound crude.
I really didn't feel offended! It's just a crazy time with M6 and =
EclipseCon preparation plus a deadline by my sponsor. I'm a bit short on =
words these days and I was just trying to make you propose a good =
solution. I know that there are some, but I hoped that you take the time =
to define them :P
Yes, I'll try to contribute too, not only wail. ;-)=20
At the moment I am too quite busy. I should present a result but =
there are several blockers to be handled before my app is running stable =
again.
If one have no insight into the code there is also no possibility to =
find out=20
what the DBStore considers a reserved word.
That's why we produce open source software. You do have all insights =
that are needed ;-)
The H2Adapter uses SQL92 keywords:
public String[] getReservedWords()
{
return getSQL92ReservedWords();
} =20
And those list "TIMESTAMP" as a reserved word. Can you point =
us to a list of the correct H2 keywords?
http://www.h2database.com/html/advanced.html#compatibility
3) The DBStore doesn't (yet) provide any CrossReferenceResolver for =
unresolved (not loaded) referencers. Afaik the only possible way=20
to resolve those referencers without having to load a whole huge =
database into memory is to use CDOQuery as I described in my=20
earlier post. Maybe there is another way, though, that I haven't =
detected yet?
No, but we have https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D300149 =
for it.
I have now adjusted the CrossReferenceUtil.java
Where is that?
to take eventual table or column name annotations into consideration =
too. This also works good in my application if I annotate all critical =
class and feature names. I think I attach the class to bug 300149 as an =
initial suggestion.
If you contribute patches,=20
Please confirm the following:
1) The number of lines that you changed is smaller than 250. (if =
applicable)
2) You are the only author of these changed lines.
3) You apply the EPL to these changed lines.
I also mailed you some code a couple of days ago in order to share my =
attempt how to handle not loaded cross referencers =
(CrossReferenceUtil.java)
4) If I use annotation to override the table name from e.g. TimeStamp to =
TimeSquare, the reference list tables still use the class=20
name e.g. MyEvent_TimeStamp_List Sounds odd. Can you file a bugzilla?
I think I'll wait with that until I see where we are heading to =
in this discussion
That depends mainly on you, maybe on Stefan (but I know that he's =
busy, too, these days and mostly reactive to severe bugs).
Well, I'm not sure if it is a bug. I might have been a bit in a =
hurry testing this. So, until I do encounter that this is an actual =
problem, I'd wait filing a bug.=20
Ok.
Cheers
/Eike
----
http://thegordian.blogspot.com
http://twitter.com/eikestepper
Cheers
Bjoern
------=_NextPart_000_025A_01CAC6A5.E5374AB0
Content-Type: text/html;
charset="ISO-8859-15"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE></TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3DISO-8859-15">
<META content=3D"MSHTML 6.00.5730.13" name=3DGENERATOR></HEAD>
<BODY text=3D#000000 bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Ok, I created bug 306356 for this=20
issue.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The CrossReferenceUtil.java is a class =
I made for=20
my application. It is not in the framework but I attached it to bug =
300149 as=20
initial suggestion</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Cheers</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Bjoern</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Eike Stepper" <<A=20
href=3D"mailto:stepper@esc-net.de">stepper@esc-net.de</A>> schrieb =
im=20
Newsbeitrag <A=20
=
href=3D"news:hnt475$lq9$1@build.eclipse.org">news:hnt475$lq9$1@build.ecli=
pse.org</A>...</DIV>Am=20
18.03.2010 11:37, schrieb Bjoern Sundin:=20
<BLOCKQUOTE cite=3Dmid:hnsvoq$dnh$1@build.eclipse.org type=3D"cite">
<META content=3D"MSHTML 6.00.5730.13" name=3DGENERATOR>
<DIV><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Are you willing to contribute a patch to give H2Adapter its =
own,=20
correct set of reserved words?<BR></DIV><BR></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Yes, I can do that. =
However, before I=20
do that I would shortly want to discuss the concept of naming=20
tables and columns. I think it might become a =
burden to=20
maintain a hardcoded list of reserved words. Using =
another version of=20
one of the the supported databases, some words might=20
have been added or removed. A more generic solution =
would be=20
handier (see patch.txt). </FONT></DIV>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>It It is easy to modify =
the=20
getName(String name, String suffix, int maxLength) method in=20
AbstractMappingStrategy.java to surround the name with a =
double=20
quote.I could test this using H2 and HSQLDB and there are no =
problems=20
using reserved words as table and/or column names. It is also =
possible to=20
send of a query using CDOQuery without surrounding the table/column =
name=20
with double quotes. So the double quotes do not need to=20
be transparent to the user of the framework, which I think is =
good.=20
This is also applicable for Derby, according to the documentation =
(<A=20
=
href=3D" http://db.apache.org/derby/docs/10.1/ref/rrefkeywords29722.h tml" =
=
moz-do-not-send=3D"true">http://db.apache.org/derby/docs/10.1/ref/rrefkey=
words29722.html</A>).=20
I could not test it with Derby yet, though, being =
blocked on=20
bug 306000. Also PostgreSQL recomment the double quote solution =
for=20
reserved words (<A=20
=
href=3D" http://www.postgresql.org/docs/8.4/interactive/sql-keywords- appen=
dix.html"=20
=
moz-do-not-send=3D"true">http://www.postgresql.org/docs/8.4/interactive/s=
ql-keywords-appendix.html</A>).=20
Remains handling MySQL, according to the documentation it is a =
possible=20
solution, howevre instead of double quotes one has to use a grave =
accent ( `=20
). By letting each DBAdapter define it's own quotation =
character this=20
could be an easy adjustment. See also attached quite inofficial =
patch.=20
<BR></FONT></DIV></BLOCKQUOTE>As inofficial as it can be in a public =
newsgroup=20
:P<BR><BR>
<BLOCKQUOTE cite=3Dmid:hnsvoq$dnh$1@build.eclipse.org type=3D"cite">
<DIV dir=3Dltr><FONT face=3DArial size=3D2>If you want, I can add an =
bug/enhancement request and submit the patch as=20
suggestion?</FONT></DIV></BLOCKQUOTE>That'd be great!<BR><BR>
<BLOCKQUOTE cite=3Dmid:hnsvoq$dnh$1@build.eclipse.org type=3D"cite">
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqtol$iig$1@build.eclipse.org =
type=3D"cite">
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: =
5px; BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">I could also verify that the annotation =
works, as I successfully named the table to e.g. TimeSquare in =
generating a new=20
database.
</PRE></BLOCKQUOTE>Yes, the IDBAdapter always has the last word in=20
specifying the final name. If it assumes a name is a reserved =
word it=20
will mangle it.<BR><BR>
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">2) In order to be sure what names are used =
one would basically have to add an annotaion for each =
class/attibute/reference in the=20
ecore model. But one of the reasons using the framework is to be able to =
swiftly generate a database from an existing model. I do=20
not have a feeling that that is so swift anymore then. =
</PRE></BLOCKQUOTE>
<DIV>So, what are you suggesting? Note, that you must satisfy =
three=20
criteria at the same time:<BR>1) Make it "swiftly"<BR>2) Make =
it=20
produce valid names for any concrete DB<BR>3) Make it=20
predictable<BR></DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Please, don't =
missunderstand me, I=20
appreciate the framework very much and merely want to give point =
to a,=20
in my opinion, more user friendly approach. You saw my =
proposal about double quotes at the end of my last post. Didn't =
intend=20
to sound crude.</FONT></DIV></BLOCKQUOTE>
<DIV>I really didn't feel offended! It's just a crazy time with M6 =
and=20
EclipseCon preparation plus a deadline by my sponsor. I'm a bit =
short on=20
words these days and I was just trying to make you propose a good=20
solution. I know that there are some, but I hoped that you take =
the time=20
to define them :P</DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Yes, I'll try to =
contribute too, not=20
only wail. ;-) </FONT></DIV>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>At the moment I am too =
quite busy. I=20
should present a result but there are several blockers to be handled =
before=20
my app is running stable again.</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqtol$iig$1@build.eclipse.org =
type=3D"cite">
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: =
5px; BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">If one have no insight into the code there =
is also no possibility to find out=20
what the DBStore considers a reserved word.
</PRE></BLOCKQUOTE>That's why we produce open source software. You=20
do have all insights that are needed ;-)<BR><BR>The H2Adapter =
uses=20
SQL92 keywords:<BR><BR>
<STYLE type=3Dtext/css>CODE {
FONT-SIZE: 10pt; MARGIN: 0px; FONT-FAMILY: Courier New, Courier
}
</STYLE>
<!-- =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D --><!-- =3D Java Sourcecode to HTML automatically =
converted code =3D --><!-- =3D Java2Html Converter 5.0 [2006-02-26] by =
Markus Gebhard markus@jave.de =3D --><!-- =3D Further =
information: http://www.java2html.de =3D -->
<DIV class=3Djava align=3Dleft>
<TABLE cellSpacing=3D0 cellPadding=3D3 bgColor=3D#ffffff =
border=3D0>
<TBODY>
<TR><!-- start source code -->
<TD vAlign=3Dtop noWrap align=3Dleft><CODE><FONT=20
color=3D#ffffff> </FONT><FONT=20
color=3D#7f0055><B>public </B></FONT><FONT=20
color=3D#000000>String</FONT><FONT=20
color=3D#000000>[] </FONT><FONT=20
color=3D#000000>getReservedWords</FONT><FONT=20
color=3D#000000>()</FONT><BR><FONT=20
color=3D#ffffff> </FONT><FONT=20
color=3D#000000>{</FONT><BR><FONT=20
color=3D#ffffff> </FONT ><FONT=20
color=3D#7f0055><B>return </B></FONT><FONT=20
color=3D#000000>getSQL92ReservedWords</FONT><FONT=20
color=3D#000000>()</FONT><FONT =
color=3D#000000>;</FONT><BR><FONT=20
color=3D#ffffff> </FONT><FONT=20
color=3D#000000>}</FONT></CODE>=20
</TD><!-- end source code --></TR></TBODY></TABLE></DIV><!-- =
=3D END of automatically generated HTML code =3D --><!-- =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D -->
<DIV><BR>And those list "TIMESTAMP" as a reserved word. Can =
you point=20
us to a list of the correct H2 keywords?</DIV>
<DIV> </DIV></BLOCKQUOTE>
<DIV dir=3Dltr><A class=3Dmoz-txt-link-freetext=20
=
href=3D"http://www.h2database.com/html/advanced.html#compatibility"=20
=
moz-do-not-send=3D"true">http://www.h2database.com/html/advanced.html#com=
patibility</A><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: =
5px; BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">3) The DBStore doesn't (yet) provide any =
CrossReferenceResolver for unresolved (not loaded) referencers. Afaik =
the only possible way=20
to resolve those referencers without having to load a whole huge =
database into memory is to use CDOQuery as I described in my=20
earlier post. Maybe there is another way, though, that I haven't =
detected yet?
</PRE></BLOCKQUOTE>
<DIV>No, but we have <A class=3Dmoz-txt-link-freetext=20
=
href=3D"https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D300149"=20
=
moz-do-not-send=3D"true">https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D=
300149</A>=20
for it.</DIV>
<DIV> </DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE >
<DIV dir=3Dltr><FONT face=3DArial size=3D2>I have now adjusted the=20
CrossReferenceUtil.java</FONT></DIV></BLOCKQUOTE>Where is =
that?<BR><BR>
<BLOCKQUOTE cite=3Dmid:hnsvoq$dnh$1@build.eclipse.org type=3D"cite">
<DIV dir=3Dltr><FONT face=3DArial size=3D2>to take eventual table or =
column name=20
annotations into consideration too. This also works good in my =
application=20
if I annotate all critical class and feature names. I think I attach =
the=20
class to bug 300149 as an initial =
suggestion.</FONT></DIV></BLOCKQUOTE>If you=20
contribute patches,<FONT face=3DArial size=3D2> </FONT><BR><PRE =
class=3Dbz_comment_text id=3Dcomment_text_2>Please confirm the =
following:
1) The number of lines that you changed is smaller than 250. (if =
applicable)
2) You are the only author of these changed lines.
3) You apply the EPL to these changed lines.
</PRE>
<BLOCKQUOTE cite=3Dmid:hnsvoq$dnh$1@build.eclipse.org type=3D"cite">
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqtol$iig$1@build.eclipse.org =
type=3D"cite">
<DIV dir=3Dltr><FONT face=3DArial size=3D2>I also mailed =
you some code a=20
couple of days ago in order to share my attempt how to =
handle not=20
loaded cross referencers =
(CrossReferenceUtil.java)</FONT><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: =
5px; BORDER-LEFT: rgb(0,0,0) 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE cite=3Dmid:hnqoi3$krq$1@build.eclipse.org =
type=3D"cite"><PRE wrap=3D"">4) If I use annotation to override the =
table name from e.g. TimeStamp to TimeSquare, the reference list tables =
still use the class=20
name e.g. MyEvent_TimeStamp_List </PRE></BLOCKQUOTE>
<DIV>Sounds odd. Can you file a bugzilla?</DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>I think =
I'll wait with that=20
until I see where we are heading to in this=20
discussion</FONT><BR></DIV></BLOCKQUOTE>
<DIV>That depends mainly on you, maybe on Stefan (but I know that =
he's=20
busy, too, these days and mostly reactive to severe=20
bugs).<BR></DIV></BLOCKQUOTE>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Well, I'm not sure if it =
is a bug. I=20
might have been a bit in a hurry testing this. So, until I do =
encounter that=20
this is an actual problem, I'd wait filing a bug.=20
</FONT></DIV></BLOCKQUOTE>Ok.<BR><BR>Cheers<BR>/Eike <BR><BR>----<BR><A =
class=3Dmoz-txt-link-freetext=20
=
href=3D"http://thegordian.blogspot.com">http://thegordian.blogspot.com</A=
><BR><A=20
class=3Dmoz-txt-link-freetext=20
=
href=3D"http://twitter.com/eikestepper">http://twitter.com/eikestepper</A=
><BR><BR><BR>
<BLOCKQUOTE cite=3Dmid:hnsvoq$dnh$1@build.eclipse.org type=3D"cite">
<DIV dir=3Dltr> </DIV>
<DIV dir=3Dltr><FONT face=3DArial size=3D2>Cheers</FONT></DIV>
<DIV dir=3Dltr><FONT face=3DArial=20
size=3D2>Bjoern</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BODY ></HTML>
------=_NextPart_000_025A_01CAC6A5.E5374AB0--
|
|
| |
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521697 is a reply to message #521681] |
Thu, 18 March 2010 14:01 |
Stefan Winkler
Messages: 307
Registered: July 2009
Location: Germany |
Senior Member |
|
|
Björn,
comments below.
>> as Eike already said, I'm a bit short on time (I understand we all are
>> ;-)). I'm currently fixing CDO-Bugs during my free week-ends. (300600 is
>> at the top of my list).
>>
> No problem, I'm anyway in awe with what you guys are all achieving! :-)
>
Thanks ;-)
>
>> Just to give an idea, SQLQueries and annotations (and I think H2
>> support) had been contributed by some people in order to solve their
>> specific issues. Therefore, some of the features there might not be as
>> complete as one might like. But it is good you found them.
>>
> I guess this is how it works, one try to suit it for ones needs and the framework gets thoroghly debugged and tested. I was really
> happy when I discovered the CDOQuery API. It helps a lot in my application (which has a quite huge and complex database). Didn't
> know about the annotations though, but one never learn out, right?
>
Ah well ... missing documentation is another one of our issues. There's
so much to do in so little time ;-)
>
>> Just to summarize: we should
>> (1) have a Bug for using double-quotes instead of mangling table names.
>> (2) have a Bug for using annotated names in reference table/column names
>>
>> (1) should make the problems of incorrect H2 keyword list go away as
>> well as the problem of an annotated name colliding with a reserved word
>> (the latter would have better thrown an exception instead of silently
>> mangling the name ...)
>>
> I filed bug 306356 for this, my proposal though is actually making the key word arrays unnecessary as each DBAdapter defines its
> quotation char. In this way, the names does not collide with the reserved words anymore. Please give your input to the patch I
> submitted there.
>
I'll have a look - thank you for your contribution!
>
>> (2) should make the whole naming stuff more 'swift'. However, there is
>> still a problem with too long names. Particularly if you use package or
>> class prefixes, names could get too long and be mangled again. As a
>> long-term solution, we could maybe parse the SQL in the store,
>> transparently add double quotes or replace identifiers with their
>> database-mapped equivalents. But this would be a bit more work, I think.
>>
> You are definitely right about that (and here was my immediate need for a solution shining through... ;-) ).
> As you say, the naming as it is is fine as long as the names doesn't get too long. In the project I made using Teneo some years ago
> I had to add a property for the Oracle database to restrict the length to 30 characters. Otherwise there would be problems creating
> the database. Those table names then looked really garbeld! I think first all vowels were removed...
>
I always wondered how an advanced product like Oracle cannot support
more than 30 characters :-P
> Maybe one would have to map the table/column name with the EClass or EFeature and resolve it on DBAdapter level? Otherwise it
> becomes quite tricky to use CDOQuery.
>
> I see it as two user interfaces:
> Model to CDO/Net4J - The user (programmer) is aware of the model he made and the CDOObjects, EPackages etc
> CDO/Net4J to DB - Basically, as a object oriented database is generated, the column names could be completely something different.
>
That's why I'm not too happy with the current SQLQuery implementation -
it goes around the layered architecture you sketched: The annotations
were more of a workaround to achieve more predictability for the
mapping. However what I'd most like to see would be an abstract SQL-like
query which could be translated to actual SQLs by the mapping. That way
the two layers (Model--CDO | CDO--DB) are maintained.
> But CDO/Net4J must then be able to translate the table and column names. Isn't this actually already being done in the Teneo plugins
> (or is it Hibernate)? Maybe there is a possibility to use a common plugin solving this problem for both projects?
>
I'd have talk to Martin about than some time, I'm currently unaware of
how or where it is done in Teneo.
Cheers,
Stefan
|
|
| |
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521735 is a reply to message #521697] |
Thu, 18 March 2010 15:39 |
|
Am 18.03.2010 15:01, schrieb Stefan Winkler:
>
>> [...]
>> I see it as two user interfaces:
>> Model to CDO/Net4J - The user (programmer) is aware of the model he made and the CDOObjects, EPackages etc
>> CDO/Net4J to DB - Basically, as a object oriented database is generated, the column names could be completely something different.
>>
>>
> That's why I'm not too happy with the current SQLQuery implementation -
> it goes around the layered architecture you sketched: The annotations
> were more of a workaround to achieve more predictability for the
> mapping. However what I'd most like to see would be an abstract SQL-like
> query which could be translated to actual SQLs by the mapping. That way
> the two layers (Model--CDO | CDO--DB) are maintained.
>
See 256931: Provide common query language
https://bugs.eclipse.org/bugs/show_bug.cgi?id=256931
>
>> But CDO/Net4J must then be able to translate the table and column names. Isn't this actually already being done in the Teneo plugins
>> (or is it Hibernate)? Maybe there is a possibility to use a common plugin solving this problem for both projects?
>>
>>
> I'd have talk to Martin about than some time, I'm currently unaware of
> how or where it is done in Teneo.
>
Well, Teneo just passes the HQL query string to Hibernate. So the magic
is mostly in Hibernate and not in Teneo.
Cheers
/Eike
----
http://thegordian.blogspot.com
http://twitter.com/eikestepper
Cheers
/Eike
----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper
|
|
|
| Re: [CDO] How to resolve 'adjusted' table and column names [message #521742 is a reply to message #521735] |
Thu, 18 March 2010 15:57 |
Martin Taal
Messages: 5468
Registered: July 2009 |
Senior Member |
|
|
Hi,
FYI, Teneo also creates all table and column names. The names of the db artifacts are all back-ticked in the mapping.
This tells Hibernate to escape/backtick all the names also in all the queries.
The naming of table/column names is also determined by Teneo. Automatic naming is not entirely trivial...:
- stability: As Eike indicated earlier, an important concept here is to be stable, so for the same model element always
the same table/column name should be generated (even when new model elements are added). So the naming logic for one
model element (efeature/eclass) should work independent from other naming/existence of other model elements.
- uniqueness: for standard cases naming is trivial, however if you get into multiple inheritance cases with foreign key
join colums for one-to-many/list or many-to-many associations then things get more difficult. Teneo has a good (imho)
algorithm to cover all these cases.
The naming logic in Teneo tries to generate readable and logical names. Teneo also has an algorithm to work with the
Oracle constraint of 30 characters (this constraint seems to come from the middle-ages somewhere...:-). The algorithm
tries to maintain readability by first stripping fowels and then goes further if that did not help.
Then if you are not satisfied with the Teneo naming you can:
- implement your own naming strategy
- use annotations, there are many annotations which you can use to influence naming: the simple one @Column but also
JoinColumn and PrimaryKeyJoinColumn. Then there are the AssociationOverride and AttributeOverride annotations which you
can use to override naming of columns.
The nice thing with Hibernate/HQL is that you are not bothered with table/column names or sql specialities like join
tables. You query using model/domain names and concepts (like lists).
The above works in Teneo standalone but also for the CDO Hibernate store.
gr. Martin
Eike Stepper wrote:
> Am 18.03.2010 15:01, schrieb Stefan Winkler:
>>
>>> [...]
>>> I see it as two user interfaces:
>>> Model to CDO/Net4J - The user (programmer) is aware of the model he
>>> made and the CDOObjects, EPackages etc
>>> CDO/Net4J to DB - Basically, as a object oriented database is
>>> generated, the column names could be completely something different.
>>>
>>>
>> That's why I'm not too happy with the current SQLQuery implementation -
>> it goes around the layered architecture you sketched: The annotations
>> were more of a workaround to achieve more predictability for the
>> mapping. However what I'd most like to see would be an abstract SQL-like
>> query which could be translated to actual SQLs by the mapping. That way
>> the two layers (Model--CDO | CDO--DB) are maintained.
>>
> See 256931: Provide common query language
> https://bugs.eclipse.org/bugs/show_bug.cgi?id=256931
>
>>
>>> But CDO/Net4J must then be able to translate the table and column
>>> names. Isn't this actually already being done in the Teneo plugins
>>> (or is it Hibernate)? Maybe there is a possibility to use a common
>>> plugin solving this problem for both projects?
>>>
>>>
>> I'd have talk to Martin about than some time, I'm currently unaware of
>> how or where it is done in Teneo.
>>
> Well, Teneo just passes the HQL query string to Hibernate. So the magic
> is mostly in Hibernate and not in Teneo.
>
> Cheers
> /Eike
>
> ----
> http://thegordian.blogspot.com
> http://twitter.com/eikestepper
>
>
--
With Regards, Martin Taal
Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
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
|
|
|
Goto Forum:
Current Time: Fri Apr 26 17:04:44 GMT 2024
Powered by FUDForum. Page generated in 0.04736 seconds |
] 
Search
Help
Register
Login
Home
