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 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 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