Create VIEW readability

#1

Hello group,

for purposes of better readability I tried to avoid subqueries (which
tend to become quite obfuscating at the 4th or 5th level of indention).
Therefore I tried the following, which is well readable:

CREATE TEMPORARY VIEW pxe1 AS
SELECT hosts.ip AS clientip, dhcppxe.filename AS pxefilename,
dhcppxe.nextserverhostid AS nextserverhostid FROM hosts JOIN dhcppxe ON
hosts.hostid = dhcppxe.hostid;
CREATE TEMPORARY VIEW pxe2 AS
SELECT pxe1.clientip, pxe1.pxefilename, hosts.ip AS serverip FROM pxe1
JOIN hosts ON pxe1.nextserverhostid = hosts.hostid;
CREATE TEMPORARY VIEW pxe3 AS
SELECT pxe2.*, dns.hostname AS clienthostname FROM pxe2 LEFT JOIN dns
ON pxe2.clientip = dns.ip;
CREATE TEMPORARY VIEW pxe4 AS
SELECT pxe3.*, dns.hostname AS serverhostname FROM pxe3 LEFT JOIN dns
ON pxe3.serverip = dns.ip;
CREATE VIEW pxe AS
SELECT * FROM pxe4;

In each temporary view one more piece of information is added until the
final VIEW is completely assembled. However, "pxe" is also temporary
because it includes the (temporary) pxe4. This approach has two solutions:

1. Make all VIEWs permanent. This would cause a lot of "half-done" views
lying around.
2. Use subqueries. This would make it all a huge mess.

What I would like to do is something like (imaginary syntax):

CREATE PERMENANT VIEW pxe AS SELECT * FROM pxe4;

which would then recursively copy the query statements from all
temporary sub-views. Is something like this possible or do I have to use
some kind of scripting language to achieve it for me?

Thanks in advance,
Johannes

#2

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:

Quote:

CREATE TEMPORARY VIEW pxe1 AS
SELECT hosts.ip AS clientip, dhcppxe.filename AS pxefilename,
dhcppxe.nextserverhostid AS nextserverhostid FROM hosts JOIN dhcppxe ON
hosts.hostid = dhcppxe.hostid;
CREATE TEMPORARY VIEW pxe2 AS
SELECT pxe1.clientip, pxe1.pxefilename, hosts.ip AS serverip FROM pxe1
JOIN hosts ON pxe1.nextserverhostid = hosts.hostid;
CREATE TEMPORARY VIEW pxe3 AS
SELECT pxe2.*, dns.hostname AS clienthostname FROM pxe2 LEFT JOIN dns
ON pxe2.clientip = dns.ip;
CREATE TEMPORARY VIEW pxe4 AS
SELECT pxe3.*, dns.hostname AS serverhostname FROM pxe3 LEFT JOIN dns
ON pxe3.serverip = dns.ip;
CREATE VIEW pxe AS
SELECT * FROM pxe4;

In each temporary view one more piece of information is added until the
final VIEW is completely assembled. However, "pxe" is also temporary
because it includes the (temporary) pxe4. This approach has two solutions:

1. Make all VIEWs permanent. This would cause a lot of "half-done" views
lying around.
2. Use subqueries. This would make it all a huge mess.

What I would like to do is something like (imaginary syntax):

CREATE PERMENANT VIEW pxe AS SELECT * FROM pxe4;

which would then recursively copy the query statements from all
temporary sub-views. Is something like this possible or do I have to use
some kind of scripting language to achieve it for me?

I don't think that i possible, and you'd have to stick with one of your
two solutions.

What is so unreadable about

SELECT h1.ip AS clientip,
dhcp.filename AS pxefilename, dhcp.nextserverhostid,
h2.ip AS serverip,
dns1.hostname AS clienthostname,
dns2.hostname AS clienthostname,
FROM hosts h1
JOIN dhcppxe dhcp ON h1.hostid = dhcp.hostid
JOIN hosts h2 ON dhcp.nextserverhostid = h2.hostid
LEFT OUTER JOIN dns dns1 ON h1.ip = dns1.ip
LEFT OUTER JOIN dns dns2 ON h2.ip = dns2.ip;

Of course it is a matter of taste, but I think it is easier to understand
than your stack of view definitions.

Yours,
Laurenz Albe

#3

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:

Quote:

CREATE TEMPORARY VIEW pxe1 AS
SELECT hosts.ip AS clientip, dhcppxe.filename AS pxefilename,
dhcppxe.nextserverhostid AS nextserverhostid FROM hosts JOIN dhcppxe ON
hosts.hostid = dhcppxe.hostid;
CREATE TEMPORARY VIEW pxe2 AS
SELECT pxe1.clientip, pxe1.pxefilename, hosts.ip AS serverip FROM pxe1
JOIN hosts ON pxe1.nextserverhostid = hosts.hostid;
CREATE TEMPORARY VIEW pxe3 AS
SELECT pxe2.*, dns.hostname AS clienthostname FROM pxe2 LEFT JOIN dns
ON pxe2.clientip = dns.ip;
CREATE TEMPORARY VIEW pxe4 AS
SELECT pxe3.*, dns.hostname AS serverhostname FROM pxe3 LEFT JOIN dns
ON pxe3.serverip = dns.ip;
CREATE VIEW pxe AS
SELECT * FROM pxe4;

In each temporary view one more piece of information is added until the
final VIEW is completely assembled. However, "pxe" is also temporary
because it includes the (temporary) pxe4. This approach has two solutions:

1. Make all VIEWs permanent. This would cause a lot of "half-done" views
lying around.
2. Use subqueries. This would make it all a huge mess.

What I would like to do is something like (imaginary syntax):

CREATE PERMENANT VIEW pxe AS SELECT * FROM pxe4;

which would then recursively copy the query statements from all
temporary sub-views. Is something like this possible or do I have to use
some kind of scripting language to achieve it for me?

I don't think that i possible, and you'd have to stick with one of your
two solutions.

What is so unreadable about

SELECT h1.ip AS clientip,
dhcp.filename AS pxefilename, dhcp.nextserverhostid,
h2.ip AS serverip,
dns1.hostname AS clienthostname,
dns2.hostname AS clienthostname,
FROM hosts h1
JOIN dhcppxe dhcp ON h1.hostid = dhcp.hostid
JOIN hosts h2 ON dhcp.nextserverhostid = h2.hostid
LEFT OUTER JOIN dns dns1 ON h1.ip = dns1.ip
LEFT OUTER JOIN dns dns2 ON h2.ip = dns2.ip;

Of course it is a matter of taste, but I think it is easier to understand
than your stack of view definitions.

Yours,
Laurenz Albe

#4

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:

Quote:

CREATE TEMPORARY VIEW pxe1 AS
SELECT hosts.ip AS clientip, dhcppxe.filename AS pxefilename,
dhcppxe.nextserverhostid AS nextserverhostid FROM hosts JOIN dhcppxe ON
hosts.hostid = dhcppxe.hostid;
CREATE TEMPORARY VIEW pxe2 AS
SELECT pxe1.clientip, pxe1.pxefilename, hosts.ip AS serverip FROM pxe1
JOIN hosts ON pxe1.nextserverhostid = hosts.hostid;
CREATE TEMPORARY VIEW pxe3 AS
SELECT pxe2.*, dns.hostname AS clienthostname FROM pxe2 LEFT JOIN dns
ON pxe2.clientip = dns.ip;
CREATE TEMPORARY VIEW pxe4 AS
SELECT pxe3.*, dns.hostname AS serverhostname FROM pxe3 LEFT JOIN dns
ON pxe3.serverip = dns.ip;
CREATE VIEW pxe AS
SELECT * FROM pxe4;

In each temporary view one more piece of information is added until the
final VIEW is completely assembled. However, "pxe" is also temporary
because it includes the (temporary) pxe4. This approach has two solutions:

1. Make all VIEWs permanent. This would cause a lot of "half-done" views
lying around.
2. Use subqueries. This would make it all a huge mess.

What I would like to do is something like (imaginary syntax):

CREATE PERMENANT VIEW pxe AS SELECT * FROM pxe4;

which would then recursively copy the query statements from all
temporary sub-views. Is something like this possible or do I have to use
some kind of scripting language to achieve it for me?

I don't think that i possible, and you'd have to stick with one of your
two solutions.

What is so unreadable about

SELECT h1.ip AS clientip,
dhcp.filename AS pxefilename, dhcp.nextserverhostid,
h2.ip AS serverip,
dns1.hostname AS clienthostname,
dns2.hostname AS clienthostname,
FROM hosts h1
JOIN dhcppxe dhcp ON h1.hostid = dhcp.hostid
JOIN hosts h2 ON dhcp.nextserverhostid = h2.hostid
LEFT OUTER JOIN dns dns1 ON h1.ip = dns1.ip
LEFT OUTER JOIN dns dns2 ON h2.ip = dns2.ip;

