<gh_stars>10-100
def upgrade(migrate_engine):

    update_schema = '''
BEGIN;

ALTER TABLE resource
    ADD COLUMN url_type TEXT;

ALTER TABLE resource_revision
    ADD COLUMN url_type TEXT;

ALTER TABLE package
    ADD COLUMN metadata_modified timestamp without time zone,
    ADD COLUMN creator_user_id TEXT;

ALTER TABLE package_revision
    ADD COLUMN metadata_modified timestamp without time zone,
    ADD COLUMN creator_user_id TEXT;


-- package
update package
set metadata_modified = greatest(max_revision, metadata_modified)
from
(
select id package_id, max(revision_timestamp) max_revision
from package_revision group by id
) max_rev
where max_rev.package_id = package.id;

-- package tag
update package
set metadata_modified = greatest(max_revision, metadata_modified)
from (select package_id, max(revision_timestamp) max_revision
from package_tag_revision group by package_id) max_rev
where max_rev.package_id = package.id;

-- package extra
update package
set metadata_modified = greatest(max_revision, metadata_modified)
from (select package_id, max(revision_timestamp) max_revision
from package_extra_revision group by package_id) max_rev
where max_rev.package_id = package.id;

--resource

update package
set metadata_modified = greatest(max_revision, metadata_modified)
from (select package_id, max(revision_timestamp) max_revision
      from resource_revision
      join resource_group
      on resource_revision.resource_group_id = resource_group.id
      group by package_id) max_rev
where max_rev.package_id = package.id
;

-- add as many creators as we can find
update package set creator_user_id = user_id from
(select
    package_revision.id as package_id,
    "user".id as user_id, revision_timestamp,
    row_number() over
    (partition by package_revision.id order by revision_timestamp) num
from package_revision
    join revision on package_revision.revision_id = revision.id join "user"
    on (revision.author = "user".name
        or revision.author = "user".openid)) first_rev
    where package_id = id and num = 1;

COMMIT;

'''
    migrate_engine.execute(update_schema)

def upgrade(migrate_engine):

    update_schema = '''
BEGIN;

ALTER TABLE resource
    ADD COLUMN url_type TEXT;

ALTER TABLE resource_revision
    ADD COLUMN url_type TEXT;

ALTER TABLE package
    ADD COLUMN metadata_modified timestamp without time zone,
    ADD COLUMN creator_user_id TEXT;

ALTER TABLE package_revision
    ADD COLUMN metadata_modified timestamp without time zone,
    ADD COLUMN creator_user_id TEXT;


-- package
update package
set metadata_modified = greatest(max_revision, metadata_modified)
from
(
select id package_id, max(revision_timestamp) max_revision
from package_revision group by id
) max_rev
where max_rev.package_id = package.id;

-- package tag
update package
set metadata_modified = greatest(max_revision, metadata_modified)
from (select package_id, max(revision_timestamp) max_revision
from package_tag_revision group by package_id) max_rev
where max_rev.package_id = package.id;

-- package extra
update package
set metadata_modified = greatest(max_revision, metadata_modified)
from (select package_id, max(revision_timestamp) max_revision
from package_extra_revision group by package_id) max_rev
where max_rev.package_id = package.id;

--resource

update package
set metadata_modified = greatest(max_revision, metadata_modified)
from (select package_id, max(revision_timestamp) max_revision
      from resource_revision
      join resource_group
      on resource_revision.resource_group_id = resource_group.id
      group by package_id) max_rev
where max_rev.package_id = package.id
;

-- add as many creators as we can find
update package set creator_user_id = user_id from
(select
    package_revision.id as package_id,
    "user".id as user_id, revision_timestamp,
    row_number() over
    (partition by package_revision.id order by revision_timestamp) num
from package_revision
    join revision on package_revision.revision_id = revision.id join "user"
    on (revision.author = "user".name
        or revision.author = "user".openid)) first_rev
    where package_id = id and num = 1;

COMMIT;

'''
    migrate_engine.execute(update_schema)
BEGIN;  ALTER TABLE resource     ADD COLUMN url_type TEXT;  ALTER TABLE resource_revision     ADD COLUMN url_type TEXT;  ALTER TABLE package     ADD COLUMN metadata_modified timestamp without time zone,     ADD COLUMN creator_user_id TEXT;  ALTER TABLE package_revision     ADD COLUMN metadata_modified timestamp without time zone,     ADD COLUMN creator_user_id TEXT;   -- package update package set metadata_modified = greatest(max_revision, metadata_modified) from ( select id package_id, max(revision_timestamp) max_revision from package_revision group by id ) max_rev where max_rev.package_id = package.id;  -- package tag update package set metadata_modified = greatest(max_revision, metadata_modified) from (select package_id, max(revision_timestamp) max_revision from package_tag_revision group by package_id) max_rev where max_rev.package_id = package.id;  -- package extra update package set metadata_modified = greatest(max_revision, metadata_modified) from (select package_id, max(revision_timestamp) max_revision from package_extra_revision group by package_id) max_rev where max_rev.package_id = package.id;  --resource  update package set metadata_modified = greatest(max_revision, metadata_modified) from (select package_id, max(revision_timestamp) max_revision       from resource_revision       join resource_group       on resource_revision.resource_group_id = resource_group.id       group by package_id) max_rev where max_rev.package_id = package.id ;  -- add as many creators as we can find update package set creator_user_id = user_id from (select     package_revision.id as package_id,     "user".id as user_id, revision_timestamp,     row_number() over     (partition by package_revision.id order by revision_timestamp) num from package_revision     join revision on package_revision.revision_id = revision.id join "user"     on (revision.author = "user".name         or revision.author = "user".openid)) first_rev     where package_id = id and num = 1;  COMMIT;