Free PlyxSQL© - 1.0.0.107 Super ETL

In breve. Il generatore SQL di TFireDACQueryBuilder, il query builder visuale per C++Builder e FireDAC, è stato rivisto a fondo. Abbiamo corretto dodici bug segnalati, dall'UNION scritta dopo il punto e virgola al DATEADD sbagliato per SQL Server. Nel farlo il componente ha cambiato impostazione: la query non viene più assemblata concatenando stringhe ma costruita a partire da un modello, e ogni dialetto ha una tabella di capability che dice cosa il database supporta davvero.

Un query builder visuale fa una promessa semplice: disegni tabelle e relazioni, ottieni SQL corretto. Il problema è che "corretto" dipende da tre cose: la semantica di ciò che hai disegnato, la sintassi del database di destinazione e la versione di quel database. Il vecchio generatore di TFireDACQueryBuilder gestiva bene i casi comuni, ma in parecchi scenari produceva SQL apparentemente valido che il database rifiutava o, peggio, eseguiva restituendo risultati diversi da quelli attesi.

In questo articolo ripercorriamo tutte le correzioni e le migliorie: cosa non andava, perché, e come è stato risolto.

Riepilogo delle correzioni

#	Problema	Gravità	Soluzione
1	UNION/INTERSECT/EXCEPT generati dopo il ;	Critica	Modello Query → SelectBlock → SetOperation, terminatore unico
2	Ordine dei JOIN dipendente dall'ordine delle relazioni	Critica	JoinTree esplicito, verso rispettato, OUTER JOIN annidati
3	Un ciclo nel grafo trattato come errore	Alta	Nuove verifiche strutturali mirate
4	Validate() con semantica invertita	Alta	true = valida, nuovo HasValidationErrors()
5	IN ('1,2,3') invece di IN (1,2,3)	Alta	Lista di valori tipizzati
6	Identificatori quotati senza escaping	Alta	QuoteIdentifier(name, dialect) centralizzata
7	Schema assente nei DML	Alta	QualifiedTableName(T) ovunque
8	NOLOCK/UPDLOCK mai generati davvero	Alta	Hint applicati al nodo tabella, anche per singola tabella
9	APPLY / LATERAL non validati per dialetto	Alta	Capability matrix dei JOIN + popup che disabilita
10	DATEADD SQL Server con sintassi errata	Alta	DATEADD(datepart, number, date) e sintassi per ogni dialetto
11	LPAD su SQL Server emulato con FORMAT()	Alta	Emulazione con REPLICATE e semantica esatta
12	Funzioni non supportate dal dialetto generate comunque	Alta	TDialectCapabilities con versione minima

Il cambio di impostazione: dal testo al modello

La causa comune dei primi tre bug era una sola: GenerateSQL() costruiva la query aggiungendo pezzi di stringa uno dopo l'altro, e ricavava l'ordine dei JOIN visitando il grafo delle relazioni ogni volta da capo. Così non c'era un punto in cui la struttura della query esistesse come dato.

Ora il diagramma viene tradotto una volta sola in un modello esplicito:

Query
 ├── SelectBlock            (tabelle + JoinTree esplicito)
 ├── SetOperation           (UNION / INTERSECT / EXCEPT)
 │    ├── SelectBlock
 │    └── SetOperation
 │         └── SelectBlock

Il modello viene prodotto da BuildQueryPlan() e ha due soli consumatori: GenerateSQL(), che lo trasforma in testo, e RunValidation(), che ne riporta la diagnostica. In questo modo quello che il validatore controlla è esattamente quello che il generatore scrive.

1. UNION dopo il punto e virgola

Il terminatore veniva aggiunto alla fine della SELECT principale, prima di generare le operazioni su insiemi:

Prima

SELECT ... FROM A
;
UNION
SELECT ... FROM B;

Il primo ; chiude lo statement e il resto diventa un frammento che inizia con UNION: sintassi non valida. Oggi il terminatore è unico e le set operation sono nodi del modello, anche annidati:

Dopo

SELECT "A"."id", "A"."name" FROM "A"
UNION
(
  SELECT "B"."id", "B"."name" FROM "B"
  INTERSECT
  SELECT "C"."id", "C"."name" FROM "C"
)
ORDER BY
  2 ASC
LIMIT 10;

Il modello si è portato dietro anche altre correzioni:

• dopo una UNION l'ORDER BY usa la posizione della colonna o il suo alias, perché riferimenti come "A"."name" non sono più validi;
• su SQL Server e Firebird il limite di righe va in fondo alla query: TOP/FIRST in testa limiterebbero solo il primo ramo;
• su Oracle EXCEPT diventa MINUS;
• le tabelle di un ramo della UNION non finiscono più anche tra le colonne del SELECT principale.

2. I JOIN seguono la semantica, non l'ordine di salvataggio

Questo era il bug più insidioso, perché non produceva errori: produceva risultati diversi. Prendiamo un diagramma con due relazioni:

A --LEFT--> B
C --INNER-> B

A seconda dell'ordine in cui le relazioni erano salvate, il vecchio generatore poteva scrivere:

Prima

FROM A
LEFT JOIN B ON A.b_id = B.id
INNER JOIN C ON B.id = C.b_id

L'INNER JOIN finale scarta tutte le righe di A senza corrispondenza in B: il LEFT JOIN diventa di fatto un INNER JOIN. Ora ogni SELECT ha un JoinTree esplicito, e un sottoalbero agganciato con un OUTER JOIN che contiene join non-LEFT viene reso annidato:

Dopo

FROM "A"
LEFT JOIN (
    "B"
    INNER JOIN "C"
      ON "B"."id" = "C"."b_id"
  )
  ON "A"."b_id" = "B"."id";

Abbiamo eseguito le due forme su SQLite con gli stessi dati: la vecchia restituiva 1 riga, la nuova 3, cioè tutte le righe di A come richiede il LEFT JOIN disegnato.

Altre regole del JoinTree:

• Il verso viene rispettato. B LEFT A raggiunto partendo da A diventa A RIGHT JOIN B; prima veniva scritto LEFT JOIN, invertendo la semantica.
• APPLY e LATERAL non si invertono mai, perché il lato destro dipende dal sinistro.
• RIGHT e FULL vengono resi per ultimi fra le tabelle allo stesso livello, così il lato preservato resta tale.
• Le relazioni che chiudono un ciclo non vengono più scartate in silenzio: la loro condizione finisce in AND nella clausola ON corretta.

3. Un ciclo non è un errore

HasCycle() segnalava come errore qualunque ciclo nel grafo delle relazioni. Ma A → B → C → A è un insieme di JOIN perfettamente legittimo:

FROM "A"
INNER JOIN "B" ON "A"."id" = "B"."a_id"
INNER JOIN "C" ON "B"."id" = "C"."b_id"
 AND "C"."id" = "A"."c_id";

Il controllo sui cicli è stato eliminato e sostituito da verifiche che individuano problemi reali:

• JOIN duplicati, anche se dichiarati con tipi di join diversi;
• JOIN impossibili: campo che non esiste più, tabella unita a se stessa (per un self-join serve aggiungere la tabella due volte con alias diversi), APPLY che andrebbe invertito;
• tabelle scollegate, escluse dalla query con avviso;
• conflitti di alias nello stesso SELECT;
• set operation con numero di colonne diverso fra i rami.

4. Validate(): il nome ora dice la verità

Il vecchio Validate() restituiva true quando c'erano errori. Un chiamante che scriveva la cosa più naturale otteneva l'esatto contrario:

if(builder->Validate())
    ExecuteQuery();   // eseguita proprio quando la query NON è valida

Il contratto adesso è esplicito:

• Validate() restituisce true se la query è valida;
• HasValidationErrors() restituisce true se ci sono errori, cioè la vecchia semantica con un nome non ambiguo;
• HasErrors() fa lo stesso controllo senza rieseguire la validazione.

Attenzione, cambio di comportamento. Se il vostro codice chiamava Validate() aspettandosi true in presenza di errori, quel controllo ora dà il risultato opposto. Sostituitelo con HasValidationErrors().

5. IN con più valori

Il valore di una condizione IN veniva trattato come un unico literal:

Prima

"A"."code" IN ('1,2,3')

