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);
