LuaPlSql plugin

There is a little demo included that represents the "wrap" example Plug-In. I guess is also helps is you know the Lua scripting language.
 
I would like to see some topic devoted to plugin announcements and everytime a new one is added, or an existing one is updated, there would be a post to it. Otherwise I have to just scan the page and hope I recognize that something has changed.
 
function names are case sensitive. using the util/main.lua as my base, when I tried SYS.tnsnames() I got an error, but with SYS.TNSNAMES() it worked.

Here are some tries of mine, most worked.

Put this after the "-- Menu" line

Code:
local menuItem = AddMenu("Lua / Util / Test1", Test1)
Put these before the "-- Menu" line

Code:
-- trying to figure out if LUA is useful for me
local function Test1()

-- this one works, always show so that i know plugin still working
  msg = SYS.TNSNAMES()
	plsql.ShowMessage(msg)

-- this demos literals
--	msg = "Hello World"
--  plsql.ShowMessage("Hello World")

-- these demo functions, uncomment the trailing ShowMessage to see results
--  msg = SYS.OracleHome()
--  msg = IDE.GetBrowserItems("TABLES",1)
--  msg = IDE.GetWindowCount()
--  which = IDE.Connected()
--  msg = IDE.GetCursorWord()	
--  msg = SQL.Execute("select count(*) from dual")
--  msg = IDE.SetStatusMessage("hello worldie")
--  msg = SQL.ErrorMessage()
--	plsql.ShowMessage(msg)

-- these demo procedures
--	IDE.CreateWindow(2,"Hello World")
--  IDE.SetCursor(10,47)

-- this sets a boolean
--	msg = IDE.GetSelectedText()
--  if which
--  then
--    msg = "Connected"
--  else
--    msg = "Not Connected"
--  end
--	plsql.ShowMessage(msg)
	

-- these don't work for some reason
--  IDE.ShowDialog("BREAKPOINTS",test) -- does not work	
--  msg = SYS.DelphiVersion() -- does not work
end
 
The demo, as supplied, doesn't seem to handle the two different menuitems correctly - wrap should only be active for certain types of objects, but tnsnames should be active for any.

Change the -- menu lines with

Code:
-- Menus
local menuItem1 = AddMenu("Lua / Util / Wrap program unit", WrapUnit)
local menuItem2 = AddMenu("Lua / Util / Open TNS file", OpenTNSFile)
local menuItem3 = AddMenu("Lua / Util / Test1", Test1)
and change the "IDE.MenuState(menuItem" reference in the OnBrowserChange unit to "IDE.MenuState(menuItem1"
 
Scott,

Have you managed to write any addons using this Plugin yet ? Would be nice to see a working example of what it can do.

Cheers,
D.
 
It can be said that luaplsql is very good open source plugin and with our help will be able to turn into
complete replacement for Browser Extender. Of course as most open source utilities it's not so friendly
as there commercial alternatives. But it's absolutely free!!!
Since 0.9 It become rather stable and fully meet my requirements.
There are a few steps you must learn to start working with luaplsql:
1- You must create your own file in lua\NewDir\main.lua to write the functionality you need.
the file should contain the initialization code:

Code:
--The variable to access to the new menu items list
local AddMenu = ...
--Shortcuts for easy code writing
local plsql = plsql
local SYS, IDE, SQL = plsql.sys, plsql.ide, plsql.sql

--Available callbacks
return {
	OnActivate,
	OnDeactivate,
	CanClose,
	AfterStart,
	OnBrowserChange,
	OnWindowChange,
	OnWindowCreate,
	OnWindowCreated,
	OnWindowClose,
	OnConnectionChange,
	OnPopup,
	OnMainMenu,
	OnTemplate,
	OnFileLoaded,
	OnFileSaved,
	About,
	CommandLine,
	RegisterExport,
	ExportInit,
	ExportFinished,
	ExportPrepare,
	ExportData
}

2 - to define new menu item you need to write something like

Code:
do
	
	local function SomeFunction()
          --something to do
	end

	AddMenu(SomeFunction, "&Lua / Some Function Name")
end

3 - to define callback you need to write something like

Code:
local function OnActivate()
  --to do some initialization
end

