Looking for minimal SQL

[Marco Aschwanden]

Hi

I would like to develop an app that is (more or less) database independet.
Python DB API helps when masking "parameters " of sql statements. The db
driver cares for the correct conversion of a date, text, etc. This already
is a big step into the right direction.

The next step would be to use the least common denominator of all sql
dialects and do without all the sql goodies that the dialects offer... and
for this part I am wondering if anyone has a link / hint / book that
assembled this minimum sql. I am looking for something that says for
example:

Table/DB Design: always use lowercase letters, ...

Allowed sql functions: min, max, count

WHERE / HAVING hints:
- Don't use subselects

The focus is actually limited to SELECT / INSERT / UPDATE / DELETE (I
don't think that GRANT / CREATE XXX / ... statements are very portable).

I am aware, that a solution created this way is not as fast and as elegant
as an app that uses all features of a dialect, but I am really looking for
something database independent...

Thanks for any hints,
Marco

[Pierre-Frédéric Caillaud]

I am aware, that a solution created this way is not as fast and as
elegant as an app that uses all features of a dialect, but I am really
looking for something database independent...

What kind of application are you developing ?

[Marco Aschwanden]

> What kind of application are you developing ?

I am developing nothing right now. I still am evaluating... eventually a
simple CRM (Customer Relationship Management). I might start doing
something next year, but my brain started to create a framework.

There are several reasons why I would like to stay "db" independent: For
one I would like to run the "same" application over some rpc-server (which
would have a powerful db) or as a standalone application with a "simple"
db behind (which will synchronized from time to time *plan*plan*). I have
already implemented a "simple" framework that allows me to reuse the same
code be it as a multi-tier app be it as a standalone app. A database
switch in the future is possible... hence... etc. etc.

-> Python offers openess when it comes to os platforms
-> db api offers a certain openness when it comes to dealing and
parameterizing sql-stmts ... I want to stick to a minimal sql-language set
to achieve real db openness

I am aware that programming "sql-neutral" is not the most efficient, the
easiest way to go. For example: If Sub-Selects are not portable than I
would split up the queries and glue them together with Python. If for
example one looks for the oldest customer:

With sub-selects you may write:

SELECT * FROM customer WHERE age = (SELECT max(age) FROM customer)

Without sub-selects I would have to write two seperate statements:

SELECT max(age) FROM customer --> take the "age" out of this query
"SELECT * FROM customer WHERE age = ?", (age)

This might not be the fastest solution but I would like to try!

Hope this answers your question,

Marco

[Oliver Fromme]

Marco Aschwanden <PP**********@s pammotel.com> wrote:

The next step would be to use the least common denominator of all sql
dialects and do without all the sql goodies that the dialects offer... and
for this part I am wondering if anyone has a link / hint / book that
assembled this minimum sql.
Then you will have a _very_ limited subset of SQL, especially
when you take mysql into account, which is not very standard-
ANSI-SQL compatible, as far as I know. For example, the
operator "||" is string-concatenation in standard SQL, while
it means logical OR in mysql. Double-quotes are used for
quoted identifiers in standard SQL, while they're used to
enclose strings in mysql. etc. etc.
example:

Table/DB Design: always use lowercase letters, ...
You will also have to be careful to avoid the reserved words
of _all_ SQL dialects. Not easy. Also, don't make your
identifiers longer than 20 characters.
The focus is actually limited to SELECT / INSERT / UPDATE / DELETE (I
don't think that GRANT / CREATE XXX / ... statements are very portable).

Both GRANT and CREATE are specified in standard SQL, and I
believe all the major databases conform to it (I don't know
if mysql does, though).

Maybe the PostgreSQL documentation is helpful for you:
For each SQL command, it contains a description of its
standard SQL conformance. Here's the URL:

http://www.postgresql.org/docs/7.4/s...-commands.html

If you stick to that, your SQL code will most probably also
work with Oracle, Sybase and other "serious" databases.

Best regards
Oliver

PS: When I say "standard SQL", I mean ANSI SQL92 and SQL99.

--
Oliver Fromme, Konrad-Celtis-Str. 72, 81369 Munich, Germany

``All that we see or seem is just a dream within a dream.''
(E. A. Poe)

[Leopold Schwinger]

Oliver Fromme schrieb:

Marco Aschwanden <PP**********@s pammotel.com> wrote:

> The next step would be to use the least common denominator of all sql
> dialects and do without all the sql goodies that the dialects offer... and
> for this part I am wondering if anyone has a link / hint / book that
> assembled this minimum sql.

Then you will have a _very_ limited subset of SQL, especially
when you take mysql into account, which is not very standard-
ANSI-SQL compatible, as far as I know. For example, the
operator "||" is string-concatenation in standard SQL, while
it means logical OR in mysql. Double-quotes are used for
quoted identifiers in standard SQL, while they're used to
enclose strings in mysql. etc. etc.

Maybe you don't have to be restricted like that. In my former company we
used the product SourcePro DB from RougeWave-Software. It's a kind
of abstraction layer. You never write an SQL-statement its more
a composition of class-objects. Of course, internal the SourcePro DB
makes an SQL-statement, but for the programmer its an C++ API.

So you don't care about how the SQL-Statement looks exactly like (if
an OR is written by 'OR' or '||'
==> Problem: Each database you want to access needs an own interface inside.

The real restrictions come from the features of an DBMS
a) does it support locks?
b) what about transactions
etc. etc.

