def build_preview_response(self, cursor, start__lt, end__ge, team__eq, table_name='temp_event'):
# get existing events
cols = all_columns
query = '''SELECT %s FROM `%s`
JOIN `user` ON `user`.`id` = `%s`.`user_id`
JOIN `team` ON `team`.`id` = `%s`.`team_id`
JOIN `role` ON `role`.`id` = `%s`.`role_id`''' % (cols, table_name, table_name, table_name, table_name)
where_params = [constraints['start__lt'], constraints['end__ge']]
where_vals = [start__lt, end__ge]
# Deal with team subscriptions and team parameters
team_where = [constraints['team__eq']]
subs_vals = [team__eq]
subs_and = ' AND '.join(team_where)
cursor.execute('''SELECT `subscription_id`, `role_id` FROM `team_subscription`
JOIN `team` ON `team_id` = `team`.`id`
WHERE %s''' % subs_and, subs_vals)
if cursor.rowcount != 0:
# Build where clause based on team params and subscriptions
subs_and = '(%s OR (%s))' % (subs_and, ' OR '.join(['`team`.`id` = %s AND `role`.`id` = %s' %
(row['subscription_id'], row['role_id']) for row in cursor]))
where_params.append(subs_and)
where_vals += subs_vals
where_query = ' AND '.join(where_params)
if where_query:
query = '%s WHERE %s' % (query, where_query)
cursor.execute(query, where_vals)
data = cursor.fetchall()
return json_dumps(data)
def on_get(req, resp, user_name):
query = '''SELECT `team`.`name` AS `team`, `role`.`name` AS `role`, `contact_mode`.`name` AS `mode`,
`notification_type`.`name` AS `type`, `notification_setting`.`time_before`,
`notification_setting`.`only_if_involved`, `notification_setting`.`id`
FROM `notification_setting` JOIN `user` ON `notification_setting`.`user_id` = `user`.`id`
JOIN `team` ON `notification_setting`.`team_id` = `team`.`id`
JOIN `contact_mode` ON `notification_setting`.`mode_id` = `contact_mode`.`id`
JOIN `notification_type` ON `notification_setting`.`type_id` = `notification_type`.`id`
JOIN `setting_role` ON `notification_setting`.`id` = `setting_role`.`setting_id`
JOIN `role` ON `setting_role`.`role_id` = `role`.`id`
WHERE `user`.`name` = %s'''
connection = db.connect()
cursor = connection.cursor(db.DictCursor)
cursor.execute(query, user_name)
data = {}
# Format roles
for row in cursor:
setting_id = row['id']
if setting_id not in data:
role = row.pop('role')
row['roles'] = [role]
data[setting_id] = row
else:
data[setting_id]['roles'].append(row['role'])
cursor.close()
connection.close()
resp.body = json_dumps(data.values())
def on_get(req, resp, team):
team = unquote(team)
fields = req.get_param_as_list('fields')
active = req.get_param('active', default=True)
connection = db.connect()
cursor = connection.cursor(db.DictCursor)
cursor.execute('SELECT `id`, `name`, `email`, `slack_channel`, `scheduling_timezone` '
'FROM `team` WHERE `name`=%s AND `active` = %s', (team, active))
results = cursor.fetchall()
if not results:
raise HTTPNotFound()
[team_info] = results
if not fields:
# default to get all data
fields = populate_map.keys()
for field in fields:
populate = populate_map.get(field)
if not populate:
continue
populate(cursor, team_info)
cursor.close()
connection.close()
resp.body = json_dumps(team_info)
def on_get(req, resp, user_name):
'''
Get all pinned team names for a user
**Example request**:
.. sourcecode:: http
GET /api/v0/users/jdoe/pinned_teams HTTP/1.1
Host: example.com
**Example response**:
.. sourcecode:: http
HTTP/1.1 200 OK
Content-Type: application/json
[
"team-foo"
]
'''
connection = db.connect()
cursor = connection.cursor()
cursor.execute('''SELECT `team`.`name`
FROM `pinned_team` JOIN `team` ON `pinned_team`.`team_id` = `team`.`id`
WHERE `pinned_team`.`user_id` = (SELECT `id` FROM `user` WHERE `name` = %s)''',
user_name)
teams = [r[0] for r in cursor]
cursor.close()
connection.close()
resp.body = json_dumps(teams)
def on_get(req, resp, service, role):
get_oncall_query = '''SELECT `user`.`full_name` AS `user`, `event`.`start`, `event`.`end`,
`contact_mode`.`name` AS `mode`, `user_contact`.`destination`
FROM `service` JOIN `team_service` ON `service`.`id` = `team_service`.`service_id`
JOIN `event` ON `event`.`team_id` = `team_service`.`team_id`
JOIN `user` ON `user`.`id` = `event`.`user_id`
JOIN `role` ON `role`.`id` = `event`.`role_id` AND `role`.`name` = %s
LEFT JOIN `user_contact` ON `user`.`id` = `user_contact`.`user_id`
LEFT JOIN `contact_mode` ON `contact_mode`.`id` = `user_contact`.`mode_id`
WHERE UNIX_TIMESTAMP() BETWEEN `event`.`start` AND `event`.`end`
AND `service`.`name` = %s'''
connection = db.connect()
cursor = connection.cursor(db.DictCursor)
cursor.execute(get_oncall_query, (role, service))
data = cursor.fetchall()
ret = {}
for row in data:
user = row['user']
# add data row into accumulator only if not already there
if user not in ret:
ret[user] = row
ret[user]['contacts'] = {}
mode = row.pop('mode')
if not mode:
continue
dest = row.pop('destination')
ret[user]['contacts'][mode] = dest
data = ret.values()
cursor.close()
connection.close()
resp.body = json_dumps(data)
def on_post(req, resp, team, roster):
"""
Add user to a roster for a team
"""
team, roster = unquote(team), unquote(roster)
data = load_json_body(req)
user_name = data.get('name')
in_rotation = int(data.get('in_rotation', True))
if not user_name:
raise HTTPBadRequest('incomplete data', 'missing field "name"')
check_team_auth(team, req)
connection = db.connect()
cursor = connection.cursor()
cursor.execute(
'''(SELECT `id` FROM `team` WHERE `name`=%s)
UNION
(SELECT `id` FROM `user` WHERE `name`=%s)''',
(team, user_name))
results = [r[0] for r in cursor]
if len(results) < 2:
raise HTTPError('422 Unprocessable Entity', 'IntegrityError',
'invalid team or user')
# TODO: validate roster
(team_id, user_id) = results
try:
# also make sure user is in the team
cursor.execute(
'''INSERT IGNORE INTO `team_user` (`team_id`, `user_id`) VALUES (%r, %r)''',
(team_id, user_id))
cursor.execute(
'''INSERT INTO `roster_user` (`user_id`, `roster_id`, `in_rotation`)
VALUES (
%r,
(SELECT `roster`.`id` FROM `roster`
JOIN `team` ON `team`.`id`=`roster`.`team_id`
WHERE `team`.`name`=%s AND `roster`.`name`=%s),
%s
)''', (user_id, team, roster, in_rotation))
# subscribe user to notifications
subscribe_notifications(team, user_name, cursor)
create_audit(
{
'roster': roster,
'user': user_name,
'request_body': data
}, team, ROSTER_USER_ADDED, req, cursor)
connection.commit()
except db.IntegrityError:
raise HTTPError('422 Unprocessable Entity', 'IntegrityError',
'user "%(name)s" is already in the roster' % data)
finally:
cursor.close()
connection.close()
resp.status = HTTP_201
resp.body = json_dumps(get_user_data(None, {'name': user_name})[0])
def on_get(req, resp):
"""
Get list of team to user mappings
**Example request**:
.. sourcecode:: http
GET /api/v0/team_users HTTP/1.1
Host: example.com
**Example response**:
.. sourcecode:: http
HTTP/1.1 200 OK
Content-Type: application/json
[
{
"team": "team1",
"user" : "jdoe"
}
]
"""
query = '''SELECT `team`.`name` as team_name, `user`.`name` as user_name FROM `team_user`
JOIN `user` ON `team_user`.`user_id`=`user`.`id`
JOIN `team` ON `team_user`.`team_id`=`team`.`id`'''
connection = db.connect()
cursor = connection.cursor()
cursor.execute(query)
data = [{'team': r[0], 'user': r[1]} for r in cursor]
cursor.close()
connection.close()
resp.body = json_dumps(data)
def run_calc(val1, val2, operator):
result = {
"+": val1.__add__, "-": val1.__sub__,
"*": val1.__mul__, "/": val1.__truediv__,
"^": val1.__pow__
}[operator](val2).tolist()
return json_dumps(result)
def dumps(value,
indent=4,
simple_type=(str, dict, int, float, list, tuple, set)):
if value is not None:
if not isinstance(value, simple_type):
# Useful in various contexts
if isinstance(value, datetime):
value = value.isoformat()
# Always use Unicode
elif isinstance(value, bytes):
value = value.decode('utf8')
# For MongoDB queries
elif isinstance(value, ObjectId):
value = 'ObjectId({})'.format(value)
else:
# We do not know how to serialize it
raise TypeError('Cannot serialize `{}` ({})'.format(
value, type(value)))
return json_dumps(value, indent=indent)
def on_get(req, resp, user_name):
connection = db.connect()
cursor = connection.cursor(db.DictCursor)
role = req.get_param('role', None)
limit = req.get_param_as_int('limit', None)
query_end = ' ORDER BY `event`.`start` ASC'
query = '''SELECT %s, (SELECT COUNT(*) FROM `event` `counter`
WHERE `counter`.`link_id` = `event`.`link_id`) AS num_events
FROM `event`
JOIN `user` ON `user`.`id` = `event`.`user_id`
JOIN `team` ON `team`.`id` = `event`.`team_id`
JOIN `role` ON `role`.`id` = `event`.`role_id`
LEFT JOIN `event` `e2` ON `event`.link_id = `e2`.`link_id` AND `e2`.`start` < `event`.`start`
WHERE `user`.`id` = (SELECT `id` FROM `user` WHERE `name` = %%s)
AND `event`.`start` > UNIX_TIMESTAMP()
AND `e2`.`start` IS NULL''' % all_columns
query_params = [user_name]
if role:
query_end = ' AND `role`.`name` = %s' + query_end
query_params.append(role)
if limit:
query_end += ' LIMIT %s'
query_params.append(limit)
cursor.execute(query + query_end, query_params)
data = cursor.fetchall()
resp.body = json_dumps(data)
def on_get(req, resp):
"""
Search for services
"""
query = 'SELECT `name` FROM `service`'
where_params = []
where_vals = []
for key in req.params:
val = req.get_param(key)
if key in constraints:
where_params.append(constraints[key])
where_vals.append(val)
where_query = ' AND '.join(where_params)
if where_query:
query = '%s WHERE %s' % (query, where_query)
connection = db.connect()
cursor = connection.cursor()
cursor.execute(query, where_vals)
data = [r[0] for r in cursor]
cursor.close()
connection.close()
resp.body = json_dumps(data)
def on_get(req, resp, team):
"""
Get list of services mapped to a team
**Example request**:
.. sourcecode:: http
GET /api/v0/teams/team-foo/services HTTP/1.1
Host: example.com
**Example response**:
.. sourcecode:: http
HTTP/1.1 200 OK
Content-Type: application/json
[
"service-foo",
"service-bar"
]
"""
team = unquote(team)
connection = db.connect()
cursor = connection.cursor()
cursor.execute(
'''SELECT `service`.`name` FROM `service`
JOIN `team_service` ON `team_service`.`service_id`=`service`.`id`
JOIN `team` ON `team`.`id`=`team_service`.`team_id`
WHERE `team`.`name`=%s''', team)
data = [r[0] for r in cursor]
cursor.close()
connection.close()
resp.body = json_dumps(data)
def on_get(req, resp):
"""
Returns all notification types and whether they are reminder notifications.
**Example request:**
.. sourcecode:: http
GET /api/v0/notification_types HTTP/1.1
Host: example.com
**Example response:**
.. sourcecode:: http
HTTP/1.1 200 OK
Content-Type: application/json
[
{
"name": "oncall_reminder",
"is_reminder": 1
}
]
"""
connection = db.connect()
cursor = connection.cursor(db.DictCursor)
cursor.execute('SELECT `name`, `is_reminder` FROM `notification_type`')
data = cursor.fetchall()
cursor.close()
connection.close()
resp.body = json_dumps(data)
def on_get(req, resp):
"""
Role search.
**Example request**:
.. sourcecode:: http
GET /api/v0/roles?name__startswith=pri HTTP/1.1
Host: example.com
**Example response:**
.. sourcecode:: http
HTTP/1.1 200 OK
Content-Type: application/json
[
{
"id": 1,
"name": "primary",
"display_order": 1
}
]
:query id: id of the role
:query id__eq: id of the role
:query id__gt: id greater than
:query id__ge: id greater than or equal
:query id__lt: id less than
:query id__le: id less than or equal
:query name: role name
:query name__eq: role name
:query name__contains: role name contains param
:query name__startswith: role name starts with param
:query name__endswith: role name ends with param
"""
fields = req.get_param_as_list('fields', transform=columns.__getitem__)
cols = ', '.join(fields) if fields else all_columns
query = 'SELECT %s FROM `role`' % cols
where_params = []
where_vals = []
for key in req.params:
val = req.get_param(key)
if key in constraints:
where_params.append(constraints[key])
where_vals.append(val)
where_queries = ' AND '.join(where_params)
if where_queries:
query = '%s WHERE %s' % (query, where_queries)
connection = db.connect()
cursor = connection.cursor(db.DictCursor)
cursor.execute(query, where_vals)
data = cursor.fetchall()
cursor.close()
connection.close()
resp.body = json_dumps(data)
def on_get(req, resp):
connection = db.connect()
cursor = connection.cursor(db.DictCursor)
cursor.execute('SELECT `name`, `is_reminder` FROM `notification_type`')
data = cursor.fetchall()
cursor.close()
connection.close()
resp.body = json_dumps(data)
def create_audit(context, team_name, action_name, req, cursor):
owner_name = req.context.get('user')
if owner_name is None:
owner_name = req.context['app']
cursor.execute(
'''INSERT INTO audit(`team_name`, `owner_name`, `action_name`, `context`, `timestamp`)
VALUES (%s, %s, %s, %s, UNIX_TIMESTAMP())''',
(team_name, owner_name, action_name, json_dumps(context)))
def __init__(self,
body,
status=200,
headers=None,
content_type="application/json"):
if isinstance(body, (dict, list)):
body = json_dumps(body)
super().__init__(body, status, headers, content_type)
def on_get(req, resp):
'''
Search for team names. Allows filtering based on a number of parameters, detailed below.
Returns list of matching team names. If "active" parameter is unspecified, defaults to
True (only displaying undeleted teams)
:query name: team name
:query name__eq: team name
:query name__contains: team name contains param
:query name__startswith: team name starts with param
:query name__endswith: team name ends with param
:query id: team id
:query id__eq: team id
:query active: team active/deleted (1 and 0, respectively)
**Example request**:
.. sourcecode:: http
GET /api/v0/teams?name__startswith=team- HTTP/1.1
Host: example.com
**Example response**:
.. sourcecode:: http
HTTP/1.1 200 OK
Content-Type: application/json
[
"team-foo",
"team-bar"
]
'''
query = 'SELECT `name`, `email`, `slack_channel`, `scheduling_timezone`, `iris_plan` FROM `team`'
if 'active' not in req.params:
req.params['active'] = True
connection = db.connect()
cursor = connection.cursor()
keys = []
query_values = []
for key in req.params:
value = req.get_param(key)
if key in constraints:
keys.append(key)
query_values.append(value)
where_query = ' AND '.join(constraints[key] for key in keys)
if where_query:
query = '%s WHERE %s' % (query, where_query)
cursor.execute(query, query_values)
data = [r[0] for r in cursor]
cursor.close()
connection.close()
resp.body = json_dumps(data)
def on_get(req, resp):
"""
Get users filtered by params. Returns a list of user info objects for all users matching
filter parameters.
:query id: id of the user
:query id__eq: id of the user
:query id__gt: id greater than
:query id__ge: id greater than or equal
:query id__lt: id less than
:query id__le: id less than or equal
:query name: username
:query name__eq: username
:query name__contains: username contains param
:query name__startswith: username starts with param
:query name__endswith: username ends with param
:query full_name: full name
:query full_name__eq: username
:query full_name__contains: full name contains param
:query full_name__startswith: full name starts with param
:query full_name__endswith: full name ends with param
:query active: whether user has been deactivated (deleted)
**Example request**:
.. sourcecode:: http
GET /api/v0/users?name=jdoe HTTP/1.1
Host: example.com
**Example response**:
.. sourcecode:: http
HTTP/1.1 200 OK
Content-Type: application/json
[
{
"active": 1,
"contacts": {
"call": "+1 111-111-1111",
"email": "*****@*****.**",
"im": "jdoe",
"sms": "+1 111-111-1111"
},
"full_name": "John Doe",
"id": 1234,
"name": "jdoe",
"photo_url": "image.example.com",
"time_zone": "US/Pacific"
}
]
"""
resp.body = json_dumps(
get_user_data(req.get_param_as_list('fields'), req.params))
async def insert(self, field, value={}, **kwargs):
"""
insert the value
"""
if isinstance(value, (list, dict)):
value = json_dumps(value)
return await self._client_conn.hset(key=self.name,
field=field,
value=value)
def run_calc(val1, val2, operator):
result = {
"+": val1.__add__,
"-": val1.__sub__,
"*": val1.__mul__,
"/": val1.__truediv__,
"^": val1.__pow__
}[operator](val2).tolist()
return json_dumps(result)
def on_get(req, resp):
"""
Find services, filtered by params
:query id: id of the service
:query id__eq: id of the service
:query id__gt: id greater than
:query id__ge: id greater than or equal
:query id__lt: id less than
:query id__le: id less than or equal
:query name: service name
:query name__eq: service name
:query name__contains: service name contains param
:query name__startswith: service name starts with param
:query name__endswith: service name ends with param
**Example request**
.. sourcecode:: http
GET /api/v0/services?name__startswith=service HTTP/1.1
Host: example.com
**Example response**:
.. sourcecode:: http
HTTP/1.1 200 OK
Content-Type: application/json
[
"service-foo"
]
"""
query = 'SELECT `name` FROM `service`'
where_params = []
where_vals = []
for key in req.params:
val = req.get_param(key)
if key in constraints:
where_params.append(constraints[key])
where_vals.append(val)
where_query = ' AND '.join(where_params)
if where_query:
query = '%s WHERE %s' % (query, where_query)
connection = db.connect()
cursor = connection.cursor()
cursor.execute(query, where_vals)
data = [r[0] for r in cursor]
cursor.close()
connection.close()
resp.body = json_dumps(data)
def json(body, status=200, headers=None, **kwargs):
"""
Returns response object with body in json format.
:param body: Response data to be serialized.
:param status: Response code.
:param headers: Custom Headers.
:param kwargs: Remaining arguments that are passed to the json encoder.
"""
return HTTPResponse(json_dumps(body, **kwargs), headers=headers,
status=status, content_type="application/json")
def on_get(req, resp, user_name):
"""
Get user info by name
"""
# Format request to filter query on user name
req.params['name'] = user_name
data = get_user_data(req.get_param_as_list('fields'), req.params)
if not data:
raise HTTPNotFound()
resp.body = json_dumps(data[0])
def json(body, status=200, headers=None, **kwargs):
"""
Returns response object with body in json format.
:param body: Response data to be serialized.
:param status: Response code.
:param headers: Custom Headers.
:param kwargs: Remaining arguments that are passed to the json encoder.
"""
return HTTPResponse(json_dumps(body, **kwargs), headers=headers,
status=status, content_type="application/json")
def ParameterException(request):
return HTTPResponse(
json_dumps({
"error_code": 1000,
"message": "invalid parameter",
"request": request.method + " " + request.path
}),
headers=None,
status=400,
content_type="application/json",
)
def create_reminder(user_id, mode, send_time, context, type_name, cursor):
context = json_dumps(context)
cursor.execute(
'''INSERT INTO `notification_queue`(`user_id`, `send_time`, `mode_id`, `active`, `context`, `type_id`)
VALUES (%s,
%s,
(SELECT `id` FROM `contact_mode` WHERE `name` = %s),
1,
%s,
(SELECT `id` FROM `notification_type` WHERE `name` = %s))''',
(user_id, send_time, mode, context, type_name))
def json(self):
if not self.parsed_json:
try:
self.parsed_json = json_loads(self.body)
except Exception:
return HTTPResponse(json_dumps(self.body),
headers=self.headers,
status=400,
content_type="application/json")
return self.parsed_json
def response_handle(request, dict_value, status=200):
"""
Return sanic.response or json depending on the request
:param request: sanic.request.Request or dict
:param dict_value:
:return:
"""
if isinstance(request, Request):
return response.json(dict_value, status=status)
else:
return json_dumps(dict_value, ensure_ascii=False)
def on_get(req, resp):
"""
Get all contact modes
"""
connection = db.connect()
cursor = connection.cursor()
cursor.execute('SELECT `name` FROM `contact_mode`')
data = [row[0] for row in cursor]
cursor.close()
connection.close()
resp.body = json_dumps(data)
def response_handle(request, dict_value, status=200):
"""
根据request参数决定返回sanic.response 或者 json
:param request: sanic.request.Request or dict
:param dict_value:
:return:
"""
if isinstance(request, Request):
return response.json(dict_value, status=status)
else:
return json_dumps(dict_value, ensure_ascii=False)
def on_get(req, resp, team):
audit_query = '''SELECT `audit_log`.`description`, `audit_log`.`timestamp`,
`audit_log`.`owner_name`, `audit_log`.`action_name`
FROM `audit_log` WHERE `team_name` = %s'''
connection = db.connect()
cursor = connection.cursor(db.DictCursor)
cursor.execute(audit_query, team)
data = cursor.fetchall()
cursor.close()
connection.close()
resp.body = json_dumps(data)
def prepare_string(self, string, args, level):
try:
string = string % args
except:
try:
string = string.format(*args)
except:
string
return json_dumps(dict(
level=level,
time=str(datetime.now()),
funcname=currentframe().f_back.f_back.f_code.co_name,
lineno=currentframe().f_back.f_back.f_lineno,
message=string,
msg_type=self.msg_type
))
def __init__(self, status=None, body=None, json=None, headers=None):
self._default_status = status
self._default_headers = headers
if json is not None:
if body is not None:
msg = 'Either json or body may be specified, but not both'
raise ValueError(msg)
self._default_body = json_dumps(json, ensure_ascii=False)
else:
self._default_body = body
self.captured_req = None
self.captured_resp = None
self.captured_kwargs = None
