Yingyi Bu has uploaded a new change for review.
https://asterix-gerrit.ics.uci.edu/1892
Change subject: [Doc]reorganize DDL documentations.
......................................................................
[Doc]reorganize DDL documentations.
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
- Re-organize DDL documentations;
- Move declaration to queries;
- Fix several links;
- Add an example for non-enforced index.
Change-Id: I269750827ef529ac0e474fa0862635cc1f3aefed
---
M asterixdb/asterix-doc/pom.xml
M asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md
A asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_dataverse.md
A asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_function.md
M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query_title.md
R asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_dataset_index.md
A asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_dml.md
A asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_function_removal.md
A asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_head.md
A asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_nonenforced_index.md
11 files changed, 332 insertions(+), 201 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb
refs/changes/92/1892/1
diff --git a/asterixdb/asterix-doc/pom.xml b/asterixdb/asterix-doc/pom.xml
index d2fbefc..a707434 100644
--- a/asterixdb/asterix-doc/pom.xml
+++ b/asterixdb/asterix-doc/pom.xml
@@ -53,7 +53,7 @@
<configuration>
<target>
<concat
destfile="${project.build.directory}/generated-site/markdown/sqlpp/manual.md">
- <filelist dir="${project.basedir}/src/main/markdown/sqlpp"
files="0_toc.md,1_intro.md,2_expr_title.md,2_expr.md,3_query_title.md,3_query.md,4_error_title.md,4_error.md,5_ddl.md,appendix_1_title.md,appendix_1_keywords.md,appendix_2_title.md,appendix_2_parameters.md"
/>
+ <filelist dir="${project.basedir}/src/main/markdown/sqlpp"
files="0_toc.md,1_intro.md,2_expr_title.md,2_expr.md,3_query_title.md,3_declare_dataverse.md,3_declare_function.md,3_query.md,4_error_title.md,4_error.md,5_ddl_head.md,5_ddl_dataset_index.md,5_ddl_nonenforced_index.md,5_ddl_function_removal.md,5_ddl_dml.md,appendix_1_title.md,appendix_1_keywords.md,appendix_2_title.md,appendix_2_parameters.md"
/>
</concat>
<concat
destfile="${project.build.directory}/generated-site/markdown/sqlpp/builtins.md">
<filelist
dir="${project.basedir}/src/main/markdown/builtins"
files="0_toc.md,1_numeric_common.md,1_numeric_delta.md,2_string_common.md,2_string_delta.md,3_binary.md,4_spatial.md,5_similarity.md,6_tokenizing.md,7_temporal.md,7_allens.md,8_record.md,9_aggregate_sql.md,10_comparison.md,11_type.md,13_conditional.md,12_misc.md"
/>
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md
index 134ce95..84986ae 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md
@@ -36,6 +36,7 @@
* [Function call Expressions](#Function_call_expressions)
* [Constructors](#Constructors)
* [3. Queries](#Queries)
+ * [Declarations](#Declarations)
* [SELECT Statements](#SELECT_statements)
* [SELECT Clauses](#Select_clauses)
* [Select Element/Value/Raw](#Select_element)
@@ -77,12 +78,14 @@
* [Type Errors](#Type_errors)
* [Resource Errors](#Resource_errors)
* [5. DDL and DML Statements](#DDL_and_DML_statements)
- * [Declarations](#Declarations)
* [Lifecycle Management Statements](#Lifecycle_management_statements)
* [Dataverses](#Dataverses)
- * [Datasets](#Datasets)
* [Types](#Types)
+ * [Datasets](#Datasets)
+ * [Indices](#Indices)
* [Functions](#Functions)
+ * [Removal](#Removal)
+ * [Load Statement](#Load_statement)
* [Modification Statements](#Modification_statements)
* [Inserts](#Inserts)
* [Upserts](#Upserts)
diff --git
a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_dataverse.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_dataverse.md
new file mode 100644
index 0000000..1d1ab8e
--- /dev/null
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_dataverse.md
@@ -0,0 +1,33 @@
+<!--
+ ! Licensed to the Apache Software Foundation (ASF) under one
+ ! or more contributor license agreements. See the NOTICE file
+ ! distributed with this work for additional information
+ ! regarding copyright ownership. The ASF licenses this file
+ ! to you under the Apache License, Version 2.0 (the
+ ! "License"); you may not use this file except in compliance
+ ! with the License. You may obtain a copy of the License at
+ !
+ ! http://www.apache.org/licenses/LICENSE-2.0
+ !
+ ! Unless required by applicable law or agreed to in writing,
+ ! software distributed under the License is distributed on an
+ ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ! KIND, either express or implied. See the License for the
+ ! specific language governing permissions and limitations
+ ! under the License.
+ !-->
+
+## <a id="Declarations">Declarations</a>
+
+ DatabaseDeclaration ::= "USE" Identifier
+
+At the uppermost level, the world of data is organized into data namespaces
called **dataverses**.
+To set the default dataverse for a series of statements, the USE statement is
provided in SQL++.
+
+As an example, the following statement sets the default dataverse to be
"TinySocial".
+
+##### Example
+
+ USE TinySocial;
+
+
diff --git
a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_function.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_function.md
new file mode 100644
index 0000000..a45996c
--- /dev/null
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_function.md
@@ -0,0 +1,45 @@
+<!--
+ ! Licensed to the Apache Software Foundation (ASF) under one
+ ! or more contributor license agreements. See the NOTICE file
+ ! distributed with this work for additional information
+ ! regarding copyright ownership. The ASF licenses this file
+ ! to you under the Apache License, Version 2.0 (the
+ ! "License"); you may not use this file except in compliance
+ ! with the License. You may obtain a copy of the License at
+ !
+ ! http://www.apache.org/licenses/LICENSE-2.0
+ !
+ ! Unless required by applicable law or agreed to in writing,
+ ! software distributed under the License is distributed on an
+ ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ! KIND, either express or implied. See the License for the
+ ! specific language governing permissions and limitations
+ ! under the License.
+ !-->
+
+When writing a complex SQL++ query, it can sometimes be helpful to define one
or more auxilliary functions
+that each address a sub-piece of the overall query.
+The declare function statement supports the creation of such helper functions.
+In general, the function body (expression) can be any legal SQL++ query
expression.
+
+ FunctionDeclaration ::= "DECLARE" "FUNCTION" Identifier ParameterList "{"
Expression "}"
+ ParameterList ::= "(" ( <VARIABLE> ( "," <VARIABLE> )* )? ")"
+
+The following is a simple example of a temporary SQL++ function definition and
its use.
+
+##### Example
+
+ DECLARE FUNCTION friendInfo(userId) {
+ (SELECT u.id, u.name, len(u.friendIds) AS friendCount
+ FROM GleambookUsers u
+ WHERE u.id = userId)[0]
+ };
+
+ SELECT VALUE friendInfo(2);
+
+For our sample data set, this returns:
+
+ [
+ { "id": 2, "name": "IsbelDull", "friendCount": 2 }
+ ]
+
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
index e44b6a7..409ca38 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
@@ -17,10 +17,6 @@
! under the License.
!-->
-A SQL++ query can be any legal SQL++ expression or `SELECT` statement. A SQL++
query always ends with a semicolon.
-
- Query ::= (Expression | SelectStatement) ";"
-
## <a id="SELECT_statements">SELECT Statements</a>
The following shows the (rich) grammar for the `SELECT` statement in SQL++.
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query_title.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query_title.md
index d5d988a..e35ad29 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query_title.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query_title.md
@@ -18,3 +18,8 @@
!-->
# <a id="Queries">3. Queries</a>
+
+A SQL++ query can be any legal SQL++ expression or `SELECT` statement. A SQL++
query always ends with a semicolon.
+
+ Query ::= (Expression | SelectStatement) ";"
+
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_dataset_index.md
similarity index 62%
rename from asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl.md
rename to asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_dataset_index.md
index 1c36f13..bdd9706 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_dataset_index.md
@@ -17,63 +17,6 @@
! under the License.
!-->
-# <a id="DDL_and_DML_statements">5. DDL and DML statements</a>
-
- Statement ::= ( SingleStatement ( ";" )? )* <EOF>
- SingleStatement ::= DatabaseDeclaration
- | FunctionDeclaration
- | CreateStatement
- | DropStatement
- | LoadStatement
- | SetStatement
- | InsertStatement
- | DeleteStatement
- | Query ";"
-
-In addition to queries, an implementation of SQL++ needs to support statements
for data definition
-and manipulation purposes as well as controlling the context to be used in
evaluating SQL++ expressions.
-This section details the DDL and DML statements supported in the SQL++
language as realized today in
-Apache AsterixDB.
-
-## <a id="Declarations">Declarations</a>
-
- DatabaseDeclaration ::= "USE" Identifier
-
-At the uppermost level, the world of data is organized into data namespaces
called **dataverses**.
-To set the default dataverse for a series of statements, the USE statement is
provided in SQL++.
-
-As an example, the following statement sets the default dataverse to be
"TinySocial".
-
-##### Example
-
- USE TinySocial;
-
-When writing a complex SQL++ query, it can sometimes be helpful to define one
or more auxilliary functions
-that each address a sub-piece of the overall query.
-The declare function statement supports the creation of such helper functions.
-In general, the function body (expression) can be any legal SQL++ query
expression.
-
- FunctionDeclaration ::= "DECLARE" "FUNCTION" Identifier ParameterList "{"
Expression "}"
- ParameterList ::= "(" ( <VARIABLE> ( "," <VARIABLE> )* )? ")"
-
-The following is a simple example of a temporary SQL++ function definition and
its use.
-
-##### Example
-
- DECLARE FUNCTION friendInfo(userId) {
- (SELECT u.id, u.name, len(u.friendIds) AS friendCount
- FROM GleambookUsers u
- WHERE u.id = userId)[0]
- };
-
- SELECT VALUE friendInfo(2);
-
-For our sample data set, this returns:
-
- [
- { "id": 2, "name": "IsbelDull", "friendCount": 2 }
- ]
-
## <a id="Lifecycle_management_statements">Lifecycle Management Statements</a>
CreateStatement ::= "CREATE" ( DatabaseSpecification
@@ -119,7 +62,7 @@
The CREATE TYPE statement is used to create a new named datatype.
This type can then be used to create stored collections or utilized when
defining one or more other datatypes.
-Much more information about the data model is available in the [data model
reference guide](datamodel.html).
+Much more information about the data model is available in the [data model
reference guide](../datamodel.html).
A new type can be a object type, a renaming of another type, an array type, or
a multiset type.
A object type can be defined as being either open or closed.
Instances of a closed object type are not permitted to contain fields other
than those specified in the create type statement.
@@ -205,14 +148,14 @@
Another advanced option shown in the syntax above, related to performance and
mentioned above, is that a **filter** can optionally be created on a field to
further optimize range queries with predicates on the filter's field.
Filters allow some range queries to avoid searching all LSM components when
the query conditions match the filter.
-(Refer to [Filter-Based LSM Index Acceleration](filters.html) for more
information about filters.)
+(Refer to [Filter-Based LSM Index Acceleration](../filters.html) for more
information about filters.)
An External dataset, in contrast to an Internal dataset, has data stored
outside of the system's control.
Files living in HDFS or in the local filesystem(s) of a cluster's nodes are
currently supported.
External dataset support allows SQL++ queries to treat foreign data as though
it were stored in the system,
making it possible to query "legacy" file data (for example, Hive data)
without having to physically import it.
When defining an External dataset, an appropriate adapter type must be
selected for the desired external data.
-(See the [Guide to External Data](externaldata.html) for more information on
the available adapters.)
+(See the [Guide to External Data](../externaldata.html) for more information
on the available adapters.)
The following example creates an Internal dataset for storing FacefookUserType
objects.
It specifies that their id field is their primary key.
@@ -247,12 +190,11 @@
("delimiter"="|"));
-
-#### Indices
+### <a id="Indices">Indices</a>
IndexSpecification ::= <INDEX> Identifier IfNotExists <ON> QualifiedName
"(" ( IndexField ) ( "," IndexField )* ")" ( "type"
IndexType "?")?
- ( <ENFORCED> )?
+ ( (<NOT>)? <ENFORCED> )?
IndexType ::= <BTREE> | <RTREE> | <KEYWORD> | <NGRAM> "("
IntegerLiteral ")"
The CREATE INDEX statement creates a secondary index on one or more fields of
a specified dataset.
@@ -276,6 +218,8 @@
The following example creates an open btree index called gbSendTimeIdx on the
(non-predeclared) sendTime field of the GleambookMessages dataset having
datetime type.
This index can be useful for accelerating exact-match queries, range search
queries, and joins involving the sendTime field.
+The index is enforced so that records that do not have the "sendTime" field or
have a mismatched type on the field
+cannot be inserted into the dataset.
#### Example
@@ -308,134 +252,3 @@
#### Example
CREATE INDEX fbMessageIdx ON GleambookMessages(message) TYPE KEYWORD;
-
-### <a id="Functions"> Functions</a>
-
-The create function statement creates a **named** function that can then be
used and reused in SQL++ queries.
-The body of a function can be any SQL++ expression involving the function's
parameters.
-
- FunctionSpecification ::= "FUNCTION" FunctionOrTypeName IfNotExists
ParameterList "{" Expression "}"
-
-The following is an example of a CREATE FUNCTION statement which is similar to
our earlier DECLARE FUNCTION example.
-It differs from that example in that it results in a function that is
persistently registered by name in the specified dataverse (the current
dataverse being used, if not otherwise specified).
-
-##### Example
-
- CREATE FUNCTION friendInfo(userId) {
- (SELECT u.id, u.name, len(u.friendIds) AS friendCount
- FROM GleambookUsers u
- WHERE u.id = userId)[0]
- };
-
-#### Removal
-
- DropStatement ::= "DROP" ( "DATAVERSE" Identifier IfExists
- | "TYPE" FunctionOrTypeName IfExists
- | "DATASET" QualifiedName IfExists
- | "INDEX" DoubleQualifiedName IfExists
- | "FUNCTION" FunctionSignature IfExists )
- IfExists ::= ( "IF" "EXISTS" )?
-
-The DROP statement in SQL++ is the inverse of the CREATE statement. It can be
used to drop dataverses, datatypes, datasets, indexes, and functions.
-
-The following examples illustrate some uses of the DROP statement.
-
-##### Example
-
- DROP DATASET GleambookUsers IF EXISTS;
-
- DROP INDEX GleambookMessages.gbSenderLocIndex;
-
- DROP TYPE TinySocial2.GleambookUserType;
-
- DROP FUNCTION friendInfo@1;
-
- DROP DATAVERSE TinySocial;
-
-When an artifact is dropped, it will be droppped from the current dataverse if
none is specified
-(see the DROP DATASET example above) or from the specified dataverse (see the
DROP TYPE example above)
-if one is specified by fully qualifying the artifact name in the DROP
statement.
-When specifying an index to drop, the index name must be qualified by the
dataset that it indexes.
-When specifying a function to drop, since SQL++ allows functions to be
overloaded by their number of arguments,
-the identifying name of the function to be dropped must explicitly include
that information.
-(`friendInfo@1` above denotes the 1-argument function named friendInfo in the
current dataverse.)
-
-### Import/Export Statements
-
- LoadStatement ::= <LOAD> <DATASET> QualifiedName <USING> AdapterName
Configuration ( <PRE-SORTED> )?
-
-The LOAD statement is used to initially populate a dataset via bulk loading of
data from an external file.
-An appropriate adapter must be selected to handle the nature of the desired
external data.
-The LOAD statement accepts the same adapters and the same parameters as
discussed earlier for External datasets.
-(See the [guide to external data](externaldata.html) for more information on
the available adapters.)
-If a dataset has an auto-generated primary key field, the file to be imported
should not include that field in it.
-
-The following example shows how to bulk load the GleambookUsers dataset from
an external file containing data that has been prepared in ADM (Asterix Data
Model) format.
-
-##### Example
-
- LOAD DATASET GleambookUsers USING localfs
-
(("path"="127.0.0.1:///Users/bignosqlfan/tinysocialnew/gbu.adm"),("format"="adm"));
-
-## <a id="Modification_statements">Modification statements</a>
-
-### <a id="Inserts">INSERTs</a>
-
- InsertStatement ::= <INSERT> <INTO> QualifiedName Query
-
-The SQL++ INSERT statement is used to insert new data into a dataset.
-The data to be inserted comes from a SQL++ query expression.
-This expression can be as simple as a constant expression, or in general it
can be any legal SQL++ query.
-If the target dataset has an auto-generated primary key field, the insert
statement should not include a
-value for that field in it.
-(The system will automatically extend the provided object with this additional
field and a corresponding value.)
-Insertion will fail if the dataset already has data with the primary key
value(s) being inserted.
-
-Inserts are processed transactionally by the system.
-The transactional scope of each insert transaction is the insertion of a
single object plus its affiliated secondary index entries (if any).
-If the query part of an insert returns a single object, then the INSERT
statement will be a single, atomic transaction.
-If the query part returns multiple objects, each object being inserted will be
treated as a separate tranaction.
-The following example illustrates a query-based insertion.
-
-##### Example
-
- INSERT INTO UsersCopy (SELECT VALUE user FROM GleambookUsers user)
-
-### <a id="Upserts">UPSERTs</a>
-
- UpsertStatement ::= <UPSERT> <INTO> QualifiedName Query
-
-The SQL++ UPSERT statement syntactically mirrors the INSERT statement
discussed above.
-The difference lies in its semantics, which for UPSERT are "add or replace"
instead of the INSERT "add if not present, else error" semantics.
-Whereas an INSERT can fail if another object already exists with the specified
key, the analogous UPSERT will replace the previous object's value with that of
the new object in such cases.
-
-The following example illustrates a query-based upsert operation.
-
-##### Example
-
- UPSERT INTO UsersCopy (SELECT VALUE user FROM GleambookUsers user)
-
-*Editor's note: Upserts currently work in AQL but are not yet enabled (at the
moment) in SQL++.
-
-### <a id="Deletes">DELETEs</a>
-
- DeleteStatement ::= <DELETE> <FROM> QualifiedName ( ( <AS> )? Variable )?
( <WHERE> Expression )?
-
-The SQL++ DELETE statement is used to delete data from a target dataset.
-The data to be deleted is identified by a boolean expression involving the
variable bound to the target dataset in the DELETE statement.
-
-Deletes are processed transactionally by the system.
-The transactional scope of each delete transaction is the deletion of a single
object plus its affiliated secondary index entries (if any).
-If the boolean expression for a delete identifies a single object, then the
DELETE statement itself will be a single, atomic transaction.
-If the expression identifies multiple objects, then each object deleted will
be handled as a separate transaction.
-
-The following examples illustrate single-object deletions.
-
-##### Example
-
- DELETE FROM GleambookUsers user WHERE user.id = 8;
-
-##### Example
-
- DELETE FROM GleambookUsers WHERE id = 5;
-
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_dml.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_dml.md
new file mode 100644
index 0000000..f2c404e
--- /dev/null
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_dml.md
@@ -0,0 +1,81 @@
+<!--
+ ! Licensed to the Apache Software Foundation (ASF) under one
+ ! or more contributor license agreements. See the NOTICE file
+ ! distributed with this work for additional information
+ ! regarding copyright ownership. The ASF licenses this file
+ ! to you under the Apache License, Version 2.0 (the
+ ! "License"); you may not use this file except in compliance
+ ! with the License. You may obtain a copy of the License at
+ !
+ ! http://www.apache.org/licenses/LICENSE-2.0
+ !
+ ! Unless required by applicable law or agreed to in writing,
+ ! software distributed under the License is distributed on an
+ ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ! KIND, either express or implied. See the License for the
+ ! specific language governing permissions and limitations
+ ! under the License.
+ !-->
+
+## <a id="Modification_statements">Modification statements</a>
+
+### <a id="Inserts">INSERTs</a>
+
+ InsertStatement ::= <INSERT> <INTO> QualifiedName Query
+
+The SQL++ INSERT statement is used to insert new data into a dataset.
+The data to be inserted comes from a SQL++ query expression.
+This expression can be as simple as a constant expression, or in general it
can be any legal SQL++ query.
+If the target dataset has an auto-generated primary key field, the insert
statement should not include a
+value for that field in it.
+(The system will automatically extend the provided object with this additional
field and a corresponding value.)
+Insertion will fail if the dataset already has data with the primary key
value(s) being inserted.
+
+Inserts are processed transactionally by the system.
+The transactional scope of each insert transaction is the insertion of a
single object plus its affiliated secondary index entries (if any).
+If the query part of an insert returns a single object, then the INSERT
statement will be a single, atomic transaction.
+If the query part returns multiple objects, each object being inserted will be
treated as a separate tranaction.
+The following example illustrates a query-based insertion.
+
+##### Example
+
+ INSERT INTO UsersCopy (SELECT VALUE user FROM GleambookUsers user)
+
+### <a id="Upserts">UPSERTs</a>
+
+ UpsertStatement ::= <UPSERT> <INTO> QualifiedName Query
+
+The SQL++ UPSERT statement syntactically mirrors the INSERT statement
discussed above.
+The difference lies in its semantics, which for UPSERT are "add or replace"
instead of the INSERT "add if not present, else error" semantics.
+Whereas an INSERT can fail if another object already exists with the specified
key, the analogous UPSERT will replace the previous object's value with that of
the new object in such cases.
+
+The following example illustrates a query-based upsert operation.
+
+##### Example
+
+ UPSERT INTO UsersCopy (SELECT VALUE user FROM GleambookUsers user)
+
+*Editor's note: Upserts currently work in AQL but are not yet enabled (at the
moment) in SQL++.
+
+### <a id="Deletes">DELETEs</a>
+
+ DeleteStatement ::= <DELETE> <FROM> QualifiedName ( ( <AS> )? Variable )?
( <WHERE> Expression )?
+
+The SQL++ DELETE statement is used to delete data from a target dataset.
+The data to be deleted is identified by a boolean expression involving the
variable bound to the target dataset in the DELETE statement.
+
+Deletes are processed transactionally by the system.
+The transactional scope of each delete transaction is the deletion of a single
object plus its affiliated secondary index entries (if any).
+If the boolean expression for a delete identifies a single object, then the
DELETE statement itself will be a single, atomic transaction.
+If the expression identifies multiple objects, then each object deleted will
be handled as a separate transaction.
+
+The following examples illustrate single-object deletions.
+
+##### Example
+
+ DELETE FROM GleambookUsers user WHERE user.id = 8;
+
+##### Example
+
+ DELETE FROM GleambookUsers WHERE id = 5;
+
diff --git
a/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_function_removal.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_function_removal.md
new file mode 100644
index 0000000..7b2c603
--- /dev/null
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_function_removal.md
@@ -0,0 +1,87 @@
+<!--
+ ! Licensed to the Apache Software Foundation (ASF) under one
+ ! or more contributor license agreements. See the NOTICE file
+ ! distributed with this work for additional information
+ ! regarding copyright ownership. The ASF licenses this file
+ ! to you under the Apache License, Version 2.0 (the
+ ! "License"); you may not use this file except in compliance
+ ! with the License. You may obtain a copy of the License at
+ !
+ ! http://www.apache.org/licenses/LICENSE-2.0
+ !
+ ! Unless required by applicable law or agreed to in writing,
+ ! software distributed under the License is distributed on an
+ ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ! KIND, either express or implied. See the License for the
+ ! specific language governing permissions and limitations
+ ! under the License.
+ !-->
+
+### <a id="Functions"> Functions</a>
+
+The create function statement creates a **named** function that can then be
used and reused in SQL++ queries.
+The body of a function can be any SQL++ expression involving the function's
parameters.
+
+ FunctionSpecification ::= "FUNCTION" FunctionOrTypeName IfNotExists
ParameterList "{" Expression "}"
+
+The following is an example of a CREATE FUNCTION statement which is similar to
our earlier DECLARE FUNCTION example.
+It differs from that example in that it results in a function that is
persistently registered by name in the specified dataverse (the current
dataverse being used, if not otherwise specified).
+
+##### Example
+
+ CREATE FUNCTION friendInfo(userId) {
+ (SELECT u.id, u.name, len(u.friendIds) AS friendCount
+ FROM GleambookUsers u
+ WHERE u.id = userId)[0]
+ };
+
+### <a id="Removal"> Removal</a>
+
+ DropStatement ::= "DROP" ( "DATAVERSE" Identifier IfExists
+ | "TYPE" FunctionOrTypeName IfExists
+ | "DATASET" QualifiedName IfExists
+ | "INDEX" DoubleQualifiedName IfExists
+ | "FUNCTION" FunctionSignature IfExists )
+ IfExists ::= ( "IF" "EXISTS" )?
+
+The DROP statement in SQL++ is the inverse of the CREATE statement. It can be
used to drop dataverses, datatypes, datasets, indexes, and functions.
+
+The following examples illustrate some uses of the DROP statement.
+
+##### Example
+
+ DROP DATASET GleambookUsers IF EXISTS;
+
+ DROP INDEX GleambookMessages.gbSenderLocIndex;
+
+ DROP TYPE TinySocial2.GleambookUserType;
+
+ DROP FUNCTION friendInfo@1;
+
+ DROP DATAVERSE TinySocial;
+
+When an artifact is dropped, it will be droppped from the current dataverse if
none is specified
+(see the DROP DATASET example above) or from the specified dataverse (see the
DROP TYPE example above)
+if one is specified by fully qualifying the artifact name in the DROP
statement.
+When specifying an index to drop, the index name must be qualified by the
dataset that it indexes.
+When specifying a function to drop, since SQL++ allows functions to be
overloaded by their number of arguments,
+the identifying name of the function to be dropped must explicitly include
that information.
+(`friendInfo@1` above denotes the 1-argument function named friendInfo in the
current dataverse.)
+
+### <a id="Load_statement">Load Statement</a>
+
+ LoadStatement ::= <LOAD> <DATASET> QualifiedName <USING> AdapterName
Configuration ( <PRE-SORTED> )?
+
+The LOAD statement is used to initially populate a dataset via bulk loading of
data from an external file.
+An appropriate adapter must be selected to handle the nature of the desired
external data.
+The LOAD statement accepts the same adapters and the same parameters as
discussed earlier for External datasets.
+(See the [guide to external data](externaldata.html) for more information on
the available adapters.)
+If a dataset has an auto-generated primary key field, the file to be imported
should not include that field in it.
+
+The following example shows how to bulk load the GleambookUsers dataset from
an external file containing data that has been prepared in ADM (Asterix Data
Model) format.
+
+##### Example
+
+ LOAD DATASET GleambookUsers USING localfs
+
(("path"="127.0.0.1:///Users/bignosqlfan/tinysocialnew/gbu.adm"),("format"="adm"));
+
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_head.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_head.md
new file mode 100644
index 0000000..99954f7
--- /dev/null
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_head.md
@@ -0,0 +1,37 @@
+<!--
+ ! Licensed to the Apache Software Foundation (ASF) under one
+ ! or more contributor license agreements. See the NOTICE file
+ ! distributed with this work for additional information
+ ! regarding copyright ownership. The ASF licenses this file
+ ! to you under the Apache License, Version 2.0 (the
+ ! "License"); you may not use this file except in compliance
+ ! with the License. You may obtain a copy of the License at
+ !
+ ! http://www.apache.org/licenses/LICENSE-2.0
+ !
+ ! Unless required by applicable law or agreed to in writing,
+ ! software distributed under the License is distributed on an
+ ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ! KIND, either express or implied. See the License for the
+ ! specific language governing permissions and limitations
+ ! under the License.
+ !-->
+
+# <a id="DDL_and_DML_statements">5. DDL and DML statements</a>
+
+ Statement ::= ( SingleStatement ( ";" )? )* <EOF>
+ SingleStatement ::= DatabaseDeclaration
+ | FunctionDeclaration
+ | CreateStatement
+ | DropStatement
+ | LoadStatement
+ | SetStatement
+ | InsertStatement
+ | DeleteStatement
+ | Query ";"
+
+In addition to queries, an implementation of SQL++ needs to support statements
for data definition
+and manipulation purposes as well as controlling the context to be used in
evaluating SQL++ expressions.
+This section details the DDL and DML statements supported in the SQL++
language as realized today in
+Apache AsterixDB.
+
diff --git
a/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_nonenforced_index.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_nonenforced_index.md
new file mode 100644
index 0000000..518941d
--- /dev/null
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/5_ddl_nonenforced_index.md
@@ -0,0 +1,31 @@
+<!--
+ ! Licensed to the Apache Software Foundation (ASF) under one
+ ! or more contributor license agreements. See the NOTICE file
+ ! distributed with this work for additional information
+ ! regarding copyright ownership. The ASF licenses this file
+ ! to you under the Apache License, Version 2.0 (the
+ ! "License"); you may not use this file except in compliance
+ ! with the License. You may obtain a copy of the License at
+ !
+ ! http://www.apache.org/licenses/LICENSE-2.0
+ !
+ ! Unless required by applicable law or agreed to in writing,
+ ! software distributed under the License is distributed on an
+ ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ! KIND, either express or implied. See the License for the
+ ! specific language governing permissions and limitations
+ ! under the License.
+ !-->
+
+
+The following example creates an open btree index called gbReadTimeIdx on the
(non-predeclared) readTime
+field of the GleambookMessages dataset having datetime type.
+This index can be useful for accelerating exact-match queries, range search
queries,
+and joins involving the `readTime` field.
+The index is not enforced so that records that do not have the `readTime`
field or have a mismatched type on the field
+can still be inserted into the dataset.
+
+#### Example
+
+ CREATE INDEX gbReadTimeIdx ON GleambookMessages(readTime: datetime?);
+
--
To view, visit https://asterix-gerrit.ics.uci.edu/1892
To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I269750827ef529ac0e474fa0862635cc1f3aefed
Gerrit-PatchSet: 1
Gerrit-Project: asterixdb
Gerrit-Branch: master
Gerrit-Owner: Yingyi Bu <[email protected]>
