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 << " CREATE TABLE IF NOT EXISTS posts(\#{SCHEMA.map { |key, value| \"\#{key} \#{value}\" }.join(', ')});\n SQL\n\n statements << <<~SQL\n CREATE UNIQUE INDEX IF NOT EXISTS index_posts_on_reddit_id ON posts(reddit_id);\n SQL\n\n statements << <<~SQL\n CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts5 USING fts5(title, tokenize=\"porter ascii tokenchars '-_'\", content=\"posts\", content_rowid=\"id\");\n SQL\n\n statements << <<~SQL\n CREATE TRIGGER IF NOT EXISTS posts_ai AFTER INSERT ON posts BEGIN\n INSERT INTO posts_fts5(rowid, title) VALUES(new.id, new.title);\n END;\n SQL\n\n statements << <<~SQL\n CREATE TRIGGER IF NOT EXISTS posts_ad AFTER DELETE ON posts BEGIN\n INSERT INTO posts_fts5(posts_fts5, rowid, title) VALUES('delete', old.id, old.title);\n END;\n SQL\n\n statements << <<~SQL\n CREATE TRIGGER IF NOT EXISTS posts_au AFTER UPDATE ON posts BEGIN\n INSERT INTO posts_fts5(posts_fts5, rowid, title) VALUES('delete', old.id, old.title);\n INSERT INTO posts_fts5(rowid, title) VALUES(new.id, new.title);\n END;\n SQL\n\n statements.each do |statement|\n database.execute(statement)\n end\n\n puts database.execute('SELECT * FROM sqlite_master') if debug\nrescue SQLite3::Exception => e\n puts e.inspect\nensure\n database&.close\nend\n"
|