This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature: Office Software

Adding a basket tool to OpenOffice.org

By Dmitri Popov on September 20, 2007 (9:00:00 AM)

Share    Print    Comments   

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.

Dmitri Popov is a freelance writer whose articles have appeared in Russian, British, US, German, and Danish computer magazines.

Share    Print    Comments   

Comments

on Adding a basket tool to OpenOffice.org

Note: Comments are owned by the poster. We are not responsible for their content.

Just FYI, Zotero already has OOo plug-in

Posted by: Anonymous [ip: 222.0.20.88] on September 20, 2007 11:04 AM

why MySQL?

Posted by: Anonymous [ip: 166.166.14.159] on September 21, 2007 08:06 AM
With a Samba client or server, you could also make the OOoBasket.odb available as a plain file on the server, available to any client running OOo.

#

a SQL engine provides concurrency

Posted by: Anonymous [ip: 75.116.117.28] on September 21, 2007 04:31 PM
From an access point of view, a DB engine will allow multiple clients. Accessing a single OOoBasket.odb with multiple running OOo instances would probably create race conditions and trash the file.

#

Adding a basket tool to OpenOffice.org

Posted by: Anonymous [ip: 72.235.32.51] on September 22, 2007 08:37 AM
Basket Note Pads - according to the developers web page, he is having to withdraw from further development due to lack of available time a/o May 27, 2007. He is looking for a volunteer programmer to pick up the project. ( http://basket.kde.org/news.php#2007-05-27 )

#

OpenOffice.org -- not really for academia

Posted by: Anonymous [ip: 76.202.118.181] on September 26, 2007 06:26 AM

Its equation formating is *horrible* all the sub-scripts/super-scripts are spaced wide apart. MS word, and even KWord are better at formating equations, and comparable to latex.... and these issues are open since many years now.



#

Re: OpenOffice.org -- not really for academia

Posted by: Anonymous [ip: 12.154.4.140] on October 12, 2007 03:26 PM
Not germane to the discussion nor many, perhaps most, academic works do not require equation editors. Granted it is an issue, but can we focus on the issues at hand?

Norman Prather
wanderingcleric@gmail.com

#

Adding a basket tool to OpenOffice.org

Posted by: Anonymous [ip: 69.255.211.64] on November 21, 2007 10:55 PM
Is there any way to link a field to a single record? What I want to do is be able to insert one of the notes in a spot in the document and allow it to be updated if I change the record in the database. This seems like such a simple and obvious thing to do, but I cannot seem to figure out how to do it in openoffice.org. I don't want to mail merge. I basically need the snippet to be updateable, the whole idea of a field.

#

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya