motedb 0.2.0

AI-native embedded multimodal database for embodied intelligence (robots, AR glasses, industrial arms).
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307

//! Multimodal regression tests — vectors, spatial, FTS, timestamp

use motedb::{Database, types::Value, sql::QueryResult};
use tempfile::TempDir;

fn create_db() -> (Database, TempDir) {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path().join("test.mote")).unwrap();
    (db, dir)
}

fn rows(db: &Database, sql: &str) -> Vec<Vec<Value>> {
    match db.execute(sql).unwrap().materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => vec![],
    }
}

fn exec(db: &Database, sql: &str) -> QueryResult {
    db.execute(sql).unwrap().materialize().unwrap()
}

// ============================================================
// Vector tests
// ============================================================

#[test]
fn test_vector_insert_and_select() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE items (id INT, embedding VECTOR(4))").unwrap();
    db.execute("INSERT INTO items (id, embedding) VALUES (1, [1.0, 0.0, 0.0, 0.0])").unwrap();
    db.execute("INSERT INTO items (id, embedding) VALUES (2, [0.0, 1.0, 0.0, 0.0])").unwrap();
    db.execute("INSERT INTO items (id, embedding) VALUES (3, [0.9, 0.1, 0.0, 0.0])").unwrap();

    let r = rows(&db, "SELECT id FROM items ORDER BY id");
    assert_eq!(r.len(), 3);
}

#[test]
fn test_vector_l2_distance() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE v (id INT, emb VECTOR(3))").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (1, [1.0, 0.0, 0.0])").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (2, [0.0, 1.0, 0.0])").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (3, [0.0, 0.0, 1.0])").unwrap();

    // L2 distance from [1,0,0] to [0,1,0] = sqrt(2) ≈ 1.414
    let r = rows(&db, "SELECT id FROM v WHERE id != 1 ORDER BY emb <-> [1.0, 0.0, 0.0]");
    assert_eq!(r.len(), 2);
}

#[test]
fn test_vector_cosine_distance() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE v (id INT, emb VECTOR(3))").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (1, [1.0, 0.0, 0.0])").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (2, [0.5, 0.5, 0.0])").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (3, [-1.0, 0.0, 0.0])").unwrap();

    // Cosine distance from [1,0,0]:
    //   to [1,0,0] = 0 (identical)
    //   to [0.5,0.5,0] = 1 - cos(45°) ≈ 0.293
    //   to [-1,0,0] = 1 - (-1) = 2.0 (opposite)
    let r = rows(&db, "SELECT id FROM v ORDER BY emb <=> [1.0, 0.0, 0.0]");
    assert_eq!(r.len(), 3);
    // Closest should be id=1 (distance 0)
    assert_eq!(r[0][0], Value::Integer(1));
    // Farthest should be id=3 (distance 2.0)
    assert_eq!(r[2][0], Value::Integer(3));
}

#[test]
fn test_vector_dot_product() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE v (id INT, emb VECTOR(3))").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (1, [1.0, 2.0, 3.0])").unwrap();

    // Dot product [1,2,3] . [1,1,1] = 6
    let r = rows(&db, "SELECT emb <#> [1.0, 1.0, 1.0] AS dp FROM v");
    assert_eq!(r.len(), 1);
    if let Value::Float(dp) = r[0][0] {
        let diff = (dp - 6.0).abs();
        assert!(diff < 0.01, "dot product should be 6.0, got {}", dp);
    }
}

#[test]
fn test_vector_zero_vector_cosine() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE v (id INT, emb VECTOR(2))").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (1, [0.0, 0.0])").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (2, [1.0, 0.0])").unwrap();

    // Zero vector cosine distance should be 1.0 (max), not NaN/Inf
    let r = rows(&db, "SELECT emb <=> [1.0, 0.0] AS dist FROM v WHERE id = 1");
    assert_eq!(r.len(), 1);
    if let Value::Float(dist) = r[0][0] {
        assert!(!dist.is_nan() && !dist.is_infinite(), "distance should be finite, got {}", dist);
    }
}

#[test]
fn test_vector_dimension_mismatch() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE v (id INT, emb VECTOR(3))").unwrap();
    db.execute("INSERT INTO v (id, emb) VALUES (1, [1.0, 0.0, 0.0])").unwrap();

    // Querying with wrong dimension vector: behavior is undefined (may error or not)
    // Currently MoteDB does not enforce dimension check at query time for vector literals
    let result = db.execute("SELECT emb <-> [1.0, 0.0] AS dist FROM v");
    // Just verify no panic — the query may succeed or error depending on implementation
    let _ = result.map(|s| s.materialize());
}

