Mémento de requêtes SQL pour Omeka Classic

Omeka Classic, la plateforme de publication de collection numérisées, repose sur le logiciel de base de données relationnelle MySQL (ou sa version entièrement GPL, MariaDB). Aussi, des connaissances de base en SQL et des droits d’accès à cette base de données vous permettront d’obtenir des informations sur les documents déposées sur votre bibliothèque numérique que l’interface d’administration de Omeka ne pourra vous fournir. Par exemple, afficher le nombre de fichiers associés aux documents des collections ou trouver des documents en doublons.

Ce document constitue un mémento de requêtes SQL utilisées pour interroger la bibliothèque numérique du Centre de recherche bretonne et celtique dont vous pouvez vous inspirer pour interroger votre instance de Omeka. Il présuppose une connaissance des concepts de Omeka (fichiers, contenus ou items, collections) et des connaissances de base en SQL1.

Si vous êtes intéressé par la même démarche avec Omeka S, vous pouvez consulter l’article de Pierre Couchet : Quelques requêtes MySql avec Omeka S.

Avertissements et pré-requis

interroger la base de donnée de votre bibliothèque numérique en SQL ne sera possible que si vous avez des droits d’accès à sa base de données relationnelle (à travers des logiciels de gestion de bases de données tels que phpMyAdmin, HeidiSQL ou DBeaver). Si ce n’est pas vous qui avez installé Omeka, contactez votre hébergeur pour obtenir ces droits.

Les URLs utilisées dans ce document

Si votre bibliothèque numérique a pour URL http://www.bibnum.org, le chemin d’accès aux contenus sera http://www.bibnum.org/admin/items. Dans ce document, seuls les suffixes des URL sont citées (par ex. /admin/items).

Les records de Omeka

Omeka désigne sous le thème de “record” les fichiers associés aux contenus, les contenus (ou items) et les collections. Des métadonnées peuvent être associées à ces trois types de records. Le jeu de métadonnées par défaut est le Dublin Core mais d’autres jeux peuvent être activés (/admin/element-sets).

Les tables des records, des jeux de métadonnées et des métadonnées des records

Omeka Classic crée dans sa base de données relationnelle plusieurs tables dont les trois tables suivantes :