4 - You should see the plugindoc.pdf :) because almost all those functions are defined in plsql.ide,plsql.sql package
It can be useful if you can explore the source code for better understand what happens

I needed auto replacement for static type method for two years. There is the example:
Code:
local function GetTypeMethod(ObjectName,SubObject)
    --Получение информации об объекте для которого вызван метод
    local OldSubObject=SubObject
    --Построение графического интерфейса
    -- Creates a list and sets its items, dropdown and amount of visible items
     local list= iup.list { dropdown="NO", visible_items=10,EDITBOX ="YES",size="300x200y"
                      ,VISIBLELINES = 10, SCROLLBAR = "YES"
                    }

    -- Creates frame with dropdown list and sets its title
     local frm_list = iup.frame {list
                ; title = ObjectName}

    -- Creates a dialog with the the frames with three lists and sets its title
     local dlg = iup.dialog {iup.hbox {frm_list}
          ; title = "Select method"}

     --Populate method list
     local function RefreshList()
      	local nvI=0;
        local sql = [[
            select  distinct lower(t.method_name)
              from dba_type_methods t
             where t.type_name=upper(']].. ObjectName ..[[')
               and upper(t.method_name) like upper(']].. SubObject ..[[%')
             order by lower(t.method_name)
      	]]
        list[1]=nil
    		if SQL.Execute(sql) ~= 0 then
    			ShowMessage(SQL.ErrorMessage())
    		else

      		while true do
      			local svName = SQL.Field(1)
      			if SQL.Eof() then break end
      			nvI=nvI+1
            list[nvI]=svName
      			SQL.Next()
      		end
    		end
        --If the list contains only one item select one
        if nvI==1 then
           list["VALUE"]=string.lower(list[1])
           list["CARET"]=string.len(list[1])+1
        else
           list["VALUE"]=string.lower(SubObject)
           list["CARET"]=string.len(SubObject)+1
        end
     end

    function list:k_any(k)
      if k==iup.K_CR then
        dlg:hide()
        --delete old method imputed before
        if OldSubObject ~= "" then
          IDE.KeyPress(8,4)
          --IDE.Refresh()
        end
        do
          --Insert selected method
          IDE.InsertText(list["VALUE"])
        end
      end
      if k==iup.K_ESC then
        --exit
        dlg:hide()
      end;
      if k==iup.K_sCR then
         --Refresh list by new filter
         SubObject=list["VALUE"]
         RefreshList()
      end
      return k
    end
    -- Show dialog
    dlg:showxy(iup.CENTER, iup.CENTER)
    RefreshList()
    iup.MainLoop()
    dlg:destroy()
end
local function AutoReplaceSTM()
    --Get Type name and method
    local nvX = IDE.GetCursorX()
    local nvY =IDE.GetCursorY()
    IDE.SetSelection(IDE.LineIndex(nvY-1),IDE.GetSelection())
    local svText=IDE.GetSelectedText()
    IDE.SetCursor(nvX,nvY)
    local NewLine=lpeg.P("\r\n")
    local Com1 = lpeg.P("--")*((lpeg.P(1)-NewLine)^0)*NewLine
    local Com2 = lpeg.P("/*")* ( (lpeg.P(1)-lpeg.P("*/"))^0 ) *lpeg.P("*/")
    local Space = lpeg.S(" \r\n\t:=[]()")+Com1+Com2
    local Delim=lpeg.S(".")
    local Any=(lpeg.P(1)-Space-Delim)
    local Tp=lpeg.C(Any^0)*((Delim*lpeg.C(Any^0)))*(-lpeg.P(1))
    local G=(Space^0*((Any+Delim)-Tp)*Space^0)^0*Space^0*Tp
    local svRes=lpeg.match(lpeg.Ct(G),svText)
   -- ShowMessage(svRes[1])
    --ShowMessage(svRes[2])
    if svRes==nil then
      ShowMessage(svText)
    else
      local svON,svOS
      if svRes[1]==nil then
        svON=""
      else
        svON=svRes[1]
      end
      if svRes[2]==nil then
        svON=""
      else
        svOS=svRes[2]
      end
      if svON~="" then
        --Show dialog  for selecting type method
        GetTypeMethod(svON,svOS)
      end
    end
  end
  iup.key_open()
  AddMenu(AutoReplaceSTM,"Lua / Edit / AutoReplace static type method")
