MySQL Connector for OpenOffice.org 1.0 GA

MySQL Connector for OpenOffice.org 1.0 has been released. This first GA release supersedes any previous Alpha and Beta releases.

The driver can be used in OpenOffice.org 3.1.1 and the upcoming OpenOffice.org 3.2 to connect to a MySQL server, versions 5.1 and later.

When using the MySQL Connector for OpenOffice.org, you have the following advantages over using the MySQL Connector/ODBC or MySQL Connector/J:

• Easy installation through the OpenOffice.org Extension Manager

• Seamless integration into OpenOffice.org

• Work on multiple MySQL schemata (databases) simultaneously

• Connect to MySQL servers using named pipes (Windows) or Sockets (Unix)

• No need to go through an additional Connector installation routine (ODBC/JDBC)

• No need to configure or register an additional connector (ODBC)

• No need to install or configure a driver manager (ODBC)

• No need for a Java Runtime Environment (JDBC)

The driver is made available as an OpenOffice.org extension, to be found in the OpenOffice.org Extension Repository at the following location:

http://extensions.services.openoffice.org/project/mysql_connector

It comes in versions for Windows, Linux Intel 32 and 64 bit, Solaris Sparc, Solaris Intel, and Mac OS X Intel.

Technically, the driver is based on Connector/C++ and Connector/C.

There is one known issue in the MySQL Connector for OpenOffice.org 1.0 release. Fixing it would require upgrading to a new Connector/C version, which is not yet available. So we decided to release 1.0 despite of this known problem, since we're convinced that Connector/OpenOffice.org has nonetheless production quality, and can give you great advantages over using Connector/ODBC or Connector/J.

You might want to visit our project's wiki for a quick start and installation guide.

Please let us know about your experiences with the connector, good or bad, so we know where we did right or can still improve! Enjoy!

There's something I still owe you: I asked those of you working with (or otherwise interested in) Base to vote for your most favorite features, out of a predefined set of features. And 'till now, I didn't give you the promised results.

Well, the amount of responses has been ... slightly below what we had expected, but nonetheless, let's have a look at what came in:

16 people voted. 15 distributed their 10 points, one just listed three of the feature items, which I translated to 4, 3, 3 points.

A few people voted for a native driver for a certain database type (SQLite, for instance), where the feature list contained one item for all of SQLite, PostgreSQL, MySQL, Firebird, and one item for MySQL (yes, this was inconsistent). To cope with this, the votes for the „all-in-one“ item were split up. For instance, if you voted 10 points to a native driver for the 4 types, this was translated into 2.5 points for every single type.

Adding up all the votes gives us:

Hmm. Hardly readable. Stripping all the items with one or two votes only leads to:

Better.

But now what does that mean? First – obviously we allowed too many points per person . Since only 16 people participated, a single person casting 10 votes on a single item (like it happened for the SQLite native driver, for instance) can make a huge difference. Other than that, it's hard (up to impossible) to derive hard facts from that small statistical basis. While the first three items came as a little surprise to me (while I certainly appreciate the second's position, since this is a long-time personal favorite of mine, which I think is essential for a lot of other improvements), „updates to joined tables” and „link tables from different DBs” are clearly known to be desired for a long time.

For some more data, let's look at another source: Issues in IssueZilla, together with their votes. Querying for the top-voted issues in DBA indicates that the poll result, though backed up by little data only, might not be too wrong: The clear leader is issue 32117, titled „SQLite SDBC driver for OOo”. Position 2 goes to issue 51904, requesting an import wizard for „simple” tables such as .dbf, .csv, .ods, followed by issue 8949 „query on several tables of a DBase/Calc database”. Position 4 then again is occupied by an item known from the poll: issue 42464 „allow to link tables from different sources into one database file”.

