OpenOffice.org and Bilboblog

Building Blogs

Dmitri shows you how to compose and publish your blog from OpenOffice.org.

Although OpenOffice.org is first and foremost a desktop productivity suite, you can use its ability to connect to external database management systems like MySQL to create rather nifty solutions that take the productivity suite beyond the limits of the desktop. For example, you can plug OpenOffice.org into the MySQL back end of a blogging engine and write and publish blog posts directly from within OpenOffice.org Writer. It might sound like a job for a skilled programmer, but you can do this with just a couple of simple OpenOffice.org macros.

Putting the Pieces Together

To make this project more manageable, I'll break it into several steps. To begin, you have to install and configure a blog application. In theory, you can use whichever one you like, as long as it uses MySQL as its back end. In practice, however, you should pick a simple blogging engine that uses a simple MySQL database. For this project, I will use a slightly tweaked version of the Bilboblog microblogging application (Figure 1) [1]. To store content, it uses a single table with only three fields, which makes it a perfect candidate for this project.

Figure 1: Bilboblog makes a perfect candidate for the coding project.

Also, you need a PHP/MySQL server or hosted service to run Bilboblog. To install Bilboblog, create a MySQL database (e.g., bilboblog), grab the latest release of the application, unpack the downloaded archive, and move the resulting bilboblog directory to the document root of your server. Next, make the bilboblog directory writable, and point your browser to http://yourserver/bilboblog. Fill out the required fields and hit the Install button.

Next, you have to connect OpenOffice.org to Bilboblog's MySQL database. To do this, you need the Sun MySQL Connector extension for OpenOffice.org [2], which integrates directly into the productivity suite and provides an easy way to create a MySQL connection (Figure 2). To install Sun MySQL Connector, download the latest version of the extension. In OpenOffice.org, choose Tools | Extension Manager, press the Add button, and select the downloaded .oxt file. Restart OpenOffice.org, and you are good to go.

Figure 2: Configuring a MySQL connection using the Database Wizard.

Finally, you have to create a simple Base file that links OpenOffice.org to Bilboblog's MySQL database (by default, it's bilboblog). In OpenOffice.org, choose File | New | Database, select the Connect to an existing database option, and select MySQL from the drop-down list. Now press Next and select the Connect native option. In the next step, specify the MySQL connection settings and the user name, then tick the Password required checkbox.

To see whether the connection works properly, press the Test Connection button, then make sure the Yes, register the database for me option is selected, untick the Open the database for editing checkbox, and save the database as Bilboblog.odb. The created file not only links to the bilboblog MySQL database but also lets you examine its structure. Now open Bilboblog.odb and switch to the Tables section. When you double-click on the bilboblog database, you should see the articles table (Figure 3). To see its structure and content, double-click it. The articles table consists of three fields: num_article for storing article ID numbers, article_pub containing timestamps, and article_content, which stores the article content.

Figure 3: Examining the bilboblog database structure.

Writing Macros

For starters, you need to write a simple OpenOffice.org Basic macro that creates a new record in the articles table and saves the text of the currently opened Writer document in the article_content field. The macro should do three things: grab the text from the current Writer document, establish a connection to the bilboblog database, and write the text in the article_content field of the articles table. Obtaining the text content of the active Writer document requires three simple statements:

ThisDoc=ThisComponent
ThisText=ThisDoc.Text
Article=ThisText.String

The next step is to establish a database connection. This, too, requires only three statements:

You need to replace the "user" and "password" strings with a database username and password. Hard-coding the connection credentials makes the macro less flexible (if you change the username or password, you have to manually edit the macro), so you might want to tweak it to prompt the user for credentials on the fly. In this case, the code block that establishes a database connection should appear as follows:

To manipulate database data, OpenOffice.org Basic uses SQL queries, so to save the obtained text in the article_content field, the macro uses an SQL query based on the INSERT INTO command, which has the following format:

Fine, but the created macro has one serious limitation: It strips the text of all formatting, so if you want the macro to support bold, italic, underline, and other text styles, you need to do some additional coding. Bilboblog uses a subset of BBCode for text formatting, including bold ([b] and [/b] tags), italic ([i] and [/i]), and underlined ([u] and [/u]). Of course, you can use these tags to format the text directly in a Writer document, but it would be much better if the macro could process the text on the fly when it inserts the content into the articles table. To accomplish that, I need to tweak the macro so it can find all bold, italic, and underlined text fragments and wrap them in the appropriate tags. Without further ado, Listing 2 is a sample macro that finds text fragments in bold and wraps them in the [b] and [/b] tags.

The macro starts by specifying name and value search attributes and initializes ReplaceObj. The properties of this object define the search-and-replace action. Setting the SearchStyles property to false prevents the macro from searching for specific styles, whereas enabling the SearchAll property allows the macro to search the entire document. The SearchRegularExpression property enables regular expressions, so the macro can use the .* value of the SearchString property to perform the search in the entire text. The ReplaceString property then specifies the replacement string for the found fragment. The ThisDoc.replaceAll (ReplaceObj) statement then performs the search-and-replace action.

To make the macro find and format text fragments in italics, you only have to change the values of the SearchAttributes(0).Name, SearchAttributes(0).Value, and ReplaceObj.ReplaceString variables as follows:

Instead of writing several subroutines for each text style, you can easily turn the macro into a function and call it from the main subroutine. All you have to do is to replace the values with variables (Listing 3).

Now you can call the function from the main subroutine and provide the required values. The code block below uses the MarkupStr function to find text fragments in bold, italic, underlined, and strikeout and apply the appropriate markup.

The macro is almost ready, but one more thing needs to be fixed. Bilboblog uses a timestamp in the so-called Unix time format, defined as the number of seconds that have elapsed since midnight January 1, 1970. If you insert a record into the articles table without specifying a timestamp, it defaults to January 1, 1970, which is not really practical. To avoid this problem, you should add a statement that prompts you to enter a timestamp value:

Timestamp=InputBox("Enter timestamp", "Attention")

Also, you should modify the SQL query, so it inserts the obtained timestamp value into the article_pub field:

Listing 4 is the final macro and the accompanying function. The question is: How do you calculate a timestamp value? Although you can write code that converts the current date and time into a timestamp in the Unix time format, it is a tricky thing to do. But an easy solution to the problem is either to use the date %s command in the terminal or to install the TimeStamp Converter extension for Firefox [3].

Finishing Touches

To round off the project, you should take a look at the macro that parses the hyperlinks in a Writer document and applies Bilboblog formatting to them. Unlike the function that deals with text formatting, the macro that processes hyperlinks uses the Enumeration object that can be used to traverse the paragraphs in the document sequentially, extract the values of the HyperlinkURL property, then apply the specified formatting (Listing 5).

To process the hyperlinks with this macro, simply call it from the main PostToBilboblog subroutine.

Dmitri Popov holds a degree in Russian language and computer linguistics. He has been writing exclusively about Linux and open source software for several years, and his articles have appeared in Danish, British, North American, German, and Russian magazines and websites.