RustとPostgreSQLで色々な型をやりとりしてみた(postgres 0.17対応)

最新版

RustとPostgreSQLで色々な型をやりとりしてみた(NUMERIC対応)

目的

RustでPostgreSQLの型をやりとりしてみたという記事ですが、postgresの0.17からだいぶかわったので、修正してみました。
以前の比べて、配列、Option型のサポートが入り、関連するcrateが新しくなりました。

以前の記事もまだ0.14系が現役だと思いますので、残しておきます。

プログラム

Cargo.toml

[package]
name = "pg"
version = "0.1.0"
edition = "2018"

[dependencies]
bit-vec = "^0.6"
chrono = "^0.4"
eui48 = "^0.4"
geo-types = "^0.4"
r2d2_postgres = "^0.16"
serde_json = "^1.0"
uuid = { version = "^0.8", features = ["serde", "v4"] }

[dependencies.postgres]
version = "^0.17"
features = [
    "with-bit-vec-0_6",
    "with-chrono-0_4",
    "with-eui48-0_4",
    "with-geo-types-0_4",
    "with-serde_json-1",
    "with-uuid-0_8",
]

main.rs

use bit_vec::BitVec;
use chrono::prelude::*;
use eui48::{MacAddress, Eui48};
use geo_types::{
    Coordinate,
    Point,
    Rect,
    LineString,
};
use r2d2_postgres::{
    r2d2::{
        Pool,
    },
    postgres::{
        NoTls,
    },
    PostgresConnectionManager,
};
use serde_json::Value;
use std::{
    collections::{
        HashMap,
    },
    net::{
        IpAddr,
        Ipv4Addr,
    },
    time::{
        SystemTime,
    },
};
use uuid::Uuid;

#[derive(Debug, PartialEq)]
struct Data {
    bool_val: bool,
    bool_array_val: Vec<bool>,
    bool_option_some_val: Option<bool>,
    bool_option_none_val: Option<bool>,
    char_val: i8,
    smallint_val: i16,
    int_val: i32,
    oid_val: u32,
    bigint_val: i64,
    real_val: f32,
    double_val: f64,
    text_val: String,
    bytes_val: Vec<u8>,
    hstore_val: HashMap<String, Option<String>>,
    system_time_val: SystemTime,
    inet_val: IpAddr,
    timestamp_val: NaiveDateTime,
    timestamptz_val: DateTime<Utc>,
    date_val: NaiveDate,
    time_val: NaiveTime,
    macaddr_val: MacAddress,
    point_val: Point<f64>,
    box_val: Rect<f64>,
    path_val: LineString<f64>,
    jsonb_val: Value,
    uuid_val: Uuid,
    varbit_val: BitVec,
}

fn main() {
    let manager = PostgresConnectionManager::new(
        "postgres://user:pass@localhost:5432/test".parse().unwrap(),
        NoTls,
    );
    let pool = Pool::new(manager).unwrap();
    let mut conn = pool.get().unwrap();
    let sql = r#"
        SELECT
            $1::BOOL AS bool_val
            ,$2::BOOL[] AS bool_array_val
            ,$3::BOOL AS bool_option_some_val
            ,$4::BOOL AS bool_option_none_val
            ,$5::"char" AS char_val
            ,$6::SMALLINT AS smallint_val
            ,$7::INT AS int_val
            ,$8::OID AS oid_val
            ,$9::BIGINT AS bigint_val
            ,$10::REAL AS real_val
            ,$11::DOUBLE PRECISION AS double_val
            ,$12::TEXT AS text_val
            ,$13::BYTEA AS bytes_val
            ,$14::HSTORE AS hstore_val
            ,$15::TIMESTAMPTZ AS system_time_val
            ,$16::INET AS inet_val
            ,$17::TIMESTAMP AS timestamp_val
            ,$18::TIMESTAMPTZ AS timestamptz_val
            ,$19::DATE AS date_val
            ,$20::TIME AS time_val
            ,$21::MACADDR AS macaddr_val
            ,$22::POINT AS point_val
            ,$23::BOX AS box_val
            ,$24::PATH AS path_val
            ,$25::JSONB AS jsonb_val
            ,$26::UUID AS uuid_val
            ,$27::VARBIT AS varbit_val
    "#;
    let stmt = conn.prepare(sql).unwrap();
    let data = Data {
        bool_val: true,
        bool_array_val: vec![true, false],
        bool_option_some_val: Some(true),
        bool_option_none_val: None,
        char_val: 1,
        smallint_val: 2,
        int_val: 3,
        oid_val: 4,
        bigint_val: 5,
        real_val: 6.1,
        double_val: 7.1,
        text_val: "予定表〜①ﾊﾝｶｸだ".to_string(),
        bytes_val: vec![240, 159, 146, 150],
        hstore_val: {
            let mut hstore_val = HashMap::new();
            hstore_val.insert("key".to_string(), Some("value".to_string()));
            hstore_val
        },
        system_time_val: SystemTime::UNIX_EPOCH, // SystemTime::now()は精度の差で一致しないが使える。
        inet_val: IpAddr::V4(Ipv4Addr::new(127, 0, 0, 1)),
        timestamp_val: NaiveDate::from_ymd(2001, 2, 3).and_hms(4, 5, 6),
        timestamptz_val: Utc.ymd(2001, 2, 3).and_hms_milli(4, 5, 6, 7),
        date_val: NaiveDate::from_ymd(2002, 3, 4),
        time_val: NaiveTime::from_hms_milli(8, 59, 59, 100),
        macaddr_val: {
            let eui: Eui48 = [ 0x12, 0x34, 0x56, 0xAB, 0xCD, 0xEF ];
            MacAddress::new( eui )
        },
        point_val: Point::new(1.234, 2.345),
        box_val: Rect::new(
            Coordinate { x: 0., y: 0. },
            Coordinate { x: 10., y: 20. },
        ),
        path_val: LineString(vec![
            Coordinate { x: 0., y: 0. },
            Coordinate { x: 10., y: 20. },]),
        jsonb_val: {
            let json_data = r#"{
                "name" : "予定表〜①ﾊﾝｶｸだ",
                "age" : 92233720368547758070
            }"#;
            serde_json::from_str(json_data).unwrap()
        },
        uuid_val: Uuid::new_v4(),
        varbit_val: {
            let mut varbit_val = BitVec::from_elem(10, false);
            varbit_val.set(2, true);
            varbit_val
        },
    };
    let rows = conn.query(&stmt, &[
        &data.bool_val,
        &data.bool_array_val,
        &data.bool_option_some_val,
        &data.bool_option_none_val,
        &data.char_val,
        &data.smallint_val,
        &data.int_val,
        &data.oid_val,
        &data.bigint_val,
        &data.real_val,
        &data.double_val,
        &data.text_val,
        &data.bytes_val,
        &data.hstore_val,
        &data.system_time_val,
        &data.inet_val,
        &data.timestamp_val,
        &data.timestamptz_val,
        &data.date_val,
        &data.time_val,
        &data.macaddr_val,
        &data.point_val,
        &data.box_val,
        &data.path_val,
        &data.jsonb_val,
        &data.uuid_val,
        &data.varbit_val,
    ]).unwrap();
    let row = rows.get(0).unwrap();
    let res = Data {
        bool_val: row.get("bool_val"),
        bool_array_val: row.get("bool_array_val"),
        bool_option_some_val: row.get("bool_option_some_val"),
        bool_option_none_val: row.get("bool_option_none_val"),
        char_val: row.get("char_val"),
        smallint_val: row.get("smallint_val"),
        int_val: row.get("int_val"),
        oid_val: row.get("oid_val"),
        bigint_val: row.get("bigint_val"),
        real_val: row.get("real_val"),
        double_val: row.get("double_val"),
        text_val: row.get("text_val"),
        bytes_val: row.get("bytes_val"),
        hstore_val: row.get("hstore_val"),
        system_time_val: row.get("system_time_val"),
        inet_val: row.get("inet_val"),
        timestamp_val: row.get("timestamp_val"),
        timestamptz_val: row.get("timestamptz_val"),
        date_val: row.get("date_val"),
        time_val: row.get("time_val"),
        macaddr_val: row.get("macaddr_val"),
        point_val: row.get("point_val"),
        box_val: row.get("box_val"),
        path_val: row.get("path_val"),
        jsonb_val: row.get("jsonb_val"),
        uuid_val: row.get("uuid_val"),
        varbit_val: row.get("varbit_val"),
    };
    assert_eq!(data, res);
}

目的(postgres 0.14系)

RustでPostgreSQLの型をやりとりしてみました。特に自分はJSONB型、配列型をよく使うので詳しく知りたかったです。
また接続ではr2d2のコネクションプールを利用していますが、これも使ってみたかったものです。

型

詳細はrust-postgresのgithubにある通りです。

BOOL, "char", SMALLINT, INT, OID, BIGINT, REAL, DOUBLE PRECISION, TEXT, BYTEA, TIMESTAMP, TIMESTAMPTZ, DATE, TIME, UUID, BIT VARYING, HSTORE, MACADDR, POINT, BOX, PATH, INT[]

はまりポイント

uuidはversion0.5系でないと動きません。
geoはversion0.4系でないと動きません。
postgres_rangeは組み込むとコンパイルに失敗するので諦めました。
#コード

Cargo.toml

[package]
name = "pg_test"
version = "0.1.0"

[dependencies]
bit-vec = "*"
chrono = "*"
eui48 = "*"
geo = "~0.4"
postgres_array = "*"
r2d2 = "*"
r2d2_postgres = "*"
serde_json = "*"
uuid = { version = "~0.5", features = ["v4"] }

[dependencies.postgres]
version = "*"
features = [
    "with-bit-vec",
    "with-chrono",
    "with-eui48",
    "with-geo",
    "with-serde_json",
    "with-uuid"
]

main.rs

extern crate bit_vec;
extern crate chrono;
extern crate eui48;
extern crate geo;
extern crate postgres_array;
extern crate r2d2;
extern crate r2d2_postgres;
extern crate serde_json;
extern crate uuid;

use bit_vec::BitVec;
use chrono::prelude::*;
use chrono::naive::{NaiveDateTime, NaiveDate, NaiveTime};
use eui48::{MacAddress, Eui48};
use geo::{Point, Bbox, LineString};
use postgres_array::array::Array;
use std::collections::HashMap;
use uuid::Uuid;

#[derive(Debug)]
struct Data {
    bool_val: bool,
    char_val: i8,
    smallint_val: i16,
    int_val: i32,
    oid_val: u32,
    bigint_val: i64,
    real_val: f32,
    double_val: f64,
    str_val: String,
    bytes_val: Vec<u8>,
    json_val: serde_json::Value,
    timestamp_val: NaiveDateTime,
    timestamptz_val: DateTime<Local>,
    date_val: NaiveDate,
    time_val: NaiveTime,
    uuid_val: Uuid,
    bitvec_val: BitVec,
    hstore_val: HashMap<String, Option<String>>,
    macaddr_val: MacAddress,
    point_val: Point<f64>,
    box_val: Bbox<f64>,
    path_val: LineString<f64>,
    intarray_val: Array<i32>,
}

