class Taxonomy(Model):
"""
A node in the taxonomy tree.
"""
__tablename__ = 'taxonomy'
id = Column(INTEGER, Sequence('seq_taxonomy'), primary_key=True)
parent_id = Column(INTEGER)
name = Column(VARCHAR(100), nullable=False)
id_source = Column(VARCHAR(20))
taxotype = Column(CHAR(1), nullable=False,
server_default='P') # P = Phylo , M = Morpho
display_name = Column(VARCHAR(200))
lastupdate_datetime = Column(TIMESTAMP(precision=0))
id_instance = Column(INTEGER)
taxostatus = Column(CHAR(1), nullable=False, server_default='A')
rename_to = Column(INTEGER)
source_url = Column(VARCHAR(200))
source_desc = Column(VARCHAR(1000))
creator_email = Column(VARCHAR(255))
creation_datetime = Column(TIMESTAMP(precision=0))
nbrobj = Column(INTEGER)
nbrobjcum = Column(INTEGER)
def __str__(self):
return "{0} ({1})".format(self.name, self.id)
class Boarding_passes(db.Model):
__tablename__ = 'boarding_passes'
__table_args__ = (
db.UniqueConstraint('flight_id',
'boarding_no',
name='boarding_passes_flight_id_boarding_no_key'),
db.UniqueConstraint('flight_id',
'seat_no',
name='boarding_passes_flight_id_seat_no_key'),
db.PrimaryKeyConstraint('ticket_no',
'flight_id',
name='boarding_passes_pkey'),
db.ForeignKeyConstraint(
['ticket_no', 'flight_id'],
['ticket_flights.ticket_no', 'ticket_flights.flight_id'],
name='boarding_passes_ticket_no_fkey'))
ticket_no = db.Column(CHAR(13), nullable=False)
flight_id = db.Column(INTEGER, nullable=False)
boarding_no = db.Column(INTEGER, nullable=False)
seat_no = db.Column(CHAR(4), nullable=False)
def __init__(self, ticket_no, flight_id, boarding_no, seat_no):
self.ticket_no = ticket_no
self.flight_id = flight_id
self.boarding_no = boarding_no
self.seat_no = seat_no
class ObjectsClassifHisto(db.Model):
__tablename__ = 'objectsclassifhisto'
objid = db.Column(BIGINT,
db.ForeignKey('obj_head.objid', ondelete="CASCADE"),
primary_key=True)
classif_date = db.Column(TIMESTAMP, primary_key=True)
classif_type = db.Column(CHAR(1)) # A : Auto, M : Manu
classif_id = db.Column(INTEGER)
classif_qual = db.Column(CHAR(1))
classif_who = db.Column(db.Integer, db.ForeignKey('users.id'))
classif_score = db.Column(DOUBLE_PRECISION)
class Objects(db.Model):
__tablename__ = 'obj_head'
objid = db.Column(BIGINT, db.Sequence('seq_objects'), primary_key=True)
projid = db.Column(INTEGER,
db.ForeignKey('projects.projid'),
nullable=False)
project = db.relationship("Projects")
latitude = db.Column(DOUBLE_PRECISION)
longitude = db.Column(DOUBLE_PRECISION)
objdate = db.Column(DATE)
objtime = db.Column(TIME)
depth_min = db.Column(FLOAT)
depth_max = db.Column(FLOAT)
images = db.relationship("Images")
classif_id = db.Column(INTEGER)
classif = db.relationship(
"Taxonomy",
primaryjoin="Taxonomy.id==Objects.classif_id",
foreign_keys="Taxonomy.id",
uselist=False,
)
classif_qual = db.Column(CHAR(1))
classif_who = db.Column(db.Integer, db.ForeignKey('users.id'))
classiffier = db.relationship(
"users",
primaryjoin="users.id==Objects.classif_who",
foreign_keys="users.id",
uselist=False,
)
classif_when = db.Column(TIMESTAMP)
classif_auto_id = db.Column(INTEGER)
classif_auto_score = db.Column(DOUBLE_PRECISION)
classif_auto_when = db.Column(TIMESTAMP)
classif_auto = db.relationship(
"Taxonomy",
primaryjoin="Taxonomy.id==foreign(Objects.classif_auto_id)",
uselist=False,
)
classif_crossvalidation_id = db.Column(INTEGER)
img0id = db.Column(BIGINT)
img0 = db.relationship("Images", foreign_keys="Images.objid")
imgcount = db.Column(INTEGER)
complement_info = db.Column(VARCHAR)
similarity = db.Column(DOUBLE_PRECISION)
sunpos = db.Column(CHAR(1)) # position du soleil
random_value = db.Column(INTEGER)
sampleid = db.Column(INTEGER, db.ForeignKey('samples.sampleid'))
sample = db.relationship("Samples")
acquisid = db.Column(INTEGER, db.ForeignKey('acquisitions.acquisid'))
acquis = db.relationship("Acquisitions")
processid = db.Column(INTEGER, db.ForeignKey('process.processid'))
processrel = db.relationship("Process")
class ObjectsClassifHisto(Model):
__tablename__ = 'objectsclassifhisto'
objid = Column(BIGINT,
ForeignKey('obj_head.objid', ondelete="CASCADE"),
primary_key=True)
# TODO: FK on taxonomy
classif_date = Column(TIMESTAMP, primary_key=True)
classif_type = Column(CHAR(1)) # A : Automatic, M : Manual
classif_id = Column(INTEGER)
classif_qual = Column(CHAR(1))
classif_who = Column(Integer, ForeignKey('users.id'))
classif_score = Column(DOUBLE_PRECISION)
# The relationships are created in Relations.py but the typing here helps the IDE
object: relationship
class Provider(PcObject, Model, DeactivableMixin):
id = Column(BigInteger, primary_key=True)
name = Column(String(90), index=True, nullable=False)
localClass = Column(
String(60),
CheckConstraint(
'("localClass" IS NOT NULL AND "apiKey" IS NULL)' + 'OR ("localClass" IS NULL AND "apiKey" IS NOT NULL)',
name="check_provider_has_localclass_or_apikey",
),
nullable=True,
unique=True,
)
apiKey = Column(CHAR(32), nullable=True)
apiKeyGenerationDate = Column(DateTime, nullable=True)
enabledForPro = Column(Boolean, nullable=False, default=False)
requireProviderIdentifier = Column(Boolean, nullable=False, default=True)
@property
def isAllocine(self) -> bool:
from pcapi import local_providers # avoid import loop
return self.localClass == local_providers.AllocineStocks.__name__
class Provider(app.model.PcObject, app.model.DeactivableMixin, db.Model):
id = db.Column(db.BigInteger, primary_key=True)
name = db.Column(db.String(60), nullable=False)
localClass = db.Column(
db.String(30),
db.CheckConstraint(
'("localClass" IS NOT NULL AND "apiKey" IS NULL)' +
'OR ("localClass" IS NULL AND "apiKey" IS NOT NULL)',
name='check_provider_has_localclass_or_apikey'),
nullable=True,
unique=True)
venueProviders = db.relationship(
app.model.VenueProvider,
back_populates="provider",
foreign_keys=[app.model.VenueProvider.providerId])
apiKey = db.Column(CHAR(32), nullable=True)
apiKeyGenerationDate = db.Column(db.DateTime, nullable=True)
def getByClassName(name):
return Provider.query\
.filter_by(localClass=name)\
.first()
class Seats(db.Model):
__tablename__ = 'seats'
__table_args__ = (db.PrimaryKeyConstraint('aircraft_code',
'seat_no',
name='boarding_passes_pkey'),
db.ForeignKeyConstraint(
['aircraft_code'], ['aircrafts_data.aircraft_code'],
name='boarding_passes_ticket_no_fkey'))
aircraft_code = db.Column(CHAR(3), nullable=False)
seat_no = db.Column(CHAR(4), nullable=False)
fare_conditions = db.Column(CHAR(10), nullable=False)
def __init__(self, aircraft_code, seat_no, fare_conditions):
self.aircraft_code = aircraft_code
self.seat_no = seat_no
self.fare_conditions = fare_conditions
class ArchiveFile(Base):
'''
A file in the archive.
'''
__tablename__ = 'archive_files'
id = Column(Integer, primary_key=True)
fname = Column(Text())
size = Column(Integer()) # the size of the file
sha256sum = Column(CHAR(64)) # the files' checksum
srcpkg_id = Column(UUID(as_uuid=True),
ForeignKey('archive_src_packages.uuid'))
binpkg_id = Column(UUID(as_uuid=True),
ForeignKey('archive_bin_packages.uuid'),
unique=True,
nullable=True)
binpkg = relationship('BinaryPackage', back_populates='bin_file')
srcpkg = relationship('SourcePackage', back_populates='files')
def make_url(self, urlbase):
if urlbase[-1] == '/':
return urlbase + str(self.fname)
else:
return urlbase + '/' + str(self.fname)
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 Tickets(db.Model):
__tablename__ = 'tickets'
ticket_no = db.Column(CHAR(13), nullable=False, primary_key=True)
book_ref = db.Column(CHAR(6),
db.ForeignKey('bookings.book_ref'),
nullable=False)
passenger_id = db.Column(CHAR(20), nullable=False)
passenger_name = db.Column(TEXT, nullable=False)
contact_data = db.Column(JSONB, nullable=True)
def __init__(self, ticket_no, book_ref, passenger_id, passenger_name,
contact_data):
self.ticket_no = ticket_no
self.book_ref = book_ref
self.passenger_id = passenger_id
self.passenger_name = passenger_name
self.contact_data = contact_data
class Ticket_flights(db.Model):
__tablename__ = 'ticket_flights'
ticket_no = db.Column(CHAR(13),
db.ForeignKey('tickets.ticket_no'),
nullable=False,
primary_key=True)
flight_id = db.Column(INTEGER,
db.ForeignKey('flights.flight_id'),
nullable=False,
primary_key=True)
fare_conditions = db.Column(CHAR(10), nullable=False)
amount = db.Column(NUMERIC(scale=10, precision=2), nullable=False)
def __init__(self, ticket_no, flight_id, fare_conditions, amount):
self.ticket_no = ticket_no
self.flight_id = flight_id
self.fare_conditions = fare_conditions
self.amount = amount
class Bookings(db.Model):
__tablename__ = 'bookings'
book_ref = db.Column(CHAR(6), nullable=False, primary_key=True)
book_date = db.Column(TIMESTAMP, nullable=False)
total_amount = db.Column(NUMERIC(scale=10, precision=2), nullable=False)
def __init__(self, book_ref, book_date, total_amount):
self.book_ref = book_ref
self.book_date = book_date
self.total_amount = total_amount
class Aircrafts_data(db.Model):
__tablename__ = 'aircrafts_data'
aircraft_code = db.Column(CHAR(3), primary_key=True)
model = db.Column(JSONB, nullable=False)
rrange = db.Column(INTEGER, nullable=False)
def __init__(self, aircraft_code, model, rrange):
self.aircraft_code = aircraft_code
self.model = model
self.rrange = rrange
class Flights(db.Model):
__tablename__ = 'flights'
__table_args__ = (db.UniqueConstraint(
'flight_no',
'scheduled_departure',
name='flights_flight_no_scheduled_departure_key'), )
flight_id = db.Column(INTEGER, nullable=False, primary_key=True)
flight_no = db.Column(CHAR(6), nullable=False)
scheduled_departure = db.Column(TIMESTAMP, nullable=False)
scheduled_arrival = db.Column(TIMESTAMP, nullable=False)
departure_airport = db.Column(CHAR(3),
db.ForeignKey('airports_data.airport_code'),
nullable=False)
arrival_airport = db.Column(CHAR(3),
db.ForeignKey('airports_data.airport_code'),
nullable=False)
status = db.Column(CHAR(20), nullable=False)
aircraft_code = db.Column(CHAR(3),
db.ForeignKey('aircrafts_data.aircraft_code'),
nullable=False)
actual_departure = db.Column(TIMESTAMP, nullable=True)
actual_arrival = db.Column(TIMESTAMP, nullable=True)
def __init__(self, flight_id, flight_no, scheduled_departure,
scheduled_arrival, departure_airport, arrival_airport, status,
aircraft_code, actual_departure, actual_arrival):
self.flight_id = flight_id
self.flight_no = flight_no
self.scheduled_departure = scheduled_departure
self.scheduled_arrival = scheduled_arrival
self.departure_airport = departure_airport
self.arrival_airport = arrival_airport
self.status = status
self.aircraft_code = aircraft_code
self.actual_departure = actual_departure
self.actual_arrival = actual_arrival
class Airports_data(db.Model):
__tablename__ = 'airports_data'
airport_code = db.Column(CHAR(3), nullable=False, primary_key=True)
airport_name = db.Column(JSONB, nullable=False)
city = db.Column(JSONB, nullable=False)
coordinates = db.Column(Point, nullable=False)
timezone = db.Column(TEXT, nullable=False)
def __init__(self, airport_code, airport_name, city, coordinates,
timezone):
self.airport_code = airport_code
self.airport_name = airport_name
self.city = city
self.coordinates = coordinates
self.timezone = timezone
def ingest_data(password: str,
data: pd.DataFrame,
table_name: str = 'order') -> None:
''' Ingests the retrieved data into the database. '''
with sql_engine(password).connect() as con:
# Creating a service table to store temporarily the data in order to perform additional checks.
data.to_sql(f'{table_name}_to_ingest',
con=con,
schema='dna_test',
if_exists='replace',
index=False,
dtype={
'order_id': BIGINT,
'order_date': TIMESTAMP,
'user_id': INTEGER,
'zipcode': INTEGER,
'total': DOUBLE_PRECISION,
'item_count': INTEGER,
'lat': DOUBLE_PRECISION,
'lng': DOUBLE_PRECISION,
'city': TEXT,
'state': TEXT,
'timezone_identifier': TEXT,
'timezone_abbr': CHAR(3),
'utc_offset_sec': INTEGER,
'is_dst': BOOLEAN,
'area_codes': TEXT
})
# Ingesting the data into the proper table avoiding to store records we already have.
# We assume here that the order_id is unique.
# This version is the minimum we should do to store data avoiding repetitions, but we can better
# use the field timerange_transaction to create a proper "point in time" table.
con.execute(f'''insert into dna_test.{table_name}
(order_id, order_date, user_id, zipcode, total, item_count,
lat, lng, city, state, timezone_identifier, timezone_abbr,
utc_offset_sec, is_dst, area_codes)
select *
from dna_test.{table_name}_to_ingest bb
where not exists (
select *
from dna_test.{table_name} cc
where cc.order_id = bb.order_id
)
''')
class Provider(PcObject, Model, DeactivableMixin):
id = Column(BigInteger, primary_key=True)
name = Column(String(90), index=True, nullable=False)
localClass = Column(
String(60),
CheckConstraint(
'("localClass" IS NOT NULL AND "apiKey" IS NULL)' +
'OR ("localClass" IS NULL AND "apiKey" IS NOT NULL)',
name="check_provider_has_localclass_or_apikey",
),
nullable=True,
unique=True,
)
apiKey = Column(CHAR(32), nullable=True)
apiKeyGenerationDate = Column(DateTime, nullable=True)
enabledForPro = Column(Boolean, nullable=False, default=False)
requireProviderIdentifier = Column(Boolean, nullable=False, default=True)
class Provider(PcObject, Model, DeactivableMixin):
id = Column(BigInteger, primary_key=True)
name = Column(String(90), nullable=False)
localClass = Column(
String(60),
CheckConstraint('("localClass" IS NOT NULL AND "apiKey" IS NULL)' +
'OR ("localClass" IS NULL AND "apiKey" IS NOT NULL)',
name='check_provider_has_localclass_or_apikey'),
nullable=True,
unique=True)
venueProviders = relationship(VenueProvider,
back_populates="provider",
foreign_keys=[VenueProvider.providerId])
apiKey = Column(CHAR(32), nullable=True)
apiKeyGenerationDate = Column(DateTime, nullable=True)
enabledForPro = Column(Boolean, nullable=False, default=False)
requireProviderIdentifier = Column(Boolean, nullable=False, default=True)
def load_dialect_impl(self, dialect):
if dialect.name == 'postgresql':
return dialect.type_descriptor(UUID())
else:
return dialect.type_descriptor(CHAR(32))
class BinaryPackage(Base):
'''
Data of a binary package.
'''
__tablename__ = 'archive_bin_packages'
uuid = Column(UUID(as_uuid=True),
primary_key=True,
default=None,
nullable=False)
deb_type = Column(Enum(DebType)) # Deb package type
name = Column(String(256)) # Package name
version = Column(DebVersion()) # Version of this package
repo_id = Column(Integer, ForeignKey('archive_repositories.id'))
repo = relationship('ArchiveRepository')
suites = relationship(
'ArchiveSuite',
secondary=binpkg_suite_assoc_table,
back_populates='bin_packages') # Suites this package is in
component_id = Column(Integer, ForeignKey('archive_components.id'))
component = relationship(
'ArchiveComponent') # Component this package is in
architecture_id = Column(Integer, ForeignKey('archive_architectures.id'))
architecture = relationship(
'ArchiveArchitecture') # Architecture this binary was built for
size_installed = Column(Integer()) # Size of the installed package
description = Column(Text())
description_md5 = Column(CHAR(32))
source_name = Column(String(256))
source_version = Column(DebVersion())
priority = Column(Enum(PackagePriority))
section = Column(String(64))
depends = Column(ARRAY(Text()))
pre_depends = Column(ARRAY(Text()))
maintainer = Column(Text())
homepage = Column(Text())
bin_file = relationship('ArchiveFile',
uselist=False,
back_populates='binpkg',
cascade='all, delete, delete-orphan')
sw_cpts = relationship('SoftwareComponent',
secondary=swcpt_binpkg_assoc_table,
back_populates='bin_packages')
__ts_vector__ = create_tsvector(cast(func.coalesce(name, ''), TEXT),
cast(func.coalesce(description, ''), TEXT),
cast(func.coalesce(source_name, ''), TEXT))
__table_args__ = (Index('idx_bin_package_fts',
__ts_vector__,
postgresql_using='gin'), )
@staticmethod
def generate_uuid(repo_name, name, version, arch_name):
return uuid.uuid5(
UUID_NS_BINPACKAGE, '{}::{}/{}/{}'.format(repo_name, name, version,
arch_name))
def update_uuid(self):
if not self.repo:
raise Exception(
'Binary package is not associated with a repository!')
self.uuid = BinaryPackage.generate_uuid(self.repo.name, self.name,
self.version,
self.architecture.name)
return self.uuid
def __str__(self):
repo_name = '?'
if self.repo:
repo_name = self.repo.name
arch_name = 'unknown'
if self.architecture:
arch_name = self.architecture.name
return '{}::{}/{}/{}'.format(repo_name, self.name, self.version,
arch_name)
class ObjectHeader(Model):
__tablename__ = 'obj_head'
# Self
objid = Column(BIGINT, Sequence('seq_objects'), primary_key=True)
# Parent
acquisid = Column(INTEGER,
ForeignKey('acquisitions.acquisid'),
nullable=False)
# User-provided identifier
orig_id = Column(VARCHAR(255), nullable=False)
objdate = Column(DATE)
objtime = Column(TIME)
latitude = Column(DOUBLE_PRECISION)
longitude = Column(DOUBLE_PRECISION)
depth_min = Column(FLOAT)
depth_max = Column(FLOAT)
#
sunpos = Column(CHAR(1)) # Sun position, from date, time and coords
#
classif_id = Column(INTEGER)
classif_qual = Column(CHAR(1))
classif_who = Column(Integer, ForeignKey('users.id'))
classif_when = Column(TIMESTAMP)
classif_auto_id = Column(INTEGER)
classif_auto_score = Column(DOUBLE_PRECISION)
classif_auto_when = Column(TIMESTAMP)
classif_crossvalidation_id = Column(INTEGER) # Always NULL in prod'
complement_info = Column(VARCHAR) # e.g. "Part of ostracoda"
similarity = Column(DOUBLE_PRECISION) # Always NULL in prod'
# TODO: Why random? It makes testing a bit more difficult
random_value = Column(INTEGER)
# TODO: Can't see any value in DB
object_link = Column(VARCHAR(255))
# The relationships are created in Relations.py but the typing here helps the IDE
fields: relationship
cnn_features: relationship
classif: relationship
classif_auto: relationship
classifier: relationship
all_images: relationship
acquisition: relationship
history: relationship
@classmethod
def fetch_existing_objects(cls, session: Session,
prj_id) -> Dict[str, int]:
# TODO: Why using the view?
sql = text("SELECT o.orig_id, o.objid "
" FROM objects o "
" WHERE o.projid = :prj")
res: Result = session.execute(sql, {"prj": prj_id})
ret = {orig_id: objid for orig_id, objid in res}
return ret # type: ignore
@classmethod
def fetch_existing_ranks(cls, session: Session,
prj_id) -> Dict[int, Set[int]]:
ret: Dict[int, Set[int]] = {}
qry: Query = session.query(Image.objid, Image.imgrank)
qry = qry.join(ObjectHeader).join(Acquisition).join(Sample).join(
Project)
qry = qry.filter(Project.projid == prj_id)
for objid, imgrank in qry.all():
ret.setdefault(objid, set()).add(imgrank)
return ret
@staticmethod
def _geo_from_txt(txt: str, min_v: float, max_v: float) -> float:
""" Convert/check latitude or longitude before setting field
:raises ValueError """
ret = convert_degree_minute_float_to_decimal_degree(txt)
if ret is None:
raise ValueError
if ret < min_v or ret > max_v:
raise ValueError
return ret
@staticmethod
def latitude_from_txt(txt: str) -> float:
return ObjectHeader._geo_from_txt(txt, -90, 90)
@staticmethod
def longitude_from_txt(txt: str) -> float:
return ObjectHeader._geo_from_txt(txt, -180, 180)
@staticmethod
def depth_from_txt(txt: str) -> float:
""" Convert depth before setting field
:raises ValueError """
return float(txt)
@staticmethod
def time_from_txt(txt: str) -> datetime.time:
""" Convert/check time before setting field. HHMM with optional SS. Or strictly HH:MM:SS
:raises ValueError """
if txt[2:3] == txt[5:6] == ":":
return datetime.time(int(txt[0:2]), int(txt[3:5]), int(txt[6:8]))
# Left pad with 0s as they tend to be truncated by spreadsheets e.g. 320 -> 0320
txt = '0' * (4 - len(txt)) + txt if len(txt) < 4 else txt
# Right pad with 0s for seconds e.g. 0320 -> 032000
txt += '0' * (6 - len(txt)) if len(txt) < 6 else ""
return datetime.time(int(txt[0:2]), int(txt[2:4]), int(txt[4:6]))
@staticmethod
def date_from_txt(txt: str) -> datetime.date:
""" Convert/check date before setting field. Format YYYYMMDD or YYYY-MM-DD
:raises ValueError """
if txt[4:5] == txt[7:8] == "-":
return datetime.date(int(txt[0:4]), int(txt[5:7]), int(txt[8:10]))
return datetime.date(int(txt[0:4]), int(txt[4:6]), int(txt[6:8]))
def __lt__(self, other):
return self.objid < other.objid
def test_has_non_sql_attributes(self):
expected = [('length', 3)]
result = list(get_type_attributes(CHAR(3)))
self.assertEqual(expected, result)
engine = create_engine('postgresql://django@localhost:5432/sqla')
# create MetaData
meta = MetaData()
meta.bind = engine
accounts = Table(
'accounts',
meta,
# (Column args)[http://ur1.ca/9lg3r]
Column('account_id', BIGINT, primary_key=True),
Column('account_name', VARCHAR(20)),
Column('first_name', VARCHAR(20)),
Column('last_name', VARCHAR(20)),
Column('email', VARCHAR(100)),
Column('password_hash', CHAR(64)),
Column('potrait_image', BYTEA),
Column('hourly_rate', NUMERIC(9, 2)),
)
bugstatus = Table('bugstatus', meta,
Column('status', VARCHAR(20), primary_key=True))
bugs = Table(
'bugs',
meta,
Column('bug_id', BIGINT, primary_key=True),
Column('date_reported', DATE, nullable=False),
Column('summary', VARCHAR(80)),
Column('description', VARCHAR(1000)),
Column('resolution', VARCHAR(1000)),