But there's even more data to evaluate: The data from the OpenOffice.org User Survey 2009. (If you didn't participate, yet, please consider doing so. For improving the OpenOffice.org product, nothing is as valuable as the input of you users, as opposed to us darkroom inhabitants knowing nothing about the Real Life™.) While this survey is still running, Chris Lukasiak, our user experience guy, kindly provided us with some preliminary data. As with all statistical data, it is subject to intentional misinterpretation and deformation, as needed and desired to prove anything you want, so let's go:

• 34% of all users (who finished the survey at the time the data was collected) – 14857 out of 42839 – use Base. Wow, that's more than I would have expected!

• Of those using Base, roughly 50% say they work with Queries (7563), Forms (8638), and Reports (6957). Hmm. Not sure what this wants to tell us.

• Only a forth of the Base users (3552) say they use scripts and macros to adjust Base to their needs. In my very personal opinion, that's not enough , as I strictly believe that making Base suitable for developing “data-driven applications” – which implies scripting it – is a Must Have ™.

• 50% use Base to access spreadsheet documents, 28% for dBase files, and 34% for .csv (comma-separated-value) files. Now that's really amazing, at least to me. According to Chris, that's a pattern found a lot in the survey data: People often use Base to access that kind of “simple” data, not caring for relationships and other such esoteric stuff …

So, what's the consequences? Well, I already said in my original posting that there are more constraints on what to implement next than the poll results (or, for that matter, the other results from above). So, please do not really expect the very next OpenOffice.org version to come with a native SQLite driver (But also don't expect it to not come with it.). Nonetheless, the feedback is valuable in judging our user's needs.

One point definitely taken is the importance of „simple” database formats such as dBase and CSV. I'd say the importance of allowing querying multiple of those tables at once (known as „joins in dBase/CSV/spreadsheet queries”) just raised. Also, I think we should seriously consider adding more functionality (wizards?) lowering the barrier of entry (this is something brought to us via other channels, too). A wizard for importing dBase/CSV data – something which is already possible today, but in a very roundabout way only – is only one of multiple facets of this topic.

So, let's see which of those we decide to go for next. For the moment, we'll concentrate on our current features: The MySQL Native Driver, and various performance improvements in Base (as part of the overall performance project).

Macros in Database Documents – Milestone 3

In the hope this is not going to be a never ending story … here's the third entry about the „ Macros in Database Documents“ feature.

