You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by pt...@apache.org on 2022/11/10 12:54:40 UTC
[ignite-3] branch main updated: IGNITE-17730 Fix issues in SQL doc (#1108)
This is an automated email from the ASF dual-hosted git repository.
ptupitsyn pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new eaa7c87a06 IGNITE-17730 Fix issues in SQL doc (#1108)
eaa7c87a06 is described below
commit eaa7c87a0677b46eff91eb161fd3d1c3fe3ad8e9
Author: IgGusev <de...@mail.ru>
AuthorDate: Thu Nov 10 15:54:33 2022 +0300
IGNITE-17730 Fix issues in SQL doc (#1108)
---
docs/_docs/sql-reference/ddl.adoc | 401 ++++++++++++++-------
docs/_docs/sql-reference/dml.adoc | 6 +-
.../sql-reference/operators-and-functions.adoc | 31 +-
3 files changed, 291 insertions(+), 147 deletions(-)
diff --git a/docs/_docs/sql-reference/ddl.adoc b/docs/_docs/sql-reference/ddl.adoc
index 8a813d6059..caba17a2df 100644
--- a/docs/_docs/sql-reference/ddl.adoc
+++ b/docs/_docs/sql-reference/ddl.adoc
@@ -14,41 +14,70 @@
// limitations under the License.
= Data Definition Language (DDL)
-This section walks you through all data definition language (DDL) commands supported by Apache Ignite 3.0 Alpha 5.
+This section walks you through all data definition language (DDL) commands supported by Apache Ignite 3.0.
== CREATE TABLE
Creates a new table.
-[source,sql]
-----
-CREATE TABLE [IF NOT EXISTS] [simpleName | schemaName.simpleName] (tableColumn [, tableColumn]...)
-[COLOCATE [BY] (columnName [, columnName]...)]
-[ENGINE engineName]
-[WITH paramName=paramValue [,paramName=paramValue]...]
-tableColumn = columnName columnType [[NOT] NULL] [DEFAULT defaultValue] [PRIMARY KEY]
-----
+
+[.diagram-container]
+Diagram(
+NonTerminal('CREATE'),
+NonTerminal('TABLE'),
+Optional('IF NOT EXISTS'),
+NonTerminal('table_name'),
+NonTerminal('('),
+Choice(1,
+NonTerminal('constraint'),
+NonTerminal('column_definition'),
+NonTerminal(',')),
+NonTerminal(')'),
+End({type:'complex'})
+)
+
+[.diagram-container]
+Diagram(
+Start({type:'complex'}),
+Optional(
+Sequence(
+NonTerminal('COLOCATE'),
+Optional('BY'),
+NonTerminal('column_list')
+)
+),
+Optional(
+Sequence(
+NonTerminal('ENGINE'),
+NonTerminal('engine_name')
+)
+),
+Optional(
+Sequence(
+NonTerminal('WITH'),
+OneOrMore('param_name')
+)
+)
+)
+
Parameters:
-* `tableName` - name of the table.
-* `tableColumn` - name and type of a column to be created in the new table.
-* `DEFAULT` - specifies a default value for the column. You can specify a constant value, or use a link:sql-reference/ddl#system-functions[system function] to generate a value.
+* `table_name` - name of the table. Can be schema-qualified.
+* `table_column` - name and type of a column to be created in the new table.
* `IF NOT EXISTS` - create the table only if a table with the same name does not exist.
-* `ENGINE` - selects the storage engine to use. Currently `pagememory` and `rocksdb` are available.
+* `ENGINE` - selects the storage engine to use. Currently `aipersist`, `aimem` and `rocksdb` are available.
* `COLOCATED BY` - colocation key. The key can be composite. Primary key must include colocation key. Was `affinity_key` in Ignite 2.x.
-* `WITH` - accepts additional parameters not defined by ANSI-99 SQL:
+* `WITH` - accepts the following additional parameters:
** `Replicas` - sets the number of partition copies, including the master copy.
** `Partitions` - sets the number of table partitions.
-** `dataRegion` - sets the data region to work with.
+** Other parameters, depending on the database engine.
-image::images/svg/create_table_bnf1.svg[Embedded,opts=inline]
-image::images/svg/create_table_bnf2.svg[Embedded,opts=inline]
Examples:
-Create Person table:
+Creates a Person table:
[source,sql]
----
@@ -60,94 +89,115 @@ CREATE TABLE IF NOT EXISTS Person (
company varchar,
)
----
-
+//
== ALTER TABLE
-
+//
Modifies the structure of an existing table.
+//
+//[NOTE]
+//====
+//[discrete]
+//=== Scope of ALTER TABLE
+//Presently, Apache Ignite only supports addition and removal of columns.
+//====
+//
+//* *ALTER TABLE IF EXISTS table LOGGING*
+//
+//[source,sql]
+//----
+//ALTER TABLE [IF EXISTS] tableName {LOGGING}
+//----
+//
+//Parameters:
+//
+//- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
+//- `qualified_table_name` - the name of the table.
+//- `LOGGING` - enables write-ahead logging for the table. The command is relevant only if Ignite link:https://ignite.apache.org/docs/3.0.0-alpha/persistence[persistence,window=_blank] is used.
+//
+//image::images/svg/alter_table_logging_bnf.svg[Embedded,opts=inline]
+//
+//* *ALTER TABLE IF EXISTS table NOLOGGING*
+//
+//[source,sql]
+//----
+//ALTER TABLE [IF EXISTS] tableName {NOLOGGING}
+//----
+//
+//Parameters:
+//
+//- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
+//- `qualified_table_name` - the name of the table.
+//- `NOLOGGING` - disables write-ahead logging for the table. The command is relevant only if Ignite link:https://ignite.apache.org/docs/3.0.0-alpha/persistence[persistence,window=_blank] is used.
+//
+//image::images/svg/alter_table_no_logging_bnf.svg[Embedded,opts=inline]
+//
-[NOTE]
-====
-[discrete]
-=== Scope of ALTER TABLE
-Presently, Apache Ignite only supports addition and removal of columns.
-====
-
-* *ALTER TABLE IF EXISTS table LOGGING*
-
-[source,sql]
-----
-ALTER TABLE [IF EXISTS] tableName {LOGGING}
-----
-
-Parameters:
-
-- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
-- `qualified_table_name` - the name of the table.
-- `LOGGING` - enables write-ahead logging for the table. The command is relevant only if Ignite link:https://ignite.apache.org/docs/3.0.0-alpha/persistence[persistence,window=_blank] is used.
+==== ALTER TABLE IF EXISTS table ADD COLUMN IF NOT EXISTS (column1 int, column2 int)
-image::images/svg/alter_table_logging_bnf.svg[Embedded,opts=inline]
+[.diagram-container]
+Diagram(
+ NonTerminal('ALTER TABLE'),
+ Optional('IF EXISTS'),
+ NonTerminal('qualified_table_name'),
+ NonTerminal('ADD'),
+ Optional('COLUMN'),
+End({type:'complex'})
+)
-* *ALTER TABLE IF EXISTS table NOLOGGING*
+[.diagram-container]
+Diagram(
+ Start({type:'complex'}),
+ Optional('IF NOT EXISTS'),
+ NonTerminal('column_definition_or_list')
+)
-[source,sql]
-----
-ALTER TABLE [IF EXISTS] tableName {NOLOGGING}
-----
Parameters:
- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
- `qualified_table_name` - the name of the table.
-- `NOLOGGING` - disables write-ahead logging for the table. The command is relevant only if Ignite link:https://ignite.apache.org/docs/3.0.0-alpha/persistence[persistence,window=_blank] is used.
-
-image::images/svg/alter_table_no_logging_bnf.svg[Embedded,opts=inline]
-
-* *ALTER TABLE IF EXISTS table ADD COLUMN IF NOT EXISTS (column1 int, column2 int)*
+- `ADD` - adds a new column or several columns to a previously created table. Once a column is added, it can be accessed using link:sql-reference/dml[DML commands,window=_blank] and indexed with the CREATE INDEX statement.
+- `COLUMN` - name of the defined column.
+- `IF NOT EXISTS` - do not throw an error if a column with the same name already exists.
+- `column_definition_or_list` - definition of the column to add to the table.
-[source,sql]
-----
-ALTER TABLE [IF EXISTS] tableName {ADD [COLUMN] {[IF NOT EXISTS] (tableColumn [column1 int, column2 int])}
-----
-Parameters:
-- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
-- `qualified_table_name` - the name of the table.
-- `ADD` - adds a new column or several columns to a previously created table. Once a column is added, it can be accessed using link:sql-reference/dml[DML commands,window=_blank] and indexed with the CREATE INDEX statement (currently not supported).
-- `COLUMN` - name of the defined column.
-- `IF NOT EXISTS` - do not throw an error if a column with the same name already exists.
-- `column_definition_or_list` - the name of the column or the list of columns to be added.
+==== ALTER TABLE IF EXISTS table DROP COLUMN IF EXISTS (column1, column2 int)
-image::images/svg/alter_table_column_no_bnf1.svg[Embedded,opts=inline]
-image::images/svg/alter_table_column_no_bnf2.svg[Embedded,opts=inline]
+[.diagram-container]
+Diagram(
+NonTerminal('ALTER TABLE'),
+Optional('IF EXISTS'),
+NonTerminal('qualified_table_name'),
+NonTerminal('DROP'),
+Optional('COLUMN'),
+End({type:'complex'})
+)
-* *ALTER TABLE IF EXISTS table ADD COLUMN IF EXISTS (column1, column2 int)*
+[.diagram-container]
+Diagram(
+Start({type:'complex'}),
+Optional('IF EXISTS'),
+NonTerminal('column_list')
+)
-[source,sql]
-----
-ALTER TABLE [IF EXISTS] tableName DROP [COLUMN] {[IF EXISTS] (columnName [column1, column2 int])}
-----
Parameters:
-- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
+- `IF NOT EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
- `qualified_table_name` - the name of the table.
- `DROP` - removes an existing column or multiple columns from a table. Once a column is removed, it cannot be accessed within queries. Consider the following notes and limitations:
-- `COLUMN` - name of the defined column.
- `column_list` - the name of the list of columns to be removed.
-image::images/svg/alter_table_column_yes_bnf1.svg[Embedded,opts=inline]
-image::images/svg/alter_table_column_yes_bnf2.svg[Embedded,opts=inline]
-- The command does not remove actual data from the cluster which means that if the column 'name' is dropped, the value of the 'name' is still stored in the cluster. This limitation is to be addressed in the next releases.
-- If the column was indexed, the index has to be dropped manually using the 'DROP INDEX' command.
-- It is not possible to remove a column that is a primary key or a part of such a key.
+- If the column was indexed, the index has to be dropped manually in advance by using the 'DROP INDEX' command.
- It is not possible to remove a column if it represents the whole value stored in the cluster. The limitation is relevant for primitive values.
Ignite stores data in the form of key-value pairs and all the new columns will belong to the value. It's not possible to change a set of columns of the key (`PRIMARY KEY`).
-Both DDL and DML commands targeting the same table are blocked for a short time until `ALTER TABLE` is in progress.
+//Both DDL and DML commands targeting the same table are blocked for a short time until `ALTER TABLE` is in progress.
-Schema changes applied by this command are persisted on disk if link:https://ignite.apache.org/docs/3.0.0-alpha/persistence[persistence,window=_blank] is enabled. Thus, the changes can survive full cluster restarts.
+Schema changes applied by this command are persisted on disk. Thus, the changes can survive full cluster restarts.
Examples:
@@ -169,7 +219,7 @@ Add a column only if the table exists:
[source,sql]
----
-ALTER TABLE IF EXISTS Missing ADD number long;
+ALTER TABLE IF EXISTS Missing ADD number bigint;
----
Add several columns to the table at once:
@@ -207,34 +257,37 @@ Drop several columns from the table at once:
ALTER TABLE Person DROP COLUMN (code, gdp);
----
-Disable write-ahead logging:
-
-[source,sql]
-----
-ALTER TABLE Person NOLOGGING
-----
+//Disable write-ahead logging:
+//
+//[source,sql]
+//----
+//ALTER TABLE Person NOLOGGING
+//----
== DROP TABLE
The `DROP TABLE` command drops an existing table.
-The underlying cache with all the data in it is destroyed, too.
-[source,sql]
-----
-DROP TABLE [IF EXISTS] tableName
-----
+
+[.diagram-container]
+Diagram(
+NonTerminal('DROP TABLE'),
+Optional('IF EXISTS'),
+NonTerminal('qualified_table_name')
+)
+
+
Parameters:
-- `tableName` - the name of the table.
-- `IF NOT EXISTS` - do not throw an error if a table with the same name does not exist.
+- `IF EXISTS` - do not throw an error if a table with the same name does not exist.
+- `qualified_table_name` - the name of the table. Can be schema-qualified.
-image::images/svg/drop_table_bnf.svg[Embedded,opts=inline]
-Both DDL and DML commands targeting the same table are blocked while the `DROP TABLE` is in progress.
-Once the table is dropped, all pending commands will fail with appropriate errors.
+//Both DDL and DML commands targeting the same table are blocked while the `DROP TABLE` is in progress.
+//Once the table is dropped, all pending commands will fail with appropriate errors.
-Schema changes applied by this command are persisted on disk if link:https://ignite.apache.org/docs/3.0.0-alpha/persistence[persistence,window=_blank] is enabled. Thus, the changes can survive full cluster restarts.
+Schema changes applied by this command are persisted on disk. Thus, the changes can survive full cluster restarts.
Examples:
@@ -249,20 +302,36 @@ DROP TABLE IF EXISTS "Person";
Creates a new index.
-[source,sql]
-----
-CREATE INDEX [IF NOT EXISTS] name ON tableName
-[USING { HASH | TREE }]
-(column_definition [, column_definition]...);
-----
+[.diagram-container]
+Diagram(
+ NonTerminal('CREATE INDEX'),
+ Optional('IF NOT EXISTS'),
+ NonTerminal('name'),
+ NonTerminal('ON'),
+ NonTerminal('qualified_table_name'),
+ End({type:'complex'})
+)
+
+[.diagram-container]
+Diagram(
+ Start({type:'complex'}),
+ Optional(
+ Sequence(
+ NonTerminal('USING'),
+ Choice (0,'HASH','TREE')
+ )
+ ),
+ Optional('column_definition')
+)
+
Parameters:
* `name` - name of the index.
-* `tableName` - name of the table to create the index on.
+* `qualified_table_name` - name of the table to create the index on. Can be schema-qualified.
* `IF NOT EXISTS` - create the index only if an index with the same name does not exist.
-* `USING` - specifies whether the command creates a sorted index or a hash index. Possible values: `HASH`, `TREE`.
+* `USING` - specifies whether the command creates a sorted index or a hash index. Possible values: `HASH`, `TREE`. Default is `TREE`.
//NOTE: Add image
@@ -278,14 +347,16 @@ CREATE INDEX IF NOT EXISTS Persons ON Person (column1)
== DROP INDEX
-[source,sql]
-----
-DROP INDEX [IF EXISTS] indexName;
-----
+[.diagram-container]
+Diagram(
+NonTerminal('DROP INDEX'),
+Optional('IF EXISTS'),
+NonTerminal('index_name')
+)
Parameters:
-- indexName - the name of the index.
+- `index_name` - the name of the index.
- `IF EXISTS` - do not throw an error if an index with the specified name does not exist.
@@ -299,43 +370,76 @@ Drop index if the one exists:
DROP INDEX IF EXISTS Persons;
----
-= Grammar Reference
+== Grammar Reference
+
+=== column_definition_or_list
-== column_definition_or_list
+[.diagram-container]
+Diagram(
+Group(Sequence(
+Optional('('),
+Choice(0,Sequence(
+NonTerminal('column_name'),
+NonTerminal('data_type')),
+NonTerminal(',')
+),
+Optional(')')
+), 'column_definition_or_list')
+)
-image::images/svg/column_definition_or_list_bnf.svg[Embedded,opts=inline]
Referenced by:
* link:sql-reference/ddl#alter-table[ALTER TABLE]
-== column_definition
+'''
-image::images/svg/columns_definition_bnf.svg[Embedded,opts=inline]
+=== column_list
-Referenced by:
-* link:sql-reference/ddl#create-table[CREATE TABLE]
-
-== column_list
+[.diagram-container]
+Diagram(Group(Sequence(
+Optional('('),
+OneOrMore('column_name', ','),
+Optional(')')))
+)
-image::images/svg/column_list_bnf.svg[Embedded,opts=inline]
Referenced by:
* link:sql-reference/ddl#alter-table[ALTER TABLE]
-== constraint
+'''
+
+=== constraint
+
+[.diagram-container]
+Diagram(Group(Sequence(
+Optional(Sequence(NonTerminal('Constraint'),NonTerminal('constraint_name')
+)),
+NonTerminal('PRIMARY KEY'),
+NonTerminal('('),
+OneOrMore('column_name', ','),
+NonTerminal(')')
+),'constraint')
+)
-image::images/svg/pk_constraint_bnf.svg[Embedded,opts=inline]
Referenced by:
* link:sql-reference/ddl#create-table[CREATE TABLE]
-== qualified_table_name
+'''
-image::images/svg/qualified_table_name_bnf.svg[Embedded,opts=inline]
+=== qualified_table_name
+
+[.diagram-container]
+Diagram(Group(Sequence(
+Optional(Sequence(NonTerminal('schema'),NonTerminal('.')
+),),
+NonTerminal('table_name')
+),'qualified_table_name')
+)
Referenced by:
@@ -343,12 +447,30 @@ Referenced by:
* link:sql-reference/ddl#alter-table[ALTER TABLE]
* link:sql-reference/ddl#drop-table[DROP TABLE]
-== column_definition
+'''
+
+=== column_definition
//NOTE: Replace code with image
-```
-column_name [ASC | DESC] [NULLS {FIRST | LAST}]
-```
+[.diagram-container]
+Diagram(
+Group(Sequence(
+NonTerminal('column_name'),
+NonTerminal('DATA TYPE'),
+Optional(Sequence(Optional('NOT'),NonTerminal('NULL')))
+), 'column_definition'),
+End({type:'complex'})
+)
+
+[.diagram-container]
+Diagram(
+Start({type:'complex'}),
+Group(Sequence(
+Optional(Sequence(NonTerminal('DEFAULT'), NonTerminal('literal_value'))),
+NonTerminal('PRIMARY KEY')
+), 'column_definition')
+)
+
Parameters:
@@ -359,9 +481,32 @@ Referenced by:
* link:sql-reference/ddl#сreate-index[CREATE INDEX]
-= System Functions
+'''
+
+=== tableColumn
+//NOTE: Replace code with image
+
+
+[.diagram-container]
+Diagram(Group(Sequence(
+NonTerminal('columnName'),
+NonTerminal('columnType'),
+Optional(Sequence(Optional('NOT'),NonTerminal('NULL')
+),),
+Optional('DEFAULT')
+),'tableColumn')
+)
+
+Parameters:
+
+* `[NOT] NULL` - specifies that values in the column always contain null or not null values.
+* `DEFAULT` - specifies a default value for the column. You can specify a constant value, or use a link:sql-reference/ddl#system-functions[system function] to generate a value.
+
+'''
+
+== System Functions
-== gen_random_uuid
+=== gen_random_uuid
This function generates a random UUID value each time it is called.
diff --git a/docs/_docs/sql-reference/dml.adoc b/docs/_docs/sql-reference/dml.adoc
index e08fc77f90..0335694da3 100644
--- a/docs/_docs/sql-reference/dml.adoc
+++ b/docs/_docs/sql-reference/dml.adoc
@@ -14,7 +14,7 @@
// limitations under the License.
= Data Manipulation Language (DML)
-This section walks you through all data manipulation language (DML) commands supported by Apache Ignite 3.0 Alpha 5.
+This section walks you through all data manipulation language (DML) commands supported by Apache Ignite 3.0.
WARNING: Currently, `WITH` and `MERGE` commands are not supported.
@@ -24,7 +24,7 @@ Retrieves data from a table or multiple tables.
[source,sql]
----
-SELECT [ hintComment ] [ STREAM ] [ ALL | DISTINCT ]
+SELECT [ hintComment ] [ ALL | DISTINCT ]
{ * | projectItem [, projectItem ]* }
FROM tableExpression
[ WHERE booleanExpression ]
@@ -34,7 +34,7 @@ FROM tableExpression
=== JOINs
-Ignite supports colocated and non-colocated distributed SQL joins. Furthermore, if the data resides in different tables (caches), Ignite allows for cross-table joins as well.
+Ignite supports colocated and non-colocated distributed SQL joins. Furthermore, if the data resides in different tables, Ignite allows for cross-table joins as well.
Joins between partitioned and replicated data sets always work without any limitations.
However, if you join partitioned data sets, then you have to make sure that the keys you are joining on are either colocated or make sure you switched on the non-colocated joins parameter for a query.
diff --git a/docs/_docs/sql-reference/operators-and-functions.adoc b/docs/_docs/sql-reference/operators-and-functions.adoc
index df54f216ce..8ca3dca505 100644
--- a/docs/_docs/sql-reference/operators-and-functions.adoc
+++ b/docs/_docs/sql-reference/operators-and-functions.adoc
@@ -50,7 +50,7 @@ Returns the sum of numeric across all input values.
MIN( [ ALL | DISTINCT ] value)
----
-Returns the minimum value of value across all input values.
+Returns the minimum value across all input values.
=== MAX
@@ -59,25 +59,25 @@ Returns the minimum value of value across all input values.
MAX( [ ALL | DISTINCT ] value)
----
-Returns the maximum value of value across all input values.
+Returns the maximum value across all input values.
-=== STRING_AGG
+//=== STRING_AGG
-[source,sql]
-----
-STRING_AGG( value [, separator ] [ ORDER BY ...])
-----
+//[source,sql]
+//----
+//STRING_AGG( value [, separator ] [ ORDER BY ...])
+//----
-Concatenates the values of string expressions and places separator values between them.
+//Concatenates the values of string expressions and places separator values between them.
-=== STRING_CONCAT
+//=== STRING_CONCAT
-[source,sql]
-----
-STRING_CONCAT(string1, string2, ... stringN)
-----
+//[source,sql]
+//----
+//STRING_CONCAT(string1, string2, ... stringN)
+//----
-Concatenates the text values in the specified data ranges.
+//Concatenates the text values in the specified data ranges.
== Functions
@@ -168,5 +168,4 @@ Concatenates the text values in the specified data ranges.
* TIMESTAMP_ADD
* TIMESTAMP_DIFF
* EXTRACT
-* LAST_DAY
-* For more information on functions supported by Apache Calcite, see the link:https://calcite.apache.org/docs/reference.html#operators-and-functions[product documentation,window=_blank].
\ No newline at end of file
+* LAST_DAY
\ No newline at end of file