Exemple #1
0
    def __call__(self, auth_id, code_str):
        with conn() as c:
            code = get_verify_code(c, auth_id, code_str)
            if not code or code.consumed:
                msg = 'the code `{}` is not valid ' \
                      'for user `{}`'.format(code_str, auth_id)
                raise SkygearException(msg, skyerror.InvalidArgument)

        user_record = fetch_user_record(auth_id)
        if not user_record:
            msg = 'user `{}` not found'.format(auth_id)
            raise SkygearException(msg, skyerror.ResourceNotFound)

        if user_record.get(code.record_key) != code.record_value:
            msg = 'the user data has since been modified, ' \
                'a new verification is required'
            raise SkygearException(msg, skyerror.InvalidArgument)

        expiry = self.settings.keys[code.record_key].expiry
        if expiry:
            expire_at = code.created_at + datetime.timedelta(seconds=expiry)
            if expire_at < datetime.datetime.now():
                msg = 'the code has expired'
                raise SkygearException(msg, skyerror.InvalidArgument)

        with conn() as c:
            set_code_consumed(c, code.id)

        user_record[verified_flag_name(code.record_key)] = True
        save_user_record(user_record)
Exemple #2
0
    def __call__(self, auth_id, record_key):
        if self.is_valid_record_key(record_key):
            msg = 'record_key `{}` is not configured to verify'.format(
                record_key)
            raise SkygearException(msg, skyerror.InvalidArgument)

        with conn() as c:
            user = get_user(c, auth_id)
            if not user:
                msg = 'user `{}` not found'.format(auth_id)
                raise SkygearException(msg, skyerror.ResourceNotFound)

        user_record = fetch_user_record(auth_id)
        if not user_record:
            msg = 'user `{}` not found'.format(auth_id)
            raise SkygearException(msg, skyerror.ResourceNotFound)

        value_to_verify = user_record.get(record_key)
        if not value_to_verify:
            msg = 'there is nothing to verify for record_key `{}` ' \
                  'with auth_id `{}`'.format(record_key, auth_id)
            raise SkygearException(msg, skyerror.InvalidArgument)

        code_str = self.get_code(record_key)

        with conn() as c:
            add_verify_code(c, auth_id, record_key, value_to_verify, code_str)

            logger.info('Added new verify code `{}` for user `{}`.'.format(
                code_str, auth_id))
        self.call_provider(record_key, user, user_record, code_str)
Exemple #3
0
    def reindex_for_followees():
        with db.conn() as conn:
            my_user_id = skygear.utils.context.current_user_id()

            for record_type in SOCIAL_FEED_RECORD_TYPES:
                table_name = name_for_followings_relation_index(
                    prefix=SOCIAL_FEED_TABLE_PREFIX, record_type=record_type)

                remove_current_index_sql = sa.text('''
                    DELETE FROM {db_name}.{table_name}
                    WHERE left_id = :my_user_id
                '''.format(db_name=DB_NAME, table_name=table_name))
                conn.execute(remove_current_index_sql, my_user_id=my_user_id)

                create_my_friends_records_index_sql = sa.text('''
                    INSERT INTO {db_name}.{table_name} (
                        _id,
                        _database_id,
                        _owner_id,
                        _created_at,
                        _created_by,
                        _updated_at,
                        _updated_by,
                        _access,
                        left_id,
                        right_id,
                        record_ref
                    )
                    SELECT
                        uuid_generate_v4() as _id,
                        '' as _database_id,
                        :my_user_id as _owner_id,
                        current_timestamp as _created_at,
                        :my_user_id as _created_by,
                        current_timestamp as _updated_at,
                        :my_user_id as _updated_by,
                        '[]'::jsonb as _access,
                        :my_user_id as left_id,
                        _owner_id as right_id,
                        _id as record_ref
                    FROM {db_name}.{record_type} record_table
                    WHERE _owner_id in (
                        SELECT f.right_id as id
                        FROM {db_name}._follow f
                        WHERE f.left_id = :my_user_id
                    )
                    AND NOT EXISTS (
                        SELECT *
                        FROM {db_name}.{table_name}
                        WHERE left_id=:my_user_id
                        AND right_id IN (record_table._owner_id)
                        AND record_ref IN (record_table._id)
                    )
                '''.format(db_name=DB_NAME,
                           table_name=table_name,
                           record_type=record_type))
                conn.execute(
                    create_my_friends_records_index_sql,
                    my_user_id=my_user_id,
                )
