DBeaver の接続設定情報、ユーザー名とパスワードを参照する

DBクライアントツールとして皆大好き、DBeaver を使っていて設定したユーザ、パスワードを忘れてしまい
暗号化されたパスワードを参照したい時、どうしようと困ったことがある。
暗号のキーを知っていれば復号できる。以下、DBeaver内部にコードから知ることができて、
https://github.com/dbeaver/dbeaver/blob/57cec8ddfdbbf311261ebd0c7f957fdcd80a085f/plugins/org.jkiss.dbeaver.model/src/org/jkiss/dbeaver/model/impl/app/DefaultSecureStorage.java#L31
どうやら、AES暗号、パディングは、PKCS5 みたいなので、
暗号化されたファイルのパスを与えて読み込ませて復号する処理のクラスを用意した。

DBeaver設定の接続情報ユーザ、パスワードを暗号化したファイルは、Windows のユーザディレクトリの以下の場所に、
　　C:\Users\${userName}\AppData\Roaming\DBeaverData\workspace6\General\.dbeaver
　　の下、
credentials-config.json　が作成されているのでこれをこのクラスで処理する。

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.security.InvalidAlgorithmParameterException;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.util.Map;
import java.util.Scanner;
import javax.crypto.Cipher;
import javax.crypto.CipherInputStream;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.SecretKey;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.SecretKeySpec;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
/**
 * DBeaver credentials-config.json の解析 
 */
public class DbeaverParse{
    private final byte[] LOCAL_KEY_CACHE 
    = new byte[]{ -70, -69, 74, -97, 119, 74, -72, 83, -55, 108, 45, 101, 61, -2, 84, 74 };
    private Path configpath;
    
    public DbeaverParse(String credentialsConfigPath) {
        configpath = Paths.get(credentialsConfigPath);
    }
    
    public String decrypt() throws InvalidAlgorithmParameterException, InvalidKeyException, IOException
    , NoSuchPaddingException, NoSuchAlgorithmException {
        byte[] contents = Files.readAllBytes(configpath);
        try (InputStream byteStream = new ByteArrayInputStream(contents)) {
            byte[] fileIv = new byte[16];
            byteStream.read(fileIv);
            Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
            SecretKey aes = new SecretKeySpec(LOCAL_KEY_CACHE, "AES");
            cipher.init(Cipher.DECRYPT_MODE, aes, new IvParameterSpec(fileIv));
            try(CipherInputStream cipherIn = new CipherInputStream(byteStream, cipher);
                 Scanner scanner = new Scanner(cipherIn).useDelimiter("\\A")){
                return scanner.hasNext() ? scanner.next() : "";
            }
        }
    }
    public String prettyJsonString(String str) {
        try{
            ObjectMapper mapper = new ObjectMapper();
            return new ObjectMapper().writerWithDefaultPrettyPrinter()
                    .writeValueAsString(mapper.readValue(str, Map.class));
        }catch(JsonProcessingException e){
            throw new RuntimeException(e.getMessage(), e);
        }
    }
}

復号した結果Json文字列が、フラットで読みにくいかもしれないので、Jackson のJsonライブラリで、
整形できるようにしておく。
Jackson は、Maven で以下のように取得しておくと良いだろう

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.13.2.1</version>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-annotations</artifactId>
    <version>2.13.2</version>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-core</artifactId>
    <version>2.13.2</version>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.datatype</groupId>
    <artifactId>jackson-datatype-jsr310</artifactId>
    <version>2.13.2</version>
</dependency>

DbeaverParse の 呼び出し

try{
    DbeaverParse dbeaverParse = new DbeaverParse("C:/Users/XXXX/AppData/Roaming/DBeaverData/workspace6/General/.dbeaver/credentials-config.json");
    
    String json = dbeaverParse.decrypt();
    
    String jsonPretty =  dbeaverParse.prettyJsonString(json);

    System.out.println(jsonPretty);

}catch(InvalidKeyException | InvalidAlgorithmParameterException | NoSuchPaddingException | NoSuchAlgorithmException | IOException e){
    e.printStackTrace();
}

おそらくPythonでも書けるだろう。

複数の文字列のsplit ⇒ 行処理するSQL （ORACLE）

先日、Oracleで１つにカンマ区切り文字列を行変換する例を書いた
文字列のsplit ⇒ 行処理するSQL （ORACLE） - Oboe吹きプログラマの黙示録

