This is a mirror of http://www.vim.org/scripts/script.php?script_id=492 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 - Author: Peter Bagyinszki and David Fishburn - http://www.vim.org/script.php?script_id=356
jphustman/SQLUtilities
SQL utilities - Formatting, generate - columns lists, procedures for databases
Vim Script