Postgresqlバージョン
- AWS RDS
- Postgresqlバージョン: 12, 13を使っています。
SQL開発方法
- UVではORMをあまり使わないです。
- 主にstoreprocedureのSQLを書きます。
DROP TYPE IF EXISTS type_sep_get_list_administrators CASCADE;
CREATE TYPE type_sep_get_list_administrators AS (
uuid UUID
,mail TEXT
);
-- 管理者 一覧
-- 引数
-- p_uuid : UUID
-- p_mail : メール
-- 戻り値
-- uuid : UUID
-- mail : メール
-- 例外
-- なし
CREATE OR REPLACE FUNCTION get_list_administrators (
p_uuid UUID DEFAULT NULL
,p_mail TEXT DEFAULT NULL
,p_limit BIGINT DEFAULT NULL
,p_offset BIGINT DEFAULT 0
,p_with_count_flag BOOLEAN DEFAULT FALSE
) RETURNS SETOF type_get_list_administrators AS $FUNCTION$
DECLARE
w_record type_sep_get_list_administrators;
BEGIN
IF p_with_count_flag IS TRUE THEN
SELECT
t1.uuid
,t1.mail
INTO
w_record
FROM
public.administrators AS t1
LIMIT
1
;
IF NOT FOUND THEN
RETURN;
END IF;
SELECT
COUNT(*)::TEXT
INTO
w_record.bk
FROM
public.administrators AS t1
WHERE
(p_uuid IS NULL OR t1.uuid = p_uuid)
AND (p_mail IS NULL OR t1.mail = p_mail)
;
END IF;
RETURN QUERY SELECT
t1.uuid
,t1.mail
FROM
public.administrators AS t1
WHERE
(p_uuid IS NULL OR t1.uuid = p_uuid)
AND (p_mail IS NULL OR t1.mail = p_mail)
LIMIT
p_limit
OFFSET
p_offset
;
IF p_with_count_flag IS TRUE THEN
RETURN NEXT w_record;
END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;
- Webフレームワークでストレスを呼びます。 Rails
class Admin < ApplicationRecord
include SerializeJsonColumn
self.primary_key = :uuid
def self.get_list_administrators(params)
sql = <<-SQL
SELECT
uuid
,mail
FROM
get_list_administrators(
p_limit := 100
,p_offset := 0
);
SQL
find_one_by_sql([sql, params])
end
end
- Webフレームワークでストレスを呼びます。 Rust
use crate::{error::SepError, external_interfaces::postgresql::*, PgClient};
use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use uuid::Uuid;
#[derive(Serialize, Deserialize, Debug, Clone)]
pub struct DbResult {
pub uuid: Uuid,
pub mail: String,
}
impl DbResult {
pub fn to_json(&self) -> serde_json::Value {
let mut json = serde_json::to_value(self).unwrap();
json
}
}
#[derive(Serialize, Deserialize, Debug, Clone, Default)]
pub struct DbParameter {
pub uuid: Option<Uuid>,
pub mail: Option<String>,
pub limit: Option<i64>,
pub offset: Option<i64>,
pub with_count_flag: Option<bool>,
}
#[derive(Serialize, Deserialize, Debug, Clone, Default)]
pub struct QueryParameter {
pub uuid: Option<Uuid>,
pub mail: Option<String>,
pub limit: Option<i64>,
pub offset: Option<i64>,
}
impl QueryParameter {
pub fn to_params(self, with_count_flag: Option<bool>) -> DbParameter {
DbParameter {
limit: self.limit,
offset: self.offset,
with_count_flag,
uuid: self.uuid,
mail: self.mail,
}
}
}
pub async fn execute(conn: &PgClient, params: DbParameter) -> Result<Rows<DbResult>, SepError> {
query(
conn,
r#"
SELECT
to_json(t1.*)
FROM
get_list_administrators(
p_uuid := $1
,p_mail := $2
,p_limit := $3
,p_offset := $4
,p_with_count_flag := $5
) AS t1
"#,
&[
¶ms.uuid,
¶ms.mail,
¶ms.limit,
¶ms.offset,
¶ms.with_count_flag,
],
)
.await
}
pub async fn execute_with_count(
conn: &PgClient,
params: DbParameter,
) -> Result<(Vec<serde_json::Value>, i64), SepError> {
let mut list = execute(conn, params).await?.as_list()?;
let count = if list.is_empty() {
0
} else {
list.pop().unwrap().bk.unwrap().parse::<i64>().unwrap()
};
let result = list
.iter()
.map(|it| it.to_json())
.collect::<Vec<serde_json::Value>>();
Ok((result, count))
}
テーブル作成・自動生成ファイル
- VS Code の UVのERDツールを使います。: https://marketplace.visualstudio.com/items?itemName=uniquevision.erd-tool
- ERDをJSON形でテーブルを定義します。
{
"lname": "メディア",
"pname": "media",
"columns": [
{
"domain": "UUID",
"pk": true,
"default": "gen_random_uuid()"
},
{
"lname": "ファイル",
"pname": "file",
"domain": "名前"
},
{
"lname": "MIMEタイプ",
"pname": "mime_type",
"domain": "コード"
},
{
"lname": "バイト",
"pname": "byte",
"domain": "数"
},
{
"lname": "S3パス",
"pname": "s3_path",
"domain": "コード"
}
]
}
- そのあとに、ERDツールは自動でテーブル作成SQLを生成してくれます。
DROP TABLE IF EXISTS source.media CASCADE;
CREATE TABLE source.media (
uuid UUID NOT NULL DEFAULT gen_random_uuid() -- UUID
,file_nm TEXT NOT NULL DEFAULT '' -- ファイル名
,mime_type_code TEXT NOT NULL DEFAULT '' -- MIMEタイプコード
,byte_count BIGINT NOT NULL DEFAULT 0 -- バイト数
,s3_path_code TEXT NOT NULL DEFAULT '' -- S3パスコード
,created_uuid UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
,updated_uuid UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
,deleted_uuid UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
,created_at TIMESTAMPTZ NOT NULL
,updated_at TIMESTAMPTZ NOT NULL
,deleted_at TIMESTAMPTZ
,created_pg TEXT NOT NULL DEFAULT ''
,updated_pg TEXT NOT NULL DEFAULT ''
,deleted_pg TEXT NOT NULL DEFAULT ''
,bk TEXT
,PRIMARY KEY(uuid)
);
CREATE TABLE public.media (
LIKE source.media INCLUDING ALL
) INHERITS (source.media);
CREATE TABLE garbage.media (
LIKE source.media INCLUDING ALL
) INHERITS (source.media);
-
ストレスの自動生成
- レコード作成ストプロ
- レコード編集ストプロ
- レコード削除
- レコード取得
-
エラーハンドリングの自動生成
{
"lname": "データなし",
"pname": "not_found",
"code": "04",
"dbCode": "U0003",
"message": "データが見つかりませんでした。",
"warning": false
}
-- データなし
CREATE OR REPLACE FUNCTION RAISE_RESULT_CODE_NOT_FOUND(
p_invalid_flag BOOLEAN DEFAULT TRUE
,p_message TEXT DEFAULT ''
) RETURNS VOID AS $FUNCTION$
BEGIN
IF p_invalid_flag THEN
RAISE SQLSTATE 'U0003' USING MESSAGE = p_message;
END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;
SQLチェック: Language Server
- Vs codeのExtensionsを使う: https://marketplace.visualstudio.com/items?itemName=uniquevision.vscode-plpgsql-lsp
- https://github.com/UniqueVision/plpgsql-lsp
DB移行・Migration Tool
-
移行内容
- テーブル編集
- ストプロ更新
- DBのデータ変更
-
UVが作ったツールを使います。
その他
- バッチ: Progresql cron : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html
- 詳細設計のとき、SQLを書きます。