thisweek_core/
db_sqlite.rs

Help
1use std::path::Path;
2
3use crate::config;
4use crate::models::Item;
5use crate::models::NewItem;
6use crate::models::{ITEM_KIND_GOAL, ITEM_KIND_NOTE};
7use crate::models::{STATUS_DONE, STATUS_UNDONE};
8use crate::prelude::Error as AppError;
9use crate::prelude::Result as AppResult;
10use diesel::dsl::sql;
11use diesel::prelude::*;
12
13use diesel_migrations::{embed_migrations, EmbeddedMigrations, MigrationHarness};
14
15const MIGRATIONS: EmbeddedMigrations = embed_migrations!("./migrations");
16
17pub fn run_migrations() {
18    let connection = &mut establish_connection();
19    connection
20        .run_pending_migrations(MIGRATIONS)
21        .expect("Failed to run migrations");
22}
23
24fn establish_connection() -> SqliteConnection {
25    let database_url = config::get_config().database;
26    SqliteConnection::establish(&database_url)
27        .unwrap_or_else(|_| panic!("Error connecting to {}", database_url))
28}
29
30use diesel::sql_types::Integer;
31
32fn last_inserted_id(conn: &mut SqliteConnection) -> Result<i32, diesel::result::Error> {
33    diesel::select(sql::<Integer>("last_insert_rowid()")).get_result(conn)
34}
35
36pub fn create_item(new_item: &NewItem) -> AppResult<i32> {
37    use crate::schema::items::dsl::*;
38    let conn = &mut establish_connection();
39
40    diesel::insert_into(items)
41        .values(new_item)
42        .execute(conn)
43        .map_err(|e| AppError::DatabaseInsertError(e.to_string()))?;
44
45    // Retrieve last inserted ID
46    last_inserted_id(conn).map_err(|e| AppError::DatabaseInsertError(e.to_string()))
47}
48
49pub fn remove_item(item_id: i32) -> Result<usize, String> {
50    use crate::schema::items::dsl::*;
51    let conn = &mut establish_connection();
52
53    diesel::delete(items.filter(id.eq(item_id)))
54        .execute(conn)
55        .map_err(|err| err.to_string())
56}
57
58pub fn update_items(items: &Vec<Item>) -> Result<usize, String> {
59    println!("updating all self items in database...");
60    let mut count: usize = 0;
61    for item in items {
62        let result = update_item(item);
63        match result {
64            Ok(_) => {
65                count += 1;
66            }
67            Err(e) => {
68                println!("error! {e}");
69            }
70        }
71    }
72    Ok(count)
73}
74
75pub fn update_item(item: &Item) -> Result<usize, String> {
76    let conn = &mut establish_connection();
77
78    // for test
79    // let query = diesel::update(item).set(item);
80    // println!(
81    //     "{}",
82    //     diesel::debug_query::<diesel::sqlite::Sqlite, _>(&query)
83    // );
84
85    // https://diesel.rs/guides/all-about-updates.html
86    diesel::update(item) // gets id from this object here
87        .set(item) // updates all the other fields from this object
88        .execute(conn)
89        .map_err(|err| err.to_string())
90}
91
92pub fn get_item(item_id: i32) -> Result<Item, String> {
93    use crate::schema::items::dsl::*;
94    let conn = &mut establish_connection();
95
96    items
97        .filter(id.eq(item_id))
98        .first(conn)
99        .map_err(|e| e.to_string())
100}
101
102pub fn read_items_between_days(
103    start_day: i32,
104    end_day: i32,
105    week_order: bool,
106    /* for the future: resolution_order: bool */
107) -> AppResult<Vec<Item>> {
108    use crate::schema::items::dsl::*;
109    let conn = &mut establish_connection();
110
111    if week_order {
112        items
113            .filter(day.ge(start_day)) // >=
114            .filter(day.le(end_day)) // <=
115            .order(order_in_week.asc())
116            .select(Item::as_select())
117            .load(conn)
118            .map_err(|e| AppError::DatabaseSelectError(e.to_string()))
119    } else {
120        items
121            .filter(day.ge(start_day)) // >=
122            .filter(day.le(end_day)) // <=
123            .select(Item::as_select())
124            .load(conn)
125            .map_err(|e| AppError::DatabaseSelectError(e.to_string()))
126    }
127}
128
129pub fn read_items_in_calendar_year(_calendar: i32, _year: i32) -> Result<Vec<Item>, String> {
130    use crate::schema::items::dsl::*;
131    let conn = &mut establish_connection();
132
133    items
134        .filter(calendar.eq(_calendar))
135        .filter(year.eq(Some(_year)))
136        .order(order_in_resolution.asc())
137        .select(Item::as_select())
138        .load(conn)
139        .map_err(|e| e.to_string())
140}
141
142pub fn backup_database_file() -> Result<(), String> {
143    let database_url = config::get_config().database;
144    // println!("database_url: {database_url}");
145    let mut timestamp = chrono::Local::now().to_rfc3339_opts(chrono::SecondsFormat::Secs, false);
146    timestamp = timestamp.replace(':', "-");
147    let mut filename = String::from(&database_url);
148    filename.push('.');
149    filename.push_str(&timestamp);
150    filename.push_str(".backup");
151    // println!("filename: {filename}");
152    std::fs::copy(database_url, filename)
153        .map(|_| ())
154        .map_err(|e| e.to_string())
155}
156
157fn check_valid_id_range(id: i32) -> Result<(), String> {
158    if id < 0 {
159        let err = format!("invalid id. ignored. id {id}");
160        println!("error: {err}");
161        Err(err)
162    } else {
163        Ok(())
164    }
165}
166
167pub fn edit_item_text(id: i32, text: String) -> Result<usize, String> {
168    println!("edit_item_text: {}", id);
169    check_valid_id_range(id)?;
170    let mut item = get_item(id)?;
171    if item.kind == ITEM_KIND_GOAL {
172        item.title = Some(text.clone());
173    }
174    if item.kind == ITEM_KIND_NOTE {
175        item.note = Some(text.clone());
176    }
177    update_item(&item)
178}
179
180pub fn toggle_item_state(id: i32) -> Result<usize, String> {
181    println!("toggle_item_state: id: {id}");
182    check_valid_id_range(id)?;
183    let mut item = get_item(id)?;
184    if item.status == Some(STATUS_DONE) {
185        item.status = Some(STATUS_UNDONE)
186    } else {
187        item.status = Some(STATUS_DONE);
188    }
189    update_item(&item)
190}
191
192pub fn update_item_objective_period(
193    id: i32,
194    year: Option<i32>,
195    season: Option<i32>,
196    month: Option<i32>,
197) -> Result<usize, String> {
198    println!("update_item_objective_period: id: {id}, {year:?}, {season:?}, {month:?}");
199    check_valid_id_range(id)?;
200    let mut item = get_item(id)?;
201    item.year = year;
202    item.season = season;
203    item.month = month;
204    update_item(&item)
205}
206
207pub fn update_item_week_ordering_key(id: i32, key: String) -> Result<usize, String> {
208    println!("update_item_week_ordering_key: id: {id}");
209    check_valid_id_range(id)?;
210    let mut item = get_item(id)?;
211    item.order_in_week = Some(key);
212    update_item(&item)
213}
214
215pub fn update_item_year_ordering_key(id: i32, key: String) -> Result<usize, String> {
216    println!("update_item_year_ordering_key: id: {id}");
217    check_valid_id_range(id)?;
218    let mut item = get_item(id)?;
219    item.order_in_resolution = Some(key);
220    update_item(&item)
221}
222
223diesel::table! {
224    sqlite_schema(name) {
225        r#type -> Text,
226        name -> Text,
227        tbl_name -> Text,
228        rootpage -> Nullable<Integer>,
229        sql -> Nullable<Text>,
230    }
231}
232
233#[derive(Queryable, Selectable, Debug)]
234#[diesel(table_name = sqlite_schema)]
235#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
236#[diesel(treat_none_as_null = true)]
237pub struct SqliteSchema {
238    pub r#type: String,
239    pub name: String,
240    pub tbl_name: String,
241    pub rootpage: Option<i32>,
242    pub sql: Option<String>,
243}
244
245pub fn is_correct_db(filepath: &str) -> bool {
246    if !Path::new(&filepath).exists() {
247        return false;
248    }
249    if let Ok(mut conn) = SqliteConnection::establish(filepath) {
250        // todo: get table content...
251        let schema: Result<Vec<SqliteSchema>, diesel::result::Error> = sqlite_schema::table
252            .filter(sqlite_schema::tbl_name.eq("items"))
253            .select(SqliteSchema::as_select())
254            .load(&mut conn);
255        // println!("{:#?}", schema);
256        if let Ok(table) = schema {
257            table.len() == 1
258        } else {
259            false
260        }
261    } else {
262        false
263    }
264}
265
266pub fn create_db(_filepath: &str) -> AppResult<()> {
267    // it seems there is no need to!
268    // the migration, take care of creating initial database!
269    todo!();
270
271    // ensure target directory exists
272    // if let Some(parent) = Path::new(&filepath).parent() {
273    //     fs::create_dir_all(parent).map_err(|_| AppError::DatabaseFileCopyError)?;
274    // }
275}
276
277pub fn search_texts(terms: Vec<&str>) -> QueryResult<Vec<Item>> {
278    // Function to perform the search.
279    use crate::schema::items::dsl::*;
280    let conn = &mut establish_connection();
281
282    // Start building the query.
283    let mut query = items.into_boxed();
284
285    // Add LIKE conditions for each term.
286    for term in terms {
287        let like_term = format!("%{}%", term);
288        query = query.or_filter(title.like(like_term.clone()).or(note.like(like_term)));
289    }
290
291    // Execute the query and return the results.
292    query.load::<Item>(conn)
293}
thisweek_core/db_sqlite.rs

thisweek_core/
db_sqlite.rs
