Edit zxchat database
Results 1 to 3 of 3

Thread: Edit zxchat database

  1. #1
    Active Member
    Join Date
    Sep 2015
    Posts
    5

    Edit zxchat database

    When I initially setup buddy lists globally using the very cool doMakeBuddies everyone was added as a member of a group called 'zimbra'. Since that time something has changed and now when I update everyone with doMakeBuddies the new staff users are added without a group 'zimbra' reference.

    At this point it doesn't really matter how or why . . .

    I could re-initialize the zxchat database and re-populate it BUT I have a lot of staff that have changed the 'nickname' field to include the staff extension and I'd rather NOT wipe that out for them.

    What I'd really like is to globally edit the database and remove all references to the group 'zimbra' so everyone's buddy list will show correctly with everyone listed alphabetically instead of some under the 'zimbra' group and some under no group.

    Even better would be an option to tweak doMakeBuddies to combine Zimbra's 'Display name' and Zimbra's 'Phone' field so that everyone would have "John Smith - ext 2403" or something similar.

    Grateful for any thoughts?
    Rois

  2. #2
    ZeXtras Community Manager ZeXtras Employee Cine's Avatar
    Join Date
    Apr 2011
    Posts
    2,356
    Hello Rois!
    It seems that the issue you are experiencing is related to a migration from a very old ZeXtras Suite release to version 2.0.x, could you please confirm this?

    About database editing: ZeXtras Suite does not include the possibility to directly act on the ZeXtras Chat Database "out of the box" but, while this is completely unsupported and not suggested, you can manually edit the database.

    As of now, ZeXtras Chat uses HSQLDB as its database - specifically hsqldb 2.3.1 in ZeXtras Suite 2.0.3.
    You can download the full hsqldb package, including the client and the appropriate driver, from here.

    Code:
    hsqldb-2.3.1/hsqldb/lib/
    
    sqltool.jar (client)
    hsqldb.jar  (driver)
    The ZxChat database cannot be accessed when ZeXtras Suite is running, so in order to perform any action you'll need to temporarily shut down the mailboxd service by running zmmailboxdctl stop as the "zimbra" user. Once the mailboxd service is fully stopped, the /opt/zimbra/conf/zextras/zxchat/db2.lck lockfile will be deleted.

    I strongly suggest to recursively copy the
    Code:
    /opt/zimbra/conf/zextras/zxchat/
    directory to a different path and to act on the copy (so that, if you wish, you can start the mailboxd service immediately after the copy to reduce the downtime).
    To access the database, enter the hsqldb/lib/ directory within the hsqldb package and run

    Code:
    java -jar sqltool.jar --inlineRc=url=jdbc:hsqldb:file:/path_to_zxchat_copy/db2,user=zextras
    When prompted, enter z1e2x3t4r5a6s as the password.

    The \dt command shows the table list:

    Code:
    sql> \dt
    TABLE_SCHEM  TABLE_NAME
    -----------  ------------
    PUBLIC       DBINFO
    PUBLIC       EVENTMESSAGE
    PUBLIC       RELATIONSHIP
    PUBLIC       STATUS
    PUBLIC       USER
    The 'RELATIONSHIP' table contains all relationship data, while the 'EVENTMESSAGE' one holds all undelivered messages.
    Code:
    sql> SELECT * FROM RELATIONSHIP LIMIT 1;
    ID  USERID  TYPE  BUDDYADDRESS         BUDDYNICKNAME  GROUP
    --  ------  ----  -------------------  -------------  -----
    You can use the standard SQL syntax (refer to the HSQLDB online docs for more info) to update all "zimbra" entries in the GROUP field to " '' " (two dashes) to empty out the field.
    Once the changes are done, shut down the mailboxd service and replace the /opt/zimbra/conf/zextras/zxchat/ directory with the edited copy and start the mailboxd again. Remember, all relationships and undelivered messages created between the two service stops will be lost.

    Have a nice day,
    Cine
    the ZeXtras Team

  3. #3
    Active Member
    Join Date
    Sep 2015
    Posts
    5
    Cine,
    Thanks for the info on manual editing. I'll try that after hours as soon as I can.

    As far as the old version of zxsuite I'm not sure since I don't have the server any more where I first ran it. I first used zxsuite (and chat) when I migrated from Zimbra 6.x to Zimbra 8.0.x but the 'zimbra' group issue didn't show up until I used zxsuite again earlier this year to migrate from Zimbra 8.6.x on one server to Zimbra 8.6.x on a new server box. Hope that helps show a time line.

    Rois

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •