def test_equal(self):
testDb_mogo_test=database.databaseSchemaBuilder(self.connectionTestDB)['mogo_test']
autoDb_mogo_test=database.databaseSchemaBuilder(self.connectionAutoDB)['mogo_test']
self.assertFalse(testDb_mogo_test==autoDb_mogo_test)
self.assertFalse(testDb_mogo_test.tables['bill_paymentrecord']==autoDb_mogo_test.tables['bill_paymentrecord'])
self.assertTrue(testDb_mogo_test.tables['bill_loan']==autoDb_mogo_test.tables['bill_loan'])
self.assertTrue(testDb_mogo_test.tables['city']==autoDb_mogo_test.tables['city'])
def test_indexSchema(self):
cur=None
try:
cur=self.connectionAutoDB.cursor(buffered=True,dictionary=True)
cur.execute("SHOW INDEXES FROM `mogo_test`.`bill_salebill`")
expectedIndexes=cur.fetchall() # list
except Exception as e:
raise e
finally:
if cur:
cur.close()
db=database.databaseSchemaBuilder(self.connectionAutoDB)
actualIndexes=db['mogo_test'].tables['bill_salebill'].indexes # dict
for expectIndex in expectedIndexes:
actualIndex=actualIndexes[expectIndex['Key_name']]
self.assertEqual( actualIndex.name,expectIndex['Key_name'])
self.assertEqual(actualIndex.non_unique,bool(expectIndex['Non_unique']))
self.assertEqual(actualIndex.table_name,expectIndex['Table'])
if expectIndex['Key_name'].upper() == 'PRIMARY':
self.assertEqual(actualIndex.kind,'PRIMARY')
elif not bool(expectIndex['Non_unique']):
self.assertEqual(actualIndex.kind,'UNIQUE')
elif expectIndex['Index_type'] in ('FULLTEXT','SPATIAL'):
self.assertEqual(actualIndex.kind,expectIndex['Index_type'])
else:
self.assertEqual(actualIndex.kind,'INDEX')
self.assertEqual(expectIndex['Index_type'],actualIndex.type)
self.assertEqual(expectIndex['Collation'],actualIndex.collation)
self.assertEqual(expectIndex['Comment'],actualIndex.comment)
self.assertListEqual([(expectIndex['Column_name'],expectIndex['Sub_part'] or 0)],actualIndex.fields)
self.assertEqual('DROP PRIMARY KEY',actualIndexes['PRIMARY'].drop())
self.assertEqual('DROP INDEX `IDX_USERID`',actualIndexes['IDX_USERID'].drop())
self.assertEqual('ADD PRIMARY KEY (`id`) USING BTREE',actualIndexes['PRIMARY'].create())
self.assertEqual('ADD INDEX `IDX_USERID` (`userId`) USING BTREE',actualIndexes['IDX_USERID'].create())
def test_databaseSchema(self):
db=database.databaseSchemaBuilder(self.connectionAutoDB)
for k,v in db.items():
self.assertEqual(k,'mogo_test')
self.assertTrue(isinstance(v,database.DatabaseSchema))
self.assertEqual(v.use(),'USE `mogo_test`;')
self.assertEqual(v.create(),'CREATE DATABASE IF NOT EXISTS `mogo_test` CHARACTER SET latin1 COLLATE latin1_swedish_ci;')
self.assertEqual(v.alter(),'ALTER DATABASE `mogo_test`')
self.assertEqual(v.drop(),'DROP DATABASE `mogo_test`;')
def test_tabledata(self):
cur=None
try:
cur=self.connectionAutoDB.cursor(buffered=True,dictionary=True)
cur.execute("SELECT * FROM `mogo_test`.`mesg_template` ORDER BY id")
expecteddatas=cur.fetchall() # list
except Exception as e:
raise e
finally:
if cur:
cur.close()
tb=database.databaseSchemaBuilder(self.connectionAutoDB)['mogo_test'].tables['mesg_template']
actualdatas=tb.data()
for expecteddata in expecteddatas:
self.assertDictEqual(expecteddata,actualdatas[str(expecteddata.get('id'))].dixt)
self.assertIn('INSERT INTO `mesg_template` SET',actualdatas['5'].insert())
self.assertIn("id=5",actualdatas['5'].insert())
self.assertEqual('DELETE FROM `mesg_template` WHERE id=5;',actualdatas['5'].delete())
mesg_template_33331=database.databaseSchemaBuilder(self.connectionTestDB)['mogo_test'].tables['mesg_template'].data()['54']
self.assertIn("UPDATE `mesg_template` SET",mesg_template_33331.modify(actualdatas['54']))
self.assertIn("type='sms_renter_saleContractConvert_success'",mesg_template_33331.modify(actualdatas['54']))
self.assertIn("paramCount=5",mesg_template_33331.modify(actualdatas['54']))
self.assertIn("content='租客${1},同意了您${2}小区${3}栋${4}号Room${5}的补录租约。'",mesg_template_33331.modify(actualdatas['54']))
self.assertIn("WHERE id=54;",mesg_template_33331.modify(actualdatas['54']))
def test_tableSchema(self):
cur=None
try:
cur=self.connectionAutoDB.cursor(buffered=True,dictionary=True)
cur.execute("SELECT TABLE_NAME, ENGINE, ROW_FORMAT, AUTO_INCREMENT,CREATE_OPTIONS, TABLE_COLLATION, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA='mogo_test' AND not isnull(ENGINE)")
expectedTables=cur.fetchall()
except Exception as e:
raise e
finally:
if cur:
cur.close()
db=database.databaseSchemaBuilder(self.connectionAutoDB)
tb=db['mogo_test'].tables
for expectedTable in expectedTables:
actualTable=tb[expectedTable['TABLE_NAME']]
self.assertEqual(actualTable.options['ENGINE'],'ENGINE={}'.format(expectedTable['ENGINE']))
charset=None
if 'TABLE_COLLATION' in expectedTable.keys():
pos=expectedTable['TABLE_COLLATION'].find('_')
if not pos:
charset= expectedTable['TABLE_COLLATION']
else:
charset=expectedTable['TABLE_COLLATION'][:pos]
if charset:
self.assertEqual(actualTable.options['CHARSET'],'CHARSET={}'.format(charset))
else:
self.assertFalse('CHARSET' in actualTable.options.keys())
self.assertEqual(actualTable.options['COLLATE'],'COLLATE={}'.format(expectedTable['TABLE_COLLATION']))
self.assertEqual(actualTable.options['ROW_FORMAT'],'ROW_FORMAT={}'.format(expectedTable['ROW_FORMAT']))
if expectedTable['AUTO_INCREMENT']:
self.assertEqual(actualTable.options['AUTO_INCREMENT'],'AUTO_INCREMENT={}'.format(expectedTable['AUTO_INCREMENT']))
else:
self.assertFalse('AUTO_INCREMENT' in actualTable.options.keys())
if expectedTable['CREATE_OPTIONS']!='':
self.assertEqual(actualTable.options['CREATE_OPTIONS'],'CREATE_OPTIONS={}'.format(expectedTable['CREATE_OPTIONS']))
else:
self.assertFalse('CREATE_OPTIONS' in actualTable.options.keys())
if expectedTable['TABLE_COMMENT']!='':
self.assertEqual(actualTable.options['TABLE_COMMENT'],"COMMENT='{}'".format(expectedTable['TABLE_COMMENT']))
else:
self.assertFalse('TABLE_COMMENT' in actualTable.options.keys())
self.assertEqual(actualTable.name,expectedTable['TABLE_NAME'])
self.assertEqual(actualTable.alter(),"ALTER TABLE `{}`".format(expectedTable['TABLE_NAME']))
if actualTable.name=='opex_warn':
self.assertEqual(actualTable.create(),"CREATE TABLE `opex_warn` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '告警编号', `warnCode` varchar(15) DEFAULT NULL COMMENT '告警编码', `warnOrigin` varchar(255) DEFAULT NULL COMMENT '告警来源模块', `warnType` int(11) DEFAULT NULL COMMENT '告警类型', `warnTime` datetime DEFAULT NULL COMMENT '告警时间', `description` varchar(255) DEFAULT NULL COMMENT '描述', `isNotice` tinyint(1) DEFAULT '0' COMMENT '是否通知', `isProcess` tinyint(1) DEFAULT '0' COMMENT '是否处理', `createBy` int(11) DEFAULT NULL COMMENT '创建人', `createTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updateBy` int(11) DEFAULT NULL COMMENT '更新人', `updateTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `remark` varchar(255) DEFAULT NULL COMMENT '备注', `status` tinyint(1) DEFAULT '1' COMMENT '状态', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='告警表';")
self.assertEqual(actualTable.drop(),"DROP TABLE IF EXISTS `{}`;".format(expectedTable['TABLE_NAME']))
def test_columnSchema(self):
cur=None
try:
cur=self.connectionAutoDB.cursor(buffered=True,dictionary=True)
cur.execute("SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_KEY, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME, COLLATION_NAME, EXTRA, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='{}' AND TABLE_NAME='{}' ORDER BY ORDINAL_POSITION;".format('mogo_test','user_renter'))
expectedColumns=cur.fetchall()
except Exception as e:
raise e
finally:
if cur:
cur.close()
db=database.databaseSchemaBuilder(self.connectionAutoDB)
acutalColumns=db['mogo_test'].tables['user_renter'].columns
for expectedColumn in expectedColumns:
acutalColumn=acutalColumns[expectedColumn['COLUMN_NAME']]
self.assertEqual(acutalColumn.parent.name,expectedColumn['TABLE_NAME'])
self.assertEqual(acutalColumn.field,expectedColumn['COLUMN_NAME'])
self.assertEqual(acutalColumn.name,expectedColumn['COLUMN_NAME'])
self.assertEqual(acutalColumn.ordinal_position,expectedColumn['ORDINAL_POSITION'])
self.assertEqual(acutalColumn.type,expectedColumn['COLUMN_TYPE'])
self.assertEqual(acutalColumn.charset,expectedColumn['CHARACTER_SET_NAME'])
self.assertEqual(acutalColumn.collation,expectedColumn['COLLATION_NAME'])
self.assertEqual(acutalColumn.key,expectedColumn['COLUMN_KEY'])
self.assertEqual(acutalColumn.default,expectedColumn['COLUMN_DEFAULT'])
self.assertEqual(acutalColumn.extra,expectedColumn['EXTRA'])
self.assertEqual(acutalColumn.comment,expectedColumn['COLUMN_COMMENT'])
if expectedColumn['IS_NULLABLE']=='YES':
self.assertTrue(acutalColumn.null)
else:
self.assertFalse(acutalColumn.null)
self.assertEqual(acutalColumns['id'].define(after=None,with_comment=True),"`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键'")
self.assertEqual("`realName` varchar(64) NULL DEFAULT NULL COMMENT '真实姓名'",acutalColumns['realName'].define(after=None,with_comment=True))
self.assertEqual(acutalColumns['sex'].define(after=None,with_comment=False),"`sex` int(11) NULL DEFAULT NULL")
self.assertEqual(acutalColumns['birthday'].define(after='career',with_comment=True),"`birthday` datetime NULL DEFAULT NULL COMMENT '生日' AFTER `career`")
self.assertEqual(acutalColumns['birthday'].create(after='career',with_comment=True),"ADD COLUMN `birthday` datetime NULL DEFAULT NULL COMMENT '生日' AFTER `career`")
acutalColumns=db['mogo_test'].tables['user_verify_info'].columns
self.assertEqual("`last_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后验证时间'",acutalColumns['last_time'].define(after=None,with_comment=True))
self.assertEqual("`user_name` varchar(100) NULL DEFAULT NULL COMMENT '用户名' AFTER `id`",acutalColumns['user_name'].define(after='id',with_comment=True))
self.assertEqual(acutalColumns['user_name'].drop(),"DROP COLUMN `{}`".format('user_name'))
acutalColumns=db['mogo_test'].tables['user_useraccounttemp'].columns
self.assertEqual("`createTime` datetime NULL DEFAULT NULL COMMENT '创建时间'",acutalColumns['createTime'].define(after=None,with_comment=True))
self.assertEqual("`balance` double NULL DEFAULT '0' COMMENT '余额'",acutalColumns['balance'].define(after=None,with_comment=True))
self.assertEqual("`status` int(11) NULL DEFAULT '1' COMMENT '账户状态 1：有效；'",acutalColumns['status'].define(after=None,with_comment=True))
self.assertEqual("CHANGE `accountType` `accountType` int(11) NULL DEFAULT NULL COMMENT '账户类型' AFTER `accountNum`",acutalColumns['accountType'].change(after='accountNum',with_comment=True))
self.assertEqual("MODIFY COLUMN `accountType` int(11) NULL DEFAULT NULL COMMENT '账户类型' AFTER `accountNum`",acutalColumns['accountType'].modify(after='accountNum',with_comment=True))
def test_table_condition(self):
tb=database.databaseSchemaBuilder(self.connectionAutoDB)['mogo_test'].tables['comm_dictionary']
self.assertEqual("code='style-1' AND groupName='style' AND value='绿色乡村'",tb.condition(code=('style-1',1),groupName=('style',2),value=('绿色乡村',3)))
self.assertEqual("code='style-1'",tb.condition(code=('style-1',1)))
self.assertEqual("code='style-1' AND groupName='style'",tb.condition(code=('style-1',1),groupName=('style',2)))