Openfire

Split OF-27 into smaller issues for a better handling.

This patch adjust the ofId table to get a correct serviceID for the first created MUC service and for running systems it checks the ofID table entry with idType=26 if the id is 1, then if updates this and set it to 2, but if not, if check the ofMucService table if there are two entrys with serviceID 1 and get a new serviceID for one of them and copy all rooms from serviceID=1 to this new serviceID.

I do not like having java-code in the core source that will be executed just once. We'll have to maintain this code indefinitely. Ideally, we solve this problem by using the database-update scripts instead. These scripts are 'run-once' in nature, and are therefor better suited for the job.

Work in progress: I'm posting several snippets of SQL that could be part of a solution.

This snippet duplicates the service properties of the first service (serviceID=1) to all other services. A pre-condition is that table ofMucService is in a valid state (meaning: all services have distinct ID's). I did not check all invariants for this snippet yet!

INSERT INTO ofMucServiceProp (
  serviceID, 
  name, 
  propValue) 
SELECT 
  ofMucService.serviceID, 
  ofMucServiceProp.name, 
  ofMucServiceProp.propValue 
FROM 
  ofMucService 
JOIN 
  ofMucServiceProp 
WHERE 
  ofMucService.serviceID <> 1;

This snippet is similar in operation. It duplicates the rooms of the first service (serviceID=1) to all other services. A pre-condition is that table ofMucService is in a valid state (meaning: all services have distinct ID's). I did not check all invariants for this snippet yet!

INSERT INTO ofMucRoom (
  serviceID, 
  roomID, 
  creationDate, 
  modificationDate, 
  name, 
  naturalName, 
  description, 
  lockedDate, 
  emptyDate, 
  canChangeSubject, 
  maxUsers, 
  publicRoom, 
  moderated, 
  membersOnly, 
  canInvite, 
  roomPassword, 
  canDiscoverJID, 
  logEnabled, 
  subject, 
  rolesToBroadcast, 
  useReservedNick, 
  canChangeNick, 
  canRegister) 
SELECT 
  ofMucService.serviceID, 
  ofMucRoom.roomID + max(ofMucRoom.roomID), 
  ofMucRoom.creationDate, 
  ofMucRoom.modificationDate, 
  ofMucRoom.name, 
  ofMucRoom.naturalName, 
  ofMucRoom.description, 
  ofMucRoom.lockedDate, 
  ofMucRoom.emptyDate, 
  ofMucRoom.canChangeSubject, 
  ofMucRoom.maxUsers, 
  ofMucRoom.publicRoom, 
  ofMucRoom.moderated, 
  ofMucRoom.membersOnly, 
  ofMucRoom.canInvite, 
  ofMucRoom.roomPassword, 
  ofMucRoom.canDiscoverJID, 
  ofMucRoom.logEnabled, 
  ofMucRoom.subject, 
  ofMucRoom.rolesToBroadcast, 
  ofMucRoom.useReservedNick, 
  ofMucRoom.canChangeNick, 
  ofMucRoom.canRegister 
FROM 
  ofMucRoom 
JOIN 
  ofMucService 
WHERE 
  ofMucService.serviceID <> 1;

Hmm, snippets above are going to fail if a third service was created - this third service (which will have serviceID=2) will be ok, and should not be modified.

Perhaps this is a better approach. This snippet assumes that the service that was created with the duplicate serviceID of 1 has been renumbered and now has the highest serviceID of all services.

INSERT INTO ofMucServiceProp (
  serviceID, 
  name, 
  propValue) 
SELECT 
  ofMucService.serviceID, 
  ofMucServiceProp.name, 
  ofMucServiceProp.propValue 
FROM 
  ofMucService 
JOIN 
  ofMucServiceProp 
WHERE 
  ofMucService.serviceID = (SELECT max(serviceID) FROM ofMucService WHERE serviceID > 1)
AND
  ofMucServiceProp.serviceID = 1;

This snippet should work in these scenario's:

• There's just one service (there was no bug in this environment): the subquery's WHERE serviceID >1 condition will prevent anything from happening if there's just one service. (Should we replace this with a COUNT() instead?)
• There are two services, one which initially was misnumbered (both services had serviceID=1) but now has been renumbered to the value '2' (which is also the highest serviceID).
• There are more than two services. The second service to be created has been renumbered and has now the highest serviceID.

The same technique can be applied to the second snipped I posted earlier.

Will this code port to other DBMSses than MySQL?

Guss, I agree a SQL script for database-updates would be a cleaner approach for this issue , but we should assure that it works with all supported DBs and I don't have the experience and testing environment to verify it.

Jive managed to verify this in the past, for the existing scripts. I'll have a chat with Gato, see if he can help us out after we've created a script that works on at least on dbms.

Jive managed to verify this in the past, for the existing scripts. I'll have a chat with Gato, see if he can help us out after we've created a script that works on at least on dbms.

Ah, bleh. This is going to take forever. We're currently under some pressure to release the next version of Openfire. Lets be pragmatic and go with Guenther's solution. It is available now and will enable us to move forward.

Guenther, I just checked in your patch, slightly modified to allow the code to be moved outside of the SchemaManager implementation. This way, we at least keep that code clean(er). Could you review my changes, apply modifications where needed and resolve this issue please?

In my opinion it looks good, so I closed the ticket.

I just hit this problem on my 3.6 server. It seems to me that there is a more fundamental database problem if the intention is to allow separate services to have rooms with the same name. If that is what is desired then the then either serviceID needs to be added everywhere roomID occurs or the roomID must be unique across all services. It makes sense in that case to generate the roomID and don't make the end manually do a uniqueness search before creating a room.