--- a/relpipe-data/download.xml Thu May 21 01:23:23 2020 +0200
+++ b/relpipe-data/download.xml Sat Jun 06 01:57:24 2020 +0200
@@ -17,7 +17,9 @@
<pre><![CDATA[hg clone https://hg.globalcode.info/relpipe/relpipe-in-cli.cpp;
hg clone https://hg.globalcode.info/relpipe/relpipe-in-csv.cpp;
hg clone https://hg.globalcode.info/relpipe/relpipe-in-filesystem.cpp;
+hg clone https://hg.globalcode.info/relpipe/relpipe-in-filesystem.doc;
hg clone https://hg.globalcode.info/relpipe/relpipe-in-fstab.cpp;
+hg clone https://hg.globalcode.info/relpipe/relpipe-in-jack.cpp;
hg clone https://hg.globalcode.info/relpipe/relpipe-in-recfile.cpp;
hg clone https://hg.globalcode.info/relpipe/relpipe-in-xml.cpp;
hg clone https://hg.globalcode.info/relpipe/relpipe-in-xmltable.cpp;
@@ -57,6 +59,7 @@
<h2>Released versions</h2>
<ul>
+ <li>2020-06-06: <m:a href="release-v0.16">v0.16</m:a></li>
<li>2020-01-31: <m:a href="release-v0.15">v0.15</m:a></li>
<li>2019-10-30: <m:a href="release-v0.14">v0.14</m:a></li>
<li>2019-07-30: <m:a href="release-v0.13">v0.13</m:a></li>
--- a/relpipe-data/examples-in-filesystem-tr-sql-indexing.xml Thu May 21 01:23:23 2020 +0200
+++ b/relpipe-data/examples-in-filesystem-tr-sql-indexing.xml Sat Jun 06 01:57:24 2020 +0200
@@ -26,7 +26,7 @@
<m:pre jazyk="bash"><![CDATA[find /bin/ /usr/bin/ -print0 \
| relpipe-in-filesystem --relation "program" \
- | relpipe-tr-sql --file bin.sqlite --file-keep true]]></m:pre>
+ | relpipe-tr-sql --data-source-string 'Driver=SQLite3;Database=file:bin.sqlite']]></m:pre>
<p>
This index allows us to do fast searches and various analysis.
@@ -34,7 +34,7 @@
</p>
<m:pre jazyk="bash"><![CDATA[relpipe-in-sql \
- --file bin.sqlite \
+ --data-source-string 'Driver=SQLite3;Database=file:bin.sqlite' \
--relation "largest" \
"SELECT path, size FROM program WHERE type = 'f' ORDER BY size DESC LIMIT 20" \
| relpipe-out-tabular]]></m:pre>
@@ -75,7 +75,7 @@
</p>
<m:pre jazyk="bash"><![CDATA[relpipe-in-sql \
- --file bin.sqlite \
+ --data-source-string 'Driver=SQLite3;Database=file:bin.sqlite' \
--relation bin "SELECT path FROM program WHERE type = 'f'" \
| relpipe-out-nullbyte \
| while read_nullbyte f; do
@@ -85,12 +85,12 @@
"dependency" \
"program" string \
"library" string \
- | relpipe-tr-sql --file bin.sqlite]]></m:pre>
+ | relpipe-tr-sql --data-source-string 'Driver=SQLite3;Database=file:bin.sqlite']]></m:pre>
<p>And then we can make a „popularity contest“ and find 20 most often used libraries:</p>
<m:pre jazyk="bash"><![CDATA[relpipe-in-sql \
- --file bin.sqlite \
+ --data-source-string 'Driver=SQLite3;Database=file:bin.sqlite' \
--relation "popular_libraries" "
SELECT
d.library,
@@ -138,6 +138,11 @@
Extended attributes are already supported (the <code>--xattr</code> option).
</p>
+ <p>
+ n.b. if we use a database frequently it is convenient to configure it as a data source in the <code>~/.odbc.ini</code> file
+ – and then connect to it using the <code>--data-source-name</code> option and its name.
+ </p>
+
</text>
</stránka>
--- a/relpipe-data/examples-in-sql-selecting-existing-database.xml Thu May 21 01:23:23 2020 +0200
+++ b/relpipe-data/examples-in-sql-selecting-existing-database.xml Sat Jun 06 01:57:24 2020 +0200
@@ -10,16 +10,16 @@
<p>
Both the <code>relpipe-tr-sql</code> and <code>relpipe-in-sql</code>
- support the option <code>--file</code> and can store data in a file instead of just in-memory.
- Primary purpose of this option is to offload to HDD and be able to process more data whithout consuming too much RAM.
- So such file is usually a temporary one and is deleted immediately after the transformation.
- But we can also direct it to an existing file and append new relations to it.
- Or we can create a new file and do not delete it – using the <code>--file-keep true</code> option.
+ support options <code>--data-source-name</code> and <code>--data-source-string</code>
+ that can be used for connecting to a different database than the in-memory SQLite one.
+ This is useful if we want to offload to HDD and be able to process more data whithout consuming too much RAM.
+ Or if we want to access an existing database instead of just transforming records from the STDIN to STDOUT.
+ These options allow connecting to any DBMS.
</p>
<p>
Thus we can use the <code>relpipe-in-sql</code> as a database client to access existing SQLite files and SELECT from them (and then convert the results to any supported format).
- An we can use the <code>relpipe-tr-sql</code> as an output filter which converts relational data to a SQLite file that can be queried later.
+ And we can use the <code>relpipe-tr-sql</code> as an output filter which converts relational data to a SQLite file that can be queried later.
</p>
<p>
@@ -35,11 +35,11 @@
<m:pre jazyk="bash"><![CDATA[fossil clone http://www.sqlite.org/cgi/src sqlite.fossil]]></m:pre>
<p>
- We can then query it using <m:name/> tools:
+ We can then query the database file <code>sqlite.fossil</code> using <m:name/> tools:
</p>
<m:pre jazyk="bash"><![CDATA[relpipe-in-sql \
- --file sqlite.fossil \
+ --data-source-string 'Driver=SQLite3;Database=file:sqlite.fossil' \
--relation tickets "
SELECT
type,
--- a/relpipe-data/examples-jack-midi-monitoring.xml Thu May 21 01:23:23 2020 +0200
+++ b/relpipe-data/examples-jack-midi-monitoring.xml Sat Jun 06 01:57:24 2020 +0200
@@ -23,7 +23,7 @@
It does not consume STDIN, it gets events from JACK instead, so no other input data are needed.
</p>
- <m:pre jazyk="bash">relpipe-in-jack | relpipe-out-gui</m:pre>
+ <m:pre jazyk="bash">relpipe-in-jack | relpipe-out-csv</m:pre>
<p>
We are connected to the JACK daemon now, but no data are routed to us.
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/relpipe-data/examples-tr-sql-odbc.xml Sat Jun 06 01:57:24 2020 +0200
@@ -0,0 +1,286 @@
+<stránka
+ xmlns="https://trac.frantovo.cz/xml-web-generator/wiki/xmlns/strana"
+ xmlns:m="https://trac.frantovo.cz/xml-web-generator/wiki/xmlns/makro">
+
+ <nadpis>Accessing SQLite, PostgreSQL and MySQL through ODBC</nadpis>
+ <perex>use various DBMS for SQL transformations or data access</perex>
+ <m:pořadí-příkladu>04200</m:pořadí-příkladu>
+
+ <text xmlns="http://www.w3.org/1999/xhtml">
+
+ <p>
+ Since <m:a href="release-v0.16">v0.16</m:a> the <code>relpipe-tr-sql</code> module
+ uses the ODBC abstraction layer and thus we can access data in any DBMS (database management system).
+ Our program depends only on the generic API and the driver for particular DBMS is loaded dynamically depending on the configuration.
+ </p>
+
+ <blockquote>
+ <p>
+ ODBC (Open Database Connectivity) is an industry standard and provides API for accessing a DBMS.
+ In late 80s several vendors (mostly from the Unix and database communities) established the SQL Access Group (SAG)
+ and then specified the Call Level Interface (CLI). ODBC, which is based on CLI, was published in early 90s.
+ ODBC is available on many operating systems and there are at least two free software implementations:
+ <a href="http://www.unixodbc.org/">unixODBC</a> and <a href="http://www.iodbc.org/">iODBC</a>.
+ </p>
+ </blockquote>
+
+ <p>For more information see the <m:a href="release-v0.16">v0.16 release notes</m:a>.</p>
+
+ <h2>General concepts and configuration</h2>
+
+ <p>
+ <strong>ODBC</strong>:
+ the API consisting of C functions; see the files <code>sql.h</code> and <code>sqlext.h</code> e.g. in unixODBC.
+ </p>
+ <p>
+ <strong>Database driver</strong>:
+ a shared library (an <code>.so</code> file)
+ that implements the API and connects to particular DBMS (SQLite, PostgreSQL, MySQL, MariaDB, Firebird etc.);
+ is usually provided by the authors of given DBMS, sometimes writen by a third-party
+ </p>
+ <p>
+ <strong>Client</strong>:
+ a program that calls the API in order to access a database; our <code>relpipe-tr-sql</code> is a client
+ </p>
+ <p>
+ <strong>Data Source Name (DSN)</strong>:
+ the name of a preconfigured data source – when connecting, we need to know only the DSN – all parameters
+ (like server name, user name, password etc.) can be then looked-up in the configuration
+ </p>
+ <p>
+ <strong>Connection string</strong>:
+ a text string consisting of serialized parameters needed for connecting
+ – we can specify all parameters ad-hoc in the connection string without creating any permanent configuration;
+ a connection string can also refer to a DSN and add or override some parameters
+ </p>
+
+ <p>
+ There is some global configuration in the <code>/etc</code> directory.
+ In <code>/etc/odbcinst.ini</code> we can a find list of ODBC drivers.
+ Thanks to it, we can refer to a driver by its name (e.g. <code>SQLite3</code>)
+ instead of the path to the shared library (e.g. <code>/usr/lib/x86_64-linux-gnu/odbc/libsqlite3odbc.so</code>).
+ In <code>/etc/odbc.ini</code> we can find a list of global (for given computer) data sources.
+ It is uncommon to put complete configurations in this file, because anyone would be able to read the passwords,
+ but we can provide here just a <i>template</i> with public parameters like server name, port etc.
+ and user will supply his own user name and password in the connection string or in his personal configuration file.
+ </p>
+
+ <p>
+ The <code>~/.odbc.ini</code> contains personal configuration of given user.
+ There are usually data sources including the passwords.
+ Thus this file must be readable only by given user (<code>chmod 600 ~/.odbc.ini</code>).
+ Providing passwords in connection strings passed as CLI arguments is not a good practice due to security reasons:
+ by default it is stored in the shell history and it is also visible to other users of the same machine in the list of running processes.
+ </p>
+
+ <p>
+ The section name – in the <code>[]</code> brackets – is the DSN.
+ Then there are parameters in form of <code>key=value</code> on each line.
+ </p>
+
+
+ <h2>CLI options</h2>
+
+ <p>
+ The <code>relpipe-tr-sql</code> and <code>relpipe-in-sql</code> support these relevant CLI options:
+ </p>
+
+ <ul>
+ <li>
+ <code>--list-data-sources</code>:
+ lists available (configured) data sources in relational format (so we pipe the output to some output filter e.g. to <code>relpipe-out-tabular</code>)
+ </li>
+ <li>
+ <code>--data-source-name</code>:
+ specifies the DSN of a configured data source
+ </li>
+ <li>
+ <code>--data-source-string</code>:
+ specifies the connections string for ad-hoc connection without need of any configuration
+ </li>
+ </ul>
+
+ <pre><![CDATA[$ relpipe-tr-sql --list-data-sources | relpipe-out-tabular
+data_source:
+ ╭───────────────┬──────────────────────╮
+ │ name (string) │ description (string) │
+ ├───────────────┼──────────────────────┤
+ │ sqlite-memory │ SQLite3 │
+ │ relpipe │ PostgreSQL Unicode │
+ ╰───────────────┴──────────────────────╯
+Record count: 2]]></pre>
+
+ <p>
+ Because output of this command is relational, we can further process it in our relational pipelines.
+ This output is also used for the Bash-completion for suggesting the DSN.
+ </p>
+
+ <p>
+ If neither <code>--data-source-name</code> nor <code>--data-source-string</code> option is provided,
+ a temporary in-memory SQLite database is used as default.
+ </p>
+
+ <h2>SQLite</h2>
+
+ <p>In Debian GNU/Linux and similar distributions we can install <a href="https://sqlite.org/">SQLite</a> ODBC driver by this command:</p>
+
+ <pre>apt install libsqliteodbc</pre>
+
+ <p>Which also installs the SQLite library that is all we need (because SQLite is a <i>serverless and self-contained</i> database).</p>
+
+ <p>
+ Then we can use the default in-memory temporary database or specify the connection string ad-hoc,
+ <m:a href="examples-in-sql-selecting-existing-database">access existing SQLite databases</m:a>
+ or <m:a href="examples-in-filesystem-tr-sql-indexing">create new ones</m:a> – e.g. this command:
+ </p>
+
+ <pre>… | relpipe-tr-sql --data-source-string 'Driver=SQLite3;Database=file:MyDatabase.sqlite'</pre>
+
+ <p>will create the <code>MyDatabase.sqlite</code> file and fill it with relations that came from STDIN.</p>
+
+ <p>For frequently used databases it is convenient to configure a data source in <code>~/.odbc.ini</code>:</p>
+
+ <m:pre jazyk="ini"><![CDATA[[MyDatabase]
+Driver=SQLite3
+Database=file:/home/hacker/MyDatabase.sqlite]]></m:pre>
+
+ <p>
+ and then connect to it simply using <code>--data-source-name MyDatabase</code>
+ (both the option and the name will be suggested by Bash-completion).
+ </p>
+
+ <p>
+ The <a href="http://www.ch-werner.de/sqliteodbc/html/index.html">SQLite ODBC driver</a> supports several parameters that are described in its documentation.
+ One of them is <code>LoadExt</code> that loads SQLite extensions:
+ </p>
+
+ <m:pre jazyk="ini"><![CDATA[LoadExt=/home/hacker/libdemo.so]]></m:pre>
+
+ <p>
+ So we can write our own SQLite extension with custom functions or other features
+ (<a href="https://blog.frantovo.cz/c/383/Komplexita%20softwaru%3A%20%C5%98e%C5%A1en%C3%AD%20a%C2%A0prevence#toc_sqlite">example</a>)
+ or chose some existing one and load it into the SQLite connected through ODBC.
+ </p>
+
+
+ <h2>PostgreSQL</h2>
+
+ <p>In Debian GNU/Linux and similar distributions we can install <a href="https://www.postgresql.org/">PostgreSQL</a> ODBC driver by this command:</p>
+
+ <pre>apt install odbc-postgresql</pre>
+
+ <p>
+ PostgreSQL is very powerful DBMS (probably most advanced free software relational database system)
+ and utilizes the client-server architecture.
+ This means that we also needs a server (can be also installed through <code>apt</code> like the driver).
+ </p>
+
+ <p>
+ Once we have a server – remote or local – we need to create a user (role).
+ For SQL transformations we configure a dedicated role that has no persistent schema and uses the temporary one as default,
+ which means that all relations we create are lost at the end of the session (when the <code>relpipe-tr-sql</code> command finishes),
+ thus it behaves very similar to the SQLite in-memory database.
+ </p>
+
+ <m:pre jazyk="sql"><![CDATA[CREATE USER relpipe WITH PASSWORD 'someSecretPassword';
+ALTER ROLE relpipe SET search_path TO 'pg_temp';]]></m:pre>
+
+ <p>
+ And then we <a href="https://odbc.postgresql.org/docs/config.html">configure</a> the ODBC data source:
+ </p>
+
+ <m:pre jazyk="ini"><![CDATA[[postgresql-temp]
+Driver=PostgreSQL Unicode
+Database=postgres
+Servername=localhost
+Port=5432
+Username=relpipe
+Password=someSecretPassword]]></m:pre>
+
+ <p>
+ Now we can use advanced PostgreSQL features for transforming data in our pipelines.
+ We can also configure a DSN for another database that contains some useful data and other database objects,
+ call existing business functions installed in such database, load data to or from this DB etc.
+ </p>
+
+
+ <h2>MySQL</h2>
+
+ <p>
+ If the <code>libmyodbc</code> package is missing in our distribution,
+ the ODBC driver for <a href="https://dev.mysql.com/downloads/connector/odbc/">MySQL</a> can be downloaded from their website.
+ We can get a binary package (<code>.deb</code>, <code>.rpm</code> etc.) or source code.
+ If we are compiling from sources, we do something like this:
+ </p>
+
+ <m:pre jazyk="bash"><![CDATA[cd mysql-connector-odbc-*-src/
+mkdir build
+cd build
+cmake ../ -DWITH_UNIXODBC=1
+make]]></m:pre>
+
+ <p>
+ We should use the driver in the same or similar version as the MySQL client library installed on our system.
+ For example 8.x driver will not work with 5.x library.
+ Successful compilation results in <code>libmyodbc*.so</code> files.
+ </p>
+
+ <p>
+ Like PostgreSQL, also MySQL is a client-server,
+ so we need a server where we create a database and some user account.
+ As root through the <code>mysql mysql</code> command we execute:
+ </p>
+
+ <m:pre jazyk="sql"><![CDATA[CREATE DATABASE relpipe CHARACTER SET = utf8;
+CREATE USER 'relpipe'@'localhost' IDENTIFIED BY 'someSecretPassword';
+GRANT ALL PRIVILEGES ON relpipe.* TO 'relpipe'@'localhost';
+FLUSH PRIVILEGES;]]></m:pre>
+
+ <p>As a normal user we add new data source to our <code>~/.odbc.ini</code> file:</p>
+
+ <m:pre jazyk="ini"><![CDATA[[mysql-relpipe-localhost]
+Driver=/home/hacker/src/mysql/build/lib/libmyodbc5w.so
+Server=localhost
+Port=3306
+Socket=/var/run/mysqld/mysqld.sock
+User=relpipe
+Password=someSecretPassword
+Database=relpipe
+InitStmt=SET SQL_MODE=ANSI_QUOTES;
+Charset=utf8]]></m:pre>
+
+ <p>
+ See that we have compiled the ODBC driver in our home directory
+ and even without installing it anywhere and registering it in the <code>/etc/odbcinst.ini</code> file,
+ we can simply refer to the <code>.so</code> file from our <code>~/.odbc.ini</code>.
+ </p>
+
+ <p>
+ If we set <code>Server=localhost</code>, the client-server communication does not go through TCP/IP
+ but rather through the unix domain socket specified in the <code>Socket</code> field.
+ If we set <code>Server=127.0.0.1</code> or some remote IP address or domain name, the communication goes through TCP/IP on given port.
+ </p>
+
+ <p>
+ The <code>SET SQL_MODE=ANSI_QUOTES;</code> init statement is important,
+ because it tells MySQL server that it should support standard SQL "quoted" identifiers
+ instead of that `weird` MySQL style.
+ We use the standard SQL while creating the tables.
+ </p>
+
+ <p>
+ There are many other parameters, quite well
+ <a href="https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html">documented</a>.
+ </p>
+
+ <p>
+ Now we can use MySQL as the <i>SQL engine</i> for transformations in our pipelines
+ and we can also access existing MySQL databases,
+ load data to and from them
+ or call functions and procedures installed on the server.
+ </p>
+
+
+ </text>
+
+</stránka>
--- a/relpipe-data/examples-tr-sqlite-custom-version.xml Thu May 21 01:23:23 2020 +0200
+++ b/relpipe-data/examples-tr-sqlite-custom-version.xml Sat Jun 06 01:57:24 2020 +0200
@@ -14,6 +14,17 @@
</p>
<p>
+ <strong>
+ n.b. This method is obsolete since <m:a href="release-v0.16">v0.16</m:a> that does not use SQLite library directly
+ and uses arbitrary database driver (including SQLite one) through an abstraction layer (ODBC).
+ This article is still valid as an example of the LD_PRELOAD hack and can be used with older versions of <m:name/>.
+ Since v0.16 we can easily replace whole ODBC driver (and thus use also different version of the SQLite),
+ there is no need for LD_PRELOAD hacking
+ – we can just configure desired driver (the <code>.so</code> file) in the INI file or ad-hoc in the connection string.
+ </strong>
+ </p>
+
+ <p>
By default, <code>relpipe-tr-sql</code> links to the SQLite library available in our distribution (e.g. 3.22).
As we can check:
</p>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/relpipe-data/examples/release-v0.16.sh Sat Jun 06 01:57:24 2020 +0200
@@ -0,0 +1,53 @@
+# Install dependencies as root:
+apt install g++ make cmake mercurial pkg-config
+apt install libxerces-c-dev # needed only for relpipe-in-xml module
+apt install guile-2.2-dev # needed only for relpipe-tr-guile module
+apt install gawk # needed only for relpipe-tr-awk module
+apt install libxml++2.6-dev # needed only for relpipe-in-xmltable module
+apt install unixodbc-dev # needed only for relpipe-tr-sql module
+apt install libsqliteodbc # needed only for relpipe-tr-sql module if used with SQLite
+apt install odbc-postgresql # needed only for relpipe-tr-sql module if used with PostgreSQL
+apt install libjack-jackd2-dev # needed only for relpipe-in-jack module
+
+# Run rest of installation as a non-root user:
+export RELPIPE_VERSION="v0.16"
+export RELPIPE_SRC=~/src
+export RELPIPE_BUILD=~/build
+export RELPIPE_INSTALL=~/install
+export PKG_CONFIG_PATH="$RELPIPE_INSTALL/lib/pkgconfig/:$PKG_CONFIG_PATH"
+export PATH="$RELPIPE_INSTALL/bin:$PATH"
+
+rm -rf "$RELPIPE_BUILD"/relpipe-*
+mkdir -p "$RELPIPE_SRC" "$RELPIPE_BUILD" "$RELPIPE_INSTALL"
+
+# Helper functions:
+relpipe_download() { for m in "$@"; do cd "$RELPIPE_SRC" && ([[ -d "relpipe-$m.cpp" ]] && hg pull -R "relpipe-$m.cpp" && hg update -R "relpipe-$m.cpp" "$RELPIPE_VERSION" || hg clone -u "$RELPIPE_VERSION" https://hg.globalcode.info/relpipe/relpipe-$m.cpp) || break; done; }
+relpipe_install() { for m in "$@"; do cd "$RELPIPE_BUILD" && mkdir -p relpipe-$m.cpp && cd relpipe-$m.cpp && cmake -DCMAKE_INSTALL_PREFIX:PATH="$RELPIPE_INSTALL" "$RELPIPE_SRC/relpipe-$m.cpp" && make && make install || break; done; }
+
+# Download all sources:
+relpipe_download lib-common lib-reader lib-writer lib-cli lib-xmlwriter in-cli in-fstab in-xml in-xmltable in-csv in-filesystem in-recfile out-gui.qt out-nullbyte out-ods out-tabular out-xml out-csv out-asn1 out-recfile tr-cut tr-grep tr-python tr-sed tr-validator tr-guile tr-awk tr-sql in-jack
+
+# Optional: At this point, we have all dependencies and sources downloaded, so we can disconnect this computer from the internet in order to verify that our build process is sane, deterministic and does not depend on any external resources.
+
+# Build and install libraries:
+relpipe_install lib-common lib-reader lib-writer lib-cli lib-xmlwriter
+
+# Build and install tools:
+relpipe_install in-fstab in-cli in-fstab in-xml in-xmltable in-csv in-recfile tr-cut tr-grep tr-sed tr-guile tr-awk tr-sql out-nullbyte out-ods out-tabular out-xml out-csv out-asn1 out-recfile in-filesystem in-jack
+
+# Load Bash completion scripts:
+for c in "$RELPIPE_SRC"/relpipe-*/bash-completion.sh ; do . "$c"; done
+
+# Enable streamlet examples:
+export RELPIPE_IN_FILESYSTEM_STREAMLET_PATH="$RELPIPE_SRC"/relpipe-in-filesystem.cpp/streamlet-examples/
+
+# Clean-up:
+unset -f relpipe_install
+unset -f relpipe_download
+unset -v RELPIPE_VERSION
+unset -v RELPIPE_SRC
+unset -v RELPIPE_BUILD
+unset -v RELPIPE_INSTALL
+
+# List configured ODBC data sources:
+relpipe-in-sql --list-data-sources | relpipe-out-tabular
--- a/relpipe-data/implementation.xml Thu May 21 01:23:23 2020 +0200
+++ b/relpipe-data/implementation.xml Sat Jun 06 01:57:24 2020 +0200
@@ -18,6 +18,7 @@
relpipe-in-csv.cpp executable input c++ GNU GPLv3
relpipe-in-filesystem.cpp executable input c++ GNU GPLv3
relpipe-in-fstab.cpp executable input c++ GNU GPLv3
+ relpipe-in-jack.cpp executable input c++ GNU GPLv3
relpipe-in-recfile.cpp executable input c++ GNU GPLv3
relpipe-in-xml.cpp executable input c++ GNU GPLv3
relpipe-in-xmltable.cpp executable input c++ GNU GPLv3
--- a/relpipe-data/release-v0.13.xml Thu May 21 01:23:23 2020 +0200
+++ b/relpipe-data/release-v0.13.xml Sat Jun 06 01:57:24 2020 +0200
@@ -43,6 +43,11 @@
</ul>
<p>
+ n.b. the <code>--file</code> and <code>--file-keep</code> have been dropped in <m:a href="release-v0.16">v0.16</m:a>
+ (replacement is described in the v0.16 release notes).
+ </p>
+
+ <p>
See the <m:a href="examples">examples</m:a> and <m:a href="screenshots">screenshots</m:a> pages for details.
</p>
--- a/relpipe-data/release-v0.14.xml Thu May 21 01:23:23 2020 +0200
+++ b/relpipe-data/release-v0.14.xml Sat Jun 06 01:57:24 2020 +0200
@@ -45,6 +45,11 @@
</ul>
<p>
+ n.b. the <code>--file</code> and <code>--file-keep</code> have been dropped in <m:a href="release-v0.16">v0.16</m:a>
+ (replacement is described in the v0.16 release notes).
+ </p>
+
+ <p>
See the <m:a href="examples">examples</m:a> and <m:a href="screenshots">screenshots</m:a> pages for details.
</p>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/relpipe-data/release-v0.16.xml Sat Jun 06 01:57:24 2020 +0200
@@ -0,0 +1,264 @@
+<stránka
+ xmlns="https://trac.frantovo.cz/xml-web-generator/wiki/xmlns/strana"
+ xmlns:m="https://trac.frantovo.cz/xml-web-generator/wiki/xmlns/makro">
+
+ <nadpis>Release v0.16</nadpis>
+ <perex>new public release of Relational pipes</perex>
+ <m:release>v0.16</m:release>
+
+ <text xmlns="http://www.w3.org/1999/xhtml">
+ <p>
+ We are pleased to introduce you the new development version of <m:name/>.
+ This release brings an abstraction layer (ODBC) in the SQL transformation + several smaller improvements.
+ </p>
+
+ <ul>
+ <li>
+ <strong>ODBC in the <code>relpipe-tr-sql</code> module</strong>: see details below</li>
+ <li>
+ <strong>new input <code>relpipe-in-jack</code> module</strong>: see details below</li>
+ <li>
+ <strong>keyboard shortcuts in the <code>relpipe-out-gui</code> module</strong>: use Ctrl+PgUp and Ctrl+PgDown to switch panels (relations) and Ctrl+Q to quit</li>
+ <li>
+ <strong>record count in the <code>relpipe-out-xhtml</code> command</strong>: number of records is printed under the table (this command part of <code>relpipe-out-xml</code>, not a standalone module)</li>
+ </ul>
+
+ <p>
+ See the <m:a href="examples">examples</m:a> and <m:a href="screenshots">screenshots</m:a> pages for details.
+ </p>
+
+ <p>
+ Please note that this is still a development relasease and thus the API (libraries, CLI arguments, formats) might and will change.
+ Any suggestions, ideas and bug reports are welcome in our <m:a href="contact">mailing list</m:a>.
+ </p>
+
+ <h2>ODBC in the SQL transformation module</h2>
+
+ <p>
+ Former versions of <code>relpipe-tr-sql</code> were tied to <a href="https://sqlite.org/">SQLite</a>
+ and user had no option to change the <i>SQL engine</i>.
+ However great SQLite is (and we are very thankful for it), having some particular DBMS (database management system) hard-coded in our program is too constraining.
+ So we added an abstraction layer (ODBC) and get rid of the direct dependency on SQLite.
+ Now any DBMS can be used with <m:name/>.
+ </p>
+
+ <p>
+ ODBC (Open Database Connectivity) is an industry standard and provides API for accessing a DBMS.
+ In late 80s several vendors (mostly from the Unix and database communities) established the SQL Access Group (SAG)
+ and then specified the Call Level Interface (CLI). ODBC, which is based on CLI, was published in early 90s.
+ ODBC is available on many operating systems and there are at least two free software implementations:
+ <a href="http://www.unixodbc.org/">unixODBC</a> and <a href="http://www.iodbc.org/">iODBC</a>.
+ We use unixODBC for development and testing.
+ Future releases of <m:name/> should be tested also with other implementations and various database drivers.
+ </p>
+
+ <p>
+ SQLite remains the default option
+ (in the C++ implementation, while Java or other implementations may have different default and may use different abstraction layer like JDBC).
+ We count on SQLite for future releases. It is the simplest way to get full SQL power in your relational pipeline.
+ However, <code>relpipe-tr-sql</code> do not depend on SQLite and can be installed without it (and then used e.g. with PostgreSQL driver).
+ Using different DBMS makes sense for two main reasons:
+ </p>
+
+ <ul>
+ <li>
+ We need specific features provided by the DBMS.
+ It might be e.g. some functions for XML processing or some advanced SQL language constructs.
+ Or maybe we have some business logic already implemented as SQL functions in e.g. PostgreSQL
+ – now we can access this logic from our pipelines or seamlessly integrate it in our shell:
+ <m:pre jazyk="bash"><![CDATA[cat source-data.csv | relpipe-in-csv \
+ | relpipe-tr-sql \
+ --data-source-name "MyDatabaseServer" \
+ --relation "transformed_data" "
+ SELECT
+ some_csv_field AS id,
+ our_special_function(some_other, third_one) AS result
+ FROM csv" \
+ | relpipe-out-xml \
+ | xsltproc template.xsl - > some-fancy-report.xhtml
+ # or just: | relpipe-out-xhtml > some-generic-report.xhtml]]></m:pre>
+ </li>
+ <li>
+ We need access to data in an existing database.
+ The <code>relpipe-tr-sql</code> and <code>relpipe-in-sql</code> can be used as a generic database clients
+ and are able to load relational data to and from any DBMS.
+ We can also write a pipeline to transfer data between two different DBMS, do some ETL (extract, transform, load) tasks
+ or just cache some result sets from a remote database in our local SQLite file.
+ We can cache e.g. some codelist tables or other data for offline use:
+ <m:pre jazyk="bash"><![CDATA[relpipe-in-sql \
+ --data-source-name "MyCompanyDatabase" \
+ --relation "country" "SELECT * FROM country" \
+ --relation "currency" "SELECT * FROM currency" \
+ --relation "exchange_rate" "SELECT * FROM exchange_rate WHERE …" \
+ --relation "phonebook" "SELECT * FROM phonebook" \
+ | relpipe-tr-sql \
+ --data-source-string 'Driver=SQLite3;Database=file:MyCachedCompanyData.sqlite']]></m:pre>
+ In previous versions, we needed <a href="https://sql-dk.globalcode.info/">SQL-DK</a> for this scenario,
+ now it is possible solely in <m:name/> without any other tools.
+ But SQL-DK is still useful – especially if we have a JDBC driver but do not have an ODBC one
+ (JDBC drivers and Java are also much more portable).
+ </li>
+ </ul>
+
+ <p>
+ n.b. However it still looks like executing a local command, we should be aware that while using a remote data source,
+ our data travel to given remote server – this impacts performance and our privacy.
+ Never use untrustworthy remote server for processing sensitive data (even if using just a temporary schema or tables).
+ If SQLite is „too small“ then PostgreSQL installed on <i>localhost</i> is usually a good option.
+ </p>
+
+ <p>
+ There are ODBC drivers for any conceivable database system.
+ We can also write a custom driver for any other resource and just plug it in <m:name/>
+ without recompiling (a driver is a shared library – simply an <code>.so</code> file).
+ </p>
+
+ <p>
+ This release also comes with better diagnostics. This feature is not specific to ODBC, but was implemented during the rewrite of the database layer.
+ So if we make a mistake in our query or try to create a table with the same name as already exists in the DB, we will get a useful message with detailed description of the problem
+ (instead of a pointless failure notice in the previous version).
+ </p>
+
+ <p>
+ The new implementation of <code>relpipe-tr-sql</code> is still a bit <i>raw</i> and will be tuned in the upcoming versions,
+ but it seems working quite well (with SQLite, PostgreSQL and MySQL on GNU/Linux).
+ As always, testers are welcomed.
+ </p>
+
+ <p>
+ More details in the example: <m:a href="examples-tr-sql-odbc">Accessing SQLite, PostgreSQL and MySQL through ODBC</m:a>.
+ </p>
+
+ <h2>JACK (MIDI) input module</h2>
+
+ <p>
+ A powerful audio system called <a href="https://jackaudio.org/">JACK</a> allows us to
+ build pipelines consisting of audio interfaces, players, recorders, filters and effects…
+ and route sound streams (both PCM and MIDI) through them.
+ MIDI messages can come from keyboards or other hardware MIDI controllers or from MIDI players and other software.
+ Sometimes it is useful to check what is happening under the hood and examine particular MIDI messages
+ instead of just playing them on a sound module or synthesizer.
+ Now we can bridge two seemingly unrelated worlds: real-time audio and relational pipes.
+ </p>
+
+ <m:img src="img/jack-connections-1.png"/>
+
+ <p>
+ We can join the JACK graph with <code>relpipe-in-jack</code> command.
+ It does not consume STDIN, it gets events from JACK instead, so no other input data are needed.
+ </p>
+
+ <p>
+ More details in the example: <m:a href="examples-jack-midi-monitoring">Monitoring MIDI messages using JACK</m:a>.
+ </p>
+
+ <h2>Feature overview</h2>
+
+ <h3>Data types</h3>
+ <ul>
+ <li m:since="v0.8">boolean</li>
+ <li m:since="v0.15">variable-length signed integer (SLEB128)</li>
+ <li m:since="v0.8">string in UTF-8</li>
+ </ul>
+ <h3>Inputs</h3>
+ <ul>
+ <li m:since="v0.11">Recfile</li>
+ <li m:since="v0.9">XML</li>
+ <li m:since="v0.13">XMLTable</li>
+ <li m:since="v0.9">CSV</li>
+ <li m:since="v0.9">file system</li>
+ <li m:since="v0.8">CLI</li>
+ <li m:since="v0.8">fstab</li>
+ <li m:since="v0.14">SQL script</li>
+ <li m:since="v0.16">JACK</li>
+ </ul>
+ <h3>Transformations</h3>
+ <ul>
+ <li m:since="v0.13">sql: filtering and transformations using the SQL language</li>
+ <li m:since="v0.12">awk: filtering and transformations using the classic AWK tool and language</li>
+ <li m:since="v0.10">guile: filtering and transformations defined in the Scheme language using GNU Guile</li>
+ <li m:since="v0.8">grep: regular expression filter, removes unwanted records from the relation</li>
+ <li m:since="v0.8">cut: regular expression attribute cutter (removes or duplicates attributes and can also DROP whole relation)</li>
+ <li m:since="v0.8">sed: regular expression replacer</li>
+ <li m:since="v0.8">validator: just a pass-through filter that crashes on invalid data</li>
+ <li m:since="v0.8">python: highly experimental</li>
+ </ul>
+ <h3>Streamlets</h3>
+ <ul>
+ <li m:since="v0.15">xpath (example, unstable)</li>
+ <li m:since="v0.15">hash (example, unstable)</li>
+ <li m:since="v0.15">jar_info (example, unstable)</li>
+ <li m:since="v0.15">mime_type (example, unstable)</li>
+ <li m:since="v0.15">exiftool (example, unstable)</li>
+ <li m:since="v0.15">pid (example, unstable)</li>
+ <li m:since="v0.15">cloc (example, unstable)</li>
+ <li m:since="v0.15">exiv2 (example, unstable)</li>
+ <li m:since="v0.15">inode (example, unstable)</li>
+ <li m:since="v0.15">lines_count (example, unstable)</li>
+ <li m:since="v0.15">pdftotext (example, unstable)</li>
+ <li m:since="v0.15">pdfinfo (example, unstable)</li>
+ <li m:since="v0.15">tesseract (example, unstable)</li>
+ </ul>
+ <h3>Outputs</h3>
+ <ul>
+ <li m:since="v0.11">ASN.1 BER</li>
+ <li m:since="v0.11">Recfile</li>
+ <li m:since="v0.9">CSV</li>
+ <li m:since="v0.8">tabular</li>
+ <li m:since="v0.8">XML</li>
+ <li m:since="v0.8">nullbyte</li>
+ <li m:since="v0.8">GUI in Qt</li>
+ <li m:since="v0.8">ODS (LibreOffice)</li>
+ </ul>
+
+ <h2>New examples</h2>
+ <ul>
+ <li><m:a href="examples-tr-sql-odbc">Accessing SQLite, PostgreSQL and MySQL through ODBC</m:a></li>
+ <li><m:a href="examples-jack-midi-monitoring">Monitoring MIDI messages using JACK</m:a></li>
+ </ul>
+
+ <h2>Backward incompatible changes</h2>
+
+ <p>
+ The options <code>--file</code> and <code>--file-keep</code> in <code>relpipe-tr-sql</code> (and <code>relpipe-in-sql</code>, which is an alias for the same binary)
+ have been dropped.
+ These options were specific to SQLite and make no sense now, when we do not depend on particular DBMS and can use any <i>engine</i> for SQL processing
+ (even a remote one somewhere on the network that could not reach our local files).
+ However SQLite is still the default option and the:
+ </p>
+
+ <m:pre jazyk="bash">relpipe-tr-sql --file 'myDatabase.sqlite'</m:pre>
+
+ <p>can be simply replaced by:</p>
+
+ <m:pre jazyk="bash">relpipe-tr-sql --data-source-string 'Driver=SQLite3;Database=file:myDatabase.sqlite'</m:pre>
+
+ <p>
+ Bash-completion works and will suggest even the <code>Driver=SQLite3;Database=file:</code> part, so it is not necessary to memorize the connection string.
+ Frequently used databases can be configured in the <code>~/.odbc.ini</code> file and then referenced just by their names using <code>--data-source-name</code>
+ (the data source names – DSN – are also suggested by Bash-completion).
+ </p>
+
+ <p>
+ There is no built-in replacement for the <code>--file-keep</code> option.
+ But if the user wants to create a temporary file and delete it at the end of the transformation,
+ he can simply add <code>rm -f myDatabase.sqlite</code> to his script.
+ </p>
+
+ <h2>Installation</h2>
+
+ <p>
+ Instalation was tested on Debian GNU/Linux 10.2.
+ The process should be similar on other distributions.
+ </p>
+
+ <m:pre src="examples/release-v0.16.sh" jazyk="bash" odkaz="ano"/>
+
+ <p>
+ <m:name/> are modular thus you can download and install only parts you need (the libraries are needed always).
+ Tools <code>out-gui.qt</code> and <code>tr-python</code> require additional libraries and are not built by default.
+ </p>
+
+ </text>
+
+</stránka>
\ No newline at end of file
--- a/relpipe-data/roadmap.xml Thu May 21 01:23:23 2020 +0200
+++ b/relpipe-data/roadmap.xml Sat Jun 06 01:57:24 2020 +0200
@@ -16,7 +16,7 @@
Released versions are described on the <m:a href="download">download</m:a> page.
</p>
- <h2>v0.16, v0.17, v0.18 etc.</h2>
+ <h2>v0.17, v0.18, v0.19 etc.</h2>
<p>
Releases for discussion and verification of the format and API design.
@@ -195,6 +195,7 @@
<p>Other libraries and tools:</p>
<ul>
<li>ORM API: mapping between classes/objects and relations/records</li>
+ <li>JDBC and ODBC drivers</li>
<li>schemas (XSD-like)</li>
<li>generators, compilers, validators, comparators</li>
<li>transformer helpers</li>