ソフトウェアエンジニアの技術ブログ：Software engineer tech blog

use regex::Regex;

fn main() {
    
    let text = "CREATE TABLE users (
        id integer,
        name text,
        age integer,
    );";
    println!("{}", text);

    let table_re = Regex::new(r"(?i)create\s+table\s+([a-zA-Z_][a-zA-Z0-9_]*)\s*\((?s)(.*?)\)").unwrap();

    if let Some(caps) = table_re.captures(text) {
        let table_name = &caps[1];
        let columns_str = &caps[2];
        println!("table name: {}", table_name);

        let column_re = Regex::new(r"(?i)([a-zA-Z_][a-zA-Z0-9_]*)\s+([a-zA-Z0-9()]+)").unwrap();

        for line in columns_str.lines() {
            if let Some(col_caps) = column_re.captures(line.trim()) {
                let column_name = &col_caps[1];
                let column_type = &col_caps[2];
                println!(" column name: {}, type: {}", column_name, column_type);
            }
        }
    } else {
        println!("CREATE TABLE 文ではありません");
    }
}

CREATE TABLE users (
id integer,
name text,
age integer,
);
table name: users
column name: id, type: integer
column name: name, type: text
column name: age, type: integer

これにprimary keyなどのconstrainも追加したい…

fn main() {
    
    let text = "CREATE TABLE users (
        id integer primarykey,
        name text not null,
        age integer
    );";
    println!("{}", text);

    let table_re = Regex::new(r"(?is)create\s+table\s+([a-zA-Z_][a-zA-Z0-9_]*)\s*\((.*?)\)").unwrap();

    if let Some(caps) = table_re.captures(text) {
        let table_name = &caps[1];
        let columns_str = &caps[2];
        println!("table name: {}", table_name);

        let column_re = Regex::new(r"(?i)^\s*([a-zA-Z_][a-zA-Z0-9_]*)\s+([a-zA-Z0-9()]+)(?:\s+(.*))?[,]?$").unwrap();

        for line in columns_str.lines() {

            let line = line.trim();
            if line.is_empty() {
                continue;
            }


            if let Some(col_caps) = column_re.captures(line) {
                let column_name = &col_caps[1];
                let column_type = &col_caps[2];
                let constraint = col_caps.get(3).map_or("", |m| m.as_str()).trim();
                println!(" column name: {}, type: {}, constraint: {}", column_name, column_type, if constraint.is_empty() { "None" } else { constraint });
            } else {
                println!(" ignored row: {}", line);
            }
        }
    } else {
        println!("CREATE TABLE 文ではありません");
    }
}

CREATE TABLE users (
id integer primarykey,
name text not null,
age integer
);
table name: users
column name: id, type: integer, constraint: primarykey,
column name: name, type: text, constraint: not null,
column name: age, type: integer, constraint: None

cargo.tomlでbincode = “1.3”を設定する

    fn save_to_binary_file(&self, path: &str) -> Result<(), String> {
        let encoded = bincode::serialize(self).map_err(|e| e.to_string())?;
        let mut file = File::create(path).map_err(|e| e.to_string())?;
        file.write_all(&encoded).map_err(|e| e.to_string())?;
        Ok(())
    }

    fn load_from_binary_file(path: &str) -> Result<Table, String> {
        let mut file = File::open(path).map_err(|e| e.to_string())?;
        let mut buffer = Vec::new();
        file.read_to_end(&mut buffer).map_err(|e| e.to_string())?;
        let table: Table = bincode::deserialize(&buffer).map_err(|e| e.to_string())?;
        Ok(table)
    }

fn main()

    let mut table = create_sample_table();

    table.save_to_binary_file("users.bin").expect("Failed to save binary");
    let loaded = Table::load_from_binary_file("users.bin").expect("Failed to load binary");
    println!("読み込んだtable: {:?}", loaded);

読み込んだtable: Table { name: “users”, columns: Columns { schema: {“name”: ColumnSchema { order: 1, column_type: Text, constraint: None }, “id”: ColumnSchema { order: 0, column_type: Integer, constraint: PrimaryKey }, “age”: ColumnSchema { order: 2, column_type: Integer, constraint: None }}, order: [“id”, “name”, “age”] }, rows: [[Integer(1), Text(“Alice”), Integer(30)], [Integer(2), Text(“Bob”), Integer(25)]] }

$ cat users.bin
usersidnameageidnameageAliceBob

impl Tableで、jsonの保存、読み取り関数を書く。export_to_json()でjson化を関数にしているので、それを実行して、write_allで書き込む。読み取りの場合は、read_to_string()

    fn save_to_file(&self, path: &str) -> Result<(), String> {
        let json = self.export_to_json();
        let serialized = serde_json::to_string_pretty(&json)
            .map_err(|e| e.to_string())?;

        let mut file = File::create(path).map_err(|e| e.to_string())?;
        file.write_all(serialized.as_bytes()).map_err(|e| e.to_string())?;

        Ok(())
    }

    fn load_from_file(path: &str) -> Result<Table, String> {
        let mut file = File::open(path).map_err(|e| e.to_string())?;
        let mut contents = String::new();
        file.read_to_string(&mut contents).map_err(|e| e.to_string())?;

        let json: JsonValue = serde_json::from_str(&contents).map_err(|e| e.to_string())?;

        Table::import_from_json(&json)
    }

### 保存、読み取りの実行
サンプルテーブルは関数で別に作っておく

fn create_sample_table() -> Table {
    let mut schema = HashMap::new();
    let mut order = vec![];

    let columns = vec![
        ("id", ColumnType::Integer, Constraint::PrimaryKey),
        ("name", ColumnType::Text, Constraint::None),
        ("age", ColumnType::Integer, Constraint::None),
    ];

    for (i, (name, col_type, constraint)) in columns.into_iter().enumerate() {
        schema.insert(name.to_string(), ColumnSchema {
            order: i,
            column_type: col_type,
            constraint,
        });
        order.push(name.to_string());
    }
    let columns = Columns { schema, order };

    let mut table = Table {
        name: "users".to_string(),
        columns: columns,
        rows: vec![],
    };

    let result = table.insert_row(vec![
        Value::Integer(1),
        Value::Text("Alice".to_string()),
        Value::Integer(30),
    ]);

    let result = table.insert_row(vec![
        Value::Integer(2),
        Value::Text("Bob".to_string()),
        Value::Integer(25),
    ]);

    table
}

    let mut table = create_sample_table();

    if let Err(e) = table.save_to_file("users.json") {
        eprintln!("保存に失敗しました: {}", e);
    }

    match Table::load_from_file("users.json") {
        Ok(loaded_table) => {
            println!("読み込み成功: {:?}", loaded_table);
        }
        Err(e) => {
            eprintln!("読み込み失敗: {}", e);
        }
    }

読み込み成功: Table { name: “users”, columns: Columns { schema: {“id”: ColumnSchema { order: 0, column_type: Integer, constraint: PrimaryKey }, “age”: ColumnSchema { order: 2, column_type: Integer, constraint: None }, “name”: ColumnSchema { order: 1, column_type: Text, constraint: None }}, order: [“id”, “name”, “age”] }, rows: [[Integer(1), Text(“Alice”), Integer(30)], [Integer(2), Text(“Bob”), Integer(25)]] }

### psqlのデータ保存
– 独自のフォーマットでファイルにテーブルデータを格納
– 各テーブルは内部的に「リレーション」として表され、base/ ディレクトリ以下にファイルとして存在
– PostgreSQL はデフォルトで 8KB 単位のページにデータを分割して保存

### mysqlのデータ保存
– 通常は /var/lib/mysql/ ディレクトリに保存されます。各データベースはサブディレクトリとして管理
– デフォルトでは1つの共有表領域ファイル（ibdata1）にすべてのデータを保存

どちらのDBMSも、保存しているファイルは バイナリ形式

