為IvorySQL增添PACKAGE語法幫助詳情 - 數據庫,postgresql,sql,程序員 IvorySQL 動態日志

認識語法幫助

當使用psql工具登錄數據庫時，\h command可以查看語法幫助。比如查看create database語法：

psql (17.6)
Type "help" for help.

ivorysql=# \h create database
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ STRATEGY [=] strategy ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ BUILTIN_LOCALE [=] builtin_locale ]
           [ ICU_LOCALE [=] icu_locale ]
           [ ICU_RULES [=] icu_rules ]
           [ LOCALE_PROVIDER [=] locale_provider ]
           [ COLLATION_VERSION = collation_version ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ]
           [ OID [=] oid ]

URL: https://www.postgresql.org/docs/17/sql-createdatabase.html

ivorysql=#

這也算是個小技巧，不需要死記太多的sql語法，使用時直接查看就行。不僅打印出了對應sql的語法，還打印出了該語法對應的官方文檔URL。

語法幫助原理分析

主要是@SOURCE_ROOT/src/bin/psql/help.c中helpSQL函數實現查看語法的功能。

從Makefile看還得依賴sql_help.c, sql_help.h一起編譯完成。而從meson.build看是make的時候才生成這兩個文件的，並且是使用create_help.pl讀取文檔目錄@SOURCE_ROOT@/doc/src/sgml/ref中的所有語法文檔生成的。

sql_help = custom_target('psql_help',
  output: ['sql_help.c', 'sql_help.h'],
  depfile: 'sql_help.dep',
  command: [
    perl, files('create_help.pl'),
    '--docdir', '@SOURCE_ROOT@/doc/src/sgml/ref',
    '--depfile', '@DEPFILE@',
    '--outdir', '@OUTDIR@',
    '--basename', 'sql_help',
  ],
)
generated_sources += sql_help.to_list()
psql_sources += sql_help

可以看到doc/src/sgml/ref目錄下確實包含了其他所有對象的create的sgml文件，並沒有create_package的文件。

ls doc/src/sgml/ref/create*
doc/src/sgml/ref/create_access_method.sgml  doc/src/sgml/ref/create_extension.sgml             doc/src/sgml/ref/create_operator.sgml     doc/src/sgml/ref/create_server.sgml        doc/src/sgml/ref/create_tsdictionary.sgml
doc/src/sgml/ref/create_aggregate.sgml      doc/src/sgml/ref/create_foreign_data_wrapper.sgml  doc/src/sgml/ref/create_opfamily.sgml     doc/src/sgml/ref/create_statistics.sgml    doc/src/sgml/ref/create_tsparser.sgml
doc/src/sgml/ref/create_cast.sgml           doc/src/sgml/ref/create_foreign_table.sgml         doc/src/sgml/ref/create_policy.sgml       doc/src/sgml/ref/create_subscription.sgml  doc/src/sgml/ref/create_tstemplate.sgml
doc/src/sgml/ref/create_collation.sgml      doc/src/sgml/ref/create_function.sgml              doc/src/sgml/ref/create_procedure.sgml    doc/src/sgml/ref/create_table_as.sgml      doc/src/sgml/ref/create_type.sgml
doc/src/sgml/ref/create_conversion.sgml     doc/src/sgml/ref/create_group.sgml                 doc/src/sgml/ref/create_publication.sgml  doc/src/sgml/ref/create_table.sgml         doc/src/sgml/ref/create_user_mapping.sgml
doc/src/sgml/ref/create_database.sgml       doc/src/sgml/ref/create_index.sgml                 doc/src/sgml/ref/create_role.sgml         doc/src/sgml/ref/create_tablespace.sgml    doc/src/sgml/ref/create_user.sgml
doc/src/sgml/ref/createdb.sgml              doc/src/sgml/ref/create_language.sgml              doc/src/sgml/ref/create_rule.sgml         doc/src/sgml/ref/create_transform.sgml     doc/src/sgml/ref/createuser.sgml
doc/src/sgml/ref/create_domain.sgml         doc/src/sgml/ref/create_materialized_view.sgml     doc/src/sgml/ref/create_schema.sgml       doc/src/sgml/ref/create_trigger.sgml       doc/src/sgml/ref/create_view.sgml
doc/src/sgml/ref/create_event_trigger.sgml  doc/src/sgml/ref/create_opclass.sgml               doc/src/sgml/ref/create_sequence.sgml     doc/src/sgml/ref/create_tsconfig.sgml

增添create package語法幫助

仿照create_table.sgml或其他文件，生成create_package.sgml和create_package_body.sgml文件。

從create_help.pl可知：
cmd取自sgml文件中refname標籤，help取自synopsis標籤，docbook_id取自refentry id標籤。

因此create_package.sgml為：

<!--
doc/src/sgml/ref/create_package.sgml
PostgreSQL documentation
-->

<refentry id="sql-createpackage">
 <indexterm zone="sql-createpackage">
  <primary>CREATE PACKAGE</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE PACKAGE</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE PACKAGE</refname>
  <refpurpose>define a new package</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE [ OR REPLACE ] PACKAGE [schema.] <replaceable class="parameter">package_name</replaceable>  [invoker_rights_clause] [IS | AS] 
   item_list[, item_list ...]  
