Мини заметки — выпуск 16

9 декабря 2013

Темы выпуска: автоматическая генерация функции UPSERT в PostgreSQL, почему при работе с деньгами нужно использовать рациональные числа, настройка всплывающих сообщений в Claws Mail и не только. Предыдущие выпуски мини заметок: пятнадцатый, четырнадцатый, тринадцатый, двенадцатый.

1. Русская раскладка в Microsoft Windows 2000

В честь попыток вспомнить WinAPI я тут игрался под VirtualBox c Windows 2000. Оказывается, чтобы под Win2k включить русскую раскладку, нужно зайти в Control Panel, открыть Regional Options, в списке Language settings for the system поставить галочку Cyrillic. Винда запросит установочный диск. После выполнения этих шагов в настройках клавиатуры можно будет выбрать русскую раскладку.

2. Сборка минимального exe-шника с помощью MinGW

Тут основная проблема состоит в том, что при компиляции кода на Си добавляется вызов процедуры __main и, похоже, нет флага, позволяющего отключить это поведение. В результате приходится делать как-то так:

$ export MINGW=/usr/libexec/gcc/i586-mingw32msvc/4.2.1-sjlj/
$ $MINGW/cc1 -DUNICODE test.c -o /tmp/test0.s
$ cat /tmp/test0.s | grep -v ___main > /tmp/test.s
$ /usr/i586-mingw32msvc/bin/as /tmp/test.s -o /tmp/test.o
$ $MINGW/collect2 /tmp/test.o -luser32 -lkernel32 -s -o test.exe

Транслируем код на Си в ассемблерный код, grep-ом выпиливаем вызов ___main, затем компилируем ассемблерный код и линкуем все хозяйство в исполняемый файл.

3. Как декомпилировать beam в код на Erlang?

Знаете ли вы, что если эрланговый модуль был собран с флагом debug_info, то можно полностью восстановить его исходный код за исключением разве что комментариев?

Рассмотрим такой модуль:

-module(test).

-export([test_func/1]).

% test comment
test_func(Name) ->
  "Hello, " ++ Name ++ "!".

Скомпилируем его с флагом debug_info:

erlc +debug_info test.erl

А теперь получим AST кода, используя только beam:

1> rp(beam_lib:chunks("test.beam", [abstract_code])).
{ok,{test,[{abstract_code,{raw_abstract_v1,[{attribute,1,file,{"test.erl",1}},{attribute,1,module,test},{attribute,3,export,[{test_func,1}]},{function,6,test_func,1,[{clause,6,[{var,6,'Name'}],[],[{op,7,'++',{string,7,"Hello, "},{op,7,'++',{var,7,'Name'},{string,7,"!"}}}]}]},{eof,8}]}}]}}

При желании не представляет большого труда написать программу, получающую исходный код из этого AST. Но, к счастью, за нас уже все написано:

2> {ok, {_, [{abstract_code, {_, [_|Abs]}}]}} =
2>   beam_lib:chunks("test.beam", [abstract_code]), ok.
ok
3> Src = erl_prettypr:format(erl_syntax:form_list(Abs)).
"-module(test).\n\n-export([test_func/1]).\n\ntest_func(Name) -> \"Hello, \" ++ Name ++ \"!\".\n\n"
4> file:write_file("test_src.erl", Src).
ok

Если теперь посмотреть test_src.erl, вы обнаружите, что он не сильно отличается от оригинального test.erl.

4. Простой FOR-цикл на PL/pgSQL

Если вы хотите выполнить какие-то запросы для каждого id пользователя, используйте следующую конструкцию:

DO $$ DECLARE x record; BEGIN for x in (select id from users) loop RAISE NOTICE 'id = %', x.id; end loop; END$$;

Пригодится, например, при миграции данных.

5. Как в PostgreSQL найти все столбцы определенного типа

Следующий запрос находит все столбцы во всех таблицах базы данных, имеющие тип numeric:

select table_name, string_agg(column_name, ',') as columns from information_schema.columns where data_type = 'numeric' group by table_name;

Благодаря этому приему можно делать разные интересные вещи. Например, можно проверить, не записаны ли где-нибудь в базе странные числа типа 0.0064999999:

