MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Martijn Tonies Date:June 1 2005 9:38am
Subject:MySQL 5: views and definition storage
View as plain text  
Hi,

A long time ago, I raised the issue with views and how you guys are
retuning the view definition. It's sad to see this hasn't changed?

"Why?" you might ask...

Well, the view definition is auto-generated gibberish that doesn't help
anyone. Users will complain about it, believe me.

For example, this view (taken from a Firebird database):
CREATE VIEW DEBITEUR_NAW
(
 RELATIEID,
 NAAM,
 ADRES,
 POSTCODE,
 PLAATS,
 LAND,
 DEBITEURNUMMER,
 TAV,
 BTWNUMMER,
 BETAALTERMIJN,
 TELEFOON,
 FAX,
 ACTUEEL,
 EMAIL,
 KVKNUMMER
) AS

select d.relatieID,
       r.naam,
       a.adres,
       a.postcode,
       a.plaats,
       l.omschrijving,
       d.debiteurnummer,
       d.Tav,
       d.btwnummer,
       d.betaaltermijn,
       (select telefoonnummer from relatie_telefoon
         where relatieid = r.relatieid and telefooncode = 1 and nummer = 1),
       (select telefoonnummer from relatie_telefoon
         where relatieid = r.relatieid and telefooncode = 2 and nummer = 1),
       r.actueel, r.email, r.kvknummer
from debiteur d
join adres a on (d.relatieid = a.relatieid and a.adrescode = 1)
join relatie r on (d.relatieid = r.relatieid)
left join land l on (a.landcode = l.landcode)


Is stored/returned as:
select `d`.`relatieid` AS `RELATIEID`,`r`.`naam` AS `NAAM`,`a`.`adres` AS
`ADRES`,`a`.`postcode` AS `POSTCODE`,`a`.`plaats` AS
`PLAATS`,`l`.`omschrijving` AS `LAND`,`d`.`debiteurnummer` AS
`DEBITEURNUMMER`,`d`.`tav` AS `TAV`,`d`.`btwnummer` AS
`BTWNUMMER`,`d`.`betaaltermijn` AS `BETAALTERMIJN`,(select
`serp`.`relatie_telefoon`.`telefoonnummer` AS `telefoonnummer` from
`serp`.`relatie_telefoon` where ((`serp`.`relatie_telefoon`.`relatieid` =
`r`.`relatieid`) and (`serp`.`relatie_telefoon`.`telefooncode` = 1) and
(`serp`.`relatie_telefoon`.`nummer` = 1))) AS `TELEFOON`,(select
`serp`.`relatie_telefoon`.`telefoonnummer` AS `telefoonnummer` from
`serp`.`relatie_telefoon` where ((`serp`.`relatie_telefoon`.`relatieid` =
`r`.`relatieid`) and (`serp`.`relatie_telefoon`.`telefooncode` = 2) and
(`serp`.`relatie_telefoon`.`nummer` = 1))) AS `FAX`,`r`.`actueel` AS
`ACTUEEL`,`r`.`email` AS `EMAIL`,`r`.`kvknummer` AS `KVKNUMMER` from
(((`serp`.`debiteur` `d` join `serp`.`adres` `a` on(((`d`.`relatieid` =
`a`.`relatieid`) and (`a`.`adrescode` = 1)))) join `serp`.`relatie` `r`
on((`d`.`relatieid` = `r`.`relatieid`))) left join `serp`.`land` `l`
on((`a`.`landcode` = `l`.`landcode`)))

I'm telling you: this really really sucks.

In my actual CREATE VIEW source, I might have SQL comments, formatting etc
etc...

Here's another example from an actual database I developed and a co-worker
took over:

CREATE VIEW EIGENAANBOD
(
 EIGENAANBODSREGELID,
 BVHNR,
 VBANR,
 VBNCODE,
 ARTIKELOMSCHRIJVING,
 POTSOORT,
 POTMAAT,
 HOOGTE,
 LEVERANCIER,
 LEVERANCIERTEL,
 LEVERANCIERFAX,
 INHOUD,
 AANTALDOZEN,
 PRODUCTVORM,
 INVOERDATUM,
 ARTIKELID,
 ARTIKELKENMERKID
) AS

/*
  Author  : Arno
  Date    : 27-11-2001 09:28:46
  Purpose :

  Date   :
  Changed:
*/

