1  mod row;
  2  
  3  use anyhow::Result;
  4  use r2d2::Pool;
  5  use r2d2_sqlite::SqliteConnectionManager;
  6  use row::Row;
  7  use sqlite::Transaction;
  8  
  9  pub struct Database {
 10      pool: Pool<SqliteConnectionManager>,
 11      profile_id: i64,
 12  }
 13  
 14  impl Database {
 15      // Constructors
 16  
 17      pub fn init(pool: &Pool<SqliteConnectionManager>, profile_id: i64) -> Self {
 18          Self {
 19              pool: pool.clone(),
 20              profile_id,
 21          }
 22      }
 23  
 24      // Getters
 25  
 26      pub fn rows(&self) -> Result<Vec<Row>> {
 27          rows(&self.pool.get()?.unchecked_transaction()?, self.profile_id)
 28      }
 29  
 30      // Setters
 31  
 32      pub fn clean(&self, keep_id: Vec<i64>) -> Result<()> {
 33          let mut c = self.pool.get()?;
 34          let tx = c.transaction()?;
 35          clean(&tx, keep_id)?;
 36          tx.commit()?;
 37          Ok(())
 38      }
 39  
 40      pub fn persist(
 41          &self,
 42          id: Option<i64>,
 43          time: i64,
 44          is_enabled: bool,
 45          host: String,
 46      ) -> Result<i64> {
 47          let mut c = self.pool.get()?;
 48          let tx = c.transaction()?;
 49          let id = match id {
 50              Some(id) => {
 51                  update(&tx, id, time, is_enabled, host)?;
 52                  id
 53              }
 54              None => insert(&tx, self.profile_id, time, is_enabled, host)?,
 55          };
 56          tx.commit()?;
 57          Ok(id)
 58      }
 59  }
 60  
 61  // Low-level DB API
 62  
 63  pub fn init(tx: &Transaction) -> Result<usize> {
 64      Ok(tx.execute(
 65          "CREATE TABLE IF NOT EXISTS `profile_proxy_ignore`
 66          (
 67              `id`         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 68              `profile_id` INTEGER NOT NULL,
 69              `time`       INTEGER NOT NULL,
 70              `is_enabled` INTEGER NOT NULL,
 71              `host`       VARCHAR(255) NOT NULL,
 72  
 73              FOREIGN KEY (`profile_id`) REFERENCES `profile` (`id`)
 74          )",
 75          [],
 76      )?)
 77  }
 78  
 79  fn clean(tx: &Transaction, keep_id: Vec<i64>) -> Result<usize> {
 80      if keep_id.is_empty() {
 81          return Ok(0);
 82      }
 83      Ok(tx.execute(
 84          &format!(
 85              "DELETE FROM `profile_proxy_ignore` WHERE `id` NOT IN ({})",
 86              keep_id
 87                  .into_iter()
 88                  .map(|id| id.to_string())
 89                  .collect::<Vec<String>>()
 90                  .join(",")
 91          ),
 92          [],
 93      )?)
 94  }
 95  
 96  fn insert(
 97      tx: &Transaction,
 98      profile_id: i64,
 99      time: i64,
100      is_enabled: bool,
101      host: String,
102  ) -> Result<i64> {
103      tx.execute(
104          "INSERT INTO `profile_proxy_ignore` (
105              `profile_id`,
106              `time`,
107              `is_enabled`,
108              `host`
109          ) VALUES (?, ?, ?, ?)",
110          (profile_id, time, is_enabled, host),
111      )?;
112      Ok(tx.last_insert_rowid())
113  }
114  
115  fn update(tx: &Transaction, id: i64, time: i64, is_enabled: bool, host: String) -> Result<usize> {
116      Ok(tx.execute(
117          "UPDATE `profile_proxy_ignore`
118              SET `time` = ?,
119                  `is_enabled` = ?,
120                  `host` = ?
121  
122              WHERE `id` = ?",
123          (time, is_enabled, host, id),
124      )?)
125  }
126  
127  fn rows(tx: &Transaction, profile_id: i64) -> Result<Vec<Row>> {
128      let mut stmt = tx.prepare(
129          "SELECT `id`,
130                  `profile_id`,
131                  `time`,
132                  `host`,
133                  `is_enabled`
134  
135                  FROM `profile_proxy_ignore`
136                  WHERE `profile_id` = ?",
137      )?;
138  
139      let result = stmt.query_map([profile_id], |row| {
140          Ok(Row {
141              id: row.get(0)?,
142              //profile_id: row.get(1)?,
143              time: row.get(2)?,
144              host: row.get(3)?,
145              is_enabled: row.get(4)?,
146          })
147      })?;
148  
149      let mut rows = Vec::new();
150  
151      for r in result {
152          let row = r?;
153          rows.push(row);
154      }
155  
156      Ok(rows)
157  }