Parser une requête [Résolu]

[datametric]

Bonjour,

je viens de me lancer dans les RegEx et je souhaitais valider mon code et les orientations à prendre, avec les spécialistes désormais identifiés.

L'objectif est de récupérer les variables utilisées dans les SQL que je trace dans les LOG.
Je pensais pouvoir lui dire : attrape tout ce qui est entre SELECT et FROM puis entre FROM et quelque chose qui peut être WHERE, ORDER ou HAVING. etc ... Mais je ne sais pas le faire en plus allégé que cela. J'ai donc dissocié les prxmatch des prxchange pour rentrer dans ce language étape par étape.

Q1 : J'attrape bien les blocs mais est-ce du code optimisé ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DATA WORK._00rq;
	LENGTH c $ 200;
	LABEL  c = "";
	FORMAT c $CHAR200.;
	INFORMAT c  $CHAR200.;
	INFILE DATALINES4 TRUNCOVER;
	INPUT @1 c  $CHAR200.;
	DATALINES4;
SELECT a,b,c FROM matable, matable2 WHERE a=12 ORDER BY a,b
SELECT a,b,c,C, z, ec FROM matable, matable2 WHERE a=12 AND b =1 ORDER BY a, b
SELECT C, z, ec FROM matable2 ORDER BY z
SELECT C, z FROM matable2 WHERE z=15
;;;;
 
 
DATA _null_;
SET WORK._00rq;
 
C_cleaned = Strip( lowcase(C) );
 
_select 	= prxmatch('/select/'	, C_cleaned );
_from		= prxmatch('/from/'	, C_cleaned );
_where	= prxmatch('/where/'	, C_cleaned );
_orderby	= prxmatch('/order by/'	, C_cleaned );
_having	= prxmatch('/having/'	, C_cleaned );
 
IF _select ne 0 then
le_select  = PrxChange( 's/.*select ([A-Za-z0-9_ ?,]+) .*from .*$/$1/io' , 1 , C_cleaned );
 
IF _from ne 0 then
le_from  = PrxChange( 's/.*from ([A-Za-z0-9_ ?,]+) (where|order|having) .*$/$1/io' , 1 , C_cleaned );
 
IF _orderby ne 0 then
do;
IF _where ne 0 then
le_where  = PrxChange( 's/.*where ([A-Za-z0-9=_ ?,]+) order .*$/$1/io' , 1 , C_cleaned );
le_orderby  = PrxChange( 's/.*by ([A-Za-z0-9=_ ?,]+) .*$/$1/io' , 1 , C_cleaned );
end;
 
IF _where ne 0 AND _orderby = 0 then
le_where  = PrxChange( 's/.*where ([A-Za-z0-9=_ ?,]+) .*$/$1/io' , 1 , C_cleaned );
 
put le_select= le_from= le_where= le_orderby= /;
run;

Mon premier soucis intervient ensuite dans la création des ARRAY que je souhaite constituer depuis les blocs capturés. Pour cela j'ai vu que je devais utiliser CALL PRXNEXT. Mais je n'arrive pas à comprendre comment l'utiliser dans mon cas.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
array ids{10} buffer1-buffer10; 
 
re = prxparse('/([A-Za-z0-9_ ?]+)/');
 
  start = 1; 
  stop = length(le_select); 
  call prxnext(re,start,stop,le_select,startposn,len); 
  do i = 1 TO 3 while(startposn > 0); 
    ids{i} = substr(le_select,startposn,len); 
    call prxnext(re,start,stop,le_select,startposn,len); 
    end;
 
put (buffer1-buffer10)(=);

Q2 : dois-je insérer quelque chose comme cela à la suite de la création de mon le_select pour capturer chacune de mes variables ? (ça ne donne rien pour l'instant, je ne comprends pas pourquoi il me renvoie une erreur au sujet d'argument numérique) ou bien dois-je créer un même type de boucle mais en utilisant à l'intérieur la fonction SCAN ?


Mon second soucis concerne les mots à nettoyer : DISTINCT, AND, OR BETWEEN etc ... car je ne veux au final que les noms de variables.

Q3 : Dois-je / puis-je créer une liste de mots à exclure et lui demander de les supprimer des blocs récupérés ?

Merci à vous.

[sasadm]

Bonjour Stéphane.

Voici une solution propre :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
 
DATA _test_;
SET WORK._00rq;
 length le_select le_from le_where $128;