select
  ar.EigenAanbodsRegelID,
  l.bvhnr,
  l.vbanr,
  a.vbncode,
  a.Omschrijving,
  ar.potsoort,
  ar.potmaat,
  ar.hoogte,
  l.naam,
  l.telefoon,
  l.fax,
  ar.aantalperfust,
  ar.aantaldozen,
  ak.kenmerk,
  ar.datum,
  ar.artikelid,
  ar.artikelkenmerkid
from
  eigenaanbodsregel ar
  join leverancier_naw l on (l.relatieid = ar.leverancierid)
  join ARTIKEL a on (a.artikelid = ar.artikelid)
  left outer join ARTIKELKENMERK ak on (ar.artikelkenmerkid =
ak.artikelkenmerkid)


/* Orginal VIEW from Martijn
select ar.EigenAanbodsRegelID, l.bvhnr, l.vbanr, a.vbncode, a.artikel_naam,
       ar.potsoort, ar.potmaat, ar.hoogte,
       l.naam, l.telefoon, l.fax,
       ar.aantalperfust, ar.aantaldozen, ak.kenmerk, ar.datum,
       ar.artikelid, ar.artikelkenmerkid
from eigenaanbodsregel ar join leverancier_naw l on (l.relatieid =
ar.leverancierid)
join ARTIKEL a on (a.artikelid = ar.artikelid)
left outer join ARTIKELKENMERK ak on (ar.artikelkenmerkid =
ak.artikelkenmerkid)
*/
;

There's a huge comment on how the view was defined originally, of course,
the view
could have had more comments about why the view has changed. Either way,
it's
returned by MySQL as:

select `ar`.`eigenaanbodsregelid` AS `EIGENAANBODSREGELID`,`l`.`BVHNR` AS
`BVHNR`,`l`.`VBANR` AS `VBANR`,`a`.`vbncode` AS `VBNCODE`,`a`.`omschrijving`
AS `ARTIKELOMSCHRIJVING`,`ar`.`potsoort` AS `POTSOORT`,`ar`.`potmaat` AS
`POTMAAT`,`ar`.`hoogte` AS `HOOGTE`,`l`.`NAAM` AS
`LEVERANCIER`,`l`.`TELEFOON` AS `LEVERANCIERTEL`,`l`.`FAX` AS
`LEVERANCIERFAX`,`ar`.`aantalperfust` AS `INHOUD`,`ar`.`aantaldozen` AS
`AANTALDOZEN`,`ak`.`kenmerk` AS `PRODUCTVORM`,`ar`.`datum` AS
`INVOERDATUM`,`ar`.`artikelid` AS `ARTIKELID`,`ar`.`artikelkenmerkid` AS
`ARTIKELKENMERKID` from (((`serp`.`eigenaanbodsregel` `ar` join
`serp`.`leverancier_naw` `l` on((`l`.`RELATIEID` = `ar`.`leverancierid`)))
join `serp`.`artikel` `a` on((`a`.`artikelid` = `ar`.`artikelid`))) left
join `serp`.`artikelkenmerk` `ak` on((`ar`.`artikelkenmerkid` =
`ak`.`artikelkenmerkid`)))

As in: bye bye comments, bye bye formatting and hello overly annoying
backticks and whatnot.


Seriously, you really really should investigate if it's possible to store
the original definition instead of this stuff...

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

Thread
MySQL 5: views and definition storageMartijn Tonies1 Jun
  • Re: MySQL 5: views and definition storageMartijn Tonies3 Jun
    • Re: MySQL 5: views and definition storageIngo Strüwing3 Jun
    • Re: MySQL 5: views and definition storageSanja Byelkin24 Jun
  • Re: MySQL 5: views and definition storageMartijn Tonies3 Jun
    • Re: MySQL 5: views and definition storageIngo Strüwing3 Jun
  • Re: MySQL 5: views and definition storageMartijn Tonies3 Jun
    • Re: MySQL 5: views and definition storageIngo Strüwing3 Jun
  • Re: MySQL 5: views and definition storageMartijn Tonies3 Jun
    • Re: MySQL 5: views and definition storageJim Winstead3 Jun
  • Re: MySQL 5: views and definition storageMartijn Tonies3 Jun
    • Re: MySQL 5: views and definition storageJim Winstead3 Jun
  • Re: MySQL 5: views and definition storageMartijn Tonies24 Jun