Serious application development with LibreOffice and LibreOffice Basic

users
#1

Greetings,

At one client, this week's production check-in of an application developed in MS Office (mostly Access) 2007/2010 code is now up to:
110,315 LOCs, 380 Modules, 249 Stored Procedures, and counting...

This is a client/server application backing up to a SQL back-end database.

I am interested in the capabilities of LibreOffice Basic along the same lines.

The documentation for LibreOffice Basic seems very space compared to MS VBA.

Does LibreOffice Basic even support Object Orientated Class development? I have double class wrapped each logical type of data record. One level is the DB table class, which manages INSERT / UPDATE / SELECT type operations with a consistent interface. The next class layer is a Validation class which handles DB class to Form Control mapping (to/from) and also performs data type validation on the fields, marks bad data fields red/error, and so on. Also I make extensive use of in-memory collection classes: Thing / Things classes, where the Things class contains one/many Thing class object instances.

What sorts of database drivers are available cross-platform? (Linux and Windows) I use a mix of ADO and DAO drivers for VBA code. ADO Command / Parameter objects when possible, ADO Recordset objects second. Both of these ADO options leave the data in VBA variables, so works very well for record INSERT / UPDATE forms. For Multiple Items forms displaying a read-only list of records I use nested DAO.QueryDef objects to issue queries in Pass-Through mode to execute a Stored Procedure, then an outer DAO.QueryDef wrapper to copy selected records into an FE DB temp table for local display. At first I would like to use MySQL as the BE DB for use with LibreOffice.

TIA!

Sincerely,

#2

Greetings Mark,

I am puzzled at what you posted in reply to my original message...

I was talking about a desktop database as the front-end in a Client/Server application.

It appears your question was with using spreadsheets as a database table.

Did I not understand something that was intended?

Sincerely,

#3

At one client, this week's production check-in of an application
developed in MS Office (mostly Access) 2007/2010 code is now up to:
110,315 LOCs, 380 Modules, 249 Stored Procedures, and counting...

They probably need that much code because VBA is so poor (while
also being quite vociferous), both as a language and concerning the
libraries available.

This is a client/server application backing up to a SQL back-end
database.

I am interested in the capabilities of LibreOffice Basic along the
same lines.

The documentation for LibreOffice Basic seems very space compared to
MS VBA.

An actual application with more logic than just CRUD would very
probably gain a lot if it was implemented in a rich, powerful language
such as Python which profits from a vast module library. Since the
Python API of LO (not only Base) is essentially undocumented, this
would require an actual GUI framework, however.

I have double class wrapped each logical type of data record. One
level is the DB table class, which manages INSERT / UPDATE / SELECT
type operations with a consistent interface. The next class layer is
a Validation class which handles DB class to Form Control mapping
(to/from) and also performs data type validation on the fields, marks
bad data fields red/error, and so on. Also I make extensive use of
in-memory collection classes: Thing / Things classes, where the
Things class contains one/many Thing class object instances.

With Python and a decent RAD framework (there are quite a few), you
don't need to implement anything like that yourself.

At first I would like to use MySQL as the BE DB for use with
LibreOffice.

That's a B-A-D idea if you want to get your data back in a consistent
state with a certain reliability. Use PostgreSQL instead.

Sincerely,

Wolfgang

#4

Hi Michael,

I am interested in the capabilities of LibreOffice Basic along the same
lines.

I seriously doubt that using LO Basic, you would be able to attain a
similar goal in any meaningful timeframe and with decent performance.
However, using another language via one of the UNO bindings might be a
more viable (if somewhat daunting) project.

The documentation for LibreOffice Basic seems very space compared to MS
VBA.

That's because it is. Documentation on UNO and Basic comes down to the
built-in help and anything you can find with regard to the
OpenOffice.org project, the current API documentation produced by the LO
project, and old Sun StarOffice documents (if they are still around).
VBA has a whole ecosystem and 20-odd years worth of product pushing
around it by people paid to push the platform forward. Although UNO has
been around since the days of StarOffice, it has never had that
commercial "acceptance".

Look at UNO, look at the language bindings, decide which is the most
appropriate for what you want to do :

