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