この記事はPostgreSQL Advent Calendar 2024の22日目の記事です。

はじめに

歳をとると1年があっという間です。
もうAdvent Calendarの時期になってしまいました。毎年クダラナイネタを考えてきましたが、今年はネタが不作であったため初心に戻りPostgreSQLのユーザ定義型を整理していこうと思います。

方針検討

「ユーザ定義型」というテーマは決まったので、早速どんな型を定義しようか考えました。
バイナリの入出力を追加したりもできますが、あまり複雑なことはやりたくなかったので、テキスト入出力のみのシンプルな型を検討しました。

流石にマニュアルに載ってる(src/tutorialにもあるらしい)、Complex型を作るだけだと芸がないので、（・・・中略・・・）モールス信号の型を定義 することとします！

モールス信号

モールス信号とは、下記の通り「モールス符号を使った信号」のこと。

モールス符号
モールス符号（モールスふごう、英語: Morse code）は、電信などで用いられている可変長符号化された文字コード。モールス符号を使った信号はモールス信号と呼ばれる。
短点（・）と長点（－）を組み合わせて文字を表現する。表現する文字種の違いにより、複数の規格がある。
WikiPediaより

有名なものだと「・・・ーーー・・・」で「SOS」となるということはご存知な方も多いのではないでしょうか？

フュージョン！

PostgreSQLの世界にモールス信号を以下のように召喚します。
「信号文字列(今回は".", "-"及び" "の3種)を入力すると、翻訳したテキスト文字列を追加して出力する。ついでに音も鳴らす。」

翻訳する際には、前述のWikipediaに記載されている和文モールス符号を事前登録しておき、SPI(Serverside Programming Interface)でアクセスして変換します。

サウンド機構

音を鳴らすのには、WiringPi を利用します。
ラズパイの圧電スピーカーをC言語から制御できるようになります。（今回もラズパイ使います）

いざ、実装！

構想ねれたので、モリモリ作っていきます！

まずは配線

圧電スピーカーを取り出して、、、

配線して、、、

完了！

続いてプログラム

フォルダ構成は以下のようにしてます。

フォルダ構成

pg_morse
├── Makefile
├── pg_morse--1.0.sql
├── pg_morse.control
├── pg_morse.h
└── pg_morse.c

それぞれのポイント説明していきます。

全プログラムを以下袋とじに記載してます。

ーーー８＜ーーー

Makefile

#MODULES = pg_morse
MODULE_big = pg_morse
OBJS = pg_morse.o
EXTENSION = pg_morse
DATA = pg_morse--1.0.sql
#PG_LDFLAGS = -lwiringPi
SHLIB_LINK = -lwiringPi

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

pg_morse--1.0.sql

/* pg_morse--1.0.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_morse" to load this file. \quit

CREATE TYPE pg_morse;

CREATE FUNCTION morse_in(cstring)
RETURNS pg_morse
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;

CREATE FUNCTION morse_out(pg_morse)
RETURNS cstring
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;

CREATE TYPE pg_morse (
        INPUT = morse_in,
        OUTPUT = morse_out,
        STORAGE = plain
);

CREATE SCHEMA morse;
CREATE TABLE morse.pg_morse(
	signal		TEXT,
	translated	TEXT
);
INSERT INTO morse.pg_morse VALUES
('.-', 'イ'), ('.-.-', 'ロ'), ('-...', 'ハ'), ('-.-.', 'ニ'), ('-..', 'ホ'), ('.', 'ヘ'), ('..-..', 'ト'), ('..-.', 'チ'), ('--.', 'リ'), ('....', 'ヌ'), ('-.--.', 'ル'), ('.---', 'ヲ'), ('-.-', 'ワ'), ('.-..', 'カ'), ('--', 'ヨ'), ('-.', 'タ'), ('---', 'レ'), ('---.', 'ソ'), ('.--.', 'ツ'), ('--.-', 'ネ'), ('.-.', 'ナ'), ('...', 'ラ'), ('-', 'ム'), ('..-', 'ウ'), ('.-..-', 'ヰ'), ('..--', 'ノ'), ('.-...', 'オ'), ('...-', 'ク'), ('.--', 'ヤ'), ('-..-', 'マ'), ('-.--', 'ケ'), ('--..', 'フ'), ('----', 'コ'), ('-.---', 'エ'), ('.-.--', 'テ'), ('--.--', 'ア'), ('-.-.-', 'サ'), ('-.-..', 'キ'), ('-..--', 'ユ'), ('-...-', 'メ'), ('..-.-', 'ミ'), ('--.-.', 'シ'), ('.--..', 'ヱ'), ('--..-', 'ヒ'), ('-..-.', 'モ'), ('.---.', 'セ'), ('---.-', 'ス'), ('.-.-.', 'ン'), ('..', '゛'), ('..--.', '゜'), ('.--.-', 'ー'), ('.----', '１'), ('..---', '２'), ('...--', '３'), ('....-', '４'), ('.....', '５'), ('-....', '６'), ('--...', '７'), ('---..', '８'), ('----.', '９'), ('-----', '０')
;

pg_morse.control

comment = 'pg_morse is a data-type for communication with morse signal.'
default_version = '1.0'
module_pathname = '$libdir/pg_morse'

pg_morse.h

#include <string.h>

#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "utils/builtins.h"

#include <wiringPi.h>
#include <softTone.h>

/* Version-1 protocol */
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

typedef struct ST_Morse {
	int size;
	char *st;
} Morse;

int morse_count(const char *str, char delim);
int morse_split(char *split[], const char *str, char delim);
void morse_beep(const char *str);
char *morse_translate(char *str);


#define C 1307
#define M 0
#define x1 100
#define x2 300
#define x3 200
#define x4 50
#define x5 1000
const int SPK = 25;

pg_morse.c

/*-------------------------------------------------------------------------
 *
 * pg_morse.c
 *
 *-------------------------------------------------------------------------
 */
#include "pg_morse.h"

int morse_count(const char *str, char delim)
{
	int n = 0;
	for(int i = 0; str[i] != '\0'; i++)
	{
		if(str[i] == delim) n++;
	}
	n++;
	return n;
}

int morse_split(char *split[], const char *str, char delim)
{
	int n = 0;
	int j = 0;
	for(int i = 0; str[i] != '\0'; i++)
	{
		if(str[i] == delim)
		{
			split[n++][j] = '\0';
			j = 0;
		}
		else
			split[n][j++] = str[i];
	}
	split[n++][j] = '\0';
	return n;
}

char *
morse_translate(char *str)
{
	int word_num = morse_count(str, ' ');
	char split[256][256];
	char *p_split[256];
	char buf[256];
	char *cmd = "SELECT translated FROM morse.pg_morse WHERE signal = $1";
	int ret;
	Oid oid[1];
	Datum dat[1];
	char *result = NULL;

	for(int i = 0; i < word_num; i++) p_split[i] = split[i];
	word_num = morse_split(p_split, str, ' ');
	oid[0] = (Oid)25;
	SPI_connect();
	result = (char*)palloc(strlen(str)*4);
	result[0] = '\0';
	for(int i = 0; i < word_num; i++)
	{
		dat[0] = CStringGetTextDatum(split[i]);
		ret = SPI_execute_with_args(cmd, 1, oid, dat, NULL, true, 1);
		if (ret > 0 && SPI_tuptable != NULL)
		{
			SPITupleTable *tuptable = SPI_tuptable;
			TupleDesc tupdesc = tuptable->tupdesc;
			HeapTuple tuple = tuptable->vals[0];
			if(tuple == NULL) elog(ERROR, "[NO SIGNAL]: oh my god...");
			sprintf(buf, "%s", SPI_getvalue(tuple, tupdesc, 1));
			sprintf(result, "%s%s", result, buf);
		}
		else	elog(ERROR, "[no signal]: oh my god...");
	}
	SPI_finish();
	return result;
}

void
morse_beep(const char *str){
	if(wiringPiSetupGpio() < 0) elog(ERROR, "gpio error...");
	if(softToneCreate(SPK) != 0) elog(ERROR, "softToken create error...");

	for(int i = 0; str[i] != '\0'; i++){
		if(str[i] == '.'){
			softToneWrite(SPK, C);
			delay(x1);
		}
		else if(str[i] == '-'){
			softToneWrite(SPK, C);
			delay(x2);
		}
		else if(str[i] == ' '){
			softToneWrite(SPK, M);
			delay(x3);
		}
		softToneWrite(SPK, M);
		delay(x4);
	}
	softToneWrite(SPK, M);
	delay(x5);
}

/*
 * morse_in
 */
PG_FUNCTION_INFO_V1(morse_in);
Datum
morse_in(PG_FUNCTION_ARGS)
{
	char *s = PG_GETARG_CSTRING(0);
	char *t = morse_translate(s);
	Morse *result;

	char *st;
	result = palloc(2 + strlen(s) + strlen(t)+1 + VARHDRSZ);
	SET_VARSIZE(result, 2 + strlen(s)+strlen(t)+1 + VARHDRSZ);
	st = VARDATA(result);
	memcpy(st, s, strlen(s));
	st[strlen(s)] = '(';
	memcpy(&st[strlen(s)+1], t, strlen(t));
	st[strlen(s)+1+strlen(t)] = ')';
	st[strlen(s)+1+strlen(t)+1] = '\0';
	morse_beep(s);
	PG_RETURN_POINTER(result);
}

/*
 * morse_out
 */
PG_FUNCTION_INFO_V1(morse_out);
Datum
morse_out(PG_FUNCTION_ARGS)
{
	Morse *morse = (Morse*)PG_GETARG_POINTER(0);
	char    *result;

	result = psprintf("%s", VARDATA(morse));
	PG_RETURN_CSTRING(result);
}

Makefile

拡張構築基盤を参考に、PGXS対応したMakefile作ります。
今回は外部ライブラリ(WiringPi)を読み込む必要があったのですが、最初やり方分からずハマりました。
PG_LIBSで定義すればよいのかと思ってたけどそれじゃダメで、MODULESをMODULE_bigとする＆ SHLIB_LINKでWiringPiを定義するでイケました。

pg_morse--1.0.sql

pg_morse型の定義と翻訳用データ登録を行ってます。
翻訳用データは、morseスキーマ内にpg_morseテーブルを作成して登録してます。

CREATE SCHEMA morse;
CREATE TABLE morse.pg_morse(
	signal		TEXT,
	translated	TEXT
);
INSERT INTO morse.pg_morse VALUES
......

スキーマ名も「pg_morse」にしたかったけど、「pg_」で始まるものはダメ！って怒られちゃいました。（接頭文字列として「pg_」は予約されてるんですね、知らんかった・・・orz)

pg_morse.control

