[GeoNetwork-devel] Oracle bugs in SQL script

GeoNetwork GeoNetwork Developer
1

Hi All,

            I've found some bugs in the Oracle SQL script used to generate
the database tables and would like some clarification on what they are
trying to do. The main problem seems to be the introduction of the Languages
table. Now a lot of other tables contain a langId field and this has been
made part of their primary key. It is this that is causing the problems when
they are referenced in constraints on other tables.

            I don't understand why there is a langId field on the Operations
table. The only reason I can think is that you want to put
internationalization support in here. But this causes more problems than it
solves, and internationalization can be done in other ways. Eg. Use a common
name in the database which is then looked up in a lookup table or XML
document to get the actual term used (which is how it works in other areas
in Geonetwork). I can't see a need to have one operation allowed in one
language but not in another. The OperationsAllowed table refers to the
Operations.ID field, but this is not a primary key anymore (both the ID and
LANGID fields form the key). So this forces you to specify that an operation
is allowed (or denied) for every language individually.

            The other problem area is in the User and UserProfile
interaction. I'm not sure what a profile means to Geonetwork, but it can
make sense to have many profiles to one user. But does it make sense to have
the UserProfiles.langId field as part of the primary key? I just don't
understand what the Languages table is doing and why it is in the database
complicating everything.

            I think the problem is that you need to allow more than 1
language to each ID but want the languages to be unique for that ID. So it
does need to be part of the primary key. But then, when it is referenced in
foreign tables, you need to reference both fields, not just the ID.

            This may be caused by a script half way through development
changes and the relevant foreign tables and constraints have not been
updated yet. I can get by just by removing all constraints, but I don't want
to do that until I understand what they are doing.

            Also, the REGIONS table defines 4 columns as type DOUBLE which
is either not a valid data type in Oracle or it expects parameters to it
because Oracle throws an error saying that there is a missing keyword after
the double word. I changed all these to type FLOAT and it worked fine. Note:
in Oracle, float and double are not the same as in Java where one is more
precise than the other. You can make any floating point column as precise as
you need it. In the end, all numeric values come down to the same data type:
NUMBER, which can take a precision and scale value in the declaration. Eg.
NUMBER(38,10) says that there are 38 spaces available for digits, 10 of
which are for the fractional part.

--
Steven Smith <steven.smith@anonymised.com>
Software Developer / Analyst

Geometry Pty Ltd

Telephone

:

03 6223 1999

Facsimile

:

03 6223 1988

Web

:

www.geometryit.com
<file:///C:\Documents%20and%20Settings\ssmith.GEOMETRY\Application%20Data\Mi
crosoft\Signatures\www.geometryit.com>

Address

:

31 Salamanca Square, Battery Point, TAS 7004, Australia

Postal

:

PO Box 844, Sandy Bay, TAS 7006, Australia

Building Intelligent Business through the Power of Spatial

2

Hi Steven,

I have thought a while about your notes and finally I made some little changes.

            I've found some bugs in the Oracle SQL script used to generate
the database tables and would like some clarification on what they are
trying to do. The main problem seems to be the introduction of the Languages
table. Now a lot of other tables contain a langId field and this has been
made part of their primary key. It is this that is causing the problems when
they are referenced in constraints on other tables.

            I don't understand why there is a langId field on the Operations
table. The only reason I can think is that you want to put
internationalization support in here. But this causes more problems than it
solves, and internationalization can be done in other ways. Eg. Use a common
name in the database which is then looked up in a lookup table or XML
document to get the actual term used (which is how it works in other areas
in Geonetwork). I can't see a need to have one operation allowed in one
language but not in another. The OperationsAllowed table refers to the
Operations.ID field, but this is not a primary key anymore (both the ID and
LANGID fields form the key). So this forces you to specify that an operation
is allowed (or denied) for every language individually.

I don't want to lookup in an external XML file. This is how categories work now
but it is very confusing for users. Regarding operations, to enforce the FKey in
OperationAllowed a simple 'Operation' table with an 'id' is enough. GeoNetwork's
operations here are quite static so a lookup XML could have sense. Nonetheless,
want we want to achieve is the freedom to add custom privileges. For this reason
I decided to use the following tables:

Operations(id, name, reserved)
OperationsDes(idDes, langId, label)

With a user interface, users could add their operations and assign privileges to their
metadata.

This fixed one problem with oracle.

            The other problem area is in the User and UserProfile
interaction. I'm not sure what a profile means to Geonetwork, but it can
make sense to have many profiles to one user. But does it make sense to have
the UserProfiles.langId field as part of the primary key? I just don't
understand what the Languages table is doing and why it is in the database
complicating everything.

A profile is simply a set of services that a user can access and is handled by Jeeves.

In this case I wanted to enforce the profile into a predefined set, so I added the UserProfiles
table. Rethinking about that, I decided to remove this table and to check the profile
using Java code. This keeps the database simpler and fixed the second problem with oracle.
Profiles are static so localization is achieved with a simple XML lookup.

            I think the problem is that you need to allow more than 1
language to each ID but want the languages to be unique for that ID. So it
does need to be part of the primary key. But then, when it is referenced in
foreign tables, you need to reference both fields, not just the ID.

            This may be caused by a script half way through development
changes and the relevant foreign tables and constraints have not been
updated yet. I can get by just by removing all constraints, but I don't want
to do that until I understand what they are doing.

???

            Also, the REGIONS table defines 4 columns as type DOUBLE which
is either not a valid data type in Oracle or it expects parameters to it
because Oracle throws an error saying that there is a missing keyword after
the double word. I changed all these to type FLOAT and it worked fine. Note:
in Oracle, float and double are not the same as in Java where one is more
precise than the other. You can make any floating point column as precise as
you need it. In the end, all numeric values come down to the same data type:
NUMBER, which can take a precision and scale value in the declaration. Eg.
NUMBER(38,10) says that there are 38 spaces available for digits, 10 of
which are for the fractional part.

Ok. I changed the DOUBLE to a FLOAT.

Here, having regions on the database allows users to add new regions using a
simple interface.

Cheers,
Andrea