| <?xml version="1.0" encoding="UTF-8" standalone="no"?> |
| <!-- |
| Licensed to the Apache Software Foundation (ASF) under one |
| or more contributor license agreements. See the NOTICE file |
| distributed with this work for additional information |
| regarding copyright ownership. The ASF licenses this file |
| to you under the Apache License, Version 2.0 (the |
| "License"); you may not use this file except in compliance |
| with the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, |
| software distributed under the License is distributed on an |
| "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| KIND, either express or implied. See the License for the |
| specific language governing permissions and limitations |
| under the License. |
| --> |
| <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd"> |
| <properties> |
| |
| <entry key="UDynGroupMembers"> |
| CREATE TABLE UDynGroupMembers( |
| any_id CHAR(36), |
| group_id CHAR(36), |
| UNIQUE(any_id, group_id)) |
| </entry> |
| <entry key="ADynGroupMembers"> |
| CREATE TABLE ADynGroupMembers( |
| anyType_id VARCHAR(255), |
| any_id CHAR(36), |
| group_id CHAR(36), |
| UNIQUE(anyType_id, any_id, group_id)) |
| </entry> |
| <entry key="DynRoleMembers"> |
| CREATE TABLE DynRoleMembers( |
| any_id CHAR(36), |
| role_id VARCHAR(255), |
| UNIQUE(any_id, role_id)) |
| </entry> |
| <entry key="DynRealmMembers"> |
| CREATE TABLE DynRealmMembers( |
| any_id CHAR(36), |
| dynRealm_id VARCHAR(255), |
| UNIQUE(any_id, dynRealm_id)) |
| </entry> |
| |
| <!-- user --> |
| <entry key="user_search"> |
| CREATE VIEW user_search AS |
| |
| SELECT u.id as any_id, u.*,attrs,attrValues |
| FROM SyncopeUser u, jsonb_array_elements(COALESCE(u.plainAttrs, '[{}]'::jsonb)) attrs, |
| jsonb_array_elements(COALESCE(attrs -> 'values', '[{}]'::jsonb)) attrValues |
| </entry> |
| <entry key="user_search_urelationship"> |
| CREATE VIEW user_search_urelationship AS |
| |
| SELECT m.user_id AS any_id, m.anyObject_id AS right_any_id, m.type_id AS type |
| FROM URelationship m |
| </entry> |
| <entry key="user_search_umembership"> |
| CREATE VIEW user_search_umembership AS |
| |
| SELECT m.user_id AS any_id, g.id AS group_id, g.name AS group_name |
| FROM UMembership m, SyncopeGroup g |
| WHERE m.group_id = g.id |
| </entry> |
| <entry key="user_search_role"> |
| CREATE VIEW user_search_role AS |
| |
| SELECT ss.user_id AS any_id, ss.role_id AS role_id |
| FROM SyncopeUser_SyncopeRole ss |
| </entry> |
| <entry key="user_search_priv"> |
| CREATE VIEW user_search_priv AS |
| |
| SELECT ss.user_id AS any_id, sp.privilege_id AS privilege_id |
| FROM SyncopeUser_SyncopeRole ss, SyncopeRole_Privilege sp |
| WHERE ss.role_id = sp.role_id |
| </entry> |
| <entry key="user_search_dynpriv"> |
| CREATE VIEW user_search_dynpriv AS |
| |
| SELECT any_id, privilege_id |
| FROM DynRoleMembers drm, SyncopeRole_Privilege rp |
| WHERE drm.role_id = rp.role_id |
| </entry> |
| <entry key="user_search_resource"> |
| CREATE VIEW user_search_resource AS |
| |
| SELECT st.user_id AS any_id, st.resource_id AS resource_id |
| FROM SyncopeUser_ExternalResource st |
| </entry> |
| <entry key="user_search_group_res"> |
| CREATE VIEW user_search_group_res AS |
| |
| SELECT m.user_id AS any_id, st.resource_id AS resource_id |
| FROM UMembership m, SyncopeGroup r, SyncopeGroup_ExternalResource st |
| WHERE m.group_id = r.id AND st.group_id = r.id |
| </entry> |
| |
| <!-- anyObject --> |
| <entry key="anyObject_search"> |
| CREATE VIEW anyObject_search AS |
| |
| SELECT a.id as any_id, a.*,attrs,attrValues |
| FROM AnyObject a, jsonb_array_elements(COALESCE(a.plainAttrs, '[{}]'::jsonb)) attrs, |
| jsonb_array_elements(COALESCE(attrs -> 'values', '[{}]'::jsonb)) attrValues |
| </entry> |
| <entry key="anyObject_search_arelationship"> |
| CREATE VIEW anyObject_search_arelationship AS |
| |
| SELECT m.left_anyObject_id AS any_id, m.right_anyObject_id AS right_any_id, m.type_id AS type |
| FROM ARelationship m |
| </entry> |
| <entry key="anyObject_search_amembership"> |
| CREATE VIEW anyObject_search_amembership AS |
| |
| SELECT m.anyObject_id AS any_id, g.id AS group_id, g.name AS group_name |
| FROM AMembership m, SyncopeGroup g |
| WHERE m.group_id = g.id |
| </entry> |
| <entry key="anyObject_search_resource"> |
| CREATE VIEW anyObject_search_resource AS |
| |
| SELECT st.anyObject_id AS any_id, st.resource_id AS resource_id |
| FROM AnyObject_ExternalResource st |
| </entry> |
| <entry key="anyObject_search_group_res"> |
| CREATE VIEW anyObject_search_group_res AS |
| |
| SELECT m.anyObject_id AS any_id, st.resource_id AS resource_id |
| FROM AMembership m, SyncopeGroup r, SyncopeGroup_ExternalResource st |
| WHERE m.group_id = r.id AND st.group_id = r.id |
| </entry> |
| |
| <!-- group --> |
| <entry key="group_search"> |
| CREATE VIEW group_search AS |
| |
| SELECT g.id as any_id, g.*,attrs,attrValues |
| FROM SyncopeGroup g, jsonb_array_elements(COALESCE(g.plainAttrs, '[{}]'::jsonb)) attrs, |
| jsonb_array_elements(COALESCE(attrs -> 'values', '[{}]'::jsonb)) attrValues |
| </entry> |
| <entry key="group_search_resource"> |
| CREATE VIEW group_search_resource AS |
| |
| SELECT st.group_id AS any_id, st.resource_id AS resource_id |
| FROM SyncopeGroup_ExternalResource st |
| </entry> |
| |
| </properties> |