Improving Openfire's SQL schema

Bpt · September 27, 2019, 9:49am

Hello,

I’m wondering if it would be possible to improve Openfire SQL schema on some points:

Increasing the size of some field, especially ‘username’ from varchar(64) to varchar(128) (or more)
Adding foreign keys for all fields that reference the same object (example given: foreign keys for all username fields)

What do you think of this ?

guus · September 27, 2019, 12:03pm

I’m not sure if we should add foreign keys, as that might break a lot of code (it shouldn’t, but it probably will). I see no issue with changing column definitions though! I’d welcome a pull request!

Bpt · September 27, 2019, 12:16pm

Ok sure, I will do the PR.

akrherz · September 27, 2019, 6:08pm

In my mind, the biggest issue would be having robust schema migration scripts that would gracefully handle people’s current openfire database. Since the current database does not enforce such foreign keys, adding them now may be painful. For example, would the upgrade script remove any user content from a given table if that user did not exist in the base user’s table? You also would have to support this for all openfire supported databases. This gets painful fast

Bpt · March 17, 2020, 1:10pm

In the end, if you add any foreign keys (especially on group/user tables) then lot’s of problems occur. Especially as we are now using JDBC*providers and thus they (group, user) are not in Openfire’s DB. So with this in mind, adding foreign keys is problematic.

Renato6 · March 23, 2020, 8:04pm

One possible way to alleviate the implementation of FK would be to do some house keeping of the existing database. But, for this to be possible, we need to understand the logical design of the database.

To that end I embarked on a new task…

I am currently running OF 4.5.1 on a mysql/MariaDB backend.

In the absence of an ER diagram, I did the next best thing:

Using phpMyAdmin, I exported the the entire structure of the database
Loaded the data into an XLS
Rearranged the data so that is it sorted my column name and table name

My assumption here is that tables with same column name and data type are related. It is easy to find the PK since they are flagged as such.

I ended up with this:

Table Name	PK	Column	Type	Null
ofPubsubDefaultConf	N	accessModel	varchar(10)	No
ofPubsubNode	N	accessModel	varchar(10)	No
				
ofGroupUser	Y	administrator	tinyint(4)	No
fpQueueAgent	N	administrator	int(11)	Yes
				
ofMucAffiliation	N	affiliation	tinyint(4)	No
ofPubsubAffiliation	N	affiliation	varchar(10)	No
				
fpAgent	N	agentID	bigint(20)	No
				
fpAgent	Y	agentJID	varchar(255)	No
fpAgentSession	N	agentJID	varchar(255)	No
				
ofMucRoom	N	allowpm	tinyint(4)	Yes
				
ofRoster	N	ask	tinyint(4)	No
				
ofPubsubDefaultConf	N	associationPolicy	varchar(15)	No
ofPubsubNode	N	associationPolicy	varchar(15)	Yes
				
ofPubsubNodeJIDs	N	associationType	varchar(20)	No
				
fpQueue	N	backupQueue	bigint(20)	Yes
				
ofConParticipant	N	bareJID	varchar(200)	No
				
ofMessageArchive	N	body	text	Yes
ofMucConversationLog	N	body	text	Yes
				
ofPubsubNode	N	bodyXSLT	varchar(100)	Yes
				
ofBookmark	Y	bookmarkID	bigint(20)	No
ofBookmarkPerm	Y	bookmarkID	bigint(20)	No
ofBookmarkProp	Y	bookmarkID	bigint(20)	No
				
ofBookmark	N	bookmarkName	varchar(255)	No
				
ofBookmarkPerm	Y	bookmarkType	tinyint(4)	No
ofBookmark	N	bookmarkType	varchar(50)	No
				
ofBookmark	N	bookmarkValue	varchar(255)	No
				
ofRRDs	N	bytes	mediumblob	Yes
				
ofMucRoom	N	canChangeNick	tinyint(4)	No
				
ofMucRoom	N	canChangeSubject	tinyint(4)	No
				
ofMucRoom	N	canDiscoverJID	tinyint(4)	No
				
