14819: Merge branch 'master' into 14819-arvados-jobs-on-stretch

[arvados.git] / services / api / db / migrate / 20170906224040_materialized_permission_view.rb

diff --git a/services/api/db/migrate/20170906224040_materialized_permission_view.rb b/services/api/db/migrate/20170906224040_materialized_permission_view.rb
index 8e5df35f0fab5196c78c7cd94feccd4f42bb0d36..b864f75c12a56329155c52114c3e98fd710f6619 100644 (file)
--- a/services/api/db/migrate/20170906224040_materialized_permission_view.rb
+++ b/services/api/db/migrate/20170906224040_materialized_permission_view.rb
@@ -4,7 +4,7 @@
 
 class MaterializedPermissionView < ActiveRecord::Migration
 
-  @@idxtables = [:collections, :container_requests, :groups, :jobs, :links, :pipeline_instances, :pipeline_templates, :repositories, :users, :virtual_machines, :workflows]
+  @@idxtables = [:collections, :container_requests, :groups, :jobs, :links, :pipeline_instances, :pipeline_templates, :repositories, :users, :virtual_machines, :workflows, :logs]
 
   def up
 
@@ -19,7 +19,7 @@ class MaterializedPermissionView < ActiveRecord::Migration
     ActiveRecord::Base.connection.execute(
 "-- constructing perm_edges
 --   1. get the list of all permission links,
---   2. any can_manage link or permission link to a group means permission should "follow through"
+--   2. any can_manage link or permission link to a group means permission should 'follow through'
 --      (as a special case, can_manage links to a user grant access to everything owned by the user,
 --       unlike can_read or can_write which only grant access to the user record)
 --   3. add all owner->owned relationships between groups as can_manage edges
@@ -27,12 +27,12 @@ class MaterializedPermissionView < ActiveRecord::Migration
 -- constructing permissions
 --   1. base case: start with set of all users as the working set
 --   2. recursive case:
---      join with edges where the tail is in the working set and "follow" is true
+--      join with edges where the tail is in the working set and 'follow' is true
 --      produce a new working set with the head (target) of each edge
 --      set permission to the least permission encountered on the path
 --      propagate trashed flag down
 
-CREATE MATERIALIZED VIEW permission_view AS
+CREATE MATERIALIZED VIEW materialized_permission_view AS
 WITH RECURSIVE
 perm_value (name, val) AS (
      VALUES
@@ -56,24 +56,22 @@ perm_edges (tail_uuid, head_uuid, val, follow, trashed) AS (
               CASE WHEN trash_at IS NOT NULL and trash_at < clock_timestamp() THEN 1 ELSE 0 END
               FROM groups
        ),
-perm (val, follow, user_uuid, target_uuid, trashed, startnode) AS (
+perm (val, follow, user_uuid, target_uuid, trashed) AS (
      SELECT 3::smallint             AS val,
-            false                   AS follow,
+            true                    AS follow,
             users.uuid::varchar(32) AS user_uuid,
             users.uuid::varchar(32) AS target_uuid,
-            0::smallint             AS trashed,
-            true                    AS startnode
+            0::smallint             AS trashed
             FROM users
      UNION
      SELECT LEAST(perm.val, edges.val)::smallint  AS val,
             edges.follow                          AS follow,
             perm.user_uuid::varchar(32)           AS user_uuid,
             edges.head_uuid::varchar(32)          AS target_uuid,
-            GREATEST(perm.trashed, edges.trashed)::smallint AS trashed,
-            false                                 AS startnode
+            GREATEST(perm.trashed, edges.trashed)::smallint AS trashed
             FROM perm
             INNER JOIN perm_edges edges
-            ON (perm.startnode or perm.follow) AND edges.tail_uuid = perm.target_uuid
+            ON perm.follow AND edges.tail_uuid = perm.target_uuid
 )
 SELECT user_uuid,
        target_uuid,
@@ -83,8 +81,8 @@ SELECT user_uuid,
        FROM perm
        GROUP BY user_uuid, target_uuid, target_owner_uuid;
 ")
-    add_index :permission_view, [:trashed, :target_uuid], name: 'permission_target_trashed'
-    add_index :permission_view, [:user_uuid, :trashed, :perm_level], name: 'permission_target_user_trashed_level'
+    add_index :materialized_permission_view, [:trashed, :target_uuid], name: 'permission_target_trashed'
+    add_index :materialized_permission_view, [:user_uuid, :trashed, :perm_level], name: 'permission_target_user_trashed_level'
 
     # Indexes on the other tables are essential to for the query planner to
     # construct an efficient join with permission_view.
@@ -102,14 +100,18 @@ SELECT user_uuid,
     @@idxtables.each do |table|
       ActiveRecord::Base.connection.execute("CREATE INDEX index_#{table.to_s}_on_modified_at_uuid ON #{table.to_s} USING btree (modified_at desc, uuid asc)")
     end
+
+    create_table :permission_refresh_lock
+    ActiveRecord::Base.connection.execute("REFRESH MATERIALIZED VIEW materialized_permission_view")
   end
 
   def down
-    remove_index :permission_view, name: 'permission_target_trashed'
-    remove_index :permission_view, name: 'permission_target_user_trashed_level'
+    drop_table :permission_refresh_lock
+    remove_index :materialized_permission_view, name: 'permission_target_trashed'
+    remove_index :materialized_permission_view, name: 'permission_target_user_trashed_level'
     @@idxtables.each do |table|
-      ActiveRecord::Base.connection.execute("DROP INDEX index_#{table.to_s}_on_modified_at_uuid")
+      ActiveRecord::Base.connection.execute("DROP INDEX IF EXISTS index_#{table.to_s}_on_modified_at_uuid")
     end
-    ActiveRecord::Base.connection.execute("DROP MATERIALIZED VIEW IF EXISTS permission_view")
+    ActiveRecord::Base.connection.execute("DROP MATERIALIZED VIEW IF EXISTS materialized_permission_view")
   end
 end