PERF: generate and store sitemaps list in database.

PERF: generate and store sitemaps list in database.

It will fix the performance issue in root /sitemap.xml file.

diff --git a/app/jobs/scheduled/update_sitemaps.rb b/app/jobs/scheduled/update_sitemaps.rb
new file mode 100644
index 0000000..f4d05bd
--- /dev/null
+++ b/app/jobs/scheduled/update_sitemaps.rb
@@ -0,0 +1,11 @@
+# frozen_string_literal: true
+
+module ::Jobs
+  class UpdateSitemaps < ::Jobs::Scheduled
+    every 1.hour
+
+    def execute(args)
+      Sitemap.update! if SiteSetting.sitemap_enabled
+    end
+  end
+end
diff --git a/app/models/sitemap.rb b/app/models/sitemap.rb
new file mode 100644
index 0000000..68b477e
--- /dev/null
+++ b/app/models/sitemap.rb
@@ -0,0 +1,52 @@
+# frozen_string_literal: true
+
+class Sitemap < ActiveRecord::Base
+
+  RECENT_SITEMAP_NAME ||= 'recent'
+
+  def update_last_posted_at!
+    query = self.name == RECENT_SITEMAP_NAME ? Sitemap.topics_query : Sitemap.topics_query_by_page(name.to_i)
+
+    self.update!(
+      last_posted_at: query.maximum(:last_posted_at) || query.maximum(:updated_at) || 3.days.ago,
+      enabled: true
+    )
+  end
+
+  def self.update!
+    count = topics_query.count
+    size = count / Sitemap.size
+    size += 1 if count % Sitemap.size > 0
+    names = [RECENT_SITEMAP_NAME]
+
+    size.times do |index|
+      name = (index + 1).to_s
+      self.find_or_initialize_by(name: name).update_last_posted_at!
+      names << name
+    end
+
+    self.find_or_initialize_by(name: RECENT_SITEMAP_NAME).update_last_posted_at!
+    self.where.not(name: names).update_all(enabled: false)
+  end
+
+  def self.topics_query(since = nil)
+    category_ids = Category.where(read_restricted: false).pluck(:id)
+    query = Topic.where(category_id: category_ids, visible: true)
+    if since
+      query = query.where('last_posted_at > ?', since)
+      query = query.order(last_posted_at: :desc)
+    else
+      query = query.order(last_posted_at: :asc)
+    end
+    query
+  end
+
+  def self.topics_query_by_page(index)
+    offset = (index - 1) * Sitemap.size
+    topics_query.limit(Sitemap.size).offset(offset)
+  end
+
+  def self.size
+    SiteSetting.sitemap_topics_per_page
+  end
+end
diff --git a/app/views/discourse_sitemap/sitemap/default.erb b/app/views/discourse_sitemap/sitemap/default.erb
index 7a850cd..9d96cd1 100644
--- a/app/views/discourse_sitemap/sitemap/default.erb
+++ b/app/views/discourse_sitemap/sitemap/default.erb
@@ -18,7 +18,7 @@
         end
     -%>
         <loc><%= url %></loc>
-        <lastmod><%= topic[2].xmlschema %></lastmod>
+        <lastmod><%= (topic[2] || topic[3]).xmlschema %></lastmod>
     </url>
     <% end %>
 </urlset>
diff --git a/app/views/discourse_sitemap/sitemap/index.erb b/app/views/discourse_sitemap/sitemap/index.erb
index c735366..fc5a25d 100644
--- a/app/views/discourse_sitemap/sitemap/index.erb
+++ b/app/views/discourse_sitemap/sitemap/index.erb
@@ -1,13 +1,9 @@
 <%= render partial: 'discourse_sitemap/sitemap/header' %>
 <sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
-  <% 1.upto(@size) do |i| %>
+  <% @sitemaps.each do |sitemap| %>
    <sitemap>
-      <loc><%= Discourse.base_url %>/sitemap_<%= i %>.xml</loc>
-      <lastmod><%= @lastmod[i] %></lastmod>
+      <loc><%= Discourse.base_url %>/sitemap_<%= sitemap.name %>.xml</loc>
+      <lastmod><%= sitemap.last_posted_at.xmlschema %></lastmod>
    </sitemap>
    <% end %>
-   <sitemap>
-      <loc><%= Discourse.base_url %>/sitemap_recent.xml</loc>
-      <lastmod><%= @lastmod['recent'] %></lastmod>
-   </sitemap>
 </sitemapindex>
diff --git a/db/migrate/20200909134134_create_sitemaps.rb b/db/migrate/20200909134134_create_sitemaps.rb
new file mode 100644
index 0000000..48b68a1
--- /dev/null
+++ b/db/migrate/20200909134134_create_sitemaps.rb
@@ -0,0 +1,13 @@
+# frozen_string_literal: true
+
+class CreateSitemaps < ActiveRecord::Migration[6.0]
+  def change
+    create_table :sitemaps do |t|
+      t.string :name, null: false
+      t.datetime :last_posted_at, null: false
+      t.boolean :enabled, null: false, default: true
+    end
+
+    add_index :sitemaps, :name, unique: true
+  end
+end
diff --git a/plugin.rb b/plugin.rb
index 6ee838a..e5fff6c 100644
--- a/plugin.rb
+++ b/plugin.rb
@@ -21,81 +21,46 @@ after_initialize do
 
   require_dependency "application_controller"
 
+  [
+    '../app/models/sitemap.rb',
+    '../app/jobs/scheduled/update_sitemaps.rb'
+  ].each { |path| load File.expand_path(path, __FILE__) }
+
   class DiscourseSitemap::SitemapController < ::ApplicationController
     layout false
     skip_before_action :preload_json, :check_xhr
+    before_action :check_sitemap_enabled
 
-    def topics_query(since = nil)
-      category_ids = Category.where(read_restricted: false).pluck(:id)
-      query = Topic.where(category_id: category_ids, visible: true)
-      if since
-        query = query.where('last_posted_at > ?', since)
-        query = query.order(last_posted_at: :desc)
-      else
-        query = query.order(last_posted_at: :asc)
-      end
-      query
-    end
-
-    def topics_query_by_page(index)
-      offset = (index - 1) * sitemap_size
-      topics_query.limit(sitemap_size).offset(offset)
+    def check_sitemap_enabled
+      raise Discourse::NotFound unless SiteSetting.sitemap_enabled
+      prepend_view_path "plugins/discourse-sitemap/app/views/"
     end
 
     def index
-      raise ActionController::RoutingError.new('Not Found') unless SiteSetting.sitemap_enabled
-      prepend_view_path "plugins/discourse-sitemap/app/views/"
-
-      # 1 hour cache just in case new pages are added
-      @output = Rails.cache.fetch("sitemap/index/v6/#{sitemap_size}", expires_in: 1.hour) do
-        count = topics_query.count
-        @size = count / sitemap_size
-        @size += 1 if count % sitemap_size > 0
-        @lastmod = {}
+      @sitemaps = Sitemap.where(enabled: true)
 
-        1.upto(@size) do |i|
-          @lastmod[i] = last_posted_at(i).xmlschema
-          Rails.cache.delete("sitemap/#{i}")
-        end
-
-        @lastmod['recent'] = last_posted_at.xmlschema
-        render_to_string :index, content_type: 'text/xml; charset=UTF-8'
-      end
-
-      render plain: @output, content_type: 'text/xml; charset=UTF-8'
+      render :index, content_type: 'text/xml; charset=UTF-8'
     end
 
     def default
-      raise ActionController::RoutingError.new('Not Found') unless SiteSetting.sitemap_enabled
-      prepend_view_path "plugins/discourse-sitemap/app/views/"
-
-      page = Integer(params[:page])
-      sitemap(page)
-    end
+      index = params.require(:page)
+      sitemap = Sitemap.find_by(enabled: true, name: index.to_s)
+      raise Discourse::NotFound if sitemap.blank?
 
-    def recent
-      raise ActionController::RoutingError.new('Not Found') unless SiteSetting.sitemap_enabled
-      prepend_view_path "plugins/discourse-sitemap/app/views/"
-
-      @output = Rails.cache.fetch("sitemap/recent/#{last_posted_at.to_i}", expires_in: 1.hour) do
-        @topics = Array.new
-        topics_query(3.days.ago).limit(sitemap_size).pluck(:id, :slug, :last_posted_at, :updated_at, :posts_count).each do |t|
-          t[2] = t[3] if t[2].nil?
-          @topics.push t
-        end
+      @output = Rails.cache.fetch("sitemap/#{index}/#{Sitemap.size}", expires_in: 24.hours) do
+        @topics = Sitemap.topics_query_by_page(index.to_i).pluck(:id, :slug, :last_posted_at, :updated_at).to_a
         render :default, content_type: 'text/xml; charset=UTF-8'
       end
       render plain: @output, content_type: 'text/xml; charset=UTF-8' unless performed?
       @output
     end
 
-    def sitemap(page)
-      @output = Rails.cache.fetch("sitemap/#{page}/#{sitemap_size}", expires_in: 24.hours) do
-        @topics = Array.new
-        topics_query_by_page(page).pluck(:id, :slug, :last_posted_at, :updated_at).each do |t|
-          t[2] = t[3] if t[2].nil?
-          @topics.push t
-        end
+    def recent
+      sitemap = Sitemap.find_or_initialize_by(name: Sitemap::RECENT_SITEMAP_NAME)
+      sitemap.update_last_posted_at!
+

[... diff too long, it was truncated ...]

GitHub sha: 06cd5ed3