class DataBaseManager(object):
def __init__(self):
self.dbc = DataBaseConnection()
def add_user(self, user_info_dict):
user_id = user_info_dict['user_id']
user_name = user_info_dict['user_name']
user_password = user_info_dict['user_password']
sql = "insert into user_info values ('%s', '%s', '%s')" % (user_id, user_name, user_password)
return self.dbc.execute(sql)
def get_user(self, user_info_dict):
condition = ""
for key in user_info_dict.keys():
condition = condition + str(key) + "='" + str(user_info_dict[key]) + "' and "
condition = condition[0:len(condition)-4]
sql = "select * from user_info where " + condition
return self.dbc.select(sql)
def get_manager(self, user_info_dict):
condition = ""
for key in user_info_dict.keys():
condition = condition + str(key) + "='" + str(user_info_dict[key]) + "' and "
if len(condition) > 0:
condition = " where " + condition[0:len(condition)-4]
sql = "select * from manager_info " + condition
return self.dbc.select(sql)
def add_content(self, content_info_dict):
user_id = content_info_dict['user_id']
content_title = content_info_dict['content_title']
content_date = content_info_dict['content_date']
content = content_info_dict['content']
sql = "insert into CONTENT_INFO VALUES ('%s', '%s', '%s', '%s')" % (
user_id,
content_title,
content_date,
content
)
return self.dbc.execute(sql)
def get_content(self, content_info_dict):
user_id = content_info_dict['user_id']
content_title = content_info_dict['content_title']
content_date = content_info_dict['content_date']
sql = "select content from CONTENT_INFO WHERE USER_ID='%s' AND CONTENT_TITLE='%s' and CONTENT_DATE='%s'" % (
user_id,
content_title,
content_date
)
return self.dbc.select(sql)
def update_content(self, content_info_dict):
user_id = content_info_dict['user_id']
content_title = content_info_dict['content_title']
content_date = content_info_dict['content_date']
content = content_info_dict['content']
sql = "UPDATE CONTENT_INFO SET CONTENT='%s' WHERE USER_ID='%s' and CONTENT_TITLE='%s' and CONTENT_DATE='%s'" % (
content,
user_id,
content_title,
content_date
)
return self.dbc.execute(sql)
def get_content_titles(self, content_title_info_dict):
user_id = content_title_info_dict['user_id']
# first_index = content_title_info_dict['first_index']
# list_length = content_title_info_dict['list_length']
# sql = "SELECT CONTENT_TITLE FROM " \
# "(SELECT ci.*,ROWNUM ROWM FROM CONTENT_INFO ci WHERE USER_ID='%s' ORDER BY ROWNUM DESC) " \
# "WHERE %s<=ROWM AND ROWM<%s" % (user_id, first_index, first_index+list_length)
# if not first_index or not list_length:
sql = "SELECT *FROM CONTENT_INFO WHERE USER_ID='%s' ORDER BY CONTENT_DATE DESC" % user_id
return self.dbc.select(sql)
def get_user_content_number(self):
sql = "SELECT USER_INFO.USER_NAME,b.ucount,uuid " \
"FROM USER_INFO,(SELECT COUNT(CONTENT_INFO.USER_ID) ucount, CONTENT_INFO.USER_ID uuid " \
"from CONTENT_INFO GROUP BY CONTENT_INFO.USER_ID) b WHERE b.uuid = USER_INFO.USER_ID"
return self.dbc.select(sql)
def get_user_number(self):
sql = "SELECT COUNT(*) NUM FROM USER_INFO"
return self.dbc.select(sql)
def get_content_number(self):
sql = "SELECT COUNT(*) NUM FROM CONTENT_INFO"
return self.dbc.select(sql)
def delete_manager(self, manager_name):
sql = "DELETE FROM MANAGER_INFO WHERE MANAGER_NAME = '%s'" % manager_name
return self.dbc.execute(sql)
def add_manager(self, manager_info):
manager_id = manager_info['manager_id']
manager_name = manager_info['manager_name']
manager_password = manager_info['manager_password']
sql = "insert into MANAGER_INFO values ('%s', '%s', '%s')" % (manager_id, manager_name, manager_password)
return self.dbc.execute(sql)
# 开发者管理
def add_developer(self, developer_info):
developer_name = developer_info['developer_name']
developer_password = developer_info['developer_password']
sql = "insert into DEVELOPER_INFO VALUES ('%s', '%s')" % (developer_name, developer_password)
return self.dbc.execute(sql)
def get_developer(self, developer_info):
sql = "select * from DEVELOPER_INFO WHERE 1=1 "
condition = ""
if developer_info['developer_name']:
condition = " and DEVELOPER_NAME='%s'" % developer_info['developer_name']
sql += condition
return self.dbc.select(sql)
# cdk管理
def add_app(self, app_info):
developer_name = app_info['developer_name']
developer_cdk = app_info['developer_cdk']
developer_cdp = app_info['developer_cdp']
app_name = app_info['app_name']
sql = "insert into DEVELOPER_CDK VALUES ('%s', '%s', '%s', '%s')" % (developer_name, developer_cdk,
developer_cdp, app_name)
return self.dbc.execute(sql)
def get_app(self, app_info):
sql = "select * from DEVELOPER_CDK WHERE 1=1 "
condition = ""
if app_info['developer_name']:
condition = " and DEVELOPER_NAME='%s'" % app_info['developer_name']
sql += condition
return self.dbc.select(sql)
# 开发者所属的用户
def add_user_developer(self, user_info):
developer_cdk = user_info['developer_cdk']
user_name = user_info['user_name']
sql = "insert into DEVELOPER_USERS VALUES ('%s', '%s')" % (developer_cdk, user_name)
return self.dbc.execute(sql)
def get_user_developer(self, user_info):
sql = "select * from DEVELOPER_USERS WHERE 1=1 "
condition = ""
if user_info['developer_cdk']:
condition = " and DEVELOPER_CDK='%s'" % user_info['developer_cdk']
sql += condition
return self.dbc.execute(sql)
def get_developer_app_user(self, developer_info):
sql = "SELECT " \
"DEVELOPER_CDK.DEVELOPER_NAME, DEVELOPER_CDK.DEVELOPER_CDK, DEVELOPER_CDK.DEVELOPER_CDP, " \
"DEVELOPER_CDK.APP_NAME, count(DEVELOPER_USERS.USER_NAME) user_num " \
"FROM DEVELOPER_CDK " \
"LEFT JOIN DEVELOPER_USERS " \
"ON DEVELOPER_CDK.DEVELOPER_CDK=DEVELOPER_USERS.DEVELOPER_CDK " \
"WHERE DEVELOPER_CDK.DEVELOPER_NAME='%s' " \
"GROUP BY DEVELOPER_CDK.DEVELOPER_NAME,DEVELOPER_CDK.DEVELOPER_CDK,DEVELOPER_CDK.DEVELOPER_CDP," \
"DEVELOPER_CDK.APP_NAME" % developer_info['developer_name']
return self.dbc.select(sql)
# 获取指定的app的用户及其数量
def get_developer_app_info(self, developer_app_info):
sql = "SELECT users.user_name,count(CONTENT_INFO.CONTENT) num " \
"FROM " \
"(SELECT DEVELOPER_USERS.DEVELOPER_CDK cdk,USER_INFO.USER_NAME user_name,USER_INFO.USER_ID user_id " \
"FROM DEVELOPER_USERS,USER_INFO " \
"WHERE DEVELOPER_USERS.USER_NAME=USER_INFO.USER_NAME " \
"AND DEVELOPER_USERS.DEVELOPER_CDK='%s' ) users " \
"LEFT JOIN CONTENT_INFO ON users.user_id = CONTENT_INFO.USER_ID " \
"GROUP BY users.user_name" % developer_app_info['developer_cdk']
return self.dbc.select(sql)
# 获取所有开发者
def get_all_developer(self):
sql = "select DEVELOPER_NAME from DEVELOPER_INFO"
return self.dbc.select(sql)
# 将用户授权到某个应用
def user_auth_app(self, info):
sql = "INSERT INTO DEVELOPER_USERS VALUES ('%s', '%s')" % (info['developer_cdk'], info['user_name'])
return self.dbc.execute(sql)
