Module: MailGrabber::DatabaseHelper

Includes:
DatabaseQueries
Included in:
DeliveryMethod, Web::Application
Defined in:
lib/mail_grabber/database_helper.rb

Constant Summary collapse

DATABASE = 
{
  folder: 'tmp',
  filename: 'mail_grabber.sqlite3',
  params: {
    type_translation: true,
    results_as_hash: true
  }
}.freeze

Instance Method Summary collapse

Methods included from DatabaseQueries

#create_mail_part_table, #create_mail_table, #insert_into_mail_part_query, #insert_into_mail_query, #select_messages_with_pagination_query

Instance Method Details

#connection ⇒ Object

Create a connection to the SQLite3 database. Use foreign_keys pragmas so that we can use the DELETE CASCADE option. It accepts a block to execute queries. If something goes wrong, then it raises a database helper error. Also, ensure to close the database (important to close the database if we don’t want to see database busy errors).



26
27
28
29
30
31
32
33
34
35
 
# File 'lib/mail_grabber/database_helper.rb', line 26

def connection
  database = open_database
  database.foreign_keys = 'ON'

  yield database
rescue SQLite3::Exception => e
  raise Error::DatabaseHelperError, e
ensure
  database&.close
end

#connection_execute(query, args = []) ⇒ Object

Create a connection and execute a query.

Parameters:

  • query (String) —

    which query would we like to execute

  • args (Array) (defaults to: []) —

    any arguments that we will use in the query



41
42
43
 
# File 'lib/mail_grabber/database_helper.rb', line 41

def connection_execute(query, args = [])
  connection { |db| db.execute(query, args) }
end

#connection_execute_transaction ⇒ Object

Create a connection and execute many queries in a transaction. It accepts a block to execute queries. If something goes wrong, it rolls back the changes and raises a database helper error.



48
49
50
51
52
53
54
55
56
57
58
59
60
 
# File 'lib/mail_grabber/database_helper.rb', line 48

def connection_execute_transaction
  connection do |db|
    db.transaction

    yield db

    db.commit
  rescue SQLite3::Exception => e
    db.rollback

    raise Error::DatabaseHelperError, e
  end
end

#delete_all_messages ⇒ Object

Helper method to delete all messages.



63
64
65
 
# File 'lib/mail_grabber/database_helper.rb', line 63

def delete_all_messages
  connection_execute('DELETE FROM mail')
end

#delete_message_by(id) ⇒ Object

Helper method to delete a message.

Parameters:

  • id (String/Integer) —

    the identifier of the message



70
71
72
 
# File 'lib/mail_grabber/database_helper.rb', line 70

def delete_message_by(id)
  connection_execute('DELETE FROM mail WHERE id = ?', [id.to_i])
end

#select_all_messages ⇒ Object

Helper method to get all messages.



75
76
77
 
# File 'lib/mail_grabber/database_helper.rb', line 75

def select_all_messages
  connection_execute('SELECT * FROM mail ORDER BY id DESC, created_at DESC')
end

#select_message_by(id) ⇒ Object

Helper method to get a message.

Parameters:

  • id (String/Integer) —

    the identifier of the message



82
83
84
 
# File 'lib/mail_grabber/database_helper.rb', line 82

def select_message_by(id)
  connection_execute('SELECT * FROM mail WHERE id = ?', [id.to_i]).first
end

#select_message_parts_by(id) ⇒ Object

Helper method to get a message part.

Parameters:

  • id (String/Integer) —

    the identifier of the message part



89
90
91
 
# File 'lib/mail_grabber/database_helper.rb', line 89

def select_message_parts_by(id)
  connection_execute('SELECT * FROM mail_part WHERE mail_id = ?', [id.to_i])
end

#select_messages_by(page, per_page) ⇒ Object

Helper method to get a specific number of messages. We can specify which part of the table we need and how many messages we want to see.

Parameters:

  • page (String/Integer) —

    which part of the table want to see

  • per_page (String/Integer) —

    how many messages give back



98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/mail_grabber/database_helper.rb', line 98

def select_messages_by(page, per_page)
  page = page.to_i
  per_page = per_page.to_i

  connection_execute(
    select_messages_with_pagination_query,
    [
      per_page * (page - 1),
      per_page
    ]
  )
end

#store_mail(message) ⇒ Object

Helper method to store a message in the database.

Parameters:

  • message (Mail::Message) —

    which we would like to store



114
115
116
117
118
119
120
 
# File 'lib/mail_grabber/database_helper.rb', line 114

def store_mail(message)
  connection_execute_transaction do |db|
    insert_into_mail(db, message)

    insert_into_mail_part(db, message)
  end
end