// ============================================================
// Spatial tests
// ============================================================

#[test]
fn test_spatial_insert_and_select() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE poi (id INT, location GEOMETRY)").unwrap();
    db.execute("INSERT INTO poi (id, location) VALUES (1, POINT(1.0, 2.0))").unwrap();
    db.execute("INSERT INTO poi (id, location) VALUES (2, POINT(3.0, 4.0))").unwrap();

    let r = rows(&db, "SELECT id FROM poi ORDER BY id");
    assert_eq!(r.len(), 2);
    assert_eq!(r[0][0], Value::Integer(1));
    assert_eq!(r[1][0], Value::Integer(2));
}

#[test]
fn test_spatial_st_distance() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE poi (id INT, location GEOMETRY)").unwrap();
    db.execute("INSERT INTO poi (id, location) VALUES (1, POINT(0.0, 0.0))").unwrap();
    db.execute("INSERT INTO poi (id, location) VALUES (2, POINT(3.0, 4.0))").unwrap();

    // ST_DISTANCE(column, x, y) — MoteDB syntax
    let r = rows(&db, "SELECT ST_DISTANCE(location, 0.0, 0.0) AS dist FROM poi WHERE id = 2");
    assert_eq!(r.len(), 1);
    if let Value::Float(dist) = r[0][0] {
        let diff = (dist - 5.0).abs();
        assert!(diff < 0.01, "ST_DISTANCE should be 5.0, got {}", dist);
    }
}

#[test]
fn test_spatial_st_distance_3d() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE poi (id INT, location GEOMETRY)").unwrap();
    db.execute("INSERT INTO poi (id, location) VALUES (1, POINT3D(1.0, 2.0, 2.0))").unwrap();
    db.execute("INSERT INTO poi (id, location) VALUES (2, POINT3D(4.0, 6.0, 14.0))").unwrap();

    // ST_DISTANCE_3D(column, x, y, z)
    let r = rows(&db, "SELECT ST_DISTANCE_3D(location, 1.0, 2.0, 2.0) AS dist FROM poi WHERE id = 2");
    assert_eq!(r.len(), 1);
    if let Value::Float(dist) = r[0][0] {
        let diff = (dist - 13.0).abs();
        assert!(diff < 0.01, "ST_DISTANCE 3D should be 13.0, got {}", dist);
    }
}

#[test]
fn test_spatial_within_radius() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE poi (id INT, location GEOMETRY)").unwrap();
    db.execute("INSERT INTO poi (id, location) VALUES (1, POINT(0.0, 0.0))").unwrap();
    db.execute("INSERT INTO poi (id, location) VALUES (2, POINT(10.0, 10.0))").unwrap();
    db.execute("INSERT INTO poi (id, location) VALUES (3, POINT(1.0, 0.0))").unwrap();

    // WITHIN_RADIUS(point, center, radius) as generic function call
    let r = rows(&db, "SELECT id FROM poi WHERE WITHIN_RADIUS(location, POINT(0.0, 0.0), 5.0) ORDER BY id");
    assert_eq!(r.len(), 2);
    assert_eq!(r[0][0], Value::Integer(1));
    assert_eq!(r[1][0], Value::Integer(3));
}

// ============================================================
// Full-text search tests
// ============================================================

#[test]
fn test_fts_insert_and_search() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE docs (id INT, content TEXT)").unwrap();
    db.execute("INSERT INTO docs (id, content) VALUES (1, 'the quick brown fox jumps over the lazy dog')").unwrap();
    db.execute("INSERT INTO docs (id, content) VALUES (2, 'a fox is a clever animal')").unwrap();
    db.execute("INSERT INTO docs (id, content) VALUES (3, 'the dog barks at night')").unwrap();

    // Simple text search with LIKE
    let r = rows(&db, "SELECT id FROM docs WHERE content LIKE '%fox%' ORDER BY id");
    assert_eq!(r.len(), 2);
    assert_eq!(r[0][0], Value::Integer(1));
    assert_eq!(r[1][0], Value::Integer(2));
}

#[test]
fn test_fts_case_insensitive_like() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE docs (id INT, content TEXT)").unwrap();
    db.execute("INSERT INTO docs (id, content) VALUES (1, 'Hello World')").unwrap();

    let r = rows(&db, "SELECT id FROM docs WHERE content LIKE '%hello%'");
    // LIKE is case-sensitive in MoteDB, so this should return 0 rows
    // If it returns 1, that means LIKE was made case-insensitive
    assert!(r.len() <= 1); // Either behavior is acceptable
}

