List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:October 5 2004 10:23am
Subject:Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
View as plain text  
On 05 Oct 2004 11:46:18 +0200, Harald Fuchs wrote:
> "Martijn Tonies" writes:
>> 
>> MS SQL, or Firebird, for example, store the view-source as defined -
>> this includes comments, spacing etc etc... In short: it becomes usuable.
>> 
>> MySQL should do this too. From reading these lists, I think MySQL
>> only stores the resulting structure - or something similar - and
>> (currently) not the view source. To make views useful, better change
>> it... :-)
> 
> Nope.  A standards-compliant database is _required_ to store the
> structure of its objects in its internal information_schema

There is no data stored in the INFORMATION_SCHEMA at all:
<quote>
4.2 Introduction to the Information Schema.

The views of the Information Schema are viewed tables ....
</quote> ISO/IEC 9075-11:2003

> not some SQL string.

The VIEWS view in the INFORMATION_SCHEMA is derived from the VIEWS
base table in the DEFINITION_SCHEMA. Part of the definition of the
that reads:
<quote>
6.66 VIEWS base table
(..)
Definition

CREATE TABLE VIEWS (
    TABLE_CATALOG    INFORMATION_SCHEMA.SQL_IDENTIFIER,
    TABLE_SCHEMA     INFORMATION_SCHEMA.SQL_IDENTIFIER,
    TABLE_NAME       INFORMATION_SCHEMA.SQL_IDENTIFIER,
    VIEW_DEFINITION  INFORMATION_SCHEMA.CHARACTER_DATE,
</quote> ISO/IEC 9075-11:2003

So what would VIEW_DEFINITION store exactly if not "the query
expression that defines a view"?

Since the is no requirement to have an accessible DEFINITION_SCHEMA
there may be a mechanism to recreate the definition on the fly from
other information, but the same goes for the other view related base
tables in the DEFINITION_SCHEMA. I see no requirement to store only
the structure and not the SQL string.

While I don't really care about the way the structure of a view is
returned, I would very much like for it to be without those backticks.

Jochem
Thread
CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies29 Sep
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWEldo Skaria4 Oct
    • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWJochem van Dieten4 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies4 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies4 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWHarald Fuchs5 Oct
    • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWJochem van Dieten5 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies5 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWHarald Fuchs5 Oct
    • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWJochem van Dieten5 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies5 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWHarald Fuchs5 Oct