背景

普段PL/pgSQLを利用することが多いです。以下のようなメリットとデメリットがあります。

メリット

ループやIFなどの制御構造が使えて柔軟なプログラムができる
共通化することで再利用できる
DB内部でまとめてSQLを実行できるのでアプリケーションサーバーとの通信が少なくて済む

デメリット

PL/pgSQLは実行するまで動作するかどうかわからない
デバッグがしずらい
エディターは補完してくれない
フォーマッターが無い

ツール

いくつかツールがあります。

psql

作成したPL/pgSQLをpsqlで読み込ませると構文エラーはチェックしてくれます。しかし実際動くことを保証はしてくれません。存在しないカラムなどがあっても構文が合っていれば正常に読み込んでしまいます。

plpgsql_check

psqlよりも詳しく解析してくれるツールです。カラムの存在チェックなど詳しく分析してくれます。しかし動的SQLの分析が苦手なことと、一度PostgreSQLに登録した後で無いとチェックできないのがデメリットです。

DBeaver + pldebugger

DBeaverというGUIのDB管理ツールがあります。このツールとpldebuggerというツールを組み合わせることご、PL/pgSQLのブレークポイントが張れるなんどデバッグ機能が使えます。

目的

開発をサポートするツールがいくつかありますが、どれもPostgreSQLに登録して使う必要があり、エディター上でさくさく実行してくれません。以下のようなツールがほしいです。

Language Server(エディター上で補完してくれたり、エラーを指摘してくれる)
VSCode拡張(plpgsql_checkを裏で実行して、エディター上でにエラーを指摘してほしい)
formatter(CLIでエディター上でフォーマットやCI/CDで自動でフォーマット)

この中でやりやすそうな気がするfomatterにチャレンジしてみます。

パーサー

formatterを作るにはパーサーが必要です。実はPL/pgSQLまでパースしてくれるパーサーライブラリがあります。
libpg_query
使ってみたところいくつか問題がありました。

関数名、パラメーター、戻り値などを解析してくれない
コメントが無視される

残念ながら自前でパーサーを書くことにしました。

手元にあるサンプルをターゲットにしてみます。

uv_is_number.sql

-- 文字列が数値か判定する
-- 引数
--   p_src : 判定したい文字列
--   p_min : 最小値
--   p_max : 最大値
-- 戻り値
--   文字列が数値の場合かつ範囲内の場合はtrue
CREATE OR REPLACE FUNCTION uv_is_number (
  p_src text
  ,p_min bigint DEFAULT -9223372036854775808
  ,p_max bigint DEFAULT 9223372036854775807
) RETURNS boolean AS $$
DECLARE
  w_value bigint;
BEGIN
  IF p_src IS NULL OR p_src = ''  THEN
    RETURN FALSE;
  END IF;
  BEGIN
    w_value := p_src::bigint;
    IF p_min <= w_value AND w_value <= p_max THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  EXCEPTION
    WHEN invalid_text_representation THEN
      RETURN FALSE;
  END;
END;
$$ LANGUAGE plpgsql;

パーサーコンビネーターnom

Rust製のパーサーコンビネーターです。簡単なパーサーを組み合わせることで目的のパーサーを組み立てます。既にnomベースのSQLのパーサーも存在します。PL/pgSQLはサポートしていませんが、大いに参考させてもらいます。

コメントのパーサー

まずコメントをパースします。fn commentは1行分のコメントをパースするパーサーです。既存で用意されているパーサーを組み合わせて構築しています。fn commentsはfn commentを利用した複数行のコメントをパースするパーサーです。

pub fn comment(input: &str) -> IResult<&str, &str> {
    let (input, (_, _, body)) = tuple((
        multispace0,
        tag("--"),
        take_till(|ch| is_newline(ch as u8))
    ))(input)?;
    Ok((input, body))
}

pub fn comments(input: &str) -> IResult<&str, Vec<&str>> {
    many0(comment)(input)
}

let sp = "....";
let (input, comments) = comments(sp).unwrap();
println!("{:?}", comments);

nom::IResult

この型はパーサーの戻り値です。一番目にパースした残りの文字列、二番目にパースした結果を返します。fn commentではコメントを&str型だ返しています。fn commentsは複数行のコメントなのでVec<&str>型で返しています。

multispace0

