print 'Table Label:', column.getTableLabel()
print 'Column Name:', column.getColumnName()
print 'Column Label:', column.getColumnLabel()
print 'Type:', column.getType()
print 'Length:', column.getLength()
print 'Fractional Digits:', column.getFractionalDigits()
print 'Is Number Signed:', column.isNumberSigned()
print 'Collation Name:', column.getCollationName()
print 'Charset Name:', column.getCharacterSetName()
print 'Is Padded:', column.isPadded()
# Metadata Validation On Other Types
result = mySession.sql("create table table2 (one json, two char(5), three varchar(20), four text, five time, six date, seven timestamp, eight set('1','2','3'), nine enum ('a','b','c'))").execute()
table = schema.getTable('table2')
result = table.insert().values('{"name":"John", "Age":23}', 'test', 'sample', 'a_text', mysqlx.expr('NOW()'), mysqlx.expr('CURDATE()'), mysqlx.expr('NOW()'), '2','c').execute()
result = table.select().execute()
columns = result.getColumns()
column_index = 0
#@ Metadata on Json Column
column = columns[column_index]
column_index += 1
print 'Schema Name:', column.getSchemaName()
print 'Table Name:', column.getTableName()
print 'Table Label:', column.getTableLabel()
print 'Column Name:', column.getColumnName()
print 'Column Label:', column.getColumnLabel()
print 'Type:', column.getType()
print 'Length:', column.getLength()
print 'Type:', column.get_type()
print 'Length:', column.get_length()
print 'Fractional Digits:', column.get_fractional_digits()
print 'Is Number Signed:', column.is_number_signed()
print 'Collation Name:', column.get_collation_name()
print 'Charset Name:', column.get_character_set_name()
print 'Is Padded:', column.is_padded()
# Metadata Validation On Other Types
result = mySession.sql(
"create table table2 (one json, two char(5), three varchar(20), four text, five time, six date, seven timestamp, eight set('1','2','3'), nine enum ('a','b','c'))"
).execute()
table = schema.get_table('table2')
result = table.insert().values('{"name":"John", "Age":23}', 'test', 'sample',
'a_text', mysqlx.expr('NOW()'),
mysqlx.expr('CURDATE()'), mysqlx.expr('NOW()'),
'2', 'c').execute()
result = table.select().execute()
columns = result.get_columns()
column_index = 0
#@ Metadata on Json Column
column = columns[column_index]
column_index += 1
print 'Schema Name:', column.get_schema_name()
print 'Table Name:', column.get_table_name()
print 'Table Label:', column.get_table_label()
print 'Column Name:', column.get_column_name()
print 'Column Label:', column.get_column_label()
city_col = schema.get_collection("city")
db.start_transaction()
# Get the current population
statement = city_col.find("Geography.State = :state")
statement.fields("Name AS CityName", "Demographics.Population AS Population")
statement.sort("Name")
statement.bind("state", "Victoria")
before = statement.execute()
# Update the population for cities
# in the state of Victoria to increase
# the population with 10%
expr = mysqlx.expr("FLOOR(Demographics.Population * 1.10)")
result = city_col.modify(
"Geography.State = :state") \
.set("Demographics.Population", expr) \
.bind("state", "Victoria") \
.execute()
print("Number of affected docs: {0}".format(result.get_affected_items_count()))
print("")
after = statement.execute()
before_cities = before.fetch_all()
after_cities = after.fetch_all()
print("{0:10s} {1:^17s}".format("City", "Population"))
#@# CollectionModify: Error conditions on bind
crud = collection.modify('name = :data and age > :years').set('hobby', 'swim').bind()
crud = collection.modify('name = :data and age > :years').set('hobby', 'swim').bind(5, 5)
crud = collection.modify('name = :data and age > :years').set('hobby', 'swim').bind('another', 5)
#@# CollectionModify: Error conditions on execute
crud = collection.modify('name = :data and age > :years').set('hobby', 'swim').execute()
crud = collection.modify('name = :data and age > :years').set('hobby', 'swim').bind('years', 5).execute()
# ---------------------------------------
# Collection.Modify Unit Testing: Execution
# ---------------------------------------
#@# CollectionModify: Set Execution
result = collection.modify('name = "brian"').set('alias', 'bri').set('last_name', 'black').set('age', mysqlx.expr('13+1')).execute()
print 'Set Affected Rows:', result.affectedItemCount, '\n'
result = collection.find('name = "brian"').execute()
doc = result.fetchOne()
print dir(doc)
#@# CollectionModify: Set Execution Binding Array
result = collection.modify('name = "brian"').set('hobbies', mysqlx.expr(':list')).bind('list', ['soccer', 'dance', 'reading']).execute()
print 'Set Affected Rows:', result.affectedItemCount, '\n'
result = collection.find('name = "brian"').execute()
doc = result.fetchOne()
print dir(doc)
print doc.hobbies[0]
print doc.hobbies[1]
crud = table.update().set('age', 17).where('name = :data and age > :years').bind('years', 5).execute()
# ---------------------------------------
# Table.Modify Unit Testing: Execution
# ---------------------------------------
#@# TableUpdate: simple test
result = result = table.update().set('name', 'aline').where('age = 13').execute()
print 'Affected Rows:', result.affectedItemCount, '\n'
result = table.select().where('name = "aline"').execute()
record = result.fetchOne()
print "Updated Record:", record.name, record.age
#@ TableUpdate: test using expression
result = table.update().set('age', mysqlx.expr('13+10')).where('age = 13').execute()
print 'Affected Rows:', result.affectedItemCount, '\n'
result = table.select().where('age = 23').execute()
record = result.fetchOne()
print "Updated Record:", record.name, record.age
#@ TableUpdate: test using limits
result = table.update().set('age', mysqlx.expr(':new_year')).where('age = :old_year').limit(2).bind('new_year', 16).bind('old_year', 15).execute()
print 'Affected Rows:', result.affectedItemCount, '\n'
try:
print "lastDocumentId:", result.lastDocumentId
except Exception, err:
print "lastDocumentId:", str(err), "\n"
shell.storedSessions.remove('mysqlx_data')
mySession = mysqlx.getSession(shell.storedSessions.mysqlx_data)
#@ Stored Sessions, session from uri
shell.storedSessions.add('mysqlx_uri', __uripwd)
mySession = mysqlx.getSession(shell.storedSessions.mysqlx_uri, __pwd)
print "%s\n" % mySession
if mySession.uri == __displayuri:
print 'Session using right URI\n'
else:
print 'Session using wrong URI\n'
mySession.close()
#@ Stored Sessions, session from uri removed
shell.storedSessions.remove('mysqlx_uri')
mySession = mysqlx.getSession(shell.storedSessions.mysqlx_uri)
# @# mysqlx module: expression errors
# expr = mysqlx.expr()
# expr = mysqlx.expr(5)
#@ mysqlx module: expression
expr = mysqlx.expr('5+6')
print expr
# ----------------------------------------------
# Collection.Find Unit Testing: Error Conditions
# ----------------------------------------------
#@# CollectionFind: Error conditions on find
crud = collection.find(5)
crud = collection.find('test = "2')
#@# CollectionFind: Error conditions on fields
crud = collection.find().fields()
crud = collection.find().fields(5)
crud = collection.find().fields([])
crud = collection.find().fields(['name as alias', 5])
crud = collection.find().fields(mysqlx.expr('concat(field, "whatever")'));
#@# CollectionFind: Error conditions on groupBy
crud = collection.find().groupBy()
crud = collection.find().groupBy(5)
crud = collection.find().groupBy([])
crud = collection.find().groupBy(['name', 5])
#@# CollectionFind: Error conditions on having
crud = collection.find().groupBy(['name']).having()
crud = collection.find().groupBy(['name']).having(5)
#@# CollectionFind: Error conditions on sort
crud = collection.find().sort()
crud = collection.find().sort(5)
crud = collection.find().sort([])
validate_crud_functions(crud, ['add', 'execute', '__shell_hook__'])
#@ CollectionAdd: valid operations after execute
result = crud.execute()
validate_crud_functions(crud, ['add', 'execute', '__shell_hook__'])
# ---------------------------------------------
# Collection.add Unit Testing: Error Conditions
# ---------------------------------------------
#@# CollectionAdd: Error conditions on add
crud = collection.add()
crud = collection.add(45)
crud = collection.add(['invalid data'])
crud = collection.add(mysqlx.expr('5+1'))
crud = collection.add({'_id':45, 'name': 'sample'});
# ---------------------------------------
# Collection.Add Unit Testing: Execution
# ---------------------------------------
#@ Collection.add execution
result = collection.add({ "name": 'my first', "Passed": 'document', "count": 1 }).execute()
print "Affected Rows Single:", result.affectedItemCount, "\n"
print "lastDocumentId Single:", result.lastDocumentId
print "getLastDocumentId Single:", result.getLastDocumentId()
print "#lastDocumentIds Single:", len(result.lastDocumentIds)
print "#getLastDocumentIds Single:", len(result.getLastDocumentIds())
result = collection.add({ "_id": "sample_document", "name": 'my first', "passed": 'document', "count": 1 }).execute()
'swim').bind(
'another', 5)
#@# CollectionModify: Error conditions on execute
crud = collection.modify('name = :data and age > :years').set(
'hobby', 'swim').execute()
crud = collection.modify('name = :data and age > :years').set(
'hobby', 'swim').bind('years', 5).execute()
# ---------------------------------------
# Collection.Modify Unit Testing: Execution
# ---------------------------------------
#@# CollectionModify: Set Execution
result = collection.modify('name = "brian"').set('alias', 'bri').set(
'last_name', 'black').set('age', mysqlx.expr('13+1')).execute()
print 'Set Affected Rows:', result.affected_item_count, '\n'
result = collection.find('name = "brian"').execute()
doc = result.fetch_one()
print dir(doc)
#@# CollectionModify: Set Execution Binding Array
result = collection.modify('name = "brian"').set(
'hobbies',
mysqlx.expr(':list')).bind('list',
['soccer', 'dance', 'reading']).execute()
print 'Set Affected Rows:', result.affected_item_count, '\n'
result = collection.find('name = "brian"').execute()
doc = result.fetch_one()
#@ Stored Sessions, session from data dictionary removed
shell.storedSessions.remove('mysqlx_data')
mySession = mysqlx.get_session(shell.storedSessions.mysqlx_data)
#@ Stored Sessions, session from uri
shell.storedSessions.add('mysqlx_uri', __uripwd)
mySession = mysqlx.get_session(shell.storedSessions.mysqlx_uri, __pwd)
print "%s\n" % mySession
if mySession.uri == __displayuri:
print 'Session using right URI\n'
else:
print 'Session using wrong URI\n'
mySession.close()
#@ Stored Sessions, session from uri removed
shell.storedSessions.remove('mysqlx_uri')
mySession = mysqlx.get_session(shell.storedSessions.mysqlx_uri)
# @# mysqlx module: expression errors
# expr = mysqlx.expr()
# expr = mysqlx.expr(5)
#@ mysqlx module: expression
expr = mysqlx.expr('5+6')
print expr
print result.fetch_one().name + '\n'
# ----------------------------------------------
# Collection.Find Unit Testing: Error Conditions
# ----------------------------------------------
#@# CollectionFind: Error conditions on find
crud = collection.find(5)
crud = collection.find('test = "2')
#@# CollectionFind: Error conditions on fields
crud = collection.find().fields()
crud = collection.find().fields(5)
crud = collection.find().fields([])
crud = collection.find().fields(['name as alias', 5])
crud = collection.find().fields(mysqlx.expr('concat(field, "whatever")'))
#@# CollectionFind: Error conditions on group_by
crud = collection.find().group_by()
crud = collection.find().group_by(5)
crud = collection.find().group_by([])
crud = collection.find().group_by(['name', 5])
#@# CollectionFind: Error conditions on having
crud = collection.find().group_by(['name']).having()
crud = collection.find().group_by(['name']).having(5)
#@# CollectionFind: Error conditions on sort
crud = collection.find().sort()
crud = collection.find().sort(5)
crud = collection.find().sort([])
validate_crud_functions(crud, ['add', 'execute', '__shell_hook__'])
#@ CollectionAdd: valid operations after execute
result = crud.execute()
validate_crud_functions(crud, ['add', 'execute', '__shell_hook__'])
# ---------------------------------------------
# Collection.add Unit Testing: Error Conditions
# ---------------------------------------------
#@# CollectionAdd: Error conditions on add
crud = collection.add()
crud = collection.add(45)
crud = collection.add(['invalid data'])
crud = collection.add(mysqlx.expr('5+1'))
crud = collection.add({'_id':45, 'name': 'sample'});
# ---------------------------------------
# Collection.Add Unit Testing: Execution
# ---------------------------------------
#@ Collection.add execution
result = collection.add({ "name": 'my first', "Passed": 'document', "count": 1 }).execute()
print "Affected Rows Single:", result.affected_item_count, "\n"
print "last_document_id Single:", result.last_document_id
print "get_last_document_id Single:", result.get_last_document_id()
print "#last_document_ids Single:", len(result.last_document_ids)
print "#get_last_document_ids Single:", len(result.get_last_document_ids())
result = collection.add({ "_id": "sample_document", "name": 'my first', "passed": 'document', "count": 1 }).execute()
# ---------------------------------------
# Table.Modify Unit Testing: Execution
# ---------------------------------------
#@# TableUpdate: simple test
result = result = table.update().set('name',
'aline').where('age = 13').execute()
print 'Affected Rows:', result.affected_item_count, '\n'
result = table.select().where('name = "aline"').execute()
record = result.fetch_one()
print "Updated Record:", record.name, record.age
#@ TableUpdate: test using expression
result = table.update().set('age',
mysqlx.expr('13+10')).where('age = 13').execute()
print 'Affected Rows:', result.affected_item_count, '\n'
result = table.select().where('age = 23').execute()
record = result.fetch_one()
print "Updated Record:", record.name, record.age
#@ TableUpdate: test using limits
result = table.update().set(
'age', mysqlx.expr(':new_year')).where('age = :old_year').limit(2).bind(
'new_year', 16).bind('old_year', 15).execute()
print 'Affected Rows:', result.affected_item_count, '\n'
try:
print "last_document_id:", result.last_document_id
except Exception, err: