Provide a customer contribution point to supply Object Browser view information.

[m-tyler]

Provide a customer contribution point to supply Object Browser view information.

The default source file and member list selections do not benefit me as much as I need. My company uses a source control product, Rocket Aldon, which allows me to create a small or large project and assign source to that project from the overall source set, which contains many thousands of source members. I don't like needing to constantly update the extension to allow me to use the details from out CMS product in the Object Browser for source files and members. Source files and member vary based on the CMS project I am current working on. I can have several CMS projects active and in various states, at one time, so I don't want to always see all members checked out to me, in different CMS projects. I would like to focus on a single CMS project member list when expanding the source and member lists.

My ask is to...

• 1. Change the following functions to allow for user exits to populate the list information
  • a. getObjectList()
  • b. getMemberList()
• 2. There should be two user exit point calls. One for user specific versions of the exit point function and one for the company level use.
  • a. This will facilitate admin types to develop code to then pass out to all others without breaking their work during development.
  • b. The user name used for current library or whatever value is used by developer for the server library needs to be exposed to these functions.
• 3. The user exit point should be a SQL table function named ILEDITOR.VCS_xxx, where xxx is the name of the extension function mentioned above.
  • a. A table function works best for getMemberList() but for getObjectList() a table function is not required but I feel its best to keep both the same.
• 4. These user exit points do not need to exist at product installation time. Users can create them but they would need template with the minimum interface, so it might be best to create a dummy set up functions that don't return anything.
• 5. The user exit function interfaces should be...
  • a. LIBRARY
  • b. OBJECT
  • c. OBJECT TYPE
  • d. MEMBER
  • e. MEMBER TYPE
• 6. To facilitate this approach for my needs, I need MEMBER and MEMBER TYPE exposed in getObjetList() in order to supply the table functions information with values I define in the filter.
  • a. This means adding in the additional items needed to getObjectList() to fill out the member details.
  • b. If this cannot or won't be done then can item Log spool files into output channel #8 be done?
• 7. The return list should be a minimum of
  • a. getObjectList
    • i. PHLIB varchar(10)
    • ii. PHFILE varchar(10)
    • iii. PHFILA varchar(10)
    • iv. PHDTAT char(1)
    • v. PHTXT varchar(128)
  • b. getMemberList
    • i. MBMXRL bigint -- Max Rec Len
    • ii. MBASP smallint -- File iASP
    • iii. MBFILE varchar(10)
    • iv. MBNAME varchar(10)
    • v. MBSEU2 varchar(10) -- Member type longer version
    • vi. MBMTXT varchar(180)
  • c. There can be additional customer return values in case customer wants to use the table function else where but these are the minimum this extension is looking for.
• 8. EXTRA! Expose custom variables into getObjectList and getMemberList
  • a. Custom variables allow for expanded parameter data that the base filter cannot provide but alas, they are not a typical part of these functions.
    • i. This means that the customer exit point command would need to be exposed like a user action
    • ii. Apply custom variables first if the ${} matches
    • iii. Apply standard filter form variables next
  • b. This could prove to be a security risk so I am not hopeful something like this would ever get passed unless someone branches this project.

Examples of what I think the functions could be changed to handle this ask.

// Need to have additional filter values in my SQL 
// using create table as it made the insert of my code simpler to have code just run getTable() function.
...
async getObjectList(filters, sortOrder = `name`) {
    const library = filters.library.toUpperCase();
    const object = (filters.object && filters.object !== `*` ? filters.object.toUpperCase() : `*ALL`);
    const sourceFilesOnly = (filters.types && filters.types.includes(`*SRCPF`));

    const tempLib = this.ibmi.config.tempLibrary;
    const TempName = Tools.makeid();
    const member=filters.member.toUpperCase();
    const memberType=filters.memberType;
    let queryStatement ='';

    if (sourceFilesOnly) {
      if(this.ibmi.config.enableSQL){
        try {
          queryStatement =`create or replace table ${o}.${s} 
          (PHLIB,PHFILE,PHFILA,PHDTAT,PHTXT) as 
          (select PHLIB,PHFILE,PHFILA,PHDTAT,PHTXT from table ( 
            '${user}'.VSC_getSourceFileListCustom
            (IN_LIB => '${library}' ,IN_SRCF => '${object}' ,IN_MBR => '${member}', IN_MBR_TYPE => '${memberType}' ) )) with data
          on replace delete rows`;
          const a= await this.runSQL(qaq)
          } catch (e1) {
          try {
            queryStatement =`create or replace table ${o}.${s} 
            (PHLIB,PHFILE,PHFILA,PHDTAT,PHTXT) as 
            (select PHLIB,PHFILE,PHFILA,PHDTAT,PHTXT from table ( 
              ILEDITOR.VSC_getSourceFileListCustom
              (IN_LIB => '${library}' ,IN_SRCF => '${object}' ,IN_MBR => '${member}', IN_MBR_TYPE => '${memberType}' ) )) with data
            on replace delete rows`;
            const a= await this.runSQL(qaq)
            } catch (e1) {
              await this.ibmi.remoteCommand(`DSPFD FILE(${r}/${i}) TYPE(*ATR) FILEATR(*PF) OUTPUT(*OUTFILE) OUTFILE(${o}/${s}) /*2*/`);
            }
        }
      }

      const results = await this.getTable(tempLib, TempName, TempName, true);
      if (results.length === 1) {
        if (results[0].PHFILE.trim() === ``) {
          return []
        }
      }
      ...
    }
}
getMemberList
...
 async getMemberList(lib, spf, mbr = `*`, ext = `*`) {
    const config = this.ibmi.config;
    const library = lib.toUpperCase();
    const sourceFile = spf.toUpperCase();
    let member = (mbr !== `*` ? mbr : null);
    let memberExt = (ext !== `*` ? ext : null);

    let results;

    if (config.enableSQL) {
      if (member) member = member.replace(/[*]/g, `%`);
      if (memberExt) memberExt = memberExt.replace(/[*]/g, `%`);
      try {
        const customStatement = `\nselect MBMXRL,MBASP,MBFILE,MBNAME,MBSEU2,MBMTXT from table (
            '${user}'.VSC_getMemberListCustom
            (IN_LIB => '${library}' ,IN_SRCF => '${sourceFile}' ${member?`,IN_MBR => '${member}'`:""} ${memberExt?`,IN_MBR_TYPE => '${memberExt}'`:""} ))`;
        results= await this.runSQL(customStatement);
        if (0 === results.length ) {
          throw '';
        }
      } catch(er) {
        try {
          const customStatement =`\nselect MBMXRL,MBASP,MBFILE,MBNAME,MBSEU2,MBMTXT from table (
            ILEDITOR.VSC_getMemberListCustom
            (IN_LIB => '${library}' ,IN_SRCF => '${sourceFile}' ${member?`,IN_MBR => '${member}'`:""} ${memberExt?`,IN_MBR_TYPE => '${memberExt}'`:""} ))`;
          results= await this.runSQL(customStatement);
          if (0 === results.length ) {
            throw '';
          }
        } catch(er) {
          const statement = `
            SELECT
              (b.avgrowsize - 12) as MBMXRL,
              a.iasp_number as MBASP,
              cast(a.system_table_name as char(10) for bit data) AS MBFILE,
              cast(b.system_table_member as char(10) for bit data) as MBNAME,
              coalesce(cast(b.source_type as varchar(10) for bit data), '') as MBSEU2,
              coalesce(b.partition_text, '') as MBMTXT
            FROM qsys2.systables AS a
              JOIN qsys2.syspartitionstat AS b
                ON b.table_schema = a.table_schema AND
                  b.table_name = a.table_name
            WHERE
              cast(a.system_table_schema as char(10) for bit data) = '${library}' 
              ${sourceFile !== `*ALL` ? `AND cast(a.system_table_name as char(10) for bit data) = '${sourceFile}'` : ``}
              ${member ? `AND rtrim(cast(b.system_table_member as char(10) for bit data)) like '${member}'` : ``}
              ${memberExt ? `AND rtrim(coalesce(cast(b.source_type as varchar(10) for bit data), '')) like '${memberExt}'` : ``}
          `;
          results = await this.runSQL(statement);
        }
      }
    }
 }

Initial table function templates for customers to modify on their own.

Create or replace function VSC_getSourceFileListCustom
( 
  IN_LIB  char(10)  
 ,IN_SRCF char(10) default null
 ,IN_MBR varchar(64) default null
 ,IN_MBR_TYPE varchar(64) default null
) 
returns table ( 
 PHLIB varchar(10)
,PHFILE varchar(10)
,PHFILA varchar(10)
,PHDTAT char(1)
,PHTXT varchar(128)
,PHNOMB int
) 
 language sql 
 specific VSC00AFN92 --<<-- customer can change this
 deterministic 
 called on null input 
 no external action 
 modifies sql data --<<-- Needed if customer calls a feature that is defined as `modifies sql data`
 not fenced --<<-- Needed if customer calls a function that is not thread safe 
set option  alwblk = *ALLREAD , 
            alwcpydta = *OPTIMIZE , 
            datfmt = *ISO, 
            commit = *NONE , 
            dbgview = *SOURCE , 
            decresult = (31, 31, 00) , 
            dftrdbcol = *NONE , 
            dyndftcol = *NO , 
            dynusrprf = *USER , 
            srtseq = *HEX 
begin 
    return with 
    NO_RESULTS (PHLIB ,PHFILE ,PHFILA ,PHDTAT ,PHNOMB ,PHNOMB_T ,PHNOMB_T_LEN) as (
         values (nullif(' ',' '),x'A1','*PHY ','S',0 ,' ',0)
     )
    select PHLIB ,PHFILE ,PHFILA ,PHDTAT ,char(' ',50) PHTXT,PHNOMB from NO_RESULTS where 1=2
    
;
end; 
comment on specific function VSC00AFN92 is 'Return list of source files for VS Code-Custom'; 
  label on specific function VSC00AFN92 is 'Return list of source files for VS Code-Custom'; 
/* Testing query 
;select * from table ( VSC_GETSOURCEFILELISTCustom(IN_MBR => '*'   ,IN_LIB => 'ILEDITOR' ,IN_SRCF=>'QTOOLS') )
 */

 create or replace function VSC_getMemberListCustom
( 
  IN_LIB  char(10)  
 ,IN_SRCF char(10) default null
 ,IN_MBR  varchar(12) default null 
 ,IN_MBR_TYPE char(10) default null
) 
returns table ( 
 MBMXRL bigint  -- Max Rec Len
,MBASP  smallint -- File iASP
,MBFILE varchar(10) 
,MBNAME varchar(12)
,MBSEU2 varchar(10) -- Member type longer version
,MBMTXT varchar(180) 
) 
 language sql 
 specific VSC00AFN91 --<<-- customer changes this
 deterministic 
 called on null input 
 no external action 
 modifies sql data -- <<-- Needed if customer calls a feature that is defined as `modifies sql data`
 not fenced --<<-- Needed if customer calls a function that is not thread safe
set option  alwblk = *ALLREAD , 
            alwcpydta = *OPTIMIZE , 
            datfmt = *ISO, 
            commit = *NONE , 
            dbgview = *SOURCE , 
            decresult = (31, 31, 00) , 
            dftrdbcol = *NONE , 
            dyndftcol = *NO , 
            dynusrprf = *USER , 
            srtseq = *HEX 
begin 
    return with 
    NO_RESULTS (MBMXRL   ,MBASP      ,MBFILE  ,MBNAME ,MBSEU2,MBMTXT) as (
        values (bigint(0),smallint(0),IN_SRCF ,IN_MBR ,'  '  ,char('*** Empty list ***',50))
         )        
        select * from NO_RESULTS where 1=2

    order by MBMXRL,MBASP,MBFILE,MBNAME,MBSEU2,MBMTXT
          
;
end; 
comment on specific function VSC00AFN91 is 'Return list of source members for VS Code - Custom'; 
  label on specific function VSC00AFN91 is 'Return list of source members for VS Code-Custom'; 
/* Testing query
;select * from table (VSC_getMemberListCustom(IN_LIB => 'ILEDITOR' ,IN_SRCF => 'QTOOLS' ,IN_MBR => '*'  ))
*/

P.S., I would have modified this extension myself and submitted a PR but I have been unable to get my company laptop to build a development environment for this extension. So I am left with asking you to make the changes for this ask.

[worksofliam]

Thanks very much for your SQL code there. That really helped put some context on this for me.

For the record, it's a very good idea, but also is a lot to maintain and handle errors for. This isn't me saying no, but I need some time to think about how viable this actually is.

Usually, I would recommend you get Rocket to be building their own extension to supply the views. We provide an API to extension authors to access the IBM i through their own extension. There are two other vendors working on their own extension for their change management. Rocket could do the same.

I am happy to advocate for this with you, also.

[worksofliam]

I have emailed someone at Rocket regarding this issue. Let's see if that goes somewhere!

[m-tyler]

Thanks for at least considering this. I have contacted Rocket about this but its unknown to me if they will or are working to provide an extension.

[m-tyler]

Unfortunately, I don't have an environment that allows me to develop this even as a separate extension, so I am at your mercy or Rockets.

[worksofliam]

@m-tyler Are you familiar with this?

https://community.rocketsoftware.com/forums/forum-home/digestviewer/viewthread?MessageKey=fa049413-3b40-4476-83b4-0185cc5a9b4f&CommunityKey=bcb311ce-06aa-4216-9f6e-9b26270b8253#bmfa049413-3b40-4476-83b4-0185cc5a9b4f

[m-tyler]

No. I have not looked at the community forum for a while. I suppose I should have. But we have not been up to date with the Rocket products, I suspect it requires the new DevOps products in the IBM server and we are sadly too far behind at the moment.

[worksofliam]

@m-tyler I am happy to say that they did reply to us. Their extension is actually a fork of vscode-ibmi, instead of building their own from scratch and using our API. There are some downsides to this like other extensions (Db2 for i ext. and RPGLE language tools) not working correctly with their fork.

Hopefully we can all agree on the standard and continue to improve the base!

[m-tyler]

I appreciate the work you have done researching this. I wasn't certain my request would get past the first step.

That's great that Rocket used the base extension as a foundation for their work.