0個以上の空白をパースします。1個以上の場合はmultispace1ドキュメント

tag

引数の文字列をパースします。ここでは「--」にマッチします。ドキュメント

take_till

クロージャーがtrueになるまで文字を回収します。ここでは改行文字が出てくるまでコメントとして回収しています。ドキュメント

tuple

複数のパーサーを組み合わせます。ここでは空白、ハイフンハイフン、コメント本体をパースしています。ドキュメント

many0

0回以上パーサーを実行します。ここではfn commentはパーサーを組み合わせた新しいパーサーでそれを0回以上繰り返します。ドキュメント

結果

[" 文字列が数値か判定する", " 引数", "   p_src : 判定したい文字列", "   p_min : 最小値", "   p_max : 最大値", " 戻り値", "   文字列が数値の場合かつ範囲内の場合はtrue"]

ストアードプロシージャのヘッダーのパーサー

関数名とreplaceがあるかないかを取得しています。

pub fn is_ident(ch: char) -> bool {
    is_alphanumeric(ch as u8) || ch == '_' || ch == '-'
}

pub fn parse_sp_header(input: &str) -> IResult<&str, (&str, bool)> {
    let (input, (_, replace_flag, _, _, _, name)) = tuple((
        tag_no_case("create"),
        opt(tuple((
            multispace1,
            tag_no_case("or"),
            multispace1,
            tag_no_case("replace"),
        ))),
        multispace1,
        tag_no_case("function"),
        multispace1,
        take_while(is_ident),
    ))(input)?;

    Ok((input, (name, replace_flag.is_some())))
}

tag_no_case

大文字小文字を無視して指定した文字列をパースします。ドキュメント

opt

パース対象がオプショナルなもんをパースします。Option型を返します。ドキュメント

take_while

指定の文字だけを集めてパース。ここでは名前っぽい文字を表すis_identを定義しています。ドキュメント

ストアードプロシージャの引数のパーサー

SqlTypeで型を定義しています。FuctionParameterは引数の情報を保持するstructです。

#[derive(Debug)]
pub enum SqlType<'a> {
    Text,
    BigInt,
    Boolean,
    Custom(&'a str),
}

impl<'a> SqlType<'a> {
    pub fn new(input: &'a str) -> Self {
        match input {
            "text" => SqlType::Text,
            "bigint" => SqlType::BigInt,
            "boolean" => SqlType::Boolean,
            _ => SqlType::Custom(input),
        }
    }
}

#[derive(Debug)]
pub struct FuctionParameter<'a> {
    pub name: &'a str,
    pub sql_type: SqlType<'a>,
    pub default_value: Option<&'a str>
}

pub fn one_parameter(input: &str) -> IResult<&str, FuctionParameter> {
    let (input, (_, _, _, name, _, sql_type, value)) = tuple((
        multispace0,
        opt(char(',')),
        multispace0,
        take_while(is_ident),
        multispace1,
        take_while(is_ident),
        opt(tuple((
            multispace1,
            tag_no_case("default"),
            multispace1,
            take_while(is_ident),
        ))),
    ))(input)?;

    Ok((
        input,
        FuctionParameter {
            name,
            sql_type: SqlType::new(sql_type),
            default_value: value.map(|(_, _, _, val)| {
                val
            })
        },
    ))
}

pub fn parse_parameters(input: &str) -> IResult<&str, Vec<FuctionParameter>> {
    many0(one_parameter)(input)
}

ストアードプロシージャの戻り値のパーサー

戻りの型とsetofの有り無しをstructに保持しています。

#[derive(Debug)]
pub struct FunctionReturn<'a> {
    pub set_of: bool,
    pub sql_type: SqlType<'a>,
}

pub fn parse_function_return(input: &str) -> IResult<&str, FunctionReturn> {
    let (input, (_, _, setof, _, sql_type)) = tuple((
        multispace0,
        tag_no_case("returns"),
        opt(tuple((
            multispace1,
            tag_no_case("set"),
            multispace1,
            tag_no_case("of"),
        ))),
        multispace1,
        take_while(is_ident),
    ))(input)?;
    Ok((input, FunctionReturn{
        set_of: setof.is_some(),
        sql_type: SqlType::new(sql_type),
    }))
}

ストアードプロシージャのbodyのパーサー

