Skip to content

Commit dc5862d

Browse files
isrgomezisrgomez
committed
WL11278: Collection.create_index
This Worklog provides a easier way to create the index on a JSON document in a more JSON style way that replaces the chained method invocation with a single method call. The deliverable product is a change of functionality for the current method 'create_index()', which in addition of receive the index name it will receive a second parameter in form of a JSON document, which will describe the index to be created. Note: This new functionality will replace the current one that requires the invocation of the 'field()' method, so for the 'field()' method will be removed as part of this worklog. Placeholder for WL#9876 for the Collection.createIndex method for the MySQL Connector Python X DevAPI. v1b
1 parent ce31702 commit dc5862d

File tree

8 files changed

+539
-41
lines changed

8 files changed

+539
-41
lines changed

‎docs/mysqlx/tutorials.rst

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,3 +6,4 @@ Tutorials
66

77
tutorials/getting_started
88
tutorials/transactions
9+
tutorials/creating_indexes

‎docs/mysqlx/tutorials/creating_indexes.rst

Lines changed: 134 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,134 @@
1+
Creating Indexes
2+
================
3+
4+
Collection indexes can be created using one or more fields. The method used to
5+
create these indexes is :func:`mysqlx.Collection.create_index()` and the
6+
following sections describes the required arguments for the function and some
7+
examples of use.
8+
9+
Arguments for :func:`mysqlx.Collection.create_index()`
10+
------------------------------------------------------
11+
12+
To use the :func:`mysqlx.Collection.create_index()` we need to specify the name
13+
of the index to be created and the members to be part of the index, in addition
14+
for each member we need to specify the type of data that holds the field in the
15+
document and if it is required or not. Fields marked as required must appear on
16+
each document in the collection.
17+
18+
.. code-block:: python
19+
20+
{"fields": [{"field": member_path, # required str
21+
"type": member_type, # required str, must be a valid type
22+
"required": member_required, # optional, True or (default) False
23+
"collation": collation, # optional str only for TEXT field type
24+
"options": options, # optional (int) only for GEOJSON field type
25+
"srid": srid}, # optional (int) only for GEOJSON field type
26+
# {... more members,
27+
# repeated as many times
28+
# as needed}
29+
],
30+
"type": type} # optional, SPATIAL or (default) INDEX
31+
32+
The valid types for the ``type`` field are:
33+
34+
* INT [UNSIGNED]
35+
* TINYINT [UNSIGNED]
36+
* SMALLINT [UNSIGNED]
37+
* MEDIUMINT [UNSIGNED]
38+
* INTEGER [UNSIGNED]
39+
* BIGINT [UNSIGNED]
40+
* REAL [UNSIGNED]
41+
* FLOAT [UNSIGNED]
42+
* DOUBLE [UNSIGNED]
43+
* DECIMAL [UNSIGNED]
44+
* NUMERIC [UNSIGNED]
45+
* DATE
46+
* TIME
47+
* TIMESTAMP
48+
* DATETIME
49+
* TEXT[(length)]
50+
* GEOJSON (extra options: options, srid)
51+
52+
Note: The use of ``type`` GEOJSON, requires the index ``type`` to be set to
53+
``SPATIAL``.
54+
55+
Using :func:`mysqlx.Collection.create_index()`
56+
----------------------------------------------
57+
58+
First we need to get a session and a schema.
59+
60+
.. code-block:: python
61+
62+
import mysqlx
63+
64+
# Connect to server on localhost
65+
session = mysqlx.get_session({
66+
'host': 'localhost',
67+
'port': 33060,
68+
'user': 'mike',
69+
'password': 's3cr3t!'
70+
})
71+
72+
schema = session.get_schema('test')
73+
74+
Next step is create a sample collection.
75+
76+
.. code-block:: python
77+
78+
# Create 'collection_GOT' in schema
79+
schema.create_collection('collection_GOT')
80+
81+
# Get 'collection_GOT' from schema
82+
collection = schema.get_collection('collection_GOT')
83+
84+
The following example shows how to create a simple index with name
85+
``index_age`` that will use a field ``age`` from the document which will
86+
hold integer values.
87+
88+
.. code-block:: python
89+
90+
collection.create_index("index_age", {"fields": [{"field": "age",
91+
"type": "INT"}],
92+
"type":"INDEX"})
93+
94+
The following example shows how to create a multi field index with name
95+
``index_name`` that will use the fields ``family_name`` and ``name``
96+
from the document that will hold small texts. This time the ``required``
97+
member has been set to ``True``, which means these fields are required for all
98+
the documents in this collection.
99+
100+
.. code-block:: python
101+
102+
collection.create_index("index_name", {"fields": [{"field": "family_name",
103+
"type": "TEXT(12)",
104+
"required": True}],
105+
"fields": [{"field": "name",
106+
"type": "TEXT(12)",
107+
"required": True}],
108+
"type":"INDEX"})
109+
110+
111+
The following example shows how to create a multi field index with name
112+
``geojson_name``, which will use fields with GEOJSON data, so for this will
113+
require the index ``type`` to be set to ``SPATIAL``, that will use the fields
114+
``$.geoField``, ``$.intField``, ``$.floatField`` and ``$.dateField``.
115+
Each field hold the data that compounds the name of the file. Note that by
116+
setting ``SPATIAL`` to the index ``type`` we will require to set for each of
117+
these members ``required`` to ``True``, which means these fields are required
118+
for all the documents in this collection.
119+
120+
.. code-block:: python
121+
122+
collection.create_index("index_age",
123+
{"fields": [{"field": "$.geoField",
124+
"type": "GEOJSON",
125+
"required": False, "options": 2,
126+
"srid": 4326},
127+
{"field": "$.intField", "type": "INT",
128+
"required": True},
129+
{"field": "$.floatField",
130+
"type": "FLOAT",
131+
"required": True},
132+
{"field": "$.dateField",
133+
"type": "DATE", "required": True}],
134+
"type" : "SPATIAL"})

