Skip to content

Commit b8466b1

Browse files
isrgomezisrgomez
committed
WL12607: Handling of Default Schema
This Worklog will ensure that the MySQL Connector Python X DEV API uses the given schema provided in connection data as the active default schema when a session is established, that way any SQL statement executed using that session, should be executed against the provided default schema when no other schema is specified.
1 parent 9bd3b4d commit b8466b1

File tree

6 files changed

+186
-17
lines changed

6 files changed

+186
-17
lines changed

‎docs/mysqlx/tutorials/getting_started.rst

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -107,6 +107,34 @@ This object chain is equivalent to the following, with the difference that the i
107107
session = mysqlx.get_session()
108108
schema = session.get_schema('test')
109109
110+
The connection settings accepts a default schema option ``schema``, which should be a valid name for a preexisting schema in the server.
111+
112+
.. code-block:: python
113+
114+
session = mysqlx.get_session('mysqlx://root:@localhost:33060/my_schema')
115+
# or
116+
session = mysqlx.get_session({
117+
'host': 'localhost',
118+
'port': 33060,
119+
'user': 'root',
120+
'password': '',
121+
'schema': 'my_schema'
122+
})
123+
124+
.. Note:: The default schema provided must exists in the server otherwise it will raise an error at connection time.
125+
126+
This way the session will use the given schema as the default schema, which can be retrieved by :func:`mysqlx.Session.get_default_schema()` and also allows to run SQL statements without specifying the schema name:
127+
128+
.. code-block:: python
129+
130+
session = mysqlx.get_session('mysqlx://root:@localhost:33060/my_schema')
131+
my_schema = session.get_default_schema()
132+
assert my_test_schema.get_name() == 'my_schema'
133+
session.sql('CREATE TABLE Pets(name VARCHAR(20))').execute()
134+
# instead of 'CREATE TABLE my_schema.Pets(name VARCHAR(20))'
135+
res = session.sql('SELECT * FROM Pets').execute().fetch_all()
136+
# instead of 'SELECT * FROM my_schema.Pets'
137+
110138
In the following example, the :func:`mysqlx.get_session()` function is used to open a session. We then get the reference to ``test`` schema and create a collection using the :func:`mysqlx.Schema.create_collection()` method of the :class:`mysqlx.Schema` object.
111139

112140
.. code-block:: python

‎lib/mysqlx/connection.py

Lines changed: 33 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -53,16 +53,18 @@
5353
from .compat import PY3, STRING_TYPES, UNICODE_TYPES, queue
5454
from .crud import Schema
5555
from .constants import SSLMode, Auth
56-
from .helpers import get_item_or_attr
56+
from .helpers import escape, get_item_or_attr
5757
from .protocol import Protocol, MessageReaderWriter
5858
from .result import Result, RowResult, DocResult
5959
from .statement import SqlStatement, AddStatement, quote_identifier
6060
from .protobuf import Protobuf
6161

6262

6363
_CONNECT_TIMEOUT = 10000 # Default connect timeout in milliseconds
64-
_DROP_DATABASE_QUERY = "DROP DATABASE IF EXISTS `{0}`"
65-
_CREATE_DATABASE_QUERY = "CREATE DATABASE IF NOT EXISTS `{0}`"
64+
_DROP_DATABASE_QUERY = "DROP DATABASE IF EXISTS {0}"
65+
_CREATE_DATABASE_QUERY = "CREATE DATABASE IF NOT EXISTS {0}"
66+
_SELECT_SCHEMA_NAME_QUERY = ("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA."
67+
"SCHEMATA WHERE SCHEMA_NAME = '{}'")
6668

6769
_CNX_POOL_MAXSIZE = 99
6870
_CNX_POOL_MAX_NAME_SIZE = 120
@@ -1217,6 +1219,16 @@ def __init__(self, settings):
12171219
else:
12181220
self._connection = Connection(self._settings)
12191221
self._connection.connect()
1222+
# Set default schema
1223+
schema = self._settings.get("schema")
1224+
if schema:
1225+
try:
1226+
self.sql("USE {}".format(quote_identifier(schema))).execute()
1227+
except OperationalError as err:
1228+
# Access denied for user will raise err.errno = 1044
1229+
errmsg = err.msg if err.errno == 1044 \
1230+
else "Default schema '{}' does not exists".format(schema)
1231+
raise InterfaceError(errmsg, err.errno)
12201232

