diff options
author | Galen Guyer <galen@galenguyer.com> | 2023-01-23 14:51:53 -0500 |
---|---|---|
committer | Galen Guyer <galen@galenguyer.com> | 2023-01-23 14:51:53 -0500 |
commit | 6d1ab7557f740f0ccd8632da48dce99e59db744b (patch) | |
tree | 50436b3b68a2651de09f1454e982163ceabc0f26 | |
parent | 7e4fa72beca2ab975d65f91514aa6e5b3e2ad7bb (diff) |
have update-db bin handle creating database and indexes properly
-rw-r--r-- | Cargo.lock | 272 | ||||
-rw-r--r-- | Cargo.toml | 2 | ||||
-rw-r--r-- | migrations/01-create-db.sql | 2 | ||||
-rw-r--r-- | migrations/02-create-indexes.sql | 55 | ||||
-rw-r--r-- | migrations/99-delete-indexes.sql | 30 | ||||
-rw-r--r-- | src/bin/update-db.rs | 27 | ||||
-rw-r--r-- | src/db.rs | 20 | ||||
-rw-r--r-- | src/lib.rs | 3 |
8 files changed, 370 insertions, 41 deletions
@@ -48,6 +48,7 @@ name = "artemis" version = "0.1.0" dependencies = [ "anyhow", + "axum", "chrono", "csv", "filetime", @@ -55,6 +56,7 @@ dependencies = [ "itertools", "regex", "serde", + "serde_json", "sqlx", "tokio", "ureq", @@ -62,6 +64,17 @@ dependencies = [ ] [[package]] +name = "async-trait" +version = "0.1.63" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "eff18d764974428cf3a9328e23fc5c986f5fbed46e6cd4cdf42544df5d297ec1" +dependencies = [ + "proc-macro2", + "quote", + "syn", +] + +[[package]] name = "atoi" version = "1.0.0" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -77,6 +90,56 @@ source = "registry+https://github.com/rust-lang/crates.io-index" checksum = "d468802bab17cbc0cc575e9b053f41e72aa36bfa6b7f55e3529ffa43161b97fa" [[package]] +name = "axum" +version = "0.6.3" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "678c5130a507ae3a7c797f9a17393c14849300b8440eac47cdb90a5bdcb3a543" +dependencies = [ + "async-trait", + "axum-core", + "bitflags", + "bytes", + "futures-util", + "http", + "http-body", + "hyper", + "itoa 1.0.5", + "matchit", + "memchr", + "mime", + "percent-encoding", + "pin-project-lite", + "rustversion", + "serde", + "serde_json", + "serde_path_to_error", + "serde_urlencoded", + "sync_wrapper", + "tokio", + "tower", + "tower-http", + "tower-layer", + "tower-service", +] + +[[package]] +name = "axum-core" +version = "0.3.2" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "1cae3e661676ffbacb30f1a824089a8c9150e71017f7e1e38f2aa32009188d34" +dependencies = [ + "async-trait", + "bytes", + "futures-util", + "http", + "http-body", + "mime", + "rustversion", + "tower-layer", + "tower-service", +] + +[[package]] name = "base64" version = "0.13.1" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -387,6 +450,12 @@ dependencies = [ ] [[package]] +name = "fnv" +version = "1.0.7" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "3f9eec918d3f24069decb9af1554cad7c880e2da24a9afd88aca000531ab82c1" + +[[package]] name = "form_urlencoded" version = "1.1.0" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -523,6 +592,69 @@ source = "registry+https://github.com/rust-lang/crates.io-index" checksum = "7f24254aa9a54b5c858eaee2f5bccdb46aaf0e486a595ed5fd8f86ba55232a70" [[package]] +name = "http" +version = "0.2.8" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "75f43d41e26995c17e71ee126451dd3941010b0514a81a9d11f3b341debc2399" +dependencies = [ + "bytes", + "fnv", + "itoa 1.0.5", +] + +[[package]] +name = "http-body" +version = "0.4.5" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "d5f38f16d184e36f2408a55281cd658ecbd3ca05cce6d6510a176eca393e26d1" +dependencies = [ + "bytes", + "http", + "pin-project-lite", +] + +[[package]] +name = "http-range-header" +version = "0.3.0" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "0bfe8eed0a9285ef776bb792479ea3834e8b94e13d615c2f66d03dd50a435a29" + +[[package]] +name = "httparse" +version = "1.8.0" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "d897f394bad6a705d5f4104762e116a75639e470d80901eed05a860a95cb1904" + +[[package]] +name = "httpdate" +version = "1.0.2" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "c4a1e36c821dbe04574f602848a19f742f4fb3c98d40449f11bcad18d6b17421" + +[[package]] +name = "hyper" +version = "0.14.23" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "034711faac9d2166cb1baf1a2fb0b60b1f277f8492fd72176c17f3515e1abd3c" +dependencies = [ + "bytes", + "futures-channel", + "futures-core", + "futures-util", + "http", + "http-body", + "httparse", + "httpdate", + "itoa 1.0.5", + "pin-project-lite", + "socket2", + "tokio", + "tower-service", + "tracing", + "want", +] + +[[package]] name = "iana-time-zone" version = "0.1.53" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -669,12 +801,24 @@ dependencies = [ ] [[package]] +name = "matchit" +version = "0.7.0" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "b87248edafb776e59e6ee64a79086f65890d3510f2c656c000bf2a7e8a0aea40" + +[[package]] name = "memchr" version = "2.5.0" source = "registry+https://github.com/rust-lang/crates.io-index" checksum = "2dffe52ecf27772e601905b7522cb4ef790d2cc203488bbd0e2fe85fcb74566d" [[package]] +name = "mime" +version = "0.3.16" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "2a60c7ce501c71e03a9c9c0d35b861413ae925bd979cc7a4e30d060069aaac8d" + +[[package]] name = "minimal-lexical" version = "0.2.1" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -943,6 +1087,12 @@ dependencies = [ ] [[package]] +name = "rustversion" +version = "1.0.11" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "5583e89e108996506031660fe09baa5011b9dd0341b89029313006d1fb508d70" + +[[package]] name = "ryu" version = "1.0.12" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -991,6 +1141,38 @@ dependencies = [ ] [[package]] +name = "serde_json" +version = "1.0.91" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "877c235533714907a8c2464236f5c4b2a17262ef1bd71f38f35ea592c8da6883" +dependencies = [ + "itoa 1.0.5", + "ryu", + "serde", +] + +[[package]] +name = "serde_path_to_error" +version = "0.1.9" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "26b04f22b563c91331a10074bda3dd5492e3cc39d56bd557e91c0af42b6c7341" +dependencies = [ + "serde", +] + +[[package]] +name = "serde_urlencoded" +version = "0.7.1" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "d3491c14715ca2294c4d6a88f15e84739788c1d030eed8c110436aafdaa2f3fd" +dependencies = [ + "form_urlencoded", + "itoa 1.0.5", + "ryu", + "serde", +] + +[[package]] name = "sha2" version = "0.10.6" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -1170,6 +1352,12 @@ dependencies = [ ] [[package]] +name = "sync_wrapper" +version = "0.1.1" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "20518fe4a4c9acf048008599e464deb21beeae3d3578418951a189c235a7a9a8" + +[[package]] name = "termcolor" version = "1.2.0" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -1278,6 +1466,80 @@ dependencies = [ ] [[package]] +name = "tower" +version = "0.4.13" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "b8fa9be0de6cf49e536ce1851f987bd21a43b771b09473c3549a6c853db37c1c" +dependencies = [ + "futures-core", + "futures-util", + "pin-project", + "pin-project-lite", + "tokio", + "tower-layer", + "tower-service", + "tracing", +] + +[[package]] +name = "tower-http" +version = "0.3.5" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "f873044bf02dd1e8239e9c1293ea39dad76dc594ec16185d0a1bf31d8dc8d858" +dependencies = [ + "bitflags", + "bytes", + "futures-core", + "futures-util", + "http", + "http-body", + "http-range-header", + "pin-project-lite", + "tower", + "tower-layer", + "tower-service", +] + +[[package]] +name = "tower-layer" +version = "0.3.2" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "c20c8dbed6283a09604c3e69b4b7eeb54e298b8a600d4d5ecb5ad39de609f1d0" + +[[package]] +name = "tower-service" +version = "0.3.2" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "b6bc1c9ce2b5135ac7f93c72918fc37feb872bdc6a5533a8b85eb4b86bfdae52" + +[[package]] +name = "tracing" +version = "0.1.37" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "8ce8c33a8d48bd45d624a6e523445fd21ec13d3653cd51f681abf67418f54eb8" +dependencies = [ + "cfg-if", + "log", + "pin-project-lite", + "tracing-core", +] + +[[package]] +name = "tracing-core" +version = "0.1.30" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "24eb03ba0eab1fd845050058ce5e616558e8f8d8fca633e6b163fe25c797213a" +dependencies = [ + "once_cell", +] + +[[package]] +name = "try-lock" +version = "0.2.4" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "3528ecfd12c466c6f163363caf2d02a71161dd5e1cc6ae7b34207ea2d42d81ed" + +[[package]] name = "typenum" version = "1.16.0" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -1368,6 +1630,16 @@ source = "registry+https://github.com/rust-lang/crates.io-index" checksum = "49874b5167b65d7193b8aba1567f5c7d93d001cafc34600cee003eda787e483f" [[package]] +name = "want" +version = "0.3.0" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "1ce8a968cb1cd110d136ff8b819a556d6fb6d919363c61534f6860c7eb172ba0" +dependencies = [ + "log", + "try-lock", +] + +[[package]] name = "wasi" version = "0.10.0+wasi-snapshot-preview1" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -7,6 +7,7 @@ edition = "2021" [dependencies] anyhow = "1.0.68" +axum = "0.6.3" chrono = {version = "0.4.23", features = ["serde"]} csv = "1.1.6" filetime = "0.2.19" @@ -14,6 +15,7 @@ indicatif = "0.17.2" itertools = "0.10.5" regex = "1.7.1" serde = { version = "1.0.152", features = ["derive"] } +serde_json = "1.0.91" sqlx = { version = "0.6.2", features = ["sqlite", "runtime-tokio-rustls", "chrono"] } tokio = { version = "1.24.1", features = ["full"] } ureq = "2.6.1" diff --git a/migrations/01-create-db.sql b/migrations/01-create-db.sql index c3dc59b..c61c2ab 100644 --- a/migrations/01-create-db.sql +++ b/migrations/01-create-db.sql @@ -1,5 +1,3 @@ -.echo on - CREATE TABLE IF NOT EXISTS updates ( id INTEGER PRIMARY KEY, daily BOOLEAN NOT NULL, diff --git a/migrations/02-create-indexes.sql b/migrations/02-create-indexes.sql index 903bfd8..9fa530c 100644 --- a/migrations/02-create-indexes.sql +++ b/migrations/02-create-indexes.sql @@ -1,33 +1,30 @@ -.echo on +CREATE INDEX IF NOT EXISTS idx_amateurs_unique_system_identifier ON amateurs (unique_system_identifier); +CREATE INDEX IF NOT EXISTS idx_amateurs_call_sign ON amateurs (call_sign); +CREATE INDEX IF NOT EXISTS idx_amateurs_operator_class ON amateurs (operator_class); -CREATE INDEX idx_amateurs_unique_system_identifier ON amateurs (unique_system_identifier); -CREATE INDEX idx_amateurs_call_sign ON amateurs (call_sign); -CREATE INDEX idx_amateurs_operator_class ON amateurs (operator_class); +CREATE INDEX IF NOT EXISTS idx_comments_unique_system_identifier ON comments (unique_system_identifier); +CREATE INDEX IF NOT EXISTS idx_comments_call_sign ON comments (call_sign); -CREATE INDEX idx_comments_unique_system_identifier ON comments (unique_system_identifier); -CREATE INDEX idx_comments_call_sign ON comments (call_sign); +CREATE INDEX IF NOT EXISTS idx_entities_unique_system_identifier ON entities (unique_system_identifier); +CREATE INDEX IF NOT EXISTS idx_entities_call_sign ON entities (call_sign); +CREATE INDEX IF NOT EXISTS idx_entities_entity_name ON entities (entity_name); +CREATE INDEX IF NOT EXISTS idx_entities_first_name ON entities (first_name); +CREATE INDEX IF NOT EXISTS idx_entities_last_name ON entities (last_name); +CREATE INDEX IF NOT EXISTS idx_entities_phone ON entities (phone); +CREATE INDEX IF NOT EXISTS idx_entities_email ON entities (email); +CREATE INDEX IF NOT EXISTS idx_entities_street_address ON entities (street_address); +CREATE INDEX IF NOT EXISTS idx_entities_city ON entities (city); +CREATE INDEX IF NOT EXISTS idx_entities_state ON entities (state); +CREATE INDEX IF NOT EXISTS idx_entities_zip_code ON entities (zip_code); +CREATE INDEX IF NOT EXISTS idx_entities_frn ON entities (frn); -CREATE INDEX idx_entities_unique_system_identifier ON entities (unique_system_identifier); -CREATE INDEX idx_entities_call_sign ON entities (call_sign); -CREATE INDEX idx_entities_entity_name ON entities (entity_name); -CREATE INDEX idx_entities_first_name ON entities (first_name); -CREATE INDEX idx_entities_last_name ON entities (last_name); -CREATE INDEX idx_entities_phone ON entities (phone); -CREATE INDEX idx_entities_email ON entities (email); -CREATE INDEX idx_entities_street_address ON entities (street_address); -CREATE INDEX idx_entities_city ON entities (city); -CREATE INDEX idx_entities_state ON entities (state); -CREATE INDEX idx_entities_zip_code ON entities (zip_code); -CREATE INDEX idx_entities_frn ON entities (frn); +CREATE INDEX IF NOT EXISTS idx_headers_unique_system_identifier ON headers (unique_system_identifier); +CREATE INDEX IF NOT EXISTS idx_headers_call_sign ON headers (call_sign); +CREATE INDEX IF NOT EXISTS idx_headers_license_status ON headers (license_status); +CREATE INDEX IF NOT EXISTS idx_headers_grant_date ON headers (grant_date); +CREATE INDEX IF NOT EXISTS idx_headers_expired_date ON headers (expired_date); +CREATE INDEX IF NOT EXISTS idx_headers_certifier_first_name ON headers (certifier_first_name); +CREATE INDEX IF NOT EXISTS idx_headers_certifier_last_name ON headers (certifier_last_name); -CREATE INDEX idx_headers_unique_system_identifier ON headers (unique_system_identifier); -CREATE INDEX idx_headers_call_sign ON headers (call_sign); -CREATE INDEX idx_headers_license_status ON headers (license_status); -CREATE INDEX idx_headers_grant_date ON headers (grant_date); -CREATE INDEX idx_headers_expired_date ON headers (expired_date); -CREATE INDEX idx_headers_certifier_first_name ON headers (certifier_first_name); -CREATE INDEX idx_headers_certifier_last_name ON headers (certifier_last_name); - - -CREATE INDEX idx_history_unique_system_identifier ON history (unique_system_identifier); -CREATE INDEX idx_history_call_sign ON history (call_sign); +CREATE INDEX IF NOT EXISTS idx_history_unique_system_identifier ON history (unique_system_identifier); +CREATE INDEX IF NOT EXISTS idx_history_call_sign ON history (call_sign); diff --git a/migrations/99-delete-indexes.sql b/migrations/99-delete-indexes.sql new file mode 100644 index 0000000..0ce8a67 --- /dev/null +++ b/migrations/99-delete-indexes.sql @@ -0,0 +1,30 @@ +DROP INDEX IF EXISTS idx_amateurs_unique_system_identifier; +DROP INDEX IF EXISTS idx_amateurs_call_sign; +DROP INDEX IF EXISTS idx_amateurs_operator_class; + +DROP INDEX IF EXISTS idx_comments_unique_system_identifier; +DROP INDEX IF EXISTS idx_comments_call_sign; + +DROP INDEX IF EXISTS idx_entities_unique_system_identifier; +DROP INDEX IF EXISTS idx_entities_call_sign; +DROP INDEX IF EXISTS idx_entities_entity_name; +DROP INDEX IF EXISTS idx_entities_first_name; +DROP INDEX IF EXISTS idx_entities_last_name; +DROP INDEX IF EXISTS idx_entities_phone; +DROP INDEX IF EXISTS idx_entities_email; +DROP INDEX IF EXISTS idx_entities_street_address; +DROP INDEX IF EXISTS idx_entities_city; +DROP INDEX IF EXISTS idx_entities_state; +DROP INDEX IF EXISTS idx_entities_zip_code; +DROP INDEX IF EXISTS idx_entities_frn; + +DROP INDEX IF EXISTS idx_headers_unique_system_identifier; +DROP INDEX IF EXISTS idx_headers_call_sign; +DROP INDEX IF EXISTS idx_headers_license_status; +DROP INDEX IF EXISTS idx_headers_grant_date; +DROP INDEX IF EXISTS idx_headers_expired_date; +DROP INDEX IF EXISTS idx_headers_certifier_first_name; +DROP INDEX IF EXISTS idx_headers_certifier_last_name; + +DROP INDEX IF EXISTS idx_history_unique_system_identifier; +DROP INDEX IF EXISTS idx_history_call_sign;
\ No newline at end of file diff --git a/src/bin/update-db.rs b/src/bin/update-db.rs index 660407d..a7f92f4 100644 --- a/src/bin/update-db.rs +++ b/src/bin/update-db.rs @@ -1,10 +1,11 @@ use chrono::{DateTime, Utc}; use regex::Regex; -use sqlx::sqlite::SqlitePool; +use sqlx::sqlite::{SqliteConnectOptions, SqlitePool}; +use std::str::FromStr; use std::{fs, os::unix::prelude::MetadataExt, time::Duration}; -use artemis::{meta, load, Update}; -use artemis::file::{unzip_file, download_file}; +use artemis::file::{download_file, unzip_file}; +use artemis::{load, meta, Update}; const WEEKLY_DUMP_URL: &str = "https://data.fcc.gov/download/pub/uls/complete/l_amat.zip"; const SUNDAY_DUMP_URL: &str = "https://data.fcc.gov/download/pub/uls/daily/l_am_sun.zip"; @@ -134,6 +135,8 @@ async fn load_weekly(db: &SqlitePool) -> chrono::DateTime<Utc> { ) .expect("Error writing file"); + artemis::db::delete_indexes(db).await.expect("Error deleting indexes"); + load::load_amateurs(db, true).await; load::load_comments(db, true).await; load::load_entities(db, true).await; @@ -145,6 +148,8 @@ async fn load_weekly(db: &SqlitePool) -> chrono::DateTime<Utc> { load::load_special_condition_codes(db, true).await; + artemis::db::create_indexes(db).await.expect("Error creating indexes"); + let meta = output_file.metadata().unwrap(); // std::fs::remove_file("l_amat.zip").expect("Error deleting l_amat.zip"); DateTime::<Utc>::from( @@ -152,10 +157,8 @@ async fn load_weekly(db: &SqlitePool) -> chrono::DateTime<Utc> { ) } - async fn load_daily(url: &str, db: &SqlitePool) -> chrono::DateTime<Utc> { - let output_file = - download_file(url, None).expect("Error downloading weekly dump file"); + let output_file = download_file(url, None).expect("Error downloading weekly dump file"); unzip_file(&output_file).expect("Error unzipping file"); std::fs::remove_file("counts").expect("Error deleting counts file"); @@ -180,9 +183,15 @@ async fn load_daily(url: &str, db: &SqlitePool) -> chrono::DateTime<Utc> { #[tokio::main] async fn main() { - let db = SqlitePool::connect("sqlite://fcc.db") - .await - .expect("Error connecting to database"); + let db = SqlitePool::connect_with( + SqliteConnectOptions::from_str("sqlite://fcc.db") + .expect("improperly formatted sqlite connection string, somehow") + .create_if_missing(true), + ) + .await + .expect("Error connecting to database"); + + artemis::db::create_db(&db).await.expect("Error creating database"); let fcc_updates = dbg!(FccUpdates::new()); diff --git a/src/db.rs b/src/db.rs new file mode 100644 index 0000000..8528868 --- /dev/null +++ b/src/db.rs @@ -0,0 +1,20 @@ +use sqlx::SqlitePool; + +const CREATE_DB_SQL: &str = include_str!("../migrations/01-create-db.sql"); +const CREATE_INDEXES_SQL: &str = include_str!("../migrations/02-create-indexes.sql"); +const DELETE_INDEXES_SQL: &str = include_str!("../migrations/99-delete-indexes.sql"); + +pub async fn create_db(db: &SqlitePool) -> anyhow::Result<()> { + sqlx::query(CREATE_DB_SQL).execute(db).await?; + Ok(()) +} + +pub async fn create_indexes(db: &SqlitePool) -> anyhow::Result<()> { + sqlx::query(CREATE_INDEXES_SQL).execute(db).await?; + Ok(()) +} + +pub async fn delete_indexes(db: &SqlitePool) -> anyhow::Result<()> { + sqlx::query(DELETE_INDEXES_SQL).execute(db).await?; + Ok(()) +} @@ -1,7 +1,8 @@ +pub mod db; pub mod fcc_date; pub mod file; pub mod load; pub mod meta; pub mod types; -pub use types::*;
\ No newline at end of file +pub use types::*; |