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)