|
819
|
1 *sql.txt* For Vim version 7.0c. Last change: Mon Apr 03 2006 10:34:00 PM
|
|
720
|
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|
|
|
818
|
20 4. OMNI SQL Completion |sql-completion|
|
|
|
21 4.1 Static mode |sql-completion-static|
|
|
|
22 4.2 Dynamic mode |sql-completion-dynamic|
|
|
|
23 4.3 Tutorial |sql-completion-tutorial|
|
|
|
24 4.3.1 Complete Tables |sql-completion-tables|
|
|
|
25 4.3.2 Complete Columns |sql-completion-columns|
|
|
|
26 4.3.3 Complete Procedures |sql-completion-procedures|
|
|
|
27 4.3.4 Complete Views |sql-completion-views|
|
|
|
28 4.4 Completion Customization |sql-completion-customization|
|
|
819
|
29 4.5 SQL Maps |sql-completion-maps|
|
|
|
30 4.6 Using with other filetypes |sql-completion-filetypes|
|
|
720
|
31
|
|
|
32 ==============================================================================
|
|
|
33 1. Navigation *sql-navigation*
|
|
|
34
|
|
|
35 The SQL ftplugin provides a number of options to assist with file
|
|
|
36 navigation.
|
|
|
37
|
|
|
38
|
|
|
39 1.1 Matchit *sql-matchit*
|
|
|
40 -----------
|
|
|
41 The matchit plugin (http://www.vim.org/scripts/script.php?script_id=39)
|
|
|
42 provides many additional features and can be customized for different
|
|
|
43 languages. The matchit plugin is configured by defining a local
|
|
|
44 buffer variable, b:match_words. Pressing the % key while on various
|
|
|
45 keywords will move the cursor to its match. For example, if the cursor
|
|
|
46 is on an "if", pressing % will cycle between the "else", "elseif" and
|
|
|
47 "end if" keywords.
|
|
|
48
|
|
|
49 The following keywords are supported: >
|
|
|
50 if
|
|
|
51 elseif | elsif
|
|
|
52 else [if]
|
|
|
53 end if
|
|
|
54
|
|
|
55 [while condition] loop
|
|
|
56 leave
|
|
|
57 break
|
|
|
58 continue
|
|
|
59 exit
|
|
|
60 end loop
|
|
|
61
|
|
|
62 for
|
|
|
63 leave
|
|
|
64 break
|
|
|
65 continue
|
|
|
66 exit
|
|
|
67 end loop
|
|
|
68
|
|
|
69 do
|
|
|
70 statements
|
|
|
71 doend
|
|
|
72
|
|
|
73 case
|
|
|
74 when
|
|
|
75 when
|
|
|
76 default
|
|
|
77 end case
|
|
|
78
|
|
|
79 merge
|
|
|
80 when not matched
|
|
|
81 when matched
|
|
|
82
|
|
|
83 create[ or replace] procedure|function|event
|
|
|
84 returns
|
|
|
85 <
|
|
|
86
|
|
|
87 1.2 Text Object Motions *sql-object-motions*
|
|
|
88 -----------------------
|
|
|
89 Vim has a number of predefined keys for working with text |object-motions|.
|
|
|
90 This filetype plugin attempts to translate these keys to maps which make sense
|
|
|
91 for the SQL language.
|
|
|
92
|
|
|
93 The following |Normal| mode and |Visual| mode maps exist (when you edit a SQL
|
|
|
94 file): >
|
|
|
95 ]] move forward to the next 'begin'
|
|
|
96 [[ move backwards to the previous 'begin'
|
|
|
97 ][ move forward to the next 'end'
|
|
|
98 [] move backwards to the previous 'end'
|
|
|
99 <
|
|
|
100
|
|
|
101 1.3 Predefined Object Motions *sql-predefined-objects*
|
|
|
102 -----------------------------
|
|
818
|
103 Most relational databases support various standard features, tables, indices,
|
|
720
|
104 triggers and stored procedures. Each vendor also has a variety of proprietary
|
|
|
105 objects. The next set of maps have been created to help move between these
|
|
|
106 objects. Depends on which database vendor you are using, the list of objects
|
|
|
107 must be configurable. The filetype plugin attempts to define many of the
|
|
|
108 standard objects, plus many additional ones. In order to make this as
|
|
|
109 flexible as possible, you can override the list of objects from within your
|
|
|
110 |vimrc| with the following: >
|
|
|
111 let g:ftplugin_sql_objects = 'function,procedure,event,table,trigger' .
|
|
|
112 \ ',schema,service,publication,database,datatype,domain' .
|
|
|
113 \ ',index,subscription,synchronization,view,variable'
|
|
|
114 <
|
|
|
115 The following |Normal| mode and |Visual| mode maps have been created which use
|
|
|
116 the above list: >
|
|
|
117 ]} move forward to the next 'create <object name>'
|
|
|
118 [{ move backward to the previous 'create <object name>'
|
|
|
119
|
|
|
120 Repeatedly pressing ]} will cycle through each of these create statements: >
|
|
|
121 create table t1 (
|
|
|
122 ...
|
|
|
123 );
|
|
|
124
|
|
|
125 create procedure p1
|
|
|
126 begin
|
|
|
127 ...
|
|
|
128 end;
|
|
|
129
|
|
|
130 create index i1 on t1 (c1);
|
|
|
131 <
|
|
|
132 The default setting for g:ftplugin_sql_objects is: >
|
|
|
133 let g:ftplugin_sql_objects = 'function,procedure,event,' .
|
|
|
134 \ '\\(existing\\\\|global\\s\\+temporary\\s\\+\\)\\\{,1}' .
|
|
|
135 \ 'table,trigger' .
|
|
|
136 \ ',schema,service,publication,database,datatype,domain' .
|
|
|
137 \ ',index,subscription,synchronization,view,variable'
|
|
|
138 <
|
|
|
139 The above will also handle these cases: >
|
|
|
140 create table t1 (
|
|
|
141 ...
|
|
|
142 );
|
|
|
143 create existing table t2 (
|
|
|
144 ...
|
|
|
145 );
|
|
|
146 create global temporary table t3 (
|
|
|
147 ...
|
|
|
148 );
|
|
|
149 <
|
|
|
150 By default, the ftplugin only searches for CREATE statements. You can also
|
|
|
151 override this via your |vimrc| with the following: >
|
|
|
152 let g:ftplugin_sql_statements = 'create,alter'
|
|
|
153
|
|
|
154 The filetype plugin defines three types of comments: >
|
|
|
155 1. --
|
|
|
156 2. //
|
|
|
157 3. /*
|
|
|
158 *
|
|
|
159 */
|
|
|
160 <
|
|
|
161 The following |Normal| mode and |Visual| mode maps have been created to work
|
|
|
162 with comments: >
|
|
|
163 ]" move forward to the beginning of a comment
|
|
|
164 [" move forward to the end of a comment
|
|
|
165
|
|
|
166
|
|
|
167
|
|
|
168 1.4 Macros *sql-macros*
|
|
|
169 ----------
|
|
|
170 Vim's feature to find macro definitions, |'define'|, is supported using this
|
|
|
171 regular expression: >
|
|
|
172 \c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>
|
|
|
173 <
|
|
|
174 This addresses the following code: >
|
|
|
175 CREATE VARIABLE myVar1 INTEGER;
|
|
|
176
|
|
|
177 CREATE PROCEDURE sp_test(
|
|
|
178 IN myVar2 INTEGER,
|
|
|
179 OUT myVar3 CHAR(30),
|
|
|
180 INOUT myVar4 NUMERIC(20,0)
|
|
|
181 )
|
|
|
182 BEGIN
|
|
|
183 DECLARE myVar5 INTEGER;
|
|
|
184
|
|
|
185 SELECT c1, c2, c3
|
|
|
186 INTO myVar2, myVar3, myVar4
|
|
|
187 FROM T1
|
|
|
188 WHERE c4 = myVar1;
|
|
|
189 END;
|
|
|
190 <
|
|
|
191 Place your cursor on "myVar1" on this line: >
|
|
|
192 WHERE c4 = myVar1;
|
|
|
193 ^
|
|
|
194 <
|
|
|
195 Press any of the following keys: >
|
|
|
196 [d
|
|
|
197 [D
|
|
|
198 [CTRL-D
|
|
|
199
|
|
|
200
|
|
|
201 ==============================================================================
|
|
|
202 2. SQL Dialects *sql-dialects* *sql-types*
|
|
|
203 *sybase* *TSQL* *Transact-SQL*
|
|
|
204 *sqlanywhere*
|
|
|
205 *oracle* *plsql* *sqlj*
|
|
|
206 *sqlserver*
|
|
|
207 *mysql* *postgress* *psql*
|
|
|
208 *informix*
|
|
|
209
|
|
|
210 All relational databases support SQL. There is a portion of SQL that is
|
|
|
211 portable across vendors (ex. CREATE TABLE, CREATE INDEX), but there is a
|
|
|
212 great deal of vendor specific extensions to SQL. Oracle supports the
|
|
|
213 "CREATE OR REPLACE" syntax, column defaults specified in the CREATE TABLE
|
|
|
214 statement and the procedural language (for stored procedures and triggers).
|
|
|
215
|
|
|
216 The default Vim distribution ships with syntax highlighting based on Oracle's
|
|
|
217 PL/SQL. The default SQL indent script works for Oracle and SQL Anywhere.
|
|
|
218 The default filetype plugin works for all vendors and should remain vendor
|
|
|
219 neutral, but extendable.
|
|
|
220
|
|
|
221 Vim currently has support for a variety of different vendors, currently this
|
|
|
222 is via syntax scripts. Unfortunately, to flip between different syntax rules
|
|
|
223 you must either create:
|
|
|
224 1. New filetypes
|
|
|
225 2. Custom autocmds
|
|
|
226 3. Manual steps / commands
|
|
|
227
|
|
|
228 The majority of people work with only one vendor's database product, it would
|
|
|
229 be nice to specify a default in your |vimrc|.
|
|
|
230
|
|
|
231
|
|
|
232 2.1 SQLSetType *sqlsettype* *SQLSetType*
|
|
|
233 --------------
|
|
|
234 For the people that work with many different databases, it would be nice to be
|
|
|
235 able to flip between the various vendors rules (indent, syntax) on a per
|
|
|
236 buffer basis, at any time. The ftplugin/sql.vim file defines this function: >
|
|
|
237 SQLSetType
|
|
|
238 <
|
|
|
239 Executing this function without any parameters will set the indent and syntax
|
|
|
240 scripts back to their defaults, see |sql-type-default|. If you have turned
|
|
|
241 off Vi's compatibility mode, |'compatible'|, you can use the <Tab> key to
|
|
|
242 complete the optional parameter.
|
|
|
243
|
|
|
244 After typing the function name and a space, you can use the completion to
|
|
|
245 supply a parameter. The function takes the name of the Vim script you want to
|
|
|
246 source. Using the |cmdline-completion| feature, the SQLSetType function will
|
|
|
247 search the |'runtimepath'| for all Vim scripts with a name containing 'sql'.
|
|
|
248 This takes the guess work out of the spelling of the names. The following are
|
|
|
249 examples: >
|
|
|
250 :SQLSetType
|
|
|
251 :SQLSetType sqloracle
|
|
|
252 :SQLSetType sqlanywhere
|
|
|
253 :SQLSetType sqlinformix
|
|
|
254 :SQLSetType mysql
|
|
|
255 <
|
|
|
256 The easiest approach is to the use <Tab> character which will first complete
|
|
|
257 the command name (SQLSetType), after a space and another <Tab>, display a list
|
|
|
258 of available Vim script names: >
|
|
|
259 :SQL<Tab><space><Tab>
|
|
|
260 <
|
|
|
261
|
|
|
262 2.2 SQL Dialect Default *sql-type-default*
|
|
|
263 -----------------------
|
|
|
264 As mentioned earlier, the default syntax rules for Vim is based on Oracle
|
|
|
265 (PL/SQL). You can override this default by placing one of the following in
|
|
|
266 your |vimrc|: >
|
|
|
267 let g:sql_type_default = 'sqlanywhere'
|
|
|
268 let g:sql_type_default = 'sqlinformix'
|
|
|
269 let g:sql_type_default = 'mysql'
|
|
|
270 <
|
|
|
271 If you added the following to your |vimrc|: >
|
|
|
272 let g:sql_type_default = 'sqlinformix'
|
|
|
273 <
|
|
|
274 The next time edit a SQL file the following scripts will be automatically
|
|
|
275 loaded by Vim: >
|
|
|
276 ftplugin/sql.vim
|
|
|
277 syntax/sqlinformix.vim
|
|
|
278 indent/sql.vim
|
|
|
279 >
|
|
|
280 Notice indent/sqlinformix.sql was not loaded. There is no indent file
|
|
|
281 for Informix, Vim loads the default files if the specified files does not
|
|
|
282 exist.
|
|
|
283
|
|
|
284
|
|
|
285 ==============================================================================
|
|
|
286 3. Adding new SQL Dialects *sql-adding-dialects*
|
|
|
287
|
|
|
288 If you begin working with a SQL dialect which does not have any customizations
|
|
|
289 available with the default Vim distribution you can check http://www.vim.org
|
|
|
290 to see if any customization currently exist. If not, you can begin by cloning
|
|
|
291 an existing script. Read |filetype-plugins| for more details.
|
|
|
292
|
|
|
293 To help identify these scripts, try to create the files with a "sql" prefix.
|
|
|
294 If you decide you wish to create customizations for the SQLite database, you
|
|
|
295 can create any of the following: >
|
|
|
296 Unix
|
|
|
297 ~/.vim/syntax/sqlite.vim
|
|
|
298 ~/.vim/indent/sqlite.vim
|
|
|
299 Windows
|
|
|
300 $VIM/vimfiles/syntax/sqlite.vim
|
|
|
301 $VIM/vimfiles/indent/sqlite.vim
|
|
|
302 <
|
|
|
303 No changes are necessary to the SQLSetType function. It will automatically
|
|
|
304 pickup the new SQL files and load them when you issue the SQLSetType command.
|
|
|
305
|
|
|
306
|
|
818
|
307 ==============================================================================
|
|
|
308 4. OMNI SQL Completion *sql-completion*
|
|
|
309 *omni-sql-completion*
|
|
720
|
310
|
|
818
|
311 Vim 7 includes a code completion interface and functions which allows plugin
|
|
|
312 developers to build in code completion for any language. Vim 7 includes
|
|
|
313 code completion for the SQL language.
|
|
|
314
|
|
|
315 There are two modes to the SQL completion plugin, static and dynamic. The
|
|
|
316 static mode populates the popups with the data generated from current syntax
|
|
|
317 highlight rules. The dynamic mode populates the popups with data retrieved
|
|
|
318 directly from a database. This includes, table lists, column lists,
|
|
|
319 procedures names and more.
|
|
|
320
|
|
|
321 4.1 Static Mode *sql-completion-static*
|
|
|
322 ---------------
|
|
|
323 The static popups created contain items defined by the active syntax rules
|
|
|
324 while editing a file with a filetype of SQL. The plugin defines (by default)
|
|
819
|
325 various maps to help the user refine the list of items to be displayed.
|
|
818
|
326 The defaults static maps are: >
|
|
819
|
327 imap <buffer> <C-C>a <C-\><C-O>:call sqlcomplete#Map('syntax')<CR><C-X><C-O>
|
|
|
328 imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O>
|
|
|
329 imap <buffer> <C-C>f <C-\><C-O>:call sqlcomplete#Map('sqlFunction')<CR><C-X><C-O>
|
|
|
330 imap <buffer> <C-C>o <C-\><C-O>:call sqlcomplete#Map('sqlOption')<CR><C-X><C-O>
|
|
|
331 imap <buffer> <C-C>T <C-\><C-O>:call sqlcomplete#Map('sqlType')<CR><C-X><C-O>
|
|
|
332 imap <buffer> <C-C>s <C-\><C-O>:call sqlcomplete#Map('sqlStatement')<CR><C-X><C-O>
|
|
818
|
333 <
|
|
|
334 The static maps (which are based on the syntax highlight groups) follow this
|
|
|
335 format: >
|
|
819
|
336 imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O>
|
|
818
|
337 <
|
|
|
338 This command breaks down as: >
|
|
|
339 imap - Create an insert map
|
|
|
340 <buffer> - Only for this buffer
|
|
819
|
341 <C-C>k - Your choice of key map
|
|
818
|
342 <C-\><C-O> - Execute one command, return to Insert mode
|
|
819
|
343 :call sqlcomplete#Map( - Allows the SQL completion plugin to perform some
|
|
|
344 housekeeping functions to allow it to be used in
|
|
|
345 conjunction with other completion plugins.
|
|
|
346 Indicate which item you want the SQL completion
|
|
|
347 plugin to complete.
|
|
|
348 In this case we are asking the plugin to display
|
|
|
349 items from the syntax highlight group
|
|
|
350 'sqlKeyword'.
|
|
818
|
351 You can view a list of highlight group names to
|
|
|
352 choose from by executing the
|
|
|
353 :syntax list
|
|
|
354 command while editing a SQL file.
|
|
|
355 'sqlKeyword' - Display the items for the sqlKeyword highlight
|
|
|
356 group
|
|
819
|
357 )<CR> - Execute the :let command
|
|
818
|
358 <C-X><C-O> - Trigger the standard omni completion key stroke.
|
|
819
|
359 Passing in 'sqlKeyword' instructs the SQL
|
|
|
360 completion plugin to populate the popup with
|
|
|
361 items from the sqlKeyword highlight group. The
|
|
|
362 plugin will also cache this result until Vim is
|
|
|
363 restarted. The syntax list is retrieved using
|
|
|
364 the syntaxcomplete plugin.
|
|
818
|
365 <
|
|
819
|
366 Using the 'syntax' keyword is a special case. This instructs the
|
|
818
|
367 syntaxcomplete plugin to retrieve all syntax items. So this will effectively
|
|
|
368 work for any of Vim's SQL syntax files. At the time of writing this includes
|
|
|
369 10 different syntax files for the different dialects of SQL (see section 3
|
|
|
370 above, |sql-dialects|).
|
|
|
371
|
|
|
372 Here are some examples of the entries which are pulled from the syntax files: >
|
|
|
373 All
|
|
|
374 - Contains the contents of all syntax highlight groups
|
|
|
375 Statements
|
|
|
376 - Select, Insert, Update, Delete, Create, Alter, ...
|
|
|
377 Functions
|
|
|
378 - Min, Max, Trim, Round, Date, ...
|
|
|
379 Keywords
|
|
|
380 - Index, Database, Having, Group, With
|
|
|
381 Options
|
|
|
382 - Isolation_level, On_error, Qualify_owners, Fire_triggers, ...
|
|
|
383 Types
|
|
|
384 - Integer, Char, Varchar, Date, DateTime, Timestamp, ...
|
|
|
385 <
|
|
|
386
|
|
|
387 4.2 Dynamic Mode *sql-completion-dynamic*
|
|
|
388 ----------------
|
|
|
389 Dynamic mode populates the popups with data directly from a database. In
|
|
|
390 order for the dynamic feature to be enabled you must have the dbext.vim
|
|
|
391 plugin installed, (http://vim.sourceforge.net/script.php?script_id=356).
|
|
|
392
|
|
|
393 Dynamic mode is used by several features of the SQL completion plugin.
|
|
819
|
394 After installing the dbext plugin see the dbext-tutorial for additional
|
|
818
|
395 configuration and usage. The dbext plugin allows the SQL completion plugin
|
|
|
396 to display a list of tables, procedures, views and columns. >
|
|
|
397 Table List
|
|
|
398 - All tables for all schema owners
|
|
|
399 Procedure List
|
|
|
400 - All stored procedures for all schema owners
|
|
|
401 View List
|
|
|
402 - All stored procedures for all schema owners
|
|
|
403 Column List
|
|
|
404 - For the selected table, the columns that are part of the table
|
|
|
405 <
|
|
|
406 To enable the popup, while in INSERT mode, use the following key combinations
|
|
|
407 for each group (where <C-C> means hold the CTRL key down while pressing
|
|
|
408 the space bar):
|
|
|
409 Table List - <C-C>t
|
|
|
410 - <C-X><C-O> (the default map assumes tables)
|
|
|
411 Stored Procedure List - <C-C>p
|
|
|
412 View List - <C-C>v
|
|
|
413 Column List - <C-C>c
|
|
819
|
414 - Windows platform only
|
|
818
|
415 - When viewing a popup window displaying the list
|
|
|
416 of tables, you can press <C-Right>, this will
|
|
|
417 replace the table currently highlighted with
|
|
|
418 the column list for that table.
|
|
|
419 - When viewing a popup window displaying the list
|
|
|
420 of columns, you can press <C-Left>, this will
|
|
|
421 replace the column list with the list of tables.
|
|
|
422
|
|
|
423 The SQL completion plugin caches various lists that are displayed in
|
|
|
424 the popup window. This makes the re-displaying of these lists very
|
|
|
425 fast. If new tables or columns are added to the database it may become
|
|
|
426 necessary to clear the plugins cache. The default map for this is: >
|
|
819
|
427 imap <buffer> <C-C>R <C-\><C-O>:call sqlcomplete#Map('ResetCache')<CR><C-X><C-O>
|
|
818
|
428 <
|
|
|
429
|
|
|
430 4.3 SQL Tutorial *sql-completion-tutorial*
|
|
|
431 ----------------
|
|
|
432
|
|
|
433 This tutorial is designed to take you through the common features of the SQL
|
|
|
434 completion plugin so that: >
|
|
|
435 a) You gain familiarity with the plugin
|
|
|
436 b) You are introduced to some of the more common features
|
|
|
437 c) Show how to customize it to your preferences
|
|
|
438 d) Demonstrate "Best of Use" of the plugin (easiest way to configure).
|
|
|
439 <
|
|
|
440 First, create a new buffer: >
|
|
|
441 :e tutorial.sql
|
|
|
442 <
|
|
|
443
|
|
|
444 Static features
|
|
|
445 ---------------
|
|
|
446 To take you through the various lists, simply enter insert mode, hit:
|
|
|
447 <C-C>s (show SQL statements)
|
|
|
448 At this point, you can page down through the list until you find "select".
|
|
|
449 If you are familiar with the item you are looking for, for example you know
|
|
|
450 the statement begins with the letter "s". You can type ahead (without the
|
|
|
451 quotes) "se" then press:
|
|
|
452 <C-Spact>t
|
|
|
453 Assuming "select" is highlighted in the popup list press <Enter> to choose
|
|
|
454 the entry. Now type:
|
|
|
455 * fr<C-C>a (show all syntax items)
|
|
|
456 choose "from" from the popup list.
|
|
|
457
|
|
|
458 When writing stored procedures using the "type" list is useful. It contains
|
|
|
459 a list of all the database supported types. This may or may not be true
|
|
|
460 depending on the syntax file you are using. The SQL Anywhere syntax file
|
|
|
461 (sqlanywhere.vim) has support for this: >
|
|
|
462 BEGIN
|
|
|
463 DECLARE customer_id <C-C>T <-- Choose a type from the list
|
|
|
464 <
|
|
|
465
|
|
|
466 Dynamic features
|
|
|
467 ----------------
|
|
|
468 To take advantage of the dynamic features you must first install the
|
|
|
469 dbext.vim plugin (http://vim.sourceforge.net/script.php?script_id=356). It
|
|
|
470 also comes with a tutorial. From the SQL completion plugin's perspective,
|
|
|
471 the main feature dbext provides is a connection to a database. dbext
|
|
|
472 connection profiles are the most efficient mechanism to define connection
|
|
|
473 information. Once connections have been setup, the SQL completion plugin
|
|
|
474 uses the features of dbext in the background to populate the popups.
|
|
|
475
|
|
|
476 What follows assumes dbext.vim has been correctly configured, a simple test
|
|
|
477 is to run the command, :DBListTable. If a list of tables is shown, you know
|
|
|
478 dbext.vim is working as expected. If not, please consult the dbext.txt
|
|
|
479 documentation.
|
|
|
480
|
|
819
|
481 Assuming you have followed the dbext-tutorial you can press <C-C>t to
|
|
818
|
482 display a list of tables. There is a delay while dbext is creating the table
|
|
|
483 list. After the list is displayed press <C-W>. This will remove both the
|
|
|
484 popup window and the table name already chosen when the list became active. >
|
|
|
485
|
|
|
486 4.3.1 Table Completion: *sql-completion-tables*
|
|
|
487 <
|
|
|
488 Press <C-C>t to display a list of tables from within the database you
|
|
|
489 have connected via the dbext plugin.
|
|
|
490 NOTE: All of the SQL completion popups support typing a prefix before pressing
|
|
|
491 the key map. This will limit the contents of the popup window to just items
|
|
|
492 beginning with those characters. >
|
|
|
493
|
|
|
494 4.3.2 Column Completion: *sql-completion-columns*
|
|
|
495 <
|
|
|
496 The SQL completion plugin can also display a list of columns for particular
|
|
|
497 tables. The column completion is trigger via <C-C>c.
|
|
|
498
|
|
|
499 NOTE: The following example uses <C-Right> to trigger a column list while
|
|
|
500 the popup window is active. This map is only available on the Windows
|
|
|
501 platforms since *nix does not recognize CTRL and the right arrow held down
|
|
|
502 together. If you wish to enable this functionality on a *nix platform choose
|
|
|
503 a key and create this mapping (see |sql-completion-maps| for further
|
|
|
504 details on where to create this imap): >
|
|
819
|
505 imap <buffer> <your_keystroke> <CR><C-\><C-O>:call sqlcomplete#Map('column')<CR><C-X><C-O>
|
|
818
|
506 <
|
|
|
507 Example of using column completion:
|
|
|
508 - Press <C-C>t again to display the list of tables.
|
|
|
509 - When the list is displayed in the completion window, press <C-Right>,
|
|
|
510 this will replace the list of tables, with a list of columns for the
|
|
|
511 table highlighted (after the same short delay).
|
|
|
512 - If you press <C-Left>, this will again replace the column list with the
|
|
|
513 list of tables. This allows you to drill into tables and column lists
|
|
|
514 very quickly.
|
|
|
515 - Press <C-Right> again while the same table is highlighted. You will
|
|
|
516 notice there is no delay since the column list has been cached. If you
|
|
|
517 change the schema of a cached table you can press <C-C>R, which
|
|
|
518 clears the SQL completion cache.
|
|
|
519 - NOTE: <C-Right> and <C-Left> have been designed to work while the
|
|
|
520 completion window is active. If you use these maps when the completion
|
|
|
521 window is not active a carriage return will be inadvertently entered in
|
|
|
522 your buffer.
|
|
|
523
|
|
|
524 Lets look how we can build a SQL statement dynamically. A select statement
|
|
|
525 requires a list of columns. There are two ways to build a column list using
|
|
|
526 the SQL completion plugin. >
|
|
|
527 One column at a time:
|
|
|
528 < 1. After typing SELECT press <C-C>t to display a list of tables.
|
|
|
529 2. Choose a table from the list.
|
|
|
530 3. Press <C-Right> to display a list of columns.
|
|
|
531 4. Choose the column from the list and press enter.
|
|
|
532 5. Enter a "," and press <C-C>c. Generating a column list
|
|
|
533 generally requires having the cursor on a table name. The plugin
|
|
|
534 uses this name to determine what table to retrieve the column list.
|
|
|
535 In this step, since we are pressing <C-C>c without the cursor
|
|
|
536 on a table name the column list displayed will be for the previous
|
|
|
537 table. Choose a different column and move on.
|
|
|
538 6. Repeat step 5 as often as necessary. >
|
|
|
539 All columns for a table:
|
|
|
540 < 1. After typing SELECT press <C-C>t to display a list of tables.
|
|
|
541 2. Highlight the table you need the column list for.
|
|
|
542 3. Press <Enter> to choose the table from the list.
|
|
|
543 4. Press <C-C>l to request a comma separated list of all columns
|
|
|
544 for this table.
|
|
|
545 5. Based on the table name chosen in step 3, the plugin attempts to
|
|
|
546 decide on a reasonable table alias. You are then prompted to
|
|
|
547 either accept of change the alias. Press OK.
|
|
|
548 6. The table name is replaced with the column list of the table is
|
|
|
549 replaced with the comma separate list of columns with the alias
|
|
|
550 prepended to each of the columns.
|
|
|
551 7. Step 3 and 4 can be replaced by pressing <C-C>L, which has
|
|
|
552 a <CR> embedded in the map to choose the currently highlighted
|
|
|
553 table in the list.
|
|
|
554
|
|
|
555 There is a special provision when writing select statements. Consider the
|
|
|
556 following statement: >
|
|
|
557 select *
|
|
|
558 from customer c,
|
|
|
559 contact cn,
|
|
|
560 department as dp,
|
|
|
561 employee e,
|
|
|
562 site_options so
|
|
|
563 where c.
|
|
|
564 <
|
|
|
565 In INSERT mode after typing the final "c." which is an alias for the
|
|
|
566 "customer" table, you can press either <C-C>c or <C-X><C-O>. This will
|
|
|
567 popup a list of columns for the customer table. It does this by looking back
|
|
|
568 to the beginning of the select statement and finding a list of the tables
|
|
|
569 specified in the FROM clause. In this case it notes that in the string
|
|
|
570 "customer c", "c" is an alias for the customer table. The optional "AS"
|
|
|
571 keyword is also supported, "customer AS c". >
|
|
|
572
|
|
|
573
|
|
|
574 4.3.3 Procedure Completion: *sql-completion-procedures*
|
|
|
575 <
|
|
|
576 Similar to the table list, <C-C>p, will display a list of stored
|
|
|
577 procedures stored within the database. >
|
|
|
578
|
|
|
579 4.3.4 View Completion: *sql-completion-views*
|
|
|
580 <
|
|
|
581 Similar to the table list, <C-C>v, will display a list of views in the
|
|
|
582 database.
|
|
|
583
|
|
|
584
|
|
|
585 4.4 Completion Customization *sql-completion-customization*
|
|
|
586 ----------------------------
|
|
|
587
|
|
|
588 The SQL completion plugin can be customized through various options set in
|
|
|
589 your |vimrc|: >
|
|
|
590 omni_sql_no_default_maps
|
|
|
591 < - Default: This variable is not defined
|
|
|
592 - If this variable is defined, no maps are created for OMNI
|
|
|
593 completion. See |sql-completion-maps| for further discussion.
|
|
|
594 >
|
|
|
595 omni_sql_use_tbl_alias
|
|
|
596 < - Default: a
|
|
|
597 - This setting is only used when generating a comma separated
|
|
|
598 column list. By default the map is <C-C>l. When generating
|
|
|
599 a column list, an alias can be prepended to the beginning of each
|
|
|
600 column, for example: e.emp_id, e.emp_name. This option has three
|
|
|
601 settings: >
|
|
|
602 n - do not use an alias
|
|
|
603 d - use the default (calculated) alias
|
|
|
604 a - ask to confirm the alias name
|
|
|
605 <
|
|
|
606 An alias is determined following a few rules:
|
|
|
607 1. If the table name has an '_', then use it as a separator: >
|
|
|
608 MY_TABLE_NAME --> MTN
|
|
|
609 my_table_name --> mtn
|
|
|
610 My_table_NAME --> MtN
|
|
|
611 < 2. If the table name does NOT contain an '_', but DOES use
|
|
|
612 mixed case then the case is used as a separator: >
|
|
|
613 MyTableName --> MTN
|
|
|
614 < 3. If the table name does NOT contain an '_', and does NOT
|
|
|
615 use mixed case then the first letter of the table is used: >
|
|
|
616 mytablename --> m
|
|
|
617 MYTABLENAME --> M
|
|
|
618 <
|
|
|
619
|
|
819
|
620 4.5 SQL Maps *sql-completion-maps*
|
|
|
621 ------------
|
|
|
622
|
|
|
623 The default SQL maps have been described in other sections of this document in
|
|
|
624 greater detail. Here is a list of the maps with a brief description of each.
|
|
|
625
|
|
|
626 Static Maps
|
|
|
627 -----------
|
|
|
628 These are maps which use populate the completion list using Vim's syntax
|
|
|
629 highlighting rules. >
|
|
|
630 <C-C>a
|
|
|
631 < - Displays all SQL syntax items. >
|
|
|
632 <C-C>k
|
|
|
633 < - Displays all SQL syntax items defined as 'sqlKeyword'. >
|
|
|
634 <C-C>f
|
|
|
635 < - Displays all SQL syntax items defined as 'sqlFunction. >
|
|
|
636 <C-C>o
|
|
|
637 < - Displays all SQL syntax items defined as 'sqlOption'. >
|
|
|
638 <C-C>T
|
|
|
639 < - Displays all SQL syntax items defined as 'sqlType'. >
|
|
|
640 <C-C>s
|
|
|
641 < - Displays all SQL syntax items defined as 'sqlStatement'. >
|
|
818
|
642
|
|
819
|
643 Dynamic Maps
|
|
|
644 ------------
|
|
|
645 These are maps which use populate the completion list using the dbext.vim plugin. >
|
|
|
646 <C-C>t
|
|
|
647 < - Displays a list of tables. >
|
|
|
648 <C-C>p
|
|
|
649 < - Displays a list of procedures. >
|
|
|
650 <C-C>v
|
|
|
651 < - Displays a list of views. >
|
|
|
652 <C-C>c
|
|
|
653 < - Displays a list of columns for a specific table. >
|
|
|
654 <C-C>l
|
|
|
655 < - Displays a comma separated list of columns for a specific table. >
|
|
|
656 <C-C>L
|
|
|
657 < - Displays a comma separated list of columns for a specific table.
|
|
|
658 This should only be used when the completion window is active. >
|
|
|
659 <C-Right>
|
|
|
660 < - Displays a list of columns for the table currently highlighted in
|
|
|
661 the completion window. <C-Right> is not recognized on most Unix
|
|
|
662 systems, so this maps is only created on the Windows platform.
|
|
|
663 If you would like the same feature on Unix, choose a different key
|
|
|
664 and make the same map in your vimrc.
|
|
|
665 This should only be used when the completion window is active. >
|
|
|
666 <C-Left>
|
|
|
667 < - Displays the list of tables.
|
|
|
668 <C-Left> is not recognized on most Unix systems, so this maps is
|
|
|
669 only created on the Windows platform. If you would like the same
|
|
|
670 feature on Unix, choose a different key and make the same map in
|
|
|
671 your vimrc.
|
|
|
672 This should only be used when the completion window is active. >
|
|
|
673 <C-C>R
|
|
|
674 < - This maps removes all cached items and forces the SQL completion
|
|
|
675 to regenerate the list of items.
|
|
|
676
|
|
|
677 Customizing Maps
|
|
|
678 ----------------
|
|
818
|
679 You can create as many additional key maps as you like. Generally, the maps
|
|
|
680 will be specifying different syntax highlight groups.
|
|
|
681
|
|
|
682 If you do not wish the default maps created or the key choices do not work on
|
|
|
683 your platform (often a case on *nix) you define the following variable in
|
|
|
684 your |vimrc|: >
|
|
|
685 let g:omni_sql_no_default_maps = 1
|
|
|
686 <
|
|
|
687 Do no edit ftplugin/sql.vim directly! If you change this file your changes
|
|
|
688 will be over written on future updates. Vim has a special directory structure
|
|
819
|
689 which allows you to make customizations without changing the files that are
|
|
818
|
690 included with the Vim distribution. If you wish to customize the maps
|
|
|
691 create an after/ftplugin/sql.vim (see |after-directory|) and place the same
|
|
|
692 maps from the ftplugin/sql.vim in it using your own key strokes. <C-C> was
|
|
|
693 chosen since it will work on both Windows and *nix platforms. On the windows
|
|
|
694 platform you can also use <C-Space> or ALT keys.
|
|
|
695
|
|
720
|
696
|
|
819
|
697 4.6 Using with other filetypes *sql-completion-filetypes*
|
|
|
698 ------------------------------
|
|
|
699
|
|
|
700 Many times SQL can be used with different filetypes. For example Perl, Java,
|
|
|
701 PHP, Javascript can all interact with a database. Often you need both the SQL
|
|
|
702 completion as well as the completion capabilities for the current language you
|
|
|
703 are editing.
|
|
|
704
|
|
|
705 This can be enabled easily with the following steps (assuming a Perl file): >
|
|
|
706 1. :e test.pl
|
|
|
707 2. :set filetype=sql
|
|
|
708 3. :set ft=perl
|
|
|
709
|
|
|
710 Step 1
|
|
|
711 ------
|
|
|
712 Begins by editing a Perl file. Vim automatically sets the filetype to
|
|
|
713 "perl". By default, Vim runs the appropriate filetype file
|
|
|
714 ftplugin/perl.vim. If you are using the syntax completion plugin by following
|
|
|
715 the directions at |ft-syntax-omni| then the |'omnifunc'| option has been set to
|
|
|
716 "syntax#Complete". Pressing <C-X><C-O> will display the omni popup containing
|
|
|
717 the syntax items for Perl.
|
|
|
718
|
|
|
719 Step 2
|
|
|
720 ------
|
|
|
721 Manually setting the filetype to 'sql' will also fire the appropriate filetype
|
|
|
722 files ftplugin/sql.vim. This file will define a number of buffer specific
|
|
|
723 maps for SQL completion, see |sql-completion-maps|. Now these maps have
|
|
|
724 been created and the SQL completion plugin has been initialized. All SQL
|
|
|
725 syntax items have been cached in preparation. The SQL filetype script detects
|
|
|
726 we are attempting to use two different completion plugins. Since the SQL maps
|
|
|
727 begin with <C-C>, the maps will toggle the |'omnifunc'| when in use. So you
|
|
|
728 can use <C-X><C-O> to continue using the completion for Perl (using the syntax
|
|
|
729 completion plugin) and <C-C> to use the SQL completion features.
|
|
|
730
|
|
|
731 Step 3
|
|
|
732 ------
|
|
|
733 Setting the filetype back to Perl sets all the usual "perl" related items back
|
|
|
734 as they were.
|
|
|
735
|
|
|
736
|
|
810
|
737 vim:tw=78:ts=8:ft=help:norl:
|