Exemple #4
0
    def __call__(self, request):
        auth_id = request.values.get('auth_id')
        code_str = request.values.get('code')

        code = None
        try:
            if not auth_id:
                raise Exception('missing auth_id')

            if not code_str:
                raise Exception('missing code_str')

            with conn() as c:
                code = get_verify_code(c, auth_id, code_str)
                if not code:
                    raise Exception('code not found')

            thelambda = VerifyCodeLambda(self.settings)
            thelambda(auth_id, code_str)
            return self.response_success(code.record_key)

        except Exception as ex:
            logger.exception('error occurred fixme')
            record_key = code.record_key if code else None
            return self.response_error(record_key=record_key, error=ex)
Exemple #5
0
    def getReceiptList(self):
        """
        Returns a list of message receipt statuses.
        """
        receipts = list()
        with db.conn() as conn:
            cur = conn.execute(
                '''
                SELECT receipt.user, read_at, delivered_at
                FROM %(schema_name)s.receipt
                WHERE
                    "message" = %(message_id)s AND
                    (read_at IS NOT NULL or delivered_at is NOT NULL)
                ''', {
                    'schema_name': AsIs(_get_schema_name()),
                    'message_id': self.id.key
                })

            for row in cur:
                receipts.append({
                    'user':
                    row['user'],
                    'read_at':
                    to_rfc3339_or_none(row['read_at']),
                    'delivered_at':
                    to_rfc3339_or_none(row['delivered_at'])
                })
        return receipts
Exemple #6
0
def social_feed_init():
    container = SkygearContainer(api_key=options.masterkey)

    container.send_action(
        'schema:create', {
            'record_types': {
                'user': {
                    'fields': [
                        {
                            'name': 'name',
                            'type': 'string',
                        },
                        {
                            'name': 'social_feed_fanout_policy',
                            'type': 'json',
                        },
                        {
                            'name': 'social_feed_fanout_policy_is_dirty',
                            'type': 'boolean',
                        },
                    ]
                }
            }
        })

    for record_type in SOCIAL_FEED_RECORD_TYPES:
        create_table_for_social_feed(container, record_type)

    with db.conn() as conn:
        sql = 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'
        conn.execute(sql)
Exemple #7
0
    def reset_password(user_id, code, expire_at, new_password):
        """
        Lambda function to handle reset password request.
        """
        if not user_id:
            raise SkygearException('user_id must be set',
                                   skyerror.InvalidArgument)
        if not code:
            raise SkygearException('code must be set',
                                   skyerror.InvalidArgument)

        if not expire_at:
            raise SkygearException('expire_at must be set',
                                   skyerror.InvalidArgument)

        with conn() as c:
            user = user_util.get_user_and_validate_code(
                c, user_id, code, expire_at)
            if not user:
                raise SkygearException('user_id is not found or code invalid',
                                       skyerror.ResourceNotFound)

            if not user.email:
                raise SkygearException('email must be set',
                                       skyerror.ResourceNotFound)

            user_util.set_new_password(c, user.id, new_password)
            logger.info('Successfully reset password for user.')

            return {'status': 'OK'}
Exemple #8
0
    def reset_password_form_handler(request):
        """
        A handler for reset password requests.
        """
        with conn() as c:
            try:
                params = get_validated_request_parameters(c, request)
            except IllegalArgumentError:
                return response_params_error(template_provider, settings)

            template_params = {
                'user': params.user,
                'user_record': params.user_record,
                'code': params.code,
                'user_id': params.user_id,
                'expire_at': params.expire_at,
            }

            if request.method != 'POST':
                return response_form(template_provider, **template_params)

            # Handle form submission
            try:
                password = get_validated_password(request)
            except IllegalArgumentError as ex:
                return response_error(template_provider,
                                      settings,
                                      error=str(ex),
                                      **template_params)

            user_util.set_new_password(c, params.user.id, password)
            logger.info('Successfully reset password for user.')

            return response_success(template_provider, settings,
                                    **template_params)