特筆することなし！(　･`ω･´)ｷﾘｯ

pg_morse.h

ヘッダファイル読み込みやpg_morse.cで使う構造体・関数定義とWiringPiで使う定数を定義してます。

pg_morse型の内部構造は次の構造体で定義してます。

typedef struct ST_Morse {
	int size;
	char *st;
} Morse;

マニュアルに記載されている Complex型と異なり可変長データを扱うため、sizeに全体の長さ、stにデータを格納してます。

マニュアル見ても可変長データの場合についての記載は↓くらいしか見当たらず、具体的にどうすればよいのかよく分からなかったけど、動くから大体あってると思う。

If the internal representation of the data type is variable-length, the internal representation must follow the standard layout for variable-length data: the first four bytes must be a char[4] field which is never accessed directly (customarily named vl_len_). You must use the SET_VARSIZE() macro to store the total size of the datum (including the length field itself) in this field and VARSIZE() to retrieve it. (These macros exist because the length field may be encoded depending on platform.)

WiringPiで使ってる定数は次の通り。
C 及び M が音の高さ。M の 0 は無音。C の 1307 は、それっぽい感じの音になるまで試行錯誤して決めた。
x1〜5 は音の長さ。それっぽい感じの音の長さになるまで試行錯誤して決めた。
SPK の 25 がスピーカー配線したBCM番号。

#define C 1307
#define M 0
#define x1 100
#define x2 300
#define x3 200
#define x4 50
#define x5 1000
const int SPK = 25;

pg_morse.c

入出力関数・内部関数あわせて6個の関数を実装してます。

関数名	処理内容	備考
morse_in	入力を外部表現形式から内部表現形式に変換	入力は'.- .-.-'といった文字列
morse_translate	モールス信号を翻訳
morse_count	何文字のモールス信号かカウント
morse_split	モールス信号を信号単位で分割
morse_beep	WiringPiで圧電スピーカーから音を出す
morse_out	内部表現形式を文字列にして出力	出力は'.- .-.-(イロ)'といった文字列

前述の通り可変長データの扱い方がわからなかったですが、入出力関数でVARHDRSZとかSET_VARSIZEとかVARDATA使ったらなんとか動きました！

morse_in

	char *st;
	result = palloc(2 + strlen(s) + strlen(t)+1 + VARHDRSZ);
	SET_VARSIZE(result, 2 + strlen(s)+strlen(t)+1 + VARHDRSZ);
	st = VARDATA(result);
	memcpy(st, s, strlen(s));
	st[strlen(s)] = '(';
	memcpy(&st[strlen(s)+1], t, strlen(t));
	st[strlen(s)+1+strlen(t)] = ')';
	st[strlen(s)+1+strlen(t)+1] = '\0';

morse_out

	char    *result;

	result = psprintf("%s", VARDATA(morse));

構成イメージ

構成図と処理フローは次の通りです。

いざ、動確！

ちゃんと動くか確かめてみます。

ビルド

make & make install するだけ。

$ ls
Makefile  pg_morse--1.0.sql  pg_morse.c  pg_morse.control  pg_morse.h

$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/home/pi/work/PostgreSQL/local/pg1700/include/postgresql/server -I/home/pi/work/PostgreSQL/local/pg1700/include/postgresql/internal  -D_GNU_SOURCE   -c -o pg_morse.o pg_morse.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o pg_morse.so pg_morse.o -L/home/pi/work/PostgreSQL/local/pg1700/lib    -Wl,--as-needed -Wl,-rpath,'/home/pi/work/PostgreSQL/local/pg1700/lib',--enable-new-dtags  -fvisibility=hidden -lwiringPi 

$ make install
/bin/mkdir -p '/home/pi/work/PostgreSQL/local/pg1700/lib/postgresql'
/bin/mkdir -p '/home/pi/work/PostgreSQL/local/pg1700/share/postgresql/extension'
/bin/mkdir -p '/home/pi/work/PostgreSQL/local/pg1700/share/postgresql/extension'
/usr/bin/install -c -m 755  pg_morse.so '/home/pi/work/PostgreSQL/local/pg1700/lib/postgresql/pg_morse.so'
/usr/bin/install -c -m 644 .//pg_morse.control '/home/pi/work/PostgreSQL/local/pg1700/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//pg_morse--1.0.sql  '/home/pi/work/PostgreSQL/local/pg1700/share/postgresql/extension/'
$

OK.
滞りなくビルドできました。

登録

適当なDB作って、CREATE EXTENSION pg_morse;するだけで登録完了です。

$ psql testdb
psql (17.0)
Type "help" for help.

testdb=# CREATE EXTENSION pg_morse;
CREATE EXTENSION
testdb=# TABLE morse.pg_morse ;
 signal | translated 
--------+------------
 .-     | イ
 .-.-   | ロ
 -...   | ハ
 -.-.   | ニ
   （中略）
  ---..  | ８
 ----.  | ９
 -----  | ０
(61 rows)

OK.
ちゃんとmorseスキーマに翻訳用のテーブルも登録されました。

ハローワールド

適当なテーブルの列にpg_morse型を定義して利用します。

testdb=# CREATE TABLE tbl (i int, m pg_morse);
CREATE TABLE
testdb=# INSERT INTO tbl VALUES (1, '-... .-.- .--.- -.- .--.- -.--. ..-.. ..');
INSERT 0 1
testdb=# TABLE tbl;
 i |                             m                              
---+------------------------------------------------------------
 1 | -... .-.- .--.- -.- .--.- -.--. ..-.. ..(ハローワールト゛)
(1 row)

testdb=#

OK.
-... .-.- .--.- -.- .--.- -.--. ..-.. .. という文字列をINSERTすると、ちゃんと音が鳴りました！（どのような音かは「おまけ」参照）
テーブルのデータを取得してみると、-... .-.- .--.- -.- .--.- -.--. ..-.. ..(ハローワールト゛)ときちんと翻訳もされてます！！

現場からは以上です。

まとめ

久しぶりにユーザ定義型作ってみました。知らなかったことも多々あり、色々と勉強になったYO！
これからも楽しくPostgreSQLライフを送りたいと思います！

おまけ

モールス信号音の動画です。
何となくポエムを吟じてみたくなりましたので、モールス信号でお届けします。
（お母さんの声は入らなかったけど、飛行機の音が入ってしまったorz）

ポエム

---. .. ..- -.-.- .-.-. -.-.
.- .-.- .- .-.- .-.
.- -. ---.- .. ... ---.- -.--. --

-... --.-. .. -...- .-.-- ..--
--- - ---.- .- ..-.- .-.-. -...
...- .. -. .. ...- .. -. ..
---.- -.-.. .. .-.--
-.. ..--. .-.-. -.. ..--. .-.-. . ..--. .- .-.-.

.-.. --- .--.- ..--
-.--. .--.- -.-. .- -..-. -...
.- ... .-. .-
.-.-- .-.-. -.-.- .- .-.. ..
.--. ...- --. -. .. --.-. -.
--.-. ---.- .-.-- -
-.--. .--.- -.--. - -- ..- ..--
-.--- .-.-. -. .--.- .-.-- .-. .--.-