Function List and PL/SQL packages

Claudia Frank · Mar 7, 2017, 9:51 PM

second part

def current_regex():
    return editor2.getCurLine().rstrip()

# -----------------------------------------------------------------------------

def regex_tester_doc_is_current_doc():
    return REGEX_TESTER_INPUT_TAB == notepad.getCurrentBufferID()

# -----------------------------------------------------------------------------

def scroll_to_position(_position):
    if _position is not None:
        start_pos, end_pos = _position
        editor1.setIndicatorCurrent(13)
        editor1.indicatorFillRange(start_pos, end_pos-start_pos)
        current_line = editor1.lineFromPosition(start_pos)
        editor1.ensureVisible(current_line)
        editor1.gotoPos(start_pos)
        editor1.verticalCentreCaret()
        editor1.setXCaretPolicy(CARETPOLICY.EVEN|CARETPOLICY.JUMPS,10)
        editor1.scrollCaret()

# -----------------------------------------------------------------------------

def get_current_regex_position_and_length():
    current_line = editor2.lineFromPosition(editor2.getCurrentPos())
    if current_line != 0 and not editor2.getLine(current_line).startswith(COMMENT_CHAR):
        lenght_line = editor2.lineLength(current_line)
        position = editor2.positionFromLine(current_line)
        return position, lenght_line
    return None, None

# -----------------------------------------------------------------------------

def mark_regex_line(match_indicator):
    position, lenght = get_current_regex_position_and_length()
    if position:
        if match_indicator == REGEX_LINE_NO_MATCH:
                editor2.setIndicatorCurrent(14)
                editor2.indicatorFillRange(position, lenght)
        elif match_indicator == REGEX_LINE_INVALID:
            editor2.setIndicatorCurrent(11)
            editor2.indicatorFillRange(position, lenght)
        else:
            for i in [11,14]:
                editor2.setIndicatorCurrent(i)
                editor2.indicatorClearRange(0, editor2.getTextLength())

# -----------------------------------------------------------------------------

def regex_tester_updateui_callback(args):
    if (regex_tester_doc_is_current_doc() and PREVIOUS_REGEX.get(CURRENT_BUFFER_ID, '') != current_regex()):
        mark_regex_line(REGEX_LINE_CLEAR)
        regex()
        if NO_MATCH_FOUND and INFORM_ABOUT_NO_MATCHES:
            mark_regex_line(REGEX_LINE_NO_MATCH)
        elif NO_MATCH_FOUND is None:
            mark_regex_line(REGEX_LINE_INVALID)
        # else:
            # scroll_to_position(POS_FIRST_OCCURANCE)
# -----------------------------------------------------------------------------

def regex_tester_file_before_close_callback(args):
    if (args['bufferID'] == REGEX_TESTER_INPUT_TAB):
        stop_regex_tester()

# -----------------------------------------------------------------------------

def regex_tester_buffer_activated_callback(args):
    global PREVIOUS_REGEX
    set_current_buffer_id()

    if not PREVIOUS_REGEX.has_key(CURRENT_BUFFER_ID):
        PREVIOUS_REGEX[CURRENT_BUFFER_ID] = ''

    if not REGEX_TESTER_IS_RUNNING:
        global COLORED_DOCS_LIST
        if args['bufferID'] in COLORED_DOCS_LIST:
            clear_indicator()
            COLORED_DOCS_LIST.remove(args['bufferID'])

        if len(COLORED_DOCS_LIST) == 0:
            notepad.clearCallbacks([NOTIFICATION.BUFFERACTIVATED])

# -----------------------------------------------------------------------------

def jump_through_matches(search_direction):
    global MATCH_POSITIONS
    global LAST_POSITION

    if MATCH_POSITIONS.has_key(CURRENT_BUFFER_ID):
        editor1.setIndicatorCurrent(13)
        editor1.indicatorClearRange(0,editor1.getTextLength())

        match_count = len(MATCH_POSITIONS[CURRENT_BUFFER_ID])

        if match_count == 0:
            return

        elif match_count == 1:
            _position = MATCH_POSITIONS[CURRENT_BUFFER_ID][0]

        else:
            current_position = editor1.getCurrentPos()

            if search_direction == 0:
                while True:
                    _position = MATCH_POSITIONS[CURRENT_BUFFER_ID].pop(0)
                    MATCH_POSITIONS[CURRENT_BUFFER_ID].append(_position)

                    if _position[0] != LAST_POSITION:
                        break
            else:
                while True:
                    _position = MATCH_POSITIONS[CURRENT_BUFFER_ID].pop()
                    MATCH_POSITIONS[CURRENT_BUFFER_ID].insert(0, _position)

                    if _position[0] != LAST_POSITION:
                        break

        LAST_POSITION = _position[0]
        scroll_to_position(_position)

