async def change_nickname(self): u: User = self.current_user if not u: return self.finish(RETCODE.PERMISSION_DENIED) vpost: ChangeNicknameDataModel = self._.validated_post if u.change_nickname_chance > 0: try: old_nickname = u.nickname u.nickname = vpost.nickname u.change_nickname_chance -= 1 u.is_new_user = False u.save() self.finish( RETCODE.SUCCESS, { 'nickname': u.nickname, 'change_nickname_chance': u.change_nickname_chance }) # note: 虽然有点奇怪,但下面这句其实没问题 18.11.13 ManageLog.add_by_post_changed(self, 'nickname', MOP.USER_NICKNAME_CHANGE, POST_TYPES.USER, True, {'nickname': old_nickname}, u) return except peewee.DatabaseError: db.rollback() self.finish(RETCODE.FAILED)
def work(): try: db.execute_sql(r""" CREATE OR REPLACE FUNCTION int2bytea(v_number bigint) RETURNS bytea AS $$ DECLARE v_str text; BEGIN v_str = to_hex(v_number)::text; return decode(concat(repeat('0', length(v_str) %% 2), v_str), 'hex'); END; $$ LANGUAGE plpgsql; CREATE SEQUENCE IF NOT EXISTS id_gen_seq NO MINVALUE NO MAXVALUE START 4096 NO CYCLE; /* 0x1000 */ CREATE SEQUENCE IF NOT EXISTS user_count_seq NO MINVALUE NO MAXVALUE START 1 NO CYCLE; """) # 请注意,这俩需要数据库的 superuser 权限,因此普通用户是做不到的 # 会提示 permission denied to create extension "hstore" 这样的错误 # db.execute_sql(""" # CREATE EXTENSION IF NOT EXISTS hstore; # CREATE EXTENSION IF NOT EXISTS citext; # """) except peewee.ProgrammingError as e: db.rollback() traceback.print_exc() quit() sql_execute('alter table "user" drop column key') sql_execute('alter table "user" drop column key_time')
def main(): """Main function for populating the database""" donors = [('Toni Morrison', random.sample(range(100, 25000), 3)), ('Mike McHargue', random.sample(range(100, 25000), 3)), ("Flannery O'Connor", random.sample(range(100, 25000), 3)), ('Angelina Davis', random.sample(range(100, 25000), 3)), ('Bell Hooks', random.sample(range(100, 25000), 3))] db.connect() db.drop_tables([Donor, Donation]) db.create_tables([Donor, Donation]) bcrypt = Bcrypt() for donor, donations in donors: try: Donor.create(name=donor, email='.'.join(donor.lower().split()) + '@gmail.com', password=bcrypt.generate_password_hash('password'), total=sum(donations), average=sum(donations) / len(donations)) except: db.rollback() for donor, donations in donors: for donation in donations: try: Donation.create(donor=donor, amount=donation) except: db.rollback()
def new(cls, username, password, *, email=None, nickname=None) -> Optional['User']: values = { 'id': CustomID().to_bin(), 'email': email, 'username': username, 'time': int(time.time()) } info = cls.gen_password_and_salt(password) values.update(info) try: uid = User.insert(values).execute() u = User.get_by_pk(uid) return u except peewee.IntegrityError as e: # traceback.print_exc() db.rollback() if e.args[0].startswith('duplicate key'): return except peewee.DatabaseError: traceback.print_exc() db.rollback()
def sql_execute(sql): try: db.execute_sql(sql) except Exception as e: print(e) print('failed') db.rollback()
async def change_nickname(self): u = self.current_user if not u: return self.finish(RETCODE.PERMISSION_DENIED) post = await self.post_data() form = NicknameForm(**post) if not form.validate(): return self.finish(RETCODE.INVALID_POSTDATA, form.errors) if u.change_nickname_chance > 0: try: old_nickname = u.nickname u.nickname = form['nickname'].data u.change_nickname_chance -= 1 u.is_new_user = False u.save() self.finish( RETCODE.SUCCESS, { 'nickname': u.nickname, 'change_nickname_chance': u.change_nickname_chance }) # note: 虽然有点奇怪,但下面这句其实没问题 18.11.13 ManageLog.add_by_post_changed(self, 'nickname', MOP.USER_NICKNAME_CHANGE, POST_TYPES.USER, True, {'nickname': old_nickname}, u) return except peewee.DatabaseError: db.rollback() self.finish(RETCODE.FAILED)
def work(): try: db.execute_sql( 'ALTER TABLE "topic" ADD COLUMN "update_time" BIGINT NULL DEFAULT 0;' ) except Exception as e: print(e) print('failed') db.rollback()
def create_category(db,catname,meta): try: db.close() db.connect() cat = Categories.create(name=json.dumps(catname), metadata=meta) cat.save() db.close() except: db.rollback() raise
def post(self): try: data = request.json entity = self.get_entity() user = entity.insert(**(data)) db.commit() return row2dict(user) except Exception as e: db.rollback() abort(500, message=str(e))
def work(): try: db.execute_sql( 'ALTER TABLE public.comment ADD reply_to_cmt_id BYTEA NULL;') except: db.rollback() for i in UserModel.select().execute(): try: UserNotifRecord.create(id=i.id, update_time=int(time.time())) except peewee.IntegrityError: db.rollback()
def refresh_key(self): count = 0 while count < 10: with db.atomic(): try: k = self.gen_key() self.key = k['key'] self.key_time = k['key_time'] self.save() return k except DatabaseError: count += 1 db.rollback() raise ValueError("generate key failed")
def new(cls, nickname, password, extra_values=None, *, auto_nickname=False, is_for_tests=True) -> Optional['User']: values = { 'nickname': nickname, 'is_new_user': True # 'is_for_tests': is_for_tests } values.update(extra_values) cls.append_post_id(values) info = cls.gen_password_and_salt(password) values.update(info) try: uid = cls.insert(values).execute() u: User = cls.get_by_pk(uid) uchanged = False # 如果是第一个用户,那么自动为管理员 if u.number == 1: u.group = USER_GROUP.ADMIN uchanged = True # 注册成功后,如果要求自动设置用户名,那么修改用户名 if auto_nickname: nprefix = config.USER_NICKNAME_AUTO_PREFIX + '_' u.change_nickname_chance = 1 u.nickname = nprefix + uid.to_hex() uchanged = True if uchanged: u.save() return u except peewee.IntegrityError: traceback.print_exc() db.rollback() # if e.args[0].startswith('duplicate key | 错误: 重复键违反唯一约束'): # return # 此处似乎无从得知,数据库会返回什么样的文本,应该是和语言相关 # 那么姑且假定 IntegrityError 都是唯一性约束 except peewee.DatabaseError: db.rollback()
def work(): sql_execute( 'ALTER TABLE "topic" ADD COLUMN "update_time" BIGINT NULL DEFAULT NULL ;' ) sql_execute( 'ALTER TABLE "user" ADD COLUMN "ip_registered" inet NULL DEFAULT NULL;' ) sql_execute('drop table "notif";') sql_execute('drop table "mention";') sql_execute('drop table "user_notif_record";') db.create_tables([UserNotifLastInfo], safe=True) for i in UserModel.select().execute(): try: UserNotifLastInfo.create(id=i.id, update_time=int(time.time())) except peewee.IntegrityError as e: print(e) db.rollback()
def post(self): try: data = request.json vehicles = '' # only one relation can be set on creation if ('vehicles' in data): vehicles = int(data['vehicles']) del data['vehicles'] user = db.users.insert(**(data)) if (vehicles != ''): v = db.objects.filter(db.objects.nid == vehicles).first() user.objects.append(v) db.commit() return row2dict(user) except Exception as e: db.rollback() abort(500, message=str(e))
def post(self): try: data = request.json entity = self.get_entity() users = '' # only one relation can be set on creation if ('users' in data): users = int(data['users']) del data['users'] vehicle = entity.insert(**(data)) if (users != ''): u = db.users.filter(db.users.nid == users).first() vehicle.users.append(u) db.commit() return row2dict(vehicle) except Exception as e: db.rollback() abort(500, message=str(e))
def login_success(token, profile): # Conditionally setting breakpoint if DEBUG: pdb.set_trace() # Checking if the user is already signed in if User.query.filter(User.ext_id == profile['id']).first() is not None: session['profile_ext_id_hashed'] = hash_id(profile['id']) User.query.filter(User.ext_id == profile['id']).first( ).ext_id_hashed = session['profile_ext_id_hashed'] try: db.session.commit() except Exception: db.rollback() session.clear() return redirect('home') else: # Setting session cookie session.clear() session['profile_name'] = profile['name'] session['profile_email'] = profile['email'] session['profile_picture'] = profile['picture'] session['profile_ext_id'] = str(profile['id']) return redirect('register')
def work(): try: db.execute_sql('DROP TABLE statistic24h;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "visible" INTEGER NULL DEFAULT NULL;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "user_id" BYTEA NULL DEFAULT NULL;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "reset_key" BYTEA NULL DEFAULT NULL;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "avatar" TEXT NULL DEFAULT NULL;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "type" INTEGER DEFAULT 0;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "url" TEXT NULL DEFAULT NULL;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "location" TEXT NULL DEFAULT NULL;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "access_time" BIGINT NULL DEFAULT NULL;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "last_check_in_time" BIGINT NULL DEFAULT NULL;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "check_in_his" INT DEFAULT 0;') db.execute_sql('ALTER TABLE "user" ADD COLUMN "exp" INT DEFAULT 0;') db.execute_sql('ALTER TABLE "user" RENAME "reg_time" TO "time";') db.execute_sql('ALTER TABLE "board" RENAME "creator_id" TO "user_id";') db.execute_sql('ALTER TABLE "comment" ADD COLUMN "post_number" INTEGER NULL;') db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "bookmark_count" INTEGER DEFAULT 0;') db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "upvote_count" INTEGER DEFAULT 0;') db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "downvote_count" INTEGER DEFAULT 0;') db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "thank_count" INTEGER DEFAULT 0;') db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "vote_weight" BIGINT DEFAULT 0;') db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "bookmarked_users" BYTEA[] NULL;') db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "upvoted_users" BYTEA[] NULL;') db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "downvoted_users" BYTEA[] NULL;') db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "thanked_users" BYTEA[] NULL;') except Exception as e: print(e) print('failed') db.rollback() for i in Comment.select(): post_number = Comment.select().where(Comment.related_id == i.related_id, Comment.id <= i.id).count() Comment.update(post_number=post_number).where(Comment.id == i.id).execute()
BEGIN v_str = to_hex(v_number)::text; return decode(concat(repeat('0', length(v_str) %% 2), v_str), 'hex'); END; $$ LANGUAGE plpgsql; CREATE SEQUENCE IF NOT EXISTS id_gen_seq NO MINVALUE NO MAXVALUE START 4096 NO CYCLE; /* 0x1000 */ CREATE SEQUENCE IF NOT EXISTS user_count_seq NO MINVALUE NO MAXVALUE START 1 NO CYCLE; """) db.execute_sql(""" CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS citext; """) except peewee.ProgrammingError: # permission denied to create extension "hstore" db.rollback() db.create_tables([ Test, Board, Follow, Comment, Topic, User, WikiArticle, Notification, UserNotifLastInfo, UserOAuth, Upload, ManageLog, Mention, Statistic, Statistic24h ], safe=True) try: db.execute_sql(""" ALTER TABLE "board" ALTER COLUMN "id" SET DEFAULT int2bytea(nextval('id_gen_seq')::bigint); ALTER TABLE "topic" ALTER COLUMN "id" SET DEFAULT int2bytea(nextval('id_gen_seq')::bigint); ALTER TABLE "user" ALTER COLUMN "id" SET DEFAULT int2bytea(nextval('id_gen_seq')::bigint); ALTER TABLE "user" ALTER COLUMN "number" SET DEFAULT nextval('user_count_seq')::bigint; """)
def work(): try: db.execute_sql('ALTER TABLE "board" ADD COLUMN "parent_id" BYTEA NULL DEFAULT NULL;') except: print('failed') db.rollback()
def new(cls, user_id): try: return cls.create(id=user_id, update_time=int(time.time())) except IntegrityError: db.rollback()
def clear_by_user_id(cls, user_id): try: cls.update(deleted_at=int(time.time())).where( cls.user_id == user_id).execute() except peewee.DatabaseError: db.rollback()
def sql_execute(sql): try: db.execute_sql(sql) except Exception as e: db.rollback()
async def create_user(self, password, email=None, phone=None) -> User: values = {} nprefix = config.USER_NICKNAME_AUTO_PREFIX + '_' if config.POST_ID_GENERATOR != config.AutoGenerator: # 若不使用数据库生成id uid = User.gen_id() values['id'] = uid.to_bin() values['nickname'] = nprefix + uid.to_hex() values['is_new_user'] = True values['change_nickname_chance'] = 1 if email: values['email'] = email.lower() if phone: values['phone'] = phone # 密码 ret = User.gen_password_and_salt(password) values.update(ret) values['group'] = USER_GROUP.NORMAL values['state'] = POST_STATE.NORMAL # 注册IP地址 values['ip_registered'] = await get_fuzz_ip(self) # 生成 access_token values.update(User.gen_key()) values['time'] = int(time.time()) try: uid = User.insert(values).execute() u = User.get_by_pk(uid) except peewee.IntegrityError as e: db.rollback() if e.args[0].startswith('duplicate key'): return self.finish(RETCODE.ALREADY_EXISTS) return self.finish(RETCODE.FAILED) except peewee.DatabaseError: db.rollback() return self.finish(RETCODE.FAILED) times = 3 success = False u.nickname = nprefix + to_hex(u.id.tobytes()) # 尝试填充用户名 while times >= 0: try: if u.number == 1: u.group = USER_GROUP.ADMIN u.save() success = True break except peewee.DatabaseError: db.rollback() times -= 1 u.nickname = nprefix + to_hex(os.urandom(8)) if not success: return self.finish(RETCODE.FAILED) # 清理现场 if email: await User.reg_code_cleanup(email) # 添加统计记录 post_stats_new(POST_TYPES.USER, u.id) UserNotifLastInfo.new(u.id) return u