‎docs/mysqlx/tutorials/getting_started.rst

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -130,8 +130,8 @@ Parameter binding is also available as a chained method to each of the CRUD oper
130130
my_coll.remove('name = :data').bind('data', 'Sakila').execute()
131131
132132
133-
Using Collection patch (:func:`Collection.patch()`).
134-
---------------------------------------------------
133+
Using Collection patch (:func:`mysqlx.ModifyStatement.patch()`)
134+
---------------------------------------------------------------
135135

136136
First we need to get a session and a schema.
137137

‎lib/mysqlx/crud.py

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -341,14 +341,28 @@ def count(self):
341341
sql = _COUNT_QUERY.format(self._schema.name, self._name)
342342
return self._connection.execute_sql_scalar(sql)
343343

344-
def create_index(self, index_name, is_unique):
344+
def create_index(self, index_name, fields_desc):
345345
"""Creates a collection index.
346346
347347
Args:
348348
index_name (str): Index name.
349-
is_unique (bool): `True` if the index is unique.
350-
"""
351-
return CreateCollectionIndexStatement(self, index_name, is_unique)
349+
fields_desc (dict): A dictionary containing the fields members that
350+
constraints the index to be created. It must
351+
have the form as shown in the following::
352+
353+
{"fields": [{"field": member_path,
354+
"type": member_type,
355+
"required": member_required,
356+
"collation": collation,
357+
"options": options,
358+
"srid": srid},
359+
# {... more members,
360+
# repeated as many times
361+
# as needed}
362+
],
363+
"type": type}
364+
"""
365+
return CreateCollectionIndexStatement(self, index_name, fields_desc)
352366

353367
def drop_index(self, index_name):
354368
"""Drops a collection index.

‎lib/mysqlx/expr.py

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -357,6 +357,11 @@ def build_int_scalar(value):
357357
msg["v_signed_int"] = value
358358
return msg
359359

360+
def build_unsigned_int_scalar(value):
361+
msg = Message("Mysqlx.Datatypes.Scalar")
362+
msg["type"] = mysqlxpb_enum("Mysqlx.Datatypes.Scalar.Type.V_UINT")
363+
msg["v_unsigned_int"] = value
364+
return msg
360365

