From: Andrew Hammond <andrew.george.hammond@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL generator
Date: 25 Jul 2006 12:31:35 -0700
Message-ID: <1153855895.203541.278400@i42g2000cwa.googlegroups.com> (raw)
References: <1153395978.590189.261100@75g2000cwc.googlegroups.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andrew.george.hammond@gmail.com
Subject: Re: SQL generator
In-Reply-To: <1153855895.203541.278400@i42g2000cwa.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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