pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: SQL generator
5+ messages / 5 participants
[nested] [flat]
* Re: SQL generator
@ 2006-07-25 19:31 Andrew Hammond <andrew.george.hammond@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Andrew Hammond @ 2006-07-25 19:31 UTC (permalink / raw)
To: pgsql-sql
1) If you don't know how to write the SQL for this, then you might want
to ask yourself if you have sufficient expertise to write a tool which
generates such queries.
2) I have seen many attempts at query generators. I have yet to see a
design which achieves a good balance between simplicity and
flexibility. The fundamental problem, as near as I can tell, is that
many people just don't think that way. Filters similar to what you can
see in iTunes are about the best I've seen. I've never seen anything
that attempts to handle aggregates. Unless you really get relational
models, it's not even possible to sufficiently formulate your query. If
you do then learning how to write a query in SQL just isn't that much
more work.
3) Your example query doesn't even begin to imply what the underlying
schema would be. Since SQL is pretty tightly coupled to the schema upon
which it operates, this is a serious ommission. It's like asking for
directions when you know where you want to go, but can't describe where
you currently are.
4) I do not understand what the "aggregate functions" in your example
are intended to calculate.
Drew
Bit Byter wrote:
> Hi,
>
> This may be a little OT, but I don't know exactly where else to post
> it. I am writing a little utility that generates valid SQL using
> "English like" text. I want to use this to allow non-technical users to
> be able to quickly write their own queries, to search through a
> database that stores information about the sales of different
> companies. I can provide more information for anyone who wants to help.
>
> Currently, the syntax is :
>
> Select ALL PRODUCT_FILTER from COMPANY where funcname(params) conditon
> ... and ITEM_DATE date_condition
>
>
> Where:
>
> product_filter specifies the product type to be included in the search
> company specifies the company whose data is to be searched
> funcname is an aggregate function
> condition specifies the criteria for the aggregate function (i.e. a
> HAVING clause)
> date_condition specifies the criteria for the dates to be used in the
> search
>
> Note: there can be more than 1 aggregate function
>
> A typical query then may look like this:
>
> Select all 'toys' from 'Walmart' where average_sales(100) > 100000 and
> avaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and
> "01-Jan-06"
>
>
> I would like to know what the underlying SQL statement will look like,
> so that I can use this knowlege to build a generic parser that creates
> SQL statements from the "English like" text, using the syntax I
> described above.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL generator
@ 2006-07-25 20:39 Chris Browne <cbbrowne@acm.org>
1 sibling, 1 reply; 5+ messages in thread
From: Chris Browne @ 2006-07-25 20:39 UTC (permalink / raw)
To: pgsql-sql
"Bit Byter" <takashi_949@hotmail.com> writes:
> I would like to know what the underlying SQL statement will look
> like, so that I can use this knowlege to build a generic parser that
> creates SQL statements from the "English like" text, using the
> syntax I described above.
I seem to recall that Lotus tried doing this once upon a time with
trying to build an "english-like" interface for doing "business
intelligence-like" queries against spreadsheets.
Nobody likely remembers HAL; it was underwhelmingly useful.
In effect, anyone that was skilful enough at composing "English-like"
queries to get HAL to work was likely to find the "native" methods
more convenient.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/linuxxian.html
"La Cicciolina [...] Electing her was an interesting contrast to the
situation in the UK: In Italy they elect a representative from the sex
industry. In the UK, they elect their clients." -- Peter Gutmann
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL generator
@ 2006-07-26 04:00 Jonah H. Harris <jonah.harris@gmail.com>
parent: Andrew Hammond <andrew.george.hammond@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jonah H. Harris @ 2006-07-26 04:00 UTC (permalink / raw)
To: Andrew Hammond <andrew.george.hammond@gmail.com>; +Cc: pgsql-sql
> > A typical query then may look like this:
> >
> > Select all 'toys' from 'Walmart' where average_sales(100) > 100000 and
> > avaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and
> > "01-Jan-06"
See Microsoft's English Query for SQL Server or the SQ-HAL project for examples:
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part9/c3261.mspx?mfr=true
http://www.csse.monash.edu.au/hons/projects/2000/Supun.Ruwanpura/
In general, it's easier to write the SQL than to optimize natural
language-based query translation.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL generator
@ 2006-07-26 15:21 Aaron Bono <postgresql@aranya.com>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 5+ messages in thread
From: Aaron Bono @ 2006-07-26 15:21 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
On 7/25/06, Chris Browne <cbbrowne@acm.org> wrote:
>
> "Bit Byter" <takashi_949@hotmail.com> writes:
> > I would like to know what the underlying SQL statement will look
> > like, so that I can use this knowlege to build a generic parser that
> > creates SQL statements from the "English like" text, using the
> > syntax I described above.
>
> I seem to recall that Lotus tried doing this once upon a time with
> trying to build an "english-like" interface for doing "business
> intelligence-like" queries against spreadsheets.
>
> Nobody likely remembers HAL; it was underwhelmingly useful.
>
> In effect, anyone that was skilful enough at composing "English-like"
> queries to get HAL to work was likely to find the "native" methods
> more convenient.
There was a company near where I live who recently tried to do natural
language parsing for search engines - like Ask Jeeves. It was an utter
failure but before they got far enough to see that, they did a study of
users to see how they search. They found that people adapted to learn how
to "talk in the search engine's language" fairly easily and it has actually
become unnatural for people to talk to search engines with a natural
language.
I have had so many problems with users who know a little SQL causing
database crashes and slow downs. I don't think I would recommend allowing
someone who can't even do SQL access to a database.
If you still think it is a good idea to do this, good luck and I hope you
prove us wrong. But just keep in mind you are going to be attempting
something that has been tried before with little success. Maybe you will
figure out what your predecessors were missing...
Bottom line though, just how difficult is SQL to read and write anyway? It
is a hell of a lot easier to read and write than any other computer language
I have seen.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL generator
@ 2006-07-26 15:38 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2006-07-26 15:38 UTC (permalink / raw)
To: pgsql-sql
On Wed, Jul 26, 2006 at 10:21:01AM -0500, Aaron Bono wrote:
> Bottom line though, just how difficult is SQL to read and write anyway? It
> is a hell of a lot easier to read and write than any other computer language
> I have seen.
It's not SQL. It's the notion of relations. If the language were
QUEL or ENGLISH (a PICK variant) or whatever you have, but relations
underneath, the same problem would be happening.
Remember, even people who ought to understand this stuff, like
developers who program against databases for a living, frequently get
it wrong. I'm not surprised that casual users can accidentally do a
5-way Cartesian product. But it would make no difference if the
commands were specified in natural language, because the conceptual
confusion is the problem.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-07-26 15:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-25 19:31 ` Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-26 04:00 ` Jonah H. Harris <jonah.harris@gmail.com>
2006-07-25 20:39 ` Chris Browne <cbbrowne@acm.org>
2006-07-26 15:21 ` Aaron Bono <postgresql@aranya.com>
2006-07-26 15:38 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox