flat_create_table_sequence.Rmd

title: "Cr\u00e9ation des tables et des s\u00e9quences"
output: html_document
editor_options:
  chunk_output_type: console
library(testthat)
library(yaml)
library(glue)
library(DBI)
library(datalibaba)
# Load already included functions if relevant
pkgload::load_all(export_all = FALSE)
# Lire le fichier de configuration
config <- yaml::read_yaml("config.yml")

# Accéder aux valeurs pour version et last_year
version <- config$version
last_year <- config$last_year
#' Créer une table de prélèvements et analyses de nitrates
#'
#' @description Cette fonction crée une table dans une base de données PostgreSQL
#' pour stocker les informations relatives aux prélèvements et analyses de
#' nitrates, incluant les contraintes et les séquences nécessaires.
#'
#' @param version String. Version de la table (par exemple, 'v1').
#' @param last_year Integer. L'année la plus récente incluse dans les données de la table.
#'
#' @return String. Le script SQL utilisé pour créer la table.
#' @export
#' @importFrom datalibaba connect_to_db
create_table_nitrate_prelevement_analyse <- function(version, last_year) {
  # Établir une connexion à la base de données PostgreSQL
  connexion <- datalibaba::connect_to_db(db = "si_eau", user = "admin")

  # Génération de la date du jour
  date_now <- format(Sys.Date(), "%d/%m/%Y")

  # Liste des requêtes SQL
  sql_commands <- c(
    glue::glue("CREATE TABLE IF NOT EXISTS nitrates.nitrate_prelevement_analyse_{version} (
      code_prelevement_analyse serial NOT NULL,
      code_intervenant character varying(20),
      source character varying(10),
      code_reseau character varying(254),
      code_station character varying(10),
      date_prelevement date,
      heure_prelevement character varying(8),
      code_support integer,
      nature_eau character varying(3),
      id_usage character varying(3),
      id_prelevement_motif character varying(2),
      commentaire character varying(254),
      code_parametre bigint,
      code_fraction_analysee integer,
      date_analyse date,
      resultat_analyse double precision,
      code_remarque integer,
      limite_detection double precision,
      limite_quantification double precision,
      CONSTRAINT pk_nitrate_prelevement_analyse_{version} PRIMARY KEY (code_prelevement_analyse),
      CONSTRAINT fk_n_support FOREIGN KEY (code_support)
          REFERENCES sandre.n_support (code_support) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE SET DEFAULT,
      CONSTRAINT fk_parametre FOREIGN KEY (code_parametre)
          REFERENCES qualite_cours_d_eau.parametre (code_parametre) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE SET DEFAULT,
      CONSTRAINT fk_n_fraction_analysee FOREIGN KEY (code_fraction_analysee)
          REFERENCES sandre.n_fraction_analysee (code_fraction_analysee) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE SET DEFAULT,
      CONSTRAINT fk_n_remarque FOREIGN KEY (code_remarque)
          REFERENCES sandre.n_remarque (code_remarque) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
    ) WITH (OIDS=FALSE);"),

    glue::glue("ALTER TABLE nitrates.nitrate_prelevement_analyse_{version} OWNER TO adminpsql;"),

    glue::glue("GRANT ALL ON TABLE nitrates.nitrate_prelevement_analyse_{version} TO adminpsql;"),
    glue::glue("GRANT ALL ON TABLE nitrates.nitrate_prelevement_analyse_{version} TO writer_production;"),
    glue::glue("GRANT SELECT ON TABLE nitrates.nitrate_prelevement_analyse_{version} TO reader_production;"),

    glue::glue("COMMENT ON TABLE nitrates.nitrate_prelevement_analyse_{version} IS
      'Table des prélèvements et analyses 2007-{last_year} (version {version} du {date_now})';"),

    glue::glue("CREATE SEQUENCE IF NOT EXISTS nitrates.nitrate_prelevement_analyse_{version}_code_prelevement_analyse_seq
      INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;"),

    glue::glue("ALTER SEQUENCE nitrates.nitrate_prelevement_analyse_{version}_code_prelevement_analyse_seq
      OWNER TO adminpsql;")
  )

  # Ajout des commentaires sur chaque champ
  comments_sql <- c(
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_prelevement_analyse IS 'Identifiant du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_intervenant IS 'Identifiant de l''intervenant';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.source IS 'Source de la donnée';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_reseau IS 'Identifiant du réseau';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_station IS 'Identifiant de la station';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.date_prelevement IS 'Date du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.heure_prelevement IS 'Heure du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_support IS 'Code du support de prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.nature_eau IS 'Nature de l''eau du prélèvement (ESO/ESU)';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.id_usage IS 'Code de l''usage du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.id_prelevement_motif IS 'Code du motif du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.commentaire IS 'Commentaire';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_parametre IS 'Identifiant du paramètre analysé';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_fraction_analysee IS 'Identifiant de la fraction analysée';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.date_analyse IS 'Date de l''analyse';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.resultat_analyse IS 'Résultat de l''analyse';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_remarque IS 'Code validant la donnée';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.limite_detection IS 'Limite de détection';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.limite_quantification IS 'Limite de quantification';")
  )

 # Exécuter chaque commande SQL
  for (sql in c(sql_commands, comments_sql)) {
    DBI::dbExecute(connexion, sql)
  }

  # Fermeture de la connexion à la base de données
  DBI::dbDisconnect(connexion)

  # Retourne toutes les commandes exécutées
  return(c(sql_commands, comments_sql))
}