Exemple #9
0
 def test_correct_db_context(self):
     with db.conn() as conn:
         result = conn.execute("""
             SELECT id, content FROM note
         """)
         r = result.fetchone()
         assert r['id'] == 'first'
         assert r['content'] == 'Hello World!'
Exemple #10
0
 def test_correct_db_context(self):
     with db.conn() as conn:
         result = conn.execute("""
             SELECT id, content FROM note
         """)
         r = result.fetchone()
         assert r['id'] == 'first'
         assert r['content'] == 'Hello World!'
Exemple #11
0
    def forgot_password(email):
        """
        Lambda function to handle forgot password request.
        """

        if email is None:
            raise SkygearException('email must be set',
                                   skyerror.InvalidArgument)

        with conn() as c:
            user = user_util.get_user_from_email(c, email)
            if not user:
                if not settings.secure_match:
                    return {'status': 'OK'}
                raise SkygearException('user_id must be set',
                                       skyerror.InvalidArgument)
            if not user.email:
                raise SkygearException('email must be set',
                                       skyerror.InvalidArgument)

            user_record = user_util.get_user_record(c, user.id)
            expire_at = round(datetime.utcnow().timestamp()) + \
                settings.reset_url_lifetime
            code = user_util.generate_code(user, expire_at)

            url_prefix = settings.url_prefix
            if url_prefix.endswith('/'):
                url_prefix = url_prefix[:-1]

            link = '{0}/reset-password?code={1}&user_id={2}&expire_at={3}'\
                .format(url_prefix, code, user.id, expire_at)

            template_params = {
                'appname': settings.app_name,
                'link': link,
                'url_prefix': url_prefix,
                'email': user.email,
                'user_id': user.id,
                'code': code,
                'user': user,
                'user_record': user_record,
                'expire_at': expire_at,
            }

            try:
                mail_sender.send(
                    (settings.sender_name, settings.sender),
                    user.email,
                    settings.subject,
                    reply_to=(settings.reply_to_name, settings.reply_to),
                    template_params=template_params)
            except Exception as ex:
                logger.exception('An error occurred sending reset password'
                                 ' email to user.')
                raise SkygearException(str(ex), skyerror.UnexpectedError)

            return {'status': 'OK'}
Exemple #12
0
def get_user_by_username(fb_id):
    with conn() as c:
        users = get_table('_user')
        stmt = select([users.c.id]) \
            .where(users.c.username == fb_id)
        r = c.execute(stmt).fetchone()
        if r is None:
            return None
        else:
            return r[0]
Exemple #13
0
def get_conversation_by_uid(uid):
    with conn() as c:
        conversation = get_table('conversation')
        stmt = select([conversation.c._id]) \
            .where(conversation.c._created_by == uid)
        r = c.execute(stmt).fetchone()
        if r is None:
            return None
        else:
            return r[0]
Exemple #14
0
    def test_reset_password(self):
        done = u.reset_password_by_username("USER_1", PLAINTEXT)
        assert done

        with db.conn() as conn:
            result = conn.execute(text("""
                SELECT password
                FROM app_{0}._user
                WHERE username=:username
                """.format(self.app_name)),
                                  username='******')
            r = result.fetchone()
            assert_correct_pw(PLAINTEXT, r[0])
Exemple #15
0
def _check_if_table_exists(tablename):
    with db.conn() as conn:
        cur = conn.execute('''
            SELECT to_regclass(%(name)s)
            ''', {
            'name': _get_schema_name() + "." + tablename,
        })
        results = []
        for row in cur:
            if row[0] is not None:
                results.append(row[0])

        return len(results) > 0
Exemple #16
0
    def test_reset_password(self):
        done = u.reset_password_by_username("USER_1", PLAINTEXT)
        assert done

        with db.conn() as conn:
            result = conn.execute(text("""
                SELECT password
                FROM app_{0}._user
                WHERE username=:username
                """.format(self.app_name)),
                username='******')
            r = result.fetchone()
            assert_correct_pw(PLAINTEXT, r[0])
Exemple #17
0
def _check_if_table_exists(tablename):
    with db.conn() as conn:
        cur = conn.execute('''
            SELECT to_regclass(%(name)s)
            ''', {
            'name': _get_schema_name() + "." + tablename,
        })
        results = []
        for row in cur:
            if row[0] is not None:
                results.append(row[0])

        return len(results) > 0
Exemple #18
0
    def get_user_fanout_policy():
        with db.conn() as conn:
            my_user_id = skygear.utils.context.current_user_id()
            get_user_fanout_policy_sql = sa.text('''
                SELECT social_feed_fanout_policy as fanout_policy
                FROM {db_name}.user
                WHERE _id=:user_id
            '''.format(db_name=DB_NAME))
            fanout_policy = conn.execute(
                get_user_fanout_policy_sql,
                user_id=my_user_id).scalar() or SOCIAL_FEED_FANOUT_POLICY

        return {
            'fanout_policy': fanout_policy,
        }
Exemple #19
0
 def update_index_if_fanout_policy_change():
     with db.conn() as conn:
         for record_type in SOCIAL_FEED_RECORD_TYPES:
             remove_relation_index_if_fanout_policy_change_to_false(
                 conn=conn, relation='friends', record_type=record_type)
             remove_relation_index_if_fanout_policy_change_to_false(
                 conn=conn, relation='following', record_type=record_type)
             reindex_relation_index_if_fanout_policy_change_to_true(
                 conn=conn,
                 relation='friends',
                 relation_direction=DIRECTION_MUTUAL,
                 record_type=record_type)
             reindex_relation_index_if_fanout_policy_change_to_true(
                 conn=conn,
                 relation='following',
                 relation_direction=DIRECTION_OUTWARD,
                 record_type=record_type)
         reset_social_feed_fanout_policy_is_dirty_flag(conn)
Exemple #20
0
def total_unread(user_id=None):
    if user_id is None:
        user_id = current_user_id()
    with db.conn() as conn:
        cur = conn.execute(
            '''
            SELECT COUNT(*), SUM("unread_count")
            FROM %(schema_name)s.user_conversation
            WHERE
                "unread_count" > 0 AND
                "user" = %(user_id)s
            ''', {
                'schema_name': AsIs(_get_schema_name()),
                'user_id': user_id
            })
        r = cur.first()
        conversation_count = r[0]
        message_count = r[1]
    return {'conversation': conversation_count, 'message': message_count}
Exemple #21
0
def _get_channels_by_user_ids(user_ids):
    # TODO: use database.query instead of raw SQL
    with db.conn() as conn:
        cur = conn.execute(
            '''
            SELECT name
            FROM %(schema_name)s.user_channel
            WHERE _owner_id in %(user_ids)s
            LIMIT %(len)s;
            ''', {
                'schema_name': AsIs(_get_schema_name()),
                'user_ids': tuple(user_ids),
                'len': len(user_ids),
            })

        results = []
        for row in cur:
            results.append(row[0])

        return results
Exemple #22
0
 def setUp(self):
     SkygearContainer.set_default_app_name(self.app_name)
     with db.conn() as conn:
         conn.execute("CREATE SCHEMA IF NOT EXISTS app_{0}"
                      .format(self.app_name))
         conn.execute("""
             CREATE TABLE IF NOT EXISTS _user (
                 id text PRIMARY KEY,
                 username text,
                 email text,
                 password text,
                 auth jsonb
             );""")
         sql = text("""
             INSERT INTO _user (id, username, password)
             VALUES (:id, :username, :password);
             """)
         conn.execute(sql,
                      id='1',
                      username='******',
                      password=u.hash_password('supersecret1'))
