Peter Vágner

Regarding various Known post types there is a story I would say I think so because at least for me it took so long to sort it out on my instance.
After I've installed Known on my domain some time in july 2016 I have realized the Repost and Like post types don't appear to be available right on the main page so I've installed Reactions plugin.
Everything worked smoothly for me at that time I was posting a lot of likes to my site, I have even reposted a few things. So given the fact it was all perfectly pieced together and worked fine I was not following the Known development and I missed all its updates. In particular one significant change was that since version 0.9.2, built in Bookmarks plugin deployed inside the IdnoPlugins/Like folder received an ability to handle likes, reposts and bookmarks. I have only acknowledged this issue about a year later in june 2017.
Looking through both issues there was no easy solution to this seemingly misterious incompatibility and that got me thinking in somewhat different direction.

• Built-in Bookmarks plugin can now handle likes and reposts.
• Do I need seperate Bookmark, Like and Repost buttons on the main page if I am either using corresponding bookmarklet or posting from the phone using an android app?
• Is there something else I might miss in the future Reactions plugins provides?

I was unable to answer the last question however first two points indicate I no longer need Reactions plugin because Bookmarks plugin which lives in the core can do what I need it to do.
Okay so I then went to examine how the data are stored in the database trying to work out if I can turn Likes and Reposts created with Reactions plugin into Likes and Reposts built-in Bookmark plugin can now handle. I'm using MariaDB and first I wanted to see how many entries there are in my database I have created using Reactions plugin.

SELECT COUNT(*) FROM entities WHERE entity_subtype LIKE "IdnoPlugins%Reactions%";

Now how many of them are Likes

SELECT COUNT(*) FROM entities WHERE entity_subtype LIKE "IdnoPlugins%Reactions%Like";

And finally how many of them are Reposts

SELECT COUNT(*) FROM entities WHERE entity_subtype LIKE "IdnoPlugins%Reactions%Repost";

While examining these data I have noticed that the entity_subtype is a dedicated database table column, however the same thing is also stored in the contents column holding all the entity content as a json string. So I have just double checked if these two places agree with each other.

SELECT COUNT(*) FROM entities WHERE contents LIKE "%\"entity_subtype\":\"IdnoPlugins%Reactions%\"%";

And now a different check of the same thing:

SELECT COUNT(*) FROM entities WHERE INSTR(contents, "\"entity_subtype\":\"IdnoPlugins\\\\Reactions") > 0;

Now I think I can use these building blocks to conditionally select entities I will be changing. However I don't yet know what changes to do.

This is how the Like created with the Reactions plugin is stored in the database:

MariaDB [mydatabase]> SELECT * FROM entities WHERE (entity_subtype LIKE "%like" ) ORDER BY created LIMIT 1;
+----------------------------------------------------------------------------------+----------------------------------+------------------------------------+-----------------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+----------------+
| uuid                                                                             | _id                              | owner                              | entity_subtype        | created             | contents                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | search                                    | publish_status |
+----------------------------------------------------------------------------------+----------------------------------+------------------------------------+-----------------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+----------------+
| https://pvagner.tk/2016/liked-ryan-rixs-catastrophic-data-loss-and-me-0647a70c63 | 58debaa17faedc195f6ca78ca23249f5 | https://pvagner.tk/profile/pvagner | IdnoPlugins\Reactions\Like | 2016-07-10 12:36:42 | {"access":"PUBLIC","owner":"https:\/\/pvagner.tk\/profile\/pvagner","likeof":"http:\/\/notes.whatthefuck.computer\/1467388500.0-note.html","syndicatedto":["https:\/\/twitter.com\/rrrrrrrix\/status\/748993485538111488","https:\/\/www.facebook.com\/10153834879468856\/posts\/10154372324658856"],"description":"Ryan Rix's Catastrophic Data Loss and Me","_id":"58debaa17faedc195f6ca78ca23249f5","slug":"liked-ryan-rixs-catastrophic-data-loss-and-me-0647a70c63","created":1468154202,"updated":1468154202,"uuid":"https:\/\/pvagner.tk\/2016\/liked-ryan-rixs-catastrophic-data-loss-and-me-0647a70c63","entity_subtype":"IdnoPlugins\\Reactions\\Like"} | ryan rix's catastrophic data loss and me  | published      |
+----------------------------------------------------------------------------------+----------------------------------+------------------------------------+-----------------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+----------------+

And this is how a different like created with Bookmarks plugin is stored:

MariaDB [mydatabase]> SELECT * FROM entities WHERE (entity_subtype LIKE "%like" ) ORDER BY created DESC LIMIT 1;
+----------------------------------------------------------+----------------------------------+------------------------------------+-----------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+----------------+
| uuid                                                     | _id                              | owner                              | entity_subtype        | created             | contents                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | search                                                                                    | publish_status |
+----------------------------------------------------------+----------------------------------+------------------------------------+-----------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+----------------+
| https://pvagner.tk/view/9251ea447da9ae82fdc29af9cac2ee56 | 9251ea447da9ae82fdc29af9cac2ee56 | https://pvagner.tk/profile/pvagner | IdnoPlugins\Like\Like | 2018-01-23 12:48:38 | {"access":"PUBLIC","owner":"https:\/\/pvagner.tk\/profile\/pvagner","body":"https:\/\/torrentfreak.com\/denuvo-has-been-sold-to-global-anti-piracy-outfit-irdeto-180123\/","likeof":"https:\/\/torrentfreak.com\/denuvo-has-been-sold-to-global-anti-piracy-outfit-irdeto-180123\/","description":null,"tags":null,"pageTitle":"Denuvo Has Been Sold to Global Anti-Piracy Outfit Irdeto - TorrentFreak","slug":"denuvo-has-been-sold-to-global-anti-piracy-outfit-irdeto","created":1516711718,"updated":1516711719,"publish_status":"published","_id":"9251ea447da9ae82fdc29af9cac2ee56","uuid":"https:\/\/pvagner.tk\/view\/9251ea447da9ae82fdc29af9cac2ee56","shorturl":"1hJTnB","posse":{"twitter":[{"url":"https:\/\/twitter.com\/pvagner\/status\/955784340310581248","identifier":"@pvagner","item_id":"955784340310581248","account_id":"pvagner"}],"mastodon":[{"url":"https:\/\/mastodon.sk\/@pvagner\/99399219252030242","identifier":"pvagner@mastodon.sk","item_id":"pvagner@mastodon.sk","account_id":""}]},"entity_subtype":"IdnoPlugins\\Like\\Like"} | https://torrentfreak.com/denuvo-has-been-sold-to-global-anti-piracy-outfit-irdeto-180123/ | published      |
+----------------------------------------------------------+----------------------------------+------------------------------------+-----------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+----------------+

Based off all the stuff I have gathered so far I compiled this short list of steps:

• Create a backup of your running database so you can go back and retry if something is not working the way you are expecting.
• Disable reactions plugin. This will stop displaying the broken Likes and Reposts for a while.
• Tweak the search database table column to contain the same URL  the same thing as body, likeof or repost inside the contents json.
• Tweak the contents column for all these Likes and reposts which were created by the Reactions plugin
  
  1. to have "entity_subtype":"IdnoPlugins\\Like\\Like" or "entity_subtype":"IdnoPlugins\\Like\\Repost" respectively. Basically replacing IdnoPlugins\\Reactions with IdnoPlugins\\Like.
  2. to have the same body than the respective "likeof" or "repost". Note that entries created with Reactions plugin are lacking the body property.
  3. To repurpose description into a pageTitle as Reactions plugin does not allow entering a description and makes no use of pageTitle.
• Change the database table column entity_subtype the same way we have changed entity_subtype within the contents column. This will make all the entries visible again.

Now I need to be able to retrieve the likeof url from the contents discarding all the rest. I know there is a json specific extension for current development version of MariaDB, however I am still running version 10.1, so I'll have to resort to string manipulation and regular expressions. If you are running other database flavour or an older version you will have to tweak this I guess.
I will be only showing queries tweaking Likes, if you are interested you can easily run them again for Reposts as well.

SELECT REPLACE(SUBSTR(REGEXP_SUBSTR(contents, '"likeof":"https?:[^"]*'), 11), '\\/', '/') FROM entities WHERE (entity_subtype LIKE "%Like" AND INSTR(contents, "\"body\":") <= 0) ORDER BY created DESC LIMIT 10;

At this point we are finally ready to run some UPDATE statements doing what I have just suggested.

Changing the search database table column for all the Likes created by the Reactions plugin (needs adjusting in order to work with Reposts):

UPDATE entities SET search = REPLACE(SUBSTR(REGEXP_SUBSTR(contents, '"likeof":"https?:[^"]*'), 11), '\\/', '/') WHERE (entity_subtype LIKE "IdnoPlugins%Reactions%Like");

Tweaking the entity_subtype within the contents column (handles both Likes and Reposts at the same time):

UPDATE entities SET contents = REPLACE(contents, "\"entity_subtype\":\"IdnoPlugins\\\\Reactions", "\"entity_subtype\":\"IdnoPlugins\\\\Like") WHERE INSTR(contents, "\"entity_subtype\":\"IdnoPlugins\\\\Reactions") > 0;

Creating a missing body property inside the contents column (you need to adjust it to work with Reposts):

UPDATE entities SET contents = REPLACE(contents, REGEXP_SUBSTR(contents, '"likeof":"https?:[^"]*'), CONCAT("\"body\":\"", SUBSTR(REGEXP_SUBSTR(contents, '"likeof":"https?:[^"]*'), 11), "\",", REGEXP_SUBSTR(contents, '"likeof":"https?:[^"]*'))) WHERE (entity_subtype LIKE "%Like" AND INSTR(contents, "\"body\":") <= 0);

Repurposing descriptions to pageTitles within the contents column for all Likes where there is no pageTitle (you need to also adjust this to work on Reposts):

UPDATE entities SET contents = REPLACE(contents, "\"description\"", "\"pageTitle\"") WHERE (entity_subtype LIKE "%Like" AND INSTR(contents, "\"pageTitle\":") <= 0);

And a final step changing entity_subtype column to be handled by the built-in Bookmarks plugin:

UPDATE entities SET entity_subtype="IdnoPlugins\\Like\\Like" WHERE entity_subtype LIKE "IdnoPlugins%Reactions%Like";
UPDATE entities SET entity_subtype="IdnoPlugins\\Like\\Repost" WHERE entity_subtype LIKE "IdnoPlugins%Reactions%Repost";

Definatelly there might be a more straightforward way on how to accomplish this. Tweaking the data may even not be the right solution, however I think it worked fine for me.

Edit: It turned out I was wrong on some points. So I have edited this article correcting incorrect stuf and I have also posted a follow up article explaining what I have done wrong here.