You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cassandra.apache.org by sl...@apache.org on 2016/06/27 18:34:08 UTC
[13/34] cassandra git commit: Fix CQL doc (incomplete)
http://git-wip-us.apache.org/repos/asf/cassandra/blob/5d65542b/doc/source/cql.rst
----------------------------------------------------------------------
diff --git a/doc/source/cql.rst b/doc/source/cql.rst
index 8d36258..8d185a2 100644
--- a/doc/source/cql.rst
+++ b/doc/source/cql.rst
@@ -16,15 +16,11 @@
.. highlight:: sql
+.. _UUID: https://en.wikipedia.org/wiki/Universally_unique_identifier
+
The Cassandra Query Language (CQL)
==================================
-CQL Syntax
-----------
-
-Preamble
-^^^^^^^^
-
This document describes the Cassandra Query Language (CQL) [#]_. Note that this document describes the last version of
the languages. However, the `changes <#changes>`_ section provides the diff between the different versions of CQL.
@@ -33,6 +29,13 @@ that reason, when used in this document, these terms (tables, rows and columns)
in SQL. But please note that as such, they do **not** refer to the concept of rows and columns found in the deprecated
thrift API (and earlier version 1 and 2 of CQL).
+.. _definitions:
+
+Definitions
+-----------
+
+.. _conventions:
+
Conventions
^^^^^^^^^^^
@@ -41,12 +44,18 @@ To aid in specifying the CQL syntax, we will use the following conventions in th
- Language rules will be given in an informal `BNF variant
<http://en.wikipedia.org/wiki/Backus%E2%80%93Naur_Form#Variants>`_ notation. In particular, we'll use square brakets
(``[ item ]``) for optional items, ``*`` and ``+`` for repeated items (where ``+`` imply at least one).
-- The grammar is provided for documentation purposes and leave some minor details out (only conveniences that can be
- ignored). For instance, the comma on the last column definition in a ``CREATE TABLE`` statement is optional but
- supported if present even though the grammar in this document suggests otherwise. Also, not everything accepted by the
- grammar will be valid CQL.
+- The grammar will also use the following convention for convenience: non-terminal term will be lowercase (and link to
+ their definition) while terminal keywords will be provided "all caps". Note however that keywords are
+ :ref:`identifiers` and are thus case insensitive in practice. We will also define some early construction using
+ regexp, which we'll indicate with ``re(<some regular expression>)``.
+- The grammar is provided for documentation purposes and leave some minor details out. For instance, the comma on the
+ last column definition in a ``CREATE TABLE`` statement is optional but supported if present even though the grammar in
+ this document suggests otherwise. Also, not everything accepted by the grammar is necessarily valid CQL.
- References to keywords or pieces of CQL code in running text will be shown in a ``fixed-width font``.
+
+.. _identifiers:
+
Identifiers and keywords
^^^^^^^^^^^^^^^^^^^^^^^^
@@ -54,7 +63,7 @@ The CQL language uses *identifiers* (or *names*) to identify tables, columns and
matching the regular expression ``[a-zA-Z][a-zA-Z0-9_]*``.
A number of such identifiers, like ``SELECT`` or ``WITH``, are *keywords*. They have a fixed meaning for the language
-and most are reserved. The list of those keywords can be found in `Appendix A <#appendixA>`__.
+and most are reserved. The list of those keywords can be found in :ref:`appendix-A`.
Identifiers and (unquoted) keywords are case insensitive. Thus ``SELECT`` is the same than ``select`` or ``sElEcT``, and
``myId`` is the same than ``myid`` or ``MYID``. A convention often used (in particular by the samples of this
@@ -69,1203 +78,1611 @@ sensitive (``"My Quoted Id"`` is *different* from ``"my quoted id"``). A fully l
``"myid"`` is equivalent to ``myid`` and to ``myId`` but different from ``"myId"``). Inside a quoted identifier, the
double-quote character can be repeated to escape it, so ``"foo "" bar"`` is a valid identifier.
-**Warning**: *quoted identifiers* allows to declare columns with arbitrary names, and those can sometime clash with
-specific names used by the server. For instance, when using conditional update, the server will respond with a
-result-set containing a special result named ``"[applied]"``. If you\u2019ve declared a column with such a name, this could
-potentially confuse some tools and should be avoided. In general, unquoted identifiers should be preferred but if you
-use quoted identifiers, it is strongly advised to avoid any name enclosed by squared brackets (like ``"[applied]"``) and
-any name that looks like a function call (like ``"f(x)"``).
+.. note:: *quoted identifiers* allows to declare columns with arbitrary names, and those can sometime clash with
+ specific names used by the server. For instance, when using conditional update, the server will respond with a
+ result-set containing a special result named ``"[applied]"``. If you\u2019ve declared a column with such a name, this
+ could potentially confuse some tools and should be avoided. In general, unquoted identifiers should be preferred but
+ if you use quoted identifiers, it is strongly advised to avoid any name enclosed by squared brackets (like
+ ``"[applied]"``) and any name that looks like a function call (like ``"f(x)"``).
-To sum up, we have:
+More formally, we have:
.. productionlist::
- identifer: `unquoted_identifier` | `quoted_identifier`
- unquoted_identifier: [a-zA-Z][a-zA-Z0-9_]*
- quoted_identifier: "\"" [any character where " can appear if doubled]+ "\""
+ identifier: `unquoted_identifier` | `quoted_identifier`
+ unquoted_identifier: re('[a-zA-Z][a-zA-Z0-9_]*')
+ quoted_identifier: '"' (any character where " can appear if doubled)+ '"'
+
+.. _constants:
Constants
^^^^^^^^^
-CQL defines the following kind of *constants*: strings, integers,
-floats, booleans, uuids and blobs:
-
-- A string constant is an arbitrary sequence of characters characters
- enclosed by single-quote(\ ``'``). One can include a single-quote in
- a string by repeating it, e.g. ``'It''s raining today'``. Those are
- not to be confused with quoted identifiers that use double-quotes.
-- An integer constant is defined by ``'-'?[0-9]+``.
-- A float constant is defined by
- ``'-'?[0-9]+('.'[0-9]*)?([eE][+-]?[0-9+])?``. On top of that, ``NaN``
- and ``Infinity`` are also float constants.
-- A boolean constant is either ``true`` or ``false`` up to
- case-insensitivity (i.e. ``True`` is a valid boolean constant).
-- A
- `UUID <http://en.wikipedia.org/wiki/Universally_unique_identifier>`__
- constant is defined by ``hex{8}-hex{4}-hex{4}-hex{4}-hex{12}`` where
- ``hex`` is an hexadecimal character, e.g. ``[0-9a-fA-F]`` and ``{4}``
- is the number of such characters.
-- A blob constant is an hexadecimal number defined by ``0[xX](hex)+``
- where ``hex`` is an hexadecimal character, e.g. ``[0-9a-fA-F]``.
-
-For how these constants are typed, see the `data types
-section <#types>`__.
+CQL defines the following kind of *constants*:
+
+.. productionlist::
+ constant: `string` | `integer` | `float` | `boolean` | `uuid` | `blob` | NULL
+ string: '\'' (any character where ' can appear if doubled)+ '\''
+ : '$$' (any character other than '$$') '$$'
+ integer: re('-?[0-9]+')
+ float: re('-?[0-9]+(\.[0-9]*)?([eE][+-]?[0-9+])?') | NAN | INFINITY
+ boolean: TRUE | FALSE
+ uuid: `hex`{8}-`hex`{4}-`hex`{4}-`hex`{4}-`hex`{12}
+ hex: re("[0-9a-fA-F]")
+ blob: '0' ('x' | 'X') `hex`+
+
+In other words:
+
+- A string constant is an arbitrary sequence of characters enclosed by single-quote(``'``). A single-quote
+ can be included by repeating it, e.g. ``'It''s raining today'``. Those are not to be confused with quoted
+ :ref:`identifiers` that use double-quotes. Alternatively, a string can be defined by enclosing the arbitrary sequence
+ of characters by two dollar characters, in which case single-quote can be use without escaping (``$$It's raining
+ today$$``). That latter form is often used when defining :ref:`user-defined functions <udfs>` to avoid having to
+ escape single-quote characters in function body (as they are more likely to occur than ``$$``).
+- Integer, float and boolean constant are defined as expected. Note however than float allows the special ``NaN`` and
+ ``Infinity`` constants.
+- CQL supports UUID_ constants.
+- Blobs content are provided in hexadecimal and prefixed by ``0x``.
+- The special ``NULL`` constant denotes the absence of value.
+
+For how these constants are typed, see the :ref:`data-types` section.
+
+Terms
+^^^^^
+
+CQL has the notion of a *term*, which denotes the kind of values that CQL support. Terms are defined by:
+
+.. productionlist::
+ term: `constant` | `literal` | `function_call` | `type_hint` | `bind_marker`
+ literal: `collection_literal` | `udt_literal` | `tuple_literal`
+ function_call: `identifier` '(' [ `term` (',' `term`)* ] ')'
+ type_hint: '(' `cql_type` `)` term
+ bind_marker: '?' | ':' `identifier`
+
+A term is thus one of:
+
+- A :ref:`constant <constants>`.
+- A literal for either :ref:`a collection <collections>`, :ref:`a user-defined type <udts>` or :ref:`a tuple <tuples>`
+ (see the linked sections for details).
+- A function call: see :ref:`the section on functions <functions>` for details on which :ref:`native function
+ <native-functions>` exists and how to define your own :ref:`user-defined ones <user-defined-functions>`.
+- A *type hint*: see the :ref:`related section <type-hints>` for details.
+- A bind marker, which denotes a variable to be bound at execution time. See the section on :ref:`prepared-statements`
+ for details. A bind marker can be either anonymous (``?``) or named (``:some_name``). The latter form provides a more
+ convenient way to refer to the variable for binding it and should generally be preferred.
+
Comments
^^^^^^^^
-A comment in CQL is a line beginning by either double dashes (``--``) or
-double slash (``//``).
+A comment in CQL is a line beginning by either double dashes (``--``) or double slash (``//``).
-Multi-line comments are also supported through enclosure within ``/*``
-and ``*/`` (but nesting is not supported).
+Multi-line comments are also supported through enclosure within ``/*`` and ``*/`` (but nesting is not supported).
-| bc(sample).
-| \u2014 This is a comment
-| // This is a comment too
-| /\* This is
-| a multi-line comment \*/
+::
+
+ \u2014 This is a comment
+ // This is a comment too
+ /* This is
+ a multi-line comment */
Statements
^^^^^^^^^^
-CQL consists of statements. As in SQL, these statements can be divided
-in 3 categories:
+CQL consists of statements that can be divided in the following categories:
-- Data definition statements, that allow to set and change the way data
- is stored.
-- Data manipulation statements, that allow to change data
-- Queries, to look up data
+- :ref:`data-definition` statements, to define and change how the data is stored (keyspaces and tables).
+- :ref:`data-manipulation` statements, for selecting, inserting and deleting data.
+- :ref:`index-and-views` statements.
+- :ref:`roles-and-permissions` statements.
+- :ref:`udfs` statements.
+- :ref:`udts` statements.
+- :ref:`triggers` statements.
-All statements end with a semicolon (``;``) but that semicolon can be
-omitted when dealing with a single statement. The supported statements
-are described in the following sections. When describing the grammar of
-said statements, we will reuse the non-terminal symbols defined below:
+All the statements are listed below and are described in the rest of this documentation (see links above):
-| bc(syntax)..
-| ::= any quoted or unquoted identifier, excluding reserved keywords
-| ::= ( \u2018.\u2019)?
+.. productionlist::
+ cql_statement: `statement` [ ';' ]
+ statement: `ddl_statement`
+ : | `dml_statement`
+ : | `index_or_view_statement`
+ : | `role_or_permission_statement`
+ : | `udf_statement`
+ : | `udt_statement`
+ : | `trigger_statement`
+ ddl_statement: `use_statement`
+ : | `create_keyspace_statement`
+ : | `alter_keyspace_statement`
+ : | `drop_keyspace_statement`
+ : | `create_table_statement`
+ : | `alter_table_statement`
+ : | `drop_table_statement`
+ : | `truncate_statement`
+ dml_statement: `select_statement`
+ : | `insert_statement`
+ : | `update_statement`
+ : | `delete_statement`
+ : | `batch_statement`
+ index_or_view_statement: `create_index_statement`
+ : | `drop_index_statement`
+ : | `create_materialized_view_statement`
+ : | `drop_materialized_view_statement`
+ role_or_permission_statement: `create_role_statement`
+ : | `alter_role_statement`
+ : | `drop_role_statement`
+ : | `grant_role_statement`
+ : | `revoke_role_statement`
+ : | `list_role_statement`
+ : | `grant_permission_statement`
+ : | `revoke_permission_statement`
+ : | `list_permission_statement`
+ : | `create_user_statement`
+ : | `alter_user_statement`
+ : | `drop_user_statement`
+ : | `list_user_statement`
+ udf_statement: `create_function_statement`
+ : | `drop_function_statement`
+ : | `create_aggregate_statement`
+ : | `drop_aggregate_statement`
+ udt_statement: `create_type_statement`
+ : | `alter_type_statement`
+ : | `drop_type_statement`
+ trigger_statement: `create_trigger_statement`
+ : | `drop_trigger_statement`
+
+.. _prepared-statements:
+
+Prepared Statements
+^^^^^^^^^^^^^^^^^^^
-| ::= a string constant
-| ::= an integer constant
-| ::= a float constant
-| ::= \|
-| ::= a uuid constant
-| ::= a boolean constant
-| ::= a blob constant
+CQL supports *prepared statements*. Prepared statements are an optimization that allows to parse a query only once but
+execute it multiple times with different concrete values.
-| ::=
-| \|
-| \|
-| \|
-| \|
-| ::= \u2018?\u2019
-| \| \u2018:\u2019
-| ::=
-| \|
-| \|
-| \| \u2018(\u2019 ( (\u2018,\u2019 )\*)? \u2018)\u2019
+Any statement that uses at least one bind marker (see :token:`bind_marker`) will need to be *prepared*. After which the statement
+can be *executed* by provided concrete values for each of its marker. The exact details of how a statement is prepared
+and then executed depends on the CQL driver used and you should refer to your driver documentation.
-| ::=
-| \|
-| \|
-| ::= \u2018{\u2019 ( \u2018:\u2019 ( \u2018,\u2019 \u2018:\u2019 )\* )? \u2018}\u2019
-| ::= \u2018{\u2019 ( ( \u2018,\u2019 )\* )? \u2018}\u2019
-| ::= \u2018[\u2019 ( ( \u2018,\u2019 )\* )? \u2018]\u2019
- ::=
+.. _data-types:
-| ::= (AND )\*
-| ::= \u2018=\u2019 ( \| \| )
-| p.
-| Please note that not every possible productions of the grammar above
- will be valid in practice. Most notably, ``<variable>`` and nested
- ``<collection-literal>`` are currently not allowed inside
- ``<collection-literal>``.
-
-A ``<variable>`` can be either anonymous (a question mark (``?``)) or
-named (an identifier preceded by ``:``). Both declare a bind variables
-for `prepared statements <#preparedStatement>`__. The only difference
-between an anymous and a named variable is that a named one will be
-easier to refer to (how exactly depends on the client driver used).
-
-The ``<properties>`` production is use by statement that create and
-alter keyspaces and tables. Each ``<property>`` is either a *simple*
-one, in which case it just has a value, or a *map* one, in which case
-it\u2019s value is a map grouping sub-options. The following will refer to
-one or the other as the *kind* (*simple* or *map*) of the property.
-
-A ``<tablename>`` will be used to identify a table. This is an
-identifier representing the table name that can be preceded by a
-keyspace name. The keyspace name, if provided, allow to identify a table
-in another keyspace than the currently active one (the currently active
-keyspace is set through the \ ``USE``\ statement).
-
-For supported ``<function>``, see the section on
-`functions <#functions>`__.
-
-Strings can be either enclosed with single quotes or two dollar
-characters. The second syntax has been introduced to allow strings that
-contain single quotes. Typical candidates for such strings are source
-code fragments for user-defined functions.
+Data Types
+----------
-*Sample:*
+CQL is a typed language and supports a rich set of data types, including :ref:`native types <native-types>`,
+:ref:`collection types <collections>`, :ref:`user-defined types <udts>`, :ref:`tuple types <tuples>` and :ref:`custom
+types <custom-types>`:
-| bc(sample)..
-| \u2018some string value\u2019
+.. productionlist::
+ cql_type: `native_type` | `collection_type` | `user_defined_type` | `tuple_type` | `custom_type`
-| $$double-dollar string can contain single \u2019 quotes$$
-| p.
-Prepared Statement
-^^^^^^^^^^^^^^^^^^
+.. _native-types:
-CQL supports *prepared statements*. Prepared statement is an
-optimization that allows to parse a query only once but execute it
-multiple times with different concrete values.
+Native Types
+^^^^^^^^^^^^
-In a statement, each time a column value is expected (in the data
-manipulation and query statements), a ``<variable>`` (see above) can be
-used instead. A statement with bind variables must then be *prepared*.
-Once it has been prepared, it can executed by providing concrete values
-for the bind variables. The exact procedure to prepare a statement and
-execute a prepared statement depends on the CQL driver used and is
-beyond the scope of this document.
+The native types supported by CQL are:
-In addition to providing column values, bind markers may be used to
-provide values for ``LIMIT``, ``TIMESTAMP``, and ``TTL`` clauses. If
-anonymous bind markers are used, the names for the query parameters will
-be ``[limit]``, ``[timestamp]``, and ``[ttl]``, respectively.
+.. productionlist::
+ native_type: ASCII
+ : | BIGINT
+ : | BLOB
+ : | BOOLEAN
+ : | COUNTER
+ : | DATE
+ : | DECIMAL
+ : | DOUBLE
+ : | FLOAT
+ : | INET
+ : | INT
+ : | SMALLINT
+ : | TEXT
+ : | TIME
+ : | TIMESTAMP
+ : | TIMEUUID
+ : | TINYINT
+ : | UUID
+ : | VARCHAR
+ : | VARINT
+
+The following table gives additional informations on the native data types, and on which kind of :ref:`constants
+<constants>` each type supports:
+
+=============== ===================== ==================================================================================
+ type constants supported description
+=============== ===================== ==================================================================================
+ ``ascii`` :token:`string` ASCII character string
+ ``bigint`` :token:`integer` 64-bit signed long
+ ``blob`` :token:`blob` Arbitrary bytes (no validation)
+ ``boolean`` :token:`boolean` Either ``true`` or ``false``
+ ``counter`` :token:`integer` Counter column (64-bit signed value). See :ref:`counters` for details
+ ``date`` :token:`integer`, A date (with no corresponding time value). See :ref:`dates` below for details
+ :token:`string`
+ ``decimal`` :token:`integer`, Variable-precision decimal
+ :token:`float`
+ ``double`` :token:`integer` 64-bit IEEE-754 floating point
+ :token:`float`
+ ``float`` :token:`integer`, 32-bit IEEE-754 floating point
+ :token:`float`
+ ``inet`` :token:`string` An IP address, either IPv4 (4 bytes long) or IPv6 (16 bytes long). Note that
+ there is no ``inet`` constant, IP address should be input as strings
+ ``int`` :token:`integer` 32-bit signed int
+ ``smallint`` :token:`integer` 16-bit signed int
+ ``text`` :token:`string` UTF8 encoded string
+ ``time`` :token:`integer`, A time (with no corresponding date value) with nanosecond precision. See
+ :token:`string` :ref:`times` below for details
+ ``timestamp`` :token:`integer`, A timestamp (date and time) with millisecond precision. See :ref:`timestamps`
+ :token:`string` below for details
+ ``timeuuid`` :token:`uuid` Version 1 UUID_, generally used as a \u201cconflict-free\u201d timestamp. Also see
+ :ref:`timeuuid-functions`
+ ``tinyint`` :token:`integer` 8-bit signed int
+ ``uuid`` :token:`uuid` A UUID_ (of any version)
+ ``varchar`` :token:`string` UTF8 encoded string
+ ``varint`` :token:`integer` Arbitrary-precision integer
+=============== ===================== ==================================================================================
+
+.. _counters:
-Data Definition
----------------
+Counters
+~~~~~~~~
-CREATE KEYSPACE
-^^^^^^^^^^^^^^^
+The ``counter`` type is used to define *counter columns*. A counter column is a column whose value is a 64-bit signed
+integer and on which 2 operations are supported: incrementing and decrementing (see the :ref:`UPDATE statement
+<update-statement>` for syntax). Note that the value of a counter cannot be set: a counter does not exist until first
+incremented/decremented, and that first increment/decrement is made as if the prior value was 0.
-*Syntax:*
+.. _counter-limitations:
-| bc(syntax)..
-| ::= CREATE KEYSPACE (IF NOT EXISTS)? WITH
-| p.
-| *Sample:*
+Counters have a number of important limitations:
-| bc(sample)..
-| CREATE KEYSPACE Excelsior
-| WITH replication = {\u2019class\u2019: \u2018SimpleStrategy\u2019, \u2018replication\_factor\u2019
- : 3};
-
-| CREATE KEYSPACE Excalibur
-| WITH replication = {\u2019class\u2019: \u2018NetworkTopologyStrategy\u2019, \u2018DC1\u2019 : 1,
- \u2018DC2\u2019 : 3}
-| AND durable\_writes = false;
-| p.
-| The ``CREATE KEYSPACE`` statement creates a new top-level *keyspace*.
- A keyspace is a namespace that defines a replication strategy and some
- options for a set of tables. Valid keyspaces names are identifiers
- composed exclusively of alphanumerical characters and whose length is
- lesser or equal to 32. Note that as identifiers, keyspace names are
- case insensitive: use a quoted identifier for case sensitive keyspace
- names.
-
-The supported ``<properties>`` for ``CREATE KEYSPACE`` are:
-
-+----------------------+------------+-------------+-----------+-------------------------------------------------------------------------------------------------------+
-| name | kind | mandatory | default | description |
-+======================+============+=============+===========+=======================================================================================================+
-| ``replication`` | *map* | yes | | The replication strategy and options to use for the keyspace. |
-+----------------------+------------+-------------+-----------+-------------------------------------------------------------------------------------------------------+
-| ``durable_writes`` | *simple* | no | true | Whether to use the commit log for updates on this keyspace (disable this option at your own risk!). |
-+----------------------+------------+-------------+-----------+-------------------------------------------------------------------------------------------------------+
-
-The ``replication`` ``<property>`` is mandatory. It must at least
-contains the ``'class'`` sub-option which defines the replication
-strategy class to use. The rest of the sub-options depends on that
-replication strategy class. By default, Cassandra support the following
-``'class'``:
-
-- ``'SimpleStrategy'``: A simple strategy that defines a simple
- replication factor for the whole cluster. The only sub-options
- supported is ``'replication_factor'`` to define that replication
- factor and is mandatory.
-- ``'NetworkTopologyStrategy'``: A replication strategy that allows to
- set the replication factor independently for each data-center. The
- rest of the sub-options are key-value pairs where each time the key
- is the name of a datacenter and the value the replication factor for
- that data-center.
-- ``'OldNetworkTopologyStrategy'``: A legacy replication strategy. You
- should avoid this strategy for new keyspaces and prefer
- ``'NetworkTopologyStrategy'``.
-
-Attempting to create an already existing keyspace will return an error
-unless the ``IF NOT EXISTS`` option is used. If it is used, the
-statement will be a no-op if the keyspace already exists.
+- They cannot be used for columns part of the ``PRIMARY KEY`` of a table.
+- A table that contains a counter can only contain counters. In other words, either all the columns of a table outside
+ the ``PRIMARY KEY`` have the ``counter`` type, or none of them have it.
+- Counters do not support :ref:`expiration <ttls>`.
+- The deletion of counters is supported, but is only guaranteed to work the first time you delete a counter. In other
+ words, you should not re-update a counter that you have deleted (if you do, proper behavior is not guaranteed).
+- Counter updates are, by nature, not `idemptotent <https://en.wikipedia.org/wiki/Idempotence>`__. An important
+ consequence is that if a counter update fails unexpectedly (timeout or loss of connection to the coordinator node),
+ the client has no way to know if the update has been applied or not. In particular, replaying the update may or may
+ not lead to an over count.
-USE
-^^^
+.. _timestamps:
-*Syntax:*
+Working with timestamps
+^^^^^^^^^^^^^^^^^^^^^^^
-bc(syntax). ::= USE
+Values of the ``timestamp`` type are encoded as 64-bit signed integers representing a number of milliseconds since the
+standard base time known as `the epoch <https://en.wikipedia.org/wiki/Unix_time>`__: January 1 1970 at 00:00:00 GMT.
-*Sample:*
+Timestamps can be input in CQL either using their value as an :token:`integer`, or using a :token:`string` that
+represents an `ISO 8601 <https://en.wikipedia.org/wiki/ISO_8601>`__ date. For instance, all of the values below are
+valid ``timestamp`` values for Mar 2, 2011, at 04:05:00 AM, GMT:
-bc(sample). USE myApp;
+- ``1299038700000``
+- ``'2011-02-03 04:05+0000'``
+- ``'2011-02-03 04:05:00+0000'``
+- ``'2011-02-03 04:05:00.000+0000'``
+- ``'2011-02-03T04:05+0000'``
+- ``'2011-02-03T04:05:00+0000'``
+- ``'2011-02-03T04:05:00.000+0000'``
-The ``USE`` statement takes an existing keyspace name as argument and
-set it as the per-connection current working keyspace. All subsequent
-keyspace-specific actions will be performed in the context of the
-selected keyspace, unless `otherwise specified <#statements>`__, until
-another USE statement is issued or the connection terminates.
+The ``+0000`` above is an RFC 822 4-digit time zone specification; ``+0000`` refers to GMT. US Pacific Standard Time is
+``-0800``. The time zone may be omitted if desired (``'2011-02-03 04:05:00'``), and if so, the date will be interpreted
+as being in the time zone under which the coordinating Cassandra node is configured. There are however difficulties
+inherent in relying on the time zone configuration being as expected, so it is recommended that the time zone always be
+specified for timestamps when feasible.
-ALTER KEYSPACE
-^^^^^^^^^^^^^^
+The time of day may also be omitted (``'2011-02-03'`` or ``'2011-02-03+0000'``), in which case the time of day will
+default to 00:00:00 in the specified or default time zone. However, if only the date part is relevant, consider using
+the :ref:`date <dates>` type.
-*Syntax:*
+.. _dates:
-| bc(syntax)..
-| ::= ALTER KEYSPACE WITH
-| p.
-| *Sample:*
+Working with dates
+^^^^^^^^^^^^^^^^^^
-| bc(sample)..
-| ALTER KEYSPACE Excelsior
-| WITH replication = {\u2019class\u2019: \u2018SimpleStrategy\u2019, \u2018replication\_factor\u2019
- : 4};
+Values of the ``date`` type are encoded as 32-bit unsigned integers representing a number of days with \u201cthe epoch\u201d at
+the center of the range (2^31). Epoch is January 1st, 1970
-The ``ALTER KEYSPACE`` statement alters the properties of an existing
-keyspace. The supported ``<properties>`` are the same as for the
-```CREATE KEYSPACE`` <#createKeyspaceStmt>`__ statement.
+As for :ref:`timestamp <timestamps>`, a date can be input either as an :token:`integer` or using a date
+:token:`string`. In the later case, the format should be ``yyyy-mm-dd`` (so ``'2011-02-03'`` for instance).
-DROP KEYSPACE
-^^^^^^^^^^^^^
+.. _times:
-*Syntax:*
+Working with times
+^^^^^^^^^^^^^^^^^^
-bc(syntax). ::= DROP KEYSPACE ( IF EXISTS )?
+Values of the ``time`` type are encoded as 64-bit signed integers representing the number of nanoseconds since midnight.
-*Sample:*
+As for :ref:`timestamp <timestamps>`, a time can be input either as an :token:`integer` or using a :token:`string`
+representing the time. In the later case, the format should be ``hh:mm:ss[.fffffffff]`` (where the sub-second precision
+is optional and if provided, can be less than the nanosecond). So for instance, the following are valid inputs for a
+time:
-bc(sample). DROP KEYSPACE myApp;
+- ``'08:12:54'``
+- ``'08:12:54.123'``
+- ``'08:12:54.123456'``
+- ``'08:12:54.123456789'``
-A ``DROP KEYSPACE`` statement results in the immediate, irreversible
-removal of an existing keyspace, including all column families in it,
-and all data contained in those column families.
-If the keyspace does not exists, the statement will return an error,
-unless ``IF EXISTS`` is used in which case the operation is a no-op.
+.. _collections:
-CREATE TABLE
-^^^^^^^^^^^^
+Collections
+^^^^^^^^^^^
-*Syntax:*
+CQL supports 3 kind of collections: :ref:`maps`, :ref:`sets` and :ref:`lists`. The types of those collections is defined
+by:
-| bc(syntax)..
-| ::= CREATE ( TABLE \| COLUMNFAMILY ) ( IF NOT EXISTS )?
-| \u2018(\u2019 ( \u2018,\u2019 )\* \u2018)\u2019
-| ( WITH ( AND )\* )?
+.. productionlist::
+ collection_type: MAP '<' `cql_type` ',' `cql_type` '>'
+ : | SET '<' `cql_type` '>'
+ : | LIST '<' `cql_type` '>'
-| ::= ( STATIC )? ( PRIMARY KEY )?
-| \| PRIMARY KEY \u2018(\u2019 ( \u2018,\u2019 )\* \u2018)\u2019
+and their values can be inputd using collection literals:
-| ::=
-| \| \u2018(\u2019 (\u2018,\u2019 )\* \u2018)\u2019
+.. productionlist::
+ collection_literal: `map_literal` | `set_literal` | `list_literal`
+ map_literal: '{' [ `term` ':' `term` (',' `term` : `term`)* ] '}'
+ set_literal: '{' [ `term` (',' `term`)* ] '}'
+ list_literal: '[' [ `term` (',' `term`)* ] ']'
-| ::=
-| \| COMPACT STORAGE
-| \| CLUSTERING ORDER
-| p.
-| *Sample:*
+Note however that neither :token:`bind_marker` nor ``NULL`` are supported inside collection literals.
-| bc(sample)..
-| CREATE TABLE monkeySpecies (
-| species text PRIMARY KEY,
-| common\_name text,
-| population varint,
-| average\_size int
-| ) WITH comment=\u2018Important biological records\u2019
-| AND read\_repair\_chance = 1.0;
-
-| CREATE TABLE timeline (
-| userid uuid,
-| posted\_month int,
-| posted\_time uuid,
-| body text,
-| posted\_by text,
-| PRIMARY KEY (userid, posted\_month, posted\_time)
-| ) WITH compaction = { \u2018class\u2019 : \u2018LeveledCompactionStrategy\u2019 };
-| p.
-| The ``CREATE TABLE`` statement creates a new table. Each such table is
- a set of *rows* (usually representing related entities) for which it
- defines a number of properties. A table is defined by a
- `name <#createTableName>`__, it defines the columns composing rows of
- the table and have a number of `options <#createTableOptions>`__. Note
- that the ``CREATE COLUMNFAMILY`` syntax is supported as an alias for
- ``CREATE TABLE`` (for historical reasons).
-
-Attempting to create an already existing table will return an error
-unless the ``IF NOT EXISTS`` option is used. If it is used, the
-statement will be a no-op if the table already exists.
+Noteworthy characteristics
+~~~~~~~~~~~~~~~~~~~~~~~~~~
-``<tablename>``
-^^^^^^^^^^^^^^^
+Collections are meant for storing/denormalizing relatively small amount of data. They work well for things like \u201cthe
+phone numbers of a given user\u201d, \u201clabels applied to an email\u201d, etc. But when items are expected to grow unbounded (\u201call
+messages sent by a user\u201d, \u201cevents registered by a sensor\u201d...), then collections are not appropriate and a specific table
+(with clustering columns) should be used. Concretely, (non-frozen) collections have the following noteworthy
+characteristics and limitations:
-Valid table names are the same as valid `keyspace
-names <#createKeyspaceStmt>`__ (up to 32 characters long alphanumerical
-identifiers). If the table name is provided alone, the table is created
-within the current keyspace (see \ ``USE``\ ), but if it is prefixed by
-an existing keyspace name (see ```<tablename>`` <#statements>`__
-grammar), it is created in the specified keyspace (but does **not**
-change the current keyspace).
+- Individual collections are not indexed internally. Which means that even to access a single element of a collection,
+ the while collection has to be read (and reading one is not paged internally).
+- While insertion operations on sets and maps never incur a read-before-write internally, some operations on lists do.
+ Further, some lists operations are not idempotent by nature (see the section on :ref:`lists <lists>` below for
+ details), making their retry in case of timeout problematic. It is thus advised to prefer sets over lists when
+ possible.
-``<column-definition>``
-^^^^^^^^^^^^^^^^^^^^^^^
+Please note that while some of those limitations may or may not be removed/improved upon in the future, it is a
+anti-pattern to use a (single) collection to store large amounts of data.
-A ``CREATE TABLE`` statement defines the columns that rows of the table
-can have. A *column* is defined by its name (an identifier) and its type
-(see the `data types <#types>`__ section for more details on allowed
-types and their properties).
+.. _maps:
-Within a table, a row is uniquely identified by its ``PRIMARY KEY`` (or
-more simply the key), and hence all table definitions **must** define a
-PRIMARY KEY (and only one). A ``PRIMARY KEY`` is composed of one or more
-of the columns defined in the table. If the ``PRIMARY KEY`` is only one
-column, this can be specified directly after the column definition.
-Otherwise, it must be specified by following ``PRIMARY KEY`` by the
-comma-separated list of column names composing the key within
-parenthesis. Note that:
+Maps
+~~~~
-| bc(sample).
-| CREATE TABLE t (
-| k int PRIMARY KEY,
-| other text
-| )
+A ``map`` is a (sorted) set of key-value pairs, where keys are unique and the map is sorted by its keys. You can define
+and insert a map with::
-is equivalent to
+ CREATE TABLE users (
+ id text PRIMARY KEY,
+ name text,
+ favs map<text, text> // A map of text keys, and text values
+ );
-| bc(sample).
-| CREATE TABLE t (
-| k int,
-| other text,
-| PRIMARY KEY (k)
-| )
+ INSERT INTO users (id, name, favs)
+ VALUES ('jsmith', 'John Smith', { 'fruit' : 'Apple', 'band' : 'Beatles' });
-Partition key and clustering columns
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-
-In CQL, the order in which columns are defined for the ``PRIMARY KEY``
-matters. The first column of the key is called the *partition key*. It
-has the property that all the rows sharing the same partition key (even
-across table in fact) are stored on the same physical node. Also,
-insertion/update/deletion on rows sharing the same partition key for a
-given table are performed *atomically* and in *isolation*. Note that it
-is possible to have a composite partition key, i.e. a partition key
-formed of multiple columns, using an extra set of parentheses to define
-which columns forms the partition key.
-
-The remaining columns of the ``PRIMARY KEY`` definition, if any, are
-called \_\_clustering columns. On a given physical node, rows for a
-given partition key are stored in the order induced by the clustering
-columns, making the retrieval of rows in that clustering order
-particularly efficient (see \ ``SELECT``\ ).
-
-``STATIC`` columns
-^^^^^^^^^^^^^^^^^^
+ // Replace the existing map entirely.
+ UPDATE users SET favs = { 'fruit' : 'Banana' } WHERE id = 'jsmith';
-Some columns can be declared as ``STATIC`` in a table definition. A
-column that is static will be \u201cshared\u201d by all the rows belonging to the
-same partition (having the same partition key). For instance, in:
+Further, maps support:
-| bc(sample).
-| CREATE TABLE test (
-| pk int,
-| t int,
-| v text,
-| s text static,
-| PRIMARY KEY (pk, t)
-| );
-| INSERT INTO test(pk, t, v, s) VALUES (0, 0, \u2018val0\u2019, \u2018static0\u2019);
-| INSERT INTO test(pk, t, v, s) VALUES (0, 1, \u2018val1\u2019, \u2018static1\u2019);
-| SELECT \* FROM test WHERE pk=0 AND t=0;
-
-the last query will return ``'static1'`` as value for ``s``, since ``s``
-is static and thus the 2nd insertion modified this \u201cshared\u201d value. Note
-however that static columns are only static within a given partition,
-and if in the example above both rows where from different partitions
-(i.e. if they had different value for ``pk``), then the 2nd insertion
-would not have modified the value of ``s`` for the first row.
-
-A few restrictions applies to when static columns are allowed:
-
-- tables with the ``COMPACT STORAGE`` option (see below) cannot have
- them
-- a table without clustering columns cannot have static columns (in a
- table without clustering columns, every partition has only one row,
- and so every column is inherently static).
-- only non ``PRIMARY KEY`` columns can be static
+- Updating or inserting one or more elements::
-``<option>``
-^^^^^^^^^^^^
+ UPDATE users SET favs['author'] = 'Ed Poe' WHERE id = 'jsmith';
+ UPDATE users SET favs = favs + { 'movie' : 'Cassablanca', 'band' : 'ZZ Top' } WHERE id = 'jsmith';
-The ``CREATE TABLE`` statement supports a number of options that
-controls the configuration of a new table. These options can be
-specified after the ``WITH`` keyword.
-
-The first of these option is ``COMPACT STORAGE``. This option is mainly
-targeted towards backward compatibility for definitions created before
-CQL3 (see
-`www.datastax.com/dev/blog/thrift-to-cql3 <http://www.datastax.com/dev/blog/thrift-to-cql3>`__
-for more details). The option also provides a slightly more compact
-layout of data on disk but at the price of diminished flexibility and
-extensibility for the table. Most notably, ``COMPACT STORAGE`` tables
-cannot have collections nor static columns and a ``COMPACT STORAGE``
-table with at least one clustering column supports exactly one (as in
-not 0 nor more than 1) column not part of the ``PRIMARY KEY`` definition
-(which imply in particular that you cannot add nor remove columns after
-creation). For those reasons, ``COMPACT STORAGE`` is not recommended
-outside of the backward compatibility reason evoked above.
-
-Another option is ``CLUSTERING ORDER``. It allows to define the ordering
-of rows on disk. It takes the list of the clustering column names with,
-for each of them, the on-disk order (Ascending or descending). Note that
-this option affects `what ``ORDER BY`` are allowed during
-``SELECT`` <#selectOrderBy>`__.
-
-Table creation supports the following other ``<property>``:
+- Removing one or more element (if an element doesn't exist, removing it is a no-op but no error is thrown)::
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| option | kind | default | description |
-+==================================+============+===============+=================================================================================================================================================================================================================================+
-| ``comment`` | *simple* | none | A free-form, human-readable comment. |
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``read_repair_chance`` | *simple* | 0.1 | The probability with which to query extra nodes (e.g. more nodes than required by the consistency level) for the purpose of read repairs. |
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``dclocal_read_repair_chance`` | *simple* | 0 | The probability with which to query extra nodes (e.g. more nodes than required by the consistency level) belonging to the same data center than the read coordinator for the purpose of read repairs. |
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``gc_grace_seconds`` | *simple* | 864000 | Time to wait before garbage collecting tombstones (deletion markers). |
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``bloom_filter_fp_chance`` | *simple* | 0.00075 | The target probability of false positive of the sstable bloom filters. Said bloom filters will be sized to provide the provided probability (thus lowering this value impact the size of bloom filters in-memory and on-disk) |
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``default_time_to_live`` | *simple* | 0 | The default expiration time (\u201cTTL\u201d) in seconds for a table. |
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``compaction`` | *map* | *see below* | Compaction options, see \u201cbelow\u201d:#compactionOptions. |
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``compression`` | *map* | *see below* | Compression options, see \u201cbelow\u201d:#compressionOptions. |
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``caching`` | *map* | *see below* | Caching options, see \u201cbelow\u201d:#cachingOptions. |
-+----------------------------------+------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+ DELETE favs['author'] FROM users WHERE id = 'jsmith';
+ UPDATE users SET favs = favs - { 'movie', 'band'} WHERE id = 'jsmith';
-Compaction options
-^^^^^^^^^^^^^^^^^^
+ Note that for removing multiple elements in a ``map``, you remove from it a ``set`` of keys.
-The ``compaction`` property must at least define the ``'class'``
-sub-option, that defines the compaction strategy class to use. The
-default supported class are ``'SizeTieredCompactionStrategy'``,
-``'LeveledCompactionStrategy'``, ``'DateTieredCompactionStrategy'`` and
-``'TimeWindowCompactionStrategy'``. Custom strategy can be provided by
-specifying the full class name as a `string constant <#constants>`__.
-The rest of the sub-options depends on the chosen class. The sub-options
-supported by the default classes are:
+Lastly, TTLs are allowed for both ``INSERT`` and ``UPDATE``, but in both case the TTL set only apply to the newly
+inserted/updated elements. In other words::
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| option | supported compaction strategy | default | description |
-+======================================+=================================+================+========================================================================================================================================================================================================================================================================================================================================+
-| ``enabled`` | *all* | true | A boolean denoting whether compaction should be enabled or not. |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``tombstone_threshold`` | *all* | 0.2 | A ratio such that if a sstable has more than this ratio of gcable tombstones over all contained columns, the sstable will be compacted (with no other sstables) for the purpose of purging those tombstones. |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``tombstone_compaction_interval`` | *all* | 1 day | The minimum time to wait after an sstable creation time before considering it for \u201ctombstone compaction\u201d, where \u201ctombstone compaction\u201d is the compaction triggered if the sstable has more gcable tombstones than ``tombstone_threshold``. |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``unchecked_tombstone_compaction`` | *all* | false | Setting this to true enables more aggressive tombstone compactions - single sstable tombstone compactions will run without checking how likely it is that they will be successful. |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``min_sstable_size`` | SizeTieredCompactionStrategy | 50MB | The size tiered strategy groups SSTables to compact in buckets. A bucket groups SSTables that differs from less than 50% in size. However, for small sizes, this would result in a bucketing that is too fine grained. ``min_sstable_size`` defines a size threshold (in bytes) below which all SSTables belong to one unique bucket |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``min_threshold`` | SizeTieredCompactionStrategy | 4 | Minimum number of SSTables needed to start a minor compaction. |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``max_threshold`` | SizeTieredCompactionStrategy | 32 | Maximum number of SSTables processed by one minor compaction. |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``bucket_low`` | SizeTieredCompactionStrategy | 0.5 | Size tiered consider sstables to be within the same bucket if their size is within [average\_size \* ``bucket_low``, average\_size \* ``bucket_high`` ] (i.e the default groups sstable whose sizes diverges by at most 50%) |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``bucket_high`` | SizeTieredCompactionStrategy | 1.5 | Size tiered consider sstables to be within the same bucket if their size is within [average\_size \* ``bucket_low``, average\_size \* ``bucket_high`` ] (i.e the default groups sstable whose sizes diverges by at most 50%). |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``sstable_size_in_mb`` | LeveledCompactionStrategy | 5MB | The target size (in MB) for sstables in the leveled strategy. Note that while sstable sizes should stay less or equal to ``sstable_size_in_mb``, it is possible to exceptionally have a larger sstable as during compaction, data for a given partition key are never split into 2 sstables |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``timestamp_resolution`` | DateTieredCompactionStrategy | MICROSECONDS | The timestamp resolution used when inserting data, could be MILLISECONDS, MICROSECONDS etc (should be understandable by Java TimeUnit) - don\u2019t change this unless you do mutations with USING TIMESTAMP (or equivalent directly in the client) |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``base_time_seconds`` | DateTieredCompactionStrategy | 60 | The base size of the time windows. |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``max_sstable_age_days`` | DateTieredCompactionStrategy | 365 | SSTables only containing data that is older than this will never be compacted. |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``timestamp_resolution`` | TimeWindowCompactionStrategy | MICROSECONDS | The timestamp resolution used when inserting data, could be MILLISECONDS, MICROSECONDS etc (should be understandable by Java TimeUnit) - don\u2019t change this unless you do mutations with USING TIMESTAMP (or equivalent directly in the client) |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``compaction_window_unit`` | TimeWindowCompactionStrategy | DAYS | The Java TimeUnit used for the window size, set in conjunction with ``compaction_window_size``. Must be one of DAYS, HOURS, MINUTES |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``compaction_window_size`` | TimeWindowCompactionStrategy | 1 | The number of ``compaction_window_unit`` units that make up a time window. |
-+--------------------------------------+---------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+ UPDATE users USING TTL 10 SET favs['color'] = 'green' WHERE id = 'jsmith';
-Compression options
-^^^^^^^^^^^^^^^^^^^
+will only apply the TTL to the ``{ 'color' : 'green' }`` record, the rest of the map remaining unaffected.
-For the ``compression`` property, the following sub-options are
-available:
-+------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| option | default | description |
-+========================+=================+=======================================================================================================================================================================================================================================================================================================================================================================================================================+
-| ``class`` | LZ4Compressor | The compression algorithm to use. Default compressor are: LZ4Compressor, SnappyCompressor and DeflateCompressor. Use ``'enabled' : false`` to disable compression. Custom compressor can be provided by specifying the full class name as a \u201cstring constant\u201d:#constants. |
-+------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``enabled`` | true | By default compression is enabled. To disable it, set ``enabled`` to ``false`` |
-+------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-|``chunk_length_in_kb`` | 64KB | On disk SSTables are compressed by block (to allow random reads). This defines the size (in KB) of said block. Bigger values may improve the compression rate, but increases the minimum size of data to be read from disk for a read |
-+------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``crc_check_chance`` | 1.0 | When compression is enabled, each compressed block includes a checksum of that block for the purpose of detecting disk bitrot and avoiding the propagation of corruption to other replica. This option defines the probability with which those checksums are checked during read. By default they are always checked. Set to 0 to disable checksum checking and to 0.5 for instance to check them every other read |
-+------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+.. _sets:
-Caching options
-^^^^^^^^^^^^^^^
+Sets
+~~~~
-For the ``caching`` property, the following sub-options are available:
+A ``set`` is a (sorted) collection of unique values. You can define and insert a map with::
-+--------------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| option | default | description |
-+==========================+===========+============================================================================================================================================================================================================================================================================+
-| ``keys`` | ALL | Whether to cache keys (\u201ckey cache\u201d) for this table. Valid values are: ``ALL`` and ``NONE``. |
-+--------------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| ``rows_per_partition`` | NONE | The amount of rows to cache per partition (\u201crow cache\u201d). If an integer ``n`` is specified, the first ``n`` queried rows of a partition will be cached. Other possible options are ``ALL``, to cache all rows of a queried partition, or ``NONE`` to disable row caching. |
-+--------------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+ CREATE TABLE images (
+ name text PRIMARY KEY,
+ owner text,
+ tags set<text> // A set of text values
+ );
-Other considerations:
-^^^^^^^^^^^^^^^^^^^^^
+ INSERT INTO images (name, owner, tags)
+ VALUES ('cat.jpg', 'jsmith', { 'pet', 'cute' });
-- When `inserting <#insertStmt>`__ / `updating <#updateStmt>`__ a given
- row, not all columns needs to be defined (except for those part of
- the key), and missing columns occupy no space on disk. Furthermore,
- adding new columns (see \ ``ALTER TABLE``\ ) is a constant time
- operation. There is thus no need to try to anticipate future usage
- (or to cry when you haven\u2019t) when creating a table.
+ // Replace the existing set entirely
+ UPDATE images SET tags = { 'kitten', 'cat\u2019, 'lol' } WHERE id = 'jsmith';
-ALTER TABLE
-^^^^^^^^^^^
+Further, sets support:
-*Syntax:*
+- Adding one or multiple elements (as this is a set, inserting an already existing element is a no-op)::
-| bc(syntax)..
-| ::= ALTER (TABLE \| COLUMNFAMILY)
-
-| ::= ALTER TYPE
-| \| ADD
-| \| ADD ( ( , )\* )
-| \| DROP
-| \| DROP ( ( , )\* )
-| \| WITH ( AND )\*
-| p.
-| *Sample:*
+ UPDATE images SET tags = tags + { 'gray', 'cuddly' } WHERE name = 'cat.jpg';
-| bc(sample)..
-| ALTER TABLE addamsFamily
-| ALTER lastKnownLocation TYPE uuid;
+- Removing one or multiple elements (if an element doesn't exist, removing it is a no-op but no error is thrown)::
-| ALTER TABLE addamsFamily
-| ADD gravesite varchar;
+ UPDATE images SET tags = tags - { 'cat' } WHERE name = 'cat.jpg';
-| ALTER TABLE addamsFamily
-| WITH comment = \u2018A most excellent and useful column family\u2019
-| AND read\_repair\_chance = 0.2;
-| p.
-| The ``ALTER`` statement is used to manipulate table definitions. It
- allows for adding new columns, dropping existing ones, changing the
- type of existing columns, or updating the table options. As with table
- creation, ``ALTER COLUMNFAMILY`` is allowed as an alias for
- ``ALTER TABLE``.
-
-The ``<tablename>`` is the table name optionally preceded by the
-keyspace name. The ``<instruction>`` defines the alteration to perform:
-
-- ``ALTER``: Update the type of a given defined column. Note that the
- type of the `clustering columns <#createTablepartitionClustering>`__
- can be modified only in very limited cases, as it induces the on-disk
- ordering of rows. Columns on which a `secondary
- index <#createIndexStmt>`__ is defined have the same restriction. To
- change the type of any other column, the column must already exist in
- type definition and its type should be compatible with the new type.
- No validation of existing data is performed. The compatibility table
- is available below.
-- ``ADD``: Adds a new column to the table. The ``<identifier>`` for the
- new column must not conflict with an existing column. Moreover,
- columns cannot be added to tables defined with the
- ``COMPACT STORAGE`` option.
-- ``DROP``: Removes a column from the table. Dropped columns will
- immediately become unavailable in the queries and will not be
- included in compacted sstables in the future. If a column is readded,
- queries won\u2019t return values written before the column was last
- dropped. It is assumed that timestamps represent actual time, so if
- this is not your case, you should NOT readd previously dropped
- columns. Columns can\u2019t be dropped from tables defined with the
- ``COMPACT STORAGE`` option.
-- ``WITH``: Allows to update the options of the table. The `supported
- ``<option>`` <#createTableOptions>`__ (and syntax) are the same as
- for the ``CREATE TABLE`` statement except that ``COMPACT STORAGE`` is
- not supported. Note that setting any ``compaction`` sub-options has
- the effect of erasing all previous ``compaction`` options, so you
- need to re-specify all the sub-options if you want to keep them. The
- same note applies to the set of ``compression`` sub-options.
+Lastly, as for :ref:`maps <maps>`, TTLs if used only apply to the newly inserted values.
-CQL type compatibility:
-^^^^^^^^^^^^^^^^^^^^^^^
+.. _lists:
-CQL data types may be converted only as the following table.
+Lists
+~~~~~
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-| Data type may be altered to: | Data type |
-+==============================================================================================================================================+=============+
-| timestamp | bigint |
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-| ascii, bigint, boolean, date, decimal, double, float, inet, int, smallint, text, time, timestamp, timeuuid, tinyint, uuid, varchar, varint | blob |
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-| int | date |
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-| ascii, varchar | text |
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-| bigint | time |
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-| bigint | timestamp |
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-| timeuuid | uuid |
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-| ascii, text | varchar |
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-| bigint, int, timestamp | varint |
-+----------------------------------------------------------------------------------------------------------------------------------------------+-------------+
-
-Clustering columns have stricter requirements, only the below
-conversions are allowed.
-
-+--------------------------------+-------------+
-| Data type may be altered to: | Data type |
-+================================+=============+
-| ascii, text, varchar | blob |
-+--------------------------------+-------------+
-| ascii, varchar | text |
-+--------------------------------+-------------+
-| ascii, text | varchar |
-+--------------------------------+-------------+
+.. note:: As mentioned above and further discussed at the end of this section, lists have limitations and specific
+ performance considerations that you should take into account before using them. In general, if you can use a
+ :ref:`set <sets>` instead of list, always prefer a set.
-DROP TABLE
-^^^^^^^^^^
+A ``list`` is a (sorted) collection of non-unique values where elements are ordered by there position in the list. You
+can define and insert a list with::
-*Syntax:*
+ CREATE TABLE plays (
+ id text PRIMARY KEY,
+ game text,
+ players int,
+ scores list<int> // A list of integers
+ )
-bc(syntax). ::= DROP TABLE ( IF EXISTS )?
+ INSERT INTO plays (id, game, players, scores)
+ VALUES ('123-afde', 'quake', 3, [17, 4, 2]);
-*Sample:*
+ // Replace the existing list entirely
+ UPDATE plays SET scores = [ 3, 9, 4] WHERE id = '123-afde';
-bc(sample). DROP TABLE worldSeriesAttendees;
+Further, lists support:
-The ``DROP TABLE`` statement results in the immediate, irreversible
-removal of a table, including all data contained in it. As for table
-creation, ``DROP COLUMNFAMILY`` is allowed as an alias for
-``DROP TABLE``.
+- Appending and prepending values to a list::
-If the table does not exist, the statement will return an error, unless
-``IF EXISTS`` is used in which case the operation is a no-op.
+ UPDATE plays SET players = 5, scores = scores + [ 14, 21 ] WHERE id = '123-afde';
+ UPDATE plays SET players = 6, scores = [ 3 ] + scores WHERE id = '123-afde';
-TRUNCATE
-^^^^^^^^
+- Setting the value at a particular position in the list. This imply that the list has a pre-existing element for that
+ position or an error will be thrown that the list is too small::
-*Syntax:*
+ UPDATE plays SET scores[1] = 7 WHERE id = '123-afde';
-bc(syntax). ::= TRUNCATE ( TABLE \| COLUMNFAMILY )?
+- Removing an element by its position in the list. This imply that the list has a pre-existing element for that position
+ or an error will be thrown that the list is too small. Further, as the operation removes an element from the list, the
+ list size will be diminished by 1, shifting the position of all the elements following the one deleted::
-*Sample:*
+ DELETE scores[1] FROM plays WHERE id = '123-afde';
-bc(sample). TRUNCATE superImportantData;
+- Deleting *all* the occurrences of particular values in the list (if a particular element doesn't occur at all in the
+ list, it is simply ignored and no error is thrown)::
-The ``TRUNCATE`` statement permanently removes all data from a table.
+ UPDATE plays SET scores = scores - [ 12, 21 ] WHERE id = '123-afde';
-CREATE INDEX
-^^^^^^^^^^^^
+.. warning:: The append and prepend operations are not idempotent by nature. So in particular, if one of these operation
+ timeout, then retrying the operation is not safe and it may (or may not) lead to appending/prepending the value
+ twice.
-*Syntax:*
+.. warning:: Setting and removing an element by position and removing occurences of particular values incur an internal
+ *read-before-write*. They will thus run more slowly and take more ressources than usual updates (with the exclusion
+ of conditional write that have their own cost).
-| bc(syntax)..
-| ::= CREATE ( CUSTOM )? INDEX ( IF NOT EXISTS )? ( )?
-| ON \u2018(\u2019 \u2018)\u2019
-| ( USING ( WITH OPTIONS = )? )?
+Lastly, as for :ref:`maps <maps>`, TTLs when used only apply to the newly inserted values.
-| ::=
-| \| keys( )
-| p.
-| *Sample:*
+.. _udts:
-| bc(sample).
-| CREATE INDEX userIndex ON NerdMovies (user);
-| CREATE INDEX ON Mutants (abilityId);
-| CREATE INDEX ON users (keys(favs));
-| CREATE CUSTOM INDEX ON users (email) USING \u2018path.to.the.IndexClass\u2019;
-| CREATE CUSTOM INDEX ON users (email) USING \u2018path.to.the.IndexClass\u2019
- WITH OPTIONS = {\u2019storage\u2019: \u2018/mnt/ssd/indexes/\u2019};
+User-Defined Types
+^^^^^^^^^^^^^^^^^^
-The ``CREATE INDEX`` statement is used to create a new (automatic)
-secondary index for a given (existing) column in a given table. A name
-for the index itself can be specified before the ``ON`` keyword, if
-desired. If data already exists for the column, it will be indexed
-asynchronously. After the index is created, new data for the column is
-indexed automatically at insertion time.
+CQL support the definition of user-defined types (UDT for short). Such a type can be created, modified and removed using
+the :token:`create_type_statement`, :token:`alter_type_statement` and :token:`drop_type_statement` described below. But
+once created, a UDT is simply referred to by its name:
-Attempting to create an already existing index will return an error
-unless the ``IF NOT EXISTS`` option is used. If it is used, the
-statement will be a no-op if the index already exists.
+.. productionlist::
+ user_defined_type: `udt_name`
+ udt_name: [ `keyspace_name` '.' ] `identifier`
-Indexes on Map Keys
-^^^^^^^^^^^^^^^^^^^
-When creating an index on a `map column <#map>`__, you may index either
-the keys or the values. If the column identifier is placed within the
-``keys()`` function, the index will be on the map keys, allowing you to
-use ``CONTAINS KEY`` in ``WHERE`` clauses. Otherwise, the index will be
-on the map values.
+Creating a UDT
+~~~~~~~~~~~~~~
-DROP INDEX
-^^^^^^^^^^
+Creating a new user-defined type is done using a ``CREATE TYPE`` statement defined by:
-*Syntax:*
+.. productionlist::
+ create_type_statement: CREATE TYPE [ IF NOT EXISTS ] `udt_name`
+ : '(' `field_definition` ( ',' `field_definition` )* ')'
+ field_definition: `identifier` `cql_type`
+
+A UDT has a name (used to declared columns of that type) and is a set of named and typed fields. Fields name can be any
+type, including collections or other UDT. For instance::
+
+ CREATE TYPE phone (
+ country_code int,
+ number text,
+ )
+
+ CREATE TYPE address (
+ street text,
+ city text,
+ zip int,
+ phones map<text, phone>
+ )
+
+ CREATE TABLE user (
+ name text PRIMARY KEY,
+ addresses map<text, frozen<address>>
+ )
-bc(syntax). ::= DROP INDEX ( IF EXISTS )? ( \u2018.\u2019 )?
+Note that:
-*Sample:*
+- Attempting to create an already existing type will result in an error unless the ``IF NOT EXISTS`` option is used. If
+ it is used, the statement will be a no-op if the type already exists.
+- A type is intrinsically bound to the keyspace in which it is created, and can only be used in that keyspace. At
+ creation, if the type name is prefixed by a keyspace name, it is created in that keyspace. Otherwise, it is created in
+ the current keyspace.
+- As of Cassandra |version|, UDT have to be frozen in most cases, hence the ``frozen<address>`` in the table definition
+ above. Please see the section on :ref:`frozen <frozen>` for more details.
-| bc(sample)..
-| DROP INDEX userIndex;
+UDT literals
+~~~~~~~~~~~~
-| DROP INDEX userkeyspace.address\_index;
-| p.
-| The ``DROP INDEX`` statement is used to drop an existing secondary
- index. The argument of the statement is the index name, which may
- optionally specify the keyspace of the index.
+Once a used-defined type has been created, value can be input using a UDT literal:
-If the index does not exists, the statement will return an error, unless
-``IF EXISTS`` is used in which case the operation is a no-op.
+.. productionlist::
+ udt_literal: '{' `identifier` ':' `term` ( ',' `identifier` ':' `term` )* '}'
+
+In other words, a UDT literal is like a :ref:`map <maps>` literal but its keys are the names of the fields of the type.
+For instance, one could insert into the table define in the previous section using::
+
+ INSERT INTO user (name, addresses)
+ VALUES ('z3 Pr3z1den7', {
+ 'home' : {
+ street: '1600 Pennsylvania Ave NW',
+ city: 'Washington',
+ zip: '20500',
+ phones: { 'cell' : { country_code: 1, number: '202 456-1111' },
+ 'landline' : { country_code: 1, number: '...' } }
+ }
+ 'work' : {
+ street: '1600 Pennsylvania Ave NW',
+ city: 'Washington',
+ zip: '20500',
+ phones: { 'fax' : { country_code: 1, number: '...' } }
+ }
+ })
+
+To be valid, a UDT literal should only include fields defined by the type it is a literal of, but it can omit some field
+(in which case those will be ``null``).
+
+Altering a UDT
+~~~~~~~~~~~~~~
+
+An existing user-defined type can be modified using an ``ALTER TYPE`` statement:
-CREATE MATERIALIZED VIEW
-^^^^^^^^^^^^^^^^^^^^^^^^
+.. productionlist::
+ alter_type_statement: ALTER TYPE `udt_name` `alter_type_modification`
+ alter_type_modification: ALTER `identifier` TYPE `cql_type`
+ : | ADD `field_definition`
+ : | RENAME `identifier` TO `identifier` ( `identifier` TO `identifier` )*
-*Syntax:*
+You can:
-| bc(syntax)..
-| ::= CREATE MATERIALIZED VIEW ( IF NOT EXISTS )? AS
-| SELECT ( \u2018(\u2019 ( \u2018,\u2019 ) \* \u2018)\u2019 \| \u2018\*\u2019 )
-| FROM
-| ( WHERE )?
-| PRIMARY KEY \u2018(\u2019 ( \u2018,\u2019 )\* \u2018)\u2019
-| ( WITH ( AND )\* )?
-| p.
-| *Sample:*
+- modify the type of particular field (``ALTER TYPE address ALTER zip TYPE bigint``). The restrictions for such change
+ are the same than when :ref:`altering the type of column <alter-table>`.
+- add a new field to the type (``ALTER TYPE address ADD country text``). That new field will be ``null`` for any values
+ of the type created before the addition.
+- rename the fields of the type (``ALTER TYPE address RENAME zip TO zipcode``).
-| bc(sample)..
-| CREATE MATERIALIZED VIEW monkeySpecies\_by\_population AS
-| SELECT \*
-| FROM monkeySpecies
-| WHERE population IS NOT NULL AND species IS NOT NULL
-| PRIMARY KEY (population, species)
-| WITH comment=\u2018Allow query by population instead of species\u2019;
-| p.
-| The ``CREATE MATERIALIZED VIEW`` statement creates a new materialized
- view. Each such view is a set of *rows* which corresponds to rows
- which are present in the underlying, or base, table specified in the
- ``SELECT`` statement. A materialized view cannot be directly updated,
- but updates to the base table will cause corresponding updates in the
- view.
+Dropping a UDT
+~~~~~~~~~~~~~~
-Attempting to create an already existing materialized view will return
-an error unless the ``IF NOT EXISTS`` option is used. If it is used, the
-statement will be a no-op if the materialized view already exists.
+You can drop an existing user-defined type using a ``DROP TYPE`` statement:
-``WHERE`` Clause
-^^^^^^^^^^^^^^^^
+.. productionlist::
+ drop_type_statement: DROP TYPE [ IF EXISTS ] `udt_name`
-The ``<where-clause>`` is similar to the `where clause of a ``SELECT``
-statement <#selectWhere>`__, with a few differences. First, the where
-clause must contain an expression that disallows ``NULL`` values in
-columns in the view\u2019s primary key. If no other restriction is desired,
-this can be accomplished with an ``IS NOT NULL`` expression. Second,
-only columns which are in the base table\u2019s primary key may be restricted
-with expressions other than ``IS NOT NULL``. (Note that this second
-restriction may be lifted in the future.)
+Dropping a type results in the immediate, irreversible removal of that type. However, attempting to drop a type that is
+still in use by another type, table
<TRUNCATED>