Alter View Unreadable

[Robert from The Hague]

The queries that I use for views start off being readable when I first enter them in the DB. (1)

When I call ALTER VIEW I get unformated versions with loads of additional characters/db-references. (2)

Is there any way to do this differently? (apart from saving the query in favorites)

[attachment=789:view.jpg]

[peterlaursen]

This is a server issue and not a SQLyog issue!

SHOW CREATE VIEW does not return the original formatting.

You can go to http://bugs.mysql.com and find lots of complaints about this.

It is a silly issue, because the original formatting is stored (in a .frm file) actually, but the server does not use it.

As SQLyog has no access to the file system, it cannot know the original formatting – only what the MySQL server returns for SHOW CREATE VIEW.

[peterlaursen]

Let me add one detail!

A view is 'frozen' at CREATE time!

(and this is a SQL standard specification – not only a MySQL server implementation!)

If you define “CREATE VIEW `someview` AS SELECT * FROM `sometable`;” — where `sometable` has columns a, b and c

.. and add more columns (like d,e) to ´sometable` later, then `someview` will (and should!) only have columns a, b and c!

So the original formatting at create time may not be valid at a later point.

This is probably one detail that causes problems with using the original formatting!

[Robert from The Hague]

Thanks Peter for your explanation. I know that MySQL returns the statements when you call SHOW CREATE VIEW but since SQLyog is a graphical front-end for MySQL it would be very nice if the query editor would interpret the query and reformat it to a readable format. Perhaps a nice extra feature.

Quote:

As SQLyog has no access to the file system, it cannot know the original formatting – only what the MySQL server returns for SHOW CREATE VIEW.

Thanks for indicating that the FRM files exist, although I don't understand what you mean by the quote. SQLyog reads/writes sql files from the file system. Wouldn't it be an idea to point out to SQLyog where the MySQL directory is so it could retrieve the FRM file and present the formated query?

regards

[peterlaursen]

SQLyog connects to MySQL with the MySQL client library (the C API).

Only when connecting to 'localhost' on a Windows system (and only if the user is an admin user) it will be possible to read the content of files.

On Linux etc. you would at least need to change Operations System/File System privileges. The .frm files are in the MySQL data folder that is owned by 'mysql' Linux user (who is the only member of the 'mysql' user group). Only this 'mysql' user and 'root' user can read the files with a *nix standard privileges setup. I am pretty sure that Linux Sysadmins would reject the idea that users 'robert' or 'peter' should be able to read the files (for security reasons)!

And when connecting to a remote host there is absolutely no way to read the files. The client can only get what info the server returns from SHOW and SELECT statements.

As my second post indicates it is also not 100% certain that the original CREATE statement is valid anymore. It was at create time – that is the only thing that is certain – but the table(s) underlying the view (and stored programs if such are used for defining specific columns of the view) may have changed in between! There is no information available (not to the server itself either) to tell if it still is! That is why the server itself cannot use it directly!

Still I think some formatting should and could be done (at least the original linebreaks and indentations should be respected), but it should be done on the server side.

[darkroastbeans]

It would be an enormous help if YOG formatted the results (even though I realize the burden shouldn't be on YOG to do so). I've evaluated several different MySQL GUI tools, and I believe that SQLMaestro for MySQL does something like this — simple line breaks and indentation go a long way 🙂

[peterlaursen]

After analysis we realize that we could query Information_Schema 'views' and 'columns' table and *construct* a better readable (but functionally identical) CREATE statement for the VIEW.

[peterlaursen]

BUT …

.. it will have to be analyzed in depth!

A VIEW definiton can use UNIONs, JOINs, GROUP BY's, aggregate functions, and even Stored Functions (do not know much about UDFs (User Defined Functions))! And frankly I do not trust MySQL Information_Schema too much before verifying every detail …

@coffeeboy .. do you know positively that this 'SQL monster' is able to handle all that stuff correctly?

[Author]

Posts