Oregami
Repositories/oxedyne/fe2o3

oxedyne/fe2o3/fe2o3_datime/src/database/indexes.rs

22.8 KiB, 31 runs

created by r1870400018:8430, which is this file's identity for as long as the history lasts, whatever it is later renamed to

download · who wrote it · its history

1//! Database index definitions and generation for the datetime types.
2//!
3//! Emits CREATE INDEX statements for SQL stores and index specifications for
4//! MongoDB.
5//!
6//! [Written with AI entirely](https://need2know.ai/entirely-ai/code)\
7//! Anthropic Claude
8
9use crate::{
10 time::{CalClock, CalClockZone},
11 clock::ClockTime,
12 calendar::CalendarDate,
13};
14
15use oxedyne_fe2o3_core::prelude::*;
16use oxedyne_fe2o3_jdat::prelude::*;
17
18use std::collections::HashMap;
19
20#[derive(Clone, Debug, PartialEq)]
21pub enum IndexType {
22 BTree, // range queries and ordering
23 Hash, // exact equality only
24 Partial(String), // WHERE clause
25 Composite(Vec<String>), // column names
26 Functional(String), // computed expression
27 FullText,
28}
29
30#[derive(Clone, Debug)]
31pub struct DatabaseIndex {
32 pub name: String,
33 pub fields: Vec<String>,
34 pub index_type: IndexType,
35 pub condition: Option<String>, // partial indexes only
36 pub unique: bool,
37 pub priority: u8, // higher is more important
38}
39
40impl DatabaseIndex {
41 pub fn new(name: &str, fields: Vec<String>, index_type: IndexType) -> Self {
42 Self {
43 name: name.to_string(),
44 fields,
45 index_type,
46 condition: None,
47 unique: false,
48 priority: 5,
49 }
50 }
51
52 pub fn unique(mut self) -> Self {
53 self.unique = true;
54 self
55 }
56
57 pub fn priority(mut self, priority: u8) -> Self {
58 self.priority = priority;
59 self
60 }
61
62 pub fn condition(mut self, condition: &str) -> Self {
63 self.condition = Some(condition.to_string());
64 self
65 }
66
67 pub fn to_sql(&self, table_name: &str) -> String {
68 let unique_clause = if self.unique { "UNIQUE " } else { "" };
69 let fields_clause = self.fields.join(", ");
70
71 let index_clause = match &self.index_type {
72 IndexType::BTree => format!("USING BTREE ({})", fields_clause),
73 IndexType::Hash => format!("USING HASH ({})", fields_clause),
74 IndexType::Partial(_) => format!("({})", fields_clause),
75 IndexType::Composite(_) => format!("({})", fields_clause),
76 IndexType::Functional(expr) => format!("({})", expr),
77 IndexType::FullText => format!("USING GIN ({})", fields_clause),
78 };
79
80 let condition_clause = if let Some(ref cond) = self.condition {
81 format!(" WHERE {}", cond)
82 } else {
83 String::new()
84 };
85
86 format!(
87 "CREATE {}INDEX {} ON {} {}{}",
88 unique_clause, self.name, table_name, index_clause, condition_clause
89 )
90 }
91
92 /// The key specification first, then the options map.
93 pub fn to_mongodb(&self) -> (HashMap<String, i32>, HashMap<String, Dat>) {
94 let mut index_spec = HashMap::new();
95 let mut options = HashMap::new();
96
97 // Field specifications
98 for field in &self.fields {
99 match &self.index_type {
100 IndexType::BTree | IndexType::Hash | IndexType::Composite(_) => {
101 index_spec.insert(field.clone(), 1); // Ascending
102 },
103 IndexType::FullText => {
104 index_spec.insert(field.clone(), 0); // Text index
105 },
106 _ => {
107 index_spec.insert(field.clone(), 1);
108 }
109 }
110 }
111
112 // Options
113 options.insert("name".to_string(), Dat::Str(self.name.clone()));
114 if self.unique {
115 options.insert("unique".to_string(), Dat::Bool(true));
116 }
117
118 if let Some(ref cond) = self.condition {
119 options.insert("partialFilterExpression".to_string(), Dat::Str(cond.clone()));
120 }
121
122 (index_spec, options)
123 }
124}
125
126pub trait IndexGenerator {
127 fn generate_indexes(&self, table_name: &str) -> Vec<DatabaseIndex>;
128
129 fn generate_query_specific_indexes(&self, table_name: &str, query_patterns: &[&str]) -> Vec<DatabaseIndex>;
130
131 /// Create these before the rest.
132 fn critical_indexes(&self, table_name: &str) -> Vec<DatabaseIndex>;
133}
134
135impl IndexGenerator for CalClock {
136 fn generate_indexes(&self, table_name: &str) -> Vec<DatabaseIndex> {
137 vec![
138 // Primary timestamp index for range queries
139 DatabaseIndex::new(
140 &format!("idx_{}_timestamp_nanos", table_name),
141 vec!["timestamp_nanos".to_string()],
142 IndexType::BTree,
143 ).priority(10),
144
145 // Timezone index for filtering
146 DatabaseIndex::new(
147 &format!("idx_{}_timezone", table_name),
148 vec!["timezone".to_string()],
149 IndexType::Hash,
150 ).priority(8),
151
152 // Date components for calendar queries
153 DatabaseIndex::new(
154 &format!("idx_{}_date", table_name),
155 vec!["year".to_string(), "month".to_string(), "day".to_string()],
156 IndexType::Composite(vec!["year".to_string(), "month".to_string(), "day".to_string()]),
157 ).priority(9),
158
159 // Year-month for monthly reports
160 DatabaseIndex::new(
161 &format!("idx_{}_year_month", table_name),
162 vec!["year".to_string(), "month".to_string()],
163 IndexType::Composite(vec!["year".to_string(), "month".to_string()]),
164 ).priority(7),
165
166 // Hour for time-based filtering
167 DatabaseIndex::new(
168 &format!("idx_{}_hour", table_name),
169 vec!["hour".to_string()],
170 IndexType::BTree,
171 ).priority(6),
172
173 // Day of week for weekly patterns
174 DatabaseIndex::new(
175 &format!("idx_{}_day_of_week", table_name),
176 vec!["day_of_week".to_string()],
177 IndexType::Hash,
178 ).priority(5),
179
180 // Leap seconds (partial index - rare data)
181 DatabaseIndex::new(
182 &format!("idx_{}_leap_second", table_name),
183 vec!["is_leap_second".to_string()],
184 IndexType::Partial("is_leap_second = TRUE".to_string()),
185 ).condition("is_leap_second = TRUE").priority(3),
186
187 // Timezone + timestamp for efficient filtering
188 DatabaseIndex::new(
189 &format!("idx_{}_timezone_timestamp", table_name),
190 vec!["timezone".to_string(), "timestamp_nanos".to_string()],
191 IndexType::Composite(vec!["timezone".to_string(), "timestamp_nanos".to_string()]),
192 ).priority(8),
193
194 // Weekend filter (partial index)
195 DatabaseIndex::new(
196 &format!("idx_{}_weekend", table_name),
197 vec!["day_of_week".to_string()],
198 IndexType::Partial("day_of_week IN (6, 7)".to_string()),
199 ).condition("day_of_week IN (6, 7)").priority(4),
200
201 // Business hours (partial index)
202 DatabaseIndex::new(
203 &format!("idx_{}_business_hours", table_name),
204 vec!["hour".to_string()],
205 IndexType::Partial("hour BETWEEN 9 AND 17".to_string()),
206 ).condition("hour BETWEEN 9 AND 17").priority(4),
207 ]
208 }
209
210 fn generate_query_specific_indexes(&self, table_name: &str, query_patterns: &[&str]) -> Vec<DatabaseIndex> {
211 let mut indexes = Vec::new();
212
213 for pattern in query_patterns {
214 match *pattern {
215 "time_range_queries" => {
216 indexes.push(DatabaseIndex::new(
217 &format!("idx_{}_time_range", table_name),
218 vec!["timestamp_nanos".to_string()],
219 IndexType::BTree,
220 ).priority(10));
221 },
222
223 "calendar_navigation" => {
224 indexes.push(DatabaseIndex::new(
225 &format!("idx_{}_calendar_nav", table_name),
226 vec!["year".to_string(), "month".to_string(), "day".to_string()],
227 IndexType::Composite(vec!["year".to_string(), "month".to_string(), "day".to_string()]),
228 ).priority(9));
229 },
230
231 "timezone_aware" => {
232 indexes.push(DatabaseIndex::new(
233 &format!("idx_{}_tz_aware", table_name),
234 vec!["timezone".to_string(), "timestamp_nanos".to_string()],
235 IndexType::Composite(vec!["timezone".to_string(), "timestamp_nanos".to_string()]),
236 ).priority(9));
237 },
238
239 "hourly_analytics" => {
240 indexes.push(DatabaseIndex::new(
241 &format!("idx_{}_hourly", table_name),
242 vec!["year".to_string(), "month".to_string(), "day".to_string(), "hour".to_string()],
243 IndexType::Composite(vec!["year".to_string(), "month".to_string(), "day".to_string(), "hour".to_string()]),
244 ).priority(7));
245 },
246
247 "weekly_patterns" => {
248 indexes.push(DatabaseIndex::new(
249 &format!("idx_{}_weekly", table_name),
250 vec!["day_of_week".to_string(), "hour".to_string()],
251 IndexType::Composite(vec!["day_of_week".to_string(), "hour".to_string()]),
252 ).priority(6));
253 },
254
255 "recent_data" => {
256 indexes.push(DatabaseIndex::new(
257 &format!("idx_{}_recent", table_name),
258 vec!["timestamp_nanos".to_string()],
259 IndexType::Functional("timestamp_nanos DESC".to_string()),
260 ).priority(8));
261 },
262
263 _ => {} // Unknown pattern
264 }
265 }
266
267 indexes
268 }
269
270 fn critical_indexes(&self, table_name: &str) -> Vec<DatabaseIndex> {
271 vec![
272 // Primary timestamp index (most critical)
273 DatabaseIndex::new(
274 &format!("idx_{}_timestamp_nanos", table_name),
275 vec!["timestamp_nanos".to_string()],
276 IndexType::BTree,
277 ).priority(10),
278
279 // Date components (very important for calendar queries)
280 DatabaseIndex::new(
281 &format!("idx_{}_date", table_name),
282 vec!["year".to_string(), "month".to_string(), "day".to_string()],
283 IndexType::Composite(vec!["year".to_string(), "month".to_string(), "day".to_string()]),
284 ).priority(9),
285
286 // Timezone filtering (important for multi-timezone systems)
287 DatabaseIndex::new(
288 &format!("idx_{}_timezone", table_name),
289 vec!["timezone".to_string()],
290 IndexType::Hash,
291 ).priority(8),
292 ]
293 }
294}
295
296impl IndexGenerator for ClockTime {
297 fn generate_indexes(&self, table_name: &str) -> Vec<DatabaseIndex> {
298 vec![
299 // Primary time index
300 DatabaseIndex::new(
301 &format!("idx_{}_nanos_of_day", table_name),
302 vec!["nanos_of_day".to_string()],
303 IndexType::BTree,
304 ).priority(10),
305
306 // Hour index for time-based queries
307 DatabaseIndex::new(
308 &format!("idx_{}_hour", table_name),
309 vec!["hour".to_string()],
310 IndexType::BTree,
311 ).priority(8),
312
313 // Hour-minute composite for precise time queries
314 DatabaseIndex::new(
315 &format!("idx_{}_hour_minute", table_name),
316 vec!["hour".to_string(), "minute".to_string()],
317 IndexType::Composite(vec!["hour".to_string(), "minute".to_string()]),
318 ).priority(7),
319
320 // Timezone index
321 DatabaseIndex::new(
322 &format!("idx_{}_timezone", table_name),
323 vec!["timezone".to_string()],
324 IndexType::Hash,
325 ).priority(6),
326
327 // Leap second partial index
328 DatabaseIndex::new(
329 &format!("idx_{}_leap_second", table_name),
330 vec!["is_leap_second".to_string()],
331 IndexType::Partial("is_leap_second = TRUE".to_string()),
332 ).condition("is_leap_second = TRUE").priority(3),
333
334 // End of day partial index
335 DatabaseIndex::new(
336 &format!("idx_{}_end_of_day", table_name),
337 vec!["is_end_of_day".to_string()],
338 IndexType::Partial("is_end_of_day = TRUE".to_string()),
339 ).condition("is_end_of_day = TRUE").priority(2),
340 ]
341 }
342
343 fn generate_query_specific_indexes(&self, table_name: &str, query_patterns: &[&str]) -> Vec<DatabaseIndex> {
344 let mut indexes = Vec::new();
345
346 for pattern in query_patterns {
347 match *pattern {
348 "business_hours" => {
349 indexes.push(DatabaseIndex::new(
350 &format!("idx_{}_business_hours", table_name),
351 vec!["hour".to_string()],
352 IndexType::Partial("hour BETWEEN 9 AND 17".to_string()),
353 ).condition("hour BETWEEN 9 AND 17").priority(7));
354 },
355
356 "time_periods" => {
357 indexes.push(DatabaseIndex::new(
358 &format!("idx_{}_time_periods", table_name),
359 vec!["hour".to_string()],
360 IndexType::BTree,
361 ).priority(6));
362 },
363
364 "precise_timing" => {
365 indexes.push(DatabaseIndex::new(
366 &format!("idx_{}_precise", table_name),
367 vec!["hour".to_string(), "minute".to_string(), "second".to_string()],
368 IndexType::Composite(vec!["hour".to_string(), "minute".to_string(), "second".to_string()]),
369 ).priority(8));
370 },
371
372 _ => {}
373 }
374 }
375
376 indexes
377 }
378
379 fn critical_indexes(&self, table_name: &str) -> Vec<DatabaseIndex> {
380 vec![
381 DatabaseIndex::new(
382 &format!("idx_{}_nanos_of_day", table_name),
383 vec!["nanos_of_day".to_string()],
384 IndexType::BTree,
385 ).priority(10),
386
387 DatabaseIndex::new(
388 &format!("idx_{}_hour", table_name),
389 vec!["hour".to_string()],
390 IndexType::BTree,
391 ).priority(8),
392 ]
393 }
394}
395
396impl IndexGenerator for CalendarDate {
397 fn generate_indexes(&self, table_name: &str) -> Vec<DatabaseIndex> {
398 vec![
399 // Primary date index
400 DatabaseIndex::new(
401 &format!("idx_{}_julian_day", table_name),
402 vec!["julian_day".to_string()],
403 IndexType::BTree,
404 ).priority(10),
405
406 // Year index for yearly queries
407 DatabaseIndex::new(
408 &format!("idx_{}_year", table_name),
409 vec!["year".to_string()],
410 IndexType::BTree,
411 ).priority(9),
412
413 // Year-month composite
414 DatabaseIndex::new(
415 &format!("idx_{}_year_month", table_name),
416 vec!["year".to_string(), "month".to_string()],
417 IndexType::Composite(vec!["year".to_string(), "month".to_string()]),
418 ).priority(8),
419
420 // Full date composite
421 DatabaseIndex::new(
422 &format!("idx_{}_date", table_name),
423 vec!["year".to_string(), "month".to_string(), "day".to_string()],
424 IndexType::Composite(vec!["year".to_string(), "month".to_string(), "day".to_string()]),
425 ).priority(9),
426
427 // Day of week for weekly patterns
428 DatabaseIndex::new(
429 &format!("idx_{}_day_of_week", table_name),
430 vec!["day_of_week".to_string()],
431 IndexType::Hash,
432 ).priority(6),
433
434 // Quarter for quarterly reports
435 DatabaseIndex::new(
436 &format!("idx_{}_quarter", table_name),
437 vec!["quarter".to_string()],
438 IndexType::Hash,
439 ).priority(5),
440
441 // Weekend partial index
442 DatabaseIndex::new(
443 &format!("idx_{}_weekend", table_name),
444 vec!["is_weekend".to_string()],
445 IndexType::Partial("is_weekend = TRUE".to_string()),
446 ).condition("is_weekend = TRUE").priority(4),
447
448 // Leap year partial index
449 DatabaseIndex::new(
450 &format!("idx_{}_leap_year", table_name),
451 vec!["is_leap_year".to_string()],
452 IndexType::Partial("is_leap_year = TRUE".to_string()),
453 ).condition("is_leap_year = TRUE").priority(3),
454
455 // Timezone index
456 DatabaseIndex::new(
457 &format!("idx_{}_timezone", table_name),
458 vec!["timezone".to_string()],
459 IndexType::Hash,
460 ).priority(6),
461 ]
462 }
463
464 fn generate_query_specific_indexes(&self, table_name: &str, query_patterns: &[&str]) -> Vec<DatabaseIndex> {
465 let mut indexes = Vec::new();
466
467 for pattern in query_patterns {
468 match *pattern {
469 "date_ranges" => {
470 indexes.push(DatabaseIndex::new(
471 &format!("idx_{}_date_range", table_name),
472 vec!["julian_day".to_string()],
473 IndexType::BTree,
474 ).priority(10));
475 },
476
477 "monthly_reports" => {
478 indexes.push(DatabaseIndex::new(
479 &format!("idx_{}_monthly", table_name),
480 vec!["year".to_string(), "month".to_string()],
481 IndexType::Composite(vec!["year".to_string(), "month".to_string()]),
482 ).priority(8));
483 },
484
485 "quarterly_reports" => {
486 indexes.push(DatabaseIndex::new(
487 &format!("idx_{}_quarterly", table_name),
488 vec!["year".to_string(), "quarter".to_string()],
489 IndexType::Composite(vec!["year".to_string(), "quarter".to_string()]),
490 ).priority(7));
491 },
492
493 "weekly_analysis" => {
494 indexes.push(DatabaseIndex::new(
495 &format!("idx_{}_weekly", table_name),
496 vec!["day_of_week".to_string()],
497 IndexType::Hash,
498 ).priority(6));
499 },
500
501 _ => {}
502 }
503 }
504
505 indexes
506 }
507
508 fn critical_indexes(&self, table_name: &str) -> Vec<DatabaseIndex> {
509 vec![
510 DatabaseIndex::new(
511 &format!("idx_{}_julian_day", table_name),
512 vec!["julian_day".to_string()],
513 IndexType::BTree,
514 ).priority(10),
515
516 DatabaseIndex::new(
517 &format!("idx_{}_year", table_name),
518 vec!["year".to_string()],
519 IndexType::BTree,
520 ).priority(9),
521
522 DatabaseIndex::new(
523 &format!("idx_{}_date", table_name),
524 vec!["year".to_string(), "month".to_string(), "day".to_string()],
525 IndexType::Composite(vec!["year".to_string(), "month".to_string(), "day".to_string()]),
526 ).priority(9),
527 ]
528 }
529}
530
531pub mod index_utils {
532 use super::*;
533
534 pub fn generate_create_indexes_sql(table_name: &str, datetime_type: &str) -> Outcome<String> {
535 let mut sql = String::new();
536 sql.push_str(&format!("-- Database indexes for {} table\n", table_name));
537 sql.push_str(&format!("-- Generated for datetime type: {}\n\n", datetime_type));
538
539 let indexes = match datetime_type {
540 "CalClock" => {
541 let sample = CalClock::new(2024, 1, 1, 0, 0, 0, 0, CalClockZone::utc()).unwrap();
542 sample.generate_indexes(table_name)
543 },
544 "ClockTime" => {
545 let sample = ClockTime::new(0, 0, 0, 0, CalClockZone::utc()).unwrap();
546 sample.generate_indexes(table_name)
547 },
548 "CalendarDate" => {
549 let sample = CalendarDate::new(2024, 1, 1, CalClockZone::utc()).unwrap();
550 sample.generate_indexes(table_name)
551 },
552 _ => return Err(err!("Unknown datetime type: {}", datetime_type; Invalid, Input)),
553 };
554
555 // Sort by priority (highest first)
556 let mut sorted_indexes = indexes;
557 sorted_indexes.sort_by(|a, b| b.priority.cmp(&a.priority));
558
559 for index in sorted_indexes {
560 sql.push_str(&format!("-- Priority: {}\n", index.priority));
561 sql.push_str(&index.to_sql(table_name));
562 sql.push_str(";\n\n");
563 }
564
565 Ok(sql)
566 }
567
568 pub fn generate_mongodb_indexes(collection_name: &str, datetime_type: &str) -> Outcome<String> {
569 let indexes = match datetime_type {
570 "CalClock" => {
571 let sample = CalClock::new(2024, 1, 1, 0, 0, 0, 0, CalClockZone::utc()).unwrap();
572 sample.generate_indexes(collection_name)
573 },
574 "ClockTime" => {
575 let sample = ClockTime::new(0, 0, 0, 0, CalClockZone::utc()).unwrap();
576 sample.generate_indexes(collection_name)
577 },
578 "CalendarDate" => {
579 let sample = CalendarDate::new(2024, 1, 1, CalClockZone::utc()).unwrap();
580 sample.generate_indexes(collection_name)
581 },
582 _ => return Err(err!("Unknown datetime type: {}", datetime_type; Invalid, Input)),
583 };
584
585 let mut commands = String::new();
586 commands.push_str(&format!("// MongoDB indexes for {} collection\n", collection_name));
587 commands.push_str(&format!("// Generated for datetime type: {}\n\n", datetime_type));
588
589 for index in indexes {
590 let (spec, options) = index.to_mongodb();
591 commands.push_str(&format!("// Priority: {}\n", index.priority));
592 commands.push_str(&format!("db.{}.createIndex(", collection_name));
593 commands.push_str(&format!("{:?}, {:?}", spec, options));
594 commands.push_str(");\n\n");
595 }
596
597 Ok(commands)
598 }
599}