Exemple #23
0
    def remove_index_for_followees(followees):
        if len(followees) <= 0:
            return

        with db.conn() as conn:
            my_user_id = skygear.utils.context.current_user_id()
            my_followees_ids = [followee['user_id'] for followee in followees]
            my_followees_ids_tuple = tuple(my_followees_ids)

            for record_type in SOCIAL_FEED_RECORD_TYPES:
                table_name = name_for_followings_relation_index(
                    prefix=SOCIAL_FEED_TABLE_PREFIX, record_type=record_type)

                remove_my_friends_records_sql = sa.text('''
                    DELETE from {db_name}.{table_name}
                    WHERE left_id = :my_user_id
                    AND right_id in :my_followees_ids
                '''.format(db_name=DB_NAME, table_name=table_name))
                conn.execute(remove_my_friends_records_sql,
                             my_user_id=my_user_id,
                             my_followees_ids=my_followees_ids_tuple)
Exemple #24
0
 def setUp(self):
     SkygearContainer.set_default_app_name(self.app_name)
     with db.conn() as conn:
         conn.execute("CREATE SCHEMA IF NOT EXISTS app_{0}".format(
             self.app_name))
         conn.execute("""
             CREATE TABLE IF NOT EXISTS _user (
                 id text PRIMARY KEY,
                 username text,
                 email text,
                 password text,
                 auth jsonb
             );""")
         sql = text("""
             INSERT INTO _user (id, username, password)
             VALUES (:id, :username, :password);
             """)
         conn.execute(sql,
                      id='1',
                      username='******',
                      password=u.hash_password('supersecret1'))
Exemple #25
0
 def setUp(self):
     SkygearContainer.set_default_app_name(self.app_name)
     with db.conn() as conn:
         conn.execute("CREATE SCHEMA IF NOT EXISTS \"app_{0}\""
                      .format(self.app_name))
         conn.execute(
             "set search_path to \"app_{0}\", public;".format(
                 self.app_name
             )
         )
         conn.execute("""
             CREATE TABLE IF NOT EXISTS note (
                 id text PRIMARY KEY,
                 content text
             );""")
         sql = text("""
             INSERT INTO note (id, content)
             VALUES (:id, :content);
             """)
         conn.execute(sql,
                      id='first',
                      content='Hello World!')
Exemple #26
0
def _get_channel_by_user_id(user_id):
    if not _check_if_table_exists('user_channel'):
        return None

    with db.conn() as conn:
        cur = conn.execute(
            '''
            SELECT name
            FROM %(schema_name)s.user_channel
            WHERE _owner_id = %(user_id)s
            LIMIT 1;
            ''', {
                'schema_name': AsIs(_get_schema_name()),
                'user_id': user_id
            })

        results = []
        for row in cur:
            results.append(row[0])

        if len(results) > 0:
            return results[0]
Exemple #27
0
def _get_channel_by_user_id(user_id):
    if not _check_if_table_exists('user_channel'):
        return None

    with db.conn() as conn:
        cur = conn.execute('''
            SELECT name
            FROM %(schema_name)s.user_channel
            WHERE _owner_id = %(user_id)s
            LIMIT 1;
            ''', {
            'schema_name': AsIs(_get_schema_name()),
            'user_id': user_id
        }
        )

        results = []
        for row in cur:
            results.append(row[0])

        if len(results) > 0:
            return results[0]
Exemple #28
0
 def setUp(self):
     SkygearContainer.set_default_app_name(self.app_name)
     with db.conn() as conn:
         conn.execute("CREATE SCHEMA IF NOT EXISTS \"app_{0}\""
                      .format(self.app_name))
         conn.execute(
             "set search_path to \"app_{0}\", public;".format(
                 self.app_name
             )
         )
         conn.execute("""
             CREATE TABLE IF NOT EXISTS note (
                 id text PRIMARY KEY,
                 content text
             );""")
         sql = text("""
             INSERT INTO note (id, content)
             VALUES (:id, :content);
             """)
         conn.execute(sql,
                      id='first',
                      content='Hello World!')
