SQLUtilities : SQL utilities - Formatting, generate - columns lists, procedures for databases
| script karma |
Rating 316/116,
Downloaded by 16767 |
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.
|
| |
script versions (upload new version)
Click on the package to download.
| 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: 18.224.96.245
|
