Line data Source code
1 : /*
2 : * SPDX-License-Identifier: MPL-2.0
3 : *
4 : * This Source Code Form is subject to the terms of the Mozilla Public
5 : * License, v. 2.0. If a copy of the MPL was not distributed with this
6 : * file, You can obtain one at http://mozilla.org/MPL/2.0/.
7 : *
8 : * Copyright 2024 MonetDB Foundation;
9 : * Copyright August 2008 - 2023 MonetDB B.V.;
10 : * Copyright 1997 - July 2008 CWI.
11 : */
12 :
13 : /**
14 : * 2016 Martin Kersten
15 : *
16 : * The SQL syntax help synopsis.
17 : */
18 :
19 : /* produce a synopsis of the SQL syntax, inspired by a competing product.
20 : * Use the conventional grammar constructs:
21 : * [ A | B ] token A or B or none
22 : * { A | B } exactly one of the options A or B should be chosen
23 : * A [',' ...] a comma separated list of A elements
24 : * { A | B } ... a series of A and B's
25 : * { A B } [',' ...] a series of A B,A B,A B,A B
26 : *
27 : * Ideally each major command line should point into the website for
28 : * more details and variations not covered here.
29 : * */
30 :
31 : #include "monetdb_config.h"
32 : #include <ctype.h>
33 : #include <string.h>
34 : #ifdef HAVE_STRINGS_H
35 : #include <strings.h> /* for strncasecmp */
36 : #endif
37 : #include "stream.h"
38 : #include "mhelp.h"
39 :
40 : typedef struct {
41 : const char *command;
42 : const char *synopsis;
43 : const char *syntax;
44 : const char *rules;
45 : const char *comments;
46 : } SQLhelp;
47 :
48 : SQLhelp sqlhelp1[] = {
49 : // major commands
50 : {"ALTER MERGE TABLE",
51 : "",
52 : "ALTER TABLE [ IF EXISTS ] qname ADD TABLE qname [ AS PARTITION partition_spec ]\n"
53 : "ALTER TABLE [ IF EXISTS ] qname DROP TABLE qname [ RESTRICT | CASCADE ]\n"
54 : "ALTER TABLE [ IF EXISTS ] qname SET TABLE qname AS PARTITION partition_spec",
55 : "qname,partition_spec",
56 : "See also https://www.monetdb.org/documentation/admin-guide/distributed-query-processing/"},
57 : {"ALTER SCHEMA",
58 : "",
59 : "ALTER SCHEMA [ IF EXISTS ] ident RENAME TO ident",
60 : "ident",
61 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/schema-definitions/"},
62 : {"ALTER SEQUENCE",
63 : "",
64 : "ALTER SEQUENCE qname [AS seq_int_datatype] [RESTART [WITH {bigint|subquery}] ] [INCREMENT BY bigint]\n"
65 : " [MINVALUE bigint | NO MINVALUE] [MAXVALUE bigint | NO MAXVALUE] [CACHE bigint] [[NO] CYCLE]",
66 : "seq_int_datatype",
67 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-types/serial-types/"},
68 : {"ALTER TABLE",
69 : "",
70 : "ALTER TABLE [ IF EXISTS ] qname ADD [ COLUMN ] column_def\n"
71 : "ALTER TABLE [ IF EXISTS ] qname ADD [ CONSTRAINT ident ] table_constraint\n"
72 : "ALTER TABLE [ IF EXISTS ] qname ALTER [ COLUMN ] ident SET DEFAULT value\n"
73 : "ALTER TABLE [ IF EXISTS ] qname ALTER [ COLUMN ] ident SET [NOT] NULL\n"
74 : "ALTER TABLE [ IF EXISTS ] qname ALTER [ COLUMN ] ident DROP DEFAULT\n"
75 : "ALTER TABLE [ IF EXISTS ] qname ALTER [ COLUMN ] ident SET STORAGE {string | NULL}\n"
76 : "ALTER TABLE [ IF EXISTS ] qname DROP [ COLUMN ] ident [ RESTRICT | CASCADE ]\n"
77 : "ALTER TABLE [ IF EXISTS ] qname DROP CONSTRAINT ident [ RESTRICT | CASCADE ]\n"
78 : "ALTER TABLE [ IF EXISTS ] qname RENAME [ COLUMN ] ident TO ident\n"
79 : "ALTER TABLE [ IF EXISTS ] qname RENAME TO ident\n"
80 : "ALTER TABLE [ IF EXISTS ] qname SET { INSERT ONLY | READ ONLY | READ WRITE }\n"
81 : "ALTER TABLE [ IF EXISTS ] qname SET SCHEMA ident",
82 : "qname,column_def,table_constraint,ident",
83 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/alter-statement/"},
84 : {"ALTER USER",
85 : "Change a user's login name or password or default schema",
86 : "ALTER USER ident RENAME TO ident\n"
87 : "ALTER USER SET [ENCRYPTED | UNENCRYPTED] PASSWORD string USING OLD PASSWORD string\n"
88 : "ALTER USER ident\n"
89 : " [WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string]\n"
90 : " [SET SCHEMA ident] [SCHEMA PATH string] [DEFAULT ROLE ident]\n"
91 : " [MAX_MEMORY posbytes | MAX_MEMORY sizestr | NO MAX_MEMORY] [MAX_WORKERS poscount | NO MAX_WORKERS]",
92 : "ident",
93 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/privileges/"},
94 : {"ANALYZE",
95 : "Analyze and update column data statistics of column(s) of one or all tables in a schema",
96 : "ANALYZE ident [ . ident [ column_list ] ]",
97 : "ident,column_list",
98 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-manipulation/analyze-statement/"},
99 : {"CALL",
100 : "Call a stored procedure",
101 : "CALL qname '(' [ scalar_expression [',' ...] ] ')' | CALL ident '.' ident",
102 : NULL,
103 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/flow-of-control/"},
104 : {"COMMENT",
105 : "Add, update or remove a comment or description for a database object",
106 : "COMMENT ON { SCHEMA | TABLE | VIEW | COLUMN | INDEX | SEQUENCE | function_type }\n"
107 : " qname IS { 'my description text' | NULL | '' }",
108 : "function_type,qname",
109 : NULL},
110 : {"COMMIT",
111 : "Commit the current transaction",
112 : "COMMIT [ WORK ] [ AND CHAIN | AND NO CHAIN ]",
113 : NULL,
114 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/transactions/"},
115 : {"COPY BINARY",
116 : "Append binary representations into a table",
117 : "COPY [{BIG | LITTLE | NATIVE} ENDIAN] BINARY INTO qname [column_list] FROM string [',' ...] [ON { CLIENT | SERVER }]",
118 : "qname,column_list",
119 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-loading/binary-loading/"},
120 : {"COPY INTO",
121 : "Write query result data to a csv file or standard output stream",
122 : "COPY query_expression INTO [STDOUT | string [ON { CLIENT | SERVER }]] [separators] [NULL [AS] string]",
123 : "query_expression,separators",
124 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-loading/export_data/"},
125 : {"COPY INTO BINARY",
126 : "Write query result data to binary files",
127 : "COPY query_expression INTO [{BIG | LITTLE | NATIVE} ENDIAN] BINARY string_commalist [ON { CLIENT | SERVER }]",
128 : "query_expression",
129 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-loading/export_data/"},
130 : {"COPY INTO FROM",
131 : "Read csv file(s) or standard input stream data and insert into a table",
132 : "COPY [nrofrecords] INTO qname [column_list] FROM string [',' ...] [headerlist] [ON { CLIENT | SERVER }] [ separators]\n"
133 : " [NULL [AS] string] [BEST EFFORT] [FWF '(' integer [',' ...] ')'\n"
134 : "COPY [nrofrecords] INTO qname [column_list] FROM STDIN [headerlist] [ separators]\n"
135 : " [NULL [AS] string] [BEST EFFORT]\n",
136 : "nrofrecords,qname,column_list,headerlist,separators",
137 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-loading/copy-from/"},
138 : {"COPY LOADER",
139 : "Copy into using a user supplied parsing function",
140 : "COPY LOADER INTO qname FROM qname '(' [ scalar_expression ... ] ')'",
141 : "qname,scalar_expression",
142 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-loading/loader-functions/"},
143 : {"CREATE AGGREGATE",
144 : "Create a user-defined aggregate function. The body of the aggregate function\n"
145 : "can also be defined in other programming languages such as Python, R, C or CPP.",
146 : "CREATE [ OR REPLACE ] AGGREGATE [ FUNCTION ] qname '(' { '*' | [ param [',' ...]] } ')'\n"
147 : " RETURNS function_return_data_type\n"
148 : " EXTERNAL NAME ident ',' ident\n"
149 : "CREATE [ OR REPLACE ] AGGREGATE [ FUNCTION ] qname '(' { '*' | [ param [',' ...]] } ')'\n"
150 : " RETURNS function_return_data_type\n"
151 : " LANGUAGE language_keyword external_code",
152 : "qname,param,function_return_data_type,ident,language_keyword,external_code",
153 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/function-definitions/"},
154 : {"CREATE FILTER FUNCTION",
155 : "Create a user-defined filter function. Currently only MAL definitions\n"
156 : "CREATE [ OR REPLACE ] FILTER [ FUNCTION ] qname '(' { '*' | [ param [',' ...]] } ')'\n"
157 : " RETURNS function_return_data_type\n"
158 : " EXTERNAL NAME ident ',' ident",
159 : "qname,param,function_return_data_type,ident",
160 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/function-definitions/"},
161 : {"CREATE FUNCTION",
162 : "Create a user-defined function (UDF). The body of the function can be defined in\n"
163 : " PL/SQL or programming languages such as Python, R, C or CPP when embedded on the server.",
164 : "CREATE [ OR REPLACE ] FUNCTION qname '(' { '*' | [ param [',' ...]] } ')'\n"
165 : " RETURNS function_return_data_type\n"
166 : " BEGIN [ ATOMIC ] statement [ ';' ...] END\n"
167 : "CREATE [ OR REPLACE ] FUNCTION qname '(' { '*' | [ param [',' ...]] } ')'\n"
168 : " RETURNS function_return_data_type\n"
169 : " EXTERNAL NAME ident ',' ident\n"
170 : "CREATE [ OR REPLACE ] FUNCTION qname '(' { '*' | [ param [',' ...]] } ')'\n"
171 : " RETURNS function_return_data_type\n"
172 : " LANGUAGE language_keyword external_code",
173 : "qname,param,function_return_data_type,statement,ident,language_keyword,external_code",
174 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/function-definitions/"},
175 : {"CREATE INDEX",
176 : "Create a hint for a secondary index on a column or set of columns of a table",
177 : "CREATE [ UNIQUE | ORDERED | IMPRINTS ] INDEX ident ON qname '(' ident_list ')'",
178 : NULL,
179 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/index-definitions/"},
180 : {"CREATE LOADER",
181 : "Create a custom (external) data loader function. The body is defined in Python language",
182 : "CREATE [ OR REPLACE ] LOADER [ FUNCTION ] qname '(' [ param [',' ...]] ')'\n"
183 : " LANGUAGE PYTHON external_code",
184 : "qname,param,external_code",
185 : "See also https://www.monetdb.org/documentation/user-guide/blog-archive/python-loader/"},
186 : {"CREATE MERGE TABLE",
187 : "",
188 : "CREATE MERGE TABLE [ IF NOT EXISTS ] qname table_source [ partition_by ]",
189 : "table_source,partition_by",
190 : "See also https://www.monetdb.org/documentation/admin-guide/distributed-query-processing/"},
191 : {"CREATE PROCEDURE",
192 : "Create a user-defined procedure",
193 : "CREATE [ OR REPLACE ] PROCEDURE qname '(' { '*' | [ param [',' ...]] } ')'\n"
194 : " BEGIN [ ATOMIC ] procedure_statement [ ';' ...] END\n"
195 : "CREATE [ OR REPLACE ] PROCEDURE qname '(' { '*' | [ param [',' ...]] } ')'\n"
196 : " EXTERNAL NAME ident ',' ident",
197 : "qname,param,procedure_statement,ident",
198 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/procedure-definitions/"},
199 : {"CREATE REMOTE TABLE",
200 : "",
201 : "CREATE REMOTE TABLE [ IF NOT EXISTS ] qname ON string [WITH [USER 'username'] [[ENCRYPTED] PASSWORD 'password']]",
202 : NULL,
203 : "remote name should match mapi:monetdb://host:port/database[/schema[/table]]"},
204 : {"CREATE UNLOGGED TABLE",
205 : "Create a new unlogged table",
206 : "CREATE UNLOGGED TABLE [ IF NOT EXISTS ] qname table_source [STORAGE ident string]\n"
207 : "CREATE UNLOGGED TABLE [ IF NOT EXISTS ] qname FROM LOADER function_ref\n"
208 : "CREATE UNLOGGED TABLE [ IF NOT EXISTS ] qname table_source [on_commit]",
209 : "table_source,on_commit,function_ref",
210 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/table-definition/"},
211 : {"CREATE REPLICA TABLE",
212 : "",
213 : "CREATE REPLICA TABLE [ IF NOT EXISTS ] qname table_source",
214 : NULL,
215 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/table-definition/"},
216 : {"CREATE ROLE",
217 : "Create a new role. You can grant privileges to a role and next\n"
218 : "grant a role (or multiple roles) to specific users",
219 : "CREATE ROLE ident [ WITH ADMIN { CURRENT_USER | CURRENT_ROLE } ]",
220 : "ident",
221 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/privileges/#roles"},
222 : {"CREATE SCHEMA",
223 : "Create a new schema",
224 : "CREATE SCHEMA [ IF NOT EXISTS ] schema_name [default_char_set] [path_spec] [schema_element]",
225 : "schema_name,default_char_set,path_spec,schema_element",
226 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/schema-definitions/"},
227 : {"CREATE SEQUENCE",
228 : "Define a new integer number sequence generator",
229 : "CREATE SEQUENCE qname [AS seq_int_datatype] [START WITH bigint] [INCREMENT BY bigint]\n"
230 : " [MINVALUE bigint | NO MINVALUE] [MAXVALUE bigint | NO MAXVALUE] [CACHE bigint] [[NO] CYCLE]",
231 : "seq_int_datatype",
232 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-types/serial-types/"},
233 : {"CREATE TABLE",
234 : "Create a new table",
235 : "CREATE TABLE [ IF NOT EXISTS ] qname table_source [STORAGE ident string]\n"
236 : "CREATE TABLE [ IF NOT EXISTS ] qname FROM LOADER function_ref\n"
237 : "CREATE [ LOCAL | GLOBAL ] { TEMPORARY | TEMP } TABLE [ IF NOT EXISTS ] qname table_source [on_commit]",
238 : "table_source,on_commit,function_ref",
239 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/table-definition/"},
240 : {"CREATE TRIGGER",
241 : "Define a triggered action for a table data update event",
242 : "CREATE [ OR REPLACE ] TRIGGER ident { BEFORE | AFTER }\n"
243 : " { INSERT | DELETE | TRUNCATE | UPDATE [ OF ident_list ] | LOGIN }\n"
244 : " [ ON qname ] [ REFERENCING trigger_reference [...] ] triggered_action",
245 : "qname,ident_list,trigger_reference,triggered_action",
246 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/trigger-definition/"},
247 : {"CREATE TYPE",
248 : "Add user defined type to the type system ",
249 : "CREATE TYPE qname EXTERNAL NAME ident",
250 : NULL,
251 : NULL},
252 : {"CREATE USER",
253 : "Create a new database user",
254 : "CREATE USER ident WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string NAME string [SCHEMA ident] [SCHEMA PATH string]\n"
255 : "[MAX_MEMORY posbytes | MAX_MEMORY sizestr | NO MAX_MEMORY] [MAX_WORKERS poscount | NO MAX_WORKERS]\n"
256 : "[OPTIMIZER string] [DEFAULT ROLE ident]",
257 : "ident",
258 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/privileges/"},
259 : {"CREATE VIEW",
260 : "Create a new view",
261 : "CREATE [ OR REPLACE ] VIEW qname [ column_list ] AS { query_expression | '(' query_expression ')' }\n"
262 : "[ WITH CHECK OPTION ]",
263 : "qname,column_list,query_expression",
264 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/view-definition/"},
265 : {"CREATE WINDOW",
266 : "Create a user-defined window function. Currently only MAL definitions\n"
267 : "are supported.",
268 : "CREATE [ OR REPLACE ] WINDOW [ FUNCTION ] qname '(' { '*' | [ param [',' ...]] } ')'\n"
269 : " RETURNS function_return_data_type\n"
270 : " EXTERNAL NAME ident ',' ident",
271 : "qname,param,function_return_data_type,ident",
272 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-manipulation/window-expressions/"},
273 : {"CURRENT_DATE",
274 : "Pseudo column or function to get the current date",
275 : "CURRENT_DATE [ '(' ')' ]",
276 : NULL,
277 : NULL},
278 : {"CURRENT_ROLE",
279 : "Pseudo column to get the current role name",
280 : "CURRENT_ROLE",
281 : NULL,
282 : NULL},
283 : {"CURRENT_SCHEMA",
284 : "Pseudo column to get the current schema name",
285 : "CURRENT_SCHEMA",
286 : NULL,
287 : NULL},
288 : {"CURRENT_TIME",
289 : "Pseudo column or function to get the current time including timezone",
290 : "CURRENT_TIME [ '(' ')' ]",
291 : NULL,
292 : NULL},
293 : {"CURRENT_TIMESTAMP",
294 : "Pseudo column or function to get the current timestamp including timezone",
295 : "CURRENT_TIMESTAMP [ '(' ')' ] | NOW [ '(' ')' ]",
296 : NULL,
297 : NULL},
298 : {"CURRENT_TIMEZONE",
299 : "Pseudo column to get the current timezone offset as a second interval",
300 : "CURRENT_TIMEZONE",
301 : NULL,
302 : NULL},
303 : {"CURRENT_USER",
304 : "Pseudo column to get the current user name",
305 : "CURRENT_USER | USER",
306 : NULL,
307 : NULL},
308 : {"DEALLOCATE",
309 : "Deallocates a prepared statement or all from the client's session cache",
310 : "DEALLOCATE [ PREPARE ] { intnr | ** | ALL }",
311 : NULL,
312 : NULL},
313 : {"DECLARE",
314 : "Define a local variable",
315 : "DECLARE ident_list data_type",
316 : "ident_list,data_type",
317 : NULL},
318 : {"DELETE",
319 : "Remove data rows from a table",
320 : "[ WITH cte_list ] DELETE FROM qname [ [AS] ident ] [ WHERE search_condition ]",
321 : "cte_list,search_condition",
322 : NULL},
323 : {"DROP AGGREGATE",
324 : "",
325 : "DROP ALL AGGREGATE [ FUNCTION ] qname [ RESTRICT | CASCADE ]\n"
326 : "DROP AGGREGATE [ FUNCTION ] [ IF EXISTS ] qname [ '(' [ data_type [',' ...]] ')' ] [ RESTRICT | CASCADE ]",
327 : NULL,
328 : NULL},
329 : {"DROP FILTER FUNCTION",
330 : "",
331 : "DROP ALL FILTER [ FUNCTION ] qname [ RESTRICT | CASCADE ]\n"
332 : "DROP FILTER [ FUNCTION ] [ IF EXISTS ] qname [ '(' [ data_type [',' ...]] ')' ] [ RESTRICT | CASCADE ]",
333 : NULL,
334 : NULL},
335 : {"DROP FUNCTION",
336 : "",
337 : "DROP ALL FUNCTION qname [ RESTRICT | CASCADE ]\n"
338 : "DROP FUNCTION [ IF EXISTS ] qname [ '(' [ data_type [',' ...]] ')' ] [ RESTRICT | CASCADE ]",
339 : NULL,
340 : NULL},
341 : {"DROP INDEX",
342 : "",
343 : "DROP INDEX qname",
344 : NULL,
345 : NULL},
346 : {"DROP LOADER",
347 : "",
348 : "DROP ALL LOADER [ FUNCTION ] qname [ RESTRICT | CASCADE ]\n"
349 : "DROP LOADER [ FUNCTION ] [ IF EXISTS ] qname [ '(' [ data_type [',' ...]] ')' ] [ RESTRICT | CASCADE ]",
350 : NULL,
351 : NULL},
352 : {"DROP PROCEDURE",
353 : "",
354 : "DROP ALL PROCEDURE qname [ RESTRICT | CASCADE ]\n"
355 : "DROP PROCEDURE [ IF EXISTS ] qname [ '(' [ data_type [',' ...]] ')' ] [ RESTRICT | CASCADE ]",
356 : NULL,
357 : NULL},
358 : {"DROP ROLE",
359 : "",
360 : "DROP ROLE ident",
361 : NULL,
362 : NULL},
363 : {"DROP SCHEMA",
364 : "",
365 : "DROP SCHEMA [ IF EXISTS ] ident [ RESTRICT | CASCADE ]",
366 : NULL,
367 : NULL},
368 : {"DROP SEQUENCE",
369 : "",
370 : "DROP SEQUENCE qname",
371 : NULL,
372 : NULL},
373 : {"DROP TABLE",
374 : "",
375 : "DROP TABLE [ IF EXISTS ] qname [ RESTRICT | CASCADE ]",
376 : NULL,
377 : NULL},
378 : {"DROP TRIGGER",
379 : "",
380 : "DROP TRIGGER [ IF EXISTS ] qname",
381 : NULL,
382 : NULL},
383 : {"DROP TYPE",
384 : "",
385 : "DROP TYPE qname [ RESTRICT | CASCADE ]",
386 : NULL,
387 : NULL},
388 : {"DROP USER",
389 : "",
390 : "DROP USER ident",
391 : NULL,
392 : NULL},
393 : {"DROP VIEW",
394 : "",
395 : "DROP VIEW [ IF EXISTS ] qname [ RESTRICT | CASCADE ]",
396 : NULL,
397 : NULL},
398 : {"DROP WINDOW",
399 : "",
400 : "DROP ALL WINDOW [ FUNCTION ] qname [ RESTRICT | CASCADE ]\n"
401 : "DROP WINDOW [ FUNCTION ] [ IF EXISTS ] qname [ '(' [ data_type [',' ...]] ')' ] [ RESTRICT | CASCADE ]",
402 : NULL,
403 : NULL},
404 : {"EXECUTE",
405 : "Execute a prepared SQL statement with supplied parameter values",
406 : "EXECUTE { intnr | ** } '(' [ value [, ...] ] ')'",
407 : NULL,
408 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-manipulation/prepare-statement/"},
409 : {"EXPLAIN",
410 : "Give MAL execution plan for the SQL statement",
411 : "EXPLAIN statement",
412 : NULL,
413 : "See also https://www.monetdb.org/documentation/admin-guide/debugging-features/explain-sql-stmt/"},
414 : {"EXTRACT",
415 : "Built-in function",
416 : "EXTRACT '(' { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | CENTURY | DECADE | QUARTER | WEEK | DOW | DOY | EPOCH } FROM scalar_expression ')'",
417 : NULL,
418 : NULL},
419 : {"INSERT",
420 : "Add data rows to a table",
421 : "[ WITH cte_list ] INSERT INTO qname [ column_list ]\n"
422 : " [ { DEFAULT VALUES | VALUES row_values | query_expression } ]",
423 : "cte_list,column_list,row_values,query_expression",
424 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-manipulation/table-updates/"},
425 : {"GRANT",
426 : "Define access privileges",
427 : "GRANT privileges TO grantee [',' ...] [ WITH GRANT OPTION ]\n"
428 : "GRANT role [',' ...] TO grantee [',' ...] [ WITH ADMIN OPTION]",
429 : "privileges,table_privileges,global_privileges,role,grantee",
430 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/privileges/#grant-and-revoke"},
431 : {"LOCALTIME",
432 : "Pseudo column or function to get the current client time excluding timezone",
433 : "LOCALTIME [ '(' ')' ]",
434 : NULL,
435 : NULL},
436 : {"LOCALTIMESTAMP",
437 : "Pseudo column or function to get the current client timestamp excluding timezone",
438 : "LOCALTIMESTAMP [ '(' ')' ]",
439 : NULL,
440 : NULL},
441 : {"MERGE",
442 : "",
443 : "[ WITH cte_list ] MERGE INTO qname [ [AS] ident ] USING table_ref [ [AS] ident ] ON search_condition merge_list",
444 : "cte_list,table_ref,search_condition,merge_list",
445 : "See also: https://www.monetdb.org/documentation/user-guide/blog-archive/merge-statements/"},
446 : {"PLAN",
447 : "Give relational execution plan for the SQL statement",
448 : "PLAN statement",
449 : NULL,
450 : "See also https://www.monetdb.org/documentation/admin-guide/debugging-features/plan-sql-stmt/"},
451 : {"PREPARE",
452 : "Prepare a SQL DML statement with optional question-mark parameter markers",
453 : "PREPARE statement",
454 : NULL,
455 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-manipulation/prepare-statement/"},
456 : {"RELEASE SAVEPOINT",
457 : "",
458 : "RELEASE SAVEPOINT ident",
459 : NULL,
460 : NULL},
461 : {"REVOKE",
462 : "Remove some privileges",
463 : "REVOKE [GRANT OPTION FOR] privileges FROM { grantee [',' ...] | CURRENT_USER | CURRENT_ROLE }\n"
464 : "REVOKE [ADMIN OPTION FOR] role [',' ...] FROM { grantee [',' ...] | CURRENT_USER | CURRENT_ROLE }",
465 : "privileges,table_privileges,global_privileges,grantee,role",
466 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/privileges/#grant-and-revoke"},
467 : {"ROLLBACK",
468 : "Rollback the current transaction",
469 : "ROLLBACK [WORK] [ AND CHAIN | AND NO CHAIN ] [TO SAVEPOINT ident]",
470 : NULL,
471 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/transactions/"},
472 : {"SAVEPOINT",
473 : NULL,
474 : "SAVEPOINT ident",
475 : NULL,
476 : NULL},
477 : {"SELECT",
478 : "",
479 : "[ WITH cte_list ]\n"
480 : "SELECT [ ALL | DISTINCT [ ON { expression [',' ...] } ] ]\n"
481 : "[ '*' | expression [ [ AS ] output_name ] [',' ...] ]\n"
482 : "[ FROM from_item [',' ...] ]\n"
483 : "[ WINDOW window_definition [',' ...] ]\n"
484 : "[ WHERE condition ]\n"
485 : "[ GROUP BY group_by_element [',' ...] ]\n"
486 : "[ HAVING condition [',' ...] ]\n"
487 : "[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] [ CORRESPONDING ] select ]\n"
488 : "[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [',' ...] ]\n"
489 : "[ limit_offset_clause | offset_fetchfirst_clause ]\n"
490 : "[ SAMPLE size [ SEED size ] ]",
491 : "cte_list,expression,group_by_element,window_definition",
492 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-manipulation/table-expressions/"},
493 : {"SET",
494 : "Assign a value to a variable or column",
495 : "SET ident '=' simple_atom",
496 : "simple_atom",
497 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/variable-definitions/"},
498 : {"SET LOCAL TRANSACTION",
499 : "",
500 : "SET LOCAL TRANSACTION [ transactionmode ]",
501 : "transactionmode",
502 : "See also https://www.monetdb.org/documentation/user-guide/sql-summary/#set-local-transaction"},
503 : {"SET ROLE",
504 : "Change current role",
505 : "SET ROLE ident",
506 : NULL,
507 : NULL},
508 : {"SET SCHEMA",
509 : "Change current schema",
510 : "SET SCHEMA ident",
511 : NULL,
512 : NULL},
513 : {"SET SESSION AUTHORIZATION",
514 : "",
515 : "SET SESSION AUTHORIZATION ident",
516 : NULL,
517 : NULL},
518 : {"SET TIME ZONE",
519 : NULL,
520 : "SET TIME ZONE interval",
521 : "interval",
522 : NULL},
523 : {"SET TIME ZONE LOCAL",
524 : NULL,
525 : "SET TIME ZONE LOCAL",
526 : NULL,
527 : NULL},
528 : {"SET TRANSACTION",
529 : "",
530 : "SET TRANSACTION [ transactionmode ]",
531 : "transactionmode",
532 : "See also https://www.monetdb.org/documentation/user-guide/sql-summary/#set-transaction"},
533 : {"SET USER",
534 : "Change current user",
535 : "SET USER '=' ident",
536 : NULL,
537 : NULL},
538 : {"START TRANSACTION",
539 : "Change transaction mode from auto-commit to user controlled commit/rollback",
540 : "{ START | BEGIN } TRANSACTION [ transactionmode ]",
541 : "transactionmode",
542 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/transactions/"},
543 : {"TABLE JOINS",
544 : "",
545 : "'(' joined_table ') |\n"
546 : "table_ref CROSS JOIN table_ref ')' |\n"
547 : "table_ref NATURAL [ INNER | LEFT | RIGHT | FULL ] JOIN table_ref |\n"
548 : "table_ref UNION JOIN table_ref { ON search_condition | USING column_list } |\n"
549 : "table_ref [ INNER | LEFT | RIGHT | FULL ] JOIN table_ref { ON search_condition | USING column_list }",
550 : "table_ref,search_condition,column_list",
551 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-manipulation/table-expressions/"},
552 : {"TRACE",
553 : "Give execution trace for the SQL statement",
554 : "TRACE statement",
555 : NULL,
556 : "See also https://www.monetdb.org/documentation/admin-guide/debugging-features/trace-sql-stmt/"},
557 : {"TRUNCATE",
558 : "Remove all rows from a table",
559 : "TRUNCATE [ TABLE ] qname [ CONTINUE IDENTITY | RESTART IDENTITY ] [ CASCADE | RESTRICT ]",
560 : "",
561 : NULL},
562 : {"UPDATE",
563 : "Change data in a table",
564 : "[ WITH cte_list ] UPDATE qname [ [AS] ident ] SET assignment_list\n"
565 : " [ FROM from_item ] [ WHERE search_condition ]",
566 : "cte_list,assignment_list,search_condition",
567 : NULL},
568 : {"VALUES",
569 : "Specify a list of row values",
570 : "VALUES row_values",
571 : "row_values",
572 : NULL},
573 : {"WINDOW FUNCTIONS",
574 : "",
575 : "{ window_aggregate_function | window_rank_function } OVER { ident | '(' window_specification ')' }",
576 : "window_aggregate_function,window_rank_function,window_specification",
577 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-manipulation/window-expressions/"},
578 : {NULL, NULL, NULL, NULL, NULL} /* End of list marker */
579 : };
580 :
581 : SQLhelp sqlhelp2[] = {
582 : // The subgrammar rules
583 : {"and_exp",
584 : NULL,
585 : "{ and_exp AND pred_exp | pred_exp }",
586 : "pred_exp",
587 : NULL},
588 : {"assignment_list",
589 : NULL,
590 : "column '=' DEFAULT | column '=' search_condition | '(' column [',' ...] ')' '=' subquery",
591 : "search_condition,column,subquery",
592 : NULL},
593 : {"authid",
594 : NULL,
595 : "restricted ident",
596 : NULL,
597 : NULL},
598 : {"case_statement",
599 : "Case statement for procedures/functions",
600 : "CASE scalar_expression [ when_statement ...] [ELSE procedure_statement ... ] END CASE",
601 : NULL,
602 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/flow-of-control/"},
603 : {"column_def",
604 : NULL,
605 : "ident { data_type [ column_option ... ] | SERIAL | BIGSERIAL }",
606 : "ident,data_type,column_option",
607 : NULL},
608 : {"column_list",
609 : NULL,
610 : "'(' ident [',' ...] ')'",
611 : NULL,
612 : NULL},
613 : {"column_option",
614 : NULL,
615 : "DEFAULT value | column_constraint | generated_column",
616 : "column_constraint,generated_column",
617 : NULL},
618 : {"column_option_list",
619 : NULL,
620 : "ident WITH OPTIONS '(' column_constraint ')' [',' ...]",
621 : "column_constraint",
622 : NULL},
623 : {"column_constraint",
624 : NULL,
625 : "[ CONSTRAINT ident ] { NOT NULL | NULL | CHECK '(' search_condition ')' |\n"
626 : " PRIMARY KEY | UNIQUE | UNIQUE NULLS [ NOT ] DISTINCT |\n"
627 : " REFERENCES qname [ column_list ] [ match_option ] [ reference_actions ] }",
628 : "column_list,search_condition,reference_actions,match_option",
629 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/table-elements/"},
630 : {"control_statement",
631 : NULL,
632 : "call_procedure | while_statement | if_statement | case_statement | return_statement",
633 : "call_procedure,while_statement,if_statement,case_statement,return_statement",
634 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/flow-of-control/"},
635 : {"datetime_type",
636 : NULL,
637 : "DATE | TIME [ time_precision ] [ WITH TIME ZONE ] |\n"
638 : " TIMESTAMP [ timestamp_precision ] [ WITH TIME ZONE ]",
639 : "time_precision,timestamp_precision",
640 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-types/temporal-types/"},
641 : {"data_type",
642 : NULL,
643 : "BOOLEAN | BOOL | TINYINT | SMALLINT | INT | INTEGER | BIGINT | HUGEINT |\n"
644 : " { DECIMAL | DEC | NUMERIC | FLOAT } [ '(' nonzero [',' nonzero ] ')' ] |\n"
645 : " REAL | DOUBLE [ PRECISION ] |\n"
646 : " { VARCHAR | CHARACTER VARYING } '(' nonzero ')' |\n"
647 : " { CHAR | CHARACTER [ LARGE OBJECT ] | CLOB | TEXT | STRING | JSON | URL } [ '(' nonzero ')' ] |\n"
648 : " { BINARY LARGE OBJECT | BLOB } [ '(' nonzero ')' ] |\n"
649 : " UUID | INET | datetime_type | interval_type | geometry_type",
650 : "datetime_type,interval_type,geometry_type",
651 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-types/"},
652 : {"default_char_set",
653 : NULL,
654 : "DEFAULT CHARACTER SET ident",
655 : NULL,
656 : NULL},
657 : {"drop_table_element",
658 : NULL,
659 : "{ CONSTRAINT | TABLE | COLUMN } ident [ RESTRICT | CASCADE ]",
660 : NULL,
661 : NULL},
662 : {"end_time",
663 : NULL,
664 : "SECOND timestamp_precision\n,timestamp_precision",
665 : NULL,
666 : NULL},
667 : {"function_return_data_type",
668 : NULL,
669 : "data_type | TABLE '(' ident data_type [',' ...] ')'",
670 : NULL,
671 : NULL},
672 : {"function_type",
673 : NULL,
674 : "{ FUNCTION | PROCEDURE | { { AGGREGATE | FILTER | LOADER | WINDOW } [ FUNCTION ] } }",
675 : NULL,
676 : NULL},
677 : {"generated_column",
678 : NULL,
679 : "AUTO_INCREMENT | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ '('\n"
680 : " [AS seq_int_datatype] [START WITH bigint] [INCREMENT BY bigint]\n"
681 : " [MINVALUE bigint | NO MINVALUE] [MAXVALUE bigint | NO MAXVALUE] [CACHE bigint] [[NO] CYCLE] ')' ]",
682 : "seq_int_datatype",
683 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-types/serial-types/"},
684 : {"global_privileges",
685 : NULL,
686 : "{ COPY FROM | COPY INTO } [',' ...]",
687 : NULL,
688 : NULL},
689 : {"grantee",
690 : NULL,
691 : "{ PUBLIC | authid } ",
692 : "authid",
693 : NULL},
694 : {"group_by_element",
695 : NULL,
696 : "{ expression | '(' ')' | ROLLUP '(' ident [',' ... ] ')' | CUBE '(' ident [',' ... ] ')'\n"
697 : "| GROUPING SETS '(' group_by_element [',' ... ] ')' }",
698 : "expression",
699 : NULL},
700 : {"headerlist",
701 : NULL,
702 : "'(' { ident [string] } [',' ...] ')'",
703 : NULL,
704 : NULL},
705 : {"ident",
706 : "An identifier name. Use double quote's around the identifier name to include\n"
707 : " mixed/upper case letters and/or special characters",
708 : NULL,
709 : NULL,
710 : NULL},
711 : {"ident_list",
712 : NULL,
713 : "ident [',' ...]",
714 : "ident",
715 : NULL},
716 : {"if_statement",
717 : NULL,
718 : "IF search_condition THEN procedure_statement ...\n"
719 : "[ELSE IF search_condition THEN procedure_statement ... ]...\n"
720 : "[ ELSE procedure_statement ... ] END IF",
721 : "search_condition,procedure_statement",
722 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/flow-of-control/"},
723 : {"seq_int_datatype",
724 : NULL,
725 : "BIGINT | INTEGER | INT | SMALLINT | TINYINT",
726 : NULL,
727 : NULL},
728 : {"interval",
729 : NULL,
730 : "INTERVAL [ '+' | '-' ] string start_field TO end_field",
731 : "start_field,end_field",
732 : NULL},
733 : {"interval_type",
734 : NULL,
735 : "INTERVAL { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND [time_precision] | start_field TO end_field }",
736 : "time_precision,start_field,end_field",
737 : NULL},
738 : {"isolevel",
739 : NULL,
740 : "READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE",
741 : NULL,
742 : NULL},
743 : {"language_keyword",
744 : NULL,
745 : "C | CPP | R | PYTHON | PYTHON3",
746 : NULL,
747 : NULL},
748 : {"match_option",
749 : NULL,
750 : "MATCH [ FULL | PARTIAL | SIMPLE ]",
751 : NULL,
752 : NULL},
753 : {"merge_list",
754 : NULL,
755 : "merge_clause [ merge_clause ]",
756 : "merge_clause",
757 : NULL},
758 : {"merge_clause",
759 : NULL,
760 : "{ WHEN NOT MATCHED THEN INSERT [ column_list ] [ { VALUES row_values | DEFAULT VALUES } ]\n"
761 : "| WHEN MATCHED THEN { UPDATE SET assignment_list | DELETE } }",
762 : "column_list,row_values,assignment_list",
763 : NULL},
764 : {"nrofrecords",
765 : NULL,
766 : "OFFSET integer | integer RECORDS | integer OFFSET integer RECORDS | integer RECORDS OFFSET integer",
767 : NULL,
768 : NULL},
769 : {"on_commit",
770 : NULL,
771 : "ON COMMIT { DELETE ROWS | PRESERVE ROWS | DROP }",
772 : NULL,
773 : NULL},
774 : {"partition_by",
775 : NULL,
776 : "PARTITION BY { RANGE | VALUES } { ON '(' ident ')' | USING '(' query_expression ')' }",
777 : "query_expression",
778 : "See also: https://www.monetdb.org/documentation/user-guide/blog-archive/update-mergetables/"},
779 : {"partition_spec",
780 : NULL,
781 : "{ IN '(' partition_list ')' [ WITH NULL VALUES ]\n"
782 : "| FROM partition_range_from TO partition_range_to [ WITH NULL VALUES ]\n"
783 : "| FOR NULL VALUES }",
784 : "partition_list,partition_range_from,partition_range_to",
785 : "See also: https://www.monetdb.org/documentation/user-guide/blog-archive/update-mergetables/"},
786 : {"param",
787 : NULL,
788 : "ident data_type",
789 : NULL,
790 : NULL},
791 : {"partition_list",
792 : NULL,
793 : "query_expression [',' ...]",
794 : "query_expression",
795 : NULL},
796 : {"partition_range_from",
797 : NULL,
798 : "{ RANGE MINVALUE | query_expression }",
799 : "query_expression",
800 : NULL},
801 : {"partition_range_to",
802 : NULL,
803 : "{ RANGE MAXVALUE | query_expression }",
804 : "query_expression",
805 : NULL},
806 : {"pred_exp",
807 : NULL,
808 : "{ NOT pred_exp | predicate }",
809 : "predicate",
810 : NULL},
811 : {"predicate",
812 : NULL,
813 : "comparison_predicate | between_predicate | like_predicate | test_for_null | in_predicate | all_or_any_predicate | existence_test | filter_exp | scalar_exp",
814 : NULL,
815 : NULL},
816 : {"privileges",
817 : NULL,
818 : "table_privileges | EXECUTE ON function_type qname | global_privileges",
819 : "function_type,table_privileges,global_privileges",
820 : NULL},
821 : {"procedure_statement",
822 : NULL,
823 : "{ update_statement | declare_statement | set_statement | control_statement | select_single_row } ';'",
824 : "update_statement,declare_statement,set_statement,control_statement,select_single_row",
825 : NULL},
826 : {"select_single_row",
827 : NULL,
828 : "SELECT [ ALL | DISTINCT ] column_exp_commalist INTO select_target_list [ from_clause ] [ window_clause ] [ where_clause ] [ group_by_clause ] [ having_clause ]",
829 : "column_exp_commalist,select_target_list,from_clause,window_clause,where_clause,group_by_clause,having_clause",
830 : NULL},
831 : {"query_expression",
832 : NULL,
833 : "select_no_parens [ order_by_clause ] [ limit_offset_clause | offset_fetchfirst_clause ] [ sample_clause ]",
834 : "select_no_parens",
835 : NULL},
836 : {"select_no_parens",
837 : NULL,
838 : "{ SELECT [ ALL | DISTINCT ] column_exp_commalist [ from_clause ] [ window_clause ] [ where_clause ] [ group_by_clause ] [ having_clause ]\n"
839 : "| select_no_parens { UNION | EXCEPT | INTERSECT } [ ALL | DISTINCT ] [ corresponding ] select_no_parens\n"
840 : "| '(' select_no_parens ')' }",
841 : "column_exp_commalist,from_clause,window_clause,where_clause,group_by_clause,having_clause,corresponding",
842 : NULL},
843 : {"order_by_clause",
844 : NULL,
845 : "ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [',' ...]",
846 : "",
847 : NULL},
848 : {"limit_offset_clause",
849 : NULL,
850 : "[ LIMIT { count | param } ] [ OFFSET { count | param } ]",
851 : "",
852 : NULL},
853 : {"offset_fetchfirst_clause",
854 : NULL,
855 : "[ OFFSET { count | param } [ {ROW|ROWS} ] ] [ FETCH {FIRST|NEXT} [ count | param ] {ROW|ROWS} ONLY ]",
856 : "",
857 : NULL},
858 : {"corresponding",
859 : NULL,
860 : "{ CORRESPONDING | CORRESPONDING BY '(' column_ref_commalist ')' }",
861 : "column_ref_commalist",
862 : NULL},
863 : {"qname",
864 : NULL,
865 : "ident [ '.' ident ['.' ident]]",
866 : NULL,
867 : NULL},
868 : {"ref_action",
869 : NULL,
870 : "RESTRICT | CASCADE | NO ACTION | SET NULL | SET DEFAULT",
871 : NULL,
872 : NULL},
873 : {"ref_on_delete",
874 : NULL,
875 : "ON DELETE ref_action",
876 : NULL,
877 : NULL},
878 : {"ref_on_update",
879 : NULL,
880 : "ON UPDATE ref_action",
881 : NULL,
882 : NULL},
883 : {"reference_actions",
884 : NULL,
885 : "ref_on_delete | ref_on_update | ref_on_delete ref_on_update | ref_on_update ref_on_delete",
886 : NULL,
887 : NULL},
888 : {"return_statement",
889 : "",
890 : "RETURN { query_expression | search_condition | TABLE '(' query_expression ')' | NULL }",
891 : "query_expression,search_condition",
892 : NULL},
893 : {"row_values",
894 : NULL,
895 : "'(' atom [ ',' atom ]... ')' [ ',' row_values ] ...",
896 : "atom",
897 : NULL},
898 : {"schema_name",
899 : NULL,
900 : "ident | [ident] AUTHORIZATION authorization_ident",
901 : NULL,
902 : NULL},
903 : {"schema_element",
904 : NULL,
905 : "grant | revoke | create_statement | drop_statement | alter_statement",
906 : NULL,
907 : NULL},
908 : {"search_condition",
909 : NULL,
910 : "{ search_condition OR and_exp | and_exp }",
911 : "and_exp",
912 : NULL},
913 : {"separators",
914 : NULL,
915 : "[USING] DELIMITERS field_sep_string [',' record_sep_string [',' quote_string]]",
916 : NULL,
917 : NULL},
918 : {"split_part",
919 : NULL,
920 : "SPLIT_PART '(' string ',' delimiter_string ',' field_index ')'",
921 : NULL,
922 : NULL,},
923 : {"table_constraint",
924 : NULL,
925 : "[ CONSTRAINT ident ] { CHECK '(' search_condition ')' |\n"
926 : " PRIMARY KEY column_list | UNIQUE column_list | UNIQUE NULLS [ NOT ] DISTINCT column_list |\n"
927 : " FOREIGN KEY column_list REFERENCES qname [ column_list ] [ match_option ] [ reference_actions ] }",
928 : "column_list,search_condition,reference_actions,match_option",
929 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/table-elements/"},
930 : {"table_element",
931 : NULL,
932 : "column_def | table_constraint | column_option_list | LIKE qname",
933 : "column_def,table_constraint,column_option_list",
934 : NULL},
935 : {"table_name",
936 : NULL,
937 : "[AS] ident ['(' name [',' ...] ')' ]",
938 : NULL,
939 : NULL},
940 : {"table_privileges",
941 : NULL,
942 : "{ ALL [ PRIVILEGES ] | INSERT | DELETE | TRUNCATE\n"
943 : "| { SELECT | UPDATE | REFERENCES } [ column_list ] } [',' ...] ON [ TABLE ] qname",
944 : "column_list",
945 : NULL},
946 : {"table_ref",
947 : NULL,
948 : "[LATERAL] func_ref [table_name] | [LATERAL] subquery | joined_table",
949 : "table_name,subquery",
950 : NULL},
951 : {"table_source",
952 : NULL,
953 : "'(' table_element [',' ...] ')' | column_list AS query_expression [ WITH [NO] DATA ] ",
954 : "table_element,column_list,query_expression",
955 : NULL},
956 : {"transaction_statement",
957 : NULL,
958 : "commit | savepoint | release | rollback | start transaction | set local transaction",
959 : "commit,savepoint,release,rollback,start transaction,set local transaction",
960 : NULL},
961 : {"time_precision",
962 : NULL,
963 : "'(' integer ')'",
964 : NULL,
965 : NULL},
966 : {"timestamp_precision",
967 : NULL,
968 : "'(' integer ')'",
969 : NULL,
970 : NULL},
971 : {"transactionmode",
972 : NULL,
973 : "{ READ ONLY | READ WRITE | ISOLATION LEVEL isolevel | DIAGNOSTICS integer } [ , ... ]",
974 : "isolevel",
975 : "Note: DIAGNOSTICS is not yet implemented"},
976 : {"trigger_reference",
977 : NULL,
978 : "{ OLD | NEW } { [ROW] | TABLE } [AS] ident",
979 : NULL,
980 : NULL},
981 : {"update_statement",
982 : NULL,
983 : "delete_stmt | truncate_stmt | insert_stmt | update_stmt | merge_stmt | copyfrom_stmt",
984 : "delete_stmt,truncate_stmt,insert_stmt,update_stmt,merge_stmt,copyfrom_stmt",
985 : NULL},
986 : {"triggered_action",
987 : NULL,
988 : "[ FOR EACH { ROW | STATEMENT } ]\n"
989 : "[ WHEN '(' search_condition ')' ]\n"
990 : "{ trigger_statement | BEGIN ATOMIC trigger_statement [ ; ... ] END }",
991 : "trigger_statement,search_condition",
992 : NULL},
993 : {"trigger_statement",
994 : NULL,
995 : "update_statement | declare_statement | set_statement | control_statement | select_single_row",
996 : "update_statement,declare_statement,set_statement,control_statement,select_single_row",
997 : NULL},
998 : {"when_statement",
999 : NULL,
1000 : "WHEN scalar_expression THEN procedure_statement ...",
1001 : "procedure_statement",
1002 : NULL},
1003 : {"while_statement",
1004 : NULL,
1005 : "[ident ':'] WHILE search_condition DO procedure_statement ... END WHILE [ident]",
1006 : "search_condition,procedure_statement",
1007 : "See also https://www.monetdb.org/documentation/user-guide/sql-programming/flow-of-control/"},
1008 : {"window_aggregate_function",
1009 : NULL,
1010 : "{ AVG '(' query_expression ')' | COUNT '(' { '*' | query_expression } ')' | MAX '(' query_expression ')'\n"
1011 : "| MIN '(' query_expression ')' | PROD '(' query_expression ')' | SUM '(' query_expression ')' }",
1012 : "query_expression",
1013 : NULL},
1014 : {"window_bound",
1015 : NULL,
1016 : "{ UNBOUNDED FOLLOWING | query_expression FOLLOWING | UNBOUNDED PRECEDING | query_expression PRECEDING | CURRENT ROW }",
1017 : "query_expression",
1018 : NULL},
1019 : {"window_definition",
1020 : NULL,
1021 : "ident AS '(' window_specification ')'",
1022 : "window_specification",
1023 : NULL},
1024 : {"window_frame_start",
1025 : NULL,
1026 : "{ UNBOUNDED PRECEDING | query_expression PRECEDING | CURRENT ROW }",
1027 : "query_expression",
1028 : NULL},
1029 : {"window_rank_function",
1030 : NULL,
1031 : "{ CUME_DIST '(' ')' | DENSE_RANK '(' ')' | FIRST_VALUE '(' query_expression ')'\n"
1032 : "| LAG '(' query_expression [ ',' query_expression [ ',' query_expression ] ] ')' | LAST_VALUE '(' query_expression ')'\n"
1033 : "| LEAD '(' query_expression [ ',' query_expression [ ',' query_expression ] ] ')'\n"
1034 : "| NTH_VALUE '(' query_expression ',' query_expression ')' | NTILE '(' query_expression ')'\n"
1035 : "| PERCENT_RANK '(' ')' | RANK '(' ')' | ROW_NUMBER '(' ')' }",
1036 : "query_expression",
1037 : NULL},
1038 : {"window_specification",
1039 : NULL,
1040 : "[ ident ]\n"
1041 : "[ PARTITION BY expression [',' ...] ]\n"
1042 : "[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [',' ...] ]\n"
1043 : "[ { ROWS | RANGE | GROUPS } { window_frame_start | BETWEEN window_bound AND window_bound }\n"
1044 : " [ EXCLUDING { CURRENT ROW | GROUP | TIES | NO OTHERS } ] ]",
1045 : "window_bound,window_frame_start",
1046 : NULL},
1047 : {"cte_list",
1048 : NULL,
1049 : "ident [ column_list ] AS query_expression [ ',' cte_list ] ...",
1050 : "column_list,query_expression",
1051 : NULL},
1052 : {NULL, NULL, NULL, NULL, NULL} /* End of list marker */
1053 : };
1054 :
1055 : static const char *
1056 0 : sql_grammar_rule(const char *word, stream *toConsole)
1057 : {
1058 0 : char buf[65], *s = buf;
1059 0 : int i;
1060 0 : while (s < buf + 64 && *word != ',' && *word && !isspace((unsigned char) *word))
1061 0 : *s++ = *word++;
1062 0 : *s = 0;
1063 :
1064 0 : for (i = 0; sqlhelp2[i].command; i++) {
1065 0 : if (strcasecmp(sqlhelp2[i].command, buf) == 0) {
1066 0 : if (sqlhelp2[i].syntax) {
1067 0 : mnstr_printf(toConsole, "%s : %s\n", buf, sqlhelp2[i].syntax);
1068 0 : if (sqlhelp2[i].synopsis)
1069 0 : mnstr_printf(toConsole, "%.*s %s\n", (int) (s - buf), "", sqlhelp2[i].synopsis);
1070 0 : } else if (sqlhelp2[i].synopsis)
1071 0 : mnstr_printf(toConsole, "%s : %s\n", buf, sqlhelp2[i].synopsis);
1072 : }
1073 : }
1074 0 : while (*word && (isalnum((unsigned char) *word || *word == '_')))
1075 0 : word++;
1076 0 : while (*word && isspace((unsigned char) *word))
1077 0 : word++;
1078 0 : return *word == ',' ? word + 1 : NULL;
1079 : }
1080 :
1081 : static void
1082 0 : sql_grammar(SQLhelp *sqlhelp, stream *toConsole)
1083 : {
1084 0 : const char *t1;
1085 0 : if (sqlhelp->synopsis == NULL) {
1086 0 : mnstr_printf(toConsole, "%s : %s\n", sqlhelp->command, sqlhelp->syntax);
1087 0 : if (sqlhelp->comments)
1088 0 : mnstr_printf(toConsole, "%s\n", sqlhelp->comments);
1089 0 : t1 = sqlhelp->rules;
1090 0 : if (t1 && *t1)
1091 0 : do
1092 0 : t1 = sql_grammar_rule(t1, toConsole);
1093 0 : while (t1);
1094 0 : return;
1095 : }
1096 0 : if (sqlhelp->command)
1097 0 : mnstr_printf(toConsole, "command : %s\n", sqlhelp->command);
1098 0 : if (sqlhelp->synopsis && *sqlhelp->synopsis)
1099 0 : mnstr_printf(toConsole, "synopsis : %s\n", sqlhelp->synopsis);
1100 0 : if (sqlhelp->syntax && *sqlhelp->syntax) {
1101 0 : mnstr_printf(toConsole, "syntax : ");
1102 0 : for (t1 = sqlhelp->syntax; *t1; t1++) {
1103 0 : if (*t1 == '\n')
1104 0 : mnstr_printf(toConsole, "\n ");
1105 : else
1106 0 : mnstr_printf(toConsole, "%c", *t1);
1107 : }
1108 0 : mnstr_printf(toConsole, "\n");
1109 0 : t1 = sqlhelp->rules;
1110 0 : if (t1 && *t1)
1111 0 : do
1112 0 : t1 = sql_grammar_rule(t1, toConsole);
1113 0 : while (t1);
1114 : }
1115 0 : if (sqlhelp->comments)
1116 0 : mnstr_printf(toConsole, "%s\n", sqlhelp->comments);
1117 : }
1118 :
1119 : static void
1120 0 : sql_word(const char *word, size_t maxlen, stream *toConsole)
1121 : {
1122 0 : size_t i;
1123 :
1124 0 : mnstr_printf(toConsole, "%s", word);
1125 0 : for (i = strlen(word); i <= maxlen; i++)
1126 0 : mnstr_printf(toConsole, " ");
1127 0 : }
1128 :
1129 : void
1130 0 : sql_help(const char *pattern, stream *toConsole, int pagewidth)
1131 : {
1132 0 : size_t maxlen = 1, len;
1133 0 : int i, step, ncolumns, total = 0;
1134 :
1135 0 : if (*pattern == '\\')
1136 0 : pattern++;
1137 0 : while (*pattern && !isspace((unsigned char) *pattern)) {
1138 0 : pattern++;
1139 : }
1140 0 : while (*pattern && isspace((unsigned char) *pattern)) {
1141 0 : pattern++;
1142 : }
1143 :
1144 0 : if (*pattern && *pattern != '*') {
1145 0 : bool first = true;
1146 0 : size_t patlen = strlen(pattern);
1147 : /* ignore possible final newline in pattern */
1148 0 : if (pattern[patlen - 1] == '\n')
1149 0 : patlen--;
1150 0 : for (i = 0; sqlhelp1[i].command; i++)
1151 0 : if (strncasecmp(sqlhelp1[i].command, pattern, patlen) == 0) {
1152 0 : if (!first)
1153 0 : mnstr_printf(toConsole, "\n");
1154 0 : sql_grammar(&sqlhelp1[i], toConsole);
1155 0 : first = false;
1156 : }
1157 0 : for (i = 0; sqlhelp2[i].command; i++)
1158 0 : if (strncasecmp(sqlhelp2[i].command, pattern, patlen) == 0) {
1159 0 : if (!first)
1160 0 : mnstr_printf(toConsole, "\n");
1161 0 : sql_grammar(&sqlhelp2[i], toConsole);
1162 0 : first = false;
1163 : }
1164 : return;
1165 : }
1166 :
1167 : // collect the major topics
1168 0 : for (i = 0; sqlhelp1[i].command; i++) {
1169 0 : total++;
1170 0 : if ((len = strlen(sqlhelp1[i].command)) > maxlen)
1171 : maxlen = len;
1172 : }
1173 : // provide summary of all major topics (=search terms)
1174 0 : ncolumns = (int) maxlen > pagewidth ? 1 : (int) (pagewidth / maxlen);
1175 0 : if (ncolumns > 1 && ncolumns * (int) maxlen + ncolumns - 1 > pagewidth)
1176 0 : ncolumns--;
1177 0 : step = total / ncolumns;
1178 0 : if(total % ncolumns) {
1179 0 : step++;
1180 : }
1181 0 : for (i = 0; i < step; i++) {
1182 0 : for (int j = 0; j < ncolumns; j++) {
1183 0 : size_t nextNum = i + j * step;
1184 0 : if(nextNum < sizeof(sqlhelp1)/sizeof(sqlhelp1[0]) - 1) {
1185 0 : sql_word(sqlhelp1[nextNum].command, j < ncolumns - 1 ? maxlen : 0, toConsole);
1186 : }
1187 : }
1188 0 : mnstr_printf(toConsole, "\n");
1189 : }
1190 0 : mnstr_printf(toConsole,
1191 : "Using the conventional grammar constructs:\n"
1192 : "[ A | B ] token A or B or none\n"
1193 : "{ A | B } exactly one of the options A or B should be chosen\n"
1194 : "A [',' ...] a comma separated list of A elements\n"
1195 : "{ A | B } ... a series of A and B's\n"
1196 : "{ A B } [',' ...] a series of A B,A B,A B,A B\n"
1197 : "For more search terms type: \\help *\n"
1198 : "See also https://www.monetdb.org/documentation/user-guide/sql-manual/\n");
1199 : }
|
