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 _get_conversation(conversation_id):
# conversation_id can be Reference, recordID or string
if isinstance(conversation_id, Reference):
conversation_id = conversation_id.recordID.key
if isinstance(conversation_id, RecordID):
conversation_id = conversation_id.key
container = SkygearContainer(api_key=skygear_config.app.api_key)
response = container.send_action(
'record:query', {
'database_id':
'_public',
'record_type':
'conversation',
'limit':
1,
'sort': [],
'include': {},
'count':
False,
'predicate':
['eq', {
'$type': 'keypath',
'$val': '_id'
}, conversation_id]
})
if 'error' in response:
raise SkygearChatException(response['error'])
if len(response['result']) == 0:
raise SkygearChatException("no conversation found")
return response['result'][0]
def _get_conversation(conversation_id):
# conversation_id can be Reference, recordID or string
if isinstance(conversation_id, Reference):
conversation_id = conversation_id.recordID.key
if isinstance(conversation_id, RecordID):
conversation_id = conversation_id.key
container = SkygearContainer(api_key=skyoptions.apikey)
response = container.send_action(
'record:query',
{
'database_id': '_public',
'record_type': 'conversation',
'limit': 1,
'sort': [],
'include': {},
'count': False,
'predicate': [
'eq', {
'$type': 'keypath',
'$val': '_id'
},
conversation_id
]
}
)
if 'error' in response:
raise SkygearChatException(response['error'])
if len(response['result']) == 0:
raise SkygearChatException("no conversation found")
return response['result'][0]
def _send_multi(self, action, **payload):
token = self._access_token()
container = SkygearContainer(access_token=token)
result = container.send_action(action, payload)
if 'error' in result:
raise SkygearException.from_dict(result['error'])
elif 'result' in result and isinstance(result['result'], list):
return result
else:
raise SkygearException('unexpected result', UnexpectedError)
def signup(user_id):
container = SkygearContainer(
api_key=os.getenv('API_KEY')
)
pw = hashlib.sha1(bytes(user_id + 'Aih2xoopho', 'utf8')).hexdigest()
result = container.send_action('auth:signup', {
'username': user_id,
'password': pw
})
log.info(result)
return result['id']
def save(self) -> None:
"""
Save the Message record to the database.
"""
container = SkygearContainer(api_key=skyoptions.masterkey,
user_id=current_user_id())
container.send_action('record:save', {
'database_id': '_public',
'records': [serialize_record(self.record)],
'atomic': True
})
def save_user_record(user_record):
"""
Save the user record to Skygear Record API.
"""
container = SkygearContainer(api_key=skyoptions.masterkey)
resp = container.send_action("record:save",
{"records": [serialize_record(user_record)]},
plugin_request=True)
try:
if "error" in resp:
raise SkygearException.from_dict(resp["error"])
except (ValueError, TypeError, KeyError):
raise SkygearContainer("container.send_action is buggy")
def _send_single(self, action, **payload):
token = self._access_token()
container = SkygearContainer(access_token=token)
result = container.send_action(action, payload)
if 'error' in result:
raise SkygearException.from_dict(result['error'])
elif 'result' in result and isinstance(result['result'], list) \
and len(result['result']) > 0:
first_result = result['result'][0]
if first_result.get('_type', None) == 'error':
raise SkygearException.from_dict(first_result)
return first_result
else:
raise SkygearException('unexpected result', UnexpectedError)
def create_message(cid, uid, body):
container = SkygearContainer(
api_key=os.getenv('MASTER_KEY'),
user_id=uid
)
result = container.send_action('record:save', {
'records': [{
'_id': 'message/' + uuid.uuid4().urn[9:],
'conversation_id': cid,
'body': body
}],
'database_id': '_private'
})
log.info(result)
return result['result'][0]['_id']
def create_conversation(uid):
container = SkygearContainer(
api_key=os.getenv('MASTER_KEY'),
user_id=uid
)
result = container.send_action('record:save', {
'records': [{
'_id': 'conversation/' + uid + CHAT_BOT_ID,
'participant_ids': [uid, CHAT_BOT_ID],
'is_direct_message': True
}],
'database_id': '_public'
})
log.info(result)
return result['result'][0]['_id']
def fetch_user_record(auth_id):
"""
Fetch the user record from Skygear Record API. The returned value
is a user record in Record class.
"""
container = SkygearContainer(api_key=skyoptions.masterkey)
resp = container.send_action("record:fetch",
{"ids": ['user/{}'.format(auth_id)]},
plugin_request=True)
try:
if "error" in resp:
raise SkygearException.from_dict(resp["error"])
except (ValueError, TypeError, KeyError):
raise SkygearContainer("container.send_action is buggy")
return deserialize_record(resp['result'][0])
def set_new_password(user_id, new_password):
"""
Set the password of a user to a new password
with auth:reset_password
"""
container = SkygearContainer(api_key=skyoptions.masterkey)
resp = container.send_action("auth:reset_password", {
"auth_id": user_id,
"password": new_password,
},
plugin_request=True)
try:
if "error" in resp:
raise SkygearException.from_dict(resp["error"])
except (ValueError, TypeError, KeyError):
raise SkygearContainer("container.send_action is buggy")
def chat_plugin_init():
container = SkygearContainer(api_key=options.masterkey)
container.send_action("schema:create", {"record_types": {"user": {"fields": [{"name": "name", "type": "string"}]}}})
container.send_action(
"schema:create",
{
"record_types": {
"conversation": {
"fields": [
{"name": "title", "type": "string"},
{"name": "admin_ids", "type": "json"},
{"name": "participant_ids", "type": "json"},
{"name": "is_direct_message", "type": "boolean"},
]
}
}
},
)
container.send_action(
"schema:create",
{
"record_types": {
"message": {
"fields": [
{"name": "attachment", "type": "asset"},
{"name": "body", "type": "string"},
{"name": "metadata", "type": "json"},
{"name": "conversation_id", "type": "ref(conversation)"},
]
}
}
},
)
container.send_action(
"schema:create",
{
"record_types": {
"user_conversation": {
"fields": [
{"name": "user", "type": "ref(user)"},
{"name": "conversation", "type": "ref(conversation)"},
{"name": "unread_count", "type": "number"},
{"name": "last_read_message", "type": "ref(message)"},
]
}
}
},
)
def schema_add_key_verified_acl(flag_names):
"""
Add field ACL to disallow owner from modifying verified flags.
"""
container = SkygearContainer(api_key=skyoptions.masterkey)
# Fetch the current field ACL. If no changes are required, we will
# not try to update the field ACL.
resp = container.send_action("schema:field_access:get", {},
plugin_request=True)
if "error" in resp:
raise SkygearException.from_dict(resp["error"])
# Create the new ACL settings. This is accomplished by first
# copying the existing field ACLs, ignoring the entries we need
# to enforce.
new_acls = []
for acl in resp['result']['access']:
if acl['record_type'] == 'user' \
and acl['record_field'] in flag_names \
and acl['user_role'] == '_owner':
continue
new_acls.append(acl)
for flag_name in flag_names:
new_acls.append({
'record_type': 'user',
'record_field': flag_name,
'user_role': '_owner',
'writable': False,
'readable': True,
'comparable': True,
'discoverable': True,
})
if not new_acls:
return
# Update the field ACL.
resp = container.send_action("schema:field_access:update",
{"access": new_acls},
plugin_request=True)
if "error" in resp:
raise SkygearException.from_dict(resp["error"])
def save(self) -> None:
"""
Save the collection of receipts to the database. This function
does nothing if there is nothing in the collection.
"""
if not len(self):
return
records_to_save = [
serialize_record(receipt.record)
for receipt in self
]
container = SkygearContainer(api_key=skyoptions.masterkey,
user_id=current_user_id())
container.send_action('record:save', {
'database_id': '_public',
'records': records_to_save,
'atomic': True
})
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("""
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 get_token():
global skygear_token
if skygear_token is not None:
return skygear_token
container = SkygearContainer()
try:
resp = container.send_action('auth:login', {
'username': os.getenv('SKYGEAR_BOT'),
'password': os.getenv('SKYGEAR_BOT_PW')
})
except SkygearException:
log.debug('New setup, registering')
resp = container.send_action('auth:signup', {
'username': os.getenv('SKYGEAR_BOT'),
'password': os.getenv('SKYGEAR_BOT_PW')
})
finally:
log.debug(resp)
skygear_token = resp['result']['access_token']
return skygear_token
def schema_add_key_verified_flags(flag_names):
"""
Add the fields into Skygear DB user record.
This function adds the specified fields into the user record schema. It
is expected that all fields have boolean data type.
"""
if not flag_names:
return
container = SkygearContainer(api_key=skyoptions.masterkey)
# Fetch schema first. If no changes are required, we will not try
# to update the schema.
resp = container.send_action("schema:fetch", {}, plugin_request=True)
if "error" in resp:
raise SkygearException.from_dict(resp["error"])
for field in resp['result']['record_types']['user']['fields']:
if field['name'] in flag_names:
flag_names.remove(field['name'])
# `flag_names` now contain the list of fields to create. Proceed
# to create the field list and save the new fields to user record
# schema.
fields = [{
'name': flag_name,
'type': 'boolean'
} for flag_name in flag_names]
resp = container.send_action(
"schema:create", {"record_types": {
'user': {
'fields': fields
}
}},
plugin_request=True)
if "error" in resp:
raise SkygearException.from_dict(resp["error"])
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 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 __init__(self, user):
self.what = 'chat'
self.step = 0
self.user = user
self.container = SkygearContainer(access_token=get_token())
response = self.container.send_action('record:query', {
'record_type': 'fb_user',
'predicate': [
'eq',
{'$type': 'keypath', '$val': '_id'},
user
]
})
log.debug(response)
result = response['result']
try:
user = result[0]
except IndexError:
return
else:
if user:
self.step = user['step']
self.what = user['enquiry_topic']
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)
import os
from psycopg2.extensions import AsIs
from skygear.container import SkygearContainer
from skygear.models import RecordID, Reference
from skygear.options import options
from skygear.utils import db
from .exc import SkygearChatException
container = SkygearContainer()
opts = vars(options)
container.api_key = os.getenv('API_KEY', opts.get('apikey'))
container.app_name = os.getenv('APP_NAME', opts.get('appname'))
schema_name = "app_%s" % container.app_name
MASTER_KEY = os.getenv('MASTER_KEY', opts.get('masterkey'))
def _get_conversation(conversation_id):
# conversation_id can be Reference, recordID or string
if isinstance(conversation_id, Reference):
conversation_id = conversation_id.recordID.key
if isinstance(conversation_id, RecordID):
conversation_id = conversation_id.key
data = {
'database_id': '_public',
'record_type': 'conversation',
'limit': 1,
'sort': [],
def _get_container():
return SkygearContainer(api_key=skyoptions.masterkey,
user_id=current_user_id())
def container(self):
token = self._access_token()
container = SkygearContainer(access_token=token)
return container
def chat_plugin_init(config):
container = SkygearContainer(api_key=skyoptions.masterkey)
container.send_action('schema:create', {
'record_types': {
'user': {
'fields': [{
'name': 'name',
'type': 'string'
}]
},
'conversation': {
'fields': [{
'name': 'title',
'type': 'string'
}, {
'name': 'admin_ids',
'type': 'json'
}, {
'name': 'participant_ids',
'type': 'json'
}, {
'name': 'participant_count',
'type': 'number'
}, {
'name': 'metadata',
'type': 'json'
}, {
'name': 'distinct_by_participants',
'type': 'boolean'
}]
}
}
},
plugin_request=True)
container.send_action('schema:create', {
'record_types': {
'message': {
'fields': [{
'name': 'attachment',
'type': 'asset'
}, {
'name': 'body',
'type': 'string'
}, {
'name': 'metadata',
'type': 'json'
}, {
'name': 'conversation_id',
'type': 'ref(conversation)'
}, {
'name': 'conversation_status',
'type': 'string'
}]
}
}
},
plugin_request=True)
container.send_action('schema:create', {
'record_types': {
'user_conversation': {
'fields': [{
'name': 'user',
'type': 'ref(user)'
}, {
'name': 'conversation',
'type': 'ref(conversation)'
}, {
'name': 'unread_count',
'type': 'number'
}, {
'name': 'last_read_message',
'type': 'ref(message)'
}]
},
'receipt': {
'fields': [{
'name': 'user_id',
'type': 'ref(user)'
}, {
'name': 'message_id',
'type': 'ref(message)'
}, {
'name': 'read_at',
'type': 'datetime'
}, {
'name': 'delivered_at',
'type': 'datetime'
}]
},
'conversation': {
'fields': [{
'name': 'last_message',
'type': 'ref(message)'
}]
}
}
},
plugin_request=True)
import os
from psycopg2.extensions import AsIs
import skygear
from skygear.container import SkygearContainer
from skygear.models import RecordID, Reference
from skygear.options import options
from skygear.utils import db
from .exc import SkygearChatException
container = SkygearContainer()
opts = vars(options)
container.api_key = os.getenv('API_KEY', opts.get('apikey'))
container.app_name = os.getenv('APP_NAME', opts.get('appname'))
schema_name = "app_%s" % container.app_name
MASTER_KEY = os.getenv('MASTER_KEY', opts.get('masterkey'))
def _get_conversation(conversation_id):
# conversation_id can be Reference, recordID or string
if isinstance(conversation_id, Reference):
conversation_id = conversation_id.recordID.key
if isinstance(conversation_id, RecordID):
conversation_id = conversation_id.key
data = {
'database_id': '_public',
'record_type': 'conversation',
'limit': 1,
def getSkygearContainer():
container = SkygearContainer(api_key=options.masterkey,
user_id='<your-user-id>')
return container
def chat_plugin_init():
container = SkygearContainer(api_key=options.masterkey)
container.send_action('schema:create', {
'record_types': {
'user': {
'fields': [{
'name': 'name',
'type': 'string'
}]
}
}
})
container.send_action(
'schema:create', {
'record_types': {
'conversation': {
'fields': [{
'name': 'title',
'type': 'string'
}, {
'name': 'admin_ids',
'type': 'json'
}, {
'name': 'participant_ids',
'type': 'json'
}, {
'name': 'is_direct_message',
'type': 'boolean'
}]
}
}
})
container.send_action(
'schema:create', {
'record_types': {
'message': {
'fields': [{
'name': 'attachment',
'type': 'asset'
}, {
'name': 'body',
'type': 'string'
}, {
'name': 'metadata',
'type': 'json'
}, {
'name': 'conversation_id',
'type': 'ref(conversation)'
}]
}
}
})
container.send_action(
'schema:create', {
'record_types': {
'user_conversation': {
'fields': [{
'name': 'user',
'type': 'ref(user)'
}, {
'name': 'conversation',
'type': 'ref(conversation)'
}, {
'name': 'unread_count',
'type': 'number'
}, {
'name': 'last_read_message',
'type': 'ref(message)'
}]
}
}
})
def fetch(cls, message_id: str):
"""
Fetch the message from skygear.
The conversation record is also fetched using eager load.
"""
# FIXME checking should not be necessary, passing correct type
# is the responsibility of the caller.
# message_id can be Reference, recordID or string
if isinstance(message_id, Reference):
message_id = message_id.recordID.key
if isinstance(message_id, RecordID):
message_id = message_id.key
if not isinstance(message_id, str):
raise ValueError()
container = SkygearContainer(api_key=skyoptions.masterkey,
user_id=current_user_id())
response = container.send_action(
'record:query',
{
'database_id': '_union',
'record_type': 'message',
'limit': 1,
'sort': [],
'count': False,
'predicate': [
'eq', {
'$type': 'keypath',
'$val': '_id'
},
message_id
]
}
)
if 'error' in response:
raise SkygearChatException(response['error'])
if len(response['result']) == 0:
raise SkygearChatException('no messages found',
code=ResourceNotFound)
obj = cls()
messageDict = response['result'][0]
obj.record = deserialize_record(messageDict)
# Conversation is in publicDB, do cannot transient include
print(obj.record['conversation_id'].recordID.key)
response = container.send_action(
'record:query',
{
'database_id': '_public',
'record_type': 'conversation',
'limit': 1,
'sort': [],
'count': False,
'predicate': [
'eq', {
'$type': 'keypath',
'$val': '_id'
},
obj.record['conversation_id'].recordID.key
]
}
)
if len(response['result']) == 0:
raise SkygearChatException("no conversation found")
conversationDict = response['result'][0]
obj.conversationRecord = deserialize_record(conversationDict)
return obj
class OurskyBot():
questions = {
'chat': [
['What free chat?, email [email protected]']
],
'web_or_app': [
['Can you briefly describe your idea?'],
['Which email shall our Project Consultant reach you at?'],
[
'Great! Thank you and we will be in touch shortly!',
{
'template_type':'generic',
'elements': [{
'title': 'Learn more about Oursky',
'subtitle': '',
'image_url': 'https://oursky.com/img/logo-square.png',
'buttons': [{
'type': 'web_url',
'url': 'https://oursky.com/prototype',
'title': 'View'
}]
}]
}
]
],
'message_bot': [
['Interesting! Which company / business are you from?'],
['How do you plan to use the Messenger Bot?'],
['Interesting! Which email shall we reach you?'],
[
'Thanks! We will be in touch shortly!',
{
'template_type':'generic',
'elements': [{
'title': 'Learn more about Oursky',
'subtitle': '',
'image_url': 'https://oursky.com/img/logo-square.png',
'buttons': [{
'type': 'web_url',
'url': 'https://oursky.com/',
'title': 'View'
}]
}]
}
]
],
'other_enquiry': [
['Can you briefly describe your needs?'],
['What is your company name?'],
['Which email shall our Project Consultant reach you at?'],
[
'Great! Thank you and we will be in touch shortly!',
{
'template_type':'generic',
'elements': [{
'title': 'Learn more about Oursky',
'subtitle': '',
'image_url': 'https://oursky.com/img/logo-square.png',
'buttons': [{
'type': 'web_url',
'url': 'https://oursky.com/',
'title': 'View'
}]
}]
}
]
]
}
def __init__(self, user):
self.what = 'chat'
self.step = 0
self.user = user
self.container = SkygearContainer(access_token=get_token())
response = self.container.send_action('record:query', {
'record_type': 'fb_user',
'predicate': [
'eq',
{'$type': 'keypath', '$val': '_id'},
user
]
})
log.debug(response)
result = response['result']
try:
user = result[0]
except IndexError:
return
else:
if user:
self.step = user['step']
self.what = user['enquiry_topic']
def client_wants(self, what):
if self.what != what:
self.what = what
self.step = 0
self.upsert_fb_user()
def upsert_fb_user(self):
self.container.send_action('record:save', {'records': [{
'_id': 'fb_user/{}'.format(self.user),
'enquiry_topic': self.what,
'step': self.step
}]})
def listen(self, message):
# Save to skygear
self.step = self.step + 1
self.upsert_fb_user()
self.container.send_action('record:save', {'records': [{
'_id': 'fb_message/{}'.format(uuid.uuid4()),
'enquiry_topic': self.what,
'message': message
}]})
def speak(self):
log.debug('Speaking %s %s', self.what, self.step)
try:
return self.questions[self.what][self.step]
except IndexError as e:
log.warn(e)
return ['Please email [email protected] for follow up']
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 chat_plugin_init(config):
container = SkygearContainer(api_key=skyoptions.masterkey)
container.send_action(
'schema:create',
{
'record_types': {
'user': {
'fields': [
{'name': 'name', 'type': 'string'}
]
},
'conversation': {
'fields': [
{
'name': 'title',
'type': 'string'
},
{
'name': 'admin_ids',
'type': 'json'
},
{
'name': 'participant_ids',
'type': 'json'
},
{
'name': 'participant_count',
'type': 'number'
},
{
'name': 'metadata',
'type': 'json'
},
{
'name': 'distinct_by_participants',
'type': 'boolean'
}
]
}
}
},
plugin_request=True
)
container.send_action(
'schema:create',
{
'record_types': {
'message': {
'fields': [
{
'name': 'attachment',
'type': 'asset'
},
{
'name': 'body',
'type': 'string'
},
{
'name': 'metadata',
'type': 'json'
},
{
'name': 'conversation_id',
'type': 'ref(conversation)'
},
{
'name': 'conversation_status',
'type': 'string'
}
]
}
}
},
plugin_request=True
)
container.send_action(
'schema:create',
{
'record_types': {
'user_conversation': {
'fields': [
{
'name': 'user',
'type': 'ref(user)'
},
{
'name': 'conversation',
'type': 'ref(conversation)'
},
{
'name': 'unread_count',
'type': 'number'
},
{
'name': 'last_read_message',
'type': 'ref(message)'
}
]
},
'receipt': {
'fields': [
{
'name': 'user_id',
'type': 'ref(user)'
},
{
'name': 'message_id',
'type': 'ref(message)'
},
{
'name': 'read_at',
'type': 'datetime'
},
{
'name': 'delivered_at',
'type': 'datetime'
}
]
},
'conversation': {
'fields': [
{
'name': 'last_message',
'type': 'ref(message)'
}
]
}
}
},
plugin_request=True
)