sponsor Vim development Vim logo Vim Book Ad

SQLUtilities : SQL utilities - Formatting, generate - columns lists, procedures for databases

 script karma  Rating 316/116, Downloaded by 15582  Comments, bugs, improvements  Vim wiki

created by
David Fishburn
 
script type
utility
 
description
Various SQL Utilities.

Version 2.0 requires Vim 7.

1. A SQL formatter, to make SQL statements (select, insert, update, delete...) more readable.  
2. Based on create table statements, will generate select lists for a table, as long as the definition exists in some open buffer.
3. Creates a generic procedure that will perform an insert, update, delete and select based on the definition of a table (if already open in some buffer).  The format is ANSI.
4.  Returns the column datatype definition for a specified column name (or supplied) based on the definition of a table (if already open in some buffer).

Functions:
[range]SQLUFormatter(..list..)
                                                                            
     Formats SQL statements into a easily readable form.
     Breaks keywords onto new lines.
     Forces column lists to be split over as many lines as
     necessary to fit the current textwidth of the buffer,
     so that lines do not wrap.
     If parentheses are unbalanced (ie a subselect) it will
     indent everything within the unbalanced paranthesis.
     Works for SELECT, INSERT, UPDATE, DELETE statements.

     Global variables to customization some aspects of the formatting:
     sqlutil_align_where - aligns the =, >=, <=, ...
     sqlutil_align_comma - places the column lists in select statement on new lines
     sqlutil_align_first_word - see examples

     You can change the case of the keywords while formatting.

                                                                            
Examples (these would look much better when using a fixed font):
                                                                            
  Original:
  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
  
                                                                            
  Formatted:
  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
  
  
                                                                            
  Original:
  UPDATE "SERVICE_REQUEST" SET "BUILDING_ID" = ?, "UNIT_ID" = ?,
  "REASON_ID" = ?, "PERSON_ID" = ?, "PRIORITY_ID" = ?, "STATUS_ID" = ?,
  "CREATED" = ?, "REQUESTED" = ?, "ARRIVED" = ?  WHERE "REQUEST_ID" = ?
                                                                            
                                                                            
  Formatted:
  UPDATE "SERVICE_REQUEST"
     SET "BUILDING_ID" = ?,
         "UNIT_ID" = ?,
         "REASON_ID" = ?,
         "PERSON_ID" = ?,
         "PRIORITY_ID" = ?,
         "STATUS_ID" = ?,
         "CREATED" = ?,
         "REQUESTED" = ?,
         "ARRIVED" = ?,
   WHERE "REQUEST_ID"  = ?
                                                                            
                                                                            
                                                                            
  Original:
  INSERT INTO "MESSAGES" ( "MSG_ID", "TO_PERSON_ID",
  "FROM_PERSON_ID", "REQUEST_ID", "CREATED", "PRIORITY_ID",
  "MSG_TYPE_ID", "STATUS_ID", "READ_WHEN", "TIMEOUT",
  "MSG_TXT", "RESEND_COUNT" ) VALUES ( ?, ?, ?,
  ?, ?, ?, ?, ?, ?, ?, ?, ? )
                                                                            
                                                                            
  Formatted:
  INSERT INTO "MESSAGES" ( "MSG_ID", "TO_PERSON_ID",
         "FROM_PERSON_ID", "REQUEST_ID", "CREATED",
         "PRIORITY_ID", "MSG_TYPE_ID", "STATUS_ID",
         "READ_WHEN", "TIMEOUT", "MSG_TXT", "RESEND_COUNT" )
  VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
                                                                            
                                                                            
Functions:
SQLUCreateColumnList( optional parameter )
                                                                            
     Assumes either the current file, or any other open buffer,
     has a CREATE TABLE statement in a format similar to this:
     CREATE TABLE customer (
     id INT DEFAULT AUTOINCREMENT,
     last_modified TIMESTAMP NULL,
     first_name     VARCHAR(30) NOT NULL,
     last_name VARCHAR(60) NOT NULL,
     balance         NUMERIC(10,2),
     PRIMARY KEY( id )
     );
     If you place the cursor on the word customer, then the
     unnamed buffer (also displayed by an echo statement) will
     contain:
     id, last_modified, first_name, last_name, balance
                                                                            
     Optionally, it will replace the word with the above and place
     the word in the unnamed buffer.  Calling the function with
     a parameter enables this feature.
                                                                            
     This also uses the g:sqlutil_cmd_terminator to determine when
     the create table statement ends if none of the following terms
     are found before the final );
            primary,reference,unique,check,foreign
     sqlutil_cmd defaults to ";"
                                                                            
                                                                            
