02 November 2015

PostgreSQL function for parsing JSON into Sphinx keywords

This post describes how to write a basic PostgreSQL function for parsing a JSON into Sphinx keywords(space-separated values of the JSON object).


  • Perl core
  • Perl JSON module
  • PostgreSQL built with JSON support

Sample Problem

We have workers table:

  id integer,
  props text
where props keeps arbitrary worker properties in JSON format.

Let's insert a worker:

INSERT INTO workers VALUES(1, '{"x":"x test", "d":104, "o":{"o1":"o1 name"}, "a":["a1", "a2"]}');

We want to make the worker properties searchable through Sphinx. So we add corresponding PostgreSQL source and an index in our sphinx.conf:

source src_workers: pgsql
  sql_query_pre = set work_mem = '100MB'
  sql_query = select id, props from workers
  sql_field_string = props

index idx_workers
  source = src_testing
  path = /home/ruslan/sphinxsearch/idx_workers
  min_prefix_len = 3
  docinfo = extern

With the current sql_query the props field will be cluttered with JSON keys and specific characters. We'll write a PostgreSQL function which will give us a single line of space-separated values:

$ createlang plperlu t # < "t" is a database name
$ psql -d t
create or replace function json2sphkw(text,text) returns text as $$
use JSON qw( decode_json );

my ($in) = @_;
$_SHARED{read_sub} = sub {
  my ($in) = @_[0];

  if (ref($in) eq "HASH") {
    my @res, $read_sub = $_SHARED{read_sub};
    foreach $v (values %$in) {
      push @res, &$read_sub($v);
    return join(' ', @res);
  } elsif (ref($in) eq "ARRAY") {
    my @res, $read_sub = $_SHARED{read_sub};
    foreach $v (values @$in) { push @res, &$read_sub($v); }
    return join(' ', @res);
  return $in;

my $h = decode_json $in;
my $read_sub = $_SHARED{read_sub};

return &$read_sub($h);
$$ LANGUAGE plperlu;

Now we can use it:

SELECT json2sphkw(props, ' ') FROM workers WHERE id = 1;
 o1 name a1 a2 x test 104
(1 row)
And the sphinx.conf can be changed as follows:
@@ -1,7 +1,7 @@
 source src_workers: pgsql
   sql_query_pre = set work_mem = '100MB'
-  sql_query = select id, props from workers
+  sql_query = select id, json2sphkw(props, ' ') from workers
   sql_field_string = props


18 December 2014

Randomly changing the desktop wallpaper via crontab

Here is a simple way to organize a random desktop wallpaper by means of crontab.

We'll use feh application here. Let's put the following code into ~/bin/wallpaper-change file:

#!/bin/bash -
# Pick and set a random wallpaper

: ${DISPLAY:=":0"}

if [ $# -lt 1 ]; then
  echo >&2 "No source directory specified"
  exit 1

if [[ ! -d "$dir" ]]; then
  echo >&2 "Directory $dir doesn't exist"
  exit 1

find $dir/ -maxdepth 1 -type f | sort -R | tail -1 | while read f
  feh --bg-fill "$f" &
(Don't forget to make it executable.)

Now we can adjust the crontab, e.g.:

@hourly ~/bin/wallpaper-change ~/Pictures/wallpapers

Note, setting the DISPLAY environment variable is crucial.

26 November 2014

Changing keyboard layout in system console

The post is about configuring a Unicode keyboard layout for the system console on Gentoo. However, the steps should be similar on other systems.

Enable Unicode in /etc/rc.conf:


Install a font for UTF-8 consoles:

# emerge -av media-fonts/terminus-font

Change /etc/conf.d/consolefont:

# consolefont="ter-v16b"
Make sure that /etc/init.d/consolefont is enabled:
# rc-update add consolefont boot

Set default console keymap in /etc/conf.d/keymaps:


Add /etc/init.d/keymaps to the boot run level:
# rc-update add keymaps boot

Configure the shell to send switch-to-unicode escape sequence at each login. For Bash add the following to your ~/.bash_profile:

if test -t 1 -a -t 2 ; then
        echo -n -e '\033%G'

To make the switch to Unicode global for all users the above-mentioned snippet should be added to /etc/profile.

Reboot the system. Now Unicode should be available on all system console logins.

To load different keyboard layout use the loadkeys command (which is shipped with sys-apps/kbd package):

# loadkeys /usr/share/keymaps/{YOUR_ARCH}/{PATH_TO_KEYMAP}
For instance, to load Russian Qwerty layout for i386 (will work on amd64):
# loadkeys /usr/share/keymaps/i386/qwerty/ru.map.gz

One might want to make a shortcut for this. Use Ctrl-Shift shortcut for switching between the keymaps.

28 June 2014

Gentoo configure error: C compiler cannot create executables

If configure phase failes with this message, then most likely the dev-libs/mpc package is removed. I removed it accidentally. Afterwards I was unable to emerge anything. Here is a simple solution for the issue.

Download stage3 tar ball from a Gentoo mirror, unpack it and copy usr/lib/libmpc.so* over /usr/lib. Then run ldconfig and try to build something.

Hopefully, the post will help another gentoer like me who will also wonder what's going wrong with the C compiler.

21 June 2014

How to capture audio stream using Pulseaudio and VLC

Some Internet radio stations offer URLs for streaming with popular software, some don't. I'll show a way to capture currently runnig audio stream to file using Pulseaudio and VLC.

Step 1. Pick an output

$ pactl list short | grep RUNNING | awk '{print $2}'
(The list of devices also be obtained via VLC GUI: View - Playlist - Audio capture.) Let's pick the second one.

Step 2. Write a Bash script

In the following script replace the value of pulseaudio_stream variable with 'pulse://YOUR_OUTPUT', where YOUR_OUTPUT is the output chosen in the first step.

#!/bin/bash -
set -o nounset


usage() {
  IFS= read -r errstr <<-errstr
  Captures current pulseaudio stream $pulseaudio_stream and saves it to a file
  Usage: $0 <output-path>
  Example: $0 ~/Music/radio.mp3

  echo >&2 "$errstr"
  exit 1

if [[ $# < 1 ]]; then


nvlc "$pulseaudio_stream" ':sout=#transcode{vcodec=none,acodec=mp3,ab=128,channels=2,samplerate=44100}:duplicate{dst=std{access=file,mux=raw,dst='$output'}}'

Save it to ~/scripts/bash/rec-audio.sh and make it executable.

Step 3. Use it

~/scripts/bash/rec-audio.sh ~/Music/radio.mp3

Currently running audio will be recorded to the file until you quit VLC.

31 March 2014

Slim and Systemd

I just want to drop in a warning from Arch linux documentation:
Warning: Currently SLiM is not fully compatible with Systemd, resulting in
various problems on a second login. See Display manager#Incompatibility with
systemd for an outline of these problems.
I had really experienced such kind of issues after switching from OpenRC to Systemd! Text artifacts in GTK-based applications, SSH keyring issues, system stall on reboot or shutdown is not full list of bugs I got with Slim running under Systemd. All of a sudden I see the Arch docs. Replacing Slim with LightDM fixed all of these issues at a time!

22 March 2014

An alternative to gksudo/kdesudo

The gksudo and kdesudo commands are okay, if we whether work in GNOME or KDE environments, or are satisfied with the number of required dependencies. However, these pacakges are pretty cumbersome, if we want just a lightweight GUI version of sudo, especially on non-GTK/non-KDE environments like Xfce. In this post I'll show a simple GUI front-end I use on my Xfce setup. Steps:
  • Install x11-ssh-askpass or similar.
  • Create ~/bin/xsudo executable with the following contents:
    #!/bin/bash -
    sudo -A $@
  • Set the $SUDO_ASKPASS environment variable in some initialization script (e.g. ~/.xinitrc, ~/.bash_profile, ~/.bashrc etc.):
    export SUDO_ASKPASS="/usr/bin/x11-ssh-askpass"
    Alternatively, put the following into /etc/sudo.conf file:
    Path askpass /usr/bin/x11-ssh-askpass
    (Make sure to adjust the path.)
Now we can use xsudo command just as if it were gksudo or kdesudo.