Ⅰ. 插件描述
SQL utilities - Formatting, generate - columns lists, procedures for databasesⅡ. 基本信息
|
Ⅲ. 安装方法
使用Vundle管理器安装
在你的.vimrc下添加:Plugin 'jphustman/sqlutilities-all-too-well':source %
:PluginInstall对于Vundle版本 < 0.10.2，请用上面的Bundle替换Plugin。
使用NeoBundle管理器安装
在你的.vimrc下添加:NeoBundle 'jphustman/sqlutilities-all-too-well':source %
:NeoBundleInstall使用VimPlug管理器安装
在你的.vimrc下添加:Plug 'jphustman/sqlutilities-all-too-well':source %
:PlugInstall使用Pathogen管理器安装
在终端中运行以下命令:cd ~/.vim/bundle
git clone https://github.com/jphustman/sqlutilitiesⅣ. 文档说明
This is a mirror of http://www.vim.org/scripts/script.php?script_id=492
Various SQL Utilities.
Version 2.0 requires Vim 7.
- A SQL formatter, to make SQL statements (select, insert, update, delete...) more readable.
- Based on create table statements, will generate select lists for a table, as long as the definition exists in some open buffer.
- 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.
- 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 PWHERE 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