Find and replace in the database

Developer help
1

Good evening,
I know that the request is not strictly related to Ghost, but the Ghost database is quite complex for me, maybe someone is familiar with Ghost can more easily manipulate the database.

I need to replace the markdown code throughout the database.

This:
text

It must become like this:

text

So where is the markdown:
<p><code>text</code></p>

It must become like this:
<pre><code>text</code></pre>

Maybe I can use RegEx in some way using an IDE, or directly from the database or smth.

I have 400 articles, unfortunately I can’t do this manually one by one.

Thanks for your help.

1 Like
2

Hey there. Can I ask if this is for stylistic reasons or for HTML structure? I’m interested in your reasons for doing this :slightly_smiling_face:

3

this is a case that not much to do.

Thist method above will work to you to modify database:

but, ghost editor can change where you save this content agais

4

For both reasons, I had modified the structure to create a personal version of “prismjs”, because I didn’t know it.
Now I have implemented prismjs, as it offers more functions than mine.
I need to restore the general structure of the articles already written, otherwise I encounter problems.

This method unfortunately does not help me much, as I have to modify the tags as I said in the first post but I also have to change the number of quotes saved in the database.
Because the text in the database is also saved like this:
`text`
```text```
So a quote must become three quotes.

Moreover the text inside the html tags and the quotes is not always the same, I cannot make a simple find and replace.

Surely it is not an easy operation, maybe it cannot be solved with a single command, but two or three.

If someone is kind enough to help me it could save me days and days of work :smiley:

5

I see! Maybe it’s more efficient, and less heavy handed, to use some js to manipulate the structure over editing the database so drastically. Something like this would swap all the <code> instances to be wrapped in a <pre>:

let codeBlocks = [...document.querySelectorAll('p')];
codeBlocks = codeBlocks.filter(p => {
	return p.children.length === 1 && p.children[0].nodeName === 'CODE'
});
codeBlocks.map(block => {
	block.innerHTML = `<pre>${block.innerHTML}</pre>`
});

I’ve put an example on CodePen: https://codepen.io/daviddarnes/pen/ZEEzGqP?editors=0010, you’ll need to run it before prism.js so prism can locate the blocks of text. Let us know how you get on!

6

I was looking for a definitive solution by modifying the database, but it is probably too complex.

Thanks for this code, I saw on codepen that works correctly. But it doesn’t seem to work on my blog.

Look at these screens:

Cattura
Cattura.PNG3644Ă—1665 248 KB

7

I see. looks like the code is still picking up <code> elements that are floating inside a paragraph with text beside it. Despite the format being not quite right, this is exactly why using JavaScript was a better idea. If you had changed them with a regex of some kind in the database you would have broken them all and would have to fix them all by hand individually.

I did a bit more thinking about this and thought that checking if the inner HTML starts and ends with the opening and closing code tags and there’s only 1 element then you can be sure it’s just a block of code:

let codeBlocks = [...document.querySelectorAll('p')];
codeBlocks = codeBlocks.filter(p => {
	return (
		p.innerHTML.startsWith('<code>') &&
		p.innerHTML.endsWith('</code>') &&
		p.children.length === 1
	);
});
codeBlocks.map(block => {
	block.innerHTML = `<pre>${block.innerHTML}</pre>`
});

I updated the CodePen too: https://codepen.io/daviddarnes/pen/ZEEzGqP?editors=0010

1 Like
8

Beautiful code, almost everything has been solved. But look at this screen, the case where there is no space between one line and another, but there is the <br> tag.
Therefore we can have two cases:

p > code > br > code

or

p > br > code

I think it’s the last specific case to be solved, I didn’t notice any other cases to evaluate.

Cattura
Cattura3772Ă—1750 352 KB

Thank you for your time

9

Could you provide a clearer example? I’m thinking there shouldn’t be <br> elements inside blocks of code and that you should probably remove them anyway.

10

Sure, here’s a screen:

Cattura
Cattura.PNG1991Ă—427 44.4 KB

11

Ah I see. I’d be inclined to edit those in the content. Are there many instances of them? I’d do a full new line (aka new paragraph line) rather than a soft return. To account for that I’ll have to check for edge cases in the JavaScript and it could cause other issues

12

Unfortunately I used this syntax for more than half of my articles written entirely in the markdown. I’m not doing it for new articles anymore, but old articles are a problem.

What do you suggest?

13

Hey :wave:

One of the problems trying to manipulate the DB is that ghost stores posts in a few formats (html, markdown, text I think…).
One solution might be to export your posts, edit the export file (grep, sed, whatever) and re-import the newly edited export.

First make sure to make a backup of your DB. As it’s sqlite you should be able to simply copy it.

See: Imports and exports in Ghost: Access your content and data - FAQ

1 Like
14

Hello @tim-ghost

As you can see in my first post, this was my first idea, but there are too many cases to evaluate, I’m not good enough to create the right commands to solve all possible problems, so I asked for help here.

15

Well I managed to solve the problem with dozens of lines of code, evaluating every possible alternative.
Unfortunately I don’t like this approach, it’s dirty, and I’m unable to create a magical loop to solve all the cases, and I don’t want to make you waste too much time.
But I ask you another thing, a RegEx formula that solves this problem?

`hello, this is my blog`

it must become like this:

```string
hello, this is my blog
```

If it is important to know, I use Sublime Text for find and replace
Thanks for your help.