def perm_take(self):
roleid = self.data.get('id')
permid = self.data.get('permid')
roleperm_id = self.data.get('roleperm_id')
where = None
if permid and roleid:
if isinstance(permid, (list, tuple)):
where = {
'permid': ('in', permid),
'roleid': self.data.get('id')
}
else:
where = {'permid': permid, 'roleid': self.data.get('id')}
elif roleperm_id:
if isinstance(roleperm_id, (list, tuple)):
where = {'id', ('in', roleperm_id)}
else:
where = {'id': roleperm_id}
else:
return ERR_PARAM, 'param error'
ret = None
with get_connection(self.dbname) as conn:
ret = conn.delete('role_perm', where)
return OK, {'rows': ret}
def get(self):
xid = self.data.get('id')
with get_connection(self.dbname) as conn:
ret = conn.select_one(self.table, where={'id':xid})
if ret:
self._convert_data(ret)
return OK, ret
def perm_give(self):
permid = self.data.get('permid')
values = []
if isinstance(permid, (list, tuple)):
for p in permid:
values.append({
'permid': p,
'roleid': self.data.get('id'),
'ctime': DBFunc('UNIX_TIMESTAMP(now())'),
'utime': DBFunc('UNIX_TIMESTAMP(now())')
})
else:
values.append({
'permid': permid,
'roleid': self.data.get('id'),
'ctime': DBFunc('UNIX_TIMESTAMP(now())'),
'utime': DBFunc('UNIX_TIMESTAMP(now())')
})
with get_connection(self.dbname) as conn:
for value in values:
value['id'] = createid.new_id64(conn=conn)
ret = conn.insert('role_perm', value)
return OK, {'rows': len(values)}
def get_data(self, page, pagesize, where=None):
retcode, retdata = BaseObjectHandler.get_data(self, page, pagesize,
where)
if retcode == OK and retdata and retdata['data']:
roleids = ','.join([str(x['id']) for x in retdata['data']])
sql = 'select p.id as id,p.name as name,p.info as info,rp.roleid as roleid from perms p, role_perm rp where rp.roleid in (%s) and rp.permid=p.id' % \
(roleids)
with get_connection(self.dbname) as conn:
ret = conn.query(sql)
if ret:
roledict = {}
for row in ret:
k = str(row['roleid'])
if k not in roledict:
roledict[k] = [row]
else:
roledict[k].append(row)
row.pop('roleid')
for row in retdata['data']:
row['perm'] = roledict.get(row['id'], [])
else:
for row in retdata['data']:
row['perm'] = []
return retcode, retdata
def group_join(self):
isadmin = self.ses['isadmin']
userid = self.ses['userid']
in_userid = self.data.get('userid')
if in_userid:
if not isadmin:
return ERR_PERM, 'permission deny'
else:
userid = in_userid
groupid = self.data.get('groupid')
t = int(time.time())
with get_connection(self.dbname) as conn:
data = {
'id': createid.new_id64(conn=conn),
'userid':userid,
'groupid':groupid,
'ctime':t,
'utime':t,
}
ret = conn.insert('user_group', data)
if ret != 1:
return ERR_DB, 'error'
ret = conn.select_one('user_group', where={'id':data['id']})
for k in ['id','userid','groupid']:
ret[k] = str(ret[k])
return OK, ret
def signup_3rd(self, appid, openid, userid=None):
try:
where = {
'appid':appid,
'openid':openid,
}
user_data = {
'password': '',
'ctime': int(time.time()),
'status': STATUS_OK, # 默认状态为2
}
lastid = -1
with get_connection(self.dbname) as conn:
ret = conn.select_one('openuser', where=where, fields='userid')
if ret:
return ERR_USER, 'user exist'
if userid:
ret = conn.select_one(self.table, where={'id':userid}, fields='id')
if not ret:
return ERR_USER, 'user error'
if not userid:
user_data['id'] = createid.new_id64(conn=conn)
user_data['username'] = '******' % (user_data['id'])
conn.insert(self.table, user_data)
openuser_data = {
'id': createid.new_id64(conn=conn),
'userid':user_data['id'],
'appid':appid,
'openid':openid,
'plat':config.OPENUSER_ACCOUNT[appid]['plat'],
'ctime':DBFunc('UNIX_TIMESTAMP(now())'),
}
conn.insert('openuser', openuser_data)
# 没有session才能创建新session
if not self.ses:
self.create_session()
log.debug('create sesssion:%s', self.ses.sid)
sesdata = {
'userid':int(user_data['id']),
'username':'',
'isadmin':0,
'status':user_data['status'],
'appid':appid,
'openid':openid,
'plat':config.OPENUSER_ACCOUNT[appid]['plat'],
}
self.ses.update(sesdata)
retcode, userinfo = self.get_user(user_data['id'])
return OK, userinfo
except Exception as e:
log.error(traceback.format_exc())
return self.fail(ERR, 'error:' + str(e))
def signin(self):
try:
username = self.data.get('username')
password = self.data.get('password')
email = self.data.get('email')
mobile = self.data.get('mobile')
if not username and not email and not mobile:
return ERR, 'username/email/mobile must have one'
if username:
login_key = 'username'
elif mobile:
login_key = 'mobile'
elif email:
login_key = 'email'
ret = None
with get_connection(self.dbname) as conn:
where = {
login_key: self.data.get(login_key)
}
#log.debug('where:%s', where)
ret = conn.select_one(self.table, where, "id,username,email,password,isadmin,status")
log.debug('select:%s', ret)
if not ret:
return ERR_USER, login_key + ' not found'
# password: sha1$123456$AJDKLJDLAKJKDLSJKLDJALASASASA
px = ret['password'].split('$')
pass_enc = create_password(password, int(px[1]))
if ret['password'] != pass_enc:
return ERR_AUTH, 'username or password error'
if ret['status'] != STATUS_OK:
return ERR_AUTH, "status error"
conn.update(self.table, {'logtime':int(time.time())}, where={'id':ret['id']})
retcode, userinfo = self.get_user(ret['id'])
log.debug('get user: %d %s', retcode, userinfo)
sesdata = {
'userid':int(ret['id']),
'username':ret['username'],
'isadmin':ret['isadmin'],
'status': userinfo['status'],
'allperm':[ x['name'] for x in userinfo['allperm']],
}
self.ses.update(sesdata)
return OK, userinfo
except Exception as e:
log.error(traceback.format_exc())
return self.fail(ERR, 'Exception:' + str(e))
def signup(self):
log.info('data:%s', self.data)
email = self.data.get('email','')
mobile = self.data.get('mobile','')
username = self.data.get('username','')
password = self.data.get('password','')
pass_enc = create_password(password)
try:
where = {}
insertdata = {
'password': pass_enc,
'ctime': int(time.time()),
'status': STATUS_OK, # 默认状态为2
}
if email:
where['email'] = email
insertdata['email'] = email
if mobile:
where['mobile'] = mobile
insertdata['mobile'] = mobile
if username:
where['username'] = username
insertdata['username'] = username
if not email and not mobile:
return ERR_PARAM, 'email/mobile must not null'
lastid = -1
with get_connection(self.dbname) as conn:
ret = conn.select(self.table, where, 'id')
if len(ret) >= 1:
return ERR_USER, 'username or email or mobile exist'
insertdata['id'] = createid.new_id64(conn=conn)
conn.insert(self.table, insertdata)
# 没有session才能创建新session
if not self.ses:
self.create_session()
log.debug('create sesssion:%s', self.ses.sid)
sesdata = {
'userid':int(insertdata['id']),
'username':username,
'isadmin':0,
'status':insertdata['status'],
}
self.ses.update(sesdata)
retcode, userinfo = self.get_user(insertdata['id'])
return OK, userinfo
except Exception as e:
log.error(traceback.format_exc())
return self.fail(ERR, 'error:' + str(e))
def perm_give(self):
isadmin = self.ses.get('isadmin', 0)
userid = self.ses['userid']
in_userid = self.data.get('userid')
if in_userid:
if not isadmin:
return ERR_PERM, 'permission deny'
else:
userid = in_userid
roleid = self.data.get('roleid')
permid = self.data.get('permid')
items = []
for k in ['roleid', 'permid']:
v = self.data.get(k)
if not v:
continue
log.debug('%s: %s', k, v)
if isinstance(v, (list,tuple)):
for one in v:
data = {'roleid':0, 'permid':0}
data[k] = one
items.append(data)
else:
data = {'roleid':0, 'permid':0}
data[k] = v
items.append(data)
t = int(time.time())
with get_connection(self.dbname) as conn:
ids = []
for item in items:
data = {
'id': createid.new_id64(conn=conn),
'userid':userid,
'roleid':item['roleid'],
'permid':item['permid'],
'ctime':t,
'utime':t,
}
ids.append(data['id'])
ret = conn.insert('user_perm', data)
if ret != 1:
return ERR_DB, 'error'
ret = conn.select('user_perm', where={'id':('in', ids)})
for row in ret:
for k in ['id','userid','roleid','permid']:
row[k] = str(row[k])
return OK, ret
def create(self):
data = {
'id': self.userid,
'mobile': self.mobile,
'username': '******' % self.userid,
'password': createpass.create_password('123456'),
'ctime': dbpool.DBFunc('FROM_UNIXTIME(now())'),
}
with dbpool.get_connection('usercenter') as conn:
ret = conn.insert('users', data)
return ret
def get_last(self, table):
where = {}
if table == 'users':
where['id'] = self.userid
else:
where['userid'] = self.userid
with dbpool.get_connection('usercenter') as conn:
ret = conn.select_one(table,
where=where,
other=' order by id desc limit 1')
return ret
def settings(self):
retdata = {}
with get_connection(self.dbname) as conn:
ret = conn.select('settings')
if not ret:
retdata = {}
else:
for row in ret:
retdata[row['name']] = row['value']
return retdata
def get_data(self, page, pagesize, where=None):
log.debug('list page:%s pagesize:%s', str(page), str(pagesize))
retdata = {'page':page, 'pagesize':pagesize, 'pagenum':0}
with get_connection(self.dbname) as conn:
sql = conn.select_sql(self.table, where=where)
ret = conn.select_page(sql, page, pagesize)
if ret.pagedata.data:
self._convert_data(ret.pagedata.data)
retdata['pagenum'] = ret.pages
retdata['data'] = ret.pagedata.data
return OK, retdata
def group_quit(self):
isadmin = self.ses.get('isadmin', 0)
groupid = self.data.get('groupid')
#userid = self.ses['userid']
where = {
'groupid':groupid,
}
if not isadmin:
where['userid'] = self.ses['useid']
with get_connection(self.dbname) as conn:
ret = conn.delete('user_group', where=where)
return OK, ret
def signin_3rd(self):
try:
appid = self.data.get('appid')
code = self.data.get('code')
openid = self.data.get('openid')
if not openid:
openid = utils.get_openid(code, appid)
if not openid:
return ERR_AUTH, 'openid error'
ret = None
with get_connection(self.dbname) as conn:
where = {
'appid': appid,
'openid': openid,
}
ret = conn.select_one('openuser', where, 'userid')
if not ret:
return ERR_AUTH, 'apppid/openid error'
userid = ret['userid']
#log.debug('where:%s', where)
ret = conn.select_one(self.table, {'id':userid}, "id,username,email,password,isadmin,status")
log.debug('select:%s', ret)
if not ret:
return ERR_USER, ' appid/openid not found'
conn.update(self.table, {'logtime':int(time.time())}, where={'id':userid})
retcode, userinfo = self.get_user(ret['id'])
sesdata = {
'userid':int(ret['id']),
'username':ret['username'],
'isadmin':ret['isadmin'],
'status': userinfo['status'],
'allperm':[ x['name'] for x in userinfo['allperm']],
'appid':appid,
'openid':openid,
'plat':config.OPENUSER_ACCOUNT[appid]['plat'],
}
self.ses.update(sesdata)
#self.succ({'id':str(ret['id']), 'username':ret['username']})
return OK, userinfo
except Exception as e:
log.error(traceback.format_exc())
return self.fail(ERR, 'Exception:' + str(e))
def get(self, xid):
sql = 'select p.id as id,p.name as name,p.info as info from perms p, role_perm rp where rp.roleid=%d and rp.permid=p.id' % (
int(xid))
with get_connection(self.dbname) as conn:
ret = conn.select_one(self.table, where={'id': xid})
if ret:
self._convert_data(ret)
ret['perm'] = []
perms = conn.query(sql)
if perms:
ret['perm'] = perms
return OK, ret
def load(self):
if self._dbname:
return
with dbpool.get_connection('dbmeta') as conn:
ret = conn.select('logic_db')
if ret:
for row in ret:
log.debug(row['policy'])
self._dbinfo[row['name']] = json.loads(row['policy'])
# 真实数据库名和该库的配置KEY的映射
for name,pool in dbpool.dbpool.items():
log.debug('%s => %s', pool.dbcf['db'], name)
self._dbname[pool.dbcf['db']] = name
def clear(self):
ret = self.user_db()
if self.appid:
sqls = ["delete from openuser where appid='%s'" % (self.appid)]
else:
sqls = [
"delete from users where id=%d" % (self.userid),
"delete from login_record where userid=%d" % (self.userid),
"delete from openuser where userid=%d" % (self.userid),
"delete from user_group where userid=%d" % (self.userid),
"delete from user_perm where userid=%d" % (self.userid),
]
with dbpool.get_connection('usercenter') as conn:
for x in sqls:
conn.execute(x)
def create(self):
data = self.validator.data
t = int(time.time())
for k in ['ctime']:
if k not in data:
data[k] = t
with get_connection(self.dbname) as conn:
if not data.get('id'):
data['id'] = createid.new_id64(conn=conn)
ret = conn.insert(self.table, data)
if ret == 1:
return OK, {'id':str(data['id'])}
else:
return ERR_DATA, 'insert error'
def modify(self):
# modify username/status/password/extend
userid_in = self.data.get('id', 0)
isadmin = self.ses.get('isadmin', 0)
userid = self.ses.get('userid', 0)
if userid_in and userid != userid_in:
if not isadmin:
return ERR_PERM, 'permission deny'
else:
userid = userid_in
values = {}
where = {'id':userid}
for k in ['username', 'password', 'extend', 'status', 'mobile']:
v = self.data.get(k)
if k == 'password' and v:
values['password'] = create_password(v)
elif k == 'extend' and v:
x = json.loads(v)
values['extend'] = v
elif v:
values[k] = v
log.debug('update values:%s', values)
if not values:
log.info('no modify info')
return ERR_PARAM, 'not modify'
values['utime'] = DBFunc("UNIX_TIMESTAMP(now())")
with get_connection(self.dbname) as conn:
ret = conn.update(self.table, values, where)
if ret != 1:
return ERR_PARAM, 'condition error'
ret = conn.select_one(self.table, where={'id':userid},
fields="id,username,mobile,status,ctime,utime")
return OK, ret
def modify(self):
data = copy.copy(self.data)
log.debug('data:%s', self.data)
if not data:
return ERR_PARAM, 'param error'
xid = data.pop('id')
if isinstance(xid, (list, tuple)):
xid = ('in', xid)
if not data:
return ERR_PARAM, 'no data'
with get_connection(self.dbname) as conn:
ret = conn.update(self.table, data, where={'id':xid})
if ret >= 1:
data['_rows'] = ret
if not isinstance(xid, (list, tuple)):
data['id'] = str(xid)
return OK, data
else:
return ERR_DATA, 'update error'
def perm_take(self):
isadmin = self.ses.get('isadmin', 0)
userid = self.data.get('userid', 0)
#userid = self.ses.get('userid', 0)
where = {}
if isadmin and userid:
where['userid'] = userid
else:
where['userid'] = self.ses.get('userid', 0)
for k in ['roleid','permid']:
v = self.data.get(k)
if v:
if isinstance(v, (list,tuple)):
where[k] = ('in', v)
else:
where[k] = v
with get_connection(self.dbname) as conn:
ret = conn.delete('user_perm', where=where)
return OK, {}
def test():
import pprint, copy
DB = {
'engine':'pymysql', # db type, eg: mysql, sqlite
'db':'test', # db name
'host':'127.0.0.1', # db host
'port':3306, # db port
'user':'******', # db user
'passwd':'123456', # db password
'charset':'utf8', # db charset
'conn':3,
}
DB1 = copy.copy(DB)
DB1['db'] = 'test1'
DB2 = copy.copy(DB)
DB2['db'] = 'test2'
DATABASE = {
'dbmeta': {
'engine':'pymysql',
'db':'dbmeta',
'host':'127.0.0.1',
'port':3306,
'user':'******',
'passwd':'123456',
'charset':'utf8',
'conn':3,
},
'test':DB,
'test-1': DB1,
'test-2': DB2,
}
dbpool.install(DATABASE)
install()
sqls = {
'test':[
"DROP TABLE testme;",
"CREATE TABLE IF NOT EXISTS testme(" \
"id int(4) not null primary key auto_increment, " \
"name varchar(128), ctime int(4));",
"CREATE TABLE IF NOT EXISTS record_201908(" \
"id int(4) not null primary key auto_increment, " \
"amt int(4) not null default 0);",
"CREATE TABLE IF NOT EXISTS record_201907(" \
"id int(4) not null primary key auto_increment, " \
"amt int(4) not null default 0);",
"CREATE TABLE IF NOT EXISTS record_201906(" \
"id int(4) not null primary key auto_increment, " \
"amt int(4) not null default 0);",
],
'test-1':[
"DROP TABLE testme1;",
"CREATE TABLE IF NOT EXISTS testme1(" \
"id int(4) not null primary key auto_increment, " \
"name varchar(128), ctime int(4));",
"CREATE TABLE IF NOT EXISTS record_201905(" \
"id int(4) not null primary key auto_increment, " \
"amt int(4) not null default 0);",
"CREATE TABLE IF NOT EXISTS record_201904(" \
"id int(4) not null primary key auto_increment, " \
"amt int(4) not null default 0);",
"CREATE TABLE IF NOT EXISTS record_201903(" \
"id int(4) not null primary key auto_increment, " \
"amt int(4) not null default 0);",
"CREATE TABLE IF NOT EXISTS record_201902(" \
"id int(4) not null primary key auto_increment, " \
"amt int(4) not null default 0);",
],
'test-2':[
"DROP TABLE testme2;",
"CREATE TABLE IF NOT EXISTS testme2(" \
"id int(4) not null primary key auto_increment, " \
"name varchar(128), ctime int(4));",
"CREATE TABLE IF NOT EXISTS record_201901(" \
"id int(4) not null primary key auto_increment, " \
"amt int(4) not null default 0);",
],
}
dbnames = ['test', 'test-1', 'test-2']
for dbn in dbnames:
with dbpool.get_connection(dbn) as conn:
for s in sqls[dbn]:
print(s)
try:
conn.execute(s)
except Exception as e:
if e.args[0] == 1051:
continue
raise
tables = ['testme','testme1','testme2']
with get_connection('test') as conn:
for i in range(1, 4):
t = random.choice(tables)
conn.insert(t, {'id':i, 'name':'haha'+str(i), 'ctime':int(time.time())+i})
for t in tables:
ret = conn.query('select * from %s' % t)
print(pprint.pformat(ret))
print(conn.select('record_201908'))
print(conn.select('record_201907'))
print(conn.select('record_201906'))
print(conn.select('record_201905'))
try:
log.debug("try trans")
conn.start()
except ShardingError as e:
log.debug(e)
with get_connection('test') as conn:
print(conn.select("aaa"))
print(conn.select("bbb"))
print(conn.select("ccc"))
log.debug('close, wait')
time.sleep(10)
def clear(self):
with dbpool.get_connection('usercenter') as conn:
for x in self.delete_sqls:
conn.execute(x)
def get_user(self, userid):
userid = int(userid)
where = {'id':userid}
user = None
groups = None
fields ='id,username,password,email,mobile,head,score,stage,FROM_UNIXTIME(ctime) as ctime,' \
'FROM_UNIXTIME(utime) as utime,FROM_UNIXTIME(logtime) as logtime,regip,status,isadmin,extend'
with get_connection(self.dbname) as conn:
user = conn.select_one(self.table, where, fields=fields)
if not user:
return ERR_USER, 'not have user info'
groups = conn.query('select g.id as id,g.name as name from user_group ug, groups g where g.id=ug.groupid and ug.userid=%d' % userid)
user['group'] = groups
user['role'] = []
user['perm'] = []
user['allperm'] = []
userperm = conn.query('select permid,roleid from user_perm where userid=%d' % userid)
if userperm:
roles = [ x['roleid'] for x in userperm if x['roleid']>0 ]
perms = [ x['permid'] for x in userperm if x['permid']>0 ]
if roles:
ret = conn.select('roles', where={'id':('in', roles)}, fields='id,name')
if ret:
user['role'] = ret
if perms:
ret = conn.select('perms', where={'id':('in', perms)}, fields='id,name')
if ret:
user['perm'] = ret
user['allperm'] = ret
if roles:
ret = conn.query('select p.id as id,p.name as name from perms p, role_perm rp where rp.roleid in (%s) and rp.permid=p.id' % \
(conn.exp2sql('rp.roleid', 'in', roles)))
if ret:
xids = [x['id'] for x in user['allperm']]
for row in ret:
if row['id'] not in xids:
user['allperm'].append(row)
#allperm = [ x['name'] for x in user['allperm'] ]
#self.ses['allperm'] = allperm
for k in ['password', 'regip', 'isadmin']:
user.pop(k)
user['id'] = str(user['id'])
if user['extend']:
user['extend'] = json.loads(user['extend'])
for k in ['group','role','perm','allperm']:
convert_data(user[k])
return OK, user
def user_db(self):
with dbpool.get_connection('usercenter') as conn:
ret = conn.select_one('users', where={'mobile': self.mobile})
if ret:
self.userid = ret['id']
return ret
def get_user_list(self):
if not self.ses.get('isadmin', 0):
return ERR_PERM, 'permission deny'
data = self.data
pagecur = int(data.get('page', 1))
pagesize = int(data.get('pagesize', 20))
page = None
log.debug('data:%s', data)
where = {}
username = data.get('username')
if username:
where['username'] = username
mobile = data.get('mobile')
if mobile:
where['mobile'] = mobile
ctime = data.get('ctime')
if ctime:
where['ctime'] = (
'between',
(DBFunc('UNIX_TIMESTAMP("%s")' % ctime[0]),
DBFunc('UNIX_TIMESTAMP("%s")' % ctime[1]))
)
groups = {}
roles = {}
perms = {}
with get_connection(self.dbname) as conn:
sql = conn.select_sql(self.table, where=where)
page = conn.select_page(sql, pagecur=pagecur, pagesize=pagesize)
# 获取组
useridstr = ','.join([ str(x['id']) for x in page.pagedata.data])
sql = "select ug.userid as userid, ug.groupid as groupid, g.name as name,g.info as info from user_group as ug, groups as g " \
"where ug.userid in(%s) and ug.groupid=g.id" % (useridstr)
ret = conn.query(sql)
log.debug('groups:%s', ret)
for row in ret:
uid = row['userid']
items = groups.get(uid)
if not items:
items = []
groups[uid] = items
items.append({'id':str(row['groupid']), 'name':row['name'], 'info':row['info']})
# 获取权限
sql = "select up.userid as userid, up.permid as permid, p.name as name, p.info as info from user_perm as up,perms as p " \
"where up.userid in (%s) and up.permid=p.id" % (useridstr)
ret = conn.query(sql)
log.debug('perms:%s', ret)
for row in ret:
uid = row['userid']
items = perms.get(uid)
if not items:
items = []
perms[uid] = items
items.append({'id':str(row['permid']), 'name':row['name'], 'info':row['info']})
# 获取角色
sql = "select up.userid as userid, up.roleid as roleid, r.name as name, r.info as info from user_perm as up,roles as r " \
"where up.userid in (%s) and up.roleid=r.id" % (useridstr)
ret = conn.query(sql)
log.debug('roles:%s', ret)
for row in ret:
uid = row['userid']
items = roles.get(uid)
if not items:
items = []
roles[uid] = items
items.append({'id':str(row['roleid']), 'name':row['name'], 'info':row['info']})
pdata = page.pagedata.data
for row in pdata:
for k in ['password']:
row.pop(k)
row['group'] = groups.get(int(row['id']), [])
row['perm'] = perms.get(int(row['id']), [])
row['role'] = roles.get(int(row['id']), [])
row['id'] = str(row['id'])
if row['extend']:
row['extend'] = json.loads(row['extend'])
pagedata = {
'page':page.page,
'pagesize':page.page_size,
'pagenum':page.pages,
'data':pdata,
}
return OK, pagedata
def set_admin(self, v=1):
with dbpool.get_connection('usercenter') as conn:
conn.update('users', {'isadmin': v}, where={'mobile': self.mobile})
def delete(self, xid):
xid = int(xid)
with get_connection(self.dbname) as conn:
ret = conn.delete(self.table, where={'id':xid})
return OK, ret
