use rusqlite::{params, Connection, Result};

fn main() -> Result<()> {
    // SQLiteデータベースに接続（ファイルが存在しない場合は作成される）
    let conn = Connection::open("sample.db")?;

    // テーブルを作成
    conn.execute(
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE
        )",
        [],
    )?;
/* 

    // データを挿入
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params!["Alice", "alice@example.com"],
    )?;
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params!["Bob", "bob@example.com"],
    )?;
*/

    insert_user_if_not_exists(&conn, "Alice", "alice@example.com")?;
    insert_user_if_not_exists(&conn, "Bob", "bob@example.com")?;
    insert_user_if_not_exists(&conn, "Alice", "alice@example.com")?; // 重複

    // データを取得
    /* 
    let mut stmt = conn.prepare("SELECT id, name, email FROM users")?;
    let user_iter = stmt.query_map([], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
        })
    })?;
    // データを表示
    println!("Users in the database:");
    for user in user_iter {
        println!("{:?}", user?);
    }
    */
    // データを取得して構造体に格納
    let users = fetch_users(&conn)?;
    for user in users {
        //println!("{:?}", user);
        // 各フィールドを展開して表示
        println!(
            "ID: {}, Name: {}, Email: {}",
            user.id, user.name, user.email
        );
    }
    Ok(())
}

// データを保持するための構造体
#[derive(Debug)]
struct User {
    id: i32,
    name: String,
    email: String,
}

fn insert_user_if_not_exists(conn: &Connection, name: &str, email: &str) -> Result<()> {
    if !is_duplicate(conn, "users", "email", email)? {
        insert_user(conn, name, email)?;
        println!("Inserted: {}, {}", name, email);
    } else {
        println!("Email '{}' already exists. Skipping insert.", email);
    }
    Ok(())
}
fn is_duplicate(conn: &Connection, table: &str, column: &str, value: &str) -> Result<bool> {
    let query = format!(
        "SELECT EXISTS(SELECT 1 FROM {} WHERE {} = ?1)",
        table, column
    );
    let exists: bool = conn.query_row(&query, params![value], |row| row.get(0))?;
    Ok(exists)
}
fn insert_user(conn: &Connection, name: &str, email: &str) -> Result<()> {
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params![name, email],
    )?;
    Ok(())
}

/// データベースから`User`構造体のリストを取得する関数
fn fetch_users(conn: &Connection) -> Result<Vec<User>> {
    let mut stmt = conn.prepare("SELECT id, name, email FROM users")?;
    let user_iter = stmt.query_map([], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
        })
    })?;

    // 結果をベクタに変換
    let users: Vec<User> = user_iter.filter_map(Result::ok).collect();
    Ok(users)
}

コメントの部分は、修正前のコードを残しています。
存在のチェックをしたり、関数化をしたり、ChatGPTにリクエストしています。
構造体のメンバーを使っていないことからワーニングが出ていたため、構造体に格納するよに指示しました。

Rustは構文チェックが厳しくエラーが出やすいのですが、ChatGPTの活用でストレスが少なくなります。

便利なのでPrismaでデータを作って、クエリのテストをしてみました。

schema.prisma

model jdata {
id String @id @default(uuid())
info Json
createdAt DateTime @default(now())
}

まずJSONの配列の中から目的のデータを取り出すことを試しました。ここでやりたかったことは、配列をレコードにすることです。

json_array_elements

データを取得してからコードで編集する方法もありますが、複雑なデータの場合はある程度DBクエリの段階でやっておくのもいいかと思ったからです。

prisma2=# select info->'arr' from jdata;
                                           ?column?                                            
-----------------------------------------------------------------------------------------------
 [{"id": "a", "name": "name01"}, {"id": "b", "name": "name02"}]
 
 [{"id": "c", "name": "name03"}, {"id": "d", "name": "name04"}, {"id": "e", "name": "name05"}]
 [{"id": "a", "name": "name01"}]
(4 rows)

prisma2=# select info->'arr'->0 from jdata;
           ?column?            
-------------------------------
 {"id": "a", "name": "name01"}
 
 {"id": "c", "name": "name03"}
 {"id": "a", "name": "name01"}
(4 rows)

prisma2=# select info->'arr'->1 from jdata;
           ?column?            
-------------------------------
 {"id": "b", "name": "name02"}
 
 {"id": "d", "name": "name04"}
 
(4 rows)

prisma2=# select info->'arr'->0 from jdata where info->'arr'->0->>'id' = 'a';
           ?column?            
-------------------------------
 {"id": "a", "name": "name01"}
 {"id": "a", "name": "name01"}
(2 rows)

prisma2=# select info->'arr'->0 from jdata where info->'arr'->0->>'id' = 'c';
           ?column?            
-------------------------------
 {"id": "c", "name": "name03"}
(1 row)

