Page MenuHomeDevCentral
Files F11723404

No OneTemporary
Actions

View Options

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..93c33a2
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,3 @@
+/target
+FANTOIR*
+Cargo.lock
diff --git a/Cargo.toml b/Cargo.toml
new file mode 100644
index 0000000..0ab04e3
--- /dev/null
+++ b/Cargo.toml
@@ -0,0 +1,23 @@
+[package]
+name = "fantoir2db"
+version = "0.1.0"
+edition = "2021"
+description = "Generates a Postgres table from FANTOIR raw file"
+authors = [
+ "Sébastien Santoro <dereckson@espace-win.org>"
+]
+license = "BSD-2-Clause"
+
+[dependencies]
+
+[dependencies.clap]
+version = "~4.0.32"
+features = ["derive"]
+
+[dependencies.sqlx]
+version = "~0.6.2"
+features = ["runtime-tokio-native-tls", "postgres"]
+
+[dependencies.tokio]
+version = "~1.23.0"
+features = ["full"]
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..7a1b8e0
--- /dev/null
+++ b/README.md
@@ -0,0 +1,25 @@
+## How to use?
+
+Define your PostgreSQL connection URL in environment:
+
+```
+export DATABASE_URL="postgres://fantoir:fantoir@localhost/fantoir"
+```
+
+## Development
+
+### Build instructions
+
+The PostgreSQL library is required to link against it.
+If not found, you can add the path to the LIB environment variable.
+
+### Prepare a test database
+
+Execute the following queries as postgres user:
+
+```
+CREATE ROLE fantoir WITH PASSWORD 'fantoir' LOGIN;
+CREATE DATABASE fantoir OWNER fantoir;
+```
+
+You can then use the code with the default DATABASE_URL documented above.
diff --git a/src/db.rs b/src/db.rs
new file mode 100644
index 0000000..1f85743
--- /dev/null
+++ b/src/db.rs
@@ -0,0 +1,60 @@
+//! # Utilities for database.
+//!
+//! This module provides helpers to interact with a PostgreSQL database.
+//! Functions expect to work with an executor from sqlx crate.
+
+use sqlx::PgPool;
+use sqlx::postgres::PgPoolOptions;
+
+pub async fn connect_to_db (database_url: &str) -> PgPool {
+ PgPoolOptions::new()
+ .max_connections(3)
+ .connect(database_url)
+ .await
+ .expect("Can't connect to database.")
+}
+
+pub async fn is_table_exists (pool: &PgPool, table: &str) -> bool {
+ let query = r#"
+ SELECT EXISTS (
+ SELECT FROM
+ pg_tables
+ WHERE
+ schemaname = 'public' AND
+ tablename = $1
+ );
+ "#;
+
+ let result: (bool,) = sqlx::query_as(query)
+ .bind(table)
+ .fetch_one(pool)
+ .await
+ .expect("Can't check if table exists.");
+
+ result.0
+}
+
+pub async fn is_table_empty (pool: &PgPool, table: &str) -> bool {
+ let query = r#"
+ SELECT EXISTS (
+ SELECT 1 FROM %%table%%
+ );
+ "#.replace("%%table%%", table);
+
+ let result: (bool,) = sqlx::query_as(&query)
+ .fetch_one(pool)
+ .await
+ .expect("Can't check if table is empty.");
+
+ !result.0
+}
+
+pub async fn truncate_table (pool: &PgPool, table: &str) {
+ let query = format!("TRUNCATE TABLE {};", table);
+
+ sqlx::query(&query)
+ .bind(table)
+ .execute(pool)
+ .await
+ .expect("Can't truncate table.");
+}
diff --git a/src/fantoir.rs b/src/fantoir.rs
new file mode 100644
index 0000000..cb60a5d
--- /dev/null
+++ b/src/fantoir.rs
@@ -0,0 +1,104 @@
+//! # Helper methods for FANTOIR database.
+//!
+//! This module offers a structure for a FANTOIR record, methods to parse the file and export it.
+//! Database functions expect to work with an executor from sqlx crate.
+
+use sqlx::PgPool;
+
+/// A voie in the FANTOIR database
+#[derive(Debug)]
+pub struct FantoirEntry {
+ /* Part 1 - commune */
+ departement: i32,
+ code_commune: i32,
+ code_insee: i32,
+ type_commune: i32,
+
+ /* Part 2 - voie */
+
+ /* Part 3 - population */
+
+ /* Part 4 - metadata */
+
+}
+
+impl FantoirEntry {
+ pub fn parse_line(line: &str) -> Self {
+ let departement = match &line[0..2] {
+ "97" => line[0..2].parse().expect("Can't parse DOM/TOM department"),
+ department => department.parse().expect("Can't parse department"),
+ };
+ let code_insee = format!("{:02}{:03}", &line[0..2], &line[3..6])
+ .parse()
+ .expect("Can't parse INSEE code");
+
+ Self {
+ /* Part 1 - commune */
+ departement,
+ code_commune: line[3..6].parse().expect("Can't parse code commune"),
+ code_insee,
+ type_commune: 0,
+
+ /* Part 2 - voie */
+
+ /* Part 3 - population */
+
+ /* Part 4 - metadata */
+
+ }
+ }
+
+ pub async fn insert_to_db(&self, pool: &PgPool, table: &str) {
+ let mut query = format!("INSERT INTO {}", table);
+ query.push_str(
+ r#"
+ (departement, code_commune, code_insee, type_commune)
+ VALUES
+ ($1, $2, $3, $4)"#
+ );
+
+ sqlx::query(&query)
+
+ /* Part 1 - commune */
+ .bind(&self.departement)
+ .bind(&self.code_commune)
+ .bind(&self.code_insee)
+ .bind(&self.type_commune)
+
+ .execute(pool)
+ .await
+ .expect("Can't insert entry to database");
+ }
+}
+
+/*
+ Python code from Nasqueron DB :: voies still to migrate
+
+ voie = {
+ "voie": {
+ "identifiant_communal_voie": line[6:10],
+ "cle_rivoli": line[10:11],
+ "code_nature_voie": code_nature_voie,
+ "nature_voie": self.get_nature_voie(code_nature_voie),
+ "libelle_voie": line[15:41].strip(),
+
+ "RUR": "pseudo-recensée" if line[45:46] == "3" else "",
+
+ "type": type,
+ "type_description": self.get_type_name(type),
+ "public": line[48:49] == "0",
+ },
+ "population": {
+ "is_large": line[49:50] == "*",
+ "à part": int(line[59:66]),
+ "fictive": int(line[66:73]),
+ },
+ "metadata": {
+ "cancelled": line[73:74] != " ",
+ "cancel_date": line[74:81],
+ "creation_date": line[81:88],
+ "MAJIC": line[103:108],
+ "last_alpha_word": line[112:120],
+ }
+ }
+ */
diff --git a/src/main.rs b/src/main.rs
new file mode 100644
index 0000000..ab89a60
--- /dev/null
+++ b/src/main.rs
@@ -0,0 +1,112 @@
+use std::env;
+use std::process::exit;
+
+use clap::Parser;
+use sqlx::PgPool;
+use tokio::fs::File;
+use tokio::io::{AsyncBufReadExt, BufReader};
+
+use crate::db::*;
+use crate::fantoir::FantoirEntry;
+
+mod db;
+mod fantoir;
+
+#[derive(Parser,Default,Debug)]
+#[clap(author="Nasqueron project", version, about="Import FANTOIR database into PostgreSQL")]
+struct Arguments {
+ /// Create table if it doesn't exist
+ #[arg(short = 'c')]
+ create_table: bool,
+
+ /// Truncate table if it already exists, allowing the overwrite mode.
+ /// If not specified, the script will fail if table exists.
+ /// If the table is truncated, the id primary key field will start to a greater value than 1.
+ #[arg(short = 't')]
+ overwrite_table: bool,
+
+ /// The FANTOIR file to import
+ fantoir_file: String,
+
+ /// The name of the table to populate
+ fantoir_table: String,
+}
+
+#[tokio::main]
+async fn main() {
+ let args = Arguments::parse(); // Will exit if argument is missing or --help/--version provided.
+
+ let database_url = env::var("DATABASE_URL")
+ .expect("The environment variable DATABASE_URL need to be set to your PostgreSQL database.");
+
+ import(&args, &database_url).await;
+}
+
+async fn import(args: &Arguments, database_url: &str) {
+ let fd = File::open(&args.fantoir_file).await.expect("Can't open file.");
+ let pool = connect_to_db(database_url).await;
+
+ // Create/truncate table as needed and as allowed by options
+ if let Err(error) = initialize_table(args, &pool).await {
+ eprintln!("{}", &error);
+ exit(1);
+ }
+
+ // Currently, async closures are unstable, see https://github.com/rust-lang/rust/issues/62290
+ // They are also largely unimplemented. As such, this code doesn't follow HOF pattern.
+ let mut buffer = BufReader::new(fd).lines();
+ while let Ok(line) = buffer.next_line().await {
+ if line.is_none() {
+ break;
+ }
+ let line = line.unwrap();
+
+ if line.len() < 90 {
+ // This record is the header or describes a department or a commune
+ continue;
+ }
+
+ let entry = FantoirEntry::parse_line(&line);
+ println!("{:?}", entry);
+ entry.insert_to_db(&pool, &args.fantoir_table).await
+ }
+}
+
+async fn initialize_table(args: &Arguments, pool: &PgPool) -> Result<(), String> {
+ if is_table_exists(pool, &args.fantoir_table).await {
+ if is_table_empty(&pool, &args.fantoir_table).await {
+ return Ok(());
+ }
+
+ if args.overwrite_table {
+ truncate_table(&pool, &args.fantoir_table).await;
+ return Ok(());
+ }
+
+ return Err(format!(
+ "Table {} already exists and contains rows. To overwrite it, run the import tool with -t option.",
+ &args.fantoir_table
+ ));
+ }
+
+ if args.create_table {
+ create_table(&pool, &args.fantoir_table).await;
+ return Ok(());
+ }
+
+ Err(format!(
+ "Table {} doesn't exist. To create it, run the import tool with -c option.",
+ &args.fantoir_table
+ ))
+}
+
+async fn create_table(pool: &PgPool, table: &str) {
+ let query = include_str!("schema.sql")
+ .replace("/*table*/fantoir", table);
+
+ println!("{}", &query);
+ sqlx::query(&query)
+ .execute(pool)
+ .await
+ .expect("Can't create fantoir table.");
+}
diff --git a/src/schema.sql b/src/schema.sql
new file mode 100644
index 0000000..964be4a
--- /dev/null
+++ b/src/schema.sql
@@ -0,0 +1,9 @@
+CREATE TABLE IF NOT EXISTS /*table*/fantoir (
+ id bigserial,
+
+ -- Part 1 - commune
+ departement integer,
+ code_commune integer,
+ code_insee integer,
+ type_commune integer
+);

File Metadata

Mime Type
text/x-diff
Expires
Thu, Sep 18, 10:17 (12 h, 45 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
2987990
Default Alt Text
(10 KB)

Event Timeline

Nasqueron DevCentral · If it had been much bigger the moon would have had a core of ice. · Powered by Phabricator