class Page(db.Model): """Page of a copy""" __tablename__ = 'page' id = Column(Integer, primary_key=True, autoincrement=True) path = Column(Text, nullable=False) copy_id = Column(Integer, ForeignKey('copy.id'), nullable=False) # backref copy number = Column(Integer, nullable=False) UniqueConstraint(copy_id, number) @hybrid_property def copy_number(self): return self.copy.number @hybrid_property def submission(self): return self.copy.submission @property def abs_path(self): return os.path.join(current_app.config['DATA_DIRECTORY'], self.path) @classmethod def retrieve(cls, copy, page_number): """Retrieve an existing page or create a new one""" return (cls.query.filter(cls.copy == copy, cls.number == page_number).one_or_none() or cls(copy=copy, number=page_number))
class Post(Versioned, Base): """Model class for posts. Each content in a :class:`Topic` and metadata regarding its poster are stored here. It has :attr:`number` which is a sequential number specifying its position within :class:`Topic`. """ __tablename__ = 'post' __table_args__ = (UniqueConstraint('topic_id', 'number'), ) id = Column(Integer, primary_key=True) created_at = Column(DateTime(timezone=True), default=func.now()) updated_at = Column(DateTime(timezone=True), onupdate=func.now()) topic_id = Column(Integer, ForeignKey('topic.id'), nullable=False) ip_address = Column(String, nullable=False) ident = Column(String(32), nullable=True) number = Column(Integer, nullable=False) name = Column(String, nullable=False) body = Column(Text, nullable=False) bumped = Column(Boolean, nullable=False, index=True, default=True) topic = relationship('Topic', backref=backref('posts', lazy='dynamic', cascade='all,delete', order_by='Post.number'))
class ReportTableDefinition(JusticeCountsBase): """The definition for what a table within a report describes.""" __tablename__ = "report_table_definition" id = Column(Integer, autoincrement=True) system = Column(Enum(System)) metric_type = Column(Enum(MetricType)) measurement_type = Column(Enum(MeasurementType)) # Any dimensions where the data in the table only accounts for a subset of values for that dimension. For instance, # a table for the population metric may only cover data for the prison population, not those on parole or # probation. In that case filters would include a filter on population type. filtered_dimensions = Column(ARRAY(String(255))) # The value for each dimension from the above array. filtered_dimension_values = Column(ARRAY(String(255))) # The dimensions that the metric is broken down by in the report table. Each cell in a table instance has a unique # combination of values for the aggregated dimensions. Dimensions are sorted deterministically within the array. aggregated_dimensions = Column(ARRAY(String(255))) __table_args__ = tuple( [ PrimaryKeyConstraint(id), UniqueConstraint( metric_type, measurement_type, filtered_dimensions, filtered_dimension_values, aggregated_dimensions, ), ] )
class Report(JusticeCountsBase): """A document that is published by a source that contains data pertaining to the Justice Counts Framework. """ __tablename__ = 'report' id = Column(Integer, autoincrement=True) # The source that this report is published by. source_id = Column(Integer, nullable=False) # This distinguishes between the many types of reports that a single source may produce, e.g. a Daily Status # Report or Monthly Fact Sheet, that contain different information and are potentially fetched and parsed using # different logic. type = Column(String(255), nullable=False) # Identifies a specific instance of a given report type. It should be constructed such that it is unique within a # given report type and source. The combination of report type and instance is used when ingesting a report to # determine whether this is an update to an existing report or a new report. For PDF reports, this may simply be # the title of the document after some validation has been performed. For webpages it may need to be dynamically # generated. instance = Column(String(255), nullable=False) # The date the report was published. publish_date = Column(Date, nullable=False) # The method used to acquire the data (e.g. scraped). acquisition_method = Column(Enum(AcquisitionMethod), nullable=False) # TODO(#4485): Add a list of projects (e.g. Justice Counts, Spark) for which this data was ingested. __table_args__ = tuple([ PrimaryKeyConstraint(id), UniqueConstraint(source_id, type, instance), ForeignKeyConstraint([source_id], [Source.id])]) source = relationship(Source)
class ReportUnknownPackage(GenericTable): __tablename__ = "reportunknownpackages" __table_args__ = ( UniqueConstraint('report_id', 'type', 'name', 'epoch', 'version', 'release', 'arch_id'), ) id = Column(Integer, primary_key=True) report_id = Column(Integer, ForeignKey("{0}.id".format(Report.__tablename__), ondelete="CASCADE"), nullable=False) type = Column(Enum("CRASHED", "RELATED", "SELINUX_POLICY", name="reportpackage_type")) name = Column(String(256), nullable=False, index=True) epoch = Column(Integer, nullable=False) version = Column(String(64), nullable=False) release = Column(String(64), nullable=False) arch_id = Column(Integer, ForeignKey("{0}.id".format(Arch.__tablename__)), nullable=False) count = Column(Integer, nullable=False) report = relationship(Report, backref="unknown_packages") arch = relationship(Arch, primaryjoin="Arch.id==ReportUnknownPackage.arch_id") semver = Column(Semver, nullable=False) # semantic version semrel = Column(Semver, nullable=False) # semantic release Index("ix_reportunknownpackages_semver_semrel", semver, semrel) def nvr(self): return "{0}-{1}-{2}".format(self.name, self.version, self.release) def nevr(self): if not self.epoch: return self.nvr() return "{0}-{1}:{2}-{3}".format(self.name, self.epoch, self.version, self.release) def evr(self): return "{0}:{1}-{2}".format(self.epoch, self.version, self.release)
class UserStat(BaseModel): __tablename__ = 'user_stats' __table_args__ = (UniqueConstraint('user_id', 'type_id', name='unique_user_stat_type'), ) value = Column(Integer, nullable=False) user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False, index=True) type_id = Column(Integer, ForeignKey('user_stat_types.id', ondelete='CASCADE'), nullable=False, index=True) user = relationship("User", lazy="select") type = relationship("UserStatType", lazy="select") def __repr__(self): s = super().__repr__()[:-2] f = "user_id={0.user_id!r},type_id={0.type_id!r},value={0.value!r}".format( self) return s + f + ")>"
class InteractionCategory(Base): __tablename__ = 'interaction_category' id = Column(Integer, primary_key=True) user_id = Column(ForeignKey('users.user_id'), nullable=False) interaction_id = Column(ForeignKey('interactions.id'), nullable=False) category_id = Column(ForeignKey('category.category_id'), nullable=False) sm_account_id = Column(ForeignKey('sm_accounts.sm_account_id'), nullable=False) created_date = Column(DateTime) user = relationship('User') interaction = relationship('Interactions') category = relationship('Category') sm_account = relationship('SmAccounts') __table_args__ = (UniqueConstraint('user_id', 'interaction_id', 'category_id', name='int_cat_u_idx'), Index('int_sm_cat_idx', id.desc(), sm_account_id, category_id, postgresql_where=user_id == 1))
class SqlIntegerSequencedItem(Base): __tablename__ = 'integer_sequenced_items' id = Column(Integer, Sequence('integer_sequened_item_id_seq'), primary_key=True) # Sequence ID (e.g. an entity or aggregate ID). sequence_id = Column(UUIDType(), index=True) # Position (index) of item in sequence. position = Column(BigInteger(), index=True) # Topic of the item (e.g. path to domain event class). topic = Column(String(255)) # State of the item (serialized dict, possibly encrypted). data = Column(Text()) # Unique constraint includes 'entity_id' which is a good value # to partition on, because all events for an entity will be in the same # partition, which may help performance. __table_args__ = UniqueConstraint('sequence_id', 'position', name='integer_sequenced_item_uc'),
class SqlTimestampSequencedItem(Base): # Explicit table name. __tablename__ = 'timestamp_sequenced_items' # Unique constraint. __table_args__ = UniqueConstraint('sequence_id', 'position', name='time_sequenced_items_uc'), # Primary key. id = Column(Integer, Sequence('integer_sequened_item_id_seq'), primary_key=True) # Sequence ID (e.g. an entity or aggregate ID). sequence_id = Column(UUIDType(), index=True) # Position (timestamp) of item in sequence. position = Column(Float(), index=True) # Topic of the item (e.g. path to domain event class). topic = Column(String(255)) # State of the item (serialized dict, possibly encrypted). data = Column(Text())
class Depot(TimeStampMixin, Base): id = Column(BigInteger, primary_key=True) code = Column(String, nullable=False,) name = Column(String) description = Column(String) # , nullable=False is_active = Column(Boolean, default=True) org_id = Column(Integer, nullable=True, default=0) max_volume = Column(Integer, nullable=True, default=0) max_weight = Column(Integer, nullable=True, default=0) # location = relationship("Location", backref="location_depot") location_id = Column(BigInteger, ForeignKey("location.id")) flex_form_data = Column(JSON, default={}) search_vector = Column( TSVectorType( "code", "name", "description", weights={"email": "A", "full_name": "B"}, ) ) __table_args__ = (UniqueConstraint('code', 'org_id', name='uix_org_id_depot_code'),)
class Item(TimeStampMixin, Base): id = Column(BigInteger, primary_key=True) code = Column( String, nullable=False, ) name = Column(String) description = Column(String) # , nullable=False is_active = Column(Boolean, default=True) org_id = Column(Integer, nullable=False, default=0) # In meter cubical volume = Column(Float, nullable=True, default=0.001) # In KG weight = Column(Float, nullable=True, default=1) flex_form_data = Column(JSON, default={}) search_vector = Column(TSVectorType( "code", "name", "description", )) __table_args__ = (UniqueConstraint('code', 'org_id', name='uix_org_id_item_code'), )
class qAnswersMCMR(Base): __tablename__ = 'qAnswersMCMR' __table_arg__ = (UniqueConstraint("questionNo", "variationNo", "answerNo", "choiceNo")) questionNo = Column(Integer, autoincrement=True, primary_key=True) variationNo = Column(SMALLINT, primary_key=True) answerNo = Column(TINYINT, primary_key=True) choiceNo = Column(TINYINT, primary_key=True) choiceValue = Column(String(255)) isAnswer = Column(TINYINT) canRandomize = Column(TINYINT) def __init__(self, questionNo, variationNo, answerNo, choiceNo, choiceValue, isAnswer=0, canRandomize=1): """Constructor""" if questionNo: self.questionNo = questionNo self.variationNo = variationNo self.answerNo = answerNo self.choiceNo = choiceNo self.choiceValue = choiceValue self.isAnswer = isAnswer self.canRandomize = canRandomize def __repr__(self): """Show this object (database record)""" return "<User(%d, %s)>" % (self.questionNo, self.variationNo, self.answerNo, self.choiceNo)
class User(Base): """Model class that provides a user.""" __tablename__ = "user" __table_args__ = (UniqueConstraint("username"), ) id = Column(Integer, primary_key=True) created_at = Column(DateTime(timezone=True), default=func.now()) parent_id = Column(Integer, ForeignKey("user.id")) username = Column(String, nullable=False) encrypted_password = Column(String, nullable=False) deactivated = Column(Boolean, nullable=False, index=True, default=False) ident = Column(String, nullable=False) ident_type = Column(IdentTypeEnum, default="ident", nullable=False) name = Column(String, nullable=False) parent = relationship( "User", remote_side=[id], backref=backref("children", lazy="dynamic", cascade="all,delete", order_by="User.id"), ) groups = relationship( "Group", secondary="user_group", order_by="Group.name", backref=backref("users", lazy="dynamic", order_by="User.id"), )
class Source(Base): __tablename__ = "sources" id = Column(Integer, primary_key=True) iso = Column(String, nullable=False) name = Column(String, nullable=True) number = Column(Integer, nullable=True) onCreated = Column(DateTime, default=datetime.datetime.utcnow) countries = relationship("Association", back_populates="source") source_destinys = relationship("Destiny", back_populates="source") __table_args__ = (UniqueConstraint("iso", name="_iso_unique"), ) def __str__(self): return f'id: {self.id}, name: {self.name}, iso: {self.iso}, number: {self.number}, onCreated: {self.onCreated}' def to_dic(self): return { 'id_source': self.id, 'name_source': self.name, 'iso': self.iso, 'number': self.number, 'onCreated': self.onCreated }
class SecurityId(Base): """Unique identifier for security. """ id = Column(Integer, primary_key=True) security_id = Column( Integer, ForeignKey("security.id", onupdate="CASCADE"), nullable=False, comment="FK security.id", ) security = relationship("Security", back_populates="ids") uniqueidtype = Column(String, nullable=False, comment="CUSIP, ISIN, etc.") uniqueid = Column(String, nullable=False, comment="CUSIP, ISIN, etc.") __table_args__ = ( UniqueConstraint("uniqueidtype", "uniqueid"), { "comment": "Unique Identifiers for Securities" }, ) def __repr__(self): rp = "SecurityId(id={}, uniqueidtype='{}', uniqueid='{}', security={})" return rp.format(self.id, self.uniqueidtype, self.uniqueid, self.security)
class Question(Base, IdMixin): __tablename__ = "questions" __table_args__ = ( UniqueConstraint("name", "base_course"), Index("chap_subchap_idx", "chapter", "subchapter"), ) base_course = Column(String(512), nullable=False, index=True) name = Column(String(512), nullable=False, index=True) chapter = Column(String(512), index=True, nullable=False) subchapter = Column(String(512), index=True, nullable=False) author = Column(String(512)) question = Column(Text) timestamp = Column(DateTime, nullable=False) question_type = Column(String(512), nullable=False) is_private = Column(Web2PyBoolean) htmlsrc = Column(Text) autograde = Column(String(512)) practice = Column(Web2PyBoolean) topic = Column(String(512)) feedback = Column(Text) from_source = Column(Web2PyBoolean, nullable=False) review_flag = Column(Web2PyBoolean) qnumber = Column(String(512)) optional = Column(Web2PyBoolean) description = Column(Text) difficulty = Column(Float(53)) pct_on_first = Column(Float(53)) mean_clicks_to_correct = Column(Float(53))
class CurrencyRate(Base, Mergeable): """Exchange rate for currency pair. """ id = Column(Integer, primary_key=True) date = Column(Date, nullable=False) fromcurrency = Column( CurrencyType, nullable=False, comment="Currency of exchange rate denominator (ISO4217)", ) tocurrency = Column( CurrencyType, nullable=False, comment="Currency of exchange rate numerator (ISO417)", ) rate = Column( Numeric, nullable=False, comment= "Multiply this rate by fromcurrency amount to yield tocurrency amount", ) __table_args__ = ( UniqueConstraint("date", "fromcurrency", "tocurrency"), { "comment": "Exchange Rates for Currency Pairs" }, ) signature = ("date", "fromcurrency", "tocurrency") @classmethod def get_rate(cls, session, fromcurrency, tocurrency, date): """ Returns `rate` (type decimal.Decimal) as `tocurrency` / `fromcurrency` i.e. `fromCurrency` * `rate` == 'tocurrency` """ if fromcurrency is None or tocurrency is None or date is None: msg = ("CurrencyRate.get_rate(): missing argument in " "(fromcurrency='{}', tocurrency='{}', date={}") raise ValueError(msg.format(fromcurrency, tocurrency, date)) try: instance = (session.query(cls).filter_by(fromcurrency=fromcurrency, tocurrency=tocurrency, date=date).one()) rate = instance.rate except NoResultFound: try: instance = (session.query(cls).filter_by( fromcurrency=tocurrency, tocurrency=fromcurrency, date=date).one()) except NoResultFound: msg = ("CurrencyRate.get_rate(): no DB record for " "(fromcurrency='{}', tocurrency='{}', date={})") raise ValueError(msg.format(fromcurrency, tocurrency, date)) rate = 1 / instance.rate return rate
class CarbonHydrogen(Base): __tablename__ = 'carbonHydrogen' __table_args__ = ( UniqueConstraint('C', 'H', name='unique_c_h'), ) id = Column(Integer, primary_key=True, unique=True, nullable=False) C = Column(Integer, nullable=False) H = Column(Integer, nullable=False) heteroAtoms = relationship("HeteroAtoms", secondary="molecularformula", ) def __repr__(self): return '<CarbonHydrogen Model {} C{} H{}>'.format(self.id, self.C, self.H) @property def mass(self): return (self.C * Atoms.atomic_masses.get('C')) + (self.H * Atoms.atomic_masses.get('H')) @hybrid_property def c(cls): return cls.C.cast(Float) @hybrid_property def h(cls): return cls.H.cast(Float) @hybrid_property def dbe(cls): return float(cls.C) - float(cls.H/2) + 1
class Authenticator(db.Model): __tablename__ = "authenticators" __table_args__ = (UniqueConstraint("credential_id", "user_id"), ) id = db.Column(pg.UUID(as_uuid=True), primary_key=True, default=uuid4) name = db.Column(db.String, nullable=False, default="authenticator") credential_id = db.Column(db.String, nullable=False) public_key = db.Column(db.String, nullable=False) sign_count = db.Column(db.Integer, nullable=False) created_at = db.Column( db.TIMESTAMP(timezone=True), nullable=False, server_default=functions.current_timestamp(), ) last_used_at = db.Column(db.TIMESTAMP(timezone=True)) user_id = db.Column(pg.UUID(as_uuid=True), db.ForeignKey("users.id"), nullable=False) user = db.relationship("User", backref="authenticators") @classmethod def generate_fake(cls): return cls( id=uuid4(), name="authenticator", credential_id="7SI3X75x6wFGxMS1TAHQEFRtifLfD9Gi", public_key="iOy7iL2iTKhQ3t4fFHrG3zwSivyaDYIS", sign_count=11, )
class TagType(Base, TimeStampMixin, ProjectMixin): __table_args__ = (UniqueConstraint("name", "project_id"), ) id = Column(Integer, primary_key=True) name = Column(String) description = Column(String) exclusive = Column(Boolean, default=False) search_vector = Column(TSVectorType("name"))
class SqlStoredEvent(Base): # Explicit table name. __tablename__ = 'stored_events' # Unique constraint. __table_args__ = UniqueConstraint('aggregate_id', 'aggregate_version', name='stored_events_uc'), # Primary key. id = Column(Integer, Sequence('stored_event_id_seq'), primary_key=True) # Sequence ID (e.g. an entity or aggregate ID). aggregate_id = Column(UUIDType(), index=True) # Position (timestamp) of item in sequence. aggregate_version = Column(BigInteger(), index=True) # Type of the event (class name). event_type = Column(String(100)) # Timestamp of the event. timestamp = Column(Float()) # State of the item (serialized dict, possibly encrypted). state = Column(Text())
def _build_measure_tables(self): self.measure = Table( "measure", self.metadata, Column("id", Integer(), primary_key=True), Column( "measure_id", String(128), nullable=False, index=True, unique=True, ), Column("instrument_name", String(64), nullable=False, index=True), Column("measure_name", String(64), nullable=False, index=True), Column("description", String(255)), Column("measure_type", Enum(MeasureType), index=True), Column("individuals", Integer()), Column("default_filter", String(255)), Column("min_value", Float(), nullable=True), Column("max_value", Float(), nullable=True), Column("values_domain", String(255), nullable=True), Column("rank", Integer(), nullable=True), UniqueConstraint( "instrument_name", "measure_name", name="measure_key" ), )
class DataOfSubmissionBoard(Base): __tablename__ = 'DataOfSubmissionBoard' submissionIndex = Column(INTEGER(unsigned=True), primary_key=True, autoincrement=True, nullable=False) memberIdIndex = Column(INTEGER(unsigned=True), ForeignKey(Members.memberIdIndex, onupdate='CASCADE', ondelete='CASCADE'), nullable=False) problemIndex = Column(INTEGER(unsigned=True), ForeignKey(Problems.problemIndex, onupdate='CASCADE', ondelete='CASCADE'), nullable=False) viewCount = Column(INTEGER(unsigned=True), default=0, nullable=False) submissionReplyCount = Column(INTEGER(unsigned=True), default=0, nullable=False) sumOfLikeCount = Column(INTEGER(unsigned=True), default=0, nullable=False) __table_args__ = (UniqueConstraint('memberIdIndex', 'problemIndex'), )
class IndividualContact(Base, ContactMixin, ProjectMixin): __table_args__ = (UniqueConstraint("email", "project_id"), ) id = Column(Integer, primary_key=True) name = Column(String) mobile_phone = Column(String) office_phone = Column(String) title = Column(String) weblink = Column(String) external_id = Column(String) events = relationship("Event", backref="individual") filters = relationship("SearchFilter", secondary=assoc_individual_filters, backref="individuals") team_contact_id = Column(Integer, ForeignKey("team_contact.id")) team_contact = relationship("TeamContact", backref="individuals") search_vector = Column( TSVectorType( "name", "title", "company", "notes", weights={ "name": "A", "title": "B", "company": "C", "notes": "D" }, ))
def _build_person_tables(self): self.family = Table( "family", self.metadata, Column("id", Integer(), primary_key=True), Column( "family_id", String(64), nullable=False, unique=True, index=True, ), ) self.person = Table( "person", self.metadata, Column("id", Integer(), primary_key=True), Column("family_id", ForeignKey("family.id")), Column("person_id", String(16), nullable=False, index=True), Column("role", Enum(Role), nullable=False), Column( "status", Enum(Status), nullable=False, default=Status.unaffected, ), Column("sex", Enum(Sex), nullable=False), Column("sample_id", String(16), nullable=True), UniqueConstraint("family_id", "person_id", name="person_key"), )
class Users(Entity, Base): __tablename__ = 'users' user_id = Column(Integer, primary_key=True) contact_id = Column(Integer) display_name = Column(String(50)) phone_number = Column(String(12)) country_phone_code = Column(String(5)) callback_url = Column(String(5)) password = Column(String(20)) telesign_api_key = Column(Text) telesign_customer_id = Column(Text) __table_args__ = (UniqueConstraint('phone_number', 'display_name', name='_phone_display_uc'), ) def __init__(self, display_name, phone_number, country_code, callback_url=None, password='******', ts_api_key=None, ts_cust_id=None, contact_id=None): Entity.__init__(self) #self.user_id = user_id self.display_name = display_name self.phone_number = phone_number + country_code self.country_phone_code = country_code self.callback_url = callback_url self.password = password self.telesign_api_key = ts_api_key self.telesign_customer_id = ts_cust_id self.contact_id = contact_id
class IncidentType(ProjectMixin, Base): __table_args__ = (UniqueConstraint("name", "project_id"),) id = Column(Integer, primary_key=True) name = Column(String) slug = Column(String) description = Column(String) exclude_from_metrics = Column(Boolean, default=False) default = Column(Boolean, default=False) visibility = Column(String, default=Visibility.open.value) plugin_metadata = Column(JSON, default=[]) template_document_id = Column(Integer, ForeignKey("document.id")) template_document = relationship("Document") commander_service_id = Column(Integer, ForeignKey("service.id")) commander_service = relationship("Service", foreign_keys=[commander_service_id]) liaison_service_id = Column(Integer, ForeignKey("service.id")) liaison_service = relationship("Service", foreign_keys=[liaison_service_id]) search_vector = Column(TSVectorType("name", "description")) @hybrid_method def get_meta(self, slug): if not self.plugin_metadata: return for m in self.plugin_metadata: if m["slug"] == slug: return m
class WorkflowTemplate(db.Model): __tablename__ = 'template_v2' __table_args__ = (UniqueConstraint('name', name='uniq_name'), Index('idx_group_alias', 'group_alias'), { 'comment': 'workflow template', 'mysql_engine': 'innodb', 'mysql_charset': 'utf8mb4', }) id = db.Column(db.Integer, primary_key=True, comment='id') name = db.Column(db.String(255), comment='name') comment = db.Column('cmt', db.String(255), key='comment', comment='comment') group_alias = db.Column(db.String(255), nullable=False, comment='group_alias') config = db.Column(db.LargeBinary(), nullable=False, comment='config') is_left = db.Column(db.Boolean, comment='is_left') def set_config(self, proto): self.config = proto.SerializeToString() def get_config(self): proto = workflow_definition_pb2.WorkflowDefinition() proto.ParseFromString(self.config) return proto
class Group(Base): """Model class for group-based ACL.""" __tablename__ = "group" __table_args__ = (UniqueConstraint("name"),) id = Column(Integer, primary_key=True) name = Column(String, nullable=False)
class Symbol(GenericTable): __tablename__ = "symbols" __table_args__ = (UniqueConstraint('name', 'normalized_path'), ) id = Column(Integer, primary_key=True) name = Column(String(32768), nullable=False) nice_name = Column(String(32768)) normalized_path = Column(String(512), nullable=False)