OpenOffice.org and Bilboblog

Building Blogs

Article from Issue 109/2009
Author(s):

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.

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.

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.

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:

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("Bilboblog")
DB=DataSource.GetConnection ("user", "password")

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:

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("Bilboblog")
UserName=InputBox("Enter MySQL user name", "Attention")
UserPassword=InputBox("Enter MySQL password", "Attention")
DB=DataSource.GetConnection (UserName, UserPassword)

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:

INSERT INTO articles (field1, field2) VALUES ('value1', 'value2')

In this case, the INSERT INTO query is as follows:

SQLQuery="INSERT INTO articles (article_content) VALUES ('" + Article + "')"

In OpenOffice.org Basic, the INSERT INTO query is followed by two statements that execute the specified query:

SQLStatement=DB.createStatement
Result=SQLStatement.executeQuery (SQLQuery)

Once the query is executed, the macro closes the database connection to keep things tidy:

DB.close
DB.dispose

The entire macro is shown in Listing 1.

Listing 1

OpenOffice.org Basic Macro

01 Sub PostToBilboblog
02 ThisDoc=ThisComponent
03 ThisText=ThisDoc.Text
04 Article=ThisText.String
05 DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
06 DataSource=DBContext.getByName("Bilboblog")
07 UserName=InputBox("Enter MySQL user name", "Attention")
08 UserPassword=InputBox("Enter MySQL password", "Attention")
09 DB=DataSource.GetConnection (UserName, UserPassword)
10 SQLQuery="INSERT INTO articles (article_content) VALUES ('" + Article + "')"
11 SQLStatement=DB.createStatement
12 Result=SQLStatement.executeQuery (SQLQuery)
13 DB.close
14 DB.dispose
15 End Sub

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.

Listing 2

Macro that Preserves Bold Font

01 Sub ReplaceBold
02 Dim SearchAttributes(0) As New com.sun.star.beans.PropertyValue
03
04 ThisDoc=ThisComponent
05 SearchAttributes(0).Name="CharWeight"
06 SearchAttributes(0).Value=com.sun.star.awt.FontWeight.BOLD
07 ReplaceObj=ThisDoc.createReplaceDescriptor
08 ReplaceObj.SearchStyles=false
09 ReplaceObj.SearchAll=true
10 ReplaceObj.SearchRegularExpression=true
11 ReplaceObj.SetSearchAttributes(SearchAttributes)
12 ReplaceObj.SearchString=".*"
13 ReplaceObj.ReplaceString="[b]&[/b]"
14 ThisDoc.replaceAll(ReplaceObj)
15 End Sub

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:

SearchAttributes(0).Name="CharPosture"
SearchAttributes(0).Value=com.sun.star.awt.FontSlant.ITALIC
ReplaceObj.ReplaceString="[i]&[/i]"

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).

Listing 3

Create Function from Subroutine

01 Function MarkupStr(SearchAttrName, SearchAttrValue, ReplaceStr)
02 Dim SearchAttributes(0) As New com.sun.star.beans.PropertyValue
03 ThisDoc=ThisComponent
04 SearchAttributes(0).Name=SearchAttrName
05 SearchAttributes(0).Value=SearchAttrValue
06 ReplaceObj=ThisDoc.createReplaceDescriptor
07 ReplaceObj.SearchStyles=false
08 ReplaceObj.SearchAll=true
09 ReplaceObj.SearchRegularExpression=true
10 ReplaceObj.SetSearchAttributes(SearchAttributes)
11 ReplaceObj.SearchString=".*"
12 ReplaceObj.ReplaceString=ReplaceStr
13 ThisDoc.replaceAll(ReplaceObj)
14 End Function

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.

MarkupStr("CharWeight", com.sun.star.awt.FontWeight.BOLD, "[b]&[/b]")
MarkupStr("CharPosture", com.sun.star.awt.FontSlant.ITALIC, "[i]&[/i]")
MarkupStr("CharUnderline", com.sun.star.awt.FontUnderline.SINGLE, "[u]&[/u]")
MarkupStr("CharStrikeout", com.sun.star.awt.FontStrikeout.SINGLE, "[s]&[/s]")

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:

SQLQuery="INSERT INTO articles (article_content, article_pub) VALUES ('" + Article + "', '" + Timestamp + "')"

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].

Listing 4

Macro and Function

01 Sub PostToBilboblog
02 MarkupStr("CharWeight", com.sun.star.awt.FontWeight.BOLD, "[b]&[/b]")
03 MarkupStr("CharPosture", com.sun.star.awt.FontSlant.ITALIC, "[i]&[/i]")
04 MarkupStr("CharUnderline", com.sun.star.awt.FontUnderline.SINGLE, "[u]&[/u]")
05 MarkupStr("CharStrikeout", com.sun.star.awt.FontStrikeout.SINGLE, "[s]&[/s]")
06 ThisDoc=ThisComponent
07 ThisText=ThisDoc.Text
08 Article=ThisText.String
09 DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
10 DataSource=DBContext.getByName("Bilboblog")
11 UserName=InputBox("Enter MySQL user name", "Attention")
12 UserPassword=InputBox("Enter MySQL password", "Attention")
13 DB=DataSource.GetConnection (UserName, UserPassword)
14 Timestamp=InputBox("Enter timestamp", "Attention")
15 SQLQuery="INSERT INTO articles (article_content, article_pub) VALUES ('" + Article + "', '" + Timestamp + "')"
16 SQLStatement=DB.createStatement
17 Result=SQLStatement.executeQuery (SQLQuery)
18 DB.close
19 DB.dispose
20 End Sub
21
22 Function MarkupStr(SearchAttrName, SearchAttrValue, ReplaceStr)
23 Dim SearchAttributes(0) As New com.sun.star.beans.PropertyValue
24 ThisDoc=ThisComponent
25 SearchAttributes(0).Name=SearchAttrName
26 SearchAttributes(0).Value=SearchAttrValue
27 ReplaceObj=ThisDoc.createReplaceDescriptor
28 ReplaceObj.SearchRegularExpression=true
29 ReplaceObj.searchStyles=false
30 ReplaceObj.searchAll=true
31 ReplaceObj.SetSearchAttributes(SearchAttributes)
32 ReplaceObj.SearchString=".*"
33 ReplaceObj.ReplaceString=ReplaceStr
34 ThisDoc.replaceAll(ReplaceObj)
35 End Function

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.

Listing 5

Parse and Format Hyperlinks

01 Sub MarkupURL
02 ThisDoc=ThisComponent
03 ThisText=ThisDoc.Text
04 ParaEnum=ThisText.createEnumeration
05 While ParaEnum.hasmoreElements
06  Para=ParaEnum.nextElement
07   PortionEnum=Para.createEnumeration
08    While PortionEnum.hasMoreElements
09     Portion=PortionEnum.nextElement
10      If Portion.HyperlinkURL <> "" then
11        Portion.String = "[url=" + Portion.HyperlinkURL +"]" + Portion.String + "[/url]"
12      End if
13    Wend
14 Wend
15 End Sub

Infos

  1. Bilboblog: http://code.google.com/p/writertools/downloads/list
  2. Sun MySQL Connector extension for OpenOffice.org: http://extensions.services.openoffice.org/project/mysql_connector
  3. TimeStamp Converter extension for Firefox: http://addons.mozilla.org/en-US/firefox/addon/2063

The Author

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.

Buy Linux Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus

Direct Download

Read full article as PDF:

News