GoでSQL発行時に渡す変数を可変にする

Web画面に複数のチェックボックスが表示されていて、ユーザが自由に任意の数を選択してsubmitされたりした場合、IN句が可変になったりする場合がある。

どうやって可変長の変数を渡すのだろう?というのが気になった。

で、試行錯誤の末、以下のようにしたら動いた。

まずこんな感じのdocker-compose.yamlを用意してdocker-composeでPostgreSQLを起動しておいて、

services:
  db:
    image: postgres:14
    container_name: postgresql
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_INITDB_ARGS: "--no-locale"
    volumes:
      - ./pgdata:/var/lib/postgresql/data
      - ./pginit:/docker-entrypoint-initdb.d

以下のようなコードにしたらうまくいった。ポイントは、any型の配列に渡したい変数を格納するのと、関数呼び出し時にその変数を渡しつつ、変数の後に...と書く、というところ。

package main

import (
    "database/sql"
    "log"

    _ "github.com/lib/pq"
)

func main() {
    conn, err := sql.Open("postgres", "host=127.0.0.1 port=5432 dbname=postgres user=postgres password=postgres sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close()

    _, err = conn.Exec(`DROP TABLE IF EXISTS a;`)
    if err != nil {
        log.Println(err)
        return
    }
    _, err = conn.Exec(`CREATE TABLE a(id integer, value text)`)
    if err != nil {
        log.Fatal(err)
    }
    _, err = conn.Exec(`INSERT INTO a VALUES(1, 'あ')`)
    if err != nil {
        log.Fatal(err)
    }
    _, err = conn.Exec(`INSERT INTO a VALUES(2, 'い')`)
    if err != nil {
        log.Fatal(err)
    }
    _, err = conn.Exec(`INSERT INTO a VALUES(3, 'う')`)
    if err != nil {
        log.Fatal(err)
    }
    // any型の配列にする
    bind := []any{"あ", "い"}
    // 最後に...をつける
    rows, err := conn.Query(`SELECT id, value FROM a WHERE value IN ($1, $2)`, bind...)
    if err != nil {
        log.Fatal(err)
    }
    for rows.Next() {
        var id int
        var value string
        err = rows.Scan(&id, &value)
        if err != nil {
            log.Fatal(err)
        }
        log.Println(id, value)
    }
}

SQLのEXISTS

WHERE句にEXISTSと書いてそれにつづけてサブクエリをかく。 各行について条件に一致するものがあるとEXISTSが真になるからその行が抽出される、みたいな感じ。

drop table if exists A;
drop table if exists B;

create table A (id integer, name text);
create table B (id integer, title text);

insert into A values(1, '佐藤');
insert into A values(2, '鈴木');
insert into A values(3, '菊池');

insert into B values(1, '会社員');
insert into B values(1, '投資家');
insert into B values(2, '会社員');
insert into B values(2, '主夫');
insert into B values(3, '投資家');
insert into B values(3, '主夫');

select id, name from A
where exists (
    select 1 from B
    where A.id = B.id and B.title = '会社員');

elasticsearchを使って全文検索してみたい

elasticsearchは検索エンジンの一種。類似するものにApache Solrなどがある。

検索エンジンでは、文書登録時に単語を抽出(日本語の場合は形態素解析という技術を使用)し、各単語からその単語が含まれているドキュメントを示すIDをひけるようにする索引である「転置インデックス」というものを用いて検索キーワードから文書を高速に見つけられるようにする。

対象ドキュメントから検索キーワードになりうる単語を抽出して転置インデックスを作成する機能をインデクサと呼び、その転置インデックスを使って検索する機能を提供するものをサーチャーと呼ぶ。

Dockerfileを準備、日本語対応のプラグインを追加。これをesというディレクトリの中に置いてみた。

FROM elasticsearch:8.3.3
RUN bin/elasticsearch-plugin install analysis-kuromoji

ちなみにファイルを別途ダウンロードしておいて、そのファイルをインストールするという方法もある。

FROM elasticsearch:8.3.3
RUN curl -O https://artifacts.elastic.co/downloads/elasticsearch-plugins/analysis-kuromoji/analysis-kuromoji-8.3.3.zip
RUN bin/elasticsearch-plugin install file:///usr/share/elasticsearch/analysis-kuromoji-8.3.3.zip

当然、ローカルファイルをコンテナに突っ込むこともできる。

FROM elasticsearch:8.3.3
COPY analysis-kuromoji-8.3.3.zip /usr/share/elasticsearch/
RUN bin/elasticsearch-plugin install file:///usr/share/elasticsearch/analysis-kuromoji-8.3.3.zip

このDockerfileを使ってelasticsearchを起動するdocker-compose.ymlを書いてみた。この記事の実験の範囲ではdockerコマンドで起動しても特に問題ないが、なんとなく。

services:
  elasticsearch:
    build: es
    ports:
      - 9200:9200
    environment:
      - discovery.type=single-node
      - xpack.security.enabled=false
    ulimits:
      memlock:
        soft: -1
        hard: -1

起動する

docker-compose up

個人情報テストデータジェネレータというありがたいサイトを使って、実験用データを生成

testdata.userlocal.jp

で、こんなコードを書いてみた。

import urllib.request
import csv
import json
import time


u = "http://localhost:9200/articles/"
hdrs = {"Content-Type": "application/json"}

#
# インデックス削除
#
def delete_index():
    try:
        req = urllib.request.Request(u, method="DELETE")
        with urllib.request.urlopen(req) as res:
            body = json.load(res)
            print(body)
            print("delete index ok")
    except:
        print("failed to delete index articles")
        pass

#
# インデックス作成
#
def create_index():
    req = urllib.request.Request(u, method="PUT")
    with urllib.request.urlopen(req) as res:
        body = json.load(res)
        print(body)
    print("create index ok")

#
# マッピングの登録
#
def set_mapping():
    data = json.dumps({
        "properties": {
            "content_id": {"type": "long"},
            "content": {"type": "text", "analyzer": "kuromoji"},
            "age": {"type": "long"}
        }
    }).encode("utf-8")
    req = urllib.request.Request(u, data, method="PUT", headers=hdrs)
    try:
        res = urllib.request.urlopen(req)
        body = json.load(res)
        print(body)
        print("set mappings ok")
    except urllib.error.HTTPError as e:
        print(e.code, e.reason, e.headers)

#
# ドキュメント追加
#
def insert_data(content_id, content, age):
    data = json.dumps({
        "content_id": i,
        "content": content,
        "age": age
    }).encode("utf-8")
    print(data)
    # IDを指定してデータ追加する場合はPOSTになる。
    # 指定せずにIDを自動生成する場合はPUT。
    req = urllib.request.Request(u+"_doc/" + str(i), data, method="POST", headers=hdrs)
    with urllib.request.urlopen(req) as res:
        body = json.load(res)
        print(body)

#
# ドキュメントIDを使ってデータを取得する
#
def get_data_by_id(id):
    time.sleep(1)
    print("=" * 72)
    url = u+"_doc/" + str(id)
    print(url)
    req = urllib.request.Request(url)
    with urllib.request.urlopen(req) as res:
        body = json.load(res)
        print("- " * 36)
        print(body)

#
# データ検索
#
def search_data(js):
    print("=" * 72)
    print(js)
    data = json.dumps(js).encode("utf-8")
    req = urllib.request.Request(u+"_search?pretty", data, headers=hdrs)
    with urllib.request.urlopen(req) as res:
        body = json.load(res)
        print("- " * 36)
        print(body)

##############################################################################

delete_index()
create_index()
set_mapping()
f = csv.reader(open("./dummy.csv"))
for i, line in enumerate(f):
    if i == 10000:
        break
    try:
        age = int(line[2])
        address = line[10]
        insert_data(i, address, age)
    except:
        pass


# 転置インデックス作成にちょっとだけ時間がかかるのでsleepする
time.sleep(1)

get_data_by_id(1)

search_data({
    "query": {
        "match" : {
            "content": {
                "query": "東京"
            }
        }
    },
    "from": 5,
    "size": 10,
    "sort": ["age"],
    "_source": ["content", "age"]
})

# 複数の条件をすべて満たしたものを抽出するには以下のようにして条件を列挙する
# 配列になっているが項目がひとつでも大丈夫
# highlightってところはおまけで、検索結果に引っかかったところを目立たせる指示
search_data({
    "query": {
        "bool": {
            "must": [
                {"match" :{"age": 45}},
                {"match": {"content": "神奈川"}}
            ]
        }
    },
    "highlight": {
        "fields": {
            "content": {}
        }
    },
    "from": 5,
    "size": 10,
    "_source": ["content", "age"]
})


search_data({
    "query": {
        "bool": {
            "must": [
                {"match" :{"content": "東京 品川"}}
            ]
        }
    },
    "highlight": {
        "fields": {
            "content": {}
        }
    },
    "from": 5,
    "size": 10,
    "_source": ["content", "age"]
})

これを実行したら無事検索結果が出てきました。

データベースからグループごとに最大値であったレコードを取得したい

データベースからグループごとに最大値であったレコードを取得したい。

学校のテストのスコアを管理するDBを想定してみる。

まず生徒の情報を保存するテーブルを作る。

create table student (
    student_id integer primary key,
    class integer,
    name varchar(128)
);

次に、科目の情報を管理するテーブルを作る

create table subject (
    subject_id integer primary key,
    name varchar(32)
);

そして、各生徒の科目ごとの得点を管理するテーブルを作る

create table exam (
    id integer primary key,
    student_id integer,
    subject_id integer,
    score integer
);

テーブルの準備ができたので、テストの結果を挿入。

insert into student values(0,1,'佐藤');
insert into student values(1,2,'鈴木');
insert into student values(2,3,'高橋');
insert into student values(3,1,'田中');
insert into student values(4,2,'伊藤');
insert into student values(5,3,'渡辺');

insert into subject values(0, '国語');
insert into subject values(1, '算数');

insert into exam values(0, 0, 0, 80);
insert into exam values(1, 0, 1, 70);
insert into exam values(2, 1, 0, 50);
insert into exam values(3, 1, 1, 40);
insert into exam values(4, 2, 0, 20);
insert into exam values(5, 2, 1, 90);
insert into exam values(6, 3, 0, 50);
insert into exam values(7, 3, 1, 76);
insert into exam values(8, 4, 0, 39);
insert into exam values(9, 4, 1, 75);
insert into exam values(10, 5, 0, 19);
insert into exam values(11, 5, 1, 100);

わかりやすいように一旦全部が載っている表にして、この後の実行結果と見比べることにする。

select
    subject.subject_id,
    subject.name as subject_name,
    student.student_id,
    student.class,
    student.name as student_name,
    score,
    id
from exam 
left join subject on exam.subject_id = subject.subject_id
left join student on exam.student_id = student.student_id
order by subject.name, student.class, student.name;

各科目の最高得点をとってみる

select subject.name, max(score)
from exam 
join subject on exam.subject_id = subject.subject_id
group by subject.name;

クラスごとの最高得点

select student.class, subject.name, max(score)
from exam 
join student on exam.student_id = student.student_id
join subject on exam.subject_id = subject.subject_id
group by student.class, subject.name
order by student.class, subject.name;

では誰がこの最高得点だったのか?

select l.class, r.subject_name, max_score, r.name
from (
    select
        student.class,
        subject.subject_id,
        max(score) as max_score
    from exam 
    join student on exam.student_id = student.student_id
    join subject on exam.subject_id = subject.subject_id
    group by
        student.class,
        subject.subject_id,
        subject.name) as l
left join (
    select
        student.class,
        subject.name as subject_name,
        subject.subject_id,
        score,
        student.name
    from exam 
    join student on exam.student_id = student.student_id
    join subject on exam.subject_id = subject.subject_id
) as r
    on l.class = r.class
    and l.subject_id = r.subject_id
    and l.max_score = r.score 
order by l.class, r.subject_name;

もっとシンプルに書けないものか。。。WITH句を使えば若干シンプル?

with t as (
    select
        subject.subject_id,
        subject.name as subject_name,
        student.student_id,
        student.class,
        student.name as student_name,
        score
    from exam 
    left join subject on exam.subject_id = subject.subject_id
    left join student on exam.student_id = student.student_id
)
select
    l.class,
    t.subject_name,
    l.max_score,
    t.student_name
from (
    select
        class,
        subject_id,
        max(score) as max_score
    from t
    group by class, subject_id
) as l
left join t on l.class = t.class
    and l.subject_id = t.subject_id
    and l.max_score = t.score 
order by l.class, t.subject_name;

もう少しスマートに、、最大値がある行を抽出する方法を工夫。

with t as (
    select
        subject.name as subject_name,
        student.class,
        student.name as student_name,
        score
    from exam 
    left join subject on exam.subject_id = subject.subject_id
    left join student on exam.student_id = student.student_id
)
select
    class,
    subject_name,
    score,
    student_name
from t
where not exists (
    select 1 from t as t0
    where t.class = t0.class
        and t.subject_name = t0.subject_name
        and t.score < t0.score
)
order by class, subject_name;

RockyLinuxにKeycloakをインストールした

全部rootで実施。

ポートを開ける

firewall-cmd --add-port=8080/tcp --zone=public --permanent
firewall-cmd --reload

インストールする

dnf -y install java-11-openjdk
dnf -y install curl
cd /tmp
curl -OL https://github.com/keycloak/keycloak/releases/download/18.0.2/keycloak-18.0.2.tar.gz
cd /usr/local/bin
tar /tmp/zxvf keycloak-18.0.2.tar.gz

環境変数にKeycloakの管理者のアカウント名とパスワードを設定しておく。

export KEYCLOAK_ADMIN=admin
export KEYCLOAK_ADMIN_PASSWORD=admin

起動する

cd /usr/local/bin/keycloak-18.0.2/bin
./kc.sh start-dev

とりあえず起動だけしたいのであればここまででOK。

Dockerを使うのであれば、ここまでと同じことが以下だけで完了する。

docker run -p 8080:8080 -e KEYCLOAK_ADMIN=admin -e KEYCLOAK_ADMIN_PASSWORD=admin quay.io/keycloak/keycloak:18.0.2 start-dev

※Keycloakの名がタイトルに含まれる日本語の書籍は現時点ではこの本しかないのだが、バージョン15を前提に書かれており、例えば起動用シェル(standalone.sh)はバージョン16までしか同梱されておらず、現行バージョンである18には存在しないので注意。バージョン17でWildFlyからQuarkusに乗せ換えた影響が大きいようだ。

ただ、これは開発用に起動しただけ。./kc.shをオプションをつけずに起動すると、いろいろなことを言われるのだが、これを読むとプロダクション環境では違う起動のしかたをしろと言われているのがわかる。

~~もうちょっと細かい設定方法をここに書く予定です~~

管理者でログインしたら、とりあえずレルムを追加する。

左上の「Master」とかいてあるあたりをクリックすると、「Add realm」というボタンが現れるのでそいつをクリック。作成するレノムの名前を入力してからCreateボタンを押す。

つぎにこのレノムの設定をしていく。

Themeを選択して「Internationalization Enabled」をONにして、Supported Localesのところで「en」と「ja」以外をすべて削除し、Default Localeをjaにする。これは主に日本語ネイティブの人が使うけれど英語表記も一応残したい、という主旨の設定のつもり。

ユーザを追加するには、左側のメニューの割と下のほうにある「Users」をクリック、右側のところで「Add user」ボタンをクリックする。

~~説明がめんどいし実運用ではREST APIをたたく気がする~~

以下でアクセストークンが返ってくる。

curl -L -X POST 'http://localhost:8080/realms/master/protocol/openid-connect/token' -H 'Content-Type: application/x-www-form-urlencoded' -d 'username=admin' -d 'password=admin' -d 'grant_type=password' -d 'client_id=admin-cli'

このアクセストークンをAuthorizationヘッダに付与(Authorization: Bearer アクセストークン みたいなかんじ)して、APIを発行する。

Reactでログインしたら元々いたページに戻すやつ

useLocationで今いるページのパスを取得、useNavigateでページのパスを渡しながらログイン画面に飛ばして、ログインに成功したら先ほど渡されたパスに移動する。

具体的には、

npx create-react-app 云々 --template typescript

をした直後の状態として、App.jsxを以下のように書き換えるとなんとなく理解できる。

import React, { useState } from "react";
import {
  BrowserRouter,
  Routes,
  Route,
  Link,
  useLocation,
  useNavigate,
  Navigate,
} from "react-router-dom";

// トップページ
const TopPage = () => {
  return (
    <>
      <h2>トップページ</h2>
      {["a", "b", "c"].map((x: string, i: number) => {
        return (
          <p key={i}><Link to={"/" + x}>{x}</Link>
          </p>
        );
      })}
    </>
  );
};

// コンテンツ
const Comp = () => {
  const location = useLocation();
  const navigate = useNavigate();
  return (
    <>
      <h2>コンテンツ</h2>
      私は今{location.pathname}にいます。
      <br />
      <button
        onClick={(e) => {
          navigate("/login", { state: location.pathname });
        }}
      >
        ログイン画面にリダイレクトするイメージ
      </button>
      <hr />
      <Link to="/">トップページ</Link>
    </>
  );
};

// ログイン画面
const Login = () => {
  const location = useLocation();
  const navigate = useNavigate();

  return (
    <>
      {location.state}から来たようだ
      <p>
        <button
          onClick={(e) => {
            let url: string = location.state as string;
            navigate(url);
          }}
        >
          ログイン
        </button>
      </p>
      <hr />
      <Link to="/">トップページ</Link>
    </>
  );
};

//
function App() {
  return (
    <>
      <BrowserRouter>
        <Routes>
          <Route path="/login" element={<Login />} />
          <Route path="/a" element={<Comp />} />
          <Route path="/b" element={<Comp />} />
          <Route path="/c" element={<Comp />} />
          <Route path="/" element={<TopPage />} />
        </Routes>
      </BrowserRouter>
    </>
  );
}

export default App;

ReactのContext

React内でグローバル変数的にアクセスできるContextを使い、ログインしたあとでそのユーザ名などを保持する機能を実現したかった。 ちなみにContextとは別にcookieなどにアクセストークンを管理し、本当に大事なデータはアクセストークンを使わないと取得できないようにすることでセキュリティは担保すればよいと思っている。

以下でプロジェクトを作った直後のところからスタート。

npx create-react-app 云々 --template typescript

いきなり一番重いところから。App.tsxを以下のように書き換える。

import React, { createContext, useState } from "react";
import { BrowserRouter, Routes, Route } from "react-router-dom";

import { Header } from "./Header";
import { Login } from "./Login";
import { Logout } from "./Logout";
import { Contents } from "./Contents";
import { Admin } from "./Admin";
import { Portal } from "./Portal";

//
// コンテキストで管理したいデータの型を定義する。
//
export type UserInfo = {
  user_name: string;
  mail_address: string;
  admin_flag: number;
};

//
// コンテキストを作る。初期値も与える。
//
export const UserContext = createContext({
  user_name: "初期値",
  mail_address: "初期値",
  admin_flag: 0,
});

function App() {
  //
  // コンテキスト上に保持する変数とそれを変更する関数の宣言
  //
  const [user_info, setUserInfo] = useState<UserInfo>({
    user_name: "",
    mail_address: "",
    admin_flag: -1,
  });

  // コンテキストを参照したいコンポーネントたちを
  //  コンテキスト.Providerでくくる。
  // LoginとLogoffにはコンテキストの値を変更するための
  // 関数を渡す。それ以外はそれぞれのコンポーネント内で
  // コンテキストにアクセスしにいく。
  return (
    <>
      <UserContext.Provider value={user_info}>
        <Header />
        <BrowserRouter>
          <Routes>
            <Route
              path="/login"
              element={<Login setUserInfo={setUserInfo} />}
            />
            <Route
              path="/logout"
              element={<Logout setUserInfo={setUserInfo} />}
            />
            <Route path="/contents" element={<Contents />} />
            <Route path="/admin" element={<Admin />} />
            <Route path="/" element={<Portal />} />
          </Routes>
        </BrowserRouter>
      </UserContext.Provider>
    </>
  );
}

export default App;

ログイン用のコンポーネントをLogin.tsxというファイルのなかに以下のように書いて実装。 本当のログイン処理はもうちょっと真面目に書く必要あり。

import { useState } from "react";
import { Link } from "react-router-dom";
import { UserInfo } from "./App";

//
// ユーザ情報を変更する関数を受け取るためのインタフェース
//
interface LoginProps {
  setUserInfo: (userInfo: UserInfo) => void;
}

export const Login = (props: LoginProps) => {
  const [username, setUsername] = useState<string>("");
  const [password, setPassword] = useState<string>("");

  const procLogin = () => {
    // ログインを処理を行う。
    // この実装はContextの使い方の勉強用なので省略し、
    // 代わりにダミーの値を入れる。
    console.log(username, password);
    if (username === "user01" && password === "user01") {
      // コンテキストを書き換える
      props.setUserInfo({
        user_name: "大谷翔平",
        mail_address: "shohei.otani@example.com",
        admin_flag: 0,
      });
    } else if (username === "admin" && password === "admin") {
      // コンテキストを書き換える
      props.setUserInfo({
        user_name: "長嶋茂雄",
        mail_address: "nagashima@example.com",
        admin_flag: 1,
      });
    }
  };

  return (
    <>
      <p>
        username :
        <input type="text" value={username}
            onChange={(e)=>{setUsername(e.target.value);}} />
      </p>
      <p>
        password :
        <input type="password" value={password}
            onChange={(e)=>{ setPassword(e.target.value); }} />
      </p>
      <p>
        <button onClick={(e) => { procLogin(); }}>login</button>
      </p>
      <hr />
      <Link to="/">ポータルへ</Link>
    </>
  );
};

ログアウトでcontextの中身を初期化する。 Logout.tsxというファイルを作って以下のように書く。

import { Link } from "react-router-dom";
import { UserInfo } from "./App";

//
// ユーザ情報を変更する関数を受け取るためのインタフェース
//
interface LogoutProps {
  setUserInfo: (userInfo: UserInfo) => void;
}

export const Logout = (props: LogoutProps) => {
  return (
    <>
      <button
        onClick={(e) => {
          props.setUserInfo({
            user_name: "",
            mail_address: "",
            admin_flag: -1,
          });
        }}
      >
        ログオフする
      </button>
      <Link to="/">ポータルへ</Link>
    </>
  );
};

どのURLにアクセスしてもページの上のほうにログインしているユーザの名前などが表示される領域を作る。 Header.tsxという名前のファイルを作り以下のように書く。

import { useContext } from "react";
import { UserContext } from "./App";

//
// 各ページの先頭に表示する、ログイン中のユーザ名とそのメアドを表示するイメージ
//
export const Header = () => {
  const user = useContext(UserContext);
  return (
    <>
      [ {user.user_name} | {user.mail_address} ] <br />
    </>
  );
};

ポータル画面では、ログインしているかどうか、またログインしている場合はそのユーザの権限次第で表示するリンクを切り替える。 Portal.tsxというファイルを作って以下のように書く。

import { useContext } from "react";
import { Link } from "react-router-dom";
import { UserContext } from "./App";

export const Portal = () => {
  const user = useContext(UserContext);
  // 権限ごとにリンクを変更する。
  // (アクセス制御は直リンも考慮しリンク先で実施)
  return (
    <>
      <h1>ポータル</h1>
      {user.admin_flag === 1 ? (
        <>
          <Link to="/admin">管理ページ</Link> |
        </>
      ) : (
        ""
      )}
      {user.admin_flag === -1 ? (
        <Link to="/login">ログイン</Link>
      ) : (
        <>
          <Link to="/contents">コンテンツ</Link> |
          <Link to="/logout">ログアウト</Link>
        </>
      )}
    </>
  );
};

あとは、適当にコンテンツを作る。 Contents.tsxという名前のファイルを作って以下のようなかんじで書く。

import { Link } from "react-router-dom";

export const Contents = () => {
  return (
    <>
      <h1>コンテンツ</h1>
      ログインしていればみることができます。
      ただこの実装だと直接URLをたたくとログインしていなくても見えてしまうので、
      実際の実装ではAPIでとってくるなどしてアクセスコントロールすること。
      <hr />
      <Link to="/">ポータルに戻る</Link>
    </>
  );
};

以上。