ss14_sqlite_to_postgres.pgloader

-- Before running this, you have to do this query on the source sqlite database:

-- `UPDATE profile SET markings = CAST(markings AS TEXT);`

-- This fixes copying the json over, because jsonb is a different format entirely in sqlite.
-- Note that you can write pgloader scripts to run queries on the target database before starting,
-- but not to run queries on the source database, so you have to do this manually.

-- You can also truncate admin_log and admin_log_player in the sqlite,
-- since they make up 99% of the data so you'll save a lot of time,
-- and long log messages can cause a weird error with
-- "word is too long to be indexed"
-- which I haven't been able to figure out.

-- `DELETE FROM admin_log; DELETE FROM admin_log_player;`


-- references:
-- https://pgloader.readthedocs.io/en/latest/ref/sqlite.html
-- https://pgloader.readthedocs.io/en/latest/command.html
-- https://www.sqlite.org/datatype3.html
-- https://www.postgresql.org/docs/current/datatype.html


LOAD DATABASE
    FROM {{SQLITEPATH}}
    INTO {{POSTGRESPATH}}


WITH
    -- don't drop or create tables, the server application creates them first
    include no drop, create no tables,
    create no indexes, no foreign keys,
    -- remove any rows created by running the server application
    truncate,
    -- disables foreign key checking while loading the data,
    -- then re-enables it afterwards.
    -- enables data to be loaded into tables with
    -- pre-existing constraints not created by pgloader
    disable triggers,
    -- set postgres sequences to appropriate ids
    -- this actually doesn't work because pgloader only
    -- does this for sequences it creates.
    -- so we do this manually with AFTER LOAD DO
    -- reset sequences,
    -- this seems to fix a "heap exhausted" error
    -- see here https://github.com/dimitri/pgloader/issues/962#issuecomment-490258129
    -- default 100000
    prefetch rows = 25000


-- sqlite types all bogstandard INTEGER, TEXT
-- BLOBS all go to bytea, default pgloader casting
-- only notable sqlite types:
-- preference.admin_ooc_color is lowercase "text"
-- admin_log.json and profile.markings are "jsonb"

-- columns named "id" are either INTEGER or TEXT uuids
-- INTEGERs are going to integer not the default bigint
-- so I would change the default type cast and cast columns that are exceptions

-- I don't know why someone suggested casting integer ids to serials
-- when they're integers with generated by default as identity
-- I have put that in but left it commented out for now

-- tables are alphabetic
-- columns within tables are in the order shown by pgAdmin 4
CAST
    column admin.user_id to uuid,

    -- column admin_flag.admin_flag_id to serial,
    column admin_flag.admin_flag_id to integer,
    column admin_flag.negative to boolean,
    column admin_flag.admin_id to uuid,

    column admin_log.date to timestamptz using sqlite-timestamp-to-timestamp,
    -- column admin_log.json to jsonb,
    column admin_log.impact to smallint,

    column admin_log_player.player_user_id to uuid,

    -- column admin_messages.admin_messages_id to serial,
    column admin_messages.admin_messages_id to integer,
    column admin_messages.player_user_id to uuid,
    column admin_messages.playtime_at_note to interval,
    column admin_messages.message to varchar,
    column admin_messages.created_by_id to uuid,
    column admin_messages.created_at to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_messages.last_edited_by_id to uuid,
    column admin_messages.last_edited_at to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_messages.expiration_time to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_messages.deleted to boolean,
    column admin_messages.deleted_by_id to uuid,
    column admin_messages.deleted_at to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_messages.seen to boolean,
    column admin_messages.dismissed to boolean,

    -- column admin_notes.admin_notes_id to serial,
    column admin_notes.admin_notes_id to integer,
    column admin_notes.player_user_id to uuid,
    column admin_notes.message to varchar,
    column admin_notes.created_by_id to uuid,
    column admin_notes.created_at to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_notes.last_edited_by_id to uuid,
    column admin_notes.last_edited_at to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_notes.deleted to boolean,
    column admin_notes.deleted_by_id to uuid,
    column admin_notes.deleted_at to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_notes.secret to boolean,
    column admin_notes.expiration_time to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_notes.playtime_at_note to interval,

    -- column admin_rank.admin_rank_id to serial,
    column admin_rank.admin_rank_id to integer,

    -- column admin_rank_flag.admin_rank_flag_id to serial,
    column admin_rank_flag.admin_rank_flag_id to integer,

    -- column admin_watchlists.admin_watchlists_id to serial,
    column admin_watchlists.admin_watchlists_id to integer,
    column admin_watchlists.player_user_id to uuid,
    column admin_watchlists.playtime_at_note to interval,
    column admin_watchlists.message to varchar,
    column admin_watchlists.created_by_id to uuid,
    column admin_watchlists.created_at to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_watchlists.last_edited_by_id to uuid,
    column admin_watchlists.last_edited_at to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_watchlists.expiration_time to timestamptz using sqlite-timestamp-to-timestamp,
    column admin_watchlists.deleted to boolean,
    column admin_watchlists.deleted_by_id to uuid,
    column admin_watchlists.deleted_at to timestamptz using sqlite-timestamp-to-timestamp,

    -- column antag.antag_id to serial,
    column antag.antag_id to integer,

    -- column assigned_user_id.assigned_user_id_id to serial,
    column assigned_user_id.assigned_user_id_id to integer,
    column assigned_user_id.user_id to uuid,

    column ban_template.length to interval,
    column ban_template.auto_delete to boolean,
    column ban_template.hidden to boolean,

    -- column connection_log.connection_log_id to serial,
    column connection_log.connection_log_id to integer,
    column connection_log.user_id to uuid,
    column connection_log.time to timestamptz using sqlite-timestamp-to-timestamp,
    column connection_log.address to inet,
    column connection_log.denied to smallint,

    -- column job.job_id to serial,
    column job.job_id to integer,

    -- column play_time.play_time_id to serial,
    column play_time.play_time_id to integer,
    column play_time.player_id to uuid,
    column play_time.time_spent to interval,

    -- column player.player_id to serial,
    column player.player_id to integer,
    column player.user_id to uuid,
    column player.first_seen_time to timestamptz using sqlite-timestamp-to-timestamp,
    column player.last_seen_time to timestamptz using sqlite-timestamp-to-timestamp,
    column player.last_seen_address to inet,
    column player.last_read_rules to timestamptz using sqlite-timestamp-to-timestamp,

    -- none in player_round table

    -- column preference.preference_id to serial,
    column preference.preference_id to integer,
    column preference.user_id to uuid,

    -- column profile.profile_id to serial,
    column profile.profile_id to integer,
    -- column profile.markings to jsonb,

    -- column profile_loadout.profile_loadout_id to serial,
    column profile_loadout.profile_loadout_id to integer,

    -- column profile_loadout_group.profile_loadout_group_id to serial,
    column profile_loadout_group.profile_loadout_group_id to integer,

    -- column profile_role_loadout.profile_role_loadout_id to serial,
    column profile_role_loadout.profile_role_loadout_id to integer,

    column role_whitelists.player_user_id to uuid,

    -- column round.round_id to serial,
    column round.round_id to integer,
    column round.start_date to timestamptz using sqlite-timestamp-to-timestamp,

    -- column server.server_id to serial,
    column server.server_id to integer,

    -- column server_ban.server_ban_id to serial,
    column server_ban.server_ban_id to integer,
    column server_ban.player_user_id to uuid,
    column server_ban.address to inet,
    column server_ban.ban_time to timestamptz using sqlite-timestamp-to-timestamp,
    column server_ban.expiration_time to timestamptz using sqlite-timestamp-to-timestamp,
    column server_ban.banning_admin to uuid,
    column server_ban.auto_delete to boolean,
    column server_ban.hidden to boolean,
    column server_ban.last_edited_at to timestamptz using sqlite-timestamp-to-timestamp,
    column server_ban.last_edited_by_id to uuid,
    column server_ban.playtime_at_note to interval,

    column server_ban_exemption.user_id to uuid,

    -- column server_ban_hit.server_ban_hit_id to serial,
    column server_ban_hit.server_ban_hit_id to integer,

    -- column server_role_ban.server_role_ban_id to serial,
    column server_role_ban.server_role_ban_id to integer,
    column server_role_ban.player_user_id to uuid,
    column server_role_ban.address to inet,
    column server_role_ban.ban_time to timestamptz using sqlite-timestamp-to-timestamp,
    column server_role_ban.expiration_time to timestamptz using sqlite-timestamp-to-timestamp,
    column server_role_ban.banning_admin to uuid,
    column server_role_ban.hidden to boolean,
    column server_role_ban.last_edited_at to timestamptz using sqlite-timestamp-to-timestamp,
    column server_role_ban.last_edited_by_id to uuid,
    column server_role_ban.playtime_at_note to interval,

    -- column server_role_unban.role_unban_id to serial,
    column server_role_unban.role_unban_id to integer,
    column server_role_unban.unbanning_admin to uuid,
    column server_role_unban.unban_time to timestamptz using sqlite-timestamp-to-timestamp,

    -- column server_unban.unban_id to serial,
    column server_unban.unban_id to integer,
    column server_unban.unbanning_admin to uuid,
    column server_unban.unban_time to timestamptz using sqlite-timestamp-to-timestamp,

    -- column trait.trait_id to serial,
    column trait.trait_id to integer,

    -- column uploaded_resource_log.uploaded_resource_log_id to serial,
    column uploaded_resource_log.uploaded_resource_log_id to integer,
    column uploaded_resource_log.date to timestamptz using sqlite-timestamp-to-timestamp,
    column uploaded_resource_log.user_id to uuid,

    column whitelist.user_id to uuid,

    -- "the first match prevents following rules to be applied"
    -- https://pgloader.readthedocs.io/en/latest/ref/pgsql.html#postgresql-database-casting-rules
    type integer to integer using integer-to-string