retain _reg_select _reg_where _reg_from _reg_order;
 
 
/* compilation des regexps  */
IF _n_=1 then do;
	_reg_select= prxparse('/select(.+)from/i');
	_reg_from= prxparse('/from(.+?)(?:where|order|having|$)/i');
	_reg_where=  prxparse('/where(.+?)(?:order\s+by|having|$)/i');
	_reg_order= prxparse('/order\s+by(.+)/i');
end;
 
/* test des matchings */
_select=prxmatch(_reg_select, C);
_from= prxmatch(_reg_from, C);
_where= prxmatch(_reg_where, C);
_order=prxmatch(_reg_order, C);
 
/* récupération des captures */
IF _select then le_select  = prxposn(_reg_select, 1 , C);
IF _where then le_where  = prxposn(_reg_where, 1 , C);
IF _from then le_from  = prxposn(_reg_from, 1 , C);
IF _order then le_order =prxposn(_reg_order, 1, C);
 
put le_select=  @30  le_from=  @60  le_where=  @100   le_order=  ;
DROP  _: ;
run;
proc print;
run;

Deux principes :

1. Lorsqu'il s'agit de récupérer des captures il vaut mieux ne pas utiliser prxchange mais préférer prxmatch+prxposn. Il est plus facile d'écrire "je récupère telle partie de la chaine" plutôt que "j'efface le tout par la partie" car la première assertion ne nécessite de définir que la "partie" alors que la seconde doit en plus définir le "tout" (d'où l'utilisation que tu fais des .* en début et fin de tes expressions).
2. Il vaut mieux compiler une fois les expressions (if _n_=1) puis les utiliser a travers leurs étiquettes (ici les _reg_xxxx qui sont retenues par retain) car cela évite la recompilation à chaque iteration du PDV.

Remarques:

1. J'écris plusieurs fois .+? pour limiter la "gourmandise" de .+. Dans l'expression /from(.+?)(?:where|order|having|$)/ si j'enlève le ? du .+? il va me ressortir le_from=matable, matable2 WHERE a=12 ORDER BY a,b car .+ va tout absorber comme si j'avais écrit /from(.+)$/. Avec le "?", SAS ne va plus ignorer le reste de l'expression mais va au contraire faire l'effort de rechercher les termes order ou where ou having.
2. le modificateur i: /xxxxxx/i permet de ne pas tenir compte de la casse.

Je te répondrai plus tard pour prxnext.

[datametric]

Merci

[sasadm]

Re,


Voici un code qui marche avec prxnext. L'erreur sur le type numérique venait de l'absence de spécification quant à la longueur de stockage.

C'est une usine à gaz=>je te conseille d'utiliser un scan par rapport à la virgule.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
 
DATA _test2_;
SET _test_;
retain reg;
array buffer{5} $12 ; 
 
IF _n_=1 then reg = prxparse('/(\w+)/');
nb=0;
 
IF prxmatch(reg, le_select) then do; /* si présence de l'expression */
	special=1;  
	start=1; /* début du parsing en position 1 */
	do until(len=0); /* boucle de récupération */
	  	call prxnext(reg, start, -1, le_select, pos, len); /* recherche le schéma à partir de la position start */
		IF len then do;
			nb+1;
			buffer[nb]=substr(le_select, pos, len); /* extraction depuis la position pos*/ 
			start=pos+len+1; /* positionnement de start juste apres le caractere qui vient d'être extrait */
		end;
 	end;
end;
put buffer[*];
 
keep buffer: le_select;
 
run;
proc print;
run;

Enfin pour ta question Q3, tu peux très bien utiliser prxchange (enfin !) en tant que premier traitement de ta data _null_

Code :

1
2
3
4
5
 
