Fix/view species

[cen-cgeier]

Optimisation des vues pr_zh.invertebrates, pr_zh.vertebrates et pr_zh.flora

• Ajout d'index sur les colonnes geom des différentes tables du schéma pr_zh
• Regroupement de with synthese_taxa as et synthese_zh as
• Jointure INNER de gn_synthese.synthese et pr_zh.t_zh sur les géométries intersectées
• Jointure LEFT de utilisateurs.v_userslist_forall_applications et gn_synthese.synthese pour la récupération des organismes

la fixation du .gitignore sur le fichier module.config.ts n'a pas abouti

[cen-cgeier]

Le paramètre limit=-1 pause toujours problème.

Log :

[bf97817e-2f57-4e9b-9527-90fe5ab0452e] Exception on /zones_humides/331/taxa [GET]
Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidRowCountInLimitClause: ERREUR:  LIMIT ne doit pas être négative

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 1823, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/home/geonatureadmin/geonature/backend/geonature/core/gn_permissions/decorators.py", line 62, in decorated_view
    return view_func(*args, **kwargs)
  File "/home/geonatureadmin/gn_module_ZH/backend/gn_module_zh/blueprint.py", line 822, in write_csv
    results = query.return_query().get("items", [])
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/utils_flask_sqla/generic.py", line 284, in return_query
    data, nb_result_without_filter, nb_results = self.query()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/utils_flask_sqla/generic.py", line 273, in query
    data = self.set_limit(q).all()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3373, in all
    return list(self)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3535, in __iter__
    return self._execute_and_instances(context)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidRowCountInLimitClause) ERREUR:  LIMIT ne doit pas être négative

[SQL: SELECT pr_zh.vertebrates.id_zh AS pr_zh_vertebrates_id_zh, pr_zh.vertebrates.cd_nom AS pr_zh_vertebrates_cd_nom, pr_zh.vertebrates.group_class AS pr_zh_vertebrates_group_class, pr_zh.vertebrates.group_order AS pr_zh_vertebrates_group_order, pr_zh.vertebrates.scientific_name AS pr_zh_vertebrates_scientific_name, pr_zh.vertebrates.vernac_name AS pr_zh_vertebrates_vernac_name, pr_zh.vertebrates.statut_type AS pr_zh_vertebrates_statut_type, pr_zh.vertebrates.statut AS pr_zh_vertebrates_statut, pr_zh.vertebrates.article AS pr_zh_vertebrates_article, pr_zh.vertebrates.doc_url AS pr_zh_vertebrates_doc_url, pr_zh.vertebrates.last_date AS pr_zh_vertebrates_last_date, pr_zh.vertebrates.observer AS pr_zh_vertebrates_observer, pr_zh.vertebrates.organisme AS pr_zh_vertebrates_organisme, pr_zh.vertebrates.obs_nb AS pr_zh_vertebrates_obs_nb 
FROM pr_zh.vertebrates 
WHERE pr_zh.vertebrates.id_zh = %(id_zh_1)s ORDER BY pr_zh.vertebrates.id_zh 
 LIMIT %(param_1)s OFFSET %(param_2)s]
[parameters: {'id_zh_1': 331, 'param_1': -1, 'param_2': 0}]
(Background on this error at: http://sqlalche.me/e/13/9h9h)
[2023-11-28 10:42:55 +0100] [432726] [ERROR] Exception on /zones_humides/331/taxa [GET]
Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidRowCountInLimitClause: ERREUR:  LIMIT ne doit pas être négative

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 1823, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/home/geonatureadmin/geonature/backend/geonature/core/gn_permissions/decorators.py", line 62, in decorated_view
    return view_func(*args, **kwargs)
  File "/home/geonatureadmin/gn_module_ZH/backend/gn_module_zh/blueprint.py", line 822, in write_csv
    results = query.return_query().get("items", [])
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/utils_flask_sqla/generic.py", line 284, in return_query
    data, nb_result_without_filter, nb_results = self.query()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/utils_flask_sqla/generic.py", line 273, in query
    data = self.set_limit(q).all()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3373, in all
    return list(self)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3535, in __iter__
    return self._execute_and_instances(context)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidRowCountInLimitClause) ERREUR:  LIMIT ne doit pas être négative

[SQL: SELECT pr_zh.vertebrates.id_zh AS pr_zh_vertebrates_id_zh, pr_zh.vertebrates.cd_nom AS pr_zh_vertebrates_cd_nom, pr_zh.vertebrates.group_class AS pr_zh_vertebrates_group_class, pr_zh.vertebrates.group_order AS pr_zh_vertebrates_group_order, pr_zh.vertebrates.scientific_name AS pr_zh_vertebrates_scientific_name, pr_zh.vertebrates.vernac_name AS pr_zh_vertebrates_vernac_name, pr_zh.vertebrates.statut_type AS pr_zh_vertebrates_statut_type, pr_zh.vertebrates.statut AS pr_zh_vertebrates_statut, pr_zh.vertebrates.article AS pr_zh_vertebrates_article, pr_zh.vertebrates.doc_url AS pr_zh_vertebrates_doc_url, pr_zh.vertebrates.last_date AS pr_zh_vertebrates_last_date, pr_zh.vertebrates.observer AS pr_zh_vertebrates_observer, pr_zh.vertebrates.organisme AS pr_zh_vertebrates_organisme, pr_zh.vertebrates.obs_nb AS pr_zh_vertebrates_obs_nb 
FROM pr_zh.vertebrates 
WHERE pr_zh.vertebrates.id_zh = %(id_zh_1)s ORDER BY pr_zh.vertebrates.id_zh 
 LIMIT %(param_1)s OFFSET %(param_2)s]
[parameters: {'id_zh_1': 331, 'param_1': -1, 'param_2': 0}]
(Background on this error at: http://sqlalche.me/e/13/9h9h)