ofMucRoom	N	canInvite	tinyint(4)	No
				
ofMucRoom	N	canRegister	tinyint(4)	No
				
fpSession	N	caseID	varchar(20)	Yes
				
ofPubsubNode	N	configSubscription	tinyint(4)	No
				
ofConversation	Y	conversationID	bigint(20)	No
ofConParticipant	N	conversationID	bigint(20)	No
ofMessageArchive	N	conversationID	bigint(20)	No
				
fpWorkgroup	N	creationDate	varchar(15)	No
ofMucRoom	N	creationDate	char(15)	No
ofOffline	N	creationDate	char(15)	No
ofPubsubItem	N	creationDate	char(15)	No
ofPubsubNode	N	creationDate	char(15)	No
ofUser	N	creationDate	char(15)	No
				
ofPubsubNode	N	creator	varchar(255)	No
				
ofPubsubNode	N	dataformXSLT	varchar(100)	Yes
				
fpChatSetting	N	defaultValue	text	Yes
				
ofPubsubSubscription	N	deliver	tinyint(4)	No
				
ofPubsubDefaultConf	N	deliverPayloads	tinyint(4)	No
ofPubsubNode	N	deliverPayloads	tinyint(4)	No
				
fpChatSetting	N	description	varchar(255)	Yes
fpDispatcher	N	description	varchar(255)	Yes
fpQueue	N	description	varchar(255)	Yes
fpWorkgroup	N	description	varchar(255)	Yes
ofGroup	N	description	varchar(255)	Yes
ofMucRoom	N	description	varchar(255)	Yes
ofMucService	N	description	varchar(255)	Yes
ofPubsubNode	N	description	varchar(255)	Yes
				
ofSecurityAuditLog	N	details	text	Yes
				
ofPubsubSubscription	N	digest	tinyint(4)	No
				
ofPubsubSubscription	N	digest_frequency	int(11)	No
				
fpWorkgroup	N	displayName	varchar(50)	Yes
				
ofMucMember	N	email	varchar(100)	Yes
ofUser	N	email	varchar(100)	Yes
				
fpOfflineSetting	N	emailAddress	varchar(255)	Yes
				
ofMucRoom	N	emptyDate	char(15)	Yes
				
ofProperty	N	encrypted	int(11)	Yes
				
ofUser	N	encryptedPassword	varchar(255)	Yes
				
fpSession	N	endTime	char(15)	No
ofUserFlag	N	endTime	char(15)	Yes
				
ofSecurityAuditLog	N	entryStamp	bigint(20)	No
				
ofPubsubSubscription	N	expire	char(15)	Yes
				
ofMucMember	N	faqentry	varchar(100)	Yes
				
ofMucMember	N	firstName	varchar(100)	Yes
				
ofMessageArchive	N	fromJID	varchar(255)	No
				
ofMessageArchive	N	fromJIDResource	varchar(100)	Yes
				
fpQueueGroup	Y	groupName	varchar(50)	No
ofGroup	Y	groupName	varchar(50)	No
ofGroupProp	Y	groupName	varchar(50)	No
ofGroupUser	Y	groupName	varchar(50)	No
ofRosterGroups	N	groupName	varchar(255)	No
				
userStatusHistory	Y	historyID	bigint(20)	No
				
ofPubsubItem	Y	id	varchar(100)	No
ofPubsubSubscription	Y	id	varchar(100)	No
ofRRDs	Y	id	varchar(100)	No
ofID	N	id	bigint(20)	No
				
ofID	Y	idType	int(11)	No
				
ofPubsubSubscription	N	includeBody	tinyint(4)	No
				
ofPrivacyList	N	isDefault	tinyint(4)	No
				
ofConversation	N	isExternal	tinyint(4)	No
				
ofBookmark	N	isGlobal	int(11)	No
				
ofMucService	N	isHidden	tinyint(4)	No
				
ofUser	N	iterations	int(11)	Yes
				
ofProperty	N	iv	char(24)	Yes
				
fpWorkgroupRoster	Y	jid	text	No
ofMucAffiliation	Y	jid	text	No
ofMucMember	Y	jid	text	No
ofPubsubAffiliation	Y	jid	varchar(255)	No
ofPubsubNodeJIDs	Y	jid	varchar(255)	No
fpWorkgroup	N	jid	varchar(255)	No
ofPubsubItem	N	jid	varchar(255)	No
ofPubsubSubscription	N	jid	varchar(255)	No
ofRoster	N	jid	varchar(1024)	No
				
ofConParticipant	N	jidResource	varchar(100)	No
				
ofConParticipant	N	joinedDate	bigint(20)	No
				
fpAgentSession	N	joinTime	char(15)	No
				
ofPubsubSubscription	N	keyword	varchar(200)	Yes
				
fpChatSetting	N	label	varchar(100)	Yes
				
ofPubsubDefaultConf	N	language	varchar(255)	Yes
ofPubsubNode	N	language	varchar(255)	Yes
				
ofConversation	N	lastActivity	bigint(20)	No
				
fpWorkgroupRoster	N	lastContact	varchar(15)	Yes
				
userStatus	N	lastIpAddress	char(45)	No
userStatusHistory	N	lastIpAddress	char(45)	No
				
userStatus	N	lastLoginDate	char(15)	No
userStatusHistory	N	lastLoginDate	char(15)	No
				
userStatus	N	lastLogoffDate	char(15)	Yes
userStatusHistory	N	lastLogoffDate	char(15)	No
				
ofMucMember	N	lastName	varchar(100)	Yes
				
fpSearchIndex	N	lastOptimization	char(15)	No
				
fpSearchIndex	N	lastUpdated	char(15)	No
				
ofPubsubDefaultConf	Y	leaf	tinyint(4)	No
ofPubsubNode	N	leaf	tinyint(4)	No
				
ofConParticipant	N	leftDate	bigint(20)	Yes
				
fpAgentSession	N	leftTime	char(15)	Yes
				
ofPrivacyList	N	list	text	No
				
ofMucRoom	N	lockedDate	char(15)	No
				
ofMucRoom	N	logEnabled	tinyint(4)	No
				
ofMucConversationLog	N	logTime	char(15)	No
				
fpAgent	N	maxchats	int(11)	No
fpQueue	N	maxchats	int(11)	No
fpWorkgroup	N	maxchats	int(11)	No
				
ofPubsubDefaultConf	N	maxItems	int(11)	No
ofPubsubNode	N	maxItems	int(11)	Yes
				
ofPubsubDefaultConf	N	maxLeafNodes	int(11)	No
ofPubsubNode	N	maxLeafNodes	int(11)	Yes
				
ofPubsubDefaultConf	N	maxPayloadSize	int(11)	No
ofPubsubNode	N	maxPayloadSize	int(11)	Yes
				
ofMucRoom	N	maxUsers	int(11)	No
				
ofMucRoom	N	membersOnly	tinyint(4)	No
				
ofConversation	N	messageCount	int(11)	No
				
ofOffline	Y	messageID	bigint(20)	No
ofMessageArchive	N	messageID	bigint(20)	Yes
ofMucConversationLog	N	messageID	bigint(20)	No
				
ofOffline	N	messageSize	int(11)	No
				
fpSessionMetadata	N	metadataName	varchar(200)	No
				
fpSessionMetadata	N	metadataValue	text	No
				
fpAgent	N	minchats	int(11)	No
fpQueue	N	minchats	int(11)	No
fpWorkgroup	N	minchats	int(11)	No
				
ofMucRoom	N	moderated	tinyint(4)	No
				
fpWorkgroup	N	modes	int(11)	No
				
fpWorkgroup	N	modificationDate	varchar(15)	No
ofMucRoom	N	modificationDate	char(15)	No
ofPubsubNode	N	modificationDate	char(15)	No
ofUser	N	modificationDate	char(15)	No
				
ofSecurityAuditLog	Y	msgID	bigint(20)	No
				