Of course it is a matter of taste, but I think it is easier to understand
than your stack of view definitions.

Yours,
Laurenz Albe

#5

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:

Quote:

CREATE TEMPORARY VIEW pxe1 AS
SELECT hosts.ip AS clientip, dhcppxe.filename AS pxefilename,
dhcppxe.nextserverhostid AS nextserverhostid FROM hosts JOIN dhcppxe ON
hosts.hostid = dhcppxe.hostid;
CREATE TEMPORARY VIEW pxe2 AS
SELECT pxe1.clientip, pxe1.pxefilename, hosts.ip AS serverip FROM pxe1
JOIN hosts ON pxe1.nextserverhostid = hosts.hostid;
CREATE TEMPORARY VIEW pxe3 AS
SELECT pxe2.*, dns.hostname AS clienthostname FROM pxe2 LEFT JOIN dns
ON pxe2.clientip = dns.ip;
CREATE TEMPORARY VIEW pxe4 AS
SELECT pxe3.*, dns.hostname AS serverhostname FROM pxe3 LEFT JOIN dns
ON pxe3.serverip = dns.ip;
CREATE VIEW pxe AS
SELECT * FROM pxe4;

In each temporary view one more piece of information is added until the
final VIEW is completely assembled. However, "pxe" is also temporary
because it includes the (temporary) pxe4. This approach has two solutions:

1. Make all VIEWs permanent. This would cause a lot of "half-done" views
lying around.
2. Use subqueries. This would make it all a huge mess.

What I would like to do is something like (imaginary syntax):

CREATE PERMENANT VIEW pxe AS SELECT * FROM pxe4;

which would then recursively copy the query statements from all
temporary sub-views. Is something like this possible or do I have to use
some kind of scripting language to achieve it for me?

I don't think that i possible, and you'd have to stick with one of your
two solutions.

What is so unreadable about

SELECT h1.ip AS clientip,
dhcp.filename AS pxefilename, dhcp.nextserverhostid,
h2.ip AS serverip,
dns1.hostname AS clienthostname,
dns2.hostname AS clienthostname,
FROM hosts h1
JOIN dhcppxe dhcp ON h1.hostid = dhcp.hostid
JOIN hosts h2 ON dhcp.nextserverhostid = h2.hostid
LEFT OUTER JOIN dns dns1 ON h1.ip = dns1.ip
LEFT OUTER JOIN dns dns2 ON h2.ip = dns2.ip;

Of course it is a matter of taste, but I think it is easier to understand
than your stack of view definitions.

Yours,
Laurenz Albe

#6

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:

Quote:

CREATE TEMPORARY VIEW pxe1 AS
SELECT hosts.ip AS clientip, dhcppxe.filename AS pxefilename,
dhcppxe.nextserverhostid AS nextserverhostid FROM hosts JOIN dhcppxe ON
hosts.hostid = dhcppxe.hostid;
CREATE TEMPORARY VIEW pxe2 AS
SELECT pxe1.clientip, pxe1.pxefilename, hosts.ip AS serverip FROM pxe1
JOIN hosts ON pxe1.nextserverhostid = hosts.hostid;
CREATE TEMPORARY VIEW pxe3 AS
SELECT pxe2.*, dns.hostname AS clienthostname FROM pxe2 LEFT JOIN dns
ON pxe2.clientip = dns.ip;
CREATE TEMPORARY VIEW pxe4 AS
SELECT pxe3.*, dns.hostname AS serverhostname FROM pxe3 LEFT JOIN dns
ON pxe3.serverip = dns.ip;
CREATE VIEW pxe AS
SELECT * FROM pxe4;

In each temporary view one more piece of information is added until the
final VIEW is completely assembled. However, "pxe" is also temporary
because it includes the (temporary) pxe4. This approach has two solutions:

1. Make all VIEWs permanent. This would cause a lot of "half-done" views
lying around.
2. Use subqueries. This would make it all a huge mess.

What I would like to do is something like (imaginary syntax):

CREATE PERMENANT VIEW pxe AS SELECT * FROM pxe4;

which would then recursively copy the query statements from all
temporary sub-views. Is something like this possible or do I have to use
some kind of scripting language to achieve it for me?

I don't think that i possible, and you'd have to stick with one of your
two solutions.

What is so unreadable about

