-
-
Save unarist/de98db4ba7c4ff915e4ccec4d4917d83 to your computer and use it in GitHub Desktop.
Loading a representative sample of data into a Mastodon database for migration tests
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 $$; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 $$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment