powered by Jive Software

Modification of the database schema

Hi,

I’'m writing a plugin for Jive Messenger and I need to modify the embedded database schema. To be more precise, the only thing I want is to add a field in the jiveRoster table.

Somebody have suggestions about the way I should proceed?

Thanks in advance

JA

Message was edited by:

ja

Hi JA,

Alterting a default table like that might become an issue further down the line should the schema change. Could you make your plugin work if you created your own table and keyed off of the jiveRoster primary key? If not, you could always just issue an “alter table” statement the first time plugin is used, and then use a property entry, which the plugin would look for everytime it starts up to see if it has already altered the table. Or, you could query the tables meta-data and see if the column exists.

Hope that helps,

Ryan

Hi Ryan,

At first, thanks for your help: I really appreciate.

Could you make your plugin work if you created your

own table and keyed off of the jiveRoster primary

key?

Yes, it would be ok but I really don’'t know where (in which file) and how I could do this. May you please give me more information?

Thanks again

JA

Hi JA,

Could you give me a few more details on what exactly it is that you’'re trying to accomplish?

Thanks,

Ryan

Hi Ryan,

What I’'m trying to accomplish is to add an integer field (for each roster) that indicates the level number of a contact in the social graph of the user. This table will be used with an algorithm (quite finished) that will build the user contacts list(that become a social graph list) while traversing the contacts list of his own contacts. Obviously, other special functions will have to be implemented but the final objective is to use the jive server (with my plugin) in collaboration with future special IM clients created by myself.

Thanks in advance

JA

Hi JA,

Sounds like an interesting project that you’‘re working on. How were you planning to popluate this additional field that you’‘re wanting to track? Was this going to be handled via one of your custom IM clients? I’'m just trying to picture the sequence of events that are going to be involved.

Thanks,

Ryan

Hi Ryan,

How were you planning to popluate this

additional field that you’'re wanting to track?

For each level 2 roster created for a main user (added like regular contacts, but with the algo), a new table with the roster item id and the level number of the concerned contact will be created. I don’'t have access right now to my code but the main pattern of the algorithm look like this: for a user, the algo obtains all the roster items. After that, for each users concerned by these rosters (the direct contacts of the main user), it find their roster items and add them in the list of the main user: they are level 2 contacts and the custom tables are created for them. They are level 2 contacts since they are contacts of the direct contacts of the main user. This principle will probably be expand to include level 3 and 4 contacts.

Was this going to be handled via one of your custom IM

clients?

Yes, each instance of my custom client will make calls to the server. The algorithm that will populate the list of the users will be on the server but it’'s the client that will ask it when to do it and how to do it.

This is a general description of the way it works: the server will also contains functions that will do the same kind of work but that will give more control to the user on how to build the social graph.

The problem I’‘m experiencing is that I don’‘t know which file to edit to add a new table in the database schema. I also don’'t know how to create effective instances of this table and how to set/get an existing table from my plugin file.

Is it enough clear, can you help me?

Thanks

JA

Message was edited by:

ja

Hi JA,

Yes, I think things are pretty clear now. Since you’‘ll be managing this new table via your plugin that’‘ll make things easier. Below is some same code that should help get you started, you could just put it directly in plugin. The only trick as I mentioned before is that you’'ll either have to set a property variable or query the database each time the plugin starts up to see if your table has already been created:

public void createTable() {
        String CREATE_TABLE =
            "CREATE TABLE rosterTracker ( " +
                "rosterID              BIGINT          NOT NULL, " +
                "jid                   VARCHAR(1024)   NOT NULL, " +
                "CONSTRAINT rosterTracker_pk PRIMARY KEY (rosterID))";
                //todo you may want to cosider also adding a index to speed queries up.
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(CREATE_TABLE);
            pstmt.execute();                    }
        catch (Exception e) {
            Log.error(e);
        }
        finally {
            close(con);
            close(pstmt);
        }
    }
        public void updateTable(int rosterID, int level) {                String UPDATE_NAME =
            "UPDATE rosterTracker SET level=? WHERE rosterID=?";
                Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(UPDATE_NAME);
            pstmt.setInt(1, level);
            pstmt.setInt(2, rosterID);
            pstmt.execute();
        }
        catch (Exception e) {
            Log.error(e);
        }
        finally {
            close(con);
            close(pstmt);
        }
    }
        private void close(Connection con) {
        try {
            if (con != null) {
                con.close();
            }
        } catch (Exception e) {
            Log.error(e);
        }
    }
        private void close(PreparedStatement pstmt) {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (Exception e) {
            Log.error(e);
        }
    }

Please let me know if this helps you at all.

Thanks,

Ryan

Hi Ryan,

Thank you for your precious help :). I will let you know soon how things are going on.

Thanks again!

JA