dobbs 01/06/06 15:37:22
Modified: xdocs proposals.xml
Added: xdocs/proposals sql.xml
Log:
Adding proposal for new query model.
Revision Changes Path
1.5 +1 -0 jakarta-turbine/xdocs/proposals.xml
Index: proposals.xml
===================================================================
RCS file: /home/cvs/jakarta-turbine/xdocs/proposals.xml,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- proposals.xml 2001/04/18 18:07:45 1.4
+++ proposals.xml 2001/06/06 22:37:19 1.5
@@ -21,6 +21,7 @@
<li><a href="proposals/core-processing.html">Core Processing</a></li>
<li><a href="proposals/i18n.html">i18n</a></li>
<li><a href="proposals/naming-service.html">Naming Service</a></li>
+<li><a href="proposals/sql.html">New Query Model</a></li>
<li><a href="proposals/package-cleanup.html">Package Cleanup</a></li>
<li><a href="proposals/peers.html">Peer</a></li>
<li><a href="proposals/policy-service.html">Policy Service</a></li>
1.1 jakarta-turbine/xdocs/proposals/sql.xml
Index: sql.xml
===================================================================
<?xml version="1.0"?>
<document>
<properties>
<title>New Query Model</title>
<author email="[EMAIL PROTECTED]">Eric Dobbs</author>
</properties>
<body>
<section name="Preamble">
<p>
The purpose of this document is to capture the discussion
about the new query model.
</p>
<p>
The present (Spring 2001) method for constructing SQL
statements in Turbine is flawed. Symptoms include:
<li>
confusion about use of Criteria, Criterion
</li>
<li>
inability to construct arbitrarily complex WHERE clauses
</li>
</p>
</section>
<section name="Classes likely to be effected by this proposal">
<p>
<li>
org.apache.turbine.util.db.Criteria
</li>
<li>
org.apache.turbine.util.db.SqlExpression
</li>
<li>
org.apache.turbine.util.db.adaptor (all classes in the package)
</li>
<li>
org.apache.turbine.om.peer.BasePeer
</li>
</p>
</section>
<section name="Example SQL statements to be generated">
<pre>
1. simple selects
select * from some_table
where some_id = n
2. multi-table joins
select table1.name,table2.type_name
from table1,
table2,
table3
where table1.description like '%foo%'
and table1.one_id=table3.one_id
and table2.two_id=table3.two_id
3. compound where clauses
select table1.name
from table1
where (table1.age>21 and table1.age<35)
or (table1.age>50 and table1.age<65)
4. functions
select sum(table1.amount) as sum
from table1
where table1.amount<5000
5. group by ... having
select type, sum(price)
from titles
where price <= 5
group by type
having sum(price) > 50
6. sub-queries
select table1.title)
from table1
where table1.price =
(
select min(table1.price)
from table1
)
select distinct pub_name
from publishers
where pub_id not in
(
select pub_id
from titles
where type='business'
)
7. transactions
begin
<statement1>
<statement2>
<statement3>
commit
8. union, intersect, except (minus)
<statement1>
union
<statement2>
9. outer joins
(mysql syntax)
select user.name,preference.name
from user left join preference
where user.user_id = preference.user_id
(postgresql and sql'92 syntax)
select user.name, preference.name
from user left join preference on user.user_id =
preference.user_id
(oracle syntax)
select user.name,preference.name
from user, preference
where user.user_id = preference.user_id (+)
10. stored procedures and functions
execute foo('bar','baz','biz')
select table.type, foo(table.column)
from table
group by table.type
11. prepared statements
(Any of the above, replacing literal values with ? place-holders.
This also calls for some means of passing in the parameters to a
prepared statement)
select user.name
from user
where userid = ?
</pre>
</section>
</body>
</document>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
