Back up and print your blog with OpenOffice.org Base

If you are running a blog (or any Web publishing system, for that matter) that relies on a database back end, you will sooner or later face the problem of backing up the content stored in the database. One way to go about it is to build a backup tool using OpenOffice.org Base. Since Base can pull data from a MySQL or any ODBC-compliant data source, you can create a simple database that connects to the blog’s back end and extracts content from it, which you can then export in different formats.

Besides keeping your content safe, this solution offers a few other benefits. First, it allows you to browse all the blog posts directly from within OpenOffice.org and to quickly insert posts into your current Writer document. More importantly, however, you can use Writer’s import feature or Sun Report Builder to create a printable version of your blog, which effectively turns the Base database into a nifty blog-to-print tool.

To see how to make this work, we’ll try it with the Gelato tumblelog software that uses MySQL as its back end. Gelato stores all of its content (i.e. blog posts) in a simple database table gelato_data, which makes it easy to figure out how to extract the relevant data from the system. If you are not familiar with Gelato or need help on installing it on your server, take a look at the article Gelato: Tasty tumblelog software.

Our first step is to create a connection between the Gelato MySQL database and OpenOffice.org. To do this, OpenOffice.org requires a properly configured database connector. If you are not sure how to create one, take a look at the Connect OpenOffice.org to MySQL article, which will help you choose the right connector and configure it. The following description assumes that you are using the Connector/J connector, since it’s the easiest one to set up.

Once you’ve configured the connector, you can create a Base database that will act as a backup tool for the content stored in the gelato_data table. In OpenOffice.org, choose New -> Database. In the Database Wizard, select the Connect to an existing database option, select MySQL from the drop-down list, and click Next. Select the Connect using JDBC (Java Database Connectivity) option and press Next. Enter the database name and database server URL into the appropriate fields, then press Next. Provide a user name and tick the Password required check box, if necessary. To see whether everything works properly, click the Test Connection button. Press the Next button, select Yes, register the database for me option, and click Finish. Give the database a name and save it. Launch the created database and switch to the Table section. Base then connects to the MySQL database, and you should see a list of its tables.

Backing up the data in the tables is straightforward. Right-click on the table you want to back up (gelato_data in Gelato) and choose Copy. To save the data in a spreadsheet, create a new Calc spreadsheet, and choose Edit -> Paste (or press Ctrl-V). This inserts the copied content into the spreadsheet, which you can then save in different formats.

Instead of a spreadsheet, you can paste the copied data into a blank Writer document. If you do that, OpenOffice.org opens the Inserts Database Columns dialog box, and you can choose how to insert the copied data into the document. You can, for example, import the data as a table or as text. To select what fields you want to import, move them from the left window to the right. With the gelato_data table, you might want to add the Title, Description, and URL fields. If you choose to import the data as a table, you can edit the table’s design by pressing on the AutoFormat button. If you opt for the text option, you can format each field by selecting the desired paragraph style from the Paragraph Style drop-down list. The result is a nicely formatted Writer document containing all your blog posts.

The created backup database is good not only for backing up your blog’s content and turning it into a printable form. You can also access the database from within any OpenOffice.org application thought the Data Sources pane and quickly insert the content of your blog into the currently opened document. This can come in handy if you, for example, want to insert a blog post into a Writer document. To do this, open the Writer document and place the cursor where you want to insert the blog post. Press F4 to open the Data Source window. Choose the gelato_data table and select the row containing the blog post you want to insert, and press the Data to Text button. Use the Insert Database Columns window to select the exact data you wan to insert as described above.