Database profile handling

["Setera, Craig" <Cr...@Kingland.com>]

Success!

After much hacking, I was finally able to get DB PSML working.  There were a
couple of things it seemed I needed to do in order to get it to work that
sort of seem like they should be the standard way of handling.  This is in
addition to the changes I had to make to Village BLOB support.

The table definition that I used for user profile (as an example) looks like
this:

CREATE TABLE JETSPEED_USER_PROFILE
(
    PSML_ID INT GENERATED BY DEFAULT AS IDENTITY,
    USER_NAME VARCHAR (32) NOT NULL,
    MEDIA_TYPE VARCHAR (99) NOT NULL,
    LANGUAGE VARCHAR (2) NOT NULL,
    COUNTRY VARCHAR (2) NOT NULL,
    PAGE VARCHAR (99) NOT NULL,
    PROFILE BLOB (16777215),
    PRIMARY KEY(PSML_ID),
    UNIQUE (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

This is based on the sources that I found in CVS for DB2.  In order for the
columns marked unique to be unique, they must be not null.  Unfortunately,
during PSML import, a number of PSML instances are attempted to be written
to the DB with NULL values.  The DB does not like that.  In order to combat
this problem, I hacked JetspeedUserProfilePeer.doInsertOrUpdate as follows
(added the null checks):

    private void doInsertOrUpdate(Profile profile, int operation) 
                         throws Exception 
    {
        JetspeedUserProfile userProfile = new JetspeedUserProfile();
        DatabasePsmlManagerService service =
(DatabasePsmlManagerService)PsmlManager.getService();

        userProfile.setUserName(profile.getUser().getUserName());
        userProfile.setMediaType(profile.getMediaType());
        userProfile.setLanguage((profile.getLanguage() == null) ? "" :
profile.getLanguage());
        userProfile.setCountry((profile.getCountry() == null) ? "" :
profile.getCountry());
        userProfile.setPage(profile.getName());
        userProfile.setProfile(DBUtils.portletsToBytes(
                                        profile.getDocument().getPortlets(),
service.getMapping()));

This works, but I'm concerned that that is not the correct approach.  Can
anyone tell me what the correct approach should be?  If we are going to
require columns to be non-null, it seems we should make sure that they are
not.  I encountered this problem in all of the Jetspeed profile peer
classes:

JetspeedAnonProfilePeer
JetspeedGroupProfilePeer
JetspeedRoleProfilePeer
JetspeedUserProfilePeer

In fact these methods all look almost identical.  I see that it was
autogenerated by Torque, so maybe that can't be controlled.  It certainly
seems like some refactoring to pull up some of the contents of these methods
would be nice.

My final question about all of this concerns the data type for the profile
data.  Does this really need to be a BLOB?  Since we already have a String,
wouldn't it be easier and faster to write it out as a VARCHAR instead of
converting to and from bytes?

Thanks for any insights,
Craig

Craig Setera
Sun Certified Java Programmer/Developer/Web Component Developer
Senior Software Architect, Kingland Systems Corporation
craig.setera@kingland.com  (507) 536-3605


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>