aboutsummaryrefslogtreecommitdiff
path: root/src/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/db')
-rw-r--r--src/db/categories.rs25
-rw-r--r--src/db/event-color.rs7
-rw-r--r--src/db/event_color.rs14
-rw-r--r--src/db/events.rs139
-rw-r--r--src/db/migrations/2-categories.sql10
-rw-r--r--src/db/migrations/3-event-color.sql5
-rw-r--r--src/db/mod.rs137
7 files changed, 200 insertions, 137 deletions
diff --git a/src/db/categories.rs b/src/db/categories.rs
new file mode 100644
index 0000000..ebefb6d
--- /dev/null
+++ b/src/db/categories.rs
@@ -0,0 +1,25 @@
+use anyhow::Result;
+use rusqlite::Connection;
+use uuid::Uuid;
+
+use crate::model::category::Category;
+
+pub fn list(conn: &Connection) -> Result<Vec<Category>> {
+ let mut stmt = conn.prepare("SELECT id, name, color FROM categories")?;
+
+ let iter = stmt.query_map([], |row| {
+ Ok(read_category(row.get(0)?, row.get(1)?, row.get(2)?))
+ })?;
+
+ let mut res = vec![];
+ for category in iter {
+ res.push(category??)
+ }
+ Ok(res)
+}
+
+fn read_category(id: String, name: String, color: String) -> Result<Category> {
+ let id = Uuid::parse_str(&id)?;
+
+ Ok(Category { id, name, color })
+}
diff --git a/src/db/event-color.rs b/src/db/event-color.rs
new file mode 100644
index 0000000..18612e4
--- /dev/null
+++ b/src/db/event-color.rs
@@ -0,0 +1,7 @@
+
+
+pub fn get_default_color(conn: &Connection) -> Result<String> {
+}
+
+// pub fn set_default_color(conon: &Connection, color: &str) -> Result<()> {
+// }
diff --git a/src/db/event_color.rs b/src/db/event_color.rs
new file mode 100644
index 0000000..33d350b
--- /dev/null
+++ b/src/db/event_color.rs
@@ -0,0 +1,14 @@
+use anyhow::Result;
+use rusqlite::Connection;
+
+pub fn get_default_color(conn: &Connection) -> Result<String> {
+ let mut stmt = conn.prepare("SELECT * FROM event_color LIMIT 1")?;
+
+ let iter = stmt.query_map([], |row| row.get(0))?;
+
+ let mut res = vec![];
+ for color in iter {
+ res.push(color?)
+ }
+ Ok(res.first().unwrap_or(&"blue".to_string()).clone())
+}
diff --git a/src/db/events.rs b/src/db/events.rs
new file mode 100644
index 0000000..86206bb
--- /dev/null
+++ b/src/db/events.rs
@@ -0,0 +1,139 @@
+use anyhow::Result;
+use chrono::{NaiveDate, NaiveTime};
+use rusqlite::{params, Connection};
+use uuid::Uuid;
+
+use crate::model::event::Event;
+
+pub fn insert(conn: &Connection, event: &Event) -> Result<()> {
+ let repetition = match &event.repetition {
+ Some(r) => Some(serde_json::to_string(&r)?),
+ None => None,
+ };
+
+ let category = event.category.map(|id| id.to_hyphenated().to_string());
+
+ conn.execute(
+ "INSERT INTO events (id, date, start, end, name, repetition, category, created, updated) VALUES (?, ?, ?, ?, ?, ?, ?, datetime(), datetime())",
+ params![event.id.to_hyphenated().to_string(), event.date, event.start, event.end, event.name, repetition, category]
+ )?;
+
+ Ok(())
+}
+
+pub fn update(conn: &Connection, event: &Event) -> Result<()> {
+ let repetition = match &event.repetition {
+ Some(r) => Some(serde_json::to_string(&r)?),
+ None => None,
+ };
+
+ let category = event.category.map(|id| id.to_hyphenated().to_string());
+
+ conn.execute(
+ "UPDATE events SET date = ?, start = ?, end = ?, name = ?, repetition = ?, category = ?, updated = datetime() WHERE id = ?",
+ params![event.date, event.start, event.end, event.name, repetition, category, event.id.to_hyphenated().to_string()]
+ )?;
+
+ Ok(())
+}
+
+pub fn delete(conn: &Connection, id: &Uuid) -> Result<()> {
+ conn.execute(
+ "DELETE FROM events WHERE id = ?",
+ params![id.to_hyphenated().to_string()],
+ )?;
+
+ Ok(())
+}
+
+pub fn list_recurring(conn: &Connection) -> Result<Vec<Event>> {
+ let mut stmt = conn.prepare(
+ "
+ SELECT id, date, start, end, name, repetition, category
+ FROM events
+ WHERE repetition IS NOT NULL",
+ )?;
+
+ let iter = stmt.query_map([], |row| {
+ Ok(read_event(
+ row.get(0)?,
+ row.get(1)?,
+ row.get(2)?,
+ row.get(3)?,
+ row.get(4)?,
+ row.get(5)?,
+ row.get(6)?,
+ ))
+ })?;
+
+ let mut res = vec![];
+ for event in iter {
+ res.push(event??)
+ }
+ Ok(res)
+}
+
+pub fn list_non_recurring_between(
+ conn: &Connection,
+ start: NaiveDate,
+ end: NaiveDate,
+) -> Result<Vec<Event>> {
+ let mut stmt = conn.prepare(
+ "
+ SELECT id, date, start, end, name, category
+ FROM events
+ WHERE
+ repetition IS NULL
+ AND date >= ?
+ AND date <= ?
+ ",
+ )?;
+
+ let iter = stmt.query_map([start, end], |row| {
+ Ok(read_event(
+ row.get(0)?,
+ row.get(1)?,
+ row.get(2)?,
+ row.get(3)?,
+ row.get(4)?,
+ None,
+ row.get(5)?,
+ ))
+ })?;
+
+ let mut res = vec![];
+ for event in iter {
+ res.push(event??)
+ }
+ Ok(res)
+}
+
+fn read_event(
+ id: String,
+ date: NaiveDate,
+ start: Option<NaiveTime>,
+ end: Option<NaiveTime>,
+ name: String,
+ repetition: Option<String>,
+ category: Option<String>,
+) -> Result<Event> {
+ let id = Uuid::parse_str(&id)?;
+ let repetition = match repetition {
+ Some(r) => Some(serde_json::from_str(&r)?),
+ None => None,
+ };
+ let category = match category {
+ Some(c) => Some(Uuid::parse_str(&c)?),
+ None => None,
+ };
+
+ Ok(Event {
+ id,
+ date,
+ start,
+ end,
+ name,
+ repetition,
+ category,
+ })
+}
diff --git a/src/db/migrations/2-categories.sql b/src/db/migrations/2-categories.sql
index 27e9699..0b373d0 100644
--- a/src/db/migrations/2-categories.sql
+++ b/src/db/migrations/2-categories.sql
@@ -1,13 +1,11 @@
CREATE TABLE IF NOT EXISTS "categories" (
"id" TEXT PRIMARY KEY, /* UUID */
- "name" TEXT NOT NULL,
- "color" TEXT NOT NULL, /* HEXA */
+ "name" TEXT NOT NULL UNIQUE,
+ "color" TEXT NOT NULL, /* COLOR */
"created" TEXT NOT NULL, /* DATETIME */
"updated" TEXT NOT NULL /* DATETIME */
);
-INSERT INTO
- "categories" ("id", "name", "color", "created", "updated")
- VALUES ("b2253284-1572-43d5-96ec-bf6ad448f46a", "Perso", "#a8c2e0", datetime(), datetime());
-
ALTER TABLE "events" ADD COLUMN "category" TEXT REFERENCES "categories" ("id"); /* UUID */
+
+PRAGMA foreign_keys = ON
diff --git a/src/db/migrations/3-event-color.sql b/src/db/migrations/3-event-color.sql
new file mode 100644
index 0000000..ec589ea
--- /dev/null
+++ b/src/db/migrations/3-event-color.sql
@@ -0,0 +1,5 @@
+CREATE TABLE IF NOT EXISTS "event_color" (
+ "color" TEXT NOT NULL /* COLOR */
+);
+
+INSERT INTO "event_color" ("color") VALUES ("#a8c2e0");
diff --git a/src/db/mod.rs b/src/db/mod.rs
index 101c874..20e7f81 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -1,143 +1,18 @@
+pub mod categories;
+pub mod event_color;
+pub mod events;
+
use anyhow::Result;
-use chrono::{NaiveDate, NaiveTime};
-use rusqlite::{params, Connection};
+use rusqlite::Connection;
use rusqlite_migration::{Migrations, M};
-use uuid::Uuid;
-
-use crate::model::event::Event;
pub fn init(db_path: &str) -> Result<Connection> {
let mut conn = Connection::open(db_path)?;
let migrations = Migrations::new(vec![
M::up(include_str!("migrations/1-init.sql")),
M::up(include_str!("migrations/2-categories.sql")),
+ M::up(include_str!("migrations/3-event-color.sql")),
]);
migrations.to_latest(&mut conn)?;
Ok(conn)
}
-
-pub fn insert(conn: &Connection, event: &Event) -> Result<()> {
- let repetition = match &event.repetition {
- Some(r) => Some(serde_json::to_string(&r)?),
- None => None,
- };
-
- conn.execute(
- "INSERT INTO events (id, date, start, end, name, repetition, created, updated) VALUES (?, ?, ?, ?, ?, ?, datetime(), datetime())",
- params![event.id.to_hyphenated().to_string(), event.date, event.start, event.end, event.name, repetition]
- )?;
-
- Ok(())
-}
-
-pub fn update(conn: &Connection, event: &Event) -> Result<()> {
- let repetition = match &event.repetition {
- Some(r) => Some(serde_json::to_string(&r)?),
- None => None,
- };
-
- conn.execute(
- "UPDATE events SET date = ?, start = ?, end = ?, name = ?, repetition = ?, updated = datetime() WHERE id = ?",
- params![event.date, event.start, event.end, event.name, repetition, event.id.to_hyphenated().to_string()]
- )?;
-
- Ok(())
-}
-
-pub fn delete(conn: &Connection, id: &Uuid) -> Result<()> {
- conn.execute(
- "DELETE FROM events WHERE id = ?",
- params![id.to_hyphenated().to_string()],
- )?;
-
- Ok(())
-}
-
-pub fn list_recurring(conn: &Connection) -> Result<Vec<Event>> {
- let mut stmt = conn.prepare(
- "
- SELECT id, date, start, end, name, repetition
- FROM events
- WHERE repetition IS NOT NULL",
- )?;
-
- let iter = stmt.query_map([], |row| {
- Ok(read_recurring_event(
- row.get(0)?,
- row.get(1)?,
- row.get(2)?,
- row.get(3)?,
- row.get(4)?,
- row.get(5)?,
- ))
- })?;
-
- let mut res = vec![];
- for event in iter {
- res.push(event??)
- }
- Ok(res)
-}
-
-fn read_recurring_event(
- uuid: String,
- date: NaiveDate,
- start: Option<NaiveTime>,
- end: Option<NaiveTime>,
- name: String,
- repetition: Option<String>,
-) -> Result<Event> {
- let id = Uuid::parse_str(&uuid)?;
- let repetition = match repetition {
- Some(r) => Some(serde_json::from_str(&r)?),
- None => None,
- };
-
- Ok(Event {
- id,
- date,
- start,
- end,
- name,
- repetition,
- })
-}
-
-pub fn list_non_recurring_between(
- conn: &Connection,
- start: NaiveDate,
- end: NaiveDate,
-) -> Result<Vec<Event>> {
- let mut stmt = conn.prepare(
- "
- SELECT id, date, start, end, name
- FROM events
- WHERE
- repetition IS NULL
- AND date >= ?
- AND date <= ?
- ",
- )?;
-
- let iter = stmt.query_map([start, end], |row| {
- let uuid: String = row.get(0)?;
- let date = row.get(1)?;
- let start = row.get(2)?;
- let end = row.get(3)?;
- let name = row.get(4)?;
- Ok(Uuid::parse_str(&uuid).map(|id| Event {
- id,
- date,
- start,
- end,
- name,
- repetition: None,
- }))
- })?;
-
- let mut res = vec![];
- for event in iter {
- res.push(event??)
- }
- Ok(res)
-}
generated by cgit v1.2.3 (git 2.25.1) at 2024-05-18 18:41:51 +0000