#[derive(Debug, Clone)]
enum Condition {
    Equals(String, Value),
    NotEquals(String, Value),
    And(Box<Condition>, Box<Condition>),
    Or(Box<Condition>, Box<Condition>),
}

    fn row_matches_condition(&self, row: &Vec<Value>, condition: &Condition) -> bool {
        match condition {
            Condition::Equals(col, val) => {
                if let Some(schema) = self.columns.schema.get(col) {
                    row.get(schema.order) == Some(val)
                } else {
                    false
                }
            }
            Condition::NotEquals(col, val) => {
                if let Some(schema) = self.columns.schema.get(col) {
                    row.get(schema.order) != Some(val)
                } else {
                    false
                }
            }
            Condition::And(lhs, rhs) => {
                self.row_matches_condition(row, lhs) && self.row_matches_condition(row, rhs)
            }
            Condition::Or(lhs, rhs) => {
                self.row_matches_condition(row, lhs) || self.row_matches_condition(row, rhs)
            }
        }
    }

    fn find_rows_by_condition(&self, condition: &Condition) -> Vec<&Vec<Value>> {
        self.rows
            .iter()
            .filter(|row| self.row_matches_condition(row, condition))
            .collect()
    }

// 

    let _ = table.update_value_by_column_condition(
        "id",
        &Value::Integer(2),
        "name",
        Value::Text("Carol".to_string()),
    );

    let condition = Condition::And(
        Box::new(Condition::Equals("age".to_string(), Value::Integer(25))),
        Box::new(Condition::NotEquals("name".to_string(), Value::Text("Bob".to_string()))),
    );

    let results = table.find_rows_by_condition(&condition);
    for row in results {
        println!("Match row: {:?}", row);
    }

カラム名の衝突や順番依存の設計リスクを解消する
L columnsにcolumのorderを値として持つようにする

#[derive(Debug)]
struct ColumnSchema {
    order: usize,
    column_type: ColumnType,
}

#[derive(Debug)]
struct Columns {
    schema: HashMap<String, ColumnSchema>,
    order: Vec<String>,
}

利用側

fn main() {
    let mut schema = HashMap::new();
    let mut order = vec![];

    let columns = vec![
        ("id", ColumnType::Integer),
        ("name", ColumnType::Text),
        ("age", ColumnType::Integer),
    ];

    for (i, (name, col_type)) in columns.into_iter().enumerate() {
        schema.insert(name.to_string(), ColumnSchema {
            order: i,
            column_type: col_type,
        });
        order.push(name.to_string());
    }
    let columns = Columns { schema, order };

    let mut table = Table {
        name: "users".to_string(),
        columns: columns,
        rows: vec![],
    };

    let result = table.insert_row(vec![
        Value::Integer(1),
        Value::Text("Alice".to_string()),
        Value::Integer(30),
    ]);

    let result = table.insert_row(vec![
        Value::Integer(2),
        Value::Text("Bob".to_string()),
        Value::Integer(25),
    ]);

    println!("{:?}", table);

    let _ = table.update_value_by_column_condition(
        "id",
        &Value::Integer(2),
        "name",
        Value::Text("Carol".to_string()),
    );

    // let matching_rows = table.find_rows_by_column_value("id", &Value::Integer(2));

    // for row in matching_rows {
    //     println!("Matched row: {:?}", row);
    // }

    // let matching_rows = table.find_rows_by_column_value("id", &Value::Integer(2));

    // for row in matching_rows {
    //     println!("Matched row: {:?}", row);
    // }

    let _ = table.delete_rows_by_column_value("id", &Value::Integer(2));
    println!("{:?}", table);
}

Table { name: “users”, columns: Columns { schema: {“age”: ColumnSchema { order: 2, column_type: Integer }, “name”: ColumnSchema { order: 1, column_type: Text }, “id”: ColumnSchema { order: 0, column_type: Integer }}, order: [“id”, “name”, “age”] }, rows: [[Integer(1), Text(“Alice”), Integer(30)], [Integer(2), Text(“Bob”), Integer(25)]] }
Table { name: “users”, columns: Columns { schema: {“age”: ColumnSchema { order: 2, column_type: Integer }, “name”: ColumnSchema { order: 1, column_type: Text }, “id”: ColumnSchema { order: 0, column_type: Integer }}, order: [“id”, “name”, “age”] }, rows: [[Integer(1), Text(“Alice”), Integer(30)]] }

なるほど、ある程度期待通りになっています。

