-----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):
A view is basically a derived table, based on a SELECT statement. That means that I can take the result of a SELECT query and create a new permanent virtual table from it. Of course, you can perform SELECTs on it as if it was a real table and its content is always synchronized with the underlying base tables. And the best is: you can grant privileges on a view as if it was a table!
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.
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.
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!
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.
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?
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.
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. :-)
But I will probably also post this to the RFE tracker, soon.
Some literature about all this:
CREATE VIEW syntax: http://dev.mysql.com/doc/mysql/en/create-view.html
About information_schema: http://dev.mysql.com/doc/mysql/en/information-schema.html
About stored procedures: http://dev.mysql.com/doc/mysql/en/stored-procedures.html
Regards,
AMT