dobbs 01/06/20 17:38:47
Modified: docs/proposals sql.html
xdocs/proposals sql.xml
Log:
added a section describing my analysis of SQL statements which
will hopefully make more clear what I am doing in the example
code posted in jakarta-turbine/proposals/eric/statement
Revision Changes Path
1.4 +116 -9 jakarta-turbine/docs/proposals/sql.html
Index: sql.html
===================================================================
RCS file: /home/cvs/jakarta-turbine/docs/proposals/sql.html,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- sql.html 2001/06/06 23:23:03 1.3
+++ sql.html 2001/06/21 00:38:44 1.4
@@ -175,13 +175,7 @@
<tr><td><br/></td></tr>
<tr><td>
<blockquote>
- <p>
- Within core pieces of Turbine, we will restrict ourselves to
- the statements below that are cross-platform while still
- offering users of Turbine an api that can take advantage of
- the more advanced options of their specific database.
- </p>
- <ol>
+ <ol>
<li>simple selects
<pre> select * from some_table
where some_id = n</pre>
@@ -252,16 +246,21 @@
</li>
<li>outer joins
-<pre> (mysql syntax)
+<pre> (MySQL syntax)
select user.name,preference.name
from user left join preference
where user.user_id = preference.user_id
- (postgresql and sql'92 syntax)
+ (PostgreSQL and sql'92 syntax)
select user.name, preference.name
from user left join preference on user.user_id =
preference.user_id
+ (Microsoft SQL Server --and probably Sybase-- syntax)
+ select user.name,preference.name
+ from user, preference
+ where user.user_id *= preference.user_id
+
(oracle syntax)
select user.name,preference.name
from user, preference
@@ -312,6 +311,114 @@
<li>
org.apache.turbine.om.peer.BasePeer
</li>
+ </p>
+ </blockquote>
+ </p>
+ </td></tr>
+ <tr><td><br/></td></tr>
+ </table>
+ <table border="0" cellspacing="0"
cellpadding="2" width="100%">
+ <tr><td bgcolor="#525D76">
+ <font color="#ffffff" face="arial,helvetica,sanserif">
+ <a name="Implementation Discussion"><strong>Implementation
Discussion</strong></a>
+ </font>
+ </td></tr>
+ <tr><td><br/></td></tr>
+ <tr><td>
+ <blockquote>
+ <p>
+ The main challenge is to be able to construct arbitrarily complex
+ SQL statements using Java (or something else) instead of SQL. SQL
+ dialects are not standard, so we will use Java classes to
+ encapsulate the differences in SQL dialects thereby making our
+ code (mostly) database independant. Outter joins, and other
+ constructs, will still present restrictions to complete database
+ independance because some database vendors expect the outter join
+ to be in the from clause where others expect it in the where
+ clause. Auto-increment, date, and datetime fields and sequences
+ will likely present other problems for complete portability.
+ </p>
+ <p>
+ Within core pieces of Turbine, we will restrict ourselves to
+ the statements that are portable across databases while still
+ offering users of Turbine an api that can take advantage of
+ the more advanced options of their specific database. Let us
+ not deny Turbine users the freedom to lock themselves into a
+ specific database if that's what they want to do.
+ </p>
+ </blockquote>
+ </p>
+ </td></tr>
+ <tr><td><br/></td></tr>
+ </table>
+ <table border="0" cellspacing="0"
cellpadding="2" width="100%">
+ <tr><td bgcolor="#525D76">
+ <font color="#ffffff" face="arial,helvetica,sanserif">
+ <a name="Analysis of sql statements"><strong>Analysis of sql
statements</strong></a>
+ </font>
+ </td></tr>
+ <tr><td><br/></td></tr>
+ <tr><td>
+ <blockquote>
+ <p>
+ Ultimately, we will just be assembling strings. Should be easy,
+ right? 8^). The particular strings of interest have the following
+ form:
+ </p>
+ <ul>
+ <li>SELECT <em>list</em></li>
+ <li>FROM <em>list</em></li>
+ <li>WHERE <em>tree</em> </li>
+ <li>ORDER BY <em>list</em></li>
+ <li>GROUP BY <em>list</em></li>
+ <li>HAVING <em>tree</em></li>
+ <li>DELETE FROM <em>item</em></li>
+ <li>UPDATE <em>item</em> SET <em>assignment</em> [, SET <em>assignment</em>
...]</li>
+ <li>INSERT (<em>list</em>)</li>
+ <li>VALUES (<em>list</em>)</li>
+ <li>most <em>functions</em> have this form: function(<em>item</em>)</li>
+ <br />
+ <li><em>list</em>: <em>item</em> [, <em>item</em> ...]</li>
+ <li><em>assignment</em>: <em>item</em>=<em>item</em></li>
+ <li><em>tree</em>:
+ <ul>
+ <li>(<em>item</em>[operator]<em>item</em>)</li>
+ <li>(<em>item</em> [NOT] IN (<em>item</em> [, <em>item</em> ...]))</li>
+ <li>(<em>tree</em> [AND|OR] <em>tree</em>)</li>
+ </ul>
+ </li>
+ <li><em>item</em>:
+ <ul>
+ <li>[table.]column [alias]</li>
+ <li>table [as alias]</li>
+ <li><em>functions</em></li>
+ <li>[table.]column [ASC|DESC]</li>
+ <li><em>literal values</em>: 'foobar', '%foo', 53, null ...</li>
+ </ul>
+ </li>
+ </ul>
+ <p>
+ Many of the above forms can be generalized. SELECT, FROM, ORDER
+ BY, GROUP BY, INSERT, VALUES, and <em>trees</em> can all be
+ described like this: <br />
+
+ <code>[<em>prefix</em>]<em>item</em>[<em>connector</em>
+ <em>item</em> ...][<em>suffix</em>]</code><br />
+
+ where the connector is usually a comma (,). In the case of
+ various comparisons that make up the <em>tree</em>
+ <code>(column=value)</code> and <code>([this] OR [that])</code>
+ the prefix and suffix are parentheses, and the connector is the
+ logical operator.
+ </p>
+ <p>
+ WHERE, HAVING, SET, ASC, DESC, and most standard functions
+ (COUNT,AVG,SUM...), can all be described like this:<br />
+
+ <code>[prefix]<em>item</em>[suffix]</code><br />
+
+ where some cases use an empty prefix or suffix (WHERE and HAVING
+ use an empty suffix, ASC and DESC use an empty prefix)
</p>
</blockquote>
</p>
1.4 +97 -9 jakarta-turbine/xdocs/proposals/sql.xml
Index: sql.xml
===================================================================
RCS file: /home/cvs/jakarta-turbine/xdocs/proposals/sql.xml,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- sql.xml 2001/06/06 23:23:04 1.3
+++ sql.xml 2001/06/21 00:38:46 1.4
@@ -27,13 +27,6 @@
</section>
<section name="Example SQL statements to be generated">
- <p>
- Within core pieces of Turbine, we will restrict ourselves to
- the statements below that are cross-platform while still
- offering users of Turbine an api that can take advantage of
- the more advanced options of their specific database.
- </p>
-
<ol>
<li>simple selects
<pre> select * from some_table
@@ -105,16 +98,21 @@
</li>
<li>outer joins
-<pre> (mysql syntax)
+<pre> (MySQL syntax)
select user.name,preference.name
from user left join preference
where user.user_id = preference.user_id
- (postgresql and sql'92 syntax)
+ (PostgreSQL and sql'92 syntax)
select user.name, preference.name
from user left join preference on user.user_id =
preference.user_id
+ (Microsoft SQL Server --and probably Sybase-- syntax)
+ select user.name,preference.name
+ from user, preference
+ where user.user_id *= preference.user_id
+
(oracle syntax)
select user.name,preference.name
from user, preference
@@ -154,6 +152,96 @@
<li>
org.apache.turbine.om.peer.BasePeer
</li>
+ </p>
+</section>
+
+<section name="Implementation Discussion">
+ <p>
+ The main challenge is to be able to construct arbitrarily complex
+ SQL statements using Java (or something else) instead of SQL. SQL
+ dialects are not standard, so we will use Java classes to
+ encapsulate the differences in SQL dialects thereby making our
+ code (mostly) database independant. Outter joins, and other
+ constructs, will still present restrictions to complete database
+ independance because some database vendors expect the outter join
+ to be in the from clause where others expect it in the where
+ clause. Auto-increment, date, and datetime fields and sequences
+ will likely present other problems for complete portability.
+ </p>
+
+ <p>
+ Within core pieces of Turbine, we will restrict ourselves to
+ the statements that are portable across databases while still
+ offering users of Turbine an api that can take advantage of
+ the more advanced options of their specific database. Let us
+ not deny Turbine users the freedom to lock themselves into a
+ specific database if that's what they want to do.
+ </p>
+</section>
+
+<section name="Analysis of sql statements">
+ <p>
+ Ultimately, we will just be assembling strings. Should be easy,
+ right? 8^). The particular strings of interest have the following
+ form:
+ </p>
+
+ <ul>
+ <li>SELECT <em>list</em></li>
+ <li>FROM <em>list</em></li>
+ <li>WHERE <em>tree</em> </li>
+ <li>ORDER BY <em>list</em></li>
+ <li>GROUP BY <em>list</em></li>
+ <li>HAVING <em>tree</em></li>
+ <li>DELETE FROM <em>item</em></li>
+ <li>UPDATE <em>item</em> SET <em>assignment</em> [, SET <em>assignment</em>
...]</li>
+ <li>INSERT (<em>list</em>)</li>
+ <li>VALUES (<em>list</em>)</li>
+ <li>most <em>functions</em> have this form: function(<em>item</em>)</li>
+ <br/>
+ <li><em>list</em>: <em>item</em> [, <em>item</em> ...]</li>
+ <li><em>assignment</em>: <em>item</em>=<em>item</em></li>
+ <li><em>tree</em>:
+ <ul>
+ <li>(<em>item</em>[operator]<em>item</em>)</li>
+ <li>(<em>item</em> [NOT] IN (<em>item</em> [, <em>item</em> ...]))</li>
+ <li>(<em>tree</em> [AND|OR] <em>tree</em>)</li>
+ </ul>
+ </li>
+ <li><em>item</em>:
+ <ul>
+ <li>[table.]column [alias]</li>
+ <li>table [as alias]</li>
+ <li><em>functions</em></li>
+ <li>[table.]column [ASC|DESC]</li>
+ <li><em>literal values</em>: 'foobar', '%foo', 53, null ...</li>
+ </ul>
+ </li>
+ </ul>
+
+ <p>
+ Many of the above forms can be generalized. SELECT, FROM, ORDER
+ BY, GROUP BY, INSERT, VALUES, and <em>trees</em> can all be
+ described like this: <br/>
+
+ <code>[<em>prefix</em>]<em>item</em>[<em>connector</em>
+ <em>item</em> ...][<em>suffix</em>]</code><br/>
+
+ where the connector is usually a comma (,). In the case of
+ various comparisons that make up the <em>tree</em>
+ <code>(column=value)</code> and <code>([this] OR [that])</code>
+ the prefix and suffix are parentheses, and the connector is the
+ logical operator.
+ </p>
+
+ <p>
+ WHERE, HAVING, SET, ASC, DESC, and most standard functions
+ (COUNT,AVG,SUM...), can all be described like this:<br/>
+
+ <code>[prefix]<em>item</em>[suffix]</code><br/>
+
+ where some cases use an empty prefix or suffix (WHERE and HAVING
+ use an empty suffix, ASC and DESC use an empty prefix)
</p>
</section>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
