1  use anyhow::Result;
  2  use r2d2::Pool;
  3  use r2d2_sqlite::SqliteConnectionManager;
  4  use sqlite::Transaction;
  5  
  6  pub struct Table {
  7      pub id: i64,
  8      //pub profile_id: i64,
  9      pub pem: String,
 10  }
 11  
 12  /// Storage for Gemini auth certificates
 13  pub struct Database {
 14      pool: Pool<SqliteConnectionManager>,
 15      profile_id: i64,
 16  }
 17  
 18  impl Database {
 19      // Constructors
 20  
 21      /// Create new `Self`
 22      pub fn build(pool: &Pool<SqliteConnectionManager>, profile_id: i64) -> Self {
 23          Self {
 24              pool: pool.clone(),
 25              profile_id,
 26          }
 27      }
 28  
 29      // Actions
 30  
 31      /// Create new record in database
 32      pub fn add(&self, pem: &str) -> Result<i64> {
 33          // Begin new transaction
 34          let mut connection = self.pool.get()?;
 35          let tx = connection.transaction()?;
 36  
 37          // Create new record
 38          insert(&tx, self.profile_id, pem)?;
 39  
 40          // Hold insert ID for result
 41          let id = last_insert_id(&tx);
 42  
 43          // Done
 44          tx.commit()?;
 45          Ok(id)
 46      }
 47  
 48      /// Delete record with given `id` from database
 49      pub fn delete(&self, id: i64) -> Result<()> {
 50          // Begin new transaction
 51          let mut connection = self.pool.get()?;
 52          let tx = connection.transaction()?;
 53  
 54          // Create new record
 55          delete(&tx, id)?;
 56  
 57          // Done
 58          tx.commit()?;
 59          Ok(())
 60      }
 61  
 62      /// Get single record match `id`
 63      pub fn record(&self, id: i64) -> Result<Option<Table>> {
 64          let records = select(&self.pool.get()?.unchecked_transaction()?, self.profile_id)?; // @TODO single record query
 65          for record in records {
 66              if record.id == id {
 67                  return Ok(Some(record));
 68              }
 69          }
 70          Ok(None)
 71      }
 72  
 73      /// Get all records match current `profile_id`
 74      pub fn records(&self) -> Result<Vec<Table>> {
 75          select(&self.pool.get()?.unchecked_transaction()?, self.profile_id)
 76      }
 77  }
 78  
 79  // Low-level DB API
 80  
 81  pub fn init(tx: &Transaction) -> Result<usize> {
 82      Ok(tx.execute(
 83          "CREATE TABLE IF NOT EXISTS `profile_identity`
 84          (
 85              `id`         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 86              `profile_id` INTEGER NOT NULL,
 87              `pem`        TEXT NOT NULL,
 88  
 89              FOREIGN KEY (`profile_id`) REFERENCES `profile`(`id`)
 90          )",
 91          [],
 92      )?)
 93  }
 94  
 95  pub fn insert(tx: &Transaction, profile_id: i64, pem: &str) -> Result<usize> {
 96      Ok(tx.execute(
 97          "INSERT INTO `profile_identity` (
 98              `profile_id`,
 99              `pem`
100          ) VALUES (?, ?)",
101          (profile_id, pem),
102      )?)
103  }
104  
105  pub fn delete(tx: &Transaction, id: i64) -> Result<usize> {
106      Ok(tx.execute("DELETE FROM `profile_identity` WHERE `id` = ?", [id])?)
107  }
108  
109  pub fn select(tx: &Transaction, profile_id: i64) -> Result<Vec<Table>> {
110      let mut stmt = tx.prepare(
111          "SELECT `id`,
112                  `profile_id`,
113                  `pem`
114  
115          FROM `profile_identity` WHERE `profile_id` = ?",
116      )?;
117  
118      let result = stmt.query_map([profile_id], |row| {
119          Ok(Table {
120              id: row.get(0)?,
121              //profile_id: row.get(1)?,
122              pem: row.get(2)?,
123          })
124      })?;
125  
126      let mut records = Vec::new();
127  
128      for record in result {
129          let table = record?;
130          records.push(table);
131      }
132  
133      Ok(records)
134  }
135  
136  pub fn last_insert_id(tx: &Transaction) -> i64 {
137      tx.last_insert_rowid()
138  }