fn main() {
    let manager = r2d2_postgres::PostgresConnectionManager::new(
        "postgres://localhost:5432/test", 
        r2d2_postgres::TlsMode::None).unwrap();
    let pool = r2d2::Pool::new(manager).unwrap();
    let conn = pool.get().unwrap();
    let sql = r#"
        SELECT
            $1::BOOL AS bool_val
            ,$2::"char" AS char_val
            ,$3::SMALLINT AS smallint_val
            ,$4::INT AS int_val
            ,$5::OID AS oid_val
            ,$6::BIGINT AS bigint_val
            ,$7::REAL AS real_val
            ,$8::DOUBLE PRECISION AS double_val
            ,$9::TEXT AS str_val
            ,$10::BYTEA AS bytes_val
            ,$11::JSONB AS json_val
            ,$12::TIMESTAMP AS timestamp_val
            ,$13::TIMESTAMPTZ AS timestamptz_val
            ,$14::DATE AS date_val
            ,$15::TIME AS time_val 
            ,$16::UUID AS uuid_val
            ,$17::BIT VARYING AS bitvec_val
            ,$18::HSTORE AS hstore_val
            ,$19::MACADDR AS macaddr_val
            ,$20::POINT AS point_val
            ,$21::BOX AS box_val
            ,$22::PATH AS path_val
            ,$23::INT[] AS intarray_val
    "#;
    let stmt = conn.prepare_cached(sql).unwrap();

    let data = Data {
        bool_val: true,
        char_val: 1,
        smallint_val: 2,
        int_val: 3,
        oid_val: 4,
        bigint_val: 5,
        real_val: 6.6,
        double_val: 7.7,
        str_val: "予定表〜①ﾊﾝｶｸだ".to_string(),
        bytes_val: vec![240, 159, 146, 150],
        json_val: {
            let json_data = r#"{
                "name" : "予定表〜①ﾊﾝｶｸだ",
                "age" : 92233720368547758070
            }"#;
            serde_json::from_str(json_data).unwrap()
        },
        timestamp_val: NaiveDate::from_ymd(2001, 2, 3).and_hms(4, 5, 6),
        timestamptz_val: Local.ymd(2001, 2, 3).and_hms_milli(4, 5, 6, 7),
        date_val: NaiveDate::from_ymd(2002, 3, 4),
        time_val: NaiveTime::from_hms_milli(8, 59, 59, 1_000),
        uuid_val: Uuid::new_v4(),
        bitvec_val: {
            let mut bitvec_val = BitVec::from_elem(10, false);
            bitvec_val.set(2, true);
            bitvec_val
        },
        hstore_val: {
            let mut hstore_val: HashMap<String, Option<String>> = HashMap::new();
            hstore_val.insert("a".to_string(), Some("abc".to_string()));
            hstore_val.insert("b".to_string(), Some("efg".to_string()));
            hstore_val
        },
        macaddr_val: {
            let eui: Eui48 = [ 0x12, 0x34, 0x56, 0xAB, 0xCD, 0xEF ];
	        MacAddress::new( eui )
        },
        point_val: Point::new(1.234, 2.345),
        box_val: Bbox { 
            xmin: 1.1,
            xmax: 2.2,
            ymin: 3.3,
            ymax: 4.4,
        },
        path_val: LineString(vec![Point::new(1.234, 2.345), Point::new(3.456, 4.567)]),
        intarray_val: Array::from_vec(vec![0i32, 1, 2, 3], 0),
    };

    let rows = stmt.query(&[
        &data.bool_val,
        &data.char_val,
        &data.smallint_val,
        &data.int_val,
        &data.oid_val,
        &data.bigint_val,
        &data.real_val,
        &data.double_val,
        &data.str_val,
        &data.bytes_val,
        &data.json_val,
        &data.timestamp_val,
        &data.timestamptz_val,
        &data.date_val,
        &data.time_val,
        &data.uuid_val,
        &data.bitvec_val,
        &data.hstore_val,
        &data.macaddr_val,
        &data.point_val,
        &data.box_val,
        &data.path_val,
        &data.intarray_val,
    ]).unwrap();
    
    let row = rows.get(0);
    let res = Data {
        bool_val: row.get("bool_val"),
        char_val: row.get("char_val"),
        smallint_val: row.get("smallint_val"),
        int_val: row.get("int_val"),
        oid_val: row.get("oid_val"),
        bigint_val: row.get("bigint_val"),
        real_val: row.get("real_val"),
        double_val: row.get("double_val"),
        str_val: row.get("str_val"),
        bytes_val: row.get("bytes_val"),
        json_val: row.get("json_val"),
        timestamp_val: row.get("timestamp_val"),
        timestamptz_val: row.get("timestamptz_val"),
        date_val: row.get("date_val"),
        time_val: row.get("time_val"),
        uuid_val: row.get("uuid_val"),
        bitvec_val: row.get("bitvec_val"),
        hstore_val: row.get("hstore_val"),
        macaddr_val: row.get("macaddr_val"),
        point_val: row.get("point_val"),
        box_val: row.get("box_val"),
        path_val: row.get("path_val"),
        intarray_val: row.get("intarray_val"),
    };
    println!("{:?}", res);
}