Мини заметки – выпуск 16
Темы выпуска: автоматическая генерация функции 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