DBeaver のSQL書式設定

その他

DBeaverを使っているのですが、
select などの文字を大文字にするか、小文字にするか、
これら、SQLの句を圧倒的に小文字に書く人が多いのですが、
個人的には、大文字派です。
DBeaverで自動で補完入力する時、小文字が厭で大文字にするなら、
「設定」画面、SQL書式設定で、次のように、Upper を選択します。

Spring DATA JPA Specification

Java

org.springframework.data.jpa.repository.JpaSpecificationExecutor

Specification を使って以下のメソッドで動的クエリを生成できる。

long count(Specification<T> spec)

boolean exists(Specification<T> spec)

List<T> findAll(Specification<T> spec)

Page<T> findAll(Specification<T> spec, Pageable pageable)

List<T> findAll(Specification<T> spec, Sort sort)

T findOne(Specification<T> spec)

でも、@Query で、native Query の使用と、Specification を同時には使えない。

CSV形式の１行分の文字列からList<String>への変換をinterface method にする。

Java

先日書いた、CSV形式の１行分の文字列からList<String>への変換 - Oboe吹きプログラマの黙示録
を単純に、interface のメソッドにする。

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.function.Function;

public interface Csvtolist extends Serializable{

    public static Function<String, List<String>> of(){
        return Csvtolist::create;
    }
    
    public static List<String> create(String str){
        char DELIMITER = ',';
        char CARRIAGE_RETURN = '\r';
        char NEWLINE = '\n';
        char DOUBLE_QUOTE = '"';
        if (str==null || str.length()==0){
            return Collections.emptyList();
        }
        List<String> tokens = new ArrayList<String>();
        StringBuilder tokenBuf = new StringBuilder();
        boolean insideDoubleQuote = false;
        boolean isDoubleQuoteEscapeActive = false;
        StringBuilder wspBuf = new StringBuilder();
        for(int ii=0; ii < str.length(); ii++){
            final char ch = str.charAt(ii);
            if (ch==CARRIAGE_RETURN || ch==NEWLINE){
                if (insideDoubleQuote){
                    tokenBuf.append(ch);
                }else{
                    throw new RuntimeException("unquoted "
                     + (ch=='\n' ? "newline" : "carriage return")
                     + " found at position #" + (ii+1));
                }
            }else if(ch==DOUBLE_QUOTE){
                if (insideDoubleQuote){
                    if (isDoubleQuoteEscapeActive){
                        tokenBuf.append(ch);
                        isDoubleQuoteEscapeActive = false;
                    }else if(((ii+1) < str.length()) && str.charAt(ii+1)==DOUBLE_QUOTE){
                        isDoubleQuoteEscapeActive = true;
                    }else{
                        insideDoubleQuote = false;
                    }
                }else{
                    insideDoubleQuote = true;
                    if (wspBuf.length() != 0){
                        if (tokenBuf.length() != 0){
                            tokenBuf.append(wspBuf);
                        }
                        wspBuf.delete(0, wspBuf.length());
                    }
                }
            }else{
                if (insideDoubleQuote){
                    tokenBuf.append(ch);
                }else{
                    if (ch==DELIMITER){
                        tokens.add(tokenBuf.toString());
                        tokenBuf.delete(0, tokenBuf.length());
                        wspBuf.delete(0, wspBuf.length());
                    }else if(Character.isWhitespace(ch)){
                         wspBuf.append(ch);
                    }else{
                        if (wspBuf.length() != 0){
                            if (tokenBuf.length() != 0){
                                tokenBuf.append(wspBuf);
                            }
                            wspBuf.delete(0, wspBuf.length());
                        }
                        tokenBuf.append(ch);
                    }
                }
            }
        }
        if (insideDoubleQuote){
            throw new RuntimeException("terminating double quote not found");
        }
        tokens.add(tokenBuf.toString());
        return tokens;
    }
}

of() で、Function<String, List<String>> を返すようにしているので、
Optional の map で使用できる。

List<String> list = Optional.ofNullable(str).map(Csvtolist.of()).orElse(List.of());

結局、以下のように書いても同じではあるけど。

List<String> list = Optional.ofNullable(str).map(Csvtolist::create).orElse(List.of());

GROUP_CONCAT からCSVにする。

SQL

MySQL の GROUP_CONCAT あるいは、PostgreSQL の STRING_AGG から、CSV１行を作る。
作ってSELECTした文字列は、先日書いた原始的なメソッド
CSV形式の１行分の文字列からList<String>への変換 - Oboe吹きプログラマの黙示録
を使って、List<String> を取得する。

任意の列で、GROUP BY して、GROUP_CONCAT ／ STRING_AGG で join する時に
RFC4180 準拠、カンマ区切り、ダブルクォーテーション括りにする。

MySQL の場合、、、

対象列が、NOT NULL制約がある場合、

CONCAT('"',
       GROUP_CONCAT(REGEXP_REPLACE(task_name, '"', '""')
                    ORDER BY create_date DESC 
                    SEPARATOR '","'),
       '"') AS task_name_csv

対象列が、NOT NULL制約がない場合、

CONCAT('"',
       GROUP_CONCAT(REGEXP_REPLACE(COALESCE(task_name, ''), '"', '""')
                    ORDER BY create_date DESC
                    SEPARATOR '","'),
       '"') AS task_name_csv

PostgreSQLの場合、、、

対象列が、NOT NULL制約がある場合、

'"' || STRING_AGG(REGEXP_REPLACE(task_name, '"', '""', 'g'), '","' ORDER BY create_date DESC)
    || '"' AS task_name_csv

対象列が、NOT NULL制約がない場合、

'"' || STRING_AGG(REGEXP_REPLACE(COALESCE(task_name, ''), '"', '""', 'g'), '","' ORDER BY create_date  DESC)
    || '"' AS task_name_csv

しかし、PostgreSQL には、列の型に、ARRAY型が存在するわけで、
PostgreSQL は、わざわざ、このようなことはしないで、ARRAY型にして SELECT すれば、
こんなCSVを生成した結果をSELECTで取り込んで、
先日書いたJavaのメソッドでリストにするなんてことをする必要性はない。

ARRAY_AGG(task_name ORDER BY create_date DESC)

あるいは、

ARRAY_AGG(COALESCE(task_name, '') ORDER BY create_date DESC)

GROUP_CONCAT結果をCSVにするなんて、MySQLだけしか有効性はないであろう。
=============================================================
ARRAY型で、思い起こせばこんなことを書いていた。。。
int[] → ArrayTypeHandler - Oboe吹きプログラマの黙示録
配列型に対するmybatis の TypeHandler よく使いそうなものを作った - Oboe吹きプログラマの黙示録
arraytypehandler · yipuran/yipuran-mybatis Wiki · GitHub

CSV形式の１行分の文字列からList<String>への変換

Java

RFC4180準拠、カンマ区切り、括り文字はダブルクォーテーションの１行分の文字列
から、List<String> に変換する原始的なロジックで書いたメソッドを
ある目的の為に書きました。
Java8以上のコードではなく、見にくくても敢えて原始的なロジックで書いています。
",,"や、”, ,” のように、カンマ区切り並んだ場合は、””空文字列をListに詰め込みます。

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public static List<String> csvlineToList(String line) {
    char DELIMITER = ',';
    char CARRIAGE_RETURN = '\r';
    char NEWLINE = '\n';
    char DOUBLE_QUOTE = '"';
    if (line==null || line.length()==0){
        return Collections.emptyList();
    }
    List<String> tokens = new ArrayList<String>();
    StringBuilder tokenBuf = new StringBuilder();
    boolean insideDoubleQuote = false;
    boolean isDoubleQuoteEscapeActive = false;
    StringBuilder wspBuf = new StringBuilder();
    for(int ii=0; ii < line.length(); ii++){
        final char ch = line.charAt(ii);
        if (ch==CARRIAGE_RETURN || ch==NEWLINE){
            if (insideDoubleQuote){
                tokenBuf.append(ch);
            }else{
                throw new RuntimeException("unquoted " + (ch=='\n' ? "newline" : "carriage return") +
                        " found at position #" + (ii+1));
            }
        }else if(ch==DOUBLE_QUOTE){
            if (insideDoubleQuote){
                if (isDoubleQuoteEscapeActive){
                    tokenBuf.append(ch);
                    isDoubleQuoteEscapeActive = false;
                }else if(((ii+1) < line.length()) && line.charAt(ii+1)==DOUBLE_QUOTE){
                    isDoubleQuoteEscapeActive = true;
                }else{
                    insideDoubleQuote = false;
                }
            }else{
                insideDoubleQuote = true;
                if (wspBuf.length() != 0){
                    if (tokenBuf.length() != 0){
                        tokenBuf.append(wspBuf);
                    }
                    wspBuf.delete(0, wspBuf.length());
                }
            }
        }else{
            if (insideDoubleQuote){
                tokenBuf.append(ch);
            }else{
                if (ch==DELIMITER){
                    tokens.add(tokenBuf.toString());
                    tokenBuf.delete(0, tokenBuf.length());
                    wspBuf.delete(0, wspBuf.length());
                }else if(Character.isWhitespace(ch)){
                     wspBuf.append(ch);
                }else{
                    if (wspBuf.length() != 0){
                        if (tokenBuf.length() != 0){
                            tokenBuf.append(wspBuf);
                        }
                        wspBuf.delete(0, wspBuf.length());
                    }
                    tokenBuf.append(ch);
                }
            }
        }
    }
    if (insideDoubleQuote){
        throw new RuntimeException("terminating double quote not found");
    }
    tokens.add(tokenBuf.toString());
    return tokens;
}

