Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Python (http://www.velocityreviews.com/forums/f43-python.html)
-   -   Looking for minimal SQL (http://www.velocityreviews.com/forums/t332740-looking-for-minimal-sql.html)

Marco Aschwanden 07-01-2004 07:45 AM

Looking for minimal SQL
 

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






=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= 07-01-2004 08:57 AM

Re: Looking for minimal SQL
 


> 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 07-01-2004 10:42 AM

Re: Looking for minimal SQL
 
> 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 07-01-2004 12:13 PM

Re: Looking for minimal SQL
 
Marco Aschwanden <PPNTWIMBXFFC@spammotel.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 07-01-2004 12:49 PM

Re: Looking for minimal SQL
 
Oliver Fromme schrieb:

> Marco Aschwanden <PPNTWIMBXFFC@spammotel.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 07-01-2004 12:52 PM

Re: Looking for minimal SQL
 
Thanks for your hints.




=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= 07-01-2004 02:07 PM

Re: Looking for minimal SQL
 

> 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 07-01-2004 02:40 PM

Re: Looking for minimal SQL
 
Marco Aschwanden <PPNTWIMBXFFC@spammotel.com> wrote in message news:<mailman.319.1088667767.27577.python-list@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 07-01-2004 03:54 PM

Re: Looking for minimal SQL
 
On Thu, 01 Jul 2004 12:42:28 +0200, Marco Aschwanden
<PPNTWIMBXFFC@spammotel.com> declaimed the following in
comp.lang.python:

> 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.

--
> ================================================== ============ <
> wlfraed@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> ================================================== ============ <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.netcom.com/> <


Sion Arrowsmith 07-01-2004 04:52 PM

Re: Looking for minimal SQL
 
Dennis Lee Bieber <wlfraed@ix.netcom.com> wrote:
>On Thu, 01 Jul 2004 12:42:28 +0200, Marco Aschwanden
><PPNTWIMBXFFC@spammotel.com> declaimed the following in
>comp.lang.python:
>> 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>


They've been available since at least 4.1.0. Not always working
correctly, mind, but they've been there. In fact, I've hit
problems running SQL written for MySQL on Firebird becuase the
latter doesn't support nested selects in all the places the
former does....

--
\S -- siona@chiark.greenend.org.uk -- http://www.chaos.org.uk/~sion/
___ | "Frankly I have no feelings towards penguins one way or the other"
\X/ | -- Arthur C. Clarke
her nu becomež se bera eadward ofdun hlęddre heafdes bęce bump bump bump


All times are GMT. The time now is 02:52 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.