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)
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)
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,
)
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)
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
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)
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'}
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)
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!'
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!'
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'}
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]
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]
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])
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
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])
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
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,
}
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)
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}
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
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'))
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)
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'))
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!')
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]
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]
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!')
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)
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
def tearDown(self):
with db.conn() as conn:
conn.execute("DROP TABLE app_{0}._user;".format(self.app_name))
def tearDown(self):
with db.conn() as conn:
conn.execute("DROP TABLE app_{0}._user;".format(self.app_name))
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)
def tearDown(self):
with db.conn() as conn:
conn.execute("DROP TABLE \"app_{0}\".note;".format(self.app_name))
def tearDown(self):
with db.conn() as conn:
conn.execute("DROP TABLE \"app_{0}\".note;".format(self.app_name))
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())
})
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)