fpAgentProp	Y	name	varchar(100)	No
fpDispatcherProp	Y	name	varchar(100)	No
fpQueueProp	Y	name	varchar(100)	No
fpSessionProp	Y	name	varchar(100)	No
fpSetting	Y	name	varchar(100)	No
fpWorkgroupProp	Y	name	varchar(100)	No
ofBookmarkPerm	Y	name	varchar(255)	No
ofBookmarkProp	Y	name	varchar(100)	No
ofGroupProp	Y	name	varchar(100)	No
ofMucRoom	Y	name	varchar(50)	No
ofMucRoomProp	Y	name	varchar(100)	No
ofMucServiceProp	Y	name	varchar(100)	No
ofPrivacyList	Y	name	varchar(100)	No
ofProperty	Y	name	varchar(100)	No
ofUserFlag	Y	name	varchar(100)	No
ofUserProp	Y	name	varchar(100)	No
ofVersion	Y	name	varchar(50)	No
fpAgent	N	name	varchar(255)	Yes
fpChatSetting	N	name	varchar(100)	Yes
fpDispatcher	N	name	varchar(50)	Yes
fpQueue	N	name	varchar(50)	No
ofPubsubNode	N	name	varchar(50)	Yes
ofUser	N	name	varchar(100)	Yes
				
fpSetting	N	namespace	varchar(255)	No
				
ofMucRoom	N	naturalName	varchar(255)	No
				
ofRoster	N	nick	varchar(255)	Yes
				
ofConParticipant	N	nickname	varchar(255)	Yes
ofMucConversationLog	N	nickname	varchar(255)	Yes
ofMucMember	N	nickname	varchar(255)	Yes
				
ofPushNotiService	N	node	varchar(1024)	No
ofSecurityAuditLog	N	node	varchar(255)	No
				
ofPubsubAffiliation	Y	nodeID	varchar(100)	No
ofPubsubItem	Y	nodeID	varchar(100)	No
ofPubsubNode	Y	nodeID	varchar(100)	No
ofPubsubNodeJIDs	Y	nodeID	varchar(100)	No
ofPubsubSubscription	Y	nodeID	varchar(100)	No
ofPubsubNodeGroups	N	nodeID	varchar(100)	No
				
fpSession	N	notes	text	Yes
				
ofPubsubDefaultConf	N	notifyConfigChanges	tinyint(4)	No
ofPubsubNode	N	notifyConfigChanges	tinyint(4)	No
				
ofPubsubDefaultConf	N	notifyDelete	tinyint(4)	No
ofPubsubNode	N	notifyDelete	tinyint(4)	No
				
ofPubsubDefaultConf	N	notifyRetract	tinyint(4)	No
ofPubsubNode	N	notifyRetract	tinyint(4)	No
				
fpQueueAgent	Y	objectID	bigint(20)	No
				
fpQueueAgent	Y	objectType	int(11)	No
				
fpDispatcher	N	offerTimeout	int(11)	No
fpWorkgroup	N	offerTimeout	int(11)	No
				
ofPresence	N	offlineDate	char(15)	No
				
ofPresence	N	offlinePresence	text	Yes
				
fpOfflineSetting	N	offlineText	text	Yes
				
userStatus	N	online	tinyint(4)	No
				
ofPushNotiService	N	options	text	Yes
				
fpQueue	N	overflow	int(11)	No
				
ofPubsubSubscription	N	owner	varchar(255)	No
				
fpAgentProp	Y	ownerID	bigint(20)	No
fpDispatcherProp	Y	ownerID	bigint(20)	No
fpQueueProp	Y	ownerID	bigint(20)	No
fpWorkgroupProp	Y	ownerID	bigint(20)	No
				
ofPubsubNode	N	parent	varchar(100)	Yes
				
ofPubsubItem	N	payload	mediumtext	Yes
				
ofPubsubNode	N	payloadType	varchar(100)	Yes
				
ofExtComponentConf	N	permission	varchar(10)	No
ofRemoteServerConf	N	permission	varchar(10)	No
				
ofPubsubDefaultConf	N	persistItems	tinyint(4)	No
ofPubsubNode	N	persistItems	tinyint(4)	Yes
				
