[PLUG] Advanced Topics February meeting

[John Jason Jordan]

On Thu, 14 Feb 2008 07:52:53 -0800
"M. Edward (Ed) Borasky" <znmeb at cesmail.net> dijo:

> > I could use a course on SQL, why it sucks, and techniques for reducing
> > its suckitude. :)
> 
> There ain't no sech animal! :) That's why there are Object-Relational 
> Mappers (ORM), Entity Relationship Diagrams (ERD), report generators, 
> etc. Over the past year, I forced myself to learn enough SQL to do what 
> I need to do with some rather large collections of numeric data I've 
> saddled myself with over the years, but I find SQL code difficult to 
> read and difficult to factor, etc.
> 
> If you use MySQL, there's a nifty front end called DBDesigner 4 that 
> helps a lot. But in general, many person decades have gone into web 
> application frameworks, etc., that present a much saner API than raw SQL.

One problem I have with SQL is that there seems to be a large variety
of flavors of it, all of which are obsessed with having their commas,
semicolons, periods, word order, etc. precisely the way they want it.
Witness the multiple suggestions I received here for how to write a
delete query. 

Then there is the problem that it requires logic. At the moment my
logic module is pretty toasted from overexposure to linguistic syntax
(algebra for linguists).

But the biggest problem is the documentation. People who write the
documentation assume I know the difference between a "command" and a
"keyword," not to mention various other terms. Understanding that stuff
is like trying to figure out what your algebra teacher is talking about
when you missed the first half of the term.

Even a GUI like Access, Kexi, OOo Base, et al. is difficult to figure
out. But at least you can drag and drop and click on stuff, which
automates the writing of the statements. Thus, even a beginner can get
the commas, semicolons and periods where they belong. It may not be a
lot faster than typing the statements manually, but when you factor in
the time and frustration involved in debugging, the GUI is way faster.

As I said previously, I finally have a solution that works for me. Kexi
(sqlite3) holds the data and I have no problem editing it, creating new
tables, forms, reports and such in Kexi. Its query abilities are GUI
also, but very little of sqlite3 has been implemented yet. Basically,
the only queries you can do in Kexi at this time are Select queries.
That is pretty limiting, but OOo rescued me. After much travail I
managed to get OOo Base to see the Kexi data. Unfortunately, it sees it
in read-only mode, so if I want to edit the data I have to do so in
Kexi. And OOo sees only the tables in Kexi - it cannot see the queries,
forms and other objects. However, OOo Base has a very nice query engine
of its own that is very robust, fully featured, and GUI. You can View
the SQL statement in case you want to, but so far I haven't had a need
to do so. So now I can edit the data in Kexi and create queries in OOo.
Ultimately I need the data merged into a Writer document, and OOo will
happily merge the Kexi data into a Writer document based on a query in
Base. Previously, when I used Access, OOo could merge only on a table,
so I had to create a dummy table that I would insert data into, then
empty after the merge. Merging on a query is much simpler.

Theoretically, now that I have OOo Base seeing the Kexi data, I should
be able to simply move the data completely into Base and forget about
Kexi. But Base requires Java and breaks every time there is a new
version of Java. Java is also very slow, and if you read comments from
OOo users there is a lot of complaining about speed. I mean serious
speed problems. Users report things like two hours to perform an
operation in Base that took a few minutes in Kexi. So for the time
being I'm just going to leave well enough alone. The version of Base
that comes with OOo 2.3 is a relatively new rewrite, and Kexi is also
in its infancy. What I have works for me and I'll just bide my time and
see how these two develop.