Moving from Cove to Ghost Comments
JSON, mysql, and some data manipulation.
I've replaced the Cove comments system I was using with the native Ghost comments system that was introduced in August. Similar to Cove, Ghost comments are only available to logged-in users.
There's no native way—that I'm aware of—to migrate comments from Cove to Ghost. As such, getting comments to display required an export of the comments stored with Cove and then some mysql
shenanigans. Here's a brief walkthrough.
Get The Data
Download either the json
or csv
extract from Cove. The json
is structured as follows:
{
"total": 129892,
"comments": [
{
"id": "9cc061fd-5fd0-4721-bdfd-be6c20f3c390",
"is_hidden": false,
"parent_id": null,
"date": 1649473135,
"body": "I am considering open-sourcing Singapore Rail. It would be unsupported, but a good resource for creating cross-platform SwiftUI apps.",
"topic": {
"title": null,
"url": null
},
"member": {
"id": "3eb7c928-566d-471d-af85-b9bc57a4e6ff",
"name": "Stuart",
"email": "[email protected]"
}
},
]
}
Excluding the body
, date
and member.email
fields, there isn't much you can use. However, this data is required when adding comments to your Ghost database.
Fill in the Blanks
In Ghost, the comments
table is structured as below:
+------------+-------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-----------+-------+
| id | varchar(24) | NO | PRI | NULL | |
| post_id | varchar(24) | NO | MUL | NULL | |
| member_id | varchar(24) | YES | MUL | NULL | |
| parent_id | varchar(24) | YES | MUL | NULL | |
| status | varchar(50) | NO | | published | |
| html | longtext | YES | | NULL | |
| edited_at | datetime | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+------------+-------------+------+-----+-----------+-------+
To create your insert
statement, you need to be aware of the following:
- For
id
— you need to create a unique 24-character alphanumeric string for each comment you add - For
post_id
— you need to identify the correctid
from theposts
table:select id, title from posts;
(It's possible that thetitle
could be detailed in thetopic.title
field in the Covejson
, but this has never worked for me.) - For
member_id
— do not usemember.id
from Cove, instead, use themember.email
field to get the correctid
from themembers
table in Ghostselect id from members where email = '[email protected]';
- For
parent_id
— if this comment is in reply to a previous comment, use theid
from the previous comment (you need to keep track!) status
can be left as default- For
html
— use the value of the Covebody
field edited_at
doesn't need a value- For
created_at
— you need to convert the Unix timestamp from the Covedate
field to a format ofYYYY-MM-DD HH:mm:ss
- For
updated_at
— keep it simple and use the same value ascreated_at
Insert the Data
With all that cleared up, my insert
statement for the sample post from Cove looks like this:
insert into comments (id, post_id, member_id, html, created_at, updated_at) values ('9910abaca5b1ff266d03ffe2', '98b82bea2b907e7308689bfc', '42p061d72b907e7308689c3b', 'I am considering open-sourcing Singapore Rail. It would be unsupported, but a good resource for creating cross-platform SwiftUI apps.', '2022-04-09 02:58:08', '2022-04-09 02:58:08');
...which results in this.
stuartbreckenridge.net Newsletter
Join the newsletter to receive the latest updates in your inbox.