OpenOffice.org and Bilboblog
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) . 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 , 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.
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:
The entire macro is shown in 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.
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).
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 .
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
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.
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
- Bilboblog: http://code.google.com/p/writertools/downloads/list
- Sun MySQL Connector extension for OpenOffice.org: http://extensions.services.openoffice.org/project/mysql_connector
- TimeStamp Converter extension for Firefox: http://addons.mozilla.org/en-US/firefox/addon/2063
Read full article as PDF:
New release comes with better semantic search and improvements to Kontact.
Annual code quality report shows FOSS is more secure at all project size levels.
The Raspberry Pi Foundation has announced an even smaller version of the tiny computer that will fit into a DIMM slot.
A new class of problems lets a malicious app pre-configure an invisible privilege update.
New Hack language adds static typing and other conveniences.
New crypto policy system will offer easier configuration and more uniform security.
Ubuntu founder denounces insecurity in proprietary, close-source software blobs.
Vulnerability affects many Linux web servers
The Bavarian capital shuns Microsoft, Google, and other alternatives to implement an open source groupware solution.
Phone vendor partnerships bring Mark Shuttleworth's dream of Ubuntu on a phone a step closer to reality.