RSQLite

RSQLite

  1. Utilisez les possibilités que vous offre R pour modifier les données de la table tmp_auteurs. Déplacer les initiales des prénoms des auteurs dans la colonne prenom. Le schéma de cette table ne doit pas être modifié après cette opération.
auteurs <- dbReadTable(db.con, "tmp1_auteurs")
dbBegin(db.con)
for(n in auteurs$nom) {
  nom_init <- n
  nom      <- unlist(strsplit(nom_init, " "))[1]
  init     <- unlist(strsplit(nom_init, " "))[2]
  statement <- sprintf("UPDATE tmp1_auteurs SET prenom = '%s', nom = '%s' where nom = '%s'", init, nom, nom_init)
  dbExecute(db.con, statement)
  # Une alternative est de construire la requête directement dans dbExecute
  # dbExecute(db.con, "UPDATE tmp1_auteurs SET prenom = ?, nom = ? where nom = ?", list(init, nom, n))
}
dbCommit(db.con)

Vous noterez l’utilisation de dbBegin() et dbCommit() qui sont les fonctions de RSQLite permettant de créér et gérer des transactions. Il n’est pas obligatoire de les utiliser mais, dans le cas présent, cela permet que toutes les requêtes ‘UPDATE’ produites par la boucle for soient exécutées en une seule fois lors du COMMIT. Si une erreur devait se produire, toutes les requêtes seraient annulées et la base de données resterait dans son état initial (celui qui prècède BEGIN ou ici dbBegin()). On parle de transaction atomique: soit toutes les changements d’une transaction sont appliqués à la base de données ou aucun d’entre eux. Au delà de l’intérêt dans la gestion de la cohérence des moidifications qui sont apportées à la base de données, englober plusieurs requêtes dans une seule transaction permet d’accélerer leur exécution. Vous trouverez plus d’informations sur l’usage de ces commandes ici et .

  1. Réalisez un script qui vous permet de remplacer toutes les chaines de caractères ‘NULL’ des tables de la base de données par des valeurs NULL
tables <- dbListTables(db.con)
for(table in tables) { # boucle sur la liste des tables
  message(table)
  fields <- dbListFields(db.con, table)
  for(field in fields){ # boucle sur les champs de la table courante
    # Sélection des lignes pour lesquelles le champ courant contient
    # la chaîne de caractère 'NULL'
    aa <- dbGetQuery(db.con, paste0('select * from ', table,' where ', field,' = "NULL"'))
    if(nrow(aa) > 0) { # des lignes ont été identifiées
      message("Champ 'NULL' trouvé dans la table ", table, " et le champs ", field)
      if(field %in% c("accession", "ID_aut", "pubmedid", "taxon", "source", "nom", "type", "sequence", "date")){ # la liste de tous les champ avec contrainte 'not null'
        # dans ce cas on supprime la ligne
        statement <- paste0('DELETE FROM ', table,' WHERE ', field,' = "NULL"')
        message("Je supprime")
      } else {
        # dans l'autre cas, on supprime simplement la valeur du champ en question.
        # Notez qu'on n'utilise pas `DELETE` qui s'applique à la ligne entière !!!
        # La valeur du champ en question est mise sur NULL et non pas la chaîne "NULL"
        statement <- paste0('UPDATE ', table,' SET ',field, ' = NULL WHERE ', field,' = "NULL"')
        message("Je modifie")
      }
    dbExecute(db.con, statement)
    }
  }
}
  1. Téléchargez le fichier ftp://ftp.ncbi.nlm.nih.gov/pub/taxonomy/taxdump.tar.gz qui contient l’ensemble des données taxonomiques du NCBI. Le fichier tabulaire names.dmp est celui qui nous intéresse. Il est formé de 4 colonnes: la première contient les numéros taxonomiques, la seconde le nom officiel de l’espèce et la dernière le type d’information. Commencez par selectionner les informations de type ‘scientific name’ puis mettez à jour le champs ’nom’ de notre table organismes en utilisant ces informations, seulement si le nom du fichier ’names.dmp’ est différent de celui de notre table. Combien d’enregistrements sont-ils modifiés ? Compléter ensuite votre base de données en recherchant et ajoutant les noms des taxons Parents qui ne seraient pas encodés dans la table.
#install.packages("data.table")
library(data.table)
#download.file("ftp://ftp.ncbi.nlm.nih.gov/pub/taxonomy/taxdump.tar.gz", destfile = "taxdump.tar.gz")
#untar("taxdump.tar.gz", exdir = "taxdump")

# Toujours vérifier le format du fichier avant de le processer !
# Utiliser la possibilité de fread de 'pré-traiter le fichier avant de le lire en stdin
names <- fread(cmd = "cat taxdump/names.dmp | tr -d '\t'", sep="|")
scientific.names <- subset(names, V4 == "scientific name")
# Equivalent 'data.table': scientific.names <- names[V4 == "scientific name"]
# Equivalent 'data.frame': scientific.names <- names[names$V4 == "scientific name",]

organismes <- dbReadTable(db.con, "organismes")
for(t in organismes$taxon) {
  old_nom  <- unlist(subset(x = organismes, taxon == t, nom))
  new_nom  <- unlist(subset(x = scientific.names, V1 == t, V2))
  if(length(new_nom) > 0 && old_nom != new_nom) {
    message(old_nom, " // ", new_nom)
    statement <- paste0('UPDATE organismes SET nom = "', new_nom,'" where taxon = ', t)
    # statement <- sprintf("UPDATE organismes SET nom = '%s' where taxon = %i", new_nom, t)
    # UPDATE organismes SET nom = "nom" where taxon = 123
    dbExecute(db.con, statement)
  }

  t.parent <- unlist(subset(x = organismes, taxon == t, parent))
  exists   <- length(unlist(subset(x = organismes, taxon == t.parent)))>0
  nom_parent <- unlist(subset(x = scientific.names, V1 == t.parent, V2))
  if(length(nom_parent)>0 && !exists) {
    message("Ajout de l'information 'parent': ", t.parent, " // ", nom_parent)
    statement <- paste0('INSERT INTO organismes (nom, taxon) values ("', nom_parent,'", "', t.parent,'")')
    dbExecute(db.con, statement)
    organismes <- dbReadTable(db.con, "organismes")
  }
}