git.infodrom.org Git - infodrom/rico3/blob - plugins/dotnet/sqlParse.ascx.vb

   1 Partial Class sqlParse\r
   2 Inherits System.Web.UI.UserControl\r
   3 Implements ICloneable\r
   4 \r
   5 Public Class sqlColumn\r
   6   Public sql As String, name As String\r
   7   Public LookupQuery As String ' query to populate column\r
   8 \r
   9   Public Sub New(Optional sqlParm As String = "", Optional nameParm As String = "")\r
  10     if sqlParm<>"" then sql=sqlParm\r
  11     if nameParm<>"" then name=nameParm\r
  12   End Sub\r
  13 \r
  14   Public function Unparse()\r
  15     dim s As String=sql\r
  16     if not IsNothing(name) then\r
  17       s &= " AS " & name\r
  18     end if\r
  19     Unparse=s\r
  20   end Function\r
  21 End Class\r
  22 \r
  23 \r
  24 \r
  25 '********************************************************************************************************\r
  26 ' Parse SQL a statement\r
  27 '********************************************************************************************************\r
  28 \r
  29 Public IsDistinct As Boolean\r
  30 Public SelectList As New ArrayList()\r
  31 Public GroupBy As New ArrayList()\r
  32 Public OrderBy As New ArrayList()\r
  33 Public FromClause As String, WhereClause As String, HavingClause As String\r
  34 Public Headings As New ArrayList()  ' set after an any unparse* call\r
  35 \r
  36 Public Function Clone As Object Implements ICloneable.Clone\r
  37   Dim NewObj as object = Me.MemberwiseClone, item as String\r
  38   ' shallow copy of OrderBy is insufficient because it may be modified by ricoResponse.ascx\r
  39   NewObj.OrderBy = New ArrayList()\r
  40   for each item in Me.OrderBy\r
  41     NewObj.OrderBy.Add(item)\r
  42   next\r
  43   Return NewObj\r
  44 End Function\r
  45 \r
  46 ' -------------------------------------------------------------\r
  47 ' Rebuilds a SQL select statement that was parsed by ParseSelect\r
  48 ' -------------------------------------------------------------\r
  49 Private Function Unparse(arSkipCols) As String\r
  50   dim sqltext As String = "SELECT "\r
  51   if IsDistinct then sqltext &= "DISTINCT "\r
  52   sqltext &= UnparseColumnListSkip(arSkipCols) & " FROM " & FromClause\r
  53   if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause\r
  54   if GroupBy.count > 0 then sqltext &= " GROUP BY " & join(GroupBy.ToArray(),",")\r
  55   if not IsNothing(HavingClause) then sqltext &= " HAVING " & HavingClause\r
  56   if OrderBy.count > 0 then sqltext &= " ORDER BY " & join(OrderBy.ToArray(),",")\r
  57   Unparse=sqltext\r
  58 end Function\r
  59 \r
  60 \r
  61 Public Function UnparseSelect() As String\r
  62   dim arSkipCols(-1) as string\r
  63   UnparseSelect=Unparse(arSkipCols)\r
  64 end Function\r
  65 \r
  66 \r
  67 Public function UnparseSelectSkip(arSkipCols)\r
  68   UnparseSelectSkip=Unparse(arSkipCols)\r
  69 end Function\r
  70 \r
  71 \r
  72 Public Function UnparseSelectDistinct() As String\r
  73   dim arSkipCols(-1) as string\r
  74   IsDistinct=true\r
  75   UnparseSelectDistinct=Unparse(arSkipCols)\r
  76 end Function\r
  77 \r
  78 \r
  79 ' MS Access does not support ordering by column name\r
  80 Public Function UnparseDistinctColumnAccess(colnum as integer) As String\r
  81   dim sqltext As String\r
  82   sqltext="SELECT DISTINCT " & SelectList(colnum).sql & " FROM " & FromClause\r
  83   if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause\r
  84   Headings.Clear()\r
  85   Headings.Add(SelectList(colnum).name)\r
  86   UnparseDistinctColumnAccess=sqltext & " ORDER BY " & SelectList(colnum).sql\r
  87 end Function\r
  88 \r
  89 \r
  90 Public Function UnparseDistinctColumn(colnum as integer) As String\r
  91   dim sqltext As String\r
  92   sqltext="SELECT DISTINCT " & SelectList(colnum).sql & " as col1 FROM " & FromClause\r
  93   if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause\r
  94   Headings.Clear()\r
  95   Headings.Add(SelectList(colnum).name)\r
  96   UnparseDistinctColumn=sqltext & " ORDER BY col1"\r
  97 end Function\r
  98 \r
  99 \r
 100 Public function UnparseColumnList() As String\r
 101   dim strSelectList As New ArrayList(), i as integer, sql as String\r
 102   Headings.Clear()\r
 103   for i=0 to SelectList.count-1\r
 104     strSelectList.Add(SelectList(i).sql & " AS rico_col" & i)\r
 105     Headings.Add(SelectList(i).name)\r
 106   next\r
 107   UnparseColumnList=join(strSelectList.ToArray(),",")\r
 108 end Function\r
 109 \r
 110 \r
 111 Public function UnparseColumnListSkip(arSkipCols() as String) As String\r
 112   dim strSelectList As New ArrayList(), i as integer\r
 113   dim SkipIdx as integer=0, skip as boolean\r
 114   Headings.Clear()\r
 115   for i=0 to SelectList.count-1\r
 116     skip=false\r
 117     if SkipIdx < arSkipCols.Length then\r
 118       skip=CBool(arSkipCols(SkipIdx)=CStr(i))\r
 119       if skip then SkipIdx+=1\r
 120     end if\r
 121     if not skip then\r
 122       strSelectList.Add(SelectList(i).sql & " AS rico_col" & i)\r
 123       Headings.Add(SelectList(i).name)\r
 124     end if\r
 125   next\r
 126   UnparseColumnListSkip=join(strSelectList.ToArray(),",")\r
 127 end Function\r
 128 \r
 129 \r
 130 ' returns a "windowed" select query\r
 131 ' includeAS should be true for SQL Server 2005+ and false for Oracle\r
 132 Public function UnparseWithRowNumber(offset as Integer, numrows as Integer, includeAS as Boolean, arSkipCols() as String) as String\r
 133   dim unparseText as String\r
 134   if OrderBy.count = 0 then Throw New Exception("an OrderBy clause is required")\r
 135   unparseText="SELECT ROW_NUMBER() OVER (ORDER BY " & join(OrderBy.ToArray(),",") & ") AS rico_rownum," & UnparseColumnListSkip(arSkipCols) & " FROM " & FromClause\r
 136   if not IsNothing(WhereClause) then unparseText &= " WHERE " & WhereClause\r
 137   if GroupBy.count > 0 then unparseText &= " GROUP BY " & join(GroupBy.ToArray(),",")\r
 138   if not IsNothing(HavingClause) then unparseText &= " HAVING " & HavingClause\r
 139   unparseText="SELECT * FROM (" & unparseText & ")"\r
 140   if includeAS then unparseText &= " AS rico_Main"\r
 141   unparseText &= " WHERE rico_rownum > " & offset & " AND rico_rownum <= " & CStr(offset+numrows)\r
 142   UnparseWithRowNumber=unparseText\r
 143 end Function\r
 144 \r
 145 \r
 146 Public sub Init()\r
 147   SelectList.Clear()\r
 148   GroupBy.Clear()\r
 149   OrderBy.Clear()\r
 150   FromClause=Nothing\r
 151   WhereClause=Nothing\r
 152   HavingClause=Nothing\r
 153   IsDistinct=false\r
 154 end sub\r
 155 \r
 156 \r
 157 ' -------------------------------------------------------------\r
 158 ' Parse a SQL select statement into its major components\r
 159 ' Does not handle:\r
 160 ' 1) union queries\r
 161 ' 2) select into\r
 162 ' 3) more than one space between "group" and "by", or "order" and "by"\r
 163 ' 4) stored procedures\r
 164 ' -------------------------------------------------------------\r
 165 Public function ParseSelect(ByVal sqltext as String) As Boolean\r
 166   dim i As Integer, j As Integer, l As Integer, idx As Integer, parencnt As Integer\r
 167   dim clause As String, ch As String, curfield As String, nexttoken As String, inquote As Boolean, endquote As String\r
 168   Init()\r
 169   ParseSelect=false\r
 170   sqltext=replace(sqltext,vbLf," ")\r
 171   sqltext=" " & replace(sqltext,vbCr," ") & " SELECT "   ' SELECT suffix forces last curfield to be saved\r
 172   'response.write "<p>ParseSelect: " & sqltext & "</p>"\r
 173   l=len(sqltext)\r
 174   parencnt=0\r
 175   inquote=false\r
 176   i=1\r
 177   curfield=""\r
 178   while i<l\r
 179     ch=mid(sqltext,i,1)\r
 180     if inquote then\r
 181       if ch=endquote then\r
 182         if endquote="'" and mid(sqltext,i,2)="''" then\r
 183           curfield &= "'"\r
 184           i=i+1\r
 185         else\r
 186           inquote=false\r
 187         end if\r
 188       end if\r
 189       curfield &= ch\r
 190     elseif ch="'" or ch="""" or ch="`" then\r
 191       inquote=true\r
 192       endquote=ch\r
 193       curfield &= ch\r
 194     elseif ch="[" then\r
 195       inquote=true\r
 196       endquote="]"\r
 197       curfield &= ch\r
 198     elseif ch="(" then\r
 199       parencnt=parencnt+1\r
 200       curfield &= ch\r
 201     elseif ch=")" then\r
 202       if parencnt=0 then exit function  ' sql statement has a syntax error\r
 203       parencnt=parencnt-1\r
 204       curfield &= ch\r
 205     elseif parencnt > 0 then\r
 206       curfield &= ch\r
 207     elseif ch="," then\r
 208       'response.write "<p>" & clause & ": " & server.htmlencode(curfield) & "</p>"\r
 209       select case clause\r
 210         case "SELECT":\r
 211           AddColumn(curfield)\r
 212           curfield=""\r
 213         case "AS":\r
 214           SelectList(SelectList.count-1).name=curfield\r
 215           curfield=""\r
 216           clause="SELECT"\r
 217         case "GROUP BY": ArrayPush(GroupBy,curfield)\r
 218         case "ORDER BY": ArrayPush(OrderBy,curfield)\r
 219         case else: curfield &= ch\r
 220       end select\r
 221     elseif ch=" " then\r
 222       j=InStr(i+1,sqltext," ")\r
 223       if j<1 then\r
 224         curfield &= ch\r
 225       else\r
 226         if ucase(mid(sqltext,j+1,3))="BY " then j=j+3\r
 227         nexttoken=ucase(mid(sqltext,i+1,j-i-1))\r
 228         'wscript.echo "'" & nexttoken & "'"\r
 229         'response.write "<p>" & clause & " : " & nexttoken & " : " & server.htmlencode(curfield) & "</p>"\r
 230         select case nexttoken\r
 231           case "SELECT","INTO","FROM","WHERE","GROUP BY","HAVING","ORDER BY":\r
 232             select case clause\r
 233               case "SELECT":\r
 234                 AddColumn(curfield)\r
 235                 curfield=""\r
 236               case "AS":\r
 237                 SelectList(SelectList.count-1).name=curfield\r
 238                 curfield=""\r
 239               case "FROM":     SetParseField(FromClause,curfield)\r
 240               case "WHERE":    SetParseField(WhereClause,curfield)\r
 241               case "GROUP BY": ArrayPush(GroupBy,curfield)\r
 242               case "HAVING":   SetParseField(HavingClause,curfield)\r
 243               case "ORDER BY": ArrayPush(OrderBy,curfield)\r
 244             end select\r
 245             clause=nexttoken\r
 246             i=j-1\r
 247 \r
 248           case "AS":\r
 249             if clause="SELECT" then\r
 250               AddColumn(curfield)\r
 251               curfield=""\r
 252               clause=nexttoken\r
 253               i=j\r
 254             elseif curfield<>"" then\r
 255               curfield &= ch\r
 256             end if\r
 257 \r
 258           case "DISTINCT":\r
 259             if clause="SELECT" then\r
 260               IsDistinct=true\r
 261               curfield=""\r
 262               i=j\r
 263             elseif curfield<>"" then\r
 264               curfield &= ch\r
 265             end if\r
 266 \r
 267           case else: if curfield<>"" then curfield &= ch\r
 268         end select\r
 269       end if\r
 270     else\r
 271       curfield &= ch\r
 272     end if\r
 273     i=i+1\r
 274   end while\r
 275   ParseSelect=true\r
 276 end function\r
 277 \r
 278 \r
 279 Private Sub ArrayPush(s as ArrayList, ByRef newvalue as string)\r
 280   s.add(newvalue)\r
 281   newvalue=""\r
 282 end sub\r
 283 \r
 284 Private Sub SetParseField(ByRef f as string, ByRef newvalue as string)\r
 285   f=newvalue\r
 286   newvalue=""\r
 287 end sub\r
 288 \r
 289 \r
 290 Public Sub AddColumn(sqlParm as String, Optional nameParm As String = "")\r
 291   SelectList.add(new sqlColumn(sqlParm,nameParm))\r
 292 End Sub\r
 293 \r
 294 \r
 295 ' -------------------------------------------------------------\r
 296 ' Add a join to the from clause\r
 297 ' -------------------------------------------------------------\r
 298 Public Sub AddJoin(ByVal JoinClause As String)\r
 299   if InStr(FromClause," join ")>0 then FromClause="(" & FromClause & ")"  ' required by Access\r
 300   FromClause=FromClause & " " & JoinClause\r
 301 end sub\r
 302 \r
 303 Private Sub SplitSortSpec(ByVal sortspec As String, ByRef sortcol As String, ByRef sortdir As String)\r
 304   sortspec=ucase(sortspec)\r
 305   if right(sortspec,3)="ASC" then\r
 306     sortcol=trim(left(sortspec,len(sortspec)-3))\r
 307     sortdir="ASC"\r
 308   elseif right(sortspec,4)="DESC" then\r
 309     sortcol=trim(left(sortspec,len(sortspec)-4))\r
 310     sortdir="DESC"\r
 311   else\r
 312     sortcol=trim(sortspec)\r
 313     sortdir=""\r
 314   end if\r
 315 End Sub\r
 316 \r
 317 Private Function FindSortColumn(ByVal sortspec As String) As Integer\r
 318   dim i As Integer, findcol As String, finddir As String, sortcol As String, sortdir As String\r
 319   FindSortColumn=-1\r
 320   SplitSortSpec(sortspec, findcol, finddir)\r
 321   for i=0 to OrderBy.count-1\r
 322     SplitSortSpec(OrderBy(i), sortcol, sortdir)\r
 323     if sortcol=findcol then\r
 324       FindSortColumn=i\r
 325       exit for\r
 326     end if\r
 327   next\r
 328 End Function\r
 329 \r
 330 ' -------------------------------------------------------------\r
 331 ' Add sort criteria to the beginning of the order by clause\r
 332 ' -------------------------------------------------------------\r
 333 Public Sub AddSort(ByVal NewSort As String)\r
 334   dim i As Integer, colidx As Integer\r
 335   colidx=FindSortColumn(NewSort)\r
 336   if colidx>=0 then\r
 337     for i=colidx to 1 step -1\r
 338       OrderBy(i)=OrderBy(i-1)\r
 339     next\r
 340     OrderBy(0)=NewSort\r
 341   else\r
 342     OrderBy.insert(0,NewSort)\r
 343   end if\r
 344 end sub\r
 345 \r
 346 ' -------------------------------------------------------------\r
 347 ' Append sort criteria to the order by clause\r
 348 ' -------------------------------------------------------------\r
 349 Public Sub AppendSort(ByVal NewSort As String)\r
 350   OrderBy.add(NewSort)\r
 351 end sub\r
 352 \r
 353 ' -------------------------------------------------------------\r
 354 ' Add a condition to the where clause\r
 355 ' -------------------------------------------------------------\r
 356 Public Sub AddWhereCondition(ByVal NewCondition)\r
 357   AddCondition(WhereClause,NewCondition)\r
 358 end sub\r
 359 \r
 360 ' -------------------------------------------------------------\r
 361 ' Add a condition to the having clause\r
 362 ' -------------------------------------------------------------\r
 363 Public Sub AddHavingCondition(ByVal NewCondition)\r
 364   AddCondition(HavingClause,NewCondition)\r
 365 end sub\r
 366 \r
 367 Private Sub AddCondition(ByRef Clause, ByVal NewCondition)\r
 368   if IsNothing(NewCondition) then exit sub\r
 369   If IsNothing(Clause) Then\r
 370     Clause="(" & NewCondition & ")"\r
 371   Else\r
 372     Clause &= " AND (" & NewCondition & ")"\r
 373   End If\r
 374 End Sub\r
 375 \r
 376 Public Sub DebugPrint(writer as object)\r
 377   dim i as integer\r
 378   writer.write("<p>Parse Result:")\r
 379   writer.write("<table border='1'>")\r
 380   if IsDistinct then writer.write("<tr valign='top'><td>DISTINCT<td>&nbsp;")\r
 381   writer.write("<tr valign='top'><td>COLUMNS:<td><ol>")\r
 382   for i=0 to SelectList.count-1\r
 383     writer.write("<li>" & SelectList(i).Unparse)\r
 384   next\r
 385   writer.write("</ol><tr valign='top'><td>FROM:<td>" & FromClause)\r
 386   if not IsNothing(WhereClause) then writer.write("<tr valign='top'><td>WHERE:<td>" & WhereClause)\r
 387   if GroupBy.count > 0 then writer.write("<tr valign='top'><td>GROUP BY:<td>" & join(GroupBy.ToArray(),"<br>"))\r
 388   if not IsNothing(HavingClause) then writer.write("<tr valign='top'><td>HAVING:<td>" & HavingClause)\r
 389   if OrderBy.count > 0 then writer.write("<tr valign='top'><td>ORDER BY:<td>" & join(OrderBy.ToArray(),"<br>"))\r
 390   writer.write("</table>")\r
 391 End Sub\r
 392 \r
 393 End Class\r