Module: MailGrabber::DatabaseQueries

Included in:
DatabaseHelper
Defined in:
lib/mail_grabber/database_queries.rb

Instance Method Summary collapse

Instance Method Details

#create_mail_part_table(db) ⇒ Object

Create mail part table if it is not exist.

Parameters:

  • db (SQLite3::Database) —

    to execute create table query



26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/mail_grabber/database_queries.rb', line 26

def create_mail_part_table(db)
  db.execute(<<-SQL)
    CREATE TABLE IF NOT EXISTS mail_part (
      id INTEGER PRIMARY KEY,
      mail_id INTEGER NOT NULL,
      cid TEXT,
      mime_type TEXT,
      is_attachment INTEGER,
      is_inline INTEGER,
      filename TEXT,
      charset TEXT,
      body BLOB,
      size INTEGER,
      created_at DATETIME DEFAULT CURRENT_DATETIME,
      FOREIGN KEY (mail_id) REFERENCES mail(id) ON DELETE CASCADE
    )
  SQL
end

#create_mail_table(db) ⇒ Object

Create mail table if it is not exist.

Parameters:

  • db (SQLite3::Database) —

    to execute create table query



8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
# File 'lib/mail_grabber/database_queries.rb', line 8

def create_mail_table(db)
  db.execute(<<-SQL)
    CREATE TABLE IF NOT EXISTS mail (
      id INTEGER PRIMARY KEY,
      subject TEXT,
      senders TEXT,
      recipients TEXT,
      carbon_copy TEXT,
      blind_carbon_copy TEXT,
      raw BLOB,
      created_at DATETIME DEFAULT CURRENT_DATETIME
    )
  SQL
end

#insert_into_mail_part_query ⇒ Srting

Insert mail part query.

Returns:

  • (Srting) —

    with the insert mail part query



66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
# File 'lib/mail_grabber/database_queries.rb', line 66

def insert_into_mail_part_query
  <<-SQL
    INSERT INTO mail_part (
      mail_id,
      cid,
      mime_type,
      is_attachment,
      is_inline,
      filename,
      charset,
      body,
      size,
      created_at
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
  SQL
end

#insert_into_mail_query ⇒ Srting

Insert mail query.

Returns:

  • (Srting) —

    with the insert mail query



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

def insert_into_mail_query
  <<-SQL
    INSERT INTO mail (
      subject,
      senders,
      recipients,
      carbon_copy,
      blind_carbon_copy,
      raw,
      created_at
    )
    VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
  SQL
end

#select_messages_with_pagination_query ⇒ Srting

Select messages with pagination query.

Returns:

  • (Srting) —

    with the select messages query



87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/mail_grabber/database_queries.rb', line 87

def select_messages_with_pagination_query
  <<-SQL
    SELECT id, subject, senders, created_at
    FROM mail
    WHERE id NOT IN (
      SELECT id
      FROM mail
      ORDER BY id DESC, created_at DESC
      LIMIT ?
    )
    ORDER BY id DESC, created_at DESC
    LIMIT ?
  SQL
end