[Phpmyadmin-devel] Having fun with views

[Marc Delisle]

Alexander M. Turek a écrit :
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hi devels,
> 
> As you have probably noticed, phpMyAdmin 2.6.2 at its current state
> should be able to live with withs, which basically means that we still
> treat them as tables, but they don't break our interface anymore.
> 
> For those of you, who don't know views yet (which would not be a big gap
> in your knowlege as this feature does not exist in MySQL before 5.0):

Hi Alexander,

I was working with views in Oracle, 20 years ago ;)

> So back to topic: As I said, phpMyAdmin already detects existing views.
> But it would be really fancy, if phpMyAdmin somehow could support the
> user when it comes to create those views.
> 
> The obvious way would be to add a link "Create new view" and when
> clicking it, displaying a big textarea where the user may enter his
> SELECT statement. Wee. Easy to implement, but absolutely unexiting,
> because for the user, it is equally complex to enter "CREATE VIEW `foo`
> AS" plus his SELECT statement into a simple query box.

yeah, let's avoid this way.
> 
> 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.

- 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

- same thing on the QBE page, adding an alias (on the Show line).
More appropriate because views can be formed of many tables.

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

But let's not forget that views have options
[WITH [CASCADED | LOCAL] CHECK OPTION]

and can be updated or inserted into! Don't know about the notion
of deleting from view...

> 
> It is currently not possible to comment views (natively), neither a view
> itself, nor its columns. And at least for the column level, I doubt that
> they ever will be. Maybe, we could reactive our pmadb based column
> comments code for this?

Yes.
> 
> Views are currently not exportable in phpMyAdmin. But when creating a
> database dump, a user might want to have the corresponding CREATE VIEW
> statements included.

Yes.

> 
> Using pmadb, it is possible to define foreign keys on views. Do you
> think, that it is possible to have views "inheriting" their relations
> from the corresponding base tables? This could be really useful,
> especially because views _can_ be updatable under certain circumstances,
> which basically means that you can insert into views or manipulate their
> records directly. I know this sounds sick, but it really works! :o)
> 
> Detecting those updatable views will be easy as soon as the
> information_schema database works without crashing mysqld (d'oh).
> 
> All this is just the result of not being able to sleep at 3 am. ;-)
> 
> 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.

Marc