def join(member): """ 회원 등록. 회원 정보를 포함하고 있는 딕셔너리를 던져 주면 회원 등록을 시도한다. 실패했을 경우 상황에 따른 오류 코드를 반환한다. member에 오는 키와 값은 다음과 같다. - username: 사용자 ID - password: 사용자 암호. 두 번 입력받는 걸 검증하는 역할은 프론트엔드에서 담당한다. 암호화되지 않음. - nick: 별명. - email: 이메일 주소. - signature: 글 뒤에 붙는 시그. - introduction: 회원 정보 페이지에 뜨는 자기 소개. @type member: dict @param member: 회원 정보 딕셔너리. @rtype tuple @return: 회원 등록 성공 여부(T/F)와 오류 코드(실패 시)를 포함하는 튜플. """ if not util.validate_username(member['username']): return (False, _('INVALID_USERNAME')) if _get_uid_from_username(member['username']) > 0: return (False, _('ID_ALREADY_EXISTS')) t = db.transaction() try: result = db.insert('Users', uNick = member['nick'], uEmail = member['email'], uId = member['username'], uPasswd = generate_password(member['password']), uDatetime = web.SQLLiteral('NOW()'), uSig = '', uPlan = '') except: t.rollback() return (False, _('DATABASE_ERROR')) else: t.commit() return (True, '')
def update_post(id=None, where=None, vars=None, **values): if id is not None: where = 'id=$id' vars = {'id': id} filter_values(values) tags = values.pop('tags', None) category_ids = values.pop('category_ids', None) values['modified'] = datetime.utcnow() t = db.transaction() try: db.update('posts', where=where, vars=vars, **values) if tags: tag_model.save_tag_relationships(post_id, 'post', tags) if category_ids: category_model.save_category_relationships(post_id, category_ids) except: t.rollback() return False else: t.commit() return True
def save_category_relationships(object_id, category_ids, new=False): if not isinstance(category_ids, list): return False category_ids = web.uniq(category_ids) if not category_ids: return False t = db.transaction() try: if new: add_category_ids = category_ids else: old_category_relationships = db.select('object_category_relationships', what='category_id', where='object_id=$object_id', vars={'object_id': object_id}) old_category_ids = [category['category_id'] for category in old_category_relationships] del_category_ids = set(old_category_ids) - set(category_ids) add_category_ids = set(category_ids) - set(old_category_ids) if del_category_ids: db.delete('object_category_relationships', where='object_id=$object_id AND category_id IN $del_category_ids', vars={'object_id': object_id, 'del_category_ids': list(del_category_ids)}) if add_category_ids: db.multiple_insert('object_category_relationships', [{'category_id': category_id, 'object_id': object_id} for category_id in add_category_ids]) except: t.rollback() return False else: t.commit() return True
def delete_user(uid): """ 회원 정보 삭제. """ result = get_owned_board(uid) has_board = False for b in result: has_board = True if has_board: return (False, _('HAS_BOARD')) # 즐겨찾는 보드 삭제 result = get_favorite_board(uid) for b in result: remove_favorite_board(uid, b.bSerial) val = dict(user_id = uid) t = db.transaction() try: result = db.delete('Users', vars=val, where='uSerial = $user_id') except: t.rollback() return (False, _('DATABASE_ERROR')) else: t.commit() return (True, _('SUCCESS'))
def del_category(ids=None, where=None, vars=None): if ids: if isinstance(ids, int): where = 'id=$id' vars = {'id': ids} elif isinstance(ids, list): if ids: where = 'id IN $ids' vars = {'ids': ids} else: return False if where is None: return False t = db.transaction() try: count = db.delete('categories', where=where, vars=vars) db.delete('object_category_relationships', where='category_id IN $ids', vars={'ids': ids}) except: t.rollback() return False else: t.commit() return count
def send_mail(from_id, to_id, title, body): sender_info = user.get_user(from_id) receiver_info = user.get_user(to_id) if not sender_info[0]: return (False, _('INVALID_SENDER')) if not receiver_info[0]: return (False, _('INVALID_RECEIVER')) if title.strip() == '': return (False, _('EMPTY_TITLE')) if body.strip() == '': return (False, _('EMPTY_BODY')) t = db.transaction() try: db.insert('Mails', mSenderSerial = from_id, mReceiverSerial = to_id, mSenderId = sender_info[1].uId, mSenderNick = sender_info[1].uNick, mDatetime = web.SQLLiteral('NOW()'), mTitle = title, mContent = body) except: t.rollback() return (False, _('DATABASE_ERROR')) else: t.commit() return (True, _('SUCCESS'))
def create_board(parent_id, settings): original_board_info = get_board_info(parent_id) if original_board_info == None: return (False, _('NO_SUCH_BOARD')) if not util.validate_boardname(settings['path']): return (False, _('INVALID_BOARDNAME')) check = _get_board_id_from_path(settings['path']) if check > 0: return (False, _('BOARD_ALREADY_EXIST')) if not acl.is_allowed('board', parent_id, settings['current_uid'], 'create'): return (False, _('NO_PERMISSION')) if settings['type'] == 2: if _get_board_id_from_path(settings['description']) < 0 or settings['description'].strip() == '': return (False, _('NO_SUCH_BOARD')) t = db.transaction() try: ret = db.insert('Boards', bName = settings['path'], uSerial = settings['board_owner'], bParent = parent_id, bDatetime = web.SQLLiteral('NOW()'), bInformation = settings['cover'], bDescription = settings['description'], bType = settings['type'], bReply = 1, bWrite = settings['guest_write'], bComment = settings['can_comment'], indexable = settings['indexable'], show_avatar = settings['show_avatar']) except: t.rollback() else: t.commit() return (True, 'SUCCESS')
def transcation(): with db.transaction(): db.table('user').where('id', 26).update({'name': 'hehe'}) db.table('user').insert([ {'uid': 111, 'name': '111', 'email': '*****@*****.**'}, {'uid': 222, 'name': '222', 'email': '*****@*****.**'} ])
def save_tag_relationships(object_id, model, tags=None, new=False): if isinstance(tags, (unicode, str)): tags = tags.split(',') if not isinstance(tags, list): return False tags = [tag.strip() for tag in tags if tag] tags = web.uniq(tags) if not tags: return False t = db.transaction() try: new_tag_ids = new_tags(tags) if new: add_tag_ids = new_tag_ids else: old_tag_relationships = db.select('object_tag_relationships', what='tag_id', where='object_id=$object_id AND model=$model', vars={'object_id': object_id, 'model': model}) old_tag_ids = [tag['tag_id'] for tag in old_tag_relationships] del_tag_ids = set(old_tag_ids) - set(new_tag_ids) add_tag_ids = set(new_tag_ids) - set(old_tag_ids) if del_tag_ids: db.delete('object_tag_relationships', where='object_id=$object_id AND model=$model AND tag_id IN $del_tag_ids', vars={'object_id': object_id, 'model': model, 'del_tag_ids': list(del_tag_ids)}) if add_tag_ids: db.multiple_insert('object_tag_relationships', [{'tag_id': tag_id, 'object_id': object_id, 'model': model} for tag_id in add_tag_ids]) except: t.rollback() return False else: t.commit() return True
def remove_subscription_board(uid, board_id): val = dict(uid = uid, board_id = board_id) t = db.transaction() try: result = db.delete('Subscriptions', vars=val, where='uSerial = $uid AND bSerial = $board_id') except: t.rollback() else: t.commit() return result
def delete_mail(mail_id): t = db.transaction() try: result = db.delete('Mails', vars=locals(), where='mSerial = $mail_id') except: t.rollback() return (False, _('DATABASE_ERROR')) else: t.commit() return (True, _('SUCCESS'))
def add_favorite_board(uid, board_id): t = db.transaction() try: result = db.insert('Favorites', uSerial = uid, bSerial = board_id) except: t.rollback() return False else: t.commit() return result
def add_subscription_board(uid, board_id): t = db.transaction() try: result = db.insert('Subscriptions', uSerial = uid, bSerial = board_id, lastSubscriptedDate=web.SQLLiteral('NOW()')) except: t.rollback() return False else: t.commit() return result
def board_edit(current_uid, board_id, settings): # settings로 넘어오는 내용 # path, name: 보드 전체 경로 # description: 보드 짧은 설명 # owner: 보대 ID. uid로 변환해야 함. # cover: 긴 설명, cover에 들어가는 내용 # board_type: 0 - 폴더, 1 - 게시판 # can_write_by_other: 쓰기 가능/불가능 # can_comment: 0 - 불가능, 1 - 가능 # indexable: 0 - 검색 제외, 1 - 검색 포함 # show_avatar: 0 - 안 보임, 1 - 보임 if not acl.is_allowed('board', board_id, current_uid, 'edit'): return (False, _('NO_PERMISSION')) original_board_info = get_board_info(board_id) if original_board_info == None: return (False, _('NO_SUCH_BOARD')) settings['board_id'] = board_id new_path = posixpath.join(settings['path'], settings['name']) if not util.validate_boardname(new_path): return (False, _('INVALID_BOARDNAME')) if settings['board_type'] == 2: if article._get_article_count(board_id) > 0: return (False, _('ALIAS_CANT_HAVE_ARTICLE')) if _get_board_id_from_path(settings['description']) < 0 or settings['description'].strip() == '': return (False, _('NO_SUCH_BOARD')) old_path = original_board_info.bName old_directory = posixpath.dirname(old_path) new_directory = settings['path'] if _get_board_id_from_path(new_path) > 0 and old_path != new_path: return (False, _('BOARD_ALREADY_EXIST')) new_parent_id = _get_board_id_from_path(settings['path']) if new_parent_id < 0: return (False, _('INVALID_PARENT')) if new_parent_id != original_board_info.bParent: if not acl.is_allowed('board', new_parent_id, current_uid, 'create'): return (False, _('NO_PERMISSION_ON_NEW_PARENT')) t = db.transaction() try: result = db.update('Boards', vars=settings, where='bSerial = $board_id', bInformation = settings['cover'], bDescription = settings['description'], bType = settings['board_type'], bReply = 1, bComment = settings['can_comment'], indexable = settings['indexable'], stylesheet = settings['stylesheet'], show_avatar = settings['show_avatar'], bWrite = settings['can_write_by_other'], uSerial = settings['owner'], bName = new_path, bParent = new_parent_id) result = move_child_boards(board_id, old_path, new_path) except: t.rollback() else: t.commit() return (True, new_path)
def update_new_article_none_hit(uid): t = db.transaction() try: result = db.update('Users', vars=locals(), where = 'uSerial = $uid', new_article_none_hit = web.SQLLiteral('new_article_none_hit + 1')) except: t.rollback() return False else: t.commit() return result
def mark_as_read(mail_id): t = db.transaction() try: result = db.update('Mails', vars=locals(), where='mSerial = $mail_id', mMark = 1) except: t.rollback() return False else: t.commit() return True
def add(module, _tags=''): del (module.directory_title) t = db.transaction() try: id = db.insert('modules', **module) tags.add(id, _tags) except: t.rollback() raise else: t.commit()
def add(module, _tags=''): del(module.directory_title) t = db.transaction() try: id = db.insert('modules', **module) tags.add(id, _tags) except: t.rollback() raise else: t.commit()
def remove_login_info(username, timestamp): time = datetime.datetime.fromtimestamp(timestamp) val = dict(username=username, time=time) t = db.transaction() try: result = db.delete('login_temp', vars=val, where='logintime=$time AND id=$username') except: t.rollback() return False else: t.commit() return True
def modify_user(uid, member): """ 회원 정보 수정. frontend에서 접근 권한을 통제해야 한다. 시삽은 임의 회원의 정보를 수정할 수 있다. C{member} 딕셔너리는 수정할 정보로, 형식은 L{register}를 참고한다. 빈 값이 들어오면 삭제를 의미하므로, 기존 정보를 수정하려면 정보를 그대로 넘겨줘야 한다. @type uid: int @param uid: 수정할 회원의 사용자 ID. @type member: dict @param member: 회원 정보 딕셔너리. (password: 암호, email: E-Mail, homepage: 홈페이지, sig: 시그, introduction: 자기 소개) @rtype tuple @return: 정보 수정 성공 여부(T/F)와 오류 코드(실패 시)를 포함하는 튜플. """ profile_image_path = os.path.join(config.pi_disk_path, '%s.png' % member['user_id']) m = magic.Magic(mime = True) mime_type = m.from_buffer(member['profile_image']) if member['delete_profile_image']: if os.path.exists(profile_image_path): os.remove(profile_image_path) else: if mime_type.startswith('image'): image_file = StringIO.StringIO(member['profile_image']) if pil: pi = Image.open(image_file) r = pi.resize(config.pi_size, Image.ANTIALIAS) r.save(profile_image_path) t = db.transaction() try: if member['password']: result = db.update('Users', vars=member, where='uSerial = $user_id', uNick = member['nick'], uEmail = member['email'], uSig = member['sig'], uPlan = member['introduction'], uHomepage = member['homepage'], language = member['language'], theme = member['theme'], uPasswd = generate_password(member['password'])) else: result = db.update('Users', vars=member, where='uSerial = $user_id', uNick = member['nick'], uEmail = member['email'], uHomepage = member['homepage'], uSig = member['sig'], uPlan = member['introduction'], language = member['language'], theme = member['theme']) except: t.rollback() return False else: t.commit() return result
def add_login_info(username, password_hash, referer, persistent): t = db.transaction() try: result = db.insert('login_temp', id=username, password_hash=password_hash, logintime = web.SQLLiteral('NOW()'), referer=referer, persistent = persistent) except: t.rollback() return -1 else: t.commit() result = db.select('login_temp', locals(), where='id = $username') return int(time.mktime(result[0].logintime.timetuple()))
def update_last_login(uid, ip_address): val = dict(uid = uid, ip_address = ip_address) t = db.transaction() try: result = db.update('Users', vars=val, where = 'uSerial = $uid', uNumLogin = web.SQLLiteral('uNumLogin + 1'), uLastLogin = web.SQLLiteral('NOW()'), uLastHost = ip_address) except: t.rollback() return False else: t.commit() return result
def move_child_boards(board_id, old_path, new_path): # board_id 보드의 자식 보드가 속해 있는 경로를 new_path로 이동한다. val = dict(old_path = old_path + r'%') result = db.select('Boards', val, where = 'bName LIKE $old_path') t = db.transaction() try: for r in result: if not r.bName.startswith(old_path): continue val2 = dict(board_id = r.bSerial) update = db.update('Boards', vars = val2, where = 'bSerial = $board_id', bName = new_path + r.bName[len(old_path):]) except: t.rollback() else: t.commit()
def update_password(uid, password): val = dict(uid = uid) result = db.select('Users', val, where='uSerial = $uid') user = None try: user = result[0] except IndexError: return False t = db.transaction() try: ret = db.update('Users', vars=val, where = 'uSerial = $uid', uPasswd = generate_password(password)) except: t.rollback() return False else: t.commit() return True
def add_reg(uid,s): with db.transaction(): db.insert('reg_log',uid = uid , studentid=s.studentid, college=s.college, name=s.name, telephone=s.telephone, gender=s.gender, ) db.update('users', vars = dict(uid=uid), where = 'uid = $uid', studentid=s.studentid, college=s.college, name=s.name, telephone=s.telephone, gender=s.gender, ) return True return False
def backpopulateCurrentFrameScores(): t = db.transaction() try: breaks = list( db.query(""" SELECT * FROM tbreak """)) for breakx in breaks: frame_score, foul_points = tframe.getCurrentFrameScore( breakx['frame_id'], breakx['player_id'], break_num=breakx['break_num'], foul_num=breakx['foul_num']) rowcount = db.update('tbreak', where='break_id=$break_id', vars={'break_id': breakx['break_id']}, frame_score=frame_score) opp_frame_score, opp_foul_points = tframe.getCurrentFrameScore( breakx['frame_id'], breakx['player_id'], break_num=breakx['break_num'], foul_num=breakx['foul_num'], opponent=True) rowcount += db.update('tbreak', where='break_id=$break_id', vars={'break_id': breakx['break_id']}, opp_frame_score=opp_frame_score) if rowcount != 2: print "F*****G HELL" raise except: t.rollback() raise else: t.commit()
def new_post(**values): filter_values(values) tags = values.pop('tags', None) category_ids = values.pop('category_ids', None) values['created'] = values['modified'] = datetime.utcnow() t = db.transaction() try: post_id = db.insert('posts', **values) if tags: tag_model.save_tag_relationships(post_id, 'post', tags, new=True) if category_ids: category_model.save_category_relationships(post_id, category_ids, new=True) except: t.rollback() return False else: t.commit() return post_id
def add_reg(uid, s): with db.transaction(): db.insert( 'reg_log', uid=uid, studentid=s.studentid, college=s.college, name=s.name, telephone=s.telephone, gender=s.gender, ) db.update( 'users', vars=dict(uid=uid), where='uid = $uid', studentid=s.studentid, college=s.college, name=s.name, telephone=s.telephone, gender=s.gender, ) return True return False
def parseCsvMatch(csv_content, date=''): func = "match.parseCsvMatch()" error = "" data = csv.reader(StringIO.StringIO(csv_content), delimiter=',') column_headers = [] player_ids = {} # dictionary of names mapping to id shots = [] # First pass to create array of shot dictionaries row_no = 0 for shotrow in data: col_no = 0 shot = {} # Check number of columns if len(shotrow) != len(MATCH_COLUMNS): error = "bad number of csv columns" return error, 0 for colval in shotrow: if row_no == 0: # Reverse lookup column header code to create dictionaries with keys from MATCH_COLUMNS try: column_header = (key for key,value in MATCH_COLUMNS.items() if value==colval).next() column_headers.append(column_header) except StopIteration: error = "invalid csv header: "+colval return error, 0 else: # Find which column we're looking at based on headers col_type = column_headers[col_no] # Add value to shot dictionary shot[col_type] = colval # Set player names if not known, create players if they don't exist, trim spaces from player names if col_type == "PLAYER": colval = colval.replace(" ", "") if colval not in player_ids: if len(player_ids) < 2: player_ids[colval] = tplayer.getOrCreatePlayer(colval) else: error = "More than two players detected" return error, 0 col_no += 1 # Add shot dictionary to list of shots if shot != {}: shots.append(shot) row_no += 1 # Errors from first pass - Known issue: Won't work if one player doesn't pot if len(player_ids) != 2: error = "Invalid number of players: "+str(len(player_ids)) return error, 0 # Check if match might already exist # select m.match_id, count(distinct p.name), count(*) # from tmatch m, tframe f, tbreak b, tbreakpot bp, tmatchscore ms, tplayer p # where m.match_id=f.match_id and f.frame_id=b.frame_id and b.break_id=bp.break_id and # m.match_id=ms.match_id and ms.player_id=p.player_id and p.name in ("David", "Jimmy") # group by 1 having count(distinct p.name)=2; # Second pass to populate database # FIXME BEGIN TRANSACTION match_id = 0 t = db.transaction() try: match_id = createMatch(date) cur_player_id = 0 cur_frame_id = 0 cur_frame = 0 cur_break_id = 0 cur_break = 0 for shot in shots: cur_player_id = player_ids[shot['PLAYER']] if shot['FRAME'] != cur_frame: # New frame if cur_break_id > 0: breakpot.closeBreak(cur_break_id) cur_break_id = 0 if cur_frame_id > 0: tframe.closeFrame(cur_frame_id) cur_frame = shot['FRAME'] cur_frame_id = tframe.createFrame(match_id, cur_frame) if shot['BREAK'] != cur_break or shot['BREAK'] == '': # New break if cur_break_id > 0: breakpot.closeBreak(cur_break_id) if shot['BREAK'] != '': cur_break = shot['BREAK'] cur_break_id = breakpot.createBreak(cur_frame_id, cur_break, cur_player_id) # Register shot breakpot.pot(cur_break_id, shot['BALL'], shot['POINTS'], shot['TYPE']) if cur_break_id > 0: breakpot.closeBreak(cur_break_id) if cur_frame_id > 0: tframe.closeFrame(cur_frame_id) closeMatch(match_id) commitMatch(match_id) except Exception, e: log.error('Failed to create match - '+str(e)) error = 'Failed to create match' log.error('rollback()') t.rollback()