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
