ActivityPub: Sprint 2

[mediaformat]

ActivityPub: Sprint 2

• [x] Setup / Planning
• [ ] Migrate annotations
• [ ] Adapt/update existing annotations db querying code
• [ ] Minimally implement http-foundation Request methods for use with landrok/activitypub

Other

• [x] fix: adds $wbr to parseURLs closure

Here's why I did it:

• Known's Datamodel needs optimizing before the floodgates of federated activities are opened!

Checklist:

• [ ] This pull request addresses a single issue
• [ ] If this code includes interface changes, I've included screenshots in this Pull Request thread
• [ ] I've adhered to Known's style guide (these codesniffer rules might help!)
• [ ] My git branch is named in a descriptive way - i.e., yourname-summary-of-issue
• [ ] I've tested my code in-browser
• [ ] My code contains descriptive comments
• [ ] I've added tests where applicable, and...
• [ ] I can run the unit tests successfully.

[mediaformat]

@benwerd The following describes the Annotations schema, and what I've considered:

CREATE TABLE IF NOT EXISTS `annotations` (
  `uuid` varchar(255) NOT NULL,
  `_id` varchar(36) NOT NULL,
  `siteid` varchar(36),
  `owner` varchar(255) NOT NULL,
  `entity_subtype` varchar(64) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `contents` longblob NOT NULL,
  `publish_status` varchar(255) NOT NULL DEFAULT 'published',
  `entity_id` varchar(255) NOT NULL,
  `parent_id` varchar(255),
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `_id` (`_id`),
  KEY `siteid` (`siteid`),
  FOREIGN KEY (owner) REFERENCES entities(uuid),
  KEY `entity_subtype` (`entity_subtype`),
  KEY `publish_status` (`publish_status`),
  FOREIGN KEY (entity_id) REFERENCES entities(uuid),
  FOREIGN KEY (parent_id) REFERENCES annotations(uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A migration script (stored procedure), would iterate each Entity row containing a (JSON) annotations object, extracting annotations and storing their types [reply, like, share, ...] into entity_subtype. So for example a reply could be stored as Idno\\Annotations\\Reply.

I'm proposing 2 new columns here, entity_id as a foreign key linking to the Entity object it relates to, and parent_id for linking to another annotation, allowing for threads with depth.

Where an Annotation originates from a remote site (webmentions, activitypub), I would create a new Idno\\Entity\\User storing the minimum needed/available info [owner_name, owner_url, owner_icon].

Any thoughts, concerns, suggestions?

[benwerd]

@mediaformat I think this looks great, and I very much see the need to make this migration. But I do have a big caveat:

Because not everyone is using the same database engine, I don't think migration should be a stored procedure. Or we should test the stored procedure for each of MySQL and Postgres, and create a command line tool for SQLite and MongoDB.

I think, actually, the latter is preferable.

It's imperative that the revised annotations methods use the database abstraction layer (presumably adding inherited methods for each db engine) rather than going straight to the SQL.

So here's what I'd suggest:

• You continue down the stored procedure road for MySQL and Postgres.
• I take the schema you've developed and adapt it for MongoDB and SQLite, with a command line update script.
• We collaborate on the annotation methods.

Does that seem reasonable?

[mediaformat]

@benwerd Totally reasonable!