I forgot a couple other important SQL forms... adding them to the
list below. I assume we will also want to be able to construct
these.
With this list of SQL statements that we want to support, we will
diverge somewhat from the original goal of cross-platform SQL.
Not all databases support stored procedures, or outer joins, or
sub-queries (but you knew that already 8^). Within core pieces
of Turbine, we can restrict ourselves to the statements 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. Any discussion?
-Eric
On Monday, May 28, 2001, at 11:16 AM, Eric Dobbs wrote:
> Taking a moment to question design goals for the new query model.
> As long as we're talking about redesigning from the ground up...
> Which of the following example sql statements would you like to
> be able to construct with the new query model?
>
> -Eric
>
> 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 = ?
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
