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