Loading ...
Sorry, an error occurred while loading the content.

ANN: SQLUtilities 7.0

Expand Messages
  • David Fishburn
    SQLUtilities : SQL utilities - Formatting SQL, generate - columns lists, procedures for databases http://www.vim.org/scripts/script.php?script_id=492
    Message 1 of 1 , Dec 4, 2012
    • 0 Attachment
      SQLUtilities : SQL utilities - Formatting SQL, generate - columns
      lists, procedures for databases
      http://www.vim.org/scripts/script.php?script_id=492

      SQLUtilities can do many things, generating column lists for pasting in code and others, but it is most used for formatting SQL statements into a nice readable format.

      Simple example:

        SELECT m.MSG_ID, m.PRIORITY_ID, CUST.CUST_NBR, CUST.CUST_NM,
        CUST.CUST_LEGAL_NM, CUST.STORE_ADDR_1, CUST.STORE_ADDR_2,
        CUST.CROSS_STREET, XMLELEMENT( 'Alerts', XMLELEMENT( 'Alert_alert_id',
        alert_id ), XMLELEMENT( 'Alert_agent_id', agent_id ), XMLELEMENT(
        'Alert_alert_type_id', alert_type_desc), XMLELEMENT(
        'Alert_alert_date', alert_date), XMLELEMENT(
        'Alert_url_reference', url_reference), XMLELEMENT(
        'Alert_read_status', read_status )) CUST.STORE_CITY,
        CUST.STORE_ST, CUST.POST_CODE, CUST.STORE_MGR_NM, FROM MESSAGES m JOIN
        PRIORITY_CD P WHERE m.to_person_id = ?  AND p.NAME = 'PRI_EMERGENCY' AND
        p.JOB = 'Plumber' AND m.status_id < ( SELECT s.STATUS_ID FROM
        MSG_STATUS_CD s WHERE s.NAME = 'MSG_READ') ORDER BY m.msg_id desc

      Becomes (assuming email doesn't mess it up):

        SELECT m.MSG_ID, m.PRIORITY_ID, CUST.CUST_NBR, CUST.CUST_NM,
               CUST.CUST_LEGAL_NM, CUST.STORE_ADDR_1, CUST.STORE_ADDR_2,
               CUST.CROSS_STREET,
               XMLELEMENT(
                   'Alerts', XMLELEMENT( 'Alert_alert_id', alert_id ),
                   XMLELEMENT( 'Alert_agent_id', agent_id ),
                   XMLELEMENT( 'Alert_alert_type_id', alert_type_desc),
                   XMLELEMENT( 'Alert_alert_date', alert_date),
                   XMLELEMENT(
                       'Alert_url_reference', url_reference
                    ), XMLELEMENT( 'Alert_read_status', read_status )
               ) CUST.STORE_CITY, CUST.STORE_ST, CUST.POST_CODE,
               CUST.STORE_MGR_NM
          FROM MESSAGES m
          JOIN PRIORITY_CD P
         WHERE m.to_person_id = ?
           AND p.NAME = 'PRI_EMERGENCY'
           AND p.JOB = 'Plumber'
           AND m.status_id < (
                SELECT s.STATUS_ID
                  FROM MSG_STATUS_CD s
                 WHERE s.NAME = 'MSG_READ'
               )
         ORDER BY m.msg_id desc

      It will also format other statements, see web page for additional examples.


      I have uploaded a new version (7.0).

      NF: Comments in your SQL are now allowed and will be formatted appropriately (which usually means ignored).  If you Vim does not have syntax support, all comments will be removed prior to formatting (you are asked to confirm).

      NF: A major overhaul to how SQL is formatted.  Fixed a few bugs which crept up while testing the new methods.  The main change was how long lines are handled.  Now, long lines are only split when a comma or open paranthesis is found.  This can still leave lines long but the previous method could leave the new SQL nearly unreadabable depending on it's content.  Thanks to Jeremey Hustman for providing the ColdFusion code samples.

      NF: Added new options which allow you to override which (small set of) keywords are flipped to UPPER or lower case.  This means if a few keywords are missing they can easily be added to your .vimrc while waiting for an update to the plugin.  See the option g:sqlutil_non_line_break_keywords.

      NF: Added new options which allows you to turn off different formatting options.  This is really only useful for debugging, but if you run into an issue you can turn off that piece of formatting.  See help for more details, the new options are: 
             g:sqlutil_indent_nested_blocks
             g:sqlutil_wrap_long_lines
             g:sqlutil_wrap_function_calls
             g:sqlutil_wrap_width
             g:sqlutil_split_unbalanced_paran

      BF: Long lines were not wrapped appropriately (Don Parker).

      BF: Based on certain conditions, the formatter could get into an endless loop (Don Parker).

      BF: ColdFusion placeholders were incorrectly formatted (Jeremy Hustman).

      BF: Some keywords (i.e. LIKE, AS, ASC, DESC, ...) were not UPPER cased (Jeremy Hustman).

      BF: Delete statements were not formatted (Jeremy Hustman).


      A special thanks to Jeremy and Don for providing me real life test cases which allowed me to improve the formatter.


      Enjoy.

      David
      Feedback always welcome.

      --
      You received this message from the "vim_use" maillist.
      Do not top-post! Type your reply below the text you are replying to.
      For more information, visit http://www.vim.org/maillist.php
    Your message has been successfully submitted and would be delivered to recipients shortly.