Re: SQL Code Formatting Patch

[Please use the list!]

> -----Original Message-----
> From: Edward Di Geronimo Jr. [mailto:edigeronimo(at)xtracards(dot)com]
> Sent: 08 June 2006 00:55
> To: Dave Page
> Subject: SQL Code Formatting Patch
>
> Hi Dave,
>
> I got sick of the unreadable SQL displayed as the source of
> views, so
> I redid the formatting code. Complex views are very nice to
> read now.
> Below is a sample before and after, using the view that
> prompted me to
> do this. Attached is the patch.

OK, looks quite nice... But (you knew there was a but right?) the brace
positioning around sub selects seems a little off, eg:

------------------------------------
CREATE OR REPLACE VIEW rep_suppreqs AS

SELECT sr_header.sr_guid,
sr_header.sr_timestamp,
sr_header.sr_owner,
sr_header.sr_user,
sr_header.sr_email,
sr_header.sr_phone,
sr_header.sr_fax,
sr_header.sr_hardware_id,
sr_header.sr_software_id,
sr_header.sr_status,
sr_header.sr_type,
sr_header.sr_helpdesk,
sr_header.sr_helpdesk_id,
sr_header.sr_helpdesk_timestamp,
sr_header.sr_description,
sr_header.sr_details,
sr_header.sr_priority,
sr_header.sr_updated,
sr_header.sr_owner_status,

CASE
WHEN sr_header.sr_status::text = 'U'::character
varying::text THEN 'With User'::text
WHEN sr_header.sr_status::text = 'O'::character
varying::text THEN 'Open'::text
WHEN sr_header.sr_status::text = 'H'::character
varying::text THEN 'With Helpdesk'::text
WHEN sr_header.sr_status::text = 'C'::character
varying::text THEN 'Closed'::text
ELSE '** UNKNOWN **'::text
END AS status,

CASE
WHEN sr_header.sr_type::text = 'H'::character varying::text
THEN 'Hardware Fault'::text
WHEN sr_header.sr_type::text = 'S'::character varying::text
THEN 'Software Fault'::text
WHEN sr_header.sr_type::text = 'I'::character varying::text
THEN 'Information Request'::text
WHEN sr_header.sr_type::text = 'W'::character varying::text
THEN 'Work Request'::text
WHEN sr_header.sr_type::text = 'B'::character varying::text
THEN 'Beta Test Report'::text
ELSE '** UNKNOWN **'::text
END AS "type",
(
SELECT sys_user.fullname
FROM sys_user
WHERE sys_user.username::text = sr_header.sr_owner::text)
AS "owner",
(
SELECT (((a1.gbl_name::text || ' - '::character
varying::text) || s1.sw_name::text) || ' v'::character varying::text) ||
s1.sw_version::text
FROM sw_software s1,
gbl_addr_book a1
WHERE s1.sw_vendor = a1.gbl_guid
AND s1.sw_guid = sr_header.sr_software_id) AS
software,
(
SELECT ((((h2.hd_hardware_id::text || ' ('::character
varying::text) || a2.gbl_name::text) || ',
'::character varying::text) ||
h2.hd_model::text) || ')'::character varying::text
FROM hd_hardware h2,
gbl_addr_book a2
WHERE h2.hd_manufacturer = a2.gbl_guid
AND h2.hd_guid = sr_header.sr_hardware_id) AS
hardware,
(
SELECT a3.gbl_name
FROM gbl_addr_book a3
WHERE a3.gbl_guid = sr_header.sr_helpdesk) AS helpdesk

FROM sr_header;
------------------------------------

I would expect the subselects to look more like:

(
SELECT ((((h2.hd_hardware_id::text || ' ('::character
varying::text) || a2.gbl_name::text) || ',
'::character varying::text) ||
h2.hd_model::text) || ')'::character varying::text
FROM hd_hardware h2,
gbl_addr_book a2
WHERE h2.hd_manufacturer = a2.gbl_guid
AND h2.hd_guid = sr_header.sr_hardware_id
) AS hardware,

Having the braces in non-matched positions decreases the readability I
think. Thoughts?

> Hope you had a nice trip to Paris.

Yes, very good thanks :-)

Regards. Dave