Is SQL silly as an RDBMS<->app interface?
Hi, this is a general RDBMS question, not specific to pg. It occurred to
me while I was trying to design an interface between application and
SQL.
Suppose that the user fills in a complex query form, and you are coding
the application that converts the user's input to a where clause. It may
prove beneficial if you construct a treelike structure like this:
AND
|
+-OR
| |
| + Condition A
| |
| + Condition B
|
+-OR
|
+ Condition C
|
+ AND
|
+ Condition D
|
+ Condition E
|
+ Condition F
This would become
WHERE (A OR B) AND (C OR (D AND E AND F))
It seems complex at first, but the code will be cleaner, scale better,
and be made portable easier if you are adding nodes and leaves to a tree
as you are scanning the user's input, than if you try to construct a
where clause directly. After finishing with the tree, it is
straightforward to convert it to a where clause, after which you send
the SQL to the RDBMS.
What will the RDBMS do next? It will parse your SQL statement and
presumably convert it to a tree of conditions. Well, I had that ready in
the first place!
Whether my idea about the tree is good or not, it is true that the
application initially has its data in some data structures suitable for
computers rather than humans; it converts them to SQL, which is suitable
for humans, only so that the SQL will be converted back to structures
suitable for computers. The most obvious example is that integers are
converted to decimal by the application only to be converted back to
binary by the RDBMS.
I understand that SQL is the interface between apps and RDBMS's because
of history, not because it is correct design. Could you point me to a
link or book or paper that deals with this paradox? Thanks!
On Sun, Jul 13, 2003 at 01:24:12PM +0300, Antonios Christofides wrote:
<snip>
What will the RDBMS do next? It will parse your SQL statement and
presumably convert it to a tree of conditions. Well, I had that ready in
the first place!<snip>
I understand that SQL is the interface between apps and RDBMS's because
of history, not because it is correct design. Could you point me to a
link or book or paper that deals with this paradox? Thanks!
There is no paradox. SQL is a standard method of describing queries that
most databases understand. It is readable by humans. The structures used
within a database are specific to that database and not usable by humans.
The structures used in your code are different from those a database would
use.
SQL is the conduit that allows one program or person to describe a query to
a server without getting bogged down in meaningless detail. In a way it is
like the 64Kb connections in the phone system; each endpoint can be a
person, answering machine, mobile phone service, etc but they can all talk
to eachother because they can convert to a common standard.
So, SQL may not be the best way of doing it but it is widly used and
well-understood. It transports the meaning in a way independant of the
programs using it.
Hope this helps.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
"the West won the world not by the superiority of its ideas or values or
religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
- Samuel P. Huntington
Antonios Christofides wrote:
[...]
suitable for computers. The most obvious example is that integers are
converted to decimal by the application only to be converted back to
binary by the RDBMS.
Very good example, indeed. How does the 64bit big-endian DB server use
your frontend's little-endian 32bit integer value? Not to speak of the
binary representation of your expression tree.
You have to do some conversion. By convention this is done by converting
into and from a communication protocol that is possibly different from
the internal representation on at least one side.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Sun, 2003-07-13 at 05:24, Antonios Christofides wrote:
Hi, this is a general RDBMS question, not specific to pg. It occurred to
me while I was trying to design an interface between application and
SQL.
[snip]
It seems complex at first, but the code will be cleaner, scale better,
and be made portable easier if you are adding nodes and leaves to a tree
as you are scanning the user's input, than if you try to construct a
where clause directly. After finishing with the tree, it is
straightforward to convert it to a where clause, after which you send
the SQL to the RDBMS.What will the RDBMS do next? It will parse your SQL statement and
presumably convert it to a tree of conditions. Well, I had that ready in
the first place!Whether my idea about the tree is good or not, it is true that the
application initially has its data in some data structures suitable for
computers rather than humans; it converts them to SQL, which is suitable
for humans, only so that the SQL will be converted back to structures
suitable for computers. The most obvious example is that integers are
converted to decimal by the application only to be converted back to
binary by the RDBMS.
When Oracle bought Rdb from Digital, it got DSRI, the Digital
Standard Relatonal Interface. A direct API into Rdb. No one
uses it, though. Why? It's too complicated.
I understand that SQL is the interface between apps and RDBMS's because
of history, not because it is correct design. Could you point me to a
link or book or paper that deals with this paradox? Thanks!
Who says SQL's design is incorrect? SQL became the de facto
standard because it was better than the competing relational
interface languages, not because of Borg-like tactics.
--
+-----------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| 4 degrees from Vladimir Putin
+-----------------------------------------------------------+
I understand that SQL is the interface between apps and RDBMS's because
of history, not because it is correct design. Could you point me to a
link or book or paper that deals with this paradox? Thanks!
I'm not sure what you mean by 'correct design'.
I think you should go back and read the works of Codd and Date on the
development of relational databases. One point that was made early on is
that RDBMS theory doesn't guarantee efficiency, but it does guarantee
consistency and accuracy in the results. (Efficiency is an implementation
issue, though without high-speed computers relational databases would
probably not be practical anyway.)
The same can be said of the SQL standard and any 'natural language' query.
There is always a way to structure a query properly, even though it may be
ugly-looking. It isn't about how the query looks, it is about being able
to ask the question--ANY question.
Back in the late 60's, when I was a budding CS/EE major, the big hardware
'concept' was associative memory, which was supposed to be more human-like
in terms of how it functioned, though grossly inefficient in terms of its
use of hardware cycles.
--
Mike Nolan
At 01:24 PM 7/13/2003 +0300, Antonios Christofides wrote:
Whether my idea about the tree is good or not, it is true that the
application initially has its data in some data structures suitable for
computers rather than humans; it converts them to SQL, which is suitable
for humans, only so that the SQL will be converted back to structures
suitable for computers. The most obvious example is that integers are
converted to decimal by the application only to be converted back to
binary by the RDBMS.I understand that SQL is the interface between apps and RDBMS's because
of history, not because it is correct design. Could you point me to a
link or book or paper that deals with this paradox? Thanks!
Often one man's impedance mismatch is another man's layer of abstraction.
An integer in the app is often not the same thing as an integer in the
database. Have fun when it comes to dates and times.
As SQL is a somewhat human readable/writable API, people can leverage[1]OK so I used the L word.
tons of different tools to use or abuse it. And that's what has been done.
I daresay if someone came up with an ultra-elegant mathematical efficient
DB interface, only a few geniuses would actually read the thesis,
understand it and use it.
Also in most cases I've seen on this list, the bottlenecks are elsewhere
(e.g. I/O, wrong plan, query, db schema) so this sort of thing might be an
unnecessary optimization.
HTH,
Link.
[1]: OK so I used the L word.
On Sun, 2003-07-13 at 10:17, nolan@celery.tssi.com wrote:
I understand that SQL is the interface between apps and RDBMS's because
of history, not because it is correct design. Could you point me to a
link or book or paper that deals with this paradox? Thanks!I'm not sure what you mean by 'correct design'.
I think you should go back and read the works of Codd and Date on the
development of relational databases. One point that was made early on is
that RDBMS theory doesn't guarantee efficiency, but it does guarantee
SQL is only one possible relational query language. It didn't
become de facto standard until the mid- to late-80s.
It is an outgrowth of SEQEL (Structured English QuEry Language),
which was IBM's 1st try at a descriptive query language. DEC
had RDML (Relational Data Manipulation Language) to access it's
RDBMS. I'm sure that Burroughs, etc, had their own access methods,
too.
--
+-----------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| 4 degrees from Vladimir Putin
+-----------------------------------------------------------+
On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote:
SQL is only one possible relational query language. It didn't
become de facto standard until the mid- to late-80s.It is an outgrowth of SEQEL (Structured English QuEry Language),
which was IBM's 1st try at a descriptive query language. DEC
had RDML (Relational Data Manipulation Language) to access it's
RDBMS. I'm sure that Burroughs, etc, had their own access methods,
too.
Of course, in the context of a PostgreSQL list you can't forget QUEL and
PostQUEL, Ingres and POSTGRES query languages respectively.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)
On Mon, 14 Jul 2003, Alvaro Herrera wrote:
On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote:
SQL is only one possible relational query language. It didn't
become de facto standard until the mid- to late-80s.It is an outgrowth of SEQEL (Structured English QuEry Language),
which was IBM's 1st try at a descriptive query language. DEC
had RDML (Relational Data Manipulation Language) to access it's
RDBMS. I'm sure that Burroughs, etc, had their own access methods,
too.Of course, in the context of a PostgreSQL list you can't forget QUEL and
PostQUEL, Ingres and POSTGRES query languages respectively.
SQL is almost the worst standard I've come across. Its the
computer equivalent of VHS. Its not readable by computer or humans. (Enough
Flaming on to why I think this)
SQL is verbose it often make you repeat your self when its obvious
what you mean.
INSERT INTO a (b,c) SELECT a+4 as b, c*6 as c from a;
SQL has many different ways of writing the same thing for
different purposes. eg
INSERT INTO a (b,c) VALUES (1,2);
UPDATE a set b=1, c=2 WHERE d=3;
Why not
INSERT INTO a set b=1, c=3;
its certainly more readable and consistent.
Parsing is hard work
No Meta Standard (How do you find out the structure of your table using
pure SQL?
Very difficult to operate with Trees and simple hierarchal data.
I could continue. Still its a language we all love to hate.
Peter Childs
Peter Childs <blue.dragon@blueyonder.co.uk> writes:
SQL is almost the worst standard I've come across.
It's certainly got its bad points, but if it's so bad how did it get to
be the de facto standard? There were plenty of alternatives awhile back.
No Meta Standard (How do you find out the structure of your table using
pure SQL?
See INFORMATION_SCHEMA.
regards, tom lane
On Mon, 2003-07-14 at 04:36, Peter Childs wrote:
On Mon, 14 Jul 2003, Alvaro Herrera wrote:
On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote:
[snip]
SQL is almost the worst standard I've come across. Its the
computer equivalent of VHS. Its not readable by computer or humans. (Enough
Flaming on to why I think this)SQL is verbose it often make you repeat your self when its obvious
what you mean.INSERT INTO a (b,c) SELECT a+4 as b, c*6 as c from a;
Without a WHERE clause, would that "just" double the number of
tuples, or recurse forever?
SQL has many different ways of writing the same thing for
different purposes. eg
INSERT INTO a (b,c) VALUES (1,2);
UPDATE a set b=1, c=2 WHERE d=3;Why not
INSERT INTO a set b=1, c=3;
its certainly more readable and consistent.
That's debatable.
If fields b and c are the only fields in the table, you can say:
INSERT INTO A VALUES (1, 2);
When there's a dozen fields in table A, your method seems that
it would get pretty unwieldy.
--
+-----------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| 4 degrees from Vladimir Putin
+-----------------------------------------------------------+
Hi all:
I am new to PostgreSQL DB, however I have years experience with Oracle 8i
and MS SQL. I am in the process to promot PostgreSQL to my future client,
due to the cost. I am just wondering if overall people feels frustrated with
PostgreSQL or feels happey with it.
I know MySQL is simpiler and cheap. With my years experience with enterprise
level DB like Oracle and MS SQL, I just don't feel right with MySQL. I love
stored procedure. Sorry to MySQL lovers.
I have the following questions. Please reply me offline, so the mailling
list won't get flood. Thanks!
1. What is your favorite GUI tool for PostgreSQL?
2. In your organization, do you have someone who works as full time
PostgreSQL DBA?
3. What is the biggest challenge you have with PostgreSQL? Administration or
Programming?
4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
5. How often do your PostgreSQL run into problem or crash? Are most of the
problem caused by PostgreSQL itself?
Any suggestion and help are welcome. Thanks!
On 14 Jul 2003 at 15:16, Terence Chang wrote:
Hi all:
I am new to PostgreSQL DB, however I have years experience with Oracle
8i and MS SQL. I am in the process to promot PostgreSQL to my future
client, due to the cost. I am just wondering if overall people feels
frustrated with PostgreSQL or feels happey with it.
I've been working with databases for about 15 years, including
Sybase, Oracle, PostgreSQL, and MySQL. Yes, I'm happy with
PostgreSQL. No frustration at all.
I know MySQL is simpiler and cheap. With my years experience with
enterprise level DB like Oracle and MS SQL, I just don't feel right
with MySQL. I love stored procedure. Sorry to MySQL lovers.
Stored procedures is one reason I moved http://www.FreshPorts.org/
from MySQL to PostgreSQL.
I have the following questions. Please reply me offline, so the mailling
list won't get flood. Thanks!
If we reply to the list, everyone who asks the same questions as you
will be able to find them in the archives.
1. What is your favorite GUI tool for PostgreSQL?
I usually use command line tools for PG work, but do use PGAdmin from
time to time.
2. In your organization, do you have someone who works as full time
PostgreSQL DBA?
Yes.
3. What is the biggest challenge you have with PostgreSQL?
Administration or Programming?'
Finding more time to spend working with it.
4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
Yes. Yes. Friends don't let friends use MySQL.
5. How often do your PostgreSQL run into problem or crash? Are most of
the problem caused by PostgreSQL itself?
I have never seen a PostgreSQL crash. Most if not all of the
problems have been caused by myself.
--
Dan Langille : http://www.langille.org/
--- Terence Chang <TChang@nqueue.com> wrote:I have the following questions. Please reply me
offline, so the mailling
list won't get flood. Thanks!
But if they don't read it, they can't correct me!
;-)
1. What is your favorite GUI tool for PostgreSQL?
I create apps using MS Access as the GUI front-end.
For administration, I use scripts and the command
line.
2. In your organization, do you have someone who
works as full time
PostgreSQL DBA?
No. We wear a lot of hats around here.
3. What is the biggest challenge you have with
PostgreSQL? Administration or
Programming?
Programming -- if I were a Programmer or a DBA, I
could do more. A solid database, a solid operating
system and a little scripting make day-to-day
administration easy enough.
4. Overall, do you like PostgreSQL? Would you
recommend it over MySQL?
Yes, and yes. MySQL has a reputation for ease of
administration; however, I moved from MySQL to
PostgreSQL because missing features in MySQL meant
difficult work-arounds for users. Also, the \help
system in psql is very useful for people learning SQL.
As an end-user, I found PostgreSQL easier to use
than MySQL.
5. How often do your PostgreSQL run into problem or
crash? Are most of the
problem caused by PostgreSQL itself?
At work, I haven't had any unplanned down time since I
started using PostgreSQL over 3 years ago. At home, I
had a hard drive crash; but the databases were
restored from backup files without any problems.
Dependable backups should _not_ be taken for granted!
Best regards,
Andrew Gould
On Mon, 2003-07-14 at 17:44, Dan Langille wrote:
On 14 Jul 2003 at 15:16, Terence Chang wrote:
Hi all:
[snip]
Stored procedures is one reason I moved http://www.FreshPorts.org/
from MySQL to PostgreSQL.
Oh, the shame! Advertising AOL For Broadband on a FreeBSD ports
site?
--
+-----------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| 4 degrees from Vladimir Putin
+-----------------------------------------------------------+
I have been working with various databases since the 80's.
Terence Chang wrote:
Hi all:
I am new to PostgreSQL DB, however I have years experience with Oracle 8i
and MS SQL. I am in the process to promot PostgreSQL to my future client,
due to the cost. I am just wondering if overall people feels frustrated with
PostgreSQL or feels happey with it.I know MySQL is simpiler and cheap. With my years experience with enterprise
level DB like Oracle and MS SQL, I just don't feel right with MySQL. I love
stored procedure. Sorry to MySQL lovers.I have the following questions. Please reply me offline, so the mailling
list won't get flood. Thanks!
1. What is your favorite GUI tool for PostgreSQL?
I use PGAdmin for a quick conversion of Indexes and Tables from MS-SQL.
/access But for other items I either use the command line or EMS
PostgreSQL Manager - This is the favorite tool of the developers I have
working on various projects.
We develope web applications and applicationsin Delphi, C++
2. In your organization, do you have someone who works as full time
PostgreSQL DBA?
No.
3. What is the biggest challenge you have with PostgreSQL? Administration or
Programming?
My biggest challenge is getting funding to convert other projects
completed using MS-SQL to PostgreSQL, but they are coming around.
PostgreSQL has been very easy to program and administer.
4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
Coming from a background where data integrity is a high priority as well
as performance - it's PostgrSQL hands down.
5. How often do your PostgreSQL run into problem or crash? Are most of the
problem caused by PostgreSQL itself?
It hasn't for over five years.
Any suggestion and help are welcome. Thanks!
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Tim.
On 14 Jul 2003 at 20:55, Ron Johnson wrote:
On Mon, 2003-07-14 at 17:44, Dan Langille wrote:
On 14 Jul 2003 at 15:16, Terence Chang wrote:
Hi all:
[snip]
Stored procedures is one reason I moved http://www.FreshPorts.org/
from MySQL to PostgreSQL.Oh, the shame! Advertising AOL For Broadband on a FreeBSD ports
site?
Yep. I'll take money from almost anyone.
--
Dan Langille : http://www.langille.org/
1. What is your favorite GUI tool for PostgreSQL?
Just psql in an emacs window.
Emacs lets me see large result sets, and keep a history of my commands.
2. In your organization, do you have someone who works as full time
PostgreSQL DBA?
Our Oracle DBA is also the DBA for our production PostgreSQL databases.
Developers administer their own databases (some Oracle, some PostgreSQL).
3. What is the biggest challenge you have with PostgreSQL?
Administration or Programming?
Challenge programming:
Familiarity in the group with Oracle makes some tasks quicker in Oracle.
Challenge administrating:
Remembering when to analyze (especially remembering to stick analyze
in the middle of big nightly scripts that make large temporary tables).
4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
Personally I like it a lot. Definately over MySQL, and even over Oracle
for anything containing data that don't have complicated replication needs.
My DBA, however, prefers Oracle over PostgreSQL. But he doesn't have the
budget for Oracle for all our systems. :-) I think he prefers PostgreSQL
over MySQL, though.
5. How often do your PostgreSQL run into problem or crash? Are most of the
problem caused by PostgreSQL itself?
Never had a crash with PostgreSQL itself. One problems with a table
where "analyze"'s sampling would generate bad stastics (correlation) for
some tables and make the planner pick slow plans. This was worked around
by reordering data in the table.
Any suggestion and help are welcome. Thanks!
One suggestion... if you get a lot of off-the-mailing-list responses,
could you post a summary?
Ron
On Monday 14 Jul 2003 11:16 pm, Terence Chang wrote:
Hi all:
I am new to PostgreSQL DB, however I have years experience with Oracle 8i
and MS SQL. I am in the process to promot PostgreSQL to my future client,
due to the cost. I am just wondering if overall people feels frustrated
with PostgreSQL or feels happey with it.
Pretty happy - easier to admin remotely the MS-SQL.
I know MySQL is simpiler and cheap. With my years experience with
enterprise level DB like Oracle and MS SQL, I just don't feel right with
MySQL. I love stored procedure. Sorry to MySQL lovers.I have the following questions. Please reply me offline, so the mailling
list won't get flood. Thanks!
1. What is your favorite GUI tool for PostgreSQL?
Mostly use psql, otherwise phpPgAdmin or PgAdmin if I'm on a windows box -
I've recently recommended PgAdmin to a client I'm working with and after a
couple of hours use he seems to like it.
2. In your organization, do you have someone who works as full time
PostgreSQL DBA?
Small devt/support shop, so it's me, and I'm not full time for any particular
client.
3. What is the biggest challenge you have with PostgreSQL? Administration
or Programming?
Hmm - error messages in plpgsql - not always as informative as they might be.
So that would be Programming.
4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
Yes, very pleased, and yes, I'd recommend it over MySQL. I use both, but I've
come to the conclusion that for the sort of custom systems I tend to build /
support it makes sense to use a more sophisticated package and perhaps spend
a few hundred pounds on hardware if it really matters.
5. How often do your PostgreSQL run into problem or crash? Are most of the
problem caused by PostgreSQL itself?
Never seen a crash on my systems (well, not one that was caused by PG). I'd
not say I've never read about a crash on the mailing lists in the few years
I've been here, but most of them seemed to be hardware related in the end.
--
Richard Huxton
On 14 Jul 2003 at 15:16, Terence Chang wrote:
due to the cost. I am just wondering if overall people feels frustrated with
PostgreSQL or feels happey with it.
Happy; and you can't beat the cost!
1. What is your favorite GUI tool for PostgreSQL?
PgAdmin, though I use psql directly on the Postgres machine a lot
too.
2. In your organization, do you have someone who works as full time
PostgreSQL DBA?
No - just me, and I work part-time both as developer and DBA.
3. What is the biggest challenge you have with PostgreSQL? Administration or
Programming?
Programming; our applications are fairly low-traffic, so the
Postrgres box just sits there and runs itself most of the time.
4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
I've no experience of MySQL, but I like Postgres a lot - I learnt all
my DBMS stuff on Postgres, and found it a good learning tool as well
as a solid DB backend.
5. How often do your PostgreSQL run into problem or crash? Are most of the
problem caused by PostgreSQL itself?
It has never crashed, in over four years - all problems I ran into
were caused by myself.
--Ray.
-------------------------------------------------------------
Raymond O'Donnell http://www.galwaycathedral.org/recitals
rod@iol.ie Galway Cathedral Recitals
-------------------------------------------------------------