String の replaceAll で IllegalArgumentException

Java

"\"バックスラッシュへの変換や、"$" 文字を含む replacement への変換を
String の replaceAll で実行すると、
IllegalArgumentException: Illegal group reference
になるので、注意！
しかもこの例外は、replaceAll(String regex, String replacement) の
regex → replacement において、 "\" や "$" 文字へ実際に変換が
実行される時のみに発生する例外なので、とても見つけにくい例外だ。

うっかり、こういう replaceAll の使い方をしてしまう。。

そういう場合は、java.util.regex.Matcher の quoteReplacement を使う。

string.replaceAll("_", Matcher.quoteReplacement("$_"));

MySQL 照合順序の指定エラー Error Code: 1253

SQL

collate utf8_unicode_ci

utfbm4 のキャラクタコードに対してこの collate を指定するとエラーになる、
Error Code: 1253. COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'
以下でデーターベースがどうなっているか確認する

SHOW VARIABLES LIKE 'character_set_database';

+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.00 sec)

データベース、グローバレベルの collation を確認すると、

show global variables like 'collation_server';

+------------------------+--------------------+
| Variable_name          | Value              |
+------------------------+--------------------+
| character_set_database | utf8mb4_0900_ai_ci |
+------------------------+--------------------+

MySQL 5.7 のデフォルト・キャラクタコード を utf8mb4 にしていたら
デフォルトの collation は、utf8mb4_general_ci でした。
しかし、MySQL 8.0 以降は、デフォルト・キャラクタコード = utf8mb4 に対して
デフォルトの collation = utf8mb4_0900_ai_ci になります。

0900 は、Unicode のバージョン 9.00 を示してます。
_ai は、Accent Insensitive の略、アクセントの違いを区別しません。
_ci は、Case Insensitive の略、大文字小文字区別しない。

https://dev.mysql.com/doc/refman/8.0/ja/charset-collation-names.html
に、照合の命名規則が書いてあります。

サフィックス 意味
_ai アクセントを区別しない
_as アクセントを区別する
_ci 大文字小文字を区別しない
_cs Case-sensitive
_ks カナを区別する
_bin バイナリ

collation で大文字小文字を区別しない厳格なバイナリ照合なら utf8mb4_bin を使います。

utf8mb4_0900_ai_ci
'あ' → 'ア', 'ｱ' にマッチする
'は' → 'バ', 'パ' にマッチする
大文字小文字区別しない

utf8mb4_0900_as_ci
'あ' → 'ア', 'ｱ' にマッチする、
'は' → 'バ', 'パ' にマッチしない
大文字小文字区別しない

utf8mb4_0900_as_cs
'あ' → 'ア', 'ｱ' にマッチしない
'は' → 'バ', 'パ' にマッチしない
大文字小文字区別する

utf8mb4_0900_ai_cs という照合順序は、存在しません。
utf8mb4_0900_cs という照合順序は、存在しません。

utf8mb4_unicode_ci
'あ' → 'ア', 'ｱ' にマッチする
'は' → 'バ', 'パ' にマッチする
大文字小文字区別しない

utf8mb4_unicode_cs という照合順序は、存在しません。
utf8mb4_unicode_ai という照合順序は、存在しません。
utf8mb4_unicode_as という照合順序は、存在しません。
utf8mb4_unicode_ai_ci という照合順序は、存在しません。
utf8mb4_unicode_as_ci という照合順序は、存在しません。

utf8mb4_general_ci
'あ' → 'ア', 'ｱ' にマッチしない
'は' → 'バ', 'パ' にマッチしない
大文字小文字区別しない

utf8mb4_general_cs という照合順序は、存在しません。

また、MySQL のマニュアルに、キャラクタコードセット utf8 を例に以下の説明で記載されてます。
utf8_unicode_ci では、縮約および無視可能な文字もサポートされています。
utf8_general_ci は、拡張、縮小または無視可能な文字をサポートしないレガシー照合です。
文字間で 1 対 1 の比較しかできません。