示例#1
0
文件: user.py 项目: dm-tyurin/tp_subd
    def details(email):
        columns = 'u.*, group_concat(distinct f.following) as following,' \
                  'group_concat(distinct f1.follower) as followers,' \
                  'group_concat(distinct s.thread) as subscriptions'

        query = Query.SELECT.format(
            columns=columns,
            table=Tables.TABLE_NAMES['USER'] + ' as u'
        ) + Query.LEFT_JOIN.format(
            table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f',
            clause='u.email = f.follower'
        ) + Query.LEFT_JOIN.format(
            table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f1',
            clause='u.email = f1.following'
        ) + Query.LEFT_JOIN.format(
            table=Tables.TABLE_NAMES['USER_SUBSCRIBE'] + ' as s',
            clause='u.email= s.user'
        ) + Query.WHERE.format(
            clause='u.email = %r' % str(email)
        )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            user = dictfetchall(cursor)[0]
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        if not user['email']:
            return Codes.NOT_FOUND, 'User %r not found' % str(email)

        return Codes.OK, User.get_response_values(user)
示例#2
0
    def details(forum, related=None):
        if not related:
            related = []

        query = Query.SELECT.format(
            columns='*',
            table=Tables.TABLE_NAMES['FORUM']
        ) + Query.WHERE.format(
            clause='short_name = %r' % str(forum)
        )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            response = dictfetchall(cursor)[0]
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        code = Codes.OK
        if 'user' in related:
            code, response['user'] = User.details(response['user'])

        return code, response
示例#3
0
    def get_thread(thread_id):
        connection = DBConnect()

        query = Query.select.format(
            columns='*',
            table=Tables.THREAD_TABLE_NAME
        ) + Query.where.format(
            clause='id = ' + str(thread_id)
        )

        try:
            cursor = connection.cursor()
            cursor.execute(query)
            result = dictfetchall(cursor)[0]
        except Exception as e:
            if 'list index out of range' in str(e):
                return Codes.NOT_FOUND, 'Post %s not found' % thread_id
            else:
                return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        result['date'] = str(result['date'])
        result['isDeleted'] = bool(result['isDeleted'])
        result['isClosed'] = bool(result['isClosed'])

        return Codes.OK, result
示例#4
0
文件: user.py 项目: dm-tyurin/tp_subd
    def list_by_forum(forum, request):
        if 'order' not in request:
            request['order'] = 'DESC'

        columns = 'u.*, group_concat(distinct f.following) as following,' \
                  'group_concat(distinct f1.follower) as followers,' \
                  'group_concat(distinct s.thread) as subscriptions'

        query = Query.SELECT.format(
            columns=columns,
            table=Tables.TABLE_NAMES['USER'] + ' as u'
        ) + Query.LEFT_JOIN.format(
            table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f',
            clause='u.email = f.follower'
        ) + Query.LEFT_JOIN.format(
            table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f1',
            clause='u.email = f1.following'
        ) + Query.LEFT_JOIN.format(
            table=Tables.TABLE_NAMES['USER_SUBSCRIBE'] + ' as s',
            clause='u.email= s.user'
        ) + Query.JOIN.format(
            table=Tables.TABLE_NAMES['POST'] + ' as p',
            clause='p.user = u.email'
        ) + Query.WHERE.format(
            clause='p.forum = %r' % str(forum)
        )
        if 'since_id' in request:
            query += Query.AND_CLAUSE.format(
                clause='u.id >= %s' % request['since_id']
            )
        query += Query.GROUP_BY.format(
            column='u.email'
        ) + Query.ORDER_BY.format(
            column='p.user_name',
            type=request['order']
        )
        if 'limit' in request:
            query += Query.LIMIT.format(
                count=request['limit']
            )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            users = dictfetchall(cursor)
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        response = []
        for user in users:
            response.append(User.get_response_values(user))

        return Codes.OK, response
