API tools faq
paste
KaoSDlanor

SQL Interpreter

KaoSDlanor
Sep 22nd, 2014
292
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Autohotkey 9.95 KB | None | 0 0
raw download clone embed print report
  1. #SingleInstance,force
  2. ;#Persistent
  3. global GUIW:=510
  4. global GUIH:=490
  5. global nDisplacement=200
  6. global bInputShown:=true
  7. global bCMDOutputShown:=false
  8. global bTableListShown:=false
  9. global bTableListOnColumn:=true
  10. global BackupList
  11. global SearchString
  12.  
  13. gui,INPUT:new,,SQL Input
  14. gui,INPUT:default
  15. gui,INPUT:font,,Courier
  16. Gui,INPUT:Add,Edit,vSearchString gRunSearch -WantReturn -multi
  17. Gui,INPUT:Add,Button,gCancelSearch vCancelSearch,X
  18. Gui,Input:Add,ListView,vTableList gTableList +NoSort,Table
  19. UpdateTableList()
  20. Gui,INPUT:Add,Edit,multi -wrap x0 y0 w500 r15 vSQLSCRIPT,
  21. Gui,INPUT:Add,Button,x0 vRUNSQL gRUNSQL,Run SQL Script
  22. Gui,INPUT:Add,Button,x0 vHideTableList gHideTableList,<
  23. Gui,INPUT:Add,Button,x0 vHideScript gHideScript,^
  24. Gui,INPUT:Add,Text,x0 vRowsE,Awaiting Command
  25. Gui,Input:Add,ListView,vSQLLIST +NoSort gColumnSelect x0,
  26. Gui,INPUT:Add,Edit,vCMDOutput +ReadOnly
  27. Gui,INPUT:Add,Button,vHideOutput gHideOutput,X
  28. GuiControl,Hide,CMDOutput
  29. GuiControl,Hide,HideOutput
  30. GUI,INPUT:+Resize +LabelINPUT
  31. gui,INPUT:show,W%GUIW% H%GUIH%
  32. GuiControl,Focus,SQLSCRIPT
  33. OnMessage(0x0202,"DragRelease")
  34. OnMessage(0x0100,"KeyPressed")
  35. return
  36.  
  37. KeyPressed(wParam)
  38. {
  39.     ;if (wParam=0x0D) and (A_GuiControl="TableList")
  40.         ;msgbox,YEAH!!!
  41.  
  42. }
  43.  
  44. DragRelease()
  45. {
  46.     Gui,INPUT:ListView,TableList
  47.     if (A_GuiControl="SQLSCRIPT") and CopyRow
  48.     {
  49.         tempClip:=Clipboard
  50.         GuiControl,Focus,SQLSCRIPT
  51.         LV_GetText(sTable,CopyRow,1)
  52.         Clipboard:=sTable
  53.         send,^v
  54.         sleep,10
  55.         Clipboard:=tempClip
  56.     }
  57.     CopyRow=
  58. }
  59.  
  60. CancelSearch:
  61.     ClearSearch()
  62. Return
  63.  
  64. ClearSearch()
  65. {
  66.     if (SearchString="") and bTableListOnColumn
  67.     {
  68.         UpdateTableList()
  69.     }
  70.     else if (SearchString<>"")
  71.     {
  72.         if BackupList
  73.             TableViewContents:=BackupList
  74.         GuiControl,,SearchString,
  75.         ShowTableList(TableViewContents)
  76.     }
  77. }
  78.  
  79. RunSearch:
  80.     Gui,INPUT:Submit,NoHide
  81.     Gui,INPUT:ListView,TableList
  82.     TableViewContents:=[BackupList[1]]
  83.     loop,% BackupList.MaxIndex()-1
  84.         if RegExMatch(BackupList[A_Index+1],"i)" . SearchString)
  85.         {
  86.             TableViewContents.Insert(BackupList[A_Index+1])
  87.         }
  88.     ShowTableList(TableViewContents)
  89. Return
  90.  
  91. TableList:
  92.     Gui,ListView,TableList
  93.     if A_GuiEvent=DoubleClick
  94.     {
  95.         LV_GetText(sTable,A_EventInfo,1)
  96.         UpdateTableList(sTable)
  97.     }
  98.     else if A_GuiEvent=D
  99.         global CopyRow:=A_EventInfo
  100.     ;else
  101.         ;ToolTip,% A_GuiEvent . " - " . A_EventInfo
  102. return
  103.  
  104.  
  105. UpdateTableList(sTable:="")
  106. {
  107.     Gui,ListView,TableList
  108.     If bTableListOnColumn
  109.     {
  110.         sOutput:=RegExReplace(RunCMDSQL("select name from sys.tables order by name"),"^[^`n`r]+[`n`r]+[^`n`r]+[`n`r]+","",,1)
  111.         global TableViewContents=["Tables"]
  112.         bTableListOnColumn:=false
  113.     }
  114.     else
  115.     {
  116.         sCMD:="select COL.name from sys.columns as COL inner join sys.tables as TAB on COL.object_id=TAB.object_id where TAB.name='" . RegExReplace(sTable,"\R","") . "' order by COL.name"
  117.         sOutput:=RegExReplace(RunCMDSQL(sCMD),"^[^`n`r]+[`n`r]+[^`n`r]+[`n`r]+","",,1)
  118.         global TableViewContents=["Columns in " . sTable]
  119.         bTableListOnColumn:=true
  120.     }
  121.     sOutput:=RegExReplace(sOutput,"\R\(\d+ rows affected\)","")
  122.     atPos:=1
  123.     while true
  124.     {
  125.         sMatch:=RegExReplace(sOutput,"sUD)^([^\n]+)\s*`n.*$","$1",,1)
  126.         sOutput:=RegExReplace(sOutput,"U)^([^\n]+)\s*`n","",,1)
  127.         if not sMatch
  128.             break
  129.         TableViewContents.Insert(sMatch)
  130.     }
  131.     ;GuiControl,,SearchString,
  132.     BackupList:=TableViewContents
  133.     ShowTableList(TableViewContents)
  134.     LV_ModifyCol(1,"AutoHdr")
  135. }
  136.  
  137. ShowTableList(tList)
  138. {
  139.     Gui,ListView,TableList
  140.     LV_Delete()
  141.     while LV_DeleteCol(1)
  142.     {
  143.  
  144.     }
  145.  
  146.     LV_InsertCol(,"",tList[1])
  147.     loop,% tList.MaxIndex()-1
  148.     {
  149.         if (RegExMatch(tList[A_Index+1],"^\R*$")=0)
  150.         {
  151.             ;if bTableListOnColumn
  152.                 ;msgbox,% A_Index . "/" . tList.MaxIndex() . " = " . tList[A_Index+1]
  153.             LV_Add("",tList[A_Index+1])
  154.         }
  155.         if (tList.MaxIndex()-A_Index-1)=0
  156.             break
  157.     }
  158. }
  159.  
  160. INPUTClose:
  161.     If Clipboard=%strOutput%
  162.         if bCMDOutputShown
  163.             Clipboard:=tempClip
  164.     ExitApp
  165. return
  166.  
  167. INPUTSize:
  168.     if ErrorLevel=1
  169.         return
  170.     global GUIW:=A_GuiWidth
  171.     global GUIH:=A_GuiHeight
  172.     InputResize()
  173. return
  174.  
  175. RunCMDSQL(sCMD)
  176. {
  177.     IfExist,SQLOUTPUT
  178.         FileDelete,SQLOUTPUT
  179.     RunWait,% ComSpec " /C sqlcmd -s ^| -U aura -P pos -d bbsql -Q """ . sCMD . """>SQLOUTPUT",,hide
  180.     FileRead,sOutput,SQLOUTPUT
  181.     FileDelete,SQLOUTPUT
  182.     return sOutput
  183. }
  184.  
  185. InputResize()
  186. {
  187.     GUIW:=GUIW-(bTableListShown ? nDisplacement : 0)
  188.     SearchStringX:=0
  189.     SearchStringY:=0
  190.     SearchStringW:=(bTableListShown ? nDisplacement-25 : 0)
  191.     SearchStringH:=25
  192.     CancelSearchX:=SearchStringW
  193.     CancelSearchY:=0
  194.     CancelSearchW:=25
  195.     CancelSearchH:=25
  196.     TableListX:=0
  197.     TableListY:=SearchStringH
  198.     TableListW:=(bTableListShown ? nDisplacement : 0)
  199.     TableListH:=GUIH-TableListY
  200.     SQLSCRIPTX:=(bTableListShown ? nDisplacement : 0)
  201.     SQLSCRIPTY:=0
  202.     SQLSCRIPTW:=GUIW
  203.     SQLSCRIPTH:=(bInputShown ? GUIH/2-25 : 0)
  204.     RUNSQLX:=(bTableListShown ? nDisplacement : 0)
  205.     RUNSQLY:=SQLSCRIPTH
  206.     RUNSQLW:=GUIW
  207.     RUNSQLH:=(bInputShown ? 25 : 0)
  208.     HideTableListX:=(bTableListShown ? nDisplacement : 0)
  209.     HideTableListY:=RUNSQLY+RUNSQLH
  210.     HideTableListW:=25
  211.     HideTableListH:=25
  212.     HideScriptX:=HideTableListW+(bTableListShown ? nDisplacement : 0)
  213.     HideScriptY:=HideTableListY
  214.     HideScriptW:=GUIW-HideTableListW
  215.     HideScriptH:=25
  216.     RowsEX:=(bTableListShown ? nDisplacement : 0)
  217.     RowsEY:=HideScriptY+HideScriptH
  218.     RowsEW:=GUIW
  219.     RowsEH:=15
  220.     SQLLISTX:=(bTableListShown ? nDisplacement : 0)
  221.     SQLLISTY:=RowsEY+RowsEH
  222.     SQLLISTW:=GUIW
  223.     SQLLISTH:=GUIH-SQLLISTY-(bCMDOutputShown ? 25 : 0)
  224.     CMDOutputX:=(bTableListShown ? nDisplacement : 0)
  225.     CMDOutputY:=SQLLISTY+SQLLISTH
  226.     CMDOutputW:=GUIW-25
  227.     CMDOutputH:=25
  228.     HideOutputX:=CMDOutputX+CMDOutputW
  229.     HideOutputY:=CMDOutputY
  230.     HideOutputW:=25
  231.     HideOutputH:=25
  232.  
  233.     GuiControl,Move,SearchString,X%SearchStringX% Y%SearchStringY% W%SearchStringW% H%SearchStringH%
  234.     GuiControl,Move,CancelSearch,X%CancelSearchX% Y%CancelSearchY% W%CancelSearchW% H%CancelSearchH%
  235.     GuiControl,Move,TableList,X%TableListX% Y%TableListY% W%TableListW% H%TableListH%
  236.     GuiControl,Move,SQLSCRIPT,X%SQLSCRIPTX% Y%SQLSCRIPTY% W%SQLSCRIPTW% H%SQLSCRIPTH%
  237.     GuiControl,Move,RUNSQL,X%RUNSQLX% Y%RUNSQLY% W%RUNSQLW% H%RUNSQLH%
  238.     GuiControl,MoveDraw,HideTableList,X%HideTableListX% Y%HideTableListY% W%HideTableListW% H%HideTableListH%
  239.     GuiControl,MoveDraw,HideScript,X%HideScriptX% Y%HideScriptY% W%HideScriptW% H%HideScriptH%
  240.     GuiControl,MoveDraw,RowsE,X%RowsEX% Y%RowsEY% W%RowsEW% H%RowsEH%
  241.     GuiControl,MoveDraw,SQLLIST,X%SQLLISTX% Y%SQLLISTY% W%SQLLISTW% H%SQLLISTH%
  242.     if bCMDOutputShown
  243.     {
  244.         GuiControl,MoveDraw,CMDOutput,X%CMDOutputX% Y%CMDOutputY% W%CMDOutputW% H%CMDOutputH%
  245.         GuiControl,MoveDraw,HideOutput,X%HideOutputX% Y%HideOutputY% W%HideOutputW% H%HideOutputH%
  246.     }
  247.     GUIW:=GUIW+(bTableListShown ? nDisplacement : 0)
  248. }
  249.  
  250. ColumnSelect:
  251.     Gui,ListView,SQLLIST
  252.     if A_GuiEvent=DoubleClick
  253.     {
  254.         global strOutput
  255.         global RowSelected:=A_EventInfo
  256.         LV_GetText(strOutput,RowSelected,1)
  257.         while true
  258.         {
  259.             LV_GetText(ColOutput,RowSelected,A_Index+1)
  260.             if ColOutput=
  261.                 break
  262.             strOutput:=strOutput . ", " . ColOutput
  263.         }
  264.         GuiControl,,CMDOutput,% strOutput
  265.         if not bCMDOutputShown
  266.             global tempClip:=Clipboard
  267.         bCMDOutputShown:=true
  268.         Clipboard:=strOutput
  269.         GuiControl,Show,CMDOutput
  270.         GuiControl,Show,HideOutput
  271.         InputResize()
  272.     }
  273.     else if A_GuiEvent=ColClick
  274.     {
  275.         if bCMDOutputShown
  276.         {
  277.             LV_GetText(strOutput,RowSelected,A_EventInfo)
  278.             GuiControl,,CMDOutput,% strOutput
  279.             Clipboard:=strOutput
  280.         }
  281.     }
  282. Return
  283.  
  284. HideOutput:
  285.     bCMDOutputShown:=false
  286.     GuiControl,Hide,CMDOutput
  287.     GuiControl,Hide,HideOutput
  288.     If Clipboard=%strOutput%
  289.         Clipboard:=tempClip
  290.     InputResize()
  291. Return
  292.  
  293. FormatSQL(ByRef sOutput,ByRef sRowsEffected,ByRef sColumns,ByRef isError)
  294. {
  295.     sOutput:=RegExReplace(sOutput,"\R`t","")
  296.     sOutput:=RegExReplace(sOutput,"\s*([\|`n])\s*","$1")
  297.     isError:=RegExMatch(sOutput,"^Msg \d+, Level \d+, State \d+, Server ")
  298.     if isError
  299.         return false
  300.     sColumns:=RegExReplace(sOutput,"sxU)`n(.*)`n\-.*?","$1")
  301.     sOutput:=RegExReplace(sOutput,"sx)^.*?\-[\-\|]*","",,1)
  302.     sRowsEffected:=RegExReplace(sOutput,".*(\(\d+ rows affected\)).*","$1")
  303.     sOutput:=RegExReplace(sOutput,"\R\(\d+ rows affected\)","")
  304. }
  305.  
  306. clear()
  307. {
  308.     LV_Delete()
  309.     while LV_DeleteCol(1)
  310.     {
  311.  
  312.     }
  313. }
  314.  
  315. RUNSQL:
  316.     gui,INPUT:Submit,nohide
  317.     Gui,Listview,SQLLIST
  318.  
  319.     sOutput:=RunCMDSQL(RegExReplace(SQLSCRIPT,"\R"," "))
  320.  
  321.     FormatSQL(sOutput,sRowsEffected,sColumns,isError)
  322.  
  323.     nPos:=1
  324.     clear()
  325.     if (isError=0)
  326.     {
  327.         RegExReplace(sColumns,"\|","",nCount)
  328.         nCount:=nCount+1
  329.         if (RegExMatch(sColumns,"[^\|`n]")<>0)
  330.         {
  331.             while (sColumns<>"")
  332.             {
  333.                 RegExMatch(sColumns,"^([^\|`n]*)",sColTitle)
  334.                 sColumns:=RegExReplace(sColumns,"^([^\|`n]*[\|`n])","",,1)
  335.                     LV_InsertCol(A_Index,,sColTitle)
  336.             }
  337.         }
  338.         else
  339.         {
  340.             loop,% nCount
  341.             {
  342.                 LV_InsertCol(A_Index,,"")
  343.             }
  344.         }
  345.         while (sOutput<>"")
  346.         {
  347.             oRow:=[]
  348.             RegExMatch(sOutput,"^(.+?)`n",sRow)
  349.             sOutput:=RegExReplace(sOutput,"^.+?`n","",,1)
  350.             while (sRow<>"")
  351.             {
  352.                 sMatch:=RegExReplace(sRow,"^(.+?)[`n\|].*$","$1")
  353.                 if not sMatch
  354.                     break
  355.                 oRow.Insert(sMatch)
  356.                 sRow:=RegExReplace(sRow,"^.+?[`n\|]","",,1)
  357.             }
  358.             LV_Add("",oRow*)
  359.         }
  360.         loop,% nCount
  361.         {
  362.             LV_ModifyCol(A_Index,"AutoHdr")
  363.         }
  364.         GuiControl,,RowsE,% sRowsEffected
  365.     }
  366.     else
  367.     {
  368.         GuiControl,,RowsE,% RegExReplace(sOutput,"`n"," | ")
  369.     }
  370. return
  371.  
  372. HideScript:
  373.     if bInputShown
  374.     {
  375.         bInputShown:=false
  376.         GuiControl,,HideScript,v
  377.         GuiControl,Hide,SQLSCRIPT
  378.         GuiControl,Hide,RUNSQL
  379.     }
  380.     else
  381.     {
  382.         bInputShown:=true
  383.         GuiControl,,HideScript,^
  384.         GuiControl,Show,SQLSCRIPT
  385.         GuiControl,Show,RUNSQL
  386.     }
  387.     InputResize()
  388. Return
  389.  
  390. HideTableList:
  391.     Gui,INPUT:+LastFound
  392.     WinGetPos,WinX,WinY
  393.     WinGet,isMax,MinMax
  394.     If bTableListShown
  395.     {
  396.         GuiControl,,HideTableList,% "<"
  397.         bTableListShown:=false
  398.         WinX:=WinX+nDisplacement
  399.         GUIW:=GUIW-nDisplacement
  400.         if isMax<>1
  401.             gui,INPUT:show,X%WinX% W%GUIW%
  402.         InputResize()
  403.     }
  404.     else
  405.     {
  406.         GuiControl,,HideTableList,% ">"
  407.         bTableListShown:=true
  408.         WinX:=WinX-nDisplacement
  409.         GUIW:=GUIW+nDisplacement
  410.         if isMax<>1
  411.             gui,INPUT:show,X%WinX% W%GUIW%
  412.         InputResize()
  413.     }
  414. return
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!