class ProgramsModel(TimestampMixinModel, db.Model): """Table des Programmes de GeoNature-citizen""" __tablename__ = "t_programs" __table_args__ = {"schema": "gnc_core"} id_program = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(50), nullable=False) short_desc = db.Column(db.String(200), nullable=False) long_desc = db.Column(db.Text(), nullable=False) image = db.Column(db.String(250)) logo = db.Column(db.String(250)) module = db.Column( db.Integer, ForeignKey(ModulesModel.id_module), nullable=False, default=1, ) taxonomy_list = db.Column( db.Integer, #ForeignKey(BibListes.id_liste), nullable=True ) is_active = db.Column( db.Boolean(), server_default=expression.true(), default=True ) geom = db.Column(Geometry("GEOMETRY", 4326)) def get_geofeature(self, recursif=True, columns=None): return self.as_geofeature( "geom", "id_program", recursif, columns=columns )
class GroupsModel(db.Model): # type: ignore """Table des groupes d'utilisateurs""" __tablename__ = "bib_groups" __table_args__ = {"schema": "gnc_core"} id_group = db.Column(db.Integer, primary_key=True) category = db.Column(db.String(150), nullable=True) group = db.Column(db.String(150), nullable=False)
class MediaModel(TimestampMixinModel, db.Model): """Table des Programmes de GeoNature-citizen """ __tablename__ = "t_medias" __table_args__ = {"schema": "gnc_core"} id_media = db.Column(db.Integer, primary_key=True) filename = db.Column(db.String(50), nullable=False)
class BibNoms(db.Model): # type: ignore __tablename__ = "bib_noms" __table_args__ = {"schema": "taxonomie", "extend_existing": True} id_nom = db.Column(db.Integer, primary_key=True) cd_nom = db.Column(db.Integer, nullable=True, unique=True) cd_ref = db.Column(db.Integer) nom_francais = db.Column(db.Unicode) comments = db.Column(db.Unicode)
class LiMunicipalities(db.Model): # type: ignore __tablename__ = "li_municipalities" __table_args__ = {"schema": "ref_geo"} id_municipality = db.Column(db.Unicode, primary_key=True, unique=True) nom_com = db.Column(db.Unicode) id_area = db.Column( db.Integer, db.ForeignKey("ref_geo.l_areas.id_area"), unique=True ) insee_com = db.Column(db.Integer)
class GeometryModel(TimestampMixinModel, db.Model): """Table des géométries associées aux programmes""" __tablename__ = "t_geometries" __table_args__ = {"schema": "gnc_core"} id_geom = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50), nullable=False) description = db.Column(db.Text(), nullable=True) geom = db.Column(Geometry("GEOMETRY", 4326)) geom_file = db.Column(db.String(250), nullable=True) def get_geom_file_path(self): return os.path.join(str(MEDIA_DIR), self.geom_file) def set_geom_from_geom_file(self): gnc_invalid_err_message = "Géométrie non valide pour GNC" name, ext = os.path.splitext(self.geom_file) with open(self.get_geom_file_path()) as geom_file: geo_data = geom_file.read() if ext in [".geojson", ".json"]: json_geom = json.loads(geo_data)["features"][0]["geometry"] # Validate geometry type if not json_geom["type"] in ["Polygon", "MultiPolygon"]: raise Exception(gnc_invalid_err_message) else: # Minimal coordinate system check coords = json_geom["coordinates"][0][0] if json_geom["type"] == "MultiPolygon": coords = coords[0] x, y = coords if abs(x) > 180 or abs(y) > 180: raise Exception("Mauvais système de projection") # Convert Geo self.geom = ST_SetSRID( ST_GeomFromGeoJSON(json.dumps(json_geom)), 4326) elif ext == ".kml": kml_root = ET.fromstring(geo_data) kml_geom_elt = None # Find first MultiGeometry or Polygon node for child in kml_root.iter(): if "MultiGeometry" in child.tag or "Polygon" in child.tag: kml_geom_elt = child if "MultiGeometry" in child.tag: # We want only Polygon nodes inside the geometry for elt in kml_geom_elt.getchildren(): if not "Polygon" in elt.tag: raise Exception(gnc_invalid_err_message) if kml_geom_elt is None: raise Exception(gnc_invalid_err_message) kml_geom = ET.tostring(kml_geom_elt, encoding="unicode", method="xml") self.geom = ST_GeomFromKML(kml_geom) # KML is always 4326 srid def __repr__(self): return self.name
class CustomFormModel(TimestampMixinModel, db.Model): """Table des Formulaires spécifiques associés aux programmes""" __tablename__ = "t_custom_form" __table_args__ = {"schema": "gnc_core"} id_form = db.Column(db.Integer, primary_key=True, unique=True) name = db.Column(db.String(250)) json_schema = db.Column(JSONB, nullable=True) def __repr__(self): return self.name
class UserGroupsModel(TimestampMixinModel, db.Model): # type: ignore """Table de classement des utilisateurs dans des groupes""" __tablename__ = "cor_users_groups" __table_args__ = {"schema": "gnc_core"} id_user_right = db.Column(db.Integer, primary_key=True) id_user = db.Column(db.Integer, db.ForeignKey(UserModel.id_user), nullable=False) id_group = db.Column(db.Integer, db.ForeignKey(GroupsModel.id_group), nullable=False)
class RevokedTokenModel(TimestampCreateMixinModel, db.Model): # type: ignore __tablename__ = "t_revoked_tokens" __table_args__ = {"schema": "gnc_core"} id = db.Column(db.Integer, primary_key=True) jti = db.Column(db.String(120)) def add(self): db.session.add(self) db.session.commit() @classmethod def is_jti_blacklisted(cls, jti): query = cls.query.filter_by(jti=jti).first() return bool(query)
def timestamp_update(cls): return db.Column( db.DateTime, nullable=True, default=datetime.utcnow, onupdate=datetime.utcnow, )
class ProjectModel(TimestampMixinModel, db.Model): """Table des projets regroupant les programmes""" __tablename__ = "t_projects" __table_args__ = {"schema": "gnc_core"} id_project = db.Column(db.Integer, primary_key=True) unique_id_project = db.Column(UUID(as_uuid=True), default=uuid.uuid4, unique=True, nullable=False) name = db.Column(db.String(50), nullable=False) short_desc = db.Column(db.String(200), nullable=True) long_desc = db.Column(db.Text(), nullable=True) def __repr__(self): return self.name
class ObservationMediaModel( TimestampMixinModel, db.Model # type: ignore ): """Table de correspondances des médias (photos) avec les observations""" __tablename__ = "cor_obstax_media" __table_args__ = {"schema": "gnc_obstax"} id_match = db.Column(db.Integer, primary_key=True, unique=True) id_data_source = db.Column( db.Integer, db.ForeignKey(ObservationModel.id_observation, ondelete="CASCADE"), nullable=False, ) id_media = db.Column( db.Integer, db.ForeignKey(MediaModel.id_media, ondelete="CASCADE"), nullable=False, )
class BibAreasTypes(db.Model): # type: ignore __tablename__ = "bib_areas_types" __table_args__ = {"schema": "ref_geo"} id_type = db.Column(db.Integer, primary_key=True) type_name = db.Column(db.Unicode) # type: ignore type_code = db.Column(db.Unicode) type_desc = db.Column(db.Unicode) ref_name = db.Column(db.Unicode) ref_version = db.Column(db.Integer) num_version = db.Column(db.Unicode)
class CorNomListe(db.Model): # type: ignore __tablename__ = "cor_nom_liste" __table_args__ = {"schema": "taxonomie", "extend_existing": True} id_liste = db.Column( db.Integer, ForeignKey("taxonomie.bib_listes.id_liste"), nullable=False, primary_key=True, ) id_nom = db.Column( db.Integer, ForeignKey("taxonomie.bib_noms.id_nom"), nullable=False, primary_key=True, ) bib_nom = db.relationship("BibNoms") bib_liste = db.relationship("BibListes") # listes = db.relationship("CorNomListe", lazy='select') # media = db.relationship("TMedias", lazy='select') def __repr__(self): return "<CorNomListe %r>" % self.id_liste
class UserRightsModel(TimestampMixinModel, db.Model): # type: ignore """Table de gestion des droits des utilisateurs de GeoNature-citizen""" __tablename__ = "t_users_rights" __table_args__ = {"schema": "gnc_core"} id_user_right = db.Column(db.Integer, primary_key=True) id_user = db.Column(db.Integer, db.ForeignKey(UserModel.id_user), nullable=False) id_module = db.Column(db.Integer, db.ForeignKey(ModulesModel.id_module), nullable=True) id_module = db.Column(db.Integer, db.ForeignKey(ProgramsModel.id_program), nullable=True) right = db.Column(db.String(150), nullable=False) create = db.Column(db.Boolean(), default=False) read = db.Column(db.Boolean(), default=False) update = db.Column(db.Boolean(), default=False) delete = db.Column(db.Boolean(), default=False)
class ModulesModel(TimestampMixinModel, db.Model): """Table des modules de GeoNature-citizen""" __tablename__ = "t_modules" __table_args__ = {"schema": "gnc_core"} id_module = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50), nullable=False) label = db.Column(db.String(50), nullable=False) desc = db.Column(db.String(200)) icon = db.Column(db.String(250)) on_sidebar = db.Column(db.Boolean(), default=False)
class LAreas(db.Model): # type: ignore __tablename__ = "l_areas" __table_args__ = {"schema": "ref_geo"} id_area = db.Column(db.Integer, primary_key=True) id_type = db.Column( db.Integer, db.ForeignKey("ref_geo.bib_areas_types.id_type") ) area_name = db.Column(db.Unicode) area_code = db.Column(db.Unicode) source = db.Column(db.Unicode) enable = db.Column(db.Boolean) geom = db.Column(Geometry("GEOMETRY", 4326)) def get_geofeature(self, recursive=True): return self.as_geofeature("geom", "id_area", recursive)
class ObservationModel( ObserverMixinModel, TimestampMixinModel, db.Model # type: ignore ): """Table des observations""" __tablename__ = "t_obstax" __table_args__ = {"schema": "gnc_obstax"} id_observation = db.Column(db.Integer, primary_key=True, unique=True) uuid_sinp = db.Column(UUID(as_uuid=True), nullable=False, unique=True) id_program = db.Column(db.Integer, db.ForeignKey(ProgramsModel.id_program), nullable=False) cd_nom = db.Column( db.Integer, db.ForeignKey(Taxref.cd_nom), # todo: removal breaks recog rewards nullable=False, ) date = db.Column(db.Date, nullable=False) count = db.Column(db.Integer) comment = db.Column(db.String(300)) municipality = db.Column(db.Integer, db.ForeignKey(LAreas.id_area)) geom = db.Column(Geometry("POINT", 4326))
class BibListes(db.Model): # type: ignore __tablename__ = "bib_listes" __table_args__ = {"schema": "taxonomie", "extend_existing": True} id_liste = db.Column(db.Integer, primary_key=True) nom_liste = db.Column(db.Unicode) desc_liste = db.Column(db.Text) picto = db.Column(db.Unicode) regne = db.Column(db.Unicode) group2_inpn = db.Column(db.Unicode) # cnl = db.relationship("CorNomListe", lazy='select') def __repr__(self): return "<BibListes %r>" % self.nom_liste
class TMedias(db.Model): # type: ignore __tablename__ = "t_medias" __table_args__ = {"schema": "taxonomie", "extend_existing": True} id_media = db.Column(db.Integer, primary_key=True) cd_ref = db.Column( db.Integer, ForeignKey("taxonomie.bib_noms.cd_ref"), nullable=False, primary_key=False, ) titre = db.Column(db.Unicode) url = db.Column(db.Unicode) chemin = db.Column(db.Unicode) auteur = db.Column(db.Unicode) desc_media = db.Column(db.Text) is_public = db.Column(db.BOOLEAN) supprime = db.Column(db.BOOLEAN) id_type = db.Column(db.Integer) source = db.Column(db.Text) licence = db.Column(db.Text) def __repr__(self): return f"<TMedias {self.titre}>"
class Taxref(db.Model): # type: ignore __tablename__ = "taxref" __table_args__ = {"schema": "taxonomie"} cd_nom = db.Column(db.Integer, primary_key=True) id_statut = db.Column(db.Unicode) id_habitat = db.Column(db.Integer) id_rang = db.Column(db.Unicode) regne = db.Column(db.Unicode) phylum = db.Column(db.Unicode) classe = db.Column(db.Unicode) ordre = db.Column(db.Unicode) famille = db.Column(db.Unicode) sous_famille = db.Column(db.Unicode) tribu = db.Column(db.Unicode) cd_taxsup = db.Column(db.Integer) cd_sup = db.Column(db.Integer) cd_ref = db.Column(db.Integer) lb_nom = db.Column(db.Unicode) lb_auteur = db.Column(db.Unicode) nom_complet = db.Column(db.Unicode) nom_complet_html = db.Column(db.Unicode) nom_vern = db.Column(db.Unicode) nom_valide = db.Column(db.Unicode) nom_vern_eng = db.Column(db.Unicode) group1_inpn = db.Column(db.Unicode) group2_inpn = db.Column(db.Unicode) url = db.Column(db.Unicode) def __repr__(self): return f"<Taxref {self.nom_complet}>"
def id_role(self): return db.Column( db.Integer, db.ForeignKey(UserModel.id_user, ondelete="SET NULL"), nullable=True, )
def obs_txt(self): return db.Column(db.String(150))
def email(self): return db.Column(db.String(150))
class UserModel(TimestampMixinModel, db.Model): # type: ignore """ Table des utilisateurs """ __tablename__ = "t_users" __table_args__ = {"schema": "gnc_core"} id_user = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), nullable=False) surname = db.Column(db.String(100), nullable=False) username = db.Column(db.String(12), unique=True, nullable=False) password = db.Column(db.String(120), nullable=False) email = db.Column(db.String(150), unique=True, nullable=False) phone = db.Column(db.String(15)) organism = db.Column(db.String(100)) admin = db.Column(db.Boolean, default=False) def save_to_db(self): db.session.add(self) db.session.commit() def update(self): db.session.commit() def as_user_dict(self): surname = self.username or "" name = self.name or "" return { "id_role": self.id_user, "name": self.name, "surname": self.surname, "username": self.username, "email": self.email, "phone": self.phone, "organism": self.organism, "full_name": name + " " + surname, "admin": self.admin, "timestamp_create": self.timestamp_create.isoformat(), "timestamp_update": self.timestamp_update.isoformat() if self.timestamp_update else None, } @staticmethod def generate_hash(password): return sha256.hash(password) @staticmethod def verify_hash(password, hash_): return sha256.verify(password, hash_) @classmethod def find_by_username(cls, username): try: return cls.query.filter_by(username=username).one() except NoResultFound: raise Exception(f"""User "{username}" not found.""") @classmethod def return_all(cls): def to_dict(x): return { "username": x.username, "password": x.password, "email": x.email, "phone": x.phone, "admin": x.admin, } return { "users": list( map( lambda x: to_dict(x), # pylint: disable=unnecessary-lambda UserModel.query.all(), )) }
def timestamp_create(self): return db.Column(db.DateTime, nullable=False, default=now)
def timestamp_create(cls): return db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
def timestamp_update(self): return db.Column(db.DateTime, nullable=True, default=now, onupdate=now,)
class ProgramsModel(TimestampMixinModel, db.Model): """Table des Programmes de GeoNature-citizen""" __tablename__ = "t_programs" __table_args__ = {"schema": "gnc_core"} id_program = db.Column(db.Integer, primary_key=True) unique_id_program = db.Column(UUID(as_uuid=True), default=uuid.uuid4, unique=True, nullable=False) id_project = db.Column(db.Integer, db.ForeignKey(ProjectModel.id_project), nullable=False) title = db.Column(db.String(50), nullable=False) short_desc = db.Column(db.String(200), nullable=False) long_desc = db.Column(db.Text(), nullable=False) form_message = db.Column(db.String(500)) image = db.Column(db.String(250)) logo = db.Column(db.String(250)) id_module = db.Column( db.Integer, ForeignKey(TModules.id_module), nullable=False, default=1, ) module = relationship("TModules") taxonomy_list = db.Column(db.Integer, nullable=True) is_active = db.Column(db.Boolean(), server_default=expression.true(), default=True) id_geom = db.Column(db.Integer, db.ForeignKey(GeometryModel.id_geom), nullable=False) id_form = db.Column(db.Integer, db.ForeignKey(CustomFormModel.id_form), nullable=True) custom_form = relationship("CustomFormModel") geometry = relationship("GeometryModel") project = relationship("ProjectModel") def get_geofeature(self, recursif=True, columns=None): geometry = to_shape(self.geometry.geom) feature = Feature( id=self.id_program, geometry=geometry, properties=self.as_dict(True, exclude=["t_obstax"]), ) return feature def __repr__(self): return self.title