ofUser	N	plainPassword	varchar(32)	Yes
				
userStatus	N	presence	char(15)	Yes
				
ofPubsubDefaultConf	N	presenceBased	tinyint(4)	No
ofPubsubNode	N	presenceBased	tinyint(4)	No
				
ofSASLAuthorized	Y	principal	text	No
				
fpQueue	N	priority	int(11)	No
				
fpAgentProp	N	propValue	text	No
fpDispatcherProp	N	propValue	text	No
fpQueueProp	N	propValue	text	No
fpSessionProp	N	propValue	text	No
fpWorkgroupProp	N	propValue	mediumtext	No
ofBookmarkProp	N	propValue	text	No
ofGroupProp	N	propValue	text	No
ofMucRoomProp	N	propValue	text	No
ofMucServiceProp	N	propValue	text	No
ofProperty	N	propValue	text	No
ofUserProp	N	propValue	text	No
				
ofMucRoom	N	publicRoom	tinyint(4)	No
				
ofPubsubDefaultConf	N	publisherModel	varchar(15)	No
ofPubsubNode	N	publisherModel	varchar(15)	No
				
fpRouteRule	N	query	text	Yes
				
fpDispatcher	Y	queueID	bigint(20)	No
fpQueue	Y	queueID	bigint(20)	No
fpQueueAgent	Y	queueID	bigint(20)	No
fpQueueGroup	Y	queueID	bigint(20)	No
fpRouteRule	N	queueID	bigint(20)	No
				
fpSession	N	queueWaitTime	bigint(20)	Yes
				
ofRosterGroups	Y	rank	tinyint(4)	No
				
ofRoster	N	recv	tinyint(4)	No
				
fpOfflineSetting	N	redirectPage	varchar(255)	Yes
				
ofRemoteServerConf	N	remotePort	int(11)	Yes
				
ofPubsubDefaultConf	N	replyPolicy	varchar(15)	Yes
ofPubsubNode	N	replyPolicy	varchar(15)	Yes
				
fpDispatcher	N	requestTimeout	int(11)	No
fpWorkgroup	N	requestTimeout	int(11)	No
				
userStatus	Y	resource	varchar(64)	No
userStatusHistory	N	resource	varchar(64)	No
				
ofMucRoom	N	rolesToBroadcast	tinyint(4)	No
				
ofConversation	N	room	varchar(255)	Yes
				
ofMucAffiliation	Y	roomID	bigint(20)	No
ofMucMember	Y	roomID	bigint(20)	No
ofMucRoomProp	Y	roomID	bigint(20)	No
ofMucConversationLog	N	roomID	bigint(20)	No
ofMucRoom	N	roomID	bigint(20)	No
				
ofMucRoom	N	roomPassword	varchar(50)	Yes
				
ofPubsubNodeGroups	N	rosterGroup	varchar(100)	No
				
ofRoster	Y	rosterID	bigint(20)	No
ofRosterGroups	Y	rosterID	bigint(20)	No
				
fpRouteRule	N	rulePosition	int(11)	No
				
ofUser	N	salt	varchar(32)	Yes
				
fpWorkgroup	N	schedule	text	Yes
				
ofExtComponentConf	N	secret	varchar(255)	Yes
				
ofMucConversationLog	N	sender	text	No
				
ofPubsubDefaultConf	N	sendItemSubscribe	tinyint(4)	No
ofPubsubNode	N	sendItemSubscribe	tinyint(4)	No
				
ofMessageArchive	N	sentDate	bigint(20)	No
				
ofUser	N	serverKey	varchar(32)	Yes
				
ofPushNotiService	N	service	varchar(1024)	No
				
