<?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.* FROM SyncopeUser u
  </entry>
  <entry key="user_search_unique_attr">
    CREATE VIEW user_search_unique_attr AS

    SELECT ua.owner_id AS any_id,
    ua.schema_id AS schema_id,
    uav.booleanvalue AS booleanvalue,
    uav.datevalue AS datevalue,
    uav.doublevalue AS doublevalue,
    uav.longvalue AS longvalue,
    uav.stringvalue AS stringvalue
    FROM UPlainAttrUniqueValue uav, UPlainAttr ua
    WHERE uav.attribute_id = ua.id
  </entry>
  <entry key="user_search_attr">
    CREATE VIEW user_search_attr AS

    SELECT ua.owner_id AS any_id,
    ua.schema_id AS schema_id,
    uav.booleanvalue AS booleanvalue,
    uav.datevalue AS datevalue,
    uav.doublevalue AS doublevalue,
    uav.longvalue AS longvalue,
    uav.stringvalue AS stringvalue
    FROM UPlainAttrValue uav, UPlainAttr ua
    WHERE uav.attribute_id = ua.id
  </entry>
  <entry key="user_search_null_attr">
    CREATE VIEW user_search_null_attr AS

    SELECT u.id AS any_id,
    PlainSchema.id AS schema_id,
    NULL AS booleanvalue,
    CAST (NULL AS DATETIME2) datevalue,
    CAST (NULL AS FLOAT) doublevalue,
    CAST (NULL AS BIGINT) longvalue,
    CAST (NULL AS VARCHAR(255)) AS stringvalue
    FROM SyncopeUser u CROSS JOIN PlainSchema
    LEFT OUTER JOIN UPlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id)
    WHERE ua.id IS NULL
  </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.* FROM AnyObject a
  </entry>
  <entry key="anyObject_search_unique_attr">
    CREATE VIEW anyObject_search_unique_attr AS

    SELECT ua.owner_id AS any_id,
    ua.schema_id AS schema_id,
    uav.booleanvalue AS booleanvalue,
    uav.datevalue AS datevalue,
    uav.doublevalue AS doublevalue,
    uav.longvalue AS longvalue,
    uav.stringvalue AS stringvalue
    FROM APlainAttrUniqueValue uav, APlainAttr ua
    WHERE uav.attribute_id = ua.id
  </entry>
  <entry key="anyObject_search_attr">
    CREATE VIEW anyObject_search_attr AS

    SELECT ua.owner_id AS any_id,
    ua.schema_id AS schema_id,
    uav.booleanvalue AS booleanvalue,
    uav.datevalue AS datevalue,
    uav.doublevalue AS doublevalue,
    uav.longvalue AS longvalue,
    uav.stringvalue AS stringvalue
    FROM APlainAttrValue uav, APlainAttr ua
    WHERE uav.attribute_id = ua.id
  </entry>
  <entry key="anyObject_search_null_attr">
    CREATE VIEW anyObject_search_null_attr AS

    SELECT u.id AS any_id,
    PlainSchema.id AS schema_id,
    NULL AS booleanvalue,
    CAST (NULL AS DATETIME2) datevalue,
    CAST (NULL AS FLOAT) doublevalue,
    CAST (NULL AS BIGINT) longvalue,
    CAST (NULL AS VARCHAR(255)) AS stringvalue
    FROM AnyObject u CROSS JOIN PlainSchema
    LEFT OUTER JOIN APlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id)
    WHERE ua.id IS NULL
  </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 r.id as any_id, r.* FROM SyncopeGroup r
  </entry>
  <entry key="group_search_unique_attr">
    CREATE VIEW group_search_unique_attr AS

    SELECT ua.owner_id AS any_id,
    ua.schema_id AS schema_id,
    uav.booleanvalue AS booleanvalue,
    uav.datevalue AS datevalue,
    uav.doublevalue AS doublevalue,
    uav.longvalue AS longvalue,
    uav.stringvalue AS stringvalue
    FROM GPlainAttrUniqueValue uav, GPlainAttr ua
    WHERE uav.attribute_id = ua.id
  </entry>
  <entry key="group_search_attr">
    CREATE VIEW group_search_attr AS

    SELECT ua.owner_id AS any_id,
    ua.schema_id AS schema_id,
    uav.booleanvalue AS booleanvalue,
    uav.datevalue AS datevalue,
    uav.doublevalue AS doublevalue,
    uav.longvalue AS longvalue,
    uav.stringvalue AS stringvalue
    FROM GPlainAttrValue uav, GPlainAttr ua
    WHERE uav.attribute_id = ua.id
  </entry>
  <entry key="group_search_null_attr">
    CREATE VIEW group_search_null_attr AS

    SELECT u.id AS any_id,
    PlainSchema.id AS schema_id,
    NULL AS booleanvalue,
    CAST (NULL AS DATETIME2) datevalue,
    CAST (NULL AS FLOAT) doublevalue,
    CAST (NULL AS BIGINT) longvalue,
    CAST (NULL AS VARCHAR(255)) AS stringvalue
    FROM SyncopeGroup u CROSS JOIN PlainSchema
    LEFT OUTER JOIN GPlainAttr ua ON (PlainSchema.id = ua.schema_id AND ua.owner_id = u.id)
    WHERE ua.id IS NULL
  </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>