-----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
Hi Alexander!
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.
That's great news. In fact, what you implemented and talk about is exactly what I thought it could be like when we met at the last years PHP conference. Shame though I didn't have time to help you on this.
Anyways, your proposal to let the views be based on our display results is very handy and intuitive behavior. Combining/Replacing views with the bookmark system also sounds both workable and intuitive.
Bottom line is: The way you outlined it, is the way to go.
I would very much like to offer help on this issue. I should also be able to get into our current codebase in the next couple of weeks, and be of use again. My other hobby-project has come to a quite stable point for now, and I also need another project for change to dedicate work again for. What I mean is: I hope to be able to help you, but I can't promise. :-))
Great work until now, I just want to add.
Regards, Garvin
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
-----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
Alexander M. Turek a écrit :
-----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...
I mean that, when a user looks at the menu tabs in table view, he sees Export but not Import. Granted, we added an "Import files" to the query window but this window is not immediately seen, so some users still wonder about how to import.
So I think that some users will be looking for a "View" tab.
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-----
-
Alexander M. Turek -
Garvin Hicking -
Marc Delisle