List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:October 5 2004 10:52am
Subject:Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
View as plain text  
On 05 Oct 2004 12:02:44 +0200, Harald Fuchs wrote:
> "Martijn Tonies" <m.tonies@stripped> writes:
>> 
>> I was asking if the view-source can be stored, so that it can be retrieved
>> the way I created it. Do you agree or disagree?
> 
> I disagree.  A proper information_schema implementation is much more
> than a stored SQL string - it's a standardized way to access meta
> information about your DB objects.  Thus it must parse the SQL DDL
> strings and store its "meaning".  Additionally storing the SQL string
> _as entered_ would be redundancy.

No, it is not redundant. If I look at the information in the
VIEW_COLUMN_USAGE, VIEW_ROUTINE_USAGE, VIEW_TABLE_USAGE and VIEWS
views in the INFORMATION_SCHEMA (or their equivalent base tables in
the DEFINITION_SCHEMA), they do not contain sufficient information to
reconstruct anything but trivial views.


> SHOW CREATE VIEW could be implemented by reconstructing some standard
> representation of SQL DDL, but this would not always be exactly what
> you entered.

I agree. One could imagine that views are stored in whatever format
comes out of the parser so they are easily integrated into the
execution plan at runtime. Obviously when you convert them back to a
human readable query expression that query expression will be
formatted differently. That seems perfectly reasonable to me.


But since the INFORMATION_SCHEMA simply does not have the information
required to reconstruct a view, you need something else too. And just
as it would be perfectly reasonable to use the query tree, it would
also be perfectly reasonable to take the original query expression for
that. Both have advantages and disadvantages.

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