def validate_empty_column_name_test(self):
cluster = self.cluster
cluster.populate(1).start()
node1 = cluster.nodelist()[0]
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'counter_tests', 1)
cursor.execute("""
CREATE TABLE compact_counter_table (
pk int,
ck text,
value counter,
PRIMARY KEY (pk, ck))
WITH COMPACT STORAGE
""")
assert_invalid(
cursor,
"UPDATE compact_counter_table SET value = value + 1 WHERE pk = 0 AND ck = ''"
)
assert_invalid(
cursor,
"UPDATE compact_counter_table SET value = value - 1 WHERE pk = 0 AND ck = ''"
)
cursor.execute(
"UPDATE compact_counter_table SET value = value + 5 WHERE pk = 0 AND ck = 'ck'"
)
cursor.execute(
"UPDATE compact_counter_table SET value = value - 2 WHERE pk = 0 AND ck = 'ck'"
)
assert_one(cursor, "SELECT pk, ck, value FROM compact_counter_table",
[0, 'ck', 3])
def drop_column_compact_test(self):
cursor = self.prepare()
cursor.execute("USE ks")
cursor.execute("CREATE TABLE cf (key int PRIMARY KEY, c1 int, c2 int) WITH COMPACT STORAGE")
assert_invalid(cursor, "ALTER TABLE cf DROP c1", "Cannot drop columns from a")
def altering_nonexistent_user_throws_exception_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
assert_invalid(
cursor,
"ALTER USER nonexistent WITH PASSWORD 'doesn''tmatter'",
"User nonexistent doesn't exist",
)
def test_type_as_part_of_pkey(self):
"""Tests user types as part of a composite pkey"""
# make sure we can define a table with a user type as part of the pkey
# and do a basic insert/query of data in that table.
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'user_type_pkeys', 2)
stmt = """
CREATE TYPE t_person_name (
first text,
middle text,
last text
)
"""
cursor.execute(stmt)
stmt = """
CREATE TABLE person_likes (
id uuid,
name frozen<t_person_name>,
like text,
PRIMARY KEY ((id, name))
)
"""
cursor.execute(stmt)
# Make sure the scheam propagate
time.sleep(2)
_id = uuid.uuid4()
stmt = """
INSERT INTO person_likes (id, name, like)
VALUES ({id}, {{first:'Nero', middle:'Claudius Caesar Augustus', last:'Germanicus'}}, 'arson');
""".format(id=_id)
cursor.execute(stmt)
# attempt to query without the user type portion of the pkey and confirm there is an error
stmt = """
SELECT id, name.first from person_likes where id={id};
""".format(id=_id)
if self.cluster.version() >= '3.0':
assert_invalid(cursor, stmt, 'Partition key parts: name must be restricted as other parts are')
else:
assert_invalid(cursor, stmt, 'Partition key part name must be restricted since preceding part is')
stmt = """
SELECT id, name.first, like from person_likes where id={id} and name = {{first:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'}};
""".format(id=_id)
rows = cursor.execute(stmt)
row_uuid, first_name, like = rows[0]
self.assertEqual(first_name, u'Nero')
self.assertEqual(like, u'arson')
def only_one_timestamp_is_valid_test(self):
""" Test that TIMESTAMP must not be used in the statements within the batch. """
session = self.prepare()
assert_invalid(session, """
BEGIN BATCH USING TIMESTAMP 1111111111111111
INSERT INTO users (id, firstname, lastname) VALUES (0, 'Jack', 'Sparrow') USING TIMESTAMP 2
INSERT INTO users (id, firstname, lastname) VALUES (1, 'Will', 'Turner')
APPLY BATCH
""", matching="Timestamp must be set either on BATCH or individual statements")
def drop_column_compact_test(self):
cursor = self.prepare()
cursor.execute("USE ks")
cursor.execute(
"CREATE TABLE cf (key int PRIMARY KEY, c1 int, c2 int) WITH COMPACT STORAGE"
)
assert_invalid(cursor, "ALTER TABLE cf DROP c1",
"Cannot drop columns from a")
def test_nested_type_dropping(self):
"""
Confirm a user type can't be dropped when being used by another user type.
"""
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'nested_user_type_dropping', 2)
stmt = """
USE nested_user_type_dropping
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE simple_type (
user_number int,
user_text text
)
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE another_type (
somefield frozen<simple_type>
)
"""
cursor.execute(stmt)
stmt = """
DROP TYPE simple_type;
"""
assert_invalid(
cursor, stmt,
'Cannot drop user type nested_user_type_dropping.simple_type as it is still used by user type another_type'
)
# drop the type that's impeding the drop, and then try again
stmt = """
DROP TYPE another_type;
"""
cursor.execute(stmt)
stmt = """
DROP TYPE simple_type;
"""
cursor.execute(stmt)
# now let's have a look at the system schema and make sure no user types are defined
stmt = """
SELECT type_name from system.schema_usertypes;
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
def cant_create_existing_user_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
cursor.execute(
"CREATE USER '*****@*****.**' WITH PASSWORD '12345' NOSUPERUSER"
)
assert_invalid(
cursor,
"CREATE USER '*****@*****.**' WITH PASSWORD '12345' NOSUPERUSER",
'User [email protected] already exists')
def test_nested_type_dropping(self):
"""
Confirm a user type can't be dropped when being used by another user type.
"""
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'nested_user_type_dropping', 2)
stmt = """
USE nested_user_type_dropping
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE simple_type (
user_number int,
user_text text
)
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE another_type (
somefield frozen<simple_type>
)
"""
cursor.execute(stmt)
stmt = """
DROP TYPE simple_type;
"""
assert_invalid(cursor, stmt, 'Cannot drop user type nested_user_type_dropping.simple_type as it is still used by user type another_type')
# drop the type that's impeding the drop, and then try again
stmt = """
DROP TYPE another_type;
"""
cursor.execute(stmt)
stmt = """
DROP TYPE simple_type;
"""
cursor.execute(stmt)
# now let's have a look at the system schema and make sure no user types are defined
stmt = """
SELECT type_name from system.schema_usertypes;
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
def test_type_enforcement(self):
"""
Confirm error when incorrect data type used for user type
"""
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.cql_connection(node1)
self.create_ks(cursor, 'user_type_enforcement', 2)
stmt = """
USE user_type_enforcement
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE simple_type (
user_number int
)
"""
cursor.execute(stmt)
stmt = """
CREATE TABLE simple_table (
id uuid PRIMARY KEY,
number frozen<simple_type>
)
"""
cursor.execute(stmt)
# Make sure the scheam propagate
time.sleep(2)
# here we will attempt an insert statement which should fail
# because the user type is an int, but the insert statement is
# providing text
_id = uuid.uuid4()
stmt = """
INSERT INTO simple_table (id, number)
VALUES ({id}, {{user_number: 'uh oh....this is not a number'}});
""".format(id=_id)
assert_invalid( cursor, stmt, 'field user_number is not of type int')
# let's check the rowcount and make sure the data
# didn't get inserted when the exception asserted above was thrown
stmt = """
SELECT * FROM simple_table;
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
def test_type_enforcement(self):
"""
Confirm error when incorrect data type used for user type
"""
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.cql_connection(node1)
self.create_ks(cursor, 'user_type_enforcement', 2)
stmt = """
USE user_type_enforcement
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE simple_type (
user_number int
)
"""
cursor.execute(stmt)
stmt = """
CREATE TABLE simple_table (
id uuid PRIMARY KEY,
number frozen<simple_type>
)
"""
cursor.execute(stmt)
# Make sure the scheam propagate
time.sleep(2)
# here we will attempt an insert statement which should fail
# because the user type is an int, but the insert statement is
# providing text
_id = uuid.uuid4()
stmt = """
INSERT INTO simple_table (id, number)
VALUES ({id}, {{user_number: 'uh oh....this is not a number'}});
""".format(id=_id)
assert_invalid(cursor, stmt, 'field user_number is not of type int')
# let's check the rowcount and make sure the data
# didn't get inserted when the exception asserted above was thrown
stmt = """
SELECT * FROM simple_table;
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
def unlogged_batch_rejects_counter_mutations_test(self):
""" Test that unlogged batch rejects counter mutations """
session = self.prepare()
if self.cluster.version() < '2.1':
err = "Counter mutations are only allowed in COUNTER batches"
else:
err = "Counter and non-counter mutations cannot exist in the same batch"
assert_invalid(session, """
BEGIN UNLOGGED BATCH
INSERT INTO users (id, firstname, lastname) VALUES (0, 'Jack', 'Sparrow')
INSERT INTO users (id, firstname, lastname) VALUES (2, 'Elizabeth', 'Swann')
UPDATE clicks SET total = total + 1 WHERE userid = 1 AND url = 'http://foo.com'
APPLY BATCH
""", matching=err)
def logged_batch_rejects_counter_mutations_test(self):
""" Test that logged batch rejects counter mutations """
session = self.prepare()
if self.cluster.version() < '2.1':
err = "Counter mutations are only allowed in COUNTER batches"
else:
err = "Cannot include a counter statement in a logged batch"
assert_invalid(session, """
BEGIN BATCH
INSERT INTO users (id, firstname, lastname) VALUES (0, 'Jack', 'Sparrow')
INSERT INTO users (id, firstname, lastname) VALUES (1, 'Will', 'Turner')
UPDATE clicks SET total = total + 1 WHERE userid = 1 and url = 'http://foo.com'
APPLY BATCH
""", matching=err)
def counter_batch_rejects_regular_mutations_test(self):
""" Test that counter batch rejects non-counter mutations """
session = self.prepare()
if self.cluster.version() < '2.1':
err = "Only counter mutations are allowed in COUNTER batches"
else:
err = "Cannot include non-counter statement in a counter batch"
assert_invalid(session, """
BEGIN COUNTER BATCH
UPDATE clicks SET total = total + 1 WHERE userid = 1 and url = 'http://foo.com'
UPDATE clicks SET total = total + 1 WHERE userid = 1 and url = 'http://bar.com'
UPDATE clicks SET total = total + 1 WHERE userid = 2 and url = 'http://baz.com'
INSERT INTO users (id, firstname, lastname) VALUES (0, 'Jack', 'Sparrow')
APPLY BATCH
""", matching=err)
def drop_counter_column_test(self):
"""Test for CASSANDRA-7831"""
cluster = self.cluster
cluster.populate(1).start()
node1, = cluster.nodelist()
session = self.patient_cql_connection(node1)
self.create_ks(session, 'counter_tests', 1)
session.execute("CREATE TABLE counter_bug (t int, c counter, primary key(t))")
session.execute("UPDATE counter_bug SET c = c + 1 where t = 1")
row = session.execute("SELECT * from counter_bug")
self.assertEqual(rows_to_list(row)[0], [1, 1])
self.assertEqual(len(row), 1)
session.execute("ALTER TABLE counter_bug drop c")
assert_invalid(session, "ALTER TABLE counter_bug add c counter", "Cannot re-add previously dropped counter column c")
def test_no_counters_in_user_types(self):
# CASSANDRA-7672
cluster = self.cluster
cluster.populate(1).start()
[node1] = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'user_types', 1)
stmt = """
USE user_types
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE t_item (
sub_one COUNTER )
"""
assert_invalid(cursor, stmt, 'A user type cannot contain counters')
def test_no_counters_in_user_types(self):
# CASSANDRA-7672
cluster = self.cluster
cluster.populate(1).start()
[node1] = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'user_types', 1)
stmt = """
USE user_types
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE t_item (
sub_one COUNTER )
"""
assert_invalid(cursor, stmt, 'A user type cannot contain counters')
def drop_counter_column_test(self):
"""Test for CASSANDRA-7831"""
cluster = self.cluster
cluster.populate(1).start()
node1, = cluster.nodelist()
session = self.patient_cql_connection(node1)
self.create_ks(session, 'counter_tests', 1)
session.execute(
"CREATE TABLE counter_bug (t int, c counter, primary key(t))")
session.execute("UPDATE counter_bug SET c = c + 1 where t = 1")
row = session.execute("SELECT * from counter_bug")
self.assertEqual(rows_to_list(row)[0], [1, 1])
self.assertEqual(len(row), 1)
session.execute("ALTER TABLE counter_bug drop c")
assert_invalid(session, "ALTER TABLE counter_bug add c counter",
"Cannot re-add previously dropped counter column c")
def udt_test(self):
""" Test (somewhat indirectly) that user queries involving UDT's are properly encoded (due to driver not recognizing UDT syntax) """
cluster = self.cluster
cluster.populate(3).start()
[node1, node2, node3] = cluster.nodelist()
time.sleep(.5)
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'ks', 3)
# create udt and insert correctly (should be successful)
cursor.execute('CREATE TYPE address (city text,zip int);')
cursor.execute('CREATE TABLE user_profiles (login text PRIMARY KEY, addresses map<text, frozen<address>>);')
cursor.execute("INSERT INTO user_profiles(login, addresses) VALUES ('tsmith', { 'home': {city: 'San Fransisco',zip: 94110 }});")
#note here address looks likes a map -> which is what the driver thinks it is. udt is encoded server side, we test that if addresses is changed slightly whether encoder recognizes the errors
# try adding a field - see if will be encoded to a udt (should return error)
assert_invalid(cursor, "INSERT INTO user_profiles(login, addresses) VALUES ('jsmith', { 'home': {street: 'El Camino Real', city: 'San Fransisco', zip: 94110 }});", "Unknown field 'street' in value of user defined type address")
# try modifying a field name - see if will be encoded to a udt (should return error)
assert_invalid(cursor, "INSERT INTO user_profiles(login, addresses) VALUES ('fsmith', { 'home': {cityname: 'San Fransisco', zip: 94110 }});", "Unknown field 'cityname' in value of user defined type address")
# try modifying a type within the collection - see if will be encoded to a udt (should return error)
assert_invalid(cursor, "INSERT INTO user_profiles(login, addresses) VALUES ('fsmith', { 'home': {city: 'San Fransisco', zip: '94110' }});", "Invalid map literal for addresses")
def grant_revoke_validation_test(self):
self.prepare()
cassandra = self.get_cursor(user='******', password='******')
cassandra.execute("CREATE USER cathy WITH PASSWORD '12345'")
cassandra.execute("CREATE KEYSPACE ks WITH replication = {'class':'SimpleStrategy', 'replication_factor':1}")
assert_invalid(cassandra, "GRANT ALL ON KEYSPACE nonexistent TO cathy", "<keyspace nonexistent> doesn't exist")
assert_invalid(cassandra, "GRANT ALL ON KEYSPACE ks TO nonexistent", "User nonexistent doesn't exist")
assert_invalid(cassandra, "REVOKE ALL ON KEYSPACE nonexistent FROM cathy", "<keyspace nonexistent> doesn't exist")
assert_invalid(cassandra, "REVOKE ALL ON KEYSPACE ks FROM nonexistent", "User nonexistent doesn't exist")
def validate_empty_column_name_test(self):
cluster = self.cluster
cluster.populate(1).start()
node1 = cluster.nodelist()[0]
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'counter_tests', 1)
cursor.execute("""
CREATE TABLE compact_counter_table (
pk int,
ck text,
value counter,
PRIMARY KEY (pk, ck))
WITH COMPACT STORAGE
""")
assert_invalid(cursor, "UPDATE compact_counter_table SET value = value + 1 WHERE pk = 0 AND ck = ''")
assert_invalid(cursor, "UPDATE compact_counter_table SET value = value - 1 WHERE pk = 0 AND ck = ''")
cursor.execute("UPDATE compact_counter_table SET value = value + 5 WHERE pk = 0 AND ck = 'ck'")
cursor.execute("UPDATE compact_counter_table SET value = value - 2 WHERE pk = 0 AND ck = 'ck'")
assert_one(cursor, "SELECT pk, ck, value FROM compact_counter_table", [0, 'ck', 3])
def grant_revoke_validation_test(self):
self.prepare()
cassandra = self.get_cursor(user='******', password='******')
cassandra.execute("CREATE USER cathy WITH PASSWORD '12345'")
cassandra.execute(
"CREATE KEYSPACE ks WITH replication = {'class':'SimpleStrategy', 'replication_factor':1}"
)
assert_invalid(cassandra, "GRANT ALL ON KEYSPACE nonexistent TO cathy",
"<keyspace nonexistent> doesn't exist")
assert_invalid(cassandra, "GRANT ALL ON KEYSPACE ks TO nonexistent",
"User nonexistent doesn't exist")
assert_invalid(cassandra,
"REVOKE ALL ON KEYSPACE nonexistent FROM cathy",
"<keyspace nonexistent> doesn't exist")
assert_invalid(cassandra, "REVOKE ALL ON KEYSPACE ks FROM nonexistent",
"User nonexistent doesn't exist")
def test_migration(self):
""" Test migration of user functions """
cluster = self.cluster
# Uses 3 nodes just to make sure function mutations are correctly serialized
cluster.populate(3).start()
node1 = cluster.nodelist()[0]
node2 = cluster.nodelist()[1]
node3 = cluster.nodelist()[2]
time.sleep(0.2)
cursor1 = self.patient_cql_connection(node1, version=cql_version)
cursor2 = self.patient_cql_connection(node2, version=cql_version)
cursor3 = self.patient_cql_connection(node3, version=cql_version)
self.create_ks(cursor1, 'ks', 1)
cursor1.execute("""
CREATE TABLE udf_kv (
key int primary key,
value double
);
""")
time.sleep(1)
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" % (1, 1))
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" % (2, 2))
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" % (3, 3))
cursor1.execute("create or replace function x_sin ( input double ) returns double language java as 'if (input==null) return null; return Double.valueOf(Math.sin(input.doubleValue()));'")
cursor2.execute("create or replace function x_cos ( input double ) returns double language java as 'if (input==null) return null; return Double.valueOf(Math.cos(input.doubleValue()));'")
cursor3.execute("create or replace function x_tan ( input double ) returns double language java as 'if (input==null) return null; return Double.valueOf(Math.tan(input.doubleValue()));'")
time.sleep(1)
res = cursor1.execute("SELECT key, value, x_sin(value), x_cos(value), x_tan(value) FROM ks.udf_kv where key = %d" % 1)
assert res == [[1, 1.0, 0.8414709848078965, 0.5403023058681398, 1.5574077246549023]], res
res = cursor2.execute("SELECT key, value, x_sin(value), x_cos(value), x_tan(value) FROM ks.udf_kv where key = %d" % 2)
assert res == [[2, 2.0, math.sin(2.0), math.cos(2.0), math.tan(2.0)]], res
res = cursor3.execute("SELECT key, value, x_sin(value), x_cos(value), x_tan(value) FROM ks.udf_kv where key = %d" % 3)
assert res == [[3, 3.0, math.sin(3.0), math.cos(3.0), math.tan(3.0)]], res
cursor2.execute("drop function x_sin")
cursor3.execute("drop function x_cos")
cursor1.execute("drop function x_tan")
assert_invalid(cursor1, "SELECT key, value, sin(value), cos(value), tan(value) FROM udf_kv where key = 1")
assert_invalid(cursor2, "SELECT key, value, sin(value), cos(value), tan(value) FROM udf_kv where key = 1")
assert_invalid(cursor3, "SELECT key, value, sin(value), cos(value), tan(value) FROM udf_kv where key = 1")
def udf_test(self):
""" Test User Defined Functions """
cluster = self.cluster
cluster.populate(3).start()
[node1,node2, node3] = cluster.nodelist()
cqlversion = "3.0.0"
cursor1 = self.patient_cql_connection(node1, version=cqlversion)
self.create_ks(cursor1, 'ks', 1)
cursor1.execute("""
CREATE TABLE udf_kv (
key int primary key,
value double
);
""")
#some test values
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" % (1, 1))
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" % (2, 2))
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" % (3, 3))
#insert basic math udfs
cursor1.execute("CREATE FUNCTION foo::sin ( input double ) RETURNS double LANGUAGE java AS 'return Double.valueOf(Math.sin(input.doubleValue()));'")
cursor1.execute("CREATE FUNCTION foo::cos ( input double ) RETURNS double LANGUAGE java AS 'return Double.valueOf(Math.cos(input.doubleValue()));'")
cursor1.execute("CREATE FUNCTION foo::tan ( input double ) RETURNS double LANGUAGE java AS 'return Double.valueOf(Math.tan(input.doubleValue()));'")
time.sleep(1)
#check that functions works by returning correct value
assert_all(cursor1,"SELECT key, value, foo::sin(value), foo::cos(value), foo::tan(value) FROM ks.udf_kv where key = 1", [[ 1, 1.0, math.sin(1.0), math.cos(1.0), math.tan(1.0) ]])
assert_all(cursor1,"SELECT key, value, foo::sin(value), foo::cos(value), foo::tan(value) FROM ks.udf_kv where key = 2", [[ 2, 2.0, math.sin(2.0), math.cos(2.0), math.tan(2.0) ]])
assert_all(cursor1,"SELECT key, value, foo::sin(value), foo::cos(value), foo::tan(value) FROM ks.udf_kv where key = 3", [[ 3, 3.0, math.sin(3.0), math.cos(3.0), math.tan(3.0) ]])
#check that functions are correctly confined to namespaces
assert_invalid(cursor1, "SELECT key, value, sin(value), cos(value), tan(value) FROM ks.udf_kv where key = 4", "Unknown function 'sin'")
#try creating function returning the wrong type, should error
assert_invalid(cursor1, "CREATE FUNCTION bad::sin ( input double ) RETURNS double LANGUAGE java AS 'return Math.sin(input.doubleValue());'", "Could not compile function 'bad::sin' from Java source:")
#try giving existing function bad input, should error
assert_invalid(cursor1, "SELECT key, value, foo::sin(key), foo::cos(KEYy), foo::tan(key) FROM ks.udf_kv where key = 1", "Type error: key cannot be passed as argument 0 of function foo::sin of type double")
def udt_test(self):
""" Test (somewhat indirectly) that user queries involving UDT's are properly encoded (due to driver not recognizing UDT syntax) """
cluster = self.cluster
cluster.populate(3).start()
[node1, node2, node3] = cluster.nodelist()
time.sleep(.5)
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'ks', 3)
# create udt and insert correctly (should be successful)
cursor.execute('CREATE TYPE address (city text,zip int);')
cursor.execute(
'CREATE TABLE user_profiles (login text PRIMARY KEY, addresses map<text, frozen<address>>);'
)
cursor.execute(
"INSERT INTO user_profiles(login, addresses) VALUES ('tsmith', { 'home': {city: 'San Fransisco',zip: 94110 }});"
)
#note here address looks likes a map -> which is what the driver thinks it is. udt is encoded server side, we test that if addresses is changed slightly whether encoder recognizes the errors
# try adding a field - see if will be encoded to a udt (should return error)
assert_invalid(
cursor,
"INSERT INTO user_profiles(login, addresses) VALUES ('jsmith', { 'home': {street: 'El Camino Real', city: 'San Fransisco', zip: 94110 }});",
"Unknown field 'street' in value of user defined type address")
# try modifying a field name - see if will be encoded to a udt (should return error)
assert_invalid(
cursor,
"INSERT INTO user_profiles(login, addresses) VALUES ('fsmith', { 'home': {cityname: 'San Fransisco', zip: 94110 }});",
"Unknown field 'cityname' in value of user defined type address")
# try modifying a type within the collection - see if will be encoded to a udt (should return error)
assert_invalid(
cursor,
"INSERT INTO user_profiles(login, addresses) VALUES ('fsmith', { 'home': {city: 'San Fransisco', zip: '94110' }});",
"Invalid map literal for addresses")
def dropping_nonexistent_user_throws_exception_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
assert_invalid(cursor, 'DROP USER nonexistent',
"User nonexistent doesn't exist")
def password_authenticator_create_user_requires_password_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
assert_invalid(cursor, "CREATE USER jackob NOSUPERUSER",
'PasswordAuthenticator requires PASSWORD option')
def test_set_indexes(self):
"""
Checks that secondary indexes on sets work for querying.
"""
cluster = self.cluster
cluster.populate(1).start()
[node1] = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'set_index_search', 1)
stmt = ("CREATE TABLE set_index_search.users ("
"user_id uuid PRIMARY KEY,"
"email text,"
"uuids set<uuid>);")
cursor.execute(stmt)
# no index present yet, make sure there's an error trying to query column
stmt = ("SELECT * from set_index_search.users where uuids contains {some_uuid}").format(some_uuid=uuid.uuid4())
assert_invalid(cursor, stmt, 'No secondary indexes on the restricted columns support the provided operators')
# add index and query again (even though there are no rows in the table yet)
stmt = "CREATE INDEX user_uuids on set_index_search.users (uuids);"
cursor.execute(stmt)
stmt = ("SELECT * from set_index_search.users where uuids contains {some_uuid}").format(some_uuid=uuid.uuid4())
row = cursor.execute(stmt)
self.assertEqual(0, len(row))
# add a row which doesn't specify data for the indexed column, and query again
user1_uuid = uuid.uuid4()
stmt = ("INSERT INTO set_index_search.users (user_id, email) values ({user_id}, '*****@*****.**')"
).format(user_id=user1_uuid)
cursor.execute(stmt)
stmt = ("SELECT * from set_index_search.users where uuids contains {some_uuid}").format(some_uuid=uuid.uuid4())
row = cursor.execute(stmt)
self.assertEqual(0, len(row))
_id = uuid.uuid4()
# alter the row to add a single item to the indexed set
stmt = ("UPDATE set_index_search.users set uuids = {{{id}}} where user_id = {user_id}").format(id=_id, user_id=user1_uuid)
cursor.execute(stmt)
stmt = ("SELECT * from set_index_search.users where uuids contains {some_uuid}").format(some_uuid=_id)
row = cursor.execute(stmt)
self.assertEqual(1, len(row))
# add a bunch of user records and query them back
shared_uuid = uuid.uuid4() # this uuid will be on all records
log = []
for i in range(50000):
user_uuid = uuid.uuid4()
unshared_uuid = uuid.uuid4()
# give each record a unique email address using the int index
stmt = ("INSERT INTO set_index_search.users (user_id, email, uuids)"
"values ({user_uuid}, '{prefix}@example.com', {{{s_uuid}, {u_uuid}}})"
).format(user_uuid=user_uuid, prefix=i, s_uuid=shared_uuid, u_uuid=unshared_uuid)
cursor.execute(stmt)
log.append(
{'user_id': user_uuid,
'email':str(i)+'@example.com',
'unshared_uuid':unshared_uuid}
)
# confirm there is now 50k rows with the 'shared' uuid above in the secondary index
stmt = ("SELECT * from set_index_search.users where uuids contains {shared_uuid}").format(shared_uuid=shared_uuid)
rows = cursor.execute(stmt)
result = [row for row in rows]
self.assertEqual(50000, len(result))
# shuffle the log in-place, and double-check a slice of records by querying the secondary index
random.shuffle(log)
for log_entry in log[:1000]:
stmt = ("SELECT user_id, email, uuids FROM set_index_search.users where uuids contains {unshared_uuid}"
).format(unshared_uuid=log_entry['unshared_uuid'])
rows = cursor.execute(stmt)
self.assertEqual(1, len(rows))
db_user_id, db_email, db_uuids = rows[0]
self.assertEqual(db_user_id, log_entry['user_id'])
self.assertEqual(db_email, log_entry['email'])
self.assertTrue(shared_uuid in db_uuids)
self.assertTrue(log_entry['unshared_uuid'] in db_uuids)
def udf_overload_test(self):
cluster = self.cluster
cluster.populate(3).start()
[node1,node2, node3] = cluster.nodelist()
cqlversion = "3.0.0"
cursor1 = self.patient_cql_connection(node1, version=cqlversion)
self.create_ks(cursor1, 'ks', 1)
cursor1.execute("CREATE TABLE tab (k text PRIMARY KEY, v int)");
test = "foo"
cursor1.execute("INSERT INTO tab (k, v) VALUES ('foo' , 1);")
# create overloaded udfs
cursor1.execute("CREATE FUNCTION overloaded(v varchar) RETURNS text LANGUAGE java AS 'return \"f1\";'");
cursor1.execute("CREATE OR REPLACE FUNCTION overloaded(i int) RETURNS text LANGUAGE java AS 'return \"f2\";'");
cursor1.execute("CREATE OR REPLACE FUNCTION overloaded(v1 text, v2 text) RETURNS text LANGUAGE java AS 'return \"f3\";'");
cursor1.execute("CREATE OR REPLACE FUNCTION overloaded(v ascii) RETURNS text LANGUAGE java AS 'return \"f1\";'");
#ensure that works with correct specificity
assert_invalid(cursor1, "SELECT v FROM tab WHERE k = overloaded('foo')");
assert_none(cursor1, "SELECT v FROM tab WHERE k = overloaded((text) 'foo')");
assert_none(cursor1, "SELECT v FROM tab WHERE k = overloaded((ascii) 'foo')");
assert_none(cursor1, "SELECT v FROM tab WHERE k = overloaded((varchar) 'foo')");
#try non-existent functions
assert_invalid(cursor1, "DROP FUNCTION overloaded(boolean)");
assert_invalid(cursor1, "DROP FUNCTION overloaded(bigint)");
#try dropping overloaded - should fail because ambiguous
assert_invalid(cursor1, "DROP FUNCTION overloaded");
cursor1.execute("DROP FUNCTION overloaded(varchar)");
assert_invalid(cursor1, "SELECT v FROM tab WHERE k = overloaded((text)'foo')");
cursor1.execute("DROP FUNCTION overloaded(text, text)");
assert_invalid(cursor1, "SELECT v FROM tab WHERE k = overloaded((text)'foo',(text)'bar')");
cursor1.execute("DROP FUNCTION overloaded(ascii)");
assert_invalid(cursor1, "SELECT v FROM tab WHERE k = overloaded((ascii)'foo')");
#should now work - unambiguous
cursor1.execute("DROP FUNCTION overloaded");
def udf_overload_test(self):
session = self.prepare(nodes=3)
session.execute("CREATE TABLE tab (k text PRIMARY KEY, v int)");
test = "foo"
session.execute("INSERT INTO tab (k, v) VALUES ('foo' , 1);")
# create overloaded udfs
session.execute("CREATE FUNCTION overloaded(v varchar) RETURNS text LANGUAGE java AS 'return \"f1\";'");
session.execute("CREATE OR REPLACE FUNCTION overloaded(i int) RETURNS text LANGUAGE java AS 'return \"f2\";'");
session.execute("CREATE OR REPLACE FUNCTION overloaded(v1 text, v2 text) RETURNS text LANGUAGE java AS 'return \"f3\";'");
session.execute("CREATE OR REPLACE FUNCTION overloaded(v ascii) RETURNS text LANGUAGE java AS 'return \"f1\";'");
#ensure that works with correct specificity
assert_invalid(session, "SELECT v FROM tab WHERE k = overloaded('foo')");
assert_none(session, "SELECT v FROM tab WHERE k = overloaded((text) 'foo')");
assert_none(session, "SELECT v FROM tab WHERE k = overloaded((ascii) 'foo')");
assert_none(session, "SELECT v FROM tab WHERE k = overloaded((varchar) 'foo')");
#try non-existent functions
assert_invalid(session, "DROP FUNCTION overloaded(boolean)");
assert_invalid(session, "DROP FUNCTION overloaded(bigint)");
#try dropping overloaded - should fail because ambiguous
assert_invalid(session, "DROP FUNCTION overloaded");
session.execute("DROP FUNCTION overloaded(varchar)");
assert_invalid(session, "SELECT v FROM tab WHERE k = overloaded((text)'foo')");
session.execute("DROP FUNCTION overloaded(text, text)");
assert_invalid(session, "SELECT v FROM tab WHERE k = overloaded((text)'foo',(text)'bar')");
session.execute("DROP FUNCTION overloaded(ascii)");
assert_invalid(session, "SELECT v FROM tab WHERE k = overloaded((ascii)'foo')");
#should now work - unambiguous
session.execute("DROP FUNCTION overloaded");
def test_map_indexes(self):
"""
Checks that secondary indexes on maps work for querying on both keys and values
"""
cluster = self.cluster
cluster.populate(1).start()
[node1] = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'map_index_search', 1)
stmt = ("CREATE TABLE map_index_search.users ("
"user_id uuid PRIMARY KEY,"
"email text,"
"uuids map<uuid, uuid>);")
cursor.execute(stmt)
# no index present yet, make sure there's an error trying to query column
stmt = (
"SELECT * from map_index_search.users where uuids contains {some_uuid}"
).format(some_uuid=uuid.uuid4())
assert_invalid(
cursor, stmt,
'No secondary indexes on the restricted columns support the provided operators'
)
stmt = (
"SELECT * from map_index_search.users where uuids contains key {some_uuid}"
).format(some_uuid=uuid.uuid4())
assert_invalid(
cursor, stmt,
'No secondary indexes on the restricted columns support the provided operators'
)
# add index on keys and query again (even though there are no rows in the table yet)
stmt = "CREATE INDEX user_uuids on map_index_search.users (KEYS(uuids));"
cursor.execute(stmt)
stmt = "SELECT * from map_index_search.users where uuids contains key {some_uuid}".format(
some_uuid=uuid.uuid4())
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
# add a row which doesn't specify data for the indexed column, and query again
user1_uuid = uuid.uuid4()
stmt = ("INSERT INTO map_index_search.users (user_id, email)"
"values ({user_id}, '*****@*****.**')").format(
user_id=user1_uuid)
cursor.execute(stmt)
stmt = (
"SELECT * from map_index_search.users where uuids contains key {some_uuid}"
).format(some_uuid=uuid.uuid4())
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
_id = uuid.uuid4()
# alter the row to add a single item to the indexed map
stmt = (
"UPDATE map_index_search.users set uuids = {{{id}:{user_id}}} where user_id = {user_id}"
).format(id=_id, user_id=user1_uuid)
cursor.execute(stmt)
stmt = (
"SELECT * from map_index_search.users where uuids contains key {some_uuid}"
).format(some_uuid=_id)
rows = cursor.execute(stmt)
self.assertEqual(1, len(rows))
# add a bunch of user records and query them back
shared_uuid = uuid.uuid4() # this uuid will be on all records
log = []
for i in range(50000):
user_uuid = uuid.uuid4()
unshared_uuid1 = uuid.uuid4()
unshared_uuid2 = uuid.uuid4()
# give each record a unique email address using the int index, add unique ids for keys and values
stmt = (
"INSERT INTO map_index_search.users (user_id, email, uuids)"
"values ({user_uuid}, '{prefix}@example.com', {{{u_uuid1}:{u_uuid2}, {s_uuid}:{s_uuid}}})"
).format(user_uuid=user_uuid,
prefix=i,
s_uuid=shared_uuid,
u_uuid1=unshared_uuid1,
u_uuid2=unshared_uuid2)
cursor.execute(stmt)
log.append({
'user_id': user_uuid,
'email': str(i) + '@example.com',
'unshared_uuid1': unshared_uuid1,
'unshared_uuid2': unshared_uuid2
})
# confirm there is now 50k rows with the 'shared' uuid above in the secondary index
stmt = (
"SELECT * from map_index_search.users where uuids contains key {shared_uuid}"
).format(shared_uuid=shared_uuid)
rows = cursor.execute(stmt)
result = [row for row in rows]
self.assertEqual(50000, len(result))
# shuffle the log in-place, and double-check a slice of records by querying the secondary index on keys
random.shuffle(log)
for log_entry in log[:1000]:
stmt = (
"SELECT user_id, email, uuids FROM map_index_search.users where uuids contains key {unshared_uuid1}"
).format(unshared_uuid1=log_entry['unshared_uuid1'])
row = cursor.execute(stmt)
rows = self.assertEqual(1, len(row))
db_user_id, db_email, db_uuids = row[0]
self.assertEqual(db_user_id, log_entry['user_id'])
self.assertEqual(db_email, log_entry['email'])
self.assertTrue(shared_uuid in db_uuids)
self.assertTrue(log_entry['unshared_uuid1'] in db_uuids)
# attempt to add an index on map values as well (should fail)
stmt = "CREATE INDEX user_uuids on map_index_search.users (uuids);"
matching = "Cannot create index on uuids values, an index on uuids keys already exists and indexing a map on both keys and values at the same time is not currently supported"
assert_invalid(cursor, stmt, matching)
# since cannot have index on map keys and values remove current index on keys
stmt = "DROP INDEX user_uuids;"
cursor.execute(stmt)
# add index on values (will index rows added prior)
stmt = "CREATE INDEX user_uids on map_index_search.users (uuids);"
cursor.execute(stmt)
# shuffle the log in-place, and double-check a slice of records by querying the secondary index
random.shuffle(log)
time.sleep(10)
# since we already inserted unique ids for values as well, check that appropriate recors are found
for log_entry in log[:1000]:
stmt = (
"SELECT user_id, email, uuids FROM map_index_search.users where uuids contains {unshared_uuid2}"
).format(unshared_uuid2=log_entry['unshared_uuid2'])
rows = cursor.execute(stmt)
self.assertEqual(1, len(rows))
db_user_id, db_email, db_uuids = rows[0]
self.assertEqual(db_user_id, log_entry['user_id'])
self.assertEqual(db_email, log_entry['email'])
self.assertTrue(shared_uuid in db_uuids)
self.assertTrue(log_entry['unshared_uuid2'] in db_uuids.values())
def test_type_dropping(self):
"""
Tests that a type cannot be dropped when in use, and otherwise can be dropped.
"""
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'user_type_dropping', 2)
stmt = """
USE user_type_dropping
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE simple_type (
user_number int
)
"""
cursor.execute(stmt)
stmt = """
CREATE TABLE simple_table (
id uuid PRIMARY KEY,
number frozen<simple_type>
)
"""
cursor.execute(stmt)
# Make sure the scheam propagate
time.sleep(2)
_id = uuid.uuid4()
stmt = """
INSERT INTO simple_table (id, number)
VALUES ({id}, {{user_number: 1}});
""".format(id=_id)
cursor.execute(stmt)
stmt = """
DROP TYPE simple_type;
"""
assert_invalid(
cursor, stmt,
'Cannot drop user type user_type_dropping.simple_type as it is still used by table user_type_dropping.simple_table'
)
# now that we've confirmed that a user type cannot be dropped while in use
# let's remove the offending table
# TODO: uncomment below after CASSANDRA-6472 is resolved
# and add another check to make sure the table/type drops succeed
stmt = """
DROP TABLE simple_table;
""".format(id=_id)
cursor.execute(stmt)
stmt = """
DROP TYPE simple_type;
"""
cursor.execute(stmt)
# now let's have a look at the system schema and make sure no user types are defined
stmt = """
SELECT type_name from system.schema_usertypes;
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
def password_authenticator_create_user_requires_password_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
assert_invalid(cursor, "CREATE USER jackob NOSUPERUSER", 'PasswordAuthenticator requires PASSWORD option')
def test_migration(self):
""" Test migration of user functions """
cluster = self.cluster
# Uses 3 nodes just to make sure function mutations are correctly serialized
cluster.populate(3).start()
node1 = cluster.nodelist()[0]
node2 = cluster.nodelist()[1]
node3 = cluster.nodelist()[2]
time.sleep(0.2)
cursor1 = self.patient_exclusive_cql_connection(node1)
cursor2 = self.patient_exclusive_cql_connection(node2)
cursor3 = self.patient_exclusive_cql_connection(node3)
self.create_ks(cursor1, 'ks', 1)
cursor2.execute("use ks")
cursor3.execute("use ks")
cursor1.execute("""
CREATE TABLE udf_kv (
key int primary key,
value double
);
""")
time.sleep(1)
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" %
(1, 1))
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" %
(2, 2))
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" %
(3, 3))
cursor1.execute(
"create or replace function x_sin ( input double ) returns double language java as 'if (input==null) return null; return Double.valueOf(Math.sin(input.doubleValue()));'"
)
cursor2.execute(
"create or replace function x_cos ( input double ) returns double language java as 'if (input==null) return null; return Double.valueOf(Math.cos(input.doubleValue()));'"
)
cursor3.execute(
"create or replace function x_tan ( input double ) returns double language java as 'if (input==null) return null; return Double.valueOf(Math.tan(input.doubleValue()));'"
)
time.sleep(1)
assert_one(
cursor1,
"SELECT key, value, x_sin(value), x_cos(value), x_tan(value) FROM ks.udf_kv where key = %d"
% 1, [
1, 1.0, 0.8414709848078965, 0.5403023058681398,
1.5574077246549023
])
assert_one(
cursor2,
"SELECT key, value, x_sin(value), x_cos(value), x_tan(value) FROM ks.udf_kv where key = %d"
% 2, [2, 2.0, math.sin(2.0),
math.cos(2.0),
math.tan(2.0)])
assert_one(
cursor3,
"SELECT key, value, x_sin(value), x_cos(value), x_tan(value) FROM ks.udf_kv where key = %d"
% 3, [3, 3.0, math.sin(3.0),
math.cos(3.0),
math.tan(3.0)])
cursor4 = self.patient_cql_connection(node1)
#check that functions are correctly confined to namespaces
assert_invalid(
cursor4,
"SELECT key, value, sin(value), cos(value), tan(value) FROM ks.udf_kv where key = 4",
"Unknown function 'sin'")
#try giving existing function bad input, should error
assert_invalid(
cursor1,
"SELECT key, value, x_sin(key), foo_cos(KEYy), foo_tan(key) FROM ks.udf_kv where key = 1",
"Type error: key cannot be passed as argument 0 of function ks.x_sin of type double"
)
cursor2.execute("drop function x_sin")
cursor3.execute("drop function x_cos")
cursor1.execute("drop function x_tan")
assert_invalid(
cursor1,
"SELECT key, value, sin(value), cos(value), tan(value) FROM udf_kv where key = 1"
)
assert_invalid(
cursor2,
"SELECT key, value, sin(value), cos(value), tan(value) FROM udf_kv where key = 1"
)
assert_invalid(
cursor3,
"SELECT key, value, sin(value), cos(value), tan(value) FROM udf_kv where key = 1"
)
#try creating function returning the wrong type, should error
assert_invalid(
cursor1,
"CREATE FUNCTION bad_sin ( input double ) RETURNS double LANGUAGE java AS 'return Math.sin(input.doubleValue());'",
"Could not compile function 'ks.bad_sin' from Java source:")
def altering_nonexistent_user_throws_exception_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
assert_invalid(cursor, "ALTER USER nonexistent WITH PASSWORD 'doesn''tmatter'", "User nonexistent doesn't exist",)
def dropping_nonexistent_user_throws_exception_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
assert_invalid(cursor, 'DROP USER nonexistent', "User nonexistent doesn't exist")
def user_cant_drop_themselves_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
assert_invalid(cursor, "DROP USER cassandra", "Users aren't allowed to DROP themselves")
def test_type_dropping(self):
"""
Tests that a type cannot be dropped when in use, and otherwise can be dropped.
"""
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'user_type_dropping', 2)
stmt = """
USE user_type_dropping
"""
cursor.execute(stmt)
stmt = """
CREATE TYPE simple_type (
user_number int
)
"""
cursor.execute(stmt)
stmt = """
CREATE TABLE simple_table (
id uuid PRIMARY KEY,
number frozen<simple_type>
)
"""
cursor.execute(stmt)
# Make sure the scheam propagate
time.sleep(2)
_id = uuid.uuid4()
stmt = """
INSERT INTO simple_table (id, number)
VALUES ({id}, {{user_number: 1}});
""".format(id=_id)
cursor.execute(stmt)
stmt = """
DROP TYPE simple_type;
"""
assert_invalid(cursor, stmt, 'Cannot drop user type user_type_dropping.simple_type as it is still used by table user_type_dropping.simple_table')
# now that we've confirmed that a user type cannot be dropped while in use
# let's remove the offending table
# TODO: uncomment below after CASSANDRA-6472 is resolved
# and add another check to make sure the table/type drops succeed
stmt = """
DROP TABLE simple_table;
""".format(id=_id)
cursor.execute(stmt)
stmt = """
DROP TYPE simple_type;
"""
cursor.execute(stmt)
# now let's have a look at the system schema and make sure no user types are defined
stmt = """
SELECT type_name from system.schema_usertypes;
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
def test_type_as_part_of_pkey(self):
"""Tests user types as part of a composite pkey"""
# make sure we can define a table with a user type as part of the pkey
# and do a basic insert/query of data in that table.
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'user_type_pkeys', 2)
stmt = """
CREATE TYPE t_person_name (
first text,
middle text,
last text
)
"""
cursor.execute(stmt)
stmt = """
CREATE TABLE person_likes (
id uuid,
name frozen<t_person_name>,
like text,
PRIMARY KEY ((id, name))
)
"""
cursor.execute(stmt)
# Make sure the scheam propagate
time.sleep(2)
_id = uuid.uuid4()
stmt = """
INSERT INTO person_likes (id, name, like)
VALUES ({id}, {{first:'Nero', middle:'Claudius Caesar Augustus', last:'Germanicus'}}, 'arson');
""".format(id=_id)
cursor.execute(stmt)
# attempt to query without the user type portion of the pkey and confirm there is an error
stmt = """
SELECT id, name.first from person_likes where id={id};
""".format(id=_id)
if self.cluster.version() >= '3.0':
assert_invalid(
cursor, stmt,
'Partition key parts: name must be restricted as other parts are'
)
else:
assert_invalid(
cursor, stmt,
'Partition key part name must be restricted since preceding part is'
)
stmt = """
SELECT id, name.first, like from person_likes where id={id} and name = {{first:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'}};
""".format(id=_id)
rows = cursor.execute(stmt)
row_uuid, first_name, like = rows[0]
self.assertEqual(first_name, u'Nero')
self.assertEqual(like, u'arson')
def test_type_secondary_indexing(self):
"""
Confirm that user types are secondary-indexable
Similar procedure to TestSecondaryIndexesOnCollections.test_list_indexes
"""
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'user_type_indexing', 2)
stmt = """
CREATE TYPE t_person_name (
first text,
middle text,
last text
)
"""
cursor.execute(stmt)
stmt = """
CREATE TABLE person_likes (
id uuid PRIMARY KEY,
name frozen<t_person_name>,
like text
)
"""
cursor.execute(stmt)
# Make sure the scheam propagate
time.sleep(2)
# no index present yet, make sure there's an error trying to query column
stmt = """
SELECT * from person_likes where name = {first:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'};
"""
assert_invalid(
cursor, stmt,
'No secondary indexes on the restricted columns support the provided operators'
)
# add index and query again (even though there are no rows in the table yet)
stmt = """
CREATE INDEX person_likes_name on person_likes (name);
"""
cursor.execute(stmt)
stmt = """
SELECT * from person_likes where name = {first:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'};
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
# add a row which doesn't specify data for the indexed column, and query again
_id = uuid.uuid4()
stmt = """
INSERT INTO person_likes (id, like)
VALUES ({id}, 'long walks on the beach');
""".format(id=_id)
cursor.execute(stmt)
stmt = """
SELECT * from person_likes where name = {first:'Bob', middle: 'Testy', last: 'McTesterson'};
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
# finally let's add a queryable row, and get it back using the index
_id = uuid.uuid4()
stmt = """
INSERT INTO person_likes (id, name, like)
VALUES ({id}, {{first:'Nero', middle:'Claudius Caesar Augustus', last:'Germanicus'}}, 'arson');
""".format(id=_id)
cursor.execute(stmt)
stmt = """
SELECT id, name.first, like from person_likes where name = {first:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'};
"""
rows = cursor.execute(stmt)
row_uuid, first_name, like = rows[0]
self.assertEqual(str(row_uuid), str(_id))
self.assertEqual(first_name, u'Nero')
self.assertEqual(like, u'arson')
# rename a field in the type and make sure the index still works
stmt = """
ALTER TYPE t_person_name rename first to first_name;
"""
cursor.execute(stmt)
stmt = """
SELECT id, name.first_name, like from person_likes where name = {first_name:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'};
"""
rows = cursor.execute(stmt)
row_uuid, first_name, like = rows[0]
self.assertEqual(str(row_uuid), str(_id))
self.assertEqual(first_name, u'Nero')
self.assertEqual(like, u'arson')
# add another row to be sure the index is still adding new data
_id = uuid.uuid4()
stmt = """
INSERT INTO person_likes (id, name, like)
VALUES ({id}, {{first_name:'Abraham', middle:'', last:'Lincoln'}}, 'preserving unions');
""".format(id=_id)
cursor.execute(stmt)
stmt = """
SELECT id, name.first_name, like from person_likes where name = {first_name:'Abraham', middle:'', last:'Lincoln'};
"""
rows = cursor.execute(stmt)
row_uuid, first_name, like = rows[0]
self.assertEqual(str(row_uuid), str(_id))
self.assertEqual(first_name, u'Abraham')
self.assertEqual(like, u'preserving unions')
def cant_create_existing_user_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
cursor.execute("CREATE USER '*****@*****.**' WITH PASSWORD '12345' NOSUPERUSER")
assert_invalid(cursor, "CREATE USER '*****@*****.**' WITH PASSWORD '12345' NOSUPERUSER", 'User [email protected] already exists')
def user_cant_drop_themselves_test(self):
self.prepare()
cursor = self.get_cursor(user='******', password='******')
assert_invalid(cursor, "DROP USER cassandra",
"Users aren't allowed to DROP themselves")
def udf_overload_test(self):
session = self.prepare(nodes=3)
session.execute("CREATE TABLE tab (k text PRIMARY KEY, v int)")
test = "foo"
session.execute("INSERT INTO tab (k, v) VALUES ('foo' , 1);")
# create overloaded udfs
session.execute(
"CREATE FUNCTION overloaded(v varchar) RETURNS text LANGUAGE java AS 'return \"f1\";'"
)
session.execute(
"CREATE OR REPLACE FUNCTION overloaded(i int) RETURNS text LANGUAGE java AS 'return \"f2\";'"
)
session.execute(
"CREATE OR REPLACE FUNCTION overloaded(v1 text, v2 text) RETURNS text LANGUAGE java AS 'return \"f3\";'"
)
session.execute(
"CREATE OR REPLACE FUNCTION overloaded(v ascii) RETURNS text LANGUAGE java AS 'return \"f1\";'"
)
#ensure that works with correct specificity
assert_invalid(session,
"SELECT v FROM tab WHERE k = overloaded('foo')")
assert_none(session,
"SELECT v FROM tab WHERE k = overloaded((text) 'foo')")
assert_none(session,
"SELECT v FROM tab WHERE k = overloaded((ascii) 'foo')")
assert_none(session,
"SELECT v FROM tab WHERE k = overloaded((varchar) 'foo')")
#try non-existent functions
assert_invalid(session, "DROP FUNCTION overloaded(boolean)")
assert_invalid(session, "DROP FUNCTION overloaded(bigint)")
#try dropping overloaded - should fail because ambiguous
assert_invalid(session, "DROP FUNCTION overloaded")
session.execute("DROP FUNCTION overloaded(varchar)")
assert_invalid(session,
"SELECT v FROM tab WHERE k = overloaded((text)'foo')")
session.execute("DROP FUNCTION overloaded(text, text)")
assert_invalid(
session,
"SELECT v FROM tab WHERE k = overloaded((text)'foo',(text)'bar')")
session.execute("DROP FUNCTION overloaded(ascii)")
assert_invalid(session,
"SELECT v FROM tab WHERE k = overloaded((ascii)'foo')")
#should now work - unambiguous
session.execute("DROP FUNCTION overloaded")
def test_set_indexes(self):
"""
Checks that secondary indexes on sets work for querying.
"""
cluster = self.cluster
cluster.populate(1).start()
[node1] = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'set_index_search', 1)
stmt = ("CREATE TABLE set_index_search.users ("
"user_id uuid PRIMARY KEY,"
"email text,"
"uuids set<uuid>);")
cursor.execute(stmt)
# no index present yet, make sure there's an error trying to query column
stmt = (
"SELECT * from set_index_search.users where uuids contains {some_uuid}"
).format(some_uuid=uuid.uuid4())
assert_invalid(
cursor, stmt,
'No secondary indexes on the restricted columns support the provided operators'
)
# add index and query again (even though there are no rows in the table yet)
stmt = "CREATE INDEX user_uuids on set_index_search.users (uuids);"
cursor.execute(stmt)
stmt = (
"SELECT * from set_index_search.users where uuids contains {some_uuid}"
).format(some_uuid=uuid.uuid4())
row = cursor.execute(stmt)
self.assertEqual(0, len(row))
# add a row which doesn't specify data for the indexed column, and query again
user1_uuid = uuid.uuid4()
stmt = (
"INSERT INTO set_index_search.users (user_id, email) values ({user_id}, '*****@*****.**')"
).format(user_id=user1_uuid)
cursor.execute(stmt)
stmt = (
"SELECT * from set_index_search.users where uuids contains {some_uuid}"
).format(some_uuid=uuid.uuid4())
row = cursor.execute(stmt)
self.assertEqual(0, len(row))
_id = uuid.uuid4()
# alter the row to add a single item to the indexed set
stmt = (
"UPDATE set_index_search.users set uuids = {{{id}}} where user_id = {user_id}"
).format(id=_id, user_id=user1_uuid)
cursor.execute(stmt)
stmt = (
"SELECT * from set_index_search.users where uuids contains {some_uuid}"
).format(some_uuid=_id)
row = cursor.execute(stmt)
self.assertEqual(1, len(row))
# add a bunch of user records and query them back
shared_uuid = uuid.uuid4() # this uuid will be on all records
log = []
for i in range(50000):
user_uuid = uuid.uuid4()
unshared_uuid = uuid.uuid4()
# give each record a unique email address using the int index
stmt = (
"INSERT INTO set_index_search.users (user_id, email, uuids)"
"values ({user_uuid}, '{prefix}@example.com', {{{s_uuid}, {u_uuid}}})"
).format(user_uuid=user_uuid,
prefix=i,
s_uuid=shared_uuid,
u_uuid=unshared_uuid)
cursor.execute(stmt)
log.append({
'user_id': user_uuid,
'email': str(i) + '@example.com',
'unshared_uuid': unshared_uuid
})
# confirm there is now 50k rows with the 'shared' uuid above in the secondary index
stmt = (
"SELECT * from set_index_search.users where uuids contains {shared_uuid}"
).format(shared_uuid=shared_uuid)
rows = cursor.execute(stmt)
result = [row for row in rows]
self.assertEqual(50000, len(result))
# shuffle the log in-place, and double-check a slice of records by querying the secondary index
random.shuffle(log)
for log_entry in log[:1000]:
stmt = (
"SELECT user_id, email, uuids FROM set_index_search.users where uuids contains {unshared_uuid}"
).format(unshared_uuid=log_entry['unshared_uuid'])
rows = cursor.execute(stmt)
self.assertEqual(1, len(rows))
db_user_id, db_email, db_uuids = rows[0]
self.assertEqual(db_user_id, log_entry['user_id'])
self.assertEqual(db_email, log_entry['email'])
self.assertTrue(shared_uuid in db_uuids)
self.assertTrue(log_entry['unshared_uuid'] in db_uuids)
def test_map_indexes(self):
"""
Checks that secondary indexes on maps work for querying on both keys and values
"""
cluster = self.cluster
cluster.populate(1).start()
[node1] = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'map_index_search', 1)
stmt = ("CREATE TABLE map_index_search.users ("
"user_id uuid PRIMARY KEY,"
"email text,"
"uuids map<uuid, uuid>);")
cursor.execute(stmt)
# no index present yet, make sure there's an error trying to query column
stmt = ("SELECT * from map_index_search.users where uuids contains {some_uuid}").format(some_uuid=uuid.uuid4())
assert_invalid(cursor, stmt, 'No secondary indexes on the restricted columns support the provided operators')
stmt = ("SELECT * from map_index_search.users where uuids contains key {some_uuid}"
).format(some_uuid=uuid.uuid4())
assert_invalid(cursor, stmt, 'No secondary indexes on the restricted columns support the provided operators')
# add index on keys and query again (even though there are no rows in the table yet)
stmt = "CREATE INDEX user_uuids on map_index_search.users (KEYS(uuids));"
cursor.execute(stmt)
stmt = "SELECT * from map_index_search.users where uuids contains key {some_uuid}".format(some_uuid=uuid.uuid4())
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
# add a row which doesn't specify data for the indexed column, and query again
user1_uuid = uuid.uuid4()
stmt = ("INSERT INTO map_index_search.users (user_id, email)"
"values ({user_id}, '*****@*****.**')"
).format(user_id=user1_uuid)
cursor.execute(stmt)
stmt = ("SELECT * from map_index_search.users where uuids contains key {some_uuid}").format(some_uuid=uuid.uuid4())
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
_id = uuid.uuid4()
# alter the row to add a single item to the indexed map
stmt = ("UPDATE map_index_search.users set uuids = {{{id}:{user_id}}} where user_id = {user_id}"
).format(id=_id, user_id=user1_uuid)
cursor.execute(stmt)
stmt = ("SELECT * from map_index_search.users where uuids contains key {some_uuid}").format(some_uuid=_id)
rows = cursor.execute(stmt)
self.assertEqual(1, len(rows))
# add a bunch of user records and query them back
shared_uuid = uuid.uuid4() # this uuid will be on all records
log = []
for i in range(50000):
user_uuid = uuid.uuid4()
unshared_uuid1 = uuid.uuid4()
unshared_uuid2 = uuid.uuid4()
# give each record a unique email address using the int index, add unique ids for keys and values
stmt = ("INSERT INTO map_index_search.users (user_id, email, uuids)"
"values ({user_uuid}, '{prefix}@example.com', {{{u_uuid1}:{u_uuid2}, {s_uuid}:{s_uuid}}})"
).format(user_uuid=user_uuid, prefix=i, s_uuid=shared_uuid, u_uuid1=unshared_uuid1, u_uuid2=unshared_uuid2)
cursor.execute(stmt)
log.append(
{'user_id': user_uuid,
'email':str(i)+'@example.com',
'unshared_uuid1':unshared_uuid1,
'unshared_uuid2':unshared_uuid2}
)
# confirm there is now 50k rows with the 'shared' uuid above in the secondary index
stmt = ("SELECT * from map_index_search.users where uuids contains key {shared_uuid}"
).format(shared_uuid=shared_uuid)
rows = cursor.execute(stmt)
result = [row for row in rows]
self.assertEqual(50000, len(result))
# shuffle the log in-place, and double-check a slice of records by querying the secondary index on keys
random.shuffle(log)
for log_entry in log[:1000]:
stmt = ("SELECT user_id, email, uuids FROM map_index_search.users where uuids contains key {unshared_uuid1}"
).format(unshared_uuid1=log_entry['unshared_uuid1'])
row = cursor.execute(stmt)
rows = self.assertEqual(1, len(row))
db_user_id, db_email, db_uuids = row[0]
self.assertEqual(db_user_id, log_entry['user_id'])
self.assertEqual(db_email, log_entry['email'])
self.assertTrue(shared_uuid in db_uuids)
self.assertTrue(log_entry['unshared_uuid1'] in db_uuids)
# attempt to add an index on map values as well (should fail)
stmt = "CREATE INDEX user_uuids on map_index_search.users (uuids);"
matching = "Cannot create index on uuids values, an index on uuids keys already exists and indexing a map on both keys and values at the same time is not currently supported"
assert_invalid(cursor, stmt, matching)
# since cannot have index on map keys and values remove current index on keys
stmt = "DROP INDEX user_uuids;"
cursor.execute(stmt)
# add index on values (will index rows added prior)
stmt = "CREATE INDEX user_uids on map_index_search.users (uuids);"
cursor.execute(stmt)
# shuffle the log in-place, and double-check a slice of records by querying the secondary index
random.shuffle(log)
time.sleep(10)
# since we already inserted unique ids for values as well, check that appropriate recors are found
for log_entry in log[:1000]:
stmt = ("SELECT user_id, email, uuids FROM map_index_search.users where uuids contains {unshared_uuid2}"
).format(unshared_uuid2=log_entry['unshared_uuid2'])
rows = cursor.execute(stmt)
self.assertEqual(1, len(rows))
db_user_id, db_email, db_uuids = rows[0]
self.assertEqual(db_user_id, log_entry['user_id'])
self.assertEqual(db_email, log_entry['email'])
self.assertTrue(shared_uuid in db_uuids)
self.assertTrue(log_entry['unshared_uuid2'] in db_uuids.values())
def test_type_secondary_indexing(self):
"""
Confirm that user types are secondary-indexable
Similar procedure to TestSecondaryIndexesOnCollections.test_list_indexes
"""
cluster = self.cluster
cluster.populate(3).start()
node1, node2, node3 = cluster.nodelist()
cursor = self.patient_cql_connection(node1)
self.create_ks(cursor, 'user_type_indexing', 2)
stmt = """
CREATE TYPE t_person_name (
first text,
middle text,
last text
)
"""
cursor.execute(stmt)
stmt = """
CREATE TABLE person_likes (
id uuid PRIMARY KEY,
name frozen<t_person_name>,
like text
)
"""
cursor.execute(stmt)
# Make sure the scheam propagate
time.sleep(2)
# no index present yet, make sure there's an error trying to query column
stmt = """
SELECT * from person_likes where name = {first:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'};
"""
assert_invalid(cursor, stmt, 'No secondary indexes on the restricted columns support the provided operators')
# add index and query again (even though there are no rows in the table yet)
stmt = """
CREATE INDEX person_likes_name on person_likes (name);
"""
cursor.execute(stmt)
stmt = """
SELECT * from person_likes where name = {first:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'};
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
# add a row which doesn't specify data for the indexed column, and query again
_id = uuid.uuid4()
stmt = """
INSERT INTO person_likes (id, like)
VALUES ({id}, 'long walks on the beach');
""".format(id=_id)
cursor.execute(stmt)
stmt = """
SELECT * from person_likes where name = {first:'Bob', middle: 'Testy', last: 'McTesterson'};
"""
rows = cursor.execute(stmt)
self.assertEqual(0, len(rows))
# finally let's add a queryable row, and get it back using the index
_id = uuid.uuid4()
stmt = """
INSERT INTO person_likes (id, name, like)
VALUES ({id}, {{first:'Nero', middle:'Claudius Caesar Augustus', last:'Germanicus'}}, 'arson');
""".format(id=_id)
cursor.execute(stmt)
stmt = """
SELECT id, name.first, like from person_likes where name = {first:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'};
"""
rows = cursor.execute(stmt)
row_uuid, first_name, like = rows[0]
self.assertEqual(str(row_uuid), str(_id))
self.assertEqual(first_name, u'Nero')
self.assertEqual(like, u'arson')
# rename a field in the type and make sure the index still works
stmt = """
ALTER TYPE t_person_name rename first to first_name;
"""
cursor.execute(stmt)
stmt = """
SELECT id, name.first_name, like from person_likes where name = {first_name:'Nero', middle: 'Claudius Caesar Augustus', last: 'Germanicus'};
"""
rows = cursor.execute(stmt)
row_uuid, first_name, like = rows[0]
self.assertEqual(str(row_uuid), str(_id))
self.assertEqual(first_name, u'Nero')
self.assertEqual(like, u'arson')
# add another row to be sure the index is still adding new data
_id = uuid.uuid4()
stmt = """
INSERT INTO person_likes (id, name, like)
VALUES ({id}, {{first_name:'Abraham', middle:'', last:'Lincoln'}}, 'preserving unions');
""".format(id=_id)
cursor.execute(stmt)
stmt = """
SELECT id, name.first_name, like from person_likes where name = {first_name:'Abraham', middle:'', last:'Lincoln'};
"""
rows = cursor.execute(stmt)
row_uuid, first_name, like = rows[0]
self.assertEqual(str(row_uuid), str(_id))
self.assertEqual(first_name, u'Abraham')
self.assertEqual(like, u'preserving unions')
def test_migration(self):
""" Test migration of user functions """
cluster = self.cluster
# Uses 3 nodes just to make sure function mutations are correctly serialized
cluster.populate(3).start()
node1 = cluster.nodelist()[0]
node2 = cluster.nodelist()[1]
node3 = cluster.nodelist()[2]
time.sleep(0.2)
cursor1 = self.patient_exclusive_cql_connection(node1)
cursor2 = self.patient_exclusive_cql_connection(node2)
cursor3 = self.patient_exclusive_cql_connection(node3)
self.create_ks(cursor1, 'ks', 1)
cursor2.execute("use ks")
cursor3.execute("use ks")
cursor1.execute("""
CREATE TABLE udf_kv (
key int primary key,
value double
);
""")
time.sleep(1)
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" % (1, 1))
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" % (2, 2))
cursor1.execute("INSERT INTO udf_kv (key, value) VALUES (%d, %d)" % (3, 3))
cursor1.execute("create or replace function x_sin ( input double ) returns double language java as 'if (input==null) return null; return Double.valueOf(Math.sin(input.doubleValue()));'")
cursor2.execute("create or replace function x_cos ( input double ) returns double language java as 'if (input==null) return null; return Double.valueOf(Math.cos(input.doubleValue()));'")
cursor3.execute("create or replace function x_tan ( input double ) returns double language java as 'if (input==null) return null; return Double.valueOf(Math.tan(input.doubleValue()));'")
time.sleep(1)
assert_one(cursor1, "SELECT key, value, x_sin(value), x_cos(value), x_tan(value) FROM ks.udf_kv where key = %d" % 1, [1, 1.0, 0.8414709848078965, 0.5403023058681398, 1.5574077246549023])
assert_one(cursor2, "SELECT key, value, x_sin(value), x_cos(value), x_tan(value) FROM ks.udf_kv where key = %d" % 2, [2, 2.0, math.sin(2.0), math.cos(2.0), math.tan(2.0)])
assert_one(cursor3, "SELECT key, value, x_sin(value), x_cos(value), x_tan(value) FROM ks.udf_kv where key = %d" % 3, [3, 3.0, math.sin(3.0), math.cos(3.0), math.tan(3.0)])
cursor4 = self.patient_cql_connection(node1)
#check that functions are correctly confined to namespaces
assert_invalid(cursor4, "SELECT key, value, sin(value), cos(value), tan(value) FROM ks.udf_kv where key = 4", "Unknown function 'sin'")
#try giving existing function bad input, should error
assert_invalid(cursor1, "SELECT key, value, x_sin(key), foo_cos(KEYy), foo_tan(key) FROM ks.udf_kv where key = 1", "Type error: key cannot be passed as argument 0 of function ks.x_sin of type double")
cursor2.execute("drop function x_sin")
cursor3.execute("drop function x_cos")
cursor1.execute("drop function x_tan")
assert_invalid(cursor1, "SELECT key, value, sin(value), cos(value), tan(value) FROM udf_kv where key = 1")
assert_invalid(cursor2, "SELECT key, value, sin(value), cos(value), tan(value) FROM udf_kv where key = 1")
assert_invalid(cursor3, "SELECT key, value, sin(value), cos(value), tan(value) FROM udf_kv where key = 1")
#try creating function returning the wrong type, should error
assert_invalid(cursor1, "CREATE FUNCTION bad_sin ( input double ) RETURNS double LANGUAGE java AS 'return Math.sin(input.doubleValue());'", "Could not compile function 'ks.bad_sin' from Java source:")