retain _reg_suppr;
IF _n_=1 then _reg_suppr=prxparse(s/DISTINCT|AND|OR|BETWEEN//i);
 
call prxchange(_reg_suppr, -1, C);

[datametric]

j'en étais à utiliser le SCAN.

Code :

1
2
3
4
5
6
7
array variable $40 variable1-variable100;
 
do idx = 1 TO nbvar_select;
variable(idx) = scan(le_select,idx,',');
IF variable(idx) ne ' ' then 
IF variable(idx) = ' ' then idx = dim( variable ) +1 ;
end;

[datametric]

Voici donc avec l'appui de sasadm, le code commenté de la version 1.0

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DATA WORK._00rq;
LENGTH STMT_TEXT $ 200;
LABEL  STMT_TEXT = "";
FORMAT STMT_TEXT $CHAR200.;
INFORMAT STMT_TEXT  $CHAR200.;
INFILE DATALINES4 TRUNCOVER;
INPUT @1 STMT_TEXT  $CHAR200.;
DATALINES4;
SELECT a,b,c FROM matable, matable2 WHERE a=12 ORDER BY a,b
SELECT a,b,c,C, z, ec FROM matable, matable2 WHERE a=12 AND b =1 ORDER BY a, b
SELECT a,b,c,C, z, ec FROM matable LEFT JOIN matable2 ON a.id=b.id WHERE a=12 AND b =1 ORDER BY a, b
SELECT a,b,c,C, z, ec FROM matable LEFT JOIN matable2 ON a.id=b.id AND matable LEFT JOIN matable3 ON a.id=b.id3 WHERE a=12 AND b =1 ORDER BY a, b
SELECT a,b,c,C, z, ec FROM matable2 RIGHT JOIN matable3 ON a.id=b.id WHERE a=12 AND b =1 ORDER BY a, b
SELECT C, z, ec FROM matable2 ORDER BY z
SELECT C, z FROM matable2 WHERE z=15
;;;;

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
DATA _test_;
SET WORK._00rq;
length le_select le_from le_where le_group le_order $512;
retain	_reg_select _reg_from _reg_where _reg_group _reg_order
	_reg_suppr _reg_as _reg_key _reg_varguill
 
;
 
/* regexps  
 
Création de l'expression à rechercher
(.+) tout caractère
\s est un espace
(?: ... ) est une assertion (ie. un bloc) nulle.  Permet de compléter une recherche d'un autre mot
sans en tenir compte lorsque l'on veur attraper une chaîne spécifique
$ fin de ligne
\b est un délimiteur de mot. un peu comme un espace
\w un mot (alpha + _ )
\w+ plusieurs mots
 
*/
IF _n_=1 then
	do;
		/*Tout ce qui est entre SELECT et FROM*/
		_reg_select	=prxparse('/select(.+)from/i');
 
		/*Tout ce qui est entre FROM et un autre mot clef SQL*/
		_reg_from	=prxparse('/from(.+?)(?:where|order\s+by|for|having|order\s+by|$)/i');
 
		/*...*/
		_reg_where	=prxparse('/where(.+?)(?:order\s+by|for|having|group|$)/i');
		_reg_group	=prxparse('/group\s+by(.+?)(?:order\s+by|for|having|$)/i');
		_reg_order	=prxparse('/order\s+by(.+?)(?:for|having|$)/i');
 
		/*Cherchera ces mots clefs à supprimer*/
		_reg_suppr	=prxparse('s/\bAND\b|\bOR\b|\bBETWEEN\b|\bFOR\b|\bREAD\b|\bONLY\b|\bCAST\b|\bVARCHAR\b|\bfetch\b|\bfirst\b|\bNULL\b|\bASC\b//i');
 
		/*Cherchera un alias avec AS */
		_reg_as 	=prxparse('s/\b+as\b+\w+//i');
 
		/*Cherchera ces mots clefs à supprimer plus spécifique au SELECT*/
		_reg_key	=prxparse('s/(\b|\()DISTINCT|\bCOUNT|\bSUM|\bMAX|\bMIN|\bCOUNT_BIG|\bAVG|\bRTRIM|\bNULL//i');
 
		/*Cherchera un mot entre guillemets*/
		_reg_varguill =prxparse('/"\w+"/');
	end;
 
/*Petit nettoyage des blancs et passage en minuscule*/
C_cleaned=Strip( lowcase(compbl(STMT_TEXT)) );
 
/*Supprime (-1) la liste des mots donnés dans _reg_suppr et ce, dans la chaîne C_Cleaned*/
call prxchange(_reg_suppr, -1, C_cleaned);
 
/* test des matchings */
_select	=prxmatch(_reg_select	, C_cleaned);
_from	=prxmatch(_reg_from		, C_cleaned);
_where	=prxmatch(_reg_where	, C_cleaned);
_group	=prxmatch(_reg_group	, C_cleaned);
_order	=prxmatch(_reg_order	, C_cleaned);
 
/* récupération des captures (.+?) dans des variables textes*/
IF _select then
	le_select  	= prxposn(_reg_select, 1 , C_cleaned);
 
IF _where then
	le_where  	= prxposn(_reg_where , 1 , C_cleaned);
 
IF _from then
	le_from  	= prxposn(_reg_from	 , 1 , C_cleaned);
 
IF _group then
	le_group 	= prxposn(_reg_group , 1 , C_cleaned);
 
IF _order then
	le_order 	= prxposn(_reg_order , 1 , C_cleaned);
 
/*Nombre de variables SELECT*/
array vars_{20} $40;
 
/*
Cet array est utilisé pour compter le nombre de mots.
Le dernier devant représenter la variable et non les alias des schémas et des tables
Select distinct DW.TS_HIR_EDS."ID_ELT_STR"
*/
array counts_{20} _temporary_;
 
/*Le select * est considéré. 
l'étoile est mis dans la var1 pour un lookup ultérieur sur le dictionnaire*/
IF compress(le_select) = '*' then
	vars_1	='*';
else
	do idx = 1 TO dim(vars_);
		/*attrape la variable entre deux virgules sans guillemet et () */
		vars_(idx) = scan(compress(le_select,'"()'),idx,',');
 
		/*Supprime (-1) la liste des mots de _reg_key et _reg_as*/
		call prxchange(_reg_key	, -1, vars_(idx));
		call prxchange(_reg_as	, -1, vars_(idx));
 
		/*Seul le dernier mot est retenu.*/
		counts_(idx)=countw(vars_(idx),'.');
		vars_(idx) = upcase(scan(vars_(idx),counts_(idx),'.'));
 
		/*lorsque la liste est vide, la boucle se stoppe*/
		IF vars_(idx) = ' ' then
			idx = dim( vars_ ) +1;
	end;
 
/*récupération des variables du WHERE*/
nb=0;
array varw_{20} $40;
 
IF prxmatch(_reg_varguill, le_where) then
	do;
		special=1;
		start=1;
 
		do until(len=0);
			/*Recherche un mot entre guillemets*/
			call prxnext(_reg_varguill, start, -1, le_where, pos, len);
 
			IF len then
			/*tant que la fin de chaîne n'est pas atteinte*/
 
				do;
					nb+1;
 
					/*Recherche le mot suivant entre guillemets dans le WHERE. Supprime les guillemets en passant*/
					varw_[nb]=upcase(compress(substr(le_where, pos, len),'"'));
					start=pos+len+1;
				end;
		end;
	end;
 
/*récupération des variables du GROUP*/
array varg_{20} $40;
array countg_{20} _temporary_;
 
do idx = 1 TO dim(varg_);
	varg_(idx) 	= scan(compress(le_group,'"()'),idx,',');
	countg_(idx)= countw(varg_(idx),'.');
	varg_(idx) 	= upcase(scan(varg_(idx),countg_(idx),'.'));
 
	IF varg_(idx) 	= ' ' then
		idx = dim( varg_ ) +1;
end;
 
/*récupération des variables du ORDER*/
array varo_{20} $40;
array counto_{20} _temporary_;
 
do idx = 1 TO dim(varo_);
	varo_(idx) 	= scan(compress(le_order,'"()'),idx,',');
	counto_(idx)= countw(varo_(idx),'.');
	varo_(idx) 	= upcase(scan(varo_(idx),counto_(idx),'.'));
 
	IF varo_(idx) 	= ' ' then
		idx = dim( varo_ ) +1;
end;
 
/*récupération des tables*/
array table_{10} $40;
array countt_{10} _temporary_;
 
/*transforme les ordres de jointure par des virgules pour le SCAN qui suit
A (LEFT JOIN) B ON a=b devient A , B ,
*/
le_from= prxchange('s/((left|inner|right|cross|full\souter|full)\sjoin\s)/ , /',-1,le_from);
 
/*joue la sécurité
cherche ON suivi d'un blanc ou plus (\s+) suivi éventuellement de n'importe quel caractère (.+?)
suivi d'un blanc ou plus (\s*) suivi d'un égal etc ...
Substitue tout cela par une virgule
*/
le_from= prxchange('s/on\s+.+?\s*=\s*.+?\s+/ , /',-1,le_from);
 
do idx = 1 TO dim(table_);
	table_(idx) = scan(compress(le_from,'"()'),idx,',');
	countt_(idx)= countw(table_(idx),'.');
	table_(idx) = upcase(scan(table_(idx),countt_(idx),'.'));
 
	IF table_(idx) 	= ' ' then
		idx = dim( table_ ) +1;
end;
 
keep var: TABLE:;
run;