ofMucRoom	Y	serviceID	bigint(20)	No
ofMucServiceProp	Y	serviceID	bigint(20)	No
ofPubsubAffiliation	Y	serviceID	varchar(100)	No
ofPubsubDefaultConf	Y	serviceID	varchar(100)	No
ofPubsubItem	Y	serviceID	varchar(100)	No
ofPubsubNode	Y	serviceID	varchar(100)	No
ofPubsubNodeJIDs	Y	serviceID	varchar(100)	No
ofPubsubSubscription	Y	serviceID	varchar(100)	No
ofMucService	N	serviceID	bigint(20)	No
ofPubsubNodeGroups	N	serviceID	varchar(100)	No
				
fpSession	Y	sessionID	varchar(31)	No
fpSessionProp	Y	sessionID	varchar(31)	No
fpAgentSession	N	sessionID	varchar(31)	No
fpSessionMetadata	N	sessionID	varchar(31)	No
				
ofPubsubSubscription	N	showValues	varchar(30)	Yes
				
ofMessageArchive	N	stanza	text	Yes
ofMucConversationLog	N	stanza	text	Yes
ofOffline	N	stanza	text	No
				
ofConversation	N	startDate	bigint(20)	No
				
fpSession	N	startTime	char(15)	No
ofUserFlag	N	startTime	char(15)	Yes
				
fpSession	N	state	int(11)	No
ofPubsubSubscription	N	state	varchar(15)	No
				
fpSession	N	status	char(15)	Yes
fpWorkgroup	N	status	int(11)	No
				
ofUser	N	storedKey	varchar(32)	Yes
				
ofRoster	N	sub	tinyint(4)	No
				
ofExtComponentConf	Y	subdomain	varchar(255)	No
ofMucService	Y	subdomain	varchar(255)	No
				
fpOfflineSetting	N	subject	varchar(255)	Yes
ofMucConversationLog	N	subject	varchar(255)	Yes
ofMucRoom	N	subject	varchar(100)	Yes
				
ofPubsubSubscription	N	subscriptionDepth	tinyint(4)	No
				
ofPubsubDefaultConf	N	subscriptionEnabled	tinyint(4)	No
ofPubsubNode	N	subscriptionEnabled	tinyint(4)	No
				
ofPubsubSubscription	N	subscriptionType	varchar(10)	No
				
ofSecurityAuditLog	N	summary	varchar(255)	No
				
ofMessageArchive	N	toJID	varchar(255)	No
				
ofMessageArchive	N	toJIDResource	varchar(100)	Yes
				
fpSession	N	transcript	text	Yes
				
fpChatSetting	N	type	int(11)	Yes
				
ofRRDs	N	updatedDate	bigint(20)	No
				
ofMucMember	N	url	varchar(100)	Yes
				
ofMucRoom	N	useReservedNick	tinyint(4)	No
				
fpSession	N	userID	varchar(255)	No
				
ofGroupUser	Y	username	varchar(100)	No
ofOffline	Y	username	varchar(64)	No
ofPresence	Y	username	varchar(64)	No
ofPrivacyList	Y	username	varchar(64)	No
ofSASLAuthorized	Y	username	varchar(64)	No
ofUser	Y	username	varchar(64)	No
ofUserFlag	Y	username	varchar(64)	No
ofUserProp	Y	username	varchar(64)	No
ofVCard	Y	username	varchar(64)	No
userStatus	Y	username	varchar(64)	No
ofPushNotiService	N	username	varchar(64)	No
ofRoster	N	username	varchar(64)	No
ofSecurityAuditLog	N	username	varchar(64)	No
userStatusHistory	N	username	varchar(64)	No
				
fpChatSetting	N	value	text	Yes
fpSetting	N	value	text	No
				
ofVCard	N	vcard	mediumtext	No
				
ofVersion	N	version	int(11)	No
				
ofExtComponentConf	N	wildcard	tinyint(4)	No
				
fpOfflineSetting	Y	workgroupID	bigint(20)	No
fpQueue	Y	workgroupID	bigint(20)	No
fpSearchIndex	Y	workgroupID	bigint(20)	No
fpWorkgroup	Y	workgroupID	bigint(20)	No
fpWorkgroupRoster	Y	workgroupID	bigint(20)	No
fpRouteRule	N	workgroupID	bigint(20)	No
fpSession	N	workgroupID	bigint(20)	No
				
