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

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