class ControladorDB:
"""
Implementación de Controlador de Usuario, correspondiente a una persistencia de DB.
El acceso a una DB se hace desde un controlador dbApi, quien hace de intermediaria para
tomar los datos y convertirlo en objetos.
"""
def __init__(self):
self.dbApi = DBApi()
self.keyUser = DEFAULT
self.keyState = 0
self.pattern = re.compile('^(\w+)$')
def login(self, jobt):
match = self.pattern.match(jobt['nick'])
if match == None:
return False
usr = self.dbApi.getUserByNick(jobt['nick'])
# TODO --Falta de implementación--
try:
if not usr:
return False
if usr.getPasswd() == jobt['passwd']:
return True
else:
return False
except:
return False
def setKeyUser(self, jobt):
if jobt:
self.keyUser = jobt['nick']
else:
self.keyUser = DEFAULT
self.keyState = 0
def getKeyUser(self):
return self.keyUser
def isKeyUser(self):
return not self.keyUser == DEFAULT
def sameUser(self, jobj):
return self.keyUser == jobj['nick']
def newUser(self, nick, passwd):
# TODO --Revisar--
return self.dbApi.newUser(nick, passwd)
def setKeyState(self, jobj):
if jobj['nick'] != self.keyUser:
return False
if not isinstance(jobj['estado'],
int) or not jobj['estado'] in state_range:
return False
else:
self.keyState = jobj['estado']
return True
def getKeyState(self):
return self.keyState
class ControladorDB:
"""
Implementación de Controlador de Usuario, correspondiente a una persistencia de DB.
El acceso a una DB se hace desde un controlador dbApi, quien hace de intermediaria para
tomar los datos y convertirlo en objetos.
"""
def __init__(self):
self.dbApi = DBApi()
self.keyUser = DEFAULT
self.keyState = 0
self.pattern = re.compile('^(\w+)$')
def login(self,jobt):
match = self.pattern.match(jobt['nick'])
if match == None:
return False
usr = self.dbApi.getUserByNick(jobt['nick'])
# TODO --Falta de implementación--
try:
if not usr:
return False
if usr.getPasswd() == jobt['passwd']:
return True
else:
return False
except:
return False
def setKeyUser(self,jobt):
if jobt:
self.keyUser = jobt['nick']
else :
self.keyUser = DEFAULT
self.keyState = 0
def getKeyUser(self):
return self.keyUser
def isKeyUser(self):
return not self.keyUser == DEFAULT
def sameUser(self,jobj):
return self.keyUser == jobj['nick']
def newUser(self,nick,passwd):
# TODO --Revisar--
return self.dbApi.newUser(nick,passwd)
def setKeyState(self,jobj):
if jobj['nick'] != self.keyUser:
return False
if not isinstance(jobj['estado'],int) or not jobj['estado'] in state_range:
return False
else :
self.keyState = jobj['estado']
return True
def getKeyState(self):
return self.keyState
def listAttributeNameOfTable(self, db, tablename):
attributes = []
str_sql = "DESC %s" % tablename
dbService = DBApi(db)
tableStructure = dbService.listDataBySQL(str_sql)
for sturcture in tableStructure:
attributes.append(sturcture[0])
#print(attributes)
return attributes
def listStructureByTablename(self, db, tablename):
'''
根据表名获取该表的表结构
:param db:数据库信息，dbtype,user/password@ip:port/databases
:param tablename:表名
:return:表结构列表
'''
str_sql = "DESC %s" % tablename
#str_sql = "select ordinal_position,colume_name,data_type from information_schema.'columns' where table_name ='%s' order by ordinal_position" % tablename
#str_sql = "SELECT ORDINAL_POSITION,COLUME_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.'COLUMNS' WHERE TABLE_NAME ='%S' ORDER BY ORDINAL_POSITION" % tablename
dbService = DBApi(db)
tableStructure = dbService.listDataBySQL(str_sql)
return tableStructure
def listTablesInDb(self, db):
'''
获取数据库用户表属性信息
:param db:dbtype,user/password@ip:port/databases
:return:表名列表
'''
tables = []
str_sql = "show tables"
dbService = DBApi(db)
list_table = dbService.listDataBySQL(str_sql)
for table in list_table:
tables.append(table[0])
return tables
def listStructureByTablenameAsDict(self, db, tablename):
'''
根据表名获取该表的表结构
:param db:数据库信息
:param tablename:表名
:return:表结构字典，{字段名：字段信息}
'''
tableStructureAsDict = {}
str_sql = "DESC %s" % tablename
dbService = DBApi(db)
tableStructure = dbService.listDataBySQL(str_sql)
for sturcture in tableStructure:
tableStructureAsDict[sturcture[0]] = sturcture
#print(tableStructureAsDict)
return tableStructureAsDict
def assertTabledataBetweenDbByAttribute(
self,
referdb="mysql,root/[email protected]:50016/pdw",
targetdb="mysql,root/[email protected]:50016/pdw",
tables="",
attribute="period",
attr_value="201705"):
'''
根据table 的 att 字段 获取数据，比较在referdb 与targetdb 中的数据是否一致
:param referdb:
:param targetdb:
:param attribute:
:param attr_value:
:return:
'''
# 验证表在数据库中是否存在
str_tables_ref = self.assertTablesInDb(referdb, tables)
if len(str_tables_ref) == 0:
print("referanceDb:%s has no tables:%s" % (referdb, tables))
return
str_tables_target = self.assertTablesInDb(targetdb, str_tables_ref)
if len(str_tables_target) == 0:
print("targetDb:%s has no tables:%s" %
(targetdb, str_tables_target))
return
#验证
list_table = str_tables_target.split(",")
bool_rs = True
if len(list_table) > 0:
for tableName in list_table:
str_sql = "SELECT * FROM %s t WHERE t.%s = '%s'" % (
tableName, attribute, attr_value)
referdbService = DBApi(referdb)
rs1 = referdbService.listDataBySQL(str_sql)
targetdbService = DBApi(targetdb)
rs2 = targetdbService.listDataBySQL(str_sql)
if len(rs1) != len(rs2):
print(
"*ERROR* Table %-34s %s:%s miss marched! excepted:%d rows, actural:%d rows"
%
(tableName, attribute, attr_value, len(rs1), len(rs2)))
bool_rs = False
else:
sum_rows = len(rs1)
for row in range(sum_rows):
if rs1[row] not in rs2:
print(
"*ERROR* Table %s %s:%s miss marched!\nLine %d miss data:%s\n"
% (tableName, attribute, attr_value, row,
rs1[row]))
bool_rs = False
if bool_rs:
print("*INFO* table %s-34s %s:%s rows:%-5d pass" %
(tableName, attribute, attr_value, sum_rows))
else:
print("*ERROR* Empty tables!")
bool_rs = False
if not bool_rs:
self.fail()
def assertTabledataBetweenDb(self, referdb, targetdb, tables):
'''
比较两个数据库中的所有数据
:param referdb: dbtype,user/password@ip:port/databases
:param targetdb: dbtype,user/password@ip:port/databases
:param tables: tablename or tablename1,tablename2,tablename3
:return:
示例：
db:"mysql,root/[email protected]:50016/pdw"
tables:"BUSI_REALIZABLE_ASSET, BUSI_HOUSE_DETAIL"
'''
print("hell")
msg = []
msg_single_tabledata = []
bool_db = True
# 验证需要验证的表在数据库中是否存在,表结构是否一致
marched_tables_str, msg_structure = self.assertTableStructureBetweenDb(
referdb, targetdb, tables)
if marched_tables_str != tables:
bool_db = False
msg.append(msg_structure)
#验证表数据
if len(marched_tables_str) > 0:
msg.append(
"*****************************data detail*********************************"
)
list_tables = marched_tables_str.split(",")
data_marched_tables = []
data_unmarched_tables = []
#断言数据
for tableName in list_tables:
msg_single_tabledata.append("\n*********%s*********" %
(tableName))
bool_table = True
print("tablename:", tableName)
str_sql = "SELECT * FROM %s" % tableName
referdbService = DBApi(referdb)
rs1 = referdbService.listDataBySQL(str_sql)
targetdbService = DBApi(targetdb)
rs2 = targetdbService.listDataBySQL(str_sql)
if len(rs1) != len(rs2):
# print("数据条数不一致！参照数据库：%d条，目标数据库：%d条" %(len(rs1),len(rs2)))
msg_single_tabledata.append(
"Data count not marched！referdb：%d，targetdb：%d" %
(len(rs1), len(rs2)))
bool_db = False
bool_table = False
#获取目标表中新增数据信息：
data_add = []
for row in range(0, len(rs2)):
if rs2[row] not in rs1:
data_add.append(rs2[row])
if len(data_add) > 0:
bool_db = False
bool_table = False
data_add_list = []
for data in data_add:
data_add_list.append(str(data))
# msg_single_tabledata.append("data added：")
# msg_single_tabledata.append("\n".join(data_add_list))
#获取目标表中减少的数据
data_del = []
for row in range(0, len(rs1)):
if rs1[row] not in rs2:
data_del.append(rs1[row])
if len(data_del) > 0:
bool_db = False
bool_table = False
data_del_list = []
for data in data_del:
data_del_list.append(str(data))
# msg_single_tabledata.append("data deleted：")
# msg_single_tabledata.append("\n".join(data_del_list))
#获取数据一致的表
if bool_table:
data_marched_tables.append(tableName)
else:
data_unmarched_tables.append(tableName)
if bool_db:
print("*INFO* DB data marched!")
else:
msg.append("DB data not marched")
if len(data_marched_tables) > 0:
msg.append("data marched tables：%s" %
(",".join(data_marched_tables)))
if len(data_unmarched_tables) > 0:
msg.append("data not marched tables：%s" %
(",".join(data_unmarched_tables)))
msg.append(
"***************************details**************************************"
)
# msg.append("\n".join(msg_single_tabledata))
print("*ERROR* %s" % ("\n".join(msg)))
else:
print("*ERROR* DB data not marched!")
# msg.append("两个数据库中表结构不一致！")
return "\n".join(msg)
def __init__(self):
self.dbApi = DBApi()
self.keyUser = DEFAULT
self.keyState = 0
self.pattern = re.compile('^(\w+)$')
def __init__(self, db_name):
self.user = None
self.userRole = None
self.functions = None
self.quitProgram = False
self.api = DBApi(db_name)
class Main():
def __init__(self, db_name):
self.user = None
self.userRole = None
self.functions = None
self.quitProgram = False
self.api = DBApi(db_name)
def setup(self):
"""
Load and intialize data and databases
"""
self.api.loadData()
def execute(self, userIn):
"""
Exectute a user instruction. Format should be COMMAND [ARGUEMENTS]
"""
args = userIn.split()[1:]
cmd = userIn.split()[0]
if not self.user:
raise Exception("User not logged in! Killing program")
elif cmd not in self.functions.keys():
print("Invalid command")
else:
# process input
try:
if len(args) > 0:
self.functions[cmd](args)
else:
self.functions[cmd]()
except Exception as e:
print(str(e))
def help(self):
"""
Print a list of commands for the avaliable user
"""
for cmd in HELP_COMMANDS:
if cmd in self.functions:
print("{} -> {}".format(cmd, HELP_COMMANDS[cmd]))
def login(self, uid, pwd):
"""
Log in a user
"""
user = self.api.getUser(uid, pwd)
if user:
print("Logging in...")
# assign user object and role
self.user = user
self.userRole = user[2]
# build set of avaible functions based on user roles
self.functions = {x: getattr(self, x[1:]) for x in ROLES[self.userRole]}
else:
raise Exception("Invalid user id or password!")
def logout(self):
"""
Log out current user
"""
print("Logging out...")
self.user = self.userRole = self.functions = None
def register(self, args):
"""
Register an object, either a marriage or a birth
"""
if args[0] == "-b" and len(args) == 10:
fname = args[1]
lname = args[2]
gender = args[3]
bdate = args[4]
bplace = args[5]
mFname = args[6]
mLname = args[7]
fFname = args[8]
fLname = args[9]
regno = randint(0, 999999)
regdate = str(dt.now())
regplace = self.user[5] # TODO: Make a user class so we dont have to directly access a tuple
mother = self.api.getPerson(mFname, mLname)
father = self.api.getPerson(fFname, fLname)
if not mother:
# get information about the mother from the user
print("No entry for mother. Please enter the following information")
# enter into persons db
self.api.createPerson(mFname, mLname)
if not father:
# get information about the mother from the user
print("No entry for father. Please enter the following information")
# enter into persons db
self.api.createPerson(fFname, fLname)
# enter new person into births and persons
if not self.api.getPerson(fname, lname):
# enter Mom's phone and address for the newborn
values = self.api.executeQuery(
"SELECT address, phone FROM persons WHERE fname = ? AND lname = ?",
(mFname, mLname)
)
self.api.executeQuery(
"""
INSERT INTO persons (fname, lname, bdate, bplace, address, phone)
VALUES (?,?,?,?,?,?)
""",
(fname, lname, bdate, bplace, values[0], values[1])
)
self.api.executeQuery(
"""
INSERT INTO births (regno, fname, lname, regdate, regplace, gender, f_fname, f_lname, m_fname,
m_lname)
VALUES (?,?,?,?,?,?,?,?,?,?)
""",
(
regno,
fname,
lname,
regdate,
regplace,
gender,
fFname,
fLname,
mFname,
mLname
)
)
print("added new birth to database")
elif args[0] == "-m" and len(args) == 5:
p1Fname = args[1]
p1Lname = args[2]
p2Fname = args[3]
p2Lname = args[4]
regno = randint(0, 999999)
regdate = dt.now()
regplace = self.user[5]
p1 = self.api.getPerson(p1Fname, p1Lname)
p2 = self.api.getPerson(p2Fname, p2Lname)
if not p1:
# get p1 from user
print("Cannot find partner 1")
self.api.getPerson(p1Fname, p1Lname)
if not p2:
# get p2 from user
print("Cannot find partner 2")
self.api.getPerson(p2Fname, p2Lname)
# enter marriage
self.api.executeQuery(
"""
INSERT INTO marriages (regdate, regplace, p1_fname, p1_lname, p2_fname, p2_lname)
VALUES (?,?,?,?,?,?)
""",
(
regdate,
regplace,
p1Fname,
p1Lname,
p2Fname,
p2Lname
)
)
print("added new marriage to database")
else:
raise Exception("Missing Argument(s)")
def renew(self, args):
"""
Renew an object
"""
if len(args) == 1:
regno = args[0]
# get the registration from the db
registration = self.api.executeQuery(
"SELECT * FROM registrations WHERE regno = ?",
(regno,)
)
if registration:
currentExpiry = dt.strptime(registration[2], "%Y-%m-%d") # TODO: Parse Correctly
newExpiry = currentExpiry + datetime.timedelta(days=(365))
if currentExpiry <= dt.now():
newExpiry = dt.now() + datetime.timedelta(days=(365))
self.api.executeQuery(
"UPDATE registrations SET expiry = ? WHERE regno = ?",
(newExpiry, regno),
True
)
print("updated registration")
else:
raise Exception("Missing Argument(s)")
def process(self, args):
"""
Process an object
"""
if args[0] == "-s":
# TODO: Finish error handling
vin = args[1]
currentOwnerFname = args[2]
currentOwnerLname = args[3]
newOwnerFname = args[4]
newOwnerLname = args[5]
plate = args[6]
# check if current owner matches most recent owner of the car
prevRegistraion = self.api.executeQuery(
"""
SELECT * FROM vehicles v, registrations r WHERE r.vin = v.vin
AND v.vin = ? AND r.fname = ? and r.lname = ?
AND r.expiry > ?
""",
(vin, currentOwnerFname, currentOwnerLname, dt.now())
)
if not prevRegistraion:
raise Exception("Transaction cannot be processed. Current owner does not match registered.")
# set the expiry date of the current registration
regnoOld = self.api.executeQuery(
"""
SELECT regno FROM registrations r, vehicles v WHERE WHERE r.vin = v.vin
AND r.fname = ? and r.lname = ?
""",
(currentOwnerFname, currentOwnerLname)
)
newExpiry = dt.now()
self.api.executeQuery(
"UPDATE registrations SET expiry = ? WHERE regno = ?",
(newExpiry, regnoOld),
True
)
# set the registration and expiry date of the new registration
regno = randint(0, 999999)
regdate = dt.now()
expiry = dt.now() + datetime.timedelta(days=365)
# create new registration in the db
self.api.executeQuery(
"""
INSERT INTO registrations (regno, regdate, expiry, plate, vin, fname, lname)
VALUES (?,?,?,?,?,?,?)
""",
(regno, regdate, expiry, plate, vin, newOwnerFname, newOwnerLname),
True
)
print("completed processing bill of sale")
# Process a payment
elif args[0] == "-p":
tno = args[1]
amount = args[2]
fine = args[3]
pdate = dt.now()
# calculate the amount paid for a given ticket
amountPaid = self.api.executeQuery(
"""
SELECT SUM(amount) FROM payments p, tickets t WHERE p.tno = t.tno
AND p.date < dt.now()
"""
)
# accept payment if amountPaid + payment Amount <= fine, otherwise raise error
totalAmount = amountPaid + amount
if fine >= totalAmount:
# Update fine amount for the ticket
fineRemaining = fine - totalAmount
self.api.executeQuery(
"UPDATE tickets SET fine = ? WHERE tno = ?",
(fineRemaining, tno),
True
)
# Insert amount into payments table
self.api.executeQuery(
"""
INSERT INTO payments (tno, pdate, amount)
VALUES (?,?,?)
""",
(tno, pdate, amount),
True
)
else:
raise Exception("Amount paid exceeds ticket fine")
else:
raise Exception("Missing Argument(s)")
def getAbstract(self, args):
# get the person's name from the user and validate it
personInDB = False
while not personInDB:
fname = input("Enter first name of the person: ").strip()
if fname == "":
return
lname = input("Enter last name of the person: ").strip()
if lname == "":
return
# get person
person = api.getPerson(fname, lname)
if person == None:
print("Invalid person entry")
def __init__(self):
self.dbApi = DBApi()
self.keyUser = DEFAULT
self.keyState = 0
self.pattern = re.compile('^(\w+)$')
