Hi Henner!
Following may be long an chaotic, before answering, have a look at
at the summary at the and please...
...
> > - from our (long) discussion on the developer's list we know
> > that what you propose is conceptually somewhere between a
> > sql database view and a parametric query. It must be clear
> > where the difference between a database view and this view
> > is, and which parts of the underlying query can be
> > manipulated at application runtime.
> > For the problem you had you needed the where clause to
> > be flexible. Please make clear what else...
> A database view is a datbase view and in dbforms only a table. You can
> not update this table but for dbforms it is a table.
There is a description within SQL when a view is updatable and when
it is not. It is rather silly, but it is defined within standard.
> The new view for
> dbforms is not a view in the database, it is only a more flexibel way to
> generate sql statements. With tables in dbforms the only possible sql
> statement is select <fieldlist> from <tablename> where <search_clause>
> order by <order_clause>. With the new tag view it becomes possible the
> generate a nearly free sql statement and use it as a table in dbforms.
> Use it with all features of a table, including searching, ordering, free
> form select (not tested yet).
>
> >
> > - When we formulate selections within dbforms, we can use
> >
> > - whereClause
> > - searchFields
> > - filters
> >
> > If we had a sql view containg 'group by ... having ', all
> > where clauses of queries using the view would in the end
> > be on the level of the having clause of the underlying query
> > (that was your problem).
> >
> > Which of the above dbforms features can still be used and
> > what is the semantic of such a usage? What has to be done
> > to extend the where clause/having clause?
> See above, view inherites all features of a table so you can use it as a
> table - with whereClause, searchFields, filters
>
If someone uses the where clause, everything else, e.g. filters will be
overwritten, navigation will also be not possible. Same here?
WhereClause may contain group-by and order-by clauses. What happens
then?
...
> >
> > How can I do a
> >
> > select field1, sum(field2)
> > where field2 > 44
> > group by field1
> > having avg(field2) < 120
> >
> > extending where and having clause at runtime?
> >
> > You seem to use your searchfields for that, but it's not
> > clear to me how this will work. What will I have to do to use
> > the same column within where and having clause?
> >
> You define field2 as search, so it will not be included in the select
> statement.
> You create a new field with expression sum(field2), new field for the
> avg function and a search field for field2
>
> Example:
> <view name="xyz_view"
> from="xyz"
> groupBy="field1"
> />
> <field name="field1" />
> <field name="field2Sum" expression="sum(field2)" />
> <field name="field2Avg" expression="avg(field2)" />
> <search name="field2" />
> </view>
>
> No you build your form as usual - With searchfields. The view will
> automatically splitt your searchfields into the part needed for where
> and needed for where. This is done by looking of the type. Search fields
> go the the where clause and the rest go to the having clause ( In case
> of groupBy!!!! Otherwise both will go to where clause!)
Looks like this could work, the columns just must have unique names in order
to check where they belong to. By use of the expression attribute it will
always be possible to rename a column
<field name="fieldrenamed" expression="field1"/>
and so it can be used at both places.
Even if someone does not use the group-by, it looks like it would
be also possible to do a
... where col1 + col2 < 12 ...
query using a field:
<field name="sum" expression="col1 + col2"/>
and then within application a filter 'sum<12', is that true?
Shouldn't the expression attribute then also be allowed for
the search tags in order to be able to do a
select..
from..
where col1 + col2 < 12
group by..
having ...
query: <search name="blabla" expression="col1 + col2"/> ?
>
> > - Is this concept capable of handling joins, may one write
> >
> > <view name="viewname"
> > from="tabl1 left join table2 on table1.id = table2.id" ....> ?
> >
> > If yes, what happens if I try to update such a view?
> >
....
> > - One of the most important thing when introducing views
> > is the question if and when a view is updatable. Surely a
> > view containing aggregate functions or a join between
> > two table connected via an 1-n relationship cannot
> > be updatable. What are the rules here?
> >
> Have not thought about of a concept for joins. I think the easier way
> would be to define a join in the database.
A database view (= join) again?
Well, couldn't happen the same problem again? You were unable to
use a view because you needed a more flexible where clause.
What if you need a
select ...
from tab1 join tab2 on ...
where ...flexible runtime condition...
group by...
having...
query?
(about updates:)
> >
> OK, let's define it!
>
> Example:
> <view name="xyz_view"
> from="table"
> fromUpdate="table2"
> />
> <field name="field1" />
> </view>
>
> Every select statement will be use table in the from part: select field1
> from table
>
> Every update/insert/delete statement will be use table2 in the
> from/into/update part:
> update table2 set field1="xyz"
> insert into table2(field1) values ("xyz")
> delete from table2 where key="xcc"
>
You are just replacing the table name by another. That is because you have
no join views. But I would still not be sure about that: If someone
uses an expression
<field name="abcdef" expression="col1 + 12"/>
will you really insert 'currentvalue of field 'abcdef' - 12' into
table column abcdef? You surely cannot reverse each expression, it is
in most cases impossible. The problems are the expressions, not only
within a group by expression, but also anywhere else.
To be updatable using the simple 'replace tablename' mechanism,
such a query
- should contain exactly one table within from attribute
- must not contain expressions as columns
- must not use group by or having
- contains each field not more than once
- must completely contain the key of the underlying table
and have it also declared as a key
More rules: To be usable for insert statements, the view
- must contain all non-nullable without-default columns of the
underlying table.
I don't think your implementation should check this all. You already
mentioned what really would be important: To be able to use these
tables within forms that several times nested. This would be a big
step forward. If that works, it's enough for me...
...
I think it would be more a win to allow to formulate a join query than
to restrict the query to use not more than one single table and have
it (sometimes) updatable.
And if we don't expect dbforms to create the join clause, it would not be
difficult:
- allow free form string as fromClause
- allow an arbitrary string to be plugged into the whereClause
this might contain the join condition, a subquery or anything else
Example:
<db:view name="customerWithOrders"
from="customer,order"
whereClausePlugIn=" customer.id = order.cust_id "
groupBy="first_name,last_name">
<field name="first_name"/>
<field name="last_name"/>
<field name="sumorders" expression="sum(order_value)"/>
</db:view>
resulting in
select first_name,last_name,sum(order_value) as sumorders
from customer,order
where customer.id = order.cust_id
group by first_name,last_name
> So it is possible to use table two times on the same form, a feature you
> suggested earlier.
> This stuff is tested.
> Example:
> <view name="xyz_view"
> from="table"
> />
> </view>
>
That looks great, even better:
<view name="xyz_view" from="table"/>
would be all to define such a 'table alias'.
(About Luca's proposal):
> > Isn't that a more general concept that could also be used
> > for these views?
> Yes it is! On the other hand it much more complex to use. You must write
> updateHandler for each view you need.
It might be possible to write a general Event handler and then pass
table specific parameters to it...
> The views are much more simple for
> simple problems. You define the view like a table. Extra definitions are
> necessary for the update table. That's it. Everything else is handled by
> the view.
> If you need more complex things, like get a sequence of sql statments to
> update a view we must implemet Luca's concept.
>
I'd agree that we should keep your extension simple.
Summary:
- consider to allow expression attribute for search tag
- consider to allow free form strings for from clause and for a
whereClause plugin (not replacement as it is using current
whereClause for dbform tag!). So it would be possible to use
join views, subqueries and a lot more.
- it is not important to try to make as many views as possible
updatable. Just a simple view containing a whole base table
should be updatable. Syntax
<view name="xyz_view" from="table"/>
can't be made better!
- I would agree (see Luca's and your last post) to choose another
name for the tag that does not make developers think of
database views.
Regards
Dirk
-------------------------------------------------------
This SF.net email is sponsored by: ApacheCon, November 18-21 in
Las Vegas (supported by COMDEX), the only Apache event to be
fully supported by the ASF. http://www.apachecon.com
_______________________________________________
DbForms Mailing List
http://www.wap-force.net/dbforms
