Investigate ssh2 & SQLite as a backend for extension testing

[worksofliam]

Removing the need for an external server would be out of this world. Of course, this would only pertain to working with the database and not uploading/downloading files. Wild idea.

edit: now that i have put together more comments, this seems like a crazy idea, like seriously crazy

[worksofliam]

Let me double up this idea. SSH2 as an SSH server that we could connect to for testing?

https://www.npmjs.com/package/ssh2#password-and-public-key-authentication-and-non-interactive-exec-command-execution

This means perhaps a Node.js env that would replicate some of the pase environment functionality we use. Sounds absolutely mental.

• SSH2 server with exec handler (that's what vscode-ibmi users)
  • custom handler for system commands, since.. we need to execute CL commands
    • we'd need to write a program that handles SQL statements
  • handler for other pase commands (no problem on mac and linux, Windows just hates us)
  • database backend for our fake IBM i server (this is where SQLite comes in!!!)
    • sqlite doesn't support stored procedures, we don't use them a lot anyway
    • sqlite doesn't support table functions.... that one burns
    • we'd have to re-implement some of the QSYS2 schema
    • sqlite does not support aliases and does not support table members, ouch

Next step is to investigate our usage of IBM i in our tests and try to make sure our edge cases are covered

[worksofliam]

CL commands being used:

• CPYTOSTMF FROMMBR('${path}') TOSTMF('${tempRmt}') STMFOPT(*REPLACE) STMFCCSID(1208) DBFCCSID(${this.config.sourceFileCCSID})
• CPYFRMSTMF FROMSTMF('${tempRmt}') TOMBR('${path}') MBROPT(*REPLACE) STMFCCSID(1208) DBFCCSID(${this.config.sourceFileCCSID})
• call QSYS/QZDFMDB2 PARM('-d' '-i' '-t') (with standard input)
• DLTOBJ OBJ(${library}/${file}) OBJTYPE(*FILE)
• DSPOBJD OBJ(QSYS/*ALL) OBJTYPE(*LIB) DETAIL(*TEXTATR) OUTPUT(*OUTFILE) OUTFILE(${tempLib}/${TempName})
• DSPFD FILE(${library}/${object}) TYPE(*ATR) FILEATR(*PF) OUTPUT(*OUTFILE) OUTFILE(${tempLib}/${tempName})
• DSPOBJD OBJ(${library}/${object}) OBJTYPE(${objectTypes}) OUTPUT(*OUTFILE) OUTFILE(${tempLib}/${tempName})
• CHKOBJ OBJ(${object.library.toLocaleUpperCase()}/${object.name.toLocaleUpperCase()}) OBJTYPE(${object.type.toLocaleUpperCase()}) AUT(${authorities.join(" ")})

        `QSYS/CPYTOIMPF FROMFILE(${library}/${file} ${member}) ` +
        `TOSTMF('${tempRmt}') ` +
        `MBROPT(*REPLACE) STMFCCSID(1208) RCDDLM(*CRLF) DTAFMT(*DLM) RMVBLANK(*TRAILING) ADDCOLNAM(*SQL) FLDDLM(',') DECPNT(*PERIOD)`

pase commands being used:

• LC_ALL=EN_US.UTF-8 system "call QSYS/QZDFMDB2 PARM('-d' '-i' '-t')" (with standard input)
• liblist (secretly QSH)
• cd '${remotePath}' && ${STAT} --dereference --printf="%A\t%h\t%U\t%G\t%s\t%Y\t%n\n" * .*
• see memberResolve and objectResolve APIs. Might be veeeery difficult lol

sql statements being used:

• CALL ${tempLib}.GETNEWLIBL('${ileCommand.replace(new RegExp(', 'g'),'')}')
• SELECT * FROM ${library}.${file}
• select CHARACTER_CODE_SET_ID from table( QSYS2.QSYUSRINFO( USERNAME => upper('${this.currentUser}') ) )
• select SYSTEM_VALUE_NAME, CURRENT_NUMERIC_VALUE from QSYS2.SYSTEM_VALUE_INFO where SYSTEM_VALUE_NAME = 'QCCSID'

        select os.OBJNAME as ODOBNM
             , coalesce(os.OBJTEXT, '') as ODOBTX
             , os.OBJATTRIBUTE as ODOBAT
          from table( SYSTOOLS.SPLIT( INPUT_LIST => '${libraries.toString()}', DELIMITER => ',' ) ) libs
             , table( QSYS2.OBJECT_STATISTICS( OBJECT_SCHEMA => 'QSYS', OBJTYPELIST => '*LIB', OBJECT_NAME => libs.ELEMENT ) ) os

        SELECT
          b.avgrowsize 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,
          b.NUMBER_ROWS as MBNRCD,
          extract(epoch from (b.CREATE_TIMESTAMP))*1000 as CREATED,
          extract(epoch from (b.LAST_SOURCE_UPDATE_TIMESTAMP))*1000 as CHANGED
        FROM qsys2.systables AS a
          JOIN qsys2.syspartitionstat AS b
            ON b.table_schema = a.table_schema AND
              b.table_name = a.table_name

              statement = `with VARIANTS ( HASH, AT, DOLLARSIGN ) as (`
                + `  values ( cast( x'7B' as varchar(1) )`
                + `         , cast( x'7C' as varchar(1) )`
                + `         , cast( x'5B' as varchar(1) ) )`
                + `)`
                + `select HASH concat AT concat DOLLARSIGN as LOCAL`
                + `  from VARIANTS; `;

[worksofliam]

• Schemas aren't naturally supported with SQLite, but can be simulated with attach: https://www.sqlitetutorial.net/sqlite-attach-database/
• SQLite does not support aliases, but views will work

[worksofliam]

Looks like the npm package doesn't support creating functions, but dotnet does?

• https://www.sqlite.org/appfunc.html
• https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions