https://nblock.org/feed.atomRecent Blog Posts2023-11-18T12:28:45.184641+00:00python-feedgenRecent blog postshttps://nblock.org/2017/05/07/export-multiple-svg-layersExport multiple SVG layers2017-05-07T00:00:00+01:05Florian Preinstorfer<p>Images in presentations tend to be very similar to each other. For example a
base image visualizes an empty sequence diagram and with each slide in the
presentation more and more items are added to the sequence diagram (e.g.
<a class="reference external" href="/static/talks/slides-glt17-ssh.pdf">slides for my talk at Grazer Linuxtage 2017</a>). Inkscape is a nice solution to draw
such diagrams because it allows to put the various steps on different layers.
Different images may be generated by selectively showing/hiding layers before
running the export process.</p>
<p>Unfortunately, Inkscape (version <= 0.92) does not provide a command line
option where the user can pass a list of layers that should be visible in the
exported image. One can export such images by hand using the Inkscape GUI but
this is an error prone and repetitive process and should be automated. Since
SVG is just an XML file, one can use other XML tools to perform the desired
processing before exporting.</p>
<p>Suppose you have an SVG file with the following layers (labels):</p>
<ul class="simple">
<li>base</li>
<li>transport-1</li>
<li>transport-2</li>
<li>transport-3</li>
</ul>
<p>In order to create a new SVG file where only the layers <em>base</em> and
<em>transport-1</em> are visible, the following command may be used:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ xmlstarlet ed -P <span class="se">\</span>
-N <span class="nv">inkscape</span><span class="o">=</span>http://www.inkscape.org/namespaces/inkscape <span class="se">\</span>
-N <span class="nv">svg</span><span class="o">=</span>http://www.w3.org/2000/svg <span class="se">\</span>
-u <span class="s1">'//*/svg:g[@inkscape:label]/@style'</span> -v display:none <span class="se">\</span>
-u <span class="s1">'//*/svg:g[@inkscape:label="base"]/@style'</span> -v display:inline <span class="se">\</span>
-u <span class="s1">'//*/svg:g[@inkscape:label="transport-1"]/@style'</span> -v display:inline <span class="se">\</span>
input.svg > output.svg
</pre></div>
</div>
</blockquote>
<p>The command above loads SVG namespaces, selects objects with by using an XPath
expression and updates the display attribute to the desired value. The first
step is to select all labels and hide them. After that, the labels <em>base</em> and
<em>transport-1</em> are selected to display them.</p>
<p>The resulting SVG file can be converted to another format such as PDF using
Inkscape:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ inkscape --without-gui --export-area-page --export-pdf-version<span class="o">=</span><span class="s2">"1.5"</span> <span class="se">\</span>
--export-pdf<span class="o">=</span>output.pdf output.svg
</pre></div>
</div>
</blockquote>
<p>Hide this somewhere in a Makefile and you're done.</p>
https://nblock.org/2017/09/19/automatically-recover-a-failing-usb-lte-modemAutomatically recover a failing USB LTE modem2017-09-19T00:00:00+01:05Florian Preinstorfer<p>My home network consists of a wireless router running LEDE 17.01 and a ZTE
MF831 LTE USB modem for Internet connectivity. From time to time the Internet
connection fails and the only way to recover was to physically reconnect the
USB modem. So each time it failed, I had to get to my wireless router, pull the
USB modem and reconnect it. This post describes the steps I took to work around
this issue.</p>
<p>The lost connection doesn't seem to follow a pattern. Sometimes it happens every
day and sometimes the connection works for weeks without any issues. But still,
the problem exists and when it kicks in, the system is not able to recover
itself. When the connection dies, <tt class="docutils literal">logread</tt> contains the following log entries:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>[snipped]
daemon.info pppd[9626]: No response to 5 echo-requests
daemon.notice pppd[9626]: Serial link appears to be disconnected.
daemon.info pppd[9626]: Connect time 39.7 minutes.
daemon.info pppd[9626]: Sent 90740877 bytes, received 878872777 bytes.
daemon.notice netifd: Network device '3g-provider' link is down
daemon.notice netifd: Interface 'provider' has lost the connection
daemon.warn dnsmasq[1466]: no servers found in /tmp/resolv.conf.auto, will retry
daemon.info odhcpd[954]: Using a RA lifetime of 0 seconds on br-lan
daemon.notice pppd[9626]: Connection terminated.
daemon.notice pppd[9626]: Modem hangup
daemon.info pppd[9626]: Exit.
daemon.notice netifd: Interface 'provider' is now down
daemon.notice netifd: Interface 'provider' is setting up now
daemon.notice netifd: provider (9858): comgt 12:02:15 -> -- Error Report --
daemon.notice netifd: provider (9858): comgt 12:02:15 -> ----> ^
daemon.notice netifd: provider (9858): comgt 12:02:15 -> Error @118, line 9, Could not \
write to COM device. (1)
daemon.notice netifd: provider (9858):
daemon.notice pppd[9873]: pppd 2.4.7 started by root, uid 0
local2.info chat[9875]: abort on (BUSY)
local2.info chat[9875]: abort on (NO CARRIER)
local2.info chat[9875]: abort on (ERROR)
local2.info chat[9875]: report (CONNECT)
local2.info chat[9875]: timeout set to 10 seconds
local2.info chat[9875]: send (AT&F^M)
local2.info chat[9875]: alarm
local2.info chat[9875]: -- write timed out
local2.err chat[9875]: Failed
daemon.err pppd[9873]: Connect script failed
[snipped]
</pre></div>
</div>
</blockquote>
<p>Interestingly, the devices in <tt class="docutils literal">/dev/ttyUSB*</tt> are still there and the logs
don't contain anything USB related.</p>
<p>PPPD notices that the serial connection with the modem is broken and shuts
down. Simply restarting the interface afterwards (<tt class="docutils literal">ifdown</tt>/<tt class="docutils literal">ifup</tt>, web
interface) does not work. The first step of the workaround is to restart the
USB modem via software. Fortunately, this <a class="reference external" href="https://unix.stackexchange.com/questions/7412/how-to-reconnect-a-logically-disconnected-usb-device/306321#306321">Stack Exchange</a> post pointed me
into the right direction. A simple unbind followed by a bind on the correct USB
port works fine. On unbind, the modem disappears and all <tt class="docutils literal">/dev/ttyUSB*</tt>
devices are removed by the kernel. On bind, the kernel re-initializes the
modem, does some mode switching and a few seconds later, the <tt class="docutils literal">/dev/ttyUSB*</tt>
devices reappear. After this unbind/bind cycle, PPPD is started automatically
and Internet connectivity is restored. A list of USB ports may be obtained via:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span><span class="c1"># find /sys/bus/usb/devices/</span>
/sys/bus/usb/devices/
/sys/bus/usb/devices/1-1
/sys/bus/usb/devices/usb1
/sys/bus/usb/devices/usb2
/sys/bus/usb/devices/1-0:1.0
/sys/bus/usb/devices/1-1:1.0
/sys/bus/usb/devices/1-1:1.1
/sys/bus/usb/devices/1-1:1.2
/sys/bus/usb/devices/2-0:1.0
</pre></div>
</div>
</blockquote>
<p>There is one problem though, I want the reconnection steps to trigger
automatically when PPPD detects that the serial link stopped working.
Fortunately, PPPD offers various <a class="reference external" href="https://man.cx/pppd(1)#heading5">hooks</a> that one can leverage. In my case, the
<tt class="docutils literal"><span class="pre">ip-down</span></tt> hook is the correct one. It is called with various arguments and
with some environment variables. To enable a <tt class="docutils literal"><span class="pre">ip-down</span></tt> hook on OpenWRT/LEDE,
create the directory <tt class="docutils literal"><span class="pre">/etc/ppp/ip-down.d</span></tt> and place your executable
<tt class="docutils literal"><span class="pre">ip-down</span></tt> script in this directory. All <tt class="docutils literal"><span class="pre">ip-down</span></tt> scripts in
<tt class="docutils literal"><span class="pre">/etc/ppp/ip-down.d</span></tt> executed each time PPPD had a working IP connectivity
and is in the process of shutting down. The last part of the puzzle is to only
trigger the reconnection when the serial link is faulty. Especially, do not
trigger when:</p>
<ul class="simple">
<li>The user requested to shutdown the interface (<tt class="docutils literal">ifdown</tt>, web interface).</li>
<li>The USB modem is physically disconnected.</li>
</ul>
<p>The complete solution is the shell script listed below. It leverages the
OpenWRT/LEDE logging system and the fact that PPPD sets the environment
variable <tt class="docutils literal">PPPD_PID</tt>. I only need to inspect log entries produced by the
<em>currently running</em> PPPD and find log entries that indicate a faulty serial
link.</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span><span class="ch">#!/bin/sh</span>
<span class="c1"># pppd ip-down script to reset a USB LTE Modem when the serial link is faulty.</span>
<span class="c1"># The USB port where the USB LTE modem is connected.</span>
<span class="nv">USB_DEVICE_ADDRESS</span><span class="o">=</span><span class="s2">"1-1"</span>
<span class="c1"># Try to find out why pppd is shutting down and only reset the device when the</span>
<span class="c1"># serial link is faulty. Exit early otherwise. Luckily, pppd provides us with</span>
<span class="c1"># some environment variables/arguments that we can leverage:</span>
<span class="c1"># - PPPD_PID -> The PID of the *calling, currently running* pppd.</span>
<span class="c1"># Exit if we are not called by pppd.</span>
<span class="o">[</span> -z <span class="s2">"</span><span class="nv">$PPPD_PID</span><span class="s2">"</span> <span class="o">]</span> <span class="o">&&</span> <span class="nb">exit</span> <span class="m">0</span>
<span class="c1"># pppd logs that a certain amount of echo-requests sent to the device failed.</span>
<span class="k">if</span> ! logread <span class="p">|</span> grep -q <span class="s2">"pppd\[</span><span class="nv">$PPPD_PID</span><span class="s2">\]: No response to .\+ echo-requests"</span><span class="p">;</span> <span class="k">then</span>
<span class="nb">exit</span> <span class="m">0</span>
<span class="k">fi</span>
<span class="c1"># pppd also logs that the serial link appears to be disconnected.</span>
<span class="k">if</span> ! logread <span class="p">|</span> grep -q <span class="s2">"pppd\[</span><span class="nv">$PPPD_PID</span><span class="s2">\]: Serial link appears to be disconnected"</span><span class="p">;</span> <span class="k">then</span>
<span class="nb">exit</span> <span class="m">0</span>
<span class="k">fi</span>
<span class="c1"># Reset the device</span>
logger <span class="s2">"Reset USB device at address </span><span class="nv">$USB_DEVICE_ADDRESS</span><span class="s2">"</span>
<span class="nb">echo</span> <span class="s2">"</span><span class="nv">$USB_DEVICE_ADDRESS</span><span class="s2">"</span> > /sys/bus/usb/drivers/usb/unbind
sleep <span class="m">1</span>
<span class="nb">echo</span> <span class="s2">"</span><span class="nv">$USB_DEVICE_ADDRESS</span><span class="s2">"</span> > /sys/bus/usb/drivers/usb/bind
logger <span class="s2">"Reset complete"</span>
</pre></div>
</div>
</blockquote>
https://nblock.org/2018/02/17/new-gnupg-keyGnuPG key transition statement2018-02-17T00:00:00+01:05Florian Preinstorfer<p>I am transitioning GPG keys from my old 4096-bit RSA key to a new 4096-bit RSA
key. The old key will continue to be valid for some time, but I prefer all new
correspondance to be encrypted in the new key, and will be making all
signatures going forward with the new key.</p>
<p>Here is my transition statement</p>
<pre class="literal-block">
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512
I am transitioning GPG keys from my old 4096-bit RSA key to a new
4096-bit RSA key. The old key will continue to be valid for some time,
but I prefer all new correspondance to be encrypted in the new key,
and will be making all signatures going forward with the new key.
This transition document is signed with both keys to validate the
transition.
If you have signed my old key, I would appreciate signatures on my new
key as well, provided that your signing policy permits that without
reauthenticating me.
The old key, which I am transitional away from, is:
pub rsa4096 2013-02-23 [SC] [expires: 2018-02-22]
Key fingerprint = 89C9 5CF0 871D 6EC1 0A3F ECD9 741E 93C2 2741 5CF9
The new key, to which I am transitioning, is:
pub rsa4096 2018-02-17 [SC] [expires: 2021-02-16]
Key fingerprint = 65D0 A6E4 6387 883E C3B5 E78C D67A 997E FEA3 D7C1
To fetch the full new key from a public key server using GnuPG, run:
gpg --recv-keys D67A997EFEA3D7C1
If you have already validated my old key, you can then validate that
the new key is signed by my old key:
gpg --check-sigs D67A997EFEA3D7C1
If you then want to sign my new key, a simple and safe way to do that
is by using caff (shipped in Debian as part of the "signing-party"
package) as follows:
caff D67A997EFEA3D7C1
Find contact details at https://nblock.org/about if you have any
questions about this document or this transition.
Florian Preinstorfer
17-02-2018
-----BEGIN PGP SIGNATURE-----
iQIzBAEBCgAdFiEEy0qC0zkB+ER+IRLzPkJ06FLyrlMFAlqINJUACgkQPkJ06FLy
rlOwZg//QH4s6rRhcHbLx0ASr+LuqbJcqLGuZZxC/TcX0RTBVK/6EOzxcw/HFYpU
P0Hb65GYeVWHAnPMqp8t+lL6dhgCjE4AtddlXX+6T0Pusuh5SPJ23ztymq7DQwF+
giC1eRYWF0tpBbEpUuTqgFAkeMi3wRJsDCuh2z542rgv3EcjeS7u/j0RsBKRFLbB
FRC+YbIWEjLURUVzpYYqlEOq/3Q5x0p+JF3oyUG4Uz8hfToM0CxzS22GUgoBUqfg
1rob57ovt8kK+bp1IdWz/3T4GS2xWvQZljSQ4xWHySqCW4Yspej/L3cFToacdVM/
kMWOh25kpZrE1GQP2LVUK7YsbAa9jvjtBg2KSQw7nopor8RD1WuW3ztW71NVEkBB
Qd3EW0bIBur7CeWxg8m6lJfUY67Dymx/fHXiaLH6wJ1eVP0JzlUpWqhFhqTfWSQG
e77MXwR5oAbKUghYzB+gNO6g3xOVTlTV1THpRuD/fI6acKdxKcOioFJg6IOP6X13
sj7QPG0Ze5xBAdGoYmNzRSEdgbgT61Mk/gu2cKXJ4050xpBQw2FhFHGLi+SwHvuv
w9nE+V9l8hJHufcn2YUpPSUJcneTET0JtLLpUTxjG+GjVCByBdM98MDr7nyb7NQc
6Sz9fF9nUunwvZZbPjI8skvCnNfOYLui9j2mhCLumqSbnkqMlQqJAjMEAQEKAB0W
IQSjDdzD+eoe0FJE12Ih7BDxHIXZfAUCWog0lQAKCRAh7BDxHIXZfFtHD/0chSc8
x2tHHHC/EnAgc52vAEi85ZPkX6TYiDTT/rEO1U5EB45CDf1MZ5wMngqAI/3b0TeW
JMuESwcbCu4CmLkPJhznnJgFzZ9pdnautzYoqwwTujX/Y4YvQzb8HoJmARK1A/JF
+JtE0mpuWQen3tJC5hyRgd798+lXqFbNSnsFJn/1UCuBBPKhVpJERyWukQuXWASe
SW61n4xWQoxIGTzI/AWm2KE/pe8O7m/eyj10I5HQj2r0eMkWuqHjdHf8+X+GcRoo
P3RmO7bhwPNbyx6yGeegykWavw+xQxSUKHlLfUzRY2cz4t5Oj9zKwHflEkeZeQYJ
VPNZWdtvEc3PmLzbOUtEJmT905I6mWgCyX1ES3AT+aYG1TWHYEss0v8olc6zqcqX
v4yrjlJ4DjbZ7dcmEKAeSvi4M7l3lEZTw9Z3YENFsjzUpQxW0Gb6V24c3Sy6zBgE
Ffo4ltPQp4Wg0+PdrYKAsaWNNgPhKzm69m24AI+jHIgZDIF546ySNc5SxaDfp7Xy
AjgfvjhQI33uWq9yLea/RN9g0C4OBttuUJbAuKOcCykOIVfuvYkCTOnDzn2MFJPr
lSq2oOexypJgUPFR6RRNvdP0WB/hJjPD5kY+X/A35kl/+/JV/dabUfzwnAaVcvEt
OpjKeOTPQaYNItLd8OPR8BBr7/QvWDgbuu1mwg==
=Zctm
-----END PGP SIGNATURE-----
</pre>
<p>You can also download the above statement from <a class="reference external" href="/static/files/gpg-transition-statement-D67A997EFEA3D7C1.txt.asc">here</a>. Use the
following commands to verify the integrity of the transition statement:</p>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ gpg --recv-keys D67A997EFEA3D7C1
$ curl https://nblock.org/static/files/gpg-transition-statement-D67A997EFEA3D7C1.txt.asc <span class="p">|</span> gpg --verify
</pre></div>
</div>
https://nblock.org/2018/04/03/sqlalchemy-automap-and-custom-typesSQLAlchemy automap and custom types2018-04-03T00:00:00+01:05Florian Preinstorfer<p>An API is an important part of an application when it comes to automation. For
those web applications that do not offer an API, I typically write some small
application on top of <a class="reference external" href="https://scrapy.org/">Scrapy</a> or <a class="reference external" href="http://docs.python-requests.org/en/master/">Requests</a> to solve the problem.</p>
<p>The application in question is an old PHP application. There is no API
available and adding an API to it is out of scope. MySQL is used as the
database and most of the 79 tables are stored with MyISAM. Recently added
tables are stored with InnoDB. There are just a few unique constraints on the
database side and no foreign key constraints (due to MyISAM). The entire logic
is coded into the PHP application.</p>
<p>The web application's database can be accessed directly, so there is no need
for scraping. The following SQL listing provides a minimal working example for
the purpose of this blog post.</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span><span class="c1">-- create tables</span>
<span class="k">CREATE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="k">IF</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">EXISTS</span><span class="w"> </span><span class="o">`</span><span class="n">hosts</span><span class="o">`</span><span class="w"> </span><span class="p">(</span><span class="w"></span>
<span class="w"> </span><span class="o">`</span><span class="n">id</span><span class="o">`</span><span class="w"> </span><span class="nb">int</span><span class="p">(</span><span class="mi">11</span><span class="p">)</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="n">AUTO_INCREMENT</span><span class="p">,</span><span class="w"></span>
<span class="w"> </span><span class="o">`</span><span class="n">name</span><span class="o">`</span><span class="w"> </span><span class="nb">varchar</span><span class="p">(</span><span class="mi">255</span><span class="p">)</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"></span>
<span class="w"> </span><span class="o">`</span><span class="k">state</span><span class="o">`</span><span class="w"> </span><span class="nb">int</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">DEFAULT</span><span class="w"> </span><span class="s1">'1'</span><span class="p">,</span><span class="w"></span>
<span class="w"> </span><span class="o">`</span><span class="n">os_id</span><span class="o">`</span><span class="w"> </span><span class="nb">int</span><span class="p">(</span><span class="mi">11</span><span class="p">)</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">DEFAULT</span><span class="w"> </span><span class="s1">'1'</span><span class="p">,</span><span class="w"></span>
<span class="w"> </span><span class="k">PRIMARY</span><span class="w"> </span><span class="k">KEY</span><span class="w"> </span><span class="p">(</span><span class="o">`</span><span class="n">id</span><span class="o">`</span><span class="p">),</span><span class="w"></span>
<span class="w"> </span><span class="k">UNIQUE</span><span class="w"> </span><span class="k">KEY</span><span class="w"> </span><span class="o">`</span><span class="n">name</span><span class="o">`</span><span class="w"> </span><span class="p">(</span><span class="o">`</span><span class="n">name</span><span class="o">`</span><span class="p">)</span><span class="w"></span>
<span class="p">)</span><span class="w"> </span><span class="n">ENGINE</span><span class="o">=</span><span class="n">MyISAM</span><span class="w"> </span><span class="k">DEFAULT</span><span class="w"> </span><span class="n">CHARSET</span><span class="o">=</span><span class="n">latin1</span><span class="w"> </span><span class="n">AUTO_INCREMENT</span><span class="o">=</span><span class="mi">8</span><span class="w"> </span><span class="p">;</span><span class="w"></span>
<span class="k">CREATE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="k">IF</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">EXISTS</span><span class="w"> </span><span class="o">`</span><span class="n">os_types</span><span class="o">`</span><span class="w"> </span><span class="p">(</span><span class="w"></span>
<span class="w"> </span><span class="o">`</span><span class="n">id</span><span class="o">`</span><span class="w"> </span><span class="nb">int</span><span class="p">(</span><span class="mi">11</span><span class="p">)</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="n">AUTO_INCREMENT</span><span class="p">,</span><span class="w"></span>
<span class="w"> </span><span class="o">`</span><span class="n">name</span><span class="o">`</span><span class="w"> </span><span class="nb">varchar</span><span class="p">(</span><span class="mi">255</span><span class="p">)</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"></span>
<span class="w"> </span><span class="k">PRIMARY</span><span class="w"> </span><span class="k">KEY</span><span class="w"> </span><span class="p">(</span><span class="o">`</span><span class="n">id</span><span class="o">`</span><span class="p">),</span><span class="w"></span>
<span class="w"> </span><span class="k">UNIQUE</span><span class="w"> </span><span class="k">KEY</span><span class="w"> </span><span class="o">`</span><span class="n">name</span><span class="o">`</span><span class="w"> </span><span class="p">(</span><span class="o">`</span><span class="n">name</span><span class="o">`</span><span class="p">)</span><span class="w"></span>
<span class="p">)</span><span class="w"> </span><span class="n">ENGINE</span><span class="o">=</span><span class="n">MyISAM</span><span class="w"> </span><span class="k">DEFAULT</span><span class="w"> </span><span class="n">CHARSET</span><span class="o">=</span><span class="n">latin1</span><span class="w"> </span><span class="n">AUTO_INCREMENT</span><span class="o">=</span><span class="mi">4</span><span class="w"> </span><span class="p">;</span><span class="w"></span>
<span class="c1">-- stuff some sample data into the tables</span>
<span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="o">`</span><span class="n">hosts</span><span class="o">`</span><span class="w"> </span><span class="p">(</span><span class="o">`</span><span class="n">id</span><span class="o">`</span><span class="p">,</span><span class="w"> </span><span class="o">`</span><span class="n">name</span><span class="o">`</span><span class="p">,</span><span class="w"> </span><span class="o">`</span><span class="k">state</span><span class="o">`</span><span class="p">,</span><span class="w"> </span><span class="o">`</span><span class="n">os_id</span><span class="o">`</span><span class="p">)</span><span class="w"> </span><span class="k">VALUES</span><span class="w"></span>
<span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'astoria'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"></span>
<span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="s1">'fiddle'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"></span>
<span class="p">(</span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="s1">'freeman'</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"></span>
<span class="p">(</span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="s1">'liard'</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"></span>
<span class="p">(</span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="s1">'leand'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"></span>
<span class="p">(</span><span class="mi">6</span><span class="p">,</span><span class="w"> </span><span class="s1">'algar'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"></span>
<span class="p">(</span><span class="mi">7</span><span class="p">,</span><span class="w"> </span><span class="s1">'ells'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span><span class="w"></span>
<span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="o">`</span><span class="n">os_types</span><span class="o">`</span><span class="w"> </span><span class="p">(</span><span class="o">`</span><span class="n">id</span><span class="o">`</span><span class="p">,</span><span class="w"> </span><span class="o">`</span><span class="n">name</span><span class="o">`</span><span class="p">)</span><span class="w"> </span><span class="k">VALUES</span><span class="w"></span>
<span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'Debian Wheezy'</span><span class="p">),</span><span class="w"></span>
<span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="s1">'Debian Jessie'</span><span class="p">),</span><span class="w"></span>
<span class="p">(</span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="s1">'Debian Stretch'</span><span class="p">);</span><span class="w"></span>
</pre></div>
</div>
</blockquote>
<p>There are two tables, <tt class="docutils literal">hosts</tt> and <tt class="docutils literal">os_types</tt>. The rows of the <tt class="docutils literal">os_types</tt>
table are referenced via <tt class="docutils literal">hosts.os_id</tt> inside the PHP application. There is
no immediate connection on the database level. The <tt class="docutils literal">hosts</tt> table contains a
<tt class="docutils literal">state</tt> column with the following magic numbers:</p>
<ul class="simple">
<li>1: active</li>
<li>2: disabled</li>
<li>3: unknown</li>
<li>4: deleted</li>
</ul>
<p>The task is simple: Connect to the database and print the name, state and the
name of the OS.</p>
<div class="section" id="try-1">
<h2>Try #1</h2>
<p>I don't want to write SQL by hand and I certainly don't want to remember all
the magic numbers. So, I decided to give <a class="reference external" href="http://www.sqlalchemy.org/">SQLAlchemy</a>, a popular Object
Relational Mapper for Python, another try. The typical usage is to define your
model in plain Python and let <a class="reference external" href="http://www.sqlalchemy.org/">SQLAlchemy</a> manage the database side for you.
This is convenient for new projects or if the database has 5 tables in total.
The database of this application manages 79 tables and some of them contain a
lot of columns (e.g. 26 columns for a single table). That's too much typing.
Fortunately, <a class="reference external" href="http://www.sqlalchemy.org/">SQLAlchemy</a> offers a feature called <a class="reference external" href="http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html">Automap</a> where it connects
to a database, inspects the tables and tries to figure out the models for you.
OK, now some code:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">create_engine</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.automap</span> <span class="kn">import</span> <span class="n">automap_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">Session</span>
<span class="c1"># The declarative base used for the SQLAlchemy reflection.</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">automap_base</span><span class="p">()</span>
<span class="k">def</span> <span class="nf">main</span><span class="p">():</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'mysql://USER:PASS@HOST:PORT/DATABASE'</span><span class="p">)</span>
<span class="c1"># Perform automap and create a session</span>
<span class="n">Base</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="n">reflect</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="c1"># Use the session</span>
<span class="n">Hosts</span> <span class="o">=</span> <span class="n">Base</span><span class="o">.</span><span class="n">classes</span><span class="o">.</span><span class="n">hosts</span>
<span class="n">OSTypes</span> <span class="o">=</span> <span class="n">Base</span><span class="o">.</span><span class="n">classes</span><span class="o">.</span><span class="n">os_types</span>
<span class="k">for</span> <span class="n">host</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Hosts</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">state</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">():</span>
<span class="n">os_type</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">OSTypes</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">host</span><span class="o">.</span><span class="n">os_id</span><span class="p">)</span>
<span class="nb">print</span><span class="p">(</span><span class="n">host</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">host</span><span class="o">.</span><span class="n">state</span><span class="p">,</span> <span class="n">os_type</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="k">if</span> <span class="vm">__name__</span> <span class="o">==</span> <span class="s2">"__main__"</span><span class="p">:</span>
<span class="n">main</span><span class="p">()</span>
</pre></div>
</div>
</blockquote>
<p>Run it:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ python sqlalchemy1.py
astoria <span class="m">1</span> Debian Stretch
leand <span class="m">1</span> Debian Wheezy
algar <span class="m">1</span> Debian Jessie
</pre></div>
</div>
</blockquote>
<p>It works but there are some obvious limitations. <a class="reference external" href="http://www.sqlalchemy.org/">SQLAlchemy</a> was not able to
figure out the relationship between <tt class="docutils literal">hosts.os_id</tt> and <tt class="docutils literal">os_types.id</tt>. So
the programmer has to manage the relationship by hand (this happens in the PHP
application). This is not only cumbersome but also error prone as one could
just set <tt class="docutils literal">os_id</tt> to an unreferenced value. Let's try to fix that.</p>
</div>
<div class="section" id="try-2">
<h2>Try #2</h2>
<p>One can provide some hints for <a class="reference external" href="http://www.sqlalchemy.org/">SQLAlchemy</a> in order to build up a relationship.
Take a look at the following version:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">ForeignKey</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">create_engine</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.automap</span> <span class="kn">import</span> <span class="n">automap_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">Session</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</span>
<span class="c1"># The declarative base used for the SQLAlchemy reflection.</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">automap_base</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">Hosts</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'hosts'</span>
<span class="c1"># custom types</span>
<span class="n">os_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'os_types.id'</span><span class="p">))</span>
<span class="c1"># relationships</span>
<span class="n">os</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s1">'os_types'</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s1">'hosts'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">main</span><span class="p">():</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'mysql://USER:PASS@HOST:PORT/DATABASE'</span><span class="p">)</span>
<span class="c1"># Perform automap and create a session</span>
<span class="n">Base</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="n">reflect</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="c1"># Use the session</span>
<span class="k">for</span> <span class="n">host</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Hosts</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">state</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">():</span>
<span class="nb">print</span><span class="p">(</span><span class="n">host</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">host</span><span class="o">.</span><span class="n">state</span><span class="p">,</span> <span class="n">host</span><span class="o">.</span><span class="n">os</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="k">if</span> <span class="vm">__name__</span> <span class="o">==</span> <span class="s2">"__main__"</span><span class="p">:</span>
<span class="n">main</span><span class="p">()</span>
</pre></div>
</div>
</blockquote>
<p>Run it:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ python sqlalchemy2.py
astoria <span class="m">1</span> Debian Stretch
leand <span class="m">1</span> Debian Wheezy
algar <span class="m">1</span> Debian Jessie
</pre></div>
</div>
</blockquote>
<p>I just added a <tt class="docutils literal">Hosts</tt> class that maps to the <tt class="docutils literal">hosts</tt> table. It adds a
<tt class="docutils literal">ForeignKey</tt> to the <tt class="docutils literal">os_id</tt> column and a relationship named <tt class="docutils literal">os</tt>. Usage
is much simpler now: it boils down to a single query and there is no need to
look up the name of the operating system by hand. But still, there is a magic
number in use (<tt class="docutils literal">state=1</tt>).</p>
</div>
<div class="section" id="try-3">
<h2>Try #3</h2>
<p>As noted above, the <tt class="docutils literal">state</tt> column has 4 known values. This pretty much looks
like an enum. <a class="reference external" href="http://www.sqlalchemy.org/">SQLAlchemy</a> has another nice feature for this particular use
case: <a class="reference external" href="http://docs.sqlalchemy.org/en/latest/core/custom_types.html">custom types</a>. The following version replaces the magic numbers with a
custom type implemented as Python enum:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span><span class="kn">import</span> <span class="nn">enum</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">ForeignKey</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">TypeDecorator</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">create_engine</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.automap</span> <span class="kn">import</span> <span class="n">automap_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">Session</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</span>
<span class="c1"># The declarative base used for the SQLAlchemy reflection.</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">automap_base</span><span class="p">()</span>
<span class="nd">@enum</span><span class="o">.</span><span class="n">unique</span>
<span class="k">class</span> <span class="nc">HostState</span><span class="p">(</span><span class="n">enum</span><span class="o">.</span><span class="n">IntEnum</span><span class="p">):</span>
<span class="c1"># This host is active and currently in use.</span>
<span class="n">ACTIVE</span> <span class="o">=</span> <span class="mi">1</span>
<span class="c1"># It is no longer in use, it is turned off.</span>
<span class="n">DISABLED</span> <span class="o">=</span> <span class="mi">2</span>
<span class="c1"># Unknown, literally</span>
<span class="n">UNKNOWN</span> <span class="o">=</span> <span class="mi">3</span>
<span class="c1"># This host is gone, away forever.</span>
<span class="n">DELETED</span> <span class="o">=</span> <span class="mi">4</span>
<span class="k">class</span> <span class="nc">HostStateTypeDecorator</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">Integer</span>
<span class="k">def</span> <span class="nf">process_bind_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="n">HostState</span><span class="p">):</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">value</span><span class="o">.</span><span class="n">value</span>
<span class="k">return</span> <span class="n">value</span>
<span class="k">def</span> <span class="nf">process_result_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">HostState</span><span class="p">(</span><span class="n">value</span><span class="p">)</span>
<span class="k">return</span> <span class="n">value</span>
<span class="k">class</span> <span class="nc">Hosts</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'hosts'</span>
<span class="c1"># custom types</span>
<span class="n">state</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">HostStateTypeDecorator</span><span class="p">)</span>
<span class="n">os_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'os_types.id'</span><span class="p">))</span>
<span class="c1"># relationships</span>
<span class="n">os</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s1">'os_types'</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s1">'hosts'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">main</span><span class="p">():</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'mysql://USER:PASS@HOST:PORT/DATABASE'</span><span class="p">)</span>
<span class="c1"># Perform automap and create a session</span>
<span class="n">Base</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="n">reflect</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="c1"># Use the session</span>
<span class="k">for</span> <span class="n">host</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Hosts</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">state</span><span class="o">=</span><span class="n">HostState</span><span class="o">.</span><span class="n">ACTIVE</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">():</span>
<span class="nb">print</span><span class="p">(</span><span class="n">host</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">host</span><span class="o">.</span><span class="n">state</span><span class="p">,</span> <span class="n">host</span><span class="o">.</span><span class="n">os</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="k">if</span> <span class="vm">__name__</span> <span class="o">==</span> <span class="s2">"__main__"</span><span class="p">:</span>
<span class="n">main</span><span class="p">()</span>
</pre></div>
</div>
</blockquote>
<p>Run it:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ python sqlalchemy3.py
astoria HostState.ACTIVE Debian Stretch
leand HostState.ACTIVE Debian Wheezy
algar HostState.ACTIVE Debian Jessie
</pre></div>
</div>
</blockquote>
<p>Sweet, the magic numbers are now gone and one can build a query using the
custom type: <tt class="docutils literal">state=HostState.ACTIVE</tt>. Magic numbers are converted in both
directions via <tt class="docutils literal">process_result_value()</tt> and <tt class="docutils literal">process_bind_param()</tt>.</p>
<p>Tested with Python 3.6.4 and <a class="reference external" href="http://www.sqlalchemy.org/">SQLAlchemy</a> 1.2.6.</p>
</div>
https://nblock.org/2018/09/27/tryton-gtk-client-inside-a-virtualenvTryton GTK client inside a virtualenv2018-09-27T00:00:00+01:05Florian Preinstorfer<p>I'm currently working on a <a class="reference external" href="https://tryton.org">Tryton</a> upgrade from 4.2 to 5.0 (via intermediate
releases 4.4, 4.6 and 4.8). <a class="reference external" href="https://tryton.org">Tryton</a> 5.0 is the first long term support release
with support for 5 years. A useful property for an ERP system. In order to test
each of the intermediate versions, I decided to quickly spawn a virtualenv and
install the <a class="reference external" href="https://tryton.org">Tryton</a> GTK client in there.</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ virtualenv -p python2 venv-tryton-4.4
$ . venv-tryton-4.4/bin/activate
$ pip install tryton~<span class="o">=</span><span class="m">4</span>.4
$ tryton
Traceback <span class="o">(</span>most recent call last<span class="o">)</span>:
File <span class="s2">"~/venv-tryton-4.4/bin/tryton"</span>, line <span class="m">48</span>, <span class="k">in</span> <module>
from tryton import client
File <span class="s2">"~/venv-tryton-4.4/local/lib/python2.7/site-packages/tryton/client.py"</span>, line <span class="m">17</span>, <span class="k">in</span> <module>
import pygtk
ImportError: No module named pygtk
</pre></div>
</div>
</blockquote>
<p>Installing <tt class="docutils literal">pygtk</tt> and its dependencies in a virtualenv is not without its
problems. But, there is a rather quick (and hackish) solution to this problem.
This is only suitable for quick tests and experiments. Use proper Debian
packages on production systems.</p>
<div class="section" id="python-2-7">
<h2>Python 2.7</h2>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ virtualenv -p python2 venv-tryton-4.4
$ . venv-tryton-4.4/bin/activate
$ pip install tryton~<span class="o">=</span><span class="m">4</span>.4
$ pip install PyGObject
$ ln -s /usr/lib/python2.7/dist-packages/pygtk.py <span class="nv">$VIRTUAL_ENV</span>/lib/python2.7/site-packages
$ ln -s /usr/lib/python2.7/dist-packages/gtk-2.0 <span class="nv">$VIRTUAL_ENV</span>/lib/python2.7/site-packages
$ ln -s /usr/lib/python2.7/dist-packages/gobject <span class="nv">$VIRTUAL_ENV</span>/lib/python2.7/site-packages
$ ln -s /usr/lib/python2.7/dist-packages/glib <span class="nv">$VIRTUAL_ENV</span>/lib/python2.7/site-packages
$ ln -s /usr/lib/python2.7/dist-packages/gi <span class="nv">$VIRTUAL_ENV</span>/lib/python2.7/site-packages
$ ln -s /usr/lib/python2.7/dist-packages/pygtkcompat <span class="nv">$VIRTUAL_ENV</span>/lib/python2.7/site-packages
$ tryton
</pre></div>
</div>
</blockquote>
<p>Obviously, GTK 2 and the respective Python bindings must be installed.</p>
</div>
<div class="section" id="python-3-x">
<h2>Python 3.x</h2>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ virtualenv -p python3 venv-tryton-4.8
$ . venv-tryton-4.8/bin/activate
$ pip install tryton~<span class="o">=</span><span class="m">4</span>.8
$ pip install PyGObject
$ tryton
</pre></div>
</div>
</blockquote>
<p>By the way: <a class="reference external" href="https://tryton.org">Tryton</a> 5.0 and later versions will only support Python 3.x and GTK
3.</p>
<p>Tested on Debian Testing with Python 2.7.15, Python 3.6.6 and <a class="reference external" href="https://tryton.org">Tryton</a> 4.4, 4.6,
4.8 and 5.0.</p>
</div>
https://nblock.org/2020/01/27/assert-in-openldap-with-password-policyAssert in OpenLDAP with password policy overlay2020-01-27T00:00:00+01:05Florian Preinstorfer<p>I got my hands on an OpenLDAP instance which started to exist sometime around
2004. The instance was upgraded several times and was quite unstable. It
crashed seemingly at random when some users logged in on an LDAP enabled
system. The only thing that popped up consistently during those crashes was
the password policy overlay (<tt class="docutils literal">ppolicy</tt>). Turning it off made the crashes
disappear. As the password policy overlay is required by the customer,
disabling it was just a temporary solution.</p>
<p>The first step was to reproduce the crash. It turned out, that enabling
password authentication in <tt class="docutils literal">OpenSSH</tt> while using <tt class="docutils literal">nslcd</tt> triggered the
assertion reliably. When a crash occurs, one can find the following line in
OpenLDAP's logs:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>slapd: ppolicy.c:912: ctrls_cleanup: Assertion `rs->sr_ctrls != NULL`
</pre></div>
</div>
</blockquote>
<p>This assertion <a class="reference external" href="https://www.openldap.org/its/index.cgi/Software%20Bugs?id=7384">was</a> <a class="reference external" href="https://www.openldap.org/its/index.cgi/Software%20Bugs?id=7966">reported</a> <a class="reference external" href="https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=670907">several</a> <a class="reference external" href="https://bugzilla.redhat.com/show_bug.cgi?id=1335194">times</a> and one of the reports was closed
with the <a class="reference external" href="https://bugzilla.redhat.com/show_bug.cgi?id=1335194#c8">comment</a>:</p>
<blockquote>
This turned out to be a configuration issue. Closing this out as NOTABUG.</blockquote>
<p>Unfortunately, the solution was not posted and it is hidden somewhere behind
RedHat's commercial support website.</p>
<p>After the usual GDB session without much success, I decided to review the
configuration of this particular instance:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ <span class="nb">cd</span> /etc/ldap/slapd.d
$ sudo grep -ri <span class="s2">"ppolicy"</span>
...
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">2</span><span class="o">}</span>ppolicy.ldif:dn: <span class="nv">olcOverlay</span><span class="o">={</span><span class="m">3</span><span class="o">}</span>ppolicy
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">2</span><span class="o">}</span>ppolicy.ldif:objectClass: olcPPolicyConfig
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">2</span><span class="o">}</span>ppolicy.ldif:olcOverlay: <span class="o">{</span><span class="m">3</span><span class="o">}</span>ppolicy
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">2</span><span class="o">}</span>ppolicy.ldif:olcPPolicyDefault: <span class="nv">cn</span><span class="o">=</span>default,ou<span class="o">=</span>...
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">2</span><span class="o">}</span>ppolicy.ldif:structuralObjectClass: olcPPolicyConfig
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">3</span><span class="o">}</span>ppolicy.ldif:dn: <span class="nv">olcOverlay</span><span class="o">={</span><span class="m">3</span><span class="o">}</span>ppolicy
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">3</span><span class="o">}</span>ppolicy.ldif:objectClass: olcPPolicyConfig
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">3</span><span class="o">}</span>ppolicy.ldif:olcOverlay: <span class="o">{</span><span class="m">3</span><span class="o">}</span>ppolicy
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">3</span><span class="o">}</span>ppolicy.ldif:olcPPolicyDefault: <span class="nv">cn</span><span class="o">=</span>default,ou<span class="o">=</span>...
<span class="nv">cn</span><span class="o">=</span>config/olcDatabase<span class="o">={</span><span class="m">1</span><span class="o">}</span>mdb/olcOverlay<span class="o">={</span><span class="m">3</span><span class="o">}</span>ppolicy.ldif:structuralObjectClass: olcPPolicyConfig
...
</pre></div>
</div>
</blockquote>
<p>As one can see, the <tt class="docutils literal">ppolicy</tt> overlay is referenced twice and the fix is
quite easy: Remove the second <tt class="docutils literal">ppolicy</tt> reference:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ sudo systemctl stop slapd
$ sudo rm cn<span class="se">\=</span>config/olcDatabase<span class="se">\=\{</span><span class="m">1</span><span class="se">\}</span>mdb/olcOverlay<span class="se">\=\{</span><span class="m">3</span><span class="se">\}</span>ppolicy.ldif
$ sudo slaptest -F /etc/ldap/slapd.d
config file testing succeeded
$ sudo systemctl start slapd
</pre></div>
</div>
</blockquote>
<p>The instance is now operating reliably.</p>
https://nblock.org/2020/01/27/temporarily-disable-journalctl-output-coloringTemporarily disable journalctl output coloring2020-01-27T00:00:00+01:05Florian Preinstorfer<p>While debugging an issue with OpenLDAP, I noticed that <tt class="docutils literal">journalctl</tt> colors
its output. From the man page:</p>
<blockquote>
When outputting to a tty, lines are colored according to priority: lines of level ERROR and higher are
colored red; lines of level NOTICE and higher are highlighted; lines of level DEBUG are colored lighter grey;
other lines are displayed normally.</blockquote>
<p>While this is nice for higher priority levels such as <tt class="docutils literal">ERROR</tt>, it is not
useful for messages with <tt class="docutils literal">DEBUG</tt> priority. Those appear in light grey which
makes them hard to read on terminal themes with a light background.</p>
<p>The easiest way is to set the environment variable <tt class="docutils literal">SYSTEMD_COLORS</tt> which
overrides automatic coloring. To follow the OpenLDAP debug log:</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ sudo <span class="nv">SYSTEMD_COLORS</span><span class="o">=</span><span class="nb">false</span> journalctl -u slapd -f
</pre></div>
</div>
</blockquote>
https://nblock.org/2020/05/16/feeds-2020.5.16Feeds 2020.5.162020-05-16T00:00:00+01:05Florian Preinstorfer<p>Almost four years after the initial commit, I'm really happy to announce the
first release of Feeds! Feeds provides Atom/RSS feeds in times of social media
and paywall. From the <a class="reference external" href="https://pyfeeds.readthedocs.io">documentation</a>:</p>
<pre class="literal-block">
Once upon a time every website offered an RSS feed to keep readers updated
about new articles/blog posts via the users’ feed readers. These times are
long gone. The once iconic orange RSS icon has been replaced by “social
share” buttons.
Feeds aims to bring back the good old reading times. It creates Atom feeds
for websites that don’t offer them (anymore). It allows you to read new
articles of your favorite websites in your feed reader (e.g. TinyTinyRSS)
even if this is not officially supported by the website.
</pre>
<p>Feeds is able to create full text Atom feeds for many different sites. Head
over to the list of <a class="reference external" href="https://pyfeeds.readthedocs.io/en/latest/spiders.html">supported websites</a> to see if your favourite site is
supported.</p>
<p>You may <a class="reference external" href="https://pyfeeds.readthedocs.io/en/latest/get.html">install Feeds</a> directly from PyPi (please note that the name on PyPi
is different):</p>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span>$ pip install PyFeeds
$ feeds crawl orf.at
</pre></div>
</div>
<p>Have a look at the <a class="reference external" href="https://pyfeeds.readthedocs.io/en/latest/quickstart.html">quickstart</a> section for more usage information.</p>
<ul class="simple">
<li>Source Code: <a class="reference external" href="https://github.com/PyFeeds/PyFeeds">https://github.com/PyFeeds/PyFeeds</a></li>
<li>PyPi: <a class="reference external" href="https://pypi.org/project/PyFeeds/">https://pypi.org/project/PyFeeds/</a></li>
<li>Documentation: <a class="reference external" href="https://pyfeeds.readthedocs.io">https://pyfeeds.readthedocs.io</a></li>
</ul>
<p>While I started the project back in 2016, it was <a class="reference external" href="https://www.notinventedhere.org/">Lukas</a> that really kept the
project going and contributed most of the code to it. Thank you and all other
<a class="reference external" href="https://github.com/PyFeeds/PyFeeds/graphs/contributors">contributors</a> very much!</p>
https://nblock.org/2021/04/09/sync-date-and-time-on-an-offline-fronius-dataloggerSync date and time on an offline Fronius Datalogger2021-04-09T00:00:00+01:05Florian Preinstorfer<div class="section" id="background-and-introduction">
<h2>Background and introduction</h2>
<p>I'm currently working on extracting metrics from multiple Fronius Symo
inverters. There are essentially two approaches to solve this problem:</p>
<ul class="simple">
<li>Connect the inverter with the Internet and upload everything to SolarWeb, a
proprietary metrics platform hosted by Fronius. Use the Fronius SolarWeb
mobile app to view the data.</li>
<li>Operate the device offline and collect the metrics yourself. Fronius offers a
JSON based <a class="reference external" href="https://www.fronius.com/en/solar-energy/installers-partners/technical-data/all-products/system-monitoring/open-interfaces/fronius-solar-api-json-">API</a> to query realtime and archive data from an inverter.
Furthermore, the device also offers a push service, where the inverter can
upload its metrics continuously to a FTP server or send it to a HTTP endpoint.
Both methods can be used without in Internet connection.</li>
</ul>
<p>As you might have guessed, I implemented the second approach where each
inverter pushes its metrics continuously to a server on the local network. From
there, it is picked up and imported into InfluxDB. Grafana is used to visualize
the metrics. Please contact me, if you are interested in how to get collection,
transfer to InfluxDB and visualization up- and running for Fronius inverters.</p>
</div>
<div class="section" id="lost-date-and-time">
<h2>Lost date and time</h2>
<p>After a few days of metrics collection, I noticed that one of the inverters
regularly loses its local date and time. Getting timeseries data with a
timestamp of 2000-01-01T05:02:15 is not very helpful.</p>
<p>The inverter's webinterface offers to set the system time and also has a
checkbox for "Set time automatically".</p>
<img alt="Configure date and time." src="/static/files/fronius-datalogger-general-settings.png" style="width: 75%;" />
<p>Great, simply enable automatic time synchronization and allow outgoing NTP
traffic (and DNS). As it turns out, the inverter does not use NTP to
synchronize its system time. It <em>requires</em> the user to enable the metrics
upload to SolarWeb in order to synchronize its system time!</p>
<p>I did not want to re-implement parts of the proprietary, UDP based protocol
(PCAP dumps are available upon request), so I decided to check the <a class="reference external" href="https://www.fronius.com/en/solar-energy/installers-partners/technical-data/all-products/system-monitoring/open-interfaces/fronius-solar-api-json-">API</a> docs
for endpoints related to date and time settings. Unfortunately, I could not
find such an endpoint.</p>
<p>The next approach was to inspect the requests of the web browser and rebuild
the necessary requests in curl. Put those requests in a script and invoke it
regularly. The steps are easy:</p>
<ul class="simple">
<li>Authenticate with the device (only HTTP Digest auth is supported)</li>
<li>Set date and time</li>
</ul>
<p>As it turns out, the HTTP Digest implementation does not conform to the
relevant <a class="reference external" href="https://tools.ietf.org/html/rfc7235">RFC 7235</a>. From section 4.1:</p>
<blockquote>
A server generating a 401 (Unauthorized) response MUST send a
WWW-Authenticate header field containing at least one challenge. A
server MAY generate a WWW-Authenticate header field in other response
messages to indicate that supplying credentials (or different
credentials) might affect the response.</blockquote>
<p>The datalogger does not respond with a <tt class="docutils literal"><span class="pre">WWW-Authenticate</span></tt> header, but instead
sends a <tt class="docutils literal"><span class="pre">X-WWW-Authenticate</span></tt> header, which breaks the authentication workflow
in curl. As there is no way to override the expected HTTP header in curl, I
decided to implement (the broken) Fronius HTTP Digest authentication myself in
Python. The workaround is easy, subclass the <tt class="docutils literal">HTTPDigestAuth</tt> class from
<a class="reference external" href="https://github.com/psf/requests/blob/v2.25.1/requests/auth.py#L108">Requests</a> and fixup the header name before Requests reads the header value.</p>
<blockquote>
<div class="literal-block-wrapper without_linenos without_caption docutils container">
<div class="hll"><pre><span></span><span class="kn">from</span> <span class="nn">requests.auth</span> <span class="kn">import</span> <span class="n">HTTPDigestAuth</span>
<span class="k">class</span> <span class="nc">HTTPDigestAuthFronius</span><span class="p">(</span><span class="n">HTTPDigestAuth</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">handle_401</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">r</span><span class="p">,</span> <span class="o">**</span><span class="n">kwargs</span><span class="p">):</span>
<span class="c1"># Replace www-authenticate unconditionally</span>
<span class="n">r</span><span class="o">.</span><span class="n">headers</span><span class="p">[</span><span class="s2">"www-authenticate"</span><span class="p">]</span> <span class="o">=</span> <span class="n">r</span><span class="o">.</span><span class="n">headers</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">"x-www-authenticate"</span><span class="p">)</span>
<span class="k">return</span> <span class="nb">super</span><span class="p">()</span><span class="o">.</span><span class="n">handle_401</span><span class="p">(</span><span class="n">r</span><span class="p">,</span> <span class="o">**</span><span class="n">kwargs</span><span class="p">)</span>
</pre></div>
</div>
</blockquote>
<p>The entire script is available <a class="reference external" href="/static/files/fronius_datalogger_set_time.py">here</a> (tested on Python 3.9 with Requests 2.25).</p>
</div>
<div class="section" id="conclusion">
<h2>Conclusion</h2>
<p>Please use open standards and established protocols for common tasks such as
NTP for keeping the system time in sync. Furthermore, don't mess with the
standards, just implement/use them as-is and test them with standard tools such
as curl.</p>
</div>
https://nblock.org/2022/07/03/direct-tailscale-connections-with-a-hp-procurve-switchDirect Tailscale connections with a HP ProCurve switch2022-07-03T00:00:00+01:05Florian Preinstorfer<p>I started experimenting with <a class="reference external" href="https://tailscale.com/">Tailscale</a> (along with the self-hosted
coordination server <a class="reference external" href="https://github.com/juanfont/headscale">Headscale</a>) and I like it pretty much. One of the
interesting properties of Tailscale is the separation of control and data
plane, where it tries to establish a <a class="reference external" href="https://tailscale.com/blog/how-nat-traversal-works/">direct</a> point-to-point <a class="reference external" href="https://www.wireguard.com/">WireGuard</a> tunnel
between peers. It gracefully falls back to <a class="reference external" href="https://tailscale.com/blog/how-tailscale-works/#encrypted-tcp-relays-derp">relay servers</a> if such a
connection is not possible. This avoids a central VPN server that needs to be
involved in every connection.</p>
<p>One can use the command <tt class="docutils literal">tailscale status</tt> to find out if a direct connection
between peers is used:</p>
<pre class="literal-block">
100.64.0.1 host1 net1 linux active; relay "lhr", tx 33036 rx 27232
100.64.0.2 host2 net2 linux active; direct 192.168.1.2:41641, tx 13892 rx 10024
</pre>
<p>The connection to <tt class="docutils literal">host1</tt> is relayed via a DERP server and the connection to
<tt class="docutils literal">host2</tt> is direct where the WireGuard tunnel uses 192.168.1.2 as the outer IP
address.</p>
<p>One day, I noticed something odd: direct connections between two peers in my
local network are only possible if one of them uses WLAN. As soon as both peers
are connected to the same switch, packets are sent via a relay. The peers are
all on the same LAN and there are no weird firewall rules that block traffic.
So it clearly should use a direct connection.</p>
<p>The solution is buried in the config of my HP ProCurve switch. It tries to be
smart about DoS protection and has the innocent looking flag <tt class="docutils literal">Auto DoS</tt> set:</p>
<img alt="HP ProCurve Advanced Security settings" src="/static/files/hp-procurve-advanced-security.png" style="width: 75%;" />
<p>A direct connection is possible as soon as the feature <tt class="docutils literal">Auto DoS</tt> is disabled.</p>