#[test]
fn test_fts_match_query() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE docs (id INT, content TEXT)").unwrap();
    db.execute("INSERT INTO docs (id, content) VALUES (1, 'machine learning algorithms')").unwrap();
    db.execute("INSERT INTO docs (id, content) VALUES (2, 'deep learning neural networks')").unwrap();
    db.execute("INSERT INTO docs (id, content) VALUES (3, 'cooking recipes for dinner')").unwrap();

    // MATCH(column, query_text) — generic function form
    let r = rows(&db, "SELECT id FROM docs WHERE MATCH(content, 'learning') ORDER BY id");
    assert!(r.len() >= 2, "Should find at least 2 docs with 'learning', got {}", r.len());
}

// ============================================================
// Timestamp tests
// ============================================================

#[test]
fn test_timestamp_insert_and_select() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE events (id INT, ts TIMESTAMP)").unwrap();
    db.execute("INSERT INTO events (id, ts) VALUES (1, 1700000000000000)").unwrap();
    db.execute("INSERT INTO events (id, ts) VALUES (2, 1700000050000000)").unwrap();

    let r = rows(&db, "SELECT id FROM events ORDER BY id");
    assert_eq!(r.len(), 2);
}

#[test]
fn test_timestamp_comparison() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE events (id INT, ts TIMESTAMP)").unwrap();
    db.execute("INSERT INTO events (id, ts) VALUES (1, 1700000000000000)").unwrap();
    db.execute("INSERT INTO events (id, ts) VALUES (2, 1700000050000000)").unwrap();
    db.execute("INSERT INTO events (id, ts) VALUES (3, 1700000100000000)").unwrap();

    // Select events after ts > 1700000050000000
    let r = rows(&db, "SELECT id FROM events WHERE ts > 1700000050000000");
    assert_eq!(r.len(), 1);
    assert_eq!(r[0][0], Value::Integer(3));
}

#[test]
fn test_timestamp_functions() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE t (id INT)").unwrap();
    db.execute("INSERT INTO t (id) VALUES (1)").unwrap();

    // HOUR extraction from a known timestamp
    // 2023-11-14 22:13:20 UTC = 1700000000 seconds = 1700000000000000 microseconds
    let r = rows(&db, "SELECT HOUR(1700000000000000) AS h FROM t");
    if let Value::Integer(h) = r[0][0] {
        assert!(h >= 0 && h <= 23, "HOUR should be 0-23, got {}", h);
    }
}

// ============================================================
// Cross-type tests
// ============================================================

#[test]
fn test_mixed_types_in_table() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE mixed (id INT, name TEXT, score FLOAT, active BOOL, embedding VECTOR(2))").unwrap();
    db.execute("INSERT INTO mixed (id, name, score, active, embedding) VALUES (1, 'Alice', 95.5, true, [1.0, 0.0])").unwrap();
    db.execute("INSERT INTO mixed (id, name, score, active, embedding) VALUES (2, 'Bob', 82.0, false, [0.0, 1.0])").unwrap();

    let r = rows(&db, "SELECT id, name, score FROM mixed WHERE active = true");
    assert_eq!(r.len(), 1);
    assert_eq!(r[0][0], Value::Integer(1));
}

#[test]
fn test_null_in_vector_column() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE t (id INT, emb VECTOR(2))").unwrap();
    db.execute("INSERT INTO t (id, emb) VALUES (1, [1.0, 0.0])").unwrap();
    db.execute("INSERT INTO t (id) VALUES (2)").unwrap();

    let r = rows(&db, "SELECT id FROM t WHERE emb IS NULL");
    assert_eq!(r.len(), 1);
    assert_eq!(r[0][0], Value::Integer(2));

    let r = rows(&db, "SELECT id FROM t WHERE emb IS NOT NULL");
    assert_eq!(r.len(), 1);
    assert_eq!(r[0][0], Value::Integer(1));
}

#[test]
fn test_multiple_vector_columns() {
    let (db, _dir) = create_db();
    db.execute("CREATE TABLE t (id INT, emb_a VECTOR(2), emb_b VECTOR(2))").unwrap();
    db.execute("INSERT INTO t (id, emb_a, emb_b) VALUES (1, [1.0, 0.0], [0.0, 1.0])").unwrap();
    db.execute("INSERT INTO t (id, emb_a, emb_b) VALUES (2, [0.0, 1.0], [1.0, 0.0])").unwrap();

    let r = rows(&db, "SELECT id FROM t ORDER BY id");
    assert_eq!(r.len(), 2);
}