[PLUG] Advanced Topics February meeting

[Ted Kubaska]

Let me second this. There is also a good talk (which unfortunately I cannot
attend) at the Microsoft Office tonite (look at http://www.padnug.org for
details). Yes, it's MS and SQL Server 2008 and all that, but the speaker's a
good one and I've been to these before and you can have good disucssion
about SQL (or TSQL which I think is what MS calls their version). Yes,
different versions from different vendors but there is a common base., The
simple things I do can't tell the difference.
 -ted

-----Original Message-----
From: plug-bounces at lists.pdxlinux.org
[mailto:plug-bounces at lists.pdxlinux.org] On Behalf Of Jason Van Cleve
Sent: Thursday, February 14, 2008 3:54 PM
To: General Linux/UNIX discussion and help; civil and on-topic; John Jason
Jordan
Subject: Re: [PLUG] Advanced Topics February meeting

Wow, Access, really?  I wrote a tool for my QA team to read sets of data
into Oracle for analysis, because playing with GUI shapes in Access gets
really frustrating and hard to edit, and its manual SQL
syntax--particular those parentheses-ridden joins--is nigh impossible to
work with.  They love my loader tool, because it gives them a "normal"
SQL interface (not to mention the power of a real RDBMS), and once you
get used to that, you won't go back to using GUIs.

No, there's no such thing as standard SQL, but most systems will support
90% of what you need (Access not included), with only minor differences.
The problem is, with an enterprise app', where performance is critical,
you'll inevitably find yourself tweaking your queries in system-specific
ways:  here a proprietary annotation, there a restructuring of
subqueries, just because your DB likes it better that way.  And that's
where ORMs fall short:  they simply can't know in every case what's the
best way to write a query.  They'll produce something that works, but to
make it hum, you're going to have to write that SQL by hand.

So I recommend biting that bullet and learning the basics of SQL, along
with whatever little quirks your DB introduces.  Learning outer joins,
"group by" and "having", "exists" and "not exists", and a few other
tricks will give you worlds of power, and you'll find those skills
really do transfer from one system to another, including Oracle, Sybase,
MySQL and Postgres (but not Access!).

With that knowledge, you'll be in a good position to tweak your queries
for best performance on whatever system you're using (except, well, you
know).

--Jason


> -----Original Message-----
> From: plug-bounces at lists.pdxlinux.org 
> [mailto:plug-bounces at lists.pdxlinux.org] On Behalf Of John 
> Jason Jordan
> Sent: Thursday, February 14, 2008 11:49 AM
> To: plug at lists.pdxlinux.org
> Subject: Re: [PLUG] Advanced Topics February meeting
> 
> 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.
> _______________________________________________
> PLUG mailing list
> PLUG at lists.pdxlinux.org
> http://lists.pdxlinux.org/mailman/listinfo/plug
> 
_______________________________________________
PLUG mailing list
PLUG at lists.pdxlinux.org
http://lists.pdxlinux.org/mailman/listinfo/plug