Re: interface for "non-SQL people"

On Fri, Oct 9, 2009 at 10:15 AM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:
>> On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> >
>> > The #1 tool you have at your disposal is the human brain.  I
>> > personally think GUI database tools are counter productive and huge
>> > time wasters.  SQL requires lateral thinking but once you have your
>> > head around how joins work and the general syntax of queries you
>> > should have no problem getting data out of your database.   SQL is a
>> > 'man machine interface' :-).  It's a very high level language with a
>> > lot of power.  The gui 'wrappers' that I've seen actually obfuscate
>> > the concepts.
>>
>> Amen to that.  I'd rather spend a little bit of my time each week
>> going over correlated subqueries with a user than trying to get good
>> performance on a reporting server that's hammered by bad queries.
>> Which is what a lot of query builders basically do.
>
> Good lord people. Not be helpful much? This guy is obviously not an SQL
> guy and nor should he have to be. My guess is he is an analyst sitting
> in a company somewhere that just wants to whip together some simple data
> entry reports or contact forms.
>
> There is *zero* reason he should have to dig around in the confounded
> tar pit of SQL. Not everyone can be an SQL guru.
>
> To answer the question.
>
> Open Office has a forms interface that works with PostgreSQL
> You can use MS Access
> You have use Kexi (KDE)
>
> There is also one written in GTK and Python but I don't recall the
> name...
>
> JD... Who sits in bewilderment
>

My 2 cents:

If the poster works in MS Windows, MS Access is the easiest, most
powerful and most flexible solution .... for a client application. I
use it for complex ad hoc projects as well as creating applications.
To keep maintenance simple, should I get hit by the proverbial bus, I
do not use any manually created code (SQL or VB) in my projects. I
use complex combinations of functions, but even those are typed within
the QBE (query by example) window.

Much of MS Access's comparative value comes from the fact that it
treats local tables, select queries and links to external data sources
as if they were all local tables. This means that you can nest
queries easily or join local tables to external tables. You can even
join tables from different database servers. Documentation for MS
Access is plentiful.

Keep in mind that MS Access does not share well and that the files
become corrupt/unusable when they reach 2GB (less admin resources for
tables, queries, etc) in size.

The down-side of GUI clients and ODBC connections is that you will not
be able to use features that are specific to the database server.

Most GUI database front-ends available for unix operating systems
focus on database administration as opposed to serious data analysis.
I tried Kexi and OpenOffice Base briefly, but became frustrated trying
to work with both local and remote data within the same project.

Gnuplot and R provide graphical views of data; but you still have to
write code (not SQL) manually. You also have to export data from the
database server in order to feed the data to these applications.

My rule of thumb for analysis is this: If I'm working in Windows PC,
I use MS Access. If I'm working in *BSD or Linux, I store SQL code in
text files and am careful to use a naming scheme that facilitates
management of those files.

I have tried using Windows and MS Access within virtual machines. It
works for small and simple queries. If you're doing large jobs or
complex analysis, however, you may have issues with memory management.

Here's some PostgreSQL-specific information:

If you are using MS Access as a client to a PostgreSQL server via
ODBC, you should keep the following in mind:
1. In the ODBC data source configuration, use true = -1.
2. In the database server, null values should not be allowed
for boolean (true/false, yes/no) data fields.

Best of luck,

Andrew