<commit_before><?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog 
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

  <changeSet author="slakhani" id="Remove not null constraint on phone number in catissue_user table">
    <dropNotNullConstraint 
      tableName="CATISSUE_USER" 
      columnName="PHONE_NUMBER" 
      columnDataType="${text.type}(63)"/>
  </changeSet>
  
  <changeSet author="slakhani" id="Remove unique constraint for site,mrn in catissue_part_medical_id table">
    <dropUniqueConstraint 
      tableName="CATISSUE_PART_MEDICAL_ID" 
      constraintName="MRN_SITE_UNIQUE"/>
  </changeSet>
  
  <changeSet author="nmarwaha" id="Added PMBC specimen type under class Cell">
    <preConditions onFail="MARK_RAN">
      <sqlCheck expectedResult="0">
        select 
          count(pv.identifier) 
        from 
          catissue_permissible_value pv 
          join catissue_permissible_value parent on pv.parent_identifier = parent.identifier
        where 
          pv.value = 'PBMC' and parent.value = 'Cell' and parent.public_id = '2003991'
      </sqlCheck>
    </preConditions>
    <sql>
      insert into catissue_permissible_value
        (identifier, concept_code, definition, parent_identifier, value, public_id, sortorder)
      values 
        (getNextPvId(),'','',getPvParentId('2003991', 'Cell'),'PBMC',null,null)
    </sql>
  </changeSet>
  
  <changeSet author="nmarwaha" id="View to compute container hierarchy" runOnChange="true" dbms="mysql">
    <sql> 
      create or replace view os_container_hierarchy_view as 
        select 
          h.descendent_id as container_id, 
          group_concat(distinct a.name order by a.identifier separator ' -> ') as hierarchy 
        from 
          os_containers_hierarchy h 
          inner join os_storage_containers a on a.identifier = h.ancestor_id 
        where 
          a.activity_status != 'Disabled' 
        group by 
          h.descendent_id;
    </sql>
  </changeSet>
  
  <changeSet author="nmarwaha" id="View to compute container hierarchy" runOnChange="true" dbms="oracle">
    <sql> 
      create or replace view cont_hierarchy_view as (
        select 
          h.descendent_id as container_id, 
          LISTAGG(a.name, '->') within group( order by a.identifier) as hierarchy 
        from <commit_msg>Updated the view name to os_container_hierarchy_view The view name is different for MySQL and Oracle, so updated to make them same<commit_after><?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog 
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

  <changeSet author="slakhani" id="Remove not null constraint on phone number in catissue_user table">
    <dropNotNullConstraint 
      tableName="CATISSUE_USER" 
      columnName="PHONE_NUMBER" 
      columnDataType="${text.type}(63)"/>
  </changeSet>
  
  <changeSet author="slakhani" id="Remove unique constraint for site,mrn in catissue_part_medical_id table">
    <dropUniqueConstraint 
      tableName="CATISSUE_PART_MEDICAL_ID" 
      constraintName="MRN_SITE_UNIQUE"/>
  </changeSet>
  
  <changeSet author="nmarwaha" id="Added PMBC specimen type under class Cell">
    <preConditions onFail="MARK_RAN">
      <sqlCheck expectedResult="0">
        select 
          count(pv.identifier) 
        from 
          catissue_permissible_value pv 
          join catissue_permissible_value parent on pv.parent_identifier = parent.identifier
        where 
          pv.value = 'PBMC' and parent.value = 'Cell' and parent.public_id = '2003991'
      </sqlCheck>
    </preConditions>
    <sql>
      insert into catissue_permissible_value
        (identifier, concept_code, definition, parent_identifier, value, public_id, sortorder)
      values 
        (getNextPvId(),'','',getPvParentId('2003991', 'Cell'),'PBMC',null,null)
    </sql>
  </changeSet>
  
  <changeSet author="nmarwaha" id="View to compute container hierarchy" runOnChange="true" dbms="mysql">
    <sql> 
      create or replace view os_container_hierarchy_view as 
        select 
          h.descendent_id as container_id, 
          group_concat(distinct a.name order by a.identifier separator ' -> ') as hierarchy 
        from 
          os_containers_hierarchy h 
          inner join os_storage_containers a on a.identifier = h.ancestor_id 
        where 
          a.activity_status != 'Disabled' 
        group by 
          h.descendent_id;
    </sql>
  </changeSet>
  
  <changeSet author="nmarwaha" id="View to compute container hierarchy" runOnChange="true" dbms="oracle">
    <sql> 
      create or replace view os_cont_hierarchy_view as (
        select 
          h.descendent_id as container_id, 
          LISTAGG(a.name, '->') within group( order by a.identifier) as hierarchy 
        from 
{'fraction_non_alphanumeric': 0.10420724581223217, 'fraction_numerical': 0.010518114530580444, 'mean_word_length': 3.11128903122498, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 2, '>': 51, 'https://': 0, 'lorem ipsum': 0, 'www.': 8, 'xml': 12}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3292533', 'n_tokens_mistral': 1692, 'n_tokens_neox': 1568, 'n_words': 350}