END [<replaceable class="parameter">package_name</replaceable>]

invoker_rights_clause:
    AUTHID [CURRENT_USER | DEFINER]

item_list: 
[
   function_declaration    | 
   procedure_declaration   | 
   type_definition         | 
   cursor_declaration      | 
   item_declaration
]

function_declaration:
     FUNCTION <replaceable class="parameter">function_name</replaceable> [ (parameter_declaration[, ...]) ] RETURN datatype;

procedure_declaration:
     PROCEDURE <replaceable class="parameter">procedure_name</replaceable> [ (parameter_declaration[, ...]) ]

type_definition:
     record_type_definition      |
     ref_cursor_type_definition

cursor_declaration:
   CURSOR <replaceable class="parameter">name</replaceable>  [(cur_param_decl[, ...])] RETURN rowtype;

item_declaration:
     cursor_declaration             |
     cursor_variable_declaration    |
     record_variable_declaration    |
     variable_declaration           |

record_type_definition:
   TYPE record_type IS RECORD  ( variable_declaration [, variable_declaration]... ) ;

ref_cursor_type_definition:
   TYPE type IS REF CURSOR [ RETURN type%ROWTYPE ];

cursor_variable_declaration:
   curvar curtype;

record_variable_declaration:
   recvar { record_type | rowtype_attribute | record_type%TYPE };

variable_declaration:
   varname datatype [ [ NOT NULL ] := expr ]

parameter_declaration:
   parameter_name [IN] datatype [[:= | DEFAULT] expr]

</synopsis>
 </refsynopsisdiv>

create_package_body.sgml為：

<!--
doc/src/sgml/ref/create_package_body.sgml
PostgreSQL documentation
-->

<refentry id="sql-createpackagebody">
 <indexterm zone="sql-createpackagebody">
  <primary>CREATE PACKAGE BODY</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE PACKAGE BODY</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE PACKAGE BODY</refname>
  <refpurpose>define a new package body</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>

CREATE [ OR REPLACE ] PACKAGE BODY [schema.] <replaceable class="parameter">package_name</replaceable> [IS | AS]
   [item_list[, item_list ...]] | 
   item_list_2 [, item_list_2 ...]
   [initialize_section]
END [ <replaceable class="parameter">package_name</replaceable> ];


initialize_section:
   BEGIN statement[, ...]

item_list: 
[
   function_declaration    | 
   procedure_declaration   | 
   type_definition         | 
   cursor_declaration      | 
   item_declaration
]

item_list_2:
[
   function_declaration
   function_definition
   procedure_declaration
   procedure_definition
   cursor_definition
]

function_definition:
   FUNCTION <replaceable class="parameter">function_name</replaceable> [ (parameter_declaration[, ...]) ] RETURN datatype  [IS | AS]
   [declare_section] body;

procedure_definition:
   PROCEDURE <replaceable class="parameter">procedure_name</replaceable> [ (parameter_declaration[, ...]) ] [IS | AS] 
   [declare_section] body;

cursor_definition:
   CURSOR <replaceable class="parameter">name</replaceable> [ (cur_param_decl[, ...]) ] RETURN rowtype IS select_statement;

body:
   BEGIN statement[, ...] END [name];

statement:
   [<<LABEL>>] pl_statments[, ...];


</synopsis>
 </refsynopsisdiv>

當然還需對helpSQL函數做些修改，當查詢的語法幫助為create package(body)時，展示IvorySQL對應的官方文檔URL。

for (i = 0; QL_HELP[i].cmd; i++)
{
    if (pg_strncasecmp(topic, QL_HELP[i].cmd, len) == 0 ||
        strcmp(topic, "*") == 0)
    {
        PQExpBufferData buffer;
        char       *url;

        initPQExpBuffer(&buffer);
        QL_HELP[i].syntaxfunc(&buffer);
        if (pg_strncasecmp(QL_HELP[i].docbook_id, "sql-createpackage",17) == 0)
            url = "https://www.ivorysql.org/en/docs/compatibillity_features/package \n"
                "DETAIL: https://docs.ivorysql.org/en/ivorysql-doc/v4.6/v4.6/28";
        else
            url = psprintf("https://www.postgresql.org/docs/%s/%s.html",
                    strstr(PG_VERSION, "devel") ? "devel" : PG_MAJORVERSION,
                    QL_HELP[i].docbook_id);
        /* # of newlines in format must match constant above! */
        fprintf(output, _("Command:     %s\n"
                  "Description: %s\n"
                  "Syntax:\n%s\n\n"
                  "URL: %s\n\n"),
            QL_HELP[i].cmd,
            _(QL_HELP[i].help),
            buffer.data,
            url);
        if (pg_strncasecmp(QL_HELP[i].docbook_id, "sql-createpackage",17))
            free(url);
        termPQExpBuffer(&buffer);

        /* If we have an exact match, exit.  Fixes \h SELECT */
        if (pg_strcasecmp(topic, QL_HELP[i].cmd) == 0)
            break;
    }
}

重新編譯安裝psql工具後，已經可以查看create package的語法幫助了。

\h create package

psql (17.6)
Type "help" for help.

ivorysql=# \h create package
Command:     CREATE PACKAGE
Description: define a new package
Syntax:
CREATE [ OR REPLACE ] PACKAGE [schema.] package_name  [invoker_rights_clause] [IS | AS] 
   item_list[, item_list ...]  
END [package_name]

invoker_rights_clause:
    AUTHID [CURRENT_USER | DEFINER]

item_list: 
[
   function_declaration    | 
   procedure_declaration   | 
   type_definition         | 
   cursor_declaration      | 
   item_declaration
]

function_declaration:
     FUNCTION function_name [ (parameter_declaration[, ...]) ] RETURN datatype;

procedure_declaration:
     PROCEDURE procedure_name [ (parameter_declaration[, ...]) ]

type_definition:
     record_type_definition      |
     ref_cursor_type_definition

cursor_declaration:
   CURSOR name  [(cur_param_decl[, ...])] RETURN rowtype;

item_declaration:
     cursor_declaration             |
     cursor_variable_declaration    |
     record_variable_declaration    |
     variable_declaration           |

record_type_definition:
   TYPE record_type IS RECORD  ( variable_declaration [, variable_declaration]... ) ;

ref_cursor_type_definition:
   TYPE type IS REF CURSOR [ RETURN type%ROWTYPE ];

cursor_variable_declaration:
   curvar curtype;

record_variable_declaration:
   recvar { record_type | rowtype_attribute | record_type%TYPE };

variable_declaration:
   varname datatype [ [ NOT NULL ] := expr ]

parameter_declaration:
   parameter_name [IN] datatype [[:= | DEFAULT] expr]

URL: https://www.ivorysql.org/en/docs/compatibillity_features/package 
DETAIL: https://docs.ivorysql.org/en/ivorysql-doc/v4.6/v4.6/28

ivorysql=#

\h create package body

psql (17.6)
Type "help" for help.

ivorysql=# \h create package body
Command:     CREATE PACKAGE BODY
Description: define a new package body
Syntax:
CREATE [ OR REPLACE ] PACKAGE BODY [schema.] package_name [IS | AS]
   [item_list[, item_list ...]] | 
   item_list_2 [, item_list_2 ...]
   [initialize_section]
END [ package_name ];


initialize_section:
   BEGIN statement[, ...]

item_list: 
[
   function_declaration    | 
   procedure_declaration   | 
   type_definition         | 
   cursor_declaration      | 
   item_declaration
]

item_list_2:
[
   function_declaration
   function_definition
   procedure_declaration
   procedure_definition
   cursor_definition
]

function_definition:
   FUNCTION function_name [ (parameter_declaration[, ...]) ] RETURN datatype  [IS | AS]
   [declare_section] body;

procedure_definition:
   PROCEDURE procedure_name [ (parameter_declaration[, ...]) ] [IS | AS] 
   [declare_section] body;

cursor_definition:
   CURSOR name [ (cur_param_decl[, ...]) ] RETURN rowtype IS select_statement;

body:
   BEGIN statement[, ...] END [name];

statement:
   [<<LABEL>>] pl_statments[, ...];

URL: https://www.ivorysql.org/en/docs/compatibillity_features/package 
DETAIL: https://docs.ivorysql.org/en/ivorysql-doc/v4.6/v4.6/28

ivorysql=#

小結

psql的語法幫助文檔挺好用的，本次給IvorySQL-4.6適配create package(body)語法幫助文檔算是比較系統的瞭解了下語法幫助文檔的原理。

引用：

[1]
helpSQL:
https://github.com/IvorySQL/IvorySQL/blob/IvorySQL_4.6/src/bin/psql/help.c#L575

[2]
URL: https://www.ivorysql.org/en/docs/Compatibillity_Features/package

IvorySQL 動態日志

@ivorysql_xiaozhuli

標簽

後端 (615)

人工智能 (486)

數據庫 (310)

程序員 (230)

開源 (159)

運維 (140)

機器學習 (105)

服務器 (101)

postgresql (89)

SQL (72)

github (66)

oracle (61)

動態

為IvorySQL增添PACKAGE語法幫助 - 動態詳情

認識語法幫助

語法幫助原理分析

增添create package語法幫助

小結

Add a new 評論

IvorySQL 動態日志

@ivorysql_xiaozhuli

標簽

後端 (615)

人工智能 (486)

數據庫 (310)

程序員 (230)

開源 (159)

運維 (140)

機器學習 (105)

服務器 (101)

postgresql (89)

SQL (72)

github (66)

oracle (61)

動態

為IvorySQL增添PACKAGE語法幫助 - 動態 詳情

認識語法幫助

語法幫助原理分析

增添create package語法幫助

小結

Add a new 評論

為IvorySQL增添PACKAGE語法幫助 - 動態詳情