How to recover theme files from the database

Problem

You have deleted a theme or theme assets by error or otherwise lost access to it.

Theme files, like Velocity Templates, JavaScript code or CSS stylesheets are code and can take some effort to be developed. Please store them in an appropriate versioning system, e.g. git. See How to create and develop a Scroll Viewport theme for tooling we provide for making that an easy workflow.

Solution

You can always restore the Confluence site from a backup as a whole to a different instance, but depending on the size of your instance and your organisational environment that could be a very high hurdle.

If you just need to recover a few theme files and have the right skills you can recover the theme files from the database tables they are stored in.

You need knowledge about your database system, SQL dialects and tooling that is provided on your Operating System. This can not be taken over by our support engineers.

The escaping you use in the SQLs below and even keywords can differ enourmsly.

Also you should perform these steps on a full or partial database backup, not on you live database.

The theme files you create and edit for Scroll Viewport are stored in the tables

AO_5C2200_THEME - this defines the (custom) themes in Scroll Viewport and contains the name and ID of the theme.
AO_5C2200_THEME_RESOURCE_V2 - this defines the files and directories that make up the theme.

The basic process for exporting is as follows:

Create a filesystem structure into which you can export the theme files
Export the files and store them in the filesystem

So here is the exemplary process fleshed out with Postgres and psql - there are a lot of different approaches you could automate this further, but we boiled it down to two SQL statements that should be relatively easy to transfer to a different database model or version.

1. Create a filesystem structure

The following SQL will output a number of bash commands that will create the file system structure for your restored files:

SQL

select 'mkdir -p /tmp'||"PATH" from "AO_5C2200_THEME_RESOURCE_V2" resource join "AO_5C2200_THEME" theme on resource."THEME_ID" = theme."ID" 
where theme."ID" = 1
and resource."DIRECTORY" = 't';

The SQL has two places that you need to adapt to make it work for your environment:

/tmp is the root path we will be using for our example and should be adapted to your needs
1 in the second line is the ID of the theme we are recovering for our example - you can find the theme you need by using select * from "AO_5C2200_THEME"

This will output a number of bash commands like this:

> mkdir -p /tmp/css

Execute them to create the directories in your environment.

2. Export the files

The following SQL will output a number of SQL commands that will export the text data from the database to files:

SQL

select 'copy (select encode(decode("DATA", ''base64''), ''escape'')  from "AO_5C2200_THEME_RESOURCE_V2" where "ID" = '||resource."ID"||') TO ''/tmp'||"PATH"||''';'
from "AO_5C2200_THEME_RESOURCE_V2" resource join "AO_5C2200_THEME" theme on resource."THEME_ID" = theme."ID"
where theme."ID" = 1 and resource."DIRECTORY" = 'f';

The SQL has two places that you need to adapt to make it work for your environment:

/tmp is the root path we will be using for our example and should be adapted to your needs
1 in the second line is the ID of the theme we are recovering for our example - you can find the theme you need by using select * from "AO_5C2200_THEME"

The DATA column is Base-64 encoded thus we need to decode and then encode the data to characters. If you also need to recover binary files the copy command needs to be adapted.

This will output a number of SQL commands like this:

> copy (select encode(decode("DATA", 'base64'), 'escape')  from "AO_5C2200_THEME_RESOURCE_V2" where "ID" = 702649) TO '/tmp/blog.vm';

Execute them to perform the export.