uls_db/
schema.rs

1//! Database schema definition and management.
2//!
3//! Creates and manages the SQLite schema for ULS data storage.
4
5use rusqlite::Connection;
6
7use crate::error::Result;
8
9/// Current schema version.
10pub const SCHEMA_VERSION: i32 = 6;
11
12/// Database schema management.
13pub struct Schema;
14
15impl Schema {
16    /// Create all tables in the database.
17    pub fn create_tables(conn: &Connection) -> Result<()> {
18        // Set optimal page size for text-heavy data (must be before first table creation)
19        // Smaller pages = less wasted space when storing many short strings
20        conn.execute_batch("PRAGMA page_size = 1024;")?;
21
22        // Metadata table for tracking schema version and update times
23        // WITHOUT ROWID: Small table with TEXT PRIMARY KEY
24        conn.execute_batch(
25            r#"
26            CREATE TABLE IF NOT EXISTS metadata (
27                key TEXT PRIMARY KEY,
28                value TEXT NOT NULL
29            ) WITHOUT ROWID;
30            "#,
31        )?;
32
33        // Set schema version
34        conn.execute(
35            "INSERT OR REPLACE INTO metadata (key, value) VALUES ('schema_version', ?1)",
36            [&SCHEMA_VERSION.to_string()],
37        )?;
38
39        // Header record (HD) - main license table
40        // WITHOUT ROWID: Largest table, integer PK, saves ~8 bytes per row
41        // license_status and radio_service_code stored as INTEGER for compactness
42        conn.execute_batch(
43            r#"
44            CREATE TABLE IF NOT EXISTS licenses (
45                unique_system_identifier INTEGER PRIMARY KEY,
46                uls_file_number TEXT,
47                ebf_number TEXT,
48                call_sign TEXT COLLATE NOCASE,
49                license_status INTEGER,
50                radio_service_code INTEGER,
51                grant_date TEXT,
52                expired_date TEXT,
53                cancellation_date TEXT,
54                effective_date TEXT,
55                last_action_date TEXT,
56                revoked_certification TEXT,
57                license_revoked TEXT,
58                licensee_name TEXT COLLATE NOCASE,
59                first_name TEXT COLLATE NOCASE,
60                middle_initial TEXT,
61                last_name TEXT COLLATE NOCASE,
62                suffix TEXT,
63                frn TEXT COLLATE NOCASE,
64                previous_call_sign TEXT COLLATE NOCASE,
65                trustee_call_sign TEXT COLLATE NOCASE,
66                trustee_name TEXT COLLATE NOCASE
67            ) WITHOUT ROWID;
68            "#,
69        )?;
70
71        // Entity record (EN)
72        // Composite unique key: (unique_system_identifier, entity_type)
73        // A license can have multiple entities (Licensee, Contact, etc.)
74        // entity_type stored as INTEGER for compactness
75        conn.execute_batch(
76            r#"
77            CREATE TABLE IF NOT EXISTS entities (
78                id INTEGER PRIMARY KEY AUTOINCREMENT,
79                unique_system_identifier INTEGER NOT NULL,
80                uls_file_number TEXT,
81                ebf_number TEXT,
82                call_sign TEXT COLLATE NOCASE,
83                entity_type INTEGER,
84                licensee_id TEXT,
85                entity_name TEXT COLLATE NOCASE,
86                first_name TEXT COLLATE NOCASE,
87                middle_initial TEXT,
88                last_name TEXT COLLATE NOCASE,
89                suffix TEXT,
90                phone TEXT,
91                fax TEXT,
92                email TEXT,
93                street_address TEXT COLLATE NOCASE,
94                city TEXT COLLATE NOCASE,
95                state TEXT COLLATE NOCASE,
96                zip_code TEXT COLLATE NOCASE,
97                po_box TEXT,
98                attention_line TEXT,
99                sgin TEXT,
100                frn TEXT COLLATE NOCASE,
101                applicant_type_code TEXT,
102                status_code TEXT,
103                status_date TEXT,
104                UNIQUE(unique_system_identifier, entity_type),
105                FOREIGN KEY (unique_system_identifier) REFERENCES licenses(unique_system_identifier)
106            );
107            "#,
108        )?;
109
110        // Amateur record (AM)
111        // Unique key: unique_system_identifier (one per license)
112        // operator_class and previous_operator_class stored as INTEGER
113        conn.execute_batch(
114            r#"
115            CREATE TABLE IF NOT EXISTS amateur_operators (
116                id INTEGER PRIMARY KEY AUTOINCREMENT,
117                unique_system_identifier INTEGER NOT NULL UNIQUE,
118                uls_file_number TEXT,
119                ebf_number TEXT,
120                call_sign TEXT COLLATE NOCASE,
121                operator_class INTEGER,
122                group_code TEXT,
123                region_code INTEGER,
124                trustee_call_sign TEXT COLLATE NOCASE,
125                trustee_indicator TEXT,
126                physician_certification TEXT,
127                ve_signature TEXT,
128                systematic_call_sign_change TEXT,
129                vanity_call_sign_change TEXT,
130                vanity_relationship TEXT,
131                previous_call_sign TEXT COLLATE NOCASE,
132                previous_operator_class INTEGER,
133                trustee_name TEXT COLLATE NOCASE,
134                FOREIGN KEY (unique_system_identifier) REFERENCES licenses(unique_system_identifier)
135            );
136            "#,
137        )?;
138
139        // History record (HS)
140        // Composite unique key: (unique_system_identifier, log_date, code)
141        conn.execute_batch(
142            r#"
143            CREATE TABLE IF NOT EXISTS history (
144                id INTEGER PRIMARY KEY AUTOINCREMENT,
145                unique_system_identifier INTEGER NOT NULL,
146                uls_file_number TEXT,
147                callsign TEXT,
148                log_date TEXT,
149                code TEXT,
150                UNIQUE(unique_system_identifier, log_date, code),
151                FOREIGN KEY (unique_system_identifier) REFERENCES licenses(unique_system_identifier)
152            );
153            "#,
154        )?;
155
156        // Comments record (CO)
157        // Composite unique key: (unique_system_identifier, comment_date)
158        conn.execute_batch(
159            r#"
160            CREATE TABLE IF NOT EXISTS comments (
161                id INTEGER PRIMARY KEY AUTOINCREMENT,
162                unique_system_identifier INTEGER NOT NULL,
163                uls_file_number TEXT,
164                callsign TEXT,
165                comment_date TEXT,
166                description TEXT,
167                status_code TEXT,
168                status_date TEXT,
169                UNIQUE(unique_system_identifier, comment_date),
170                FOREIGN KEY (unique_system_identifier) REFERENCES licenses(unique_system_identifier)
171            );
172            "#,
173        )?;
174
175        // Special conditions record (SC)
176        // Composite unique key: (unique_system_identifier, special_condition_code)
177        conn.execute_batch(
178            r#"
179            CREATE TABLE IF NOT EXISTS special_conditions (
180                id INTEGER PRIMARY KEY AUTOINCREMENT,
181                unique_system_identifier INTEGER NOT NULL,
182                uls_file_number TEXT,
183                ebf_number TEXT,
184                callsign TEXT,
185                special_condition_type TEXT,
186                special_condition_code INTEGER,
187                status_code TEXT,
188                status_date TEXT,
189                UNIQUE(unique_system_identifier, special_condition_code),
190                FOREIGN KEY (unique_system_identifier) REFERENCES licenses(unique_system_identifier)
191            );
192            "#,
193        )?;
194
195        // Import status tracking - which record types have been imported per service
196        // WITHOUT ROWID: Small table with composite TEXT PRIMARY KEY
197        conn.execute_batch(
198            r#"
199            CREATE TABLE IF NOT EXISTS import_status (
200                radio_service_code TEXT NOT NULL,
201                record_type TEXT NOT NULL,
202                imported_at TEXT,
203                record_count INTEGER,
204                PRIMARY KEY (radio_service_code, record_type)
205            ) WITHOUT ROWID;
206            "#,
207        )?;
208
209        // Applied patches tracking - which daily files have been applied since last weekly
210        conn.execute_batch(
211            r#"
212            CREATE TABLE IF NOT EXISTS applied_patches (
213                radio_service_code TEXT NOT NULL,
214                patch_date TEXT NOT NULL,
215                patch_weekday TEXT NOT NULL,
216                applied_at TEXT NOT NULL,
217                etag TEXT,
218                record_count INTEGER,
219                PRIMARY KEY (radio_service_code, patch_date)
220            );
221            "#,
222        )?;
223
224        Ok(())
225    }
226
227    /// Create indexes for efficient queries.
228    pub fn create_indexes(conn: &Connection) -> Result<()> {
229        conn.execute_batch(
230            r#"
231            -- License indexes
232            CREATE INDEX IF NOT EXISTS idx_licenses_call_sign ON licenses(call_sign);
233            CREATE INDEX IF NOT EXISTS idx_licenses_status ON licenses(license_status);
234            CREATE INDEX IF NOT EXISTS idx_licenses_service ON licenses(radio_service_code);
235            CREATE INDEX IF NOT EXISTS idx_licenses_frn ON licenses(frn);
236            CREATE INDEX IF NOT EXISTS idx_licenses_name ON licenses(licensee_name);
237            CREATE INDEX IF NOT EXISTS idx_licenses_grant_date ON licenses(grant_date);
238            CREATE INDEX IF NOT EXISTS idx_licenses_expired_date ON licenses(expired_date);
239            
240            -- Entity indexes
241            CREATE INDEX IF NOT EXISTS idx_entities_usi ON entities(unique_system_identifier);
242            CREATE INDEX IF NOT EXISTS idx_entities_call_sign ON entities(call_sign);
243            CREATE INDEX IF NOT EXISTS idx_entities_frn ON entities(frn);
244            CREATE INDEX IF NOT EXISTS idx_entities_city_state ON entities(city, state);
245            CREATE INDEX IF NOT EXISTS idx_entities_name ON entities(entity_name);
246            CREATE INDEX IF NOT EXISTS idx_entities_last_name ON entities(last_name);
247            
248            -- Amateur operator indexes
249            CREATE INDEX IF NOT EXISTS idx_amateur_usi ON amateur_operators(unique_system_identifier);
250            CREATE INDEX IF NOT EXISTS idx_amateur_call_sign ON amateur_operators(call_sign);
251            CREATE INDEX IF NOT EXISTS idx_amateur_class ON amateur_operators(operator_class);
252            
253            -- History indexes
254            CREATE INDEX IF NOT EXISTS idx_history_usi ON history(unique_system_identifier);
255            CREATE INDEX IF NOT EXISTS idx_history_callsign ON history(callsign);
256            
257            -- Comments indexes
258            CREATE INDEX IF NOT EXISTS idx_comments_usi ON comments(unique_system_identifier);
259            CREATE INDEX IF NOT EXISTS idx_comments_callsign ON comments(callsign);
260            
261            -- Special conditions indexes
262            CREATE INDEX IF NOT EXISTS idx_special_cond_usi ON special_conditions(unique_system_identifier);
263            "#,
264        )?;
265
266        Ok(())
267    }
268
269    /// Drop all non-primary indexes (for bulk import performance).
270    /// After import completes, call `create_indexes` to rebuild them.
271    pub fn drop_indexes(conn: &Connection) -> Result<()> {
272        conn.execute_batch(
273            r#"
274            -- License indexes
275            DROP INDEX IF EXISTS idx_licenses_call_sign;
276            DROP INDEX IF EXISTS idx_licenses_status;
277            DROP INDEX IF EXISTS idx_licenses_service;
278            DROP INDEX IF EXISTS idx_licenses_frn;
279            DROP INDEX IF EXISTS idx_licenses_name;
280            DROP INDEX IF EXISTS idx_licenses_grant_date;
281            DROP INDEX IF EXISTS idx_licenses_expired_date;
282            
283            -- Entity indexes
284            DROP INDEX IF EXISTS idx_entities_usi;
285            DROP INDEX IF EXISTS idx_entities_call_sign;
286            DROP INDEX IF EXISTS idx_entities_frn;
287            DROP INDEX IF EXISTS idx_entities_city_state;
288            DROP INDEX IF EXISTS idx_entities_name;
289            DROP INDEX IF EXISTS idx_entities_last_name;
290            
291            -- Amateur operator indexes
292            DROP INDEX IF EXISTS idx_amateur_usi;
293            DROP INDEX IF EXISTS idx_amateur_call_sign;
294            DROP INDEX IF EXISTS idx_amateur_class;
295            
296            -- History indexes
297            DROP INDEX IF EXISTS idx_history_usi;
298            DROP INDEX IF EXISTS idx_history_callsign;
299            
300            -- Comments indexes
301            DROP INDEX IF EXISTS idx_comments_usi;
302            DROP INDEX IF EXISTS idx_comments_callsign;
303            
304            -- Special conditions indexes
305            DROP INDEX IF EXISTS idx_special_cond_usi;
306            "#,
307        )?;
308
309        Ok(())
310    }
311
312    /// Initialize a new database with schema and indexes.
313    pub fn initialize(conn: &Connection) -> Result<()> {
314        Self::create_tables(conn)?;
315        Self::create_indexes(conn)?;
316        Ok(())
317    }
318
319    /// Get the current schema version from the database.
320    /// Returns None if the database is not initialized (table doesn't exist or no rows).
321    pub fn get_version(conn: &Connection) -> Result<Option<i32>> {
322        let result = conn.query_row(
323            "SELECT value FROM metadata WHERE key = 'schema_version'",
324            [],
325            |row| row.get::<_, String>(0),
326        );
327
328        match result {
329            Ok(v) => Ok(v.parse().ok()),
330            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
331            Err(rusqlite::Error::SqliteFailure(_, Some(ref msg)))
332                if msg.contains("no such table") =>
333            {
334                // Table doesn't exist = not initialized
335                Ok(None)
336            }
337            Err(e) => Err(e.into()),
338        }
339    }
340
341    /// Set a metadata value.
342    pub fn set_metadata(conn: &Connection, key: &str, value: &str) -> Result<()> {
343        conn.execute(
344            "INSERT OR REPLACE INTO metadata (key, value) VALUES (?1, ?2)",
345            [key, value],
346        )?;
347        Ok(())
348    }
349
350    /// Get a metadata value.
351    pub fn get_metadata(conn: &Connection, key: &str) -> Result<Option<String>> {
352        let result = conn.query_row("SELECT value FROM metadata WHERE key = ?1", [key], |row| {
353            row.get(0)
354        });
355
356        match result {
357            Ok(v) => Ok(Some(v)),
358            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
359            Err(e) => Err(e.into()),
360        }
361    }
362
363    /// Migrate the database schema if needed.
364    ///
365    /// This handles upgrades from older schema versions to the current version.
366    pub fn migrate_if_needed(conn: &Connection) -> Result<()> {
367        let current_version = Self::get_version(conn)?;
368
369        match current_version {
370            None => {
371                // Database not initialized, nothing to migrate
372                Ok(())
373            }
374            Some(v) if v >= SCHEMA_VERSION => {
375                // Already at or above current version
376                Ok(())
377            }
378            Some(v) => {
379                // Need to migrate
380                tracing::info!(
381                    "Migrating database from schema v{} to v{}",
382                    v,
383                    SCHEMA_VERSION
384                );
385
386                // Apply migrations in order
387                if v < 5 {
388                    Self::migrate_to_v5(conn)?;
389                }
390
391                // Update schema version
392                conn.execute(
393                    "INSERT OR REPLACE INTO metadata (key, value) VALUES ('schema_version', ?1)",
394                    [&SCHEMA_VERSION.to_string()],
395                )?;
396
397                Ok(())
398            }
399        }
400    }
401
402    /// Migrate from v4 to v5: Add applied_patches table.
403    fn migrate_to_v5(conn: &Connection) -> Result<()> {
404        tracing::info!("Applying migration to v5: adding applied_patches table");
405
406        conn.execute_batch(
407            r#"
408            CREATE TABLE IF NOT EXISTS applied_patches (
409                id INTEGER PRIMARY KEY AUTOINCREMENT,
410                radio_service_code TEXT NOT NULL,
411                patch_date TEXT NOT NULL,
412                patch_weekday TEXT NOT NULL,
413                applied_at TEXT NOT NULL,
414                etag TEXT,
415                record_count INTEGER,
416                UNIQUE(radio_service_code, patch_date)
417            );
418            
419            CREATE INDEX IF NOT EXISTS idx_applied_patches_service 
420                ON applied_patches(radio_service_code);
421            "#,
422        )?;
423
424        Ok(())
425    }
426}
427
428#[cfg(test)]
429mod tests {
430    use super::*;
431    use rusqlite::Connection;
432
433    #[test]
434    fn test_create_schema() {
435        let conn = Connection::open_in_memory().unwrap();
436        Schema::initialize(&conn).unwrap();
437
438        // Verify tables exist
439        let count: i32 = conn
440            .query_row(
441                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='licenses'",
442                [],
443                |row| row.get(0),
444            )
445            .unwrap();
446        assert_eq!(count, 1);
447
448        // Verify schema version
449        let version = Schema::get_version(&conn).unwrap();
450        assert_eq!(version, Some(SCHEMA_VERSION));
451    }
452
453    #[test]
454    fn test_metadata() {
455        let conn = Connection::open_in_memory().unwrap();
456        Schema::initialize(&conn).unwrap();
457
458        Schema::set_metadata(&conn, "test_key", "test_value").unwrap();
459        let value = Schema::get_metadata(&conn, "test_key").unwrap();
460        assert_eq!(value, Some("test_value".to_string()));
461
462        let missing = Schema::get_metadata(&conn, "nonexistent").unwrap();
463        assert_eq!(missing, None);
464    }
465
466    #[test]
467    fn test_unique_constraints() {
468        let conn = Connection::open_in_memory().unwrap();
469        Schema::initialize(&conn).unwrap();
470
471        // Insert a license
472        conn.execute(
473            "INSERT INTO licenses (unique_system_identifier, call_sign) VALUES (1, 'W1AW')",
474            [],
475        )
476        .unwrap();
477
478        // Insert entity
479        conn.execute(
480            "INSERT INTO entities (unique_system_identifier, entity_type, entity_name) VALUES (1, 'L', 'Test')",
481            [],
482        ).unwrap();
483
484        // Duplicate should fail
485        let result = conn.execute(
486            "INSERT INTO entities (unique_system_identifier, entity_type, entity_name) VALUES (1, 'L', 'Test2')",
487            [],
488        );
489        assert!(result.is_err());
490
491        // Different entity_type should succeed
492        conn.execute(
493            "INSERT INTO entities (unique_system_identifier, entity_type, entity_name) VALUES (1, 'C', 'Contact')",
494            [],
495        ).unwrap();
496    }
497
498    #[test]
499    fn test_drop_and_recreate_indexes() {
500        let conn = Connection::open_in_memory().unwrap();
501        Schema::initialize(&conn).unwrap();
502
503        // Count indexes before drop
504        let count_before: i32 = conn
505            .query_row(
506                "SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name LIKE 'idx_%'",
507                [],
508                |row| row.get(0),
509            )
510            .unwrap();
511        assert!(count_before > 0, "Should have indexes after initialize");
512
513        // Drop indexes
514        Schema::drop_indexes(&conn).unwrap();
515
516        // Count indexes after drop
517        let count_after_drop: i32 = conn
518            .query_row(
519                "SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name LIKE 'idx_%'",
520                [],
521                |row| row.get(0),
522            )
523            .unwrap();
524        assert_eq!(count_after_drop, 0, "All indexes should be dropped");
525
526        // Recreate indexes
527        Schema::create_indexes(&conn).unwrap();
528
529        // Count indexes after recreate
530        let count_after_recreate: i32 = conn
531            .query_row(
532                "SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name LIKE 'idx_%'",
533                [],
534                |row| row.get(0),
535            )
536            .unwrap();
537        assert_eq!(
538            count_before, count_after_recreate,
539            "All indexes should be recreated"
540        );
541    }
542}
uls_db/schema.rs

uls_db/
schema.rs