fpSetting	Y	workgroupName	varchar(100)	No
fpChatSetting	N	workgroupNode	varchar(100)	Yes
				
ofRemoteServerConf	Y	xmppDomain	varchar(255)	No

This extract would suggest the following:
For example, table fpAgent has a PK on AgentJID and table fpAgentSession “could” have a FK on the same column name.

Also, looking at the data, I noticed the following:

In some cases, columns with the same name have different data types. Is this indicative that there exists no relationship with other table? Or was it an oversight?
A few of the table have the same column name and they are ALL PKs. Again, was this by design or should the tables be consolidated?

And now the obvious question. Is the official ER diagram available anywhere?

Thanks
Renato

akrherz · March 23, 2020, 9:08pm

Greetings, Thanks for your efforts here, but understand that what you currently see within openfire’s database schema is not pretty due to a number of reasons.

Long term (10-20 years) development with a lowest common denominator of HSQL always supported, so that sometimes limited what was possible within SQL.
Generally no ORM usage
Many of the database changes were low blast-radius changes that added hacks upon hacks to keep things going and backwards compatable and even sometimes forwards compatable so that you could downgrade Openfire without downgrading the database.
We’re all volunteers here and scratch-where-it-itches. Taking on database schema updates is not fun as none of us are experts in all the databases Openfire currently supports. You can’t just fix things for one database and ignore the others, they all have to come along for the ride together.

Renato6 · March 23, 2020, 11:02pm

Thanks for the quick reply. I am not trying to beat a dead horse!

From my standpoint, the concept of a relational database is the common denominator here. Some vendors will call the datatype VARCHAR other vendors will call is VARCHAR2. In the end, it is a variable length character field. I am not trying to trivialize the subject since I have experience with both mysql, Oracle and Db2.

I would be more than happy to assist with the revamping of the logical design, but for that to be possible I would have to better understand the relationship among the tables. A workaround to the Referential Integrity could be (given the right circumstances) to create a series of database triggers which would fire after a given event (i.e. a parent record is deleted, the trigger(s) would fire and delete all corresponding children records from other tables). This would not affect the application logic.

Renato

akrherz · March 23, 2020, 11:13pm

Please feel free to give it a shot, but implementing triggers properly against all the databases we support does not sound like fun. I think the first step here would be to add a mechanism that all databases are somehow tested in CI.

Bpt · March 24, 2020, 9:14am

Hello Renato, so to clarify my previous message, one of the difficulty you will face trying to add FK into Openfire’s schema (which is the “goal” of a -relationnal- DB I agree) is the possibilty to delegate to external databases some objects like users or groups
.
For example if you do so for the users, then the table ofUser in Openfire’s db will be empty.
So if before that you added some foreign keys on username between ofuser and ofproperties (example), you won’t be able to add any user properties and you will basically prevent some functionnalities to work at all (eg: user properties, offline messages, presence…).
And I think it will be the same story if you use LDAP providers.

In my opinion this is a major blocker to FK in Openfire’schema.

Renato6 · March 24, 2020, 12:20pm

Let me replay back what I believe the challenges are…

Before any RI can be even considered, we need to make sure that the right relationships are in place i.e. every parent record has >= 0 children records. Conversely, every child records needs to have one parent record.

In other words, the repository would have to be sanitized FIRST with a script what would identify all broken relationships and fix them.

Implementing a PF-FK structure will dictate the sequence that the inserts have to be executed. A parent would have to be inserted BEFORE any children records can be inserted. Today that sequence is not critical since the RDBMS is non enforcing RI.

I noticed that my current schema “appears” to have several orphan records. Suggesting that the “deletes” are (or at some point i.e. prior versions of OF) incompletely handled. That is why I was suggesting triggers. Now, triggers will add overhead to the database, hence they are not the “silver bullet”. More of a stop gap to keep the repository clean.

In both scenarios 1 & 2, an understanding of the logical design is needed before either one can be implemented.

As for LDAP, I am afraid I cannot speak half intelligently about that

Renato
Renato