-- reset existing sequences
-- you can see how pgloader does it here:
-- https://github.com/dimitri/pgloader/blob/29afa9de0563b25bf2da3a9b2d84e2b1b25de098/src/pgsql/pgsql-create-schema.lisp#L381
AFTER LOAD DO
    $$ SELECT setval('admin_flag_admin_flag_id_seq', (SELECT GREATEST(MAX(admin_flag_id), 1) FROM admin_flag)); $$,
    $$ SELECT setval('admin_messages_admin_messages_id_seq', (SELECT GREATEST(MAX(admin_messages_id), 1) FROM admin_messages)); $$,
    $$ SELECT setval('admin_notes_admin_notes_id_seq', (SELECT GREATEST(MAX(admin_notes_id), 1) FROM admin_notes)); $$,
    $$ SELECT setval('admin_rank_admin_rank_id_seq', (SELECT GREATEST(MAX(admin_rank_id), 1) FROM admin_rank)); $$,
    $$ SELECT setval('admin_rank_flag_admin_rank_flag_id_seq', (SELECT GREATEST(MAX(admin_rank_flag_id), 1) FROM admin_rank_flag)); $$,
    $$ SELECT setval('admin_watchlists_admin_watchlists_id_seq', (SELECT GREATEST(MAX(admin_watchlists_id), 1) FROM admin_watchlists)); $$,
    $$ SELECT setval('antag_antag_id_seq', (SELECT GREATEST(MAX(antag_id), 1) FROM antag)); $$,
    $$ SELECT setval('assigned_user_id_assigned_user_id_id_seq', (SELECT GREATEST(MAX(assigned_user_id_id), 1) FROM assigned_user_id)); $$,
    $$ SELECT setval('ban_template_ban_template_id_seq', (SELECT GREATEST(MAX(ban_template_id), 1) FROM ban_template)); $$,
    $$ SELECT setval('connection_log_connection_log_id_seq', (SELECT GREATEST(MAX(connection_log_id), 1) FROM connection_log)); $$,
    $$ SELECT setval('job_job_id_seq', (SELECT GREATEST(MAX(job_id), 1) FROM job)); $$,
    $$ SELECT setval('play_time_play_time_id_seq', (SELECT GREATEST(MAX(play_time_id), 1) FROM play_time)); $$,
    $$ SELECT setval('player_player_id_seq', (SELECT GREATEST(MAX(player_id), 1) FROM player)); $$,
    $$ SELECT setval('preference_preference_id_seq', (SELECT GREATEST(MAX(preference_id), 1) FROM preference)); $$,
    $$ SELECT setval('profile_loadout_group_profile_loadout_group_id_seq', (SELECT GREATEST(MAX(profile_loadout_group_id), 1) FROM profile_loadout_group)); $$,
    $$ SELECT setval('profile_loadout_profile_loadout_id_seq', (SELECT GREATEST(MAX(profile_loadout_id), 1) FROM profile_loadout)); $$,
    $$ SELECT setval('profile_profile_id_seq', (SELECT GREATEST(MAX(profile_id), 1) FROM profile)); $$,
    $$ SELECT setval('profile_role_loadout_profile_role_loadout_id_seq', (SELECT GREATEST(MAX(profile_role_loadout_id), 1) FROM profile_role_loadout)); $$,
    $$ SELECT setval('round_round_id_seq', (SELECT GREATEST(MAX(round_id), 1) FROM round)); $$,
    $$ SELECT setval('server_ban_hit_server_ban_hit_id_seq', (SELECT GREATEST(MAX(server_ban_hit_id), 1) FROM server_ban_hit)); $$,
    $$ SELECT setval('server_ban_server_ban_id_seq', (SELECT GREATEST(MAX(server_ban_id), 1) FROM server_ban)); $$,
    $$ SELECT setval('server_role_ban_server_role_ban_id_seq', (SELECT GREATEST(MAX(server_role_ban_id), 1) FROM server_role_ban)); $$,
    $$ SELECT setval('server_role_unban_role_unban_id_seq', (SELECT GREATEST(MAX(role_unban_id), 1) FROM server_role_unban)); $$,
    $$ SELECT setval('server_server_id_seq', (SELECT GREATEST(MAX(server_id), 1) FROM server)); $$,
    $$ SELECT setval('server_unban_unban_id_seq', (SELECT GREATEST(MAX(unban_id), 1) FROM server_unban)); $$,
    $$ SELECT setval('trait_trait_id_seq', (SELECT GREATEST(MAX(trait_id), 1) FROM trait)); $$,
    $$ SELECT setval('uploaded_resource_log_uploaded_resource_log_id_seq', (SELECT GREATEST(MAX(uploaded_resource_log_id), 1) FROM uploaded_resource_log)); $$
;