### column名とvalueを指定してupdate

    fn update_value_by_column_condition(
        &mut self,
        where_column: &str,
        where_value: &Value,
        set_column: &str,
        new_value: Value,
    ) -> Result<usize, String> {
        let where_index = self.columns.columns.iter().position(|c| c.name == where_column)
            .ok_or_else(|| format!("Column '{}' not found", where_column))?;

        let set_index = self.columns.columns.iter().position(|c| c.name == set_column)
            .ok_or_else(|| format!("Column '{}' not found", set_column))?;

        let expected_type = &self.columns.columns[set_index].constraint;
        if !type_matches(&new_value, expected_type) {
            return Err(format!(
                "Type mismatch: column '{}' expects {:?}, got {:?}",
                set_column, expected_type, new_value
            ));
        }

        let mut updated = 0;
        for row in self.rows.iter_mut() {
            if let Some(v) = row.get(where_index) {
                if v == where_value {
                    row[set_index] = new_value.clone();
                    updated += 1;
                }
            }
        }

        Ok(updated)
    }

    let result = table.insert_row(vec![
        Value::Integer(2),
        Value::Text("Bob".to_string()),
        Value::Integer(25),
    ]);
    println!("{:?}", table);

    let _ = table.update_value_by_column_condition(
        "id",
        &Value::Integer(2),
        "name",
        Value::Text("Carol".to_string()),
    );

    let matching_rows = table.find_rows_by_column_value("id", &Value::Integer(2));

    for row in matching_rows {
        println!("Matched row: {:?}", row);
    }

Matched row: [Integer(2), Text(“Carol”), Integer(25)]

### delete

    fn delete_rows_by_column_value(
        &mut self,
        column_name: &str,
        target: &Value,
    ) -> Result<usize, String> {
        let col_index = self
            .columns
            .columns
            .iter()
            .position(|col| col.name == column_name)
            .ok_or_else(|| format!("Column '{}' not found", column_name))?;

        let original_len = self.rows.len();

        self.rows.retain(|row| row.get(col_index) != Some(target));

        Ok(original_len - self.rows.len())
    }

    let matching_rows = table.find_rows_by_column_value("id", &Value::Integer(2));

    for row in matching_rows {
        println!("Matched row: {:?}", row);
    }

    let _ = table.delete_rows_by_column_value("id", &Value::Integer(2));
    println!("{:?}", table);

Matched row: [Integer(2), Text(“Carol”), Integer(25)]
Table { name: “users”, columns: Columns { columns: [Column { name: “id”, constraint: Integer }, Column { name: “name”, constraint: Text }, Column { name: “age”, constraint: Integer }] }, rows: [[Integer(1), Text(“Alice”), Integer(30)]] }

これで、一通りcreate, insert, select, update, deleteまではできた。
ここから改善していく。

### データ挿入時のエラー処理
impl Tableでinsert_rowとして、カラムにマッチするデータのみ挿入可能とする。

impl Table {
    fn insert_row(&mut self, row: Vec<Value>) -> Result<(), String> {
        if row.len() != self.columns.columns.len() {
            return Err(format!(
                "Row length({}) does not match column count({}",
                row.len(),
                self.columns.columns.len()
            ));
        }

        for (i, (value, column)) in row.iter().zip(&self.columns.columns).enumerate() {
            let expected = column.constraint.to_lowercase();
            let actual = match value {
                Value::Integer(_) => "integer",
                Value::Float(_) => "float",
                Value::Text(_) => "text",
                Value::Boolean(_) => "boolean",
                Value::Date(_) => "date",
                Value::DateTime(_) => "datetime",
                Value::Time(_) => "time",
                Value::Null => "null",
                Value::Binary(_) => "binary",
                Value::UUID(_) => "uuid",
                Value::Json(_) => "json",
                Value::Decimal(_) => "decimal",
            };

            if expected != actual {
                return Err(format!(
                    "Type mismatch at column {} ({}): expected {}, got {}",
                    i, column.name, expected, actual
                ));
            }
        }

        self.rows.push(row);
        Ok(())
    }
}


fn main() {
    let columns = Columns {
        columns: vec![
            Column {
                name: "id".to_string(),
                constraint: "integer".to_string(),
            },
            Column {
                name: "name".to_string(),
                constraint: "text".to_string(),
            },
            Column {
                name: "age".to_string(),
                constraint: "integer".to_string(),
            },
        ]
    };

    let mut table = Table {
        name: "users".to_string(),
        columns: columns,
        rows: vec![],
    };

    let result = table.insert_row(vec![
        Value::Integer(1),
        Value::Text("Alice".to_string()),
        Value::Integer(30),
    ]);
    println!("{:?}", table);
}

Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.50s
Running `target/debug/rds`
Table { name: “users”, columns: Columns { columns: [Column { name: “id”, constraint: “integer” }, Column { name: “name”, constraint: “text” }, Column { name: “age”, constraint: “integer” }] }, rows: [[Integer(1), Text(“Alice”), Integer(30)]] }

### テーブル作成時のカラムの指定
カラムタイプもenumで指定し、その中のtypeしか設定できないようにする。

#[derive(Debug, Clone, PartialEq)]
enum ColumnType {
    Integer,
    Float,
    Text,
    Boolean,
    Date,
    DateTime,
    Time,
    Null,
    Binary,
    UUID,
    Json,
    Decimal,
}

すると、impl Tableのinsert_rowも以下のようになる。

        for (i, (value, column)) in row.iter().zip(self.columns.columns.iter()).enumerate() {
            if !type_matches(value, &column.constraint) {
                return Err(format!(
                    "Type mismatch at column {} ({}): expected {:?}, got {:?}",
                    i, column.name, column.constraint, value
                ));
            }
        }

//

fn type_matches(value: &Value, column_type: &ColumnType) -> bool {
    match(value, column_type) {
        (Value::Integer(_), ColumnType::Integer) => true,
        (Value::Float(_), ColumnType::Float) => true,
        (Value::Text(_), ColumnType::Text) => true,
        (Value::Boolean(_), ColumnType::Boolean) => true,
        (Value::Date(_), ColumnType::Date) => true,
        (Value::DateTime(_), ColumnType::DateTime) => true,
        (Value::Time(_), ColumnType::Time) => true,
        (Value::Null, ColumnType::Null) => true,
        (Value::Binary(_), ColumnType::Binary) => true,
        (Value::UUID(_), ColumnType::UUID) => true,
        (Value::Json(_), ColumnType::Json) => true,
        (Value::Decimal(_), ColumnType::Decimal) => true,
        _ => false,
    }
}

実際の使用例

fn main() {
    let columns = Columns {
        columns: vec![
            Column {
                name: "id".to_string(),
                constraint: ColumnType::Integer,
            },
            Column {
                name: "name".to_string(),
                constraint: ColumnType::Text,
            },
            Column {
                name: "age".to_string(),
                constraint: ColumnType::Integer,
            },
        ]
    };

    let mut table = Table {
        name: "users".to_string(),
        columns: columns,
        rows: vec![],
    };

    let result = table.insert_row(vec![
        Value::Integer(1),
        Value::Text("Alice".to_string()),
        Value::Integer(30),
    ]);

    let result = table.insert_row(vec![
        Value::Integer(2),
        Value::Text("Bob".to_string()),
        Value::Integer(25),
    ]);
    println!("{:?}", table);
}

Table { name: “users”, columns: Columns { columns: [Column { name: “id”, constraint: Integer }, Column { name: “name”, constraint: Text }, Column { name: “age”, constraint: Integer }] }, rows: [[Integer(1), Text(“Alice”), Integer(30)], [Integer(2), Text(“Bob”), Integer(25)]] }

### 検索機能
colum名と値を指定して、そのレコードを抽出したい

    fn find_rows_by_column_value(&self, column_name: &str, target: &Value) -> Vec<&Vec<Value>> {
        let index = self.columns.columns.iter().position(|col| col.name == column_name);

        if let Some(idx) = index {
            self.rows
                .iter()
                .filter(|row| row.get(idx) == Some(target))
                .collect()
        } else {
            vec![]
        }
    }