Functions:
SQLUGetColumnDef( optional parameter )
SQLUGetColumnDataType( expand("<cword>"), 1 )
                                                                            
     Assumes either the current file, or any other open buffer,
     has a CREATE TABLE statement in a format similar to this:
     CREATE TABLE customer (
     id INT DEFAULT AUTOINCREMENT,
     last_modified TIMESTAMP NULL,
     first_name     VARCHAR(30) NOT NULL,
     last_name VARCHAR(60) NOT NULL,
     balance         NUMERIC(10,2),
     PRIMARY KEY( id )
     );
     If you place the cursor on the word first_name, then the
     column definition will be placed in the unnamed buffer (and also
     displayed by an echo statement).
     VARCHAR(30) NOT NULL        
                                                                            
     If the command is called as SQLUGetColumnDef( expand("<cword>"), 1 )
     or using the default mapping \scdt, just the datatype (instead
     of the column definition) will be returned.  A separate command
     SQLUGetColumnDataType has been created for this.
     VARCHAR(30)
                                                                            
                                                                            
Functions:
SQLUCreateProcedure()
                                                                            
     Assumes either the current file, or any other open buffer,
     has a CREATE TABLE statement in a format similar to this:
     CREATE TABLE customer (
     id         INT DEFAULT AUTOINCREMENT,
     last_modified   TIMESTAMP NULL,
     first_name     VARCHAR(30) NOT NULL,
     last_name VARCHAR(60) NOT NULL,
     balance         NUMERIC(10,2),
     PRIMARY KEY( id )
     );
     By calling SQLUCreateProcedure while on the name of a table
     the unnamed buffer will contain the create procedure statement
     for insert, update, delete and select statements.
     Once pasted into the buffer, unneeded functionality can be
     removed.
                                                                            
                                                                            
                                                                            
Commands:
[range]SQLUFormatter ..list..    
                     : Reformats the SQL statements over the specified
                       range.  Statement will lined up given the
                       existing indent of the first word.
SQLUCreateColumnList:  Creates a comma separated list of column names
                       for the table name under the cursor, assuming
                       the table definition exists in any open
                       buffer.  The column list is placed in the unnamed
                       buffer.
                       This also uses the g:sqlutil_cmd_terminator.
                       By default a table alias will be added to each of the columns, this is
                       configurable, see documentation (new 1.3.7).
                       This routine can optionally take 2 parameters
                       SQLUCreateColumnList T1
                           Creates a column list for T1
                       SQLUCreateColumnList T1 1
                           Creates a column list for T1 but only for
                           the primary keys for that table.
SQLUGetColumnDef     : Displays the column definition of the column name
                       under the cursor.  It assumes the CREATE TABLE
                       statement is in an open buffer.
SQLUGetColumnDataType
                     : Displays the column datatype of the column name
                       under the cursor.  It assumes the CREATE TABLE
                       statement is in an open buffer.
SQLUCreateProcedure  : Creates a stored procedure to perform standard
                       operations against the table that the cursor
                       is currently under.
                      
                                                                            

Suggested Mappings:
    vmap <silent>sf        <Plug>SQLU_Formatter<CR>
    nmap <silent>scl       <Plug>SQLU_CreateColumnList<CR>
    nmap <silent>scd       <Plug>SQLU_GetColumnDef<CR>
    nmap <silent>scdt      <Plug>SQLU_GetColumnDataType<CR>
    nmap <silent>scp       <Plug>SQLU_CreateProcedure<CR>
                                                                            
    mnemonic explanation
    s - sql
      f   - format
      cl  - column list
      cd  - column definition
      cdt - column datatype
      cp  - create procedure
                                                                            
    To prevent the default mappings from being created, place the
    following in your _vimrc:
        let g:sqlutil_load_default_maps = 0
                                                                            
Customization:
    By default this script assumes a command is terminated by a ;
    If you are using Microsoft SQL Server a command terminator
    would be "go", or perhaps "\ngo".
    To permenantly override the terminator in your _vimrc file you can add
          let g:sqlutil_cmd_terminator = "\ngo"
                                                                            
                                                                            
    When building a column list from a script file (ie CREATE TABLE
    statements), you can customize the script to detect when the
    column list finishes by creating the following in your _vimrc:
          let g:sqlutil_col_list_terminators =
                       \ 'primary,reference,unique,check,foreign'
                                                                            
    This can be necessary in the following example:
          CREATE TABLE customer (
             id         INT DEFAULT AUTOINCREMENT,
             first_name VARCHAR(30) NOT NULL,
             last_name  VARCHAR(60) NOT NULL,
             PRIMARY KEY( id )
          );

