services/api/db/migrate/20140811184643_collection_use_regular_uuids.rb

   1 # Copyright (C) The Arvados Authors. All rights reserved.
   2 #
   3 # SPDX-License-Identifier: AGPL-3.0
   4
   5 class CollectionUseRegularUuids < ActiveRecord::Migration
   6   def up
   7     add_column :collections, :name, :string
   8     add_column :collections, :description, :string
   9     add_column :collections, :properties, :text
  10     add_column :collections, :expires_at, :date
  11     remove_column :collections, :locator
  12
  13     say_with_time "Step 1. Move manifest hashes into portable_data_hash field" do
  14       ActiveRecord::Base.connection.execute("update collections set portable_data_hash=uuid, uuid=null")
  15     end
  16
  17     say_with_time "Step 2. Create new collection objects from the name links in the table." do
  18       from_clause = %{
  19 from links inner join collections on head_uuid=collections.portable_data_hash
  20 where link_class='name' and collections.uuid is null
  21 }
  22       links = ActiveRecord::Base.connection.select_all %{
  23 select links.uuid, head_uuid, tail_uuid, links.name,
  24 manifest_text, links.created_at, links.modified_at, links.modified_by_client_uuid, links.modified_by_user_uuid
  25 #{from_clause}
  26 }
  27       links.each do |d|
  28         ActiveRecord::Base.connection.execute %{
  29 insert into collections (uuid, portable_data_hash, owner_uuid, name, manifest_text, created_at, modified_at, modified_by_client_uuid, modified_by_user_uuid, updated_at)
  30 values (#{ActiveRecord::Base.connection.quote Collection.generate_uuid},
  31 #{ActiveRecord::Base.connection.quote d['head_uuid']},
  32 #{ActiveRecord::Base.connection.quote d['tail_uuid']},
  33 #{ActiveRecord::Base.connection.quote d['name']},
  34 #{ActiveRecord::Base.connection.quote d['manifest_text']},
  35 #{ActiveRecord::Base.connection.quote d['created_at']},
  36 #{ActiveRecord::Base.connection.quote d['modified_at']},
  37 #{ActiveRecord::Base.connection.quote d['modified_by_client_uuid']},
  38 #{ActiveRecord::Base.connection.quote d['modified_by_user_uuid']},
  39 #{ActiveRecord::Base.connection.quote d['modified_at']})
  40 }
  41       end
  42       ActiveRecord::Base.connection.execute "delete from links where links.uuid in (select links.uuid #{from_clause})"
  43     end
  44
  45     say_with_time "Step 3. Create new collection objects from the can_read links in the table." do
  46       from_clause = %{
  47 from links inner join collections on head_uuid=collections.portable_data_hash
  48 where link_class='permission' and links.name='can_read' and collections.uuid is null
  49 }
  50       links = ActiveRecord::Base.connection.select_all %{
  51 select links.uuid, head_uuid, tail_uuid, manifest_text, links.created_at, links.modified_at
  52 #{from_clause}
  53 }
  54       links.each do |d|
  55         ActiveRecord::Base.connection.execute %{
  56 insert into collections (uuid, portable_data_hash, owner_uuid, manifest_text, created_at, modified_at, modified_by_client_uuid, modified_by_user_uuid, updated_at)
  57 values (#{ActiveRecord::Base.connection.quote Collection.generate_uuid},
  58 #{ActiveRecord::Base.connection.quote d['head_uuid']},
  59 #{ActiveRecord::Base.connection.quote d['tail_uuid']},
  60 #{ActiveRecord::Base.connection.quote d['manifest_text']},
  61 #{ActiveRecord::Base.connection.quote d['created_at']},
  62 #{ActiveRecord::Base.connection.quote d['modified_at']},
  63 #{ActiveRecord::Base.connection.quote d['modified_by_client_uuid']},
  64 #{ActiveRecord::Base.connection.quote d['modified_by_user_uuid']},
  65 #{ActiveRecord::Base.connection.quote d['modified_at']})
  66 }
  67       end
  68       ActiveRecord::Base.connection.execute "delete from links where links.uuid in (select links.uuid #{from_clause})"
  69     end
  70
  71     say_with_time "Step 4. Migrate remaining orphan collection objects" do
  72       links = ActiveRecord::Base.connection.select_all %{
  73 select portable_data_hash, owner_uuid, manifest_text, created_at, modified_at
  74 from collections
  75 where uuid is null and portable_data_hash not in (select portable_data_hash from collections where uuid is not null)
  76 }
  77       links.each do |d|
  78         ActiveRecord::Base.connection.execute %{
  79 insert into collections (uuid, portable_data_hash, owner_uuid, manifest_text, created_at, modified_at, modified_by_client_uuid, modified_by_user_uuid, updated_at)
  80 values (#{ActiveRecord::Base.connection.quote Collection.generate_uuid},
  81 #{ActiveRecord::Base.connection.quote d['portable_data_hash']},
  82 #{ActiveRecord::Base.connection.quote d['owner_uuid']},
  83 #{ActiveRecord::Base.connection.quote d['manifest_text']},
  84 #{ActiveRecord::Base.connection.quote d['created_at']},
  85 #{ActiveRecord::Base.connection.quote d['modified_at']},
  86 #{ActiveRecord::Base.connection.quote d['modified_by_client_uuid']},
  87 #{ActiveRecord::Base.connection.quote d['modified_by_user_uuid']},
  88 #{ActiveRecord::Base.connection.quote d['modified_at']})
  89 }
  90       end
  91     end
  92
  93     say_with_time "Step 5. Delete old collection objects." do
  94       ActiveRecord::Base.connection.execute("delete from collections where uuid is null")
  95     end
  96
  97     say_with_time "Step 6. Delete permission links where tail_uuid is a collection (invalid records)" do
  98       ActiveRecord::Base.connection.execute %{
  99 delete from links where links.uuid in (select links.uuid
 100 from links
 101 where tail_uuid like '________________________________+%' and link_class='permission' )
 102 }
 103     end
 104
 105     say_with_time "Step 7. Migrate collection -> collection provenance links to jobs" do
 106       from_clause = %{
 107 from links
 108 where head_uuid like '________________________________+%' and tail_uuid like '________________________________+%' and links.link_class = 'provenance'
 109 }
 110       links = ActiveRecord::Base.connection.select_all %{
 111 select links.uuid, head_uuid, tail_uuid, links.created_at, links.modified_at, links.modified_by_client_uuid, links.modified_by_user_uuid, links.owner_uuid
 112 #{from_clause}
 113 }
 114       links.each do |d|
 115         newuuid = Job.generate_uuid
 116         ActiveRecord::Base.connection.execute %{
 117 insert into jobs (uuid, script_parameters, output, running, success, created_at, modified_at, modified_by_client_uuid, modified_by_user_uuid, owner_uuid, updated_at)
 118 values (#{ActiveRecord::Base.connection.quote newuuid},
 119 #{ActiveRecord::Base.connection.quote "---\ninput: "+d['tail_uuid']},
 120 #{ActiveRecord::Base.connection.quote d['head_uuid']},
 121 #{ActiveRecord::Base.connection.quote false},
 122 #{ActiveRecord::Base.connection.quote true},
 123 #{ActiveRecord::Base.connection.quote d['created_at']},
 124 #{ActiveRecord::Base.connection.quote d['modified_at']},
 125 #{ActiveRecord::Base.connection.quote d['modified_by_client_uuid']},
 126 #{ActiveRecord::Base.connection.quote d['modified_by_user_uuid']},
 127 #{ActiveRecord::Base.connection.quote d['owner_uuid']},
 128 #{ActiveRecord::Base.connection.quote d['modified_at']})
 129 }
 130       end
 131       ActiveRecord::Base.connection.execute "delete from links where links.uuid in (select links.uuid #{from_clause})"
 132     end
 133
 134     say_with_time "Step 8. Migrate remaining links with head_uuid pointing to collections" do
 135       from_clause = %{
 136 from links inner join collections on links.head_uuid=portable_data_hash
 137 where collections.uuid is not null
 138 }
 139       links = ActiveRecord::Base.connection.select_all %{
 140 select links.uuid, collections.uuid as collectionuuid, tail_uuid, link_class, links.properties,
 141 links.name, links.created_at, links.modified_at, links.modified_by_client_uuid, links.modified_by_user_uuid, links.owner_uuid
 142 #{from_clause}
 143 }
 144       links.each do |d|
 145         ActiveRecord::Base.connection.execute %{
 146 insert into links (uuid, head_uuid, tail_uuid, link_class, name, properties, created_at, modified_at, modified_by_client_uuid, modified_by_user_uuid, owner_uuid, updated_at)
 147 values (#{ActiveRecord::Base.connection.quote Link.generate_uuid},
 148 #{ActiveRecord::Base.connection.quote d['collectionuuid']},
 149 #{ActiveRecord::Base.connection.quote d['tail_uuid']},
 150 #{ActiveRecord::Base.connection.quote d['link_class']},
 151 #{ActiveRecord::Base.connection.quote d['name']},
 152 #{ActiveRecord::Base.connection.quote d['properties']},
 153 #{ActiveRecord::Base.connection.quote d['created_at']},
 154 #{ActiveRecord::Base.connection.quote d['modified_at']},
 155 #{ActiveRecord::Base.connection.quote d['modified_by_client_uuid']},
 156 #{ActiveRecord::Base.connection.quote d['modified_by_user_uuid']},
 157 #{ActiveRecord::Base.connection.quote d['owner_uuid']},
 158 #{ActiveRecord::Base.connection.quote d['modified_at']})
 159 }
 160       end
 161       ActiveRecord::Base.connection.execute "delete from links where links.uuid in (select links.uuid #{from_clause})"
 162     end
 163
 164     say_with_time "Step 9. Delete any remaining name links" do
 165       ActiveRecord::Base.connection.execute("delete from links where link_class='name'")
 166     end
 167
 168     say_with_time "Step 10. Validate links table" do
 169       links = ActiveRecord::Base.connection.select_all %{
 170 select links.uuid, head_uuid, tail_uuid, link_class, name
 171 from links
 172 where head_uuid like '________________________________+%' or tail_uuid like '________________________________+%'
 173 }
 174       links.each do |d|
 175         raise "Bad row #{d}"
 176       end
 177     end
 178
 179   end
 180
 181   def down
 182     raise ActiveRecord::IrreversibleMigration, "Can't downmigrate changes to collections and links without potentially losing data."
 183   end
 184 end