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