http://api.libreoffice.org/

http://api.libreoffice.org/docs/common/ref/com/sun/star/sdb/module-ix.html

http://api.libreoffice.org/docs/common/ref/com/sun/star/sdbc/module-ix.html

http://api.libreoffice.org/docs/common/ref/com/sun/star/sdbcx/module-ix.html

http://www.openoffice.org/udk/

Alex

#5

I seriously doubt that using LO Basic, you would be able to attain a similar goal in any meaningful timeframe and with decent performance. However, using another language via one of the UNO bindings might be a more viable (if somewhat daunting) project.

If you use another language, then you have full access to everything that it offers.

The documentation for LibreOffice Basic seems very space compared to MS
VBA.

That's because it is. Documentation on UNO and Basic comes down to the
built-in help and anything you can find with regard to the
OpenOffice.org project, the current API documentation produced by the LO
project, and old Sun StarOffice documents (if they are still around).
VBA has a whole ecosystem and 20-odd years worth of product pushing
around it by people paid to push the platform forward. Although UNO has
been around since the days of StarOffice, it has never had that
commercial "acceptance".

Look at UNO, look at the language bindings, decide which is the most
appropriate for what you want to do :

http://api.libreoffice.org/

http://api.libreoffice.org/docs/common/ref/com/sun/star/sdb/module-ix.html

http://api.libreoffice.org/docs/common/ref/com/sun/star/sdbc/module-ix.html

http://api.libreoffice.org/docs/common/ref/com/sun/star/sdbcx/module-ix.html

http://www.openoffice.org/udk/

I usually check the online API docs (as shown above), inspecting the objects, and I have even bothered to dive into the source code when I was totally befuddled.

#6

Your post was requesting some guidance for "Serious application development with LibreOffice and LibreOffice Basic". My reply was intended to communicate the fact that LO Base is very limited when it comes to serious uses.

LO Base is not capable of connecting to an SQL based back end, and linking to a LO Calc spreadsheet at the same time. If you link to a spreadsheet all tables used in your LO Base application must exist as worksheets inside that same LO Calc spreadsheet.

That one restriction was enough to disqualify LO Base from consideration for what I consider to be a very simple application. I have four different LO Calc spreadsheets that contain related information. I wanted to link to all four of those spreadsheets and extract the appropriate data from each to build a report combining information from each.

The data contained in the LO Calc spreadsheets change on a weekly basis. I need to combine all that weekly data into a large database containing combined data for many years. Can't get there from here.

Something like this would be trivial for MS Access.

What I'm trying to tell you is that you should consider LO Base as a front end for a single LO Calc spreadsheet and not much else. You can not import data from a spreadsheet into a LO Base application.

Another issue is the lack of documentation. I've searched and asked but found only the barest of guidance. A computer program without documentation is what they call a video game.

Now, if anyone on this list knows better, please feel free to post your illustrative prose in reply.

#7

Greetings All,

I successfully warped up the application development project I described. Final end-of-project stats were as follows:

Total
123,576 LOC's, 413 Modules (Classes/Modules/Forms/Reports), 264 Stored Procedures, and fin!

It has a Front-End (FE) which is the bulk of that code, an automated replication application transferring data from the production JDE ERP system for use by the application I built, and finally I developed for them an application server architecture to asynchronously process requests without tying up the FE application.

The legacy application this new one replaced had the following stats:
1,366 LOC's in 12 module(s)

So, getting finally back and replying...

Mark LaPierre wrote:

Your post was requesting some guidance for "Serious application development with LibreOffice and LibreOffice Basic". My reply was intended to communicate the fact that LO Base is very limited when it
comes to serious uses.

I guess I can now see what you were communicating.

LO Base is not capable of connecting to an SQL based back end, and linking to a LO Calc spreadsheet at the same time. <snip>

Is LO Calc spreadsheets really the preferable file format for LO Base data?

That one restriction was enough to disqualify LO Base from consideration for what I consider to be a very simple application.

That is unfortunate.

Wolfgang Keller wrote:

At first I would like to use MySQL as the BE DB for use with
LibreOffice.

