1" Vim OMNI completion script for SQL 2" Language: SQL 3" Maintainer: David Fishburn <dfishburn dot vim at gmail dot com> 4" Version: 16.0 5" Last Change: 2017 Oct 15 6" Homepage: http://www.vim.org/scripts/script.php?script_id=1572 7" Usage: For detailed help 8" ":help sql.txt" 9" or ":help ft-sql-omni" 10" or read $VIMRUNTIME/doc/sql.txt 11 12" History 13" 14" TODO 15" - Jonas Enberg - if no table is found when using column completion 16" look backwards to a FROM clause and find the first table 17" and complete it. 18" 19" Version 16.0 (Dec 2015) 20" - NF: If reseting the cache and table, procedure or view completion 21" had been used via dbext, have dbext delete or recreate the 22" dictionary so that new objects are picked up for the 23" next completion. 24" 25" Version 15.0 (May 2013) 26" - NF: Changed the SQL precached syntax items, omni_sql_precache_syntax_groups, 27" to use regular expressions to pick up extended syntax group names. 28" This requires an updated SyntaxComplete plugin version 13.0. 29" If the required versions have not been installed, previous 30" behaviour will not be impacted. 31" 32" Version 14.0 (Dec 2012) 33" - BF: Added check for cpo 34" 35" Version 13.0 (Dec 2012) 36" - NF: When completing column lists or drilling into a table 37" and g:omni_sql_include_owner is enabled, the 38" only the table name would be replaced with the column 39" list instead of the table name and owner (if specified). 40" - NF: When completing column lists using table aliases 41" and g:omni_sql_include_owner is enabled, account 42" for the owner name when looking up the table 43" list instead of the table name and owner (if specified). 44" - BF: When completing column lists or drilling into a table 45" and g:omni_sql_include_owner is enabled, the 46" column list could often not be found for the table. 47" - BF: When OMNI popped up, possibly the wrong word 48" would be replaced for column and column list options. 49" 50" Version 12.0 (Feb 2012) 51" - Partial column name completion did not work when a table 52" name or table alias was provided (Jonas Enberg). 53" - Improved the handling of column completion. First we match any 54" columns from a previous completion. If not matches are found, we 55" consider the partial name to be a table or table alias for the 56" query and attempt to match on it. 57" 58" Version 11.0 (Jan 2012) 59" Added g:omni_sql_default_compl_type variable 60" - You can specify which type of completion to default to 61" when pressing <C-X><C-O>. The entire list of available 62" choices can be found in the calls to sqlcomplete#Map in: 63" ftplugin/sql.vim 64" 65" Version 10.0 66" Updated PreCacheSyntax() 67" - Now returns a List of the syntax items it finds. 68" This allows other plugins / scripts to use this list for their own 69" purposes. In this case XPTemplate can use them for a Choose list. 70" - Verifies the parameters are the correct type and displays a 71" warning if not. 72" - Verifies the parameters are the correct type and displays a 73" warning if not. 74" Updated SQLCWarningMsg() 75" - Prepends warning message with SQLComplete so you know who issued 76" the warning. 77" Updated SQLCErrorMsg() 78" - Prepends error message with SQLComplete so you know who issued 79" the error. 80" 81" Version 9.0 (May 2010) 82" This change removes some of the support for tables with spaces in their 83" names in order to simplify the regexes used to pull out query table 84" aliases for more robust table name and column name code completion. 85" Full support for "table names with spaces" can be added in again 86" after 7.3. 87" 88" Version 8.0 89" Incorrectly re-executed the g:ftplugin_sql_omni_key_right and g:ftplugin_sql_omni_key_left 90" when drilling in and out of a column list for a table. 91" 92" Version 7.0 (Jan 2010) 93" Better handling of object names 94" 95" Version 6.0 (Apr 2008) 96" Supports object names with spaces "my table name" 97" 98" Set completion with CTRL-X CTRL-O to autoloaded function. 99" This check is in place in case this script is 100" sourced directly instead of using the autoload feature. 101if exists('&omnifunc') 102 " Do not set the option if already set since this 103 " results in an E117 warning. 104 if &omnifunc == "" 105 setlocal omnifunc=sqlcomplete#Complete 106 endif 107endif 108 109if exists('g:loaded_sql_completion') 110 finish 111endif 112let g:loaded_sql_completion = 160 113let s:keepcpo= &cpo 114set cpo&vim 115 116" Maintains filename of dictionary 117let s:sql_file_table = "" 118let s:sql_file_procedure = "" 119let s:sql_file_view = "" 120 121" Define various arrays to be used for caching 122let s:tbl_name = [] 123let s:tbl_alias = [] 124let s:tbl_cols = [] 125let s:syn_list = [] 126let s:syn_value = [] 127 128" Used in conjunction with the syntaxcomplete plugin 129let s:save_inc = "" 130let s:save_exc = "" 131if !exists('g:omni_syntax_group_include_sql') 132 let g:omni_syntax_group_include_sql = '' 133endif 134if !exists('g:omni_syntax_group_exclude_sql') 135 let g:omni_syntax_group_exclude_sql = '' 136endif 137let s:save_inc = g:omni_syntax_group_include_sql 138let s:save_exc = g:omni_syntax_group_exclude_sql 139 140" Used with the column list 141let s:save_prev_table = "" 142 143" Default the option to verify table alias 144if !exists('g:omni_sql_use_tbl_alias') 145 let g:omni_sql_use_tbl_alias = 'a' 146endif 147" Default syntax items to precache 148if !exists('g:omni_sql_precache_syntax_groups') 149 let g:omni_sql_precache_syntax_groups = [ 150 \ 'syntax\w*', 151 \ 'sqlKeyword\w*', 152 \ 'sqlFunction\w*', 153 \ 'sqlOption\w*', 154 \ 'sqlType\w*', 155 \ 'sqlStatement\w*' 156 \ ] 157endif 158" Set ignorecase to the ftplugin standard 159if !exists('g:omni_sql_ignorecase') 160 let g:omni_sql_ignorecase = &ignorecase 161endif 162" During table completion, should the table list also 163" include the owner name 164if !exists('g:omni_sql_include_owner') 165 let g:omni_sql_include_owner = 0 166 if exists('g:loaded_dbext') 167 if g:loaded_dbext >= 300 168 " New to dbext 3.00, by default the table lists include the owner 169 " name of the table. This is used when determining how much of 170 " whatever has been typed should be replaced as part of the 171 " code replacement. 172 let g:omni_sql_include_owner = 1 173 endif 174 endif 175endif 176" Default type of completion used when <C-X><C-O> is pressed 177if !exists('g:omni_sql_default_compl_type') 178 let g:omni_sql_default_compl_type = 'table' 179endif 180 181" This function is used for the 'omnifunc' option. 182" It is called twice by omni and it is responsible 183" for returning the completion list of items. 184" But it must also determine context of what to complete 185" and what to "replace" with the completion. 186" The a:base, is replaced directly with what the user 187" chooses from the choices. 188" The s:prepend provides context for the completion. 189function! sqlcomplete#Complete(findstart, base) 190 191 " Default to table name completion 192 let compl_type = 'table' 193 " Allow maps to specify what type of object completion they want 194 if exists('b:sql_compl_type') 195 let compl_type = b:sql_compl_type 196 endif 197 let begindot = 0 198 199 " First pass through this function determines how much of the line should 200 " be replaced by whatever is chosen from the completion list 201 if a:findstart 202 " Locate the start of the item, including "." 203 let line = getline('.') 204 let start = col('.') - 1 205 let lastword = -1 206 " Check if the first character is a ".", for column completion 207 if line[start - 1] == '.' 208 let begindot = 1 209 endif 210 while start > 0 211 " Additional code was required to handle objects which 212 " can contain spaces like "my table name". 213 if line[start - 1] !~ '\(\w\|\.\)' 214 " If the previous character is not a period or word character 215 break 216 " elseif line[start - 1] =~ '\(\w\|\s\+\)' 217 " let start -= 1 218 elseif line[start - 1] =~ '\w' 219 " If the previous character is word character continue back 220 let start -= 1 221 elseif line[start - 1] =~ '\.' && 222 \ compl_type =~ 'column\|table\|view\|procedure' 223 " If the previous character is a period and we are completing 224 " an object which can be specified with a period like this: 225 " table_name.column_name 226 " owner_name.table_name 227 228 " If lastword has already been set for column completion 229 " break from the loop, since we do not also want to pickup 230 " a table name if it was also supplied. 231 " Unless g:omni_sql_include_owner == 1, then we can 232 " include the ownername. 233 if lastword != -1 && compl_type == 'column' 234 \ && g:omni_sql_include_owner == 0 235 break 236 endif 237 " If column completion was specified stop at the "." if 238 " a . was specified, otherwise, replace all the way up 239 " to the owner name (if included). 240 if lastword == -1 && compl_type == 'column' && begindot == 1 241 let lastword = start 242 endif 243 " If omni_sql_include_owner = 0, do not include the table 244 " name as part of the substitution, so break here 245 if lastword == -1 && 246 \ compl_type =~ '\<\(table\|view\|procedure\|column\|column_csv\)\>' && 247 \ g:omni_sql_include_owner == 0 248 let lastword = start 249 break 250 endif 251 let start -= 1 252 else 253 break 254 endif 255 endwhile 256 257 " Return the column of the last word, which is going to be changed. 258 " Remember the text that comes before it in s:prepended. 259 if lastword == -1 260 let s:prepended = '' 261 return start 262 endif 263 let s:prepended = strpart(line, start, lastword - start) 264 return lastword 265 endif 266 267 " Second pass through this function will determine what data to put inside 268 " of the completion list 269 " s:prepended is set by the first pass 270 let base = s:prepended . a:base 271 272 " Default the completion list to an empty list 273 let compl_list = [] 274 275 " Default to table name completion 276 let compl_type = g:omni_sql_default_compl_type 277 " Allow maps to specify what type of object completion they want 278 if exists('b:sql_compl_type') 279 let compl_type = b:sql_compl_type 280 unlet b:sql_compl_type 281 endif 282 283 if compl_type == 'tableReset' 284 let compl_type = 'table' 285 let base = '' 286 endif 287 288 if compl_type == 'table' || 289 \ compl_type == 'procedure' || 290 \ compl_type == 'view' 291 292 " This type of completion relies upon the dbext.vim plugin 293 if s:SQLCCheck4dbext() == -1 294 return [] 295 endif 296 297 " Allow the user to override the dbext plugin to specify whether 298 " the owner/creator should be included in the list 299 if g:loaded_dbext >= 300 300 let saveSetting = DB_listOption('dict_show_owner') 301 exec 'DBSetOption dict_show_owner='.(g:omni_sql_include_owner==1?'1':'0') 302 endif 303 304 let compl_type_uc = substitute(compl_type, '\w\+', '\u&', '') 305 " Same call below, no need to do it twice 306 " if s:sql_file_{compl_type} == "" 307 " let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type_uc) 308 " endif 309 let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type_uc) 310 if s:sql_file_{compl_type} != "" 311 if filereadable(s:sql_file_{compl_type}) 312 let compl_list = readfile(s:sql_file_{compl_type}) 313 endif 314 endif 315 316 if g:loaded_dbext > 300 317 exec 'DBSetOption dict_show_owner='.saveSetting 318 endif 319 elseif compl_type =~? 'column' 320 321 " This type of completion relies upon the dbext.vim plugin 322 if s:SQLCCheck4dbext() == -1 323 return [] 324 endif 325 326 if base == "" 327 " The last time we displayed a column list we stored 328 " the table name. If the user selects a column list 329 " without a table name of alias present, assume they want 330 " the previous column list displayed. 331 let base = s:save_prev_table 332 endif 333 334 let owner = '' 335 let column = '' 336 337 if base =~ '\.' 338 " Check if the owner/creator has been specified 339 let owner = matchstr( base, '^\zs.*\ze\..*\..*' ) 340 let table = matchstr( base, '^\(.*\.\)\?\zs.*\ze\..*' ) 341 let column = matchstr( base, '.*\.\zs.*' ) 342 343 if g:omni_sql_include_owner == 1 && owner == '' && table != '' && column != '' 344 let owner = table 345 let table = column 346 let column = '' 347 endif 348 349 " It is pretty well impossible to determine if the user 350 " has entered: 351 " owner.table 352 " table.column_prefix 353 " So there are a couple of things we can do to mitigate 354 " this issue. 355 " 1. Check if the dbext plugin has the option turned 356 " on to even allow owners 357 " 2. Based on 1, if the user is showing a table list 358 " and the DrillIntoTable (using <Right>) then 359 " this will be owner.table. In this case, we can 360 " check to see the table.column exists in the 361 " cached table list. If it does, then we have 362 " determined the user has actually chosen 363 " owner.table, not table.column_prefix. 364 let found = -1 365 if g:omni_sql_include_owner == 1 && owner == '' 366 if filereadable(s:sql_file_table) 367 let tbl_list = readfile(s:sql_file_table) 368 let found = index( tbl_list, ((table != '')?(table.'.'):'').column) 369 endif 370 endif 371 " If the table.column was found in the table list, we can safely assume 372 " the owner was not provided and shift the items appropriately. 373 " OR 374 " If the user has indicated not to use table owners at all and 375 " the base ends in a '.' we know they are not providing a column 376 " name, so we can shift the items appropriately. 377 " if found != -1 || (g:omni_sql_include_owner == 0 && base !~ '\.$') 378 " let owner = table 379 " let table = column 380 " let column = '' 381 " endif 382 else 383 " If no "." was provided and the user asked for 384 " column level completion, first attempt the match 385 " on any previous column lists. If the user asked 386 " for a list of columns comma separated, continue as usual. 387 if compl_type == 'column' && s:save_prev_table != '' 388 " The last time we displayed a column list we stored 389 " the table name. If the user selects a column list 390 " without a table name of alias present, assume they want 391 " the previous column list displayed. 392 let table = s:save_prev_table 393 let list_type = '' 394 395 let compl_list = s:SQLCGetColumns(table, list_type) 396 if ! empty(compl_list) 397 " If no column prefix has been provided and the table 398 " name was provided, append it to each of the items 399 " returned. 400 let compl_list = filter(deepcopy(compl_list), 'v:val=~"^'.base.'"' ) 401 402 " If not empty, we have a match on columns 403 " return the list 404 if ! empty(compl_list) 405 return compl_list 406 endif 407 endif 408 endif 409 " Since no columns were found to match the base supplied 410 " assume the user is trying to complete the column list 411 " for a table (and or an alias to a table). 412 let table = base 413 endif 414 415 " Get anything after the . and consider this the table name 416 " If an owner has been specified, then we must consider the 417 " base to be a partial column name 418 " let base = matchstr( base, '^\(.*\.\)\?\zs.*' ) 419 420 if table != "" 421 let s:save_prev_table = base 422 let list_type = '' 423 424 if compl_type == 'column_csv' 425 " Return one array element, with a comma separated 426 " list of values instead of multiple array entries 427 " for each column in the table. 428 let list_type = 'csv' 429 endif 430 431 " If we are including the OWNER for the objects, then for 432 " table completion, if we have it, it should be included 433 " as there can be the same table names in a database yet 434 " with different owner names. 435 if g:omni_sql_include_owner == 1 && owner != '' && table != '' 436 let compl_list = s:SQLCGetColumns(owner.'.'.table, list_type) 437 else 438 let compl_list = s:SQLCGetColumns(table, list_type) 439 endif 440 441 if column != '' 442 " If no column prefix has been provided and the table 443 " name was provided, append it to each of the items 444 " returned. 445 let compl_list = map(compl_list, 'table.".".v:val') 446 if owner != '' 447 " If an owner has been provided append it to each of the 448 " items returned. 449 let compl_list = map(compl_list, 'owner.".".v:val') 450 endif 451 else 452 let base = '' 453 endif 454 455 if compl_type == 'column_csv' 456 " Join the column array into 1 single element array 457 " but make the columns column separated 458 let compl_list = [join(compl_list, ', ')] 459 endif 460 endif 461 elseif compl_type == 'resetCache' 462 " Reset all cached items 463 let s:tbl_name = [] 464 let s:tbl_alias = [] 465 let s:tbl_cols = [] 466 let s:syn_list = [] 467 let s:syn_value = [] 468 469 if s:sql_file_table != "" 470 if g:loaded_dbext >= 2300 471 call DB_DictionaryDelete("table") 472 else 473 DBCompleteTables! 474 endif 475 endif 476 if s:sql_file_procedure != "" 477 if g:loaded_dbext >= 2300 478 call DB_DictionaryDelete("procedure") 479 else 480 DBCompleteProcedures! 481 endif 482 endif 483 if s:sql_file_view != "" 484 if g:loaded_dbext >= 2300 485 call DB_DictionaryDelete("view") 486 else 487 DBCompleteViews! 488 endif 489 endif 490 491 let s:sql_file_table = "" 492 let s:sql_file_procedure = "" 493 let s:sql_file_view = "" 494 495 let msg = "All SQL cached items have been removed." 496 call s:SQLCWarningMsg(msg) 497 " Leave time for the user to read the error message 498 :sleep 2 499 else 500 let compl_list = s:SQLCGetSyntaxList(compl_type) 501 endif 502 503 if base != '' 504 " Filter the list based on the first few characters the user entered. 505 " Check if the text matches at the beginning 506 " \\(^.base.'\\) 507 " or 508 " Match to a owner.table or alias.column type match 509 " ^\\(\\w\\+\\.\\)\\?'.base.'\\) 510 " or 511 " Handle names with spaces "my table name" 512 " "\\(^'.base.'\\|^\\(\\w\\+\\.\\)\\?'.base.'\\)"' 513 " 514 let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\|^\\(\\w\\+\\.\\)\\?'.base.'\\)"' 515 " let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\)"' 516 " let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\|\\(\\.\\)\\?'.base.'\\)"' 517 " let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\|\\([^.]*\\)\\?'.base.'\\)"' 518 let compl_list = filter(deepcopy(compl_list), expr) 519 520 if empty(compl_list) && compl_type == 'table' && base =~ '\.$' 521 " It is possible we could be looking for column name completion 522 " and the user simply hit C-X C-O to lets try it as well 523 " since we had no hits with the tables. 524 " If the base ends with a . it is hard to know if we are 525 " completing table names or column names. 526 let list_type = '' 527 528 let compl_list = s:SQLCGetColumns(base, list_type) 529 endif 530 endif 531 532 if exists('b:sql_compl_savefunc') && b:sql_compl_savefunc != "" 533 let &omnifunc = b:sql_compl_savefunc 534 endif 535 536 if empty(compl_list) 537 call s:SQLCWarningMsg( 'Could not find type['.compl_type.'] using prepend[.'.s:prepended.'] base['.a:base.']' ) 538 endif 539 540 return compl_list 541endfunc 542 543function! sqlcomplete#PreCacheSyntax(...) 544 let syn_group_arr = [] 545 let syn_items = [] 546 547 if a:0 > 0 548 if type(a:1) != 3 549 call s:SQLCWarningMsg("Parameter is not a list. Example:['syntaxGroup1', 'syntaxGroup2']") 550 return '' 551 endif 552 let syn_group_arr = a:1 553 else 554 let syn_group_arr = g:omni_sql_precache_syntax_groups 555 endif 556 " For each group specified in the list, precache all 557 " the sytnax items. 558 if !empty(syn_group_arr) 559 for group_name in syn_group_arr 560 let syn_items = extend( syn_items, s:SQLCGetSyntaxList(group_name) ) 561 endfor 562 endif 563 564 return syn_items 565endfunction 566 567function! sqlcomplete#ResetCacheSyntax(...) 568 let syn_group_arr = [] 569 570 if a:0 > 0 571 if type(a:1) != 3 572 call s:SQLCWarningMsg("Parameter is not a list. Example:['syntaxGroup1', 'syntaxGroup2']") 573 return '' 574 endif 575 let syn_group_arr = a:1 576 else 577 let syn_group_arr = g:omni_sql_precache_syntax_groups 578 endif 579 " For each group specified in the list, precache all 580 " the sytnax items. 581 if !empty(syn_group_arr) 582 for group_name in syn_group_arr 583 let list_idx = index(s:syn_list, group_name, 0, &ignorecase) 584 if list_idx > -1 585 " Remove from list of groups 586 call remove( s:syn_list, list_idx ) 587 " Remove from list of keywords 588 call remove( s:syn_value, list_idx ) 589 endif 590 endfor 591 endif 592endfunction 593 594function! sqlcomplete#Map(type) 595 " Tell the SQL plugin what you want to complete 596 let b:sql_compl_type=a:type 597 " Record previous omnifunc, if the SQL completion 598 " is being used in conjunction with other filetype 599 " completion plugins 600 if &omnifunc != "" && &omnifunc != 'sqlcomplete#Complete' 601 " Record the previous omnifunc, the plugin 602 " will automatically set this back so that it 603 " does not interfere with other ftplugins settings 604 let b:sql_compl_savefunc=&omnifunc 605 endif 606 " Set the OMNI func for the SQL completion plugin 607 let &omnifunc='sqlcomplete#Complete' 608endfunction 609 610function! sqlcomplete#DrillIntoTable() 611 " If the omni popup window is visible 612 if pumvisible() 613 call sqlcomplete#Map('column') 614 " C-Y, makes the currently highlighted entry active 615 " and trigger the omni popup to be redisplayed 616 call feedkeys("\<C-Y>\<C-X>\<C-O>", 'n') 617 else 618 " If the popup is not visible, simple perform the normal 619 " key behaviour. 620 " Must use exec since they key must be preceeded by "\" 621 " or feedkeys will simply push each character of the string 622 " rather than the "key press". 623 exec 'call feedkeys("\'.g:ftplugin_sql_omni_key_right.'", "n")' 624 endif 625 return "" 626endfunction 627 628function! sqlcomplete#DrillOutOfColumns() 629 " If the omni popup window is visible 630 if pumvisible() 631 call sqlcomplete#Map('tableReset') 632 " Trigger the omni popup to be redisplayed 633 call feedkeys("\<C-X>\<C-O>") 634 else 635 " If the popup is not visible, simple perform the normal 636 " key behaviour. 637 " Must use exec since they key must be preceeded by "\" 638 " or feedkeys will simply push each character of the string 639 " rather than the "key press". 640 exec 'call feedkeys("\'.g:ftplugin_sql_omni_key_left.'", "n")' 641 endif 642 return "" 643endfunction 644 645function! s:SQLCWarningMsg(msg) 646 echohl WarningMsg 647 echomsg 'SQLComplete:'.a:msg 648 echohl None 649endfunction 650 651function! s:SQLCErrorMsg(msg) 652 echohl ErrorMsg 653 echomsg 'SQLComplete:'.a:msg 654 echohl None 655endfunction 656 657function! s:SQLCGetSyntaxList(syn_group) 658 let syn_group = a:syn_group 659 let compl_list = [] 660 661 " Check if we have already cached the syntax list 662 let list_idx = index(s:syn_list, syn_group, 0, &ignorecase) 663 if list_idx > -1 664 " Return previously cached value 665 let compl_list = s:syn_value[list_idx] 666 else 667 let s:save_inc = g:omni_syntax_group_include_sql 668 let s:save_exc = g:omni_syntax_group_exclude_sql 669 let g:omni_syntax_group_include_sql = '' 670 let g:omni_syntax_group_exclude_sql = '' 671 672 " Request the syntax list items from the 673 " syntax completion plugin 674 if syn_group == 'syntax' 675 " Handle this special case. This allows the user 676 " to indicate they want all the syntax items available, 677 " so do not specify a specific include list. 678 let syn_value = syntaxcomplete#OmniSyntaxList() 679 else 680 " The user has specified a specific syntax group 681 let g:omni_syntax_group_include_sql = syn_group 682 let syn_value = syntaxcomplete#OmniSyntaxList(syn_group) 683 endif 684 let g:omni_syntax_group_include_sql = s:save_inc 685 let g:omni_syntax_group_exclude_sql = s:save_exc 686 " Cache these values for later use 687 let s:syn_list = add( s:syn_list, syn_group ) 688 let s:syn_value = add( s:syn_value, syn_value ) 689 let compl_list = syn_value 690 endif 691 692 return compl_list 693endfunction 694 695function! s:SQLCCheck4dbext() 696 if !exists('g:loaded_dbext') 697 let msg = "The dbext plugin must be loaded for dynamic SQL completion" 698 call s:SQLCErrorMsg(msg) 699 " Leave time for the user to read the error message 700 :sleep 2 701 return -1 702 elseif g:loaded_dbext < 600 703 let msg = "The dbext plugin must be at least version 5.30 " . 704 \ " for dynamic SQL completion" 705 call s:SQLCErrorMsg(msg) 706 " Leave time for the user to read the error message 707 :sleep 2 708 return -1 709 endif 710 return 1 711endfunction 712 713function! s:SQLCAddAlias(table_name, table_alias, cols) 714 " Strip off the owner if included 715 let table_name = matchstr(a:table_name, '\%(.\{-}\.\)\?\zs\(.*\)' ) 716 let table_alias = a:table_alias 717 let cols = a:cols 718 719 if g:omni_sql_use_tbl_alias != 'n' 720 if table_alias == '' 721 if 'da' =~? g:omni_sql_use_tbl_alias 722 if table_name =~ '_' 723 " Treat _ as separators since people often use these 724 " for word separators 725 let save_keyword = &iskeyword 726 setlocal iskeyword-=_ 727 728 " Get the first letter of each word 729 " [[:alpha:]] is used instead of \w 730 " to catch extended accented characters 731 " 732 let table_alias = substitute( 733 \ table_name, 734 \ '\<[[:alpha:]]\+\>_\?', 735 \ '\=strpart(submatch(0), 0, 1)', 736 \ 'g' 737 \ ) 738 " Restore original value 739 let &iskeyword = save_keyword 740 elseif table_name =~ '\u\U' 741 let table_alias = substitute( 742 \ table_name, '\(\u\)\U*', '\1', 'g') 743 else 744 let table_alias = strpart(table_name, 0, 1) 745 endif 746 endif 747 endif 748 if table_alias != '' 749 " Following a word character, make sure there is a . and no spaces 750 let table_alias = substitute(table_alias, '\w\zs\.\?\s*$', '.', '') 751 if 'a' =~? g:omni_sql_use_tbl_alias && a:table_alias == '' 752 let table_alias = inputdialog("Enter table alias:", table_alias) 753 endif 754 endif 755 if table_alias != '' 756 let cols = substitute(cols, '\<\w', table_alias.'&', 'g') 757 endif 758 endif 759 760 return cols 761endfunction 762 763function! s:SQLCGetObjectOwner(object) 764 " The owner regex matches a word at the start of the string which is 765 " followed by a dot, but doesn't include the dot in the result. 766 " ^ - from beginning of line 767 " \("\|\[\)\? - ignore any quotes 768 " \zs - start the match now 769 " .\{-} - get owner name 770 " \ze - end the match 771 " \("\|\[\)\? - ignore any quotes 772 " \. - must by followed by a . 773 " let owner = matchstr( a:object, '^\s*\zs.*\ze\.' ) 774 let owner = matchstr( a:object, '^\("\|\[\)\?\zs\.\{-}\ze\("\|\]\)\?\.' ) 775 return owner 776endfunction 777 778function! s:SQLCGetColumns(table_name, list_type) 779 if a:table_name =~ '\.' 780 " Check if the owner/creator has been specified 781 let owner = matchstr( a:table_name, '^\zs.*\ze\..*\..*' ) 782 let table = matchstr( a:table_name, '^\(.*\.\)\?\zs.*\ze\..*' ) 783 let column = matchstr( a:table_name, '.*\.\zs.*' ) 784 785 if g:omni_sql_include_owner == 1 && owner == '' && table != '' && column != '' 786 let owner = table 787 let table = column 788 let column = '' 789 endif 790 else 791 let owner = '' 792 let table = matchstr(a:table_name, '^["\[\]a-zA-Z0-9_ ]\+\ze\.\?') 793 let column = '' 794 endif 795 796 " Check if the table name was provided as part of the column name 797 " let table_name = matchstr(a:table_name, '^["\[\]a-zA-Z0-9_ ]\+\ze\.\?') 798 let table_name = table 799 let table_cols = [] 800 let table_alias = '' 801 let move_to_top = 1 802 803 let table_name = substitute(table_name, '\s*\(.\{-}\)\s*$', '\1', 'g') 804 805 " If the table name was given as: 806 " where c. 807 let table_name = substitute(table_name, '^\c\(WHERE\|AND\|OR\)\s\+', '', '') 808 if g:loaded_dbext >= 300 809 let saveSettingAlias = DB_listOption('use_tbl_alias') 810 exec 'DBSetOption use_tbl_alias=n' 811 endif 812 813 let table_name_stripped = substitute(table_name, '["\[\]]*', '', 'g') 814 815 " Check if we have already cached the column list for this table 816 " by its name 817 let list_idx = index(s:tbl_name, table_name_stripped, 0, &ignorecase) 818 if list_idx > -1 819 let table_cols = split(s:tbl_cols[list_idx], '\n') 820 else 821 " Check if we have already cached the column list for this table 822 " by its alias, assuming the table_name provided was actually 823 " the alias for the table instead 824 " select * 825 " from area a 826 " where a. 827 let list_idx = index(s:tbl_alias, table_name_stripped, 0, &ignorecase) 828 if list_idx > -1 829 let table_alias = table_name_stripped 830 let table_name = s:tbl_name[list_idx] 831 let table_cols = split(s:tbl_cols[list_idx], '\n') 832 endif 833 endif 834 835 " If we have not found a cached copy of the table 836 " And the table ends in a "." or we are looking for a column list 837 " if list_idx == -1 && (a:table_name =~ '\.' || b:sql_compl_type =~ 'column') 838 " if list_idx == -1 && (a:table_name =~ '\.' || a:list_type =~ 'csv') 839 if list_idx == -1 840 let saveY = @y 841 let saveSearch = @/ 842 let saveWScan = &wrapscan 843 let curline = line(".") 844 let curcol = col(".") 845 846 " Do not let searchs wrap 847 setlocal nowrapscan 848 " If . was entered, look at the word just before the . 849 " We are looking for something like this: 850 " select * 851 " from customer c 852 " where c. 853 " So when . is pressed, we need to find 'c' 854 " 855 856 " Search backwards to the beginning of the statement 857 " and do NOT wrap 858 " exec 'silent! normal! v?\<\(select\|update\|delete\|;\)\>'."\n".'"yy' 859 exec 'silent! normal! ?\<\c\(select\|update\|delete\|;\)\>'."\n" 860 861 " Start characterwise visual mode 862 " Advance right one character 863 " Search forward until one of the following: 864 " 1. Another select/update/delete statement 865 " 2. A ; at the end of a line (the delimiter) 866 " 3. The end of the file (incase no delimiter) 867 " Yank the visually selected text into the "y register. 868 exec 'silent! normal! vl/\c\(\<select\>\|\<update\>\|\<delete\>\|;\s*$\|\%$\)'."\n".'"yy' 869 870 let query = @y 871 let query = substitute(query, "\n", ' ', 'g') 872 let found = 0 873 874 " if query =~? '^\c\(select\)' 875 if query =~? '^\(select\|update\|delete\)' 876 let found = 1 877 " \(\(\<\w\+\>\)\.\)\? - 878 " '\c\(from\|join\|,\).\{-}' - Starting at the from clause (case insensitive) 879 " '\zs\(\(\<\w\+\>\)\.\)\?' - Get the owner name (optional) 880 " '\<\w\+\>\ze' - Get the table name 881 " '\s\+\<'.table_name.'\>' - Followed by the alias 882 " '\s*\.\@!.*' - Cannot be followed by a . 883 " '\(\<where\>\|$\)' - Must be followed by a WHERE clause 884 " '.*' - Exclude the rest of the line in the match 885 " let table_name_new = matchstr(@y, 886 " \ '\c\(from\|join\|,\).\{-}'. 887 " \ '\zs\(\("\|\[\)\?.\{-}\("\|\]\)\.\)\?'. 888 " \ '\("\|\[\)\?.\{-}\("\|\]\)\?\ze'. 889 " \ '\s\+\%(as\s\+\)\?\<'. 890 " \ matchstr(table_name, '.\{-}\ze\.\?$'). 891 " \ '\>'. 892 " \ '\s*\.\@!.*'. 893 " \ '\(\<where\>\|$\)'. 894 " \ '.*' 895 " \ ) 896 " 897 " 898 " ''\c\(\<from\>\|\<join\>\|,\)\s*' - Starting at the from clause (case insensitive) 899 " '\zs\(\("\|\[\)\?\w\+\("\|\]\)\?\.\)\?' - Get the owner name (optional) 900 " '\("\|\[\)\?\w\+\("\|\]\)\?\ze' - Get the table name 901 " '\s\+\%(as\s\+\)\?\<'.matchstr(table_name, '.\{-}\ze\.\?$').'\>' - Followed by the alias 902 " '\s*\.\@!.*' - Cannot be followed by a . 903 " '\(\<where\>\|$\)' - Must be followed by a WHERE clause 904 " '.*' - Exclude the rest of the line in the match 905 let table_name_new = matchstr(@y, 906 \ '\c\(\<from\>\|\<join\>\|,\)\s*'. 907 \ '\zs\(\("\|\[\)\?\w\+\("\|\]\)\?\.\)\?'. 908 \ '\("\|\[\)\?\w\+\("\|\]\)\?\ze'. 909 \ '\s\+\%(as\s\+\)\?\<'. 910 \ matchstr(table_name, '.\{-}\ze\.\?$'). 911 \ '\>'. 912 \ '\s*\.\@!.*'. 913 \ '\(\<where\>\|$\)'. 914 \ '.*' 915 \ ) 916 917 if table_name_new != '' 918 let table_alias = table_name 919 if g:omni_sql_include_owner == 1 920 let table_name = matchstr( table_name_new, '^\zs\(.\{-}\.\)\?\(.\{-}\.\)\?.*\ze' ) 921 else 922 " let table_name = matchstr( table_name_new, '^\(.*\.\)\?\zs.*\ze' ) 923 let table_name = matchstr( table_name_new, '^\(.\{-}\.\)\?\zs\(.\{-}\.\)\?.*\ze' ) 924 endif 925 926 let list_idx = index(s:tbl_name, table_name, 0, &ignorecase) 927 if list_idx > -1 928 let table_cols = split(s:tbl_cols[list_idx]) 929 let s:tbl_name[list_idx] = table_name 930 let s:tbl_alias[list_idx] = table_alias 931 else 932 let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase) 933 if list_idx > -1 934 let table_cols = split(s:tbl_cols[list_idx]) 935 let s:tbl_name[list_idx] = table_name 936 let s:tbl_alias[list_idx] = table_alias 937 endif 938 endif 939 940 endif 941 else 942 " Simply assume it is a table name provided with a . on the end 943 let found = 1 944 endif 945 946 let @y = saveY 947 let @/ = saveSearch 948 let &wrapscan = saveWScan 949 950 " Return to previous location 951 call cursor(curline, curcol) 952 953 if found == 0 954 if g:loaded_dbext > 300 955 exec 'DBSetOption use_tbl_alias='.saveSettingAlias 956 endif 957 958 " Not a SQL statement, do not display a list 959 return [] 960 endif 961 endif 962 963 if empty(table_cols) 964 " Specify silent mode, no messages to the user (tbl, 1) 965 " Specify do not comma separate (tbl, 1, 1) 966 " let table_cols_str = DB_getListColumn(table_name, 1, 1) 967 let table_cols_str = DB_getListColumn((owner!=''?owner.'.':'').table_name, 1, 1) 968 969 if table_cols_str != "" 970 let s:tbl_name = add( s:tbl_name, table_name ) 971 let s:tbl_alias = add( s:tbl_alias, table_alias ) 972 let s:tbl_cols = add( s:tbl_cols, table_cols_str ) 973 let table_cols = split(table_cols_str, '\n') 974 endif 975 976 endif 977 978 if g:loaded_dbext > 300 979 exec 'DBSetOption use_tbl_alias='.saveSettingAlias 980 endif 981 982 " If the user has asked for a comma separate list of column 983 " values, ask the user if they want to prepend each column 984 " with a tablename alias. 985 if a:list_type == 'csv' && !empty(table_cols) 986 let cols = join(table_cols, ', ') 987 let cols = s:SQLCAddAlias(table_name, table_alias, cols) 988 let table_cols = [cols] 989 endif 990 991 return table_cols 992endfunction 993" Restore: 994let &cpo= s:keepcpo 995unlet s:keepcpo 996" vim: ts=4 fdm=marker 997