寻找最小的SQL [英] Looking for minimal SQL

问题描述

您好


我想开发一个（或多或少）数据库独立的应用程序。

Python DB API在屏蔽参数时有帮助。的SQL语句。 db

驱动程序需要正确转换日期，文本等。这已经是朝着正确方向迈出的重要一步。

下一步是使用所有sql

方言的最小公分母，并且不使用方言提供的所有sql好东西......和

对于这部分我想知道是否有人有一个链接/提示/书

汇总了这个最小的sql。我正在寻找的东西可以说是

例如：


表/数据库设计：总是使用小写字母，......

允许的sql函数：min，max，count


WHERE / HAVING提示：

- 不要使用子选择


重点实际上仅限于SELECT / INSERT / UPDATE / DELETE（我不认为GRANT / CREATE XXX / ...语句是非常便携的） 。


我知道，以这种方式创建的解决方案并不像使用方言的所有功能的应用程序一样快速和优雅

，但是我真的在寻找

数据库独立的东西...


感谢任何提示，

Marco

解决方案

我知道，以这种方式创建的解决方案并不像

您正在开发什么样的应用程序？

>你正在开发什么样的应用程序？


我现在正在开发什么。我还在评估......最终是一个简单的CRM（客户关系管理）。我可能会在明年开始做一些事情，但我的大脑开始创建一个框架。


有几个原因我想留下来db ;独立：对于

，我想运行相同的应用程序通过一些rpc-server（

将有一个强大的数据库）或作为一个独立的应用程序与简单

db落后（将从时间同步到时间*计划*计划*）。我已经实现了简单的b&b。框架，允许我重用相同的

代码作为一个多层应用程序，它作为一个独立的应用程序。未来可以使用数据库

切换......因此......等等。


- > Python在os平台上提供了开放性

- > db api提供了一定的开放性，当涉及到交易和

参数化sql-stmts ...我想坚持一个最小的sql语言集

来实现真正的db开放性


我知道编程sql-neutral不是最有效的，最简单的方法。例如：如果Sub-Selects不可移植，那么
将拆分查询并将它们与Python粘合在一起。如果用于

示例，则查找最老的客户：


通过子选择，您可以写：


SELECT * FROM customer WHERE age =（SELECT max（age）FROM customer）


如果没有子选择，我将不得不写两个单独的语句：


SELECT max（age）FROM customer - >采取年龄超出此查询

" SELECT * FROM customer WHERE age =？"，（age）


这可能不是最快的解决方案，但我会喜欢试试！


希望这能回答你的问题，


Marco

Marco Aschwanden< PP ********** @ spammotel.com>写道：

下一步将使用所有sql
方言的最小公分母，并且没有方言提供的所有sql好东西......和
为此部分我想知道是否有人有一个链接/提示/书，
组装这个最小的SQL。


然后你会有一个_very_有限的SQL子集，特别是当你把mysql考虑进来时，你需要
，这不是很标准 -

与ANSI-SQL兼容，据我所知。例如，

运算符||是标准SQL中的字符串连接，而

表示mysql中的逻辑OR。双引号用于标准SQL中引用标识符的

，而它们用于将
括在mysql中的字符串中。等等。

例如：

表/数据库设计：总是使用小写字母，...


你也会必须小心避免_all_ SQL方言的保留字

。不容易。另外，不要使你的

标识符超过20个字符。

重点实际上仅限于SELECT / INSERT / UPDATE / DELETE（我不要我认为GRANT / CREATE XXX / ...语句非常便于移植。

GRANT和CREATE都是在标准SQL中指定的，我是

相信所有主要数据库都符合它（我不知道如果mysql确实如此，那么
）。


也许是PostgreSQL文档对您有所帮助：

对于每个SQL命令，它包含其

标准SQL一致性的描述。这是网址：

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

如果你坚持这样，你的SQL代码很可能也会与Oracle，Sybase和其他严重的人合作。数据库。


最好的问候

奥利弗


PS：当我说标准SQL时，我的意思是ANSI SQL92和SQL99。


-

Oliver Fromme，Konrad-Celtis-Str。 72,81369慕尼黑，德国


`所有我们看到或看起来只是梦中的梦想。'''

（EA Poe）

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

解决方案

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 ?

> 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

Marco Aschwanden <PP**********@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)

这篇关于寻找最小的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！