Prepare removal of core entities

Users want to be able to (reliably) remove accounts, headings, companies, persons and entity_credit_accounts (customers/vendors).
ledgersmb · ehuelsmann · Jan 24, 2025 · Jan 24, 2025 · Jan 24, 2025 · Jan 24, 2025
commit 4e832b4fa44a86fa93d3a347f7ea1909c63c7e10
diff --git a/UI/Contact/divs/company.html b/UI/Contact/divs/company.html
@@ -133,14 +133,25 @@
                value = 'get_by_cc'
                class = 'submit'
         } %]
-                [% INCLUDE button element_data = {
-                        text = text('Save')
-                        id = 'company-action-save'
-                        class="submit"
-                        type="submit"
-                        name="__action"
-                        value="save_company"
-                } %]
+        [% INCLUDE button element_data = {
+               text = text('Save')
+                 id = 'company-action-save'
+               class="submit"
+                type="submit"
+                name="__action"
+               value="save_company"
+        } %]
+        [%
+        IF entity_id.defined AND NOT company.is_used ;
+        INCLUDE button element_data = {
+               text = text('Delete')
+                 id = 'company-action-delete'
+               class="submit"
+                type="submit"
+                name="__action"
+               value="delete_company"
+        };
+        END %]
 </div>
 </form>
 [% FOREACH n = notes %]

diff --git a/lib/LedgerSMB/Entity/Company.pm b/lib/LedgerSMB/Entity/Company.pm
@@ -81,11 +81,20 @@ has 'sic_code' => (is => 'rw', isa => 'Maybe[Str]', required => 0);
 
 Date when the company was entered into LedgerSMB
 
