[Phpmyadmin-devel] Having fun with views

[Alexander M. Turek]

-----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...

>> 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-----