aboutsummaryrefslogtreecommitdiff
path: root/src/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/db')
-rw-r--r--src/db/categories.rs4
-rw-r--r--src/db/event-color.rs7
-rw-r--r--src/db/event_color.rs14
-rw-r--r--src/db/event_colors.rs12
-rw-r--r--src/db/events.rs64
-rw-r--r--src/db/migrations/01-init.sql (renamed from src/db/migrations/1-init.sql)0
-rw-r--r--src/db/migrations/02-categories.sql (renamed from src/db/migrations/2-categories.sql)0
-rw-r--r--src/db/migrations/03-event-color.sql (renamed from src/db/migrations/3-event-color.sql)0
-rw-r--r--src/db/migrations/04-strict-tables.sql57
-rw-r--r--src/db/mod.rs24
10 files changed, 137 insertions, 45 deletions
diff --git a/src/db/categories.rs b/src/db/categories.rs
index ebefb6d..f81b855 100644
--- a/src/db/categories.rs
+++ b/src/db/categories.rs
@@ -5,7 +5,8 @@ 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 query = r#"SELECT id, name, color FROM categories"#;
+ let mut stmt = conn.prepare(query)?;
let iter = stmt.query_map([], |row| {
Ok(read_category(row.get(0)?, row.get(1)?, row.get(2)?))
@@ -20,6 +21,5 @@ pub fn list(conn: &Connection) -> Result<Vec<Category>> {
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
deleted file mode 100644
index 18612e4..0000000
--- a/src/db/event-color.rs
+++ /dev/null
@@ -1,7 +0,0 @@
-
-
-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
deleted file mode 100644
index 33d350b..0000000
--- a/src/db/event_color.rs
+++ /dev/null
@@ -1,14 +0,0 @@
-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/event_colors.rs b/src/db/event_colors.rs
new file mode 100644
index 0000000..bf7f541
--- /dev/null
+++ b/src/db/event_colors.rs
@@ -0,0 +1,12 @@
+use anyhow::Result;
+use rusqlite::Connection;
+
+pub fn get_default_color(conn: &Connection) -> Result<String> {
+ let mut stmt = conn.prepare("SELECT * FROM event_colors LIMIT 1")?;
+ let mut iter = stmt.query_map([], |row| row.get(0))?;
+
+ match iter.next() {
+ Some(Ok(color)) => Ok(color),
+ _ => Ok("blue".to_string()),
+ }
+}
diff --git a/src/db/events.rs b/src/db/events.rs
index 1721967..7f09466 100644
--- a/src/db/events.rs
+++ b/src/db/events.rs
@@ -1,11 +1,16 @@
use anyhow::Result;
use chrono::{NaiveDate, NaiveTime};
-use rusqlite::{params, Connection};
+use rusqlite::{named_params, Connection};
use uuid::Uuid;
use crate::model::event::Event;
pub fn insert(conn: &Connection, event: &Event) -> Result<()> {
+ let query = r#"
+ INSERT INTO events (id, date, start, end, name, repetition, category, created, updated)
+ VALUES (:id, :date, :start, :end, :name, :repetition, :category, datetime(), datetime())
+ "#;
+
let repetition = match &event.repetition {
Some(r) => Some(serde_json::to_string(&r)?),
None => None,
@@ -14,14 +19,35 @@ pub fn insert(conn: &Connection, event: &Event) -> Result<()> {
let category = event.category.map(|id| id.hyphenated().to_string());
conn.execute(
- "INSERT INTO events (id, date, start, end, name, repetition, category, created, updated) VALUES (?, ?, ?, ?, ?, ?, ?, datetime(), datetime())",
- params![event.id.hyphenated().to_string(), event.date, event.start, event.end, event.name, repetition, category]
+ query,
+ named_params![
+ ":id": event.id.hyphenated().to_string(),
+ ":date": event.date,
+ ":start": event.start,
+ ":end": event.end,
+ ":name": event.name,
+ ":repetition": repetition,
+ ":category": category
+ ],
)?;
Ok(())
}
pub fn update(conn: &Connection, event: &Event) -> Result<()> {
+ let query = r#"
+ UPDATE events
+ SET
+ date = :date,
+ start = :start,
+ end = :end,
+ name = :name,
+ repetition = :repetition,
+ category = :category,
+ updated = datetime()
+ WHERE id = :id
+ "#;
+
let repetition = match &event.repetition {
Some(r) => Some(serde_json::to_string(&r)?),
None => None,
@@ -30,29 +56,35 @@ pub fn update(conn: &Connection, event: &Event) -> Result<()> {
let category = event.category.map(|id| id.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.hyphenated().to_string()]
+ query,
+ named_params![
+ ":date": event.date,
+ ":start": event.start,
+ ":end": event.end,
+ ":name": event.name,
+ ":repetition": repetition,
+ ":category": category,
+ ":id": event.id.hyphenated().to_string()
+ ],
)?;
Ok(())
}
pub fn delete(conn: &Connection, id: &Uuid) -> Result<()> {
- conn.execute(
- "DELETE FROM events WHERE id = ?",
- params![id.hyphenated().to_string()],
- )?;
-
+ let query = r#"DELETE FROM events WHERE id = :id"#;
+ conn.execute(query, named_params![":id": id.hyphenated().to_string()])?;
Ok(())
}
pub fn list_recurring(conn: &Connection) -> Result<Vec<Event>> {
- let mut stmt = conn.prepare(
- "
+ let query = r#"
SELECT id, date, start, end, name, repetition, category
FROM events
- WHERE repetition IS NOT NULL",
- )?;
+ WHERE repetition IS NOT NULL
+ "#;
+
+ let mut stmt = conn.prepare(query)?;
let iter = stmt.query_map([], |row| {
Ok(read_event(
@@ -82,8 +114,8 @@ pub fn list_non_recurring_between(
"
SELECT id, date, start, end, name, category
FROM events
- WHERE
- repetition IS NULL
+ WHERE
+ repetition IS NULL
AND date >= ?
AND date <= ?
",
diff --git a/src/db/migrations/1-init.sql b/src/db/migrations/01-init.sql
index 467e481..467e481 100644
--- a/src/db/migrations/1-init.sql
+++ b/src/db/migrations/01-init.sql
diff --git a/src/db/migrations/2-categories.sql b/src/db/migrations/02-categories.sql
index 0b373d0..0b373d0 100644
--- a/src/db/migrations/2-categories.sql
+++ b/src/db/migrations/02-categories.sql
diff --git a/src/db/migrations/3-event-color.sql b/src/db/migrations/03-event-color.sql
index ec589ea..ec589ea 100644
--- a/src/db/migrations/3-event-color.sql
+++ b/src/db/migrations/03-event-color.sql
diff --git a/src/db/migrations/04-strict-tables.sql b/src/db/migrations/04-strict-tables.sql
new file mode 100644
index 0000000..7a10b31
--- /dev/null
+++ b/src/db/migrations/04-strict-tables.sql
@@ -0,0 +1,57 @@
+-- Categories
+
+ALTER TABLE "categories" RENAME TO "categories_non_strict";
+
+CREATE TABLE IF NOT EXISTS "categories" (
+ "id" TEXT PRIMARY KEY, /* UUID */
+ "name" TEXT NOT NULL UNIQUE,
+ "color" TEXT NOT NULL, /* COLOR */
+ "created" TEXT NOT NULL, /* DATETIME */
+ "updated" TEXT NOT NULL /* DATETIME */
+) STRICT;
+
+INSERT INTO categories (id, name, color, created, updated)
+ SELECT id, name, color, created, updated
+ FROM categories_non_strict;
+
+DROP TABLE categories_non_strict;
+
+-- Event color
+
+CREATE TABLE IF NOT EXISTS "event_colors" (
+ "color" TEXT NOT NULL /* COLOR */
+) STRICT;
+
+INSERT INTO event_colors (color)
+ SELECT color
+ FROM event_color;
+
+DROP TABLE event_color;
+
+-- Events
+
+ALTER TABLE "events" RENAME TO "events_non_strict";
+
+CREATE TABLE IF NOT EXISTS "events" (
+ "id" TEXT PRIMARY KEY, /* UUID */
+ "date" TEXT NOT NULL, /* DATE */
+ "start" TEXT NULL, /* TIME */
+ "end" TEXT NULL, /* TIME */
+ "name" TEXT NOT NULL,
+ "repetition" TEXT NULL, /* JSON */
+ "created" TEXT NOT NULL, /* DATETIME */
+ "updated" TEXT NOT NULL, /* DATETIME */
+ "category" TEXT REFERENCES "categories" ("id")
+) STRICT;
+
+INSERT INTO events (id, date, start, end, name, repetition, created, updated, category)
+ SELECT id, date, start, end, name, repetition, created, updated, category
+ FROM events_non_strict;
+
+DROP TABLE events_non_strict;
+
+DROP INDEX IF EXISTS events_date_index;
+CREATE INDEX events_date_index on events (date);
+
+DROP INDEX IF EXISTS events_repetition_index;
+CREATE INDEX events_repetition_index on events (repetition);
diff --git a/src/db/mod.rs b/src/db/mod.rs
index 20e7f81..0e73f30 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -1,5 +1,5 @@
pub mod categories;
-pub mod event_color;
+pub mod event_colors;
pub mod events;
use anyhow::Result;
@@ -8,11 +8,23 @@ use rusqlite_migration::{Migrations, M};
pub fn init(db_path: &str) -> Result<Connection> {
let mut conn = Connection::open(db_path)?;
+ apply_migrations(&mut conn)?;
+ set_pragma(&conn, "foreign_keys", "ON")?;
+ set_pragma(&conn, "journal_mode", "wal")?;
+ Ok(conn)
+}
+
+fn apply_migrations(conn: &mut Connection) -> Result<()> {
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")),
+ M::up(include_str!("migrations/01-init.sql")),
+ M::up(include_str!("migrations/02-categories.sql")),
+ M::up(include_str!("migrations/03-event-color.sql")),
+ M::up(include_str!("migrations/04-strict-tables.sql")),
]);
- migrations.to_latest(&mut conn)?;
- Ok(conn)
+ migrations.to_latest(conn)?;
+ Ok(())
+}
+
+fn set_pragma(conn: &Connection, key: &str, value: &str) -> Result<()> {
+ Ok(conn.pragma_update(None, key, value)?)
}
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-07 06:51:50 +0000