Loading ...
Sorry, an error occurred while loading the content.
 

RE: [hackers-il] RFC: standard SQL syntax for querying database i nformation

Expand Messages
  • Chen Shapira
    ... I agree 100%. One of the problems with the SQL standard is that its too small. I have to support 5 diffrent databases in my application
    Message 1 of 5 , Oct 23 11:29 AM
      > If not, maybe it would be a good idea to form a working group
      > that will
      > suggest a unified syntax for this kind of thing. It would
      > remove a lot of
      > cross-database compatibility issues.

      I agree 100%. One of the problems with the SQL standard is that its too
      small.
      I have to support 5 diffrent databases in my application
      (Oracle,Mysql,SQLserver,Postgres and Access)
      And I have large chunks of code dedicated to build a diffrent query for
      each.
      It doesn't cover meta-data like column names, table names, etc. (BTW. in
      sqlserver this data is avaliable in a seperate table that you can query, but
      you can't get the meta data about *that* table. chicken and egg problem)
      It doesn't cover important things like date manipulation, standard supported
      types, built in functions etc.

      And the vendors do their worst on top of this.
      SQL is never ever fully supported, and what they call "pure sql queries" are
      never optimized (in oracle they don't even go into the query pool!) they
      basicly force you to use their own special language features that are never
      similar to anything else.

      Yep, a change is defiantly in place.

      Think of how much noise there is around web-standards and having browsers
      fully support a good standard. This is getting to be quite an issue, and
      rightly so, otherwise web developers have to work 3 times as hard writing
      and maintaining cross-browser code.

      There should be a similar amount of an issue around the productivity being
      wasted and bugs being introduced by sql incompatability.

      IMO the first step should be to have a good, usefull, all-covering standard,
      not just an unusefull lowest common denominator.
      Then vendors will have a reason to support it, or will at least be able to.

      BTW, maybe most programmers/products just support one DB? perhaps there's
      not enough need for sql?

      Thanks,
      Chen.
    • Nadav Har'El
      ... I m no database expert (though I do know a little about SQL and MySQL), so take what I say with a grain of salt (and educate me when necessary!): How about
      Message 2 of 5 , Oct 23 2:55 PM
        On Mon, Oct 23, 2000, Chen Shapira wrote about "RE: [hackers-il] RFC: standard SQL syntax for querying database i nformation":
        >
        >
        > > If not, maybe it would be a good idea to form a working group
        > > that will
        > > suggest a unified syntax for this kind of thing. It would
        > > remove a lot of
        > > cross-database compatibility issues.
        >...
        > Yep, a change is defiantly in place.
        > IMO the first step should be to have a good, usefull, all-covering standard,
        > not just an unusefull lowest common denominator.
        > Then vendors will have a reason to support it, or will at least be able to.

        I'm no database expert (though I do know a little about SQL and MySQL), so
        take what I say with a grain of salt (and educate me when necessary!):

        How about inventing something, say SQL++, that looks like standard SQL with
        any additions you think are worthwhile (e.g., commands to get metadata). Then
        create a small library which converts SQL++ queries into queries suitable
        (and optimized) for the various DB programs (MySQL, etc.). This should be
        pretty easy to do (because all the varients are, after all, very similar to
        the given SQL), and does not require any standartization: if you write
        such a converter, and make it available, people may choose to use it. If
        you make it good enough, vendors may even choose to support this SQL++
        directly ;)

        Of course, your converter may have some trouble deciding which SQL varient
        to convert the queries into - whenever possible it will try to figure it out
        itself (by querying the server), but if it's not possible, it will have to
        be an option given by the user.

        Does this idea make any sense?

        It's like the idea of autoconf: instead of hoping (falsely) that all systems
        will become alike by standartization, you create a tool that uses the
        appropriate commands in each system. Of course, the fact that each system is
        mostly similar and a Unix derivative (e.g., you don't want it to work on a
        PDP-11 running Unix III and Commodore-64 running Basic) makes autoconf's task
        very doable.

        --
        Nadav Har'El | Monday, Oct 23 2000, 25 Tishri 5761
        nyh@... |-----------------------------------------
        Phone: +972-53-245868, ICQ 13349191 |You do not need a parachute to skydive.
        http://nadav.harel.org.il |You only need one to skydive twice.
      • Shlomi Fish
        ... Well, judging from your letter, we can see that the standard needs to cover the following aspects: 1. Querying the database for meta-data (column names,
        Message 3 of 5 , Oct 25 12:07 AM
          On Mon, 23 Oct 2000, Chen Shapira wrote:

          >
          >
          > > If not, maybe it would be a good idea to form a working group
          > > that will
          > > suggest a unified syntax for this kind of thing. It would
          > > remove a lot of
          > > cross-database compatibility issues.
          >
          > I agree 100%. One of the problems with the SQL standard is that its too
          > small.
          > I have to support 5 diffrent databases in my application
          > (Oracle,Mysql,SQLserver,Postgres and Access)
          > And I have large chunks of code dedicated to build a diffrent query for
          > each.
          > It doesn't cover meta-data like column names, table names, etc. (BTW. in
          > sqlserver this data is avaliable in a seperate table that you can query, but
          > you can't get the meta data about *that* table. chicken and egg problem)
          > It doesn't cover important things like date manipulation, standard supported
          > types, built in functions etc.
          >
          > And the vendors do their worst on top of this.
          > SQL is never ever fully supported, and what they call "pure sql queries" are
          > never optimized (in oracle they don't even go into the query pool!) they
          > basicly force you to use their own special language features that are never
          > similar to anything else.
          >
          > Yep, a change is defiantly in place.
          >
          > Think of how much noise there is around web-standards and having browsers
          > fully support a good standard. This is getting to be quite an issue, and
          > rightly so, otherwise web developers have to work 3 times as hard writing
          > and maintaining cross-browser code.
          >
          > There should be a similar amount of an issue around the productivity being
          > wasted and bugs being introduced by sql incompatability.
          >
          > IMO the first step should be to have a good, usefull, all-covering standard,
          > not just an unusefull lowest common denominator.
          > Then vendors will have a reason to support it, or will at least be able to.
          >

          Well, judging from your letter, we can see that the standard needs to
          cover the following aspects:
          1. Querying the database for meta-data (column names, column types, tables
          in a DB, etc.)
          2. Standard way to do date manipulation.
          3. Have a comprehensive list of standard types, from which DB vendors can
          choose to support a subset. (a list of the supported types can be
          retrieved by item #1)
          4. Have a comprehensive list of standard built-in SQL functions, which DB
          vendors implement a subset of. (again, a list of the supported functions
          can be retrieved by item #1)
          5. A standard syntax to create and drop indexes.
          6. Did I miss anything?

          Anyway, we could form a working group (i.e: mailing-list, web-site, etc.)
          that will try to standarize that. If we put an announement for it on
          Slashdot, we may get recognition and acceptance, at least from the free
          software world. The way I see it, even if only MySQL, PostgreSQL and
          InterBase support this standard initially, it may eventually mean that
          commercial DB vendors will also support it.

          What do you think of it?

          Regards,

          Shlomi Fish


          ----------------------------------------------------------------------
          Shlomi Fish shlomif@...
          Home Page: http://t2.technion.ac.il/~shlomif/
          Home E-mail: shlomif@...

          The prefix "God Said" has the extraordinary logical property of
          converting any statement that follows it into a true one.
        • Shlomi Fish
          ... I did not receive any reply about this letter, so I m asking you people more precisely. Do you want me to take this idea forward. That is: 1. Form a
          Message 4 of 5 , Oct 26 7:21 AM
            On Wed, 25 Oct 2000, Shlomi Fish wrote:

            > On Mon, 23 Oct 2000, Chen Shapira wrote:
            >
            > >
            > >
            > > > If not, maybe it would be a good idea to form a working group
            > > > that will
            > > > suggest a unified syntax for this kind of thing. It would
            > > > remove a lot of
            > > > cross-database compatibility issues.
            > >
            > > I agree 100%. One of the problems with the SQL standard is that its too
            > > small.
            > > I have to support 5 diffrent databases in my application
            > > (Oracle,Mysql,SQLserver,Postgres and Access)
            > > And I have large chunks of code dedicated to build a diffrent query for
            > > each.
            > > It doesn't cover meta-data like column names, table names, etc. (BTW. in
            > > sqlserver this data is avaliable in a seperate table that you can query, but
            > > you can't get the meta data about *that* table. chicken and egg problem)
            > > It doesn't cover important things like date manipulation, standard supported
            > > types, built in functions etc.
            > >
            > > And the vendors do their worst on top of this.
            > > SQL is never ever fully supported, and what they call "pure sql queries" are
            > > never optimized (in oracle they don't even go into the query pool!) they
            > > basicly force you to use their own special language features that are never
            > > similar to anything else.
            > >
            > > Yep, a change is defiantly in place.
            > >
            > > Think of how much noise there is around web-standards and having browsers
            > > fully support a good standard. This is getting to be quite an issue, and
            > > rightly so, otherwise web developers have to work 3 times as hard writing
            > > and maintaining cross-browser code.
            > >
            > > There should be a similar amount of an issue around the productivity being
            > > wasted and bugs being introduced by sql incompatability.
            > >
            > > IMO the first step should be to have a good, usefull, all-covering standard,
            > > not just an unusefull lowest common denominator.
            > > Then vendors will have a reason to support it, or will at least be able to.
            > >
            >
            > Well, judging from your letter, we can see that the standard needs to
            > cover the following aspects:
            > 1. Querying the database for meta-data (column names, column types, tables
            > in a DB, etc.)
            > 2. Standard way to do date manipulation.
            > 3. Have a comprehensive list of standard types, from which DB vendors can
            > choose to support a subset. (a list of the supported types can be
            > retrieved by item #1)
            > 4. Have a comprehensive list of standard built-in SQL functions, which DB
            > vendors implement a subset of. (again, a list of the supported functions
            > can be retrieved by item #1)
            > 5. A standard syntax to create and drop indexes.
            > 6. Did I miss anything?
            >
            > Anyway, we could form a working group (i.e: mailing-list, web-site, etc.)
            > that will try to standarize that. If we put an announement for it on
            > Slashdot, we may get recognition and acceptance, at least from the free
            > software world. The way I see it, even if only MySQL, PostgreSQL and
            > InterBase support this standard initially, it may eventually mean that
            > commercial DB vendors will also support it.
            >

            I did not receive any reply about this letter, so I'm asking you people
            more precisely. Do you want me to take this idea forward. That is:
            1. Form a mailing-list.
            2. Create a preliminary web-page.
            3. Post an announcement with the details of both those facilities here on
            hackers-il.
            4. Compose an article to be posted to Slashdot and post it on the
            mailing-list.
            5. After everybody are content with the article, send it to Slashdot.
            6. Start discussing the standard.

            I'd like to call this initiative "Extended-SQL" or "X-SQL" for short.

            Now, that I am more decisive, what does everybody feel about it?

            Regards,

            Shlomi Fish


            > What do you think of it?
            >
            > Regards,
            >
            > Shlomi Fish
            >
            >
            > ----------------------------------------------------------------------
            > Shlomi Fish shlomif@...
            > Home Page: http://t2.technion.ac.il/~shlomif/
            > Home E-mail: shlomif@...
            >
            > The prefix "God Said" has the extraordinary logical property of
            > converting any statement that follows it into a true one.
            >
            >
            >
            > To unsubscribe from this group, send an email to:
            > hackers-il-unsubscribe@egroups.com
            >
            >
            >
            >



            ----------------------------------------------------------------------
            Shlomi Fish shlomif@...
            Home Page: http://t2.technion.ac.il/~shlomif/
            Home E-mail: shlomif@...

            The prefix "God Said" has the extraordinary logical property of
            converting any statement that follows it into a true one.
          • Chen Shapira
            ... I m pessimistic about these kinds of groups. A working group of this kind would have a chance to succeed when/if we ll have representetives of all the big
            Message 5 of 5 , Oct 26 8:54 AM
              >
              > I'd like to call this initiative "Extended-SQL" or "X-SQL" for short.
              >
              > Now, that I am more decisive, what does everybody feel about it?

              I'm pessimistic about these kinds of groups.
              A working group of this kind would have a chance to succeed when/if we'll
              have representetives of all the big DB vendors
              (Oracle,MS,IBM,Borland,Informix).
              A standard that is supported by MySQL and PostGres isn't much of an
              improvement, the whole point of a standard is to have support by *every
              one*.
              I'm also pessimistic about standards.

              Anyway, if you form such group please invite Mr. Reuven Lerner. He has wide
              Database knowledge, and perhaps even enough connections to help this
              grassroots group grow (Article on Linux Journal can help). Perhaps you can
              also contact Moshe Bar, although he works mostly with Oracle, and mainframe
              DB's (SAP,BAAN,ERP).

              If you have the spare time to invest in this initiative - Yishar Koach!
            Your message has been successfully submitted and would be delivered to recipients shortly.
            »
            «