12211233
@property
12221234
def use_pure(self):
@@ -1281,13 +1293,25 @@ def get_default_schema(self):
12811293
Returns:
12821294
mysqlx.Schema: The Schema object with the given name at connect
12831295
time.
1296+
None: In case the default schema was not provided with the
1297+
initialization data.
12841298
12851299
Raises:
1286-
:class:`mysqlx.ProgrammingError`: If default schema not provided.
1300+
:class:`mysqlx.ProgrammingError`: If the provided default schema
1301+
does not exists.
12871302
"""
1288-
if self._connection.settings.get("schema"):
1289-
return Schema(self, self._connection.settings["schema"])
1290-
raise ProgrammingError("Default schema not provided")
1303+
schema = self._connection.settings.get("schema")
1304+
if schema:
1305+
res = self.sql(
1306+
_SELECT_SCHEMA_NAME_QUERY.format(escape(schema))
1307+
).execute().fetch_all()
1308+
try:
1309+
if res[0][0] == schema:
1310+
return Schema(self, schema)
1311+
except IndexError:
1312+
raise ProgrammingError(
1313+
"Default schema '{}' does not exists".format(schema))
1314+
return None
12911315

12921316
def drop_schema(self, name):
12931317
"""Drops the schema with the specified name.
@@ -1296,7 +1320,7 @@ def drop_schema(self, name):
12961320
name (string): The name of the Schema object to be retrieved.
12971321
"""
12981322
self._connection.execute_nonquery(
1299-
"sql", _DROP_DATABASE_QUERY.format(name), True)
1323+
"sql", _DROP_DATABASE_QUERY.format(quote_identifier(name)), True)
13001324

13011325
def create_schema(self, name):
13021326
"""Creates a schema on the database and returns the corresponding
@@ -1306,7 +1330,7 @@ def create_schema(self, name):
13061330
name (string): A string value indicating the schema name.
13071331
"""
13081332
self._connection.execute_nonquery(
1309-
"sql", _CREATE_DATABASE_QUERY.format(name), True)
1333+
"sql", _CREATE_DATABASE_QUERY.format(quote_identifier(name)), True)
13101334
return Schema(self, name)
13111335

13121336
def start_transaction(self):

‎lib/mysqlx/protocol.py

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -249,7 +249,7 @@ def _read_message(self, result):
249249
while True:
250250
msg = self._reader.read_message()
251251
if msg.type == "Mysqlx.Error":
252-
raise OperationalError(msg["msg"])
252+
raise OperationalError(msg["msg"], msg["code"])
253253
elif msg.type == "Mysqlx.Notice.Frame":
254254
try:
255255
self._process_frame(msg, result)

‎lib/mysqlx/statement.py

Lines changed: 1 addition & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -75,8 +75,7 @@ def is_quoted_identifier(identifier, sql_mode=""):
7575

7676
def quote_identifier(identifier, sql_mode=""):
7777
"""Quote the given identifier with backticks, converting backticks (`) in
78-
the identifier name with the correct escape sequence (``) unless the
79-
identifier is quoted (") as in sql_mode set to ANSI_QUOTES.
78+
the identifier name with the correct escape sequence (``).
8079
8180
Args:
8281
identifier (string): Identifier to quote.
@@ -87,8 +86,6 @@ def quote_identifier(identifier, sql_mode=""):
8786
"""
8887
if len(identifier) == 0:
8988
return "``"
90-
elif is_quoted_identifier(identifier, sql_mode):
91-
return identifier
9289
if "ANSI_QUOTES" in sql_mode:
9390
return '"{0}"'.format(identifier.replace('"', '""'))
9491
return "`{0}`".format(identifier.replace("`", "``"))

‎tests/test_mysqlx_connection.py

Lines changed: 63 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,7 @@
4545
from time import sleep
4646

4747
from mysqlx.connection import SocketStream
48-
from mysqlx.errors import InterfaceError, ProgrammingError
48+
from mysqlx.errors import InterfaceError, OperationalError, ProgrammingError
4949
from mysqlx.protocol import Message, MessageReaderWriter, Protocol
5050
from mysqlx.protobuf import HAVE_MYSQLXPB_CEXT, mysqlxpb_enum, Protobuf
5151

@@ -589,14 +589,74 @@ def test_get_default_schema(self):
589589
schema = self.session.get_default_schema()
590590
self.assertTrue(schema, mysqlx.Schema)
591591
self.assertEqual(schema.get_name(), self.connect_kwargs["schema"])
592+
self.assertTrue(schema.exists_in_database())
592593

593-
# Test without default schema configured at connect time
594+
# Test None value is returned if no schema name is specified
594595
settings = self.connect_kwargs.copy()
595-
settings["schema"] = None
596+
settings.pop("schema")
597+
session = mysqlx.get_session(settings)
598+
schema = session.get_default_schema()
599+
self.assertIsNone(schema,
600+
"None value was expected but got '{}'".format(schema))
601+
session.close()
602+
603+
# Test SQL statements not fully qualified, which must not raise error:
604+
# mysqlx.errors.OperationalError: No database selected
605+
self.session.sql('CREATE DATABASE my_test_schema').execute()
606+
self.session.sql('CREATE TABLE my_test_schema.pets(name VARCHAR(20))'
607+
).execute()
608+
settings = self.connect_kwargs.copy()
609+
settings["schema"] = "my_test_schema"
596610
session = mysqlx.get_session(settings)
611+
schema = session.get_default_schema()
612+
self.assertTrue(schema, mysqlx.Schema)
613+
self.assertEqual(schema.get_name(),
614+
"my_test_schema")
615+
result = session.sql('SHOW TABLES').execute().fetch_all()
616+
self.assertEqual("pets", result[0][0])
617+
self.session.sql('DROP DATABASE my_test_schema').execute()
618+
self.assertFalse(schema.exists_in_database())
597619
self.assertRaises(mysqlx.ProgrammingError, session.get_default_schema)
598620
session.close()
599621

622+
# Test without default schema configured at connect time (passing None)
623+
settings = self.connect_kwargs.copy()
624+
settings["schema"] = None
625+
build_uri(**settings)
626+
session = mysqlx.get_session(settings)
627+
schema = session.get_default_schema()
628+
self.assertIsNone(schema,
629+
"None value was expected but got '{}'".format(schema))
630+
session.close()
631+
632+
# Test not existing default schema at get_session raise error
633+
settings = self.connect_kwargs.copy()
634+
settings["schema"] = "nonexistent"
635+
self.assertRaises(InterfaceError, mysqlx.get_session, settings)
636+
637+
# Test BUG#28942938: 'ACCESS DENIED' error for unauthorized user tries
638+
# to use the default schema if not exists at get_session
639+
self.session.sql("DROP USER IF EXISTS 'def_schema'@'%'").execute()
640+
self.session.sql("CREATE USER 'def_schema'@'%' IDENTIFIED WITH "
641+
"mysql_native_password BY 'test'").execute()
642+
settings = self.connect_kwargs.copy()
643+
settings['user'] = 'def_schema'
644+
settings['password'] = 'test'
645+
settings["schema"] = "nonexistent"
646+
# a) Test with no Granted privileges
647+
with self.assertRaises(InterfaceError) as context:
648+
_ = mysqlx.get_session(settings)
649+
# Access denied for this user
650+
self.assertEqual(1044, context.exception.errno)
651+
652+
# Grant privilege to one unrelated schema
653+
self.session.sql("GRANT ALL PRIVILEGES ON nonexistent.* TO "
654+
"'def_schema'@'%'").execute()
655+
with self.assertRaises(InterfaceError) as context:
656+
_ = mysqlx.get_session(settings)
657+
# Schema does not exist
658+
self.assertNotEqual(1044, context.exception.errno)
659+
600660
def test_drop_schema(self):
601661
test_schema = 'mysql_session_test_drop_schema'
602662
schema = self.session.create_schema(test_schema)

‎tests/test_mysqlx_pooling.py

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -490,3 +490,63 @@ def thread2(client):
490490
self.assertTrue(len(open_connections) >= 1)
491491

492492
client.close()
493+
494+
495+
@unittest.skipIf(tests.MYSQL_VERSION < (5, 7, 12), "XPlugin not compatible")
496+
class MySQLxPoolingSessionTests(tests.MySQLxTests):
497+
def setUp(self):
498+
self.connect_kwargs = tests.get_mysqlx_config()
499+
self.session = mysqlx.get_session(self.connect_kwargs)
500+
self.session.sql('DROP DATABASE IF EXISTS my_test_schema').execute()
501+
502+
def test_get_default_schema(self):
503+
pooling_dict = {"max_size": 1, "max_idle_time": 3000,
504+
"queue_timeout": 10}
505+
# Test None value is returned if no schema name is specified
506+
settings = self.connect_kwargs.copy()
507+
settings.pop("schema")
508+
client = mysqlx.get_client(settings, pooling_dict)
509+
session = client.get_session()
510+
schema = session.get_default_schema()
511+
self.assertIsNone(schema,
512+
"None value was expected but got '{}'".format(schema))
513+
session.close()
514+
515+
# Test SQL statements not fully qualified, which must not raise error:
516+
# mysqlx.errors.OperationalError: No database selected
517+
self.session.sql('CREATE DATABASE my_test_schema').execute()
518+
self.session.sql('CREATE TABLE my_test_schema.pets(name VARCHAR(20))'
519+
).execute()
520+
settings = self.connect_kwargs.copy()
521+
settings["schema"] = "my_test_schema"
522+
523+
client = mysqlx.get_client(settings, pooling_dict)
524+
session = client.get_session()
525+
schema = session.get_default_schema()
526+
self.assertTrue(schema, mysqlx.Schema)
527+
self.assertEqual(schema.get_name(),
528+
"my_test_schema")
529+
result = session.sql('SHOW TABLES').execute().fetch_all()
530+
self.assertEqual("pets", result[0][0])
531+
self.session.sql('DROP DATABASE my_test_schema').execute()
532+
self.assertFalse(schema.exists_in_database())
533+
self.assertRaises(mysqlx.ProgrammingError, session.get_default_schema)
534+
session.close()
535+
client.close()
536+
537+
# Test without default schema configured at connect time (passing None)
538+
settings = self.connect_kwargs.copy()
539+
settings["schema"] = None
540+
client = mysqlx.get_client(settings, pooling_dict)
541+
session = client.get_session()
542+
schema = session.get_default_schema()
543+
self.assertIsNone(schema,
544+
"None value was expected but got '{}'".format(schema))
545+
session.close()
546+
client.close()
547+
548+
# Test not existing default schema at get_session raise error
549+
settings = self.connect_kwargs.copy()
550+
settings["schema"] = "nonexistent"
551+
client = mysqlx.get_client(settings, pooling_dict)
552+
self.assertRaises(InterfaceError, client.get_session)

0 commit comments

Comments
 (0)