class ApiKey(ModelTimestampsMixin, BaseModel): org = peewee.ForeignKeyField(Organization) api_key = peewee.CharField(index=True, default=lambda: generate_token(40)) active = peewee.BooleanField(default=True) object_type = peewee.CharField() object_id = peewee.IntegerField() object = GFKField('object_type', 'object_id') created_by = peewee.ForeignKeyField(User, null=True) class Meta: db_table = 'api_keys' indexes = ( (('object_type', 'object_id'), False), ) @classmethod def get_by_api_key(cls, api_key): return cls.get(cls.api_key==api_key, cls.active==True) @classmethod def get_by_object(cls, object): return cls.select().where(cls.object_type==object._meta.db_table, cls.object_id==object.id, cls.active==True).first() @classmethod def create_for_object(cls, object, user): return cls.create(org=user.org, object=object, created_by=user)
class ApiKey(TimestampMixin, GFKBase, db.Model): id = Column(db.Integer, primary_key=True) org_id = Column(db.Integer, db.ForeignKey("organizations.id")) org = db.relationship(Organization) api_key = Column(db.String(255), index=True, default=lambda: generate_token(40)) active = Column(db.Boolean, default=True) # 'object' provided by GFKBase created_by_id = Column(db.Integer, db.ForeignKey("users.id"), nullable=True) created_by = db.relationship(User) __tablename__ = 'api_keys' __table_args__ = (db.Index('api_keys_object_type_object_id', 'object_type', 'object_id'), ) @classmethod def get_by_api_key(cls, api_key): return cls.query.filter(cls.api_key == api_key, cls.active == True).one() @classmethod def get_by_object(cls, object): return cls.query.filter( cls.object_type == object.__class__.__tablename__, cls.object_id == object.id, cls.active == True).first() @classmethod def create_for_object(cls, object, user): k = cls(org=user.org, object=object, created_by=user) db.session.add(k) return k
def pre_save(self, created): super(User, self).pre_save(created) if not self.api_key: self.api_key = generate_token(40)
def test_format(self): token = generate_token(40) self.assertRegexpMatches(token, r"[a-zA-Z0-9]{40}")
class User(TimestampMixin, db.Model, BelongsToOrgMixin, UserMixin, PermissionsCheckMixin): id = Column(db.Integer, primary_key=True) org_id = Column(db.Integer, db.ForeignKey('organizations.id')) org = db.relationship("Organization", backref=db.backref("users", lazy="dynamic")) name = Column(db.String(320)) email = Column(EmailType) _profile_image_url = Column('profile_image_url', db.String(320), nullable=True) password_hash = Column(db.String(128), nullable=True) group_ids = Column('groups', MutableList.as_mutable(postgresql.ARRAY(db.Integer)), nullable=True) api_key = Column(db.String(40), default=lambda: generate_token(40), unique=True) disabled_at = Column(db.DateTime(True), default=None, nullable=True) details = Column(MutableDict.as_mutable(postgresql.JSON), nullable=True, server_default='{}', default={}) active_at = json_cast_property(db.DateTime(True), 'details', 'active_at', default=None) is_invitation_pending = json_cast_property(db.Boolean(True), 'details', 'is_invitation_pending', default=False) is_email_verified = json_cast_property(db.Boolean(True), 'details', 'is_email_verified', default=True) __tablename__ = 'users' __table_args__ = (db.Index('users_org_id_email', 'org_id', 'email', unique=True), ) def __str__(self): return '%s (%s)' % (self.name, self.email) def __init__(self, *args, **kwargs): if kwargs.get('email') is not None: kwargs['email'] = kwargs['email'].lower() super(User, self).__init__(*args, **kwargs) @property def is_disabled(self): return self.disabled_at is not None def disable(self): self.disabled_at = db.func.now() def enable(self): self.disabled_at = None def regenerate_api_key(self): self.api_key = generate_token(40) def to_dict(self, with_api_key=False): profile_image_url = self.profile_image_url if self.is_disabled: assets = app.extensions['webpack']['assets'] or {} path = 'images/avatar.svg' profile_image_url = url_for('static', filename=assets.get(path, path)) d = { 'id': self.id, 'name': self.name, 'email': self.email, 'profile_image_url': profile_image_url, 'groups': self.group_ids, 'updated_at': self.updated_at, 'created_at': self.created_at, 'disabled_at': self.disabled_at, 'is_disabled': self.is_disabled, 'active_at': self.active_at, 'is_invitation_pending': self.is_invitation_pending, 'is_email_verified': self.is_email_verified, } if self.password_hash is None: d['auth_type'] = 'external' else: d['auth_type'] = 'password' if with_api_key: d['api_key'] = self.api_key return d def is_api_user(self): return False @property def profile_image_url(self): if self._profile_image_url is not None: return self._profile_image_url email_md5 = hashlib.md5(self.email.lower().encode()).hexdigest() return "https://www.gravatar.com/avatar/{}?s=40&d=identicon".format( email_md5) @property def permissions(self): # TODO: this should be cached. return list( itertools.chain(*[ g.permissions for g in Group.query.filter(Group.id.in_(self.group_ids)) ])) @classmethod def get_by_org(cls, org): return cls.query.filter(cls.org == org) @classmethod def get_by_id(cls, _id): return cls.query.filter(cls.id == _id).one() @classmethod def get_by_email_and_org(cls, email, org): return cls.get_by_org(org).filter(cls.email == email).one() @classmethod def get_by_api_key_and_org(cls, api_key, org): return cls.get_by_org(org).filter(cls.api_key == api_key).one() @classmethod def all(cls, org): return cls.get_by_org(org).filter(cls.disabled_at.is_(None)) @classmethod def all_disabled(cls, org): return cls.get_by_org(org).filter(cls.disabled_at.isnot(None)) @classmethod def search(cls, base_query, term): term = '%{}%'.format(term) search_filter = or_(cls.name.ilike(term), cls.email.like(term)) return base_query.filter(search_filter) @classmethod def pending(cls, base_query, pending): if pending: return base_query.filter(cls.is_invitation_pending.is_(True)) else: return base_query.filter(cls.is_invitation_pending.isnot( True)) # check for both `false`/`null` @classmethod def find_by_email(cls, email): return cls.query.filter(cls.email == email) def hash_password(self, password): self.password_hash = pwd_context.encrypt(password) def verify_password(self, password): return self.password_hash and pwd_context.verify( password, self.password_hash) def update_group_assignments(self, group_names): groups = Group.find_by_name(self.org, group_names) groups.append(self.org.default_group) self.group_ids = [g.id for g in groups] db.session.add(self) db.session.commit() def has_access(self, obj, access_type): return AccessPermission.exists(obj, access_type, grantee=self) def get_id(self): identity = hashlib.md5("{},{}".format( self.email, self.password_hash).encode()).hexdigest() return "{0}-{1}".format(self.id, identity)
def regenerate_api_key(self): self.api_key = generate_token(40)
class Query(ChangeTrackingMixin, TimestampMixin, BelongsToOrgMixin, db.Model): id = Column(db.Integer, primary_key=True) version = Column(db.Integer, default=1) org_id = Column(db.Integer, db.ForeignKey('organizations.id')) org = db.relationship(Organization, backref="queries") data_source_id = Column(db.Integer, db.ForeignKey("data_sources.id"), nullable=True) data_source = db.relationship(DataSource, backref='queries') latest_query_data_id = Column(db.Integer, db.ForeignKey("query_results.id"), nullable=True) latest_query_data = db.relationship(QueryResult) name = Column(db.String(255)) description = Column(db.String(4096), nullable=True) query_text = Column("query", db.Text) query_hash = Column(db.String(32)) api_key = Column(db.String(40), default=lambda: generate_token(40)) user_id = Column(db.Integer, db.ForeignKey("users.id")) user = db.relationship(User, foreign_keys=[user_id]) last_modified_by_id = Column(db.Integer, db.ForeignKey('users.id'), nullable=True) last_modified_by = db.relationship(User, backref="modified_queries", foreign_keys=[last_modified_by_id]) is_archived = Column(db.Boolean, default=False, index=True) is_draft = Column(db.Boolean, default=True, index=True) schedule = Column(MutableDict.as_mutable(PseudoJSON), nullable=True) schedule_failures = Column(db.Integer, default=0) visualizations = db.relationship("Visualization", cascade="all, delete-orphan") options = Column(MutableDict.as_mutable(PseudoJSON), default={}) search_vector = Column(TSVectorType('id', 'name', 'description', 'query', weights={ 'name': 'A', 'id': 'B', 'description': 'C', 'query': 'D' }), nullable=True) tags = Column('tags', MutableList.as_mutable(postgresql.ARRAY(db.Unicode)), nullable=True) query_class = SearchBaseQuery __tablename__ = 'queries' __mapper_args__ = { "version_id_col": version, 'version_id_generator': False } def __str__(self): return text_type(self.id) def archive(self, user=None): db.session.add(self) self.is_archived = True self.schedule = None for vis in self.visualizations: for w in vis.widgets: db.session.delete(w) for a in self.alerts: db.session.delete(a) if user: self.record_changes(user) def regenerate_api_key(self): self.api_key = generate_token(40) @classmethod def create(cls, **kwargs): query = cls(**kwargs) db.session.add( Visualization(query_rel=query, name="Table", description='', type="TABLE", options="{}")) return query @classmethod def all_queries(cls, group_ids, user_id=None, include_drafts=False, include_archived=False): query_ids = (db.session.query(distinct(cls.id)).join( DataSourceGroup, Query.data_source_id == DataSourceGroup.data_source_id).filter( Query.is_archived.is_(include_archived)).filter( DataSourceGroup.group_id.in_(group_ids))) queries = ( cls.query.options( joinedload(Query.user), joinedload(Query.latest_query_data).load_only( 'runtime', 'retrieved_at', )).filter(cls.id.in_(query_ids)) # Adding outer joins to be able to order by relationship .outerjoin(User, User.id == Query.user_id).outerjoin( QueryResult, QueryResult.id == Query.latest_query_data_id).options( contains_eager(Query.user), contains_eager(Query.latest_query_data), )) if not include_drafts: queries = queries.filter( or_(Query.is_draft.is_(False), Query.user_id == user_id)) return queries @classmethod def favorites(cls, user, base_query=None): if base_query is None: base_query = cls.all_queries(user.group_ids, user.id, include_drafts=True) return base_query.join((Favorite, and_(Favorite.object_type == u'Query', Favorite.object_id == Query.id))).filter( Favorite.user_id == user.id) @classmethod def all_tags(cls, user, include_drafts=False): queries = cls.all_queries( group_ids=user.group_ids, user_id=user.id, include_drafts=include_drafts, ) tag_column = func.unnest(cls.tags).label('tag') usage_count = func.count(1).label('usage_count') query = (db.session.query( tag_column, usage_count).group_by(tag_column).filter( Query.id.in_(queries.options(load_only('id')))).order_by( usage_count.desc())) return query @classmethod def by_user(cls, user): return cls.all_queries(user.group_ids, user.id).filter(Query.user == user) @classmethod def by_api_key(cls, api_key): return cls.query.filter(cls.api_key == api_key).one() @classmethod def past_scheduled_queries(cls): now = utils.utcnow() queries = (Query.query.filter(Query.schedule.isnot(None)).order_by( Query.id)) return filter( lambda x: x.schedule["until"] is not None and pytz.utc.localize( datetime.datetime.strptime(x.schedule['until'], '%Y-%m-%d')) <= now, queries) @classmethod def outdated_queries(cls): queries = (Query.query.options( joinedload( Query.latest_query_data).load_only('retrieved_at')).filter( Query.schedule.isnot(None)).order_by(Query.id)) now = utils.utcnow() outdated_queries = {} scheduled_queries_executions.refresh() for query in queries: if query.schedule['interval'] is None: continue if query.schedule['until'] is not None: schedule_until = pytz.utc.localize( datetime.datetime.strptime(query.schedule['until'], '%Y-%m-%d')) if schedule_until <= now: continue if query.latest_query_data: retrieved_at = query.latest_query_data.retrieved_at else: retrieved_at = now retrieved_at = scheduled_queries_executions.get( query.id) or retrieved_at if should_schedule_next(retrieved_at, now, query.schedule['interval'], query.schedule['time'], query.schedule['day_of_week'], query.schedule_failures): key = "{}:{}".format(query.query_hash, query.data_source_id) outdated_queries[key] = query return outdated_queries.values() @classmethod def search(cls, term, group_ids, user_id=None, include_drafts=False, limit=None, include_archived=False, multi_byte_search=False): all_queries = cls.all_queries( group_ids, user_id=user_id, include_drafts=include_drafts, include_archived=include_archived, ) if multi_byte_search: # Since tsvector doesn't work well with CJK languages, use `ilike` too pattern = u'%{}%'.format(term) return all_queries.filter( or_(cls.name.ilike(pattern), cls.description.ilike(pattern))).order_by( Query.id).limit(limit) # sort the result using the weight as defined in the search vector column return all_queries.search(term, sort=True).limit(limit) @classmethod def search_by_user(cls, term, user, limit=None): return cls.by_user(user).search(term, sort=True).limit(limit) @classmethod def recent(cls, group_ids, user_id=None, limit=20): query = (cls.query.filter( Event.created_at > (db.func.current_date() - 7)).join( Event, Query.id == Event.object_id.cast(db.Integer)).join( DataSourceGroup, Query.data_source_id == DataSourceGroup.data_source_id).filter( Event.action.in_([ 'edit', 'execute', 'edit_name', 'edit_description', 'view_source' ]), Event.object_id != None, Event.object_type == 'query', DataSourceGroup.group_id.in_(group_ids), or_(Query.is_draft == False, Query.user_id == user_id), Query.is_archived == False).group_by( Event.object_id, Query.id).order_by(db.desc(db.func.count(0)))) if user_id: query = query.filter(Event.user_id == user_id) query = query.limit(limit) return query @classmethod def get_by_id(cls, _id): return cls.query.filter(cls.id == _id).one() @classmethod def all_groups_for_query_ids(cls, query_ids): query = """SELECT group_id, view_only FROM queries JOIN data_source_groups ON queries.data_source_id = data_source_groups.data_source_id WHERE queries.id in :ids""" return db.session.execute(query, {'ids': tuple(query_ids)}).fetchall() def fork(self, user): forked_list = [ 'org', 'data_source', 'latest_query_data', 'description', 'query_text', 'query_hash', 'options' ] kwargs = {a: getattr(self, a) for a in forked_list} # Query.create will add default TABLE visualization, so use constructor to create bare copy of query forked_query = Query(name=u'Copy of (#{}) {}'.format( self.id, self.name), user=user, **kwargs) for v in self.visualizations: forked_v = v.copy() forked_v['query_rel'] = forked_query fv = Visualization( **forked_v ) # it will magically add it to `forked_query.visualizations` db.session.add(fv) db.session.add(forked_query) return forked_query @property def runtime(self): return self.latest_query_data.runtime @property def retrieved_at(self): return self.latest_query_data.retrieved_at @property def groups(self): if self.data_source is None: return {} return self.data_source.groups @hybrid_property def lowercase_name(self): "Optional property useful for sorting purposes." return self.name.lower() @lowercase_name.expression def lowercase_name(cls): "The SQLAlchemy expression for the property above." return func.lower(cls.name) @property def parameters(self): return self.options.get("parameters", []) @property def parameterized(self): return ParameterizedQuery(self.query_text, self.parameters)
class Query(ChangeTrackingMixin, TimestampMixin, BelongsToOrgMixin, db.Model): id = primary_key("Query") version = Column(db.Integer, default=1) org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id")) org = db.relationship(Organization, backref="queries") data_source_id = Column(key_type("DataSource"), db.ForeignKey("data_sources.id"), nullable=True) data_source = db.relationship(DataSource, backref="queries") latest_query_data_id = Column( key_type("QueryResult"), db.ForeignKey("query_results.id"), nullable=True ) latest_query_data = db.relationship(QueryResult) name = Column(db.String(255)) description = Column(db.String(4096), nullable=True) query_text = Column("query", db.Text) query_hash = Column(db.String(32)) api_key = Column(db.String(40), default=lambda: generate_token(40)) user_id = Column(key_type("User"), db.ForeignKey("users.id")) user = db.relationship(User, foreign_keys=[user_id]) last_modified_by_id = Column(key_type("User"), db.ForeignKey("users.id"), nullable=True) last_modified_by = db.relationship( User, backref="modified_queries", foreign_keys=[last_modified_by_id] ) is_archived = Column(db.Boolean, default=False, index=True) is_draft = Column(db.Boolean, default=True, index=True) schedule = Column(MutableDict.as_mutable(PseudoJSON), nullable=True) interval = pseudo_json_cast_property(db.Integer, "schedule", "interval", default=0) schedule_failures = Column(db.Integer, default=0) visualizations = db.relationship("Visualization", cascade="all, delete-orphan") options = Column(MutableDict.as_mutable(PseudoJSON), default={}) search_vector = Column( TSVectorType( "id", "name", "description", "query", weights={"name": "A", "id": "B", "description": "C", "query": "D"}, ), nullable=True, ) tags = Column( "tags", MutableList.as_mutable(postgresql.ARRAY(db.Unicode)), nullable=True ) query_class = SearchBaseQuery __tablename__ = "queries" __mapper_args__ = {"version_id_col": version, "version_id_generator": False} def __str__(self): return str(self.id) def archive(self, user=None): db.session.add(self) self.is_archived = True self.schedule = None for vis in self.visualizations: for w in vis.widgets: db.session.delete(w) for a in self.alerts: db.session.delete(a) if user: self.record_changes(user) def regenerate_api_key(self): self.api_key = generate_token(40) @classmethod def create(cls, **kwargs): query = cls(**kwargs) db.session.add( Visualization( query_rel=query, name="Table", description="", type="TABLE", options="{}", ) ) return query @classmethod def all_queries( cls, group_ids, user_id=None, include_drafts=False, include_archived=False ): query_ids = ( db.session.query(distinct(cls.id)) .join( DataSourceGroup, Query.data_source_id == DataSourceGroup.data_source_id ) .filter(Query.is_archived.is_(include_archived)) .filter(DataSourceGroup.group_id.in_(group_ids)) ) queries = ( cls.query.options( joinedload(Query.user), joinedload(Query.latest_query_data).load_only( "runtime", "retrieved_at" ), ) .filter(cls.id.in_(query_ids)) # Adding outer joins to be able to order by relationship .outerjoin(User, User.id == Query.user_id) .outerjoin(QueryResult, QueryResult.id == Query.latest_query_data_id) .options( contains_eager(Query.user), contains_eager(Query.latest_query_data) ) ) if not include_drafts: queries = queries.filter( or_(Query.is_draft.is_(False), Query.user_id == user_id) ) return queries @classmethod def favorites(cls, user, base_query=None): if base_query is None: base_query = cls.all_queries(user.group_ids, user.id, include_drafts=True) return base_query.join( ( Favorite, and_(Favorite.object_type == "Query", Favorite.object_id == Query.id), ) ).filter(Favorite.user_id == user.id) @classmethod def all_tags(cls, user, include_drafts=False): queries = cls.all_queries( group_ids=user.group_ids, user_id=user.id, include_drafts=include_drafts ) tag_column = func.unnest(cls.tags).label("tag") usage_count = func.count(1).label("usage_count") query = ( db.session.query(tag_column, usage_count) .group_by(tag_column) .filter(Query.id.in_(queries.options(load_only("id")))) .order_by(usage_count.desc()) ) return query @classmethod def by_user(cls, user): return cls.all_queries(user.group_ids, user.id).filter(Query.user == user) @classmethod def by_api_key(cls, api_key): return cls.query.filter(cls.api_key == api_key).one() @classmethod def past_scheduled_queries(cls): now = utils.utcnow() queries = Query.query.filter(Query.schedule.isnot(None)).order_by(Query.id) return [ query for query in queries if query.schedule["until"] is not None and pytz.utc.localize( datetime.datetime.strptime(query.schedule["until"], "%Y-%m-%d") ) <= now ] @classmethod def outdated_queries(cls): queries = ( Query.query.options( joinedload(Query.latest_query_data).load_only("retrieved_at") ) .filter(Query.schedule.isnot(None)) .order_by(Query.id) .all() ) now = utils.utcnow() outdated_queries = {} scheduled_queries_executions.refresh() for query in queries: try: if query.schedule.get("disabled"): continue if query.schedule["until"]: schedule_until = pytz.utc.localize( datetime.datetime.strptime(query.schedule["until"], "%Y-%m-%d") ) if schedule_until <= now: continue retrieved_at = scheduled_queries_executions.get(query.id) or ( query.latest_query_data and query.latest_query_data.retrieved_at ) if should_schedule_next( retrieved_at or now, now, query.schedule["interval"], query.schedule["time"], query.schedule["day_of_week"], query.schedule_failures, ): key = "{}:{}".format(query.query_hash, query.data_source_id) outdated_queries[key] = query except Exception as e: query.schedule["disabled"] = True db.session.commit() message = ( "Could not determine if query %d is outdated due to %s. The schedule for this query has been disabled." % (query.id, repr(e)) ) logging.info(message) sentry.capture_exception( type(e)(message).with_traceback(e.__traceback__) ) return list(outdated_queries.values()) @classmethod def search( cls, term, group_ids, user_id=None, include_drafts=False, limit=None, include_archived=False, multi_byte_search=False, ): all_queries = cls.all_queries( group_ids, user_id=user_id, include_drafts=include_drafts, include_archived=include_archived, ) if multi_byte_search: # Since tsvector doesn't work well with CJK languages, use `ilike` too pattern = "%{}%".format(term) return ( all_queries.filter( or_(cls.name.ilike(pattern), cls.description.ilike(pattern)) ) .order_by(Query.id) .limit(limit) ) # sort the result using the weight as defined in the search vector column return all_queries.search(term, sort=True).limit(limit) @classmethod def search_by_user(cls, term, user, limit=None): return cls.by_user(user).search(term, sort=True).limit(limit) @classmethod def recent(cls, group_ids, user_id=None, limit=20): query = ( cls.query.filter(Event.created_at > (db.func.current_date() - 7)) .join(Event, Query.id == Event.object_id.cast(db.Integer)) .join( DataSourceGroup, Query.data_source_id == DataSourceGroup.data_source_id ) .filter( Event.action.in_( ["edit", "execute", "edit_name", "edit_description", "view_source"] ), Event.object_id != None, Event.object_type == "query", DataSourceGroup.group_id.in_(group_ids), or_(Query.is_draft == False, Query.user_id == user_id), Query.is_archived == False, ) .group_by(Event.object_id, Query.id) .order_by(db.desc(db.func.count(0))) ) if user_id: query = query.filter(Event.user_id == user_id) query = query.limit(limit) return query @classmethod def get_by_id(cls, _id): return cls.query.filter(cls.id == _id).one() @classmethod def all_groups_for_query_ids(cls, query_ids): query = """SELECT group_id, view_only FROM queries JOIN data_source_groups ON queries.data_source_id = data_source_groups.data_source_id WHERE queries.id in :ids""" return db.session.execute(query, {"ids": tuple(query_ids)}).fetchall() @classmethod def update_latest_result(cls, query_result): # TODO: Investigate how big an impact this select-before-update makes. queries = Query.query.filter( Query.query_hash == query_result.query_hash, Query.data_source == query_result.data_source, ) for q in queries: q.latest_query_data = query_result # don't auto-update the updated_at timestamp q.skip_updated_at = True db.session.add(q) query_ids = [q.id for q in queries] logging.info( "Updated %s queries with result (%s).", len(query_ids), query_result.query_hash, ) return query_ids def fork(self, user): forked_list = [ "org", "data_source", "latest_query_data", "description", "query_text", "query_hash", "options", "tags", ] kwargs = {a: getattr(self, a) for a in forked_list} # Query.create will add default TABLE visualization, so use constructor to create bare copy of query forked_query = Query( name="Copy of (#{}) {}".format(self.id, self.name), user=user, **kwargs ) for v in sorted(self.visualizations, key=lambda v: v.id): forked_v = v.copy() forked_v["query_rel"] = forked_query fv = Visualization( **forked_v ) # it will magically add it to `forked_query.visualizations` db.session.add(fv) db.session.add(forked_query) return forked_query @property def runtime(self): return self.latest_query_data.runtime @property def retrieved_at(self): return self.latest_query_data.retrieved_at @property def groups(self): if self.data_source is None: return {} return self.data_source.groups @hybrid_property def lowercase_name(self): "Optional property useful for sorting purposes." return self.name.lower() @lowercase_name.expression def lowercase_name(cls): "The SQLAlchemy expression for the property above." return func.lower(cls.name) @property def parameters(self): return self.options.get("parameters", []) @property def parameterized(self): return ParameterizedQuery(self.query_text, self.parameters, self.org) @property def dashboard_api_keys(self): query = """SELECT api_keys.api_key FROM api_keys JOIN dashboards ON object_id = dashboards.id JOIN widgets ON dashboards.id = widgets.dashboard_id JOIN visualizations ON widgets.visualization_id = visualizations.id WHERE object_type='dashboards' AND active=true AND visualizations.query_id = :id""" api_keys = db.session.execute(query, {"id": self.id}).fetchall() return [api_key[0] for api_key in api_keys] def update_query_hash(self): should_apply_auto_limit = self.options.get("apply_auto_limit", False) if self.options else False query_runner = self.data_source.query_runner if self.data_source else BaseQueryRunner({}) self.query_hash = query_runner.gen_query_hash(self.query_text, should_apply_auto_limit)
class User(TimestampMixin, db.Model, BelongsToOrgMixin, UserMixin, PermissionsCheckMixin): id = primary_key("User") org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id")) org = db.relationship("Organization", backref=db.backref("users", lazy="dynamic")) name = Column(db.String(320)) email = Column(EmailType) password_hash = Column(db.String(128), nullable=True) group_ids = Column("groups", MutableList.as_mutable( postgresql.ARRAY(key_type("Group"))), nullable=True) api_key = Column(db.String(40), default=lambda: generate_token(40), unique=True) disabled_at = Column(db.DateTime(True), default=None, nullable=True) details = Column( MutableDict.as_mutable(postgresql.JSONB), nullable=True, server_default="{}", default={}, ) active_at = json_cast_property(db.DateTime(True), "details", "active_at", default=None) _profile_image_url = json_cast_property(db.Text(), "details", "profile_image_url", default=None) is_invitation_pending = json_cast_property(db.Boolean(True), "details", "is_invitation_pending", default=False) is_email_verified = json_cast_property(db.Boolean(True), "details", "is_email_verified", default=True) __tablename__ = "users" __table_args__ = (db.Index("users_org_id_email", "org_id", "email", unique=True), ) def __str__(self): return "%s (%s)" % (self.name, self.email) def __init__(self, *args, **kwargs): if kwargs.get("email") is not None: kwargs["email"] = kwargs["email"].lower() super(User, self).__init__(*args, **kwargs) @property def is_disabled(self): return self.disabled_at is not None def disable(self): self.disabled_at = db.func.now() def enable(self): self.disabled_at = None def regenerate_api_key(self): self.api_key = generate_token(40) def to_dict(self, with_api_key=False): profile_image_url = self.profile_image_url if self.is_disabled: assets = app.extensions["webpack"]["assets"] or {} path = "images/avatar.svg" profile_image_url = url_for("static", filename=assets.get(path, path)) d = { "id": self.id, "name": self.name, "email": self.email, "profile_image_url": profile_image_url, "groups": self.group_ids, "updated_at": self.updated_at, "created_at": self.created_at, "disabled_at": self.disabled_at, "is_disabled": self.is_disabled, "active_at": self.active_at, "is_invitation_pending": self.is_invitation_pending, "is_email_verified": self.is_email_verified, } if self.password_hash is None: d["auth_type"] = "external" else: d["auth_type"] = "password" if with_api_key: d["api_key"] = self.api_key return d def is_api_user(self): return False @property def profile_image_url(self): if self._profile_image_url is not None: return self._profile_image_url email_md5 = hashlib.md5(self.email.lower().encode()).hexdigest() return "//sdn.geekzu.org/avatar/{}?s=40&d=identicon".format(email_md5) @property def permissions(self): # TODO: this should be cached. return list( itertools.chain(*[ g.permissions for g in Group.query.filter(Group.id.in_(self.group_ids)) ])) @classmethod def get_by_org(cls, org): return cls.query.filter(cls.org == org) @classmethod def get_by_id(cls, _id): return cls.query.filter(cls.id == _id).one() @classmethod def get_by_email_and_org(cls, email, org): return cls.get_by_org(org).filter(cls.email == email).one() @classmethod def get_by_api_key_and_org(cls, api_key, org): return cls.get_by_org(org).filter(cls.api_key == api_key).one() @classmethod def all(cls, org): return cls.get_by_org(org).filter(cls.disabled_at.is_(None)) @classmethod def all_disabled(cls, org): return cls.get_by_org(org).filter(cls.disabled_at.isnot(None)) @classmethod def search(cls, base_query, term): term = "%{}%".format(term) search_filter = or_(cls.name.ilike(term), cls.email.like(term)) return base_query.filter(search_filter) @classmethod def pending(cls, base_query, pending): if pending: return base_query.filter(cls.is_invitation_pending.is_(True)) else: return base_query.filter(cls.is_invitation_pending.isnot( True)) # check for both `false`/`null` @classmethod def find_by_email(cls, email): return cls.query.filter(cls.email == email) def hash_password(self, password): self.password_hash = pwd_context.encrypt(password) def verify_password(self, password): return self.password_hash and pwd_context.verify( password, self.password_hash) def update_group_assignments(self, group_names): groups = Group.find_by_name(self.org, group_names) groups.append(self.org.default_group) self.group_ids = [g.id for g in groups] db.session.add(self) db.session.commit() def has_access(self, obj, access_type): return AccessPermission.exists(obj, access_type, grantee=self) def get_id(self): identity = hashlib.md5("{},{}".format( self.email, self.password_hash).encode()).hexdigest() return "{0}-{1}".format(self.id, identity)