Index: openacs-4/packages/acs-service-contract/sql/postgresql/upgrade/upgrade-4.7d2-4.7d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-service-contract/sql/postgresql/upgrade/upgrade-4.7d2-4.7d3.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-service-contract/sql/postgresql/upgrade/upgrade-4.7d2-4.7d3.sql 10 Sep 2003 14:53:06 -0000 1.1 +++ openacs-4/packages/acs-service-contract/sql/postgresql/upgrade/upgrade-4.7d2-4.7d3.sql 16 Sep 2003 08:29:59 -0000 1.2 @@ -7,6 +7,8 @@ -- add column impl_pretty_name alter table acs_sc_impls add column impl_pretty_name varchar(200); +update acs_sc_impls set impl_pretty_name = impl_name; + create or replace function acs_sc_impl__new(varchar,varchar,varchar,varchar) returns integer as ' declare @@ -43,3 +45,54 @@ return v_impl_id; end;' language 'plpgsql'; + + +drop view valid_uninstalled_bindings; +create view valid_uninstalled_bindings as + select c.contract_id, c.contract_name, i.impl_id, i.impl_name, i.impl_owner_name, i.impl_pretty_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_aliases a + where a.impl_contract_name = c.contract_name + and a.impl_id = i.impl_id + and a.impl_operation_name = o.operation_name)); + + + +drop view invalid_uninstalled_bindings; +create view invalid_uninstalled_bindings as + select c.contract_id, c.contract_name, i.impl_id, i.impl_name, i.impl_owner_name, i.impl_pretty_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_aliases a + where a.impl_contract_name = c.contract_name + and a.impl_id = i.impl_id + and a.impl_operation_name = o.operation_name)); + + +drop view orphan_implementations; +create view orphan_implementations as + select i.impl_id, i.impl_name, i.impl_owner_name, i.impl_contract_name, i.impl_pretty_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);