Завдання 1. Моделювання структур бази даних реєстру

<changeSet author="registry owner" id="searchCondition koatuu_obl_contains_name">
    <ext:createSearchCondition name="koatuu_obl_contains_name">
        <ext:table name="koatuu" alias="k">
            <ext:column name="koatuu_id"/>
            <ext:column name="code"/>
            <ext:column name="name" sorting="asc" searchType="contains"/>
        </ext:table>
        <ext:where>
            <ext:condition tableAlias="k" columnName="type" operator="eq" value="'О'"/>
        </ext:where>
    </ext:createSearchCondition>
</changeSet>

SELECT k.koatuu_id,
       k.code,
       k.name
  FROM koatuu k
 WHERE k.type = 'О'::text
 ORDER BY k.name;

<changeSet author="registry owner" id="searchCondition koatuu_np_starts_with_name_by_obl">
    <ext:createSearchCondition name="koatuu_np_starts_with_name_by_obl" limit="100">
        <ext:table name="koatuu" alias="np">
            <ext:column name="koatuu_id"/>
            <ext:column name="name" searchType="startsWith" sorting="asc"/>
            <ext:column name="level1" searchType="equal"/>
        </ext:table>
        <ext:table name="koatuu" alias="rn">
            <ext:column name="name" alias="name_rn"/>
        </ext:table>
        <ext:join type="left">
            <ext:left alias="np">
                <ext:column name="level2"/>
            </ext:left>
            <ext:right alias="rn">
                <ext:column name="code"/>
            </ext:right>
            <ext:condition logicOperator="and" tableAlias="rn" columnName="type" operator="eq" value="'Р'"/>
        </ext:join>
        <ext:where>
        <ext:condition tableAlias="np" columnName="type" operator="eq" value="'НП'"/>
        </ext:where>
    </ext:createSearchCondition>
</changeSet>

SELECT np.koatuu_id,
       np.name,
       np.level1,
       rn.name AS name_rn
  FROM koatuu np
         LEFT JOIN koatuu rn ON np.level2 = rn.code AND rn.type = 'Р'::text
 WHERE np.type = 'НП'::text
 ORDER BY np.name;

<changeSet author="registry owner" id="searchCondition ownership_contains_name">
    <ext:createSearchCondition name="ownership_contains_name">
        <ext:table name="ownership" alias="o">
            <ext:column name="ownership_id"/>
            <ext:column name="code"/>
            <ext:column name="name" sorting="asc" searchType="contains"/>
        </ext:table>
    </ext:createSearchCondition>
</changeSet>

SELECT o.ownership_id,
       o.code,
       o.name
  FROM ownership o
 ORDER BY o.name;

<changeSet author="registry owner" id="searchCondition staff_contains_name">
<comment>CREATE search condition staff_contains_name</comment>
    <ext:createSearchCondition name="staff_contains_name">
        <ext:table name="staff_status" alias="s">
            <ext:column name="staff_status_id"/>
            <ext:column name="name" sorting="asc" searchType="contains"/>
        </ext:table>
    </ext:createSearchCondition>
</changeSet>

SELECT s.staff_status_id,
       s.name
  FROM staff_status s
 ORDER BY s.name;