LoginSignup
12
5

More than 1 year has passed since last update.

@aoyagikouhei(青柳 康平)inユニークビジョン株式会社

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

Last updated at Posted at 2018-03-14

最新版

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);
}
12
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
5