Incubator » CB Activity

Database structure would likely change as follows.

id | user_id | asset ...

To the following.

id | user_id | stream | type | object | object_id

Example as follows.

1 | 42 | profile.42
1 | 42 | profile.42.registration
1 | 42 | profile.42.blog.3
1 | 42 | groupjive.group.135.create

To the following.

1 | 42 | 1 | post | user | 42
1 | 42 | 1 | registration | user | 42
1 | 42 | 1 | create | blog | 3
1 | 42 | 2 | create | group | 135

"type" in this case should probably just be "verb", which should likely follow proper activity schema.

https://github.com/activitystreams/activity-schema/blob/master/activity-schema.md

Both "type" and "object" can technically be paths as well e.g. a "object" of "groupjive.group" should also be acceptable if determines that is necessary. Needs a lot of consideration, but this is basically the direction the database schema should go to improve access speed even further.

Reviewing the activity schema further this should be split up even more as follows.

id | user_id | stream | type | target | target_id | object | object_id

So for example you'd have the old usage below.

1 | 42 | profile.42.blog.3

Changed to the following.

1 | 42 | 1 | post | user | 42 | blog | 3

The target is a user with an id of 42 and the object is a blog post with an id of 3. Note that asset will still be used from an API and parameters perspective because it's easier to have that 1 parameter vs 4. The way it will work is as follows.

TARGET.TARGET_ID.OBJECT.OBJECT_ID

The first set is always the target followed by the object. If only target is supplied then target and object are one in the same. Next this completely resolves issues with asset construction since we can now just do the following for example.

group.[target_id]
user.[target_id].photos.[object_id]

This solves the issue mentioned earlier with needing this information stored for asset reconstruction when directly linking to an activity. Activity objects will simply have a ->getAsset function to construct their assets so we can use that in PHP if necessary. Wildcards will still work fine as we'll handle them in SQL. Similar can likely be applied to notifications (probably doesn't need target) and comments (should be same as activity).

Instead of "type" use standardized "verb". So the following structure would be a standardized storage.

id | user_id | stream | verb | target | target_id | object | object_id

Object should be before the target based off schema, but this doesn't really work with our Asset parameter usage which probably doesn't matter since we're parsing that into whatever we like but it makes it hard for a user to more well define the target and object so need to consider dropping Asset in favor for a 4 input setup to directly specify this.

id | user_id | stream | verb | object | object_id | target | target_id

Example below of a user being tagged in an activity entry.

1 | 42 | 1 | tag | user | 43 | activity | 56

This means some old usages above don't make logical sense. Example as follows.

1 | 42 | 1 | post | blog | 3 | user | 42

In this case we don't need the target at all. The blog doesn't target someone. So this would just be as follows.

1 | 42 | 1 | post | blog | 3

Now the old asset for this would be profile.42.blog.3 and in reality the asset should just be blog.3. We however want to keep Asset for configuration purposes as it's easier. So if we do blog.% as our Asset then get all blog activity entries. If we did profile.42.blog.% then get all blog posts for user_id 42.

Using the following structure.

verb | object | object_id | target | target_id

The below are some examples of asset conversions.

gallery.photos.30512

upload | photo | 30512 | profile | 42

groupjive.group.500149.gallery.photos.30374

upload | photo | 30374 | group | 500149

The old usage never specified a prefix target for a lot of activity, but for the new usage we really do want to include it. For example GJ group create activity is a PROFILE target NOT a group target as it's redundant to have an activity entry in the group that the group was created. Example as follows.

groupjive.group.135.create

create | group | 135 | profile | 42

This targets the activity to a profile and tells us what the activity is (a group).

kunena.28.create

create | kunena | 28 | profile | 42

kunena.29.reply

reply | kunena | 29 | profile | 42

Ideally with this one the target would be the topic being replied to, but we don't have that information currently so we'll have to handle the conversion as safely as possible.

groupjive.group.498596.join

join | profile | 42 | group | 498596

See in this case the target is the group and the object of the activity is the profile joining.

profile.1051150.connection

connection | profile | 42 | profile | 1051150

In this case the profile 42 (the object) is connecting with the profile 1051150 (the target). We know it's profile 42 because 42 is the owner of the activity entry and since this is a hardcoded structure it's safe to assume here.

So there will be several situations where target and object are one in the same, BUT we still need to store this information for query efficiency as we don't want to have to do strange OR cases. For example the following.

profile.42

This is just a post on profile.42 activity stream. The target is the profile and the object is also the profile. This gives us the following.

post | profile | 42 | profile | 42

The alternative is that object doesn't always need to be fulfilled, but target does, but this again complicates queries if for example you want to filter to all posts of which profile 42 is the object of the post and in PHP side if there was no object we'd just inherit from target anyway so it's better and safer to just goahead and store it.

This should allow a generic parsing behavior as follows.

profile.42.blog.32

TARGET.TARGET_ID.OBJECT.OBJECT_ID.VERB

So this even covers the following cases.

profile.42.update

Then becomes the following.

update | profile | 42 | profile | 42

Still needs improvements as some of the structures don't make sense. The schema dictates the following.

ACTOR - VERB - OBJECT - TARGET

In CB Activity the ACTOR is user_id and the rest are as stated. So with the following the group 135 is being created by the actor.

groupjive.group.135.create

This should mean we have the following.

actor | verb | object | object_id | target | target_id

42 | create | group | 135 | group | 135

This also means the following.

groupjive.group.498596.join

Should become the following.

42 | create | group | 498596 | group | 498596

Since the actor is joining the specific group. Object and Target can be one in the same. This also means some profile activity also need adjusting. Example as follows.

profile.1051150.connection

42 | connection | profile | 1051150 | profile | 42

The profile 42 is connecting with profile 1051150 and we want it displayed on profile 42 stream or should this be as follows?

42 | connection | profile | 1051150 | profile | 1051150

THAT is A LOT closer to what the asset is saying. Basically the asset is saying the object AND the target are the same thing.

Ok, OBJECT should only be used if necessary.

profile.1051150.connection

Becomes the following.

42 | connection | NULL | NULL | profile | 1051150

The target is the user being connected to. There is no additional object. Target can mean ON or TO. Object is purely an extension of that meaning VERB on OBJECT to TARGET vs VERB to TARGET. Leaving OBJECT as optional and the majority of the time NULL will also reduce storage and make transition from asset to this new structure easier.

Re-opened as going back to drawing board on this. The asset column is useful in some regards, but starts falling apart during scalability testing. At 1 million rows it's ok, but filtering starts falling apart on performance once more filters are applied to the stream. Primarily due to the massive amount of LIKE wildcard usages that are being applied.