SELECT h1.ip AS clientip,
dhcp.filename AS pxefilename, dhcp.nextserverhostid,
h2.ip AS serverip,
dns1.hostname AS clienthostname,
dns2.hostname AS clienthostname,
FROM hosts h1
JOIN dhcppxe dhcp ON h1.hostid = dhcp.hostid
JOIN hosts h2 ON dhcp.nextserverhostid = h2.hostid
LEFT OUTER JOIN dns dns1 ON h1.ip = dns1.ip
LEFT OUTER JOIN dns dns2 ON h2.ip = dns2.ip;

Of course it is a matter of taste, but I think it is easier to understand
than your stack of view definitions.

Yours,
Laurenz Albe

#7

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:

Quote:

CREATE TEMPORARY VIEW pxe1 AS
SELECT hosts.ip AS clientip, dhcppxe.filename AS pxefilename,
dhcppxe.nextserverhostid AS nextserverhostid FROM hosts JOIN dhcppxe ON
hosts.hostid = dhcppxe.hostid;
CREATE TEMPORARY VIEW pxe2 AS
SELECT pxe1.clientip, pxe1.pxefilename, hosts.ip AS serverip FROM pxe1
JOIN hosts ON pxe1.nextserverhostid = hosts.hostid;
CREATE TEMPORARY VIEW pxe3 AS
SELECT pxe2.*, dns.hostname AS clienthostname FROM pxe2 LEFT JOIN dns
ON pxe2.clientip = dns.ip;
CREATE TEMPORARY VIEW pxe4 AS
SELECT pxe3.*, dns.hostname AS serverhostname FROM pxe3 LEFT JOIN dns
ON pxe3.serverip = dns.ip;
CREATE VIEW pxe AS
SELECT * FROM pxe4;

In each temporary view one more piece of information is added until the
final VIEW is completely assembled. However, "pxe" is also temporary
because it includes the (temporary) pxe4. This approach has two solutions:

1. Make all VIEWs permanent. This would cause a lot of "half-done" views
lying around.
2. Use subqueries. This would make it all a huge mess.

What I would like to do is something like (imaginary syntax):

CREATE PERMENANT VIEW pxe AS SELECT * FROM pxe4;

which would then recursively copy the query statements from all
temporary sub-views. Is something like this possible or do I have to use
some kind of scripting language to achieve it for me?

I don't think that i possible, and you'd have to stick with one of your
two solutions.

What is so unreadable about

SELECT h1.ip AS clientip,
dhcp.filename AS pxefilename, dhcp.nextserverhostid,
h2.ip AS serverip,
dns1.hostname AS clienthostname,
dns2.hostname AS clienthostname,
FROM hosts h1
JOIN dhcppxe dhcp ON h1.hostid = dhcp.hostid
JOIN hosts h2 ON dhcp.nextserverhostid = h2.hostid
LEFT OUTER JOIN dns dns1 ON h1.ip = dns1.ip
LEFT OUTER JOIN dns dns2 ON h2.ip = dns2.ip;

Of course it is a matter of taste, but I think it is easier to understand
than your stack of view definitions.

Yours,
Laurenz Albe

#8

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:

Quote:

CREATE TEMPORARY VIEW pxe1 AS
SELECT hosts.ip AS clientip, dhcppxe.filename AS pxefilename,
dhcppxe.nextserverhostid AS nextserverhostid FROM hosts JOIN dhcppxe ON
hosts.hostid = dhcppxe.hostid;
CREATE TEMPORARY VIEW pxe2 AS
SELECT pxe1.clientip, pxe1.pxefilename, hosts.ip AS serverip FROM pxe1
JOIN hosts ON pxe1.nextserverhostid = hosts.hostid;
CREATE TEMPORARY VIEW pxe3 AS
SELECT pxe2.*, dns.hostname AS clienthostname FROM pxe2 LEFT JOIN dns
ON pxe2.clientip = dns.ip;
CREATE TEMPORARY VIEW pxe4 AS
SELECT pxe3.*, dns.hostname AS serverhostname FROM pxe3 LEFT JOIN dns
ON pxe3.serverip = dns.ip;
CREATE VIEW pxe AS
SELECT * FROM pxe4;

In each temporary view one more piece of information is added until the
final VIEW is completely assembled. However, "pxe" is also temporary
because it includes the (temporary) pxe4. This approach has two solutions:

1. Make all VIEWs permanent. This would cause a lot of "half-done" views
lying around.
2. Use subqueries. This would make it all a huge mess.

What I would like to do is something like (imaginary syntax):

