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()
