[Phpmyadmin-devel] Having fun with views

[Marc Delisle]

Alexander M. Turek a écrit :
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hi Marc & list,
> 
> Sorry for answering that late... I somehow forgot the thread I started
> myself... ;-)
> 
> Marc Delisle schrieb:
> 
>>I was working with views in Oracle, 20 years ago ;)
> 
> 
> I thought you were, but everyone who reads this list was... ;-)
> 
> 
>>>Generally, it is not a bad idea to test a query first before creating a
>>>view from it - at least I do so. So when displaying a SELECT query,
>>>maybe we should offer an option "Create view from query" that leads to a
>>>page that allows me to give that view a name and maybe a comment and
>>>ajust some settings for it.
>>
>>Good idea. However, we would need a mechanism for the optional
>>column_list of the view. I see some ways to do it:
>>
>>- possibility of editing the view's structure the same way we edit a
>>table's structure. Sorry I did not check if this is currently possible
>>in PMA 2.6.2. So users create a view then refines it by column renaming.
> 
> 
> PMA 2.6.2 displays the structure as if he is working a base table. MySQL
> reports the column types, so this is no big deal.
> 
> Allowing to edit the columns is a bit tricky. You cannot alter the
> column types because they are determined by the query. What
> theoretically you could do is allowing to rename, move and remove
> columns. Maybe we could even allow to change the definition of the
> columns, but this could be a tough one.
> 
> For all those operations, we would need to parse the SELECT statement,
> apply our changes, reassemble the statement and recreate the view.
> 
> Especially altering the column definitions would get pretty ugly when it
> comes to UNION queries - and just imaging how fun it would be if MySQL
> decided to support subqueries in views... ;-)
> 
> 
>>- if using the Search page to select data, add on the interface a new
>>"Alias" column when we can enter an alias for each field. This could
>>become the column list; maybe not quite intuitive
> 
> 
> No, but this would be worth a feature request anyway. :-)
> 
> 
>>- same thing on the QBE page, adding an alias (on the Show line).
>>More appropriate because views can be formed of many tables.
> 
> 
> Agreed.
> 
> 
>>>Another way would be merging it with the export code: when clicking the
>>>"Export" link in browse mode, we could offer to export the SELECT
>>>statement as view.
>>>
>>>Both solutions would allow us to combine the view creation with
>>>phpMyAdmin's search feature or the QBE with automatic joins. The latter
>>>could be turned into a powerful view editor this way!
>>
>>Yes. But the same argument we had about having an Import tab could be
>>said about views.
> 
> 
> I'm afraid, I didn't get your point...

I mean that, when a user looks at the menu tabs in table view, he sees 
Export but not Import. Granted, we added an "Import files" to the query 
window but this window is not immediately seen, so some users still 
wonder about how to import.

So I think that some users will be looking for a "View" tab.

> 
> 
>>>Some other things, I thought about:
>>>
>>>Maybe we can also merge it with the bookmarks code: Somehow, the
>>>combination of stored procedures and views can be seen as MySQL's answer
>>>to our bookmark system: SELECT statements can be "bookmarked" as views,
>>>everything else as stored procedures. Nevertheless, those two don't
>>>replace the bookmarks, as there may be users who are not allowed to
>>>create either of them.
>>
>>Yes, and also our bookmarks allow to have non-SELECT statements.
> 
> 
> So do stored procedures.
> 
> 
>>But let's not forget that views have options
>>[WITH [CASCADED | LOCAL] CHECK OPTION]
> 
> 
> If we chose the way via the export feature, those would be our export
> options. If we chose the way via the "create view from query" way, we
> link should lead us to a new page where the user can set all those
> options before creating the view.
> 
> 
>>and can be updated or inserted into!
> 
> 
> This shouldn't be too different from inserting into or updating a base
> table. Detecting updatable views is currently only possible via the
> information_schema database, afaik.
> 
> 
>>Don't know about the notion of deleting from view...
> 
> 
> This is possible, if the view is updatable.
> 
> 
>>>I just wanted to get your comments about this and to check for
>>>volunteers - especially for the view creation part. :-)
>>
>>
>>Thanks for this initial thinking about views. I will be a volunteer,
>>but I would like to work first on some 4.1.x missing features
>>before 2.6.2-rc1, then on bug fixing before 2.6.2.
> 
> 
> Sure, this was meant for the development after 2.6.2. Of course, MySQL
> 4.1 struggles have a higher priority because 4.1 is already stable and
> we would probably face them with 5.0, too.
> 
> What I did so far, is preventing the new MySQL 5.0 features (like views
> or information_schema) from breaking PMA's interface.
> 
> Last week, I have spoken to a MySQL guy at the CeBIT in Hannover. He
> told me that MySQL 5.0 will enter the beta statium in Q2/2005. In fact,
> MySQL 5.0.3 has already been feature-frozen and its current development
> snapshots are labelled as "beta".
> 
> Regards,
> 
> AMT
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
> 
> iD8DBQFCQz218c/ssWf/SMcRAiZMAJ43KBTtODxA6u7DUFF512od3NYWiwCfdOQ2
> 9bYx2Ulz27Iktk1GtLk4dG8=
> =3gLs
> -----END PGP SIGNATURE-----
>