示例#5
0
文件: user.py 项目: dm-tyurin/tp-subd
    def details(email):
        connection = DBConnect()

        columns = 'u.*, group_concat(distinct f.followee) as following,' \
                  'group_concat(distinct f1.follower) as followers,' \
                  'group_concat(distinct s.thread) as subscriptions'

        query = Query.select.format(
            columns=columns,
            table=Tables.USER_TABLE_NAME + ' as u'
        ) + Query.left_join.format(
            table=Tables.USER_FOLLOW_TABLE_NAME + ' as f',
            clause='u.email = f.follower'
        ) + Query.left_join.format(
            table=Tables.USER_FOLLOW_TABLE_NAME + ' as f1',
            clause='u.email = f1.followee'
        ) + Query.left_join.format(
            table=Tables.THREAD_SUBSCRIBES_TABLE_NAME + ' as s',
            clause='u.email= s.user'
        ) + Query.where.format(
            clause='u.email = %r' % str(email)
        )

        try:
            cursor = connection.cursor()
            cursor.execute(query)
            response = dictfetchall(cursor)[0]
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        if not response['email']:
            return Codes.NOT_FOUND, 'User ' + email + ' not found'

        response['isAnonymous'] = bool(response['isAnonymous'])

        if response['following']:
            response['following'] = response['following'].split(',')
        else:
            response['following'] = []
        if response['followers']:
            response['followers'] = response['followers'].split(',')
        else:
            response['followers'] = []
        if response['subscriptions']:
            subscribes_list = []
            subscribes = response['subscriptions'].split(',')
            for subscribe in subscribes:
                subscribes_list.append(int(subscribe))
            response['subscriptions'] = subscribes_list
        else:
            response['subscriptions'] = []

        return Codes.OK, response
示例#6
0
    def get_forum(forum):
        query = Query.SELECT.format(
            columns='*',
            table=Tables.TABLE_NAMES['FORUM']
        ) + Query.WHERE.format(
            clause='short_name = %r' % str(forum)
        )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            response = dictfetchall(cursor)[0]
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        return Codes.OK, response
示例#7
0
文件: post.py 项目: dm-tyurin/tp_subd
    def list_by_thread(thread, request):
        if 'order' not in request:
            request['order'] = 'DESC'

        columns = 'p.id, p.message, p.date, p.likes, p.dislikes, p.points, p.isApproved, ' \
                  'p.isHighlighted, p.isEdited, p.isSpam, p.isDeleted, p.forum, p.thread, p.user, p.parent'

        query = Query.SELECT.format(
            columns=columns,
            table=Tables.TABLE_NAMES['POST'] + ' as p'
        ) + Query.WHERE.format(
            clause='p.thread = %r' % str(thread)
        )
        if 'since' in request:
            query += Query.AND_CLAUSE.format(
                clause='p.date > %r' % str(request['since'])
            )
        query += Query.ORDER_BY.format(
            column='p.date',
            type=request['order']
        )
        if 'limit' in request:
            query += Query.LIMIT.format(
                count=request['limit']
            )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            post = dictfetchall(cursor)
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        response = []
        code = Codes.OK
        for post in post:
            response.append(Post.get_response_values(post))

        return code, response
示例#8
0
文件: post.py 项目: dm-tyurin/tp-subd
    def get_posts_by_user(user, request=None):
        connection = DBConnect()

        query = Query.select.format(
            columns='*',
            table=Tables.POST_TABLE_NAME
        )
        query += Query.where.format(
            clause='user = %r' % str(user)
        )
        if request and ('since' in request):
            query += Query.and_clause.format(
                clause='date >= %r' % str(request['since'])
            )
        if request and ('order' in request):
            query += Query.order_by.format(
                column='date',
                type=request['order']
            )
        if request and ('limit' in request):
            query += Query.limit.format(count=request['limit'])

        try:
            cursor = connection.cursor()
            cursor.execute(query)

            response = dictfetchall(cursor)
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        for post in response:
            post['date'] = str(post['date'])
            post['isDeleted'] = bool(post['isDeleted'])
            post['isApproved'] = bool(post['isApproved'])
            post['isEdited'] = bool(post['isEdited'])
            post['isSpam'] = bool(post['isSpam'])
            post['isHighlighted'] = bool(post['isHighlighted'])

        return Codes.OK, response
示例#9
0
文件: user.py 项目: dm-tyurin/tp_subd
    def get_user(email):
        query = Query.SELECT.format(
            columns='*',
            table=Tables.TABLE_NAMES['USER']
        ) + Query.WHERE.format(
            clause='email = %r' % str(email)
        )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            response = dictfetchall(cursor)[0]
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        response['isAnonymous'] = bool(response['isAnonymous'])

        return Codes.OK, response
示例#10
0
文件: post.py 项目: dm-tyurin/tp_subd
    def get_post(post):
        query = Query.SELECT.format(
            columns='id, message, date, isApproved, isHighlighted, isEdited, isSpam, isDeleted, forum, thread, user',
            table=Tables.TABLE_NAMES['POST']
        ) + Query.WHERE.format(
            clause='id = %s' % post
        )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            response = dictfetchall(cursor)[0]
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        response = Post.get_response_values(response)

        return Codes.OK, response
示例#11
0
文件: user.py 项目: dm-tyurin/tp_subd
    def list_following(email):
        columns = 'u.*, group_concat(distinct f.following) as following,' \
                  'group_concat(distinct f1.follower) as followers,' \
                  'group_concat(distinct s.thread) as subscriptions'

        query = Query.SELECT.format(
            columns=columns,
            table=Tables.TABLE_NAMES['USER'] + ' as u'
        ) + Query.LEFT_JOIN.format(
            table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f',
            clause='u.email = f.follower'
        ) + Query.LEFT_JOIN.format(
            table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f1',
            clause='u.email = f1.following'
        ) + Query.LEFT_JOIN.format(
            table=Tables.TABLE_NAMES['USER_SUBSCRIBE'] + ' as s',
            clause='u.email= s.user'
        ) + Query.WHERE.format(
            clause='f1.follower = %r' % str(email)
        ) + Query.GROUP_BY.format(
            column='f1.following'
        )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            users = dictfetchall(cursor)
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        response = []
        for user in users:
            response.append(User.get_response_values(user))

        return Codes.OK, response
示例#12
0
文件: user.py 项目: dm-tyurin/tp-subd
    def get_user(email):
        connection = DBConnect()

        query = Query.select.format(
            columns='*',
            table=Tables.USER_TABLE_NAME
        ) + Query.where.format(
            clause='email = %r' % str(email)
        )

        try:
            cursor = connection.cursor()
            cursor.execute(query)
            result = dictfetchall(cursor)[0]
            result['isAnonymous'] = bool(result['isAnonymous'])
        except Exception as e:
            if str(e) == 'list index out of range':
                return Codes.NOT_FOUND, "No such user"
            else:
                return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        return Codes.OK, result
示例#13
0
    def get_forum(short_name):
        connection = DBConnect()

        query = Query.select.format(
            columns='*',
            table=Tables.FORUM_TABLE_NAME
        ) + Query.where.format(
            clause='short_name = %r' % str(short_name)
        )

        try:
            cursor = connection.cursor()
            cursor.execute(query)

            result = dictfetchall(cursor)[0]
        except Exception as e:
            if str(e) == 'list index out of range':
                return Codes.NOT_FOUND, "No such user"
            else:
                return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        return Codes.OK, result
示例#14
0
文件: post.py 项目: dm-tyurin/tp-subd
    def get_posts_by_forum(forum, related, request=None):
        connection = DBConnect()

        columns = 'p.*'
        if 'thread' in related:
            columns += ', t.id as t_id, t.forum as t_forum, t.title as t_title, ' \
                       't.isClosed as t_isClosed, t.user as t_user, t.date as t_date, ' \
                       't.message as t_message, t.slug as t_slug, t.isDeleted as t_isDeleted, ' \
                       't.posts as t_posts, t.likes as t_likes, t.dislikes as t_dislikes, t.points as t_points'
        if 'forum' in related:
            columns += ', f.id as f_id, f.name as f_name, f.short_name as f_short_name, f.user as f_user'

        query = Query.select.format(
            columns=columns,
            table=Tables.POST_TABLE_NAME + ' as p'
        )
        if 'thread' in related:
            query += Query.join.format(
                table=Tables.THREAD_TABLE_NAME + ' as t',
                clause='p.thread = t.id'
            )
        if 'forum' in related:
            query += Query.join.format(
                table=Tables.FORUM_TABLE_NAME + ' as f',
                clause='p.forum = f.short_name'
            )
        query += Query.where.format(
            clause='p.forum = %r' % str(forum)
        )
        if request and ('since' in request):
            query += Query.and_clause.format(
                clause='p.date >= %r' % str(request['since'])
            )
        if request and ('order' in request):
            query += Query.order_by.format(
                column='p.date',
                type=request['order']
            )
        if request and ('limit' in request):
            query += Query.limit.format(count=request['limit'])

        try:
            cursor = connection.cursor()

            cursor.execute(query)
            posts = dictfetchall(cursor)
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        response = []
        for post in posts:
            post['date'] = str(post['date'])
            post['isDeleted'] = bool(post['isDeleted'])
            post['isApproved'] = bool(post['isApproved'])
            post['isEdited'] = bool(post['isEdited'])
            post['isSpam'] = bool(post['isSpam'])
            post['isHighlighted'] = bool(post['isHighlighted'])

            if 'thread' in related:
                post['t_date'] = str(post['t_date'])
                post['t_isDeleted'] = bool(post['t_isDeleted'])
                post['t_isClosed'] = bool(post['t_isClosed'])

                post['thread'] = {}
                keys = []
                for val in post:
                    if val[:2] == 't_':
                        post['thread'][val[2:]] = post[val]
                        keys.append(val)
                for key in keys:
                    del post[key]

            if 'forum' in related:
                post['forum'] = {}
                keys = []
                for val in post:
                    if val[:2] == 'f_':
                        post['forum'][val[2:]] = post[val]
                        keys.append(val)
                for key in keys:
                    del post[key]

            response.append(post)

        return Codes.OK, response
示例#15
0
文件: post.py 项目: dm-tyurin/tp_subd
    def details(post, related=None):
        if not related:
            related = []

        columns = 'p.id, p.message, p.date, p.isApproved, p.isHighlighted, p.isEdited, p.isSpam, ' \
                  'p.isDeleted, p.forum, p.thread, p.user, p.dislikes, p.likes, p.points, p.parent'
        if 'forum' in related:
            columns += ', f.name f_name, f.short_name f_short_name, f.user f_user, f.id f_id'
        if 'thread' in related:
            columns += ', t.id t_id, t.forum t_forum, t.title t_title, t.isClosed t_isClosed, ' \
                       't.user t_user, t.date t_date, t.message t_message, t.slug t_slug, t.isDeleted t_isDeleted, ' \
                       't.posts t_posts, t.likes t_likes, t.dislikes t_dislikes, t.points t_points'

        query = Query.SELECT.format(
            columns=columns,
            table=Tables.TABLE_NAMES['POST'] + ' as p'
        )
        if 'forum' in related:
            query += Query.JOIN.format(
                table=Tables.TABLE_NAMES['FORUM'] + ' as f',
                clause='p.forum = f.short_name'
            )
        if 'thread' in related:
            query += Query.JOIN.format(
                table=Tables.TABLE_NAMES['THREAD'] + ' as t',
                clause='p.thread = t.id'
            )
        query += Query.WHERE.format(
            clause='p.id = %s' % post
        )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            response = dictfetchall(cursor)[0]
        except Exception as e:
            if 'list index out of range' in str(e):
                return Codes.NOT_FOUND, 'Post %s not found' % post
            else:
                return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        response = Post.get_response_values(response)

        code = Codes.OK
        if 'thread' in related:
            response['t_date'] = str(response['t_date'])
            response['t_isDeleted'] = bool(response['t_isDeleted'])
            response['t_isClosed'] = bool(response['t_isClosed'])

            response['thread'] = {}
            keys = []
            for val in response:
                if val[:2] == 't_':
                    response['thread'][val[2:]] = response[val]
                    keys.append(val)
            for key in keys:
                del response[key]
        if 'forum' in related:
            response['forum'] = {}
            keys = []
            for val in response:
                if val[:2] == 'f_':
                    response['forum'][val[2:]] = response[val]
                    keys.append(val)
            for key in keys:
                del response[key]
        if 'user' in related:
            code, response['user'] = User.details(response['user'])

        return code, response