Ora il valore diventa una lista in cui ogni elemento ha il proprio tipo (literal, parametro o subquery). Le virgole dentro gli apici vengono rispettate e il formato salvato nei progetti non cambia:

Dopo

1,2,3               →  IN (1, 2, 3)
A, B, C             →  IN ('A', 'B', 'C')
'A','B,C','O''Hara' →  IN ('A', 'B,C', 'O''Hara')
:p1, @p2            →  IN (:p1, :p2)
SELECT id FROM t    →  IN (SELECT id FROM t)
(lista vuota)       →  1 = 0

6. Escaping degli identificatori

QI() racchiudeva il nome tra delimitatori senza raddoppiare il delimitatore stesso: un campo chiamato Nome"Campo produceva SQL non valido. C'è ora un'unica funzione, QuoteIdentifier(name, dialect):

Dialetto	Delimitatore	Esempio
SQL Server	[ ]	a]b → [a]]b]
MySQL	` `	a`b → `a``b`
ANSI (PostgreSQL, Oracle, Firebird, SQLite…)	" "	Nome"Campo → "Nome""Campo"

La funzione è usata ovunque: DDL, DML, WHERE, alias, ORDER BY, subquery, PIVOT. Sono stati corretti anche i punti che quotavano a mano, come le tabelle temporanee, dove "T"_TMP è diventato "T_TMP".

7. Schema qualificato anche nei DML

La SELECT scriveva correttamente "dbo"."Customers", ma INSERT, UPDATE, DELETE e gli altri generatori usavano solo il nome della tabella. Con più schemi il rischio era di modificare la tabella sbagliata. Ora c'è QualifiedTableName(T), usata da SELECT, INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DDL, VIEW e TEMP TABLE:

INSERT INTO [dbo].[Customers] ( [id], [name] ) VALUES ( @id, @name );
UPDATE [dbo].[Customers] SET [name] = @name WHERE [id] = @id;
TRUNCATE TABLE [dbo].[Customers];

• Sequence e trigger di PostgreSQL e Oracle vengono creati nello schema della tabella.
• Il nome di una vista si può scrivere come dbo.V_Clienti: le due parti vengono quotate separatamente.
• Le tabelle temporanee sono qualificate solo dove il database lo permette: PostgreSQL, SQLite e SQL Server (#tmp) rifiutano un nome qualificato.
• UPDATE e DELETE usavano l'alias della tabella nel WHERE senza dichiararlo nello statement: ora le colonne non sono qualificate.

8. Lock hint reali, anche per singola tabella

Selezionando NOLOCK il vecchio generatore aggiungeva soltanto un commento:

Prima

-- NOTE: use WITH (NOLOCK) in FROM clause for SQL Server

L'interfaccia diceva "lock hint attivo", ma la query non lo conteneva. Ora l'hint è applicato al nodo tabella:

Dopo

FROM [dbo].[Orders] [o] WITH (NOLOCK)
LEFT JOIN [dbo].[Cust] [c] WITH (NOLOCK)
  ON [o].[cust] = [c].[id];

In più, ogni tabella può avere un lock e un hint propri (dal menu contestuale Lock / hint tabella). Il generatore li traduce nella forma supportata dal database:

Database	Resa
SQL Server	WITH (UPDLOCK), WITH (NOLOCK, INDEX(ix_prod)) sulla singola tabella
PostgreSQL, MySQL 8	FOR UPDATE OF "o" + FOR SHARE OF "c", anche con SKIP LOCKED
Oracle	FOR UPDATE OF "o"."id", "c"."id" (una sola clausola ammessa)
Firebird	lock solo a livello di statement: FOR UPDATE WITH LOCK più un avviso

Quando un hint non si può applicare (ad esempio NOLOCK su PostgreSQL) non viene emesso, e lo segnalano sia una nota nell'SQL sia un warning di validazione. Su Firebird PLAN ora precede ORDER BY: in fondo alla query era sintatticamente errato.

9. APPLY e LATERAL validati per dialetto

Il builder permetteva CROSS APPLY, OUTER APPLY e JOIN LATERAL su qualunque database, e accettava perfino JOIN LATERAL <tabella>, che non è una lateral subquery. Ora ogni tipo di JOIN ha una riga nella capability matrix:

Dialetto	INNER/LEFT	RIGHT	FULL	APPLY	LATERAL
SQL Server	✓	✓	✓	✓	✓ come APPLY
PostgreSQL	✓	✓	✓	✓ come LATERAL	✓
Oracle	✓	✓	✓	12c+	12c+
MySQL	✓	✓	—	8.0.14+	8.0.14+
Firebird	✓	✓	✓	4.0+	4.0+
InterBase	✓	✓	✓	—	—
SQLite	✓	3.39+	3.39+	—	—

Dove esiste una forma equivalente il builder la usa: CROSS APPLY diventa CROSS JOIN LATERAL su PostgreSQL, MySQL e Firebird; OUTER APPLY diventa LEFT JOIN LATERAL … ON TRUE; LATERAL diventa CROSS APPLY su SQL Server e Oracle.

Anche l'interfaccia si adegua:

• nel popup JOIN i tipi non supportati sono barrati, marcati "n/a" e non selezionabili;
• quelli che richiedono una versione minima la mostrano accanto;
• LATERAL è disabilitato se la tabella di destinazione non è una subquery;
• le relazioni che diventano non supportate dopo un cambio di dialetto appaiono in rosso con ⚠.

10. DATEADD per SQL Server

Prima

DATEADD(1 DAY, MyDate)

Dopo

DATEADD(DAY, 1, MyDate)

L'argomento si scrive come prima (1 DAY, -3 months); è accettato anche l'ordine di SQL Server (DAY, 7). La sintassi era sbagliata anche in altri dialetti:

Dialetto	Risultato per "2 MONTH"
SQL Server	DATEADD(MONTH, 2, d)
MySQL	DATE_ADD(d, INTERVAL 2 MONTH)
PostgreSQL	(d + 2 * INTERVAL '1 month')
Oracle	ADD_MONTHS(d, 2)
Firebird	DATEADD(MONTH, 2, d) (prima: d + 2 MONTH)
SQLite	DATETIME(d, (2) || ' months')

11. LPAD su SQL Server

FORMAT() non è un equivalente di LPAD(): funziona su numeri e date con stringhe di formato, non riempie una stringa con un carattere. La nuova emulazione converte il valore in NVARCHAR, così funziona con qualsiasi tipo, e riproduce anche il troncamento di LPAD quando il valore è più lungo di n:

CASE WHEN DATALENGTH(CAST(x AS NVARCHAR(4000))) / 2 >= 5
     THEN LEFT(CAST(x AS NVARCHAR(4000)), 5)
     ELSE RIGHT(REPLICATE('0', 5) + CAST(x AS NVARCHAR(4000)), 5)
END

È stato aggiunto RPAD, con la stessa emulazione anche per SQLite. Su PostgreSQL il valore viene convertito in testo, perché lì LPAD applicato a un numero dà errore.

12. TDialectCapabilities: niente più SQL ineseguibile

Il wizard offriva JSON_OBJECT, LPAD, DATE_TRUNC, STRING_AGG, REGEXP e altre funzioni senza verificare se il database le supportasse. Ora c'è una struttura dedicata:

struct TDialectCapabilities {
    TSQLDialect     Dialect;
    TDialectFeature SupportsJson;
    TDialectFeature SupportsWindowFunctions;
    TDialectFeature SupportsLateral;
    TDialectFeature SupportsStringAgg;
    TDialectFeature SupportsDateTrunc;
    TDialectFeature SupportsRegex;
    TDialectFeature SupportsReturning;
    TDialectFeature SupportsOffsetFetch;
    // ... FullJoin, IntersectExcept, LPad, Apply, SimilarTo
    static TDialectCapabilities For(TSQLDialect ADialect);
};

Ogni TDialectFeature indica se la funzionalità è supportata, disponibile da una versione minima (MinVersion, ad esempio "2017+") o non supportata. Alcuni esempi:

Funzionalità	SQL Server	PostgreSQL	MySQL	Oracle	Firebird	SQLite
JSON	2016+	9.4+	5.7.8+	12.2+	—	3.38+
Window functions	✓	✓	8.0+	✓	3.0+	3.25+
STRING_AGG	2017+	✓	GROUP_CONCAT	11gR2+	LIST	GROUP_CONCAT
REGEXP	2025+	✓	✓	✓	—	estensione
RETURNING	OUTPUT	✓	—	INTO	2.1+	3.35+

Le capability sono usate in tre punti:

• Validazione. Funzioni non supportate sono errori, quelle legate a una versione sono warning. Lo stesso vale per window function, IIF, REGEXP e SIMILAR TO nel WHERE, OFFSET/FETCH e RETURNING.
• Wizard. Ogni funzione riporta "[n/a SQLite]" oppure "[2017+]"; l'anteprima mostra la disponibilità e gli errori negli argomenti, e "Applica" rifiuta una funzione non disponibile.
• Generatore. Dove esiste un equivalente reale viene usato: DATE_TRUNC diventa TRUNC su Oracle e DATEADD/DATEDIFF su SQL Server, JSON_OBJECT su SQL Server diventa FOR JSON PATH. SIMILAR TO e REGEXP, invece, non vengono più convertiti in silenzio in LIKE, che ha un significato diverso.

Altre correzioni emerse durante la revisione

• Il wizard delle funzioni scalari applicava sempre la prima funzione della categoria, qualunque fosse quella scelta.
• SUBSTRING, LEFT e RIGHT generavano sintassi non valida su Oracle e SQLite.
• YEAR() e MONTH() non esistono su PostgreSQL e Oracle: ora escono come EXTRACT.
• Su MySQL CONVERT aveva gli argomenti invertiti e CAST AS INTEGER non è valido: ora SIGNED e CHAR(n).
• Il path di JSON_VALUE usciva con doppi apici.
• STRING_AGG su colonne numeriche falliva su PostgreSQL e SQL Server.
• InterBase usava FIRST e OFFSET … FETCH, che sono sintassi Firebird: ora usa ROWS n e ROWS m TO n.
• OUTPUT INSERTED.id, created_at di SQL Server metteva INSERTED. solo sulla prima colonna.
• Con OFFSET attivo uscivano due LIMIT, oppure TOP e OFFSET insieme.
• Il ; finale poteva finire dentro un commento -- sull'ultima riga.
• TableHint veniva salvato nel progetto ma non era mai emesso nell'SQL.

Come sono state verificate le modifiche

Le funzioni che generano l'SQL sono state compilate con g++, affiancate da un piccolo strato che sostituisce String e le classi VCL, ed esercitate con 94 verifiche automatiche che coprono tutti i punti descritti. Per SQLite le espressioni generate sono state anche eseguite su un database reale (LPAD/RPAD emulati, DATE_TRUNC, DATE_ADD, JSON, JOIN annidati, UNION) confrontando i risultati con quelli attesi.

Cosa resta da fare. Le forme per SQL Server, PostgreSQL, Oracle, MySQL e Firebird sono state controllate come testo ma non eseguite su quei server, e l'interfaccia (popup, menu, wizard) va provata nell'IDE. Le versioni minime della matrice vanno confrontate con le versioni dei database effettivamente in uso.

Note per chi aggiorna

• Validate() ora restituisce true quando la query è valida. Chi si affidava al vecchio comportamento deve passare a HasValidationErrors().
• Nuove API pubbliche: QuoteIdentifier(), QualifiedTableName(), QualifiedName(), WhereValueList(), JoinCapability(), IsJoinSupported(), TDialectCapabilities::For(), DialectCapabilities(), ScalarFuncCapability(), IsScalarFuncSupported(), SetTableLockHint(), ClearTableLockHint(), SetTableHintText(), EffectiveLockHint().
• Progetti salvati: i nuovi campi JSON (lockOv, lockHint, tblHint) sono opzionali, per cui i progetti esistenti si aprono con lo stesso SQL di prima.
• SQL diverso da prima: in diversi casi l'SQL generato cambia, ed è voluto: JOIN annidati, schema nei DML, hint reali, sintassi di data corrette. Conviene rigenerare e rileggere le query salvate come testo.

#SQL #database #ETL #DataEngineering #plyxSQL #FireDAC #PGSOFT