Exemple #29
0
    def set_enable_fanout_to_relation(relation, enable):
        with db.conn() as conn:
            my_user_id = skygear.utils.context.current_user_id()

            get_user_fanout_policy_sql = sa.text('''
                SELECT social_feed_fanout_policy as fanout_policy
                FROM {db_name}.user
                WHERE _id=:user_id
            '''.format(db_name=DB_NAME))
            fanout_policy = conn.execute(get_user_fanout_policy_sql,
                                         user_id=my_user_id).scalar() or {}
            fanout_policy[relation] = enable

            update_user_fanout_policy_sql = sa.text('''
                UPDATE {db_name}.user
                SET social_feed_fanout_policy_is_dirty = TRUE,
                social_feed_fanout_policy = :fanout_policy ::jsonb
                WHERE _id = :user_id
            '''.format(db_name=DB_NAME))
            conn.execute(update_user_fanout_policy_sql,
                         fanout_policy=json.dumps(fanout_policy),
                         user_id=my_user_id)
Exemple #30
0
    def getReceiptList(self):
        """
        Returns a list of message receipt statuses.
        """
        receipts = list()
        with db.conn() as conn:
            cur = conn.execute('''
                SELECT user_id, read_at, delivered_at
                FROM %(schema_name)s.receipt
                WHERE
                    "message_id" = %(message_id)s
                ''', {
                    'schema_name': AsIs(_get_schema_name()),
                    'message_id': self.record.id.key
                }
            )

            for row in cur:
                receipts.append({
                    'user_id': row['user_id'],
                    'read_at': to_rfc3339_or_none(row['read_at']),
                    'delivered_at': to_rfc3339_or_none(row['delivered_at'])
                })
        return receipts
Exemple #31
0
 def tearDown(self):
     with db.conn() as conn:
         conn.execute("DROP TABLE app_{0}._user;".format(self.app_name))
Exemple #32
0
 def tearDown(self):
     with db.conn() as conn:
         conn.execute("DROP TABLE app_{0}._user;".format(self.app_name))