As always, this took longer than expected (but don't assume that I spent all the time since the first blog entry with the feature), but finally, here it goes:

Milestone 3 of the implementaion is finished, and available for preview to interested parties. A number of bugs in the milestone 1 release have been fixed, the migration of „old“ database documents now works as expected, and you can now assign macros to database document events (Yes! „ ThisDatabaseDocument.FormDocuments.Startup.open“, bound to the „Open Document“ event of a database document …).

The CWS which is dedicated to implementing the feature is called odbmacros3, and a snapshot of it is available to everybody for download, for Windows, Linux, and Mac OS X. The version installs as OOo-DBDocMacros 0.1 Beta, and should conflict neither with regular OpenOffice.org installations, nor with OpenOffice.org developer snapshots (OOo-dev). (If in doubt, refer to the Wiki article which describes how to run OpenOffice.org versions in parallel.)

If you are curious (I hope you are!), then please try out this version, and be sure to give your feedback, good as well as bad one. Preferably, you should user our project's mailing list for this purpose: users@dba.openoffice.org.

Have fun!

Milestone 680m233 has been finished recently, containing the child workspace basmgr03. This had three consequences (ordered by increasing significance):

1. The number of my open CWS decreased by 1 to 14. (Hmm. Perhaps next time I feel like doing exorcism I should practice on child workspaces, not on code).

2. Two bugs have been fixed by passing, which will already slightly improve your "scripting experience" in Base:

   • Issue 81217 describes the cryptic "no script" error message when you (first-time) open a database form which contains macros

   • Issue 76129 describes that you could not bind macros to document events (such as OnLoad) using the usual Tools/Customize functionality

3. The final (well, hopefully) foundations in the application framework have been laid, so that we now can embark upon Issue 49133: allow to embed macros in database documents like in the other application's documents.

(
In case you wonder why I talk about "Base" vs. "other applications" here: For historical reasons, Base does not use the non-UNO application framework as Writer/Calc/Draw/Impress/Math do, but started implementation from scratch. This decision, dating some years back, had some good and some bad consequences in the last years – one of them being that often enough, we needed to re-implement the other app's functionality, respectively re-factor it so it can be used everywhere. This is what we did with the Basic-IDE in CWS basmgr03.
)

There's still some open questions around this, though. That is, implementing the functionality to embed Basic macros in an ODB file is the first step. Of course you will be able then to execute those macros, to edit them in the IDE, and the like – as you know it from the other applications.

But surely you also want to bind those macros to elements in your forms and macros. Say you want to have a button in a form, which, when pressed, executes a macro located in the embodying database document. Looking at how such binding is done technically, the script is represented as URI, which later is fed into the scripting framework. The URI might be something like vnd.sun.star.script:Standard.Module1.Main?language=Basic&location=document. Note there's the "location=document" parameter: It specifies the function "Main" should be looked up in the module "Module1" in the Library "Standard" in the document's Basic. In contrast, if it should be looked up in the application-wide Basic, the parameter would be "location=application". (Just noticed that chapter 19.6.1 of the Developer's Guide talks about allowed locations being "document", "user", and "share". Seems this is somewhat outdated.)

So, we need to think about how to design a hierarchy of document-local Basic scripts. Should they all be subsumed in the "document" location? That is, if location=document is specified, the macro could be in the form (if there is such a macro), or in the database document which this form belongs to. Or, should a new location type be introduced? location=parentdocument? Doesn't really sounds convincing to me.

Well, for the moment I'm looking forward to start the core implementation. It may be that in a first step, we won't solve the above problem. Even if so, it would still mean you already can embed Basic in .odb files, and you can use it to implement logic in your database application (e.g. by configuring it into the document's toolbar or menu). That alone would be a major step forward, don't you think?

If you ever used Base with forms and reports, you most probably already noticed the following: Say you open a form, which comes up with a certain size, work with it, then close it. Afterwards, you open an existing, or create a new text document. Guess what? This text document comes up with the same size as previously your database form. Also, if you previously switched on, say, the Drawing Object Properties toolbar in Writer, it will also be switched on in all your database forms. If you switch it off there, it will also be switched off in your Writer documents. Same for all other toolbars, and for all your menu/toolbar/keyboard customizations – they're all shared between Writer and database forms.

This usually is quite annoying, since the tasks you do in both modules are different, so your working habits probably are different, too. Also – but maybe that's only me – it looks rather unprofessional, doesn't it?

Conceptually, the reason is OpenOffice.org's understanding of modules: Toolbars, menus, keyboard accelerators, as well as window position and size, are attributes of a module (you might look into your installation's share/config/soffice.cfg/modules directory to get an impression). While different applications in OpenOffice.org define different modules – e.g., Calc defines an own module, Writer does, Impress does, plus some more –, database forms are implemented using a somewhat customized Writer, thus they're identified as being the Writer module. Until now.

The child workspace fwkdbdesign01 introduces a concept where modules are separated from applications (thanks to Andreas Schlüns from the framework team for answering our long-year begging!). That is, using the new UNO interface XModule, we can now programmatically change the identifier of a module. Doing so, we can define database forms to be a module different from Writer. As an immediate consequence, all symptoms mentioned above (plus some more, for instance flickering in the Form Design toolbar when initially opening a Writer document or database form) are gone. On a medium term, we are able to better customize form UI to the tasks of database form creation, without affecting Writer. For instance, we could remove (or at least hide from the top-level) all the functionality which is relevant to Writer only, but not to form design.

So, I'm looking forward to this CWS being integrated (it's in QA currently), and I hope I'm not the only one who thinks that this is a change for the better in Base's usability.