SQL scripts containing DDL (Data Definition Language) and DML (Data Manipulation Language) contain both data structures (relations) and data (records). Simple example:
CREATE TABLE a (
id integer,
message varchar
);
CREATE VIEW b AS SELECT upper(message) AS msg FROM a;
INSERT INTO a VALUES (1, "Hello,");
INSERT INTO a VALUES (2, "world!");
We can read such data using the relpipe-in-sql
command
in a similar way we read CSV, XML or Recfile streams – just pipe the stream into the particular input filter
and let it convert data to the relational format:
cat relpipe-in-sql-1.sql | relpipe-in-sql | relpipe-out-tabular
And in the next step we use an output filter and covert relational data to some other format e.g. the tabular output displayed in our terminal:
a:
╭─────────────┬──────────────────╮
│ id (string) │ message (string) │
├─────────────┼──────────────────┤
│ 1 │ Hello, │
│ 2 │ world! │
╰─────────────┴──────────────────╯
Record count: 2
b:
╭──────────────╮
│ msg (string) │
├──────────────┤
│ HELLO, │
│ WORLD! │
╰──────────────╯
Record count: 2
Of course, we can add further steps in our pipeline and use any transformation tool for filtering or modifying data:
# AWK transformation:
cat relpipe-in-sql-1.sql \
| relpipe-in-sql \
| relpipe-tr-awk \
--relation 'a' --where 'message == "Hello,"' \
--relation '.*' --drop \
| relpipe-out-tabular
# Scheme transformation:
cat relpipe-in-sql-1.sql \
| relpipe-in-sql \
| relpipe-tr-scheme \
--relation 'a' --where '(string= $message "Hello,")' \
--relation '.*' --drop \
| relpipe-out-tabular
and get filtered output:
a:
╭─────────────┬──────────────────╮
│ id (string) │ message (string) │
├─────────────┼──────────────────┤
│ 1 │ Hello, │
╰─────────────┴──────────────────╯
Record count: 1
However, it is usually not necessary, because once we have data in an in-memory database (which happens on-the-fly in the relpipe-in-sql
step),
we can use the SQL language for filtering and transformations and get the same output as above:
cat relpipe-in-sql-1.sql \
| relpipe-in-sql \
--relation 'a' "SELECT * FROM a WHERE message = 'Hello,'" \
| relpipe-out-tabular
Actually, the relpipe-in-sql
is just a symbolic link to the relpipe-tr-sql
and have the same capabilities (with just bit different default behavior to match general logic of the input filters).
So if we do not need special feautres of Scheme, AWK or other transformation tool, it is better to stay with SQL (when we already use the SQL module).
The difference between relpipe-tr-sql
and relpipe-in-sql
is that
the SQL input filter reads SQL scripts from the STDIN while the SQL transformation reads relational data from the STDIN.
And the input filter has implicit --copy '.*'
option, if executed without any arguments (so it passes through all relations from the input without need of writing any SELECTs).
We can override this default behavior by using any argument – we can e.g. copy only certain relations using --copy 'a|c'
(it is a regular expression for matching relation names)
or rename them: --copy-renamed 'a|c' 'copy_of_$0'
or run arbitrary SELECT: --relation 'a' "SELECT * FROM …"
as we have seen above.
SQL scripts can be used for storing relational data as an alternative to other human-readable and human-editable text formats like XML, CSV or Recfiles. And compared to the other formats, SQL scripts may contain even some logic (e.g. call SQL functions) or views.
n.b. The SQL script does not contain only data – it is an executable script and running it might be dangerous.
Depending on SQL engine used (the default one is SQLite, but others like PostgreSQL or MySQL/MariaDB can be used),
such script may call various functions and some of them might read or write local files or do some other unsafe operations.
Thus the SQL scripts comming from untrusted sources must be carefully reviewed or executed in an isolated environment (sandbox).
We can run relpipe-in-sql
using sudo
under an unprivileged account or using ssh
doing the same even on a remote machine (virtual od physical one or a container dedicated for such dirty work).
Or we can use the --data-source-name
or --data-source-string
options and run such script on a remote DBMS under an unprivileged database account or on a sandbox database that will be destroyed or refreshed after use.
Relational pipes, open standard and free software © 2018-2022 GlobalCode