Exemple #33
0
    def social_feed_create_index_for_friends(maybe_my_friends):
        if not maybe_my_friends:
            return

        with db.conn() as conn:
            my_user_id = skygear.utils.context.current_user_id()
            maybe_my_friend_ids = [
                user['user_id'] for user in maybe_my_friends
            ]
            maybe_my_friend_ids_tuple = tuple(maybe_my_friend_ids)

            sql = sa.text('''
                SELECT f1.right_id as id
                FROM {db_name}._friend f1
                JOIN {db_name}._friend f2
                ON f1.right_id = f2.left_id
                WHERE f1.left_id = :my_user_id
                AND f2.right_id = :my_user_id
                AND f1.right_id IN :maybe_my_friend_ids
            '''.format(db_name=DB_NAME))
            results = conn.execute(
                sql,
                my_user_id=my_user_id,
                maybe_my_friend_ids=maybe_my_friend_ids_tuple)
            my_friend_ids = [user.id for user in results]
            if not my_friend_ids:
                return
            my_friend_ids_tuple = tuple(my_friend_ids)

            should_fanout_my_records = should_record_be_indexed(
                DB_NAME, SOCIAL_FEED_RECORD_TYPES, conn, my_user_id, 'friends')

            for record_type in SOCIAL_FEED_RECORD_TYPES:
                table_name = name_for_friends_relation_index(
                    prefix=SOCIAL_FEED_TABLE_PREFIX, record_type=record_type)

                create_my_friends_records_index_sql = sa.text('''
                    INSERT INTO {db_name}.{table_name} (
                        _id,
                        _database_id,
                        _owner_id,
                        _created_at,
                        _created_by,
                        _updated_at,
                        _updated_by,
                        _access,
                        left_id,
                        right_id,
                        record_ref
                    )
                    SELECT
                        uuid_generate_v4() as _id,
                        '' as _database_id,
                        :my_user_id as _owner_id,
                        current_timestamp as _created_at,
                        :my_user_id as _created_by,
                        current_timestamp as _updated_at,
                        :my_user_id as _updated_by,
                        '[]'::jsonb as _access,
                        :my_user_id as left_id,
                        record_table._owner_id as right_id,
                        record_table._id as record_ref
                    FROM {db_name}.{record_type} record_table
                    JOIN {db_name}.user user_table
                    ON (
                        record_table._owner_id = user_table._id
                        AND COALESCE(
                                user_table.social_feed_fanout_policy,
                                '{default_fanout_policy}'::jsonb
                            ) @> '{req_fanout_policy}'::jsonb
                    )
                    WHERE record_table._owner_id in :my_friend_ids
                    AND NOT EXISTS (
                        SELECT *
                        FROM {db_name}.{table_name}
                        WHERE left_id=:my_user_id
                        AND right_id IN (record_table._owner_id)
                        AND record_ref IN (record_table._id)
                    )
                '''.format(
                    db_name=DB_NAME,
                    table_name=table_name,
                    record_type=record_type,
                    default_fanout_policy=SOCIAL_FEED_FANOUT_POLICY_JSON_STR,
                    req_fanout_policy='{"friends": true}'))
                conn.execute(create_my_friends_records_index_sql,
                             my_user_id=my_user_id,
                             my_friend_ids=my_friend_ids_tuple)

                if should_fanout_my_records:
                    create_friends_to_my_records_index_sql = sa.text('''
                        INSERT INTO {db_name}.{table_name} (
                            _id,
                            _database_id,
                            _owner_id,
                            _created_at,
                            _created_by,
                            _updated_at,
                            _updated_by,
                            _access,
                            left_id,
                            right_id,
                            record_ref
                        )
                        SELECT
                            uuid_generate_v4() as _id,
                            '' as _database_id,
                            u.id as _owner_id,
                            current_timestamp as _created_at,
                            u.id as _created_by,
                            current_timestamp as _updated_at,
                            u.id as _updated_by,
                            '[]'::jsonb as _access,
                            u.id as left_id,
                            :my_user_id as right_id,
                            record_table._id as record_ref
                        FROM {db_name}.{record_type} record_table,
                             {db_name}._user u
                        WHERE record_table._owner_id = :my_user_id
                        AND u.id in :my_friend_ids
                        AND NOT EXISTS (
                            SELECT *
                            FROM {db_name}.{table_name}
                            WHERE right_id = :my_user_id
                            AND left_id IN :my_friend_ids
                            AND record_ref IN (record_table._id)
                        )
                    '''.format(db_name=DB_NAME,
                               table_name=table_name,
                               record_type=record_type))
                    conn.execute(create_friends_to_my_records_index_sql,
                                 my_user_id=my_user_id,
                                 my_friend_ids=my_friend_ids_tuple)
Exemple #34
0
 def tearDown(self):
     with db.conn() as conn:
         conn.execute("DROP TABLE \"app_{0}\".note;".format(self.app_name))
Exemple #35
0
 def tearDown(self):
     with db.conn() as conn:
         conn.execute("DROP TABLE \"app_{0}\".note;".format(self.app_name))
Exemple #36
0
    def chat_plugin_init(config):
        container = SkygearContainer(api_key=skyoptions.masterkey)
        schema_helper = SchemaHelper(container)
        # We need this to provision the record type. Otherwise, make the follow
        # up `ref` type will fails.
        schema_helper.create([
            Schema('user', []),
            Schema('message', []),
            Schema('conversation', [])
        ], plugin_request=True)

        conversation_schema = Schema('conversation',
                                     [Field('title', 'string'),
                                      Field('metadata', 'json'),
                                      Field('deleted', 'boolean'),
                                      Field('distinct_by_participants',
                                            'boolean'),
                                      Field('last_message', 'ref(message)')])
        user_schema = Schema('user', [Field('name', 'string')])
        user_conversation_schema = Schema('user_conversation',
                                          [Field('user', 'ref(user)'),
                                           Field('conversation',
                                                 'ref(conversation)'),
                                           Field('unread_count',
                                                 'number'),
                                           Field('last_read_message',
                                                 'ref(message)'),
                                           Field('is_admin',
                                                 'boolean')])
        receipt_schema = Schema('receipt',
                                [Field('user', 'ref(user)'),
                                 Field('message', 'ref(message)'),
                                 Field('read_at', 'datetime'),
                                 Field('delivered_at', 'datetime')])
        user_channel_schema = Schema('user_channel',
                                     [Field('name', 'string')])
        message_schema = _message_schema()
        message_history_schema = _message_history_schema()
        schema_helper.create([user_schema,
                              user_conversation_schema,
                              conversation_schema,
                              message_schema,
                              message_history_schema,
                              receipt_schema,
                              user_channel_schema],
                             plugin_request=True)

        # Create unique constraint to _database_id in user_channel table
        # to ensure there is only one user_channel for each user
        with db.conn() as conn:
            result = conn.execute("""
                select 1
                    FROM information_schema.constraint_column_usage
                WHERE table_schema = '%(schema_name)s'
                    AND table_name = 'user_channel'
                    AND constraint_name = 'user_channel_database_id_key'
                """, {
                    'schema_name': AsIs(_get_schema_name())
                })
            first_row = result.first()
            if first_row is None:
                conn.execute("""
                    DELETE
                        FROM %(schema_name)s.user_channel
                    WHERE _id IN (
                        SELECT _id
                        FROM (
                            SELECT
                                _id,
                                ROW_NUMBER() OVER(
                                    PARTITION BY
                                    _database_id ORDER BY _created_at
                                ) AS row_num
                            FROM  %(schema_name)s.user_channel
                        ) u2 WHERE u2.row_num > 1
                    )
                """, {
                    'schema_name': AsIs(_get_schema_name())
                })
                conn.execute("""
                    ALTER TABLE %(schema_name)s.user_channel
                        ADD CONSTRAINT user_channel_database_id_key
                            UNIQUE (_database_id);
                """, {
                    'schema_name': AsIs(_get_schema_name())
                })
Exemple #37
0
    def create_index_for_followee(followees):
        if not followees:
            return

        with db.conn() as conn:
            my_user_id = skygear.utils.context.current_user_id()
            my_followees_ids = [followee['user_id'] for followee in followees]
            my_followees_ids_tuple = tuple(my_followees_ids)

            for record_type in SOCIAL_FEED_RECORD_TYPES:
                table_name = name_for_followings_relation_index(
                    prefix=SOCIAL_FEED_TABLE_PREFIX, record_type=record_type)

                create_my_followees_records_index_sql = sa.text('''
                    INSERT INTO {db_name}.{table_name} (
                        _id,
                        _database_id,
                        _owner_id,
                        _created_at,
                        _created_by,
                        _updated_at,
                        _updated_by,
                        _access,
                        left_id,
                        right_id,
                        record_ref
                    )
                    SELECT
                        uuid_generate_v4() as _id,
                        '' as _database_id,
                        :my_user_id as _owner_id,
                        current_timestamp as _created_at,
                        :my_user_id as _created_by,
                        current_timestamp as _updated_at,
                        :my_user_id as _updated_by,
                        '[]'::jsonb as _access,
                        :my_user_id as left_id,
                        record_table._owner_id as right_id,
                        record_table._id as record_ref
                    FROM {db_name}.{record_type} record_table
                    JOIN {db_name}.user user_table
                    ON (
                        record_table._owner_id = user_table._id
                        AND COALESCE(
                                user_table.social_feed_fanout_policy,
                                '{default_fanout_policy}'::jsonb
                            ) @> '{req_fanout_policy}'::jsonb
                    )
                    WHERE record_table._owner_id in :my_followees_ids
                    AND NOT EXISTS (
                        SELECT *
                        FROM {db_name}.{table_name}
                        WHERE left_id=:my_user_id
                        AND right_id IN (record_table._owner_id)
                        AND record_ref IN (record_table._id)
                    )
                '''.format(
                    db_name=DB_NAME,
                    table_name=table_name,
                    record_type=record_type,
                    default_fanout_policy=SOCIAL_FEED_FANOUT_POLICY_JSON_STR,
                    req_fanout_policy='{"following": true}'))
                conn.execute(create_my_followees_records_index_sql,
                             my_user_id=my_user_id,
                             my_followees_ids=my_followees_ids_tuple)