[Phpmyadmin-devel] Having fun with views

[Alexander M. Turek]

-----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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCLRlf8c/ssWf/SMcRAoJwAJ9v6FFFiG2GHTsO1zQp2A9oFwMX4ACggYj8
f/YDJ0wtgGQdRtXWJOTrxrQ=
=Iizh
-----END PGP SIGNATURE-----