end
 
Open AfterConnect File:

Code:
-- Open AfterConnect File
do
	local function OpenAfterConnectFile()
		IDE.OpenFile(plsql.WindowType.SQL, SYS.RootDir() .. "\AfterConnect.sql") 		
	end

	AddMenu(OpenAfterConnectFile, "&Lua / Utilities / Open AfterConnect File")	
end

Open Login File:

Code:
-- Open Login File
do
	local function OpenAfterConnectFile()
		IDE.OpenFile(plsql.WindowType.SQL, SYS.RootDir() .. "\Login.sql") 		
	end

	AddMenu(OpenAfterConnectFile, "&Lua / Utilities / Open Login File")	
end
 
It can be interesting for everybody who loves plsql developer and suffers from its beautifier :) (To say the Truth it's not very good and doesn't support some of the new plsql construction)
There is the way to use quest software formatter plus with plsq develper. You just need luaplsql and autohotkey ( www.autohotkey.com)

Code:
do
  local function Format()
    local svCurFile=IDE.Filename()
    local nvWT=IDE.GetWindowType()
    if nvWT==plsql.WindowType["Test"] then
      local svText=string.gsub(IDE.GetText(),"(\r\n)","\n")
      local fl=io.open("c:\buf\reg\reg8.rg","w")
      fl:write(svText)
      fl:close()
      os.execute("PlugIns\lua\ManUtl\\Format.ahk")
      fl=io.open("c:\buf\reg\reg8.rg","r")
      svText=fl:read("*a")
      fl:close()
      svText=string.gsub(svText,"(\n)","\r\n")
      IDE.SetText(svText)
    else
      syslib.remove("c:\buf\reg\reg8.rg")
      IDE.SetFilename("c:\buf\reg\reg8.rg")
      IDE.SaveFile()
      os.execute("PlugIns\lua\ManUtl\\Format.ahk")
      IDE.ReloadFile()
      IDE.SetFilename(svCurFile)
    end
    plsql.SetForegroundWindow(IDE.GetAppHandle())
  end;
  AddMenu(Format,"Lua / Edit / Format")
end;

Code:
; format.ahk
; AutoHotkey Version: 1.x
; Script Function:
;	Template script (you can customize this template by editing "ShellNew\Template.ahk" in your Windows folder)
;
f_regprefix=c:\buf\reg\reg
#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

run, "C:\Program Files\Quest Software\SQL Navigator 5.5\FmtPlus.exe" %f_regprefix%8.rg
WinWaitActive Formatter Plus
SendPlay {alt}ef^s
UniqueFmtID := WinExist("A")
WinClose, ahk_id %UniqueFmtID%
WinWaitClose, ahk_id %UniqueFmtID%
 
Last edited:
Where does one find a concise guide on LUA commands, etc?

This looks very promising, but I don't have time to figure it out by trial and error.

Thank you
 
I have asked the author about a concise guide and I am not sure It will be done soon.
Today You should look up "Lua library" declaration to find out what Lua commands are included into luaplsql.
For example:
There is the end of plsql_ide.c

Code:
static luaL_reg plsql_idelib[] = {
    {"MenuState",		plsql_ide_MenuState},
    {"Connected",		plsql_ide_Connected},
    {"GetConnectionInfo",	plsql_ide_GetConnectionInfo},
    {"GetBrowserInfo",		plsql_ide_GetBrowserInfo},
    {"GetWindowType",		plsql_ide_GetWindowType},
    {"GetAppHandle",		plsql_ide_GetAppHandle},
    {"GetWindowHandle",		plsql_ide_GetWindowHandle},
    {"GetClientHandle",		plsql_ide_GetClientHandle},
    {"GetChildHandle",		plsql_ide_GetChildHandle},
    {"Refresh",			plsql_ide_Refresh},

//and so on .....

    {NULL, NULL}
};

The description of basic libraries can be found in
http://www.lua.org/manual/5.1/
Of course the standard libraries have good documentation.
- IUP:http://luaforge.net/projects/iup/
- LPeg:http://www.inf.puc-rio.br/~roberto/lpeg.html
 
Last edited:
It can be very comfortable to have the ability to add package\type header tab in body program window editor.

