hydra-migrate-db.nix

with import <nixpkgs> {};

let
  junitFuck = junit.overrideDerivation (drv: {
    postPatch = (drv.postPatch or "") + ''
      sed -i -e '/InaccessibleBaseClassTest/d' \
        src/test/java/org/junit/tests/AllTests.java
    '';
  });

  # TODO: Add to <nixpkgs>
  apgdiff = releaseTools.antBuild rec {
    name = "apgdiff-${version}";
    version = "2.4";

    src = fetchFromGitHub {
      owner = "fordfrog";
      repo = "apgdiff";
      rev = "c1843510463c1a57586420e89cc47ed019d6dae6";
      sha256 = "1sls15bl41yav6qb2j8dgdknzlbdy6wqlmw9bmrfi4fvbwq0552w";
    };

    patchFlags = "--merge -p1";

    patches = lib.singleton (fetchpatch {
      url = "https://github.com/fordfrog/apgdiff/pull/120.patch";
      sha256 = "10lr4mghrd6bhsn02b1vwlanv0iw7k0xvz8calqzp2lz68fbr5q2";
      postFetch = ''
        sed -i -e '/PgDiffTables/,/^---/ {
          /^[ +]import/!b
          s/PgColumnUtils/PgInheritedColumn/
          /ColumnComparator/a \ import ${
            "cz.startnet.utils.pgdiff.schema.PgRelationPrivilege;"
          }
          /ArrayList/d
        }' -e '/PgDiffTest.java/,/^---/ {
          s/add_empty_table.*[^,]$/&,/
          s/modify_column.*$/&,/
          s,});,    // Tests view with column names whose query changes,
          s!}$!                {"view_colnames", false, false, false, false},!
          s!..\*$!                // Tests objects with the $ sign in the name!
        }' "$out"
      '';
    });

    buildInputs = [ junitFuck ];
    antProperties = lib.singleton (lib.nameValuePair "noget" "true");
    jarWrappers = lib.singleton {
      name = "apgdiff";
      jar = "$out/share/java/${name}.jar";
    };
  };

  convertSQL = writeScript "convert-sql.pl" ''
    #!${perl}/bin/perl -pi
    my @s = split /--/;
    if ($s[0] !~ /INSERT|UPDATE|DELETE/) {
      $s[0] =~ s/CVS/_cvs/g;
      $s[0] =~ s/starttime/start_time/g;
      $s[0] =~ s/stoptime/stop_time/g;
      $s[0] =~ s/ ([A-Z])/ \l$1/g;
      $s[0] =~ s/[A-Z]/_\l$&/g;
      $s[0] =~ s/d_e_s_c/DESC/g;
      $s[0] =~ s/drvpath/drv_path/g;
      $s[0] =~ s/altnr/alt_nr/g;
      $s[0] =~ s/user(?:_n|N)ame/username/g;
      $s[0] =~ s/schema_version/schemaversion/g;
    }
    $_ = join("--", @s);
  '';

  hydraPerlDeps = let
    inherit (builtins) currentSystem;
    build = (import ~/hydra/release.nix {}).build.${currentSystem};
  in build.perlDeps;

  writeShell = name: data: let
    text = "#!${stdenv.shell} -e\n${data}";
  in writeScript "${name}.sh" text;

  runTempDB = writeShell "run-tempdb" ''
    export PATH="${lib.makeBinPath [ postgresql coreutils glibc.bin ]}"
    exec "${stdenv.shell}" -e -- src/sql/run-tempdb.sh "$@"
  '';

  updateSchema = writeShell "update-schema" ''
    exec "${runTempDB}" src/sql/hydra.sql \
      "${perl}/bin/perl" -I "${hydraPerlDeps}/lib/perl5/site_perl" -I src/lib \
        -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:src/lib \
        src/sql/update-dbix.pl
  '';

  generateRenames = writeShell "generate-renames" ''
    git diff src/lib/Hydra/Schema | sed -n -e '/^-=head1 TABLE:/ {
      s/^.*C<\([^>]\+\)>.*/ALTER TABLE "\L\1\E"/; h
    }' -e '/^+=head1 TABLE:/ {
      s/^.*C<\([^>]\+\)>.*/ RENAME TO "\1";/; H; x; s/\n//g; p
    }' | sed -e '/ALTER TABLE "\([^"]\+\)" RENAME TO "\1"/d'

    git diff src/sql/hydra.sql | grep '^.create index' | sort -k 1.2 \
      | sed -ne 'h;n;G;p' | sed -n -e '/^-/ {
        s/^.create index \([^ ]\+\).*/ALTER INDEX "\L\1\E"/; h
      }' -e '/^+/ {
        s/^.create index \([^ ]\+\).*/ RENAME TO "\1";/; H; x; s/\n//g; p
      }' | sed -e '/ALTER INDEX "\([^"]\+\)" RENAME TO "\1"/d'

    echo 'ALTER SEQUENCE buildinputs_id_seq RENAME TO build_inputs_id_seq;'
    echo 'ALTER SEQUENCE jobsetevals_id_seq RENAME TO jobset_evals_id_seq;'
    echo 'ALTER SEQUENCE newsitems_id_seq RENAME TO news_items_id_seq;'
  '';

  genUpgradeFile = writeShell "generate-upgrade-file" ''
    newNum="$(expr "$(for i in src/sql/upgrade-*.sql; do
      echo "''${i//[^0-9]/}"
    done | sort -rn | head -n1)" + 1)"
    echo "src/sql/upgrade-$newNum.sql"
  '';

  dbi2postgres = writeShell "dbi2postgres" ''
    IFS=';'; for i in $(echo "''${HYDRA_DBI#dbi:Pg:}"); do
      name="''${i%%=*}"
      value="''${i#*=}"
      case "$name" in
        dbname) name=PGDATABASE;;
        port) name=PGPORT;;
        host) name=PGHOST;;
        *) exit 1;;
      esac
      what="$name=$value"
      whatesc="''${what//\'/\'\\\'\'}"
      echo "export '$whatesc'"
    done
  '';

  dumpdb = outfile: writeShell "dumpdb" ''
    eval "$(${dbi2postgres})"
    pg_dump hydra > "${outfile}"
  '';

  # Handpicked from the output of the following command:
  # git grep -f =(git diff | sed -n -e 's/^-=head2 \+\(.*\)$/\1/p')
  sedIncludes = toString [
    "doc/dev-notes.txt"
    "src/lib/Hydra/Base/Controller/ListBuilds.pm"
    "src/lib/Hydra/Base/Controller/NixChannel.pm"
    "src/lib/Hydra/Controller/API.pm"
    "src/lib/Hydra/Controller/Admin.pm"
    "src/lib/Hydra/Controller/Build.pm"
    "src/lib/Hydra/Controller/Channel.pm"
    "src/lib/Hydra/Controller/Job.pm"
    "src/lib/Hydra/Controller/Jobset.pm"
    "src/lib/Hydra/Controller/JobsetEval.pm"
    "src/lib/Hydra/Controller/Project.pm"
    "src/lib/Hydra/Controller/Release.pm"
    "src/lib/Hydra/Controller/Root.pm"
    "src/lib/Hydra/Controller/User.pm"
    "src/lib/Hydra/Helper/AddBuilds.pm"
    "src/lib/Hydra/Helper/CatalystUtils.pm"
    "src/lib/Hydra/Helper/Nix.pm"
    "src/lib/Hydra/Plugin/BazaarInput.pm"
    "src/lib/Hydra/Plugin/CoverityScan.pm"
    "src/lib/Hydra/Plugin/DarcsInput.pm"
    "src/lib/Hydra/Plugin/EmailNotification.pm"
    "src/lib/Hydra/Plugin/GithubStatus.pm"
    "src/lib/Hydra/Plugin/GitInput.pm"
    "src/lib/Hydra/Plugin/HipChatNotification.pm"
    "src/lib/Hydra/Plugin/MercurialInput.pm"
    "src/lib/Hydra/Plugin/PathInput.pm"
    "src/lib/Hydra/Plugin/S3Backup.pm"
    "src/lib/Hydra/Plugin/SlackNotification.pm"
    "src/lib/Hydra/Plugin/SubversionInput.pm"
    "src/lib/Hydra/View/TT.pm"
    "src/root/all.tt"
    "src/root/auth.tt"
    "src/root/build-deps.tt"
    "src/root/build.tt"
    "src/root/channel-contents.tt"
    "src/root/common.tt"
    "src/root/dashboard-my-jobsets-tab.tt"
    "src/root/dashboard-my-jobs-tab.tt"
    "src/root/dashboard.tt"
    "src/root/deps.tt"
    "src/root/edit-jobset.tt"
    "src/root/edit-project.tt"
    "src/root/edit-release.tt"
    "src/root/error.tt"
    "src/root/evals.tt"
    "src/root/job-metrics-tab.tt"
    "src/root/jobset-channels-tab.tt"
    "src/root/jobset-eval.tt"
    "src/root/jobset-jobs-tab.tt"
    "src/root/jobset.tt"
    "src/root/job.tt"
    "src/root/layout.tt"
    "src/root/log.tt"
    "src/root/machine-status.tt"
    "src/root/machines.tt"
    "src/root/metric.tt"
    "src/root/news.tt"
    "src/root/overview.tt"
    "src/root/plain-reload.tt"
    "src/root/plain.tt"
    "src/root/product-list.tt"
    "src/root/project.tt"
    "src/root/queue-runner-status.tt"
    "src/root/queue-summary.tt"
    "src/root/queue.tt"
    "src/root/release.tt"
    "src/root/reproduce.tt"
    "src/root/runtime-deps.tt"
    "src/root/search.tt"
    "src/root/status.tt"
    "src/root/steps.tt"
    "src/root/topbar.tt"
    "src/root/users.tt"
    "src/root/user.tt"
    "src/script/hydra-create-user"
    "src/script/hydra-evaluator"
    "src/script/hydra-notify"
    "src/script/hydra-update-gc-roots"
    "tests/Setup.pm"
    "tests/api-test.pl"
    "tests/evaluation-tests.pl"
    "tests/s3-backup-test.pl"
  ];

  genSedScript = writeShell "gen-sedscript" ''
    echo '/^ *#/b' # don't replace comments
    echo '/^sub nrbuilds/b' # API.pm
    echo '/accessDenied/b' # User.pm
    echo '/inputData.*emailresponsible/b' # Jobset.pm

    echo '/tabs-buildsteps/b' # build.tt
    echo '/input-template-emailresponsible/b' # edit-jobset.tt

    echo 's/\(<input.*id="[^"]*\)\(.."\)/\1DUMMYXXXDUMMY\2/g'
    echo 's/\(<input.*name="[^"]*\)\(.."\)/\1DUMMYXXXDUMMY\2/g'
    echo 's/stash.*params[^{]*{.../&DUMMYXXXDUMMY/g'
    echo 's/isCurrent/is_current/g'

    git diff | sed -n -e '/^[-+]=head2/p' | tac | uniq -w1 | tac \
      | sed -n -e 's,^-=head2 \+\(.\+\)$,s/\\<\1\\>/,; h; n' \
               -e 's,^+=head2 \+\(.\+\)$,\1/g,; H; x; s/\n//' \
               -e 'p' | sort -u

    echo 's/\$email_responsible/$emailresponsible/g' # AddBuilds.pm

    echo 's/DUMMYXXXDUMMY//g'

    echo 's/build_steps_builds/build_steps/g'
  '';

  replaceManually = writeShell "replace-manually.sh" ''
    sed -i -e '/".*\(${lib.concatStringsSep "\\|" [
      "update" "delete from" "select" "insert" "lock table" "where" "join"
    ]}\)/ {
      s/BuildMetrics/build_metrics/g
      s/BuildOutputs/build_outputs/g
      s/BuildProducts/build_products/g
      s/BuildStepOutputs/build_step_outputs/g
      s/BuildSteps/build_steps/g
      s/Builds/builds/g
      s/FailedPaths/failed_paths/g
      s/Jobsets/jobsets/g
      s/SystemStatus/system_status/g
      s/buildStatus/build_status/g
      s/closureSize/closure_size/g
      s/defaultPath/default_path/g
      s/drvPath/drv_path/g
      s/errorMsg/error_msg/g
      s/fileSize/file_size/g
      s/globalPriority/global_priority/g
      s/isCachedBuild/is_cached_build/g
      s/propagatedFrom/propagated_from/g
      s/releaseName/release_name/g
      s/schedulingShares/scheduling_shares/g
      s/startTime/start_time/g
      s/stopTime/stop_time/g
    }' src/hydra-queue-runner/*.cc \
       src/hydra-queue-runner/*.hh

    sed -i -e 's/buildStatus/build_status/g' src/lib/Hydra/Helper/AddBuilds.pm

    sed -i \
      -e 's/"globalPriority"/"global_priority"/g' \
      -e 's/"drvPath"/"drv_path"/g' \
      -e 's/"schedulingShares"/"scheduling_shares"/g' \
      -e 's/"schedulingPriority"/"scheduling_priority"/g' \
      -e 's/"isChannel"/"is_channel"/g' \
      -e 's/"maxSilent"/"max_silent"/g' \
      -e 's/"startTime"/"start_time"/g' \
      -e 's/"stopTime"/"stop_time"/g' \
      src/hydra-queue-runner/queue-monitor.cc \

    sed -i -e '/select/ {
      s/JobsetEvalMembers/jobset_eval_members/g
      s/Builds/builds/g
    }' src/lib/Hydra/Helper/AddBuilds.pm

    sed -i \
      -e '/select count/s/buildsteps/build_steps/g' \
      -e 's/IndexBuildStepsOnStopTime/index_build_steps_on_stop_time/g' \
      -e 's/displayName/display_name/g' \
      -e '/select/s/Builds/builds/g' \
      -e '/from BuildSteps/ {
        s/BuildSteps/build_steps/g
        s/Builds/builds/g
      }' src/lib/Hydra/Controller/Root.pm

    sed -i \
      -e '/role_relation/s/userroles/user_roles/g' \
      src/lib/Hydra.pm

    sed -i \
      -e 's/display_name/displayname/g' \
      -e 's/nix_expr_path/nixexprpath/g' \
      -e 's/nix_expr_input/nixexprinput/g' \
      -e 's/check_interval/checkinterval/g' \
      tests/api-test.pl

    sed -i -e 's/\$jobset_input/$jobsetinput/g' tests/s3-backup-test.pl
    sed -i -e 's/\$nix_expr_path/$nixexprpath/g' tests/Setup.pm
    sed -i -e 's/releaseName/release_name/g' src/lib/Hydra/View/NixExprs.pm

    sed -i \
      -e 's/JobsetEvalMembers/jobset_eval_members/g' \
      -e 's/join Builds/join builds/g' \
      -e 's/userName\>/username/g' \
      src/lib/Hydra/Helper/CatalystUtils.pm

    sed -i \
      -e 's/buildinputs/build_inputs/g' \
      -e 's/isCurrent/is_current/g' \
      -e '/select/s/Builds/builds/g' \
      -e 's/buildStatus/build_status/g' \
      -e 's/altNr/alt_nr/g' \
      src/lib/Hydra/Helper/Nix.pm

    sed -i -e '/select/ {
      s/hasNewBuilds/has_new_builds/g
      s/JobsetEvalMembers/jobset_eval_members/g
      s/JobsetEvals/jobset_evals/g
      s/Builds/builds/g
    }' src/lib/Hydra/Controller/Admin.pm
  '';

