🐝
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

518 lines
18 KiB

  1. /*
  2. * meli - sqlite3.rs
  3. *
  4. * Copyright 2019 Manos Pitsidianakis
  5. *
  6. * This file is part of meli.
  7. *
  8. * meli is free software: you can redistribute it and/or modify
  9. * it under the terms of the GNU General Public License as published by
  10. * the Free Software Foundation, either version 3 of the License, or
  11. * (at your option) any later version.
  12. *
  13. * meli is distributed in the hope that it will be useful,
  14. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  16. * GNU General Public License for more details.
  17. *
  18. * You should have received a copy of the GNU General Public License
  19. * along with meli. If not, see <http://www.gnu.org/licenses/>.
  20. */
  21. /*! Use an sqlite3 database for fast searching.
  22. */
  23. use crate::melib::ResultIntoMeliError;
  24. use melib::search::{
  25. escape_double_quote,
  26. Query::{self, *},
  27. };
  28. use melib::{
  29. backends::{MailBackend, ResultFuture},
  30. email::{Envelope, EnvelopeHash},
  31. log,
  32. sqlite3::{
  33. self as melib_sqlite3,
  34. rusqlite::{self, params},
  35. DatabaseDescription,
  36. },
  37. thread::{SortField, SortOrder},
  38. MeliError, Result, ERROR,
  39. };
  40. use smallvec::SmallVec;
  41. use std::convert::TryInto;
  42. use std::path::PathBuf;
  43. use std::sync::{Arc, RwLock};
  44. const DB: DatabaseDescription = DatabaseDescription {
  45. name: "index.db",
  46. init_script:Some( "CREATE TABLE IF NOT EXISTS envelopes (
  47. id INTEGER PRIMARY KEY,
  48. account_id INTEGER REFERENCES accounts ON UPDATE CASCADE,
  49. hash BLOB NOT NULL UNIQUE,
  50. date TEXT NOT NULL,
  51. _from TEXT NOT NULL,
  52. _to TEXT NOT NULL,
  53. cc TEXT NOT NULL,
  54. bcc TEXT NOT NULL,
  55. subject TEXT NOT NULL,
  56. message_id TEXT NOT NULL,
  57. in_reply_to TEXT NOT NULL,
  58. _references TEXT NOT NULL,
  59. flags INTEGER NOT NULL,
  60. has_attachments BOOLEAN NOT NULL,
  61. body_text TEXT NOT NULL,
  62. timestamp BLOB NOT NULL
  63. );
  64. CREATE TABLE IF NOT EXISTS folders (
  65. id INTEGER PRIMARY KEY,
  66. account_id INTEGER NOT NULL REFERENCES accounts ON UPDATE CASCADE,
  67. hash BLOB NOT NULL,
  68. date TEXT NOT NULL,
  69. name TEXT NOT NULL
  70. );
  71. CREATE TABLE IF NOT EXISTS accounts (
  72. id INTEGER PRIMARY KEY,
  73. name TEXT NOT NULL UNIQUE
  74. );
  75. CREATE TABLE IF NOT EXISTS folder_and_envelope (
  76. folder_id INTEGER NOT NULL,
  77. envelope_id INTEGER NOT NULL,
  78. PRIMARY KEY (folder_id, envelope_id),
  79. FOREIGN KEY(folder_id) REFERENCES folders(id) ON UPDATE CASCADE,
  80. FOREIGN KEY(envelope_id) REFERENCES envelopes(id) ON UPDATE CASCADE
  81. );
  82. CREATE INDEX IF NOT EXISTS folder_env_idx ON folder_and_envelope(folder_id);
  83. CREATE INDEX IF NOT EXISTS env_folder_idx ON folder_and_envelope(envelope_id);
  84. CREATE UNIQUE INDEX IF NOT EXISTS acc_idx ON accounts(name);
  85. CREATE INDEX IF NOT EXISTS envelope_timestamp_index ON envelopes (timestamp);
  86. CREATE INDEX IF NOT EXISTS envelope__from_index ON envelopes (_from);
  87. CREATE INDEX IF NOT EXISTS envelope__to_index ON envelopes (_to);
  88. CREATE INDEX IF NOT EXISTS envelope_cc_index ON envelopes (cc);
  89. CREATE INDEX IF NOT EXISTS envelope_bcc_index ON envelopes (bcc);
  90. CREATE INDEX IF NOT EXISTS envelope_message_id_index ON envelopes (message_id);
  91. CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts5(subject, body_text, content=envelopes, content_rowid=id);
  92. -- Triggers to keep the FTS index up to date.
  93. CREATE TRIGGER IF NOT EXISTS envelopes_ai AFTER INSERT ON envelopes BEGIN
  94. INSERT INTO fts(rowid, subject, body_text) VALUES (new.id, new.subject, new.body_text);
  95. END;
  96. CREATE TRIGGER IF NOT EXISTS envelopes_ad AFTER DELETE ON envelopes BEGIN
  97. INSERT INTO fts(fts, rowid, subject, body_text) VALUES('delete', old.id, old.subject, old.body_text);
  98. END;
  99. CREATE TRIGGER IF NOT EXISTS envelopes_au AFTER UPDATE ON envelopes BEGIN
  100. INSERT INTO fts(fts, rowid, subject, body_text) VALUES('delete', old.id, old.subject, old.body_text);
  101. INSERT INTO fts(rowid, subject, body_text) VALUES (new.id, new.subject, new.body_text);
  102. END; "),
  103. version: 1,
  104. };
  105. pub fn db_path() -> Result<PathBuf> {
  106. melib_sqlite3::db_path(DB.name)
  107. }
  108. //#[inline(always)]
  109. //fn fts5_bareword(w: &str) -> Cow<str> {
  110. // if w == "AND" || w == "OR" || w == "NOT" {
  111. // Cow::from(w)
  112. // } else {
  113. // if !w.is_ascii() {
  114. // Cow::from(format!("\"{}\"", escape_double_quote(w)))
  115. // } else {
  116. // for &b in w.as_bytes() {
  117. // if !(b > 0x2f && b < 0x3a)
  118. // || !(b > 0x40 && b < 0x5b)
  119. // || !(b > 0x60 && b < 0x7b)
  120. // || b != 0x60
  121. // || b != 26
  122. // {
  123. // return Cow::from(format!("\"{}\"", escape_double_quote(w)));
  124. // }
  125. // }
  126. // Cow::from(w)
  127. // }
  128. // }
  129. //}
  130. //
  131. //
  132. pub async fn insert(
  133. envelope: Envelope,
  134. backend: Arc<RwLock<Box<dyn MailBackend>>>,
  135. acc_name: String,
  136. ) -> Result<()> {
  137. let db_path = db_path()?;
  138. if !db_path.exists() {
  139. return Err(MeliError::new(
  140. "Database hasn't been initialised. Run `reindex` command",
  141. ));
  142. }
  143. let conn = melib_sqlite3::open_db(db_path)?;
  144. let op = backend
  145. .read()
  146. .unwrap()
  147. .operation(envelope.hash())?
  148. .as_bytes()?;
  149. let body = match op.await.map(|bytes| envelope.body_bytes(&bytes)) {
  150. Ok(body) => body.text(),
  151. Err(err) => {
  152. debug!(
  153. "{}",
  154. format!(
  155. "Failed to open envelope {}: {}",
  156. envelope.message_id_display(),
  157. err.to_string()
  158. )
  159. );
  160. log(
  161. format!(
  162. "Failed to open envelope {}: {}",
  163. envelope.message_id_display(),
  164. err.to_string()
  165. ),
  166. ERROR,
  167. );
  168. return Err(err);
  169. }
  170. };
  171. if let Err(err) = conn.execute(
  172. "INSERT OR IGNORE INTO accounts (name) VALUES (?1)",
  173. params![acc_name,],
  174. ) {
  175. debug!(
  176. "Failed to insert envelope {}: {}",
  177. envelope.message_id_display(),
  178. err.to_string()
  179. );
  180. log(
  181. format!(
  182. "Failed to insert envelope {}: {}",
  183. envelope.message_id_display(),
  184. err.to_string()
  185. ),
  186. ERROR,
  187. );
  188. return Err(MeliError::new(err.to_string()));
  189. }
  190. let account_id: i32 = {
  191. let mut stmt = conn
  192. .prepare("SELECT id FROM accounts WHERE name = ?")
  193. .unwrap();
  194. let x = stmt
  195. .query_map(params![acc_name], |row| row.get(0))
  196. .unwrap()
  197. .next()
  198. .unwrap()
  199. .unwrap();
  200. x
  201. };
  202. if let Err(err) = conn.execute(
  203. "INSERT OR REPLACE INTO envelopes (account_id, hash, date, _from, _to, cc, bcc, subject, message_id, in_reply_to, _references, flags, has_attachments, body_text, timestamp)
  204. VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15)",
  205. params![account_id, envelope.hash().to_be_bytes().to_vec(), envelope.date_as_str(), envelope.field_from_to_string(), envelope.field_to_to_string(), envelope.field_cc_to_string(), envelope.field_bcc_to_string(), envelope.subject().into_owned().trim_end_matches('\u{0}'), envelope.message_id_display().to_string(), envelope.in_reply_to_display().map(|f| f.to_string()).unwrap_or(String::new()), envelope.field_references_to_string(), i64::from(envelope.flags().bits()), if envelope.has_attachments() { 1 } else { 0 }, body, envelope.date().to_be_bytes().to_vec()],
  206. )
  207. .map_err(|e| MeliError::new(e.to_string())) {
  208. debug!(
  209. "Failed to insert envelope {}: {}",
  210. envelope.message_id_display(),
  211. err.to_string()
  212. );
  213. log(
  214. format!(
  215. "Failed to insert envelope {}: {}",
  216. envelope.message_id_display(),
  217. err.to_string()
  218. ),
  219. ERROR,
  220. );
  221. }
  222. Ok(())
  223. }
  224. pub fn remove(env_hash: EnvelopeHash) -> Result<()> {
  225. let db_path = db_path()?;
  226. if !db_path.exists() {
  227. return Err(MeliError::new(
  228. "Database hasn't been initialised. Run `reindex` command",
  229. ));
  230. }
  231. let conn = melib_sqlite3::open_db(db_path)?;
  232. if let Err(err) = conn
  233. .execute(
  234. "DELETE FROM envelopes WHERE hash = ?",
  235. params![env_hash.to_be_bytes().to_vec(),],
  236. )
  237. .map_err(|e| MeliError::new(e.to_string()))
  238. {
  239. debug!(
  240. "Failed to remove envelope {}: {}",
  241. env_hash,
  242. err.to_string()
  243. );
  244. log(
  245. format!(
  246. "Failed to remove envelope {}: {}",
  247. env_hash,
  248. err.to_string()
  249. ),
  250. ERROR,
  251. );
  252. return Err(err);
  253. }
  254. Ok(())
  255. }
  256. pub fn index(context: &mut crate::state::Context, account_index: usize) -> ResultFuture<()> {
  257. let account = &context.accounts[account_index];
  258. let (acc_name, acc_mutex, backend_mutex): (String, Arc<RwLock<_>>, Arc<_>) = (
  259. account.name().to_string(),
  260. account.collection.envelopes.clone(),
  261. account.backend.clone(),
  262. );
  263. let conn = melib_sqlite3::open_or_create_db(&DB, None)?;
  264. let env_hashes = acc_mutex
  265. .read()
  266. .unwrap()
  267. .keys()
  268. .cloned()
  269. .collect::<Vec<_>>();
  270. /* Sleep, index and repeat in order not to block the main process */
  271. Ok(Box::pin(async move {
  272. conn.execute(
  273. "INSERT OR REPLACE INTO accounts (name) VALUES (?1)",
  274. params![acc_name.as_str(),],
  275. )
  276. .chain_err_summary(|| "Failed to update index:")?;
  277. let account_id: i32 = {
  278. let mut stmt = conn
  279. .prepare("SELECT id FROM accounts WHERE name = ?")
  280. .unwrap();
  281. let x = stmt
  282. .query_map(params![acc_name.as_str()], |row| row.get(0))
  283. .unwrap()
  284. .next()
  285. .unwrap()
  286. .unwrap();
  287. x
  288. };
  289. let mut ctr = 0;
  290. debug!(
  291. "{}",
  292. format!(
  293. "Rebuilding {} index. {}/{}",
  294. acc_name,
  295. ctr,
  296. env_hashes.len()
  297. )
  298. );
  299. for chunk in env_hashes.chunks(200) {
  300. ctr += chunk.len();
  301. for env_hash in chunk {
  302. let mut op = backend_mutex.read().unwrap().operation(*env_hash)?;
  303. let bytes = op
  304. .as_bytes()?
  305. .await
  306. .chain_err_summary(|| format!("Failed to open envelope {}", env_hash))?;
  307. let envelopes_lck = acc_mutex.read().unwrap();
  308. if let Some(e) = envelopes_lck.get(&env_hash) {
  309. let body = e.body_bytes(&bytes).text().replace('\0', "");
  310. conn.execute("INSERT OR REPLACE INTO envelopes (account_id, hash, date, _from, _to, cc, bcc, subject, message_id, in_reply_to, _references, flags, has_attachments, body_text, timestamp)
  311. VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15)",
  312. params![account_id, e.hash().to_be_bytes().to_vec(), e.date_as_str(), e.field_from_to_string(), e.field_to_to_string(), e.field_cc_to_string(), e.field_bcc_to_string(), e.subject().into_owned().trim_end_matches('\u{0}'), e.message_id_display().to_string(), e.in_reply_to_display().map(|f| f.to_string()).unwrap_or(String::new()), e.field_references_to_string(), i64::from(e.flags().bits()), if e.has_attachments() { 1 } else { 0 }, body, e.date().to_be_bytes().to_vec()],
  313. ).chain_err_summary(|| format!( "Failed to insert envelope {}", e.message_id_display()))?;
  314. }
  315. }
  316. let sleep_dur = std::time::Duration::from_millis(20);
  317. std::thread::sleep(sleep_dur);
  318. }
  319. Ok(())
  320. }))
  321. }
  322. pub fn search(
  323. query: &Query,
  324. (sort_field, sort_order): (SortField, SortOrder),
  325. ) -> ResultFuture<SmallVec<[EnvelopeHash; 512]>> {
  326. let db_path = db_path()?;
  327. if !db_path.exists() {
  328. return Err(MeliError::new(
  329. "Database hasn't been initialised. Run `reindex` command",
  330. ));
  331. }
  332. let conn = melib_sqlite3::open_db(db_path)?;
  333. let sort_field = match debug!(sort_field) {
  334. SortField::Subject => "subject",
  335. SortField::Date => "timestamp",
  336. };
  337. let sort_order = match debug!(sort_order) {
  338. SortOrder::Asc => "ASC",
  339. SortOrder::Desc => "DESC",
  340. };
  341. let mut stmt = conn
  342. .prepare(
  343. debug!(format!(
  344. "SELECT hash FROM envelopes WHERE {} ORDER BY {} {};",
  345. query_to_sql(&query),
  346. sort_field,
  347. sort_order
  348. ))
  349. .as_str(),
  350. )
  351. .map_err(|e| MeliError::new(e.to_string()))?;
  352. let results = stmt
  353. .query_map(rusqlite::NO_PARAMS, |row| Ok(row.get(0)?))
  354. .map_err(|e| MeliError::new(e.to_string()))?
  355. .map(|r: std::result::Result<Vec<u8>, rusqlite::Error>| {
  356. Ok(u64::from_be_bytes(
  357. r.map_err(|e| MeliError::new(e.to_string()))?
  358. .as_slice()
  359. .try_into()
  360. .map_err(|e: std::array::TryFromSliceError| MeliError::new(e.to_string()))?,
  361. ))
  362. })
  363. .collect::<Result<SmallVec<[EnvelopeHash; 512]>>>();
  364. Ok(Box::pin(async { results }))
  365. }
  366. /// Translates a `Query` to an Sqlite3 expression in a `String`.
  367. pub fn query_to_sql(q: &Query) -> String {
  368. fn rec(q: &Query, s: &mut String) {
  369. match q {
  370. Subject(t) => {
  371. s.push_str("subject LIKE \"%");
  372. s.extend(escape_double_quote(t).chars());
  373. s.push_str("%\" ");
  374. }
  375. From(t) => {
  376. s.push_str("_from LIKE \"%");
  377. s.extend(escape_double_quote(t).chars());
  378. s.push_str("%\" ");
  379. }
  380. To(t) => {
  381. s.push_str("_to LIKE \"%");
  382. s.extend(escape_double_quote(t).chars());
  383. s.push_str("%\" ");
  384. }
  385. Cc(t) => {
  386. s.push_str("cc LIKE \"%");
  387. s.extend(escape_double_quote(t).chars());
  388. s.push_str("%\" ");
  389. }
  390. Bcc(t) => {
  391. s.push_str("bcc LIKE \"%");
  392. s.extend(escape_double_quote(t).chars());
  393. s.push_str("%\" ");
  394. }
  395. AllText(t) => {
  396. s.push_str("body_text LIKE \"%");
  397. s.extend(escape_double_quote(t).chars());
  398. s.push_str("%\" ");
  399. }
  400. And(q1, q2) => {
  401. s.push_str("(");
  402. rec(q1, s);
  403. s.push_str(") AND (");
  404. rec(q2, s);
  405. s.push_str(") ");
  406. }
  407. Or(q1, q2) => {
  408. s.push_str("(");
  409. rec(q1, s);
  410. s.push_str(") OR (");
  411. rec(q2, s);
  412. s.push_str(") ");
  413. }
  414. Not(q) => {
  415. s.push_str("NOT (");
  416. rec(q, s);
  417. s.push_str(") ");
  418. }
  419. Flags(v) => {
  420. let total = v.len();
  421. if total > 1 {
  422. s.push_str("(");
  423. }
  424. for (i, f) in v.iter().enumerate() {
  425. match f.as_str() {
  426. "draft" => {
  427. s.push_str(" (flags & 8 > 0) ");
  428. }
  429. "deleted" | "trashed" => {
  430. s.push_str(" (flags & 6 > 0) ");
  431. }
  432. "flagged" => {
  433. s.push_str(" (flags & 16 > 0) ");
  434. }
  435. "recent" => {
  436. s.push_str(" (flags & 4 == 0) ");
  437. }
  438. "seen" | "read" => {
  439. s.push_str(" (flags & 4 > 0) ");
  440. }
  441. "unseen" | "unread" => {
  442. s.push_str(" (flags & 4 == 0) ");
  443. }
  444. "answered" | "replied" => {
  445. s.push_str(" (flags & 2 > 0) ");
  446. }
  447. "unanswered" => {
  448. s.push_str(" (flags & 2 == 0) ");
  449. }
  450. _ => {
  451. continue;
  452. }
  453. }
  454. if total > 1 && i != total - 1 {
  455. s.push_str(" AND ");
  456. }
  457. }
  458. if total > 1 {
  459. s.push_str(") ");
  460. }
  461. }
  462. HasAttachment => {
  463. s.push_str("has_attachments == 1 ");
  464. }
  465. _ => {}
  466. }
  467. }
  468. let mut ret = String::new();
  469. rec(q, &mut ret);
  470. ret
  471. }
  472. #[test]
  473. fn test_query_to_sql() {
  474. use melib::parsec::Parser;
  475. use melib::search::query;
  476. assert_eq!(
  477. "(subject LIKE \"%test%\" ) AND (body_text LIKE \"%i%\" ) ",
  478. &query_to_sql(&query().parse_complete("subject: test and i").unwrap().1)
  479. );
  480. assert_eq!(
  481. "(subject LIKE \"%github%\" ) OR ((_from LIKE \"%epilys%\" ) AND ((subject LIKE \"%lib%\" ) OR (subject LIKE \"%meli%\" ) ) ) ",
  482. &query_to_sql(
  483. &query()
  484. .parse_complete(
  485. "subject: github or (from: epilys and (subject:lib or subject: meli))"
  486. )
  487. .unwrap()
  488. .1
  489. )
  490. );
  491. }