aboutsummaryrefslogtreecommitdiff
path: root/src/guff/database-updater.cr
blob: d472c50312e713af4fc2e5d92fd216496b97e35d (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
require "./database"

module Guff
  class DatabaseUpdater
    CURRENT_VERSION = 2_i64

    SQL = {
      get_version: "
        SELECT value
          FROM metadata
         WHERE name = 'version'
      ",

      set_version_delete: "
        DELETE FROM metadata WHERE name = 'version'
      ",

      set_version_insert: "
        INSERT INTO metadata(name, value) VALUES ('version', ?)
      ",
    }

    MIGRATIONS = [{
      id: "0-null",
      backup: false,
      sql: %w{},
    }, {
      id: "1-create-tags-and-posts",
      backup: false,

      sql: [%{
        CREATE TABLE metadata (
          name    TEXT PRIMARY KEY,
          value   TEXT NOT NULL
        )
      }, %{
        CREATE TABLE sites (
          -- site id
          site_id     INTEGER PRIMARY KEY,

          -- name of site
          name        TEXT UNIQUE NOT NULL
                      CHECK (LENGTH(name) > 0),

          -- is this the default site?
          is_default  BOOLEAN NOT NULL DEFAULT false
        )
      }, %{
        INSERT INTO sites(name, is_default) VALUES ('Default', 1)
      }, %{
        CREATE TABLE posts (
          -- unique id
          post_id     INTEGER PRIMARY KEY,

          site_id     INTEGER NOT NULL
                      REFERENCES sites(site_id),

          -- false if this post has been deleted
          is_active   BOOLEAN NOT NULL
                      DEFAULT true,

          -- when this post was created
          created_at  TIMESTAMP WITH TIME ZONE
                      NOT NULL DEFAULT CURRENT_TIMESTAMP,

          -- when this post was posted
          -- (that is, the draft status was removed)
          posted_at   TIMESTAMP WITH TIME ZONE
                      NOT NULL DEFAULT CURRENT_TIMESTAMP,

          -- title of post
          name        TEXT NOT NULL
                      CHECK (LENGTH(name) > 0),

          -- slug of post (url fragment)
          slug        TEXT NOT NULL
                      CHECK (LENGTH(slug) > 0),

          -- body (raw text before filters)
          body        TEXT NOT NULL
                      CHECK (LENGTH(body) > 0),

          -- generated html (after filters)
          html        TEXT NOT NULL
                      CHECK (LENGTH(html) > 0)
        )
      }, %{
        CREATE INDEX in_posts_slug ON posts(slug)
      }],
    }, {
      id: "2-create-tags",
      backup: false,

      sql: [%{
        CREATE TABLE tags (
          tag_id      INTEGER PRIMARY KEY,
          name        TEXT UNIQUE NOT NULL
        )
      }, %{
        CREATE INDEX in_tags_name ON tags(name)
      }, %{
        CREATE TABLE post_tags (
          tag_id      INTEGER NOT NULL 
                      REFERENCES tags(tag_id),

          post_id     INTEGER NOT NULL 
                      REFERENCES posts(post_id),

          UNIQUE(tag_id, post_id)
        )
      }, %{
        CREATE INDEX in_post_tags_tag_id ON post_tags(tag_id)
      }, %{
        CREATE INDEX in_post_tags_post_id ON post_tags(post_id)
      }],
    }, {
      id: "3-create-domains",
      backup: false,

      sql: [%{
        CREATE TABLE domains (
          -- domain
          domain      TEXT PRIMARY KEY CHECK (
                        LENGTH(domain) > 0 AND
                        domain = LOWER(domain)
                      ),

          -- site id
          site_id     INTEGER NOT NULL
                      REFERENCES sites(site_id)
        )
      }],
    }]

    def self.run(path, config)
      new(path, config).run
    end

    def initialize(
      @path   : String,
      @config : Config
    )
      @db = Database.new(@path)
    end

    def run
      # get version from database
      db_version = @db.table_exists?("metadata") ? get_version : 0
      app_version = MIGRATIONS.size - 1

      if db_version < app_version
        puts "starting db migration (db = %d, app = %d)" % [
          db_version,
          app_version
        ]

        (db_version + 1).upto(app_version) do |v|
          migrate_to(v)
        end
      end
    end

    private def migrate_to(version)
      # map version number to migration id
      m = MIGRATIONS[version]
      puts "  * migration: %s" % [m[:id]]

      @db.transaction do
        # exec queries
        (m[:sql] as Array(String)).each do |sql|
          @db.query(sql)
        end

        # update database version
        set_version(version)
      end
    end

    private def get_version
      (@db.one(SQL[:get_version]) || 0).to_i
    end

    private def set_version(version)
      @db.query(SQL[:set_version_delete])
      @db.query(SQL[:set_version_insert], [version.to_s])
    end
  end
end