361366
def build_string_scalar(value):
362367
if isinstance(value, STRING_TYPES):
@@ -419,6 +424,8 @@ def clean_expression(self):
419424
Removes the keywords "SELECT" and "WHERE" that does not form part of
420425
the expression itself.
421426
"""
427+
if not isinstance(self.string, STRING_TYPES):
428+
self.string = repr(self.string)
422429
self.string = self.string.strip(" ")
423430
if len(self.string) > 1 and self.string[-1] == ';':
424431
self.string = self.string[:-1]

‎lib/mysqlx/protocol.py

Lines changed: 55 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@
2828
from .compat import STRING_TYPES, INT_TYPES
2929
from .errors import InterfaceError, OperationalError, ProgrammingError
3030
from .expr import (ExprParser, build_expr, build_scalar, build_bool_scalar,
31-
build_int_scalar)
31+
build_int_scalar, build_unsigned_int_scalar)
3232
from .helpers import encode_to_bytes, get_item_or_attr
3333
from .result import ColumnMetaData
3434
from .protobuf import SERVER_MESSAGES, Message, mysqlxpb_enum
@@ -156,8 +156,38 @@ def _create_any(self, arg):
156156
return Message("Mysqlx.Datatypes.Any", type=1,
157157
scalar=build_bool_scalar(arg))
158158
elif isinstance(arg, INT_TYPES):
159+
if arg < 0:
160+
return Message("Mysqlx.Datatypes.Any", type=1,
161+
scalar=build_int_scalar(arg))
159162
return Message("Mysqlx.Datatypes.Any", type=1,
160-
scalar=build_int_scalar(arg))
163+
scalar=build_unsigned_int_scalar(arg))
164+
165+
elif isinstance(arg, tuple) and len(arg) == 2:
166+
arg_key, arg_value = arg
167+
obj_fld = Message("Mysqlx.Datatypes.Object.ObjectField",
168+
key=arg_key, value=self._create_any(arg_value))
169+
obj = Message("Mysqlx.Datatypes.Object",
170+
fld=[obj_fld.get_message()])
171+
return Message("Mysqlx.Datatypes.Any", type=2, obj=obj)
172+
173+
elif isinstance(arg, dict) or (isinstance(arg, (list, tuple)) and
174+
isinstance(arg[0], dict)):
175+
array_values = []
176+
for items in arg:
177+
obj_flds = []
178+
for key, value in items.items():
179+
# Array can only handle Any types, Mysqlx.Datatypes.Any.obj
180+
obj_fld = Message("Mysqlx.Datatypes.Object.ObjectField",
181+
key=key, value=self._create_any(value))
182+
obj_flds.append(obj_fld.get_message())
183+
msg_obj = Message("Mysqlx.Datatypes.Object", fld=obj_flds)
184+
msg_any = Message("Mysqlx.Datatypes.Any", type=2, obj=msg_obj)
185+
array_values.append(msg_any.get_message())
186+
187+
msg = Message("Mysqlx.Datatypes.Array")
188+
msg["value"] = array_values
189+
return Message("Mysqlx.Datatypes.Any", type=3, array=msg)
190+
161191
return None
162192

163193
def _process_frame(self, msg, result):
@@ -407,9 +437,25 @@ def send_execute_statement(self, namespace, stmt, args):
407437
"""
408438
msg = Message("Mysqlx.Sql.StmtExecute", namespace=namespace, stmt=stmt,
409439
compact_metadata=False)
410-
for arg in args:
411-
value = self._create_any(arg)
412-
msg["args"].extend([value.get_message()])
440+
441+
if namespace == "mysqlx":
442+
# mysqlx namespace behavior: one object with a list of arguments
443+
items = args[0].items() if isinstance(args, (list, tuple)) else \
444+
args.items()
445+
obj_flds = []
446+
for key, value in items:
447+
obj_fld = Message("Mysqlx.Datatypes.Object.ObjectField",
448+
key=key, value=self._create_any(value))
449+
obj_flds.append(obj_fld.get_message())
450+
msg_obj = Message("Mysqlx.Datatypes.Object", fld=obj_flds)
451+
msg_any = Message("Mysqlx.Datatypes.Any", type=2, obj=msg_obj)
452+
msg["args"] = [msg_any.get_message()]
453+
else:
454+
# xplugin namespace behavior: list of arguments
455+
for arg in args:
456+
value = self._create_any(arg)
457+
msg["args"].extend([value.get_message()])
458+
413459
self._writer.write_message(
414460
mysqlxpb_enum("Mysqlx.ClientMessages.Type.SQL_STMT_EXECUTE"), msg)
415461

@@ -497,8 +543,10 @@ def get_column_metadata(self, result):
497543
col = ColumnMetaData(msg["type"], msg["catalog"], msg["schema"],
498544
msg["table"], msg["original_table"],
499545
msg["name"], msg["original_name"],
500-
msg["length"], msg["collation"],
501-
msg["fractional_digits"], msg["flags"],
546+
msg.get("length", 21),
547+
msg.get("collation", 0),
548+
msg.get("fractional_digits", 0),
549+
msg.get("flags", 16),
502550
msg.get("content_type"))
503551
columns.append(col)
504552
return columns

0 commit comments

Comments
 (0)