Sub InitSQLColours
SQLColours(0) = Colors.RGB(0, 0, 0)
SQLColours(1) = Colors.RGB(0, 0, 220)
SQLColours(2) = Colors.RGB(200, 60, 0)
SQLColours(3) = Colors.RGB(0, 0, 220)
SQLColours(4) = Colors.RGB(220, 0, 0)
SQLColours(5) = Colors.RGB(220, 0, 0)
SQLColours(6) = Colors.RGB(0, 80, 255)
SQLColours(7) = Colors.RGB(128, 0, 255)
SQLColours(8) = Colors.RGB(0, 200, 0) 'literal
SQLColours(9) = Colors.RGB(128, 128, 0) 'for brackets
SQLColours(10) = Colors.RGB(140, 100, 0) 'for table names
SQLColours(11) = Colors.RGB(0, 140, 100) 'for view names
SQLColours(12) = Colors.RGB(100, 32, 200) 'for SQL replace strings
SQLColours(13) = Colors.RGB(100, 32, 200) 'for variable SQL replace strings
SQLColours(14) = Colors.RGB(100, 32, 200) 'for custom function, for now only SD2XLD and CDMinusXDays
End Sub
Sub FormatSQL(strSQL As String) As CSBuilder
Dim i As Int
Dim cs As CSBuilder
Dim arrSQLWords() As SQLWord
Dim UB As Int
Dim strWordLower As String
Dim tf As Typeface
tf = Typeface.CreateNew(Typeface.MONOSPACE, Typeface.STYLE_ITALIC)
cs.Initialize.Color (Colors.Black)
If bNoSQLFormattingAtAll Or bNoSQLFormatting Then
Return cs
End If
'note that we shouldn't remove comments here as otherwise the SQL in the editor will change!
'-------------------------------------------------------------------------------------------
'this is very quick, about 1 msecs
arrSQLWords = GetSQLWords(strSQL, 0, "", False, False)
' For i = 0 To arrSQLWords.Length - 1
' General.RunLog("FormatSQL, Word: |" & arrSQLWords(i).strWord & "|")
' Next
If arrSQLWords.Length = 0 Then
Return cs
End If
General.StartSW (3)
UB = arrSQLWords.Length - 1
For i = 0 To UB
'General.RunLog("FormatSQL, Word: " & arrSQLWords(i).strWord)
If arrSQLWords(i).bWord Then
If arrSQLWords(i).bLiteral Or arrSQLWords(i).bCommentStart Or arrSQLWords(i).bComment Or arrSQLWords(i).bCommentEnd Then
'green for literal string or comment
'-----------------------------------
If arrSQLWords(i).bLiteral Or arrSQLWords(i).bComment Then
cs.Color(SQLColours(8)).Append(arrSQLWords(i).strWord).Pop
Else
cs.Color(SQLColours(4)).Append(arrSQLWords(i).strWord).Pop
End If
Else 'If arrSQLWords(i).bLiteral
strWordLower = arrSQLWords(i).strWord.ToLowerCase
If mapSQLWordTypes.ContainsKey(strWordLower) Then
Select Case mapSQLWordTypes.Get(strWordLower)
Case 1
'deal with SQL key word in square brackets or double quotes
'no need to check previous byte as if not matching, SQL will fail Prepare
'------------------------------------------------------------------------
If arrSQLWords(i).lPos > 0 And _
(arrSQLWords(i).btNextByte = 34 Or arrSQLWords(i).btNextByte = 93) Then
cs.Append (arrSQLWords(i).strWord)
Else
cs.Bold.Color(SQLColours(1)).Append(arrSQLWords(i).strWord).Pop.Pop
End If
Case 2
cs.Bold.Color(SQLColours(2)).Append(arrSQLWords(i).strWord).Pop.Pop
Case 3
cs.Color(SQLColours(3)).Append(arrSQLWords(i).strWord).Pop
Case 4
cs.Color(SQLColours(4)).Append(arrSQLWords(i).strWord).Pop
Case 5
cs.Color(SQLColours(4)).Append(arrSQLWords(i).strWord).Pop
Case 6
cs.Color(SQLColours(6)).Append(arrSQLWords(i).strWord).Pop
Case 7
cs.Color(SQLColours(7)).Append(arrSQLWords(i).strWord).Pop
Case 10
'tables, take care for fields that have same name as a table or view
'-------------------------------------------------------------------
If arrSQLWords(i).strPreviousWord.ToLowerCase = "from" Or _
arrSQLWords(i).strPreviousWord.ToLowerCase = "join" Or _
arrSQLWords(i).strPreviousWord.ToLowerCase = "table" Or _
arrSQLWords(i).strPreviousWord.ToLowerCase = "into" Or _
arrSQLWords(i).strPreviousWord.ToLowerCase = "update" Or _
arrSQLWords(i).strPreviousWord.ToLowerCase = "exists" Then
cs.Underline.Bold.Color(SQLColours(11)).Clickable("Table", _
arrSQLWords(i).strWord).Append(arrSQLWords(i).strWord).Pop.Pop.Pop.Pop
Else
cs.Append (arrSQLWords(i).strWord)
End If
Case 11
'views, take care for fields that have same name as a table or view
'-----------------------------------------------------------------
If arrSQLWords(i).strPreviousWord.ToLowerCase = "from" Or _
arrSQLWords(i).strPreviousWord.ToLowerCase = "join" Or _
arrSQLWords(i).strPreviousWord.ToLowerCase = "view" Then
cs.Underline.Bold.Color(SQLColours(11)).Clickable("View", _
arrSQLWords(i).strWord).Append(arrSQLWords(i).strWord).Pop.Pop.Pop.Pop
Else
cs.Append (arrSQLWords(i).strWord)
End If
Case 12
'SQL replace strings
cs.Underline.Color(SQLColours(12)).Clickable("Shortcut", _
arrSQLWords(i).strWord).Append(arrSQLWords(i).strWord).Pop.Pop.Pop
Case 13
'SQL replace strings, based on variable
cs.Typeface(tf).Underline.Color(SQLColours(13)).Clickable("VariableShortcut", _
arrSQLWords(i).strWord).Append(arrSQLWords(i).strWord).Pop.Pop.Pop.Pop
Case 14
'custom SQL functions for now only SD2XLD
cs.Typeface(tf).Color(SQLColours(6)).Append(arrSQLWords(i).strWord).Pop.Pop
End Select
Else 'If mapSQLWordTypes.ContainsKey(strWordLower)
'cs.Color(SQLColours(0)).Append(arrSQLWords(i).strWord).Pop
cs.Append (arrSQLWords(i).strWord)
End If 'If mapSQLWordTypes.ContainsKey(strWordLower)
End If 'If arrSQLWords(i).bLiteral
Else 'If arrSQLWords(i).bWord
If arrSQLWords(i).strWord.Trim.Length = 0 Then
cs.Append (arrSQLWords(i).strWord)
Else
'only problem with this is that commas and % will be done like brackets
cs.Color(SQLColours(9)).Append(arrSQLWords(i).strWord).Pop
End If
End If 'If arrSQLWords(i).bWord
Next
cs.PopAll
cs.EnableClickEvents (edtSQL)
'formatting takes a bit longer about 7 msecs for the Sudoku SQL
'--------------------------------------------------------------
'General.RunLog ("FormatSQL, time Formatting: " & General.StopSW(3) & " msecs")
Return cs
End Sub