[kwlug-disc] What's the simple next level beyond OpenOffice Base?

[Khalid Baheyeldin]

On Sat, Nov 27, 2010 at 3:43 PM, unsolicited <unsolicited at swiz.ca> wrote:

> Khalid Baheyeldin wrote, On 11/27/2010 10:19 AM:
>
>  On Sat, Nov 27, 2010 at 1:54 AM, unsolicited <unsolicited at swiz.ca<mailto:
>> unsolicited at swiz.ca>> wrote:
>>
>>        Since you are already using spreadsheets but consistency is the
>>        issue,
>>        why not use Google Documents, which has hosted spreadsheets, that
>>        people can collaborate on simultaneously.
>>
>>
>>    I've tested Google Docs. There is no data consistency / validation
>>    facilities within it. It just hasn't gotten that far, yet.
>>
>>        And you can also upload your existing Open Office sheets in it,
>>        so setup
>>        is quick.
>>
>>
>>    Yes. But, for example, I uploaded to it with some cells data
>>    validated. Then brought it right back - the data validation was
>>    gone. I tried it both ways - they have 2. One that facilitates
>>    online editing as well, the other where it is merely storage. I did
>>    not expect the storage to change the file, but it did.
>>
>>    Google Docs won't do here.
>>
>>
>> It seems that you already have logic inside your spreadsheet, and that
>> gets
>> lost when you upload the sheet. You may be using OpenOffice specific
>> validation or macros.
>>
>
> And your point is?
>
> i.e. That's the nature of the beast.


That is the nature of the beast, if you allow it to. If you use Excel or
Open
Office Spreadsheet as an application development tool and allow your
data and code to be intermingled, then that is what you get.

A little of that is perhaps OK, but when it sprawls, you are in for a lot of

pain for the long run, for the reasons I stated.


>  This is the start of "code sprawl". Instead of spreadsheets being just
>> data,
>> they contain code (business logic) in them, and in effect, you have an
>> "application".
>>
>
> Yep, that's the nature of the beast. People do what they can, to the extent
> that they can, with the tool they have in front of them.


And then suffer later. They either find that the hard way, after they have
the bulk of their business logic and code in the sheets. If they are
for-warned
and disciplined, they should not allow that to happen.

Separate the data from the code from the presentation and you will get far
less headaches over the long run.

This is the same reason we have MVC (model/view/controller) and all this
separation of code from presentation (e.g. a theme in Drupal should be where
all the presentation stuff). Mix it up, and you get trouble.


>  An application is something you have to maintain for many years to come:
>> debug, modify, extend, ...etc. They have a life of their own, and require
>> time and effort to continue to exist. Application development is not a one
>> time thing. It is an on going effort.
>>
>
> Yes. Nature of the beast. Been doing this stuff for almost 30 years. I knew
> what I was signing up for when I volunteered to help out.


As long as you know the pitfalls ...


>  At least with Google, your data is still downloadable, and they will not
>> shut
>> down for some time.
>>
>
> Arguably, Google fits your prior paragraph. Nothing says the data will be
> retrievable in a suitable format in the future.


It is retrievable NOW as an Excel sheet, OpenOffice sheet, as well as .txt,
.csv, and more.

Read about it here:
http://docs.google.com/support/bin/topic.py?topic=15148

They have a team about Data Liberation", and specifically address Docs here

http://www.dataliberation.org/google/google-docs


>  The Glom path seems like a good one too. If you don't mind sticking with
>> their data format for the future.
>>
>
> In poking around, it does seem like that is inevitable. If, by data format,
> you are referring to the 'code' that surrounds the data itself. e.g. An
> OpenOffice form / document / spreadsheet / database. Move to anything else,
> and the wheel will have to be reinvented in that new environment. And
> environments are going to inevitably change, over time. Particularly as each
> new kid on the block appears.
>

True to a certain extent that you have to rework things when you change
apps.

But you always want to have a migration path. If the new app provides an
import option, then you are good, at least for the data part. If not, then
having the data in some standard helps a lot, e.g. CSV export, or relational
database.

You can then "fit" the old data to the new app, rather than having to guess
the file structure, record padding and/or record alignment, endianess,
...etc.

As I indicated, the significant value of spreadsheets, at the moment, is the
> users can work with it to explore and discover wherever it is they are
> trying to go. When that settles down, the system requirements will also
> become more complete. In essence, they are prototyping, and that's no bad
> thing. At least, with this mechanism, they are able to, on their own.
> Further, by establishing some data validation rules, they inherently refine
> those requirements.
>

Largely agree.

Given the nature of the organization and that they are prototyping, this is
an acceptable scenario.

Otherwise, I would say that validation belongs to the application layer.
Whether you implement it as javascript in the browser, or in the backend
code, or both (to have instant usability for those who have JS enabled, as
well as a fool proof way that applies when it is disabled).

Right now, I'm not entirely convinced they should move beyond spreadsheets.
> The back end data extraction requirements are not well defined / known /
> understood at the moment - it is still evolving. Largely I'm not convince
> because I think they will need to be able to work against any database moved
> to, in a spreadsheet, to explore, mash, mangle, discover, etc.  Currently,
> OpenOffice, even against Base, is just broken when connecting to things via
> data source / calc.


Something I remembered: sometime back I was able to connect OpenOffice to a
backend of MySQL via ODBC, on Linux (both front end and backend).

That is something to consider, since it can potentially give you the best of
both words: front end in the familiar OpenOffice and a backend on a
relational database.

But not sure how/if a spreadsheet would interact with that. Maybe it is
limited only to Base?

e.g. Within the data source view, one can directly edit the database - there
> goes your data validation, formatting, input controls. Multiple ways of
> inputting a record to a database would seem to be a very bad thing here.


That is why an app should have back end logic, and only that logic would be
the one to write to the database.

Or use triggers/stored procedures if you lean that way. Makes the app tied
to one particular database engine though, and makes portability near
impossible ...


> I have hints, though, that a Base database connecting to an external
> server/file (e.g. MySQL or .dbf) has the same issues. I may have to play -
> which is part of the point of my post: I can download MySQL, but there is no
> IDE / front end for application development surrounding it - thus the part
> of my post about what FOSS is out there to use from an IDE perspective.
>

Valid point (lack of IDE).
-- 
Khalid M. Baheyeldin
2bits.com, Inc.
http://2bits.com
Drupal optimization, development, customization and consulting.
Simplicity is prerequisite for reliability. --  Edsger W.Dijkstra
Simplicity is the ultimate sophistication. --   Leonardo da Vinci
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://kwlug.org/pipermail/kwlug-disc_kwlug.org/attachments/20101127/3c57852f/attachment.htm>