in writeShell "convert-hydra-schema" ''
  git checkout src/lib/Hydra/Schema src/sql/hydra.sql ${sedIncludes} \
               src/hydra-eval-jobs src/hydra-queue-runner \
               src/lib/Hydra.pm
  git clean -f src/lib/Hydra/Schema 'src/sql/upgrade-*.sql'

  if [ "$1" = "-c" ]; then
    exit 0
  fi

  "${convertSQL}" src/sql/hydra.sql

  for i in 1 2; do
    echo '/^# DO NOT MODIFY THIS OR ANYTHING ABOVE!/,$ {' > rewrite_code.sed
    "${genSedScript}" >> rewrite_code.sed
    echo 's/drvpath/drv_path/g' >> rewrite_code.sed
    echo '}' >> rewrite_code.sed
    sed -i -f rewrite_code.sed src/lib/Hydra/Schema/*.pm

    "${updateSchema}"
  done

  upgradeFile="$("${genUpgradeFile}")"
  cat > "$upgradeFile" <<EOF
  -- Rename all database identifiers to use underscores. Generated by:
  -- https://gist.github.com/aszlig/59ea813c28731d339b9764c46f363200
  EOF
  "${generateRenames}" >> "$upgradeFile"
  oldConcat="$(git show HEAD:src/sql/hydra.sql; cat "$upgradeFile")"
  "${runTempDB}" <(echo "$oldConcat") "${dumpdb "old_dump_combined.sql"}"
  "${runTempDB}" src/sql/hydra.sql "${dumpdb "new_dump_combined.sql"}"
  ${apgdiff}/bin/apgdiff old_dump_combined.sql new_dump_combined.sql \
    >> "$upgradeFile"
  perl -pi -e 's,/ *\*.*?\*/ *,,g' "$upgradeFile"
  # apgdiff generates invalid RENAME COLUMN statements, fix them:
  sed -i -n -e '/ALTER TABLE/ {
    h; n; /RENAME COLUMN/! { H; x; p; b }
    :l; /RENAME COLUMN/! { p; b }; G
    s/\(.*\)\n\(.*\)/\2 \1/
    s/[\t ]\+/ /g
    s/[\t ]*[;,]$/;/
    p; n; bl
  }; p' "$upgradeFile"

  # Reorder dropping some constraints, because they depend on each other:
  for reorder in \
    buildstepoutputs_build_fkey1:buildsteps_pkey \
    jobsetevalmembers_pkey:jobsetevalinputs_eval_fkey \
    jobsetevalinputs_eval_fkey:jobsetevalmembers_eval_fkey \
    jobsetevalmembers_eval_fkey:jobsetinputalts_project_fkey
  do (
    echo "/DROP CONSTRAINT ''${reorder#*:};/"
    echo -e '-1,+1d'
    echo "/DROP CONSTRAINT ''${reorder%:*};/"
    echo -e '\nx\nw'
  ) | "${ed}/bin/ed" -s "$upgradeFile" > /dev/null; done

  # The rename detector from apgdiff gets confused here:
  sed -i -e '/RENAME COLUMN errormsg TO fetch_error_msg/ {
    s/errormsg/fetcherrormsg/g
  }' "$upgradeFile"

  sed -i -n -e '/ALTER TABLE/ {
    h; n; /scheduling_\?shares_check/ { n; b; }; H; x
  }; p' "$upgradeFile"

  "${genSedScript}" > rewrite_code.sed
  sed -i -f rewrite_code.sed ${sedIncludes}
  "${replaceManually}"
  rm -f old_dump_combined.sql new_dump_combined.sql rewrite_code.sed
''