# -----------------------------------------------------------------------------

def regex_tester_doc_already_exists():
    files = notepad.getFiles()
    f = [i[1][1] for i in enumerate(files) if i[1][0].upper() == TEMP_FILE_NAME.upper()]
    if f:
        return f[0]
    else:
        return 0

# -----------------------------------------------------------------------------

def color_regex_tester_status():
    status_line = editor2.getLine(0)
    if REGEX_TESTER_IS_RUNNING:
        start = status_line.find('[')
        stop = status_line.find(']')+1
        editor2.setIndicatorCurrent(12)
        editor2.indicatorFillRange(0,20)
        editor2.setIndicatorCurrent(12)
        editor2.indicatorFillRange(start,stop-start)
    else:
        editor2.setIndicatorCurrent(12)
        editor2.indicatorClearRange(0,len(status_line))

# -----------------------------------------------------------------------------

def start_regex_tester():
    if TEMP_FILE_NAME == '':
        notepad.messageBox('You need to indicate, in the TEMP_FILE_NAME variable,\n' +
                           'the fully qualified file name of the TEXT file containing ' +
                           'the REGEX(ES) to test', 'TEMP_FILE_NAME is not set')
        return False

    current_document = 0 if notepad.getCurrentView() == 1 else notepad.getCurrentBufferID()

    global REGEX_TESTER_INPUT_TAB
    REGEX_TESTER_INPUT_TAB = regex_tester_doc_already_exists()
    if REGEX_TESTER_INPUT_TAB == 0 :
        notepad.open(TEMP_FILE_NAME)
        if notepad.getCurrentFilename().upper() == TEMP_FILE_NAME.upper():
            REGEX_TESTER_INPUT_TAB = notepad.getCurrentBufferID()
        else:
            notepad.messageBox('Could not open specified file\n' +
                               '{0}'.format(TEMP_FILE_NAME),
                               'Regex Tester Startup Failed', 0)
            return False
    else:
        notepad.activateBufferID(REGEX_TESTER_INPUT_TAB)

    if notepad.getCurrentView() != 1:
        notepad.menuCommand(MENUCOMMAND.VIEW_GOTO_ANOTHER_VIEW)

    STATUS_LINE = 'RegexTester isActive [] flags:sitp, '

    STATUS_LINE += 'fwd:{}  bwd:{}\r\n'.format(JUMP_FORWARD_SHORTCUT,
                                               JUMP_BACKWARD_SHORTCUT,)

    current_status_line = editor2.getLine(0)
    if 'RegexTester' in current_status_line:
        editor2.replace('RegexTester inActive', 'RegexTester isActive')
    else:
        editor2.insertText(0, STATUS_LINE)


    global REGEX_TESTER_IS_RUNNING
    REGEX_TESTER_IS_RUNNING = True

    color_regex_tester_status()

    set_current_buffer_id()

    global PREVIOUS_REGEX
    PREVIOUS_REGEX[CURRENT_BUFFER_ID] = ''

    editor.callbackSync(regex_tester_updateui_callback, [SCINTILLANOTIFICATION.UPDATEUI])

    if current_document != 0:
        notepad.activateBufferID(current_document)

    editor2.setFocus(True)
    editor2.gotoLine(0)
    notepad.save()

    global REGEX_TESTER_HWND
    REGEX_TESTER_HWND = get_focused_window()

    notepad.callback(regex_tester_file_before_close_callback, [NOTIFICATION.FILEBEFORECLOSE])
    notepad.callback(regex_tester_buffer_activated_callback, [NOTIFICATION.BUFFERACTIVATED])

    return True

# -----------------------------------------------------------------------------

def stop_regex_tester():
    editor.clearCallbacks([SCINTILLANOTIFICATION.UPDATEUI])
    notepad.clearCallbacks([NOTIFICATION.FILEBEFORECLOSE])

    notepad.activateBufferID(REGEX_TESTER_INPUT_TAB)

    if regex_tester_doc_is_current_doc():
        editor2.replace('RegexTester isActive', 'RegexTester inActive')
        notepad.save()

    global REGEX_TESTER_IS_RUNNING
    REGEX_TESTER_IS_RUNNING = False

    clear_indicator()
    color_regex_tester_status()
    mark_regex_line(0)

    global MATCH_POSITIONS
    MATCH_POSITIONS = {}

    _hook.unregister()

    editor1.setFocus(True)