//

    let matching_rows = table.find_rows_by_column_value("id", &Value::Integer(2));

    for row in matching_rows {
        println!("Matched row: {:?}", row);
    }

Matched row: [Integer(2), Text(“Bob”), Integer(25)]

### Column
カラムは、カラム名と制約が1対1になるので、hashmapを使用する

fn main() {
    let mut columns = HashMap::new();
    columns.insert("id", "integer");
    columns.insert("name", "text");
    columns.insert("age", "integer");

    println!("{:?}", columns);
}

{“name”: “text”, “age”: “integer”, “id”: “integer”}

構造体で書くと以下のようになるが、かなり冗長だ。

#[derive(Debug)]
struct Column {
    name: String,
    constraint: String,
}

#[derive(Debug)]
struct Columns {
    columns: Vec<Column>,
}

fn main() {
    let columns = Columns {
        columns: vec![
            Column {
                name: "id".to_string(),
                constraint: "integer".to_string(),
            },
            Column {
                name: "name".to_string(),
                constraint: "text".to_string(),
            },
            Column {
                name: "age".to_string(),
                constraint: "integer".to_string(),
            },
        ]
    };

    println!("{:?}", columns);
}

### Table
テーブルに対して、上記のカラムがある

#[derive(Debug)]
struct Column {
    name: String,
    constraint: String,
}

#[derive(Debug)]
struct Columns {
    columns: Vec<Column>,
}

#[derive(Debug)]
struct Table {
    name: String,
    columns: Columns,
}


fn main() {
    let columns = Columns {
        columns: vec![
            Column {
                name: "id".to_string(),
                constraint: "integer".to_string(),
            },
            Column {
                name: "name".to_string(),
                constraint: "text".to_string(),
            },
            Column {
                name: "age".to_string(),
                constraint: "integer".to_string(),
            },
        ]
    };

    let table = Table {
        name: "users".to_string(),
        columns: columns,
    };
    println!("{:?}", table);
}

Table { name: “users”, columns: Columns { columns: [Column { name: “id”, constraint: “integer” }, Column { name: “name”, constraint: “text” }, Column { name: “age”, constraint: “integer” }] } }

### 実データを挿入する

#[derive(Debug)]
struct Column {
    name: String,
    constraint: String,
}

#[derive(Debug)]
struct Columns {
    columns: Vec<Column>,
}

#[derive(Debug)]
struct Table {
    name: String,
    columns: Columns,
    rows: Vec<Vec<String>>,
}


fn main() {
    let columns = Columns {
        columns: vec![
            Column {
                name: "id".to_string(),
                constraint: "integer".to_string(),
            },
            Column {
                name: "name".to_string(),
                constraint: "text".to_string(),
            },
            Column {
                name: "age".to_string(),
                constraint: "integer".to_string(),
            },
        ]
    };

    let rows = vec![
        vec!["1".to_string(), "Alice".to_string(), "30".to_string()],
        vec!["2".to_string(), "Bob".to_string(), "25".to_string()],
    ];

    let table = Table {
        name: "users".to_string(),
        columns: columns,
        rows: rows,
    };
    println!("{:?}", table);
}

### データ型をenumで定義する

#[derive(Debug, Clone)]
enum Value {
    Integer(i32),
    Float(f64),
    Text(String),
    Boolean(bool),
    Date(NaiveDate),
    DateTime(NaiveDateTime),
    Time(NaiveTime),
    Null,
    Binary(Vec<u8>),
    UUID(Uuid),
    Json(JsonValue),
    Decimal(Decimal),
}

データ挿入の書き方は以下のようになる。

    let rows = vec![
        vec![Value::Integer(1), Value::Text("Alice".to_string()), Value::Integer(30)],
        vec![Value::Integer(2), Value::Text("Bob".to_string()), Value::Integer(25)],
    ];

Table { name: “users”, columns: Columns { columns: [Column { name: “id”, constraint: “integer” }, Column { name: “name”, constraint: “text” }, Column { name: “age”, constraint: “integer” }] }, rows: [[Integer(1), Text(“Alice”), Integer(30)], [Integer(2), Text(“Bob”), Integer(25)]] }

データ挿入のところをimplで書きたい。