La table des contenus (ou items), omeka_items, qui a pour colonnes :

  • id (l’identifiant Omeka de l’item, il s’agit du chiffre que l’on retrouve dans l’URL du contenu, par exemple id = 24 pour https://bibnumcrbc.huma-num.fr/document/24),
  • item_type_id (un chiffre correspondant au type de contenu, tel que détaillé dans la table omeka_item_type et sur la page /admin/item-types. Par ex. Text, Moving Image, Oral History, Sound),
  • collection_id (l’identifiant de la collection à laquelle le contenu est éventuellement rattaché)
  • featured (= 1 ou 0, indique si le contenu est mis ou pas en avant),
  • public (= 1 ou 0, indique si le contenu est public ou non),
  • modified (la date de dernière modification du contenu. La documentation ne précise pas de quelles modifications il s’agit : modification des métadonnées et/ou des fichiers associés),
  • added (date d’ajout du contenu),
  • owner_id (identifiant de l’utilisateur propriétaire du contenu)

La table de description des différentes métadonnées disponibles pour décrire les records, omeka_elements, qui contient les intitulés et le descriptif des éléments des jeux de métadonnées et qui a pour colonnes :

  • id (l’identifiant interne à Omeka de l’élément)
  • element_set_id (l’identifiant interne à Omeka pour le jeu de métadonnées auquel appartient l’élément, par exemple le Dublin Core a pour element_set_id = 1, défini dans omeka_element_sets)
  • order (l’ordre d’affichage de l’élément dans le formulaire de saisie des métadonnées d’un record. L’ordre des éléments du Dublin Core est défini dans /admin/element-sets/edit/1)
  • name (ex. = Title pour le Dublin Core Title)
  • description (description de l’élément telle qu’elle apparait dans le formulaire de saisie des métadonnées d’un record, modifiable dans /admin/element-sets/edit/1 pour le Dublin Core)

La table des valeurs des métadonnées des records (= fichiers, contenus, collections), omeka_element_texts, qui a pour colonnes :

  • id (l’identifiant de la métadonnée)
  • record_id (le chiffre généré par Omeka à la création du record, par exemple = 24 pour https://bibnumcrbc.huma-num.fr/document/24 ou = 1 pour https://bibnumcrbc.huma-num.fr/collections/show/1 ou = 18133 pour https://bibnumcrbc.huma-num.fr/files/show/18133)
  • record_type (= File ou Item ou Collection)
  • element_id (l’identifiant de l’élément du jeu de métadonnées, par exemple, le Dublin Core Title a pour element_id = 50, selon la table omeka_elements)
  • html (= 1 ou 0 selon que la valeur de l’élément est saisie ou non en HTML. Correspond à la case à cocher “Utiliser le HTML” dans les formulaires de saisie des métadonnées)
  • text (la valeur de l’élément, telle que saisie dans les formulaires de l’espace d’administration, = “Documents envoyés par Marie-Basilius L’Her habitant à Plounéour-Trez à l’abbé Jean-Marie Perrot en réponse au concours du Barzaz Bro-Leon” pour le Dublin Core Title de https://bibnumcrbc.huma-num.fr/document/24).

Toutes les valeurs d’une métadonnée des contenus d’une collection

La bibliothèque numérique du CRBC héberge le Barzaz Breo Leon, les réponses à des concours de chansons en breton lancés entre 1906 et 1909 dans le Courrier du Finistère. L’unité documentaire choisie pour ce fonds est l’ensemble des documents envoyés par une même personne en réponse aux différentes éditions du concours. Par exemple, Marie-Basilius L’Her, habitante de Plounéour-Trez a envoyé en 1906 et 1907, huit cahiers d’écolier (soit 69 chansons) et une lettre en breton sur une feuille à part. Les titres des chansons, documentées par l’historienne Eva Guillorel, ont été saisis dans des champs répétés du Dublin Core Subject (il y a donc autant d’éléments Dublin Core Subject saisis pour chaque contenu que de titres de chansons).

A la demande d’un chercheur émérite, la liste des chansons de la collection Barzaz Bro Leon a  été obtenue par la requête SQL suivante :

SELECT DISTINCT text
FROM omeka_element_texts
WHERE record_id IN (SELECT id FROM omeka_items WHERE collection_id = 1)
AND record_type = "Item"
AND element_id = 49

Les valeurs des champs Dublin Core Subject sont dans la colonne text de la table omeka_element_text. La colonne id de la table omeka_elements nous indique que l’identifiant du Dublin Core Subjet est = 49. Nous ne sommes intéressés que par les records de type “Item” (cette précision est nécessaire car un contenu, une collection ou un fichier peuvent avoir le même identifiant). Les identifiants des contenus de la collection “Barzaz Bro Leon” dont l’identifiant = 1, sont récupérés par une sous-requête (SELECT id FROM omeka_items WHERE collection_id = 1).

Nombre de fichiers associés à une collection

Une collection Omeka est constituée de plusieurs contenus auxquels sont associés un ou plusieurs fichiers. La requête SQL suivante permet d’obtenir le nombre total des fichiers associés aux contenus de la collection ayant pour identifiant = 17.

SELECT COUNT(id) FROM omeka_files WHERE item_id IN (SELECT id FROM omeka_items WHERE collection_id = 17)

La requête interroge la table omeka_files qui contient la liste de tous les fichiers uploadés dans Omeka ainsi que le contenu qui leur est associé (un fichier ne peut être associé qu’à un seul contenu dont l’identifiant est stocké dans la colonne item_id de la table omeka_files).

Notez que le SELECT compte les identifiants des fichiers (COUNT(id)). Pour plus de lisibilité, la requête précédente peut aussi s’écrire de la manière suivante :

SELECT COUNT(omeka_files.id) FROM omeka_files WHERE item_id IN (SELECT omeka_items.id FROM omeka_items WHERE collection_id = 17)

Nombre de fichiers dont les contenus ont été créés entre 2020 et 2021

La colonne added de la table omeka_items contient la date de création du contenu (la date de mise en ligne n’est pas sauvegardée), il est aussi possible d’afficher le nombre de fichiers d’une collection (ici la collection dont l’identifiant = 17) associés à des contenus créés entre 2020 et 2021 :

SELECT COUNT(id) FROM omeka_files WHERE item_id IN (SELECT id FROM `omeka_items` WHERE (YEAR(`added`) = 2020 OR YEAR(`added`) = 2021) AND `collection_id` = 17)

Nombre de fichiers des contenus publics classés par collections

On veut connaitre le nombre de fichiers des contenus publics de chaque collection. Ces fichiers ne doivent pas être des fichiers PDF (ils ne doivent pas avoir pour type MIME application/pdf) :

SELECT COUNT(files.id) AS nbrefichiers, collections.id AS collection_id, texts.text
FROM omeka_files AS files

JOIN omeka_items AS items ON items.id = files.item_id
JOIN omeka_collections AS collections ON collections.id = items.collection_id
JOIN omeka_element_texts AS texts ON texts.record_id = collections.id
WHERE texts.record_type = 'Collection' AND texts.element_id = 50 AND files.mime_type != 'application/pdf' AND items.public = 1
GROUP BY collections.id
ORDER BY nbrefichiers

Les informations affichées par le SELECT ainsi que celles filtrées par WHERE sont réparties dans les tables omeka_file, omeka_items, omeka_collections et omeka_element_texts d’où l’utilisation de jointures (JOIN). Notez l’utilisation d’alias (AS) pour lever les ambiguïtés (toutes les tables ont une colonne id, files.id permet de spécifier qu’il s’agit de la colonne id de la table omeka_files, alias files).

Nombre de fichiers associés aux collections publiques de la bibliothèque numérique du Centre de recherche bretonne et celtique. Affichage du résultat de la requête SQL dans le logiciel de gestion de bases de données HeidiSQL.

Trouvez des contenus en doublon à partir de leur cote d’inventaire

Cent trente-neuf items Omeka étaient rattachés à la collection Anatole Le Braz (collection id = 18) sur la bibliothèque numérique du CRBC alors que le train de numérisation prévoyait la mise en ligne de 137 documents. La requête suivante a permis d’afficher les identifiants des contenus rattachés à la même cote. Notez que Dublin Core Identifier est utilisé pour la saisie de la cote d’inventaire mais aussi du DOI Nakala, d’où le NOT LIKE.

La différence venait d’items créés sans fichiers, avec un nombre minimal de métadonnées qui n’avaient pas été supprimés.

L’astuce consiste à grouper les contenus par cote d’inventaire. GROUP_CONCAT permet d’afficher les identifiants des contenus en doublon.

SELECT COUNT(omeka_element_texts.text) AS nbre_doublons, GROUP_CONCAT(omeka_items.id) AS identifiants_doublons, omeka_element_texts.text AS cote_inventaire
FROM omeka_items
RIGHT JOIN omeka_element_texts ON omeka_element_texts.record_id = omeka_items.id
# La collection Anatole Le Braz a pour identifiant = 18

WHERE collection_id = 18 AND omeka_element_texts.record_type = 'Item'
# L'identifiant Omeka du Dublin Core Identifier est = 43
AND omeka_element_texts.element_id = 43
# Le Dublin Core Identifier est aussi utilisé dans la bibliothèque numérique du CRBC pour la saisie du DOI Nakalaqui contient la chaine de caractères 10.34847
AND omeka_element_texts.text NOT LIKE '10.34847%'
# On regroupe les contenus par la cote d'inventaire.
GROUP BY omeka_element_texts.text
HAVING nbre_doublons > 1
ORDER BY nbre_doublons DESC

La requête SQL indique que les contenus ayant pour identifiant 4606 et 4794 ont tous les deux la cote ALB1I2 (Affiché avec le logiciel de gestion de bases de données HeidiSQL).

  1. Pour une introduction au SQL, voir Des fichiers Excel au SQL, introduction aux bases de données relationnelles et Administrez vos bases de données avec MySQL []

1 réflexion sur « Mémento de requêtes SQL pour Omeka Classic »

  1. Ping : Quelques requêtes MySql avec Omeka S – AHP numérique

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.