|
720
|
1 *sql.txt* For Vim version 7.0aa. Last change: Fri Jan 06 2006 8:09:25 AM
|
|
|
2
|
|
|
3 by David Fishburn
|
|
|
4
|
|
|
5 This is a filetype plugin to work with SQL files.
|
|
|
6
|
|
|
7 The Structured Query Language (SQL) is a standard which specifies statements
|
|
|
8 that allow a user to interact with a relational database. Vim includes
|
|
|
9 features for navigation, indentation and syntax highlighting.
|
|
|
10
|
|
|
11 1. Navigation |sql-navigation|
|
|
|
12 1.1 Matchit |sql-matchit|
|
|
|
13 1.2 Text Object Motions |sql-object-motions|
|
|
|
14 1.3 Predefined Object Motions |sql-predefined-objects|
|
|
|
15 1.4 Macros |sql-macros|
|
|
|
16 2. SQL Dialects |sql-dialects|
|
|
|
17 2.1 SQLSetType |SQLSetType|
|
|
|
18 2.2 SQL Dialect Default |sql-type-default|
|
|
|
19 3. Adding new SQL Dialects |sql-adding-dialects|
|
|
|
20
|
|
|
21 ==============================================================================
|
|
|
22 1. Navigation *sql-navigation*
|
|
|
23
|
|
|
24 The SQL ftplugin provides a number of options to assist with file
|
|
|
25 navigation.
|
|
|
26
|
|
|
27
|
|
|
28 1.1 Matchit *sql-matchit*
|
|
|
29 -----------
|
|
|
30 The matchit plugin (http://www.vim.org/scripts/script.php?script_id=39)
|
|
|
31 provides many additional features and can be customized for different
|
|
|
32 languages. The matchit plugin is configured by defining a local
|
|
|
33 buffer variable, b:match_words. Pressing the % key while on various
|
|
|
34 keywords will move the cursor to its match. For example, if the cursor
|
|
|
35 is on an "if", pressing % will cycle between the "else", "elseif" and
|
|
|
36 "end if" keywords.
|
|
|
37
|
|
|
38 The following keywords are supported: >
|
|
|
39 if
|
|
|
40 elseif | elsif
|
|
|
41 else [if]
|
|
|
42 end if
|
|
|
43
|
|
|
44 [while condition] loop
|
|
|
45 leave
|
|
|
46 break
|
|
|
47 continue
|
|
|
48 exit
|
|
|
49 end loop
|
|
|
50
|
|
|
51 for
|
|
|
52 leave
|
|
|
53 break
|
|
|
54 continue
|
|
|
55 exit
|
|
|
56 end loop
|
|
|
57
|
|
|
58 do
|
|
|
59 statements
|
|
|
60 doend
|
|
|
61
|
|
|
62 case
|
|
|
63 when
|
|
|
64 when
|
|
|
65 default
|
|
|
66 end case
|
|
|
67
|
|
|
68 merge
|
|
|
69 when not matched
|
|
|
70 when matched
|
|
|
71
|
|
|
72 create[ or replace] procedure|function|event
|
|
|
73 returns
|
|
|
74 <
|
|
|
75
|
|
|
76 1.2 Text Object Motions *sql-object-motions*
|
|
|
77 -----------------------
|
|
|
78 Vim has a number of predefined keys for working with text |object-motions|.
|
|
|
79 This filetype plugin attempts to translate these keys to maps which make sense
|
|
|
80 for the SQL language.
|
|
|
81
|
|
|
82 The following |Normal| mode and |Visual| mode maps exist (when you edit a SQL
|
|
|
83 file): >
|
|
|
84 ]] move forward to the next 'begin'
|
|
|
85 [[ move backwards to the previous 'begin'
|
|
|
86 ][ move forward to the next 'end'
|
|
|
87 [] move backwards to the previous 'end'
|
|
|
88 <
|
|
|
89
|
|
|
90 1.3 Predefined Object Motions *sql-predefined-objects*
|
|
|
91 -----------------------------
|
|
|
92 Most relational databases support various standard features, tables, indicies,
|
|
|
93 triggers and stored procedures. Each vendor also has a variety of proprietary
|
|
|
94 objects. The next set of maps have been created to help move between these
|
|
|
95 objects. Depends on which database vendor you are using, the list of objects
|
|
|
96 must be configurable. The filetype plugin attempts to define many of the
|
|
|
97 standard objects, plus many additional ones. In order to make this as
|
|
|
98 flexible as possible, you can override the list of objects from within your
|
|
|
99 |vimrc| with the following: >
|
|
|
100 let g:ftplugin_sql_objects = 'function,procedure,event,table,trigger' .
|
|
|
101 \ ',schema,service,publication,database,datatype,domain' .
|
|
|
102 \ ',index,subscription,synchronization,view,variable'
|
|
|
103 <
|
|
|
104 The following |Normal| mode and |Visual| mode maps have been created which use
|
|
|
105 the above list: >
|
|
|
106 ]} move forward to the next 'create <object name>'
|
|
|
107 [{ move backward to the previous 'create <object name>'
|
|
|
108
|
|
|
109 Repeatedly pressing ]} will cycle through each of these create statements: >
|
|
|
110 create table t1 (
|
|
|
111 ...
|
|
|
112 );
|
|
|
113
|
|
|
114 create procedure p1
|
|
|
115 begin
|
|
|
116 ...
|
|
|
117 end;
|
|
|
118
|
|
|
119 create index i1 on t1 (c1);
|
|
|
120 <
|
|
|
121 The default setting for g:ftplugin_sql_objects is: >
|
|
|
122 let g:ftplugin_sql_objects = 'function,procedure,event,' .
|
|
|
123 \ '\\(existing\\\\|global\\s\\+temporary\\s\\+\\)\\\{,1}' .
|
|
|
124 \ 'table,trigger' .
|
|
|
125 \ ',schema,service,publication,database,datatype,domain' .
|
|
|
126 \ ',index,subscription,synchronization,view,variable'
|
|
|
127 <
|
|
|
128 The above will also handle these cases: >
|
|
|
129 create table t1 (
|
|
|
130 ...
|
|
|
131 );
|
|
|
132 create existing table t2 (
|
|
|
133 ...
|
|
|
134 );
|
|
|
135 create global temporary table t3 (
|
|
|
136 ...
|
|
|
137 );
|
|
|
138 <
|
|
|
139 By default, the ftplugin only searches for CREATE statements. You can also
|
|
|
140 override this via your |vimrc| with the following: >
|
|
|
141 let g:ftplugin_sql_statements = 'create,alter'
|
|
|
142
|
|
|
143 The filetype plugin defines three types of comments: >
|
|
|
144 1. --
|
|
|
145 2. //
|
|
|
146 3. /*
|
|
|
147 *
|
|
|
148 */
|
|
|
149 <
|
|
|
150 The following |Normal| mode and |Visual| mode maps have been created to work
|
|
|
151 with comments: >
|
|
|
152 ]" move forward to the beginning of a comment
|
|
|
153 [" move forward to the end of a comment
|
|
|
154
|
|
|
155
|
|
|
156
|
|
|
157 1.4 Macros *sql-macros*
|
|
|
158 ----------
|
|
|
159 Vim's feature to find macro definitions, |'define'|, is supported using this
|
|
|
160 regular expression: >
|
|
|
161 \c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>
|
|
|
162 <
|
|
|
163 This addresses the following code: >
|
|
|
164 CREATE VARIABLE myVar1 INTEGER;
|
|
|
165
|
|
|
166 CREATE PROCEDURE sp_test(
|
|
|
167 IN myVar2 INTEGER,
|
|
|
168 OUT myVar3 CHAR(30),
|
|
|
169 INOUT myVar4 NUMERIC(20,0)
|
|
|
170 )
|
|
|
171 BEGIN
|
|
|
172 DECLARE myVar5 INTEGER;
|
|
|
173
|
|
|
174 SELECT c1, c2, c3
|
|
|
175 INTO myVar2, myVar3, myVar4
|
|
|
176 FROM T1
|
|
|
177 WHERE c4 = myVar1;
|
|
|
178 END;
|
|
|
179 <
|
|
|
180 Place your cursor on "myVar1" on this line: >
|
|
|
181 WHERE c4 = myVar1;
|
|
|
182 ^
|
|
|
183 <
|
|
|
184 Press any of the following keys: >
|
|
|
185 [d
|
|
|
186 [D
|
|
|
187 [CTRL-D
|
|
|
188
|
|
|
189
|
|
|
190 ==============================================================================
|
|
|
191 2. SQL Dialects *sql-dialects* *sql-types*
|
|
|
192 *sybase* *TSQL* *Transact-SQL*
|
|
|
193 *sqlanywhere*
|
|
|
194 *oracle* *plsql* *sqlj*
|
|
|
195 *sqlserver*
|
|
|
196 *mysql* *postgress* *psql*
|
|
|
197 *informix*
|
|
|
198
|
|
|
199 All relational databases support SQL. There is a portion of SQL that is
|
|
|
200 portable across vendors (ex. CREATE TABLE, CREATE INDEX), but there is a
|
|
|
201 great deal of vendor specific extensions to SQL. Oracle supports the
|
|
|
202 "CREATE OR REPLACE" syntax, column defaults specified in the CREATE TABLE
|
|
|
203 statement and the procedural language (for stored procedures and triggers).
|
|
|
204
|
|
|
205 The default Vim distribution ships with syntax highlighting based on Oracle's
|
|
|
206 PL/SQL. The default SQL indent script works for Oracle and SQL Anywhere.
|
|
|
207 The default filetype plugin works for all vendors and should remain vendor
|
|
|
208 neutral, but extendable.
|
|
|
209
|
|
|
210 Vim currently has support for a variety of different vendors, currently this
|
|
|
211 is via syntax scripts. Unfortunately, to flip between different syntax rules
|
|
|
212 you must either create:
|
|
|
213 1. New filetypes
|
|
|
214 2. Custom autocmds
|
|
|
215 3. Manual steps / commands
|
|
|
216
|
|
|
217 The majority of people work with only one vendor's database product, it would
|
|
|
218 be nice to specify a default in your |vimrc|.
|
|
|
219
|
|
|
220
|
|
|
221 2.1 SQLSetType *sqlsettype* *SQLSetType*
|
|
|
222 --------------
|
|
|
223 For the people that work with many different databases, it would be nice to be
|
|
|
224 able to flip between the various vendors rules (indent, syntax) on a per
|
|
|
225 buffer basis, at any time. The ftplugin/sql.vim file defines this function: >
|
|
|
226 SQLSetType
|
|
|
227 <
|
|
|
228 Executing this function without any parameters will set the indent and syntax
|
|
|
229 scripts back to their defaults, see |sql-type-default|. If you have turned
|
|
|
230 off Vi's compatibility mode, |'compatible'|, you can use the <Tab> key to
|
|
|
231 complete the optional parameter.
|
|
|
232
|
|
|
233 After typing the function name and a space, you can use the completion to
|
|
|
234 supply a parameter. The function takes the name of the Vim script you want to
|
|
|
235 source. Using the |cmdline-completion| feature, the SQLSetType function will
|
|
|
236 search the |'runtimepath'| for all Vim scripts with a name containing 'sql'.
|
|
|
237 This takes the guess work out of the spelling of the names. The following are
|
|
|
238 examples: >
|
|
|
239 :SQLSetType
|
|
|
240 :SQLSetType sqloracle
|
|
|
241 :SQLSetType sqlanywhere
|
|
|
242 :SQLSetType sqlinformix
|
|
|
243 :SQLSetType mysql
|
|
|
244 <
|
|
|
245 The easiest approach is to the use <Tab> character which will first complete
|
|
|
246 the command name (SQLSetType), after a space and another <Tab>, display a list
|
|
|
247 of available Vim script names: >
|
|
|
248 :SQL<Tab><space><Tab>
|
|
|
249 <
|
|
|
250
|
|
|
251 2.2 SQL Dialect Default *sql-type-default*
|
|
|
252 -----------------------
|
|
|
253 As mentioned earlier, the default syntax rules for Vim is based on Oracle
|
|
|
254 (PL/SQL). You can override this default by placing one of the following in
|
|
|
255 your |vimrc|: >
|
|
|
256 let g:sql_type_default = 'sqlanywhere'
|
|
|
257 let g:sql_type_default = 'sqlinformix'
|
|
|
258 let g:sql_type_default = 'mysql'
|
|
|
259 <
|
|
|
260 If you added the following to your |vimrc|: >
|
|
|
261 let g:sql_type_default = 'sqlinformix'
|
|
|
262 <
|
|
|
263 The next time edit a SQL file the following scripts will be automatically
|
|
|
264 loaded by Vim: >
|
|
|
265 ftplugin/sql.vim
|
|
|
266 syntax/sqlinformix.vim
|
|
|
267 indent/sql.vim
|
|
|
268 >
|
|
|
269 Notice indent/sqlinformix.sql was not loaded. There is no indent file
|
|
|
270 for Informix, Vim loads the default files if the specified files does not
|
|
|
271 exist.
|
|
|
272
|
|
|
273
|
|
|
274 ==============================================================================
|
|
|
275 3. Adding new SQL Dialects *sql-adding-dialects*
|
|
|
276
|
|
|
277 If you begin working with a SQL dialect which does not have any customizations
|
|
|
278 available with the default Vim distribution you can check http://www.vim.org
|
|
|
279 to see if any customization currently exist. If not, you can begin by cloning
|
|
|
280 an existing script. Read |filetype-plugins| for more details.
|
|
|
281
|
|
|
282 To help identify these scripts, try to create the files with a "sql" prefix.
|
|
|
283 If you decide you wish to create customizations for the SQLite database, you
|
|
|
284 can create any of the following: >
|
|
|
285 Unix
|
|
|
286 ~/.vim/syntax/sqlite.vim
|
|
|
287 ~/.vim/indent/sqlite.vim
|
|
|
288 Windows
|
|
|
289 $VIM/vimfiles/syntax/sqlite.vim
|
|
|
290 $VIM/vimfiles/indent/sqlite.vim
|
|
|
291 <
|
|
|
292 No changes are necessary to the SQLSetType function. It will automatically
|
|
|
293 pickup the new SQL files and load them when you issue the SQLSetType command.
|
|
|
294
|
|
|
295
|
|
|
296
|
|
|
297
|
|
|
298 vim:tw=78:ts=8:ft=help:norl:ff=unix:
|