Dependencies:
       Align.vim - Version 15
                        - Author: Charles E. Campbell, Jr.
                        - http://www.vim.org/script.php?script_id=294

Suggested (Complementary) Plugins:
     dbext.vim - Provides database access to many DBMS (Oracle, Sybase, Microsoft, MySQL, DBI,..)
                    - Author: Peter Bagyinszki and David Fishburn
                    - http://www.vim.org/script.php?script_id=356
 
install details
Put <SQLUtilities.vim> into your .vim/plugin or vimfiles/plugin directory.
Put <SQLUtilities.vim> into your .vim/autoload or vimfiles/autoload directory.
Put <SQLUtilities.txt> into your .vim/doc or vimfiles/doc directory, run :helptags $VIM/vimfiles/doc.

 

rate this script Life Changing Helpful Unfulfilling 
script versions (upload new version)

Click on the package to download.

package script version date Vim version user release notes
sqlutil_700.zip 7.0 2012-12-04 7.0 David Fishburn 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).
sqlutil_600.zip 6.00 2012-10-10 7.0 David Fishburn NF: When formatting INSERT statements, the sqlutil_align_comma is also respected to place each column on a newline.

NF: When formatting UPDATE statements and sqlutil_align_comma is set comments at the end of the line were removed (Rodrigo Laporte).
sqlutil_500.zip 5.00 2012-02-28 7.0 David Fishburn NF: Now an extra check is made for the Align plugin.  If not found when attempting to format a SQL statement a friendly message is shown (Enrico Teotti).

NF: Updated documentation added the Global Options section plus updated the Customization section.

NF: Problem: Keywords within strings were being formatted (Olivier Laurent).
      Solution: Added an option, g:sqlutil_use_syntax_support, to determine whether or not to use Vim's syntax support to decide if the keyword is highlighted as a "string".  If so, leave it and move on to the next keyword.  Default value is 1 (enabled).  See |sqlutil-customization| for details on usage.

NF: Added an option, g:sqlutil_syntax_elements, to allow the user to decide which syntax elements should be considered a string.  Default value is 'Constant,sqlString'.
sqlutil_400.zip 4.00 2010-08-15 7.0 David Fishburn NF: Added a tutorial to take users through the common features of the plugin.

NF: Added an option, g:sqlutil_align_keyword_right, to left or right (default) align the keywords (Per Winkvist).

NF: Documented and added new global variables (g:sqlutil_menu_root, g:sqlutil_menu_priority) to customize the location of the SQLUtil menu.

BF: SQLite used slightly different syntax specifying UNIQUE values and would not correctly generate a column list from the CREATE TABLE statement (Oliver Peters).

BF: Ensured all normal commands used ! to ensure no mappings interfered with the commands (Benoit Mortgat).
sqlutil_300.zip 3.00 2009-01-15 7.0 David Fishburn NF: Added new menu links so that commonly used options can be quickly and easily toggled without having to remember the global variable names or values.  For example, if you cut the menu so that it floats beside your buffer you can easily hit "Toggle Align Where" or "Uppercase Keywords".  
NF: Using the menu_mode option you can control if and where the SQLUtil menu is created.  See the menu_mode option for more details.
NF: Added the default maps to the menus.  If you use a floating menu, or simply view the menu you can more easily memorize your most commonly used maps.
BF: UPPER and lower case of keywords did not work for multi-word keywords (ORDER BY, LEFT OUTER JOIN, ...).
sqlutil_200.zip 2.0.0 2007-09-09 7.0 David Fishburn NF: Added support for Vim 7's autoload functionality
sqlutil_141.zip 1.4.1 2005-10-07 6.0 David Fishburn NF: Added additional statements to g:sqlutil_col_list_terminators
BF: Forgot to increment g:loaded_sqlutilities
sqlutil_140.zip 1.4.0 2005-03-14 6.0 David Fishburn BF: If a statement has parenthesis inside of strings, the formatter was not ignoring them.  This could result in the formatter reporting there were mismatched parenthesis.
    Example: select 'string' + '(' from dummy
BF: If a keyword was not followed by a space, it was not placed on a newline.  
    Example:  SELECT * FROM(T1)