select table_name, column_name from information_schema.columns where data_type = 'numeric' and eval('select count(*) from "' || table_name || '" where length(abs("' || column_name || '") - floor(abs("' || column_name || '")) || '''') > 10 limit 1') > 0;

Функция eval была найдена на StackOverflow:

create or replace function eval(expression text) returns integer
as
$body$
declare
  result integer;
begin
  execute expression into result;
  return result;
end;
$body$
language plpgsql

Как нетрудно догадаться, она выполняет запрос, переданный первым аргументом в виде строки.

6. Как подрубить PL/Perl к PostgreSQL

Как отмечалось в заметке Некоторые интересные отличия PostgreSQL от MySQL, PostgreSQL позволяет писать тригеры и хранимые процедуры на языках Perl, Python, Tcl и других. В ряде случаев это оказывается весьма полезно.

Для установки PL/Perl на сервере, где крутиться PostgreSQL, говорим:

sudo apt-get install postgresql-plperl-9.3
sudo -u postgres psql

Включаем PL/Perl для требуемой базы данных:

postgres=# \c my_database;
my_database=# CREATE EXTENSION plperl;
CREATE EXTENSION
my_database=# \q

Проверяем, что все работает:

create or replace function my_inc(x decimal) returns decimal as $$
  my ($x) = @_;
  return $x + 1;
$$ language plperl;

select my_inc(1.23);
 my_inc
--------
   2.23
(1 row)

А вот пример функции, округляющей числа типа 12.349999999 и -5.43200000001 до 12.35 и -5.432 соответственно:

create or replace function my_round(x decimal) returns decimal as $$
  use strict;
  my ($x) = @_;
  my ($l, $r) = split /\./, $x;
  my $i = index $r, "00000";
  if($i >= 0) {
    $r = substr $r, 0, $i;
    $r = "0" unless $r;
  } else {
    $i = index $r, "99999";
    if($i >= 0) {
      $r = substr $r, 0, $i;
      $r++;
    }
  }
  return "$l.$r";
$$ language plperl;

Попробуйте написать такое на PL/pgSQL!

7. Как в PostgreSQL сгенерировать функцию MERGE/UPSERT

Дополнение: В более новых версиях PostgreSQL есть встроенный UPSERT. Пример его использования можно найти в посте Пример использования триггеров в PostgreSQL.

Теперь, владея информацией из пунктов 5 и 6, не представляет труда написать функцию, генерирующую код UPSERT-функции для таблицы с заданным именем:

create or replace function gen_merge_fun(x varchar) returns varchar as
$$
  use strict;
  my ($tbl) = @_;
  my $fun_name = "merge_$tbl";
 
  my $pk_rv = spi_exec_query(qq{
    SELECT c.column_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.constraint_column_usage AS ccu
       USING (constraint_schema, constraint_name)
    JOIN information_schema.columns AS c
    ON c.table_schema = tc.constraint_schema AND
       tc.table_name = c.table_name AND
       ccu.column_name = c.column_name
    WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name = '$tbl'
    ORDER BY c.ordinal_position;
  });
  my @pk_arr;
  push @pk_arr, $pk_rv->{rows}[$_]->{column_name}
    for 0..$pk_rv->{processed} - 1;
 
  my $info_rv = spi_exec_query(qq{
    select column_name, data_type
    from information_schema.columns
    where table_name = '$tbl'
    order by ordinal_position;
  });
 
  my @columns;
  my @args;
  for (0..$info_rv->{processed} - 1) {
    my $col = $info_rv->{rows}[$_]->{column_name};
    my $type = $info_rv->{rows}[$_]->{data_type};
    push @columns, $col;
    push @args, qq{"$col" $type};
  }
 
  my $sep = "\n" . (" " x 15);
  my $arguments = join ",$sep", @args;
  my $insert_fields = join ",$sep", (map { qq{"$_"} } @columns);
  my $insert_values = join ",$sep", (map { qq{$fun_name."$_"} }
                                         @columns);
 
  my %pk_filt;
  @pk_filt{@pk_arr} = 1;
  my @upd_tmp;
  for my $c (grep { not exists $pk_filt{$_} } @columns) {
    push @upd_tmp, qq{"$c" = $fun_name."$c"};
  }
  my $update_set = join ",$sep", @upd_tmp;
 
  my @updw_tmp;
  for my $c (@pk_arr) {
    push @updw_tmp, qq{$tbl."$c" = $fun_name."$c"};
  }
  my $update_where = join " AND$sep", @updw_tmp;

  return qq{
CREATE OR REPLACE FUNCTION $fun_name($sep$arguments) RETURNS VOID AS
\$\$
BEGIN
    LOOP
        UPDATE $tbl SET $sep$update_set
        WHERE $sep$update_where;
        IF found THEN
            RETURN;
        END IF;
        BEGIN
            INSERT INTO $tbl($sep$insert_fields
            ) VALUES ($sep$insert_values);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
          -- do nothing
        END;
    END LOOP;
END;
\$\$
LANGUAGE plpgsql;
  };
$$ language plperl;

Чтобы не копипастить генерируемый код в скрипты миграции, можно использовать такую конструкцию:

DO $$ BEGIN EXECUTE gen_merge_fun('users'); END$$;

Ну или если вы зомбированы Хабрахабром и испытываете беспричинный страх перед PL/Perl в продакшене, можно получить код UPSERT-функции в тестовом окружении как-то так:

echo "select gen_merge_fun('users');" | \
  psql -h postgresql.test.example.com mydb myuser -A -t

… а потом копипастить его куда вам нужно.

8. О настройке всплывающих сообщений в Skype и Claws Mail

В Ubuntu для вывода стандартных всплывающих сообщений есть замечательная утилита netify-send:

notify-send test
notify-send --help

В настройках Skype можно отключить стандартные всплывающие сообщения с мелким текстом и использовать вместо них notify-send. Подробности о доступных переменных (имя контакта, сообщение, размер передаваемого файла) можно найти, например, здесь.

Этот же прием работает и с Claws Mail. Идем в Configuration → Preferences → Receiving → Run command и прописываем там что-то вроде:

notify-send "Claws Mail" "У вас %d новых писем" -i claws-mail

А еще для меня недавно стало открытием, что в Skype можно включать и отключать уведомления о новых сообщениях в чате при помощи команд /alertson и /alertsoff.

9. Как отличить 32-х битный Erlang от 64-х битного

В REPL говорим:

erlang:system_info({wordsize, external}).

Если Erlang 64-х битный, увидим 8, иначе — 4.

10. Почему при работе с деньгами нужно использовать rational

В мире Erlang для представления чисел с произвольной точностью часто рекомендуется использовать библиотеку decimal. Эта библиотека эксплуатирует возможность Erlang’а работать с произвольно большими целыми числами, представляя дробные числа в виде кортежа {S, M, E}, где S представляет собой знак числа, M — мантиссу, а E — порядок. Например, числа 1.23 и -45000 хранятся в виде кортежей {0, 123, -2} и {1, 45, 3} соответственно. Проблема этого представления заключается в том, что с его помощью нельзя с произвольной точностью представить такие числа, как 1/3, 100/85 и другие. Как правило, это не представляет собой проблемы, так как при разработке системы оговаривается, что некие расчеты производятся с точностью, например, до 100 знаков после запятой. Однако следует быть готовым к тому, что, например, 1/3 * 3 не будет равно в точности 1:

1> decimal_conv:string(decimal:multiply(decimal:divide(1, 3), 3)).
"0.999999999"

Если в своих вычислениях вы используете только операции сложения, вычитания, умножения, деления и, может быть, возведения в степень, возможно, следует воспользоваться библиотекой для работы с рациональными числами. При этих условиях ваши расчеты будут производится действительно с произвольной точностью, то есть 1/3 * 3 будет равно в точности 1. В мире Haskell для работы с рациональными числами есть модуль Data.Ratio, а реализацию для Erlang можно найти в библиотеке erlang-tools.

Дополнение: Мини заметки — выпуск 17, полностью посвященный Haskell

Метки: .


Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.