collie_core/repository/
database.rs

1use rusqlite::Connection as RusqliteConnection;
2use sea_query::{
3    ColumnDef, Expr, ForeignKey, ForeignKeyAction, Iden, Index, SqliteQueryBuilder, Table,
4    TableStatement,
5};
6use std::{
7    path::Path,
8    sync::{Arc, Mutex},
9};
10
11use crate::error::Result;
12
13pub type DbConnection = Arc<Mutex<RusqliteConnection>>;
14
15#[derive(Iden)]
16pub enum Feeds {
17    Table,
18    Id,
19    Title,
20    Link,
21    Status,
22    CheckedAt,
23    FetchOldItems,
24}
25
26#[derive(Iden)]
27pub enum Items {
28    Table,
29    Id,
30    Fingerprint,
31    Author,
32    Title,
33    Description,
34    Link,
35    Status,
36    IsSaved,
37    PublishedAt,
38    Feed,
39}
40
41pub struct Migration {
42    tables: Vec<Vec<TableStatement>>,
43}
44
45impl Default for Migration {
46    fn default() -> Self {
47        Self::new()
48    }
49}
50
51impl Migration {
52    pub fn new() -> Self {
53        Self { tables: Vec::new() }
54    }
55
56    pub fn table(mut self, stmts: Vec<TableStatement>) -> Self {
57        self.tables.push(stmts);
58        self
59    }
60
61    pub fn migrate(&self, db: &RusqliteConnection) -> Result<()> {
62        let sql = self
63            .tables
64            .iter()
65            .map(|stmts| {
66                stmts
67                    .iter()
68                    .map(|stmt| stmt.build(SqliteQueryBuilder))
69                    .collect::<Vec<_>>()
70                    .join(";")
71            })
72            .collect::<Vec<_>>();
73
74        for stmt in sql {
75            let _ = db.execute_batch(&stmt);
76        }
77
78        Ok(())
79    }
80}
81
82pub fn open_connection(path: &Path) -> Result<RusqliteConnection> {
83    Ok(RusqliteConnection::open(path)?)
84}
85
86pub fn feeds_table() -> Vec<TableStatement> {
87    let create_stmt = Table::create()
88        .table(Feeds::Table)
89        .if_not_exists()
90        .col(
91            ColumnDef::new(Feeds::Id)
92                .integer()
93                .not_null()
94                .auto_increment()
95                .primary_key(),
96        )
97        .col(ColumnDef::new(Feeds::Title).text().not_null())
98        .col(ColumnDef::new(Feeds::Link).text().not_null())
99        .col(
100            ColumnDef::new(Feeds::Status)
101                .text()
102                .check(Expr::col(Feeds::Status).is_in(["subscribed", "unsubscribed"]))
103                .not_null()
104                .default("subscribed"),
105        )
106        .col(ColumnDef::new(Feeds::CheckedAt).date_time().not_null())
107        .col(
108            ColumnDef::new(Feeds::FetchOldItems)
109                .boolean()
110                .not_null()
111                .default(true),
112        )
113        .index(
114            Index::create()
115                .unique()
116                .name("uk_feeds_title_link")
117                .col(Feeds::Title)
118                .col(Feeds::Link),
119        )
120        .to_owned();
121
122    let alter_stmt = Table::alter()
123        .table(Feeds::Table)
124        .add_column_if_not_exists(
125            ColumnDef::new(Feeds::FetchOldItems)
126                .boolean()
127                .not_null()
128                .default(true),
129        )
130        .to_owned();
131
132    vec![
133        TableStatement::Create(create_stmt),
134        TableStatement::Alter(alter_stmt),
135    ]
136}
137
138pub fn items_table() -> Vec<TableStatement> {
139    let create_stmt = Table::create()
140        .table(Items::Table)
141        .if_not_exists()
142        .col(
143            ColumnDef::new(Items::Id)
144                .integer()
145                .not_null()
146                .auto_increment()
147                .primary_key(),
148        )
149        .col(
150            ColumnDef::new(Items::Fingerprint)
151                .text()
152                .not_null()
153                .unique_key(),
154        )
155        .col(ColumnDef::new(Items::Author).text())
156        .col(ColumnDef::new(Items::Title).text().not_null())
157        .col(ColumnDef::new(Items::Description).text().not_null())
158        .col(ColumnDef::new(Items::Link).text().not_null())
159        .col(
160            ColumnDef::new(Items::Status)
161                .text()
162                .check(Expr::col(Items::Status).is_in(["unread", "read"]))
163                .not_null()
164                .default("unread"),
165        )
166        .col(
167            ColumnDef::new(Items::IsSaved)
168                .integer()
169                .check(Expr::col(Items::IsSaved).is_in([0, 1]))
170                .not_null()
171                .default(0),
172        )
173        .col(ColumnDef::new(Items::PublishedAt).date_time().not_null())
174        .col(ColumnDef::new(Items::Feed).integer().not_null())
175        .foreign_key(
176            ForeignKey::create()
177                .name("fk_items_feeds")
178                .from(Items::Table, Items::Feed)
179                .to(Feeds::Table, Feeds::Id)
180                .on_delete(ForeignKeyAction::Cascade)
181                .on_update(ForeignKeyAction::Cascade),
182        )
183        .to_owned();
184
185    vec![TableStatement::Create(create_stmt)]
186}
collie_core/repository/database.rs

collie_core/repository/
database.rs