# -----------------------------------------------------------------------------

class Hook():

    def __init__(self):
        self.nppHandle = windll.user32.FindWindowA('Notepad++',None)
        self.oldWndProc = None

        self.SHIFT_PRESSED = False
        self.CTRL_PRESSED = False
        self.ALT_PRESSED = False

    def register(self):
        if REGEX_TESTER_HWND:
            self.new_wnd_proc = WndProcType(self.sciWndProc)
            windll.kernel32.SetLastError(0)
            self.oldWndProc = windll.user32.SetWindowLongA(REGEX_TESTER_HWND, GWL_WNDPROC, self.new_wnd_proc)

            if self.oldWndProc:
                global OLD_WND_PROC
                OLD_WND_PROC = self.oldWndProc
            else:
                _err = 'GetLastError:{}'.format(windll.kernel32.GetLastError())
                notepad.messageBox('Could not register hook:\n{}\n'.format(_err) +
                                    'Shortcuts won\'t work',
                                    'Register Hook Failure', 0)

    def unregister(self):
        if OLD_WND_PROC:
            self.oldWndProc = OLD_WND_PROC
            windll.kernel32.SetLastError(0)
            dummy = windll.user32.SetWindowLongA(REGEX_TESTER_HWND, GWL_WNDPROC, self.oldWndProc)
            if not dummy:
                _err = 'GetLastError:{}'.format(windll.kernel32.GetLastError())
                notepad.messageBox('Could not unregister hook:\n{}\n'.format(_err) +
                                    'It is recommended to save data and restart npp to prevent data loss',
                                    'Unregister Hook Failure', 0)

    def sciWndProc(self, hWnd, msg, wParam, lParam):
        if msg in [WM_KEYDOWN, WM_SYSKEYDOWN]:
            if wParam == VK_SHIFT:
                self.SHIFT_PRESSED = True
            elif wParam == VK_CONTROL:
                self.CTRL_PRESSED = True
            elif wParam == VK_ALT:
                self.ALT_PRESSED = True

        elif msg in [WM_KEYUP,WM_SYSKEYUP]:
            if wParam == VK_SHIFT:
                self.SHIFT_PRESSED = False
            elif wParam == VK_CONTROL:
                self.CTRL_PRESSED = False
            elif wParam == VK_ALT:
                self.ALT_PRESSED = False
            else:
                modifier = 'SHIFT+' if self.SHIFT_PRESSED else ''
                modifier += 'CTRL+' if self.CTRL_PRESSED else ''
                modifier += 'ALT+' if self.ALT_PRESSED else ''
                if wParam in FUNC_KEYS:
                    key_combo = '{}{}'.format(modifier,FUNC_KEYS[wParam])
                else:
                    key_combo = '{}{}'.format(modifier,chr(wParam))

                func = dict_func.get(key_combo, None)
                if func:
                    func()

        elif msg == WM_KILLFOUCS:
            self.SHIFT_PRESSED = False
            self.CTRL_PRESSED = False
            self.ALT_PRESSED = False

        return windll.user32.CallWindowProcA (self.oldWndProc, hWnd, msg, wParam, lParam)

# -----------------------------------------------------------------------------

def main():
    if REGEX_TESTER_IS_RUNNING:
        stop_regex_tester()
    else:
        if start_regex_tester():
            _hook.register()

# -----------------------------------------------------------------------------

dict_func={JUMP_FORWARD_SHORTCUT  : lambda: jump_through_matches(FORWARD_SEARCH),
           JUMP_BACKWARD_SHORTCUT : lambda: jump_through_matches(BACKWARD_SEARCH),}
           # JUMP_TO_FIRST_SHORTCUT : lambda: scroll_to_position(POS_FIRST_OCCURANCE),
           # JUMP_TO_LAST_SHORTCUT  : lambda: scroll_to_position(POS_LAST_OCCURANCE)}


_hook = Hook()
main()

MAPJe71 · Mar 7, 2017, 9:53 PM

Thanx!
Will give it a try.

Claudia Frank · Mar 7, 2017, 9:58 PM

@MAPJe71

one other thing.
The script doesn’t rerun the same regexes. Meaning,
if one line has

^def.*()

and the next line also and you move the cursor from the first to the next line
it doesn’t trigger another research. There must be a difference within the regexes.

Cheers
Claudia

Claudia Frank · Mar 7, 2017, 10:17 PM

One thing I forgot to mention,
special thanks to Scott and Guy which provided
a lot of information and doing beta testing.
Without there patient it wouldn’t be what it is.

THANK YOU!!
Claudia

Jean-Marc Malmedy · Mar 8, 2017, 8:50 AM

@MAPJe71 said:

Although my regular expressions work in RegexBuddy I’m not able to get them to show any packages in Notepad++ Function List.
This needs more investigation, sorry I can’t give you a solution any time soon :(

I’m not surprised since my regular expression worked too outside Function List.
Anyway, thank you very much for trying.

MAPJe71 · Mar 8, 2017, 8:56 AM

@Jean-Marc-Malmedy after using @Claudia-Frank 's (thanks Claudia, awesome script!) script I was able to create a working parser. Needs some fine tuning/cleaning though.

MAPJe71 · Mar 8, 2017, 9:10 AM

@Jean-Marc-Malmedy could you try this one:

			<parser
				displayName="SQL-mehods"
				id         ="sql_syntax"
				commentExpr="(?x)                                               # Utilize inline comments (see `RegEx - Pattern Modifiers`)
								(?s:\x2F\x2A.*?\x2A\x2F)                        # Multi Line Comment
							|	(?m-s:-{2}.*$)                                  # Single Line Comment
							"
			>
				<classRange
					mainExpr    ="(?x)                                          # Utilize inline comments (see `RegEx - Pattern Modifiers`)
							(?mi)                                               # case insensitive
							^\h*                                                # optional leading blanks
							CREATE\s+(?:OR\s+REPLACE\s+)?PACKAGE\s+(?:BODY\s+)? # start-of-package indicator
							(?:\w+\.)?                                          # schema name, optional
							(?'PACKAGE_ID'\w+)                                  # package name
							(?s:.*?)                                            # whatever, until...
							^\h*END(?:\s+\k'PACKAGE_ID')?\s*;                   # ...end-of-package indicator
						"
				>
					<className>
						<nameExpr expr="(?i:PACKAGE\s+(?:BODY\s+)?)\K(?:\w+\.)?\w+" />
					</className>
					<function
						mainExpr="^\h*(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*\([^()]*\)"
					>
						<functionName>
							<funcNameExpr expr="\w+" />
						</functionName>
					</function>
				</classRange>
				<function
					mainExpr="^\h*(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*\([^()]*\)"
				>
					<functionName>
						<nameExpr expr="\w+" />
					</functionName>
				</function>
			</parser>

Jean-Marc Malmedy · Mar 8, 2017, 1:37 PM

Wonderful, it works.

I just had to make to small modifications:

making the parenthesis optional for the declaration of function or procedure
starting the declaration of a function or procedure outside a package with “create or replace”

This is my final version of the parser:

        <parser
            displayName="SQL-mehods"
            id         ="sql_syntax"
            commentExpr="(?x)                                               # Utilize inline comments (see `RegEx - Pattern Modifiers`)
                            (?s:\x2F\x2A.*?\x2A\x2F)                        # Multi Line Comment
                        |   (?m-s:-{2}.*$)                                  # Single Line Comment
                        "
        >
            <classRange
                mainExpr    ="(?x)                                          # Utilize inline comments (see `RegEx - Pattern Modifiers`)
                        (?mi)                                               # case insensitive
                        ^\h*                                                # optional leading blanks
                        CREATE\s+(?:OR\s+REPLACE\s+)?PACKAGE\s+(?:BODY\s+)? # start-of-package indicator
                        (?:\w+\.)?                                          # schema name, optional
                        (?'PACKAGE_ID'\w+)                                  # package name
                        (?s:.*?)                                            # whatever, until...
                        ^\h*END(?:\s+\k'PACKAGE_ID')?\s*;                   # ...end-of-package indicator
                    "
            >
                <className>
                    <nameExpr expr="(?i:PACKAGE\s+(?:BODY\s+)?)\K(?:\w+\.)?\w+" />
                </className>
                <function
                    mainExpr="^\h*(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*(\([^()]*\)){0,1}"
                >
                    <functionName>
                        <funcNameExpr expr="\w+" />
                    </functionName>
                </function>
            </classRange>
            <function
                mainExpr="^\h*CREATE\s+(?:OR\s+REPLACE\s+)?(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*(\([^()]*\)){0,1}"
            >
                <functionName>
                    <nameExpr expr="\w+" />
                </functionName>
            </function>
        </parser>

Many many thanks for your help.

Jean-Marc

MAPJe71 · Mar 8, 2017, 1:41 PM

@Jean-Marc-Malmedy FYI: {0,1} can be replaced with a ?.

Jean-Marc Malmedy · Mar 8, 2017, 2:02 PM

Yes, indeed. Thanks for the suggestion.

MAPJe71 · Mar 8, 2017, 2:02 PM

You’re welcome!

Gábor Madács · Sep 16, 2017, 10:03 PM

HI! Wonderful, I came here for this… :D

I have tried his solution, but immediately bumped into a serious problem:
I can see only the first method in my package. :(

if i understand correctly, the classRange - mainExpr should match for the whole package body.
I think the problem lies here at the last line:

^\h*END(?:\s+\k'PACKAGE_ID')?\s*;

this matches the first “end;” in the code.

In a real-world code there is lots of “end;” statements, as we can close methods with it, and even there is unnamed blocks inside them.

Example:

CREATE OR REPLACE PACKAGE BODY sch_001.pck_001 AS
  PROCEDURE p_proc_001(pn_id PLS_INTEGER) IS
    sd date;
  BEGIN
     -- unnamed block begin: "try-catch" in PL/SQL
     begin
        select sysdate into sd from dual;
     exception when others then
         sd := null;
     end;
     -- do something else
  END;
END pck_001;
/

Please help me on this.
Unfortunately I’m not really familiar with greedy multi-line regexp statements…

For start, it would be OK, if it could end only on the end of file…

It would be a huge help for us! :)

MAPJe71 · Sep 16, 2017, 10:55 PM

@Gábor-Madács
I don’t understand your problem.

Gábor Madács · Sep 17, 2017, 2:38 PM

Thank you for the quick response!

Your results are better than mine… :)

