 
-1 |
|
Ik zit met een dynamische query waar in runtime filter velden aan toegevoegd moeten worden. Daarom probeer ik in deze query de select statement en de where statement te onderscheiden.
Omdat er verschillende subqueries in kunnen zitten had ik gehoopt met REGEX hier wat verschil te kunnen maken.
Je kan bijvoorbeeld niet gewoon zoeken naar de laatste where omdat er binnen een where ook nog subqueries kunnen zijn met (select * from blaat where foo = bar) hetzelfde geld voor de eerste where want deze kan komen uit een subquery in de select.
Om die reden wou ik naar regex stappen maar krijg het niet voor elkaar.
Bijvoorbeeld:
Query:
select A, (select B from C where D = F) from G where H exists (select I from J where K = L) and M = N order by O,P Desc
Regex:
select (.*) from (.*) where (.*)
Result:
* A, (select B from C where D = F) from G where H exists (select I
* J
* K = L) and M = N order by O,P Desc
Query: select A, (select B from C where D = F) from G where H exists (select I from J where K = L) and M = N order by O,P Desc Regex: select (.*) from (.*) where (.*) Result: * A, (select B from C where D = F) from G where H exists (select I * J * K = L) and M = N order by O,P Desc
Maar het resultaat dat ik wil is:
Result:
* A, (select B from C where D = F)
* G
* H exists (select I From J where K = L) and M = N order by O,P Desc
Result: * A, (select B from C where D = F) * G * H exists (select I From J where K = L) and M = N order by O,P Desc
Dit lijkt met alleen met een recursive regex te doen. Maar weet niet hoe. Probeer nu niet een regex te maken die werkt voor de regel die ik als voorbeeld geef wat dit kan evenzeer met 5 subqueries en 3 subsubqueries binnen een andere subquery zijn.
De query is dynamisch.
Iemand een idee voor een oplossing? JeXuS  ?
Probleem opgelost:
Zonder regex omdat het blijkbaar niet mogelijk is dit binnen 1 reguliere expressie te schrijven.
Ik heb het nu opgelost door mijn eigen functie te schrijven binnen .NET:
Private Function ParseQuery(ByVal query As String, ByVal filters As String) As String
Dim orgiQuery As String = query
query = orgiQuery.ToLower
Dim openQuotes As New ArrayList()
Dim closeQuotes As New ArrayList()
Dim whereIndex As Integer = -1
Dim orderIndex As Integer = -1
Dim groupIndex As Integer = -1
Dim startIndex As Integer = 0
While query.IndexOf("(", startIndex) > -1
startIndex = query.IndexOf("(", startIndex) + 1
openQuotes.Add(startIndex)
End While
startIndex = 0
While query.IndexOf(")", startIndex) > -1
startIndex = query.IndexOf(")", startIndex) + 1
closeQuotes.Add(startIndex)
End While
startIndex = 0
While query.IndexOf("where", startIndex) > -1
startIndex = query.IndexOf("where", startIndex) + 1
Dim blnFound As Boolean = True
For i As Integer = 0 To openQuotes.Count - 1
If CInt(openQuotes(i)) < startIndex And CInt(closeQuotes(i)) > startIndex Then
blnFound = False
Exit For
End If
Next
If blnFound Then
whereIndex = startIndex
End If
End While
If whereIndex = -1 Then
startIndex = 0
While query.IndexOf("order by", startIndex) > -1
startIndex = query.IndexOf("order by", startIndex) + 1
Dim blnFound As Boolean = True
For i As Integer = 0 To openQuotes.Count - 1
If CInt(openQuotes(i)) < startIndex And CInt(closeQuotes(i)) > startIndex Then
blnFound = False
Exit For
End If
Next
If blnFound Then
orderIndex = startIndex
End If
End While
startIndex = 0
While query.IndexOf("group by", startIndex) > -1
startIndex = query.IndexOf("group by", startIndex) + 1
Dim blnFound As Boolean = True
For i As Integer = 0 To openQuotes.Count - 1
If CInt(openQuotes(i)) < startIndex And CInt(closeQuotes(i)) > startIndex Then
blnFound = False
Exit For
End If
Next
If blnFound Then
groupIndex = startIndex
End If
End While
End If
If groupIndex > -1 Then
orgiQuery = orgiQuery.Insert(groupIndex - 1, " where " & filters)
ElseIf orderIndex > -1 Then
orgiQuery = orgiQuery.Insert(orderIndex - 1, " where " & filters)
ElseIf whereIndex > -1 Then
orgiQuery = orgiQuery.Insert(whereIndex + 4, " " & filters & " and ")
Else
orgiQuery &= " where blaat = blaat"
End If
Return orgiQuery
End Function
Private Function ParseQuery(ByVal query As String, ByVal filters As String) As String Dim orgiQuery As String = query query = orgiQuery.ToLower Dim openQuotes As New ArrayList() Dim closeQuotes As New ArrayList() Dim whereIndex As Integer = -1 Dim orderIndex As Integer = -1 Dim groupIndex As Integer = -1 Dim startIndex As Integer = 0 While query.IndexOf("(", startIndex) > -1 startIndex = query.IndexOf("(", startIndex) + 1 openQuotes.Add(startIndex) End While startIndex = 0 While query.IndexOf(")", startIndex) > -1 startIndex = query.IndexOf(")", startIndex) + 1 closeQuotes.Add(startIndex) End While startIndex = 0 While query.IndexOf("where", startIndex) > -1 startIndex = query.IndexOf("where", startIndex) + 1 Dim blnFound As Boolean = True For i As Integer = 0 To openQuotes.Count - 1 If CInt(openQuotes(i)) < startIndex And CInt(closeQuotes(i)) > startIndex Then blnFound = False Exit For End If Next If blnFound Then whereIndex = startIndex End If End While If whereIndex = -1 Then startIndex = 0 While query.IndexOf("order by", startIndex) > -1 startIndex = query.IndexOf("order by", startIndex) + 1 Dim blnFound As Boolean = True For i As Integer = 0 To openQuotes.Count - 1 If CInt(openQuotes(i)) < startIndex And CInt(closeQuotes(i)) > startIndex Then blnFound = False Exit For End If Next If blnFound Then orderIndex = startIndex End If End While startIndex = 0 While query.IndexOf("group by", startIndex) > -1 startIndex = query.IndexOf("group by", startIndex) + 1 Dim blnFound As Boolean = True For i As Integer = 0 To openQuotes.Count - 1 If CInt(openQuotes(i)) < startIndex And CInt(closeQuotes(i)) > startIndex Then blnFound = False Exit For End If Next If blnFound Then groupIndex = startIndex End If End While End If If groupIndex > -1 Then orgiQuery = orgiQuery.Insert(groupIndex - 1, " where " & filters) ElseIf orderIndex > -1 Then orgiQuery = orgiQuery.Insert(orderIndex - 1, " where " & filters) ElseIf whereIndex > -1 Then orgiQuery = orgiQuery.Insert(whereIndex + 4, " " & filters & " and ") Else orgiQuery &= " where blaat = blaat" End If Return orgiQuery End Function
|
Wat is jouw favoriete javascript framework? (S: 18, R: 2)
Gesponsorde links
Actiefste leden van de maand
