common/db/migrations/001_create_base_schema.rb
require_relative 'utils'
Sequel.migration do
up do
create_table(:session) do
primary_key :id
String :session_id, :unique => true, :null => false
DateTime :system_mtime, :null => false, :index => true
Integer :expirable, :default => 1
TextBlobField :session_data, :null => true
end
create_table(:enumeration) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
String :name, :null => false, :unique => true
Integer :default_value
Integer :editable, :default => 1
apply_mtime_columns
end
create_table(:enumeration_value, :collate => :utf8_bin) do
primary_key :id
Integer :enumeration_id, :null => false, :index => true
String :value, :null => false, :index => true
Integer :readonly, :default => 0
end
alter_table(:enumeration_value) do
add_foreign_key([:enumeration_id], :enumeration, :key => :id)
add_unique_constraint([:enumeration_id, :value], :name => "enumeration_value_uniq")
end
create_table(:auth_db) do
primary_key :id
String :username, :unique => true, :null => false
DateTime :create_time, :null => false
DateTime :system_mtime, :null => false, :index => true
String :pwhash, :null => false
end
create_table(:notification) do
primary_key :id
DateTime :time, :null => false, :index => true
String :code, :null => false
BlobField :params, :null => false
end
create_table(:agent_person) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :notes_json_schema_version, :null => false
MediumBlobField :notes, :null => true
Integer :publish
apply_mtime_columns
end
create_table(:agent_family) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :notes_json_schema_version, :null => false
MediumBlobField :notes, :null => true
Integer :publish
apply_mtime_columns
end
create_table(:agent_corporate_entity) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :notes_json_schema_version, :null => false
MediumBlobField :notes, :null => true
Integer :publish
apply_mtime_columns
end
create_table(:agent_software) do
primary_key :id
String :system_role, :default => "none", :index => true, :null => false
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :notes_json_schema_version, :null => false
MediumBlobField :notes, :null => true
Integer :publish
apply_mtime_columns
end
create_table(:user) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
String :username, :null => false, :unique => true
String :name, :null => false
String :source, :null => true
Integer :agent_record_id, :null => true
String :agent_record_type, :null => true
Integer :is_system_user, :default => 0, :null => false
Integer :is_hidden_user, :default => 0, :null => false
String :email
String :first_name
String :last_name
String :telephone
String :title
String :department
TextField :additional_contact
apply_mtime_columns
end
alter_table(:user) do
add_foreign_key([:agent_record_id], :agent_person, :key => :id)
end
create_table(:repository) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
String :repo_code, :null => false, :unique => true
String :name, :null => false
String :org_code
String :parent_institution_name
String :url
String :image_url
TextField :contact_persons
DynamicEnum :country_id
Integer :agent_representation_id, :null => true
Integer :hidden, :default => 0
apply_mtime_columns
end
alter_table(:repository) do
add_foreign_key([:agent_representation_id], :agent_corporate_entity, :key => :id)
end
create_table(:group) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :repo_id, :null => false
String :group_code, :null => false
String :group_code_norm, :null => false
TextField :description, :null => false
apply_mtime_columns
end
alter_table(:group) do
add_foreign_key([:repo_id], :repository, :key => :id)
add_unique_constraint([:repo_id, :group_code_norm], :name => "group_uniq")
end
create_table(:group_user) do
primary_key :id
Integer :user_id, :null => false
Integer :group_id, :null => false
end
alter_table(:group_user) do
add_foreign_key([:user_id], :user, :key => :id)
add_foreign_key([:group_id], :group, :key => :id)
add_index(:group_id)
add_index(:user_id)
end
create_table(:permission) do
primary_key :id
String :permission_code, :unique => true
TextField :description, :null => false
String :level, :default => "repository"
Integer :system, :default => 0, :null => false
apply_mtime_columns
end
create_table(:group_permission) do
primary_key :id
Integer :permission_id, :null => false
Integer :group_id, :null => false
end
alter_table(:group_permission) do
add_foreign_key([:permission_id], :permission, :key => :id)
add_foreign_key([:group_id], :group, :key => :id)
add_index(:permission_id)
add_index(:group_id)
add_index([:permission_id, :group_id], :unique => true)
end
create_table(:accession) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :repo_id, :null => false
Integer :suppressed, :default => 0, :null => false
String :identifier, :null => false
HalfLongString :title, :null => true
TextField :display_string, :null => true
Integer :publish
TextField :content_description, :null => true
TextField :condition_description, :null => true
TextField :disposition
TextField :inventory
TextField :provenance
TextField :general_note
DynamicEnum :resource_type_id
DynamicEnum :acquisition_type_id
Date :accession_date, :null => true
Integer :restrictions_apply
TextField :retention_rule, :null => true
Integer :access_restrictions
TextField :access_restrictions_note
Integer :use_restrictions
TextField :use_restrictions_note
apply_mtime_columns
end
alter_table(:accession) do
add_foreign_key([:repo_id], :repository, :key => :id)
add_unique_constraint([:repo_id, :identifier], :name => "accession_unique_identifier")
add_index(:suppressed)
end
create_table(:resource) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :repo_id, :null => false
Integer :accession_id, :null => true
HalfLongString :title, :null => false
String :identifier
DynamicEnum :language_id, :null => true
DynamicEnum :level_id, :null => false
String :other_level
DynamicEnum :resource_type_id, :null => true
Integer :publish
Integer :restrictions
TextField :repository_processing_note
String :ead_id
String :ead_location
TextField :finding_aid_title
TextField :finding_aid_filing_title
String :finding_aid_date
TextField :finding_aid_author
DynamicEnum :finding_aid_description_rules_id
String :finding_aid_language
TextField :finding_aid_sponsor
TextField :finding_aid_edition_statement
TextField :finding_aid_series_statement
String :finding_aid_revision_date
TextField :finding_aid_revision_description
DynamicEnum :finding_aid_status_id
TextField :finding_aid_note
Integer :notes_json_schema_version, :null => false
MediumBlobField :notes, :null => true
Integer :system_generated, :default => 0
apply_mtime_columns
end
alter_table(:resource) do
add_foreign_key([:repo_id], :repository, :key => :id)
add_foreign_key([:accession_id], :accession, :key => :id)
add_unique_constraint([:repo_id, :identifier], :name => "resource_unique_identifier")
add_unique_constraint([:repo_id, :ead_id], :name => "resource_unique_ead_id")
end
create_table(:archival_object) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :repo_id, :null => false
Integer :root_record_id, :null => true
Integer :parent_id, :null => true
String :parent_name, :null => true
Integer :position, :null => true
Integer :publish
String :ref_id, :null => false, :unique => false
String :component_id, :null => true
HalfLongString :title, :null => true
TextField :display_string, :null => true
DynamicEnum :level_id, :null => false
String :other_level
DynamicEnum :language_id, :null => true
Integer :notes_json_schema_version, :null => false
MediumBlobField :notes, :null => true
Integer :system_generated, :default => 0
Integer :restrictions_apply
TextField :repository_processing_note
apply_mtime_columns
end
alter_table(:archival_object) do
add_foreign_key([:repo_id], :repository, :key => :id)
add_foreign_key([:root_record_id], :resource, :key => :id)
add_foreign_key([:parent_id], :archival_object, :key => :id)
add_index([:parent_name, :position], :unique => true, :name => "uniq_ao_pos")
add_unique_constraint([:root_record_id, :ref_id], :name => "ao_unique_refid")
end
create_table(:digital_object) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :repo_id, :null => false
String :digital_object_id, :null => false
HalfLongString :title
DynamicEnum :level_id
DynamicEnum :digital_object_type_id
DynamicEnum :language_id
Integer :publish
Integer :restrictions
Integer :notes_json_schema_version, :null => false
MediumBlobField :notes, :null => true
Integer :system_generated, :default => 0
apply_mtime_columns
end
alter_table(:digital_object) do
add_foreign_key([:repo_id], :repository, :key => :id)
add_index([:repo_id, :digital_object_id], :unique => true)
end
create_table(:digital_object_component) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :repo_id, :null => false
Integer :root_record_id, :null => true
Integer :parent_id, :null => true
Integer :position, :null => true
String :parent_name, :null => true
Integer :publish
String :component_id
HalfLongString :title
TextField :display_string, :null => true
String :label
DynamicEnum :language_id
Integer :notes_json_schema_version, :null => false
MediumBlobField :notes, :null => true
Integer :system_generated, :default => 0
apply_mtime_columns
end
alter_table(:digital_object_component) do
add_foreign_key([:repo_id], :repository, :key => :id)
add_foreign_key([:root_record_id], :digital_object, :key => :id)
add_foreign_key([:parent_id], :digital_object_component, :key => :id)
add_index([:parent_name, :position], :unique => true, :name => "uniq_do_pos")
add_unique_constraint([:repo_id, :component_id], :name => "doc_unique_identifier")
end
create_table(:instance) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :resource_id
Integer :archival_object_id
Integer :accession_id
DynamicEnum :instance_type_id, :null => false
apply_mtime_columns
end
alter_table(:instance) do
add_foreign_key([:resource_id], :resource, :key => :id)
add_foreign_key([:archival_object_id], :archival_object, :key => :id)
add_foreign_key([:accession_id], :accession, :key => :id)
end
create_table(:instance_do_link_rlshp) do
primary_key :id
Integer :digital_object_id
Integer :instance_id
Integer :aspace_relationship_position
apply_mtime_columns(false)
end
alter_table(:instance_do_link_rlshp) do
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
add_foreign_key([:instance_id], :instance, :key => :id)
end
create_table(:container) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :instance_id
DynamicEnum :type_1_id, :null => false
String :indicator_1, :null => false
String :barcode_1
DynamicEnum :type_2_id
String :indicator_2
DynamicEnum :type_3_id
String :indicator_3
String :container_extent
DynamicEnum :container_extent_type_id
apply_mtime_columns
end
alter_table(:container) do
add_foreign_key([:instance_id], :instance, :key => :id)
end
create_table(:vocabulary) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
String :name, :null => false, :unique => true
String :ref_id, :null => false, :unique => true
apply_mtime_columns
end
self[:vocabulary].insert(:name => "global", :ref_id => "global",
:create_time => Time.now,
:system_mtime => Time.now,
:user_mtime => Time.now)
create_table(:subject) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :vocab_id, :null => false
HalfLongString :title
String :terms_sha1, :index => true, :null => false
String :authority_id
TextField :scope_note
DynamicEnum :source_id, :null => true
apply_mtime_columns
end
alter_table(:subject) do
add_foreign_key([:vocab_id], :vocabulary, :key => :id)
add_unique_constraint([:vocab_id, :authority_id, :source_id], :name => "subj_auth_source_uniq")
add_unique_constraint([:vocab_id, :terms_sha1, :source_id], :name => "subj_terms_uniq")
end
create_table(:term) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :vocab_id, :null => false
String :term, :null => false
DynamicEnum :term_type_id, :null => false
apply_mtime_columns
end
alter_table(:term) do
add_foreign_key([:vocab_id], :vocabulary, :key => :id)
add_index([:vocab_id, :term, :term_type_id], :unique => true)
end
create_table(:subject_term) do
primary_key :id
Integer :subject_id, :null => false
Integer :term_id, :null => false
end
alter_table(:subject_term) do
add_foreign_key([:subject_id], :subject, :key => :id)
add_foreign_key([:term_id], :term, :key => :id)
add_index([:subject_id, :term_id], :name => "subject_term_idx")
end
create_table(:name_person) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :agent_person_id, :null => false
String :primary_name, :null => false
DynamicEnum :name_order_id, :null => false
HalfLongString :title, :null => true
TextField :prefix, :null => true
TextField :rest_of_name, :null => true
TextField :suffix, :null => true
TextField :fuller_form, :null => true
String :number, :null => true
apply_name_columns
apply_mtime_columns
end
alter_table(:name_person) do
add_foreign_key([:agent_person_id], :agent_person, :key => :id)
end
create_table(:name_family) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :agent_family_id, :null => false
TextField :family_name, :null => false
TextField :prefix, :null => true
apply_name_columns
apply_mtime_columns
end
alter_table(:name_family) do
add_foreign_key([:agent_family_id], :agent_family, :key => :id)
end
create_table(:name_corporate_entity) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :agent_corporate_entity_id, :null => false
TextField :primary_name, :null => false
TextField :subordinate_name_1, :null => true
TextField :subordinate_name_2, :null => true
String :number, :null => true
apply_name_columns
apply_mtime_columns
end
alter_table(:name_corporate_entity) do
add_foreign_key([:agent_corporate_entity_id], :agent_corporate_entity, :key => :id)
end
create_table(:name_software) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :agent_software_id, :null => false
TextField :software_name, :null => false
TextField :version, :null => true
TextField :manufacturer, :null => true
apply_name_columns
apply_mtime_columns
end
alter_table(:name_software) do
add_foreign_key([:agent_software_id], :agent_software, :key => :id)
end
create_table(:agent_contact) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :agent_person_id, :null => true
Integer :agent_family_id, :null => true
Integer :agent_corporate_entity_id, :null => true
Integer :agent_software_id, :null => true
TextField :name, :null => false
DynamicEnum :salutation_id, :null => true
TextField :address_1, :null => true
TextField :address_2, :null => true
TextField :address_3, :null => true
TextField :city, :null => true
TextField :region, :null => true
TextField :country, :null => true
TextField :post_code, :null => true
TextField :telephone, :null => true
TextField :telephone_ext, :null => true
TextField :fax, :null => true
TextField :email, :null => true
TextField :email_signature, :null => true
TextField :note, :null => true
apply_mtime_columns
end
alter_table(:agent_contact) do
add_foreign_key([:agent_person_id], :agent_person, :key => :id)
add_foreign_key([:agent_family_id], :agent_family, :key => :id)
add_foreign_key([:agent_corporate_entity_id], :agent_corporate_entity, :key => :id)
add_foreign_key([:agent_software_id], :agent_software, :key => :id)
end
create_table(:deaccession) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :accession_id, :null => true
Integer :resource_id, :null => true
DynamicEnum :scope_id, :null => false
TextField :description, :null => false
TextField :reason
TextField :disposition
Integer :notification
apply_mtime_columns
end
alter_table(:deaccession) do
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
end
create_table(:extent) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :accession_id, :null => true
Integer :deaccession_id, :null => true
Integer :archival_object_id, :null => true
Integer :resource_id, :null => true
Integer :digital_object_id, :null => true
Integer :digital_object_component_id, :null => true
Integer :portion_id, :null => false
String :number, :null => false
DynamicEnum :extent_type_id, :null => false
TextField :container_summary, :null => true
TextField :physical_details, :null => true
String :dimensions, :null => true
apply_mtime_columns
end
alter_table(:extent) do
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:archival_object_id], :archival_object, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
add_foreign_key([:deaccession_id], :deaccession, :key => :id)
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
add_foreign_key([:digital_object_component_id], :digital_object_component, :key => :id)
end
create_table(:related_agents_rlshp) do
primary_key :id
Integer :agent_person_id_0
Integer :agent_person_id_1
Integer :agent_corporate_entity_id_0
Integer :agent_corporate_entity_id_1
Integer :agent_software_id_0
Integer :agent_software_id_1
Integer :agent_family_id_0
Integer :agent_family_id_1
String :relator, :null => false
String :relationship_target, :null => false
String :jsonmodel_type, :null => false
TextField :description, :null => true
Integer :aspace_relationship_position
apply_mtime_columns
end
alter_table(:related_agents_rlshp) do
add_foreign_key([:agent_person_id_0], :agent_person, :key => :id)
add_foreign_key([:agent_person_id_1], :agent_person, :key => :id)
add_foreign_key([:agent_corporate_entity_id_0], :agent_corporate_entity, :key => :id)
add_foreign_key([:agent_corporate_entity_id_1], :agent_corporate_entity, :key => :id)
add_foreign_key([:agent_software_id_0], :agent_software, :key => :id)
add_foreign_key([:agent_software_id_1], :agent_software, :key => :id)
add_foreign_key([:agent_family_id_0], :agent_family, :key => :id)
add_foreign_key([:agent_family_id_1], :agent_family, :key => :id)
end
create_table(:date) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :accession_id, :null => true
Integer :deaccession_id, :null => true
Integer :archival_object_id, :null => true
Integer :resource_id, :null => true
Integer :event_id, :null => true
Integer :digital_object_id, :null => true
Integer :digital_object_component_id, :null => true
Integer :related_agents_rlshp_id, :null => true
Integer :agent_person_id, :null => true
Integer :agent_family_id, :null => true
Integer :agent_corporate_entity_id, :null => true
Integer :agent_software_id, :null => true
Integer :name_person_id, :null => true
Integer :name_family_id, :null => true
Integer :name_corporate_entity_id, :null => true
Integer :name_software_id, :null => true
DynamicEnum :date_type_id, :null => true
DynamicEnum :label_id, :null => false
DynamicEnum :certainty_id, :null => true
String :expression, :null => true
String :begin, :null => true
String :end, :null => true
DynamicEnum :era_id, :null => true
DynamicEnum :calendar_id, :null => true
apply_mtime_columns
end
create_table(:event) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :suppressed, :default => 0, :null => false
Integer :repo_id, :null => false
DynamicEnum :event_type_id, :null => false
DynamicEnum :outcome_id, :null => true
LongString :outcome_note, :null => true
DateTime :timestamp, :null => true
apply_mtime_columns
end
alter_table(:event) do
add_index(:suppressed)
add_foreign_key([:repo_id], :repository, :key => :id)
end
alter_table(:date) do
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:archival_object_id], :archival_object, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
add_foreign_key([:event_id], :event, :key => :id)
add_foreign_key([:deaccession_id], :deaccession, :key => :id)
add_foreign_key([:related_agents_rlshp_id], :related_agents_rlshp, :key => :id)
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
add_foreign_key([:digital_object_component_id], :digital_object_component, :key => :id)
end
create_table(:rights_statement) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :accession_id, :null => true
Integer :archival_object_id, :null => true
Integer :resource_id, :null => true
Integer :digital_object_id, :null => true
Integer :digital_object_component_id, :null => true
Integer :repo_id, :null => false
String :identifier, :null => false
DynamicEnum :rights_type_id, :null => false
Integer :active
String :materials, :null => true
DynamicEnum :ip_status_id, :null => true
Date :ip_expiration_date, :null => true
String :license_identifier_terms, :null => true
String :statute_citation, :null => true
DynamicEnum :jurisdiction_id, :null => true
String :type_note, :null => true
TextField :permissions, :null => true
TextField :restrictions, :null => true
Date :restriction_start_date, :null => true
Date :restriction_end_date, :null => true
String :granted_note, :null => true
apply_mtime_columns
end
alter_table(:rights_statement) do
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:archival_object_id], :archival_object, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
add_foreign_key([:digital_object_component_id], :digital_object_component, :key => :id)
add_foreign_key([:repo_id], :repository, :key => :id)
add_unique_constraint([:repo_id, :identifier], :name => "rights_unique_identifier")
end
create_table(:external_document) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
HalfLongString :title, :null => false
HalfLongString :location, :null => false
Integer :publish
apply_mtime_columns
end
records_supporting_external_documents = [:accession, :archival_object,
:resource, :subject,
:agent_person,
:agent_family,
:agent_corporate_entity,
:agent_software,
:rights_statement,
:digital_object,
:digital_object_component]
records_supporting_external_documents.each do |record|
table = "#{record}_external_document".intern
create_table(table) do
primary_key :id
Integer "#{record}_id".intern
Integer :external_document_id
end
alter_table(table) do
add_foreign_key(["#{record}_id".intern], record, :key => :id)
add_foreign_key([:external_document_id], :external_document, :key => :id)
end
end
create_table(:location) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
String :building, :null => false
HalfLongString :title
String :floor
String :room
String :area
String :barcode
String :classification
String :coordinate_1_label
String :coordinate_1_indicator
String :coordinate_2_label
String :coordinate_2_indicator
String :coordinate_3_label
String :coordinate_3_indicator
DynamicEnum :temporary_id
apply_mtime_columns
end
create_table(:collection_management) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :accession_id, :null => true
Integer :resource_id, :null => true
Integer :digital_object_id, :null => true
TextField :cataloged_note, :null => true
String :processing_hours_per_foot_estimate, :null => true
String :processing_total_extent, :null => true
DynamicEnum :processing_total_extent_type_id, :null => true
String :processing_hours_total, :null => true
TextField :processing_plan, :null => true
DynamicEnum :processing_priority_id, :null => true
DynamicEnum :processing_status_id, :null => true
TextField :processing_funding_source, :null => true
TextField :processors, :null => true
Integer :rights_determined, :default => 0, :null => false
apply_mtime_columns
end
alter_table(:collection_management) do
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
end
create_table(:user_defined) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :accession_id, :null => true
Integer :resource_id, :null => true
Integer :digital_object_id, :null => true
Integer :boolean_1
Integer :boolean_2
Integer :boolean_3
String :integer_1, :null => true
String :integer_2, :null => true
String :integer_3, :null => true
String :real_1, :null => true
String :real_2, :null => true
String :real_3, :null => true
String :string_1, :null => true
String :string_2, :null => true
String :string_3, :null => true
String :string_4, :null => true
TextField :text_1, :null => true
TextField :text_2, :null => true
TextField :text_3, :null => true
TextField :text_4, :null => true
TextField :text_5, :null => true
Date :date_1, :null => true
Date :date_2, :null => true
Date :date_3, :null => true
apply_mtime_columns
end
alter_table(:user_defined) do
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
end
create_table(:file_version) do
primary_key :id
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
Integer :digital_object_id, :null => true
Integer :digital_object_component_id, :null => true
DynamicEnum :use_statement_id, :null => true
DynamicEnum :checksum_method_id, :null => true
LongString :file_uri, :null => false
Integer :publish
DynamicEnum :xlink_actuate_attribute_id
DynamicEnum :xlink_show_attribute_id
DynamicEnum :file_format_name_id
String :file_format_version
Integer :file_size_bytes
String :checksum
String :checksum_method
apply_mtime_columns
end
alter_table(:file_version) do
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
add_foreign_key([:digital_object_component_id], :digital_object_component, :key => :id)
end
create_table(:classification) do
primary_key :id
Integer :repo_id, :null => false
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
String :identifier, :null => false
HalfLongString :title, :null => false
TextField :description
apply_mtime_columns
end
alter_table(:classification) do
add_foreign_key([:repo_id], :repository, :key => :id)
end
create_table(:classification_term) do
primary_key :id
Integer :repo_id, :null => false
Integer :lock_version, :default => 0, :null => false
Integer :json_schema_version, :null => false
String :identifier, :null => false
HalfLongString :title, :null => false
String :title_sha1, :null => false
TextField :description
Integer :root_record_id, :null => true
Integer :parent_id, :null => true
String :parent_name, :null => true
Integer :position, :null => true
apply_mtime_columns
end
alter_table(:classification_term) do
add_foreign_key([:repo_id], :repository, :key => :id)
add_index([:parent_name, :title_sha1], :unique => true)
add_index([:parent_name, :identifier], :unique => true)
add_index([:parent_name, :position], :unique => true, :name => "uniq_ct_pos")
end
create_table(:sequence) do
String :sequence_name, :primary_key => true
Integer :value, :null => false
end
create_table(:deleted_records) do
primary_key :id
String :uri, :null => false
String :operator, :null => false
DateTime :timestamp, :null => false
end
create_table(:active_edit) do
primary_key :id
String :uri, :null => false
String :operator, :null => false
DateTime :timestamp, :null => false, :index => true
end
create_editable_enum('linked_agent_archival_record_relators',
['act', 'adp', 'anl', 'anm', 'ann', 'app', 'arc', 'arr', 'acp',
'art', 'ard', 'asg', 'asn', 'att', 'auc', 'aut', 'aqt', 'aft',
'aud', 'aui', 'aus', 'ant', 'bnd', 'bdd', 'blw', 'bkd', 'bkp',
'bjd', 'bpd', 'bsl', 'cll', 'ctg', 'cns', 'chr', 'cng', 'cli',
'clb', 'col', 'clt', 'clr', 'cmm', 'cwt', 'com', 'cpl', 'cpt',
'cpe', 'cmp', 'cmt', 'ccp', 'cnd', 'con', 'csl', 'csp', 'cos',
'cot', 'coe', 'cts', 'ctt', 'cte', 'ctr', 'ctb', 'cpc', 'cph',
'crr', 'crp', 'cst', 'cov', 'cre', 'cur', 'dnc', 'dtc', 'dtm',
'dte', 'dto', 'dfd', 'dft', 'dfe', 'dgg', 'dln', 'dpc', 'dpt',
'dsr', 'drt', 'dis', 'dbp', 'dst', 'drm', 'dub', 'edt', 'elg',
'elt', 'eng', 'egr', 'etr', 'evp', 'exp', 'fac', 'fld', 'flm',
'fpy', 'frg', 'fmo', 'dnr', 'fnd', 'gis', 'grt', 'hnr', 'hst',
'ilu', 'ill', 'ins', 'itr', 'ive', 'ivr', 'inv', 'lbr', 'ldr',
'lsa', 'led', 'len', 'lil', 'lit', 'lie', 'lel', 'let', 'lee',
'lbt', 'lse', 'lso', 'lgd', 'ltg', 'lyr', 'mfp', 'mfr', 'mrb',
'mrk', 'mdc', 'mte', 'mod', 'mon', 'mcp', 'msd', 'mus', 'nrt',
'opn', 'orm', 'org', 'oth', 'own', 'ppm', 'pta', 'pth', 'pat',
'prf', 'pma', 'pht', 'ptf', 'ptt', 'pte', 'plt', 'prt', 'pop',
'prm', 'prc', 'pro', 'pmn', 'prd', 'prp', 'prg', 'pdr', 'pfr',
'prv', 'pup', 'pbl', 'pbd', 'ppt', 'rcp', 'rce', 'rcd', 'red',
'ren', 'rpt', 'rps', 'rth', 'rtm', 'res', 'rsp', 'rst', 'rse',
'rpy', 'rsg', 'rev', 'rbr', 'sce', 'sad', 'scr', 'scl', 'spy',
'sec', 'std', 'stg', 'sgn', 'sng', 'sds', 'spk', 'spn', 'stm',
'stn', 'str', 'stl', 'sht', 'srv', 'tch', 'tcd', 'ths', 'trc',
'trl', 'tyd', 'tyg', 'uvp', 'vdg', 'voc', 'wit', 'wde', 'wdc',
'wam'])
create_editable_enum('linked_event_archival_record_roles',
['source', 'outcome', 'transfer'])
create_editable_enum('linked_agent_event_roles',
["authorizer", "executing_program", "implementer", "recipient",
"transmitter", "validator"])
create_editable_enum('name_source', ["local", "naf", "nad", "ulan"])
create_editable_enum('name_rule', ["local", "aacr", "dacs", "rda"])
create_editable_enum('accession_acquisition_type', ["deposit", "gift", "purchase", "transfer"])
create_editable_enum('accession_resource_type', ["collection", "publications", "papers", "records"])
create_editable_enum('collection_management_processing_priority', ["high", "medium", "low"])
create_editable_enum('collection_management_processing_status', ["new", "in_progress", "completed"])
create_editable_enum('date_era', ["ce"])
create_editable_enum('date_calendar', ["gregorian"])
create_editable_enum('digital_object_digital_object_type', ["cartographic", "mixed_materials", "moving_image", "notated_music", "software_multimedia", "sound_recording", "sound_recording_musical", "sound_recording_nonmusical", "still_image", "text"])
create_editable_enum('digital_object_level', ["collection", "work", "image"])
create_editable_enum('extent_extent_type', ["cassettes", "cubic_feet", "gigabytes", "leaves", "linear_feet", "megabytes", "photographic_prints", "photographic_slides", "reels", "sheets", "terabytes", "volumes"])
create_editable_enum('event_event_type',
["accession", "accumulation",
"acknowledgement_sent", "acknowledgement_received",
"agreement_signed", "agreement_received",
"agreement_sent", "appraisal", "assessment", "capture",
"cataloged", "collection", "compression",
"contribution", "component_transfer",
"copyright_transfer", "custody_transfer",
"deaccession", "decompression", "decryption",
"deletion", "digital_signature_validation",
"fixity_check", "ingestion",
"message_digest_calculation", "migration",
"normalization", "processed", "publication",
"replication", "validation", "virus_check"],
nil,
# These values are used in the accession "Add Event"
# form, so they need to be here.
#
# See accessions_controller.rb for the definitive list.
:readonly_values => ['acknowledgement_sent',
'agreement_sent',
'agreement_signed',
'cataloged',
'copyright_transfer',
'processed'])
create_editable_enum('container_type', ["box", "carton", "case", "folder", "frame", "object", "reel"])
create_editable_enum('agent_contact_salutation', ["mr", "mrs", "ms", "madame", "sir"])
create_editable_enum('event_outcome', ["pass", "partial pass", "fail"])
create_editable_enum('resource_resource_type', ["collection", "publications", "papers", "records"])
create_editable_enum('resource_finding_aid_description_rules', ["aacr", "cco", "dacs", "rad", "isadg"])
create_editable_enum('resource_finding_aid_status', ["completed", "in_progress", "under_revision", "unprocessed"])
create_editable_enum('instance_instance_type', ["accession", "audio", "books", "computer_disks", "digital_object", "graphic_materials", "maps", "microform", "mixed_materials", "moving_images", "realia", "text"])
create_editable_enum('subject_source', ["aat", "rbgenr", "tgn", "lcsh", "local", "mesh", "gmgpc"])
create_editable_enum('file_version_use_statement',
[ "application",
"application-pdf",
"audio-clip",
"audio-master",
"audio-master-edited",
"audio-service",
"image-master",
"image-master-edited",
"image-service",
"image-service-edited",
"image-thumbnail",
"text-codebook",
"test-data",
"text-data_definition",
"text-georeference",
"text-ocr-edited",
"text-ocr-unedited",
"text-tei-transcripted",
"text-tei-translated",
"video-clip",
"video-master",
"video-master-edited",
"video-service",
"video-streaming"])
create_editable_enum('file_version_checksum_methods',
["md5", "sha-1", "sha-256", "sha-384", "sha-512"])
create_enum("language_iso639_2", ["aar", "abk", "ace", "ach", "ada", "ady", "afa", "afh", "afr", "ain", "aka", "akk", "alb", "ale", "alg", "alt", "amh", "ang", "anp", "apa", "ara", "arc", "arg", "arm", "arn", "arp", "art", "arw", "asm", "ast", "ath", "aus", "ava", "ave", "awa", "aym", "aze", "bad", "bai", "bak", "bal", "bam", "ban", "baq", "bas", "bat", "bej", "bel", "bem", "ben", "ber", "bho", "bih", "bik", "bin", "bis", "bla", "bnt", "bos", "bra", "bre", "btk", "bua", "bug", "bul", "bur", "byn", "cad", "cai", "car", "cat", "cau", "ceb", "cel", "cha", "chb", "che", "chg", "chi", "chk", "chm", "chn", "cho", "chp", "chr", "chu", "chv", "chy", "cmc", "cop", "cor", "cos", "cpe", "cpf", "cpp", "cre", "crh", "crp", "csb", "cus", "cze", "dak", "dan", "dar", "day", "del", "den", "dgr", "din", "div", "doi", "dra", "dsb", "dua", "dum", "dut", "dyu", "dzo", "efi", "egy", "eka", "elx", "eng", "enm", "epo", "est", "ewe", "ewo", "fan", "fao", "fat", "fij", "fil", "fin", "fiu", "fon", "fre", "frm", "fro", "frr", "frs", "fry", "ful", "fur", "gaa", "gay", "gba", "gem", "geo", "ger", "gez", "gil", "gla", "gle", "glg", "glv", "gmh", "goh", "gon", "gor", "got", "grb", "grc", "gre", "grn", "gsw", "guj", "gwi", "hai", "hat", "hau", "haw", "heb", "her", "hil", "him", "hin", "hit", "hmn", "hmo", "hrv", "hsb", "hun", "hup", "iba", "ibo", "ice", "ido", "iii", "ijo", "iku", "ile", "ilo", "ina", "inc", "ind", "ine", "inh", "ipk", "ira", "iro", "ita", "jav", "jbo", "jpn", "jpr", "jrb", "kaa", "kab", "kac", "kal", "kam", "kan", "kar", "kas", "kau", "kaw", "kaz", "kbd", "kha", "khi", "khm", "kho", "kik", "kin", "kir", "kmb", "kok", "kom", "kon", "kor", "kos", "kpe", "krc", "krl", "kro", "kru", "kua", "kum", "kur", "kut", "lad", "lah", "lam", "lao", "lat", "lav", "lez", "lim", "lin", "lit", "lol", "loz", "ltz", "lua", "lub", "lug", "lui", "lun", "luo", "lus", "mac", "mad", "mag", "mah", "mai", "mak", "mal", "man", "mao", "map", "mar", "mas", "may", "mdf", "mdr", "men", "mga", "mic", "min", "mis", "mkh", "mlg", "mlt", "mnc", "mni", "mno", "moh", "mon", "mos", "mul", "mun", "mus", "mwl", "mwr", "myn", "myv", "nah", "nai", "nap", "nau", "nav", "nbl", "nde", "ndo", "nds", "nep", "new", "nia", "nic", "niu", "nno", "nob", "nog", "non", "nor", "nqo", "nso", "nub", "nwc", "nya", "nym", "nyn", "nyo", "nzi", "oci", "oji", "ori", "orm", "osa", "oss", "ota", "oto", "paa", "pag", "pal", "pam", "pan", "pap", "pau", "peo", "per", "phi", "phn", "pli", "pol", "pon", "por", "pra", "pro", "pus", "qaa-qtz", "que", "raj", "rap", "rar", "roa", "roh", "rom", "rum", "run", "rup", "rus", "sad", "sag", "sah", "sai", "sal", "sam", "san", "sas", "sat", "scn", "sco", "sel", "sem", "sga", "sgn", "shn", "sid", "sin", "sio", "sit", "sla", "slo", "slv", "sma", "sme", "smi", "smj", "smn", "smo", "sms", "sna", "snd", "snk", "sog", "som", "son", "sot", "spa", "srd", "srn", "srp", "srr", "ssa", "ssw", "suk", "sun", "sus", "sux", "swa", "swe", "syc", "syr", "tah", "tai", "tam", "tat", "tel", "tem", "ter", "tet", "tgk", "tgl", "tha", "tib", "tig", "tir", "tiv", "tkl", "tlh", "tli", "tmh", "tog", "ton", "tpi", "tsi", "tsn", "tso", "tuk", "tum", "tup", "tur", "tut", "tvl", "twi", "tyv", "udm", "uga", "uig", "ukr", "umb", "und", "urd", "uzb", "vai", "ven", "vie", "vol", "vot", "wak", "wal", "war", "was", "wel", "wen", "wln", "wol", "xal", "xho", "yao", "yap", "yid", "yor", "ypk", "zap", "zbl", "zen", "zha", "znd", "zul", "zun", "zxx", "zza",])
create_enum("linked_agent_role", ["creator", "source", "subject"])
create_enum("agent_relationship_associative_relator", ["is_associative_with"])
create_enum("agent_relationship_earlierlater_relator", ["is_earlier_form_of", "is_later_form_of"])
create_enum("agent_relationship_parentchild_relator", ["is_parent_of", "is_child_of"])
create_enum("agent_relationship_subordinatesuperior_relator", ["is_subordinate_to", "is_superior_of"])
create_enum("archival_record_level", ["class", "collection", "file", "fonds", "item", "otherlevel", "recordgrp", "series", "subfonds", "subgrp", "subseries"])
create_enum("container_location_status", ["current", "previous"], "current")
create_enum("date_type", ["single", "bulk", "inclusive"])
create_enum("date_label", ["broadcast", "copyright", "creation", "deaccession", "digitized", "event", "issued", "modified", "publication", "agent_relation", "other", "usage", "existence", "record_keeping"])
create_enum("date_certainty", ["approximate", "inferred", "questionable"])
create_enum("deaccession_scope", ["whole", "part"], "whole")
create_enum("extent_portion", ["whole", "part"], "whole")
create_enum("file_version_xlink_actuate_attribute", ["none", "other", "onLoad", "onRequest"])
create_enum("file_version_xlink_show_attribute", ["new", "replace", "embed", "other", "none"])
create_editable_enum("file_version_file_format_name", ["aiff", "avi", "gif", "jpeg", "mp3", "pdf", "tiff", "txt"])
create_enum("location_temporary", ["conservation", "exhibit", "loan", "reading_room"])
create_enum("name_person_name_order", ["inverted", "direct"], "inverted")
create_enum("note_digital_object_type", ["summary", "bioghist", "accessrestrict", "userestrict", "custodhist", "dimensions", "edition", "extent", "altformavail", "originalsloc", "note", "acqinfo", "inscription", "langmaterial", "legalstatus", "physdesc", "prefercite", "processinfo", "relatedmaterial"])
create_enum("note_multipart_type", ["accruals", "appraisal", "arrangement", "bioghist", "accessrestrict", "userestrict", "custodhist", "dimensions", "altformavail", "originalsloc", "fileplan", "odd", "acqinfo", "legalstatus", "otherfindaid", "phystech", "prefercite", "processinfo", "relatedmaterial", "scopecontent", "separatedmaterial"])
create_enum("note_orderedlist_enumeration", ["arabic", "loweralpha", "upperalpha", "lowerroman", "upperroman"])
create_enum("note_singlepart_type", ["abstract", "physdesc", "langmaterial", "physloc", "materialspec", "physfacet"])
create_enum("note_bibliography_type", ["bibliography"])
create_enum("note_index_type", ["index"])
create_enum("note_index_item_type", ["name", "person", "family", "corporate_entity", "subject", "function", "occupation", "title", "geographic_name", "genre_form"])
create_enum("country_iso_3166", ["AF", "AX", "AL", "DZ", "AS", "AD", "AO", "AI", "AQ", "AG", "AR", "AM", "AW", "AU", "AT", "AZ", "BS", "BH", "BD", "BB", "BY", "BE", "BZ", "BJ", "BM", "BT", "BO", "BQ", "BA", "BW", "BV", "BR", "IO", "BN", "BG", "BF", "BI", "KH", "CM", "CA", "CV", "KY", "CF", "TD", "CL", "CN", "CX", "CC", "CO", "KM", "CG", "CD", "CK", "CR", "CI", "HR", "CU", "CW", "CY", "CZ", "DK", "DJ", "DM", "DO", "EC", "EG", "SV", "GQ", "ER", "EE", "ET", "FK", "FO", "FJ", "FI", "FR", "GF", "PF", "TF", "GA", "GM", "GE", "DE", "GH", "GI", "GR", "GL", "GD", "GP", "GU", "GT", "GG", "GN", "GW", "GY", "HT", "HM", "VA", "HN", "HK", "HU", "IS", "IN", "ID", "IR", "IQ", "IE", "IM", "IL", "IT", "JM", "JP", "JE", "JO", "KZ", "KE", "KI", "KP", "KR", "KW", "KG", "LA", "LV", "LB", "LS", "LR", "LY", "LI", "LT", "LU", "MO", "MK", "MG", "MW", "MY", "MV", "ML", "MT", "MH", "MQ", "MR", "MU", "YT", "MX", "FM", "MD", "MC", "MN", "ME", "MS", "MA", "MZ", "MM", "NA", "NR", "NP", "NL", "NC", "NZ", "NI", "NE", "NG", "NU", "NF", "MP", "NO", "OM", "PK", "PW", "PS", "PA", "PG", "PY", "PE", "PH", "PN", "PL", "PT", "PR", "QA", "RE", "RO", "RU", "RW", "BL", "SH", "KN", "LC", "MF", "PM", "VC", "WS", "SM", "ST", "SA", "SN", "RS", "SC", "SL", "SG", "SX", "SK", "SI", "SB", "SO", "ZA", "GS", "SS", "ES", "LK", "SD", "SR", "SJ", "SZ", "SE", "CH", "SY", "TW", "TJ", "TZ", "TH", "TL", "TG", "TK", "TO", "TT", "TN", "TR", "TM", "TC", "TV", "UG", "UA", "AE", "GB", "US", "UM", "UY", "UZ", "VU", "VE", "VN", "VG", "VI", "WF", "EH", "YE", "ZM", "ZW"])
create_enum("rights_statement_rights_type", ["intellectual_property", "license", "statute", "institutional_policy"])
create_enum("rights_statement_ip_status", ["copyrighted", "public_domain", "unknown"])
create_enum("subject_term_type", ["cultural_context", "function", "geographic", "genre_form", "occupation", "style_period", "technique", "temporal", "topical", "uniform_title"])
create_table(:linked_agents_rlshp) do
primary_key :id
Integer :agent_person_id
Integer :agent_software_id
Integer :agent_family_id
Integer :agent_corporate_entity_id
Integer :accession_id
Integer :archival_object_id
Integer :digital_object_id
Integer :digital_object_component_id
Integer :event_id
Integer :resource_id
Integer :aspace_relationship_position
apply_mtime_columns
String :role
DynamicEnum :role_id
DynamicEnum :relator_id
end
alter_table(:linked_agents_rlshp) do
add_foreign_key([:agent_person_id], :agent_person, :key => :id)
add_foreign_key([:agent_software_id], :agent_software, :key => :id)
add_foreign_key([:agent_family_id], :agent_family, :key => :id)
add_foreign_key([:agent_corporate_entity_id], :agent_corporate_entity, :key => :id)
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:archival_object_id], :archival_object, :key => :id)
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
add_foreign_key([:digital_object_component_id], :digital_object_component, :key => :id)
add_foreign_key([:event_id], :event, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
end
create_table(:linked_agent_term) do
primary_key :id
Integer :linked_agents_rlshp_id, :null => false
Integer :term_id, :null => false
end
alter_table(:linked_agent_term) do
add_foreign_key([:linked_agents_rlshp_id], :linked_agents_rlshp, :key => :id)
add_foreign_key([:term_id], :term, :key => :id)
add_index([:linked_agents_rlshp_id, :term_id], :name => "linked_agent_term_idx")
end
# Event relationships
create_table(:event_link_rlshp) do
primary_key :id
Integer :accession_id
Integer :resource_id
Integer :archival_object_id
Integer :digital_object_id
Integer :digital_object_component_id
Integer :agent_person_id
Integer :agent_family_id
Integer :agent_corporate_entity_id
Integer :agent_software_id
Integer :event_id
Integer :aspace_relationship_position
apply_mtime_columns(false)
DynamicEnum :role_id
end
alter_table(:event_link_rlshp) do
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
add_foreign_key([:archival_object_id], :archival_object, :key => :id)
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
add_foreign_key([:agent_person_id], :agent_person, :key => :id)
add_foreign_key([:agent_family_id], :agent_family, :key => :id)
add_foreign_key([:agent_corporate_entity_id], :agent_corporate_entity, :key => :id)
add_foreign_key([:agent_software_id], :agent_software, :key => :id)
add_foreign_key([:event_id], :event, :key => :id)
end
# Accession/resource "spawned from" relationships
create_table(:spawned_rlshp) do
primary_key :id
Integer :accession_id
Integer :resource_id
Integer :aspace_relationship_position
apply_mtime_columns(false)
end
alter_table(:spawned_rlshp) do
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
end
create_table(:subject_rlshp) do
primary_key :id
Integer :accession_id
Integer :archival_object_id
Integer :resource_id
Integer :digital_object_id
Integer :digital_object_component_id
Integer :subject_id
Integer :aspace_relationship_position
apply_mtime_columns(false)
end
alter_table(:subject_rlshp) do
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:archival_object_id], :archival_object, :key => :id)
add_foreign_key([:resource_id], :resource, :key => :id)
add_foreign_key([:digital_object_id], :digital_object, :key => :id)
add_foreign_key([:digital_object_component_id], :digital_object_component, :key => :id)
add_foreign_key([:subject_id], :subject, :key => :id)
end
create_table(:housed_at_rlshp) do
primary_key :id
Integer :container_id
Integer :location_id
Integer :aspace_relationship_position
String :status
Date :start_date
Date :end_date
String :note
apply_mtime_columns(false)
end
alter_table(:housed_at_rlshp) do
add_foreign_key([:container_id], :container, :key => :id)
add_foreign_key([:location_id], :location, :key => :id)
end
[:subject, :accession, :archival_object, :collection_management, :digital_object,
:digital_object_component, :event, :location, :resource].each do |record|
table = "#{record}_ext_id".intern
create_table(table) do
primary_key :id
Integer "#{record}_id".intern, :null => false
String :external_id, :null => false
String :source, :null => false
end
alter_table(table) do
add_foreign_key(["#{record}_id".intern], record, :key => :id)
end
end
create_table(:classification_creator_rlshp) do
primary_key :id
Integer :agent_person_id
Integer :agent_software_id
Integer :agent_family_id
Integer :agent_corporate_entity_id
Integer :classification_id
Integer :aspace_relationship_position
apply_mtime_columns(false)
end
alter_table(:classification_creator_rlshp) do
add_foreign_key([:agent_person_id], :agent_person, :key => :id)
add_foreign_key([:agent_family_id], :agent_family, :key => :id)
add_foreign_key([:agent_corporate_entity_id], :agent_corporate_entity, :key => :id)
add_foreign_key([:agent_software_id], :agent_software, :key => :id)
add_foreign_key([:classification_id], :classification, :key => :id)
end
create_table(:classification_term_creator_rlshp) do
primary_key :id
Integer :agent_person_id
Integer :agent_software_id
Integer :agent_family_id
Integer :agent_corporate_entity_id
Integer :classification_term_id
Integer :aspace_relationship_position
apply_mtime_columns(false)
end
alter_table(:classification_term_creator_rlshp) do
add_foreign_key([:agent_person_id], :agent_person, :key => :id)
add_foreign_key([:agent_family_id], :agent_family, :key => :id)
add_foreign_key([:agent_corporate_entity_id], :agent_corporate_entity, :key => :id)
add_foreign_key([:agent_software_id], :agent_software, :key => :id)
add_foreign_key([:classification_term_id], :classification_term, :key => :id)
end
create_table(:classification_rlshp) do
primary_key :id
Integer :resource_id
Integer :accession_id
Integer :classification_id
Integer :classification_term_id
Integer :aspace_relationship_position
apply_mtime_columns(false)
end
alter_table(:classification_rlshp) do
add_foreign_key([:resource_id], :resource, :key => :id)
add_foreign_key([:accession_id], :accession, :key => :id)
add_foreign_key([:classification_id], :classification, :key => :id)
add_foreign_key([:classification_term_id], :classification_term, :key => :id)
end
end
down do
remaining = tables.reject {|t| t == :schema_info}
ceiling = 100
begin
greylist = []
remaining.each do |table|
foreign_key_list(table).each do |fk|
next if fk[:table] == table
if (not greylist.include?(fk[:table])) && remaining.include?(fk[:table])
greylist << fk[:table]
end
end
end
remaining.each do |table|
if not greylist.include?(table)
puts "Dropping #{table}"
drop_table?(table)
end
end
remaining = greylist.clone
ceiling = ceiling - 1
end while (not remaining.empty?) && ceiling > 0
unless remaining.empty?
$stderr.puts "Could not drop the following tables : #{remaining.join(',')}"
$stderr.puts "( check fk constaints )"
end
end
end