Author: Dmitri Popov
No matter whether you are working on an article, an academic paper, or a novel, research is a crucial part of the writing process. And as with any research, you need a place to save your notes, ideas, relevant links, and text snippets. While there are tools like Basket Note Pads and the Zotero Firefox extension, wouldn’t it be nice if you could store and manage your stuff directly from within OpenOffice.org? This is not only doable, but also easy to implement using just a Base database and a macro.
The first piece of the solution we are about to build — let’s call it OOoBasket — is a database. To keep things simple, let’s use OpenOffice.org’s built-in database engine. Launch OpenOffice.org and choose File -> New -> Database to create a new database. In the main window, switch to the Tables section and click on the Create Table in Design View link. This opens the Table Design window, where you populate the table with fields. What fields you want to add is up to you: it all depends on how you want to structure the stored data. For example, you might want to use the following fields:
- ID [INTEGER] — a mandatory field that acts as a primary key
- Snippet [LONNGVARCHAR] — a “storage” field for your notes, links, text snippets, etc.
- Date [DATE] — this one is self-explanatory
- Category [VARCHAR] — used to assign a category to the record
- Tags [VARCHAR] — allows you to assign tags to the record
If you feel more comfortable working with external database engines, you can build the OOoBasket database using MySQL, then create a connection to it from OpenOffice.org. This would allow you to access data stored in the OOoBasket database from any machine running OpenOffice.org.
Once you’ve added all the fields you need, save the table with the name Basket, then save the database as OOoBasket.odb. Next, you have to register the created database as a data source for use with OpenOffice.org applications. Choose Tools -> Options, then navigate to OpenOffice.org -> Databases. Press the New button, select the OOoBasket.odb database, make sure that the name in the Registered name field is “OOoBasket”, then press OK to save the settings and close the window.
Now you can start working on the macro, which consists of a dialog window that acts as a simple GUI and the code itself. Let’s create the dialog window first. In OpenOffice.org, choose Tools -> Macros -> Organize Macros -> OpenOffice.org Basic, press the Organizer button, switch to the Dialogs tab, press New, give the new dialog a name (e.g. BasketDialog), press OK, and then Edit. This opens the Dialog Designer window, which contains all the tools you need to build the dialog. Here, you need to add three input text fields for each field in the OOoBasket database: TextField1 for Snippet, TextField2 for Category, and TextField3 for Tags. While you could create an input field for the Date field in OOoBasket to enter the date manually, you can let the macro do the job. You will also need to create two buttons: an OK button (set the Button type option in the Properties window to OK) that submits the entered data, and a Cancel button (set the Button Type to Cancel) that dismisses the dialog window. The final result should look similar to the one in the figure.
Now it’s time to write the macro that ties all the components together. First of all, if the user selects a text fragment in the currently opened document, the macro acquires it, so it can then insert it into the Snippet text field:
ThisDoc=ThisComponent
SelectedSnippet=ThisDoc.getCurrentController().getSelection().getByIndex(0).getString()
This way the user can insert a text snippet from the document into the database without manually typing it. Next, the macro initiates the dialog window:
exitOK=com.sun.star.ui.dialogs.ExecutableDialogResults.OK
DialogLibraries.LoadLibrary("OOoBasket")
Library=DialogLibraries.GetByName("BasketDB")
TheDialog=Library.GetByName("BasketDialog")
Dialog=CreateUnoDialog(TheDialog)
In this example, the BasketDialog dialog is stored in the OOoBasket macro library in the BasketDB module. If you’ve saved the dialog in a different library or module, you have to adjust the code accordingly. The macro then inserts the acquired text selection into the Snippet text field (“TextField1”):
DialogField1=Dialog.getControl("TextField1").setText(SelectedSnippet)
It then opens the dialog window, so the user can fill out the rest of the fields:
If Dialog.Execute=exitOK Then
DialogField1=Dialog.getControl("TextField1")
TextSnippet=DialogField1.Text
DialogField2=Dialog.GetControl("TextField2")
SnippetCategory=DialogField2.Text
DialogField3=Dialog.GetControl("TextField3")
SnippetTags=DialogField3.Text
If no text fragment has been selected, TextField1 will be blank, and the macro acquires the text that the user enters manually in the field. Next, the macro establishes a connection to the OOoBasket database:
DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("OOoBasket")
ConnectToDB=DataSource.GetConnection ("","")
If you prefer the macro to automatically insert the current date into the Date field in the database, you have to add the following code to the macro:
DateToday=Format(Year(Now), "0000") & "-" & Format(Month(Now), "00") & "-" & Format(Day(Now), "00")
This code converts the current date into the ISO format (i.e. 2007/11/30), but you can tweak it to save in any other format you like. Just make sure that the Date field in OOoBasket is formatted accordingly.
Since OpenOffice.org Basic uses SQL to manipulate data in Base databases, the next step is to construct an SQL query that inserts the entered data into the appropriate fields in the OOoBasket database. To do this, the macro uses the INSERT SQL statement: INSERT INTO table (Field1, Field2, Field3) VALUE (Value1, Value2, Value3). In this particular case, the SQL query looks like this:
SQLQuery="INSERT INTO ""Basket"" " + "(""Snippet"", ""Category"", ""Tags"", ""Date"") VALUES "_
+ "('" + TextSnippet + "','" + SnippetCategory + "','" + SnippetTags + "','" + DateToday + "')"
The macro then runs the created SQL query, which inserts the data into the Basket table:
SQLStatement=ConnectToDB.createStatement
Result=SQLStatement.executeQuery (SQLQuery)
Once this is done, the macro closes the database connection, disposes the dialog window, and notifies the user that the data has been added successfully:
ConnectToDB.close
ConnectToDB.dispose()
MsgBox ("The text snippet has been saved.", , "All done!")
Dialog.Dispose
That’s all there is to it. Here is the final macro:
Sub InsertTextSnippet()
ThisDoc=ThisComponent
SelectedSnippet=ThisDoc.getCurrentController().getSelection().getByIndex(0).getString()
exitOK=com.sun.star.ui.dialogs.ExecutableDialogResults.OK
DialogLibraries.LoadLibrary("OOoBasket")
Library=DialogLibraries.GetByName("BasketDB")
TheDialog=Library.GetByName("BasketDialog")
Dialog=CreateUnoDialog(TheDialog)
DialogField1=Dialog.getControl("TextField1").setText(SelectedSnippet)
If Dialog.Execute=exitOK Then
DialogField1=Dialog.getControl("TextField1")
TextSnippet=DialogField1.Text
DialogField2=Dialog.GetControl("TextField2")
SnippetCategory=DialogField2.Text
DialogField3=Dialog.GetControl("TextField3")
SnippetTags=DialogField3.Text
DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("OOoBasket")
ConnectToDB=DataSource.GetConnection ("","")
DateToday=Format(Year(Now), "0000") & "-" & Format(Month(Now), "00") & "-" & Format(Day(Now), "00")
SQLQuery="INSERT INTO ""Basket"" " + "(""Snippet"", ""Category"", ""Tags"", ""Date"") VALUES "_
+ "('" + TextSnippet + "','" + SnippetCategory + "','" + SnippetTags + "','" + DateToday + "')"
SQLStatement=ConnectToDB.createStatement
Result=SQLStatement.executeQuery (SQLQuery)
ConnectToDB.close
ConnectToDB.dispose()
MsgBox ("The text snippet has been saved.", , "All done!")
End If
Dialog.Dispose
End Sub
The described solution is part of the Writer’s Tools extension developed by yours truly and released under GNU GPL. You can download the latest release of the extension and tweak it to your heart’s content.
Category:
- Office Software
