Help us understand the problem. What is going on with this article?

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

目的

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);
}
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした