powered by Jive Software

SELECT ofUser with alternate id

I’d like to query the ofUser table to get a specific user. But, the wrinkle is that I need to specify an alternate id, for example a Student ID. There are two problems I have here; of course, let me know if you see more!

First, I’m not sure where to store this additional column Student ID. I could modify the ofUser table (yuck!). I’ve also looked at ofUserProp table with some interest.

Second, if I happen to use something like ofUserProp, how do I populate the Student ID easily. I know I can write SQL for a bulk import. But, I expected to find somplace within the Openfire Admin console where I could add items to the ofUserProp table. Am I missing anything?

Thanks for any suggestions/help.

i wouldn’t modify the tables out of fear that it may break some queries… INSERT queries mainly i guess… depending how they were written.

Instead, I’d create a new table and store the student_id’s there…something like

CREATE TABLE student_id

(

id varchar(whatever) REFERENCES ofUser,

student_id varchar(whatever) UNIQUE

);

I’m no DBA, but i think this is a more “normalized” solution anyways… and you could modify any queries to select from this table if or when needed…

Off the top of my head, i’m not aware of a place in openfire conifg that allows you to add to tables unless you install the DB plugin… but that just lets you run any query on the DB from the openfire gui…

@Jason, thanks for the reply. Here’s the route I took;

  • I used the ofUserProp table, inserting with a statement like the following for each user

INSERT INTO dbo.ofUserProp VALUES(‘joesmith’,‘StudentID’,‘13-01234’);

  • From my custom application, I use the StudentID to look up the Username
  • I use the Username as the receiver for the XMPP message

My question really is, what is the best way to maintain these values in ofUserProp. I can edit them easily enough with SQL Server tools. I was hoping that the Openfire Admin tool had some kind of user interface for editing the table ofUserProp, much like it has for Server Properties.