mysql stored function with recursive query

I recently had a requirement for an advanced search/filter mechanism for an entity in a JSF application outside the scope of the relevant database entity's regular attributes.  It required a recursive query, as the database table represents a hierarchy of the items that it contains.  Our database backend is mysql/mariadb 8.

I decided to use a mysql stored function for the recursive query because I wanted to use the query from arbitrary SQL in contexts than the original requirement.  I have used recursive queries or stored functions in mysql before, so I just wanted to document my approach here for future reference.

I found the following links helpful:

• https://mariadb.com/kb/en/recursive-common-table-expressions-overview/
• https://www.geeksforgeeks.org/mysql-creating-stored-function/

The recursive part of the query looks like this:

WITH RECURSIVE ancestors as ( 

     SELECT parent_item_id FROM item_element WHERE parent_item_id in ( 

          SELECT parent_item_id FROM item_element WHERE id in ( 

                 SELECT first_item_element_id  

           FROM item_element_relationship ier  

           WHERE ier.relationship_type_id = 4 AND  

        second_item_element_id = item_self_element_id)) 

     UNION  

           SELECT ie.parent_item_id  

           FROM item_element ie, ancestors AS a  

           WHERE ie.contained_item_id1 = a.parent_item_id 

) SELECT count(i.name)  

INTO row_count  

FROM item i, ancestors  

WHERE i.id = ancestors.parent_item_id and i.name like name_filter_value;

The first part of the query (before the union) is the "anchor".  It is executed first and selects the elements that for the cable's direct endpoint machine design items.  The "recursive part" (after the union) is executed repeatedly until it returns no new data.

The function accepts parameters for the name filter value, and an optional specification of cable end for constraining the result to one end of the cable or the other.  Example queries look like this:

-- don't constrain cable end, matches any name against any device ancestor

select * from item where domain_id = 9 and cable_design_ancestor_filter(id, 'DLMA', '');

-- constrain matches by cable end (1 or 2)

select * 

from item 

where domain_id = 9 

and cable_design_ancestor_filter(id, 'Rack 01-01', '1') 

and cable_design_ancestor_filter(id, 'DLMA', '2');

Also worth documenting here is the mechanism for calling a stored function from JPA.  This is done by using "FUNCTION()" in the SQL statement, as is shown in these two named query examples:

@NamedQueries({

    @NamedQuery(name = "ItemDomainCableDesign.filterAncestorAny",

            query = "SELECT i FROM Item i WHERE i.domain.name = :domainName AND FUNCTION('cable_design_ancestor_filter', i.id, :nameFilterValue, '')"),

    @NamedQuery(name = "ItemDomainCableDesign.filterAncestorByEnd",

            query = "SELECT i FROM Item i WHERE i.domain.name = :domainName AND FUNCTION('cable_design_ancestor_filter', i.id, :end1Value, '1') AND FUNCTION('cable_design_ancestor_filter', i.id, :end2Value, '2')")

})

Here is the full code of the stored function. One thing I tried to avoid was that there are two separate recursive queries for the conditional cases where cable end either is or is not specified.  I was unable to find a solution using a stored procedure or by concatenating strings to form the query, but I'm guessing there must be a way...

DROP FUNCTION IF EXISTS cable_design_ancestor_filter// 

CREATE FUNCTION cable_design_ancestor_filter 

(cable_item_id INT, 

name_filter_value VARCHAR(64), 

cable_end VARCHAR(64)) 

RETURNS BOOLEAN 

BEGIN  

DECLARE row_count INT; 

DECLARE item_self_element_id INT; 

-- check that cable_item_id is specified

IF ISNULL(cable_item_id)

THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'cable item id must be specified';

END IF;

-- add % to both ends of filter value 

IF ISNULL(name_filter_value) OR name_filter_value = ''

THEN

RETURN true;

END IF;

SET name_filter_value = CONCAT('%', name_filter_value); 

SET name_filter_value = CONCAT(name_filter_value, '%');

-- get self element id for cable design item 

SELECT self_element_id INTO item_self_element_id FROM v_item_self_element  WHERE item_id = cable_item_id; 

-- check if any endpoint or its ancestors match the filter using recursive query 

-- limit relationship elements to cable relationship type and specified cable end 

IF ISNULL(cable_end) OR cable_end = ''

THEN

-- cable end not specified

WITH RECURSIVE ancestors as ( 

     SELECT parent_item_id FROM item_element WHERE parent_item_id in ( 

          SELECT parent_item_id FROM item_element WHERE id in ( 

                 SELECT first_item_element_id  

           FROM item_element_relationship ier  

           WHERE ier.relationship_type_id = 4 AND  

        second_item_element_id = item_self_element_id)) 

     UNION  

           SELECT ie.parent_item_id  

           FROM item_element ie, ancestors AS a  

           WHERE ie.contained_item_id1 = a.parent_item_id 

) SELECT count(i.name)  

INTO row_count  

FROM item i, ancestors  

WHERE i.id = ancestors.parent_item_id and i.name like name_filter_value;

ELSE

-- cable end specified

WITH RECURSIVE ancestors as ( 

     SELECT parent_item_id FROM item_element WHERE parent_item_id in ( 

          SELECT parent_item_id FROM item_element WHERE id in ( 

                 SELECT first_item_element_id  

           FROM item_element_relationship ier,  

        item_element_relationship_property ierp,  

property_value pv  

           WHERE ier.relationship_type_id = 4 AND  

        second_item_element_id = item_self_element_id AND 

  ierp.item_element_relationship_id = ier.id AND 

  pv.id = ierp.property_value_id AND 

          pv.value = cable_end)) 

     UNION  

           SELECT ie.parent_item_id  

           FROM item_element ie, ancestors AS a  

           WHERE ie.contained_item_id1 = a.parent_item_id 

) SELECT count(i.name)  

INTO row_count  

FROM item i, ancestors  

WHERE i.id = ancestors.parent_item_id and i.name like name_filter_value;

END IF;

RETURN row_count > 0; 

END//

displaying busy indicator on primefaces wizard component

 Using primefaces 8 to build a java web application, I had trouble figuring out how to display a busy indicator from a primefaces wizard component.  Other views use onstart/oncomplete attributes for button actions to display a "loading dialog" that indicates the application is busy while the action completes.  That approach doesn't work for wizard navigation, because the action is performed asynchronously from the button click.  I found a stack overflow thread with a description of the solution.

In a nutshell, I used the "onnext" attribute of the p:wizard tag to open the busy dialog, and the flowListener method (onFlowProcess()) to close it.  Here are a couple of code snippets.

First the xhtml code for the wizard:

        <p:wizard id="importWizard"
                  flowListener="#{wizardController.onFlowProcess}" 
                  widgetVar="#{rootViewId}"
                  showStepStatus="false" 
                  showNavBar="false"
                  onnext="PF('loadingDialog').show();">

The code for the modal loading dialog:

    <p:dialog modal="true" 
              id="loadingDialog"
              widgetVar="loadingDialog" 
              showHeader="false" 
              styleClass="viewTransparentBackgroundDialog"
              resizable="false">
        <h:outputText value="Loading Results... Please Wait..." />
        <p/>
        <p:graphicImage library="images" name="ajax-loader.gif" />
    </p:dialog>

The java server code:

     public String onFlowProcess(FlowEvent event) {
        String result = onFlowProcessHandler(event);
        SessionUtility.executeRemoteCommand("PF('loadingDialog').hide();");
        return result;
    }

 

sql logging for jsf application in netbeans

To enable SQL logging to the netbeans console for a JSF application running in Payara with EclipseLink, add the following properties to the persistence.xml file: 

<property name="eclipselink.logging.level" value="FINE"/>
<property name="eclipselink.logging.logger" value="DefaultLogger"/>

More information here and here.

On a related note, logging can also be enabled directly in mysql, if that's the underlying database.  Here are some notes about doing that:

log in to mysql as root:

-> use mysql; 

-> SET global log_output = 'table'; 

-> SET GLOBAL general_log = 1;

​

then you can see the logged statements using:

select * from general_log;

or count the number of sql statements:

select count(*) from general_log; 

​

and clean it up with:

truncate general_log; 

​

update tag behavior for components nested in Primefaces dataTable

I discovered the hard way that update="someComponentId" does not work for a commandLink nested inside a datatable. After some searching I found that you must use the syntax 'update=":formId:componentId" to make the update work. E.g., the example below works, but if I change the commandLink update to update="#{viewName}MembersPanel", an error results:

                <p:dataTable id="#{viewName}MemberDataTable"
                             var="member"
                             value="#{wizardController.members}"
                             emptyMessage="No members added.">

                    <p:column headerText="Cable name">
                        <h:outputText value="#{member.name}" />
                    </p:column>

                    <p:column headerText="Action">
                        <p:commandLink  id="#{viewName}RemoveCommandLink"
                                        value="Remove"
                                        action="#{wizardController.removeMember(member)}"
                                        onstart="PF('loadingDialog').show()"
                                        oncomplete="PF('loadingDialog').hide();update#{rootViewId}WizardButtons();"
                                        update="@form:#{viewName}MembersPanel"
                                        process="@form:#{viewName}MembersPanel">
                        </p:commandLink>
                    </p:column>

                </p:dataTable>

determining the events supported by Primefaces components

Apparently like others have experienced, I've found it a bit of a challenge to determine exactly what ajax events are supported by Primefaces components.  There is a helpful stackoverflow thread that I've encountered more than once that gives some useful suggestions, and I wanted to list some of them here in case the thread disappears.

The first place to look, of course, is the Primefaces documentation for your version.  The problem is that not all supported events are listed for all components.  There is some helpful general information about DOM events on w3schools, and jQuery events.

One post suggests looking in the primefaces javascript code:
If you want to find out which events are supported:

If you want to find out which events are supported:

1. Download and unpack primefaces source jar

1. Find the JavaScript file, where your component is defined (for example, most form components such as SelectOneMenu are defined in forms.js)

1. Search for this.cfg.behaviors references

For example, this section is responsible for launching toggleSelect event in SelectCheckboxMenu component:
fireToggleSelectEvent: function(checked) { if(this.cfg.behaviors) { var toggleSelectBehavior = this.cfg.behaviors['toggleSelect']; if(toggleSelectBehavior) { var ext = { params: [{name: this.id + '_checked', value: checked}] } } toggleSelectBehavior.call(this, null, ext); }},

But the most interesting post gives mechanisms for listing the events in xhtml code and in java code.

The xhtml approach:

You can output the list directly in xhtml by binding that component to a request scoped variable and printing the eventNames property:
<p:autoComplete binding="#{ac}"></p:autoComplete><h:outputText value="#{ac.eventNames}" />This outputs
[blur, change, valueChange, click, dblclick, focus, keydown, keypress, keyup, mousedown, mousemove, mouseout, mouseover, mouseup, select, itemSelect, itemUnselect, query, moreText, clear]

The java approach:

Figure out the component implementation class and invoke its' implementation of javax.faces.component.UIComponentBase.getEventNames() method:import javax.faces.component.UIComponentBase; public class SomeTest { public static void main(String[] args) { dumpEvents(new org.primefaces.component.inputtext.InputText()); dumpEvents(new org.primefaces.component.autocomplete.AutoComplete()); dumpEvents(new org.primefaces.component.datatable.DataTable()); } private static void dumpEvents(UIComponentBase comp) { System.out.println( comp + ":\n\tdefaultEvent: " + comp.getDefaultEventName() + ";\n\tEvents: " + comp.getEventNames()); } }This outputs:
org.primefaces.component.inputtext.InputText@239963d8: defaultEvent: valueChange; Events: [blur, change, valueChange, click, dblclick, focus, keydown, keypress, keyup, mousedown, mousemove, mouseout, mouseover, mouseup, select]org.primefaces.component.autocomplete.AutoComplete@72d818d1: defaultEvent: valueChange; Events: [blur, change, valueChange, click, dblclick, focus, keydown, keypress, keyup, mousedown, mousemove, mouseout, mouseover, mouseup, select, itemSelect, itemUnselect, query, moreText, clear]org.primefaces.component.datatable.DataTable@614ddd49: defaultEvent: null; Events: [rowUnselect, colReorder, tap, rowEditInit, toggleSelect, cellEditInit, sort, rowToggle, cellEdit, rowSelectRadio, filter, cellEditCancel, rowSelect, contextMenu, taphold, rowReorder, colResize, rowUnselectCheckbox, rowDblselect, rowEdit, page, rowEditCancel, virtualScroll, rowSelectCheckbox]