Comment remplir une table avec des données de test : des INSERT à la main à leur génération en SQL

Presque tous les DBA que je connais ont d'abord été développeurs, ou le sont encore. J'écrivais du code avant de me consacrer aux bases de données : j'ai commencé la première version de Calíope en 2008, et je travaille comme DBA depuis 2016. De mes premières années de développeur, je garde un problème qui n'a rien de sophistiqué et que nous avons tous connu : on crée une nouvelle table et il faut des données dedans pour voir l'application fonctionner.

Cet article passe en revue les façons dont je l'ai résolu, dans l'ordre où je les ai apprises. Les exemples sont écrits pour MySQL, et chaque bloc de code indique sur quels moteurs il s'exécute tel quel.

Ils utilisent tous cette table :

CREATE TABLE clientes (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100)  NOT NULL,
    email  VARCHAR(150)  NOT NULL,
    saldo  DECIMAL(10,2) NOT NULL DEFAULT 0,
    alta   DATE          NOT NULL
);

Sur les autres moteurs, seul l'auto-incrément change : GENERATED ALWAYS AS IDENTITY sur PostgreSQL et Oracle, IDENTITY(1,1) sur SQL Server et INTEGER PRIMARY KEY sur SQLite. ClickHouse n'a pas d'auto-incrément.

1. Un INSERT après l'autre

Au début, par inexpérience et parce que je ne savais pas qu'il existait une autre façon de faire, je saisissais les données à la main. Un INSERT, puis un autre, puis un autre :

INSERT INTO clientes (nombre, email, saldo, alta)
VALUES ('Ana Mora', 'ana@ejemplo.com', 150.00, '2010-03-01');

INSERT INTO clientes (nombre, email, saldo, alta)
VALUES ('Luis Rojas', 'luis@ejemplo.com', 80.50, '2010-03-02');

INSERT INTO clientes (nombre, email, saldo, alta)
VALUES ('Sofía Vargas', 'sofia@ejemplo.com', 0.00, '2010-03-05');

Sur Oracle, la date s'écrit DATE '2010-03-01' : une simple chaîne est interprétée avec le format de date de la session, et avec celui par défaut elle échoue.

Ça marche, mais il y a deux problèmes. Écrire plus d'une poignée de lignes demande beaucoup d'efforts, et les données sont toujours les mêmes. Avec trois clients, on ne voit pas que la pagination est cassée, que la liste est lente ou qu'un nom long casse l'écran.

2. La table qui se copie elle-même

Plus tard, j'ai découvert qu'un INSERT n'a pas besoin de VALUES : il peut être suivi d'un SELECT. Et si le SELECT lit la même table que celle où l'on insère, la table double :

INSERT INTO clientes (nombre, email, saldo, alta)
SELECT nombre, email, saldo, alta
FROM clientes;

Chaque exécution double les lignes. Les trois du départ deviennent 6, puis 12, 24… et après 18 exécutions il y en a déjà 786 432. La colonne id n'est pas dans la liste, donc le moteur numérote les copies.

Le problème, c'est que les données restent les trois mêmes lignes répétées, et si email a un index unique, la deuxième exécution échoue. La solution consiste à faire varier les valeurs dans la copie elle-même :

INSERT INTO clientes (nombre, email, saldo, alta)
SELECT CONCAT(nombre, ' ', id),
       CONCAT(id, '.', email),
       ROUND(RAND() * 1000, 2),
       alta + INTERVAL FLOOR(RAND() * 365) DAY
FROM clientes;

Sur les autres moteurs, la fonction aléatoire et l'arithmétique des dates changent :

• PostgreSQL : random() et alta + (random() * 365)::int.
• SQLite : abs(random()) % 1000 et date(alta, '+' || (abs(random()) % 365) || ' days').
• SQL Server : RAND() renvoie la même valeur pour toutes les lignes de l'instruction. Pour qu'elle change d'une ligne à l'autre, on utilise ABS(CHECKSUM(NEWID())) % 1000.
• Oracle : DBMS_RANDOM.VALUE(0, 1000).
• ClickHouse : rand() % 1000.

Un avertissement : la croissance est exponentielle, et ce qu'écrit le journal des transactions l'est aussi. Deux exécutions de trop et la table est quatre fois plus grosse.

3. Générer les lignes en SQL

Avec les années, j'ai appris qu'il n'est pas nécessaire de partir de lignes existantes : le moteur lui-même peut générer une série de nombres, et chaque nombre donne une ligne. Sur MySQL, on le fait avec une CTE récursive :

SET SESSION cte_max_recursion_depth = 100000;

INSERT INTO clientes (nombre, email, saldo, alta)
WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 100000
)
SELECT CONCAT('Cliente ', n),
       CONCAT('cliente', n, '@ejemplo.com'),
       ROUND(RAND() * 10000, 2),
       DATE '2020-01-01' + INTERVAL FLOOR(RAND() * 2000) DAY
FROM serie;

La première ligne est nécessaire parce que MySQL arrête la récursion à 1000 niveaux par défaut.

Chaque moteur a sa propre façon de générer la série, et sur certains elle est beaucoup plus courte :

SELECT seq FROM seq_1_to_100000;

SELECT n FROM generate_series(1, 100000) AS n;

SELECT value FROM GENERATE_SERIES(1, 100000);

SELECT LEVEL FROM dual CONNECT BY LEVEL <= 100000;

SELECT number + 1 FROM numbers(100000);

Sur SQLite, et sur SQL Server avant 2022, on utilise la CTE récursive :

WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 100000
)
SELECT n FROM serie;

