Alopex DB v0.8.8チュートリアル 第5回：サーバーで開く

cargo install alopex-cli --version "=0.8.8" --locked
cargo install alopex-server --version "=0.8.8" --locked

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  views INTEGER,
  rating REAL
);
INSERT INTO articles VALUES (1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5);
INSERT INTO articles VALUES (2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5);
INSERT INTO articles VALUES (3, 'ベクトル検索の基礎', 'ren', 1500, 4.5);

alopex --batch --output json --data-dir ./srv-data sql -f setup.sql

from alopex import Database

db = Database.open("./srv-data")
print(db.execute_sql("SELECT id, title, views FROM articles ORDER BY id"))

[{'id': 1, 'title': 'Rustで書くLSMツリー', 'views': 1200},
 {'id': 2, 'title': 'SQLパーサをNimで実装する', 'views': 800},
 {'id': 3, 'title': 'ベクトル検索の基礎', 'views': 1500}]

data_dir = "./srv-data"
http_bind = "127.0.0.1:18080"
grpc_bind = "127.0.0.1:19090"
admin_bind = "127.0.0.1:18081"

alopex-server --config alopex.toml

INFO alopex_server::server: Cluster startup configuration applied
  cluster_mode=SingleNode node_id=local role=Gateway
  http_bind=127.0.0.1:18080 grpc_bind=127.0.0.1:19090 admin_bind=127.0.0.1:18081

curl -s -X POST http://127.0.0.1:18080/api/sql/query \
  -H 'Content-Type: application/json' \
  -d '{"sql":"SELECT id, title, views FROM articles ORDER BY id"}'

{"columns":[{"name":"id","data_type":"INTEGER"},
            {"name":"title","data_type":"TEXT"},
            {"name":"views","data_type":"INTEGER"}],
 "rows":[[{"Integer":1},{"Text":"Rustで書くLSMツリー"},{"Integer":1200}],
         [{"Integer":2},{"Text":"SQLパーサをNimで実装する"},{"Integer":800}],
         [{"Integer":3},{"Text":"ベクトル検索の基礎"},{"Integer":1500}]],
 "affected_rows":null}

curl -s -X POST http://127.0.0.1:18080/api/sql/query \
  -H 'Content-Type: application/json' \
  -d "{\"sql\":\"INSERT INTO articles VALUES (4, 'HTTPから追加した記事', 'sora', 500, 4.0)\"}"

{"columns":[],"rows":[],"affected_rows":1}

from alopex import Database

db = Database.open("./srv-data")
for row in db.execute_sql("SELECT id, title, author FROM articles ORDER BY id"):
    print(row)

{'id': 1, 'title': 'Rustで書くLSMツリー', 'author': 'mio'}
{'id': 2, 'title': 'SQLパーサをNimで実装する', 'author': 'mio'}
{'id': 3, 'title': 'ベクトル検索の基礎', 'author': 'ren'}
{'id': 4, 'title': 'HTTPから追加した記事', 'author': 'sora'}

curl -s http://127.0.0.1:18081/healthz

curl -s http://127.0.0.1:18080/api/admin/status

{"version": "0.8.8",
 "uptime_secs": 20,
 "cluster": {
   "mode": "single_node",
   "identity": {"node_id": "local", "role": "gateway",
                "lifecycle_state": "unconfigured"},
   "membership": {"source": "local_default", "members": []},
   "routing_capabilities": {"local_only": true,
                            "future_distributed_execution_required": true}}}

data_dir = "./srv-data"
http_bind = "127.0.0.1:18080"
grpc_bind = "127.0.0.1:19090"
admin_bind = "127.0.0.1:18081"

[cluster]
mode = "cluster_aware"
node_id = "node-1"
cluster_id = "blog-cluster"
advertised_endpoint = "127.0.0.1:19090"

failed to load config: invalid config: cluster.cluster_id is required when cluster.mode is cluster_aware

{"cluster": {
   "mode": "cluster_aware",
   "identity": {"node_id": "node-1", "cluster_id": "blog-cluster",
                "advertised_endpoint": "127.0.0.1:19090",
                "role": "gateway", "lifecycle_state": "active"},
   "membership": {"source": "chirps", "members": []},
   "routing_capabilities": {"local_only": true},
   "degraded": false}}

alopex --batch --output json --data-dir ./srv-data sql "SELECT io_stats()"

{"io_stats": "{\"wal_write_bytes\":0,\"sstable_read_bytes\":0,
               \"buffer_pool_hit_rate\":1,\"buffer_pool_size_bytes\":0,
               \"memtable_size_bytes\":1656,\"compaction_bytes_written\":0}"}

[dependencies]
alopex-embedded = "=0.8.8"

use alopex_embedded::{Database, SqlResult};
use std::path::Path;

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let db = Database::open(Path::new("./srv-data"))?;
    if let SqlResult::Query(q) = db.execute_sql("SELECT id, title, author FROM articles ORDER BY id")? {
        for row in &q.rows {
            let cells: Vec<String> = row.iter().map(|v| format!("{v:?}")).collect();
            println!("{}", cells.join(" | "));
        }
    }
    Ok(())
}

Integer(1) | Text("Rustで書くLSMツリー") | Text("mio")
Integer(2) | Text("SQLパーサをNimで実装する") | Text("mio")
Integer(3) | Text("ベクトル検索の基礎") | Text("ren")
Integer(4) | Text("HTTPから追加した記事") | Text("sora")

[dependencies]
ureq = { version = "2", features = ["json"] }
serde_json = "1"

use serde_json::json;

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let body: serde_json::Value = ureq::post("http://127.0.0.1:18080/api/sql/query")
        .send_json(json!({"sql": "SELECT id, title, views FROM articles ORDER BY id"}))?
        .into_json()?;

    let names: Vec<&str> = body["columns"].as_array().unwrap().iter()
        .map(|c| c["name"].as_str().unwrap()).collect();
    println!("{}", names.join(" | "));

    for row in body["rows"].as_array().unwrap() {
        let cells: Vec<String> = row.as_array().unwrap().iter()
            .map(|v| v.as_object().unwrap().values().next().unwrap().to_string())
            .collect();
        println!("{}", cells.join(" | "));
    }
    Ok(())
}

id | title | views
1 | "Rustで書くLSMツリー" | 1200
2 | "SQLパーサをNimで実装する" | 800
3 | "ベクトル検索の基礎" | 1500
4 | "HTTPから追加した記事" | 500