2022-08-13

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

データベースからグループごとに最大値であったレコードを取得したい。最大値そのものではなくて、あるカラムが最大値になっているレコードすべてを取得。

学校のテストのスコアを管理する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;

2022-07-02

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は使わないことになったので、おしまい。

2022-05-22

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;

2022-05-21

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>
    </>
  );
};

以上。

2022-05-04

Goのinterface

Goで単体試験などをしたくなった場合、テスト対象となる機能が呼び出す関数を外から渡したくなる。そんなときはinterfaceを使うことで実現できる。

こんな感じ

package main

import "fmt"

// インタフェースとして、どんな関数を具備している必要があるかを定義。
type Speaker interface {
    Greeting()
}

// そのインタフェースで定義された関数を使う関数を定義
// 単体テストのことを想定しているならば、これがテスト対象
func Run(i Speaker) {
    i.Greeting()
}

// インタフェースを持たせる構造体を定義
type Japanese struct {
}

// インタフェースに合うように、インタフェースで定義されている
// 関数を構造体に対して実装
func (p *Japanese)Greeting() {
    fmt.Println("こんにちは")
}

// もうひとつ、同じインタフェースを持たせる別の構造体を定義しておいて、
type American struct {
}

// 先ほどと同じく、インタフェースに合うように、インタフェースで定義されている
// 関数を構造体に対して実装
func (a *American)Greeting() {
    fmt.Println("Hi!")
}


// 使ってみる
func main() {

    // Japanese構造体を渡してもちゃんと実行できるし、
    japanesePeople := &Japanese{}
    Run(japanesePeople)

    // American構造体を渡してもちゃんと実行できる
    americanPeople := &American{}
    Run(americanPeople)
}

2022-04-30

Redmineを試したくなった

Dockerを使うのが簡単だ。

docker -p 3000:3000 redmine

管理者の初期アカウントはadmin、パスワードもadmin。

hub.docker.com

管理者権限のあるアカウントでログインして、以下の作業を実施していく。ページ最上部の左寄りの場所に「管理」というリンクがあるのでそこをクリックして管理画面に遷移。各項目の設定画面へのリンクがあるのでそちらで作業する。

ユーザを作る
- ユーザはAPIでも作ることができるので、何かのシステムと同じユーザを作るなどといったことも簡単にできる。
ロールを作る
- プロジェクトにユーザを登録する際に必要
- ロールはAPIでは作れない。GUIを操作して作るしかない。
ステータスを作る
- トラッカーを作るのに必要
トラッカーを作る
プロジェクトを作る
- APIで作ることも可能、だけど、そんなにたくさん作るものでもないのであまり意味ないか？
プロジェクトにユーザを追加する、同時にそのプロジェクトにおけるそのユーザのロールも設定する。
- チェックボックスになっているので、同じロールの人であれば複数のユーザを一気に登録可能。
優先度を作る
- チケットを作るときに必要
- 設定の中の値のリストの中にあるので注意

この辺までいじって、自分がやりたいことができないことがわかったので、終了。 (複数のグループをissueに割り当てたかった）

2022-04-29

ginを使って作成したWebアプリをsystemdを使ってデーモンとして動かす

ginを使って作成したWebアプリをデーモンとして動かす方法について、少し前の記事でsupervisordを用いる方法を延々書いてしまったが、ここではsystemdを使う方法を書いてみる。

1. ログをsyslogに吐くようにする。

デーモン化したらログは画面ではなくファイルに出したいところ。 Linuxで動かす（よね？）なら、syslogを使ってしまうのがお手軽だ。

   logger, err := syslog.New(
        syslog.LOG_NOTICE|syslog.LOG_USER,
        "SampleWebApp")
    if err != nil {
        panic(err)
    }
    gin.SetMode(gin.ReleaseMode)
    gin.DisableConsoleColor()
    gin.DefaultWriter = logger

2. systemd用のユニットファイルを作成する。

たぶん、これくらいが一番シンプルなんじゃないかな。。

[Unit]
Description=Sample Web Application

[Service]
ExecStart=/usr/local/bin/SampleWebApp
KillMode=process
Restart=always

[Install]
WantedBy=multi-user.target

3. sytemctlコマンドで起動する。

systemctl restart サービス名

4. おまけ firewalldでポートを開放する

とりあえず今の状態を確認して、

firewall-cmd --list-all

たとえばこんなコマンドを打って、

firewall-cmd --add-port=3000/tcp --zone=public --permanent

そのあとリロードしてから、

firewall-cmd --reload

再度状態を確認すると、

firewall-cmd --list-all

先ほど追加したポートが開放されていることがわかるはず。