Bienvenido irissqlcli - terminal avanzado para IRIS SQL

Os presento mi nuevo proyecto: irissqlcli, REPL (Read-Eval-Print Loop) para InterSystems IRIS SQL.

• Resaltado de sintaxis
• Sugerencias (tablas, funciones)
• +20 formatos de salida
• Soporte a stdin
• Salida a ficheros 

Instalación con pip

pipinstallirissqlcli

O se puede ejecutar con docker

dockerrun-itcaretdev/irissqlcliirissqlcliiris://_SYSTEM:SYS@host.docker.internal:1972/USER

Conexión a IRIS

$ irissqlcli iris://_SYSTEM@localhost:1972/USER -W
Password for _SYSTEM:
Server:  InterSystems IRIS Version 2022.3.0.606 xDBC Protocol Version 65
Version: 0.1.0
[SQL]_SYSTEM@localhost:USER> select $ZVERSION
+---------------------------------------------------------------------------------------------------------+
| Expression_1                                                                                            |
+---------------------------------------------------------------------------------------------------------+
| IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2022.3 (Build 606U) Mon Jan 30202309:05:12 EST |
+---------------------------------------------------------------------------------------------------------+
1 row in set
Time: 0.063s
[SQL]_SYSTEM@localhost:USER> help
+----------+-------------------+------------------------------------------------------------+
| Command  | Shortcut          | Description                                                |
+----------+-------------------+------------------------------------------------------------+
| .exit    | \q                | Exit.                                                      |
| .mode    | \T                | Change the table format used to output results.            |
| .once    | \o [-o] filename  | Append next result to an output file (overwrite using -o). |
| .schemas | \ds               | List schemas.                                              |
| .tables  | \dt [schema]      | List tables.                                               |
| \e       | \e                | Edit command with editor (uses $EDITOR).                   |
| help     | \?                | Show this help.                                            |
| nopager  | \n                | Disable pager, print to stdout.                            |
| notee    | notee             | Stop writing results to an output file.                    |
| pager    | \P [command]      | Set PAGER. Print the query results via PAGER.              |
| prompt   | \R                | Change prompt format.                                      |
| quit     | \q                | Quit.                                                      |
| tee      | tee [-o] filename | Append all results to an output file (overwrite using -o). |
+----------+-------------------+------------------------------------------------------------+
Time: 0.012s
[SQL]_SYSTEM@localhost:USER>

$ irissqlcli--helpUsage: irissqlcli[OPTIONS][URI][USERNAME]Options:
  -h, --hostTEXTHostaddressoftheIRISinstance.
  -p, --portINTEGERPortnumberatwhichtheIRISinstanceislistening.
  -U, --usernameTEXTUsernametoconnecttotheIRISinstance.
  -u, --userTEXTUsernametoconnecttotheIRISinstance.
  -W, --passwordForcepasswordprompt.
  -v, --versionVersionofirissqlcli.
  -n, --nspaceTEXTnamespacenametoconnectto.
  -q, --quietQuietmode, skipintroonstartupandgoodbyeonexit.
  -l, --logfileFILENAMELogeveryqueryanditsresultstoafile.
  --irissqlclircFILELocationofirissqlclircfile.
  --auto-vertical-outputAutomaticallyswitchtoverticaloutputmodeiftheresultiswiderthantheterminalwidth.
  --row-limitINTEGERSetthresholdforrowlimitprompt. Use 0 todisableprompt.
  -t, --tableDisplaybatchoutputintableformat.
  --csvDisplaybatchoutputinCSVformat.
  --warn / --no-warnWarnbeforerunningadestructivequery.
  -e, --executeTEXTExecutecommandandquit.
  --helpShowthismessageandexit.

o en el modo Python Embebido (requiere que %Service_CallIn esté habilitado)

$ irissqlcliiris+emb:///USERServer:  IRISforUNIX (UbuntuServerLTSforARM64Containers) 2022.2 (Build 368U) FriOct 21 2022 16:39:41EDTVersion: 0.1.0[SQL]irisowner@/usr/irissys/:USER>

Soporta stdin, así que se pueden canalizar algunos ficheros SQL con un grupo de consultas SQL y comandos irissqcli. Por ejemplo, este comando producirá 3 ficheros en diferentes formatos (de entre más de 20 formatos disponibles).

$ cat <<EOF | irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER

.mode csv;
tee -o test.csv;
select top 10 TABLE_SCHEMA,TABLE_NAME 
from information_schema.tables 
orderby TABLE_SCHEMA,TABLE_NAME;
notee;

.mode latex;
tee -o test.tex;
select top 10 TABLE_SCHEMA,TABLE_NAME 
from information_schema.tables 
orderby TABLE_SCHEMA,TABLE_NAME;
notee;

.mode html;
tee -o test.html;
select top 10 TABLE_SCHEMA,TABLE_NAME 
from information_schema.tables 
orderby TABLE_SCHEMA,TABLE_NAME;
notee;

EOF

Además, es posible ejecutar un terminal web con docker

docker run -d --name irissqlcli \
  --restart always \
  -p 7681:7681\
  caretdev/irissqlcli-web irissqlcli iris://_SYSTEM:SYS@host.docker.internal:1972/USER

http://localhost:7681/

Y con docker-compose

version: '3'
services:
  iris:
    image: intersystemsdc/iris-community
    ports:
      - 1972
      - 52773
    command:
      - -a
      - '##class(Security.Users).UnExpireUserPasswords("*")'
  cli:
    image: caretdev/irissqlcli-web
    ports:
      - 7681:7681
    environment:
      - IRIS_HOSTNAME:iris
      - IRIS_PORT=1972
      - IRIS_NAMESPACE=USER
      - IRIS_USERNAME=_SYSTEM
      - IRIS_PASSWORD=SYS