Loading a representative sample of data into a Mastodon database for migration tests

only_statuses.sql

DO $$
DECLARE ratio float;
DECLARE statuses_count integer;
DECLARE conversations_count integer;
DECLARE notifications_count integer;
DECLARE media_attachments_count integer;
DECLARE oauth_access_tokens_count integer;
DECLARE stream_entries_count integer;
DECLARE favourites_count integer;
DECLARE mentions_count integer;
DECLARE statuses_tags_count integer;
DECLARE preview_cards_statuses_count integer;
DECLARE accounts_count integer;
DECLARE preview_cards_count integer;
DECLARE users_count integer;
DECLARE subscriptions_count integer;
DECLARE blocks_count integer;
DECLARE session_activations_count integer;
DECLARE follow_requests_count integer;
DECLARE follows_count integer;
DECLARE tags_count integer;
DECLARE oauth_access_grants_count integer;
DECLARE web_push_subscriptions_count integer;
DECLARE oauth_applications_count integer;
DECLARE mutes_count integer;
DECLARE settings_count integer;
DECLARE web_settings_count integer;
BEGIN
  SELECT 1 INTO ratio;
  SELECT ceil(600000 * ratio) INTO statuses_count;
  SELECT ceil(4000 * ratio) INTO mentions_count;
  SELECT ceil(20 * ratio) INTO accounts_count;

  TRUNCATE statuses CASCADE;
  TRUNCATE conversations CASCADE;
  TRUNCATE notifications CASCADE;
  TRUNCATE media_attachments CASCADE;
  TRUNCATE oauth_access_tokens CASCADE;
  TRUNCATE stream_entries CASCADE;
  TRUNCATE favourites CASCADE;
  TRUNCATE mentions CASCADE;
  TRUNCATE statuses_tags CASCADE;
  TRUNCATE preview_cards_statuses CASCADE;
  TRUNCATE accounts CASCADE;
  TRUNCATE preview_cards CASCADE;
  TRUNCATE users CASCADE;
  TRUNCATE subscriptions CASCADE;
  TRUNCATE blocks CASCADE;
  TRUNCATE session_activations CASCADE;
  TRUNCATE follow_requests CASCADE;
  TRUNCATE follows CASCADE;
  TRUNCATE tags CASCADE;
  TRUNCATE oauth_access_grants CASCADE;
  TRUNCATE web_push_subscriptions CASCADE;
  TRUNCATE oauth_applications CASCADE;
  TRUNCATE mutes CASCADE;
  TRUNCATE settings CASCADE;
  TRUNCATE web_settings CASCADE;

  -- We need accounts, users, and statuses for a bunch of foreign keys
  INSERT INTO accounts (id, username, created_at, updated_at) SELECT
    generate_series(1,accounts_count),
    generate_series(1,accounts_count),
    NOW(), NOW();
  INSERT INTO statuses (id, text, account_id, visibility, local, created_at, updated_at) SELECT
    generate_series(1,statuses_count),
    'Test Status Text',
    --generate_series(0,statuses_count-1) % (accounts_count / 2) % accounts_count + 1,
    --ceil(pow(random(), 3) * (accounts_count - 1)) + 1,
    --ceil(pow(generate_series(0,statuses_count-1)::real / statuses_count, 3) * (accounts_count - 1)) + 1,
    ceil(pow(abs(generate_series(0,statuses_count-1)::real / statuses_count - 0.5), 3) * (accounts_count - 1)) + 1,
    generate_series(1,statuses_count) % 3,
    random() < 0.3,
    NOW(), NOW();
  INSERT INTO stream_entries (id, account_id, activity_id, activity_type, created_at, updated_at, hidden) SELECT
    statuses.id,
    statuses.account_id,
    statuses.id,
    'Status',
    NOW(), NOW(),
    statuses.visibility IN (0, 1) -- NOT IN
    FROM statuses;
  INSERT INTO mentions (id, account_id, status_id, created_at, updated_at) SELECT
    generate_series(1,mentions_count),
    generate_series(0,mentions_count-1) % accounts_count + 1,
    generate_series(1,mentions_count) % statuses_count + 1,
    NOW(), NOW();
END $$;

sample_load.sql

