> I think it would not be bad to extend the documentation of
> what you propose to answer the following question. Please
> don't touch your code, just extend the documentation. Maybe
> you answer in form of a section for the manual, the
> documentation has to be written anyway.
OK, will try...
>
> - 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. 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
>
> You write:
> > 1. <view name="my_view"
> > from="my_table"
> > >
> > field="field1" type="integer"
> > field="field2" type="integer"
> > search="searchfield1" type="integer"
> > search="searchfield2" type="integer"
> > </view>
> >
> > Will result in:
> > select
> > field1,
> > field2
> > from
> > my_table
> > where
> > searchfield1="xxx" and/or searchfield2="xxx"
> >
>
> But where do these "xxx" come from?
>From the form like before. The difference between field and search is
that field will be included in the select part of the query and search
not.
>
> 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!)
> - 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?
>
> - What about keys? The keys of a group by query are the
> columns within group by, must that be set so by developers?
Not thought about yet, good hint. Have a problem with keys in groupBy.
But this hint gives the solution.
>
> - 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. This join could be made
updateable by defining a special tag for the table which you want to
update. While constructing the insert/delete/update statement this table
can be used instead of the table.
> You seem to be able to use the build in update mechanism
> of dbforms. What has to be done to be able to do so?
You must define the table, which should be used for the update.
>
> > Next step could be to make the view updateable - this could
> be easily
> > done introducing new tags for update and insert statments. I would
> > implement this at the time i need it.
>
> If it really so easy, why don't you document and implement it now?
> And I'm not really sure that a general solution would be easy...
> you would need a type of template or parameter mechanism within
> the sql statements and sometimes could need features sql is
> perhaps not
> capable of...maybe a sequence of statements, branching...
This solution supports only standard sql! Everything must at least be
done with a table! This tabel must be defined for dbforms.
>
> Even if you don't want to implement it, I'd like to see a
> documentation of how this could look like.
>
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"
Implementing this is not the problem. Problem comes from testing. In the
moment defining a view xyz_view with a from part is working, for update
too.
Fields in the view and in the table in the from tag must be the same.
You can define a view without any fields. In this case the fields would
come from the table in the from part. This view is updateable and uses
table for the update/insert/delete.
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>
> Luca proposed an extension to override the build in event handlers
> for database updates with something written by developers. Wouldn't
> your suggestion be a special case of it? If we can pass
> parameters to
> these handlers, using Luca's proposal one could write
>
> <view name="..." from="..."
> updateHandler="foo.bar.myUpdateHandler"
> updateHandlerParameter1="update .dfad..asdjblabla"
> updateHandlerParameter2="insert... .dfad..asdjblabla"
> deletehandler="..."
>
> >
>
> 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. 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.
Regards,
Henner
-------------------------------------------------------
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
