Come riempire una tabella con dati di test: dagli INSERT a mano alla generazione con SQL

Quasi tutti i DBA che conosco sono stati prima sviluppatori, o lo sono ancora. Io programmavo già prima di dedicarmi ai database: nel 2008 ho iniziato la prima versione di Calíope, e lavoro come DBA dal 2016. Di quei primi anni da sviluppatore mi è rimasto un problema che non ha niente di sofisticato e che abbiamo avuto tutti: crei una tabella nuova e ti servono dei dati dentro per vedere l'applicazione funzionare.

Questo articolo ripercorre i modi in cui l'ho risolto, nell'ordine in cui li ho imparati. Gli esempi sono scritti per MySQL, e ogni blocco di codice dice su quali motori funziona così com'è.

Usano tutti questa tabella:

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
);

Negli altri motori cambia solo l'autoincremento: GENERATED ALWAYS AS IDENTITY in PostgreSQL e Oracle, IDENTITY(1,1) in SQL Server e INTEGER PRIMARY KEY in SQLite. ClickHouse non ha l'autoincremento.

1. Un INSERT dopo l'altro

All'inizio, per inesperienza e perché non sapevo che ci fosse un altro modo, inserivo i dati a mano. Un INSERT, poi un altro, poi un altro:

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');

In Oracle la data si scrive DATE '2010-03-01': una stringa semplice viene interpretata con il formato di data della sessione, e con quello predefinito fallisce.

Funziona, ma ha due problemi. Scrivere più di una manciata di righe costa molta fatica, e i dati sono sempre gli stessi. Con tre clienti non ti accorgi che la paginazione è sbagliata, che l'elenco è lento o che un nome lungo rompe la schermata.

2. La tabella che copia se stessa

Poi ho scoperto che un INSERT non ha bisogno di VALUES: può essere seguito da un SELECT. E se il SELECT legge dalla stessa tabella in cui si inserisce, la tabella raddoppia:

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

Ogni esecuzione raddoppia le righe. Le tre iniziali diventano 6, poi 12, 24… e dopo 18 esecuzioni sono già 786.432. La colonna id non è nell'elenco, quindi il motore numera le copie.

Il problema è che i dati restano le stesse tre righe ripetute, e se email ha un indice univoco la seconda esecuzione fallisce. Si risolve facendo variare i valori nella copia stessa:

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;

Negli altri motori cambiano la funzione casuale e l'aritmetica delle date:

• PostgreSQL: random() e alta + (random() * 365)::int.
• SQLite: abs(random()) % 1000 e date(alta, '+' || (abs(random()) % 365) || ' days').
• SQL Server: RAND() restituisce lo stesso valore per tutte le righe dell'istruzione. Perché cambi da una riga all'altra si usa ABS(CHECKSUM(NEWID())) % 1000.
• Oracle: DBMS_RANDOM.VALUE(0, 1000).
• ClickHouse: rand() % 1000.

Un avvertimento: la crescita è esponenziale, e lo è anche ciò che scrive il log delle transazioni. Due esecuzioni di troppo e la tabella diventa quattro volte più grande.

3. Generare le righe con SQL

Con gli anni ho imparato che non serve partire da righe già esistenti: il motore stesso può generare una serie di numeri, e da ogni numero nasce una riga. In MySQL si fa con una CTE ricorsiva:

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 prima riga serve perché MySQL interrompe la ricorsione a 1000 livelli per impostazione predefinita.

Ogni motore ha il suo modo di generare la serie, e in alcuni è molto più breve:

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);

In SQLite, e in SQL Server prima del 2022, si usa la CTE ricorsiva:

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

SQL Server non accetta RECURSIVE e interrompe la ricorsione a 100 livelli. Il limite si toglie con un'opzione alla fine:

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

Da quando si può?

Nel 2008 non sapevo che esistesse, e in MySQL, che era quello che usavo, in effetti non esisteva. Questa è la cronologia, dalla più vecchia alla più recente:

• PostgreSQL 8.0, gennaio 2005: generate_series. Le CTE ricorsive sono arrivate dopo, nella 8.4 (2009).
• SQL Server 2005, novembre 2005: CTE ricorsive. GENERATE_SERIES è arrivato solo con SQL Server 2022.
• Oracle: CONNECT BY LEVEL su dual si usava già come generatore di righe in Oracle 10g (2004). La CTE ricorsiva standard è arrivata con la 11g R2 (2009).
• SQLite 3.8.3, febbraio 2014: WITH RECURSIVE.
• MariaDB 10.0 (2014): il motore Sequence e le sue tabelle seq_1_to_N. Le CTE ricorsive, in MariaDB 10.2 (2017).
• ClickHouse: numbers() c'è da quando è stato pubblicato come open source, nel 2016.
• MySQL 8.0, aprile 2018: CTE ricorsive. Prima non c'era modo di generare una serie con una sola istruzione.

In altre parole, nel 2008 PostgreSQL, SQL Server e Oracle potevano già farlo. In MySQL si è dovuto aspettare altri dieci anni, e nel frattempo restava raddoppiare la tabella, oppure una procedura con un ciclo.

4. Una procedura che legge la tabella

Quando ero diventato un po' più professionale, ho scritto una stored procedure che faceva quel lavoro al posto mio: leggeva le colonne della tabella e i loro tipi da information_schema, e costruiva un INSERT con un valore casuale adatto a ciascuna.

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);

Gli altri motori hanno il loro catalogo delle colonne (information_schema in PostgreSQL e SQL Server, ALL_TAB_COLUMNS in Oracle, pragma_table_info in SQLite) e il loro linguaggio procedurale, quindi bisogna riscriverla per ciascuno.

5. Oggi, con Calíope

Quella procedura è l'antenata di una funzione di Calíope. In Cataloghi, nell'elenco delle tabelle, il menu contestuale di una tabella ha Popola con dati dummy (clic destro sul Mac, tocco prolungato sull'iPad). Scegli quante righe vuoi, da 1 a 5000, e Calíope:

• legge le colonne e i loro tipi dal catalogo del server, compresi i valori di un ENUM;
• salta le colonne autoincrementali e quelle calcolate, che il motore riempie da solo;
• genera un valore di esempio valido per il tipo di ogni colonna, e te ne mostra uno prima di iniziare;
• inserisce a blocchi di 50 righe, con una barra di avanzamento, e se un blocco fallisce ti dice perché: un valore fuori intervallo, un vincolo CHECK, un duplicato.

Nelle colonne che sono chiavi esterne, anche i valori sono casuali. In MySQL, MariaDB e PostgreSQL, Calíope disattiva il controllo durante il caricamento, quindi possono restare righe che puntano a un padre che non esiste, utili per provare come risponde l'applicazione a un JOIN senza corrispondenza. In SQL Server e Oracle il controllo resta attivo, e un blocco con un valore che non esiste nella tabella padre fallisce con il suo avviso.

Funziona in MySQL, MariaDB, PostgreSQL, SQL Server, Oracle e ClickHouse.

Cosa uso per cosa

Nessuno di questi modi è sparito dal mio lavoro:

• Pochi INSERT a mano quando mi servono dati precisi che conosco, come il caso che riproduce un bug.
• La serie generata con SQL quando mi servono volumi e controllare come si distribuiscono i valori: centomila clienti con l'80 % delle iscrizioni nell'ultimo anno.
• Calíope quando voglio solo che la tabella contenga qualcosa per vedere la schermata funzionare.