SQL Server ne prend pas RECURSIVE, et il arrête la récursion à 100 niveaux. Une option à la fin supprime la limite :

WITH serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 100000
)
SELECT n FROM serie
OPTION (MAXRECURSION 0);

Depuis quand est-ce possible ?

En 2008, je ne savais pas que cela existait, et sur MySQL, que j'utilisais, ça n'existait d'ailleurs pas. Voici la chronologie, de la plus ancienne à la plus récente :

• PostgreSQL 8.0, janvier 2005 : generate_series. Les CTE récursives sont arrivées plus tard, dans la 8.4 (2009).
• SQL Server 2005, novembre 2005 : CTE récursives. GENERATE_SERIES n'est arrivé qu'avec SQL Server 2022.
• Oracle : CONNECT BY LEVEL sur dual servait déjà de générateur de lignes dans Oracle 10g (2004). La CTE récursive standard est arrivée avec la 11g R2 (2009).
• SQLite 3.8.3, février 2014 : WITH RECURSIVE.
• MariaDB 10.0 (2014) : le moteur Sequence et ses tables seq_1_to_N. Les CTE récursives, dans MariaDB 10.2 (2017).
• ClickHouse : numbers() existe depuis sa publication en open source, en 2016.
• MySQL 8.0, avril 2018 : CTE récursives. Avant cela, il n'y avait aucun moyen de générer une série en une seule instruction.

Autrement dit, en 2008, PostgreSQL, SQL Server et Oracle savaient déjà le faire. Sur MySQL, il a fallu attendre dix ans de plus, et en attendant, il restait à doubler la table, ou une procédure avec une boucle.

4. Une procédure qui lit la table

Une fois devenu un peu plus professionnel, j'ai écrit une procédure stockée qui faisait ce travail à ma place : elle lisait les colonnes de la table et leurs types dans information_schema, et construisait un INSERT avec une valeur aléatoire adaptée à chacune.

DELIMITER //
CREATE PROCEDURE poblar(IN p_tabla VARCHAR(64), IN p_filas INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE v_columnas TEXT;
    DECLARE v_valores  TEXT;

    SELECT GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY ordinal_position),
           GROUP_CONCAT(
               CASE
                   WHEN data_type IN ('int', 'bigint', 'smallint', 'tinyint')
                       THEN 'FLOOR(RAND() * 1000)'
                   WHEN data_type IN ('decimal', 'double', 'float')
                       THEN 'ROUND(RAND() * 1000, 2)'
                   WHEN data_type IN ('date', 'datetime', 'timestamp')
                       THEN 'CURRENT_DATE - INTERVAL FLOOR(RAND() * 2000) DAY'
                   ELSE CONCAT('LEFT(SHA2(RAND(), 256), ',
                               LEAST(IFNULL(character_maximum_length, 32), 32), ')')
               END
               ORDER BY ordinal_position)
      INTO v_columnas, v_valores
      FROM information_schema.columns
     WHERE table_schema = DATABASE()
       AND table_name = p_tabla
       AND extra NOT LIKE '%auto_increment%';

    SET @sql = CONCAT('INSERT INTO `', p_tabla, '` (', v_columnas, ') VALUES (', v_valores, ')');
    PREPARE sentencia FROM @sql;
    WHILE i < p_filas DO
        EXECUTE sentencia;
        SET i = i + 1;
    END WHILE;
    DEALLOCATE PREPARE sentencia;
END //
DELIMITER ;

CALL poblar('clientes', 1000);

Les autres moteurs ont leur propre catalogue de colonnes (information_schema sur PostgreSQL et SQL Server, ALL_TAB_COLUMNS sur Oracle, pragma_table_info sur SQLite) et leur propre langage procédural, il faut donc la réécrire pour chacun.

5. Aujourd'hui, avec Calíope

Cette procédure est l'ancêtre d'une fonction de Calíope. Dans Catalogues, dans la liste des tables, le menu contextuel d'une table propose Remplir avec des données fictives (clic droit sur le Mac, appui prolongé sur l'iPad). Vous choisissez le nombre de lignes, de 1 à 5000, et Calíope :

• lit les colonnes et leurs types dans le catalogue du serveur, y compris les valeurs d'un ENUM ;
• ignore les colonnes auto-incrémentées et calculées, que le moteur remplit lui-même ;
• génère une valeur d'exemple valide pour le type de chaque colonne, et vous en montre une avant de commencer ;
• insère par lots de 50 lignes, avec une barre de progression, et si un lot échoue, il vous dit pourquoi : une valeur hors limites, une contrainte CHECK, un doublon.

Dans les colonnes qui sont des clés étrangères, les valeurs sont aussi aléatoires. Sur MySQL, MariaDB et PostgreSQL, Calíope désactive la vérification pendant le chargement, si bien qu'il peut rester des lignes qui pointent vers un parent inexistant, utiles pour tester comment l'application réagit à un JOIN sans correspondance. Sur SQL Server et Oracle, la vérification reste active, et un lot contenant une valeur absente de la table parente échoue avec son avertissement.

Cela fonctionne sur MySQL, MariaDB, PostgreSQL, SQL Server, Oracle et ClickHouse.

Ce que j'utilise pour quoi

Aucune de ces méthodes n'a disparu de mon travail :

• Quelques INSERT à la main quand j'ai besoin de données précises que je connais, comme le cas qui reproduit un bug.
• La série générée en SQL quand j'ai besoin de volume et de contrôler la répartition des valeurs : cent mille clients avec 80 % des inscriptions dans la dernière année.
• Calíope quand je veux seulement que la table contienne quelque chose pour voir l'écran fonctionner.