Re: SQL Code Formatting Patch

[Please use the list!]

> -----Original Message-----
> From: Edward Di Geronimo Jr. [mailto:edigeronimo@xtracards.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