CREATE PERMENANT VIEW pxe AS SELECT * FROM pxe4;

which would then recursively copy the query statements from all
temporary sub-views. Is something like this possible or do I have to use
some kind of scripting language to achieve it for me?

I don't think that i possible, and you'd have to stick with one of your
two solutions.

What is so unreadable about

SELECT h1.ip AS clientip,
dhcp.filename AS pxefilename, dhcp.nextserverhostid,
h2.ip AS serverip,
dns1.hostname AS clienthostname,
dns2.hostname AS clienthostname,
FROM hosts h1
JOIN dhcppxe dhcp ON h1.hostid = dhcp.hostid
JOIN hosts h2 ON dhcp.nextserverhostid = h2.hostid
LEFT OUTER JOIN dns dns1 ON h1.ip = dns1.ip
LEFT OUTER JOIN dns dns2 ON h2.ip = dns2.ip;

Of course it is a matter of taste, but I think it is easier to understand
than your stack of view definitions.

Yours,
Laurenz Albe

#9

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:

Quote:

CREATE TEMPORARY VIEW pxe1 AS
SELECT hosts.ip AS clientip, dhcppxe.filename AS pxefilename,
dhcppxe.nextserverhostid AS nextserverhostid FROM hosts JOIN dhcppxe ON
hosts.hostid = dhcppxe.hostid;
CREATE TEMPORARY VIEW pxe2 AS
SELECT pxe1.clientip, pxe1.pxefilename, hosts.ip AS serverip FROM pxe1
JOIN hosts ON pxe1.nextserverhostid = hosts.hostid;
CREATE TEMPORARY VIEW pxe3 AS
SELECT pxe2.*, dns.hostname AS clienthostname FROM pxe2 LEFT JOIN dns
ON pxe2.clientip = dns.ip;
CREATE TEMPORARY VIEW pxe4 AS
SELECT pxe3.*, dns.hostname AS serverhostname FROM pxe3 LEFT JOIN dns
ON pxe3.serverip = dns.ip;
CREATE VIEW pxe AS
SELECT * FROM pxe4;

In each temporary view one more piece of information is added until the
final VIEW is completely assembled. However, "pxe" is also temporary
because it includes the (temporary) pxe4. This approach has two solutions:

1. Make all VIEWs permanent. This would cause a lot of "half-done" views
lying around.
2. Use subqueries. This would make it all a huge mess.

What I would like to do is something like (imaginary syntax):

CREATE PERMENANT VIEW pxe AS SELECT * FROM pxe4;

which would then recursively copy the query statements from all
temporary sub-views. Is something like this possible or do I have to use
some kind of scripting language to achieve it for me?

I don't think that i possible, and you'd have to stick with one of your
two solutions.

What is so unreadable about

SELECT h1.ip AS clientip,
dhcp.filename AS pxefilename, dhcp.nextserverhostid,
h2.ip AS serverip,
dns1.hostname AS clienthostname,
dns2.hostname AS clienthostname,
FROM hosts h1
JOIN dhcppxe dhcp ON h1.hostid = dhcp.hostid
JOIN hosts h2 ON dhcp.nextserverhostid = h2.hostid
LEFT OUTER JOIN dns dns1 ON h1.ip = dns1.ip
LEFT OUTER JOIN dns dns2 ON h2.ip = dns2.ip;

Of course it is a matter of taste, but I think it is easier to understand
than your stack of view definitions.

Yours,
Laurenz Albe

#10

Laurenz Albe schrieb:

Quote:

I don't think that i possible, and you'd have to stick with one of your
two solutions.

OK.

Quote:

What is so unreadable about

SELECT h1.ip AS clientip,
dhcp.filename AS pxefilename, dhcp.nextserverhostid,
h2.ip AS serverip,
dns1.hostname AS clienthostname,
dns2.hostname AS clienthostname,
FROM hosts h1
JOIN dhcppxe dhcp ON h1.hostid = dhcp.hostid
JOIN hosts h2 ON dhcp.nextserverhostid = h2.hostid
LEFT OUTER JOIN dns dns1 ON h1.ip = dns1.ip
LEFT OUTER JOIN dns dns2 ON h2.ip = dns2.ip;

Of course it is a matter of taste, but I think it is easier to understand
than your stack of view definitions.

Yes, indeed it is much more readable than the subqueries, which I used
initially because I found them more readable than the JOINs. But you are
right, I think I'll stick with your solution.

Thanks,
Johannes