class TOccurrencesOccurrence(DB.Model): __tablename__ = "t_occurrences_occtax" __table_args__ = {"schema": "pr_occtax"} id_occurrence_occtax = DB.Column(DB.Integer, primary_key=True) id_releve_occtax = DB.Column( DB.Integer, ForeignKey("pr_occtax.t_releves_occtax.id_releve_occtax")) id_nomenclature_obs_technique = DB.Column(DB.Integer) id_nomenclature_bio_condition = DB.Column(DB.Integer) id_nomenclature_bio_status = DB.Column(DB.Integer) id_nomenclature_naturalness = DB.Column(DB.Integer) id_nomenclature_exist_proof = DB.Column(DB.Integer) id_nomenclature_observation_status = DB.Column(DB.Integer) id_nomenclature_blurring = DB.Column(DB.Integer) id_nomenclature_source_status = DB.Column(DB.Integer) determiner = DB.Column(DB.Unicode) id_nomenclature_determination_method = DB.Column(DB.Integer) id_nomenclature_behaviour = DB.Column(DB.Integer) cd_nom = DB.Column(DB.Integer, ForeignKey(Taxref.cd_nom)) nom_cite = DB.Column(DB.Unicode) meta_v_taxref = DB.Column( DB.Unicode, default=select( [func.gn_commons.get_default_parameter("taxref_version")]), ) sample_number_proof = DB.Column(DB.Unicode) digital_proof = DB.Column(DB.Unicode) non_digital_proof = DB.Column(DB.Unicode) comment = DB.Column(DB.Unicode) #additional fields dans occtax MET 28/09/2020 additional_fields = DB.Column(JSONB) unique_id_occurence_occtax = DB.Column( UUID(as_uuid=True), default=select([func.uuid_generate_v4()]), ) cor_counting_occtax = relationship( "CorCountingOccurrence", lazy="joined", cascade="all,delete-orphan", uselist=True, backref=DB.backref("occurence", lazy="joined"), ) taxref = relationship(Taxref, lazy="joined") readonly_fields = ["id_occurrence_occtax", "id_releve_occtax", "taxref"]
class Export(DB.Model): __tablename__ = 't_exports_logs' __table_args__ = {'schema': 'gn_intero', 'extend_existing': True} id = DB.Column(DB.TIMESTAMP(timezone=False), primary_key=True, nullable=False) standard = DB.Column(DB.Numeric, default=0) format = DB.Column(DB.Integer, nullable=False) status = DB.Column(DB.Numeric, default=-2) log = DB.Column(DB.UnicodeText) start = DB.Column(DB.DateTime) end = DB.Column(DB.DateTime) id_export = DB.Column(DB.Integer(), DB.ForeignKey('gn_intero.t_exports.id')) export = DB.relationship('ExportType', foreign_keys='Export.id_export', backref=DB.backref('ExportType', lazy='dynamic')) def __init__(self, standard, format, selection=None): self.standard = standard self.id = datetime.utcnow() self.format = int(format) if selection: self.selection = selection def __repr__(self): return "<Export(id='{}', selection='{}', date='{}', standard='{}', format='{}')>".format( # noqa E501 float(self.id), self.export.selection, self.start, self.standard, self.format) # noqa E501 def as_dict(self): return { 'id': float(self.ts()), 'extension': format_map_ext[self.format], 'selection': self.export.selection, 'label': self.export.label, 'standard': standard_map_label[self.standard], 'date': self.start, } def ts(self): return (datetime.strptime(str(self.id), '%Y-%m-%d %H:%M:%S.%f') - datetime.utcfromtimestamp(0)).total_seconds()
class TAcquisitionFramework(CruvedHelper): __tablename__ = "t_acquisition_frameworks" __table_args__ = {"schema": "gn_meta"} id_acquisition_framework = DB.Column(DB.Integer, primary_key=True) unique_acquisition_framework_id = DB.Column( UUID(as_uuid=True), default=select([func.uuid_generate_v4()]) ) acquisition_framework_name = DB.Column(DB.Unicode) acquisition_framework_desc = DB.Column(DB.Unicode) id_nomenclature_territorial_level = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("NIVEAU_TERRITORIAL"), ) territory_desc = DB.Column(DB.Unicode) keywords = DB.Column(DB.Unicode) id_nomenclature_financing_type = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("TYPE_FINANCEMENT"), ) target_description = DB.Column(DB.Unicode) ecologic_or_geologic_target = DB.Column(DB.Unicode) acquisition_framework_parent_id = DB.Column(DB.Integer) is_parent = DB.Column(DB.Boolean) opened = DB.Column(DB.Boolean, default=True) id_digitizer = DB.Column(DB.Integer, ForeignKey("utilisateurs.t_roles.id_role")) acquisition_framework_start_date = DB.Column(DB.Date) acquisition_framework_end_date = DB.Column(DB.Date) meta_create_date = DB.Column(DB.DateTime) meta_update_date = DB.Column(DB.DateTime) initial_closing_date = DB.Column(DB.DateTime) creator = DB.relationship("User", lazy="joined") nomenclature_territorial_level = DB.relationship( TNomenclatures, lazy="select", primaryjoin=(TNomenclatures.id_nomenclature == id_nomenclature_territorial_level), ) nomenclature_financing_type = DB.relationship( TNomenclatures, lazy="select", primaryjoin=(TNomenclatures.id_nomenclature == id_nomenclature_financing_type), ) cor_af_actor = relationship( CorAcquisitionFrameworkActor, lazy="joined", #cascade="save-update, merge, delete, delete-orphan", cascade="all,delete-orphan", uselist=True, backref=DB.backref("actor_af", lazy="select") ) cor_objectifs = DB.relationship( TNomenclatures, lazy="select", secondary=CorAcquisitionFrameworkObjectif.__table__, primaryjoin=( CorAcquisitionFrameworkObjectif.id_acquisition_framework == id_acquisition_framework ), secondaryjoin=( CorAcquisitionFrameworkObjectif.id_nomenclature_objectif == TNomenclatures.id_nomenclature ), foreign_keys=[ CorAcquisitionFrameworkObjectif.id_acquisition_framework, CorAcquisitionFrameworkObjectif.id_nomenclature_objectif, ], backref=DB.backref("objectif_af", lazy="select") ) cor_volets_sinp = DB.relationship( TNomenclatures, lazy="select", secondary=CorAcquisitionFrameworkVoletSINP.__table__, primaryjoin=(CorAcquisitionFrameworkVoletSINP.id_acquisition_framework == id_acquisition_framework), secondaryjoin=(CorAcquisitionFrameworkVoletSINP.id_nomenclature_voletsinp == TNomenclatures.id_nomenclature), foreign_keys=[ CorAcquisitionFrameworkVoletSINP.id_acquisition_framework, CorAcquisitionFrameworkVoletSINP.id_nomenclature_voletsinp, ], backref=DB.backref("volet_sinp_af", lazy="select") ) cor_territories = DB.relationship( TNomenclatures, lazy="select", secondary=CorAcquisitionFrameworkTerritory.__table__, primaryjoin=(CorAcquisitionFrameworkTerritory.id_acquisition_framework == id_acquisition_framework), secondaryjoin=(CorAcquisitionFrameworkTerritory.id_nomenclature_territory == TNomenclatures.id_nomenclature), foreign_keys=[ CorAcquisitionFrameworkTerritory.id_acquisition_framework, CorAcquisitionFrameworkTerritory.id_nomenclature_territory, ], backref=DB.backref("territory_af", lazy="select") ) bibliographical_references = DB.relationship( "TBibliographicReference", lazy="select", cascade="all,delete-orphan", uselist=True, backref=DB.backref("acquisition_framework", lazy="select"), ) t_datasets = DB.relationship( "TDatasets", lazy="select", cascade="all,delete-orphan", uselist=True, backref=DB.backref("acquisition_framework", lazy="select"), ) def get_object_cruved( self, info_role, user_cruved ): """ Return the user's cruved for a Model instance. Use in the map-list interface to allow or not an action params: - user_cruved: object retourner by cruved_for_user_in_app(user) {'C': '2', 'R':'3' etc...} - id_object (int): id de l'objet sur lqurqul on veut vérifier le CRUVED (self.id_dataset/ self.id_ca) - id_role: identifiant de la personne qui demande la route - id_object_users_actor (list): identifiant des objects ou l'utilisateur est lui même acteur - id_object_organism_actor (list): identifiants des objects ou l'utilisateur ou son organisme sont acteurs Return: dict {'C': True, 'R': False ...} """ return { action: self.user_is_allowed_to(self.cor_af_actor, info_role, level) for action, level in user_cruved.items() } @staticmethod def get_id(uuid_af): """ return the acquisition framework's id from its UUID if exist or None """ a_f = ( DB.session.query(TAcquisitionFramework.id_acquisition_framework) .filter(TAcquisitionFramework.unique_acquisition_framework_id == uuid_af) .first() ) if a_f: return a_f[0] return a_f @staticmethod def get_user_af(user, only_query=False, only_user=False): """get the af(s) where the user is actor (himself or with its organism - only himelsemf id only_use=True) or digitizer param: - user from TRole model - only_query: boolean (return the query not the id_datasets allowed if true) - only_user: boolean: return only the dataset where user himself is actor (not with its organoism) return: a list of id_dataset or a query""" q = DB.session.query(TAcquisitionFramework.id_acquisition_framework).outerjoin( CorAcquisitionFrameworkActor, CorAcquisitionFrameworkActor.id_acquisition_framework == TAcquisitionFramework.id_acquisition_framework, ) if user.id_organisme is None or only_user: q = q.filter( or_( CorAcquisitionFrameworkActor.id_role == user.id_role, TAcquisitionFramework.id_digitizer == user.id_role, ) ) else: q = q.filter( or_( CorAcquisitionFrameworkActor.id_organism == user.id_organisme, CorAcquisitionFrameworkActor.id_role == user.id_role, TAcquisitionFramework.id_digitizer == user.id_role, ) ) if only_query: return q data = q.all() return list(set([d.id_acquisition_framework for d in data]))
class TDatasets(CruvedHelper): __tablename__ = "t_datasets" __table_args__ = {"schema": "gn_meta"} id_dataset = DB.Column(DB.Integer, primary_key=True) unique_dataset_id = DB.Column(UUID(as_uuid=True), default=select([func.uuid_generate_v4()])) id_acquisition_framework = DB.Column( DB.Integer, ForeignKey("gn_meta.t_acquisition_frameworks.id_acquisition_framework"), ) dataset_name = DB.Column(DB.Unicode) dataset_shortname = DB.Column(DB.Unicode) dataset_desc = DB.Column(DB.Unicode) id_nomenclature_data_type = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("DATA_TYP"), ) keywords = DB.Column(DB.Unicode) marine_domain = DB.Column(DB.Boolean) terrestrial_domain = DB.Column(DB.Boolean) id_nomenclature_dataset_objectif = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("JDD_OBJECTIFS"), ) bbox_west = DB.Column(DB.Float) bbox_east = DB.Column(DB.Float) bbox_south = DB.Column(DB.Float) bbox_north = DB.Column(DB.Float) id_nomenclature_collecting_method = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("METHO_RECUEIL"), ) id_nomenclature_data_origin = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("DS_PUBLIQUE"), ) id_nomenclature_source_status = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("STATUT_SOURCE"), ) id_nomenclature_resource_type = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("RESOURCE_TYP"), ) meta_create_date = DB.Column(DB.DateTime) meta_update_date = DB.Column(DB.DateTime) active = DB.Column(DB.Boolean, default=True) validable = DB.Column(DB.Boolean) id_digitizer = DB.Column(DB.Integer, ForeignKey("utilisateurs.t_roles.id_role")) id_taxa_list = DB.Column(DB.Integer) modules = DB.relationship("TModules", secondary=cor_module_dataset, lazy="select") creator = DB.relationship("User", lazy="joined") nomenclature_data_type = DB.relationship( TNomenclatures, lazy="select", primaryjoin=(TNomenclatures.id_nomenclature == id_nomenclature_data_type), ) nomenclature_dataset_objectif = DB.relationship( TNomenclatures, lazy="select", primaryjoin=(TNomenclatures.id_nomenclature == id_nomenclature_dataset_objectif), ) nomenclature_collecting_method = DB.relationship( TNomenclatures, lazy="select", primaryjoin=( TNomenclatures.id_nomenclature == id_nomenclature_collecting_method ), ) nomenclature_data_origin = DB.relationship( TNomenclatures, lazy="select", primaryjoin=(TNomenclatures.id_nomenclature == id_nomenclature_data_origin), ) nomenclature_source_status = DB.relationship( TNomenclatures, lazy="select", primaryjoin=(TNomenclatures.id_nomenclature == id_nomenclature_source_status), ) nomenclature_resource_type = DB.relationship( TNomenclatures, lazy="select", primaryjoin=(TNomenclatures.id_nomenclature == id_nomenclature_resource_type), ) cor_territories = DB.relationship( TNomenclatures, lazy="select", secondary=CorDatasetTerritory.__table__, primaryjoin=(CorDatasetTerritory.id_dataset == id_dataset), secondaryjoin=(CorDatasetTerritory.id_nomenclature_territory == TNomenclatures.id_nomenclature), foreign_keys=[ CorDatasetTerritory.id_dataset, CorDatasetTerritory.id_nomenclature_territory, ], backref=DB.backref("territory_dataset", lazy="select") ) # because CorDatasetActor could be an User or an Organisme object... cor_dataset_actor = relationship( CorDatasetActor, lazy="joined", cascade="save-update, merge, delete, delete-orphan", backref=DB.backref("actor_dataset", lazy="select") ) def __str__(self): return self.dataset_name def get_object_cruved( self, info_role, user_cruved ): """ Return the user's cruved for a Model instance. Use in the map-list interface to allow or not an action params: - user_cruved: object retourner by cruved_for_user_in_app(user) {'C': '2', 'R':'3' etc...} - id_object (int): id de l'objet sur lqurqul on veut vérifier le CRUVED (self.id_dataset/ self.id_ca) - id_role: identifiant de la personne qui demande la route - id_object_users_actor (list): identifiant des objects ou l'utilisateur est lui même acteur - id_object_organism_actor (list): identifiants des objects ou l'utilisateur ou son organisme sont acteurs Return: dict {'C': True, 'R': False ...} """ return { action: self.user_is_allowed_to(self.cor_dataset_actor, info_role, level) for action, level in user_cruved.items() } @staticmethod def get_id(uuid_dataset): id_dataset = ( DB.session.query(TDatasets.id_dataset) .filter(TDatasets.unique_dataset_id == uuid_dataset) .first() ) if id_dataset: return id_dataset[0] return id_dataset @staticmethod def get_uuid(id_dataset): uuid_dataset = ( DB.session.query(TDatasets.unique_dataset_id) .filter(TDatasets.id_dataset == id_dataset) .first() ) if uuid_dataset: return uuid_dataset[0] return uuid_dataset @staticmethod def get_user_datasets(user, only_query=False, only_user=False): """get the dataset(s) where the user is actor (himself or with its organism - only himelsemf id only_use=True) or digitizer param: - user from TRole model - only_query: boolean (return the query not the id_datasets allowed if true) - only_user: boolean: return only the dataset where user himself is actor (not with its organism) return: a list of id_dataset or a query""" q = DB.session.query(TDatasets).outerjoin( CorDatasetActor, CorDatasetActor.id_dataset == TDatasets.id_dataset ) if user.id_organisme is None or only_user: q = q.filter( or_( CorDatasetActor.id_role == user.id_role, TDatasets.id_digitizer == user.id_role, ) ) else: q = q.filter( or_( CorDatasetActor.id_organism == user.id_organisme, CorDatasetActor.id_role == user.id_role, TDatasets.id_digitizer == user.id_role, ) ) if only_query: return q return list(set([d.id_dataset for d in q.all()]))
class Synthese(DB.Model): __tablename__ = "synthese" __table_args__ = {"schema": "gn_synthese"} query_class = SyntheseQuery nomenclature_fields = [ 'nomenclature_geo_object_nature', 'nomenclature_grp_typ', 'nomenclature_obs_technique', 'nomenclature_bio_status', 'nomenclature_bio_condition', 'nomenclature_naturalness', 'nomenclature_exist_proof', 'nomenclature_valid_status', 'nomenclature_diffusion_level', 'nomenclature_life_stage', 'nomenclature_sex', 'nomenclature_obj_count', 'nomenclature_type_count', 'nomenclature_sensitivity', 'nomenclature_observation_status', 'nomenclature_blurring', 'nomenclature_source_status', 'nomenclature_info_geo_type', 'nomenclature_behaviour', 'nomenclature_biogeo_status', 'nomenclature_determination_method', ] id_synthese = DB.Column(DB.Integer, primary_key=True) unique_id_sinp = DB.Column(UUID(as_uuid=True)) unique_id_sinp_grp = DB.Column(UUID(as_uuid=True)) id_source = DB.Column(DB.Integer, ForeignKey(TSources.id_source)) source = relationship(TSources) id_module = DB.Column(DB.Integer, ForeignKey(TModules.id_module)) module = DB.relationship(TModules) entity_source_pk_value = DB.Column(DB.Integer) # FIXME varchar in db! id_dataset = DB.Column(DB.Integer, ForeignKey(TDatasets.id_dataset)) dataset = DB.relationship(TDatasets, backref=DB.backref('synthese_records', lazy='dynamic')) grp_method = DB.Column(DB.Unicode(length=255)) id_nomenclature_geo_object_nature = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_geo_object_nature = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_geo_object_nature]) id_nomenclature_grp_typ = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_grp_typ = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_grp_typ]) id_nomenclature_obs_technique = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_obs_technique = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_obs_technique]) id_nomenclature_bio_status = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_bio_status = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_bio_status]) id_nomenclature_bio_condition = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_bio_condition = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_bio_condition]) id_nomenclature_naturalness = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_naturalness = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_naturalness]) id_nomenclature_exist_proof = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_exist_proof = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_exist_proof]) id_nomenclature_valid_status = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_valid_status = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_valid_status]) id_nomenclature_exist_proof = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_exist_proof = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_exist_proof]) id_nomenclature_diffusion_level = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_diffusion_level = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_diffusion_level]) id_nomenclature_life_stage = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_life_stage = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_life_stage]) id_nomenclature_sex = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_sex = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_sex]) id_nomenclature_obj_count = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_obj_count = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_obj_count]) id_nomenclature_type_count = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_type_count = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_type_count]) id_nomenclature_sensitivity = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_sensitivity = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_sensitivity]) id_nomenclature_observation_status = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_observation_status = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_observation_status]) id_nomenclature_blurring = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_blurring = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_blurring]) id_nomenclature_source_status = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_source_status = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_source_status]) id_nomenclature_info_geo_type = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_info_geo_type = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_info_geo_type]) id_nomenclature_behaviour = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_behaviour = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_behaviour]) id_nomenclature_biogeo_status = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_biogeo_status = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_biogeo_status]) id_nomenclature_determination_method = db.Column(db.Integer, ForeignKey(TNomenclatures.id_nomenclature)) nomenclature_determination_method = db.relationship(TNomenclatures, foreign_keys=[id_nomenclature_determination_method]) reference_biblio = DB.Column(DB.Unicode(length=5000)) count_min = DB.Column(DB.Integer) count_max = DB.Column(DB.Integer) cd_nom = DB.Column(DB.Integer, ForeignKey(Taxref.cd_nom)) taxref = relationship(Taxref) cd_hab = DB.Column(DB.Integer, ForeignKey(Habref.cd_hab)) habitat = relationship(Habref) nom_cite = DB.Column(DB.Unicode(length=1000), nullable=False) meta_v_taxref = DB.Column(DB.Unicode(length=50)) sample_number_proof = DB.Column(DB.UnicodeText) digital_proof = DB.Column(DB.UnicodeText) non_digital_proof = DB.Column(DB.UnicodeText) altitude_min = DB.Column(DB.Integer) altitude_max = DB.Column(DB.Integer) depth_min = DB.Column(DB.Integer) depth_max = DB.Column(DB.Integer) place_name = DB.Column(DB.Unicode(length=500)) the_geom_4326 = DB.Column(Geometry("GEOMETRY", 4326)) the_geom_point = DB.Column(Geometry("GEOMETRY", 4326)) the_geom_local = DB.Column(Geometry("GEOMETRY", config["LOCAL_SRID"])) precision = DB.Column(DB.Integer) id_area_attachment = DB.Column(DB.Integer) date_min = DB.Column(DB.DateTime, nullable=False) date_max = DB.Column(DB.DateTime, nullable=False) validator = DB.Column(DB.Unicode(length=1000)) validation_comment = DB.Column(DB.Unicode) observers = DB.Column(DB.Unicode(length=1000)) determiner = DB.Column(DB.Unicode(length=1000)) id_digitiser = DB.Column(DB.Integer, ForeignKey(User.id_role)) digitiser = db.relationship(User, foreign_keys=[id_digitiser]) comment_context = DB.Column(DB.UnicodeText) comment_description = DB.Column(DB.UnicodeText) additional_data = DB.Column(JSONB) meta_validation_date = DB.Column(DB.DateTime) meta_create_date = DB.Column(DB.DateTime) meta_update_date = DB.Column(DB.DateTime) last_action = DB.Column(DB.Unicode) areas = relationship('LAreas', secondary=corAreaSynthese) validations = relationship(TValidations, backref='attached_row') last_validation = relationship(last_validation, uselist=False, viewonly=True) medias = relationship( TMedias, primaryjoin=(TMedias.uuid_attached_row==foreign(unique_id_sinp)), uselist=True ) cor_observers = DB.relationship(User, secondary=cor_observer_synthese) def get_geofeature(self, recursif=True, fields=None): return self.as_geofeature("the_geom_4326", "id_synthese", recursif, fields=fields) def has_instance_permission(self, scope): if scope == 0: return False elif scope in (1, 2): if g.current_user == self.digitiser: return True if g.current_user in self.cor_observers: return True return self.dataset.has_instance_permission(scope) elif scope == 3: return True
class TAcquisitionFramework(CruvedMixin, FilterMixin, db.Model): __tablename__ = "t_acquisition_frameworks" __table_args__ = {"schema": "gn_meta"} query_class = TAcquisitionFrameworkQuery id_acquisition_framework = DB.Column(DB.Integer, primary_key=True) unique_acquisition_framework_id = DB.Column( UUIDType(as_uuid=True), default=select([func.uuid_generate_v4()]) ) acquisition_framework_name = DB.Column(DB.Unicode(255)) acquisition_framework_desc = DB.Column(DB.Unicode) id_nomenclature_territorial_level = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("NIVEAU_TERRITORIAL"), ) territory_desc = DB.Column(DB.Unicode) keywords = DB.Column(DB.Unicode) id_nomenclature_financing_type = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("TYPE_FINANCEMENT"), ) target_description = DB.Column(DB.Unicode) ecologic_or_geologic_target = DB.Column(DB.Unicode) acquisition_framework_parent_id = DB.Column(DB.Integer) is_parent = DB.Column(DB.Boolean) opened = DB.Column(DB.Boolean, default=True) id_digitizer = DB.Column(DB.Integer, ForeignKey(User.id_role)) acquisition_framework_start_date = DB.Column(DB.Date, default=datetime.datetime.utcnow) acquisition_framework_end_date = DB.Column(DB.Date) meta_create_date = DB.Column(DB.DateTime) meta_update_date = DB.Column(DB.DateTime) initial_closing_date = DB.Column(DB.DateTime) creator = DB.relationship(User, lazy="joined") # = digitizer nomenclature_territorial_level = DB.relationship( TNomenclatures, lazy="select", primaryjoin=(TNomenclatures.id_nomenclature == id_nomenclature_territorial_level), ) nomenclature_financing_type = DB.relationship( TNomenclatures, lazy="select", primaryjoin=(TNomenclatures.id_nomenclature == id_nomenclature_financing_type), ) cor_af_actor = relationship( CorAcquisitionFrameworkActor, lazy="joined", #cascade="save-update, merge, delete, delete-orphan", cascade="all,delete-orphan", uselist=True, backref=DB.backref("actor_af", lazy="select") ) cor_objectifs = DB.relationship( TNomenclatures, lazy="select", secondary=CorAcquisitionFrameworkObjectif.__table__, primaryjoin=( CorAcquisitionFrameworkObjectif.id_acquisition_framework == id_acquisition_framework ), secondaryjoin=( CorAcquisitionFrameworkObjectif.id_nomenclature_objectif == TNomenclatures.id_nomenclature ), foreign_keys=[ CorAcquisitionFrameworkObjectif.id_acquisition_framework, CorAcquisitionFrameworkObjectif.id_nomenclature_objectif, ], backref=DB.backref("objectif_af", lazy="select") ) cor_volets_sinp = DB.relationship( TNomenclatures, lazy="select", secondary=CorAcquisitionFrameworkVoletSINP.__table__, primaryjoin=(CorAcquisitionFrameworkVoletSINP.id_acquisition_framework == id_acquisition_framework), secondaryjoin=(CorAcquisitionFrameworkVoletSINP.id_nomenclature_voletsinp == TNomenclatures.id_nomenclature), foreign_keys=[ CorAcquisitionFrameworkVoletSINP.id_acquisition_framework, CorAcquisitionFrameworkVoletSINP.id_nomenclature_voletsinp, ], backref=DB.backref("volet_sinp_af", lazy="select") ) cor_territories = DB.relationship( TNomenclatures, lazy="select", secondary=CorAcquisitionFrameworkTerritory.__table__, primaryjoin=(CorAcquisitionFrameworkTerritory.id_acquisition_framework == id_acquisition_framework), secondaryjoin=(CorAcquisitionFrameworkTerritory.id_nomenclature_territory == TNomenclatures.id_nomenclature), foreign_keys=[ CorAcquisitionFrameworkTerritory.id_acquisition_framework, CorAcquisitionFrameworkTerritory.id_nomenclature_territory, ], backref=DB.backref("territory_af", lazy="select") ) bibliographical_references = DB.relationship( "TBibliographicReference", lazy="select", cascade="all,delete-orphan", uselist=True, backref=DB.backref("acquisition_framework", lazy="select"), ) t_datasets = DB.relationship( "TDatasets", lazy="joined", # DS required for permissions checks cascade="all,delete-orphan", uselist=True, ) datasets = synonym("t_datasets") @hybrid_property def user_actors(self): return [ actor.role for actor in self.cor_af_actor if actor.role is not None ] @hybrid_property def organism_actors(self): return [ actor.organism for actor in self.cor_af_actor if actor.organism is not None ] def is_deletable(self): return not db.session.query( TDatasets.query .filter_by(id_acquisition_framework=self.id_acquisition_framework) .exists() ).scalar() def has_instance_permission(self, scope, _through_ds=True): if scope == 0: return False elif scope in (1, 2): if g.current_user == self.creator or g.current_user in self.user_actors: return True if scope == 2 and g.current_user.organisme in self.organism_actors: return True # rights on DS give rights on AF! return _through_ds and any(map(lambda ds: ds.has_instance_permission(scope, _through_af=False), self.t_datasets)) elif scope == 3: return True @staticmethod def get_id(uuid_af): """ return the acquisition framework's id from its UUID if exist or None """ a_f = ( DB.session.query(TAcquisitionFramework.id_acquisition_framework) .filter(TAcquisitionFramework.unique_acquisition_framework_id == uuid_af) .first() ) if a_f: return a_f[0] return a_f @staticmethod def get_user_af(user, only_query=False, only_user=False): """get the af(s) where the user is actor (himself or with its organism - only himelsemf id only_use=True) or digitizer param: - user from TRole model - only_query: boolean (return the query not the id_datasets allowed if true) - only_user: boolean: return only the dataset where user himself is actor (not with its organoism) return: a list of id_dataset or a query""" q = DB.session.query(TAcquisitionFramework.id_acquisition_framework).outerjoin( CorAcquisitionFrameworkActor, CorAcquisitionFrameworkActor.id_acquisition_framework == TAcquisitionFramework.id_acquisition_framework, ) if user.id_organisme is None or only_user: q = q.filter( or_( CorAcquisitionFrameworkActor.id_role == user.id_role, TAcquisitionFramework.id_digitizer == user.id_role, ) ) else: q = q.filter( or_( CorAcquisitionFrameworkActor.id_organism == user.id_organisme, CorAcquisitionFrameworkActor.id_role == user.id_role, TAcquisitionFramework.id_digitizer == user.id_role, ) ) if only_query: return q data = q.all() return list(set([d.id_acquisition_framework for d in data])) @classmethod def compute_filter(cls, **kwargs): f = super().compute_filter(**kwargs) uuid = kwargs.get('uuid') if uuid is not None: try: uuid = UUID(uuid.strip()) except TypeError: pass else: f &= TAcquisitionFramework.unique_acquisition_framework_id == uuid name = kwargs.get('name') if name is not None: f &= TAcquisitionFramework.acquisition_framework_name.ilike(f'%{name}%') return f
class TDatasets(CruvedMixin, FilterMixin, db.Model): __tablename__ = "t_datasets" __table_args__ = {"schema": "gn_meta"} query_class = TDatasetsQuery id_dataset = DB.Column(DB.Integer, primary_key=True) unique_dataset_id = DB.Column(UUIDType(as_uuid=True), default=select([func.uuid_generate_v4()])) id_acquisition_framework = DB.Column( DB.Integer, ForeignKey("gn_meta.t_acquisition_frameworks.id_acquisition_framework"), ) acquisition_framework = DB.relationship("TAcquisitionFramework", lazy="joined") # join AF as required for permissions checks dataset_name = DB.Column(DB.Unicode) dataset_shortname = DB.Column(DB.Unicode) dataset_desc = DB.Column(DB.Unicode) id_nomenclature_data_type = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("DATA_TYP"), ) keywords = DB.Column(DB.Unicode) marine_domain = DB.Column(DB.Boolean) terrestrial_domain = DB.Column(DB.Boolean) id_nomenclature_dataset_objectif = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("JDD_OBJECTIFS"), ) bbox_west = DB.Column(DB.Float) bbox_east = DB.Column(DB.Float) bbox_south = DB.Column(DB.Float) bbox_north = DB.Column(DB.Float) id_nomenclature_collecting_method = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("METHO_RECUEIL"), ) id_nomenclature_data_origin = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("DS_PUBLIQUE"), ) id_nomenclature_source_status = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("STATUT_SOURCE"), ) id_nomenclature_resource_type = DB.Column( DB.Integer, ForeignKey("ref_nomenclatures.t_nomenclatures.id_nomenclature"), default=lambda: TNomenclatures.get_default_nomenclature("RESOURCE_TYP"), ) meta_create_date = DB.Column(DB.DateTime) meta_update_date = DB.Column(DB.DateTime) active = DB.Column(DB.Boolean, default=True) validable = DB.Column(DB.Boolean, server_default=FetchedValue()) id_digitizer = DB.Column(DB.Integer, ForeignKey(User.id_role)) digitizer = DB.relationship(User, lazy="joined") # joined for permission check id_taxa_list = DB.Column(DB.Integer) modules = DB.relationship("TModules", secondary=cor_module_dataset, lazy="select") creator = DB.relationship(User, lazy="joined") # = digitizer nomenclature_data_type = DB.relationship( TNomenclatures, lazy="select", foreign_keys=[id_nomenclature_data_type], ) nomenclature_dataset_objectif = DB.relationship( TNomenclatures, lazy="select", foreign_keys=[id_nomenclature_dataset_objectif], ) nomenclature_collecting_method = DB.relationship( TNomenclatures, lazy="select", foreign_keys=[id_nomenclature_collecting_method], ) nomenclature_data_origin = DB.relationship( TNomenclatures, lazy="select", foreign_keys=[id_nomenclature_data_origin], ) nomenclature_source_status = DB.relationship( TNomenclatures, lazy="select", foreign_keys=[id_nomenclature_source_status], ) nomenclature_resource_type = DB.relationship( TNomenclatures, lazy="select", foreign_keys=[id_nomenclature_resource_type], ) cor_territories = DB.relationship( TNomenclatures, lazy="select", secondary=CorDatasetTerritory.__table__, primaryjoin=(CorDatasetTerritory.id_dataset == id_dataset), secondaryjoin=(CorDatasetTerritory.id_nomenclature_territory == TNomenclatures.id_nomenclature), foreign_keys=[ CorDatasetTerritory.id_dataset, CorDatasetTerritory.id_nomenclature_territory, ], backref=DB.backref("territory_dataset", lazy="select") ) # because CorDatasetActor could be an User or an Organisme object... cor_dataset_actor = relationship( CorDatasetActor, lazy="joined", cascade="save-update, merge, delete, delete-orphan", backref=DB.backref("actor_dataset", lazy="select") ) @hybrid_property def user_actors(self): return [ actor.role for actor in self.cor_dataset_actor if actor.role is not None ] @hybrid_property def organism_actors(self): return [ actor.organism for actor in self.cor_dataset_actor if actor.organism is not None ] def is_deletable(self): return not DB.session.query(self.synthese_records.exists()).scalar() def has_instance_permission(self, scope, _through_af=True): """ _through_af prevent infinite recursion """ if scope == 0: return False elif scope in (1, 2): if g.current_user == self.digitizer or g.current_user in self.user_actors: return True if scope == 2 and g.current_user.organisme in self.organism_actors: return True return _through_af and self.acquisition_framework.has_instance_permission(scope, _through_ds=False) elif scope == 3: return True def __str__(self): return self.dataset_name @staticmethod def get_id(uuid_dataset): id_dataset = ( DB.session.query(TDatasets.id_dataset) .filter(TDatasets.unique_dataset_id == uuid_dataset) .first() ) if id_dataset: return id_dataset[0] return id_dataset @staticmethod def get_uuid(id_dataset): uuid_dataset = ( DB.session.query(TDatasets.unique_dataset_id) .filter(TDatasets.id_dataset == id_dataset) .first() ) if uuid_dataset: return uuid_dataset[0] return uuid_dataset @classmethod def compute_filter(cls, **kwargs): f = super().compute_filter(**kwargs) uuid = kwargs.get('uuid') if uuid is not None: try: uuid = UUID(uuid.strip()) except TypeError: pass else: f &= TDatasets.unique_dataset_id == uuid name = kwargs.get('name') if name is not None: f &= TDatasets.dataset_name.ilike(f'%{name}%') return f