BBcode bitfield and other serialized data

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Ideas Centre
User avatar
AmigoJack
Registered User
Posts: 5390
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: BBcode bitfield and other serialized data

Post by AmigoJack » Thu Nov 22, 2018 8:26 am

JoshyPHP wrote:
Tue Nov 20, 2018 7:24 pm
What's the alternative?
All-purpose database columns. My design of storing events and their metadata is creating a table like this:

Code: Select all

CREATE TABLE _trackmatch
( match_id 	INT UNSIGNED NOT NULL AUTO_INCREMENT
, rule_id 	MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 			COMMENT 'matched rule defined by user'
, track_event 	TINYINT UNSIGNED NOT NULL DEFAULT 0 			COMMENT 'occasion/context'
, refer_where 	INT UNSIGNED NOT NULL DEFAULT 0 			COMMENT 'mostly post/topic/celeb'
, refer_by 	INT UNSIGNED NOT NULL DEFAULT 0 			COMMENT 'mostly user/celeb'
, refer_by2 	INT UNSIGNED NOT NULL DEFAULT 0 			COMMENT 'opposite user'
, refer_post 	MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
, refer_forum 	MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
, refer_text 	VARCHAR( 255 ) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''  COMMENT 'matched portions of text comparison'
, refer_text2 	VARCHAR( 255 ) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''  COMMENT 'opposite text comparison'
, track_when 	INT UNSIGNED NOT NULL DEFAULT 0 			COMMENT 'unix timestamp'
, status INT 	UNSIGNED NOT NULL DEFAULT 0 				COMMENT 'processed/viewed/notified'
, match_trace 	VARCHAR( 2000 ) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'discover loops'
, PRIMARY KEY( match_id )
) COMMENT='Log each match';
...and the various events can store everything in those columns:

Code: Select all

// _trackmatch.track_event	 // _where	// _by		// _by2		// _f	// _text	// _text2
( 'NewTopicCreated', 1 );        // topic	topic_user			forum	post_subject	topic_desc
( 'NewPollCreated', 2 );         // topic	topic_user			forum	poll_title	topic_desc
( 'ReplyPosted', 3 );            // post	post_user			forum	post_text
( 'NewCelebCreated', 4 );        // celeb	(user)					celeb_name
( 'CelebToTopicAssociated', 5 ); // topic	celeb				forum
( 'CelebHasNewFan', 6 );         // celeb	(user)					celeb_name
( 'PostGotEdited', 7 );          // post	edit_user	edit_user	forum	post_text	post_subject
( 'TopicWasMoved', 8 );          // topic	(user)		old_forum	forum
( 'PostQuotedUser', 9 );         // post	post_user	quoted_user	forum	post_text	post_subject
( 'PmSentToMe', 10 );            // msg		author					msg_text	msg_subject
( 'DutyStatusChanged', 11 );     // user						duty_text
( 'SubscribeToTopic', 12 );      // topic	user				forum	topic_title	topic_desc
( 'UnsubscribeFromTopic', 13 );  // topic	user				forum	topic_title	topic_desc
( 'BookmarkTopic', 14 );         // topic	user				forum	topic_title	topic_desc
( 'UnbookmarkTopic', 15 );       // topic	user				forum	topic_title	topic_desc
( 'PollVoted', 16 );             // topic	vote_user			forum	poll_title	topic_desc
( 'PosterOfMonthVoted', 17 );    // topic	vote_user			forum	topic_desc
( 'UserHasNewFriend', 18 );      // user	zebra						
( 'UserHasNewFoe', 19 );         // user	zebra						
( 'UserStartedFollowing', 20 );  // user	zebra						
( 'UserFilledRequest', 21 );     // post	poster				forum
( 'UserEarnedRibbon', 22 );      // ribbon	user						
( 'MyReportClosed', 23 );        // report	user		report_user		
( 'NewForumCreated', 24 );       // 						forum	forum_name
( 'SubscribeToForum', 25 );      // 		user				forum
( 'UnsubscribeFromForum', 26 );  // 		user				forum
( 'NewPomCreated', 27 );         // topic	topic_user			forum	topic_desc
( 'LoginAttempt', 28 );          // 		user					ip_address	useragent	
( 'VisitTopicNewUser', 29 );     // topic	user				forum	topic_celebs	topic_desc
( 'NewEnrollment', 30 );         // enroll						usernames	comment
This design is a compromise of not using (any form of) serialization, not being that future proof, and still storing as much data as possible. Both text columns may be overwritten with a matched text portion when using a text comparison as part of the rule, which is then used in the optional text for a potential notification/listing. This concept of using dependant-purpose table columns partly comes from how WinAPI's SendMessage() does it, which also is basically ( hwnd, msg, info1, info2 ).

Toxyy wrote:
Thu Nov 22, 2018 7:13 am
So no, I wouldn't say this would require 18 tables, just 11 additional columns to the notifications table, which has 8 columns now.
The advantage of not using that approach is being future proof: when new types are created they'll never need a new/different table column. Both approaches (mine and phpBB's) have advantages and disadvantages.
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

User avatar
JoshyPHP
Code Contributor
Posts: 972
Joined: Mon Jul 11, 2011 12:28 am

Re: BBcode bitfield and other serialized data

Post by JoshyPHP » Thu Nov 22, 2018 1:10 pm

Toxyy wrote:
Thu Nov 22, 2018 7:13 am
So no, I wouldn't say this would require 18 tables, just 11 additional columns to the notifications table
If the goal is to achieve some unspecified normal form for the notification table then adding columns won't help much. You may still end up with repeating groups of data, a whole lot of NULL fields, etc... From a practical standpoint, the biggest issue is that extensions would have to manipulate the table schema to add new columns. They may also run into name collision issues.

In the end, I think it's important to weigh the cost against the benefits. The limiting resource for a volunteer-run project is the amount of time developers can spend. Both core developers and extension developers would have to spend more time handling schema changes and dealing with incompatibilities between extensions. On the other hand, what are the benefits? It becomes possible to run queries on the previously-serialized data, but the application is limited. It would become possible to, say, delete notifications triggered by the actions of a now-deleted user or delete notifications related to a topic.
I wrote the thing that does BBCodes in 3.2.

Post Reply

Return to “phpBB Discussion”

Who is online

Users browsing this forum: No registered users and 26 guests

cron