Welcome irissqlcli - advanced terminal for IRIS SQL
Let me introduce my new project, which is irissqlcli, REPL (Read-Eval-Print Loop) for InterSystems IRIS SQL
- Syntax Highlighting
- Suggestions (tables, functions)
- 20+ output formats
- stdin support
- Output to files
Install it with pip
pip install irissqlcli
Or run with docker
docker run -it caretdev/irissqlcli irissqlcli iris://_SYSTEM:SYS@host.docker.internal:1972/USER
Connect to 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 30 2023 09: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 --help
Usage: irissqlcli [OPTIONS] [URI] [USERNAME]
Options:
-h, --host TEXT Host address of the IRIS instance.
-p, --port INTEGER Port number at which the IRIS instance is listening.
-U, --username TEXT Username to connect to the IRIS instance.
-u, --user TEXT Username to connect to the IRIS instance.
-W, --password Force password prompt.
-v, --version Version of irissqlcli.
-n, --nspace TEXT namespace name to connect to.
-q, --quiet Quiet mode, skip intro on startup and goodbye on
exit.
-l, --logfile FILENAME Log every query and its results to a file.
--irissqlclirc FILE Location of irissqlclirc file.
--auto-vertical-output Automatically switch to vertical output mode if the
result is wider than the terminal width.
--row-limit INTEGER Set threshold for row limit prompt. Use 0 to disable
prompt.
-t, --table Display batch output in table format.
--csv Display batch output in CSV format.
--warn / --no-warn Warn before running a destructive query.
-e, --execute TEXT Execute command and quit.
--help Show this message and exit.
or in Python Embedded mode (requires %Service_CallIn enabled)
$ irissqlcli iris+emb:///USER
Server: IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2022.2 (Build 368U) Fri Oct 21 2022 16:39:41 EDT
Version: 0.1.0
[SQL]irisowner@/usr/irissys/:USER>
It supports stdin, so you can pipe some SQL file with a bunch of SQL queries and irissqcli commands. For instance, this command, will produce 3 files in different formats (out of more than 20 formats available)
$ 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
order by TABLE_SCHEMA,TABLE_NAME;
notee;
.mode latex;
tee -o test.tex;
select top 10 TABLE_SCHEMA,TABLE_NAME
from information_schema.tables
order by TABLE_SCHEMA,TABLE_NAME;
notee;
.mode html;
tee -o test.html;
select top 10 TABLE_SCHEMA,TABLE_NAME
from information_schema.tables
order by TABLE_SCHEMA,TABLE_NAME;
notee;
EOF
Additionally it's possible to run a web terminal with docker
docker run -d --name irissqlcli \
--restart always \
-p 7681:7681\
caretdev/irissqlcli-web irissqlcli iris://_SYSTEM:SYS@host.docker.internal:1972/USER
And with 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
Please vote for the project in the contest
In my point of view, this app is a game changer.
Let me explain why.
Imagine you have an DDL statement that you want to execute on a database.
Example:
File: misc/init.sql
You can execute this statement in the irissqlcli app by using the following command:
Before this app, you had to use the terminal to execute this command.
Let's compare the two commands:
irissqlcli iris://_SYSTEM@localhost:51776/USER -W < misc/init.sql
cat <<EOF \| iris session iris <br> do \$SYSTEM.SQL.Schema.ImportDDL("/irisdev/app/misc/init.sql",.log) <br>if log'="" { write "Error: ",log } <br>h <br>EOF
One last thing, if I want to extract the data from a table, I can use the following command:
This command will return the following result:
Try to do the same thing with the terminal ;).
@Guillaume Rongier
your terminal example is just poor , (sorry!) pauvre
I do it his way, easy to read and easy to follow, (your notation)
I'm not tottaly agree with you.
Imagine you are new to iris, you just wish to play with our SQL engine.
With your proposal, you have to know how to connect to a terminal and know the magic command (zzq, BTW i was not aware of this one) to run a query.
Then, you can't do that remotely or with out SSH.
To finish, with your example, you don't show us how to do it from a Shell.
I would have been surprised if you knew ZZQ
It's my personal customized COS command for DO $SYSTEM.SQL.Shell()
But I'm also aware you are selling and pushing PY. OK
And I use happily COS and its predecessor for 45 years which made ISC big
And I dislike black painting its values.
This is just cool!
I tried it with my Stream example using xDBC and no fake mimic.
What Python should I install on my laptop to make irissqlcli work?
I'm on mac.
Supported versions 3.7 -3.10
Tip, you can go to pypi, https://pypi.org/project/irissqlcli/, and there you may find the list of supported versions
And I would recommend using pyenv, which may help to install and use multiple versions of Python, and select needed version
If there is a step-by-step instruction how to setup python environment on Mac I’d appreciate very much
Was able to install with Brew:
Amazing.
Thank you @Dmitry Maslennikov
This is a great tool.
I am wondering if it will work for everyone. In the world of finance, you do not get SSH access to servers.
Most of the times the super-server port is also closed off for everything except the web gateway.
If the web version can be run on it, it is great - but in banking environment, not everyone is on the "containerised" buzz yet, so this will not be allowed.
Sure, I can probably install and configure the package and set up the web application.
Now there are two things left I want to raise:
Stefan, for 1: have you tried DBeaver? It has IRIS driver and looks like the most popular SQL dev tool nowadays.
Also, there is actively supported for IRIS SQL add on to VSCode
I can't recommend IRIS SQLTools for VSCode, as it's too far away from xDBC realizations, and works just over REST, and there is no way for a good improvement. This tool can be quite enough for some simple queries, or as some simple demonstration, with a small amount of data.
If nothing is in the list I encourage to submit an idea on ideas.intersystems.com
Access only by web, looks a bit odd, but possible. In my example of irissqlcli-web, I've just used external tool ttyd, which I'm sure can be easily tied with any webserver in use. ttyd is deamon process, supposed to be launched on the server, it opens some port, which can be used in webserver to as a proxy endpoint.
In the example below, I've just copied and pasted many lines there and executed them. And done, including changing the output format, rollback transaction
As for verifying results before coming, this sounds wonderful. I don't have anything yet, in way of automation. But I don't see that it's not possible to implement. If you have any examples of how it has probably already been implemented somewhere, let me know, so, do not reinvent the wheel.
And all the features requests are welcome in the GitHub repo
And as for deeper integration SQL and IRIS, I'm sure it is worth looking at dbt project. I'm part of the implementation IRIS support, there.
Thank you for the clarification.
If rollback and commit is supported, then verifying the results is just the step of doing a select before committing in order to verify the update/insert was correct and as expected.
Nothing special to it or automated in any way.
This is great. Thank you.
And thanks to open source and @Guillaume Rongier, it's now possible to connect to IRIS in a secure way with SSL
requires testing, but, you can find examples here
https://github.com/caretdev/irissqlcli/pull/4
How to setup a secure connection :
https://docs.intersystems.com/iris20223/csp/docbook/Doc.View.cls?KEY=GTL...
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue