infra_compass_db/
lib.rs

1#![deny(missing_docs)]
2
3//! NREL's ordinance database
4
5mod error;
6mod scraper;
7
8use duckdb::Connection;
9use serde::Serialize;
10use tracing::{self, trace};
11
12use error::Result;
13
14/// Initialize the database
15///
16/// Create a new database as a local single file ready to store the ordinance
17/// data.
18pub fn init_db(path: &str) -> Result<()> {
19    trace!("Creating a new database at {:?}", &path);
20
21    let mut db = Connection::open(path)?;
22    trace!("Database opened: {:?}", &db);
23
24    db.execute_batch("SET VARIABLE ordinancedb_version = '0.0.1';")?;
25    trace!("Defining ordinance data model version as: 0.0.1");
26
27    /*
28     * Change the source structure to have a database of all sources, and
29     * the run links to the source used. Also link that to the jurisdiction
30     * such that it could later request all sources related to a certain
31     * jurisdiction.
32     *
33     * Multiple sources for the same jurisdiction (consider multiple
34     * technologies) should be possible.
35     *
36     * In case of multiple sources for one jurisdiction, we should be able
37     * to support what was the latest document available, or list all of
38     * them.
39     *
40     * Check the new jurisdiction file.
41     *
42     * If user don't have a database. Download it in the right path.
43     *
44     *
45     */
46    trace!("Creating table bookkeeper");
47    db.execute_batch(
48        "BEGIN;
49    CREATE SEQUENCE bookkeeper_sequence START 1;
50    CREATE TABLE bookkeeper (
51        id INTEGER PRIMARY KEY DEFAULT NEXTVAL('bookkeeper_sequence'),
52        hash TEXT NOT NULL,
53        created_at TIMESTAMP NOT NULL DEFAULT NOW(),
54        username TEXT,
55        comment TEXT,
56        model TEXT
57        );
58
59    INSTALL spatial;
60    LOAD spatial;
61
62    CREATE SEQUENCE jurisdiction_sequence START 1;
63    CREATE TYPE jurisdiction_rank AS ENUM ('state', 'county', 'parish', 'city', 'town', 'district', 'other');
64    CREATE TABLE jurisdiction (
65      id INTEGER PRIMARY KEY DEFAULT NEXTVAL('jurisdiction_sequence'),
66      bookkeeper_lnk INTEGER REFERENCES bookkeeper(id) NOT NULL,
67      name TEXT NOT NULL,
68      FIPS INTEGER NOT NULL,
69      geometry GEOMETRY NOT NULL,
70      rank jurisdiction_rank NOT NULL,
71      parent_id INTEGER REFERENCES jurisdiction(id),
72      created_at TIMESTAMP NOT NULL DEFAULT NOW(),
73      src TEXT,
74      comments TEXT
75      );
76
77    COMMIT;",
78    )?;
79
80    let conn = db.transaction()?;
81    scraper::ScrappedOrdinance::init_db(&conn)?;
82    conn.commit()?;
83
84    println!("{}", db.is_autocommit());
85
86    trace!("Database initialized");
87    Ok(())
88}
89
90/// Scan and load features from a CSV file
91///
92/// Proof of concept. Parse a CSV file and load the features into the
93/// database.
94pub fn load_ordinance<P: AsRef<std::path::Path> + std::fmt::Debug>(
95    mut database: duckdb::Connection,
96    username: &String,
97    ordinance_path: P,
98) -> Result<()> {
99    // insert into bookkeeper (hash, username) and get the pk to be used in all the following
100    // inserts.
101    trace!("Starting a transaction");
102    let conn = database.transaction().unwrap();
103
104    let commit_id: usize = conn
105        .query_row(
106            "INSERT INTO bookkeeper (hash, username) VALUES (?, ?) RETURNING id",
107            ["dummy hash".to_string(), username.to_string()],
108            |row| row.get(0),
109        )
110        .expect("Failed to insert into bookkeeper");
111
112    tracing::debug!("Commit id: {:?}", commit_id);
113
114    /*
115    dbg!(&ordinance_path);
116    let raw_filename = ordinance_path.as_ref().join("ord_db.csv");
117    dbg!(&raw_filename);
118    dbg!("========");
119    */
120
121    let rt = tokio::runtime::Builder::new_current_thread()
122        .enable_all()
123        .build()
124        .unwrap();
125
126    let ordinance = rt
127        .block_on(scraper::ScrappedOrdinance::open(ordinance_path))
128        .unwrap();
129    conn.commit().unwrap();
130    tracing::debug!("Transaction committed");
131
132    trace!("Ordinance: {:?}", ordinance);
133    rt.block_on(ordinance.push(&mut database, commit_id))
134        .unwrap();
135
136    /*
137    let mut rdr = csv::Reader::from_path(raw_filename).unwrap();
138    let mut stmt = conn.prepare_cached("INSERT INTO property (county, state, FIPS, feature, fixed_value, mult_value, mult_type, adder, min_dist, max_dist, value, units, ord_year, last_updated, section, source, comments) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)").unwrap();
139    for result in rdr.records() {
140        let record = result.unwrap();
141        // println!("{:?}", record);
142        stmt.execute([
143            record[0].to_string(),
144            record[1].to_string(),
145            record[2].to_string(),
146            record[3].to_string(),
147            record[4].to_string(),
148            record[5].to_string(),
149            record[6].to_string(),
150            record[7].to_string(),
151            record[8].to_string(),
152            record[9].to_string(),
153            record[10].to_string(),
154            record[11].to_string(),
155            record[12].to_string(),
156            record[13].to_string(),
157            record[14].to_string(),
158            record[15].to_string(),
159            record[16].to_string(),
160        ])
161        .unwrap();
162    }
163
164    */
165    //let df = polars::io::csv::read::CsvReadOptions::default().with_has_header(true).try_into_reader_with_file_path(Some("sample.csv".into())).unwrap().finish();
166
167    Ok(())
168}
169
170#[allow(dead_code, non_snake_case)]
171#[derive(Debug, Serialize)]
172/// Ordinance record that combines quantitative and qualitative
173///
174/// It is currently used for handle reVX ordinance for reVX standard only
175/// but we might expand this in the future to handle more complete outputs.
176struct OrdinanceRecord {
177    /// FIPS code of the jurisdiction
178    FIPS: u32,
179    /// Feature type, e.g., "setback", "height", etc.
180    feature: String,
181    /// Feature subtype, currently not used but required by reVX standard.
182    feature_subtype: Option<String>,
183    /// Quantitative feature value, e.g., 3.14
184    quantitative: Option<f64>,
185    /// Qualitative feature value, e.g., "color of the tips of the blades"
186    qualitative: Option<String>,
187}
188
189#[derive(Debug)]
190/// Technologies supported by the ordinance database
191enum Technology {
192    /// Everything related to wind energy
193    Wind,
194    /// Everything related to solar energy
195    Solar,
196}
197
198impl std::fmt::Display for Technology {
199    fn fmt(&self, f: &mut std::fmt::Formatter) -> std::fmt::Result {
200        match self {
201            Technology::Wind => write!(f, "wind"),
202            Technology::Solar => write!(f, "solar"),
203        }
204    }
205}
206
207/// Export the database
208///
209/// Currently, it is a proof of concept. It reads the database and prints
210/// some fields to the standard output in CSV format.
211pub fn export<W: std::io::Write>(
212    wtr: &mut W,
213    db_filename: &str,
214    format: &str,
215    technology: &str,
216) -> Result<()> {
217    trace!("Exporting database: {:?}", db_filename);
218    // Not used yet, but ready for future use
219    trace!("Export format: {:?}", format);
220
221    let technology = match technology {
222        "wind" => Technology::Wind,
223        "solar" => Technology::Solar,
224        _ => {
225            return Err(error::Error::Undefined(format!(
226                "Unknown technology: {technology}"
227            )));
228        }
229    };
230
231    let conn = Connection::open(db_filename)?;
232    trace!("Database opened: {:?}", &conn);
233
234    let mut stmt = conn
235        .prepare( &format!("SELECT FIPS, feature, feature_subtype, quantitative, qualitative FROM ordinance JOIN scraper_metadata ON (ordinance.bookkeeper_lnk=scraper_metadata.bookkeeper_lnk) WHERE scraper_metadata.technology='{technology}' ORDER BY FIPS, feature;")
236            )
237        .expect("Failed to prepare statement");
238    //dbg!("Row count", stmt.row_count());
239    let row_iter = stmt
240        .query_map([], |row| {
241            Ok(OrdinanceRecord {
242                FIPS: row.get(0)?,
243                feature: row.get(1)?,
244                feature_subtype: row.get(2)?,
245                quantitative: row.get(3)?,
246                qualitative: row.get(4)?,
247            })
248        })
249        .expect("Failed to query");
250
251    let mut wtr = csv::Writer::from_writer(wtr);
252
253    for row in row_iter {
254        wtr.serialize(row?)?;
255    }
256    wtr.flush()?;
257
258    Ok(())
259}
260
261#[cfg(test)]
262mod tests {
263    use super::*;
264
265    #[test]
266    fn it_works() {
267        let _ = init_db("test");
268    }
269}
infra_compass_db/lib.rs

infra_compass_db/
lib.rs
