tsurugidb のデータ型

公式

Project Tsurugi

Available SQL features in Tsurugi

tsurugidb/docs/sql-features.md at master · project-tsurugi/tsurugidb
だいたいPostgreSQLと同じ

全部マシマシ

tgsql> create table test (id1 INT, id2 BIGINT, value1 REAL, value2 DOUBLE, value3 DECIMAL(10,2), value4 DECIMAL,
name1 CHAR(10) , name2 CHARACTER(10), name3 VARCHAR(12), name4 CHAR VARYING(12), name5 CHARACTER VARYING,bin1 BINARY(14), bin2 VARBINARY, bin3 BINARY VARYING, date1 DATE, time1 TIME, stamp1 TIMESTAMP, stamp2 TIMESTAMP WITH TIME ZONE);

tgsql> insert into test VALUES (1, 1234567890123, 12.34, 12345.6789, 12345678.90,4, 'a','b','c','d','e',X'aa',X'ba',X'ca',date'2000-01-01',time'00:00:01', timestamp'2001-01-01 11:22:33',timestamp with time zone'2000-01-02 11:22:33+09:00');

tgsql> select * from test;
[id1: INT4, id2: INT8, value1: FLOAT4, value2: FLOAT8, value3: DECIMAL, value4: DECIMAL, name1: CHARACTER, name2: CHARACTER, name3: CHARACTER, name4: CHARACTER, name5: CHARACTER, bin1: OCTET, bin2: OCTET, bin3: OCTET, date1: DATE, time1: TIME_OF_DAY, stamp1: TIME_POINT, stamp2: TIME_POINT_WITH_TIME_ZONE]
[1, 1234567890123, 12.34, 12345.6789, 12345678.90, 4, a , b , c, d, e, aa00000000000000000000000000, ba, ca, 2000-01-01, 00:00:01.000000000, 2001-01-01 11:22:33.000000000, 2000-01-02 02:22:33.000000000Z]
(1 row)

javaで書くとどうなる？

CREATE TABLE文で全部マシマシにする

Tsurugi DBにjavaプログラムからアクセスする(tsubakuro)｜nishimura
に記載した"drop文create文だけ版"の以下の記述だけを変更すればよいが、後々のことを考えてclass化する

元コード

String create = String.format("create table %s (id int primary key)", tableName);

対応コード

class ColumnDefinition {
    String name;
    String sqlType;

    public ColumnDefinition(String name, String sqlType) {
        this.name = name;
        this.sqlType = sqlType;
    }

    public String toSql() {
        return name + " " + sqlType;
    }

}
       List<ColumnDefinition> columns = List.of(
                new ColumnDefinition("id1", "INT PRIMARY KEY"),
                new ColumnDefinition("id2", "BIGINT"),
                new ColumnDefinition("value1", "REAL"),
                new ColumnDefinition("value2", "DOUBLE"),
                new ColumnDefinition("value3", "DECIMAL(10,2)"),
                new ColumnDefinition("value4", "DECIMAL"),
                new ColumnDefinition("name1", "CHAR(10)"),
                new ColumnDefinition("name2", "CHARACTER(10)"),
                new ColumnDefinition("name3", "VARCHAR(12)"),
                new ColumnDefinition("name4", "CHAR VARYING(12)"),
                new ColumnDefinition("name5", "CHARACTER VARYING"),
                new ColumnDefinition("bin1", "BINARY(14)"),
                new ColumnDefinition("bin2", "VARBINARY"),
                new ColumnDefinition("bin3", "BINARY VARYING"),
                new ColumnDefinition("date1", "DATE"),
                new ColumnDefinition("time1", "TIME"),
                new ColumnDefinition("stamp1", "TIMESTAMP"),
                new ColumnDefinition("stamp2", "TIMESTAMP WITH TIME ZONE"));
        String create = String.format(
                "CREATE TABLE %s (%s)",
                tableName,
                columns.stream().map(ColumnDefinition::toSql).collect(Collectors.joining(", ")));

INSERT文で全部マシマシにする

Tsurugi DB、sqlのinsertとkvsのputの性能を比較する(tsubakuro)｜nishimuraに記載した"insert文のみ(KVS)"の以下の記述だけを変更すればよいが、後々のことを考えてこれもclass化する(ただし確認できなかった、BINARY(14)、VARBINARY、BINARY VARYING、TIMESTAMP WITH TIME ZONEは除く)

元コード

record.add("id", 1);

対応コード

class ColumnDefinition {
    String name;
    String sqlType;
    Object value;

    public ColumnDefinition(String name, String sqlType, Object value) {
        this.name = name;
        this.sqlType = sqlType;
        this.value = value;
    }

    public String toSql() {
        return name + " " + sqlType;
    }

    public void addToRecordBuffer(RecordBuffer record) {
        if (value instanceof Integer) {
            record.add(name, (Integer) value);
        } else if (value instanceof Long) {
            record.add(name, (Long) value);
        } else if (value instanceof Float) {
            record.add(name, (Float) value);
        } else if (value instanceof Double) {
            record.add(name, (Double) value);
        } else if (value instanceof BigDecimal) {
            record.add(name, (BigDecimal) value);
        } else if (value instanceof String) {
            record.add(name, (String) value);
        } else if (value instanceof LocalDate) {
            record.add(name, (LocalDate) value);
        } else if (value instanceof LocalTime) {
            record.add(name, (LocalTime) value);
        } else if (value instanceof LocalDateTime) {
            record.add(name, (LocalDateTime) value);
        } else if (value instanceof OffsetDateTime) {
            record.add(name, (OffsetDateTime) value);
        } else {
            throw new IllegalArgumentException("Unsupported type: " + value.getClass());
        }

    }
}
       List<ColumnDefinition> columns = List.of(
            new ColumnDefinition("id1", "INT PRIMARY KEY", 1),
            new ColumnDefinition("id2", "BIGINT", (long) 1),
            new ColumnDefinition("value1", "REAL", 1.0f),
            new ColumnDefinition("value2", "DOUBLE", 1.0d),
            new ColumnDefinition("value3", "DECIMAL(10,2)", new BigDecimal("1")),
            new ColumnDefinition("value4", "DECIMAL", new BigDecimal("1")),
            new ColumnDefinition("name1", "CHAR(10)", "a"),
            new ColumnDefinition("name2", "CHARACTER(10)", "b"),
            new ColumnDefinition("name3", "VARCHAR(12)", "c"),
            new ColumnDefinition("name4", "CHAR VARYING(12)", "d"),
            new ColumnDefinition("name5", "CHARACTER VARYING", "e"),
            new ColumnDefinition("date1", "DATE", LocalDate.of(2001, 1, 1)),
            new ColumnDefinition("time1", "TIME", LocalTime.of(12, 1, 1)),
            new ColumnDefinition("stamp1", "TIMESTAMP", LocalDateTime.of(2001, 1, 1, 1, 1, 1))
        );
 
                for (ColumnDefinition column : columns) {
                    column.addToRecordBuffer(record);
                }

参考

Tsurugiデータ型(Hishidama's TsurugiDB SQL data type Memo)