footerと言っていますがbodyの部分です。中身は今のところ全体で一つの文字列として回収しています。

pub fn parse_sp_footer(input: &str) -> IResult<&str, &str> {
    let (input, ( _, _, _, value, _)) = tuple((
        tag_no_case("as"),
        multispace1,
        tag("$"),
        take_while(|ch| ch != '$'),
        tag("$"),
    ))(input)?;

    let mut terminator = "$".to_owned();
    terminator.push_str(value);
    terminator.push_str("$");

    let (input, (_, body, _, _, _, _, _, _, _,)) = tuple((
        multispace0,
        take_until(&*terminator),
        tag(&*terminator),
        multispace1,
        tag_no_case("language"),
        multispace1,
        tag_no_case("plpgsql"),
        multispace0,
        opt(tag(";")),
    ))(input)?;

    Ok((input, body))
}

全体

最後にStoredProcedueというstructを作ってここにまとめています。いままでの全部を入れて動くサンプルです。

use nom::{
    bytes::complete::{tag, tag_no_case, take_till, take_until, take_while},
    character::{
        complete::{char, multispace0, multispace1},
        is_alphanumeric,
        is_newline,
    },
    combinator::{opt},
    IResult,
    multi::{many0},
    sequence::{tuple},
};

pub fn comment(input: &str) -> IResult<&str, &str> {
    let (input, (_, _, body)) = tuple((
        multispace0,
        tag("--"),
        take_till(|ch| is_newline(ch as u8))
    ))(input)?;
    Ok((input, body))
}

pub fn comments(input: &str) -> IResult<&str, Vec<&str>> {
    many0(comment)(input)
}

pub fn is_ident(ch: char) -> bool {
    is_alphanumeric(ch as u8) || ch == '_' || ch == '-'
}

#[derive(Debug)]
pub enum SqlType<'a> {
    Text,
    BigInt,
    Boolean,
    Custom(&'a str),
}

impl<'a> SqlType<'a> {
    pub fn new(input: &'a str) -> Self {
        match input {
            "text" => SqlType::Text,
            "bigint" => SqlType::BigInt,
            "boolean" => SqlType::Boolean,
            _ => SqlType::Custom(input),
        }
    }
}

#[derive(Debug)]
pub struct FuctionParameter<'a> {
    pub name: &'a str,
    pub sql_type: SqlType<'a>,
    pub default_value: Option<&'a str>
}

pub fn parse_parameters(input: &str) -> IResult<&str, Vec<FuctionParameter>> {
    many0(one_parameter)(input)
}

pub fn one_parameter(input: &str) -> IResult<&str, FuctionParameter> {
    let (input, (_, _, _, name, _, sql_type, value)) = tuple((
        multispace0,
        opt(char(',')),
        multispace0,
        take_while(is_ident),
        multispace1,
        take_while(is_ident),
        opt(tuple((
            multispace1,
            tag_no_case("default"),
            multispace1,
            take_while(is_ident),
        ))),
    ))(input)?;

    Ok((
        input,
        FuctionParameter {
            name,
            sql_type: SqlType::new(sql_type),
            default_value: value.map(|(_, _, _, val)| {
                val
            })
        },
    ))
}

#[derive(Debug)]
pub struct FunctionReturn<'a> {
    pub set_of: bool,
    pub sql_type: SqlType<'a>,
}

pub fn parse_function_return(input: &str) -> IResult<&str, FunctionReturn> {
    let (input, (_, _, setof, _, sql_type)) = tuple((
        multispace0,
        tag_no_case("returns"),
        opt(tuple((
            multispace1,
            tag_no_case("set"),
            multispace1,
            tag_no_case("of"),
        ))),
        multispace1,
        take_while(is_ident),
    ))(input)?;
    Ok((input, FunctionReturn{
        set_of: setof.is_some(),
        sql_type: SqlType::new(sql_type),
    }))
}

#[derive(Debug)]
pub struct StoredFunction<'a> {
    pub name: &'a str,
    pub replace_flag: bool,
    pub parameters: Vec<FuctionParameter<'a>>,
    pub function_return: FunctionReturn<'a>,
    pub body: &'a str,
}

pub fn parse_sp_header(input: &str) -> IResult<&str, (&str, bool)> {
    let (input, (_, replace_flag, _, _, _, name)) = tuple((
        tag_no_case("create"),
        opt(tuple((
            multispace1,
            tag_no_case("or"),
            multispace1,
            tag_no_case("replace"),
        ))),
        multispace1,
        tag_no_case("function"),
        multispace1,
        take_while(is_ident),
    ))(input)?;

    Ok((input, (name, replace_flag.is_some())))
}

pub fn parse_sp_footer(input: &str) -> IResult<&str, &str> {
    let (input, ( _, _, _, value, _)) = tuple((
        tag_no_case("as"),
        multispace1,
        tag("$"),
        take_while(|ch| ch != '$'),
        tag("$"),
    ))(input)?;

    let mut terminator = "$".to_owned();
    terminator.push_str(value);
    terminator.push_str("$");

    let (input, (_, body, _, _, _, _, _, _, _,)) = tuple((
        multispace0,
        take_until(&*terminator),
        tag(&*terminator),
        multispace1,
        tag_no_case("language"),
        multispace1,
        tag_no_case("plpgsql"),
        multispace0,
        opt(tag(";")),
    ))(input)?;

    Ok((input, body))
}

pub fn stored_procedure(input: &str) -> IResult<&str, StoredFunction> {
    let (input, (_, header,  _, _, _, parameters, _, _, function_return, _, body)) = tuple((
        multispace0,
        parse_sp_header,
        multispace0,
        tag("("),
        multispace0,
        parse_parameters,
        multispace0,
        tag(")"),
        parse_function_return,
        multispace1,
        parse_sp_footer,
    ))(input)?;
    Ok((input, StoredFunction{
        name: header.0,
        replace_flag: header.1,
        parameters,
        function_return,
        body,
    }))
}

#[cfg(test)]
mod tests {
    use crate::*;
    #[test]
    fn it_works() {
        let sp = r#"
          -- 文字列が数値か判定する
          -- 引数
          --   p_src : 判定したい文字列
          --   p_min : 最小値
          --   p_max : 最大値
          -- 戻り値
          --   文字列が数値の場合かつ範囲内の場合はtrue
          CREATE OR REPLACE FUNCTION uv_is_number (
            p_src text
            ,p_min bigint DEFAULT -9223372036854775808
            ,p_max bigint DEFAULT 9223372036854775807
          ) RETURNS boolean AS $$
          DECLARE
            w_value bigint;
          BEGIN
            IF NOT uv_is_set(p_src) THEN
              RETURN FALSE;
            END IF;
            BEGIN
              w_value := p_src::bigint;
              IF p_min <= w_value AND w_value <= p_max THEN
                RETURN TRUE;
              ELSE
                RETURN FALSE;
              END IF;
            EXCEPTION
              WHEN invalid_text_representation THEN
                RETURN FALSE;
            END;
          END;
          $$ LANGUAGE plpgsql;
        "#;

        let (input, comments) = comments(sp).unwrap();
        println!("{:?}", comments);
        let (input, stored_function) = stored_procedure(input).unwrap();
        println!("{:?}",stored_function);
    }
}

結果

StoredFunction { 
name: "uv_is_number", 
replace_flag: true, 
parameters: [
  FuctionParameter { name: "p_src", sql_type: Text, default_value: None }, 
  FuctionParameter { name: "p_min", sql_type: BigInt, default_value: Some("-9223372036854775808") }, 
  FuctionParameter { name: "p_max", sql_type: BigInt, default_value: Some("9223372036854775807") }
], 
function_return: FunctionReturn { set_of: false, sql_type: Boolean }, 
body: "DECLARE\n            w_value bigint;\n          BEGIN\n            IF NOT uv_is_set(p_src) THEN\n              RETURN FALSE;\n            END IF;\n            BEGIN\n              w_value := p_src::bigint;\n              IF p_min <= w_value AND w_value <= p_max THEN\n                RETURN TRUE;\n              ELSE\n                RETURN FALSE;\n              END IF;\n            EXCEPTION\n              WHEN invalid_text_representation THEN\n                RETURN FALSE;\n            END;\n          END;\n          " 
}

まとめ

ストアードプロシージャの本体以外はパースできました。もちろん本体以外も色々バリエーションがあるのでもう少し改造は必要です。

肝心なのは本体で再帰的にパースする必要があったり、今まで以上に難しくなっていきます。がんばって実装していきます。