class UserCountryTableCreator(object):
DEST_TABLE = 'UserCountry'
def __init__(self, user, pwd):
self.ipCountryXlater = IpCountryDict()
self.user = user
self.pwd = pwd
self.db = MySQLDB(user=self.user, passwd=self.pwd, db='Edx')
self.db.dropTable(UserCountryTableCreator.DEST_TABLE)
self.db.createTable(UserCountryTableCreator.DEST_TABLE,
OrderedDict({'anon_screen_name' : 'varchar(40) NOT NULL DEFAULT ""',
'two_letter_country' : 'varchar(2) NOT NULL DEFAULT ""',
'three_letter_country' : 'varchar(3) NOT NULL DEFAULT ""',
'country' : 'varchar(255) NOT NULL DEFAULT ""'}))
def fillTable(self):
values = []
for (user, ip3LetterCountry) in self.db.query("SELECT DISTINCT anon_screen_name, ip_country FROM EventXtract"):
try:
(twoLetterCode, threeLetterCode, country) = self.ipCountryXlater.getBy3LetterCode(ip3LetterCountry)
except (ValueError,TypeError,KeyError) as e:
sys.stderr.write("Could not look up one country from (%s/%s): %s\n" % (user, ip3LetterCountry,`e`))
continue
values.append(tuple(['%s'%user,'%s'%twoLetterCode,'%s'%threeLetterCode,'%s'%country]))
colNameTuple = ('anon_screen_name','two_letter_country','three_letter_country','country')
self.db.bulkInsert(UserCountryTableCreator.DEST_TABLE, colNameTuple, values)
def makeIndex(self):
self.db.execute("CALL createIndexIfNotExists('UserCountryAnonIdx', 'UserCountry', 'anon_screen_name', 40);")
self.db.execute("CALL createIndexIfNotExists('UserCountryThreeLetIdx', 'UserCountry', 'three_letter_country', 3);")
def close(self):
self.db.close()
def log_into_mysql(self, user, db_pwd, db=None, host='localhost', **kwargs):
try:
# Try logging in, specifying the database in which all the tables
# will be created:
db = MySQLDB(user=user, passwd=db_pwd, db=db, host=host, **kwargs)
except ValueError as e:
# Does the db not exist yet?
if str(e).find("OperationalError(1049,") > -1:
# Log in, specifying an always present db to 'use':
db = MySQLDB(user=user, passwd=db_pwd, db='information_schema', host=host)
# Create the db:
db.execute('CREATE DATABASE %s;' % self.config_info.canvas_db_aux)
else:
raise DatabaseError(f"Cannot open Canvas database:\n{repr(e)}")
except Exception as e:
raise DatabaseError(f"Cannot open Canvas database:\n{repr(e)}")
# Work in UTC, b/c default on Mac MySQL 8 is local time,
# on Centos MySQL 5.7 is UTC; it's a mess:
(err, _warn) = db.execute('SET @@session.time_zone = "+00:00"')
if err is not None:
self.log_warn(f"Cannot set session time zone to UTC: {repr(err)}")
return db
class UserCountryTableCreator(object):
DEST_TABLE = 'UserCountry'
def __init__(self, user, pwd):
self.ipCountryXlater = IpCountryDict()
self.user = user
self.pwd = pwd
self.db = MySQLDB(user=self.user, passwd=self.pwd, db='Edx')
self.db.dropTable(UserCountryTableCreator.DEST_TABLE)
self.db.createTable(
UserCountryTableCreator.DEST_TABLE,
OrderedDict({
'anon_screen_name': 'varchar(40) NOT NULL DEFAULT ""',
'two_letter_country': 'varchar(2) NOT NULL DEFAULT ""',
'three_letter_country': 'varchar(3) NOT NULL DEFAULT ""',
'country': 'varchar(255) NOT NULL DEFAULT ""'
}))
def fillTable(self):
values = []
for (user, ip3LetterCountry) in self.db.query(
"SELECT DISTINCT anon_screen_name, ip_country FROM EventXtract"
):
try:
(twoLetterCode, threeLetterCode, country
) = self.ipCountryXlater.getBy3LetterCode(ip3LetterCountry)
except (ValueError, TypeError, KeyError) as e:
sys.stderr.write(
"Could not look up one country from (%s/%s): %s\n" %
(user, ip3LetterCountry, ` e `))
continue
values.append(
tuple([
'%s' % user,
'%s' % twoLetterCode,
'%s' % threeLetterCode,
'%s' % country
]))
colNameTuple = ('anon_screen_name', 'two_letter_country',
'three_letter_country', 'country')
self.db.bulkInsert(UserCountryTableCreator.DEST_TABLE, colNameTuple,
values)
def makeIndex(self):
self.db.execute(
"CALL createIndexIfNotExists('UserCountryAnonIdx', 'UserCountry', 'anon_screen_name', 40);"
)
self.db.execute(
"CALL createIndexIfNotExists('UserCountryThreeLetIdx', 'UserCountry', 'three_letter_country', 3);"
)
def close(self):
self.db.close()
def tearDownClass(cls):
super(AuxTableCopyTester, cls).tearDownClass()
if cls.test_host == 'localhost':
return
db = None
try:
# Remove the unittest db we created:
print(f"Removing database '{cls.db_name}'...")
db = MySQLDB(user=cls.user,
passwd=cls.mysql_pwd,
db='information_schema',
host=cls.test_host)
db.execute(f"DROP DATABASE {cls.db_name}")
print(print(f"Done removing database '{cls.db_name}'..."))
#AuxTableCopyTester.copier_obj.close()
pass
finally:
if db is not None:
db.close()
def log_into_mysql(cls, user, db_pwd, db=None):
host = AuxTableCopyTester.test_host
try:
# Try logging in, specifying the database in which all the tables
# will be created:
db = MySQLDB(user=user, passwd=db_pwd, db=db, host=host)
except ValueError as e:
# Does unittest not exist yet?
if str(e).find("OperationalError(1049,") > -1:
# Log in without specifying a db to 'use':
db = MySQLDB(user=user, passwd=db_pwd, host=host)
# Create the db:
db.execute('CREATE DATABASE %s;' % 'unittest')
else:
raise RuntimeError("Cannot open Canvas database: %s" % repr(e))
except Exception as e:
raise RuntimeError("Cannot open Canvas database: %s" % repr(e))
return db
class UserCountryTableCreator(object):
DEST_TABLE = 'UserCountry'
# Number of anon ids-country-2-letter-3-letter
# tuples to accumulate before inserting into
# UserCountry:
INSERT_BULK_SIZE = 15000
def __init__(self, user, pwd):
self.ipCountryXlater = IpCountryDict()
self.user = user
self.pwd = pwd
self.db = MySQLDB(user=self.user, passwd=self.pwd, db='Edx')
# Make sure table exists. It should, and it should be filled
# with all anon_screen_name and countries up the previous
# load:
createCmd = '''CREATE TABLE UserCountry (
anon_screen_name varchar(40) NOT NULL DEFAULT "",
two_letter_country varchar(2) NOT NULL DEFAULT "",
three_letter_country varchar(3) NOT NULL DEFAULT "",
country varchar(255) NOT NULL DEFAULT ""
) ENGINE=MyISAM;
'''
self.db.dropTable('UserCountry')
print("Creating table UserCountry...")
self.db.execute(createCmd)
print("Done creating table UserCountry.")
def fillTable(self):
query = "SELECT DISTINCT anon_screen_name, ip_country FROM EventXtract"
query_res_it = self.db.query(query)
done = False
# Order of columns for insert:
colNameTuple = ('anon_screen_name', 'two_letter_country',
'three_letter_country', 'country')
while not done:
values = []
print("%s: Starting one set of %s lookups..." %\
(str(datetime.datetime.today()),
UserCountryTableCreator.INSERT_BULK_SIZE))
for _ in range(UserCountryTableCreator.INSERT_BULK_SIZE):
try:
(anon_screen_name, ip3LetterCountry) = query_res_it.next()
except StopIteration:
done = True
break
# Try translating:
try:
(twoLetterCode, threeLetterCode,
country) = self.ipCountryXlater.getBy3LetterCode(
ip3LetterCountry)
except (ValueError, TypeError, KeyError):
twoLetterCode = 'XX'
threeLetterCode = 'XXX'
country = 'Not in lookup tbl'
#sys.stderr.write("Could not look up one country from (%s/%s): %s\n" % (user, ip3LetterCountry,`e`))
values.append(
tuple([
'%s' % anon_screen_name,
'%s' % twoLetterCode,
'%s' % threeLetterCode,
'%s' % country
]))
# Insert this chunk into the UserCountry table
print("%s: Inserting %s rows into UserCountry table..." %
(str(datetime.datetime.today()), len(values)))
(errors,
warnings) = self.db.bulkInsert(UserCountryTableCreator.DEST_TABLE,
colNameTuple, values)
if errors is not None:
print('Error(s) during UserCountry insert: %s' % errors)
sys.exit(1)
if warnings is not None:
print('Warning(s) during UserCountry insert: %s' % warnings)
print("%s: Done inserting %s rows into UserCountry table..." %
(str(datetime.datetime.today()), len(values)))
# ... and loop to process the next INSERT_BULK_SIZE batch
def makeIndex(self):
self.db.execute(
"CALL createIndexIfNotExists('UserCountryAnonIdx', 'UserCountry', 'anon_screen_name', 40);"
)
self.db.execute(
"CALL createIndexIfNotExists('UserCountryThreeLetIdx', 'UserCountry', 'three_letter_country', 3);"
)
def close(self):
self.db.close()
class TestPymysqlUtils(unittest.TestCase):
'''
Tests pymysql_utils.
'''
@classmethod
def setUpClass(cls):
# Ensure that a user unittest with the proper
# permissions exists in the db:
TestPymysqlUtils.env_ok = True
TestPymysqlUtils.err_msg = ''
try:
needed_grants = ['SELECT', 'INSERT', 'UPDATE',
'DELETE', 'CREATE', 'CREATE TEMPORARY TABLES',
'DROP', 'ALTER']
mysqldb = MySQLDB(host='localhost', port=3306, user='******', db='unittest')
grant_query = 'SHOW GRANTS FOR unittest@localhost'
query_it = mysqldb.query(grant_query)
# First row of the SHOW GRANTS response should be
# one of:
first_grants = ["GRANT USAGE ON *.* TO 'unittest'@'localhost'",
"GRANT USAGE ON *.* TO `unittest`@`localhost`"
]
# Second row depends on the order in which the
# grants were provided. The row will look something
# like:
# GRANT SELECT, INSERT, UPDATE, DELETE, ..., CREATE, DROP, ALTER ON `unittest`.* TO 'unittest'@'localhost'
# Verify:
usage_grant = query_it.next()
if usage_grant not in first_grants:
TestPymysqlUtils.err_msg = '''
User 'unittest' is missing USAGE grant needed to run the tests.
Also need this in your MySQL:
%s
''' % 'GRANT %s ON unittest.* TO unittest@localhost' % ','.join(needed_grants)
TestPymysqlUtils.env_ok = False
return
grants_str = query_it.next()
for needed_grant in needed_grants:
if grants_str.find(needed_grant) == -1:
TestPymysqlUtils.err_msg = '''
User 'unittest' does not have the '%s' permission needed to run the tests.
Need this in your MySQL:
%s
''' % (needed_grant, 'GRANT %s ON unittest.* TO unittest@localhost;' % ','.join(needed_grants))
TestPymysqlUtils.env_ok = False
return
except (ValueError,RuntimeError):
TestPymysqlUtils.err_msg = '''
For unit testing, localhost MySQL server must have
user 'unittest' without password, and a database
called 'unittest'. To create these prerequisites
in MySQL:
CREATE USER unittest@localhost;
CREATE DATABASE unittest;
This user needs permissions:
%s
''' % 'GRANT %s ON unittest.* TO unittest@localhost;' % ','.join(needed_grants)
TestPymysqlUtils.env_ok = False
# Check MySQL version:
try:
(major, minor) = TestPymysqlUtils.get_mysql_version()
except Exception as e:
raise OSError('Could not get mysql version number: %s' % str(e))
if major is None:
print('Warning: MySQL version number not found; testing as if V5.7')
TestPymysqlUtils.major = 5
TestPymysqlUtils.minor = 7
else:
TestPymysqlUtils.major = major
TestPymysqlUtils.minor = minor
known_versions = [(5,6), (5,7), (8,0)]
if (major,minor) not in known_versions:
print('Warning: MySQL version is %s.%s; but testing as if V5.7')
TestPymysqlUtils.major = 5
TestPymysqlUtils.minor = 7
def setUp(self):
if not TestPymysqlUtils.env_ok:
raise RuntimeError(TestPymysqlUtils.err_msg)
try:
self.mysqldb = MySQLDB(host='localhost', port=3306, user='******', db='unittest')
except ValueError as e:
self.fail(str(e) + " (For unit testing, localhost MySQL server must have user 'unittest' without password, and a database called 'unittest')")
# Make MySQL version more convenient to check:
if (TestPymysqlUtils.major == 5 and TestPymysqlUtils.minor >= 7) or \
TestPymysqlUtils.major >= 8:
self.mysql_ge_5_7 = True
else:
self.mysql_ge_5_7 = False
def tearDown(self):
if self.mysqldb.isOpen():
self.mysqldb.dropTable('unittest')
# Make sure the test didn't set a password
# for user unittest in the db:
self.mysqldb.execute("SET PASSWORD FOR unittest@localhost = '';")
self.mysqldb.close()
# ----------------------- Table Manilupation -------------------------
#-------------------------
# Creating and Dropping Tables
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testCreateAndDropTable(self):
mySchema = {
'col1' : 'INT',
'col2' : 'varchar(255)',
'col3' : 'FLOAT',
'col4' : 'TEXT',
#'col5' : 'JSON' # Only works MySQL 5.7 and up.
}
self.mysqldb.createTable('myTbl', mySchema, temporary=False)
# Get (('col4', 'text'), ('col2', 'varchar(255)'), ('col3', 'float'), ('col1', 'int(11)'))
# in some order:
cols = self.mysqldb.query('''SELECT COLUMN_NAME,COLUMN_TYPE
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'unittest'
AND TABLE_NAME = 'myTbl';
'''
)
self.assertEqual(sorted(cols),
[('col1', 'int(11)'),
('col2', 'varchar(255)'),
('col3', 'float'),
('col4', 'text')]
)
# Query mysql information schema to check for table
# present. Use raw cursor to test independently from
# the pymysql_utils query() method:
self.mysqldb.dropTable('myTbl')
cursor = self.mysqldb.connection.cursor()
tbl_exists_query = '''
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'unittest'
AND table_name = 'myTbl';
'''
cursor.execute(tbl_exists_query)
self.assertEqual(cursor.rowcount, 0)
cursor.close()
#-------------------------
# Creating Temporary Tables
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testCreateTempTable(self):
mySchema = {
'col1' : 'INT',
'col2' : 'varchar(255)',
'col3' : 'FLOAT',
'col4' : 'TEXT',
#'col5' : 'JSON' # Only works MySQL 5.7 and up.
}
self.mysqldb.createTable('myTbl', mySchema, temporary=True)
# Check that tbl exists.
# NOTE: can't use query to mysql.informationschema,
# b/c temp tables aren't listed there.
try:
# Will return some tuple; we don't
# care what exaclty, as long as the
# cmd doesn't fail:
self.mysqldb.query('DESC myTbl').next()
except Exception:
self.fail('Temporary table not found after creation.')
# Start new session, which should remove the table.
# Query mysql information schema to check for table
# present. Use raw cursor to test independently from
# the pymysql_utils query() method:
self.mysqldb.close()
try:
self.mysqldb = MySQLDB(host='localhost', port=3306, user='******', db='unittest')
except ValueError as e:
self.fail(str(e) + "Could not re-establish MySQL connection.")
# NOTE: can't use query to mysql.informationschema,
# b/c temp tables aren't listed there.
try:
self.mysqldb.query('DESC myTbl').next()
self.fail("Temporary table did not disappear with session exit.")
except ValueError:
pass
#-------------------------
# Table Truncation
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testTruncate(self):
# Initial test db with known num of rows:
rows_in_test_db = self.buildSmallDb()
cursor = self.mysqldb.connection.cursor()
cursor.execute('SELECT * FROM unittest;')
self.assertEqual(cursor.rowcount, rows_in_test_db)
self.mysqldb.truncateTable('unittest')
cursor.execute('SELECT * FROM unittest;')
self.assertEqual(cursor.rowcount, 0)
cursor.close()
# ----------------------- Insertion and Update -------------------------
#-------------------------
# Insert One Row
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testInsert(self):
schema = OrderedDict([('col1', 'INT'), ('col2', 'TEXT')])
self.mysqldb.createTable('unittest', schema)
colnameValueDict = OrderedDict([('col1', 10)])
self.mysqldb.insert('unittest', colnameValueDict)
self.assertEqual((10, None), self.mysqldb.query("SELECT * FROM unittest").next())
# for value in self.mysqldb.query("SELECT * FROM unittest"):
# print value
# Insert row with an explicit None:
colnameValueDict = OrderedDict([('col1', None)])
self.mysqldb.insert('unittest', colnameValueDict)
cursor = self.mysqldb.connection.cursor()
cursor.execute('SELECT col1 FROM unittest')
# Swallow the first row: 10, Null:
cursor.fetchone()
# Get col1 of the row we added (the 2nd row):
val = cursor.fetchone()
self.assertEqual(val, (None,))
cursor.close()
#-------------------------
# Insert One Row With Error
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testInsertWithError(self):
schema = OrderedDict([('col1', 'INT'), ('col2', 'TEXT')])
self.mysqldb.createTable('unittest', schema)
colnameValueDict = OrderedDict([('col1', 10)])
(errors,warnings) = self.mysqldb.insert('unittest', colnameValueDict)
self.assertIsNone(errors)
self.assertIsNone(warnings)
self.assertEqual((10, None), self.mysqldb.query("SELECT * FROM unittest").next())
# for value in self.mysqldb.query("SELECT * FROM unittest"):
# print value
#-------------------------
# Insert Several Columns
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testInsertSeveralColumns(self):
schema = OrderedDict([('col1', 'INT'), ('col2', 'TEXT')])
self.mysqldb.createTable('unittest', schema)
colnameValueDict = OrderedDict([('col1', 10), ('col2', 'My Poem')])
self.mysqldb.insert('unittest', colnameValueDict)
res = self.mysqldb.query("SELECT * FROM unittest").next()
self.assertEqual((10, 'My Poem'), res)
#-------------------------
# Bulk Insertion
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testBulkInsert(self):
# Called twice: once by the unittest engine,
# and again by testWithMySQLPassword() to
# exercise the pwd-bound branch in bulkInsert().
# Build test db (this already tests basic bulkinsert):
# col1 col2
# 10, 'col1'
# 20, 'col2'
# 30, 'col3'
self.buildSmallDb()
self.mysqldb.execute('ALTER TABLE unittest ADD PRIMARY KEY(col1)')
# Provoke a MySQL error: duplicate primary key (i.e. 10):
# Add another row: 10, 'newCol1':
colNames = ['col1', 'col2']
colValues = [(10, 'newCol1')]
(errors, warnings) = self.mysqldb.bulkInsert('unittest', colNames, colValues) #@UnusedVariable
# For MySQL 5.7, expect something like:
# ((u'Warning', 1062L, u"Duplicate entry '10' for key 'PRIMARY'"),)
# MySQL 5.6 just skips:
if self.mysql_ge_5_7:
self.assertEqual(len(warnings), 1)
else:
self.assertIsNone(warnings)
# First tuple should still be (10, 'col1'):
self.assertEqual('col1', self.mysqldb.query('SELECT col2 FROM unittest WHERE col1 = 10').next())
# Try update again, but with replacement:
(errors, warnings) = self.mysqldb.bulkInsert('unittest', colNames, colValues, onDupKey=DupKeyAction.REPLACE) #@UnusedVariable
self.assertIsNone(warnings)
# Now row should have changed:
self.assertEqual('newCol1', self.mysqldb.query('SELECT col2 FROM unittest WHERE col1 = 10').next())
# Insert a row with duplicate key, specifying IGNORE:
colNames = ['col1', 'col2']
colValues = [(10, 'newCol2')]
(errors, warnings) = self.mysqldb.bulkInsert('unittest', colNames, colValues, onDupKey=DupKeyAction.IGNORE) #@UnusedVariable
# Even when ignoring dup keys, MySQL 5.7/8.x issue a warning
# for each dup key:
if self.mysql_ge_5_7:
self.assertEqual(len(warnings), 1)
else:
self.assertIsNone(warnings)
self.assertEqual('newCol1', self.mysqldb.query('SELECT col2 FROM unittest WHERE col1 = 10').next())
# Insertions that include NULL values:
colValues = [(40, None), (50, None)]
(errors, warnings) = self.mysqldb.bulkInsert('unittest', colNames, colValues) #@UnusedVariable
self.assertEqual(None, self.mysqldb.query('SELECT col2 FROM unittest WHERE col1 = 40').next())
self.assertEqual(None, self.mysqldb.query('SELECT col2 FROM unittest WHERE col1 = 50').next())
# Provoke an error:
colNames = ['col1', 'col2', 'col3']
colValues = [(10, 'newCol2')]
(errors, warnings) = self.mysqldb.bulkInsert('unittest', colNames, colValues, onDupKey=DupKeyAction.IGNORE) #@UnusedVariable
self.assertEqual(len(errors), 1)
#-------------------------
# Updates
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testUpdate(self):
num_rows = self.buildSmallDb()
cursor = self.mysqldb.connection.cursor()
# Initially, col2 of row0 must be 'col1':
cursor.execute('SELECT col2 FROM unittest WHERE col1 = 10')
col2_row_zero = cursor.fetchone()
self.assertTupleEqual(col2_row_zero, ('col1',))
self.mysqldb.update('unittest', 'col1', 40, fromCondition='col1 = 10')
# Now no col1 with value 10 should exist:
cursor.execute('SELECT col2 FROM unittest WHERE col1 = 10')
self.assertEqual(cursor.rowcount, 0)
# But a row with col1 == 40 should have col2 == 'col1':
cursor.execute('SELECT col2 FROM unittest WHERE col1 = 40')
col2_res = cursor.fetchone()
self.assertTupleEqual(col2_res, ('col1',))
# Update *all* rows in one column:
self.mysqldb.update('unittest', 'col1', 0)
cursor.execute('SELECT count(*) FROM unittest WHERE col1 = 0')
res_count = cursor.fetchone()
self.assertTupleEqual(res_count, (num_rows,))
# Update with a MySQL NULL value by using Python None
# for input and output:
self.mysqldb.update('unittest', 'col1', None)
cursor.execute('SELECT count(*) FROM unittest WHERE col1 is %s', (None,))
res_count = cursor.fetchone()
self.assertTupleEqual(res_count, (num_rows,))
# Update with a MySQL NULL value by using Python None
# with WHERE clause: only set col1 to NULL where col2 = 'col2',
# i.e. in the 2nd row:
num_rows = self.buildSmallDb()
self.mysqldb.update('unittest', 'col1', None, "col2 = 'col2'")
cursor.execute('SELECT count(*) FROM unittest WHERE col1 is %s', (None,))
res_count = cursor.fetchone()
self.assertTupleEqual(res_count, (1,))
# Provoke an error:
(errors,warnings) = self.mysqldb.update('unittest', 'col6', 40, fromCondition='col1 = 10') #@UnusedVariable
self.assertEqual(len(errors), 1)
cursor.close()
# ----------------------- Queries -------------------------
#-------------------------
# Query With Result Iteration
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testQueryIterator(self):
self.buildSmallDb()
for rowNum, result in enumerate(self.mysqldb.query('SELECT col1,col2 FROM unittest')):
if rowNum == 0:
self.assertEqual((10, 'col1'), result)
elif rowNum == 1:
self.assertEqual((20, 'col2'), result)
elif rowNum == 2:
self.assertEqual((30, 'col3'), result)
# Test the dict cursor
self.mysqldb.close()
self.mysqldb = MySQLDB(host='localhost',
user='******',
db='unittest',
cursor_class=Cursors.DICT)
for result in self.mysqldb.query('SELECT col1,col2 FROM unittest'):
self.assertIsInstance(result, dict)
if result['col1'] == 10:
self.assertEqual(result['col2'], 'col1')
elif result['col1'] == 20:
self.assertEqual(result['col2'], 'col2')
elif result['col1'] == 30:
self.assertEqual(result['col2'], 'col3')
#-------------------------
# Query Unparameterized
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testExecuteArbitraryQuery(self):
self.buildSmallDb()
self.mysqldb.execute("UPDATE unittest SET col1=120")
for result in self.mysqldb.query('SELECT col1 FROM unittest'):
self.assertEqual(120, result)
#-------------------------
# Query Parameterized
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testExecuteArbitraryQueryParameterized(self):
self.buildSmallDb()
myVal = 130
self.mysqldb.executeParameterized("UPDATE unittest SET col1=%s", (myVal,))
for result in self.mysqldb.query('SELECT col1 FROM unittest'):
self.assertEqual(130, result)
# Provoke an error:
(errors,warnings) = self.mysqldb.executeParameterized("UPDATE unittest SET col10=%s", (myVal,)) #@UnusedVariable
self.assertEqual(len(errors), 1)
#-------------------------
# Reading System Variables
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testReadSysVariable(self):
this_host = socket.gethostname()
mysql_hostname = self.mysqldb.query('SELECT @@hostname').next()
self.assertIn(mysql_hostname, [this_host, 'localhost'])
#-------------------------
# User-Level Variables
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testUserVariables(self):
pre_foo = self.mysqldb.query("SELECT @foo").next()
self.assertEqual(pre_foo, None)
self.mysqldb.execute("SET @foo = 'new value';")
post_foo = self.mysqldb.query("SELECT @foo").next()
self.assertEqual(post_foo, 'new value')
self.mysqldb.execute("SET @foo = 'NULL';")
#-------------------------
# testDbName
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testDbName(self):
self.assertEqual(self.mysqldb.dbName(), 'unittest')
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testWithMySQLPassword(self):
try:
# Set a password for the unittest user:
if self.mysql_ge_5_7:
self.mysqldb.execute("SET PASSWORD FOR unittest@localhost = 'foobar'")
else:
self.mysqldb.execute("SET PASSWORD FOR unittest@localhost = PASSWORD('foobar')")
self.mysqldb.close()
# We should be unable to log in without a pwd:
with self.assertRaises(ValueError):
self.mysqldb = MySQLDB(host='localhost', user='******', db='unittest')
# Open new pymysql_db.MySQLDb instance, supplying pwd:
self.mysqldb = MySQLDB(host='localhost', user='******', passwd='foobar', db='unittest')
# Do a test query:
self.buildSmallDb()
res = self.mysqldb.query("SELECT col2 FROM unittest WHERE col1 = 10;").next()
self.assertEqual(res, 'col1')
# Bulk insert is also different for pwd vs. none:
self.testBulkInsert()
finally:
# Make sure the remove the pwd from user unittest,
# so that other tests will run successfully:
if self.mysql_ge_5_7:
self.mysqldb.execute("SET PASSWORD FOR unittest@localhost = ''")
else:
self.mysqldb.execute("SET PASSWORD FOR unittest@localhost = PASSWORD('')")
#-------------------------
# testResultCount
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testResultCount(self):
self.buildSmallDb()
query_str = 'SELECT * FROM unittest'
self.mysqldb.query(query_str)
self.assertEqual(self.mysqldb.result_count(query_str), 3)
#-------------------------
# testInterleavedQueries
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testInterleavedQueries(self):
self.buildSmallDb()
query_str1 = 'SELECT col2 FROM unittest ORDER BY col1'
query_str2 = 'SELECT col2 FROM unittest WHERE col1 = 20 or col1 = 30 ORDER BY col1'
res_it1 = self.mysqldb.query(query_str1)
res_it2 = self.mysqldb.query(query_str2)
self.assertEqual(res_it1.result_count(), 3)
self.assertEqual(res_it2.result_count(), 2)
self.assertEqual(self.mysqldb.result_count(query_str1), 3)
self.assertEqual(self.mysqldb.result_count(query_str2), 2)
self.assertEqual(res_it1.next(), 'col1')
self.assertEqual(res_it2.next(), 'col2')
self.assertEqual(res_it1.result_count(), 3)
self.assertEqual(res_it2.result_count(), 2)
self.assertEqual(self.mysqldb.result_count(query_str1), 3)
self.assertEqual(self.mysqldb.result_count(query_str2), 2)
self.assertEqual(res_it1.next(), 'col2')
self.assertEqual(res_it2.next(), 'col3')
self.assertEqual(res_it1.next(), 'col3')
with self.assertRaises(StopIteration):
res_it2.next()
with self.assertRaises(ValueError):
res_it2.result_count()
with self.assertRaises(ValueError):
self.mysqldb.result_count(query_str2)
#-------------------------
# testBadParameters
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testBadParameters(self):
self.mysqldb.close()
# Test setting parameters illegally to None:
try:
with self.assertRaises(Exception) as context:
MySQLDB(host=None, port=3306, user='******', db='unittest')
self.assertTrue("None value(s) for ['host']; none of host,port,user,passwd or db must be None"
in str(context.exception))
with self.assertRaises(Exception) as context:
MySQLDB(host='localhost', port=None, user='******', db='unittest')
self.assertTrue("None value(s) for ['port']; none of host,port,user,passwd or db must be None"
in str(context.exception))
with self.assertRaises(Exception) as context:
MySQLDB(host='localhost', port=3306, user=None, db='unittest')
self.assertTrue("None value(s) for ['user']; none of host,port,user,passwd or db must be None"
in str(context.exception))
with self.assertRaises(Exception) as context:
MySQLDB(host='localhost', port=3306, user='******', db=None)
self.assertTrue("None value(s) for ['db']; none of host,port,user,passwd or db must be None"
in str(context.exception))
with self.assertRaises(Exception) as context:
MySQLDB(host='localhost', port=3306, user='******', passwd=None, db='unittest')
self.assertTrue("None value(s) for ['passwd']; none of host,port,user,passwd or db must be None"
in str(context.exception))
with self.assertRaises(Exception) as context:
MySQLDB(host=None, port=3306, user=None, db=None)
self.assertTrue("None value(s) for ['host', 'db', 'user']; none of host,port,user,passwd or db must be None"
in str(context.exception))
except AssertionError:
# Create a better message than 'False is not True'.
# That useless msg is generated if an expected exception
# above is NOT raised:
raise AssertionError('Expected ValueError exception "%s" was not raised.' % context.exception.message)
# Check data types of parameters:
try:
# One illegal type: host==10:
with self.assertRaises(Exception) as context:
# Integer instead of string for host:
MySQLDB(host=10, port=3306, user='******', db='myDb')
self.assertTrue("Value(s) ['host'] have bad type;host,user,passwd, and db must be strings; port must be int."
in str(context.exception))
# Two illegal types: host and user:
with self.assertRaises(Exception) as context:
# Integer instead of string for host:
MySQLDB(host=10, port=3306, user=30, db='myDb')
self.assertTrue("Value(s) ['host', 'user'] have bad type;host,user,passwd, and db must be strings; port must be int."
in str(context.exception))
# Port being string instead of required int:
with self.assertRaises(Exception) as context:
# Integer instead of string for host:
MySQLDB(host='myHost', port='3306', user='******', db='myDb')
self.assertTrue("Port must be an integer; was" in str(context.exception))
except AssertionError:
# Create a better message than 'False is not True'.
# That useless msg is generated if an expected exception
# above is NOT raised:
raise AssertionError('Expected ValueError exception "%s" was not raised.' % context.exception.message)
#-------------------------
# testIsOpen
#--------------
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testIsOpen(self):
self.assertTrue(self.mysqldb.isOpen())
self.mysqldb.close()
self.assertFalse(self.mysqldb.isOpen())
# ----------------------- UTILITIES -------------------------
#-------------------------
# buildSmallDb
#--------------
def buildSmallDb(self):
'''
Creates a two-col, three-row table in database
unittest. The table is called 'unittest'.
Returns number of rows created.
==== ======
col1 col2
==== ======
10 'col1'
20 'col2'
30 'col3'
==== ======
'''
cur = self.mysqldb.connection.cursor()
with no_warn_no_table():
cur.execute('DROP TABLE IF EXISTS unittest')
cur.execute('CREATE TABLE unittest (col1 INT, col2 TEXT)')
cur.execute("INSERT INTO unittest VALUES (10, 'col1')")
cur.execute("INSERT INTO unittest VALUES (20, 'col2')")
cur.execute("INSERT INTO unittest VALUES (30, 'col3')")
self.mysqldb.connection.commit()
cur.close()
return 3
#-------------------------
# get_mysql_version
#--------------
@classmethod
def get_mysql_version(cls):
'''
Return a tuple: (major, minor).
Example, for MySQL 5.7.15, return (5,7).
Return (None,None) if version number not found.
'''
# Where is mysql client program?
mysql_path = MySQLDB.find_mysql_path()
# Get version string, which looks like this:
# 'Distrib 5.7.15, for osx10.11 (x86_64) using EditLine wrapper\n'
version_str = subprocess.check_output([mysql_path, '--version']).decode('utf-8')
# Isolate the major and minor version numbers (e.g. '5', and '7')
pat = re.compile(r'([0-9]*)[.]([0-9]*)[.]')
match_obj = pat.search(version_str)
if match_obj is None:
return (None,None)
(major, minor) = match_obj.groups()
return (int(major), int(minor))
# self.mysqldb.dropTable('unittest')
# self.mysqldb.createTable('unittest', schema)
# colNames = ['col1', 'col2']
# colValues = [(10, 'col1'), (20, 'col2'), (30, 'col3')]
# warnings = self.mysqldb.bulkInsert('unittest', colNames, colValues)
# self.assertIsNone(warnings)
# return 3
#-------------------------
# convert_to_string
#--------------
def convert_to_string(self, strLike):
'''
The str/byte/unicode type mess between
Python 2.7 and 3.x. We want as 'normal'
a string as possible. Surely there is a
more elegant way.
@param strLike: a Python 3 str (i.e. unicode string), a Python 3 binary str.
a Python 2.7 unicode string, or a Python 2.7 str.
@type strLike: {str|unicode|byte}
'''
try:
if type(strLike) == eval('unicode'):
# Python 2.7 unicode --> str:
strLike = strLike.encode('UTF-8')
except NameError:
pass
try:
if type(strLike) == eval('bytes'):
# Python 3 byte string:
strLike = strLike.decode('UTF-8')
except NameError:
pass
return strLike
#-------------------------
# read_config_file_content
#--------------
@classmethod
def read_config_file_content(cls):
'''
Read and return content of pymysql_utils.cnf.py
'''
curr_dir = os.path.dirname(__file__)
config_file_name = os.path.join(curr_dir, 'pymysql_utils.cnf.py')
with open(config_file_name, 'r') as fd:
return fd.read()
#-------------------------
# write_config_file_content
#--------------
@classmethod
def write_config_file_content(cls, content):
curr_dir = os.path.dirname(__file__)
config_file_name = os.path.join(curr_dir, 'pymysql_utils.cnf.py')
with open(config_file_name, 'w') as fd:
return fd.write(content)
class ExtToAnonTableMaker(object):
def __init__(self, extIdsFileName):
user = '******'
# Try to find pwd in specified user's $HOME/.ssh/mysql
currUserHomeDir = os.getenv('HOME')
if currUserHomeDir is None:
pwd = None
else:
try:
# Need to access MySQL db as its 'root':
with open(os.path.join(currUserHomeDir, '.ssh/mysql_root')) as fd:
pwd = fd.readline().strip()
# Switch user to 'root' b/c from now on it will need to be root:
user = '******'
except IOError:
# No .ssh subdir of user's home, or no mysql inside .ssh:
pwd = None
self.db = MySQLDB(user=user, passwd=pwd, db='Misc')
self.makeTmpExtsTable()
self.loadExtIds(extIdsFileName)
outfile = tempfile.NamedTemporaryFile(prefix='extsIntsScreenNames', suffix='.csv', delete=True)
# Need to close this file, and thereby delete it,
# so that MySQL is willing to write to it. Yes,
# that's a race condition. But this is an
# admin script, run by one person:
outfile.close()
self.findScreenNames(outfile.name)
self.computeAnonFromScreenNames(outfile.name)
def makeTmpExtsTable(self):
# Create table to load the CSV file into:
self.externalsTblNm = self.idGenerator(prefix='ExternalsTbl_')
mysqlCmd = 'CREATE TEMPORARY TABLE %s (ext_id varchar(32));' % self.externalsTblNm
self.db.execute(mysqlCmd)
def loadExtIds(self, csvExtsFileName):
# Clean up line endings in the extIds file.
# Between Win, MySQL, Mac, and R, we get
# linefeeds and crs:
cleanExtsFile = tempfile.NamedTemporaryFile(prefix='cleanExts', suffix='.csv', delete=False)
os.chmod(cleanExtsFile.name, stat.S_IRUSR | stat.S_IRGRP | stat.S_IROTH)
rawExtsFd = open(csvExtsFileName, 'r')
for line in rawExtsFd:
cleanExtsFile.write(line.strip() + '\n')
cleanExtsFile.close()
rawExtsFd.close()
mysqlCmd = "LOAD DATA INFILE '%s' " % cleanExtsFile.name +\
'INTO TABLE %s ' % self.externalsTblNm +\
"FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
self.db.execute(mysqlCmd)
# Delete the cleaned-exts file:
os.remove(cleanExtsFile.name)
def findScreenNames(self, outCSVFileName):
mysqlCmd = "SELECT 'ext_id','user_int_id','screen_name'" +\
"UNION " +\
"SELECT ext_id," +\
" user_int_id," +\
" username " +\
" INTO OUTFILE '%s'" % outCSVFileName +\
" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'" +\
" FROM " +\
" (SELECT ext_id," +\
" user_id AS user_int_id " +\
" FROM %s LEFT JOIN edxprod.student_anonymoususerid " % self.externalsTblNm +\
" ON %s.ext_id = edxprod.student_anonymoususerid.anonymous_user_id " % self.externalsTblNm +\
" ) AS ExtAndInts " +\
" LEFT JOIN edxprod.auth_user " +\
" ON edxprod.auth_user.id = ExtAndInts.user_int_id;"
self.db.execute(mysqlCmd)
def computeAnonFromScreenNames(self, extIntNameFileName):
with open(extIntNameFileName, 'r') as inFd:
print('ext_id,anon_screen_name')
firstLineDiscarded = False
for line in inFd:
(extId, intId, screenName) = line.split(',') #@UnusedVariable
#********
#print('ScreenName.strip(\'"\'): \'%s\'' % screenName.strip().strip('"'))
#********
if firstLineDiscarded:
screenName = screenName.strip().strip('"')
if screenName == '\\N':
print ('%s,%s' % (extId.strip('"'),'NULL'))
else:
print('%s,%s' % (extId.strip('"'),EdXTrackLogJSONParser.makeHash(screenName)))
else:
firstLineDiscarded = True
def idGenerator(self, prefix='', size=6, chars=string.ascii_uppercase + string.digits):
randPart = ''.join(random.choice(chars) for _ in range(size))
return prefix + randPart
class EdxForumScrubber(object):
'''
Given a .bson file of OpenEdX Forum posts, load the file
into a MongoDB. Then pull a post at a time, anonymize, and
insert a selection of fields into a MySQL db. The MongoDb
entries look like this::
{
"_id" : ObjectId("51b75a48f359c40a00000028"),
"_type" : "Comment",
"abuse_flaggers" : [ ],
"anonymous" : false,
"anonymous_to_peers" : false,
"at_position_list" : [ ],
"author_id" : "26344",
"author_username" : "Minelly48",
"body" : "I am Gwen.I am a nursing professor who took statistics many years ago and want to refresh my knowledge.",
"comment_thread_id" : ObjectId("51b754e5f359c40a0000001d"),
"course_id" : "Medicine/HRP258/Statistics_in_Medicine",
"created_at" : ISODate("2013-06-11T17:11:36.831Z"),
"endorsed" : false,
"historical_abuse_flaggers" : [ ],
"parent_ids" : [ ],
"updated_at" : ISODate("2013-06-11T17:11:36.831Z"),
"visible" : true,
"votes" : {
"count" : 2,
"down" : [ ],
"down_count" : 0,
"point" : 2,
"up" : [
"40325",
"20323"
],
"up_count" : 2
},
"sk" : "51b75a48f359c40a00000028"
}
Depending on parameter allowAnonScreenName in the __init__() method,
forum entries in the relational database will be associated with the
same hash that is used to anonymize other parts of the OpenEdX data.
'''
LOG_DIR = '/home/dataman/Data/EdX/NonTransformLogs'
# Pattern for email id - strings of alphabets/numbers/dots/hyphens followed
# by an @ or at followed by combinations of dot/. followed by the edu/com
# also, allow for spaces
emailPattern='(.*)\s+([a-zA-Z0-9\(\.\-]+)[@]([a-zA-Z0-9\.]+)(.)(edu|com)\\s*(.*)'
#emailPattern='(.*)\\s+([a-zA-Z0-9\\.]+)\\s*(\\(f.*b.*)?(@)\\s*([a-zA-Z0-9\\.\\s;]+)\\s*(\\.)\\s*(edu|com)\\s+(.*)'
compiledEmailPattern = re.compile(emailPattern);
# Pattern for replacing embedded double quotes in post bodies,
# unless they are already escaped w/ a backslash. The
# {0,1} means a match if zero or one repetition. It's
# needed so that double quotes at the very start of a
# string are matched: no preceding character at all:
#doublQuoteReplPattern = re.compile(r'[^\\]{0,1}"')
doublQuoteReplPattern = re.compile(r'[\\]{0,}"')
# Schema of EdxForum.contents: an ordered dict that is
# used twice: the table creation MySQL command is constructed
# from this dict, and the dict is used to ensure that
# all its keys (i.e. future column names) are present
# in each MongoDB object. See also createForumTable().
# In createForumTable() either entry anon_screen_name,
# or screen_name in the dict below will be deleted, based
# on whether we are asked to anonymize or not:
forumSchema = OrderedDict({})
forumSchema['forum_post_id'] = "varchar(40) NOT NULL DEFAULT 'unavailable'"
forumSchema['anon_screen_name'] = "varchar(40) NOT NULL DEFAULT 'anon_screen_name_redacted'" # This or next deleted based on anonymize yes/no
forumSchema['screen_name'] = "varchar(40) NOT NULL DEFAULT 'anon_screen_name_redacted'" # This or prev deleted based on anonymize yes/no
forumSchema['type'] = "varchar(20) NOT NULL"
forumSchema['anonymous'] = "varchar(10) NOT NULL"
forumSchema['anonymous_to_peers'] = "varchar(10) NOT NULL"
forumSchema['at_position_list'] = "varchar(200) NOT NULL"
forumSchema['forum_uid'] = "varchar(40) NOT NULL"
forumSchema['body'] = "TEXT NOT NULL" #"varchar(2500) NOT NULL"
forumSchema['course_display_name'] = "varchar(100) NOT NULL"
forumSchema['created_at'] = "datetime NOT NULL"
forumSchema['votes'] = "TEXT NOT NULL" # "varchar(200) NOT NULL"
forumSchema['count'] = "int(11) NOT NULL"
forumSchema['down_count'] = "int(11) NOT NULL"
forumSchema['up_count'] = "int(11) NOT NULL"
forumSchema['up'] = "varchar(200) DEFAULT NULL"
forumSchema['down'] = "varchar(200) DEFAULT NULL"
forumSchema['comment_thread_id'] = "varchar(255) DEFAULT NULL"
forumSchema['parent_id'] = "varchar(255) DEFAULT NULL"
forumSchema['parent_ids'] = "varchar(255) DEFAULT NULL"
forumSchema['sk'] = "varchar(255) DEFAULT NULL"
forumSchema['confusion'] = "varchar(20) NOT NULL DEFAULT ''"
forumSchema['happiness'] = "varchar(20) NOT NULL DEFAULT ''"
def __init__(self,
bsonFileName,
mysqlDbObj=None,
forumTableName='contents',
allUsersTableName='EdxPrivate.UserGrade',
anonymize=True,
allowAnonScreenName=False):
'''
Given a .bson file containing OpenEdX Forum entries, anonymize the entries (if desired),
and place them into a MySQL table.
:param bsonFileName: full path the .bson table. Set to None if instantiating
for unit testing.
:type bsonFileName: String
:param mysqlDbObj: a pymysql_utils.MySQLDB object where anonymized entries are
to be placed. If None, a new such object is created into MySQL db 'EdxForum'
:type mysqlDbObj: MySQLDB
:param forumTableName: name of table into which anonymized Forum entries are to be placed
:type forumTableName: String
:param allUsersTable: fully qualified name of table listing all in-the-clear mySQLUser names
of users who post to the Forum. Used to redact their names from their own posts.
:type allUsersTable: String
:param anonymize: If true, Forum post entries in the MySQL table will be anonymized
:type anonymize: bool
:param allow_anon_screen_name: if True, then occurrences of poster's name in
post bodies are replaced by <redacName_<anon_screen_name>>, where anon_screen_name
is the hash used in other tables of the OpenEdX data.
:type allow_anon_screen_name: Bool
'''
self.bsonFileName = bsonFileName
self.forumTableName = forumTableName
self.forumDbName = 'EdxForum'
self.allUsersTableName = allUsersTableName
self.anonymize = anonymize
self.allowAnonScreenName = allowAnonScreenName
# If not unittest, but regular run, then mysqlDbObj is None
if mysqlDbObj is None:
self.mysql_passwd = self.getMySQLPasswd()
self.mysql_dbhost ='localhost'
self.mysql_user = getpass.getuser() # mySQLUser that started this process
self.mydb = MySQLDB(user=self.mysql_user, passwd=self.mysql_passwd, db=self.forumDbName)
else:
self.mydb = mysqlDbObj
self.counter=0
self.userCache = {}
self.userSet = set()
warnings.filterwarnings('ignore', category=MySQLdb.Warning)
self.setupLogging()
self.prepDatabase()
#******mysqldb.commit();
#******logging.info('commit completed!')
def runConversion(self):
'''
Do the actual work. We don't call this method from __init__()
so that unittests can create an EdxForumScrubber instance without
doing the actual work. Instead, unittests call individual methods.
'''
self.populateUserCache();
self.mongo_database_name = 'TmpForum'
self.collection_name = 'contents'
# Load bson file into Mongodb:
self.loadForumIntoMongoDb(self.bsonFileName)
self.mongodb = MongoDB(dbName=self.mongo_database_name, collection=self.collection_name)
# Anonymize each forum record, and transfer to MySQL db:
self.forumMongoToRelational(self.mongodb, self.mydb,'contents' )
self.mydb.close()
self.mongodb.close()
self.logInfo('Entered %d records into %s' % (self.counter, self.forumDbName + '.' + self.forumTableName))
def loadForumIntoMongoDb(self, bsonFilename):
mongoclient = MongoClient();
db = mongoclient[self.mongo_database_name];
# Get collection object:
collection = db[self.collection_name];
# Clear out any old forum entries:
self.logInfo('Preparing to delete the collection ')
collection.remove()
self.logInfo('Deleting mongo collection completed. Will now attempt a mongo restore')
self.logInfo('Spawning subprocess to execute mongo restore')
with open(self.logFilePath,'w') as outfile:
ret = subprocess.call(
['mongorestore',
'--drop',
'--db', self.mongo_database_name,
'--collection', self.collection_name,
bsonFilename],
stdout=outfile, stderr=outfile)
self.logDebug('Return value from mongorestore is %s' % (ret))
objCount = subprocess.check_output(
['mongo',
'--quiet',
'--eval',
'printjson(db.contents.count())',
self.mongo_database_name,
],
stderr=outfile)
self.numMongoItems = objCount
self.logInfo('Available Forum posts %s' % objCount)
def forumMongoToRelational(self, mongodb, mysqlDbObj, mysqlTable):
'''
Given a pymongo collection object in which Forum posts are stored,
and a MySQL db object and table name, anonymize each mongo record,
and insert it into the MySQL table.
:param collection: collection object obtained via a mangoclient object
:type collection: Collection
:param mysqlDbObj: wrapper to MySQL db. See pymysql_utils.py
:type mysqlDbObj: MYSQLDB
:param mysqlTable: name of table where posts are to be deposited.
Example: 'contents'.
:type mysqlTable: String
'''
#command = 'mongorestore %s -db %s -mongoForumRec %s'%(self.bson_filename,self.mongo_database_name,self.collection_name)
#print command
self.logInfo('Will start inserting from mongo collection to MySQL')
for mongoForumRec in mongodb.query({}):
mongoRecordObj = MongoRecord(mongoForumRec)
try:
# Check whether 'up' can be converted to a list
list(mongoRecordObj['up'])
except Exception as e:
self.logInfo("Error in conversion of 'up' field to a list (setting cell to -1):" + `e`)
mongoRecordObj['up'] ='-1'
# Make sure the MongoDB object has all fields that will
# be needed for the forum schema:
self.ensureSchemaAdherence(mongoRecordObj)
self.insert_content_record(mysqlDbObj, mysqlTable, mongoRecordObj);
def prepDatabase(self):
'''
Declare variables and execute statements preparing the database to
configure options - e.g.: setting char set to utf, connection type to utf
truncating the already existing table.
'''
try:
self.logDebug("Setting and assigning char set for mysqld. will truncate old values")
self.mydb.execute('SET NAMES utf8;');
self.mydb.execute('SET CHARACTER SET utf8;');
self.mydb.execute('SET character_set_connection=utf8;');
# Compose fully qualified table name from the db name to
# which self.mydb is connected, and the forum table name
# that was established in __init__():
fullTblName = self.mydb.dbName() + '.' + self.forumTableName
# Clear old forum data out of the table:
try:
self.mydb.dropTable(fullTblName)
# Create MySQL table for the posts. If we are to
# anonymize, the poster name column will be 'screen_name',
# else it will be 'anon_screen_name':
self.createForumTable(self.anonymize)
self.logDebug("setting and assigning char set complete. Truncation succeeded")
except ValueError as e:
self.logDebug("Failed either to set character codes, or to create forum table %s: %s" % (fullTblName, `e`))
except MySQLdb.Error,e:
self.logInfo("MySql Error exiting %d: %s" % (e.args[0],e.args[1]))
# print e
sys.exit(1)
class EdxForumScrubber(object):
'''
Given a .bson file of OpenEdX Forum posts, load the file
into a MongoDB. Then pull a post at a time, anonymize, and
insert a selection of fields into a MySQL db. The MongoDb
entries look like this::
{
"_id" : ObjectId("51b75a48f359c40a00000028"),
"_type" : "Comment",
"abuse_flaggers" : [ ],
"anonymous" : false,
"anonymous_to_peers" : false,
"at_position_list" : [ ],
"author_id" : "26344",
"author_username" : "Minelly48",
"body" : "I am Gwen.I am a nursing professor who took statistics many years ago and want to refresh my knowledge.",
"comment_thread_id" : ObjectId("51b754e5f359c40a0000001d"),
"course_id" : "Medicine/HRP258/Statistics_in_Medicine",
"created_at" : ISODate("2013-06-11T17:11:36.831Z"),
"endorsed" : false,
"historical_abuse_flaggers" : [ ],
"parent_ids" : [ ],
"updated_at" : ISODate("2013-06-11T17:11:36.831Z"),
"visible" : true,
"votes" : {
"count" : 2,
"down" : [ ],
"down_count" : 0,
"point" : 2,
"up" : [
"40325",
"20323"
],
"up_count" : 2
},
"sk" : "51b75a48f359c40a00000028"
}
Depending on parameter allowAnonScreenName in the __init__() method,
forum entries in the relational database will be associated with the
same hash that is used to anonymize other parts of the OpenEdX data.
'''
LOG_DIR = '/home/dataman/Data/EdX/NonTransformLogs'
# Pattern for email id - strings of alphabets/numbers/dots/hyphens followed
# by an @ or at followed by combinations of dot/. followed by the edu/com
# also, allow for spaces
emailPattern='(.*)\s+([a-zA-Z0-9\(\.\-]+)[@]([a-zA-Z0-9\.]+)(.)(edu|com)\\s*(.*)'
#emailPattern='(.*)\\s+([a-zA-Z0-9\\.]+)\\s*(\\(f.*b.*)?(@)\\s*([a-zA-Z0-9\\.\\s;]+)\\s*(\\.)\\s*(edu|com)\\s+(.*)'
compiledEmailPattern = re.compile(emailPattern);
# Pattern for replacing embedded double quotes in post bodies,
# unless they are already escaped w/ a backslash. The
# {0,1} means a match if zero or one repetition. It's
# needed so that double quotes at the very start of a
# string are matched: no preceding character at all:
#doublQuoteReplPattern = re.compile(r'[^\\]{0,1}"')
doublQuoteReplPattern = re.compile(r'[\\]{0,}"')
def __init__(self,
bsonFileName,
mysqlDbObj=None,
forumTableName='contents',
allUsersTableName='EdxPrivate.UserGrade',
anonymize=True,
allowAnonScreenName=False):
'''
Given a .bson file containing OpenEdX Forum entries, anonymize the entries (if desired),
and place them into a MySQL table.
:param bsonFileName: full path the .bson table. Set to None if instantiating
for unit testing.
:type bsonFileName: String
:param mysqlDbObj: a pymysql_utils.MySQLDB object where anonymized entries are
to be placed. If None, a new such object is created into MySQL db 'EdxForum'
:type mysqlDbObj: MySQLDB
:param forumTableName: name of table into which anonymized Forum entries are to be placed
:type forumTableName: String
:param allUsersTable: fully qualified name of table listing all in-the-clear mySQLUser names
of users who post to the Forum. Used to redact their names from their own posts.
:type allUsersTable: String
:param anonymize: If true, Forum post entries in the MySQL table will be anonymized
:type anonymize: bool
:param allow_anon_screen_name: if True, then occurrences of poster's name in
post bodies are replaced by <redacName_<anon_screen_name>>, where anon_screen_name
is the hash used in other tables of the OpenEdX data.
:type allow_anon_screen_name: Bool
'''
self.bsonFileName = bsonFileName
self.forumTableName = forumTableName
self.forumDbName = 'EdxForum'
self.allUsersTableName = allUsersTableName
self.anonymize = anonymize
self.allowAnonScreenName = allowAnonScreenName
# If not unittest, but regular run, then mysqlDbObj is None
if mysqlDbObj is None:
self.mysql_passwd = self.getMySQLPasswd()
self.mysql_dbhost ='localhost'
self.mysql_user = getpass.getuser() # mySQLUser that started this process
self.mydb = MySQLDB(mySQLUser=self.mysql_user, passwd=self.mysql_passwd, db=self.forumDbName)
else:
self.mydb = mysqlDbObj
self.counter=0
self.userCache = {}
self.userSet = set()
warnings.filterwarnings('ignore', category=MySQLdb.Warning)
self.setupLogging()
self.prepDatabase()
#******mysqldb.commit();
#******logging.info('commit completed!')
def runConversion(self):
'''
Do the actual work. We don't call this method from __init__()
so that unittests can create an EdxForumScrubber instance without
doing the actual work. Instead, unittests call individual methods.
'''
self.populateUserCache();
self.mongo_database_name = 'TmpForum'
self.collection_name = 'contents'
# Load bson file into Mongodb:
self.loadForumIntoMongoDb(self.bsonFileName)
self.mongodb = MongoDB(dbName=self.mongo_database_name, collection=self.collection_name)
# Anonymize each forum record, and transfer to MySQL db:
self.forumMongoToRelational(self.mongodb, self.mydb,'contents' )
self.mydb.close()
self.mongodb.close()
self.logInfo('Entered %d records into %s' % (self.counter, self.forumDbName + self.forumTableName))
def loadForumIntoMongoDb(self, bsonFilename):
mongoclient = MongoClient();
db = mongoclient[self.mongo_database_name];
# Get collection object:
collection = db[self.collection_name];
# Clear out any old forum entries:
self.logInfo('Preparing to delete the collection ')
collection.remove()
self.logInfo('Deleting mongo collection completed. Will now attempt a mongo restore')
self.logInfo('Spawning subprocess to execute mongo restore')
with open(self.logFilePath,'w') as outfile:
ret = subprocess.call(
['mongorestore',
'--drop',
'--db', self.mongo_database_name,
'--collection', self.collection_name,
bsonFilename],
stdout=outfile, stderr=outfile)
self.logDebug('Return value from mongorestore is %s' % (ret))
objCount = subprocess.check_output(
['mongo',
'--quiet',
'--eval',
'printjson(db.contents.count())',
self.mongo_database_name,
],
stderr=outfile)
self.numMongoItems = objCount
self.logInfo('Available Forum posts %s' % objCount)
def forumMongoToRelational(self, mongodb, mysqlDbObj, mysqlTable):
'''
Given a pymongo collection object in which Forum posts are stored,
and a MySQL db object and table name, anonymize each mongo record,
and insert it into the MySQL table.
:param collection: collection object obtained via a mangoclient object
:type collection: Collection
:param mysqlDbObj: wrapper to MySQL db. See pymysql_utils.py
:type mysqlDbObj: MYSQLDB
:param mysqlTable: name of table where posts are to be deposited.
Example: 'contents'.
:type mysqlTable: String
'''
#command = 'mongorestore %s -db %s -mongoForumRec %s'%(self.bson_filename,self.mongo_database_name,self.collection_name)
#print command
self.logInfo('Will start inserting from mongo collection to MySQL')
for mongoForumRec in mongodb.query({}):
mongoRecordObj = MongoRecord(mongoForumRec)
try:
# Check whether 'up' can be converted to a list
list(mongoRecordObj['up'])
except Exception as e:
self.logInfo('Error in conversion' + `e`)
mongoRecordObj['up'] ='-1'
self.insert_content_record(mysqlDbObj, mysqlTable, mongoRecordObj);
def prepDatabase(self):
'''
Declare variables and execute statements preparing the database to
configure options - e.g.: setting char set to utf, connection type to utf
truncating the already existing table.
'''
try:
self.logDebug("Setting and assigning char set for mysqld. will truncate old values")
self.mydb.execute('SET NAMES utf8;');
self.mydb.execute('SET CHARACTER SET utf8;');
self.mydb.execute('SET character_set_connection=utf8;');
# Compose fully qualified table name from the db name to
# which self.mydb is connected, and the forum table name
# that was established in __init__():
fullTblName = self.mydb.dbName() + '.' + self.forumTableName
# Clear old forum data out of the table:
try:
self.mydb.dropTable(fullTblName)
# Create MySQL table for the posts. If we are to
# anonymize, the poster name column will be 'screen_name',
# else it will be 'anon_screen_name':
self.createForumTable(self.anonymize)
self.logDebug("setting and assigning char set complete. Truncation succeeded")
except ValueError as e:
self.logDebug("Failed either to set character codes, or to create forum table %s: %s" % (fullTblName, `e`))
except MySQLdb.Error,e:
self.logInfo("MySql Error exiting %d: %s" % (e.args[0],e.args[1]))
# print e
sys.exit(1)
class ExportClassTest(unittest.TestCase):
# Test data for one student in one class. Student is active in 2 of the
# class' weeks:
#
# Week 4:
# Session1: 15 total each week: Week4: 20
# Session2: 5 Week6: 72
#
# Week 6:
# Session3: 15
# Session4: 42
# Session5: 15
# ------------
# 92
#
# Sessions in weeks:
# week4: [20] ==> median = 20
# week6: [15,42,15] ==> median = 15
#
# The engagement summary file for one class:
# totalStudentSessions, totalEffortAllStudents, oneToTwentyMin, twentyoneToSixtyMin, greaterSixtyMin
# 5 92 2 0 0
#
# The all_data detail file resulting from the data:
# Platform,Course,Student,Date,Time,SessionLength
# 'OpenEdX,CME/MedStats/2013-2015,abc,2013-08-30,03:27:00,15
# 'OpenEdX,CME/MedStats/2013-2015,abc,2013-08-30,04:10:00,5
# 'OpenEdX,CME/MedStats/2013-2015,abc,2013-09-14,03:27:24,15
# 'OpenEdX,CME/MedStats/2013-2015,abc,2013-09-15,03:27:25,42
# 'OpenEdX,CME/MedStats/2013-2015,abc,2013-09-15,04:36:54,15
#
# The weekly effort file from the data:
# platform,course,student,week,effortMinutes
# 'OpenEdX,CME/MedStats/2013-2015,abc,4,20
# 'OpenEdX,CME/MedStats/2013-2015,abc,6,72
oneStudentTestData = [
("CME/MedStats/2013-2015", "abc", "page_close", "2013-08-30 03:27:00", 0), # week 4; start session
("CME/MedStats/2013-2015", "abc", "load_video", "2013-08-30 03:27:20", 1), # 20sec (gets rounded to 0min)
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-08-30 03:37:00", 0), # 9min:40sec (gets rounded to 10min)
# 0min + 10min + 5min = 15min
("CME/MedStats/2013-2015", "abc", "page_close", "2013-08-30 04:10:00", 0), # 5min
(
"CME/MedStats/2013-2015",
"abc",
"load_video",
"2013-09-14 03:27:24",
1,
), # week 6; 15min (for the single video)
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-09-15 03:27:25", 0),
("CME/MedStats/2013-2015", "abc", "page_close", "2013-09-15 03:30:35", 0), # 3min
("CME/MedStats/2013-2015", "abc", "load_video", "2013-09-15 03:59:00", 1), # 28min
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-09-15 04:05:00", 0), # 6min
# 3min + 28min + 6min + 5min = 42
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-09-15 04:36:54", 1), # 15
]
courseRuntimesData = [
("CME/MedStats/2013-2015", "2013-07-30 03:27:00", "2013-10-30 03:27:00"),
("My/RealCourse/2013-2015", "2013-09-01 03:27:00", "2013-10-30 03:27:00"),
]
userGradeData = [
(10, "CME/MedStats/2013-2015", "abc"),
(20, "My/RealCourse/Summer2014", "def"),
(30, "CME/MedStats/2013-2015", "def"),
]
demographicsData = [("abc", "f", 1988, "hs", "USA", "United States"), ("def", "m", 1990, "p", "FRG", "Germany")]
true_courseenrollmentData = [
(10, "CME/MedStats/2013-2015", "2013-08-30 03:27:00", "nomode"),
(30, "CME/MedStats/2013-2015", "2014-08-30 03:27:00", "yesmode"),
]
courseInfoData = [
("CME/MedStats/2013-2015", "medStats", 2014, "fall", 1, 0, "2014-08-01", "2014-09-01", "2014-11-31")
]
userCountryData = [("US", "USA", "abc", "United States"), ("DE", "DEU", "def", "Germany")]
twoStudentsOneClassTestData = [
("CME/MedStats/2013-2015", "abc", "page_close", "2013-08-30 03:27:00", 0),
("CME/MedStats/2013-2015", "abc", "load_video", "2013-08-30 03:27:20", 1),
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-08-30 03:37:00", 0),
("CME/MedStats/2013-2015", "abc", "page_close", "2013-08-30 04:10:00", 0),
("CME/MedStats/2013-2015", "def", "page_close", "2013-08-30 04:10:00", 1), # Second student
("CME/MedStats/2013-2015", "abc", "load_video", "2013-09-14 03:27:24", 1),
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-09-15 03:27:25", 0),
("CME/MedStats/2013-2015", "abc", "page_close", "2013-09-15 03:30:35", 0),
("CME/MedStats/2013-2015", "abc", "load_video", "2013-09-15 03:59:00", 1),
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-09-15 04:05:00", 0),
("CME/MedStats/2013-2015", "def", "page_close", "2013-09-16 04:10:00", 1), # Second student
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-09-15 04:36:54", 1),
]
twoStudentsTwoClassesTestData = [
("CME/MedStats/2013-2015", "abc", "page_close", "2013-08-30 03:27:00", 0),
("CME/MedStats/2013-2015", "abc", "load_video", "2013-08-30 03:27:20", 1),
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-08-30 03:37:00", 0),
("CME/MedStats/2013-2015", "abc", "page_close", "2013-08-30 04:10:00", 0),
("My/RealCourse/2013-2015", "def", "page_close", "2013-09-01 04:10:00", 1), # Second student
("CME/MedStats/2013-2015", "abc", "load_video", "2013-09-14 03:27:24", 1),
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-09-15 03:27:25", 0),
("CME/MedStats/2013-2015", "abc", "page_close", "2013-09-15 03:30:35", 0),
("CME/MedStats/2013-2015", "abc", "load_video", "2013-09-15 03:59:00", 1),
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-09-15 04:05:00", 0),
("My/RealCourse/2013-2015", "def", "page_close", "2013-09-16 04:10:00", 1), # Second student
("CME/MedStats/2013-2015", "abc", "seq_goto", "2013-09-15 04:36:54", 1),
]
def setUp(self):
application = None
request = None # HTTPRequest.HTTPRequest()
self.courseServer = CourseCSVServer(application, request, testing=True)
try:
self.mysqldb = MySQLDB(host="localhost", port=3306, user="******", db="unittest")
except ValueError as e:
self.fail(
str(e)
+ " (For unit testing, localhost MySQL server must have user 'unittest' without password, and a database called 'unittest')"
)
def tearDown(self):
try:
self.mysqldb.dropTable("unittest.Activities")
self.mysqldb.close()
except:
pass
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testOneStudentOneClass(self):
self.buildSupportTables(TestSet.ONE_STUDENT_ONE_CLASS)
jsonMsg = '{"req" : "getData", "args" : {"courseId" : "CME/MedStats/2013-2015", "engagementData" : "True", "wipeExisting" : "True", "inclPII" : "False", "cryptoPwd" : "foobar"}}'
self.courseServer.on_message(jsonMsg)
with open(self.courseServer.latestResultSummaryFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
# print(courseSummaryLine)
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,5,92,2,0,0\n", fd.readline())
with open(self.courseServer.latestResultDetailFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-08-30,03:27:00,15\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-08-30,04:10:00,5\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-09-14,03:27:24,15\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-09-15,03:27:25,42\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-09-15,04:36:54,15\n", fd.readline())
with open(self.courseServer.latestResultWeeklyEffortFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
# print(courseWeeklyLine)
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,5,20\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,7,72\n", fd.readline())
os.remove(self.courseServer.latestResultSummaryFilename)
os.remove(self.courseServer.latestResultDetailFilename)
os.remove(self.courseServer.latestResultWeeklyEffortFilename)
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testTwoStudentsOneClass(self):
self.buildSupportTables(TestSet.TWO_STUDENTS_ONE_CLASS)
jsonMsg = '{"req" : "getData", "args" : {"courseId" : "CME/MedStats/2013-2015", "engagementData" : "True", "wipeExisting" : "True", "inclPII" : "False", "cryptoPwd" : "foobar"}}'
self.courseServer.on_message(jsonMsg)
with open(self.courseServer.latestResultSummaryFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
# print(courseSummaryLine)
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,7,122,4,0,0\n", fd.readline())
with open(self.courseServer.latestResultDetailFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-08-30,03:27:00,15\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-08-30,04:10:00,5\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-09-14,03:27:24,15\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-09-15,03:27:25,42\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-09-15,04:36:54,15\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,def,2013-08-30,04:10:00,15\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,def,2013-09-16,04:10:00,15\n", fd.readline())
with open(self.courseServer.latestResultWeeklyEffortFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
# print(courseWeeklyLine)
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,5,20\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,7,72\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,def,5,15\n", fd.readline())
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,def,7,15\n", fd.readline())
os.remove(self.courseServer.latestResultSummaryFilename)
os.remove(self.courseServer.latestResultDetailFilename)
os.remove(self.courseServer.latestResultWeeklyEffortFilename)
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testTwoStudentsTwoClasses(self):
self.buildSupportTables(TestSet.TWO_STUDENTS_TWO_CLASSES)
jsonMsg = '{"req" : "getData", "args" : {"courseId" : "None", "engagementData" : "True", "wipeExisting" : "True", "inclPII" : "False", "cryptoPwd" : "foobar"}}'
self.courseServer.on_message(jsonMsg)
with open(self.courseServer.latestResultSummaryFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
# print(courseSummaryLine)
# Read the rest of the summary lines, and
# sort them just to ensure that we compare each
# line to its ground truth:
allSummaryLines = fd.readlines()
allSummaryLines.sort()
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,5,92,2,0,0\n", allSummaryLines[0])
self.assertEqual("OpenEdX,My/RealCourse/2013-2015,2,30,2,0,0\n", allSummaryLines[1])
with open(self.courseServer.latestResultDetailFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
allDetailLines = fd.readlines()
allDetailLines.sort()
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-08-30,03:27:00,15\n", allDetailLines[0])
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-08-30,04:10:00,5\n", allDetailLines[1])
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-09-14,03:27:24,15\n", allDetailLines[2])
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-09-15,03:27:25,42\n", allDetailLines[3])
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,2013-09-15,04:36:54,15\n", allDetailLines[4])
self.assertEqual("OpenEdX,My/RealCourse/2013-2015,def,2013-09-01,04:10:00,15\n", allDetailLines[5])
self.assertEqual("OpenEdX,My/RealCourse/2013-2015,def,2013-09-16,04:10:00,15\n", allDetailLines[6])
with open(self.courseServer.latestResultWeeklyEffortFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
# print(courseWeeklyLine)
allWeeklyLines = fd.readlines()
allWeeklyLines.sort()
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,5,20\n", allWeeklyLines[0])
self.assertEqual("OpenEdX,CME/MedStats/2013-2015,abc,7,72\n", allWeeklyLines[1])
self.assertEqual("OpenEdX,My/RealCourse/2013-2015,def,1,15\n", allWeeklyLines[2])
self.assertEqual("OpenEdX,My/RealCourse/2013-2015,def,3,15\n", allWeeklyLines[3])
os.remove(self.courseServer.latestResultSummaryFilename)
os.remove(self.courseServer.latestResultDetailFilename)
os.remove(self.courseServer.latestResultWeeklyEffortFilename)
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testForumIsolated(self):
self.buildSupportTables(TestSet.TWO_STUDENTS_ONE_CLASS)
jsonMsg = '{"req" : "getData", "args" : {"courseId" : "MITx/6.002x/2012_Fall", "forumData" : "True", "wipeExisting" : "True", "relatable" : "False", "cryptoPwd" : "foobar"}}'
self.courseServer.on_message(jsonMsg)
zipObj = zipfile.ZipFile(self.courseServer.latestForumFilename, "r")
forumFd = zipObj.open("MITx_6.002x_2012_Fall_Forum.csv", "r", "foobar")
forumExportHeader = (
"'forum_post_id','anon_screen_name','type','anonymous',"
+ "'anonymous_to_peers','at_position_list','forum_int_id','body',"
+ "'course_display_name','created_at','votes','count','down_count',"
+ "'up_count','up','down','comment_thread_id','parent_id','parent_ids',"
+ "'sk','confusion','happiness'\n"
)
forum1stLine = "\"519461545924670200000001\",\"<anon_screen_name_redacted>\",\"CommentThread\",\"False\",\"False\",\"[]\",11,\"First forum entry.\",\"MITx/6.002x/2012_Fall\",\"2013-05-16 04:32:20\",\"{'count': 10, 'point': -6, 'down_count': 8, 'up': ['2', '10'], 'down': ['1', '3', '4', '5', '6', '7', '8', '9'], 'up_count': 2}\",10,8,2,\"['2', '10']\",\"['1', '3', '4', '5', '6', '7', '8', '9']\",\"None\",\"None\",\"None\",\"None\",\"none\",\"none\""
forum2ndLine = "\"519461545924670200000005\",\"<anon_screen_name_redacted>\",\"Comment\",\"False\",\"False\",\"[]\",7,\"Second forum entry.\",\"MITx/6.002x/2012_Fall\",\"2013-05-16 04:32:20\",\"{'count': 10, 'point': 4, 'down_count': 3, 'up': ['1', '2', '5', '6', '7', '8', '9'], 'down': ['3', '4', '10'], 'up_count': 7}\",10,3,7,\"['1', '2', '5', '6', '7', '8', '9']\",\"['3', '4', '10']\",\"519461545924670200000001\",\"None\",\"[]\",\"519461545924670200000005\",\"none\",\"none\""
header = forumFd.readline()
self.assertEqual(forumExportHeader, header)
self.assertEqual(forum1stLine, forumFd.readline().strip())
self.assertEqual(forum2ndLine, forumFd.readline().strip())
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testForumRelatable(self):
self.buildSupportTables(TestSet.TWO_STUDENTS_ONE_CLASS)
jsonMsg = '{"req" : "getData", "args" : {"courseId" : "MITx/6.002x/2012_Fall", "forumData" : "True", "wipeExisting" : "True", "relatable" : "True", "cryptoPwd" : "foobar"}}'
self.courseServer.on_message(jsonMsg)
zipObj = zipfile.ZipFile(self.courseServer.latestForumFilename, "r")
forumFd = zipObj.open("MITx_6.002x_2012_Fall_Forum.csv", "r", "foobar")
forumExportHeader = (
"'forum_post_id','anon_screen_name','type','anonymous',"
+ "'anonymous_to_peers','at_position_list','forum_int_id','body',"
+ "'course_display_name','created_at','votes','count','down_count',"
+ "'up_count','up','down','comment_thread_id','parent_id','parent_ids',"
+ "'sk','confusion','happiness'\n"
)
forum1stLine = "\"519461545924670200000001\",\"e07a3da71f0330452a6aa650ed598e2911301491\",\"CommentThread\",\"False\",\"False\",\"[]\",0,\"First forum entry.\",\"MITx/6.002x/2012_Fall\",\"2013-05-16 04:32:20\",\"{'count': 10, 'point': -6, 'down_count': 8, 'up': ['2', '10'], 'down': ['1', '3', '4', '5', '6', '7', '8', '9'], 'up_count': 2}\",10,8,2,\"['2', '10']\",\"['1', '3', '4', '5', '6', '7', '8', '9']\",\"None\",\"None\",\"None\",\"None\",\"none\",\"none\""
forum2ndLine = "\"519461545924670200000005\",\"e07a3da71f0330452a6aa650ed598e2911301491\",\"Comment\",\"False\",\"False\",\"[]\",0,\"Second forum entry.\",\"MITx/6.002x/2012_Fall\",\"2013-05-16 04:32:20\",\"{'count': 10, 'point': 4, 'down_count': 3, 'up': ['1', '2', '5', '6', '7', '8', '9'], 'down': ['3', '4', '10'], 'up_count': 7}\",10,3,7,\"['1', '2', '5', '6', '7', '8', '9']\",\"['3', '4', '10']\",\"519461545924670200000001\",\"None\",\"[]\",\"519461545924670200000005\",\"none\",\"none\""
header = forumFd.readline()
self.assertEqual(forumExportHeader, header)
self.assertEqual(forum1stLine, forumFd.readline().strip())
self.assertEqual(forum2ndLine, forumFd.readline().strip())
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testForumIsolatedCourseNotInForum(self):
self.buildSupportTables(TestSet.TWO_STUDENTS_ONE_CLASS)
jsonMsg = '{"req" : "getData", "args" : {"courseId" : "Course/Not/Exists", "forumData" : "True", "wipeExisting" : "True", "inclPII" : "False", "cryptoPwd" : "foobar"}}'
self.courseServer.on_message(jsonMsg)
os.path.exists(self.courseServer.latestForumFilename)
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testDemographics(self):
self.buildSupportTables(TestSet.TWO_STUDENTS_ONE_CLASS)
jsonMsg = '{"req" : "getData", "args" : {"courseId" : "testtest/MedStats/2013-2015", "demographics" : "True", "wipeExisting" : "True", "relatable" : "False", "cryptoPwd" : "foobar"}}'
self.courseServer.on_message(jsonMsg)
# Allow result to be computed:
time.sleep(3)
with open(self.courseServer.latestDemographicsFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
# print(courseSummaryLine)
# Read the rest of the summary lines, and
# sort them just to ensure that we compare each
# line to its ground truth:
allDemographicsLines = fd.readlines()
allDemographicsLines.sort()
# abc,f,1988,hs,USA,United States
self.assertEqual('"abc","f","1988","hs","USA","United States"', allDemographicsLines[0].strip())
self.assertEqual('"def","m","1990","p","FRG","Germany"', allDemographicsLines[1].strip())
os.remove(self.courseServer.latestDemographicsFilename)
# ******@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testQuarterlyDemographics(self):
self.buildSupportTables(TestSet.TWO_STUDENTS_ONE_CLASS)
jsonMsg = '{"req" : "getData", "args" : {"courseId" : "testtest/MedStats/2013-2015", "quarterRep": "True", "quarterRepDemographics" : "True", "quarterRepQuarter" : "fall", "quarterRepYear": "2014", "wipeExisting" : "True", "relatable" : "False", "cryptoPwd" : "foobar"}}'
self.courseServer.on_message(jsonMsg)
# Allow result to be computed:
time.sleep(3)
with open(self.courseServer.latestQuarterlyDemographicsFilename, "r") as fd:
# Read and discard the csv file's header line:
fd.readline()
# print(courseSummaryLine)
# Read the rest of the summary lines, and
# sort them just to ensure that we compare each
# line to its ground truth:
allDemographicsLines = fd.readlines()
# allDemographicsLines.sort()
self.assertEqual(
"openedx,CME/MedStats/2013-2015,1,1,0,0,2,1,0,0,1,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0",
allDemographicsLines[0].strip(),
)
os.remove(self.courseServer.latestDemographicsFilename)
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testZipFiles(self):
file1 = tempfile.NamedTemporaryFile()
file2 = tempfile.NamedTemporaryFile()
file1.write("foo")
file2.write("bar")
file1.flush()
file2.flush()
self.courseServer.zipFiles("/tmp/zipFileUnittest.zip", "foobar", [file1.name, file2.name])
# Read it all back:
zipfile.ZipFile("/tmp/zipFileUnittest.zip").extractall(pwd="foobar")
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testExportPIIDetails(self):
pass
@unittest.skipIf(not TEST_ALL, "Temporarily disabled")
def testLearnerPerformance(self):
pass
def buildSupportTables(self, testSetToLoad):
# Activities table:
schema = OrderedDict(
[
("course_display_name", "varchar(255)"),
("anon_screen_name", "varchar(40)"),
("event_type", "varchar(120)"),
("time", "datetime"),
("isVideo", "TINYINT"),
]
)
self.mysqldb.dropTable("unittest.Activities")
self.mysqldb.createTable("unittest.Activities", schema)
colNames = ["course_display_name", "anon_screen_name", "event_type", "time", "isVideo"]
if testSetToLoad == TestSet.ONE_STUDENT_ONE_CLASS:
colValues = ExportClassTest.oneStudentTestData
elif testSetToLoad == TestSet.TWO_STUDENTS_ONE_CLASS:
colValues = ExportClassTest.twoStudentsOneClassTestData
elif testSetToLoad == TestSet.TWO_STUDENTS_TWO_CLASSES:
colValues = ExportClassTest.twoStudentsTwoClassesTestData
else:
raise ValueError("Requested test set unavailable: %s" % testSetToLoad)
self.mysqldb.bulkInsert("Activities", colNames, colValues)
# Course runtimes:
schema = OrderedDict(
[
("course_display_name", "varchar(255)"),
("course_start_date", "datetime"),
("course_end_date", "datetime"),
]
)
self.mysqldb.dropTable("unittest.CourseRuntimes")
self.mysqldb.createTable("unittest.CourseRuntimes", schema)
colNames = ["course_display_name", "course_start_date", "course_end_date"]
colValues = ExportClassTest.courseRuntimesData
self.mysqldb.bulkInsert("CourseRuntimes", colNames, colValues)
# UserGrade:
schema = OrderedDict(
[("user_int_id", "int"), ("course_id", "varchar(255)"), ("anon_screen_name", "varchar(40)")]
)
self.mysqldb.dropTable("unittest.UserGrade")
self.mysqldb.createTable("unittest.UserGrade", schema)
colNames = ["user_int_id", "course_id", "anon_screen_name"]
colValues = ExportClassTest.userGradeData
self.mysqldb.bulkInsert("UserGrade", colNames, colValues)
# true_courseenrollment
schema = OrderedDict(
[
("user_id", "int"),
("course_display_name", "varchar(255)"),
("created", "datetime"),
("mode", "varchar(10)"),
]
)
self.mysqldb.dropTable("unittest.true_courseenrollment")
self.mysqldb.createTable("unittest.true_courseenrollment", schema)
colNames = ["user_id", "course_display_name", "created", "mode"]
colValues = ExportClassTest.true_courseenrollmentData
self.mysqldb.bulkInsert("unittest.true_courseenrollment", colNames, colValues)
# UserCountry:
schema = OrderedDict(
[
("two_letter_country", "varchar(2)"),
("three_letter_country", "varchar(3)"),
("anon_screen_name", "varchar(40)"),
("country", "varchar(255)"),
]
)
self.mysqldb.dropTable("unittest.UserCountry")
self.mysqldb.createTable("unittest.UserCountry", schema)
colNames = ["two_letter_country", "three_letter_country", "anon_screen_name", "country"]
colValues = ExportClassTest.userCountryData
self.mysqldb.bulkInsert("unittest.UserCountry", colNames, colValues)
# Demographics
schema = OrderedDict(
[
("anon_screen_name", "varchar(40)"),
("gender", "varchar(255)"),
("year_of_birth", "int(11)"),
("level_of_education", "varchar(42)"),
("country_three_letters", "varchar(3)"),
("country_name", "varchar(255)"),
]
)
self.mysqldb.dropTable("unittest.Demographics")
self.mysqldb.execute("DROP VIEW IF EXISTS unittest.Demographics")
self.mysqldb.createTable("unittest.Demographics", schema)
colNames = [
"anon_screen_name",
"gender",
"year_of_birth",
"level_of_education",
"country_three_letters",
"country_name",
]
colValues = ExportClassTest.demographicsData
self.mysqldb.bulkInsert("unittest.Demographics", colNames, colValues)
# Quarterly Report Demographics:
# CourseInfo:
schema = OrderedDict(
[
("course_display_name", "varchar(255)"),
("course_catalog_name", "varchar(255)"),
("academic_year", "int"),
("quarter", "varchar(7)"),
("num_quarters", "int"),
("is_internal", "tinyint"),
("enrollment_start", "datetime"),
("start_date", "datetime"),
("end_date", "datetime"),
]
)
self.mysqldb.dropTable("unittest.CourseInfo")
self.mysqldb.createTable("unittest.CourseInfo", schema)
colNames = [
"course_display_name",
"course_catalog_name",
"academic_year",
"quarter",
"num_quarters",
"is_internal",
"enrollment_start",
"start_date",
"end_date",
]
colValues = ExportClassTest.courseInfoData
self.mysqldb.bulkInsert("unittest.CourseInfo", colNames, colValues)
# Forum table:
# This tables gets loaded via a .sql file imported into mysql.
# That file drops any existing unittest.contents, so we
# don't do that here:
mysqlCmdFile = "data/forumTests.sql"
mysqlLoadCmd = ["mysql", "-u", "unittest"]
with open(mysqlCmdFile, "r") as theStdin:
# Drop table unittest.contents, and load a fresh copy:
subprocess.call(mysqlLoadCmd, stdin=theStdin)
class ExtToAnonTableMaker(object):
def __init__(self, extIdsFileName):
user = '******'
# Try to find pwd in specified user's $HOME/.ssh/mysql
currUserHomeDir = os.getenv('HOME')
if currUserHomeDir is None:
pwd = None
else:
try:
# Need to access MySQL db as its 'root':
with open(os.path.join(currUserHomeDir,
'.ssh/mysql_root')) as fd:
pwd = fd.readline().strip()
# Switch user to 'root' b/c from now on it will need to be root:
user = '******'
except IOError:
# No .ssh subdir of user's home, or no mysql inside .ssh:
pwd = None
self.db = MySQLDB(user=user, passwd=pwd, db='Misc')
self.makeTmpExtsTable()
self.loadExtIds(extIdsFileName)
outfile = tempfile.NamedTemporaryFile(prefix='extsIntsScreenNames',
suffix='.csv',
delete=True)
# Need to close this file, and thereby delete it,
# so that MySQL is willing to write to it. Yes,
# that's a race condition. But this is an
# admin script, run by one person:
outfile.close()
self.findScreenNames(outfile.name)
self.computeAnonFromScreenNames(outfile.name)
def makeTmpExtsTable(self):
# Create table to load the CSV file into:
self.externalsTblNm = self.idGenerator(prefix='ExternalsTbl_')
mysqlCmd = 'CREATE TEMPORARY TABLE %s (ext_id varchar(32));' % self.externalsTblNm
self.db.execute(mysqlCmd)
def loadExtIds(self, csvExtsFileName):
# Clean up line endings in the extIds file.
# Between Win, MySQL, Mac, and R, we get
# linefeeds and crs:
cleanExtsFile = tempfile.NamedTemporaryFile(prefix='cleanExts',
suffix='.csv',
delete=False)
os.chmod(cleanExtsFile.name,
stat.S_IRUSR | stat.S_IRGRP | stat.S_IROTH)
rawExtsFd = open(csvExtsFileName, 'r')
for line in rawExtsFd:
cleanExtsFile.write(line.strip() + '\n')
cleanExtsFile.close()
rawExtsFd.close()
mysqlCmd = "LOAD DATA INFILE '%s' " % cleanExtsFile.name +\
'INTO TABLE %s ' % self.externalsTblNm +\
"FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
self.db.execute(mysqlCmd)
# Delete the cleaned-exts file:
os.remove(cleanExtsFile.name)
def findScreenNames(self, outCSVFileName):
mysqlCmd = "SELECT 'ext_id','user_int_id','screen_name'" +\
"UNION " +\
"SELECT ext_id," +\
" user_int_id," +\
" username " +\
" INTO OUTFILE '%s'" % outCSVFileName +\
" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'" +\
" FROM " +\
" (SELECT ext_id," +\
" user_id AS user_int_id " +\
" FROM %s LEFT JOIN edxprod.student_anonymoususerid " % self.externalsTblNm +\
" ON %s.ext_id = edxprod.student_anonymoususerid.anonymous_user_id " % self.externalsTblNm +\
" ) AS ExtAndInts " +\
" LEFT JOIN edxprod.auth_user " +\
" ON edxprod.auth_user.id = ExtAndInts.user_int_id;"
self.db.execute(mysqlCmd)
def computeAnonFromScreenNames(self, extIntNameFileName):
with open(extIntNameFileName, 'r') as inFd:
print('ext_id,anon_screen_name')
firstLineDiscarded = False
for line in inFd:
(extId, intId, screenName) = line.split(',') #@UnusedVariable
#********
#print('ScreenName.strip(\'"\'): \'%s\'' % screenName.strip().strip('"'))
#********
if firstLineDiscarded:
screenName = screenName.strip().strip('"')
if screenName == '\\N':
print('%s,%s' % (extId.strip('"'), 'NULL'))
else:
print('%s,%s' %
(extId.strip('"'),
EdXTrackLogJSONParser.makeHash(screenName)))
else:
firstLineDiscarded = True
def idGenerator(self,
prefix='',
size=6,
chars=string.ascii_uppercase + string.digits):
randPart = ''.join(random.choice(chars) for _ in range(size))
return prefix + randPart
