Translate To English! Poco fa` mi sono trovato di fronte ad un problema piuttosto interessante. Stavo scrivendo una query per un database Oracle, e dovevo rendere questa query parametrizzabile.
La query era simile alla seguente:
SELECT * FROM MiaTable WHERE campo1 = :parametro1 AND campo2 = :parametro2
Il campo1 era un numero intero, mentre il campo2 era una stringa.
Ho allora creato una piccola funzione in grado di sostituire automaticamente, e in un colpo solo, tutti i parametri della query. Inoltre la stessa funzione, in base alle indicazioni fornite, poteva formattare i parametri nel modo corretto (per esempio aggiungendo gli apici singoli intorno alla stringa).
La funzione e` questa:
(define (replace-sql argSql argReplace , (delimiter "") ) (dolist (i argReplace) (if (= (i 1) 'string) (setq delimiter "'")) (replace (i 0) argSql (string delimiter (i 2) delimiter) ) );dolist argSql ; Valore di ritorno )
Come si usa? Semplicissimo!
Il primo parametro e` la query stessa (non importa se e` una SELECT, una INSERT o altro!) ed il secondo parametro e` una lista di elementi da sostituire nella query. Ecco un esempio completo:
;Query parametrica.
(setq sql "select * from myTable where t1 = :t1 and t2 = :t2")
; Questi sono i parametri da sostituire.
(setq valori '(
(":t1" integer "100")
(":t2" string "Pippo")
) )
(replace-sql sql valori)
La variabile valori contiene delle liste, in cui ognuna e` cosi` composta:
- Nome completo del parametro (esempio: “:t1″)
- Tipo di dato (per ora solo “string” esegue una vera formattazione aggiungendo gli apici; potete pero` modificare molto facilmente la funzione originale aggiungendo altri tipi di dato).
- Valore del campo (esempio: “Pippo”)
La funzione ritornerà il codice SQL con tutti i valori correttamente sostituiti.
Attenzione: poiche` viene eseguito un “brutale” replace, non usate parametri che possano avere nomi che compaiono in altre parti del codice sql stesso, altrimenti effettuerete il replace anche di altre parti di codice! Vi suggerisco nomi tipo: <par1> oppure @par1@.
Spero che questa funzione vi possa essere utile!
Alla prossima!
UPDATE: su suggerimento di Carlo (grazie!), ho modificato la funzione per poter riconoscere, in modo automatico, il datatype dei parametri. La funzione completa e` la seguente:;==============================================================================
; @SYNTAX (replace-params SQL-Code parameters-list)
; SQL-Code : SQL statement.
; parameters-list : List composed in this way:
; ( params01 params02 params03 ... )
; Every "param" is a list, and is composed in this way:
; (param-name datatype value)
; param-name : name of the parameter
; value : value that will substitute the parameter (string)
; datatype : datatype (e.g.: integer, string, date, etc...).
; This value is optional. If not passed, it will be automatically
; detected from the function self.
;
; Currently, parameter type can be one of the following:
; string : the value will be surrounded with single apex.
; date : the value must be in the following format: DD-MM-YYYY.
; It will be automatically formatted by this function.
; float :
; integer : will be inserted without any other extra-job.
;
; @RETURN SQL string with all requested terms replaced.
;
; Replace one or more sql parameters with the real values.
;
; @EXAMPLE
; In this example, the original sql string is "sql". The variable "val"
; contains two lists that contain needed info to make the replacement.
; ":t1" is INTEGER and the value is "100"
; ":t2" is STRING and the value is "Alessandro"
;
; (setq sql "select * from myTable where t1 = :t1 and t2 = :t2")
;
; (setq val '(
; (":t1" integer "100")
; (":t2" "Alessandro")
; ) )
;
; (replace-params sql val)
;==============================================================================
(define (replace-params argSql argReplace ,
(locPrefix "") ; string before the value
(locPostfix "") ; string after the value
(locDatatype nil) ; datatype for a specific value
)
(dolist (i argReplace)
(if (= (length i) 2) ; Datatype is missing: I will take the default one.
(cond ;IF
( (string? (i 1) ) (setq locDatatype 'string) )
( (integer? (i 1)) (setq locDatatype 'integer) )
( (float? (i 1) ) (setq locDatatype 'float) )
);cond
(setq locDatatype (i 2) ) ;ELSE
);if
(cond
(;condition_1
(= locDatatype 'string) (setq locPrefix "'" locPostfix "'")
)
(;condition_2
(= locDatatype 'date) (setq locPrefix "to_date(" locPostfix {, 'DD-MM-YYYY')} )
)
);cond
(replace (i 0) argSql (string locPrefix (i 1) locPostfix) )
);dolist
argSql
)
E’ possibile modificare il programma in modo tale da “abbreviare” quello che devo scrivere quando passo i parametri?
Pensavo ad una cosa tipo questa:
(setq valori '(
("t1" 100)
("t2" "Pippo")
) )
in questo modo non devo specificare i “:” nel nome parametro, dando per scontato che per “convenzione” i parametri li definisco sempre nella forma :param1,:param2, ecc… con Hibernate per esempio è così.
L’altra “scorciatoia” è quella di non dover dire di che “tipo” si tratta, ma è il programma a dover intuire se gli passo un numero o una stringa ….
è realizzabile?
Ciao,
eliminare i “:” non e` un problema, visto che la funzione fa` un “brutale” replace dei parametri con i nuovi valori.
Se passi un parametro come “t1″ (senza “:”) puoi aggiungere i “:” nella funzione stessa, facendo cosi`:
(setq parametroCompleto (string “:” parametroCorto) )
In questo modo “parametroCorto” (passato dall’utilizzatore della funzione) sara` “t1″, mentre “parametroCompleto” (quello che verra` usato nel replace) sara` “:t1″. Potresti anche omettere i “:”, ma te lo sconsiglio, visto che rischieresti di fare un replace di tutt’altra stringa (esempio: “SELECT docs_letti, numero_letture FROM visitati WHERE docs_letti > letti” – se usassi “letti” come parametro, andresti a cambiare anche la “SELECT docs_letti” !!!)
Potresti anche mettere questa funzione in un context (per esempio chiamato: IT.MYNAME.SQL_UTILS), poi inizializzare il context con alcuni valori di default, tra cui il simbolo da usare per i parametri. In questo modo potresti configurare dinamicamente il tutto, migliorandone il suo riutilizzo in altri ambiti (una volta come separatore usi “:”, poi decidi di usare “@” oppure “#”).
Ecco la funzione completa che usa i “:” per default:
(define (replace-sql argSql argReplace , (delimiter “”) )
(dolist (i argReplace)
(if (= (i 1) ’string) (setq delimiter “‘”))
; LA RIGA SEGUENTE E` QUELLA MODIFICATA!
(replace (string “:” (i 0) ) argSql (string delimiter (i 2) delimiter) ) ;
);dolist
argSql ; Valore di ritorno
)
—————-
Per “scoprire” invece qual’e` il datatype potresti ricorrere alle funzioni che restituiscono proprio il datatype associato ad un campo. Qui pero` ci “incastriamo” nello specifico DB in uso. Se ti va` bene di usare ODBC, allora, usando il modulo ODBC.lsp, potresti usare la funzione “ODBC:columns” per farti ritornare le caratteristiche delle colonne (anche il formato) della table in oggetto.
Generalmente parlando, il problema e` che la funzione che ho scritto “replace-sql” non e` un database-abstraction-layer (come Hybernate o simili, i quali mappano un database “tradizione” in uno ad oggetti), ma e` una semplice replace. La funzione non ha la minima idea del codice SQL che hai scritto, non sa` se stai lavorando su tabelle, viste, snapshots, sinonimi, etc… (per la verità` non sa` nemmeno che si tratta di SQL!).
Pero` potrebbe essere MOLTO interessante realizzare in NewLisp qualcosa simile a Hybernate, oppure creando una gestione di DB simile a quella di Ruby On Rails. A quel punto pero` si tratterebbe di scrivere un vero abstraction-layer che si vada ad interporre tra il tuo software ed il DB.
ottimo,
per riconoscimento del data type indentevo però una cosa più semplice (non so se poi reallizzarla è in realtà più semplice), e cioè riconoscere in automatico il tipo di parametro che ti passo:
invece di (“t1″ integer 100) solo (“t1″ 100), e poi sarà compito di replace-sql sapere che ti ho passato un numero e che quindi il replace va fatto senza mettere il valore fra apici …
non intendevo il riconoscimento in automatico del datatype della colonna sulla tabella (anche se sarebbe comunque una cosa carina da implementare)
Ora ho capito
Ma come possiamo riconoscere tipi di dato tra apici? Per esempio, io utilizzo Oracle, e le date si scrivono fra apici, in un formato “formattabile” tramite una TO_DATE() e quindi io scrivero`: “10-Jan-2008″ oppure “10-01-2008″ oppure…. etc….
Purtroppo, visto che non esistono tipi di dato per distinguere questi dati, si rischia di incorrere in molti problemi.
Comunque, se vuoi, puoi provare a fare il “discover” del datatype con le funzioni:
atom? checks if an expression is an atom
array? checks if an expression is an array
context? checks if an expression is a context
directory? checks if a disk node is a directory
empty? checks if a list or string is empty
file? checks for the existence of a file
float? checks if an expression is a float
global? checks if a symbol is global
integer? checks if an expression is an integer
lambda? checks if an expression is a lambda expression
legal? checks if a string contains a legal symbol
list? checks if an expression is a list
macro? checks if an expression is a lambda-macro expression
NaN? checks if a float is NaN (not a number)
nil? checks if an expression is nil
null? checks if an expression is nil, “”, () or 0.
number? checks if an expression is a float or an integer
protected? checks if a symbol is protected
primitive? checks if an expression is a primitive
quote? checks if an expression is quoted
string? checks if an expression is a string
symbol? checks if an expression is a symbol
true? checks if an expression is not nil
zero? checks if an expression is 0 or 0.0
Mettendola in una “case” forse… (oppure in una “cond”, oppure “if”).
Un momento! Mi e` venuta in mente una idea: potremmo fare una modifica per far si che, se passi due valori nella lista allora la funzione “lavora” su scelte standard (apici -> stringa, no apici->integer, etc…), mentre se ci si trova in una condizione “speciale” si aggiunge il terzo campo. Quindi:
(“field1″ 100) –> integer
(“field2″ “10-12-2008″) –> stringa generica
(“field3″ “10-12-2008″ date) —> formato speciale (data).
Che ne dici? Mi metto subito all’opera (sara` utile anche a me
)
mi piace !!