From d6b9a62e0a13497b8cc40eb1db56d1ec2b3760ea Mon Sep 17 00:00:00 2001 From: Eugen Rochko Date: Mon, 19 Nov 2018 00:43:52 +0100 Subject: Extract counters from accounts table to account_stats table (#9295) --- db/migrate/20181116165755_create_account_stats.rb | 12 +++++ db/migrate/20181116173541_copy_account_stats.rb | 54 +++++++++++++++++++++++ 2 files changed, 66 insertions(+) create mode 100644 db/migrate/20181116165755_create_account_stats.rb create mode 100644 db/migrate/20181116173541_copy_account_stats.rb (limited to 'db/migrate') diff --git a/db/migrate/20181116165755_create_account_stats.rb b/db/migrate/20181116165755_create_account_stats.rb new file mode 100644 index 000000000..a798e8166 --- /dev/null +++ b/db/migrate/20181116165755_create_account_stats.rb @@ -0,0 +1,12 @@ +class CreateAccountStats < ActiveRecord::Migration[5.2] + def change + create_table :account_stats do |t| + t.belongs_to :account, null: false, foreign_key: { on_delete: :cascade }, index: { unique: true } + t.bigint :statuses_count, null: false, default: 0 + t.bigint :following_count, null: false, default: 0 + t.bigint :followers_count, null: false, default: 0 + + t.timestamps + end + end +end diff --git a/db/migrate/20181116173541_copy_account_stats.rb b/db/migrate/20181116173541_copy_account_stats.rb new file mode 100644 index 000000000..bb523fbbd --- /dev/null +++ b/db/migrate/20181116173541_copy_account_stats.rb @@ -0,0 +1,54 @@ +class CopyAccountStats < ActiveRecord::Migration[5.2] + disable_ddl_transaction! + + def up + safety_assured do + if supports_upsert? + up_fast + else + up_slow + end + end + end + + def down + # Nothing + end + + private + + def supports_upsert? + version = select_one("SELECT current_setting('server_version_num') AS v")['v'].to_i + version >= 90500 + end + + def up_fast + say 'Upsert is available, importing counters using the fast method' + + Account.unscoped.select('id').find_in_batches(batch_size: 5_000) do |accounts| + execute <<-SQL.squish + INSERT INTO account_stats (account_id, statuses_count, following_count, followers_count, created_at, updated_at) + SELECT id, statuses_count, following_count, followers_count, created_at, updated_at + FROM accounts + WHERE id IN (#{accounts.map(&:id).join(', ')}) + ON CONFLICT (account_id) DO UPDATE + SET statuses_count = EXCLUDED.statuses_count, following_count = EXCLUDED.following_count, followers_count = EXCLUDED.followers_count + SQL + end + end + + def up_slow + say 'Upsert is not available in PostgreSQL below 9.5, falling back to slow import of counters' + + # We cannot use bulk INSERT or overarching transactions here because of possible + # uniqueness violations that we need to skip over + Account.unscoped.select('id, statuses_count, following_count, followers_count, created_at, updated_at').find_each do |account| + begin + params = [[nil, account.id], [nil, account.statuses_count], [nil, account.following_count], [nil, account.followers_count], [nil, account.created_at], [nil, account.updated_at]] + exec_insert('INSERT INTO account_stats (account_id, statuses_count, following_count, followers_count, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, $6)', nil, params) + rescue ActiveRecord::RecordNotUnique + next + end + end + end +end -- cgit From 4afda5444a3cc864cb0f9a4f59493022d8a5e080 Mon Sep 17 00:00:00 2001 From: Eugen Rochko Date: Tue, 27 Nov 2018 13:56:25 +0100 Subject: Change identities id column to a bigint (#9371) * fix: change Identity's id column to a bigint This appears to be the last model created using a 5.0 migration, where column types defaulted to `integer` rather than `bigint`. This migration changes the column type to match that of all of the other ID columns. * Change user_id column in identities to bigint and fix down-migration --- app/models/identity.rb | 4 ++-- db/migrate/20181127130500_identity_id_to_bigint.rb | 27 ++++++++++++++++++++++ db/schema.rb | 8 +++---- 3 files changed, 33 insertions(+), 6 deletions(-) create mode 100644 db/migrate/20181127130500_identity_id_to_bigint.rb (limited to 'db/migrate') diff --git a/app/models/identity.rb b/app/models/identity.rb index a5e0c09ec..8cc65aef4 100644 --- a/app/models/identity.rb +++ b/app/models/identity.rb @@ -3,12 +3,12 @@ # # Table name: identities # -# id :integer not null, primary key -# user_id :integer # provider :string default(""), not null # uid :string default(""), not null # created_at :datetime not null # updated_at :datetime not null +# id :bigint(8) not null, primary key +# user_id :bigint(8) # class Identity < ApplicationRecord diff --git a/db/migrate/20181127130500_identity_id_to_bigint.rb b/db/migrate/20181127130500_identity_id_to_bigint.rb new file mode 100644 index 000000000..5f388aca7 --- /dev/null +++ b/db/migrate/20181127130500_identity_id_to_bigint.rb @@ -0,0 +1,27 @@ +require Rails.root.join('lib', 'mastodon', 'migration_helpers') + +class IdentityIdToBigint < ActiveRecord::Migration[5.2] + include Mastodon::MigrationHelpers + + disable_ddl_transaction! + + def up + safety_assured do + change_column_type_concurrently :identities, :id, :bigint + cleanup_concurrent_column_type_change :identities, :id + + change_column_type_concurrently :identities, :user_id, :bigint + cleanup_concurrent_column_type_change :identities, :user_id + end + end + + def down + safety_assured do + change_column_type_concurrently :identities, :id, :integer + cleanup_concurrent_column_type_change :identities, :id + + change_column_type_concurrently :identities, :user_id, :integer + cleanup_concurrent_column_type_change :identities, :user_id + end + end +end diff --git a/db/schema.rb b/db/schema.rb index b0f14954f..d67521442 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: 2018_11_16_184611) do +ActiveRecord::Schema.define(version: 2018_11_27_130500) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -235,12 +235,12 @@ ActiveRecord::Schema.define(version: 2018_11_16_184611) do t.index ["target_account_id"], name: "index_follows_on_target_account_id" end - create_table "identities", id: :serial, force: :cascade do |t| - t.integer "user_id" + create_table "identities", force: :cascade do |t| t.string "provider", default: "", null: false t.string "uid", default: "", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "user_id" t.index ["user_id"], name: "index_identities_on_user_id" end @@ -651,7 +651,7 @@ ActiveRecord::Schema.define(version: 2018_11_16_184611) do add_foreign_key "follow_requests", "accounts", name: "fk_76d644b0e7", on_delete: :cascade add_foreign_key "follows", "accounts", column: "target_account_id", name: "fk_745ca29eac", on_delete: :cascade add_foreign_key "follows", "accounts", name: "fk_32ed1b5560", on_delete: :cascade - add_foreign_key "identities", "users", on_delete: :cascade + add_foreign_key "identities", "users", name: "fk_bea040f377", on_delete: :cascade add_foreign_key "imports", "accounts", name: "fk_6db1b6e408", on_delete: :cascade add_foreign_key "invites", "users", on_delete: :cascade add_foreign_key "list_accounts", "accounts", on_delete: :cascade -- cgit From 53d0293d259f3b4e8fcfd96aca33867c6af3dd50 Mon Sep 17 00:00:00 2001 From: Thibaut Girka Date: Sun, 4 Nov 2018 12:02:04 +0100 Subject: Add database support for list show-reply preferences --- app/lib/feed_manager.rb | 3 ++- app/models/list.rb | 13 +++++++----- .../20181127165847_add_show_replies_to_lists.rb | 23 ++++++++++++++++++++++ db/schema.rb | 3 ++- 4 files changed, 35 insertions(+), 7 deletions(-) create mode 100644 db/migrate/20181127165847_add_show_replies_to_lists.rb (limited to 'db/migrate') diff --git a/app/lib/feed_manager.rb b/app/lib/feed_manager.rb index 31ff53860..0cdb178c1 100644 --- a/app/lib/feed_manager.rb +++ b/app/lib/feed_manager.rb @@ -42,7 +42,8 @@ class FeedManager def push_to_list(list, status) if status.reply? && status.in_reply_to_account_id != status.account_id should_filter = status.in_reply_to_account_id != list.account_id - should_filter &&= !ListAccount.where(list_id: list.id, account_id: status.in_reply_to_account_id).exists? + should_filter &&= !list.show_all_replies? + should_filter &&= !(list.show_list_replies? && ListAccount.where(list_id: list.id, account_id: status.in_reply_to_account_id).exists?) return false if should_filter end return false unless add_to_feed(:list, list.id, status) diff --git a/app/models/list.rb b/app/models/list.rb index c9c94fca1..8493046e5 100644 --- a/app/models/list.rb +++ b/app/models/list.rb @@ -3,11 +3,12 @@ # # Table name: lists # -# id :bigint(8) not null, primary key -# account_id :bigint(8) not null -# title :string default(""), not null -# created_at :datetime not null -# updated_at :datetime not null +# id :bigint(8) not null, primary key +# account_id :bigint(8) not null +# title :string default(""), not null +# created_at :datetime not null +# updated_at :datetime not null +# replies_policy :integer default("list_replies"), not null # class List < ApplicationRecord @@ -15,6 +16,8 @@ class List < ApplicationRecord PER_ACCOUNT_LIMIT = 50 + enum replies_policy: [:list_replies, :all_replies, :no_replies], _prefix: :show + belongs_to :account, optional: true has_many :list_accounts, inverse_of: :list, dependent: :destroy diff --git a/db/migrate/20181127165847_add_show_replies_to_lists.rb b/db/migrate/20181127165847_add_show_replies_to_lists.rb new file mode 100644 index 000000000..f68c98daf --- /dev/null +++ b/db/migrate/20181127165847_add_show_replies_to_lists.rb @@ -0,0 +1,23 @@ +require Rails.root.join('lib', 'mastodon', 'migration_helpers') + +class AddShowRepliesToLists < ActiveRecord::Migration[5.2] + include Mastodon::MigrationHelpers + + disable_ddl_transaction! + + def up + safety_assured do + add_column_with_default( + :lists, + :replies_policy, + :integer, + allow_null: false, + default: 0 + ) + end + end + + def down + remove_column :lists, :replies_policy + end +end diff --git a/db/schema.rb b/db/schema.rb index cd70b7ed2..4db687137 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: 2018_11_27_130500) do +ActiveRecord::Schema.define(version: 2018_11_27_165847) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -293,6 +293,7 @@ ActiveRecord::Schema.define(version: 2018_11_27_130500) do t.string "title", default: "", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.integer "replies_policy", default: 0, null: false t.index ["account_id"], name: "index_lists_on_account_id" end -- cgit