prisma2=# select info->'arr' from jdata where info->'arr'->0->>'id' = 'c';
                                           ?column?                                            
-----------------------------------------------------------------------------------------------
 [{"id": "c", "name": "name03"}, {"id": "d", "name": "name04"}, {"id": "e", "name": "name05"}]
(1 row)

prisma2=# select json_array_elements((info->'arr')::json) from jdata where info->'arr'->0->>'id' = 'c';
      json_array_elements      
-------------------------------
 {"id": "c", "name": "name03"}
 {"id": "d", "name": "name04"}
 {"id": "e", "name": "name05"}
(3 rows)

prisma2=# select * from(select json_array_elements((info->'arr')::json) as js from jdata where info->'arr'->0->>'id' = 'c') where js->>'name' = 'name05';
              js               
-------------------------------
 {"id": "e", "name": "name05"}
(1 row)

すぐ忘れてしまうのでメモでした。

関連）

Vector Data / PostgreSQL

参考）　https://www.sraoss.co.jp/tech-blog/pgsql/pgvector-intro/
環境）　PostgreSQL v16 / Mac(arm)

拡張機能はデフォルトで入っていたためcreate extensionコマンドで使えるようになりました。

ベクトルデータの類似性による分類は、下記の例などで使われます。

Support Vector Machine

mydb=# create extension vector;
CREATE EXTENSION
mydb=# \dx
                        List of installed extensions
  Name   | Version |   Schema   |                Description                 
---------+---------+------------+--------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 vector  | 0.5.0   | public     | vector data type and ivfflat access method
(2 rows)

mydb=# CREATE TABLE items (id bigserial PRIMARY KEY, vec vector(3));
CREATE TABLE
mydb=# insert into items (vec) select array[random() * 100, random() * 100, random() * 100] from generate_series(1, 1000);
INSERT 0 1000
mydb=# select * from items limit 5;
 id |              vec               
----+--------------------------------
  1 | [43.189995,31.358536,26.84495]
  2 | [37.70672,48.738,26.866821]
  3 | [96.311966,53.182575,9.592497]
  4 | [5.7535515,89.065216,36.55039]
  5 | [70.51249,35.026676,40.871254]
(5 rows)

mydb=# select vec, vec <#> '[1,2,3]' from items order by vec <#> '[1,2,3]' limit 10;
              vec              |      ?column?      
-------------------------------+--------------------
 [81.54763,94.854904,96.15609] | -559.7257080078125
 [82.12906,90.73807,96.854774] | -554.1695556640625
 [75.41077,94.3053,92.4181]    |  -541.275634765625
 [52.821335,96.40333,98.39411] |  -540.810302734375
 [94.34945,78.67333,96.08497]  | -539.9510498046875
 [79.69239,95.93435,88.05331]  | -535.7210083007812
 [91.20255,86.22478,88.92222]  | -530.4187622070312
 [43.513527,96.38977,96.76844] |  -526.598388671875
 [91.98024,67.0477,99.18572]   |       -523.6328125
 [72.57968,78.32932,97.56131]  | -521.9222412109375
(10 rows)

mydb=#

DBというと、どうしても条件の完全一致、部分的に一致したデータの抽出をイメージしがちでしたが、このような似たものをランク付けして抽出できるのは、最近のAIなどで使われそうな機能と思いました。

参考) 「最強の分析ツールと言われるWindow関数について私が学んだこと」
https://dev.classmethod.jp/articles/sql-window-function/

集約関数(Group by)は、一行にまとめますが、Window関数(Partition by)(分析関数ともいわれる)は行がそのままになります。

データ)
https://www.postgresqltutorial.com/postgresql-getting-started/postgresql-sample-database/

環境) Postgresql / Docker / WSL2
https://decode.red/net/archives/1589
ここで作成したDockerのPostgreSQL単体のもの。

サンプルデータのインポート。(便利!)

pg_restore -U postgres -d dvdrental -h localhost ./dvdrental.tar

まずは参考サイトと同じ検索でデータ確認します。

monthlyデータとdailyデータでgroup byをつかった例

最終的にやりたかったこと

月と日の合計を一緒に表示できました。Group Byでも同様なことはできますが、こちらの方が直感的です。

余談になりますが、下記に面白い記述がありました。

「うっかりチューリング完全になっちゃったもの」 より
https://cpplover.blogspot.com/2013/10/blog-post_20.html

“SQLは通常、チューリング完全とはみなされない。しかし、Common Table Expressionとwindow機能により、SQLはチューリング完全である。”

Group Byの使いにくさを、克服したWindow関数は言語的にも完成度を増したということでしょうか。

チューリングマシンについてか過去記事に以下のようなものを書きました。
「チューリングマシン」
https://decode.red/ed/archives/130
「Lazy K」

Lazy K

関数のみで全てを計算する究極の関数型言語”Lazy K”は、チューリングマシンの例として優れていると思います。