class <%= migration_class_name %> < ActiveRecord::Migration<%= migration_version %>
def change # enable ltree extension begin execute "create extension ltree" rescue p "LTREE was already enabled" end # create the main table as set up within ACTIVE_TREE_OPTIONS[:table_name] execute <<-SQL create table #{ ACTIVE_TREE_OPTIONS[:table_name] } ( id serial, owner_id integer, owner_type character varying, status integer, data_external_id character varying, data_provider character varying, type character varying, name text, parent_entity_id integer, parent_entity_type character varying, path ltree, path_slug text, metadata_inline jsonb, created_at timestamp(6) without time zone not null, updated_at timestamp(6) without time zone not null, primary key (id, owner_id) ) partition by list(owner_id)
SQL
# add indexes add_index ACTIVE_TREE_OPTIONS[:table_name], :id add_index ACTIVE_TREE_OPTIONS[:table_name], :owner_id add_index ACTIVE_TREE_OPTIONS[:table_name], :type add_index ACTIVE_TREE_OPTIONS[:table_name], :parent_entity_id # next two indexes unfortunately can't be unique since a cycle can appear several times under an owner add_index ACTIVE_TREE_OPTIONS[:table_name], :path, using: :gist add_index ACTIVE_TREE_OPTIONS[:table_name], [:data_provider, :data_external_id] # create an "others" partition for when the owner is undefined/unknown? just in case / may help in some edge cases execute "CREATE TABLE #{ACTIVE_TREE_OPTIONS[:table_name]}_others PARTITION OF #{ACTIVE_TREE_OPTIONS[:table_name]} DEFAULT" execute "create index index_others_by_id on #{ACTIVE_TREE_OPTIONS[:table_name]}_others (id)" execute "create index index_others_by_owner_id on #{ACTIVE_TREE_OPTIONS[:table_name]}_others (owner_id)" execute "create index index_others_by_type on #{ACTIVE_TREE_OPTIONS[:table_name]}_others (type)" execute "create index index_others_by_parent_entity_id on #{ACTIVE_TREE_OPTIONS[:table_name]}_others (parent_entity_id)" execute "create index index_others_by_path on #{ACTIVE_TREE_OPTIONS[:table_name]}_others using gist (path)" execute "create index index_others_by_data_provider_and_data_external_id on #{ACTIVE_TREE_OPTIONS[:table_name]}_others (data_provider, data_external_id)" # when postgrest is enabled... if ACTIVE_TREE_OPTIONS[:create_postgrest_roles] # create postgrest anon user with no privs # postgrest may pass an user's role using JWT execute "drop role if exists postgrest_anon" execute "create role postgrest_anon nologin" execute "grant postgrest_anon to #{ ACTIVE_TREE_OPTIONS[:database_user] }" end # metadata table create_table "#{ ACTIVE_TREE_OPTIONS[:table_name] }_metadata" do |t| t.string :model_type t.integer :model_id t.string :key t.text :value t.timestamps end add_index "#{ ACTIVE_TREE_OPTIONS[:table_name] }_metadata", [ :model_type, :model_id ] add_index "#{ ACTIVE_TREE_OPTIONS[:table_name] }_metadata", [ :key ] end
end