Class: Mechmarket::Database

Inherits:
Object
  • Object
show all
Defined in:
lib/mechmarket/database.rb

Constant Summary collapse

SCHEMA = 
{
  id: 'INTEGER PRIMARY KEY AUTOINCREMENT',
  reddit_id: 'TEXT NOT NULL',
  created_utc: 'INTEGER NOT NULL',
  url: 'TEXT NOT NULL',
  author: 'TEXT NOT NULL',
  title: 'TEXT NOT NULL',
  body: 'TEXT NOT NULL',
  type: 'TEXT NOT NULL'
}.freeze

Class Method Summary collapse

Class Method Details

.database ⇒ Object 



17
18
19
 
# File 'lib/mechmarket/database.rb', line 17

def database
  @database ||= SQLite3::Database.new(File.expand_path('../../db/mechmarket.db', __dir__))
end

.migrate(debug: false) ⇒ Object 



21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/mechmarket/database.rb', line 21

def migrate(debug: false)
  statements = []

  statements << <<~SQL
    CREATE TABLE IF NOT EXISTS posts(#{SCHEMA.map { |key, value| "#{key} #{value}" }.join(', ')});
  SQL

  statements << <<~SQL
    CREATE UNIQUE INDEX IF NOT EXISTS index_posts_on_reddit_id ON posts(reddit_id);
  SQL

  statements << <<~SQL
    CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts5 USING fts5(title, tokenize="porter ascii tokenchars '-_'", content="posts", content_rowid="id");
  SQL

  statements << <<~SQL
    CREATE TRIGGER IF NOT EXISTS posts_ai AFTER INSERT ON posts BEGIN
      INSERT INTO posts_fts5(rowid, title) VALUES(new.id, new.title);
    END;
  SQL

  statements << <<~SQL
    CREATE TRIGGER IF NOT EXISTS posts_ad AFTER DELETE ON posts BEGIN
      INSERT INTO posts_fts5(posts_fts5, rowid, title) VALUES('delete', old.id, old.title);
    END;
  SQL

  statements << <<~SQL
    CREATE TRIGGER IF NOT EXISTS posts_au AFTER UPDATE ON posts BEGIN
      INSERT INTO posts_fts5(posts_fts5, rowid, title) VALUES('delete', old.id, old.title);
      INSERT INTO posts_fts5(rowid, title) VALUES(new.id, new.title);
    END;
  SQL

  statements.each do |statement|
    database.execute(statement)
  end

  puts database.execute('SELECT * FROM sqlite_master') if debug
rescue SQLite3::Exception => e
  puts e.inspect
ensure
  database&.close
end