DO $$
DECLARE ratio float;
DECLARE statuses_count integer;
DECLARE conversations_count integer;
DECLARE notifications_count integer;
DECLARE media_attachments_count integer;
DECLARE oauth_access_tokens_count integer;
DECLARE stream_entries_count integer;
DECLARE favourites_count integer;
DECLARE mentions_count integer;
DECLARE statuses_tags_count integer;
DECLARE preview_cards_statuses_count integer;
DECLARE accounts_count integer;
DECLARE preview_cards_count integer;
DECLARE users_count integer;
DECLARE subscriptions_count integer;
DECLARE blocks_count integer;
DECLARE session_activations_count integer;
DECLARE follow_requests_count integer;
DECLARE follows_count integer;
DECLARE tags_count integer;
DECLARE oauth_access_grants_count integer;
DECLARE web_push_subscriptions_count integer;
DECLARE oauth_applications_count integer;
DECLARE mutes_count integer;
DECLARE settings_count integer;
DECLARE web_settings_count integer;
BEGIN
  SELECT 0.01 INTO ratio;
  SELECT ceil(33597681 * ratio) INTO statuses_count;
  SELECT ceil(18729250 * ratio) INTO conversations_count;
  SELECT ceil(12705005 * ratio) INTO notifications_count;
  SELECT ceil(2254240 * ratio) INTO media_attachments_count;
  SELECT ceil(1911506 * ratio) INTO oauth_access_tokens_count;
  SELECT ceil(597439 * ratio) INTO stream_entries_count;
  SELECT ceil(507040 * ratio) INTO favourites_count;
  SELECT ceil(405300 * ratio) INTO mentions_count;
  SELECT ceil(377983 * ratio) INTO statuses_tags_count;
  SELECT ceil(320344 * ratio) INTO preview_cards_statuses_count;
  SELECT ceil(287677 * ratio) INTO accounts_count;
  SELECT ceil(193562 * ratio) INTO preview_cards_count;
  SELECT ceil(148902 * ratio) INTO users_count;
  SELECT ceil(148865 * ratio) INTO subscriptions_count;
  SELECT ceil(138923 * ratio) INTO blocks_count;
  SELECT ceil(72603 * ratio) INTO session_activations_count;
  SELECT ceil(19857 * ratio) INTO follow_requests_count;
  SELECT ceil(18868 * ratio) INTO follows_count;
  SELECT ceil(16052 * ratio) INTO tags_count;
  SELECT ceil(11427 * ratio) INTO oauth_access_grants_count;
  SELECT ceil(9101 * ratio) INTO web_push_subscriptions_count;
  SELECT ceil(7666 * ratio) INTO oauth_applications_count;
  SELECT ceil(6247 * ratio) INTO mutes_count;
  SELECT ceil(4074 * ratio) INTO settings_count;
  SELECT ceil(1068 * ratio) INTO web_settings_count;

  TRUNCATE ratio float;
  TRUNCATE statuses_count;
  TRUNCATE conversations_count;
  TRUNCATE notifications_count;
  TRUNCATE media_attachments_count;
  TRUNCATE oauth_access_tokens_count;
  TRUNCATE stream_entries_count;
  TRUNCATE favourites_count;
  TRUNCATE mentions_count;
  TRUNCATE statuses_tags_count;
  TRUNCATE preview_cards_statuses_count;
  TRUNCATE accounts_count;
  TRUNCATE preview_cards_count;
  TRUNCATE users_count;
  TRUNCATE subscriptions_count;
  TRUNCATE blocks_count;
  TRUNCATE session_activations_count;
  TRUNCATE follow_requests_count;
  TRUNCATE follows_count;
  TRUNCATE tags_count;
  TRUNCATE oauth_access_grants_count;
  TRUNCATE web_push_subscriptions_count;
  TRUNCATE oauth_applications_count;
  TRUNCATE mutes_count;
  TRUNCATE settings_count;
  TRUNCATE web_settings_count;

  -- We need accounts, users, and statuses for a bunch of foreign keys
  INSERT INTO accounts (id, username, created_at, updated_at) SELECT
    generate_series(2,accounts_count),
    generate_series(2,accounts_count),
    NOW(), NOW();
  INSERT INTO users (id, email, account_id, created_at, updated_at) SELECT
    generate_series(2,users_count),
    generate_series(2,users_count),
    generate_series(2,users_count) % accounts_count + 1,
    NOW(), NOW();
  INSERT INTO statuses (id, text, account_id, application_id, in_reply_to_account_id, created_at, updated_at) SELECT
    generate_series(1,statuses_count),
    'Test Status Text',
    generate_series(0,statuses_count-1) % accounts_count + 1,
    generate_series(1,statuses_count) % oauth_applications_count + 1,
    generate_series(0,statuses_count-1) % accounts_count + 1,
    NOW(), NOW();

  -- Skiping conversations as there are no columns to migrate
  INSERT INTO notifications (id, activity_id, account_id, from_account_id, created_at, updated_at) SELECT
    generate_series(1,notifications_count),
    generate_series(1,notifications_count),
    generate_series(0,notifications_count-1) % accounts_count + 1,
    generate_series(0,notifications_count-1) % accounts_count + 1,
    NOW(), NOW();
  INSERT INTO media_attachments (id, status_id, account_id, created_at, updated_at) SELECT
    generate_series(1,media_attachments_count),
    generate_series(1,media_attachments_count),
    generate_series(0,media_attachments_count-1) % accounts_count + 1,
    NOW(), NOW();
  INSERT INTO oauth_applications (id, name, uid, secret, redirect_uri, owner_id, created_at, updated_at) SELECT
    generate_series(2,oauth_applications_count),
    'OAuth Name',
    generate_series(2,oauth_applications_count),
    'secret',
    'redirect_uri',
    generate_series(2,oauth_applications_count),
    NOW(), NOW();
  INSERT INTO oauth_access_tokens (id, token, refresh_token, resource_owner_id, application_id, created_at) SELECT
    generate_series(1,oauth_access_tokens_count),
    generate_series(1,oauth_access_tokens_count),
    generate_series(1,oauth_access_tokens_count),
    generate_series(0,oauth_access_tokens_count-1) % users_count + 1,
    generate_series(0,oauth_access_tokens_count-1) % oauth_applications_count + 1,
    NOW();
  INSERT INTO stream_entries (id, account_id, created_at, updated_at) SELECT
    generate_series(1,stream_entries_count),
    generate_series(0,stream_entries_count-1) % accounts_count + 1,
    NOW(), NOW();
  INSERT INTO favourites (id, account_id, status_id, created_at, updated_at) SELECT
    generate_series(1,favourites_count),
    generate_series(0,favourites_count-1) % accounts_count + 1,
    generate_series(1,favourites_count) % statuses_count + 1,
    NOW(), NOW();
  INSERT INTO mentions (id, account_id, status_id, created_at, updated_at) SELECT
    generate_series(1,mentions_count),
    generate_series(0,mentions_count-1) % accounts_count + 1,
    generate_series(1,mentions_count) % statuses_count + 1,
    NOW(), NOW();
  -- Skiping statuses_tags as there are no columns to migrate
  -- Skiping preview_cards_statuses as there are no columns to migrate
  -- Skiping preview_cards as there are no columns to migrate
  INSERT INTO subscriptions (id, callback_url, account_id, created_at, updated_at) SELECT
    generate_series(1,subscriptions_count),
    'callback',
    generate_series(1,subscriptions_count) % accounts_count + 1,
    NOW(), NOW();
  INSERT INTO blocks (id, account_id, target_account_id, created_at, updated_at) SELECT
    generate_series(2,blocks_count+1),
    generate_series(2,blocks_count+1) % accounts_count + 1,
    generate_series(2,blocks_count+1) % (accounts_count - 1) + 1,
    NOW(), NOW();
  INSERT INTO session_activations (id, session_id, access_token_id, user_id, web_push_subscription_id, created_at, updated_at) SELECT
    generate_series(1,session_activations_count),
    generate_series(1,session_activations_count),
    generate_series(1,session_activations_count),
    generate_series(1,session_activations_count) % users_count + 1,
    generate_series(1,session_activations_count),
    NOW(), NOW();
  INSERT INTO follow_requests (id, account_id, target_account_id, created_at, updated_at) SELECT
    generate_series(1,follow_requests_count),
    generate_series(1,follow_requests_count) % accounts_count + 1,
    generate_series(1,follow_requests_count) % (accounts_count - 1) + 1,
    NOW(), NOW();
  INSERT INTO follows (id, account_id, target_account_id, created_at, updated_at) SELECT
    generate_series(1,follows_count),
    generate_series(1,follows_count) % accounts_count + 1,
    generate_series(1,follows_count) % (accounts_count - 1) + 1,
    NOW(), NOW();
  INSERT INTO tags (id, name, created_at, updated_at) SELECT
    generate_series(1,tags_count),
    generate_series(1,tags_count),
    NOW(), NOW();
  INSERT INTO oauth_access_grants (id, token, expires_in, redirect_uri, application_id, resource_owner_id, created_at) SELECT
    generate_series(1,oauth_access_grants_count),
    generate_series(1,oauth_access_grants_count),
    360000,
    'redirect_uri',
    generate_series(0,oauth_access_grants_count-1) % oauth_applications_count + 1,
    generate_series(1,oauth_access_grants_count),
    NOW();
  -- Skipping web_push_subscriptions as there are no columns to migrate
  INSERT INTO mutes (id, account_id, target_account_id, created_at, updated_at) SELECT
    generate_series(1,mutes_count),
    generate_series(1,mutes_count) % accounts_count + 1,
    generate_series(1,mutes_count) % (accounts_count - 1) + 1,
    NOW(), NOW();
  INSERT INTO settings (id, thing_id, var, value, created_at, updated_at) SELECT
    generate_series(1,settings_count),
    generate_series(1,settings_count),
    'var',
    'value',
    NOW(), NOW();
  INSERT INTO web_settings (id, user_id, created_at, updated_at) SELECT
    generate_series(1,web_settings_count),
    generate_series(1,web_settings_count),
    NOW(), NOW();

  PERFORM SETVAL('account_domain_blocks_id_seq', COALESCE(MAX(id), 1) ) FROM account_domain_blocks;
  PERFORM SETVAL('accounts_id_seq', COALESCE(MAX(id), 1) ) FROM accounts;
  PERFORM SETVAL('blocks_id_seq', COALESCE(MAX(id), 1) ) FROM blocks;
  PERFORM SETVAL('conversation_mutes_id_seq', COALESCE(MAX(id), 1) ) FROM conversation_mutes;
  PERFORM SETVAL('conversations_id_seq', COALESCE(MAX(id), 1) ) FROM conversations;
  PERFORM SETVAL('custom_emojis_id_seq', COALESCE(MAX(id), 1) ) FROM custom_emojis;
  PERFORM SETVAL('domain_blocks_id_seq', COALESCE(MAX(id), 1) ) FROM domain_blocks;
  PERFORM SETVAL('favourites_id_seq', COALESCE(MAX(id), 1) ) FROM favourites;
  PERFORM SETVAL('follow_requests_id_seq', COALESCE(MAX(id), 1) ) FROM follow_requests;
  PERFORM SETVAL('follows_id_seq', COALESCE(MAX(id), 1) ) FROM follows;
  PERFORM SETVAL('imports_id_seq', COALESCE(MAX(id), 1) ) FROM imports;
  PERFORM SETVAL('media_attachments_id_seq', COALESCE(MAX(id), 1) ) FROM media_attachments;
  PERFORM SETVAL('mentions_id_seq', COALESCE(MAX(id), 1) ) FROM mentions;
  PERFORM SETVAL('mutes_id_seq', COALESCE(MAX(id), 1) ) FROM mutes;
  PERFORM SETVAL('notifications_id_seq', COALESCE(MAX(id), 1) ) FROM notifications;
  PERFORM SETVAL('oauth_access_grants_id_seq', COALESCE(MAX(id), 1) ) FROM oauth_access_grants;
  PERFORM SETVAL('oauth_access_tokens_id_seq', COALESCE(MAX(id), 1) ) FROM oauth_access_tokens;
  PERFORM SETVAL('oauth_applications_id_seq', COALESCE(MAX(id), 1) ) FROM oauth_applications;
  PERFORM SETVAL('preview_cards_id_seq', COALESCE(MAX(id), 1) ) FROM preview_cards;
  PERFORM SETVAL('reports_id_seq', COALESCE(MAX(id), 1) ) FROM reports;
  PERFORM SETVAL('session_activations_id_seq', COALESCE(MAX(id), 1) ) FROM session_activations;
  PERFORM SETVAL('settings_id_seq', COALESCE(MAX(id), 1) ) FROM settings;
  PERFORM SETVAL('site_uploads_id_seq', COALESCE(MAX(id), 1) ) FROM site_uploads;
  PERFORM SETVAL('status_pins_id_seq', COALESCE(MAX(id), 1) ) FROM status_pins;
  PERFORM SETVAL('statuses_id_seq', COALESCE(MAX(id), 1) ) FROM statuses;
  PERFORM SETVAL('stream_entries_id_seq', COALESCE(MAX(id), 1) ) FROM stream_entries;
  PERFORM SETVAL('subscriptions_id_seq', COALESCE(MAX(id), 1) ) FROM subscriptions;
  PERFORM SETVAL('tags_id_seq', COALESCE(MAX(id), 1) ) FROM tags;
  PERFORM SETVAL('users_id_seq', COALESCE(MAX(id), 1) ) FROM users;
  PERFORM SETVAL('web_push_subscriptions_id_seq', COALESCE(MAX(id), 1) ) FROM web_push_subscriptions;
  PERFORM SETVAL('web_settings_id_seq', COALESCE(MAX(id), 1) ) FROM web_settings;
END $$;