about summary refs log tree commit diff
path: root/db
diff options
context:
space:
mode:
authorClaire <claire.github-309c@sitedethib.com>2020-12-15 14:27:06 +0100
committerClaire <claire.github-309c@sitedethib.com>2020-12-15 14:27:06 +0100
commite4f8679eaeea062e1f9ca9f58703b51ff8162c35 (patch)
tree13940a853f1278a3c4ef89dd3a0bbedfeaaf7140 /db
parent1978f7265e1e83bda25413da26f53c53110af764 (diff)
parent8485c436d5d083c28df8c942fe521bfb46edfc9f (diff)
Merge branch 'master' into glitch-soc/merge-upstream
Conflicts:
- `app/models/form/admin_settings.rb`:
  New setting added upstream. Ported it.
- `app/views/statuses/_simple_status.html.haml`:
  Upstream removed RTL classes. Did the same.
- `config/settings.yml`:
  New setting added upstream. Ported it.
Diffstat (limited to 'db')
-rw-r--r--db/migrate/20200309150742_add_forwarded_to_reports.rb5
-rw-r--r--db/migrate/20201206004238_create_instances.rb9
-rw-r--r--db/schema.rb28
-rw-r--r--db/views/instances_v01.sql17
4 files changed, 58 insertions, 1 deletions
diff --git a/db/migrate/20200309150742_add_forwarded_to_reports.rb b/db/migrate/20200309150742_add_forwarded_to_reports.rb
new file mode 100644
index 000000000..df278240b
--- /dev/null
+++ b/db/migrate/20200309150742_add_forwarded_to_reports.rb
@@ -0,0 +1,5 @@
+class AddForwardedToReports < ActiveRecord::Migration[5.2]
+  def change
+    add_column :reports, :forwarded, :boolean
+  end
+end
diff --git a/db/migrate/20201206004238_create_instances.rb b/db/migrate/20201206004238_create_instances.rb
new file mode 100644
index 000000000..a4b866894
--- /dev/null
+++ b/db/migrate/20201206004238_create_instances.rb
@@ -0,0 +1,9 @@
+class CreateInstances < ActiveRecord::Migration[5.2]
+  def change
+    create_view :instances, materialized: true
+
+    # To be able to refresh the view concurrently,
+    # at least one unique index is required
+    safety_assured { add_index :instances, :domain, unique: true }
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 7d39126f0..23541d95a 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -10,7 +10,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 2020_10_17_234926) do
+ActiveRecord::Schema.define(version: 2020_12_06_004238) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -717,6 +717,7 @@ ActiveRecord::Schema.define(version: 2020_10_17_234926) do
     t.bigint "target_account_id", null: false
     t.bigint "assigned_account_id"
     t.string "uri"
+    t.boolean "forwarded"
     t.index ["account_id"], name: "index_reports_on_account_id"
     t.index ["target_account_id"], name: "index_reports_on_target_account_id"
   end
@@ -1047,4 +1048,29 @@ ActiveRecord::Schema.define(version: 2020_10_17_234926) do
   add_foreign_key "web_push_subscriptions", "users", on_delete: :cascade
   add_foreign_key "web_settings", "users", name: "fk_11910667b2", on_delete: :cascade
   add_foreign_key "webauthn_credentials", "users"
+
+  create_view "instances", materialized: true, sql_definition: <<-SQL
+      WITH domain_counts(domain, accounts_count) AS (
+           SELECT accounts.domain,
+              count(*) AS accounts_count
+             FROM accounts
+            WHERE (accounts.domain IS NOT NULL)
+            GROUP BY accounts.domain
+          )
+   SELECT domain_counts.domain,
+      domain_counts.accounts_count
+     FROM domain_counts
+  UNION
+   SELECT domain_blocks.domain,
+      COALESCE(domain_counts.accounts_count, (0)::bigint) AS accounts_count
+     FROM (domain_blocks
+       LEFT JOIN domain_counts ON (((domain_counts.domain)::text = (domain_blocks.domain)::text)))
+  UNION
+   SELECT domain_allows.domain,
+      COALESCE(domain_counts.accounts_count, (0)::bigint) AS accounts_count
+     FROM (domain_allows
+       LEFT JOIN domain_counts ON (((domain_counts.domain)::text = (domain_allows.domain)::text)));
+  SQL
+  add_index "instances", ["domain"], name: "index_instances_on_domain", unique: true
+
 end
diff --git a/db/views/instances_v01.sql b/db/views/instances_v01.sql
new file mode 100644
index 000000000..94acd61a1
--- /dev/null
+++ b/db/views/instances_v01.sql
@@ -0,0 +1,17 @@
+WITH domain_counts(domain, accounts_count)
+AS (
+  SELECT domain, COUNT(*) as accounts_count
+  FROM accounts
+  WHERE domain IS NOT NULL
+  GROUP BY domain
+)
+SELECT domain, accounts_count
+FROM domain_counts
+UNION
+SELECT domain_blocks.domain, COALESCE(domain_counts.accounts_count, 0)
+FROM domain_blocks
+LEFT OUTER JOIN domain_counts ON domain_counts.domain = domain_blocks.domain
+UNION
+SELECT domain_allows.domain, COALESCE(domain_counts.accounts_count, 0)
+FROM domain_allows
+LEFT OUTER JOIN domain_counts ON domain_counts.domain = domain_allows.domain