So you can delete header tab to escape package\type revalidation and quick add this tab when you need it.

To do this you need:
1 - Create the new template in program unit for example "Header.tpl"
2 - defint callback in luaplsql

Code:
local function OnTemplate(FileName, Data)
  local ObjectType,ObjectOwner,ObjectName=IDE.GetWindowObject()
  if ObjectType==nil then
    ObjectType=""
  end
  if string.upper(ObjectType)=="TYPE BODY" or
     string.upper(ObjectType)=="PACKAGE BODY"     then
    local Slash = lpeg.P("\")
    local Any = (lpeg.P(1)-Slash)^0
    local Path=(Any*Slash)^0
    local Templ=Path*lpeg.C(Any)
    --Get the name of the template
    local svText=lpeg.match(Templ,FileName)

    if string.upper(svText)==string.upper("header.tpl") and string.upper(ObjectType)=="TYPE BODY"  then
      return IDE.GetObjectSource("TYPE",string.upper(ObjectOwner),string.upper(ObjectName))
    end
    return true
  end
  return true
end;

3- While editing in body program windows click on insert new program before\header in popup menu.
 
Last edited:
Hello everyone,

I am actually working on a new plugin using LuaPLSQL. I created a new dialog with text field and list of value using IUP and everything is working fine. My actual problem is that i am trying to make my dialog modal so that when it open i can not click back on PLSQL Developer until it close.
I am using dlg:showxy(iup.CENTER, iup.CENTER) to open it and it's not working. So I also tried dlg:popup but it don't seem to work too.
I checked the documentation of IUP and it seem to have some attribute named PARENTDIALOG. Anyone used this yet? because i don't seem to be able to get the good syntax to make it work.

Thank for helping.
 
So since IUP does not know about your dialog, you have to disable
it yourself before showing the IUP dialogs. Simply do:

EnableWindow(hWnd,FALSE);

IupPopup(...

EnableWindow(hWnd,TRUE);

Unfortunately "EnableWindow" is winapi function, so you should write your own dll or ask the plugin's author to add this function to basic library.
Can you notify me, if you perform this task?. It will be useful for me too.
 
Last edited:
Cann anone post what Result do you have in plsql.RootPath() function?
I use not devalut Plugins Directory in PLD and lua plugin will "not load".

G.
 
Unfortunately you must use default directory to store luaplugin because the lua loader use standard path to load it.

See luaplug.c
#define PLUGIN_DLL_NAME "luaplsql.dll"
#define PLUGIN_DLL_PATH ".\\PlugIns\\lua\\clibs\\"
 
It's not a commercial project so it would be better to say nothing or may be to ask the author to modify "Lua loader", or just do it yourself :)
 
Reload object source in program windows from data base.
Code:
do
 local function Refresh()
    local function RefreshSrc()
      local ObjectType,ObjectOwner,ObjectName=IDE.GetWindowObject()
      if string.len(ObjectName)>0 then
        IDE.SetText(
          IDE.GetObjectSource(
             string.upper(ObjectType)
            ,string.upper(ObjectOwner),string.upper(ObjectName)
          )
        )
      end
    end;
    local nvWT=IDE.GetWindowType()
    if nvWT==plsql.WindowType["Procedure"] then
      local nvX = IDE.GetCursorX()
      local nvY =IDE.GetCursorY()
      if string.len(IDE.TabInfo(0))>0 then
        local nvTab=0
        local nvCurTab=IDE.TabIndex(-1)
        while string.len(IDE.TabInfo(nvTab))>0 do
          IDE.TabIndex(nvTab)
          RefreshSrc()
          nvTab=nvTab+1
        end
        IDE.TabIndex(nvCurTab)
      else
        RefreshSrc()
      end
      IDE.SetCursor(nvX,nvY)
    end
  end
  AddMenu(Refresh, "Lua / Edit / Refresh procedure")
end;
 
Last edited:
It's very useful when you work with large queries to have the ability to copy sub-query for example:
[#]-Cursor position
select * from [#](
...large text with subquery
),
...large text
To copy sub-query quickly use following script:
Code:
local plsql = plsql
local SYS, IDE, SQL = plsql.sys, plsql.ide, plsql.sql
require'lpeg'

local syslib = require("sys")

local function SetClipBoardText( spText)
  plsql.SetClipboardText(spText)
end

local ShowMessage = plsql.ShowMessage

--Return text between branches
local function GetSubQuery()
    --Get cursor coordinates
    local nvX = IDE.GetCursorX()
    local nvY =IDE.GetCursorY()

    IDE.SetSelection(IDE.GetSelection(), -1)
    --Recieve the text to be parsed
    local svText=IDE.GetSelectedText()
    --Retrun the cursor
    IDE.SetCursor(nvX,nvY)
    --lpeg gramma
    local Space = lpeg.S(" \r\n\t")^0
    local Open = "("
    local Close = ")"
    local NewLine=lpeg.S("\n")
    local EmptyLine=((lpeg.S(" \t\r"))^0*lpeg.P("\n") )^0
    local Com1 = lpeg.P("--")*((lpeg.P(1)-NewLine)^0)*NewLine
    local Com2 = lpeg.P("/*")* ( (lpeg.P(1)-lpeg.P("*/"))^0 ) *lpeg.P("*/")

    local Exp, Term = lpeg.V"Exp", lpeg.V"Term"
    local Any= lpeg.V"Any"
    local AnyR= lpeg.V"AnyR"
    G = lpeg.P{ Exp,
      Exp = Any*Open*EmptyLine*lpeg.C(Any*Term^0*Any)*Close*lpeg.P(1)^0;
      Term = Any*Open*(Any*Term^0*Any)*Close*Any;
      Any =  ( Com1+Com2+( (lpeg.P(1)-Open-Close-lpeg.P("--")-lpeg.P("/*") )^1 ) )^0
    }
    --Get subquery
    svText=lpeg.match(G,svText)
    return svText
end

do
 --Menu item for copy subquery
 local function CopySubQuery()
    local svText=GetSubQuery()
    if svText==nil then
      ShowMessage("Sub query is not found.")
    else
      SetClipBoardText(svText)
    end
  end
  pvCopySubQuery=AddMenu(CopySubQuery, "Lua / Edit / Copy SubQuery")
end;
 
Last edited:
AMatveev,
I had to change

local syslib = require("sys")

to be

local syslib = require("sys")

local AddMenu = ...

to get around errors loading the macro.

also, just so others know, the macro only copies the sub-query when the cursor is on the opening "(", once you execute it the sub-query in in the clipboard.
 
It's not actual in version 8 and above.
Just for example
It's Create a list of static method
ObjectName-Type name
SubObject-Pattern for the quick search
There are key processing
Code:
local syslib = require("sys")
require( "iuplua" )
local AddMenu = ...
local function AutoComplitStaticTypeMethod(ObjectName,SubObject)
    --Better to put in into on Activate event
	iup.key_open()
	
	local OldSubObject=SubObject
    -- Creates a list and sets its items, dropdown and amount of visible items
     local list= iup.list { dropdown="NO", visible_items=10,EDITBOX ="YES",size="300x200y"
                      ,VISIBLELINES = 10, SCROLLBAR = "YES"
                    }

    -- Creates frame with dropdown list and sets its title
     local frm_list = iup.frame {list
                ; title = string.upper(ObjectName) .. "               (Shift+Enter - Refresh)"}

    -- Creates a dialog with the the frames with three lists and sets its title
     local dlg = iup.dialog {iup.hbox {frm_list}
          ; title = "Select method (Enter)"}

     --Populate list by method
     local function RefreshList()
      	local nvI=0;
        local sql = [[
            select  distinct lower(t.method_name)
              from dba_type_methods t
             where t.type_name=upper(:ObjectName)
               and upper(t.method_name) like upper(:SubObject||'%')
             order by lower(t.method_name)
      	]]
        SQL.SetVariable("ObjectName", ObjectName)
        SQL.SetVariable("SubObject", SubObject)
        list[1]=nil
    		if SQL.Execute(sql) ~= 0 then
    			ShowMessage(SQL.ErrorMessage())
    		else

      		while true do
      			local svName = SQL.Field(1)
      			if SQL.Eof() then break end
      			nvI=nvI+1
            list[nvI]=svName
      			SQL.Next()
      		end
    		end
        SQL.ClearVariables()
        --If there is only one item put in into curent value
        if nvI==1 then
           list["VALUE"]=string.lower(list[1])
           list["CARET"]=string.len(list[1])+1
        else
           list["VALUE"]=string.lower(SubObject)
           list["CARET"]=string.len(SubObject)+1
        end
     end
    --Key event
    function list:k_any(k)
      if k==iup.K_CR then
        dlg:hide()
        --Delete sumobject in editor
        if OldSubObject ~= "" then
          IDE.KeyPress(8,4)
          --IDE.Refresh()
        end
        do
          IDE.InsertText(list["VALUE"])
        end
      end
      if k==iup.K_ESC then
        --Exit
        dlg:hide()
      end;
      if k==iup.K_sCR then
         --Refresh
         SubObject=list["VALUE"]
         RefreshList()
      end
      return k
    end
    -- ShowDiolog
    dlg:showxy(iup.CENTER, iup.CENTER)
    --RefreshList
    RefreshList()
	--Message  loop
    iup.MainLoop()
    dlg:destroy()
end

There are good documentation on IUP in the Internet
 
Last edited:
Thank you for the IUP example, but how do I get it to run? I made a folder under PlugIns\lua, put the code in the new folder as main.lua and then started PL/SQL Dev. There were no errors on startup, but I don't see a menu entry, no right click menu item to make the code run.

Which IUP documentation on the Internet do you use? I have found lots of hits searching for IUP, but I haven't been very successful in finding working examples.

Thank you.
 
For example visithttp://www.tecgraf.puc-rio.br/iup/en/basic/index.html
It's seems a good quick guide.
Also, in Lua plsql you should call dlg:destroy() after iup.MainLoop()

I don't think there are many working example for luaPlSql.
If you want to test my lua example or any others you should create your own menu item.
I think it's not difficult and can be pasted from other examples.
So I don't want to spend my time for it.
 
AMatveev,
Thank you for the link, it is better than any I have found so far.

OK, it helps me to know that the code is not runnable "as-is".
 
The URL given by AMatveev above is a great getting started site for IUP. It isn't geared to the LUAplsql plugin, but hey, that is why we get paid the big (substitute the currency of your choice)!

From that site I took this code:

Code:
require 'iuplua'

btn = iup.button{title = "Click me!"}

function btn:action ()
    iup.Message("Note","I have been clicked!")
    return iup.DEFAULT
end

dlg = iup.dialog{btn; title="Simple Dialog",size="QUARTERxQUARTER"}

dlg:show()

iup.MainLoop()

and put some needed LUAplsql code around it and came up with this working example:

Code:
-- Pl/Sql Developer Lua Plug-In Addon: Test Harness

-- Variables

local AddMenu = ...

local plsql = plsql
local SYS, IDE, SQL = plsql.sys, plsql.ide, plsql.sql

-- code to test goes below this

require 'iuplua'

do
	local upMenuItem

  local function btns()
    btn = iup.button{title = "Click me!"}

    function btn:action ()
        iup.Message("Note","I have been clicked!")
        return iup.DEFAULT
    end

    dlg = iup.dialog{btn; title="Simple Dialog",size="QUARTERxQUARTER"}

    dlg:show()

    iup.MainLoop()
  end

	upMenuItem = AddMenu(btns, "&Lua / Tests / Btns")
	AddMenu(btns, "&Lua / Tests / Btns")
end

-- code to test goes above this

Save this code as "main.lua" in a separate folder under "PLSQL Developer\PlugIns\lua" and click "Lua / Reload Plug-in" to reload the LUAplsql plugin and you should see a new menu entry labeled "Tests / Btns".

This could be prettied up a bit and I am sure that my comments in the code that show where the test should go aren't quite right, but it is a start.
 
New luaplsq release has come:
Release Name: 1.6
Notes: New function: plsql.EnableWindow()
Now you can make your dialog modal:

Code:
local function ShowFileCont(spFileName)
    local hwnd=IDE.GetWindowHandle()
    plsql.EnableWindow(hwnd,false);
    ...	
		dlg:show()
		iup.MainLoop()
		dlg:destroy()	
    ...	
    plsql.EnableWindow(hwnd,true)
    plsql.SetForegroundWindow(hwnd)	
end
 
Back
Top