That's a B-A-D idea if you want to get your data back in a consistent
state with a certain reliability. Use PostgreSQL instead.

MySQL is trusted by this client organization to handle their production website... LAMP software stack. Why would it suddenly not be reliable for back office Client/Server use?

I guess for the moment I will leave the DB requirements in Access, and later port them to Free Pascal / Lazarus when I have the time to get up-to-speed with that cross platform serious development tool.

Thank you all for your helpful responses. Actually last week I came across a link referring to StarOffice... I had forgotten that origin in the LibreOffice history timeline!

Sincerely,

#8

Hi :slight_smile:
I think Base can either use an SQL-based back-end or LO Calc spreadsheets.  Just not both at the same time.

Errr, the rest of it was a bit over my head!  Hopefully someone else will be around soon
Regards from
tom :slight_smile:

#9

> This is a client/server application backing up to a SQL back-end
> database.
> I am interested in the capabilities of LibreOffice Basic along the
> same lines.
> The documentation for LibreOffice Basic seems very space compared to
> MS VBA.
An actual application with more logic than just CRUD would very
probably gain a lot if it was implemented in a rich, powerful language
such as Python which profits from a vast module library. Since the
Python API of LO (not only Base) is essentially undocumented, this
would require an actual GUI framework, however.

Yep. I stare at it occasionally thinking
man-i-bet-that-can-do-some-awesome-stuff... but the lack of
documentation is overwhelming.

> I have double class wrapped each logical type of data record. One
> level is the DB table class, which manages INSERT / UPDATE / SELECT
> type operations with a consistent interface. The next class layer is
> a Validation class which handles DB class to Form Control mapping
> (to/from) and also performs data type validation on the fields, marks
> bad data fields red/error, and so on. Also I make extensive use of
> in-memory collection classes: Thing / Things classes, where the
> Things class contains one/many Thing class object instances.
With Python and a decent RAD framework (there are quite a few), you
don't need to implement anything like that yourself.

"and a decent RAD framework (there are quite a few)"

Really??? Name *ONE*.

ORMs are a no brainer; SQLalchemy is simply fantastic. But the UI part
of the RAD solution is seriously lacking.

> At first I would like to use MySQL as the BE DB for use with
> LibreOffice.
That's a B-A-D idea if you want to get your data back in a consistent
state with a certain reliability. Use PostgreSQL instead.

+1 PostgreSQL.

#10

One thing I haven't found is a good example of presenting / driving some
UI from within the scope of LOo from Python. I don't even know that it
is possible. But I've been lurking the developer list[s] and see talk
about LOo & glade... that would be really nice.

#11

Hi Adam,

One thing I haven't found is a good example of presenting / driving some
UI from within the scope of LOo from Python. I don't even know that it
is possible. But I've been lurking the developer list[s] and see talk
about LOo & glade... that would be really nice.

The best that I can think of currently in the 4.0/1 branch of LO would
be the wizards, these are now written in Python and manage UI elements
to present something more palatable to the user.

Have a look at, for example, AgendaWizardDialog.py, which can be found
in the source code at :

core/wizards/com/sun/star/wizards/agenda/AgendaWizardDialog.py

The Glade UI stuff takes a Glade UI template and converts it to a LO AWT
UI with a bit of under the hood magic. Check out the UI rework effort on
the wiki to see whether there is anything remotely useful that you could
throw into a python module.

Alex

#12

> With Python and a decent RAD framework (there are quite a few), you
> don't need to implement anything like that yourself.

"and a decent RAD framework (there are quite a few)"

Really??? Name *ONE*.

using PyQt (& Sqlalchemy):
Qtalchemy: www.qtalchemy.org
Camelot: www.python-camelot.com
Pypapi: www.pypapi.org

using PyGTK:
Sqlkit: sqlkit.argolinux.org (also uses Sqlalchemy)
Kiwi: www.async.com.br/projects/kiwi

using wxPython:
Gui2Py: code.google.com/p/gui2py/
Dabo: www.dabodev.com
Defis: sourceforge.net/projects/defis (Russian only)
GNUe: www.gnuenterprise.org

Sincerely,

Wolfgang