greetings Leo

[Marco Aschwanden]

Thanks for your hints.

[David Fraser]

Marco Aschwanden wrote:

Hi

I would like to develop an app that is (more or less) database
independet. Python DB API helps when masking "parameters " of sql
statements. The db driver cares for the correct conversion of a date,
text, etc. This already is a big step into the right direction.

The next step would be to use the least common denominator of all sql
dialects and do without all the sql goodies that the dialects offer...
and for this part I am wondering if anyone has a link / hint / book that
assembled this minimum sql. I am looking for something that says for
example:

Table/DB Design: always use lowercase letters, ...

Allowed sql functions: min, max, count

WHERE / HAVING hints:
- Don't use subselects

The focus is actually limited to SELECT / INSERT / UPDATE / DELETE (I
don't think that GRANT / CREATE XXX / ... statements are very portable).

I am aware, that a solution created this way is not as fast and as
elegant as an app that uses all features of a dialect, but I am really
looking for something database independent...

Thanks for any hints,
Marco

What we have found the best approach is to start development with two
databases in mind. In that case, you have to build in a basic layer of
database-independence. Then later, adding other databases tends to be an
easy refactor.

If you like you could check out the data/dbwrapper module in jToolkit
which is an example of this approach. See jtoolkit.source forge.net

Regards
David

[Pierre-Frédéric Caillaud]

Then you will have a _very_ limited subset of SQL, especially
when you take mysql into account, which is not very standard-

Um, also consider the fact you can't do much with Mysql...

[Paul Miller]

Marco Aschwanden <PP**********@s pammotel.com> wrote in message news:<ma******* *************** *************** *@python.org>.. .

Hi

I would like to develop an app that is (more or less) database independet.
Python DB API helps when masking "parameters " of sql statements. The db
driver cares for the correct conversion of a date, text, etc. This already
is a big step into the right direction.

The next step would be to use the least common denominator of all sql
dialects and do without all the sql goodies that the dialects offer... and
for this part I am wondering if anyone has a link / hint / book that
assembled this minimum sql. I am looking for something that says for
example:

I think what you are looking for is the ANSI SQL standard, probably
the SQL-92 version. However, I think something like PDO (recommended
in another message on this thread) might serve your needs better.
That is, if I understand what PDO does; it seems to me like it does
for database access what wxPython does for GUIs. If so, then there is
your consistent, multiple-DB compatible syntax, without needing to
cripple performance by dropping to SQL-92 or something.

[Dennis Lee Bieber]

On Thu, 01 Jul 2004 12:42:28 +0200, Marco Aschwanden
<PP**********@s pammotel.com> declaimed the following in
comp.lang.pytho n:

easiest way to go. For example: If Sub-Selects are not portable than I
"If"??? I think they are still on the TBD list for MySQL, so at
the moment it is not an "if", it is an "as". <G>

I suppose you could start with the O'Reilly SQL book -- as I
recall, that covered MySQL, SQL Server, Oracle (and/or Sybase -- though
didn't SQL Server start life as a rebadged Sybase?).

Of course, MySQL also doesn't (yet) support triggers, stored
procedures, and only begins to touch foreign key/referential integrity.

-- =============== =============== =============== =============== == <
wl*****@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
=============== =============== =============== =============== == <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.ne tcom.com/> <