Ft_sql

1. 導覽

1.1 Matchit sql-matchit

if
elseif | elsif
else [if]
end if
[while condition] loop
    leave
    break
    continue
    exit
end loop
for
    leave
    break
    continue
    exit
end loop
do
    statements
doend
case
when
when
default
end case
merge
when not matched
when matched
create[ or replace] procedure|function|event
returns

1.2 文字物件移動 sql-object-motions

]]                    move forward to the next 'begin'
[[                    move backwards to the previous 'begin'
][                    move forward to the next 'end'
[]                    move backwards to the previous 'end'

1.3 預定義物件移動 sql-predefined-objects

let g:ftplugin_sql_objects = 'function,procedure,event,table,trigger' ..
            \ ',schema,service,publication,database,datatype,domain' ..
            \ ',index,subscription,synchronization,view,variable'

]}                    move forward to the next 'create <object name>'
[{                    move backward to the previous 'create <object name>'

create table t1 (
    ...
);
create procedure p1
begin
    ...
end;
create index i1 on t1 (c1);

let g:ftplugin_sql_objects = 'function,procedure,event,' ..
            \ '\\(existing\\\\|global\\s\\+temporary\\s\\+\\)\\\{,1}' ..
            \ 'table,trigger' ..
            \ ',schema,service,publication,database,datatype,domain' ..
            \ ',index,subscription,synchronization,view,variable'

create table t1 (
    ...
);
create existing table t2 (
    ...
);
create global temporary table t3 (
    ...
);

let g:ftplugin_sql_statements = 'create,alter'

1.  --
2.  //
3.  /*
     *
     */

]"                    move forward to the beginning of a comment
["                    move forward to the end of a comment

1.4 巨集 sql-macros

\c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>

CREATE VARIABLE myVar1 INTEGER;
CREATE PROCEDURE sp_test(
    IN myVar2 INTEGER,
    OUT myVar3 CHAR(30),
    INOUT myVar4 NUMERIC(20,0)
)
BEGIN
    DECLARE myVar5 INTEGER;
    SELECT c1, c2, c3
      INTO myVar2, myVar3, myVar4
      FROM T1
     WHERE c4 = myVar1;
END;

WHERE c4 = myVar1;
            ^

[d
[D
[CTRL-D

2. SQL 方言 sql-dialects sql-types

2.1 SQLSetType sqlsettype SQLSetType

SQLSetType

:SQLSetType
:SQLSetType sqloracle
:SQLSetType sqlanywhere
:SQLSetType sqlinformix
:SQLSetType mysql

:SQL<Tab><space><Tab>

2.2 SQLGetType sqlgettype SQLGetType

SQLGetType

Current SQL dialect in use:sqlanywhere

2.3 SQL 方言預設值 sql-type-default

let g:sql_type_default = 'sqlanywhere'
let g:sql_type_default = 'sqlinformix'
let g:sql_type_default = 'mysql'

let g:sql_type_default = 'sqlinformix'

ftplugin/sql.vim
syntax/sqlinformix.vim
indent/sql.vim

3. 新增 SQL 方言 sql-adding-dialects

Unix
    ~/.config/nvim/syntax/sqlite.vim
    ~/.config/nvim/indent/sqlite.vim

4. OMNI SQL 完成 sql-completion

4.1 靜態模式 sql-completion-static

imap <buffer> <C-C>a <C-\><C-O>:call sqlcomplete#Map('syntax')<CR><C-X><C-O>
imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O>
imap <buffer> <C-C>f <C-\><C-O>:call sqlcomplete#Map('sqlFunction')<CR><C-X><C-O>
imap <buffer> <C-C>o <C-\><C-O>:call sqlcomplete#Map('sqlOption')<CR><C-X><C-O>
imap <buffer> <C-C>T <C-\><C-O>:call sqlcomplete#Map('sqlType')<CR><C-X><C-O>
imap <buffer> <C-C>s <C-\><C-O>:call sqlcomplete#Map('sqlStatement')<CR><C-X><C-O>

let g:ftplugin_sql_omni_key = '<C-C>'

imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O>
imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword\w*')<CR><C-X><C-O>

imap                   - Create an insert map
<buffer>                   - Only for this buffer
<C-C>k                   - Your choice of key map
<C-\><C-O>                   - Execute one command, return to Insert mode
:call sqlcomplete#Map( - Allows the SQL completion plugin to perform some
                         housekeeping functions to allow it to be used in
                         conjunction with other completion plugins.
                         Indicate which item you want the SQL completion
                         plugin to complete.
                         In this case we are asking the plugin to display
                         items from the syntax highlight group
                         'sqlKeyword'.
                         You can view a list of highlight group names to
                         choose from by executing the
                             :syntax list
                         command while editing a SQL file.
'sqlKeyword'           - Display the items for the sqlKeyword highlight
                         group
'sqlKeyword\w*'           - A second option available with Vim 7.4 which
                         uses a regular expression to determine which
                         syntax groups to use
)<CR>                   - Execute the :let command
<C-X><C-O>                   - Trigger the standard omni completion key stroke.
                         Passing in 'sqlKeyword' instructs the SQL
                         completion plugin to populate the popup with
                         items from the sqlKeyword highlight group.  The
                         plugin will also cache this result until Vim is
                         restarted.  The syntax list is retrieved using
                         the syntaxcomplete plugin.

All
    - Contains the contents of all syntax highlight groups
Statements
    - Select, Insert, Update, Delete, Create, Alter, ...
Functions
    - Min, Max, Trim, Round, Date, ...
Keywords
    - Index, Database, Having, Group, With
Options
    - Isolation_level, On_error, Qualify_owners, Fire_triggers, ...
Types
    - Integer, Char, Varchar, Date, DateTime, Timestamp, ...

4.2 動態模式 sql-completion-dynamic

Table List
    - All tables for all schema owners
Procedure List
    - All stored procedures for all schema owners
View List
    - All stored procedures for all schema owners
Column List
    - For the selected table, the columns that are part of the table

a) You gain familiarity with the plugin
b) You are introduced to some of the more common features
c) Show how to customize it to your preferences
d) Demonstrate "Best of Use" of the plugin (easiest way to configure).

:e tutorial.sql

BEGIN
   DECLARE customer_id <C-C>T <-- Choose a type from the list

One column at a time:

All columns for a table:

select *
  from customer c,
       contact cn,
       department as dp,
       employee e,
       site_options so
 where c.

4.4 自動完成自訂 sql-completion-customization

omni_sql_no_default_maps

n - do not use an alias
d - use the default (calculated) alias
a - ask to confirm the alias name

MY_TABLE_NAME --> MTN
my_table_name --> mtn
My_table_NAME --> MtN

MyTableName --> MTN

               mytablename --> m
               MYTABLENAME --> M
omni_sql_ignorecase

omni_sql_include_owner

omni_sql_precache_syntax_groups

4.5 SQL 對應 sql-completion-maps

<C-C>a

<C-C>k

<C-C>f

<C-C>o

<C-C>T

<C-C>s

<C-C>t

<C-C>p

<C-C>v

<C-C>c

<C-C>l

<C-C>L

<Right>

<Left>

<C-C>R

let g:omni_sql_no_default_maps = 1

4.6 與其他檔案類型搭配使用 sql-completion-filetypes

1.  :e test.pl
2.  :set filetype=sql
3.  :set ft=perl