示例#16
0
文件: post.py 项目: dm-tyurin/tp_subd
    def list_by_forum(forum, request, related=None):
        if not related:
            related = []
        if 'order' not in request:
            request['order'] = 'DESC'

        columns = 'p.id, p.message, p.date, p.likes, p.dislikes, p.points, p.isApproved, ' \
                  'p.isHighlighted, p.isEdited, p.isSpam, p.isDeleted, p.forum, p.thread, p.user, p.parent'
        if 'forum' in related:
            columns += ', f.id f_id, f.name f_name, f.short_name f_short_name, f.user f_user'
        if 'thread' in related:
            columns += ', t.id t_id, t.title t_title, t.slug t_slug, t.message t_message, t.date t_date, ' \
                       't.posts t_posts, t.likes t_likes, t.dislikes t_dislikes, t.points t_points, ' \
                       't.isClosed t_isClosed, t.isDeleted t_isDeleted, t.forum t_forum, t.user t_user'

        query = Query.SELECT.format(
            columns=columns,
            table=Tables.TABLE_NAMES['POST'] + ' as p'
        )
        if 'forum' in related:
            query += Query.JOIN.format(
                table=Tables.TABLE_NAMES['FORUM'] + ' as f',
                clause='p.forum = f.short_name'
            )
        if 'thread' in related:
            query += Query.JOIN.format(
                table=Tables.TABLE_NAMES['THREAD'] + ' as t',
                clause='p.thread = t.id'
            )
        query += Query.WHERE.format(
            clause='p.forum = %r' % str(forum)
        )
        if 'since' in request:
            query += Query.AND_CLAUSE.format(
                clause='p.date > %r' % str(request['since'])
            )
        query += Query.ORDER_BY.format(
            column='p.date',
            type=request['order']
        )
        if 'limit' in request:
            query += Query.LIMIT.format(
                count=request['limit']
            )

        connection = db_connect.get_connection()
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            posts = dictfetchall(cursor)
        except Exception as e:
            return Codes.UNKNOWN_ERROR, str(e)
        finally:
            connection.close()

        response = []
        code = Codes.OK
        for post in posts:
            if 'forum' in related:
                post['forum'] = {}
                keys = []
                for val in post:
                    if val[:2] == 'f_':
                        post['forum'][val[2:]] = post[val]
                        keys.append(val)
                for key in keys:
                    del post[key]

            if 'thread' in related:
                post['thread'] = {}
                keys = []
                for val in post:
                    if val[:2] == 't_':
                        post['thread'][val[2:]] = post[val]
                        keys.append(val)
                for key in keys:
                    del post[key]

                post['thread'] = Thread.get_response_values(post['thread'])

            if 'user' in related:
                code, post['user'] = User.details(post['user'])

            response.append(Post.get_response_values(post))

        return code, response