class AmzAccountProfileRel(Base, BaseMethod): __tablename__ = 'amz_account_profile_rels' id = Column(BIGINT, primary_key=True) account_id = Column(VARCHAR(64)) # id in AmzAccountInfo market_id = Column(BIGINT) # for multiple markets profile_id = Column(VARCHAR(64)) # profile_id in MlaProfile
class DeveloperData(Base_item): __tablename__ = "developer_Data" id = Column(Integer, primary_key=True) developer_group_id = Column(VARCHAR(20), ForeignKey("developers.developer_group_id"), nullable=False) region_id = Column(VARCHAR(20), ForeignKey("regions.region_id"), nullable=False) startDate = Column(VARCHAR(20)) endDate = Column(VARCHAR(20)) total_living_floor_size = Column(INTEGER, nullable=True) appt_num = Column(INTEGER, nullable=True) object_count = Column(INTEGER, nullable=True) total_living_floor_size_pct = Column(FLOAT, nullable=True) typed_volume_pct = Column(FLOAT, nullable=True) rating = Column(INTEGER, nullable=True) def __repr__(self): return "<Base_item(id='%s', developer_group_id='%s',\ region_id='%s', startDate='%s'\ endDate='%s', total_living_floor_size='%s'\ appt_num='%s', object_count='%s'\ total_living_floor_size_pct='%s', typed_volume_pct='%s'\ rating='%s')>"\ %(self.id, self.developer_group_id, self.region_id, self.startDate, self.endDate, self.total_living_floor_size, self.appt_num, self.object_count, self.total_living_floor_size_pct, self.typed_volume_pct, self.rating)
class AmzSaleFarmPrjTask(Base, BaseMethod): __tablename__ = 'amz_sale_farm_prj_tasks' _STATE_INIT = 0 _STATE_SCHEDULED = 1 _STATE_ADD_TO_CART = 2 _STATE_ADD_TO_WISH = 3 _STATE_ORDER_DONE = 4 _STATE_SHIPMENT_DONE = 5 _STATE_REVIEW_DONE = 6 id = Column(BIGINT, primary_key=True) prj_id = Column(BIGINT, ForeignKey('amz_sale_farm_prjs.id')) state = Column(BIGINT, default=_STATE_INIT) addr_id = Column(BIGINT, default=0) buyer_id = Column(BIGINT, default=0) debit_card_id = Column(BIGINT, default=0) order_no = Column(VARCHAR(64), default='') shipment_no = Column(VARCHAR(64), default='') created_at = Column(TIMESTAMP(timezone=True), default=datetime.utcnow) updated_at = Column(TIMESTAMP(timezone=True), onupdate=datetime.utcnow) scheduled_at = Column(TIMESTAMP(timezone=True)) carted_at = Column(TIMESTAMP(timezone=True)) wished_at = Column(TIMESTAMP(timezone=True)) ordered_at = Column(TIMESTAMP(timezone=True)) shipped_at = Column(TIMESTAMP(timezone=True)) reviewed_at = Column(TIMESTAMP(timezone=True)) comments = Column(TEXT, default='')
class Taxa(db.Model): __tablename__ = 'taxa' id = db.Column(BIGINT, primary_key=True) tax_rank = db.Column(VARCHAR(128), default=True, nullable=True) tax_name = db.Column(VARCHAR(128), default=True, nullable=True) tax_color = db.Column(VARCHAR(12), default=True, nullable=True) parent = db.Column(BIGINT, default=True, nullable=True) superkingdom = db.Column(BIGINT, default=True, nullable=True) reads = db.Column(BIGINT, default=0, nullable=False) results_taxoncount = db.relationship('ResultsTaxoncount', backref='taxa') taxon_infos = db.relationship('TaxonInfo', uselist=False, backref='taxa') def __init__(self, tax_rank, tax_name, tax_color, parent, superkingdom, reads): self.tax_rank = tax_rank self.tax_name = tax_name self.tax_color = tax_color self.parent = parent self.superkingdom = superkingdom self.reads = reads def __repr__(self): return '<id {}>'.format(self.id)
class UserSurvey(db.Model): __tablename__ = 'user_survey' id = db.Column(BIGINT, primary_key=True) user = db.Column(BIGINT, db.ForeignKey('user.id'), nullable=False) dob = db.Column(VARCHAR(128), nullable=False) city_and_country = db.Column(VARCHAR(128), nullable=False) gender = db.Column(VARCHAR(128), nullable=False) race = db.Column(VARCHAR(128), nullable=False) allow_data_comparison = db.Column(BOOLEAN, default=False) term_of_service = db.Column(BOOLEAN, default=False) privacy_policy = db.Column(BOOLEAN, default=False) induction_completed = db.Column(BOOLEAN, default=False) service_consent = db.Column(BOOLEAN, default=False) consent_on_file = db.Column(BOOLEAN, default=False) record_created = db.Column(TIMESTAMP, default=func.current_timestamp(), nullable=False) def __init__(self, user, dob, city_and_country, gender, race, allow_data_comparison, term_of_service, privacy_policy, induction_completed, service_consent): self.user = user self.dob = dob self.city_and_country = city_and_country self.gender = gender self.race = race self.allow_data_comparison = allow_data_comparison self.privacy_policy = privacy_policy self.induction_completed = induction_completed self.service_consent = service_consent def __repr__(self): return '<id {}>'.format(self.id)
class Image(Model): __tablename__ = 'images' imgid = Column(BIGINT, Sequence('seq_images'), primary_key=True) # The Object that this image belongs to # TODO: It looks like we have a relationship cycle Object->Image->Object # Probably due to the fact that several images can exist for a single Object # Real PK: objid + imgrank or better orig_id + imgrank objid = Column(BIGINT, ForeignKey('obj_head.objid')) imgrank = Column(INTEGER) file_name = Column(VARCHAR(255)) orig_file_name = Column(VARCHAR(255)) width = Column(INTEGER) height = Column(INTEGER) thumb_file_name = Column(VARCHAR(255)) thumb_width = Column(INTEGER) thumb_height = Column(INTEGER) @staticmethod def fetch_existing_images(session: Session, prj_id): """ Get all object/image pairs from the project """ res: ResultProxy = session.execute( # Must be reloaded from DB, as phase 1 added all objects for duplicates checking # TODO: Why using the view? "SELECT concat(o.orig_id,'*',i.orig_file_name) " " FROM images i " " JOIN objects o ON i.objid = o.objid " " WHERE o.projid = :prj", {"prj": prj_id}) ret = {img_id for img_id, in res} return ret def __lt__(self, other): return self.imgid < other.imgid
class ProjectPrivilege(Model): """ What users can do on a project. Is an Association object, @see https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object """ __tablename__ = 'projectspriv' # TODO: Isn't there a natural PK with all columns? id = Column(INTEGER, Sequence('seq_projectspriv'), primary_key=True) # links projid = Column(INTEGER, ForeignKey('projects.projid', ondelete="CASCADE"), nullable=False) # TODO: Same as project: if a user is gone, no interest in keeping its privileges. # OTOH we don't so far (17 Aug 2020) delete users. member = Column(INTEGER, ForeignKey('users.id', ondelete="CASCADE"), nullable=False) # association value privilege = Column(VARCHAR(255), nullable=False) # complement of the privilege, so far just 'C' for Contact who is a manager extra = Column(VARCHAR(1), nullable=True) # relationships # The relationships are created in Relations.py but the typing here helps the IDE project: relationship user: relationship def __str__(self): return "{0} ({1})".format(self.member, self.privilege)
class Experiments(db.Model): __tablename__ = 'experiments' id = db.Column(BIGINT, primary_key = True) name = db.Column(VARCHAR(64), default = True, nullable = False) color = db.Column(VARCHAR(6), default = True, nullable = False) description = db.Column(TEXT, default = True, nullable = False) instructions = db.Column(TEXT) spare_answer_id = db.Column(BIGINT, default = True, nullable = True) tag = db.Column(VARCHAR(10), default = True, nullable = True) deliver_data = db.Column(SMALLINT, default = 0, nullable = False) samples = db.relationship('Samples', backref='experiments') kit_types_questions = db.relationship('KitTypesQuestions', secondary=kit_types_questions_X_experiments, backref=db.backref('experiments', lazy='dynamic')) def __init__(self, name, color, description, instructions, spare_answer_id, tag, deliver_data): self.name = name self.color = color self.description = description self.instructions = instructions self.spare_answer_id = spare_answer_id self.tag = tag self.deliver_data = deliver_data def __repr__(self): return '<id {}>'.format(self.id)
class AmzSaleFarmPrj(Base, BaseMethod): __tablename__ = 'amz_sale_farm_prjs' id = Column(BIGINT, primary_key=True) marketplace_id = Column(BIGINT) asin = Column(VARCHAR(16), nullable=False) is_adult = Column(BOOLEAN, nullable=False) group_desc = Column(VARCHAR(128), default='aps') merchant_name = Column(VARCHAR(256)) brand = Column(VARCHAR(256)) keywords = Column(VARCHAR(256), nullable=False) actual_price = Column(REAL, default=0.0) # min value is $0.01 start_date = Column(BIGINT, default=make_date_period) end_date = Column(BIGINT) plan = Column(JSONB) # e.g. {date_period: order_cnt} has_coupon = Column(BOOLEAN, default=False) created_at = Column(TIMESTAMP(timezone=True), default=datetime.utcnow) updated_at = Column(TIMESTAMP(timezone=True), default=datetime.utcnow) def to_json(self): return { 'marketplace_id': self.marketplace_id, 'asin': self.asin, 'is_adult': self.is_adult, 'group_desc': self.group_desc, 'merchant_name': self.merchant_name, 'brand': self.brand_name, 'keywords': self.keywords, 'actual_price': self.actual_price, }
class User(MODEL): """ Defines a user entry and logged user """ __tablename__ = "user" userid = Column(INTEGER, primary_key=True) email = Column(VARCHAR(128), index=True) password = Column(VARCHAR(128)) entries = relationship("Entry", back_populates="user", lazy="joined", cascade="all, delete") @property def is_authenticated(self): return True @property def is_active(self): return True @property def is_anonymous(self): return False def get_id(self): return str(self.email)
class EcotaxaInst(db.Model): __tablename__ = 'ecotaxainst' id = db.Column(INTEGER, db.Sequence('seq_ecotaxainst'), primary_key=True) name = db.Column(VARCHAR(100), nullable=False) url = db.Column(VARCHAR(100)) laststatupdate_datetime = db.Column(TIMESTAMP(precision=0)) ecotaxa_version = db.Column(VARCHAR(10)) sharedsecret = db.Column(VARCHAR(100), nullable=False)
class TempTaxo(db.Model): __tablename__ = 'temp_taxo' idtaxo = db.Column(VARCHAR(20), primary_key=True) idparent = db.Column(VARCHAR(20)) name = db.Column(VARCHAR(100)) status = db.Column(CHAR(1)) typetaxo = db.Column(VARCHAR(20)) idfinal = db.Column(INTEGER)
class Source_entry(Base_item): __tablename__ = "sources" source_id = Column(INTEGER, primary_key=True) source_spider = Column(VARCHAR(20), unique=True) source_domain = Column(VARCHAR(50), unique=True) def __repr__(self): return "<Base_item(source_id='%s', source_spider='%s', source_domain='%s')>"\ %(self.source_id, self.source_spider, self.source_domain)
class CourseCorequisite(Base): __tablename__ = 'course_corequisite' department = Column(VARCHAR(length=255)) level = Column(INTEGER) corequisite = Column(VARCHAR(length=255)) __table_args__ = (PrimaryKeyConstraint('department', 'level', 'corequisite'), )
class NormJournal(Base): __tablename__ = 'norm_journal' id = Column(INTEGER, primary_key=True, autoincrement=True) title = Column(VARCHAR(2048)) abbreviated_title = Column(VARCHAR(512)) print_issn = Column(VARCHAR(9)) electronic_issn = Column(VARCHAR(9))
class User(MyBase, Base): __tablename__ = 'users' id = Column(UUID(), primary_key=True, nullable=False) active = Column(BOOLEAN()) username = Column(VARCHAR(50), unique=True) password = Column(TEXT()) google_id = Column(VARCHAR(50)) email = Column(VARCHAR(256), unique=True) face_analysis = Column(TEXT())
class StudentCourseSelection(Base): __tablename__ = "student_course_selection" user_id = Column(INTEGER, ForeignKey('user_account.user_id')) semester = Column(VARCHAR(length=255)) course_name = Column(VARCHAR(length=255)) crn = Column(VARCHAR(length=255)) __table_args__ = (PrimaryKeyConstraint('user_id', 'semester', 'course_name', 'crn'), )
class CollectionOrgaRole(Model): __tablename__ = 'collection_orga_role' """ n<->n valued relationship b/w collection and organisations """ collection_id = Column(INTEGER, ForeignKey('collection.id'), primary_key=True) organisation = Column(VARCHAR(255), primary_key=True) role = Column(VARCHAR(1), # 'C' for data Creator, 'A' for Associated 'person' nullable=False, primary_key=True) def __str__(self): return "{0},{1}:{2}".format(self.collection_id, self.organisation, self.role)
def upgrade() -> None: op.create_table( "requests", sa.Column("request_id", UUID, primary_key=True), sa.Column("remote", VARCHAR(64), nullable=True), sa.Column("method", VARCHAR(7), nullable=False), sa.Column("path", VARCHAR(50), nullable=False), sa.Column("body", JSONB, nullable=True), sa.Column("created_at", TIMESTAMP, server_default=SERVER_NOW), sa.PrimaryKeyConstraint("request_id"), )
class RawDocument(Base): __tablename__ = 'raw_document' __table_args__ = (UniqueConstraint('gathering_source', 'collection', 'pid'), ) id = Column(INTEGER, primary_key=True, autoincrement=True) gathering_source = Column(VARCHAR(255), nullable=False) gathering_date = Column(TIMESTAMP, nullable=False) collection = Column(VARCHAR(255), nullable=False) pid = Column(VARCHAR(255), nullable=False) data = Column(JSON)
class Acquisitions(db.Model): __tablename__ = 'acquisitions' acquisid = db.Column(BIGINT, db.Sequence('seq_acquisitions'), primary_key=True) projid = db.Column(INTEGER, db.ForeignKey('projects.projid')) project = db.relationship("Projects") orig_id = db.Column(VARCHAR(255)) instrument = db.Column(VARCHAR(255)) def __str__(self): return "{0} ({1})".format(self.orig_id, self.processid)
class AmzMWSAccount(Base, BaseMethod): __tablename__ = 'amz_mws_accounts' id = Column(BIGINT, primary_key=True) marketplace_id = Column(BIGINT, default=1) name = Column(VARCHAR(32), nullable=False) merchant_id = Column(VARCHAR(16), nullable=False) key = Column(VARCHAR(32), nullable=False) secret = Column(VARCHAR(64), nullable=False) ALL_ACCOUNTS = [ { 'id': 1, 'marketplace_id': 4, 'name': 'KingLove', 'merchant_id': 'A2FQRB3OKQXG87', 'key': 'AKIAI455C5UONC4FBREA', 'secret': '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' }, { 'id': 2, 'marketplace_id': 1, 'name': 'Yerongzhen', 'merchant_id': 'ADRAG77Y5JHQM', 'key': 'AKIAJAN5GVRH6AX2MFEA', 'secret': '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' }, { 'id': 3, 'marketplace_id': 1, 'name': 'KingLove', 'merchant_id': 'APH09EB54VUX5', 'key': 'AKIAIL2OQKM2TOWS6JLA', 'secret': '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' }, ] @classmethod def get_by_market_name(cls, marketplace_id, name): for acct in cls.ALL_ACCOUNTS: if acct['marketplace_id'] != marketplace_id\ or acct['name'] != name: continue acct = AmzMWSAccount(id=acct['id'], marketplace_id=acct['marketplace_id'], name=acct['name'], merchant_id=acct['merchant_id'], key=acct['key'], secret=acct['secret']) return acct else: return None
class Samples(db.Model): __tablename__ = 'samples' sampleid = db.Column(BIGINT, db.Sequence('seq_samples'), primary_key=True) projid = db.Column(INTEGER, db.ForeignKey('projects.projid')) project = db.relationship("Projects") orig_id = db.Column(VARCHAR(255)) latitude = db.Column(DOUBLE_PRECISION) longitude = db.Column(DOUBLE_PRECISION) dataportal_descriptor = db.Column(VARCHAR(8000)) def __str__(self): return "{0} ({1})".format(self.orig_id, self.processid)
class AmzGiftCard(Base, BaseMethod): __tablename__ = 'amz_gift_cards' id = Column(BIGINT, primary_key=True) marketplace_id = Column(BIGINT) gift_card = Column(VARCHAR(64), default='') value = Column(REAL, default=0.0) # min value is $0.01 provider = Column(VARCHAR(64), default='') is_used = Column(BOOLEAN, default=False) used_by = Column(BIGINT, nullable=True) created_at = Column(TIMESTAMP(timezone=True), default=datetime.utcnow) updated_at = Column(TIMESTAMP(timezone=True), onupdate=datetime.utcnow)
class Project(Model): """ Main holder of image data. """ __tablename__ = 'projects' projid = Column(INTEGER, Sequence('seq_projects'), primary_key=True) title = Column(VARCHAR(255), nullable=False) visible = Column(Boolean(), default=True) # owner_id = Column(INTEGER, default=0, nullable=False) # TODO: FK to user license = Column(VARCHAR(16), default=LicenseEnum.NO_LICENSE, nullable=False) status = Column(VARCHAR(40), default=ANNOTATE_STATUS ) # Annotate, ExploreOnly, Annotate No Prediction # The mappings for this Project # TODO: What happens if there is a conflict from one import to another? mappingobj = Column(VARCHAR) mappingsample = Column(VARCHAR) mappingacq = Column(VARCHAR) mappingprocess = Column(VARCHAR) # Calculated objcount = Column(DOUBLE_PRECISION) pctvalidated = Column(DOUBLE_PRECISION) pctclassified = Column(DOUBLE_PRECISION) # Settings classifsettings = Column(VARCHAR) # Settings for Automatic classification. initclassiflist = Column(VARCHAR) # Initial list of categories classiffieldlist = Column( VARCHAR ) # Fields available on sort & displayed field of Manual classif screen popoverfieldlist = Column( VARCHAR) # Fields available on popover of Manual classif screen comments = Column(VARCHAR) projtype = Column(VARCHAR(50)) # Note: It's loaded file_s_ fileloaded = Column(VARCHAR) rf_models_used = Column(VARCHAR) cnn_network_id = Column(VARCHAR(50)) # Associated taxonomy statistics. Commented out to avoid that the ORM loads the whole list, which can be big. # taxo_stats = relationship("ProjectTaxoStat") # The relationships are created in Relations.py but the typing here helps IDE all_samples: relationship # The users involved somehow in this project privs_for_members: relationship # owner: relationship # The twin EcoPart project ecopart_project: relationship def __str__(self): return "{0} ({1})".format(self.title, self.projid)
class Orgy(Base): __tablename__ = 'orgy' id = Column(INTEGER, primary_key=True) name = Column(VARCHAR(length=200), nullable=False) is_old = Column(BOOLEAN, nullable=False) foreground_url = Column(VARCHAR(length=100), nullable=False) background_url = Column(VARCHAR(length=100), nullable=False) is_left_to_right = Column(BOOLEAN, nullable=False) youtube_id = Column(VARCHAR(length=100)) creator_ip = Column(CIDR) def __repr__(self): return '<Orgy %s by %s>' % (self.name, self.creator_ip)
class CourseSession(Base): __tablename__ = "course_session" crn = Column(VARCHAR(length=255)) section = Column(VARCHAR(length=255)) semester = Column(VARCHAR(length=255)) time_start = Column(TIME) time_end = Column(TIME) day_of_week = Column(INTEGER) location = Column(VARCHAR(length=255)) __table_args__ = (PrimaryKeyConstraint('crn', 'section', 'semester', 'day_of_week'), )
class ObjectsFields(db.Model): __tablename__ = 'obj_field' objfid = db.Column(BIGINT, db.ForeignKey('obj_head.objid', ondelete="CASCADE"), primary_key=True) objhrel = db.relationship( "Objects", foreign_keys="Objects.objid", primaryjoin="ObjectsFields.objfid==Objects.objid", uselist=False, backref="objfrel") orig_id = db.Column(VARCHAR(255)) object_link = db.Column(VARCHAR(255))
class Images(db.Model): __tablename__ = 'images' imgid = db.Column(BIGINT, db.Sequence('seq_images'), primary_key=True) # manuel ,db.Sequence('seq_images') objid = db.Column(BIGINT, db.ForeignKey('obj_head.objid')) imgrank = db.Column(INTEGER) file_name = db.Column(VARCHAR(255)) orig_file_name = db.Column(VARCHAR(255)) width = db.Column(INTEGER) height = db.Column(INTEGER) thumb_file_name = db.Column(VARCHAR(255)) thumb_width = db.Column(INTEGER) thumb_height = db.Column(INTEGER)
class Metric_entry(Base_item): __tablename__ = "metrics" scraper = Column(VARCHAR(50)) spider = Column(VARCHAR(50)) activity_type = Column(VARCHAR(50)) table_name = Column(VARCHAR(50)) entry_time = Column(TIMESTAMP) def __repr__(self): return "<Base_item(scraper='%s', spider='%s', activity_type='%s',\ table_name='%s', entry_time='%s')>"\ % (self.scraper, self.spider, self.activity_type, self.table_name, self.entry_time)