Re: A PGsendQuery API design for your review

On 02/02/16 21:04, Karl O. Pinc wrote:
> On Tue, 02 Feb 2016 19:58:16 +0100
> Federico Di Gregorii <fog@dndg.it> wrote:
>
>> I can say with some confidence that we'll never implement such an
>> API. Even the
>> ux, i.e., accepting multi-queries from the user is wrong, imho. Let
>> the user
>> write single queries, organize them, exrcute them using bound
>> variables and NOT
>> mogrify() and then return the result. Anything less is lazy.
>
> I'm don't think you've a complete picture of my users, what
> they're doing, and why it is useful to submit multiple queries
> at once.

Dear Karl,

I never said that your users are lazy. I said your design of the UX was
lazy. Let me explain: if you want to provide everything you describe in
the rest of your email, then splitting SQL in multiple queries is the
least of your problems (see below). What you really want is an
interactive environment with multiple "data pads", each asociated to a
query, that user can easily compare, save, restore and so on. You want
interactive SQL completion and that's much much more difficult than
splitting a query. Just sending a multi-query to the database,
leveraging your proposed API, won't help your users much.

Now, how to split a query? It's easy: you split at ";" unless inside a
text literal. Parsing text literals in SQL is incredibly easy: just
count the number of "'" you encounter: if odd then you're inside an
literal and you ignore the ";". PostgreSQL also has E'' literals but
they are just a little bit more complex - you have to check for "\;".
That's all. Now, parsing the SQL to implement completion, that's where
things get interesting... :)

TL;DR. Why add an API that nobody will use when what you need will
probably cost you just a couple of hours of coding?

Hope this clarifies why I don't like your proposal.

federico


>
> I support these people:
> http://amboselibaboons.nd.edu/
> http://gombechimpanzees.org/
>
> Who use interfaces like:
> http://papio.biology.duke.edu/babasewiki/MyStartingPage
> https://gombemi.ccas.gwu.edu/gombemiwiki/Main_Page
>
> My users are scientists, from undergraduates to senior
> researchers and American Academy of Science members, with a wide
> variety of SQL expertise.  They have direct access to their
> databases, writing SQL to explore their data.  They want a
> simple, comprehensive interface.  (One that not only provides
> support for SQL but also allows the unsophisticated to create
> their own tables, etc.  Which is neither here nor there.)
>
> They repeatedly try different queries and compare results,
> iterating until they tease the data apart.  They might write a
> query, run it, modify it, and then run both the modified and the
> original version.  They want to see the results in a single
> window so they can compare.  They want to be able to edit and
> tweak either or both and hit a "resubmit" button and again review
> the results.
>
> Some are sophisticated SQL users; regardless most don't want to
> think about SQL.  It's easier for them to cut and paste the same
> query 3 times and tweak the SQL to get 3 results (adult males,
> adult females, juveniles) than to try to write a single query
> that does a GROUP BY.  But they do want to be able to see and
> compare the results of the 3 queries.
>
> They use SQL post-validate their data and check for sanity after
> a bulk data upload.  They have a file containing from a few to
> 10s of SQL queries that they cut and paste into a "SQL window"
> and run.  Usually many of the queries produce a single number as
> output.  Then they examine the output and see if the numbers add
> up.
>
> They use sets of SELECT INTO queries to copy, or pre-join, or
> summarize data in the master schema, moving it into their own
> schema for further analysis.  They again use cut-and-paste to
> replay these operations when there's new data from a fresh bulk
> data upload.  (They likewise have full permissions, within their
> own schemas, to create whatever tables they need to support their
> analysis.)
>
> Etc.
>
> Of course everything _could_ be done by submitting a single query
> at a time.  (Or an interface that requires such parsing by the
> end-user, and then runs them in bulk.  Or some sort of arbitrary
> file format that contains easily parse-able statement delimiters.
> Etc.)  But an interface that allows submission of multiple
> statements in a single go, and review of all the results at once,
> greatly simplifies their work flow.  (And it's nice that their
> interface has point-and-click table creation, deletion, etc.)
>
> I'm surprised (Shocked I say!  Shocked! :-) that you'd want to
> place arbitrary restrictions on the user interfaces that can be
> created with psycopg2.  And Sir!  I must take offense at the
> implication that my users are lazy.  They work hard to press the
> semicolon key to end their statements!  ;-)
>
> Seriously, the real world is complex.  If PG allows me to create
> a user interface where the SQL parsing into separate statements
> is done by PG then psycopg2 should let me do the same.  There's
> a reason you can pipe multiple SQL statements to psql without
> having to pre-parse them into separate statements.
>
> If you really want to reject the idea of accepting multiple
> queries from the user let me know and I'll not pursue the
> matter.
>
> Regards,
>
>
> Karl <kop@meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                   -- Robert A. Heinlein
>


--
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
  There's no greys, only white that's got grubby. I'm surprised you
   don't know that. And sin, young man, is when you treat people as
   things. Including yourself.                       -- Granny Weatherwax