tools/dev/iz/run-queries.sh

   1 #!/bin/sh
   2
   3 if test $# != 3; then
   4   echo "USAGE: $0 DATABASE_USER DATABASE_PASSWORD MYSQL_DATABASE"
   5   exit 1
   6 fi
   7
   8 dbuser="$1"
   9 dbpass="$2"
  10 dbdb="$3"
  11
  12 q1='select issues.issue_id, issue_type, user1.LOGIN_NAME "reporter",
  13        user2.LOGIN_NAME "assigned_to", target_milestone, creation_ts,
  14        max(issue_when) "resolved_ts", resolution, short_desc
  15   from issues left join issues_activity
  16            on issues.issue_id=issues_activity.issue_id and newvalue="RESOLVED",
  17        profiles prof1,
  18        profiles prof2 left join tigris.HELM_USER user1
  19            on user1.USER_ID=prof1.helm_user_id
  20          left join tigris.HELM_USER user2
  21            on user2.USER_ID=prof2.helm_user_id
  22   where prof1.userid=reporter and prof2.userid=assigned_to
  23   group by issues.issue_id
  24   order by issues.issue_id'
  25
  26 q2='select issues.issue_id, issue_type, user1.LOGIN_NAME "reporter",
  27        user2.LOGIN_NAME "assigned_to", target_milestone, creation_ts,
  28        max(issue_when) "resolved_ts", resolution, short_desc,
  29        priority
  30   from issues left join issues_activity
  31            on issues.issue_id=issues_activity.issue_id and newvalue="RESOLVED",
  32        profiles prof1,
  33        profiles prof2 left join tigris.HELM_USER user1
  34            on user1.USER_ID=prof1.helm_user_id
  35          left join tigris.HELM_USER user2
  36            on user2.USER_ID=prof2.helm_user_id
  37   where prof1.userid=reporter and prof2.userid=assigned_to
  38   group by issues.issue_id
  39   order by issues.issue_id'
  40
  41 mysql --batch -e "use $dbdb; $q1" --user=$dbuser --password=$dbpass --silent > iz-data/query-set-1.tsv
  42 mysql --batch -e "use $dbdb; $q2" --user=$dbuser --password=$dbpass --silent > iz-data/query-set-2.tsv