def define_tables(cls, metadata): import json class JSONEncodedDict(TypeDecorator): impl = VARCHAR(50) def process_bind_param(self, value, dialect): if value is not None: value = json.dumps(value) return value def process_result_value(self, value, dialect): if value is not None: value = json.loads(value) return value MutableDict = cls._type_fixture() MutableDict.associate_with(JSONEncodedDict) Table('foo', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('data', JSONEncodedDict), Column('unrelated_data', String(50)) )
def setup_mappers(cls): foo = cls.tables.foo subfoo = cls.tables.subfoo mapper(Foo, foo) mapper(SubFoo, subfoo, inherits=Foo) MutableDict.associate_with_attribute(Foo.data)
def upgrade(): meta_catalogstar = sa.Column('meta', MutableDict.as_mutable(JSON), default={}) op.add_column('catalog_star', meta_catalogstar) meta_obs = sa.Column('meta', MutableDict.as_mutable(JSON), default={}) op.add_column('observation', meta_obs)
def associate_with(sqltype): # TODO(leizhang) When we removed sqlalchemy 0.7 dependence # we can import MutableDict directly and remove ./mutable.py try: from sqlalchemy.ext.mutable import MutableDict as sa_MutableDict sa_MutableDict.associate_with(Json) except ImportError: from heat.db.sqlalchemy.mutable import MutableDict MutableDict.associate_with(Json)
def document(self): d = MutableDict() d.update(self._document) d.update({ 'kind': self.kind, 'id': self.id, 'source': self.source, 'timestamp': self.timestamp, }) return d
def define_tables(cls, metadata): MutableDict = cls._type_fixture() MutableDict.associate_with(PickleType) Table('foo', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('skip', PickleType), Column('data', PickleType), Column('unrelated_data', String(50)) )
def define_tables(cls, metadata): MutableDict = cls._type_fixture() MutableDict.associate_with(PickleType) Table( "foo", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("skip", PickleType), Column("data", PickleType), Column("unrelated_data", String(50)), )
def downgrade(): op.drop_column('catalog', 'metajson') meta = sa.Column('meta', MutableDict.as_mutable(HSTORE), nullable=False, default={}, index=True) op.add_column('catalog', meta)
def define_tables(cls, metadata): import json class JSONEncodedDict(TypeDecorator): impl = VARCHAR(50) def process_bind_param(self, value, dialect): if value is not None: value = json.dumps(value) return value def process_result_value(self, value, dialect): if value is not None: value = json.loads(value) return value MutableDict = cls._type_fixture() Table( "foo", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("data", MutableDict.as_mutable(JSONEncodedDict)), Column("non_mutable_data", JSONEncodedDict), Column("unrelated_data", String(50)), )
def define_tables(cls, metadata): MutableDict = cls._type_fixture() mutable_pickle = MutableDict.as_mutable(PickleType) Table( 'foo', metadata, Column( 'id', Integer, primary_key=True, test_needs_autoincrement=True), Column('data', mutable_pickle, default={}), )
def __init__(self, name=u"", type=u"node", id=None, schema=None, attrs=None, system_attrs=None, orderpos=None): self.name = name if not isinstance(type, unicode): warn("type arg of Node should be unicode (hint: don't create nodes with Node(type='{}')!)".format(type), DeprecationWarning) if "/" in type: warn("use separate type and schema parameters instead of 'type/schema'", DeprecationWarning) type, schema = type.split("/") self.type = type self.attrs = MutableDict() self.system_attrs = MutableDict() if id: self.id = id if schema: self.schema = schema if attrs: self.attrs.update(attrs) if system_attrs: self.system_attrs.update(system_attrs) if orderpos: self.orderpos = orderpos
def get_data_element_postgres_extensions(task, indexes): if indexes: q = task.__class__.data[indexes] else: q = task.__class__.data s = object_session(task) tup = s.query(q).filter_by(id=task.id).one() return tup[0] class json_array_length(GenericFunction): type = Integer def get_data_size_postgres_extensions(task, indexes): if indexes: q = task.__class__.data[indexes] else: q = task.__class__.data s = object_session(task) tup = s.query(json_array_length(q)).filter_by(id=task.id).one() return tup[0] MutableJSONDict = MutableDict.as_mutable(psqlJSON) JSON = psqlJSON get_data_element = get_data_element_postgres_extensions get_data_size = get_data_size_postgres_extensions
class User(db.Model, DomainObject, UserMixin): '''A registered user of the PYBOSSA system''' __tablename__ = 'user' id = Column(Integer, primary_key=True) #: UTC timestamp of the user when it's created. created = Column(Text, default=make_timestamp) email_addr = Column(Unicode(length=254), unique=True, nullable=False) #: Name of the user (this is used as the nickname). name = Column(Unicode(length=254), unique=True, nullable=False) #: Fullname of the user. fullname = Column(Unicode(length=500), nullable=False) #: Language used by the user in the PYBOSSA server. locale = Column(Unicode(length=254), default=u'en', nullable=False) api_key = Column(String(length=36), default=make_uuid, unique=True) passwd_hash = Column(Unicode(length=254), unique=True) ldap = Column(Unicode, unique=True) admin = Column(Boolean, default=False) pro = Column(Boolean, default=False) privacy_mode = Column(Boolean, default=True, nullable=False) category = Column(Integer) flags = Column(Integer) twitter_user_id = Column(BigInteger, unique=True) facebook_user_id = Column(BigInteger, unique=True) google_user_id = Column(String, unique=True) ckan_api = Column(String, unique=True) newsletter_prompted = Column(Boolean, default=False) valid_email = Column(Boolean, default=False) confirmation_email_sent = Column(Boolean, default=False) subscribed = Column(Boolean, default=False) consent = Column(Boolean, default=False) info = Column(MutableDict.as_mutable(JSONB), default=dict()) user_pref = Column(JSONB) ## Relationships task_runs = relationship(TaskRun, backref='user') projects = relationship(Project, backref='owner') blogposts = relationship(Blogpost, backref='owner') def get_id(self): '''id for login system. equates to name''' return self.name def set_password(self, password): self.passwd_hash = signer.generate_password_hash(password) def check_password(self, password): # OAuth users do not have a password if self.passwd_hash: return signer.check_password_hash(self.passwd_hash, password) return False @classmethod def public_attributes(self): """Return a list of public attributes.""" return [ 'created', 'name', 'fullname', 'info', 'n_answers', 'registered_ago', 'rank', 'score', 'locale' ] @classmethod def public_info_keys(self): """Return a list of public info keys.""" default = ['avatar', 'container', 'extra', 'avatar_url'] extra = current_app.config.get('USER_INFO_PUBLIC_FIELDS') if extra: return list(set(default).union(set(extra))) else: return default
class AnsibleScript(Script): __tablename__ = 'AnsibleScript' id = Column(Integer, ForeignKey('Script.id'), primary_key=True) playbook_path = Column(String) options = Column(MutableDict.as_mutable(PickleType), default={}) __mapper_args__ = { 'polymorphic_identity': 'AnsibleScript', } def __init__(self, playbook_path, **data): name = data['name'][0] super(AnsibleScript, self).__init__(name) self.playbook_path = playbook_path self.options = {} for key, value in data.items(): if key in ansible_options: self.options[key] = value[0] if value else None def job(self, task): loader = DataLoader() hosts = [node.ip_address for node in task.nodes] temporary_file = NamedTemporaryFile(delete=False) temporary_file.write('\n'.join(hosts)) temporary_file.close() # sources is a list of paths to inventory files" inventory = InventoryManager(loader=loader, sources=temporary_file.name) variable_manager = VariableManager(loader=loader, inventory=inventory) options_dict = { 'listtags': False, 'listtasks': False, 'listhosts': False, 'syntax': False, 'connection': 'ssh', 'module_path': None, 'forks': 100, 'remote_user': None, 'private_key_file': None, 'ssh_common_args': None, 'ssh_extra_args': None, 'sftp_extra_args': None, 'scp_extra_args': None, 'become': False, 'become_method': None, 'become_user': None, 'verbosity': None, 'check': False, 'diff': False } Options = namedtuple('Options', list(options_dict)) passwords = {} playbook_executor = PlaybookExecutor(playbooks=[self.playbook_path], inventory=inventory, variable_manager=variable_manager, loader=loader, options=Options(**options_dict), passwords=passwords) results = playbook_executor.run() return results
class RemoteAccount(db.Model): """Storage for remote linked accounts.""" __tablename__ = 'oauthclient_remoteaccount' __table_args__ = (db.UniqueConstraint('user_id', 'client_id'), ) # # Fields # id = db.Column(db.Integer, primary_key=True, autoincrement=True) """Primary key.""" user_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False) """Local user linked with a remote app via the access token.""" client_id = db.Column(db.String(255), nullable=False) """Client ID of remote application (defined in OAUTHCLIENT_REMOTE_APPS).""" extra_data = db.Column(MutableDict.as_mutable(JSONType), nullable=False) """Extra data associated with this linked account.""" # # Relationships properties # user = db.relationship(User, backref='remote_accounts') """SQLAlchemy relationship to user.""" @classmethod def get(cls, user_id, client_id): """Get RemoteAccount object for user. :param user_id: User id :param client_id: Client id. :returns: A :class:`invenio_oauthclient.models.RemoteAccount` instance. """ return cls.query.filter_by( user_id=user_id, client_id=client_id, ).first() @classmethod def create(cls, user_id, client_id, extra_data): """Create new remote account for user. :param user_id: User id. :param client_id: Client id. :param extra_data: JSON-serializable dictionary of any extra data that needs to be save together with this link. :returns: A :class:`invenio_oauthclient.models.RemoteAccount` instance. """ with db.session.begin_nested(): account = cls(user_id=user_id, client_id=client_id, extra_data=extra_data or dict()) db.session.add(account) return account def delete(self): """Delete remote account together with all stored tokens.""" with db.session.begin_nested(): db.session.delete(self) def __repr__(self): """String representation for model.""" return 'Remote Account <id={0.id}, user_id={0.user.id}>'.format(self)
class Node(DeclarativeBase, NodeMixin): """Base class for Nodes which holds all SQLAlchemy fields definitions """ __metaclass__ = BaseNodeMeta __tablename__ = "node" __versioned__ = { "base_classes": (NodeVersionMixin, MtVersionBase, DeclarativeBase), "exclude": ["subnode", "system_attrs"] } id = C(Integer, node_id_seq, server_default=node_id_seq.next_value(), primary_key=True) type = C(Unicode, index=True) schema = C(Unicode, index=True) name = C(Unicode, index=True) orderpos = C(Integer, default=1, index=True) fulltext = deferred(C(Unicode)) # indicate that this node is a subnode of a content type node # subnode exists just for performance reasons and is updated by the database # unversioned subnode = C(Boolean, server_default="false") attrs = deferred(C(MutableDict.as_mutable(JSONB))) # Migration from old mediatum: all attributes starting with "system." go here. # We should get rid of most (all?) such attributes in the future. # unversioned system_attrs = deferred(C(MutableDict.as_mutable(JSONB))) @hybrid_property def a_expr(self): """ see: Attributes""" raise Exception("node.a_expr") if "_attributes_accessor" not in self.__dict__: setattr(self, "_attributes_accessor", Attributes(self, "attrs")) return self._attributes_accessor @a_expr.expression def a(self): """ see: AttributesExpression""" if "_attributes_accessor" not in self.__dict__: setattr(self, "_attributes_accessor", AttributesExpressionAdapter(self, "attrs")) return self._attributes_accessor @a.setter def a_set(self, value): raise NotImplementedError("immutable!") @hybrid_property def sys(self): """ see: Attributes""" if "_system_attributes_accessor" not in self.__dict__: setattr(self, "_system_attributes_accessor", Attributes(self, "system_attrs")) return self._system_attributes_accessor @sys.expression def sys_expr(self): """ see: AttributesExpression""" if "_system_attributes_accessor" not in self.__dict__: setattr(self, "_system_attributes_accessor", AttributesExpressionAdapter(self, "system_attrs")) return self._system_attributes_accessor @a.setter def sys_set(self, value): raise NotImplementedError("immutable!") def __init__(self, name=u"", type=u"node", id=None, schema=None, attrs=None, system_attrs=None, orderpos=None): self.name = name if not isinstance(type, unicode): warn("type arg of Node should be unicode (hint: don't create nodes with Node(type='{}')!)".format(type), DeprecationWarning) if "/" in type: warn("use separate type and schema parameters instead of 'type/schema'", DeprecationWarning) type, schema = type.split("/") self.type = type self.attrs = MutableDict() self.system_attrs = MutableDict() if id: self.id = id if schema: self.schema = schema if attrs: self.attrs.update(attrs) if system_attrs: self.system_attrs.update(system_attrs) if orderpos: self.orderpos = orderpos @property def slow_content_children_for_all_subcontainers(self): """ !!! very slow, use content_children_for_all_subcontainers instead!!! Collects all Content nodes in all subcontainers of this node. This excludes content nodes that are children of other content nodes. """ warn("very slow, use content_children_for_all_subcontainers instead", DeprecationWarning) from contenttypes.data import Content from core import db sq = _subquery_subtree_container(self) query = db.query(Content).\ join(t_noderelation, Node.id == t_noderelation.c.cid).\ filter(t_noderelation.c.nid.in_(sq) | (t_noderelation.c.nid == self.id)).\ filter(t_noderelation.c.distance == 1) return query @property def content_children_for_all_subcontainers(self): """Collects all Content nodes in all subcontainers of this node. This excludes content nodes that are children of other content nodes. """ from contenttypes.data import Content from core import db sq = _subquery_subtree_distinct(self) return object_session(self).query(Content).filter(Node.id.in_(sq)).filter_by(subnode=False) @property def content_children_for_all_subcontainers_with_duplicates(self): """Collects all Content nodes in all subcontainers of this node. This excludes content nodes that are children of other content nodes. This method can be much faster than content_children_for_all_subcontainers, but may return lesser nodes than expected (when using limit). Don't use distinct() on this method, use content_children_for_all_subcontainers instead if you need it! """ from contenttypes.data import Content nr = t_noderelation # TODO: check if it's better to use the _subquery_subtree() here return object_session(self).query(Content).filter_by(subnode=False).join(nr, Content.id == nr.c.cid).filter(nr.c.nid==self.id) @property def content_children_count_for_all_subcontainers(self): if USE_CACHED_CHILDCOUNT: return exec_sqlfunc(object_session(self), mediatumfunc.count_content_children_for_all_subcontainers(self.id)) else: return self.content_children_for_all_subcontainers.count() def all_children_by_query(self, query): sq = _subquery_subtree_distinct(self) query = query.filter(Node.id.in_(sq)) return query @staticmethod def req_has_access_to_node_id(node_id, accesstype, req=None, date=func.current_date()): # XXX: the database-independent code could move to core.node from core.transition import request from core.users import user_from_session if req is None: req = request user = user_from_session(req.session) # XXX: like in mysql version, what's the real solution? try: ip = IPv4Address(req.remote_addr) except AddressValueError: logg.warn("illegal IP address %s, refusing IP-based access", req.remote_addr) ip = None return Node.has_access_to_node_id(node_id, accesstype, user, ip, date) @staticmethod def has_access_to_node_id(node_id, accesstype, user=None, ip=None, date=None): # XXX: the database-independent code could move to core.node from core import db from core.users import get_guest_user if user is None: user = get_guest_user() if user.is_admin: return True if ip is None: ip = IPv4Address("0.0.0.0") if date is None: date = func.current_date() accessfunc = access_funcs[accesstype] group_ids = user.group_ids access = accessfunc(node_id, group_ids, ip, date) return db.session.execute(select([access])).scalar() def _parse_searchquery(self, searchquery): """ * `searchquery` is a string type: Parses `searchquery` and transforms it into the search tree. * `searchquery` already is already in search tree form: work is already done, return it unchanged. """ from core.search import parse_searchquery if isinstance(searchquery, SearchTreeElement): searchtree = searchquery else: searchtree = parse_searchquery(searchquery) return searchtree def _search_query_object(self): """Builds the query object that is used as basis for content node searches below this node""" from contenttypes import Content, Collections q = object_session(self).query sq = _subquery_subtree(self) if self == q(Collections).one(): # no need to filter, the whole tree can be searched base_query = q(Content) else: base_query = q(Content).filter(Node.id.in_(sq)) return base_query def search(self, searchquery, languages=None): """Creates a search query. :param searchquery: query in search language or parsed query (search tree) as `SearchTreeElement`: :param language: sequence of language config strings matching Fts.config :returns: Node Query """ from core.database.postgres.search import apply_searchtree_to_query searchtree = self._parse_searchquery(searchquery) query = self._search_query_object() return apply_searchtree_to_query(query, searchtree, languages) def search_multilang(self, searchquery, languages=None): """Creates search queries for a sequence of languages. :param searchquery: query in search language or parsed query (search tree) as `SearchTreeElement`: :param languages: language config strings matching Fts.config :returns list of Node Query """ from core.database.postgres.search import apply_searchtree_to_query searchtree = self._parse_searchquery(searchquery) query = self._search_query_object() return [apply_searchtree_to_query(query, searchtree, l) for l in languages] @property def tagged_versions(self): Transaction = versioning_manager.transaction_cls TransactionMeta = versioning_manager.transaction_meta_cls version_cls = version_class(self.__class__) return (self.versions.join(Transaction, version_cls.transaction_id == Transaction.id).join(Transaction.meta_relation). filter(TransactionMeta.key == u"tag")) def get_tagged_version(self, tag): return self.tagged_versions.filter_by(value=tag).scalar() def get_published_version(self): Transaction = versioning_manager.transaction_cls TransactionMeta = versioning_manager.transaction_meta_cls version_cls = version_class(self.__class__) published_versions = self.versions.join(Transaction, version_cls.transaction_id == Transaction.id).\ join(Transaction.meta_relation). filter(TransactionMeta.key == u"publish") return published_versions.scalar() def new_tagged_version(self, tag=None, comment=None, publish=None, user=None): """Returns a context manager that manages the creation of a new tagged node version. :param tag: a unicode tag assigned to the transaction belonging to the new version. If none is given, assume that we want to add a new numbered version. The tag will be the incremented version number of the last numbered version. If no numbered version is present, assign 1 to the last version and 2 to the new version. :param comment: optional comment for the transaction :param user: user that will be associated with the transaction. """ node = self class VersionContextManager(object): def __enter__(self): self.session = s = object_session(node) if s.new or s.dirty: raise Exception("Refusing to create a new tagged node version. Session must be clean!") uow = versioning_manager.unit_of_work(s) tx = uow.create_transaction(s) if user is not None: tx.user = user if tag: if node.get_tagged_version(tag): raise ValueError("tag already exists") tx.meta[u"tag"] = tag elif publish: if node.get_published_version(): raise ValueError("publish version already exists") tx.meta[u"publish"] = publish else: NodeVersion = version_class(node.__class__) # in case you were wondering: order_by(None) resets the default order_by last_tagged_version = node.tagged_versions.order_by(None).order_by(NodeVersion.transaction_id.desc()).first() if last_tagged_version is not None: next_version = int(last_tagged_version.tag) + 1 else: node.versions[-1].tag = u"1" next_version = 2 tx.meta[u"tag"] = unicode(next_version) if comment: tx.meta[u"comment"] = comment return tx def __exit__(self, exc_type, exc_value, traceback): if exc_type: self.session.rollback() else: self.session.commit() return VersionContextManager() def is_descendant_of(self, node): return exec_sqlfunc(object_session(self), mediatumfunc.is_descendant_of(self.id, node.id)) def _get_nearest_ancestor_by_type(self, ancestor_type): """Returns a nearest ancestor of `ancestor_type`. If none is found, return `Collections` as default. It's undefined which one will be returned if more than one nearest ancestor is found. """ nr = t_noderelation q = object_session(self).query maybe_ancestor = (q(ancestor_type) .join(nr, Node.id == nr.c.nid) .filter_by(cid=self.id) .order_by(nr.c.distance).limit(1).first()) if maybe_ancestor is None: from contenttypes import Collections return q(Collections).one() return maybe_ancestor def get_container(self): from contenttypes import Container return self._get_nearest_ancestor_by_type(Container) def get_collection(self): from contenttypes import Collection return self._get_nearest_ancestor_by_type(Collection) @property def has_files(self): return len(self.file_objects) > 0 __mapper_args__ = { 'polymorphic_identity': 'node', 'polymorphic_on': type } def to_yaml(self): """overwrite default DeclarativeBase.to_yaml method because we need to convert MutableDicts first """ node_dict = self.to_dict() node_dict["attrs"] = dict(node_dict["attrs"]) node_dict["system_attrs"] = dict(node_dict["system_attrs"]) return pyaml.dump(node_dict)
class Member(Base): __tablename__ = 'members' guild_id = Column(String, ForeignKey('guilds.id'), primary_key=True) id = Column(String, ForeignKey('users.id'), primary_key=True) json = Column(MutableDict.as_mutable(JSON), default={}, nullable=False)
if value is not None: value = json.dumps(value) return value def process_literal_param(self, value, dialect): return value def process_result_value(self, value, dialect): try: return json.loads(value) except (ValueError, TypeError): return None Json = MutableDict.as_mutable(JSONEncodedDict) class User(Base): __tablename__ = 'user' column_id = Column(Integer, primary_key=True, autoincrement=True) user_name = Column(String(32), nullable=False, unique=True) email = Column(String(64), nullable=False, unique=True) category = Column(String(128)) endpoint = Column(String(255)) endpoint_data = Column(Text()) class Monitor(Base): __tablename__ = 'monitor' column_id = Column(Integer, primary_key=True, autoincrement=True)
class Project(db.Model, DomainObject): '''A microtasking Project to which Tasks are associated. ''' __tablename__ = 'project' #: ID of the project id = Column(Integer, primary_key=True) #: UTC timestamp when the project is created created = Column(Text, default=make_timestamp) #: UTC timestamp when the project is updated (or any of its relationships) updated = Column(Text, default=make_timestamp, onupdate=make_timestamp) #: Project name name = Column(Unicode(length=255), unique=True, nullable=False) #: Project slug for the URL short_name = Column(Unicode(length=255), unique=True, nullable=False) #: Project description description = Column(Unicode(length=255), nullable=False) #: Project long description long_description = Column(UnicodeText) #: Project webhook webhook = Column(Text) #: If the project allows anonymous contributions allow_anonymous_contributors = Column(Boolean, default=True) #: If the project is published published = Column(Boolean, nullable=False, default=False) # If the project is hidden hidden = Column(Boolean, default=False) # If the project is featured featured = Column(Boolean, nullable=False, default=False) # Secret key for project secret_key = Column(Text, default=make_uuid) # Zip download zip_download = Column(Boolean, default=True) # If the project owner has been emailed contacted = Column(Boolean, nullable=False, default=False) #: Project owner_id owner_id = Column(Integer, ForeignKey('user.id'), nullable=False) #: Project Category category_id = Column(Integer, ForeignKey('category.id'), nullable=False) #: Project info field formatted as JSONB info = Column(MutableDict.as_mutable(JSONB), default=dict()) #: If emails are sent to users about new tasks email_notif = Column(Boolean, default=False) tasks = relationship(Task, cascade='all, delete, delete-orphan', backref='project') task_runs = relationship(TaskRun, backref='project', cascade='all, delete-orphan', order_by='TaskRun.finish_time.desc()') category = relationship(Category) blogposts = relationship(Blogpost, cascade='all, delete-orphan', backref='project') owners_ids = Column(MutableList.as_mutable(ARRAY(Integer)), default=list()) def needs_password(self): return self.get_passwd_hash() is not None def get_passwd_hash(self): return self.info.get('passwd_hash') def set_password(self, password): if len(password) > 1: self.info['passwd_hash'] = signer.generate_password_hash(password) return True self.info['passwd_hash'] = None return False def check_password(self, password): if self.needs_password(): return signer.check_password_hash(self.get_passwd_hash(), password) return False def has_autoimporter(self): return self.get_autoimporter() is not None def get_autoimporter(self): return self.info.get('autoimporter') def set_autoimporter(self, new=None): self.info['autoimporter'] = new def delete_autoimporter(self): del self.info['autoimporter'] def has_presenter(self): if current_app.config.get('DISABLE_TASK_PRESENTER') is True: return True else: return self.info.get('task_presenter') not in ('', None) def get_default_n_answers(self): return self.info.get('default_n_answers', 1) def set_default_n_answers(self, default_n_answers): self.info['default_n_answers'] = default_n_answers @classmethod def public_attributes(self): """Return a list of public attributes.""" return ['id', 'description', 'info', 'n_tasks', 'n_volunteers', 'name', 'overall_progress', 'short_name', 'created', 'category_id', 'long_description', 'last_activity', 'last_activity_raw', 'n_task_runs', 'n_results', 'owner', 'updated', 'featured', 'owner_id', 'n_completed_tasks', 'n_blogposts', 'owners_ids', 'published'] @classmethod def public_info_keys(self): """Return a list of public info keys.""" default = ['container', 'thumbnail', 'thumbnail_url', 'tutorial', 'sched'] extra = current_app.config.get('PROJECT_INFO_PUBLIC_FIELDS') if extra: return list(set(default).union(set(extra))) else: return default def get_presenter_field_set(self): fields = set() task_presenter = self.info.get('task_presenter') if not task_presenter: return fields search_backward_stop = 0 for match in re.finditer('\.info\.([a-zA-Z0-9_]+)', task_presenter): linebreak_index = task_presenter.rfind( '\n', search_backward_stop, match.start()) if linebreak_index > -1: search_start = linebreak_index else: search_start = search_backward_stop if task_presenter.rfind('//', search_start, match.start()) > -1: continue comment_start = task_presenter.rfind( '/*', search_backward_stop, match.start()) if comment_start > -1: search_backward_stop = comment_start comment_end = 'task_presenter'.rfind( '*/', search_backward_stop, match.start()) if comment_end < 0: continue field = match.group(1) if not field.endswith('__upload_url'): fields.add(field) search_backward_stop = match.end() return fields def set_project_users(self, users): from pybossa.cache.users import get_users_access_levels from pybossa.data_access import can_assign_user valid_users = set([]) proj_levels = self.info.get('data_access', []) if not proj_levels: return users = get_users_access_levels(users) for user in users: user_levels = user.get('data_access', []) if can_assign_user(proj_levels, user_levels): valid_users.add(user['id']) self.info['project_users'] = list(valid_users) def get_project_users(self): return self.info.get('project_users', []) def get_quiz(self): quiz = self.info.get( 'quiz', { 'enabled': False, 'passing': 0, 'questions': 0, 'short_circuit': True, 'completion_mode': 'short_circuit' } ) return quiz def set_quiz(self, quiz): self.info['quiz'] = quiz def get_gold_task_probability(self): return self.info.get('sched_gold_task_probability', .1) def set_gold_task_probability(self, value): self.info['sched_gold_task_probability'] = float(value)
import sqlalchemy_utils import sqlalchemy_utils.types.json from sqlalchemy_json import NestedMutable as _NestedMutable class MutableJson(sqlalchemy.types.JSON): """JSON type for SQLAlchemy with change tracking at top level.""" class NestedMutableJson(sqlalchemy.types.JSON): """JSON type for SQLAlchemy with nested change tracking.""" _MutableDict.associate_with(MutableJson) _NestedMutable.associate_with(NestedMutableJson) # Monkey patch sqlalchemy_utils serializer. Note that sqlalchemy_json inherits # from the data type from sqlalchemy_utils - so setting serializer there takes # care of both instances. # # TODO: # 1. This patching should maybe be done differently -- maybe override the # JsonType-method for serializing to the db? # 2. We might want to implement our own Json serializer to deal with custom # data types. The flask one simply extends the stdlib json module with # support for serializing datetime.datatime and uuid.UUID # 3. Note that JsonType only use the json-implementation for serializing to # json-strings for all db-engines *but* postgres -- if using postgres, json # serialization is delegated to the engine, so we'll have to make sure to
class Annotation(Base): """Model class representing a single annotation.""" __tablename__ = 'annotation' __table_args__ = ( # Tags are stored in an array-type column, and indexed using a # generalised inverted index. For more information on the use of GIN # indices for array columns, see: # # http://www.databasesoup.com/2015/01/tag-all-things.html # http://www.postgresql.org/docs/9.5/static/gin-intro.html # sa.Index('ix__annotation_tags', 'tags', postgresql_using='gin'), sa.Index('ix__annotation_updated', 'updated'), ) #: Annotation ID: these are stored as UUIDs in the database, and mapped #: transparently to a URL-safe Base64-encoded string. id = sa.Column(types.URLSafeUUID, server_default=sa.func.uuid_generate_v1mc(), primary_key=True) #: The timestamp when the annotation was created. created = sa.Column(sa.DateTime, default=datetime.datetime.utcnow, server_default=sa.func.now(), nullable=False) #: The timestamp when the user edited the annotation last. updated = sa.Column(sa.DateTime, server_default=sa.func.now(), default=datetime.datetime.utcnow, nullable=False) #: The full userid (e.g. 'acct:[email protected]') of the owner of this #: annotation. userid = sa.Column(sa.UnicodeText, nullable=False, index=True) #: The string id of the group in which this annotation is published. #: Defaults to the global public group, "__world__". groupid = sa.Column(sa.UnicodeText, default='__world__', server_default='__world__', nullable=False, index=True) #: The textual body of the annotation. _text = sa.Column('text', sa.UnicodeText) #: The Markdown-rendered and HTML-sanitized textual body of the annotation. _text_rendered = sa.Column('text_rendered', sa.UnicodeText) #: The tags associated with the annotation. tags = sa.Column( types.MutableList.as_mutable( pg.ARRAY(sa.UnicodeText, zero_indexes=True))) #: A boolean indicating whether this annotation is shared with members of #: the group it is published in. "Private"/"Only me" annotations have #: shared=False. shared = sa.Column(sa.Boolean, nullable=False, default=False, server_default=sa.sql.expression.false()) #: The URI of the annotated page, as provided by the client. _target_uri = sa.Column('target_uri', sa.UnicodeText) #: The URI of the annotated page in normalized form. _target_uri_normalized = sa.Column('target_uri_normalized', sa.UnicodeText) #: The serialized selectors for the annotation on the annotated page. target_selectors = sa.Column(types.AnnotationSelectorJSONB, default=list, server_default=sa.func.jsonb('[]')) #: An array of annotation IDs which are ancestors of this annotation. references = sa.Column(pg.ARRAY(types.URLSafeUUID), default=list, server_default=sa.text('ARRAY[]::uuid[]')) #: Any additional serialisable data provided by the client. extra = sa.Column(MutableDict.as_mutable(pg.JSONB), default=dict, server_default=sa.func.jsonb('{}'), nullable=False) document_id = sa.Column(sa.Integer, sa.ForeignKey('document.id'), nullable=False) document = sa.orm.relationship('Document', backref='annotations') @hybrid_property def target_uri(self): return self._target_uri @target_uri.setter def target_uri(self, value): self._target_uri = value self._target_uri_normalized = uri.normalize(value) @hybrid_property def target_uri_normalized(self): return self._target_uri_normalized @hybrid_property def text(self): return self._text @text.setter def text(self, value): self._text = value self._text_rendered = markdown.render(value) @hybrid_property def text_rendered(self): return self._text_rendered @property def parent_id(self): """ Return the ID of the annotation that this annotation is a reply to. Return None if this annotation is not a reply. """ if self.references: return self.references[-1] @property def thread_root_id(self): """ Return the ID of the root annotation of this annotation's thread. Return the ID of the root annotation of the thread to which this annotation belongs. May be this annotation's own ID if it is the root annotation of its thread. """ if self.references: return self.references[0] else: return self.id def __acl__(self): """Return a Pyramid ACL for this annotation.""" acl = [] if self.shared: group = 'group:{}'.format(self.groupid) if self.groupid == '__world__': group = security.Everyone acl.append((security.Allow, group, 'read')) else: acl.append((security.Allow, self.userid, 'read')) for action in ['admin', 'update', 'delete']: acl.append((security.Allow, self.userid, action)) # If we haven't explicitly authorized it, it's not allowed. acl.append(security.DENY_ALL) return acl def __repr__(self): return '<Annotation %s>' % self.id
_null = None _type = object def process_bind_param(self, value, dialect): return json.dumps(value) def process_literal_param(self, value, dialect): return value def process_result_value(self, value, dialect): try: value = json.loads(value) except (ValueError, TypeError): value = self._null return value class List(Json): _null = [] _type = list class Dict(Json): _null = {} _type = dict MutableDict.associate_with(Dict) Base = declarative_base()
impl = HSTORE def process_bind_param(self, value, dialect): if not isinstance(value, dict): return value ret = {k: json.dumps(v) for k, v in value.items()} return ret def process_result_value(self, value, dialect): if not value: return MutableDict() ret = MutableDict({k: json.loads(v) for k, v in value.items()}) return ret MutableDict.associate_with(JSONValuesColumn) class IntegerEnumColumn(types.TypeDecorator): impl = types.INTEGER def __init__(self, enum_values): super(IntegerEnumColumn, self).__init__() self.enum_values = enum_values self.reverse_enum_values = reverse_dict(enum_values) def process_bind_param(self, value, dialect): return self.reverse_enum_values.get(value, value) def process_result_value(self, value, dialect): return self.enum_values.get(value, value)
#XXX NOTICE XXX DO NOT NAME THINGS types.py it breaks EVERYTHING from sqlalchemy.types import PickleType from sqlalchemy.dialects import postgres,postgresql from sqlalchemy.ext.mutable import MutableDict def array_base(column_type): array = PickleType() array.with_variant(postgres.ARRAY(column_type), 'postgres') array.with_variant(postgres.ARRAY(column_type), 'postgresql') return array Array=array_base _DictType = MutableDict.as_mutable(PickleType) _DictType.with_variant(MutableDict.as_mutable(postgres.HSTORE), 'postgres') #_DictType.with_variant(MutableDict.as_mutable(postgresql.HSTORE), 'postgresql') #_DictType.with_variant(MutableDict.as_mutable(postgresql.HSTORE), 'psycopg2') #_DictType.with_variant(MutableDict.as_mutable(postgresql.HSTORE), 'postgresql+psycopg2') DictType=_DictType #FIXME not working as hstore :/ __all__=[ 'Array', 'DictType', ] #ArrayFloat = PickleType() #ArrayFloat.with_variant(postgresql.ARRAY(Float), 'postgresql') #ArrayString = PickleType() #ArrayString.with_variant(postgresql.ARRAY(String), 'postgresql')
return cls(value) return super(cls).coerce(key, value) class NestedMutable(Mutable): """SQLAlchemy `mutable` extension with nested change tracking.""" @classmethod def coerce(cls, key, value): """Convert plain dictionary to NestedMutable.""" if value is None: return value if isinstance(value, cls): return value if isinstance(value, dict): return NestedMutableDict.coerce(key, value) if isinstance(value, list): return NestedMutableList.coerce(key, value) return super(cls).coerce(key, value) class MutableJson(JSONType): """JSON type for SQLAlchemy with change tracking at top level.""" class NestedMutableJson(JSONType): """JSON type for SQLAlchemy with nested change tracking.""" MutableDict.associate_with(MutableJson) NestedMutable.associate_with(NestedMutableJson)
return dialect.type_descriptor(mysql.LONGTEXT()) else: return self.impl def process_bind_param(self, value, dialect): return dumps(value) def process_result_value(self, value, dialect): return loads(value) # TODO(leizhang) When we removed sqlalchemy 0.7 dependence # we can import MutableDict directly and remove ./mutable.py try: from sqlalchemy.ext.mutable import MutableDict as sa_MutableDict sa_MutableDict.associate_with(Json) except ImportError: from dragon.db.sqlalchemy.mutable import MutableDict MutableDict.associate_with(Json) class DragonBase(object): """Base class for Heat Models.""" __table_args__ = {'mysql_engine': 'InnoDB'} __table_initialized__ = False created_at = sqlalchemy.Column(sqlalchemy.DateTime, default=timeutils.utcnow) updated_at = sqlalchemy.Column(sqlalchemy.DateTime, onupdate=timeutils.utcnow) def save(self, session=None):
@author: peterb ''' from blueshed.model_helpers.sqla_views import view from blueshed.model_helpers.sql_extensions import JSONEncodedDict from sqlalchemy.ext.mutable import MutableDict from sqlalchemy.sql.expression import select, join from sqlalchemy.sql.functions import func from examples.simple.model import * from blueshed.model_helpers.access_model import Person, Permission,\ person_permissions_permission Person._token = Column(String(80)) Person._preferences = Column(MutableDict.as_mutable(JSONEncodedDict(255))) Person.firstname = Column(String(80)) Person.lastname = Column(String(80)) Person.photo = Column(String(128)) ''' An example View ''' q = select([Person.id.label('id'), Person.email.label('email'), func.count(Permission.id).label('permission_count')]).\ select_from(join(Person, person_permissions_permission, Person.id==person_permissions_permission.c.permissions_id).\ join(Permission, Permission.id==person_permissions_permission.c.permission_id)).\
class GroupInfo(BASE): # pylint:disable=too-few-public-methods """ Some info about an LMS group that was created in h. This info is stored purely for metrics/analytics purposes and shouldn't be used for application logic. The app should treat the h API as the "single-source of truth" about what h groups exist and what their IDs and other properties are. """ __tablename__ = "group_info" id = sa.Column(sa.Integer(), autoincrement=True, primary_key=True) #: The authority_provided_id of the group in h. #: #: This corresponds to the ID part of the groupid that's used in h's groups #: API. For example if the groupid is "group:[email protected]" #: then the authority_provided_id is the "SOME_ID" part without the leading #: "group:" or the trailing "@lms.hypothes.is". #: #: This also corresponds to the group.authority_provided_id column in h's #: DB. authority_provided_id = sa.Column(sa.UnicodeText(), nullable=False, unique=True) #: The LTI consumer_key (oauth_consumer_key) of the application instance #: that this access token belongs to. consumer_key = sa.Column( sa.String(), sa.ForeignKey("application_instances.consumer_key", ondelete="cascade"), nullable=False, ) #: The ApplicationInstance that this group belongs to. application_instance = sa.orm.relationship("ApplicationInstance", back_populates="group_infos") #: The value of the context_id param this group was last launched with. context_id = sa.Column(sa.UnicodeText()) #: The value of the context_title param this group was last launched with. context_title = sa.Column(sa.UnicodeText()) #: The value of the context_label param this group was last launched with. context_label = sa.Column(sa.UnicodeText()) #: The value of the tool_consumer_info_product_family_code param this group was last launched with. tool_consumer_info_product_family_code = sa.Column(sa.UnicodeText()) #: The value of the tool_consumer_info_version param this group was last launched with. tool_consumer_info_version = sa.Column(sa.UnicodeText()) #: The value of the tool_consumer_instance_name param this group was last launched with. tool_consumer_instance_name = sa.Column(sa.UnicodeText()) #: The value of the tool_consumer_instance_description param this group was last launched with. tool_consumer_instance_description = sa.Column(sa.UnicodeText()) #: The value of the tool_consumer_instance_url param this group was last launched with. tool_consumer_instance_url = sa.Column(sa.UnicodeText()) #: The value of the tool_consumer_instance_contact_email param this group was last launched with. tool_consumer_instance_contact_email = sa.Column(sa.UnicodeText()) #: The value of the tool_consumer_instance_guid param this group was last launched with. tool_consumer_instance_guid = sa.Column(sa.UnicodeText()) #: The value of the custom_canvas_api_domain param this group was last launched with. custom_canvas_api_domain = sa.Column(sa.UnicodeText()) #: The value of the custom_canvas_course_id param this group was last launched with. custom_canvas_course_id = sa.Column(sa.UnicodeText()) #: A dict of info about this group. info = sa.Column(MutableDict.as_mutable(JSONB)) def __init__(self, *args, **kwargs): kwargs.setdefault("info", {}) kwargs["info"].setdefault("instructors", []) kwargs["info"].setdefault("type", None) super().__init__(*args, **kwargs) @property def instructors(self): return self.info["instructors"] @instructors.setter def instructors(self, new_instructors): self.info["instructors"] = new_instructors @property def type(self): return self.info["type"] @type.setter def type(self, new_type): self.info["type"] = new_type def upsert_instructor(self, new_instructor): updated_instructors = [] found = False for existing_instructor in self.instructors: if existing_instructor["username"] == new_instructor["username"]: updated_instructors.append(new_instructor) found = True else: updated_instructors.append(existing_instructor) if not found: updated_instructors.append(new_instructor) if updated_instructors != self.instructors: self.instructors = updated_instructors
return json.loads(value) class AstonFrameBinary(TypeDecorator): impl = LargeBinary def process_bind_param(self, value, dialect): if value is not None: return value.compress() def process_result_value(self, value, dialect): if value is not None: return decompress(value) MutableDict.associate_with(JSONDict) Base = declarative_base() def initialize_sql(engine): DBSession = scoped_session(sessionmaker(expire_on_commit=False)) DBSession.configure(bind=engine) Base.metadata.bind = engine Base.metadata.create_all(engine) return DBSession def quick_sqlite(filename): from sqlalchemy import create_engine
class Survey(Base): __tablename__ = 'surveys' name = db.Column(db.String(255), nullable=False, unique=True) pretty_name = db.Column(db.String(80)) language = db.Column(db.String(2)) about_text = db.Column(db.Text) terms_of_service = db.Column(db.Text) contact_email = db.Column(db.String(255)) avatar_uri = db.Column(db.String(255)) max_survey_days = db.Column(db.Integer, default=14) max_prompts = db.Column(db.Integer, default=20) trip_break_interval = db.Column(db.Integer, nullable=False, default=360) trip_subway_buffer = db.Column(db.Integer, nullable=False, default=300) last_export = db.Column(MutableDict.as_mutable(JSONB)) record_acceleration = db.Column(db.Boolean, default=True) record_mode = db.Column(db.Boolean, default=True) web_users = db.relationship('WebUser', backref='survey', cascade='all, delete-orphan', lazy='dynamic') survey_questions = db.relationship('SurveyQuestion', backref='survey', cascade='all, delete-orphan', lazy='dynamic') prompt_questions = db.relationship('PromptQuestion', backref='survey', cascade='all, delete-orphan', lazy='dynamic') mobile_users = db.relationship('MobileUser', backref='survey', cascade='all, delete-orphan', lazy='dynamic') survey_responses = db.relationship('SurveyResponse', backref='survey', cascade='all, delete-orphan', lazy='dynamic') prompt_responses = db.relationship('PromptResponse', backref='survey', cascade='all, delete-orphan', lazy='dynamic') cancelled_prompts = db.relationship('CancelledPromptResponse', backref='survey', cascade='all, delete-orphan', lazy='dynamic') mobile_coordinates = db.relationship('MobileCoordinate', backref='survey', cascade='all, delete-orphan', lazy='dynamic') subway_stops = db.relationship('SubwayStop', backref='survey', cascade='all, delete-orphan', lazy='dynamic') researcher_invite_token = db.relationship('ResearcherInviteToken', backref='survey', cascade='all, delete-orphan', lazy='dynamic') def __repr__(self): return '<Survey %d>' % self.id
class PredictionTile(db.Model): """ Store individual tile predictions """ __tablename__ = 'prediction_tiles' id = db.Column(db.Integer, primary_key=True) prediction_id = db.Column(db.BigInteger, db.ForeignKey('predictions.id', name='fk_predictions'), nullable=False) quadkey = db.Column(db.String, nullable=False) quadkey_geom = db.Column(Geometry('POLYGON', srid=4326), nullable=False) centroid = db.Column(Geometry('POINT', srid=4326)) predictions = db.Column(postgresql.JSONB, nullable=False) validity = db.Column(MutableDict.as_mutable(postgresql.JSONB), nullable=True) prediction_tiles_quadkey_idx = db.Index( 'prediction_tiles_quadkey_idx', 'prediction_tiles.quadkey', postgresql_ops={'quadkey': 'text_pattern_ops'}) @staticmethod def get(predictiontile_id: int): query = db.session.query( PredictionTile.id, PredictionTile.prediction_id, PredictionTile.validity, ).filter(PredictionTile.id == predictiontile_id) return PredictionTile.query.get(predictiontile_id) def update(self, validity): self.validity = validity db.session.commit() @staticmethod def inferences(prediction_id: int): results = db.session.execute( text(''' SELECT DISTINCT jsonb_object_keys(predictions) FROM prediction_tiles WHERE prediction_id = :pred '''), { 'pred': prediction_id }).fetchall() inferences = [] for res in results: inferences.append(res[0]) return inferences @staticmethod def count(prediction_id: int): return db.session.query( func.count(PredictionTile.quadkey).label("count")).filter( PredictionTile.prediction_id == prediction_id).one() @staticmethod def bbox(prediction_id: int): result = db.session.execute( text(''' SELECT ST_Extent(quadkey_geom) FROM prediction_tiles WHERE prediction_id = :pred '''), { 'pred': prediction_id }).fetchone() bbox = [] for corners in result[0].replace('BOX(', '').replace(')', '').split(' '): for corner in corners.split(','): bbox.append(float(corner)) return bbox def mvt(prediction_id: int, z: int, x: int, y: int): grid = mercantile.xy_bounds(x, y, z) result = db.session.execute( text(''' SELECT ST_AsMVT(q, 'data', 4096, 'geom', 'id') AS mvt FROM ( SELECT p.id AS id, quadkey AS quadkey, predictions || COALESCE(v.validity, '{}'::JSONB) AS props, ST_AsMVTGeom(quadkey_geom, ST_Transform(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy, 3857), 4326), 4096, 256, false) AS geom FROM prediction_tiles AS p LEFT JOIN ( SELECT id, JSONB_Object_Agg('v_'||key, value) AS validity FROM prediction_tiles, jsonb_each(validity) GROUP BY id ) AS v ON p.id = v.id WHERE p.prediction_id = :pred AND ST_Intersects(p.quadkey_geom, ST_Transform(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy, 3857), 4326)) ) q '''), { 'pred': prediction_id, 'minx': grid[0], 'miny': grid[1], 'maxx': grid[2], 'maxy': grid[3] }).fetchone() return bytes(result.values()[0]) @staticmethod def get_tiles_by_quadkey(prediction_id: int, quadkeys: tuple, zoom: int): return db.session.query( func.substr(PredictionTile.quadkey, 1, zoom).label('qaudkey'), func.avg( cast( cast(PredictionTile.predictions['ml_prediction'], sqlalchemy.String), sqlalchemy.Float)).label('ml_prediction'), func.avg( cast( cast(PredictionTile.predictions['osm_building_area'], sqlalchemy.String), sqlalchemy.Float)).label('osm_building_area')).filter( PredictionTile.prediction_id == prediction_id).filter( func.substr(PredictionTile.quadkey, 1, zoom).in_(quadkeys)).group_by( func.substr( PredictionTile.quadkey, 1, zoom)).all() @staticmethod def get_aggregate_for_polygon(prediction_id: int, polygon: str): return db.session.query( func.avg( cast( cast(PredictionTile.predictions['ml_prediction'], sqlalchemy.String), sqlalchemy.Float)).label('ml_prediction'), func.avg( cast( cast(PredictionTile.predictions['osm_building_area'], sqlalchemy.String), sqlalchemy.Float)).label('osm_building_area')).filter( PredictionTile.prediction_id == prediction_id).filter( ST_Within(PredictionTile.centroid, ST_GeomFromText(polygon)) == 'True').one()
class Project(db.Model, DomainObject): '''A microtasking Project to which Tasks are associated. ''' __tablename__ = 'project' #: ID of the project id = Column(Integer, primary_key=True) #: UTC timestamp when the project is created created = Column(Text, default=make_timestamp) #: UTC timestamp when the project is updated (or any of its relationships) updated = Column(Text, default=make_timestamp, onupdate=make_timestamp) #: Project name name = Column(Unicode(length=255), unique=True, nullable=False) #: Project slug for the URL short_name = Column(Unicode(length=255), unique=True, nullable=False) #: Project description description = Column(Unicode(length=255), nullable=False) #: Project long description long_description = Column(UnicodeText) #: Project webhook webhook = Column(Text) #: If the project allows anonymous contributions allow_anonymous_contributors = Column(Boolean, default=True) long_tasks = Column(Integer, default=0) #: If the project is hidden hidden = Column(Integer, default=0) # If the project is featured featured = Column(Boolean, nullable=False, default=False) # If the project owner has been emailed contacted = Column(Boolean, nullable=False, default=False) #: Project owner_id owner_id = Column(Integer, ForeignKey('user.id'), nullable=False) time_estimate = Column(Integer, default=0) time_limit = Column(Integer, default=0) calibration_frac = Column(Float, default=0) bolt_course_id = Column(Integer, default=0) #: Project Category category_id = Column(Integer, ForeignKey('category.id'), nullable=False) #: Project info field formatted as JSON info = Column(MutableDict.as_mutable(JSON)) tasks = relationship(Task, cascade='all, delete, delete-orphan', backref='project') task_runs = relationship(TaskRun, backref='project', cascade='all, delete-orphan', order_by='TaskRun.finish_time.desc()') category = relationship(Category) blogposts = relationship(Blogpost, cascade='all, delete-orphan', backref='project') def needs_password(self): return self.get_passwd_hash() is not None def get_passwd_hash(self): return self.info.get('passwd_hash') def get_passwd(self): if self.needs_password(): return signer.loads(self.get_passwd_hash()) return None def set_password(self, password): if len(password) > 1: self.info['passwd_hash'] = signer.dumps(password) return True self.info['passwd_hash'] = None return False def check_password(self, password): if self.needs_password(): return self.get_passwd() == password return False def has_autoimporter(self): return self.get_autoimporter() is not None def get_autoimporter(self): return self.info.get('autoimporter') def set_autoimporter(self, new=None): self.info['autoimporter'] = new def delete_autoimporter(self): del self.info['autoimporter']
class Installation(db.Model, ModelIter): __tablename__ = 'installations' allowed_widget = True fillable = [ 'installed_capacity', # kWp 'egauge_url', 'egauge_serial', 'egauge_mac', 'start_date', 'specific_yield', # kWh/kWp/Year 'project_id', 'sale_type_id', 'price_per_kwp', 'responsible_party', 'setup_summary' ] id = db.Column(db.Integer, primary_key=True) # project can have multiple installations because customers may ask to add more equipment after project is done project = relationship(CustomerProject, uselist=False, backref='installations', cascade='all, delete') responsible_party = db.Column(db.String(32)) installed_capacity = db.Column(db.Numeric(8, 3), nullable=False) sale_type = relationship(SaleType, uselist=False, lazy='joined') price_per_kwp = db.Column(db.Numeric(10, 2), nullable=False) setup_summary = db.Column( MutableDict.as_mutable(db.JSON), comment= 'A JSON schema that allows free form data, i.e. historical consumption data', server_default=('{' '"historical_consumption": [],' '"historical_power": [],' '"expected_generation": []' '}')) panels = relationship( InstallationPanelModel, backref='installations', primaryjoin=id == InstallationPanelModel.installation_id, lazy='joined') inverters = relationship( InstallationInverterModel, backref='installations', primaryjoin=id == InstallationInverterModel.installation_id, lazy='joined') egauge_url = db.Column(db.String(255, collation=configs.DB_COLLATION)) egauge_serial = db.Column(db.String(255, collation=configs.DB_COLLATION)) egauge_mac = db.Column(MacAddress) start_date = db.Column(db.DateTime()) specific_yield = db.Column(db.SmallInteger) @property def installation_size(self): if self.installed_capacity < 50: return 'Pequeño' elif 51 <= self.installed_capacity <= 200: return 'Mediano' elif 201 <= self.installed_capacity <= 500: return 'Comercial Pequeño' elif 501 <= self.installed_capacity <= 1000: return 'Comercial Mediano' elif 1001 <= self.installed_capacity <= 1500: return 'Comercial Grande' else: return 'Utilidad' @property def total_investment(self): return self.installed_capacity * self.price_per_kwp @property def annual_production(self): return self.installed_capacity * self.specific_yield sale_type_id = deferred( db.Column(db.Integer, db.ForeignKey('sale_types.id'), index=True, nullable=False)) project_id = deferred( db.Column(db.Integer, db.ForeignKey('customer_projects.id'), index=True, nullable=False))
class Task(CustomBase): __tablename__ = 'Task' id = Column(Integer, primary_key=True) type = Column(String) recurrent = Column(Boolean, default=False) name = Column(String(120), unique=True) status = Column(String) creation_time = Column(Integer) logs = Column(MutableDict.as_mutable(PickleType), default={}) nodes = Column(MutableList.as_mutable(PickleType), default=[]) # scheduling parameters frequency = Column(String(120)) scheduled_date = Column(String) # script parameters creator = Column(String) def __init__(self, user, **data): self.name = data['name'] self.frequency = data['frequency'] self.recurrent = bool(data['frequency']) self.creation_time = str(datetime.now()) self.creator = user.username self.status = 'active' # if the scheduled date is left empty, we turn the empty string into # None as this is what AP Scheduler is expecting if data['scheduled_date']: self.scheduled_date = self.datetime_conversion( data['scheduled_date']) else: self.scheduled_date = None self.is_active = True if data['frequency']: self.recurrent_scheduling() else: self.one_time_scheduling() def datetime_conversion(self, scheduled_date): dt = datetime.strptime(scheduled_date, '%d/%m/%Y %H:%M:%S') return datetime.strftime(dt, '%Y-%m-%d %H:%M:%S') def pause_task(self): scheduler.pause_job(self.creation_time) self.status = 'suspended' db.session.commit() def resume_task(self): scheduler.resume_job(self.creation_time) self.status = "active" db.session.commit() def recurrent_scheduling(self): if not self.scheduled_date: self.scheduled_date = datetime.now() + timedelta(seconds=15) # run the job on a regular basis with an interval trigger id = scheduler.add_job(id=self.creation_time, func=self.job, args=self.args, trigger='interval', start_date=self.scheduled_date, seconds=int(self.frequency), replace_existing=True) def one_time_scheduling(self): if not self.scheduled_date: # when the job is scheduled to run immediately, it may happen that # the job is run even before the task is created, in which case # it fails because it cannot be retrieve from the Task column of # the database: we introduce a delta of 2 seconds. # other situation: the server is too slow and the job cannot be # run at all, eg 'job was missed by 0:00:09.465684' self.scheduled_date = datetime.now() + timedelta(seconds=5) # execute the job immediately with a date-type job # when date is used as a trigger and run_date is left undetermined, # the job is executed immediately. id = scheduler.add_job(id=self.creation_time, run_date=self.scheduled_date, func=self.job, args=self.args, trigger='date')
def __init__(self): self.key = uuid4().hex self.startmoment = datetime.now() self.values = MutableDict() self.values['check'] = uuid4().hex
class Badge(db.Model): id = db.Column(db.Text, primary_key=True) name = db.Column(db.Text) orcid_id = db.Column(db.Text, db.ForeignKey('person.orcid_id')) created = db.Column(db.DateTime) value = db.Column(db.Float) percentile = db.Column(db.Float) support = db.Column(db.Text) products = db.Column(MutableDict.as_mutable(JSONB)) def __init__(self, assigned=True, **kwargs): self.id = shortuuid.uuid()[0:10] self.created = datetime.datetime.utcnow().isoformat() self.assigned = assigned self.products = {} super(Badge, self).__init__(**kwargs) @property def dois(self): if self.products: return self.products.keys() return [] @property def num_products(self): if self.products: return len(self.products) else: return 0 def add_product(self, my_product): self.products[my_product.doi] = True def add_products(self, products_list): for my_product in products_list: self.add_product(my_product) def remove_all_products(self): self.products = {} @property def my_badge_type(self): assigner = get_badge_assigner(self.name) if assigner: my_assigner = assigner() else: my_assigner = dummy_badge_assigner() return my_assigner @property def sort_score(self): if self.percentile: sort_score = self.percentile * self.my_badge_type.importance else: sort_score = 0.5 * self.my_badge_type.importance if self.my_badge_type.group == "fun": sort_score -= 0.25 return sort_score @property def description(self): description_template = self.my_badge_type.description description_string = description_template.format( value=conversational_number(self.value), one_hundred_minus_value=conversational_number(100 - self.value)) return description_string @property def display_in_the_top_percentile(self): if not self.percentile: return None ret = int(100 - self.percentile * 100) if ret == 100: ret = 99 if ret < 1: ret = 1 return ret @property def display_percentile(self): if not self.percentile: return None ret = int(self.percentile * 100) if ret == 100: ret = 99 if ret < 1: ret = 1 return ret # what the UI is currently expecting @property def display_percentile_fraction(self): if not self.percentile: return None if self.percentile > 0.99: return 0.99 return self.percentile @property def context(self): context_template = self.my_badge_type.context if context_template == None: context_template = u" This puts you in the top {in_the_top_percentile}% of researchers." inverse_percentiles = ["reading_level"] if self.name in inverse_percentiles: if u"{percentile}" in context_template: if self.display_percentile > 50: return None if u"{in_the_top_percentile}" in context_template: if self.display_in_the_top_percentile < 50: return None else: if u"{percentile}" in context_template: if self.display_percentile < 50: return None if u"{in_the_top_percentile}" in context_template: if self.display_in_the_top_percentile > 50: return None context_string = context_template.format( value=conversational_number(self.value), one_hundred_minus_value=conversational_number(100 - self.value), in_the_top_percentile=self.display_in_the_top_percentile, percentile=self.display_percentile) return context_string @property def group(self): return self.my_badge_type.group @property def support_items(self): try: parts = self.support.split(": ") except AttributeError: return None try: support_phrase = parts[1] except IndexError: return None items = support_phrase.split(",") trimmed = [x.strip() for x in items] deduped = list(set(trimmed)) deduped.sort() return deduped @property def support_intro(self): try: parts = self.support.split(": ") except AttributeError: return None return parts[0] def set_percentile(self, refset_list): if refset_list: self.percentile = calculate_percentile(refset_list, self.value) # print u"set percentile for {} {} to {}".format(self.name, self.value, self.percentile) else: print "not setting percentile, no refest. maybe local?" def __repr__(self): return u'<Badge {id} {name} ({value})>'.format(id=self.id, name=self.name, value=self.value) def to_dict(self): if self.products: product_list = self.products.keys() resp = { "id": self.id, "orcid_id": self.orcid_id, "name": self.name, "created": date_as_iso_utc(self.created), "show_in_ui": self.my_badge_type.show_in_ui, "support_items": self.support_items, "support_intro": self.support_intro, "support_finale": self.my_badge_type.support_finale, "value": self.value, "importance": self.my_badge_type.importance, "percentile": self.display_percentile_fraction, "sort_score": self.sort_score, "description": self.description, "extra_description": self.my_badge_type.extra_description, "context": self.context, "group": self.my_badge_type.group, "display_name": self.my_badge_type.display_name } return resp
class NapalmGettersService(Service): __tablename__ = 'NapalmGettersService' id = Column(Integer, ForeignKey('Service.id'), primary_key=True) has_targets = True validation_method = Column(String, default='text') validation_method_values = (('text', 'Validation by text match'), ('dict_equal', 'Validation by dictionnary equality'), ('dict_included', 'Validation by dictionnary inclusion')) content_match = Column(String) content_match_textarea = True content_match_regex = Column(Boolean) dict_match = Column(MutableDict.as_mutable(PickleType), default={}) negative_logic = Column(Boolean) delete_spaces_before_matching = Column(Boolean) driver = Column(String) driver_values = NAPALM_DRIVERS use_device_driver = Column(Boolean, default=True) getters = Column(MutableList.as_mutable(PickleType), default=[]) getters_values = (('get_arp_table', 'ARP table'), ('get_interfaces_counters', 'Interfaces counters'), ('get_facts', 'Facts'), ('get_environment', 'Environment'), ('get_config', 'Configuration'), ('get_interfaces', 'Interfaces'), ('get_interfaces_ip', 'Interface IP'), ('get_lldp_neighbors', 'LLDP neighbors'), ('get_lldp_neighbors_detail', 'LLDP neighbors detail'), ('get_mac_address_table', 'MAC address'), ('get_ntp_servers', 'NTP servers'), ('get_ntp_stats', 'NTP statistics'), ('get_optics', 'Transceivers'), ('get_snmp_information', 'SNMP'), ('get_users', 'Users'), ('get_network_instances', 'Network instances (VRF)'), ('get_ntp_peers', 'NTP peers'), ('get_bgp_config', 'BGP configuration'), ('get_bgp_neighbors', 'BGP neighbors'), ('get_ipv6_neighbors_table', 'IPv6'), ('is_alive', 'Is alive')) optional_args = Column(MutableDict.as_mutable(PickleType), default={}) __mapper_args__ = { 'polymorphic_identity': 'NapalmGettersService', } def job(self, device, _): napalm_driver, result = self.napalm_connection(device), {} napalm_driver.open() for getter in self.getters: try: result[getter] = getattr(napalm_driver, getter)() except Exception as e: result[getter] = f'{getter} failed because of {e}' if self.validation_method == 'text': success = self.match_content( str(result), self.sub(self.content_match, locals())) else: success = self.match_dictionnary(result) napalm_driver.close() return { 'negative_logic': self.negative_logic, 'result': result, 'success': success }
class Job(Base): __tablename__ = 'Job' id = Column(Integer, primary_key=True) hidden = Column(Boolean, default=False) name = Column(String, unique=True) description = Column(String) number_of_retries = Column(Integer, default=0) time_between_retries = Column(Integer, default=10) positions = Column(MutableDict.as_mutable(PickleType), default={}) logs = Column(MutableDict.as_mutable(PickleType), default={}) state = Column(String, default='Idle') status = Column(MutableDict.as_mutable(PickleType), default={}) tasks = relationship('Task', back_populates='job', cascade='all,delete') type = Column(String) vendor = Column(String) operating_system = Column(String) waiting_time = Column(Integer, default=0) workflows = relationship('Workflow', secondary=job_workflow_table, back_populates='jobs') devices = relationship('Device', secondary=job_device_table, back_populates='jobs') pools = relationship('Pool', secondary=job_pool_table, back_populates='jobs') log_rules = relationship('LogRule', secondary=job_log_rule_table, back_populates='jobs') send_notification = Column(Boolean, default=False) send_notification_method = Column(String) mail_recipient = Column(String) __mapper_args__ = {'polymorphic_identity': 'Job', 'polymorphic_on': type} def compute_targets(self): targets = set(self.devices) for pool in self.pools: targets |= set(pool.devices) return targets def job_sources(self, workflow, type='all'): return [ x.source for x in self.sources if (type == 'all' or x.type == type) and x.workflow == workflow ] def job_successors(self, workflow, type='all'): return [ x.destination for x in self.destinations if (type == 'all' or x.type == type) and x.workflow == workflow ] def try_run(self, payload=None, remaining_targets=None): failed_attempts = {} for i in range(self.number_of_retries + 1): results, remaining_targets = self.run(payload, remaining_targets) if results['success']: break if i != self.number_of_retries: failed_attempts[f'Attempts {i + 1}'] = results sleep(self.time_between_retries) results['failed_attempts'] = failed_attempts self.logs[str(datetime.now())] = results return results def get_results(self, payload, device=None): try: return self.job(device, payload) if device else self.job(payload) except Exception as e: return {'success': False, 'result': str(e)} def run(self, payload=None, targets=None): if not targets: targets = self.compute_targets() if targets: results = {'result': {'devices': {}}} if self.multiprocessing: pool = ThreadPool(processes=min(len(targets), 1)) pool.map(self.device_run, [(device, results, payload) for device in targets]) pool.close() pool.join() else: results['result']['devices'] = { device.name: self.get_results(payload, device) for device in targets } remaining_targets = { device for device in targets if not results['result']['devices'][device.name]['success'] } results['success'] = not bool(remaining_targets) return results, remaining_targets else: return self.get_results(payload), None def device_run(self, args): device, results, payload = args device_result = self.get_results(payload, device) results['result']['devices'][device.name] = device_result
class Annotation(Base): """Model class representing a single annotation.""" __tablename__ = "annotation" __table_args__ = ( # Tags are stored in an array-type column, and indexed using a # generalised inverted index. For more information on the use of GIN # indices for array columns, see: # # http://www.databasesoup.com/2015/01/tag-all-things.html # http://www.postgresql.org/docs/9.5/static/gin-intro.html # sa.Index("ix__annotation_tags", "tags", postgresql_using="gin"), sa.Index("ix__annotation_updated", "updated"), # This is a functional index on the *first* of the annotation's # references, pointing to the top-level annotation it refers to. We're # using 1 here because Postgres uses 1-based array indexing. sa.Index("ix__annotation_thread_root", sa.text('("references"[1])')), ) #: Annotation ID: these are stored as UUIDs in the database, and mapped #: transparently to a URL-safe Base64-encoded string. id = sa.Column(types.URLSafeUUID, server_default=sa.func.uuid_generate_v1mc(), primary_key=True) #: The timestamp when the annotation was created. created = sa.Column( sa.DateTime, default=datetime.datetime.utcnow, server_default=sa.func.now(), nullable=False, ) #: The timestamp when the user edited the annotation last. updated = sa.Column( sa.DateTime, server_default=sa.func.now(), default=datetime.datetime.utcnow, nullable=False, ) #: The full userid (e.g. 'acct:[email protected]') of the owner of this #: annotation. userid = sa.Column(sa.UnicodeText, nullable=False, index=True) #: The string id of the group in which this annotation is published. #: Defaults to the global public group, "__world__". groupid = sa.Column( sa.UnicodeText, default="__world__", server_default="__world__", nullable=False, index=True, ) #: The textual body of the annotation. _text = sa.Column("text", sa.UnicodeText) #: The Markdown-rendered and HTML-sanitized textual body of the annotation. _text_rendered = sa.Column("text_rendered", sa.UnicodeText) #: The tags associated with the annotation. tags = sa.Column( MutableList.as_mutable(pg.ARRAY(sa.UnicodeText, zero_indexes=True))) #: A boolean indicating whether this annotation is shared with members of #: the group it is published in. "Private"/"Only me" annotations have #: shared=False. shared = sa.Column( sa.Boolean, nullable=False, default=False, server_default=sa.sql.expression.false(), ) #: The URI of the annotated page, as provided by the client. _target_uri = sa.Column("target_uri", sa.UnicodeText) #: The URI of the annotated page in normalized form. _target_uri_normalized = sa.Column("target_uri_normalized", sa.UnicodeText) #: The serialized selectors for the annotation on the annotated page. target_selectors = sa.Column(types.AnnotationSelectorJSONB, default=list, server_default=sa.func.jsonb("[]")) #: An array of annotation IDs which are ancestors of this annotation. references = sa.Column( pg.ARRAY(types.URLSafeUUID, zero_indexes=True), default=list, server_default=sa.text("ARRAY[]::uuid[]"), ) #: Any additional serialisable data provided by the client. extra = sa.Column( MutableDict.as_mutable(pg.JSONB), default=dict, server_default=sa.func.jsonb("{}"), nullable=False, ) #: Has the annotation been deleted? deleted = sa.Column( sa.Boolean, nullable=False, default=False, server_default=sa.sql.expression.false(), ) document_id = sa.Column(sa.Integer, sa.ForeignKey("document.id"), nullable=False) document = sa.orm.relationship("Document", backref="annotations") thread = sa.orm.relationship( "Annotation", primaryjoin=(sa.orm.foreign(id) == sa.orm.remote(references[0])), viewonly=True, uselist=True, ) @hybrid_property def target_uri(self): return self._target_uri @target_uri.setter def target_uri(self, value): self._target_uri = value self._target_uri_normalized = uri.normalize(value) @hybrid_property def target_uri_normalized(self): return self._target_uri_normalized @hybrid_property def text(self): return self._text @text.setter def text(self, value): self._text = value # N.B. We MUST take care here of appropriately escaping the user # input. Code elsewhere will assume that the content of the # `text_rendered` field is safe for printing without further escaping. # # `markdown.render` does the hard work for now. self._text_rendered = markdown.render(value) @hybrid_property def text_rendered(self): return self._text_rendered @property def thread_ids(self): return [thread_annotation.id for thread_annotation in self.thread] @property def is_reply(self): return bool(self.references) @property def parent_id(self): """ Return the ID of the annotation that this annotation is a reply to. Return None if this annotation is not a reply. """ if self.references: return self.references[-1] @property def thread_root_id(self): """ Return the ID of the root annotation of this annotation's thread. Return the ID of the root annotation of the thread to which this annotation belongs. May be this annotation's own ID if it is the root annotation of its thread. """ if self.references: return self.references[0] return self.id @property def authority(self): """ Return the authority of the user and group this annotation belongs to. For example, returns "hypothes.is" for Hypothesis first-party annotations, or "elifesciences.org" for eLife third-party annotations. If this annotation doesn't have a userid (which is possible for annotations that haven't been saved to the DB yet) then return None. :raises ValueError: if the annotation's userid is invalid """ if self.userid is None: return None return split_user(self.userid)["domain"] def __repr__(self): return "<Annotation %s>" % self.id
class PredictionTile(db.Model): """ Store individual tile predictions """ __tablename__ = "prediction_tiles" id = db.Column(db.Integer, primary_key=True) prediction_id = db.Column( db.BigInteger, db.ForeignKey("predictions.id", name="fk_predictions"), nullable=False, ) quadkey = db.Column(db.String, nullable=True) geom = db.Column(Geometry("POLYGON", srid=4326), nullable=False) predictions = db.Column(postgresql.JSONB, nullable=False) validity = db.Column(MutableDict.as_mutable(postgresql.JSONB), nullable=True) prediction_tiles_quadkey_idx = db.Index( "prediction_tiles_quadkey_idx", "prediction_tiles.quadkey", postgresql_ops={"quadkey": "text_pattern_ops"}, ) @staticmethod def get(predictiontile_id: int): db.session.query( PredictionTile.id, PredictionTile.prediction_id, PredictionTile.validity, ).filter(PredictionTile.id == predictiontile_id) return PredictionTile.query.get(predictiontile_id) def update(self, validity): self.validity = validity db.session.commit() @staticmethod def inferences(prediction_id: int): results = db.session.execute( text(""" SELECT DISTINCT jsonb_object_keys(predictions) FROM prediction_tiles WHERE prediction_id = :pred """), { "pred": prediction_id }, ).fetchall() inferences = [] for res in results: inferences.append(res[0]) return inferences @staticmethod def count(prediction_id: int): return (db.session.query( func.count(PredictionTile.geom).label("count")).filter( PredictionTile.prediction_id == prediction_id).one()) @staticmethod def bbox(prediction_id: int): result = db.session.execute( text(""" SELECT ST_Extent(geom) FROM prediction_tiles WHERE prediction_id = :pred """), { "pred": prediction_id }, ).fetchone() bbox = [] for corners in result[0].replace("BOX(", "").replace(")", "").split(" "): for corner in corners.split(","): bbox.append(float(corner)) return bbox def mvt(prediction_id: int, z: int, x: int, y: int): grid = mercantile.xy_bounds(x, y, z) result = db.session.execute( text(""" SELECT ST_AsMVT(q, 'data', 4096, 'geom', 'id') AS mvt FROM ( SELECT p.id AS id, quadkey AS quadkey, predictions || COALESCE(v.validity, '{}'::JSONB) AS props, ST_AsMVTGeom(geom, ST_Transform(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy, 3857), 4326), 4096, 256, false) AS geom FROM prediction_tiles AS p LEFT JOIN ( SELECT id, JSONB_Object_Agg('v_'||key, value) AS validity FROM prediction_tiles, jsonb_each(validity) GROUP BY id ) AS v ON p.id = v.id WHERE p.prediction_id = :pred AND ST_Intersects(p.geom, ST_Transform(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy, 3857), 4326)) ) q """), { "pred": prediction_id, "minx": grid[0], "miny": grid[1], "maxx": grid[2], "maxy": grid[3], }, ).fetchone() return bytes(result.values()[0]) @staticmethod def get_tiles_by_quadkey(prediction_id: int, quadkeys: tuple, zoom: int): return (db.session.query( func.substr(PredictionTile.quadkey, 1, zoom).label("qaudkey"), func.avg( cast( cast( PredictionTile.predictions["ml_prediction"], sqlalchemy.String, ), sqlalchemy.Float, )).label("ml_prediction"), func.avg( cast( cast( PredictionTile.predictions["osm_building_area"], sqlalchemy.String, ), sqlalchemy.Float, )).label("osm_building_area"), ).filter(PredictionTile.prediction_id == prediction_id).filter( func.substr(PredictionTile.quadkey, 1, zoom).in_(quadkeys)).group_by( func.substr(PredictionTile.quadkey, 1, zoom)).all())
class SocialAuthAccount(AbstractAgentAccount, AppSocialAuthMixin, UserMixin): """An account with an external :py:class:`.auth.IdentityProvider`""" __tablename__ = "social_auth_account" __mapper_args__ = { 'polymorphic_identity': 'social_auth_account', } __table_args__ = (UniqueConstraint('provider_id', 'provider_domain', 'uid'), ) UID_LENGTH = config.get('UID_LENGTH', 255) id = Column(Integer, ForeignKey('abstract_agent_account.id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True) provider_id = Column(Integer, ForeignKey('identity_provider.id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False, info={'rdf': QuadMapPatternS(None, SIOC.member_of)}) identity_provider = relationship(IdentityProvider) username = Column(Unicode(200)) # info={'rdf': QuadMapPatternS(None, SIOC.name)}) provider_domain = Column(String(255)) uid = Column(String(UID_LENGTH), nullable=False) # info={'rdf': QuadMapPatternS(None, SIOC.id)}) extra_data = Column(MutableDict.as_mutable(JSONType)) picture_url = Column(URLString) user = relationship(AgentProfile, backref='identity_accounts') last_checked = Column(DateTime) def successful_login(self): self.last_checked = datetime.utcnow() def login_expiry(self): if self.last_checked is None: return datetime.utcnow() - timedelta(seconds=1) expiry = self.login_duration() if not expiry: return None return self.last_checked + timedelta(expiry) @property def provider(self): return self.identity_provider.provider_type @property def provider_with_idp(self): provider = self.provider if provider == 'saml': # PSA prefixes SAML uids with the idp_name idp_name = self.uid.split(':')[0] # Also available as self.extra_data['idp_name'] return ':'.join((provider, idp_name)) return provider @provider.setter def provider(self, value): self.identity_provider = IdentityProvider.get_by_type(value) def __init__(self, **kwargs): super(SocialAuthAccount, self).__init__(**kwargs) self.interpret_profile(self.extra_data) # reimplementation of UserSocialAuth @classmethod def username_max_length(cls): return User.__table__.columns.get('username').type.length @classmethod def user_model(cls): return User # reimplementation of SQLAlchemyUserMixin @classmethod def changed(cls, user): cls._save_instance(user) def set_extra_data(self, extra_data=None): if super(SocialAuthAccount, self).set_extra_data(extra_data): self.interpret_profile(self.extra_data) @classmethod def allowed_to_disconnect(cls, user, backend_name, association_id=None): if association_id is not None: qs = cls._query().filter(cls.id != association_id) else: qs = cls._query().join(cls.identity_provider).filter( IdentityProvider.provider_type != backend_name) qs = qs.filter(cls.user == user) if hasattr(user, 'has_usable_password'): # TODO valid_password = user.has_usable_password() else: valid_password = True return valid_password or qs.count() > 0 @classmethod def disconnect(cls, entry): cls._session().delete(entry) cls._flush() @classmethod def user_query(cls): return cls._session().query(cls.user_model()) @classmethod def user_exists(cls, *args, **kwargs): """ Return True/False if a User instance exists with the given arguments. Arguments are directly passed to filter() manager method. """ query = cls.user_query() username = kwargs.pop('username', None) if username: query = query.filter(User.username == username) return query.filter_by(*args, **kwargs).count() > 0 @classmethod def get_username(cls, user): """Return the username for given user""" # assume user is a User, not an AgentProfile return user.username @classmethod def create_user(cls, email=None, username=None, fullname=None, *args, **kwargs): if fullname: kwargs['name'] = fullname user = cls._new_instance(cls.user_model(), *args, **kwargs) return user @classmethod def get_user(cls, pk): return cls.user_query().get(pk) @classmethod def get_users_by_email(cls, email): # Find users with similar email. # Only use if social provider is trusted to have verified email. users = cls.default_db().query(User).join(User.accounts).filter( AbstractAgentAccount.email_ci == email, ).all() # choose best known profile for base_account # prefer profiles with verified users, then users, then oldest profiles users.sort( key=lambda p: (isinstance(p, User) and p.verified, isinstance(p, User), -p.id), reverse=True) return users @classmethod def get_social_auth(cls, provider, uid): if not isinstance(uid, six.string_types): uid = str(uid) return cls._query().join(cls.identity_provider).filter( IdentityProvider.provider_type == provider, cls.uid == uid).first() @classmethod def get_social_auth_for_user(cls, user, provider=None, id=None): qs = cls._query().filter_by(profile_id=user.id) if provider: qs = qs.join(cls.identity_provider).filter( IdentityProvider.provider_type == provider) if id: qs = qs.filter(cls.id == id) return qs @classmethod def create_social_auth(cls, user, uid, provider): if not isinstance(uid, six.string_types): uid = str(uid) id_provider = IdentityProvider.get_by_type(provider) return cls._new_instance(cls, profile=user, uid=uid, identity_provider=id_provider, verified=id_provider.trust_emails) # override social_core.storage.UserMixin.get_backend_instance def get_backend_instance(self, strategy): try: backend_class = self.get_backend(strategy) except MissingBackend: return None else: if issubclass(backend_class, GenericAuth): return backend_class(strategy=strategy, name=self.provider) else: return backend_class(strategy=strategy) # Lifted from IdentityProviderAccount def signature(self): return ('idprovider_agent_account', self.provider_id, self.username, self.uid) def interpret_profile(self, profile=None): profile = profile or self.extra_data if profile: self.populate_picture(profile) if not self.email: # May be missed by social auth. compensate. emails = profile.get('emails', []) if emails: self.email = emails[0].get('value', '') def interpret_social_auth_details(self, details): self.email = details.get("email", self.email) self.username = details.get('username', self.username) # TODO: Maybe see if username usable for user? fullname = details.get("fullname") if not fullname: first_name = details.get('first_name', None) last_name = details.get('last_name', None) if first_name and last_name: fullname = ' '.join((first_name, last_name)) if fullname and not self.user.name: self.user.name = fullname def display_name(self): # TODO: format according to provider, ie @ for twitter. if self.username: name = self.username else: name = self.uid return ":".join((self.identity_provider.provider_type, name)) def get_provider_name(self): return self.identity_provider.name def get_provider_type(self): return self.identity_provider.provider_type def real_name(self): if not self.full_name: info = self.extra_data name = info.get('name', None) or {} if isinstance(name, str): self.fullname = name elif name.get('formatted', None): self.full_name = name['formatted'] elif 'givenName' in name and 'familyName' in name: self.full_name = ' '.join( (name['givenName'], name['familyName'])) return self.full_name def populate_picture(self, profile): if 'photos' in profile: # google, facebook photos = [x.get('value', None) for x in profile['photos']] photos = [x for x in photos if x] if photos: self.picture_url = photos[0] elif 'image' in profile: # google photo = profile['image'].get('url', None) if photo: self.picture_url = photo elif profile.get('user', {}).get('mugshot_url_template', None): # yammer self.picture_url = profile['user']['mugshot_url_template'] elif profile.get('user', {}).get('mugshot_url', None): # yammer self.picture_url = profile['user']['mugshot_url'] elif profile.get('mugshot_url', None): # yammer self.picture_url = profile['mugshot_url'] elif self.identity_provider.provider_type.startswith('facebook'): account = profile.get('id', None) if account is None: accounts = [x.get('uid') for x in profile.get('accounts', ())] accounts = [x for x in accounts if x] if not accounts: return account = accounts[0] self.picture_url = 'http://graph.facebook.com/%s/picture' % ( account) facebook_sizes = (('square', 50), ('small', 50), ('normal', 100), ('large', 200)) twitter_sizes = (('_mini', 25), ('_normal', 48), ('_bigger', 73), ('', 1000)) def avatar_url(self, size=32): picture_url = self.picture_url if not picture_url: return if config.get("accept_secure_connection"): # Make the connection https, known services can handle both. # Ideally we should check which ones work. picture_url = "https://" + picture_url.split("://", 1)[-1] if "{width}" in unquote(picture_url): # yammer picture_url = unquote(picture_url).format(width=size, height=size) return picture_url if self.identity_provider.provider_type.startswith('google'): modified = re.sub(r"((\?|&)(size|sz))=(\d+)", r"\1=%d" % (size, ), picture_url) if modified == picture_url: separator = "&" if "?" in picture_url else "?" modified = picture_url + separator + 'sz=' + str(size) return modified elif self.identity_provider.provider_type.startswith('facebook'): for (size_name, name_size) in self.facebook_sizes: if size <= name_size: break return '%s?type=%s' % (picture_url, size_name) elif self.identity_provider.provider_type == 'twitter': for (size_name, name_size) in self.twitter_sizes: if size <= name_size: break return size_name.join(picture_url.split('_normal')) # @classmethod # def special_quad_patterns(cls, alias_maker, discussion_id): # return [QuadMapPatternS(AgentProfile.iri_class().apply( # SocialAuthAccount.profile_id), # FOAF.img, SocialAuthAccount.picture_url, # name=QUADNAMES.foaf_image, # conditions=(SocialAuthAccount.picture_url != None), # sections=(PRIVATE_USER_SECTION,))] def unique_query(self): query, _ = super(SocialAuthAccount, self).unique_query() return query.filter_by(type=self.type, provider_id=self.provider_id, username=self.username), True @classmethod def find_accounts(cls, provider, social_account): # Probably deprecated if 'email' in social_account: return provider.db.query(cls).filter_by( provider=provider, domain=social_account['domain'], email_ci=social_account['email']).all() elif 'username' in social_account: return provider.db.query(cls).filter_by( provider=provider, domain=social_account['domain'], uid=social_account['username']).all() else: log.error("account needs username or email" + social_account) raise RuntimeError("account needs username or uid") def login_duration(self): data = self.extra_data intrinsic = None if 'expires' in data: intrinsic = data['expires'] elif 'expires_in' in data: intrinsic = data['expires_in'] provider = self.provider_with_idp provider = '_'.join(provider.split(':')) config_t = config.get('login_expiry_' + provider, None) if config_t is None and '_' in provider: config_t = config.get('login_expiry_' + provider.split('_')[0], None) if config_t is None: config_t = config.get('login_expiry_default', None) if intrinsic is not None: # convert to days intrinsic = float(intrinsic) / 864000 if config_t is not None: # take minimum of intrinsic or config. intrinsic = min(float(config_t), intrinsic) return float(intrinsic or config_t or 0) # temporary shims @property def profile_info_json(self): return self.extra_data @profile_info_json.setter def profile_info_json(self, val): self.extra_data = val self.interpret_profile(val)
def upgrade(): metajson = sa.Column('metajson', MutableDict.as_mutable(JSON), default={}) op.add_column('catalog', metajson)
class MSScan(Base): __tablename__ = "MSScan" id = Column(Integer, primary_key=True, autoincrement=True) index = Column(Integer, index=True) ms_level = Column(Integer) scan_time = Column(Numeric(10, 5, asdecimal=False), index=True) title = Column(String(512)) scan_id = Column(String(512), index=True) sample_run_id = Column(Integer, ForeignKey( SampleRun.id, ondelete='CASCADE'), index=True) peak_set = relationship("FittedPeak", backref="scan", lazy="dynamic") deconvoluted_peak_set = relationship( "DeconvolutedPeak", backref='scan', lazy='dynamic') info = deferred(Column(MutableDict.as_mutable(PickleType))) def __repr__(self): f = "{}({}, {}, {}, {}".format( self.__class__.__name__, self.scan_id, self.ms_level, self.scan_time, self.deconvoluted_peak_set.count()) if self.ms_level > 1: f = "%s %s" % (f, self.precursor_information) f += ")" return f def convert(self, fitted=True, deconvoluted=True): precursor_information = self.precursor_information.convert( ) if self.precursor_information is not None else None session = object_session(self) conn = session.connection() if fitted: q = conn.execute(select([FittedPeak.__table__]).where( FittedPeak.__table__.c.scan_id == self.id)).fetchall() peak_set_items = list( map(make_memory_fitted_peak, q)) peak_set = PeakSet(peak_set_items) peak_set._index() peak_index = PeakIndex(np.array([], dtype=np.float64), np.array( [], dtype=np.float64), peak_set) else: peak_index = PeakIndex(np.array([], dtype=np.float64), np.array( [], dtype=np.float64), PeakSet([])) if deconvoluted: q = conn.execute(select([DeconvolutedPeak.__table__]).where( DeconvolutedPeak.__table__.c.scan_id == self.id)).fetchall() deconvoluted_peak_set_items = list( map(make_memory_deconvoluted_peak, q)) deconvoluted_peak_set = DeconvolutedPeakSet( deconvoluted_peak_set_items) deconvoluted_peak_set._reindex() else: deconvoluted_peak_set = DeconvolutedPeakSet([]) info = self.info or {} scan = ProcessedScan( self.scan_id, self.title, precursor_information, int(self.ms_level), float(self.scan_time), self.index, peak_index, deconvoluted_peak_set, activation=info.get('activation')) return scan @classmethod def _serialize_scan(cls, scan, sample_run_id=None): db_scan = cls( index=scan.index, ms_level=scan.ms_level, scan_time=float(scan.scan_time), title=scan.title, scan_id=scan.id, sample_run_id=sample_run_id, info={'activation': scan.activation}) return db_scan @classmethod def serialize(cls, scan, sample_run_id=None): db_scan = cls._serialize_scan(scan, sample_run_id) db_scan.peak_set = map(FittedPeak.serialize, scan.peak_set) db_scan.deconvoluted_peak_set = map( DeconvolutedPeak.serialize, scan.deconvoluted_peak_set) return db_scan @classmethod def serialize_bulk(cls, scan, sample_run_id, session, fitted=True, deconvoluted=True): db_scan = cls._serialize_scan(scan, sample_run_id) session.add(db_scan) session.flush() if fitted: FittedPeak._serialize_bulk_list(scan.peak_set, db_scan.id, session) if deconvoluted: DeconvolutedPeak._serialize_bulk_list( scan.deconvoluted_peak_set, db_scan.id, session) return db_scan
impl = VARCHAR def process_bind_param(self, value, dialect): if value is not None: value = str(value) return value def process_result_value(self, value, dialect): if value is not None: value = ast.literal_eval(value) return value ARRAY_TYPE = Array() JSON_TYPE = MutableDict.as_mutable(JSONEncodedDict) BASE = declarative_base() class Product(BASE): """docstring for Product.""" __tablename__ = "products" id = Column('id', Integer, primary_key=True, autoincrement=False) style_no = Column('style_no', String) title = Column('title', String) keywords = Column('keywords', ARRAY_TYPE) owner = Column('owner', String) modify_time = Column('modify_time', Date) update = Column('update', Date, default=date.today)
class NapalmGettersService(Service): __tablename__ = "NapalmGettersService" id = Column(Integer, ForeignKey("Service.id"), primary_key=True) has_targets = True validation_method = Column(String, default="text") validation_method_values = ( ("text", "Validation by text match"), ("dict_equal", "Validation by dictionary equality"), ("dict_included", "Validation by dictionary inclusion"), ) content_match = Column(String) content_match_textarea = True content_match_regex = Column(Boolean) dict_match = Column(MutableDict.as_mutable(PickleType), default={}) negative_logic = Column(Boolean) delete_spaces_before_matching = Column(Boolean) driver = Column(String) driver_values = NAPALM_DRIVERS use_device_driver = Column(Boolean, default=True) getters = Column(MutableList.as_mutable(PickleType), default=[]) getters_values = ( ("get_arp_table", "ARP table"), ("get_interfaces_counters", "Interfaces counters"), ("get_facts", "Facts"), ("get_environment", "Environment"), ("get_config", "Configuration"), ("get_interfaces", "Interfaces"), ("get_interfaces_ip", "Interface IP"), ("get_lldp_neighbors", "LLDP neighbors"), ("get_lldp_neighbors_detail", "LLDP neighbors detail"), ("get_mac_address_table", "MAC address"), ("get_ntp_servers", "NTP servers"), ("get_ntp_stats", "NTP statistics"), ("get_optics", "Transceivers"), ("get_snmp_information", "SNMP"), ("get_users", "Users"), ("get_network_instances", "Network instances (VRF)"), ("get_ntp_peers", "NTP peers"), ("get_bgp_config", "BGP configuration"), ("get_bgp_neighbors", "BGP neighbors"), ("get_ipv6_neighbors_table", "IPv6"), ("is_alive", "Is alive"), ) optional_args = Column(MutableDict.as_mutable(PickleType), default={}) __mapper_args__ = {"polymorphic_identity": "NapalmGettersService"} def job(self, payload: dict, device: Device) -> dict: napalm_driver, result = self.napalm_connection(device), {} napalm_driver.open() self.logs.append( f"Fetching NAPALM getters ({', '.join(self.getters)}) on {device.name}" ) for getter in self.getters: try: result[getter] = getattr(napalm_driver, getter)() except Exception as e: result[getter] = f"{getter} failed because of {e}" match = self.sub(self.content_match, locals()) napalm_driver.close() return { "match": match if self.validation_method == "text" else self.dict_match, "negative_logic": self.negative_logic, "result": result, "success": self.match_content(result, match), }
class Task(CustomBase): __tablename__ = 'Task' id = Column(Integer, primary_key=True) name = Column(String, unique=True) creation_time = Column(String) status = Column(String) type = Column(String) user_id = Column(Integer, ForeignKey('User.id')) user = relationship('User', back_populates='tasks') logs = Column(MutableDict.as_mutable(PickleType), default={}) frequency = Column(String(120)) start_date = Column(String) end_date = Column(String) positions = Column(MutableDict.as_mutable(PickleType), default={}) waiting_time = Column(Integer, default=0) transfer_payload = Column(Boolean, default=False) workflows = relationship('Workflow', secondary=task_workflow_table, back_populates='tasks') log_rules = relationship('LogRule', secondary=task_log_rule_table, back_populates='tasks') __mapper_args__ = {'polymorphic_identity': 'Task', 'polymorphic_on': type} def __init__(self, **kwargs): self.update(**kwargs) self.status = 'active' self.creation_time = str(datetime.now()) self.is_active = True if 'do_not_run' not in kwargs: self.schedule(run_now='run_immediately' in kwargs) def aps_conversion(self, date): dt = datetime.strptime(date, '%d/%m/%Y %H:%M:%S') return datetime.strftime(dt, '%Y-%m-%d %H:%M:%S') def aps_date(self, datetype): date = getattr(self, datetype) return self.aps_conversion(date) if date else None def pause_task(self): scheduler.pause_job(self.creation_time) self.status = 'suspended' db.session.commit() def resume_task(self): scheduler.resume_job(self.creation_time) self.status = 'active' db.session.commit() def delete_task(self): try: scheduler.delete_job(self.creation_time) except JobLookupError: pass db.session.commit() def task_sources(self, workflow, type): return [ x.source for x in self.sources if x.type == type and x.workflow == workflow ] def task_neighbors(self, workflow, type): return [ x.destination for x in self.destinations if x.type == type and x.workflow == workflow ] def get_payloads(self, workflow, runtime): if not workflow: return {} payloads = {} for edge_type in (True, False): for task in self.task_sources(workflow, edge_type): if not task.transfer_payload: continue if runtime in task.logs and 'success' in task.logs[runtime]: success = task.logs[runtime]['success'] if edge_type == success: pl = task.logs[runtime]['payload']['outgoing_payload'] payloads[task.name] = pl return payloads def schedule(self, run_now=True): now = datetime.now() + timedelta(seconds=15) runtime = now if run_now else self.aps_date('start_date') if self.frequency: scheduler.add_job(id=self.creation_time, func=job, args=[self.name, str(runtime)], trigger='interval', start_date=runtime, end_date=self.aps_date('end_date'), seconds=int(self.frequency), replace_existing=True) else: scheduler.add_job(id=str(runtime), run_date=runtime, func=job, args=[self.name, str(runtime)], trigger='date') return str(runtime) @property def properties(self): return {p: getattr(self, p) for p in cls_to_properties['Task']}
class Task(CustomBase): __tablename__ = 'Task' id = Column(Integer, primary_key=True) name = Column(String, unique=True) creation_time = Column(String) status = Column(String) type = Column(String) user_id = Column(Integer, ForeignKey('User.id')) user = relationship('User', back_populates='tasks') logs = Column(MutableDict.as_mutable(PickleType), default={}) frequency = Column(String(120)) start_date = Column(String) end_date = Column(String) x = Column(Integer, default=0) y = Column(Integer, default=0) waiting_time = Column(Integer, default=0) workflows = relationship('Workflow', secondary=task_workflow_table, back_populates='tasks') log_rules = relationship('LogRule', secondary=task_log_rule_table, back_populates='tasks') __mapper_args__ = {'polymorphic_identity': 'Task', 'polymorphic_on': type} def __init__(self, **data): self.status = 'active' self.waiting_time = data['waiting_time'] self.name = data['name'] self.user = data['user'] self.creation_time = str(datetime.now()) self.frequency = data['frequency'] # if the start date is left empty, we turn the empty string into # None as this is what AP Scheduler is expecting for date in ('start_date', 'end_date'): js_date = data[date] value = self.datetime_conversion(js_date) if js_date else None setattr(self, date, value) self.is_active = True if 'do_not_run' not in data: self.schedule(run_now='run_immediately' in data) def datetime_conversion(self, date): dt = datetime.strptime(date, '%d/%m/%Y %H:%M:%S') return datetime.strftime(dt, '%Y-%m-%d %H:%M:%S') def pause_task(self): scheduler.pause_job(self.creation_time) self.status = 'suspended' db.session.commit() def resume_task(self): scheduler.resume_job(self.creation_time) self.status = 'active' db.session.commit() def delete_task(self): try: scheduler.delete_job(self.creation_time) except JobLookupError: pass db.session.commit() def task_neighbors(self, workflow, type): return [ x.destination for x in self.destinations if x.type == type and x.workflow == workflow ] def schedule(self, run_now=True): now = datetime.now() + timedelta(seconds=15) runtime = now if run_now else self.start_date if self.frequency: scheduler.add_job(id=self.creation_time, func=job, args=[self.name, str(runtime)], trigger='interval', start_date=runtime, end_date=self.end_date, seconds=int(self.frequency), replace_existing=True) else: scheduler.add_job(id=str(runtime), run_date=runtime, func=job, args=[self.name, str(runtime)], trigger='date') return str(runtime) @property def properties(self): return {p: getattr(self, p) for p in cls_to_properties['Task']}
class Project(db.Model, DomainObject): '''A microtasking Project to which Tasks are associated. ''' __tablename__ = 'project' #: ID of the project id = Column(Integer, primary_key=True) #: UTC timestamp when the project is created created = Column(Text, default=make_timestamp) #: UTC timestamp when the project is updated (or any of its relationships) updated = Column(Text, default=make_timestamp, onupdate=make_timestamp) #: Project name name = Column(Unicode(length=255), unique=True, nullable=False) #: Project slug for the URL short_name = Column(Unicode(length=255), unique=True, nullable=False) #: Project description description = Column(Unicode(length=255), nullable=False) #: Project long description long_description = Column(UnicodeText) #: Project webhook webhook = Column(Text) #: If the project allows anonymous contributions allow_anonymous_contributors = Column(Boolean, default=True) #: If the project is published published = Column(Boolean, nullable=False, default=False) # If the project is featured featured = Column(Boolean, nullable=False, default=False) # Secret key for project secret_key = Column(Text, default=make_uuid) # If the project owner has been emailed contacted = Column(Boolean, nullable=False, default=False) #: Project owner_id owner_id = Column(Integer, ForeignKey('user.id'), nullable=False) #: Project Category category_id = Column(Integer, ForeignKey('category.id'), nullable=False) #: Project info field formatted as JSON info = Column(MutableDict.as_mutable(JSON), default=dict()) tasks = relationship(Task, cascade='all, delete, delete-orphan', backref='project') task_runs = relationship(TaskRun, backref='project', cascade='all, delete-orphan', order_by='TaskRun.finish_time.desc()') category = relationship(Category) blogposts = relationship(Blogpost, cascade='all, delete-orphan', backref='project') def needs_password(self): return self.get_passwd_hash() is not None def get_passwd_hash(self): return self.info.get('passwd_hash') def get_passwd(self): if self.needs_password(): return signer.loads(self.get_passwd_hash()) return None def set_password(self, password): if len(password) > 1: self.info['passwd_hash'] = signer.dumps(password) return True self.info['passwd_hash'] = None return False def check_password(self, password): if self.needs_password(): return self.get_passwd() == password return False def has_autoimporter(self): return self.get_autoimporter() is not None def get_autoimporter(self): return self.info.get('autoimporter') def set_autoimporter(self, new=None): self.info['autoimporter'] = new def delete_autoimporter(self): del self.info['autoimporter'] def has_presenter(self): return self.info.get('task_presenter') not in ('', None) @classmethod def public_attributes(self): """Return a list of public attributes.""" return ['id', 'description', 'info', 'n_tasks', 'n_volunteers', 'name', 'overall_progress', 'short_name', 'created', 'description', 'last_activity', 'last_activity_raw', 'overall_progress', 'n_tasks', 'n_volunteers', 'owner', 'updated', 'featured', 'owner_id'] @classmethod def public_info_keys(self): """Return a list of public info keys.""" return ['container', 'thumbnail', 'thumbnail_url', 'task_presenter', 'tutorial', 'sched']
class Task(AbstractBase): __tablename__ = type = "Task" id = Column(Integer, primary_key=True) aps_job_id = Column(String(SMALL_STRING_LENGTH)) name = Column(String(SMALL_STRING_LENGTH), unique=True) description = Column(String(SMALL_STRING_LENGTH)) creation_time = Column(String(SMALL_STRING_LENGTH)) scheduling_mode = Column(String(SMALL_STRING_LENGTH), default="standard") periodic = Column(Boolean) frequency = Column(Integer) frequency_unit = Column(String(SMALL_STRING_LENGTH), default="seconds") start_date = Column(String(SMALL_STRING_LENGTH)) end_date = Column(String(SMALL_STRING_LENGTH)) crontab_expression = Column(String(SMALL_STRING_LENGTH)) is_active = Column(Boolean, default=False) payload = Column(MutableDict.as_mutable(PickleType), default={}) devices = relationship("Device", secondary=task_device_table, back_populates="tasks") pools = relationship("Pool", secondary=task_pool_table, back_populates="tasks") job_id = Column(Integer, ForeignKey("Job.id")) job = relationship("Job", back_populates="tasks") job_name = association_proxy("job", "name") def __init__(self, **kwargs: Any) -> None: super().update(**kwargs) self.creation_time = controller.get_time() self.aps_job_id = kwargs.get("aps_job_id", self.creation_time) if self.is_active: self.schedule() def update(self, **kwargs: Any) -> None: super().update(**kwargs) if self.is_active: self.schedule() def generate_row(self, table: str) -> List[str]: status = "Pause" if self.is_active else "Resume" return [ f"""<button id="pause-resume-{self.id}" type="button" class="btn btn-success btn-xs" onclick= "{status.lower()}Task('{self.id}')">{status}</button>""", f"""<button type="button" class="btn btn-primary btn-xs" onclick="showTypePanel('task', '{self.id}')">Edit</button>""", f"""<button type="button" class="btn btn-primary btn-xs" onclick="showTypePanel('task', '{self.id}', true)"> Duplicate</button>""", f"""<button type="button" class="btn btn-danger btn-xs" onclick="showDeletionPanel('task', '{self.id}', '{self.name}')"> Delete</button>""", ] @hybrid_property def status(self) -> str: return "Active" if self.is_active else "Inactive" @status.expression # type: ignore def status(cls) -> str: # noqa: N805 return case([(cls.is_active, "Active")], else_="Inactive") @property def next_run_time(self) -> Optional[str]: job = controller.scheduler.get_job(self.aps_job_id) if job and job.next_run_time: return job.next_run_time.strftime("%Y-%m-%d %H:%M:%S") return None @property def time_before_next_run(self) -> Optional[str]: job = controller.scheduler.get_job(self.aps_job_id) if job and job.next_run_time: delta = job.next_run_time.replace(tzinfo=None) - datetime.now() hours, remainder = divmod(delta.seconds, 3600) minutes, seconds = divmod(remainder, 60) days = f"{delta.days} days, " if delta.days else "" return f"{days}{hours}h:{minutes}m:{seconds}s" return None def aps_conversion(self, date: str) -> str: dt: datetime = datetime.strptime(date, "%d/%m/%Y %H:%M:%S") return datetime.strftime(dt, "%Y-%m-%d %H:%M:%S") def aps_date(self, datetype: str) -> Optional[str]: date = getattr(self, datetype) return self.aps_conversion(date) if date else None def pause(self) -> None: controller.scheduler.pause_job(self.aps_job_id) self.is_active = False Session.commit() def resume(self) -> None: self.schedule() controller.scheduler.resume_job(self.aps_job_id) self.is_active = True Session.commit() def delete_task(self) -> None: if controller.scheduler.get_job(self.aps_job_id): controller.scheduler.remove_job(self.aps_job_id) Session.commit() def compute_targets(self) -> Set[int]: targets = {device.id for device in self.devices} for pool in self.pools: targets |= {device.id for device in pool.devices} return targets def kwargs(self) -> Tuple[dict, dict]: default = { "id": self.aps_job_id, "func": threaded_job, "replace_existing": True, "args": [ self.job.id, self.aps_job_id, self.compute_targets(), self.payload, ], } if self.scheduling_mode == "cron": self.periodic = True trigger = { "trigger": CronTrigger.from_crontab(self.crontab_expression) } elif self.frequency: self.periodic = True frequency_in_seconds = (int(self.frequency) * { "seconds": 1, "minutes": 60, "hours": 3600, "days": 86400 }[self.frequency_unit]) trigger = { "trigger": "interval", "start_date": self.aps_date("start_date"), "end_date": self.aps_date("end_date"), "seconds": frequency_in_seconds, } else: self.periodic = False trigger = { "trigger": "date", "run_date": self.aps_date("start_date") } return default, trigger def schedule(self) -> None: default, trigger = self.kwargs() if not controller.scheduler.get_job(self.aps_job_id): controller.scheduler.add_job(**{**default, **trigger}) else: controller.scheduler.reschedule_job(default.pop("id"), **trigger)
def hstore_table_for(self, name): return sa.Table(name, self.Base.metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('data', MutableDict.as_mutable(pg.HSTORE)))
class Json(types.TypeDecorator): impl = types.Text def process_bind_param(self, value, dialect): return dumps(value) def process_result_value(self, value, dialect): return loads(value) # TODO(leizhang) When we removed sqlalchemy 0.7 dependence # we can import MutableDict directly and remove ./mutable.py try: from sqlalchemy.ext.mutable import MutableDict as sa_MutableDict sa_MutableDict.associate_with(Json) except ImportError: from heat.db.sqlalchemy.mutable import MutableDict MutableDict.associate_with(Json) class HeatBase(object): """Base class for Heat Models.""" __table_args__ = {'mysql_engine': 'InnoDB'} __table_initialized__ = False created_at = sqlalchemy.Column(sqlalchemy.DateTime, default=timeutils.utcnow) updated_at = sqlalchemy.Column(sqlalchemy.DateTime, onupdate=timeutils.utcnow) def save(self, session=None):
class Job(Base): __tablename__ = "Job" type = Column(String(255)) __mapper_args__ = {"polymorphic_identity": "Job", "polymorphic_on": type} id = Column(Integer, primary_key=True) hidden = Column(Boolean, default=False) name = Column(String(255), unique=True) description = Column(String(255)) multiprocessing = Column(Boolean, default=False) max_processes = Column(Integer, default=5) number_of_retries = Column(Integer, default=0) time_between_retries = Column(Integer, default=10) positions = Column(MutableDict.as_mutable(PickleType), default={}) results = Column(MutableDict.as_mutable(PickleType), default={}) is_running = Column(Boolean, default=False) number_of_targets = Column(Integer, default=0) completed = Column(Integer, default=0) failed = Column(Integer, default=0) state = Column(MutableDict.as_mutable(PickleType), default={}) credentials = Column(String(255), default="device") tasks = relationship("Task", back_populates="job", cascade="all,delete") vendor = Column(String(255)) operating_system = Column(String(255)) waiting_time = Column(Integer, default=0) creator_id = Column(Integer, ForeignKey("User.id")) creator = relationship("User", back_populates="jobs") creator_name = association_proxy("creator", "name") push_to_git = Column(Boolean, default=False) workflows = relationship("Workflow", secondary=job_workflow_table, back_populates="jobs") devices = relationship("Device", secondary=job_device_table, back_populates="jobs") pools = relationship("Pool", secondary=job_pool_table, back_populates="jobs") log_rules = relationship("LogRule", secondary=job_log_rule_table, back_populates="jobs") send_notification = Column(Boolean, default=False) send_notification_method = Column(String(255), default="mail_feedback_notification") display_only_failed_nodes = Column(Boolean, default=True) mail_recipient = Column(String(255), default="") logs = Column(MutableList.as_mutable(PickleType), default=[]) @hybrid_property def status(self) -> str: return "Running" if self.is_running else "Idle" @status.expression # type: ignore def status(cls) -> str: # noqa: N805 return case([(cls.is_running, "Running")], else_="Idle") @property def progress(self) -> str: if self.is_running: return f"{self.completed}/{self.number_of_targets} ({self.failed} failed)" else: return "N/A" def compute_targets(self) -> Set[Device]: targets = set(self.devices) for pool in self.pools: targets |= set(pool.devices) self.number_of_targets = len(targets) db.session.commit() return targets def job_sources(self, workflow: "Workflow", subtype: str = "all") -> List["Job"]: return [ x.source for x in self.sources if (subtype == "all" or x.subtype == subtype) and x.workflow == workflow ] def job_successors(self, workflow: "Workflow", subtype: str = "all") -> List["Job"]: return [ x.destination for x in self.destinations if (subtype == "all" or x.subtype == subtype) and x.workflow == workflow ] def build_notification(self, results: dict, now: str) -> str: summary = [ f"Job: {self.name} ({self.type})", f"Runtime: {now}", f'Status: {"PASS" if results["results"]["success"] else "FAILED"}', ] if "devices" in results[ "results"] and not results["results"]["success"]: failed = "\n".join(device for device, device_results in results["results"]["devices"].items() if not device_results["success"]) summary.append(f"FAILED\n{failed}") if not self.display_only_failed_nodes: passed = "\n".join(device for device, device_results in results["results"]["devices"].items() if device_results["success"]) summary.append(f"\n\nPASS:\n{passed}") server_url = environ.get("ENMS_SERVER_ADDR", "http://SERVER_IP") results_url = f"{server_url}/automation/results/{self.id}/{now}" summary.append(f"Results: {results_url}") return "\n\n".join(summary) def notify(self, results: dict, time: str) -> None: fetch("Job", name=self.send_notification_method).try_run({ "job": self.serialized, "results": self.results, "runtime": time, "result": results["results"]["success"], "content": self.build_notification(results, time), }) def try_run( self, payload: Optional[dict] = None, targets: Optional[Set[Device]] = None, workflow: Optional["Workflow"] = None, ) -> Tuple[dict, str]: self.is_running, self.state, self.logs = True, {}, [] db.session.commit() results: dict = {"results": {}} if not payload: payload = {} job_from_workflow_targets = bool(workflow and targets) if not targets and getattr(self, "use_workflow_targets", True): targets = self.compute_targets() has_targets = bool(targets) if has_targets and not job_from_workflow_targets: results["results"]["devices"] = {} now = str(datetime.now()).replace(" ", "-") logs = workflow.logs if workflow else self.logs logs.append(f"{self.type} {self.name}: Starting.") for i in range(self.number_of_retries + 1): self.completed = self.failed = 0 db.session.commit() logs.append(f"Running {self.type} {self.name} (attempt n°{i + 1})") attempt = self.run(payload, job_from_workflow_targets, targets, workflow) if has_targets and not job_from_workflow_targets: assert targets is not None for device in set(targets): if not attempt["devices"][device.name]["success"]: continue results["results"]["devices"][ device.name] = attempt["devices"][device.name] targets.remove(device) if not targets: results["results"]["success"] = True break else: if self.number_of_retries: results[f"Attempts {i + 1}"] = attempt if i != self.number_of_retries: sleep(self.time_between_retries) else: results["results"]["success"] = False for device in targets: results["results"]["devices"][ device.name] = attempt["devices"][device.name] else: if self.number_of_retries: results[f"Attempts {i + 1}"] = attempt if attempt["success"] or i == self.number_of_retries: results["results"] = attempt break else: sleep(self.time_between_retries) logs.append(f"{self.type} {self.name}: Finished.") self.results[now] = {**results, "logs": logs} self.is_running, self.state = False, {} self.completed = self.failed = 0 db.session.commit() if not workflow and self.send_notification: self.notify(results, now) return results, now def get_results( self, payload: dict, device: Optional[Device] = None, workflow: Optional["Workflow"] = None, threaded: bool = False, ) -> dict: logs = workflow.logs if workflow else self.logs try: if device: logs.append(f"Running {self.type} on {device.name}.") results = self.job(payload, device) success = "SUCCESS" if results["success"] else "FAILURE" logs.append( f"Finished running service on {device.name}. ({success})") else: results = self.job(payload) except Exception: if device: logs.append( f"Finished running service on {device.name}. (FAILURE)") results = { "success": False, "result": chr(10).join(format_exc().splitlines()), } self.completed += 1 self.failed += 1 - results["success"] if not threaded: db.session.commit() return results def device_run( self, args: Tuple[Device, dict, dict, Optional["Workflow"]]) -> None: with controller.app.app_context(): with session_scope() as session: device, results, payload, workflow = args device_result = self.get_results(payload, device, workflow, True) session.merge(workflow or self) results["devices"][device.name] = device_result def run( self, payload: dict, job_from_workflow_targets: bool, targets: Optional[Set[Device]] = None, workflow: Optional["Workflow"] = None, ) -> dict: if job_from_workflow_targets: assert targets is not None device, = targets return self.get_results(payload, device, workflow) elif targets: results: dict = {"devices": {}} if self.multiprocessing: processes = min(len(targets), self.max_processes) pool = ThreadPool(processes=processes) pool.map( self.device_run, [(device, results, payload, workflow) for device in targets], ) pool.close() pool.join() else: results["devices"] = { device.name: self.get_results(payload, device, workflow) for device in targets } return results else: return self.get_results(payload)