-- $Id: acs-sc-views-create.sql,v 1.1 2001/09/19 22:59:01 donb Exp $

create or replace view valid_uninstalled_binding as
    select c.contract_id, c.contract_name, i.impl_id, i.impl_name
    from acs_sc_contracts c, acs_sc_impls i
    where c.contract_name = i.impl_contract_name
    and not exists (select 1
		    from acs_sc_bindings b
		    where b.contract_id = c.contract_id
		    and b.impl_id = i.impl_id)
    and not exists (select 1
		    from acs_sc_operations o
		    where o.contract_id = c.contract_id
		    and not exists (select 1
				    from acs_sc_impl_alias a
				    where a.impl_contract_name = c.contract_name
				    and a.impl_id = i.impl_id
				    and a.impl_operation_name = o.operation_name));



create or replace view invalid_uninstalled_binding as
    select c.contract_id, c.contract_name, i.impl_id, i.impl_name
    from acs_sc_contracts c, acs_sc_impls i
    where c.contract_name = i.impl_contract_name
    and not exists (select 1
		    from acs_sc_bindings b
		    where b.contract_id = c.contract_id
		    and b.impl_id = i.impl_id)
    and exists (select 1
	        from acs_sc_operations o
		where o.contract_id = c.contract_id
		and not exists (select 1
			        from acs_sc_impl_alias a
				where a.impl_contract_name = c.contract_name
				and a.impl_id = i.impl_id
				and a.impl_operation_name = o.operation_name));


create or replace view orphan_implementation as
    select i.impl_id, i.impl_name, i.impl_contract_name
    from acs_sc_impls i
    where not exists (select 1
		      from acs_sc_bindings b
		      where b.impl_id = i.impl_id)
    and not exists (select 1
		    from acs_sc_contracts c
		    where c.contract_name = i.impl_contract_name);