1  use anyhow::Result;
  2  use gtk::glib::DateTime;
  3  use r2d2::Pool;
  4  use r2d2_sqlite::SqliteConnectionManager;
  5  use sqlite::Transaction;
  6  
  7  pub struct Table {
  8      pub id: i64,
  9      pub is_active: bool,
 10      pub time: DateTime,
 11      pub name: Option<String>,
 12  }
 13  
 14  pub struct Database {
 15      pub pool: Pool<SqliteConnectionManager>,
 16  }
 17  
 18  impl Database {
 19      // Constructors
 20  
 21      /// Create new `Self`
 22      pub fn build(pool: &Pool<SqliteConnectionManager>) -> Self {
 23          Self { pool: pool.clone() }
 24      }
 25  
 26      // Getters
 27  
 28      /// Get all records
 29      pub fn records(&self) -> Result<Vec<Table>> {
 30          select(&self.pool.get()?.unchecked_transaction()?)
 31      }
 32  
 33      /// Get active profile record if exist
 34      pub fn active(&self) -> Result<Option<Table>> {
 35          let records = self.records()?;
 36          Ok(records.into_iter().find(|record| record.is_active))
 37      }
 38  
 39      // Setters
 40  
 41      /// Create new record in `Self` database connected
 42      pub fn add(&self, is_active: bool, time: DateTime, name: Option<String>) -> Result<i64> {
 43          let mut connection = self.pool.get()?;
 44          let tx = connection.transaction()?;
 45          if is_active {
 46              for record in select(&tx)? {
 47                  update(&tx, record.id, false, record.time, record.name)?;
 48              }
 49          }
 50          let id = insert(&tx, is_active, time, name)?;
 51          tx.commit()?;
 52          Ok(id)
 53      }
 54  }
 55  
 56  // Low-level DB API
 57  
 58  pub fn init(tx: &Transaction) -> Result<usize> {
 59      Ok(tx.execute(
 60          "CREATE TABLE IF NOT EXISTS `profile`
 61          (
 62              `id`        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 63              `is_active` INTEGER NOT NULL,
 64              `time`      INTEGER NOT NULL,
 65              `name`      VARCHAR(255)
 66          )",
 67          [],
 68      )?)
 69  }
 70  
 71  pub fn insert(
 72      tx: &Transaction,
 73      is_active: bool,
 74      time: DateTime,
 75      name: Option<String>,
 76  ) -> Result<i64> {
 77      tx.execute(
 78          "INSERT INTO `profile` (
 79              `is_active`,
 80              `time`,
 81              `name`
 82          ) VALUES (?, ?, ?)",
 83          (is_active, time.to_unix(), name),
 84      )?;
 85      Ok(tx.last_insert_rowid())
 86  }
 87  
 88  pub fn update(
 89      tx: &Transaction,
 90      id: i64,
 91      is_active: bool,
 92      time: DateTime,
 93      name: Option<String>,
 94  ) -> Result<usize> {
 95      Ok(tx.execute(
 96          "UPDATE `profile` SET `is_active` = ?, `time` = ?, `name` = ? WHERE `id` = ?",
 97          (is_active, time.to_unix(), name, id),
 98      )?)
 99  }
100  
101  pub fn select(tx: &Transaction) -> Result<Vec<Table>> {
102      let mut stmt = tx.prepare("SELECT `id`, `is_active`, `time`, `name` FROM `profile`")?;
103      let result = stmt.query_map([], |row| {
104          Ok(Table {
105              id: row.get(0)?,
106              is_active: row.get(1)?,
107              time: DateTime::from_unix_local(row.get(2)?).unwrap(),
108              name: row.get(3)?,
109          })
110      })?;
111  
112      let mut records = Vec::new();
113  
114      for record in result {
115          let table = record?;
116          records.push(table);
117      }
118  
119      Ok(records)
120  }