BF: Nested function calls could potentially lead to an endless loop.  
sqlutil_139.zip 1.3.9 2005-02-10 6.0 David Fishburn BF:  When g:sqlutil_align_comma=1 and a select list spanned more than 1 line (via functions calls and so on), it was possible the remaining comma separated list was not aligned properly.
sqlutil_138.zip 1.3.8 2004-12-03 6.0 David Fishburn NF: Enhanced the feature to change the case of keywords, to include the optional join words, left, right, full, inner, outer.  These words were missed prior to this change and only the JOIN word was capitalized.
CH: Changed the default for sqlutil_use_tbl_alias = 'a' (ask) from being on at all times.  This can be overridden in your vimrc.
sqlutil_137.zip 1.3.7 2004-07-21 6.0 David Fishburn NF: Added new option g:sqlutil_use_tbl_alias, which is on by default.
    When creating a column list for a table, it will add a table
    alias to each column.  The table alias is calculated based on
    rules.  This feature has 3 settings:
       n - no alias
       d - default calculated alias
       a - ask the user using default alias
NF: Updated g:sqlutil_col_list_terminators to handle some additional
    cases.
BF: In some cases -@- could be left in the query.  This seems to be
    a bug in Vim, where the marks could move.  Unsure why, but put
    a workaround in.
BF: When g:sqlutil_align_comma=1, and the lines did not begin with
    commas, the formatting was incorrect.
BF: When searching through buffers (SQLU_CreateColumnList), the
    alternate buffer could be changed.
sqlutil_136.zip 1.3.6 2004-06-21 6.0 David Fishburn NF: Added support for window functions (OVER, PARTITION BY, ROWS,     RANGE), and additional Oracle SELECT keywords (DIMENSION,     MEASURES, ITERATE, WITHIN GROUP, IGNORE, KEEP, RETURN, RULES)
sqlutil_135.zip 1.3.5 2004-03-07 6.0 David Fishburn New features added:
New global variables to customization some aspects of the formatting:
sqlutil_align_where - aligns the =, >=, <=, ...
sqlutil_align_comma - places the column lists in select statement on new lines
sqlutil_align_first_word - see examples

You can change the case of the keywords while formatting.

A help has been created to list all the various options and examples for each of them.

Thanks to Ronald Speelman, for suggestions and testing.
SQLUtilities.vim 1.3.3 2003-09-06 6.0 David Fishburn NF: Added global variable sqlutil_col_list_terminators for  customization.
NF: Changed all functions to be prefixed bySQLU_ for consistency.
BF: Fixed SQLU_GetColumnDataType andSQLU_GetColumnDef to handle tabs.
BF: -@- could be left after incorrect formatting.
SQLUtilities.vim 1.3 2003-06-06 6.0 David Fishburn NF: Support the formatting of FUNCTIONS or
    stored procedures used as derived tables.  This
    will nest the function calls on new lines and
    correctly split the paranthesis on new lines if
    the function call is longer than one line.  You
    would notice this mainly in the SELECT
    column list.
NF: Support the formatting of nested CASE
    statements.
NF: Added the GetColumnDataType command
NF: Improved primary key determination, it no
    longer requires the PRIMARY KEY statement to
    be part of the CREATE TABLE statement, it can
    be part of an ALTER TABLE statement.
NF: Improved formatting of SQL keywords.  
    INSERT INTO statement, the INTO will no longer
    be split onto a new line.
    Now correctly format the various JOIN keywords:
    NATURAL RIGHT OUTER JOIN will be placed one
    online instead of just the JOIN keyword as
    before.
BF: Did not properly handle the formatting of
    nested open paranthesis in all cases.
BF: Using new technique to determine how to change
    the textwidth to utilitize more screen space
    when wrapping long lines.
    nested open paranthesis in all cases.
NF: Create procedure uses shiftwidth for indent.
BF: Save/restore previous search.
SQLUtilities.vim 1.1 2003-02-04 6.0 David Fishburn Improved a number of items.

1.  If a PRIMARY KEY cannot be found for a given table the WHERE clause will contain all columns.

2.  Made the lookup for primary keys smarter.  Now the script will also look for ALTER TABLE ... ADD PRIMARY KEY statements instead of just the PRIMARY KEY clause as part of the CREATE TABLE statement.

3.  If hidden is set, the script will cycle through the buffers looking for the table.  Now, it will leave the cursor positioned in the same location when it leaves hidden buffers.

4.  You can now define g:sqlutil_cmd_terminator.  It currently defaults to ";".  This is used when looking gathering columns lists.  
SQLUtilities.vim 1 2002-11-23 6.0 David Fishburn Initial upload
ip used for rating: 44.206.248.122

If you have questions or remarks about this site, visit the vimonline development pages. Please use this site responsibly.
Questions about Vim should go to the maillist. Help Bram help Uganda.
   
Vim at Github