PostgreSQL
Rust

RustとPostgreSQLで色々な型をやりとりしてみた

目的

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);
}