Corso MariaDB / Progetto finale: ordini, privilegi, backup e ripristino

Questo esercizio finale costruisce un database di ordini, modifica scorte e ordine in una transazione tramite un account limitato e salva lo stato confermato. Ripristinare il backup in un altro database e verificare separatamente struttura, tutti i valori, privilegi e totali: il solo messaggio di importazione riuscita non basta.

Definire ruoli e criteri osservabili di completamento

Usare un nuovo database di esercitazione final48_lab, separato da servizi reali di ordini o pagamenti. Creare clienti, prodotti, ordini e dettagli e confermare l'ordine 105 prima del backup. Distinguere amministratore, account di studio autorizzato a gestire la struttura e account applicativo limitato. Un'applicazione reale richiede anche validazione, parametri delle query, autenticazione e gestione delle richieste ripetute per evitare transazioni duplicate.

La prova delle transazioni InnoDB con COMMIT e ROLLBACK spiega conferma e annullamento. La prova di account e GRANT aiuta a verificare anche le operazioni che devono fallire.

Parte Risultato da verificare
Struttura Quattro tabelle InnoDB con chiavi esterne e vincoli sui valori
Nuovo ordine Scorta del prodotto 1 da 20 a 18; merci dell'ordine 105 pari a 7000
Privilegi Lettura, inserimento di ordini e dettagli e modifica della colonna scorte necessaria
Ripristino Destinazione vuota separata con 4, 4, 5 e 7 righe
Riconciliazione Merci paid per 46000; campi e vincoli corrispondenti

Creare un database autonomo con tutti i dati iniziali

Nella connessione amministrativa al server di prova controllare SELECT VERSION();, SHOW DATABASES e gli account disponibili. Accertare che final48_lab, final48_restore e 'final48_app'@'127.0.0.1' non siano usati. Se esistono, scegliere altri nomi e aggiornare tutti i blocchi, senza sovrascrivere né eliminare oggetti preesistenti.

CREATE DATABASE final48_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE final48_lab;
CREATE TABLE c48_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c48_order_customer FOREIGN KEY (customer_id)
  REFERENCES c48_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c48_item_order FOREIGN KEY (order_id)
  REFERENCES c48_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c48_item_product FOREIGN KEY (product_id)
  REFERENCES c48_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c48_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c48_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c48_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c48_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Nomi di clienti, città e prodotti conservano i testi coreani della fonte. I prodotti 1–4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 appartengono a Seoul, il cliente 2 a Busan e il cliente 4 a Daejeon. Conservare i testi UTF-8 per mantenere coerenti i confronti.

Se necessario concedere i privilegi seguenti a course_user dalla connessione amministrativa. I privilegi su shopcourse non si estendono automaticamente al nuovo database.

GRANT ALL PRIVILEGES ON final48_lab.* TO 'course_user'@'127.0.0.1';

I dati iniziali comprendono quattro clienti, quattro prodotti, quattro ordini e sei dettagli. Controllare chiavi esterne e vincoli di quantità, prezzi e scorte con SHOW CREATE TABLE. Prezzo di catalogo corrente e unit_price, prezzo registrato nel dettaglio dell'acquisto, hanno significati diversi: il valore storico non deve cambiare con il catalogo.

Collegare un account limitato ai compiti applicativi

Come amministratore sostituire la password dimostrativa con un valore univoco prima di creare l'account. Può leggere i dati di prova, aggiungere ordini e dettagli e modificare le scorte. Eliminare tabelle, gestire account, cambiare prezzi o eliminare ordini non rientra nei suoi compiti. Esaminare SHOW GRANTS privatamente perché può contenere informazioni di autenticazione.

CREATE USER 'final48_app'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON final48_lab.* TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_orders TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_items TO 'final48_app'@'127.0.0.1';
GRANT UPDATE(stock) ON final48_lab.c48_products TO 'final48_app'@'127.0.0.1';
SHOW GRANTS FOR 'final48_app'@'127.0.0.1';

Aprire una nuova connessione dal terminale del sistema operativo con l'account applicativo. Inserire la password al prompt e adeguare la porta al server di prova:

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=final48_app --password --default-character-set=utf8mb4 final48_lab

Nel client controllare SELECT CURRENT_USER(); e una query sui prodotti. Un tentativo di ALTER TABLE senza privilegi deve essere rifiutato. In un'applicazione reale limitare tabelle, colonne e compiti secondo necessità; SELECT sull'intero database di prova è una scelta esplicita di questo esempio. Il riferimento ufficiale di GRANT descrive ambiti di database, tabella e colonna.

Controllare e confermare insieme scorte, ordine e dettagli

Eseguire i passaggi nella stessa connessione applicativa. SELECT FOR UPDATE blocca la riga prodotto durante la transazione; l'UPDATE condizionale tenta poi di ridurre la scorta di due unità.

USE final48_lab;
START TRANSACTION;
SELECT product_id,price,stock FROM c48_products WHERE product_id=1 FOR UPDATE;
UPDATE c48_products SET stock=stock-2 WHERE product_id=1 AND stock>=2;
SELECT ROW_COUNT() AS reserved_rows;

Proseguire soltanto se reserved_rows vale 1. Se nessuna riga cambia o si verifica un errore, eseguire ROLLBACK; nella stessa connessione e non inserire ordine e dettagli. Controllare ROW_COUNT subito dopo la modifica, come nell'esempio, per evitare che un'altra istruzione sostituisca il risultato da osservare.

INSERT INTO c48_orders(order_id,customer_id,ordered_at,status,shipping_fee)
VALUES(105,4,'2026-03-01 10:00:00','paid',1000);
INSERT INTO c48_items(order_id,product_id,quantity,unit_price)
VALUES(105,1,2,3500);
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c48_items WHERE order_id=105 GROUP BY order_id;

Con i dati iniziali invariati, la prenotazione deve modificare una riga e le merci dell'ordine 105 devono valere 7000.00. Se un'istruzione fallisce, fermarsi e annullare l'intero tentativo. È una prova sorvegliata con input noti: un programma deve eseguire autonomamente i rami condizionali e associare i valori mediante parametri del driver.

Soltanto dopo il successo dei controlli confermare e rileggere i risultati:

COMMIT;
SELECT stock FROM c48_products WHERE product_id=1;
SELECT COUNT(*) FROM c48_orders;
SELECT COUNT(*) FROM c48_items;

La scorta del prodotto 1 è ora 18, gli ordini sono cinque e i dettagli sette. Ripetere l'ID 105 genera un errore di chiave duplicata. Gestire esplicitamente richieste ripetute, senza nasconderle con IGNORE o REPLACE. Se il tentativo fallito ha già ridotto la scorta, ROLLBACK deve annullare quella riduzione prima di riprovare. Un ROLLBACK successivo a un commit riuscito non annulla l'ordine già confermato, secondo le regole di START TRANSACTION, COMMIT e ROLLBACK.

Salvare lo stato confermato e preparare un'altra destinazione

Usare course_user con i privilegi di backup, anziché l'account applicativo limitato. In PowerShell scegliere un file nuovo, controllare il codice di uscita ed esaminare il dump per istruzioni che cambiano database. Eseguire nel terminale del sistema operativo:

New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$courseBackupPath='C:/mariadb-lab/final48-checked.sql'
if (Test-Path -LiteralPath $courseBackupPath) { throw 'Choose a new backup filename.' }
mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=$courseBackupPath final48_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed; do not restore this file.' }
Get-Item -LiteralPath $courseBackupPath | Select-Object Name,Length
Get-FileHash -LiteralPath $courseBackupPath -Algorithm SHA256

Dalla connessione amministrativa creare il database di ripristino, dopo aver verificato che non sia usato, e concedere i privilegi di esercitazione necessari:

CREATE DATABASE final48_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON final48_restore.* TO 'course_user'@'127.0.0.1';

Dal terminale collegare il client alla destinazione con arresto di SOURCE in caso di errore:

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

Nel client verificare nome del database e lista vuota delle tabelle prima di importare il file attendibile. Se la destinazione contiene già tabelle, fermarsi.

SELECT DATABASE();
SHOW TABLES;
SOURCE C:/mariadb-lab/final48-checked.sql;

Restano valide le condizioni di mariadb-dump per lo snapshot InnoDB e le modifiche strutturali concorrenti. La guida a ripristino e verifica descrive compatibilità dei client e fallimenti parziali. Il dump non ripristina tutti gli account o la configurazione del server. Non indirizzare automaticamente l'account applicativo alla destinazione di prova.

Alternativa di backup sul terminale Ubuntu: scegliere questo blocco oppure PowerShell. La subshell tra parentesi limita impostazioni dei permessi e protezione dalla sovrascrittura alla sola operazione.

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/final48-checked.sql"
  if mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 \
      --user=course_user --password --default-character-set=utf8mb4 \
      --single-transaction --quick --skip-lock-tables final48_lab > "$backup_file"; then
    test -s "$backup_file" || exit 1
    sha256sum "$backup_file"
  else
    printf '%s\n' 'Backup failed; preserve the partial file for diagnosis.' >&2
    exit 1
  fi
)

Un file di destinazione esistente causa un errore senza essere sovrascritto. Non ripristinare un file parziale dopo un fallimento: controllare la causa e usare un nome nuovo al tentativo successivo. Dimensione e SHA-256 identificano il file senza dimostrarne il corretto ripristino.

Per ripristinare tramite Ubuntu aprire la stessa destinazione con il comando seguente. Sostituire SOURCE con il percorso assoluto effettivo: learner è soltanto un nome dimostrativo.

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore
SELECT DATABASE();
SHOW TABLES;
SOURCE /home/learner/mariadb-lab/final48-checked.sql;

Eseguire una sola alternativa di ripristino per destinazione. L'arresto di SOURCE impedisce le istruzioni successive nel file, ma non annulla oggetti o modifiche precedenti. Dopo un fallimento correggere la causa e riprovare in una nuova destinazione vuota.

Per errori di Transport Layer Security, o TLS, la protezione cifrata della connessione, controllare versioni, nome del certificato, autorità attendibile ed eventuali certificati client secondo i requisiti ufficiali di cifratura delle connessioni. Applicare le impostazioni corrette agli strumenti senza usare abitualmente la disattivazione della verifica. Il riferimento di mariadb-dump descrive opzioni e prerequisiti.

Confrontare fonte, ripristino e operazioni rifiutate

SELECT (SELECT COUNT(*) FROM final48_restore.c48_customers) AS customers,
 (SELECT COUNT(*) FROM final48_restore.c48_products) AS products,
 (SELECT COUNT(*) FROM final48_restore.c48_orders) AS orders_count,
 (SELECT COUNT(*) FROM final48_restore.c48_items) AS detail_rows;
SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM final48_restore.c48_orders o JOIN final48_restore.c48_items i ON i.order_id=o.order_id
WHERE o.status='paid';
SELECT stock FROM final48_restore.c48_products WHERE product_id=1;
SHOW CREATE TABLE final48_restore.c48_items;

I conteggi attesi sono 4, 4, 5 e 7, le merci paid valgono 46000.00 e la scorta del prodotto 1 è 18. Confrontare tutti i campi per chiave, vincoli e JOIN importanti. Conteggi uguali o un'importazione riuscita non bastano. Provare separatamente il rifiuto di chiavi esterne inesistenti, chiavi duplicate e modifiche non autorizzate.

Conservare versioni, hash del dump, query e risultati di verifica e ambito dei privilegi. Chiudere le sessioni ed esaminare proprietà e conservazione prima di rimuovere account o database. L'esercizio non elimina automaticamente fonte, backup o database di verifica.