1  import os
 2  import sqlite3
 3  from multiprocessing import Lock
 4  
 5  from praw.models import Submission
 6  
 7  from blottertrax.value_objects.parsed_submission import ParsedSubmission
 8  
 9  
10  class Database:
11      def __init__(self, lock: Lock):
12          self.lock = lock
13  
14          self.lock.acquire()
15          self.sql = sqlite3.connect('{}/../database/database.db'.format(os.path.dirname(os.path.realpath(__file__))))
16          self.cursor = self.sql.cursor()
17  
18          self.cursor.execute('CREATE TABLE IF NOT EXISTS submissions(id TEXT)')
19          self.cursor.execute('CREATE TABLE IF NOT EXISTS modmail(id TEXT)')
20          self.cursor.execute('CREATE INDEX IF NOT EXISTS postindex on submissions(id)')
21          self.cursor.execute(
22              '''
23              CREATE TABLE IF NOT EXISTS errorCausingSubmissions(
24                  id TEXT NOT NULL,
25                  permalink TEXT,
26                  url TEXT,
27                  postTitle TEXT,
28                  artist TEXT,
29                  songTitle TEXT,
30                  error TEXT
31              )
32              '''
33          )
34          self.lock.release()
35  
36      def save_submission(self, submission: Submission):
37          self.lock.acquire()
38          self.cursor.execute('INSERT INTO submissions VALUES(?)', [submission.id])
39          self.sql.commit()
40          self.lock.release()
41  
42      def log_error_causing_submission(self, parsed: ParsedSubmission, raw: Submission, error):
43          self.lock.acquire()
44          if parsed.success is True:
45              self.cursor.execute('INSERT INTO errorCausingSubmissions VALUES(?, ?, ?, ?, ?, ?, ?)',
46                                  [raw.id, raw.permalink, parsed.url, raw.title, parsed.artist, parsed.track_title,
47                                   error])
48          else:
49              self.cursor.execute('INSERT INTO errorCausingSubmissions VALUES(?, ?, ?, ?, ?, ?, ?)',
50                                  [raw.id, raw.permalink, raw.url, raw.title, "", "", error])
51          self.sql.commit()
52          self.lock.release()
53  
54      def known_submission(self, submission: Submission) -> bool:
55          self.cursor.execute('SELECT id FROM submissions WHERE id == ?', [submission.id])
56  
57          return self.cursor.fetchone() is not None
58  
59      def save_mod_mail(self, mail):
60          self.lock.acquire()
61          self.cursor.execute('INSERT INTO modmail VALUES(?)', [mail.id])
62          self.sql.commit()
63          self.lock.release()
64  
65      def known_mod_mail(self, mail) -> bool:
66          self.cursor.execute('select id from modmail where id == ?', [mail.id])
67          return self.cursor.fetchone() is not None