Redmine Issues with HTML Formatting

I recently had an issue with a client where we had deployed Redmine with an add-on plugin (CKEditor) that displayed all updates to issues as HTML. This resulted in all new issues and content being created with HTML tags but existing/previous content was not and it looked like a big glob on the page. To resolve this, I created a simple script that would connect to the database and update the journals table notes column to add basic paragraph and line breaks to format the output in a clean manner.

I’ll note that I did try the rake task that comes with ckeditor to convert all notes to texttile formatting but it also formatted all of the new content that was already correct so I had to rollback (via database restore).

The script is a simple redmine loop that loops over the results of a SQL query like the following that looks for notes without formatting and longer than 10 characters:

@raw_journal_results = @raw_journal_client.query("
  SELECT
    id
    ,journalized_id
    ,notes
  FROM #{@db_schema}.journals
  WHERE
      notes NOT LIKE '<%' 
    AND 
      LENGTH(notes) > 10
  ").each do |row|
...

Then update the notes to replace all newlines with an HTML break, and add a paragraph markup to the start and end of the notes.

I then set this to run every few minutes as we also use the email fetch feature to pull in email updates and they are not formatted either. This script, when run successfully once, only takes <1 minute to run so it's easy on the system and keeps things formatted properly for new issues.