You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jetspeed-dev@portals.apache.org by David Sean Taylor <da...@bluesunrise.com> on 2008/09/08 20:08:31 UTC
new 2.2 portlet preferences
Im looking at the portlet preferences in 2.2. I would like to suggest
using only one FK for the entity, unless there is a reason for having
both an OID and ID association on the same single logical association
<field-descriptor
name="entityOid"
column="ENTITY_OID"
jdbc-type="BIGINT"
nullable="false"
primarykey="true"
>
</field-descriptor>
<field-descriptor
name="entityId"
column="ENTITY_ID"
jdbc-type="VARCHAR"
nullable="true"
length="80"
>
</field-descriptor>
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
Re: new 2.2 portlet preferences
Posted by David Sean Taylor <da...@bluesunrise.com>.
On Sep 8, 2008, at 3:48 PM, Ate Douma wrote:
> David Sean Taylor wrote:
>> Im looking at the portlet preferences in 2.2. I would like to
>> suggest using only one FK for the entity, unless there is a reason
>> for having both an OID and ID association on the same single
>> logical association
>> <field-descriptor
>> name="entityOid"
>> column="ENTITY_OID"
>> jdbc-type="BIGINT"
>> nullable="false"
>> primarykey="true"
>> >
>> </field-descriptor>
>> <field-descriptor
>> name="entityId"
>> column="ENTITY_ID"
>> jdbc-type="VARCHAR"
>> nullable="true"
>> length="80"
>> >
>> </field-descriptor>
>
> Hi David,
>
> These are both needed to support default (portlet scoped) preference
> values.
> As those are not tied to a specific portlet entity, we need to
> identify separately from those for a specific entity.
>
> As for portlet scoped preference values there is no entity
> reference, I initially tried to use an optional foreign key.
> I hit problems with that though using a PostgreSQL database (my
> primary test database).
>
> To ensure integrity, the entity reference (if defined) should be
> part of the PK (or at least a unique constraint).
> But, PostgreSQL doesn't support optional fields in a PK (like Oracle).
> Far worse though, defining an (additional) unique index didn't work
> either although I could create it: the uniqueness constraint simply
> isn't enforced :(
>
> I'm not sure if other databases (except Oracle which can deal with
> this) would give similar problems, but I could solve this technical
> database problem generically by using the additional (and optional)
> ENTITY_ID field.
>
> The integrity is now ensured by storing a -1 value in the ENTITY_OID
> field for portlet scoped preference values.
> Because of that, a FK to the PORTLET_ENTITY table isn't possible,
> which is why I added the additional optional ENTITY_ID column which
> (is supposed to) be linked to the entity ID column with CASCADE
> DELETE restriction.
> So, when an entity is deleted, its linked preferences will also be
> automatically removed by the database.
>
> I actually tested this before and it worked as intended.
> But... somehow I must have messed up when I was in a hurry before I
> went on holiday in July, because the current definition in
> registry_schema.xml is incorrectly specifying the FK between the
> PORTLET_PREFERENCE_VALUE.ENTITY_OID and PORTLET_ENTITY.PEID, not
> between PORTLET_PREFERENCE_VALUE.ENTITY_ID and PORTLET_ENTITY.ID as
> intended.
>
> As result, the current configuration clearly is broken for handling
> portlet scoped preferences.
> I haven't tested this anymore since I came back from holiday but it
> definitely will result in an exception when you try to edit prefs
> through the j2-admin PAM portlet.
>
> I can look into fixing this some time this Wednesday (tomorrow I'll
> be tied up with a client), or you can try to redefine the FK as
> intended and see if it works again then.
>
> Anyway, if you know of an alternative solution which will handle
> both the integrity constraint and the optional reference to the
> portlet entity, I'm all for "fixing" this rather clunky solution.
> The only other solution I could come up with would be storing
> portlet scoped preferences separately in another table.
> But that solution will have its own set of technical and usability
> issues, especially if you need to access the preference values
> directly through sql (would require UNION ALL queries).
I've been playing around with the JPA based solution and just by
prototyping with a more powerful tool, its shedding some new light and
making data modeling easier. I am still in exploratory stages. What
about 2 sets of tables, one for portlet "default" preferences and one
for user preferences.
CREATE TABLE PORTLET_PREFERENCE
(
ID INTEGER NOT NULL,
PORTLET_ID INTEGER NOT NULL,
-- WINDOW_ID VARCHAR(80) NOT NULL, -- could be used for PSML
preferences or just leave this feature as is
NAME VARCHAR(128) NOT NULL,
PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX UIX_PORTLET_PREFERENCE ON PORTLET_PREFERENCE
(PORTLET_ID, NAME);
CREATE TABLE PORTLET_PREFERENCE_VALUE
(
ID INTEGER NOT NULL,
READONLY SMALLINT NOT NULL,
PREF_VALUE VARCHAR(4000)
);
CREATE INDEX IX_PREFS_PREF_ID ON PORTLET_PREFERENCE_VALUE (ID);
CREATE TABLE USER_PREFERENCE
(
ID INTEGER NOT NULL,
PORTLET_ID INTEGER NOT NULL,
USER_NAME VARCHAR(80) NOT NULL,
WINDOW_ID VARCHAR(80) NOT NULL,
NAME VARCHAR(128) NOT NULL,
PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX UIX_USER_PREFERENCE ON USER_PREFERENCE (USER_NAME,
WINDOW_ID, NAME);
CREATE TABLE USER_PREFERENCE_VALUE
(
ID INTEGER NOT NULL,
READONLY SMALLINT NOT NULL,
PREF_VALUE VARCHAR(4000)
);
CREATE INDEX IX_USER_PREF_ID ON USER_PREFERENCE_VALUE (ID);
I would prefer not having 2 sets of tables, but Im not sure how to
represent that NULL in the entity/window id that is causing you
trouble short of coming up with a constant value such as "_defaults_"
for the entity id
The PORTLET_ENTITY table can be dropped along with the entity manager,
simplifying and clarifying the solution, localizing it to preferences
as entities were really all about preferences anyway
I know Im going to get a lecture now on trying to cut out the entity
table, because window id != entity id...
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
Re: new 2.2 portlet preferences
Posted by Ate Douma <at...@douma.nu>.
David Sean Taylor wrote:
> Im looking at the portlet preferences in 2.2. I would like to suggest
> using only one FK for the entity, unless there is a reason for having
> both an OID and ID association on the same single logical association
>
> <field-descriptor
> name="entityOid"
> column="ENTITY_OID"
> jdbc-type="BIGINT"
> nullable="false"
> primarykey="true"
> >
> </field-descriptor>
>
> <field-descriptor
> name="entityId"
> column="ENTITY_ID"
> jdbc-type="VARCHAR"
> nullable="true"
> length="80"
> >
> </field-descriptor>
Hi David,
These are both needed to support default (portlet scoped) preference values.
As those are not tied to a specific portlet entity, we need to identify separately from those for a specific entity.
As for portlet scoped preference values there is no entity reference, I initially tried to use an optional foreign key.
I hit problems with that though using a PostgreSQL database (my primary test database).
To ensure integrity, the entity reference (if defined) should be part of the PK (or at least a unique constraint).
But, PostgreSQL doesn't support optional fields in a PK (like Oracle).
Far worse though, defining an (additional) unique index didn't work either although I could create it: the uniqueness
constraint simply isn't enforced :(
I'm not sure if other databases (except Oracle which can deal with this) would give similar problems, but I could solve
this technical database problem generically by using the additional (and optional) ENTITY_ID field.
The integrity is now ensured by storing a -1 value in the ENTITY_OID field for portlet scoped preference values.
Because of that, a FK to the PORTLET_ENTITY table isn't possible, which is why I added the additional optional ENTITY_ID
column which (is supposed to) be linked to the entity ID column with CASCADE DELETE restriction.
So, when an entity is deleted, its linked preferences will also be automatically removed by the database.
I actually tested this before and it worked as intended.
But... somehow I must have messed up when I was in a hurry before I went on holiday in July, because the current
definition in registry_schema.xml is incorrectly specifying the FK between the PORTLET_PREFERENCE_VALUE.ENTITY_OID and
PORTLET_ENTITY.PEID, not between PORTLET_PREFERENCE_VALUE.ENTITY_ID and PORTLET_ENTITY.ID as intended.
As result, the current configuration clearly is broken for handling portlet scoped preferences.
I haven't tested this anymore since I came back from holiday but it definitely will result in an exception when you try
to edit prefs through the j2-admin PAM portlet.
I can look into fixing this some time this Wednesday (tomorrow I'll be tied up with a client), or you can try to
redefine the FK as intended and see if it works again then.
Anyway, if you know of an alternative solution which will handle both the integrity constraint and the optional
reference to the portlet entity, I'm all for "fixing" this rather clunky solution.
The only other solution I could come up with would be storing portlet scoped preferences separately in another table.
But that solution will have its own set of technical and usability issues, especially if you need to access the
preference values directly through sql (would require UNION ALL queries).
Regards,
Ate
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org