+=cut
+
+has 'created' => (is => 'rw', isa => 'LedgerSMB::PGDate');
+
+=item is_used
+
+Boolean (Readonly). True if the company entity is used in the database
+(and hence can't be deleted).
+
 =back
 
 =cut
 
-has 'created' => (is => 'rw', isa => 'LedgerSMB::PGDate');
+has 'is_used' => (is => 'ro');
 
 =head1 METHODS
 

diff --git a/sql/changes/1.13/fk-actions-for-is_used-functions.sql b/sql/changes/1.13/fk-actions-for-is_used-functions.sql
@@ -0,0 +1,122 @@
+
+/*
+
+  This file contains the revisited foreign key constraints which need ON DELETE
+  set to CASCADE, instead of 'NO ACTION', in order to make the *__is_used()
+  functions work.  These functions try to delete a record (e.g., an account);
+  when that succeeds, it concludes that the account isn't used (and aborts the
+  deletion!).
+
+  However, accounts will have associated account_link records and
+  cr_coa_to_account records. These should not block deletion however, meaning
+  that the delete should cascade. This way, the delete gets blocked when the
+  deletion of the cr_coa_to_account row deletion fails due to further foreign
+  keys being checked (and failing).
+
+ */
+
+
+-- ACCOUNT (gl accounts)
+ALTER TABLE account_link
+  DROP CONSTRAINT account_link_account_id_fkey,
+  ADD FOREIGN KEY (account_id) REFERENCES account (id) ON DELETE CASCADE;
+
+ALTER TABLE cr_coa_to_account
+  DROP CONSTRAINT cr_coa_to_account_chart_id_fkey,
+  ADD FOREIGN KEY (chart_id) REFERENCES account (id) ON DELETE CASCADE;
+
+-- ENTITY
+
+-- entity additional data
+ALTER TABLE entity_other_name
+  DROP CONSTRAINT entity_other_name_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+ALTER TABLE entity_to_contact
+  DROP CONSTRAINT entity_to_contact_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+ALTER TABLE entity_to_location
+  DROP CONSTRAINT entity_to_location_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+ALTER TABLE entity_bank_account
+  DROP CONSTRAINT entity_bank_account_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+ALTER TABLE entity_note
+  DROP CONSTRAINT entity_note_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+ALTER TABLE entity_note
+  DROP CONSTRAINT entity_note_ref_key_fkey,
+  ADD FOREIGN KEY (ref_key) REFERENCES entity (id) ON DELETE CASCADE;
+
+ALTER TABLE file_entity
+  DROP CONSTRAINT file_entity_ref_key_fkey,
+  ADD FOREIGN KEY (ref_key) REFERENCES entity (id) ON DELETE CASCADE;
+
+-- entity type specialization
+ALTER TABLE company
+  DROP CONSTRAINT company_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+ALTER TABLE person
+  DROP CONSTRAINT person_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+ALTER TABLE robot
+  DROP CONSTRAINT robot_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+-- entity roles
+ALTER TABLE entity_credit_account
+  DROP CONSTRAINT entity_credit_account_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+ALTER TABLE entity_employee
+  DROP CONSTRAINT entity_employee_entity_id_fkey,
+  ADD FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE;
+
+-- explicitly not adding 'users': cascading will delete the user
+-- but whether users are used or not isn't detectable in the schema
+
+
+-- ENTITY_CREDIT_ACCOUNT
+
+-- eca additional data
+
+ALTER TABLE eca_to_contact
+  DROP CONSTRAINT eca_to_contact_credit_id_fkey,
+  ADD FOREIGN KEY (credit_id) REFERENCES entity_credit_account (id) ON DELETE CASCADE;
+
+ALTER TABLE eca_to_location
+  DROP CONSTRAINT eca_to_location_credit_id_fkey,
+  ADD FOREIGN KEY (credit_id) REFERENCES entity_credit_account (id) ON DELETE CASCADE;
+
+ALTER TABLE eca_note
+  DROP CONSTRAINT eca_note_ref_key_fkey,
+  ADD FOREIGN KEY (ref_key) REFERENCES entity_credit_account (id) ON DELETE CASCADE;
+
+ALTER TABLE eca_tax
+  DROP CONSTRAINT eca_tax_eca_id_fkey,
+  ADD FOREIGN KEY (eca_id) REFERENCES entity_credit_account (id) ON DELETE CASCADE;
+
+ALTER TABLE file_eca
+  DROP CONSTRAINT file_eca_ref_key_fkey,
+  ADD FOREIGN KEY (ref_key) REFERENCES entity_credit_account (id) ON DELETE CASCADE;
+
+-- (price matrix data as customer)
+ALTER TABLE partscustomer
+  DROP CONSTRAINT partscustomer_credit_id_fkey,
+  ADD FOREIGN KEY (credit_id) REFERENCES entity_credit_account (id) ON DELETE CASCADE;
+
+-- (price matrix data as vendor)
+ALTER TABLE partsvendor
+  DROP CONSTRAINT partsvendor_credit_id_fkey,
+  ADD FOREIGN KEY (credit_id) REFERENCES entity_credit_account (id) ON DELETE CASCADE;
+
+-- eca as business reporting unit
+ALTER TABLE business_unit
+  DROP CONSTRAINT business_unit_credit_id_fkey,
+  ADD FOREIGN KEY (credit_id) REFERENCES entity_credit_account (id) ON DELETE CASCADE;
diff --git a/sql/changes/LOADORDER b/sql/changes/LOADORDER
@@ -196,3 +196,4 @@ mc/delete-migration-validation-data.sql
 # 1.13 changes
 1.13/change-logout.sql
 1.13/rm-status.sql
+1.13/fk-actions-for-is_used-functions.sql
diff --git a/sql/modules/Account.sql b/sql/modules/Account.sql
@@ -228,6 +228,50 @@ COMMENT ON FUNCTION account__get_from_accno(in_accno text) IS
 $$ Returns the account where the accno field matches (excatly) the
 in_accno provided.$$;
 
+CREATE OR REPLACE FUNCTION account__is_used(in_id int)
+RETURNS BOOL AS
+$$
+BEGIN
+  BEGIN
+    delete from account where id = in_id;
+    raise sqlstate 'P0004';
+  EXCEPTION
+    WHEN foreign_key_violation THEN
+      return true;
+    WHEN assert_failure THEN
+      return false;
+  END;
+END;
+$$ language plpgsql;
+
+COMMENT ON FUNCTION account__is_used(in_id int) IS
+  $$Checks whether the general ledger account is used or not.
+
+In case it isn't used, it should be possible to delete it.
+$$; --'
+
+CREATE OR REPLACE FUNCTION account_heading__is_used(in_id int)
+RETURNS BOOL AS
+$$
+BEGIN
+  BEGIN
+    delete from account_heading where id = in_id;
+    raise sqlstate 'P0004';
+  EXCEPTION
+    WHEN foreign_key_violation THEN
+      return true;
+    WHEN assert_failure THEN
+      return false;
+  END;
+END;
+$$ language plpgsql;
+
+COMMENT ON FUNCTION account_heading__is_used(in_id int) IS
+  $$Checks whether the general ledger account heading is used or not.
+
+In case it isn't used, it should be possible to delete it.
+$$; --'
+
 CREATE OR REPLACE FUNCTION account__is_recon(in_accno text) RETURNS BOOL AS
 $$ SELECT count(*) > 0
      FROM cr_coa_to_account c2a
@@ -747,21 +791,14 @@ ta(account_id) AS (
   SELECT a.id, false, a.accno, a.description, a.category,
          a.contra,
          (EXISTS (select 1 from cr_coa_to_account cca where chart_id = a.id)),
-         a.tax, a.obsolete, a.gifi_accno,
-       (EXISTS (select 1 from ac where a.id = ac.chart_id)
-        OR EXISTS (select 1 from eca where a.id = eca.account_id)
-        OR EXISTS (select 1 from p where a.id = p.account_id)
-        OR EXISTS (select 1 from ta where a.id = ta.account_id)
-        ),
+         a.tax, a.obsolete, a.gifi_accno, account__is_used(a.id),
        link
   FROM account a
        LEFT JOIN l ON a.id = l.account_id
   UNION
   SELECT id, true, accno, description, null::char(1),
          null::boolean, null::boolean, null::boolean, null::boolean, null::text,
-         (EXISTS (select 1 from ha where h.id = ha.heading)
-           OR EXISTS (select 1 from hh where h.id = hh.parent_id)),
-         null::text
+         account_heading__is_used(h.id), null::text
    FROM account_heading h
 
   ) x ORDER BY accno;

diff --git a/sql/modules/BLACKLIST b/sql/modules/BLACKLIST
@@ -6,6 +6,7 @@ account__get_by_link_desc
 account__get_from_accno
 account__get_taxes
 account__is_recon
+account__is_used
 account__list_by_heading
 account__list_translations
 account__obtain_balance
@@ -18,6 +19,7 @@ account_has_transactions
 account_heading__check_tree
 account_heading__delete
 account_heading__delete_translation
+account_heading__is_used
 account_heading__list
 account_heading__list_translations
 account_heading__save_translation
@@ -184,6 +186,7 @@ eca__get_pricematrix_by_pricegroup
 eca__get_taxes
 eca__history
 eca__history_summary
+eca__is_used
 eca__list_contacts
 eca__list_locations
 eca__list_notes
@@ -207,6 +210,7 @@ entity__delete_contact
 entity__delete_location
 entity__get
 entity__get_bank_account
+entity__is_used
 entity__list_bank_account
 entity__list_classes
 entity__list_contacts

diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
@@ -20,7 +20,8 @@ CREATE TYPE company_entity AS(
   license_number text,
   sic_code varchar,
   control_code text,
-  country_id int
+  country_id int,
+  is_used bool
 );
 
 DROP TYPE IF EXISTS eca__pricematrix CASCADE;
@@ -415,7 +416,27 @@ USING in_entity_class, in_contact, in_contact_info, in_meta_number,
 END
 $$ LANGUAGE PLPGSQL;
 
+CREATE OR REPLACE FUNCTION eca__is_used(in_id int)
+  RETURNS boolean AS
+$$
+  BEGIN
+    BEGIN
+      delete from entity_credit_account where id = in_id;
+      raise sqlstate 'P0004';
+    EXCEPTION
+      WHEN foreign_key_violation THEN
+        return true;
+      WHEN assert_failure THEN
+        return false;
+    END;
+  END;
+$$ language plpgsql;
 
+COMMENT ON FUNCTION eca__is_used(in_id int) IS
+  $$Checks whether the credit account is used or not.
+
+In case it isn't used, it should be possible to delete it.
+$$; --'
 
 DROP FUNCTION IF EXISTS eca__get_taxes(in_credit_id int);
 
@@ -618,11 +639,12 @@ entity class.$$;
 CREATE OR REPLACE FUNCTION company__get (in_entity_id int)
 RETURNS company_entity AS
 $$
-        SELECT c.entity_id, c.legal_name, c.tax_id, c.sales_tax_id,
-               c.license_number, c.sic_code, e.control_code, e.country_id
-          FROM company c
-          JOIN entity e ON e.id = c.entity_id
-         WHERE entity_id = $1;
+  SELECT c.entity_id, c.legal_name, c.tax_id, c.sales_tax_id,
+         c.license_number, c.sic_code, e.control_code, e.country_id,
+         entity__is_used(in_entity_id)
+    FROM company c
+    JOIN entity e ON e.id = c.entity_id
+   WHERE entity_id = $1;
 $$ language sql;
 
 COMMENT ON FUNCTION company__get (in_entity_id int) IS
@@ -632,7 +654,8 @@ CREATE OR REPLACE FUNCTION company__get_by_cc (in_control_code text)
 RETURNS company_entity AS
 $$
         SELECT c.entity_id, c.legal_name, c.tax_id, c.sales_tax_id,
-               c.license_number, c.sic_code, e.control_code, e.country_id
+               c.license_number, c.sic_code, e.control_code, e.country_id,
+               entity__is_used(e.id)
           FROM company c
           JOIN entity e ON e.id = c.entity_id
          WHERE e.control_code = $1;

diff --git a/sql/modules/Entity.sql b/sql/modules/Entity.sql
@@ -11,6 +11,30 @@ set client_min_messages = 'warning';
 
 BEGIN;
 
+
+CREATE OR REPLACE FUNCTION entity__is_used(in_id int)
+  RETURNS boolean AS
+  $$
+BEGIN
+  BEGIN
+    delete from entity where id = in_id;
+    raise sqlstate 'P0004';
+  EXCEPTION
+    WHEN foreign_key_violation THEN
+      return true;
+    WHEN assert_failure THEN
+      return false;
+  END;
+END;
+$$ language plpgsql;
+
+COMMENT ON FUNCTION entity__is_used(in_id int) IS
+  $$Checks whether the entity is used or not.
+
+In case the entity isn't used, it should be possible to delete it.
+$$; --'
+
+
 CREATE OR REPLACE FUNCTION entity_save(
     in_entity_id int, in_name text, in_entity_class INT
 ) RETURNS INT AS $$