gerrit1_import/import_gerrit1_a.sql - gerrit - Git at Google

 -- PostgreSQL conversion from Gerrit 1 -> Gerrit 2
 --
 -- If this is the first time the Gerrit1 schema has been used it
 -- needs to be renamed:
 --     psql -c 'ALTER SCHEMA public RENAME TO gerrit1' $srcdb
 --
 -- Make sure there is a current dump file:
 --     pg_dump -O -Fc $src >gerrit1.dump
 --
 -- Write your "UPDATE system_config SET ..." and put it into
 -- some config.sql script.  At least git_base_path must be set.
 --
 -- Execute the conversion script:
 --     gerrit1_import/1-to-2.sh gerrit1.dump config.sql
 --

 DELETE FROM accounts;
 INSERT INTO accounts
 (account_id,
  registered_on,
  full_name,
  preferred_email,
  ssh_user_name,
  contact_address,
  contact_country,
  contact_phone_nbr,
  contact_fax_nbr,
  default_context
 ) SELECT
  nextval('account_id'),
  a.created,
  a.real_name,
  a.user_email,
  CASE WHEN a.user_email LIKE '%@%'
       THEN lower(substring(a.user_email from '^(.*)@.*$'))
       ELSE NULL
  END,
  a.mailing_address,
  a.mailing_address_country,
  a.phone_number,
  a.fax_number,
  a.default_context
  FROM gerrit1.accounts a;

 UPDATE system_config
 SET allow_google_account_upgrade = 'Y';

 DELETE FROM account_external_ids;
 INSERT INTO account_external_ids
 (account_id,
  external_id,
  email_address) SELECT
  l.account_id,
  'Google Account ' || a.user_email,
  a.user_email
  FROM gerrit1.accounts a, accounts l
  WHERE l.preferred_email = a.user_email;

 DELETE FROM contributor_agreements;
 INSERT INTO contributor_agreements
 (active,
  require_contact_information,
  auto_verify,
  short_name,
  short_description,
  agreement_url,
  id) VALUES (
  'Y',
  'Y',
  'Y',
  'Individual',
  'If you are going to be contributing code on your own, this is the one you want. You can sign this one online.',
  'static/cla_individual.html',
  nextval('contributor_agreement_id'));
 INSERT INTO contributor_agreements
 (active,
  require_contact_information,
  auto_verify,
  short_name,
  short_description,
  agreement_url,
  id) VALUES (
  'Y',
  'N',
  'N',
  'Corporate',
  'If you are going to be contributing code on behalf of your company, this is the one you want. We\'ll give you a form that will need to printed, signed and sent back via post, email or fax.',
  'static/cla_corporate.html',
  nextval('contributor_agreement_id'));

 DELETE FROM account_agreements;
 INSERT INTO account_agreements
 (accepted_on,
  status,
  account_id,
  review_comments,
  reviewed_by,
  reviewed_on,
  cla_id) SELECT
  a.individual_cla_timestamp,
  CASE WHEN a.cla_verified = 'Y' THEN 'V'
       ELSE 'n'
  END,
  r.account_id,
  a.cla_comments,
  (SELECT m.account_id FROM accounts m
   WHERE m.preferred_email = a.cla_verified_by),
  a.cla_verified_timestamp,
  i.id
  FROM contributor_agreements i,
  gerrit1.accounts a,
  accounts r
  WHERE i.short_name = 'Individual'
  AND a.individual_cla_version = 1
  AND r.preferred_email = a.user_email;
 INSERT INTO account_agreements
 (accepted_on,
  status,
  account_id,
  review_comments,
  reviewed_by,
  reviewed_on,
  cla_id) SELECT
  CASE WHEN a.individual_cla_timestamp IS NOT NULL
       THEN a.individual_cla_timestamp
       ELSE a.created
       END,
  'V',
  r.account_id,
  a.cla_comments,
  (SELECT m.account_id FROM accounts m
   WHERE m.preferred_email = a.cla_verified_by),
  a.cla_verified_timestamp,
  i.id
  FROM contributor_agreements i,
  gerrit1.accounts a,
  accounts r
  WHERE i.short_name = 'Corporate'
  AND a.individual_cla_version = 0
  AND a.cla_verified = 'Y'
  AND r.preferred_email = a.user_email;

 DELETE FROM account_groups;
 INSERT INTO account_groups
 (group_id,
  description,
  name) SELECT
  nextval('account_group_id'),
  g.comment,
  g.name
  FROM gerrit1.account_groups g;

 INSERT INTO account_groups
 (group_id,
  description,
  name) VALUES
 (nextval('account_group_id'),
 'Any user, signed-in or not',
 'Anonymous Users');

 INSERT INTO account_groups
 (group_id,
  description,
  name) VALUES
 (nextval('account_group_id'),
 'Any signed-in user',
 'Registered Users');


 DELETE FROM account_group_members;
 INSERT INTO account_group_members
 (account_id,
  group_id) SELECT
  a.account_id,
  g.group_id
  FROM accounts a,
  account_groups g,
  gerrit1.account_group_users o
  WHERE
  o.group_name = g.name
  AND a.preferred_email = o.email;

 UPDATE system_config
 SET
  use_contributor_agreements = 'Y'
 ,admin_group_id = (SELECT group_id
                   FROM account_groups
                   WHERE name = 'admin')
 ,anonymous_group_id = (SELECT group_id
                       FROM account_groups
                       WHERE name = 'Anonymous Users')
 ,registered_group_id = (SELECT group_id
                       FROM account_groups
                       WHERE name = 'Registered Users');

 UPDATE account_groups
 SET owner_group_id = (SELECT admin_group_id FROM system_config);

 DELETE FROM projects WHERE project_id <> 0;
 INSERT INTO projects
 (project_id,
  description,
  name,
  use_contributor_agreements,
  owner_group_id) SELECT
  p.project_id,
  p.comment,
  p.name,
  'Y',
  (SELECT admin_group_id FROM system_config)
  FROM gerrit1.projects p;

 DELETE FROM account_project_watches;
 INSERT INTO account_project_watches
 (account_id,
  project_id) SELECT
  a.account_id,
  p.project_id
  FROM gerrit1.projects p,
  accounts a,
  gerrit1.account_unclaimed_changes_projects q
  WHERE a.preferred_email = q.email
  AND p.gae_key = q.project_key;

 DELETE FROM branches;
 INSERT INTO branches
 (branch_id,
  project_name,
  branch_name) SELECT
  nextval('branch_id'),
  p.name,
  b.name
  FROM gerrit1.branches b, gerrit1.projects p
  WHERE p.gae_key = b.project_key;

 CREATE TEMPORARY TABLE need_groups (project_id INT NOT NULL);
 INSERT INTO need_groups
  SELECT p.project_id
  FROM projects p, gerrit1.project_owner_groups o
  WHERE p.project_id = o.project_id
  GROUP BY p.project_id
  HAVING COUNT(*) > 1;
 INSERT INTO need_groups
  SELECT p.project_id
  FROM projects p
  WHERE EXISTS (SELECT 1 FROM gerrit1.project_owner_users u
                WHERE u.project_id = p.project_id)
  AND NOT EXISTS (SELECT 1 FROM need_groups n
                  WHERE n.project_id = p.project_id);

 INSERT INTO account_groups
 (group_id,
  owner_group_id,
  description,
  name) SELECT
  nextval('account_group_id'),
  (SELECT admin_group_id FROM system_config),
  p.name || ' maintainers',
  substring(p.name from '^.*/([^/]*)$') || '_' || p.project_id || '-owners'
  FROM projects p, need_groups g
  WHERE p.project_id = g.project_id;

 UPDATE account_groups
 SET owner_group_id = group_id
 WHERE name IN (SELECT
  substring(p.name from '^.*/([^/]*)$') || '_' || p.project_id || '-owners'
  FROM projects p, need_groups g
  WHERE p.project_id = g.project_id);

 UPDATE projects
 SET owner_group_id = (SELECT group_id
 FROM account_groups
 WHERE name = substring(projects.name
 from '^.*/([^/]*)$') || '_' || projects.project_id || '-owners')
 WHERE project_id IN (SELECT project_id FROM need_groups);

 INSERT INTO account_group_members
 (account_id,
  group_id) SELECT DISTINCT
  q.account_id,
  p.owner_group_id
  FROM projects p,
  need_groups n,
  gerrit1.account_groups og,
  gerrit1.project_owner_groups o,
  account_groups g,
  account_group_members q
  WHERE
  n.project_id = p.project_id
  AND o.project_id = p.project_id
  AND og.gae_key = o.group_key
  AND g.name = og.name
  AND q.group_id = g.group_id
  UNION
  SELECT
  a.account_id,
  p.owner_group_id
  FROM accounts a,
  projects p,
  need_groups n,
  gerrit1.project_owner_users o
  WHERE
  n.project_id = p.project_id
  AND o.project_id = p.project_id
  AND a.preferred_email = o.email;

 UPDATE projects
 SET owner_group_id = (
  SELECT g.group_id
  FROM account_groups g,
  gerrit1.project_owner_groups o,
  gerrit1.account_groups og
  WHERE projects.project_id = o.project_id
  AND og.gae_key = o.group_key
  AND g.name = og.name)
 WHERE project_id NOT IN (SELECT project_id FROM need_groups)
 AND EXISTS (
  SELECT g.group_id
  FROM account_groups g,
  gerrit1.project_owner_groups o,
  gerrit1.account_groups og
  WHERE projects.project_id = o.project_id
  AND og.gae_key = o.group_key
  AND g.name = og.name);

 DROP TABLE need_groups;


 DELETE FROM changes;
 INSERT INTO changes
 (created_on,
  last_updated_on,
  owner_account_id,
  dest_project_name,
  dest_branch_name,
  status,
  open,
  nbr_patch_sets,
  current_patch_set_id,
  subject,
  change_id) SELECT
  c.created,
  c.modified,
  a.account_id,
  p.name,
  b.name,
  CASE WHEN c.merged = 'Y' THEN 'M'
       WHEN c.closed = 'Y' THEN 'A'
       ELSE 'n'
  END,
  CASE WHEN c.merged = 'Y' THEN 'N'
       WHEN c.closed = 'Y' THEN 'N'
       ELSE 'Y'
  END,
  c.n_patchsets,
  c.n_patchsets,
  c.subject,
  c.change_id
  FROM gerrit1.changes c,
  accounts a,
  gerrit1.projects p,
  gerrit1.branches b
  WHERE
  a.preferred_email = c.owner
  AND p.gae_key = c.dest_project_key
  AND b.gae_key = c.dest_branch_key
  ;

 UPDATE gerrit1.messages
 SET sender = substring(sender from '<(.*)>')
 WHERE sender LIKE '%<%>';

 UPDATE gerrit1.messages
 SET sender = NULL
 WHERE sender = 'code-review@android.com';

 UPDATE gerrit1.messages
 SET body = 'Change has been successfully merged into the git repository.'
 WHERE body LIKE '
 Hi.

 Your change has been successfully merged into the git repository.

 -Your friendly git merger%'
 AND sender IS NULL;

 UPDATE gerrit1.messages
 SET body = 'Change could not be merged because of a missing dependency.  As soon as its dependencies are submitted, the change will be submitted.'
 WHERE (body LIKE '
 Hi.

 Your change could not be merged because of a missing dependency.%

 -Your friendly git merger%'
 OR body LIKE '
 Your change could not be merged because of a missing dependency.

 As soon as all dependencies are submitted, your change will be
 resubmitted automatically.%'
 ) AND sender IS NULL;

 UPDATE gerrit1.messages
 SET body = 'Change cannot be merged because of a path conflict.'
 WHERE body LIKE '
 Hi.

 Your change has not been successfully merged into the git repository
 because of a path conflict.

 -Your friendly git merger%'
 AND sender is NULL;

 UPDATE gerrit1.messages
 SET body = TRIM(both '
 ' FROM body);

 DELETE FROM change_messages;
 INSERT INTO change_messages
 (change_id,
  uuid,
  author_id,
  written_on,
  message) SELECT
  c.change_id,
  substr(m.gae_key, length(m.change_key) + length('wLEgdNZXNzYWdlG')),
  a.account_id,
  m.date_sent,
  m.body
  FROM gerrit1.messages m
  LEFT OUTER JOIN accounts a ON a.preferred_email = m.sender,
  gerrit1.changes c
  WHERE
  c.gae_key = m.change_key;

 DELETE FROM patch_sets;
 INSERT INTO patch_sets
 (revision,
  change_id,
  patch_set_id,
  created_on,
  uploader_account_id) SELECT
  r.revision_id,
  c.change_id,
  p.patchset_id,
  p.created,
  a.account_id
  FROM gerrit1.patch_sets p
  JOIN accounts a ON a.preferred_email = p.owner
  JOIN gerrit1.changes c ON p.change_key = c.gae_key
  LEFT OUTER JOIN gerrit1.revisions r ON r.gae_key = p.revision_key;

 DELETE FROM patch_set_info;
 INSERT INTO patch_set_info
 (subject,
  message,
  author_name,
  author_email,
  author_when,
  author_tz,
  committer_name,
  committer_email,
  committer_when,
  committer_tz,
  change_id,
  patch_set_id) SELECT DISTINCT
  (SELECT c.subject FROM changes c
   WHERE c.change_id = p.change_id
   AND c.current_patch_set_id = p.patch_set_id),
  r.message,
  r.author_name,
  r.author_email,
  r.author_when,
  r.author_tz,
  r.committer_name,
  r.committer_email,
  r.committer_when,
  r.committer_tz,
  p.change_id,
  p.patch_set_id
  FROM gerrit1.revisions r, patch_sets p
  WHERE r.revision_id = p.revision;

 DELETE FROM patch_set_ancestors;
 INSERT INTO patch_set_ancestors
 (ancestor_revision,
 change_id,
 patch_set_id,
 position
 ) SELECT DISTINCT
  p.parent_id,
  ps.change_id,
  ps.patch_set_id,
  p.position
  FROM gerrit1.revision_ancestors p,
  patch_sets ps
  WHERE ps.revision = p.child_id;

 DELETE FROM patches;
 INSERT INTO patches
 (change_type,
  patch_type,
  nbr_comments,
  change_id,
  patch_set_id,
  file_name) SELECT
  p.status,
  CASE WHEN p.multi_way_diff = 'Y' THEN 'N'
       ELSE 'U'
  END,
  p.n_comments,
  c.change_id,
  ps.patchset_id,
  p.filename
  FROM gerrit1.patches p,
  gerrit1.patch_sets ps,
  gerrit1.changes c
  WHERE p.patchset_key = ps.gae_key
  AND ps.change_key = c.gae_key;

 DELETE FROM patch_comments;
 INSERT INTO patch_comments
 (line_nbr,
  author_id,
  written_on,
  status,
  side,
  message,
  change_id,
  patch_set_id,
  file_name,
  uuid) SELECT
  c.lineno,
  a.account_id,
  c.written,
  CASE WHEN c.draft = 'Y' THEN 'd'
       ELSE 'P'
  END,
  CASE WHEN c.is_left = 'Y' THEN 0
       ELSE 1
  END,
  c.body,
  o_c.change_id,
  o_ps.patchset_id,
  o_p.filename,
  c.message_id
  FROM gerrit1.comments c,
  accounts a,
  gerrit1.patches o_p,
  gerrit1.patch_sets o_ps,
  gerrit1.changes o_c
  WHERE o_p.patchset_key = o_ps.gae_key
  AND o_ps.change_key = o_c.gae_key
  AND o_p.gae_key = c.patch_key
  AND a.preferred_email = c.author;

 DELETE FROM change_approvals;
 INSERT INTO change_approvals
 (value,
  change_id,
  account_id,
  category_id) SELECT
  1,
  c.change_id,
  a.account_id,
  'VRIF'
  FROM gerrit1.review_status s,
  gerrit1.changes c,
  accounts a
  WHERE
  s.verified = 'Y'
  AND s.change_key = c.gae_key
  AND a.preferred_email = s.email;
 INSERT INTO change_approvals
 (value,
  change_id,
  account_id,
  category_id) SELECT
  CASE WHEN s.lgtm = 'lgtm' THEN 2
       WHEN s.lgtm = 'yes' THEN 1
       WHEN s.lgtm = 'abstain' THEN 0
       WHEN s.lgtm = 'no' THEN -1
       WHEN s.lgtm = 'reject' THEN -2
       ELSE NULL
  END,
  c.change_id,
  a.account_id,
  'CRVW'
  FROM gerrit1.review_status s,
  gerrit1.changes c,
  accounts a
  WHERE
  s.lgtm IS NOT NULL
  AND s.change_key = c.gae_key
  AND a.preferred_email = s.email;

 DELETE FROM starred_changes;
 INSERT INTO starred_changes
 (account_id,
  change_id) SELECT
  a.account_id,
  c.change_id
 FROM gerrit1.account_stars s,
      accounts a,
      changes c
 WHERE a.preferred_email = s.email
       AND c.change_id = s.change_id;

 UPDATE account_groups
 SET name = 'Administrators'
 WHERE name = 'admin';


 -- Fix change.nbr_patch_sets
 --
 UPDATE changes
 SET nbr_patch_sets = (SELECT MAX(p.patch_set_id)
                       FROM patch_sets p
                       WHERE p.change_id = changes.change_id);

 -- Fix change.last_updated_on
 --
 CREATE TEMPORARY TABLE temp_dates (
 change_id INT NOT NULL,
 dt TIMESTAMP NOT NULL);

 INSERT INTO temp_dates
 SELECT change_id,written_on
 FROM patch_comments
 WHERE status = 'P';

 INSERT INTO temp_dates
 SELECT change_id,written_on FROM change_messages;

 INSERT INTO temp_dates
 SELECT change_id,merge_submitted
 FROM gerrit1.changes
 WHERE merge_submitted IS NOT NULL
 AND merged = 'Y';

 UPDATE changes
 SET last_updated_on = (SELECT MAX(m.dt)
                        FROM temp_dates m
                        WHERE m.change_id = changes.change_id)
 WHERE EXISTS (SELECT 1 FROM temp_dates m
               WHERE m.change_id = changes.change_id);
 DROP TABLE temp_dates;


 -- Fix patches.nbr_comments
 --
 UPDATE patches
 SET nbr_comments = (SELECT COUNT(*)
                     FROM patch_comments c
                     WHERE c.status = 'P'
                     AND c.change_id = patches.change_id
                     AND c.patch_set_id = patches.patch_set_id
                     AND c.file_name = patches.file_name);

 SELECT
  (SELECT COUNT(*) FROM gerrit1.accounts) as accounts_g1,
  (SELECT COUNT(*) FROM accounts) as accounts_g1
 WHERE
   (SELECT COUNT(*) FROM gerrit1.accounts)
 !=(SELECT COUNT(*) FROM accounts);

 SELECT
  (SELECT COUNT(*) FROM gerrit1.changes) as changes_g1,
  (SELECT COUNT(*) FROM changes) as changes_g2
 WHERE
   (SELECT COUNT(*) FROM gerrit1.changes)
 !=(SELECT COUNT(*) FROM changes);

 SELECT
  (SELECT COUNT(*) FROM gerrit1.messages) as messages_g1,
  (SELECT COUNT(*) FROM change_messages) as messages_g2
 WHERE
   (SELECT COUNT(*) FROM gerrit1.messages)
 !=(SELECT COUNT(*) FROM change_messages);

 SELECT
  (SELECT COUNT(*) FROM gerrit1.patch_sets) as patch_sets_g1,
  (SELECT COUNT(*) FROM patch_sets) as patch_sets_g2
 WHERE
   (SELECT COUNT(*) FROM gerrit1.patch_sets)
 !=(SELECT COUNT(*) FROM patch_sets);

 SELECT
  (SELECT COUNT(*) FROM gerrit1.patches g, gerrit1.patch_sets p
   WHERE g.patchset_key = p.gae_key) as patches_g1,
  (SELECT COUNT(*) FROM patches) as patches_g2
 WHERE
   (SELECT COUNT(*) FROM gerrit1.patches g, gerrit1.patch_sets p
    WHERE g.patchset_key = p.gae_key)
 !=(SELECT COUNT(*) FROM patches);

 SELECT
  (SELECT COUNT(*) FROM gerrit1.comments) as comments_g1,
  (SELECT COUNT(*) FROM patch_comments) as comments_g2
 WHERE
   (SELECT COUNT(*) FROM gerrit1.comments)
 !=(SELECT COUNT(*) FROM patch_comments);

 -- Reset sequences
 --
 -- account_group_id (above)
 -- account_id (above)
 -- branch_id (above)
 SELECT setval('change_id',(SELECT MAX(change_id) FROM changes));
 -- contributor_agreement_id (above)
 SELECT setval('project_id',(SELECT MAX(project_id) FROM projects));

 -- Grant access to read tables needed for import
 --
 GRANT SELECT ON gerrit1.project_code_reviews TO gerrit2;
 GRANT SELECT ON gerrit1.approval_right_groups TO gerrit2;
 GRANT SELECT ON gerrit1.approval_right_users TO gerrit2;
 GRANT SELECT ON gerrit1.approval_rights TO gerrit2;
 GRANT SELECT ON gerrit1.account_groups TO gerrit2;
	-- PostgreSQL conversion from Gerrit 1 -> Gerrit 2
	--
	-- If this is the first time the Gerrit1 schema has been used it
	-- needs to be renamed:
	-- psql -c 'ALTER SCHEMA public RENAME TO gerrit1' $srcdb
	--
	-- Make sure there is a current dump file:
	-- pg_dump -O -Fc $src >gerrit1.dump
	--
	-- Write your "UPDATE system_config SET ..." and put it into
	-- some config.sql script. At least git_base_path must be set.
	--
	-- Execute the conversion script:
	-- gerrit1_import/1-to-2.sh gerrit1.dump config.sql
	--

	DELETE FROM accounts;
	INSERT INTO accounts
	(account_id,
	registered_on,
	full_name,
	preferred_email,
	ssh_user_name,
	contact_address,
	contact_country,
	contact_phone_nbr,
	contact_fax_nbr,
	default_context
	) SELECT
	nextval('account_id'),
	a.created,
	a.real_name,
	a.user_email,
	CASE WHEN a.user_email LIKE '%@%'
	THEN lower(substring(a.user_email from '^(.)@.$'))
	ELSE NULL
	END,
	a.mailing_address,
	a.mailing_address_country,
	a.phone_number,
	a.fax_number,
	a.default_context
	FROM gerrit1.accounts a;

	UPDATE system_config
	SET allow_google_account_upgrade = 'Y';

	DELETE FROM account_external_ids;
	INSERT INTO account_external_ids
	(account_id,
	external_id,
	email_address) SELECT
	l.account_id,
	'Google Account ' \|\| a.user_email,
	a.user_email
	FROM gerrit1.accounts a, accounts l
	WHERE l.preferred_email = a.user_email;

	DELETE FROM contributor_agreements;
	INSERT INTO contributor_agreements
	(active,
	require_contact_information,
	auto_verify,
	short_name,
	short_description,
	agreement_url,
	id) VALUES (
	'Y',
	'Y',
	'Y',
	'Individual',
	'If you are going to be contributing code on your own, this is the one you want. You can sign this one online.',
	'static/cla_individual.html',
	nextval('contributor_agreement_id'));
	INSERT INTO contributor_agreements
	(active,
	require_contact_information,
	auto_verify,
	short_name,
	short_description,
	agreement_url,
	id) VALUES (
	'Y',
	'N',
	'N',
	'Corporate',
	'If you are going to be contributing code on behalf of your company, this is the one you want. We\'ll give you a form that will need to printed, signed and sent back via post, email or fax.',
	'static/cla_corporate.html',
	nextval('contributor_agreement_id'));

	DELETE FROM account_agreements;
	INSERT INTO account_agreements
	(accepted_on,
	status,
	account_id,
	review_comments,
	reviewed_by,
	reviewed_on,
	cla_id) SELECT
	a.individual_cla_timestamp,
	CASE WHEN a.cla_verified = 'Y' THEN 'V'
	ELSE 'n'
	END,
	r.account_id,
	a.cla_comments,
	(SELECT m.account_id FROM accounts m
	WHERE m.preferred_email = a.cla_verified_by),
	a.cla_verified_timestamp,
	i.id
	FROM contributor_agreements i,
	gerrit1.accounts a,
	accounts r
	WHERE i.short_name = 'Individual'
	AND a.individual_cla_version = 1
	AND r.preferred_email = a.user_email;
	INSERT INTO account_agreements
	(accepted_on,
	status,
	account_id,
	review_comments,
	reviewed_by,
	reviewed_on,
	cla_id) SELECT
	CASE WHEN a.individual_cla_timestamp IS NOT NULL
	THEN a.individual_cla_timestamp
	ELSE a.created
	END,
	'V',
	r.account_id,
	a.cla_comments,
	(SELECT m.account_id FROM accounts m
	WHERE m.preferred_email = a.cla_verified_by),
	a.cla_verified_timestamp,
	i.id
	FROM contributor_agreements i,
	gerrit1.accounts a,
	accounts r
	WHERE i.short_name = 'Corporate'
	AND a.individual_cla_version = 0
	AND a.cla_verified = 'Y'
	AND r.preferred_email = a.user_email;

	DELETE FROM account_groups;
	INSERT INTO account_groups
	(group_id,
	description,
	name) SELECT
	nextval('account_group_id'),
	g.comment,
	g.name
	FROM gerrit1.account_groups g;

	INSERT INTO account_groups
	(group_id,
	description,
	name) VALUES
	(nextval('account_group_id'),
	'Any user, signed-in or not',
	'Anonymous Users');

	INSERT INTO account_groups
	(group_id,
	description,
	name) VALUES
	(nextval('account_group_id'),
	'Any signed-in user',
	'Registered Users');


	DELETE FROM account_group_members;
	INSERT INTO account_group_members
	(account_id,
	group_id) SELECT
	a.account_id,
	g.group_id
	FROM accounts a,
	account_groups g,
	gerrit1.account_group_users o
	WHERE
	o.group_name = g.name
	AND a.preferred_email = o.email;

	UPDATE system_config
	SET
	use_contributor_agreements = 'Y'
	,admin_group_id = (SELECT group_id
	FROM account_groups
	WHERE name = 'admin')
	,anonymous_group_id = (SELECT group_id
	FROM account_groups
	WHERE name = 'Anonymous Users')
	,registered_group_id = (SELECT group_id
	FROM account_groups
	WHERE name = 'Registered Users');

	UPDATE account_groups
	SET owner_group_id = (SELECT admin_group_id FROM system_config);

	DELETE FROM projects WHERE project_id <> 0;
	INSERT INTO projects
	(project_id,
	description,
	name,
	use_contributor_agreements,
	owner_group_id) SELECT
	p.project_id,
	p.comment,
	p.name,
	'Y',
	(SELECT admin_group_id FROM system_config)
	FROM gerrit1.projects p;

	DELETE FROM account_project_watches;
	INSERT INTO account_project_watches
	(account_id,
	project_id) SELECT
	a.account_id,
	p.project_id
	FROM gerrit1.projects p,
	accounts a,
	gerrit1.account_unclaimed_changes_projects q
	WHERE a.preferred_email = q.email
	AND p.gae_key = q.project_key;

	DELETE FROM branches;
	INSERT INTO branches
	(branch_id,
	project_name,
	branch_name) SELECT
	nextval('branch_id'),
	p.name,
	b.name
	FROM gerrit1.branches b, gerrit1.projects p
	WHERE p.gae_key = b.project_key;

	CREATE TEMPORARY TABLE need_groups (project_id INT NOT NULL);
	INSERT INTO need_groups
	SELECT p.project_id
	FROM projects p, gerrit1.project_owner_groups o
	WHERE p.project_id = o.project_id
	GROUP BY p.project_id
	HAVING COUNT(*) > 1;
	INSERT INTO need_groups
	SELECT p.project_id
	FROM projects p
	WHERE EXISTS (SELECT 1 FROM gerrit1.project_owner_users u
	WHERE u.project_id = p.project_id)
	AND NOT EXISTS (SELECT 1 FROM need_groups n
	WHERE n.project_id = p.project_id);

	INSERT INTO account_groups
	(group_id,
	owner_group_id,
	description,
	name) SELECT
	nextval('account_group_id'),
	(SELECT admin_group_id FROM system_config),
	p.name \|\| ' maintainers',
	substring(p.name from '^./([^/])$') \|\| '_' \|\| p.project_id \|\| '-owners'
	FROM projects p, need_groups g
	WHERE p.project_id = g.project_id;

	UPDATE account_groups
	SET owner_group_id = group_id
	WHERE name IN (SELECT
	substring(p.name from '^./([^/])$') \|\| '_' \|\| p.project_id \|\| '-owners'
	FROM projects p, need_groups g
	WHERE p.project_id = g.project_id);

	UPDATE projects
	SET owner_group_id = (SELECT group_id
	FROM account_groups
	WHERE name = substring(projects.name
	from '^./([^/])$') \|\| '_' \|\| projects.project_id \|\| '-owners')
	WHERE project_id IN (SELECT project_id FROM need_groups);

	INSERT INTO account_group_members
	(account_id,
	group_id) SELECT DISTINCT
	q.account_id,
	p.owner_group_id
	FROM projects p,
	need_groups n,
	gerrit1.account_groups og,
	gerrit1.project_owner_groups o,
	account_groups g,
	account_group_members q
	WHERE
	n.project_id = p.project_id
	AND o.project_id = p.project_id
	AND og.gae_key = o.group_key
	AND g.name = og.name
	AND q.group_id = g.group_id
	UNION
	SELECT
	a.account_id,
	p.owner_group_id
	FROM accounts a,
	projects p,
	need_groups n,
	gerrit1.project_owner_users o
	WHERE
	n.project_id = p.project_id
	AND o.project_id = p.project_id
	AND a.preferred_email = o.email;

	UPDATE projects
	SET owner_group_id = (
	SELECT g.group_id
	FROM account_groups g,
	gerrit1.project_owner_groups o,
	gerrit1.account_groups og
	WHERE projects.project_id = o.project_id
	AND og.gae_key = o.group_key
	AND g.name = og.name)
	WHERE project_id NOT IN (SELECT project_id FROM need_groups)
	AND EXISTS (
	SELECT g.group_id
	FROM account_groups g,
	gerrit1.project_owner_groups o,
	gerrit1.account_groups og
	WHERE projects.project_id = o.project_id
	AND og.gae_key = o.group_key
	AND g.name = og.name);

	DROP TABLE need_groups;


	DELETE FROM changes;
	INSERT INTO changes
	(created_on,
	last_updated_on,
	owner_account_id,
	dest_project_name,
	dest_branch_name,
	status,
	open,
	nbr_patch_sets,
	current_patch_set_id,
	subject,
	change_id) SELECT
	c.created,
	c.modified,
	a.account_id,
	p.name,
	b.name,
	CASE WHEN c.merged = 'Y' THEN 'M'
	WHEN c.closed = 'Y' THEN 'A'
	ELSE 'n'
	END,
	CASE WHEN c.merged = 'Y' THEN 'N'
	WHEN c.closed = 'Y' THEN 'N'
	ELSE 'Y'
	END,
	c.n_patchsets,
	c.n_patchsets,
	c.subject,
	c.change_id
	FROM gerrit1.changes c,
	accounts a,
	gerrit1.projects p,
	gerrit1.branches b
	WHERE
	a.preferred_email = c.owner
	AND p.gae_key = c.dest_project_key
	AND b.gae_key = c.dest_branch_key
	;

	UPDATE gerrit1.messages
	SET sender = substring(sender from '<(.*)>')
	WHERE sender LIKE '%<%>';

	UPDATE gerrit1.messages
	SET sender = NULL
	WHERE sender = 'code-review@android.com';

	UPDATE gerrit1.messages
	SET body = 'Change has been successfully merged into the git repository.'
	WHERE body LIKE '
	Hi.

	Your change has been successfully merged into the git repository.

	-Your friendly git merger%'
	AND sender IS NULL;

	UPDATE gerrit1.messages
	SET body = 'Change could not be merged because of a missing dependency. As soon as its dependencies are submitted, the change will be submitted.'
	WHERE (body LIKE '
	Hi.

	Your change could not be merged because of a missing dependency.%

	-Your friendly git merger%'
	OR body LIKE '
	Your change could not be merged because of a missing dependency.

	As soon as all dependencies are submitted, your change will be
	resubmitted automatically.%'
	) AND sender IS NULL;

	UPDATE gerrit1.messages
	SET body = 'Change cannot be merged because of a path conflict.'
	WHERE body LIKE '
	Hi.

	Your change has not been successfully merged into the git repository
	because of a path conflict.

	-Your friendly git merger%'
	AND sender is NULL;

	UPDATE gerrit1.messages
	SET body = TRIM(both '
	' FROM body);

	DELETE FROM change_messages;
	INSERT INTO change_messages
	(change_id,
	uuid,
	author_id,
	written_on,
	message) SELECT
	c.change_id,
	substr(m.gae_key, length(m.change_key) + length('wLEgdNZXNzYWdlG')),
	a.account_id,
	m.date_sent,
	m.body
	FROM gerrit1.messages m
	LEFT OUTER JOIN accounts a ON a.preferred_email = m.sender,
	gerrit1.changes c
	WHERE
	c.gae_key = m.change_key;

	DELETE FROM patch_sets;
	INSERT INTO patch_sets
	(revision,
	change_id,
	patch_set_id,
	created_on,
	uploader_account_id) SELECT
	r.revision_id,
	c.change_id,
	p.patchset_id,
	p.created,
	a.account_id
	FROM gerrit1.patch_sets p
	JOIN accounts a ON a.preferred_email = p.owner
	JOIN gerrit1.changes c ON p.change_key = c.gae_key
	LEFT OUTER JOIN gerrit1.revisions r ON r.gae_key = p.revision_key;

	DELETE FROM patch_set_info;
	INSERT INTO patch_set_info
	(subject,
	message,
	author_name,
	author_email,
	author_when,
	author_tz,
	committer_name,
	committer_email,
	committer_when,
	committer_tz,
	change_id,
	patch_set_id) SELECT DISTINCT
	(SELECT c.subject FROM changes c
	WHERE c.change_id = p.change_id
	AND c.current_patch_set_id = p.patch_set_id),
	r.message,
	r.author_name,
	r.author_email,
	r.author_when,
	r.author_tz,
	r.committer_name,
	r.committer_email,
	r.committer_when,
	r.committer_tz,
	p.change_id,
	p.patch_set_id
	FROM gerrit1.revisions r, patch_sets p
	WHERE r.revision_id = p.revision;

	DELETE FROM patch_set_ancestors;
	INSERT INTO patch_set_ancestors
	(ancestor_revision,
	change_id,
	patch_set_id,
	position
	) SELECT DISTINCT
	p.parent_id,
	ps.change_id,
	ps.patch_set_id,
	p.position
	FROM gerrit1.revision_ancestors p,
	patch_sets ps
	WHERE ps.revision = p.child_id;

	DELETE FROM patches;
	INSERT INTO patches
	(change_type,
	patch_type,
	nbr_comments,
	change_id,
	patch_set_id,
	file_name) SELECT
	p.status,
	CASE WHEN p.multi_way_diff = 'Y' THEN 'N'
	ELSE 'U'
	END,
	p.n_comments,
	c.change_id,
	ps.patchset_id,
	p.filename
	FROM gerrit1.patches p,
	gerrit1.patch_sets ps,
	gerrit1.changes c
	WHERE p.patchset_key = ps.gae_key
	AND ps.change_key = c.gae_key;

	DELETE FROM patch_comments;
	INSERT INTO patch_comments
	(line_nbr,
	author_id,
	written_on,
	status,
	side,
	message,
	change_id,
	patch_set_id,
	file_name,
	uuid) SELECT
	c.lineno,
	a.account_id,
	c.written,
	CASE WHEN c.draft = 'Y' THEN 'd'
	ELSE 'P'
	END,
	CASE WHEN c.is_left = 'Y' THEN 0
	ELSE 1
	END,
	c.body,
	o_c.change_id,
	o_ps.patchset_id,
	o_p.filename,
	c.message_id
	FROM gerrit1.comments c,
	accounts a,
	gerrit1.patches o_p,
	gerrit1.patch_sets o_ps,
	gerrit1.changes o_c
	WHERE o_p.patchset_key = o_ps.gae_key
	AND o_ps.change_key = o_c.gae_key
	AND o_p.gae_key = c.patch_key
	AND a.preferred_email = c.author;

	DELETE FROM change_approvals;
	INSERT INTO change_approvals
	(value,
	change_id,
	account_id,
	category_id) SELECT
	1,
	c.change_id,
	a.account_id,
	'VRIF'
	FROM gerrit1.review_status s,
	gerrit1.changes c,
	accounts a
	WHERE
	s.verified = 'Y'
	AND s.change_key = c.gae_key
	AND a.preferred_email = s.email;
	INSERT INTO change_approvals
	(value,
	change_id,
	account_id,
	category_id) SELECT
	CASE WHEN s.lgtm = 'lgtm' THEN 2
	WHEN s.lgtm = 'yes' THEN 1
	WHEN s.lgtm = 'abstain' THEN 0
	WHEN s.lgtm = 'no' THEN -1
	WHEN s.lgtm = 'reject' THEN -2
	ELSE NULL
	END,
	c.change_id,
	a.account_id,
	'CRVW'
	FROM gerrit1.review_status s,
	gerrit1.changes c,
	accounts a
	WHERE
	s.lgtm IS NOT NULL
	AND s.change_key = c.gae_key
	AND a.preferred_email = s.email;

	DELETE FROM starred_changes;
	INSERT INTO starred_changes
	(account_id,
	change_id) SELECT
	a.account_id,
	c.change_id
	FROM gerrit1.account_stars s,
	accounts a,
	changes c
	WHERE a.preferred_email = s.email
	AND c.change_id = s.change_id;

	UPDATE account_groups
	SET name = 'Administrators'
	WHERE name = 'admin';


	-- Fix change.nbr_patch_sets
	--
	UPDATE changes
	SET nbr_patch_sets = (SELECT MAX(p.patch_set_id)
	FROM patch_sets p
	WHERE p.change_id = changes.change_id);

	-- Fix change.last_updated_on
	--
	CREATE TEMPORARY TABLE temp_dates (
	change_id INT NOT NULL,
	dt TIMESTAMP NOT NULL);

	INSERT INTO temp_dates
	SELECT change_id,written_on
	FROM patch_comments
	WHERE status = 'P';

	INSERT INTO temp_dates
	SELECT change_id,written_on FROM change_messages;

	INSERT INTO temp_dates
	SELECT change_id,merge_submitted
	FROM gerrit1.changes
	WHERE merge_submitted IS NOT NULL
	AND merged = 'Y';

	UPDATE changes
	SET last_updated_on = (SELECT MAX(m.dt)
	FROM temp_dates m
	WHERE m.change_id = changes.change_id)
	WHERE EXISTS (SELECT 1 FROM temp_dates m
	WHERE m.change_id = changes.change_id);
	DROP TABLE temp_dates;


	-- Fix patches.nbr_comments
	--
	UPDATE patches
	SET nbr_comments = (SELECT COUNT(*)
	FROM patch_comments c
	WHERE c.status = 'P'
	AND c.change_id = patches.change_id
	AND c.patch_set_id = patches.patch_set_id
	AND c.file_name = patches.file_name);

	SELECT
	(SELECT COUNT(*) FROM gerrit1.accounts) as accounts_g1,
	(SELECT COUNT(*) FROM accounts) as accounts_g1
	WHERE
	(SELECT COUNT(*) FROM gerrit1.accounts)
	!=(SELECT COUNT(*) FROM accounts);

	SELECT
	(SELECT COUNT(*) FROM gerrit1.changes) as changes_g1,
	(SELECT COUNT(*) FROM changes) as changes_g2
	WHERE
	(SELECT COUNT(*) FROM gerrit1.changes)
	!=(SELECT COUNT(*) FROM changes);

	SELECT
	(SELECT COUNT(*) FROM gerrit1.messages) as messages_g1,
	(SELECT COUNT(*) FROM change_messages) as messages_g2
	WHERE
	(SELECT COUNT(*) FROM gerrit1.messages)
	!=(SELECT COUNT(*) FROM change_messages);

	SELECT
	(SELECT COUNT(*) FROM gerrit1.patch_sets) as patch_sets_g1,
	(SELECT COUNT(*) FROM patch_sets) as patch_sets_g2
	WHERE
	(SELECT COUNT(*) FROM gerrit1.patch_sets)
	!=(SELECT COUNT(*) FROM patch_sets);

	SELECT
	(SELECT COUNT(*) FROM gerrit1.patches g, gerrit1.patch_sets p
	WHERE g.patchset_key = p.gae_key) as patches_g1,
	(SELECT COUNT(*) FROM patches) as patches_g2
	WHERE
	(SELECT COUNT(*) FROM gerrit1.patches g, gerrit1.patch_sets p
	WHERE g.patchset_key = p.gae_key)
	!=(SELECT COUNT(*) FROM patches);

	SELECT
	(SELECT COUNT(*) FROM gerrit1.comments) as comments_g1,
	(SELECT COUNT(*) FROM patch_comments) as comments_g2
	WHERE
	(SELECT COUNT(*) FROM gerrit1.comments)
	!=(SELECT COUNT(*) FROM patch_comments);

	-- Reset sequences
	--
	-- account_group_id (above)
	-- account_id (above)
	-- branch_id (above)
	SELECT setval('change_id',(SELECT MAX(change_id) FROM changes));
	-- contributor_agreement_id (above)
	SELECT setval('project_id',(SELECT MAX(project_id) FROM projects));

	-- Grant access to read tables needed for import
	--
	GRANT SELECT ON gerrit1.project_code_reviews TO gerrit2;
	GRANT SELECT ON gerrit1.approval_right_groups TO gerrit2;
	GRANT SELECT ON gerrit1.approval_right_users TO gerrit2;
	GRANT SELECT ON gerrit1.approval_rights TO gerrit2;
	GRANT SELECT ON gerrit1.account_groups TO gerrit2;