１つではなく、複数のカンマ区切り文字列から、TABLE を作成する方法は、以下のように、
TABLE(CAST(MULTISET( 区切りレベル CONNECT BY REGEXP_SUBSTR 対象を
各々に適用させれば良い。

例）

WITH prefcture_strings AS (
    SELECT '東京,神奈川,千葉,茨城' AS value FROM dual
), prfctcd_strings AS (
    SELECT '13,14,12,08' AS value FROM dual
), point_strings AS (
    SELECT '100,80,60,55' AS value FROM dual
), splits AS (
     SELECT lines.column_value AS ROWNO
    , TRIM(REGEXP_SUBSTR(prefcture_strings.value,'[^,]+', 1, lines.column_value))  AS PREFECTURE
    , TRIM(REGEXP_SUBSTR(prfctcd_strings.value,'[^,]+', 1, lines.column_value))    AS PRFCTCD
    , TRIM(REGEXP_SUBSTR(point_strings.value,'[^,]+', 1, lines.column_value))      AS POINT
    FROM prefcture_strings, prfctcd_strings, point_strings
        ,TABLE(CAST(MULTISET(SELECT LEVEL FROM dual
CONNECT BY REGEXP_SUBSTR(prefcture_strings.value, '[^,]+', 1, LEVEL) IS NOT NULL) AS sys.odciNumberList
     )) lines
)
SELECT ROWNO
       , PREFECTURE
       , PRFCTCD
       , POINT
FROM splits

FROM句で対象を並べ、REGEXP_SUBSTR で各カンマ区切り文字列 FROM dual を指定する。
splits テーブルSELECT結果は以下のようになる。

ROWNO	PREFECTURE	PRFCTCD	POINT
1	東京	13	100
2	神奈川	14	80
3	千葉	12	60
4	茨城	08	55

区切りの数が全て同じであることが前提である。

UNPIVOTで格納される値を日本語にする【Oracle】

以下のテーブルがある。
JOBROUTE テーブル

id	STATION	CAR	TRAIN	WALK
1	新宿	5	12	120
2	渋谷	7	18	130

このCAR, TRAIN, WALK 列をUNPIVOTする。

SELECT 
     id, STATION, M_TYPE, T_MINUTES
FROM JOBROUTE 
UNPIVOT(T_MINUTES FOR M_TYPE IN(CAR , TRAIN , WALK))

結果

id	STATION	M_TYPE	T_MINUTES
1	新宿	CAR	5
1	新宿	TRAIN	12
1	新宿	WALK	120
2	渋谷	CAR	7
2	渋谷	TRAIN	18
2	渋谷	WALK	130

UNPIVOT で作成される列名を日本語にしたい。
CAR ⇒ 車
TRAIN ⇒ 電車
WALK ⇒ 徒歩
というように。。。

安全で確実な方法は、SELECT句でCASE文を書く方法

SELECT 
      id
     ,STATION
     ,CASE M_TYPE
          WHEN 'CAR' THEN '車'
          WHEN 'TRAIN' THEN '電車'
          WHEN 'WALK' THEN '徒歩'
      END AS M_TYPE
     ,T_MINUTES
FROM JOBROUTE 
UNPIVOT(T_MINUTES FOR M_TYPE IN(CAR , TRAIN , WALK))

SELECTでCASE式を避ける方法、一旦サブクエリになってしまうが、

WITH TJOBROUTE AS (
    SELECT 
      id
     ,STATION
     ,CAR       AS "車"
     ,TRAIN     AS "電車"
     ,WALK      AS "徒歩"
     ,T_MINUTES
FROM JOBROUTE 
)
SELECT 
      id
     ,STATION
     ,M_TYPE
     ,T_MINUTES
FROM TJOBROUTE 
UNPIVOT(T_MINUTES FOR M_TYPE IN("車", "電車", "徒歩"))

ここで、注意しなければならないのは、
Oracle は、AS 句で日本語など２バイト文字を使う場合は、シングルクォートではなく、
ダブルクォーテーションを使うことである。

id	STATION	M_TYPE	T_MINUTES
1	新宿	車	5
1	新宿	電車	12
1	新宿	徒歩	120
2	渋谷	車	7
2	渋谷	電車	18
2	渋谷	徒歩	130

文字列のsplit ⇒ 行処理するSQL （PostgreSQL）

文字列のsplit ⇒ 行処理するSQL （ORACLE） - Oboe吹きプログラマの黙示録
を書いたので、PostgreSQL で同じことをしてみる。

方法は２通り。

string_to_array() を使う。

WITH strings AS (
    SELECT UNNEST(string_to_array('A,B,C', ',')) AS split_value
)
SELECT 
    row_number() OVER () AS position
  , split_value
  , COUNT(*) OVER()      AS splitlength
FROM strings

string_to_table() を使う

WITH strings AS (
    SELECT string_to_table('A,B,C', ',') AS  split_value
)
SELECT 
    row_number() OVER () AS position
  , split_value
  , COUNT(*) OVER()      AS splitlength
FROM strings

結果は、

position	split_value	splitlength
1	A	3
2	B	3
3	C	3

文字列のsplit ⇒ 行処理するSQL （ORACLE）

UNPIVOT に近い、わざわざSQLで行うことがナンセンスな気もしたが、
split 対象の文字列を mybatis などから動的パラメータとして指定して結果を他のテーブルと結合して
処理するＳＱＬなど応用範囲は広がると思った。

Oracle の場合のサンプル

’A,B,C' をカンマ区切りで分割、

WITH strings AS (
    SELECT
        'A,B,C' AS value
    FROM dual
)
, splits AS (
    SELECT
        lines.column_value AS position
        , REGEXP_SUBSTR(strings.value,'[^,]+', 1, lines.column_value) AS split_value
        , COUNT(*) OVER()  AS splitlength
    FROM strings
        ,TABLE(CAST(MULTISET(SELECT LEVEL FROM dual
 CONNECT BY REGEXP_SUBSTR(strings.value, '[^,]+', 1, LEVEL) IS NOT NULL) AS sys.odciNumberList
                   )
              ) lines
)
SELECT * FROM splits

結果、、

position	split_value	splitlength
1	A	3
2	B	3
3	C	3

WITH句を使ったINSERTの書き方

WITH句を使った検索クエリは、

WITH  xxxx AS (
    ～
)
SELECT * FROM xxxx

のように、WITH句の後で、SELECT ～ FROM with句で定義した名称
と書くものだが、
INSERT INTO ～ SELECT で使用する場合、
INSERT INTO の前に書くのか？、後ろに書くのか？

どうもデータベースによって違うようである。
PostgreSQL は、
マニュアル
https://www.postgresql.jp/docs/17/queries-with.html#QUERIES-WITH-MODIFYING
に書いてあるように、 INSERT INTO の前に書く
サンプル

WITH temp AS (
   SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
   FROM TEST_USERS
   WHERE AGE > 29
) 
INSERT INTO CUSTOMERS (
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
)
SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
FROM GUESTS a 
INNER JOIN temp b ON a.LAST_NAME = b.LAST_NAME

しかし、Oracle ではこれはエラーになってしまう。
WITH句はSELECTの前、
INSERT INTO と SELECT の間に書かないと Oracle では動かない。

INSERT INTO CUSTOMERS (
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
)
WITH temp AS (
   SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
   FROM TEST_USERS
   WHERE AGE > 29
) 
SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
FROM GUESTS a 
INNER JOIN temp b ON a.LAST_NAME = b.LAST_NAME

トリッキーな、SELECT * FROM dual

Oracleでは、

SELECT * FROM dual

は、列名＝'DUMMY' 値＝'X'の1行を返す。

PostgreSQL では、、、
dual は存在しないのでFROM句を省略するのだが、

SELECT * 

とすると、
　　SQLエラー [42601]: ERROR: テーブル指定のないSELECT *は無効です
になる
Oracleと同じことをするには、WITH句でdualを用意する

WITH dual AS(
   SELECT 'X' AS DUMMY
)
SELECT * FROM dual

これでOracleと同じことができる。

MySQLでは、

SELECT 1 FROM dual

は、列名＝'1' 値＝1 が返ってくるしFROMを省略して

SELECT 1

でも同じである。

ただ上に書いた * （アスタリスク）はどうなるかというと、、

SELECT *

や、

SELECT * FROM dual

は、
　　SQLエラー [1096] [HY000]: No tables used
となる。
でも、

 SELECT COUNT(*) AS cnt  FROM DUAL

や、

SELECT COUNT(*) AS cnt

は、きちんと COUNT = 1 が返ってくる