What parser do you use? This is by the one from the last post from Jean-Marc Malmedy.
I have also tried the one before this - posted by you - that is showed the second procedure too, but out of the body.
(That’s because of here no “CREATE” in the procedure name - that is a difference between the two version. “CREATE” is for procedures not in packages.)

Please post your fine parser version! :)

Gabor

MAPJe71 · Sep 17, 2017, 2:38 PM

@Gábor-Madács See my post on GitHub.

Gábor Madács · Sep 17, 2017, 10:05 PM

@MAPJe71 Wow… Thank you very much! :D
(May I note, now the “I don’t understand your problem.” seems somewhat unfounded… ;) )

Work like a charm! (Looks like a charm, indeed… ;) )

Hope, it will be part of the official NPP distribution!

Thank you again!

Gabor

Marco Gonzalez · Dec 3, 2020, 12:14 AM

Beautiful work! But i have a little bit problem.
When the procedure or function name is missing after the “end”, it seems the parser consumes the file to the end skipping other functions!

Example:
function foo is
Begin
–Todo function logic…
null;
End;

function bar is…

In this case function bar (and other functions after it) will be skipped! I know it is not the best practice but it’s pretty common.
although it’s simple to fix that on the source code I will appreciate your help to fix the parser.

Thanks in advance!

Marco Gonzalez · Dec 3, 2020, 11:56 PM

Hi guys, after a few little changes it seems to work now for all my Plsql files…
<?xml version=“1.0” encoding=“UTF-8” ?>
<!-- ==========================================================================\

To learn how to make your own language parser, please check the following
link:
https://npp-user-manual.org/docs/function-list/

|
=========================================================================== -->
<NotepadPlus>
<functionList>

<parser
displayName=“SQL-mehods”
id =“sql_syntax”
commentExpr=“(?x) # Utilize inline comments (see RegEx - Pattern Modifiers)
(?s:\x2F\x2A.?\x2A\x2F) # Multi Line Comment
| (?m-s:-{2}.$) # Single Line Comment
" >
<classRange
mainExpr =”(?x) # Utilize inline comments (see RegEx - Pattern Modifiers)
(?mi) # case insensitive
^\h* # optional leading blanks
(CREATE\s+(?:OR\s+REPLACE\s+)?)?PACKAGE\s+(?:BODY\s+)? # start-of-package indicator
(?:\w+.)? # schema name, optional
(?‘PACKAGE_ID’\w+) # package name
(?s:.?) # whatever, until…
^\hEND(?:\s+\k’PACKAGE_ID’)\s*; # …end-of-package indicator
" >
<className><nameExpr expr=“(?i:PACKAGE\s+(?:BODY\s+)?)\K(?:\w+.)?\w+” /></className>
<function mainExpr=“^\h*(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*(([^()]))?“>
<functionName><funcNameExpr expr=”\w+" /></functionName>
</function>
</classRange>
<function mainExpr="^\h(CREATE\s+(?:OR\s+REPLACE\s+)?)?(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*(([^()]*))?” >
<functionName><nameExpr expr=“\w+” /></functionName>
</function>
</parser>
</functionList>
</NotepadPlus>

I also indented it my own way, sorry for that, enjoy it!