schema-programmability.sql

/* ==========================================================================
   ccCRM - Firebird 5 schema, part 2: view, procedure and triggers.
   Run AFTER schema.sql.

   DBeaver: if "Execute script" (Alt+X) chokes on SET TERM, delete the two
   SET TERM lines and every trailing "^", and instead run each statement on
   its own: select the whole CREATE PROCEDURE / CREATE TRIGGER text and press
   Ctrl+Enter. isql handles the file as written.
   ========================================================================== */

/* ---------- Email list view ----------
   Main contact + additional contacts, one row per distinct address.
   Example: SELECT EMAIL FROM V_EMAIL_LIST
            WHERE TYPE_NAME = 'ABS Cloud' AND STATUS_NAME = 'Active';

   Customers without a type or status are left out (inner joins). */

CREATE VIEW V_EMAIL_LIST (WAVE_ID, CUSTOMER_NAME, TYPE_NAME, STATUS_NAME, EMAIL) AS
  SELECT C.WAVE_ID, C.NAME, T.NAME, S.NAME, C.EMAIL
  FROM CUSTOMER C
  JOIN CUSTOMER_TYPE   T ON T.TYPE_ID   = C.TYPE_ID
  JOIN CUSTOMER_STATUS S ON S.STATUS_ID = C.STATUS_ID
  WHERE C.IN_WAVE AND C.EMAIL <> ''
  UNION
  SELECT C.WAVE_ID, C.NAME, T.NAME, S.NAME, K.EMAIL
  FROM CONTACT K
  JOIN CUSTOMER C        ON C.WAVE_ID   = K.WAVE_ID
  JOIN CUSTOMER_TYPE   T ON T.TYPE_ID   = C.TYPE_ID
  JOIN CUSTOMER_STATUS S ON S.STATUS_ID = C.STATUS_ID
  WHERE C.IN_WAVE AND K.EMAIL <> '';

/* ---------- Automatic change log ---------- */

SET TERM ^ ;

/* Writes one automatic log entry: "<field>: <old> -> <new>" */
CREATE PROCEDURE LOG_CHANGE (
  P_WAVE_ID VARCHAR(100) CHARACTER SET ASCII,
  P_FIELD   VARCHAR(40),
  P_OLD     VARCHAR(500),
  P_NEW     VARCHAR(500)
) AS
BEGIN
  INSERT INTO CUSTOMER_LOG (WAVE_ID, ENTRY_SOURCE, FIELD_NAME, OLD_VALUE, NEW_VALUE, SUMMARY)
  VALUES (:P_WAVE_ID, 'A', :P_FIELD, LEFT(:P_OLD, 200), LEFT(:P_NEW, 200),
          LEFT(:P_FIELD || ': ' || COALESCE(:P_OLD, '(none)') || ' -> ' || COALESCE(:P_NEW, '(none)'), 500));
END^

CREATE TRIGGER CUSTOMER_AI_LOG FOR CUSTOMER ACTIVE AFTER INSERT POSITION 0 AS
BEGIN
  INSERT INTO CUSTOMER_LOG (WAVE_ID, ENTRY_SOURCE, SUMMARY)
  VALUES (NEW.WAVE_ID, 'A', 'Customer added from Wave');
END^

CREATE TRIGGER CUSTOMER_AU_LOG FOR CUSTOMER ACTIVE AFTER UPDATE POSITION 0 AS
DECLARE VARIABLE O VARCHAR(500);
DECLARE VARIABLE N VARCHAR(500);
BEGIN
  /* Type and status: log names, not ids */
  IF (OLD.TYPE_ID IS DISTINCT FROM NEW.TYPE_ID) THEN
  BEGIN
    O = NULL; N = NULL;
    SELECT NAME FROM CUSTOMER_TYPE WHERE TYPE_ID = OLD.TYPE_ID INTO :O;
    SELECT NAME FROM CUSTOMER_TYPE WHERE TYPE_ID = NEW.TYPE_ID INTO :N;
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'Type', :O, :N);
  END

  IF (OLD.STATUS_ID IS DISTINCT FROM NEW.STATUS_ID) THEN
  BEGIN
    O = NULL; N = NULL;
    SELECT NAME FROM CUSTOMER_STATUS WHERE STATUS_ID = OLD.STATUS_ID INTO :O;
    SELECT NAME FROM CUSTOMER_STATUS WHERE STATUS_ID = NEW.STATUS_ID INTO :N;
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'Status', :O, :N);
  END

  IF (OLD.USER_COUNT IS DISTINCT FROM NEW.USER_COUNT) THEN
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'User count',
      CAST(OLD.USER_COUNT AS VARCHAR(20)), CAST(NEW.USER_COUNT AS VARCHAR(20)));

  IF (OLD.AD_GROUP IS DISTINCT FROM NEW.AD_GROUP) THEN
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'AD group', OLD.AD_GROUP, NEW.AD_GROUP);

  /* Wave-owned fields */
  IF (OLD.NAME IS DISTINCT FROM NEW.NAME) THEN
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'Name', OLD.NAME, NEW.NAME);

  IF (OLD.FIRST_NAME IS DISTINCT FROM NEW.FIRST_NAME) THEN
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'First name', OLD.FIRST_NAME, NEW.FIRST_NAME);

  IF (OLD.LAST_NAME IS DISTINCT FROM NEW.LAST_NAME) THEN
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'Last name', OLD.LAST_NAME, NEW.LAST_NAME);

  IF (OLD.EMAIL IS DISTINCT FROM NEW.EMAIL) THEN
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'Email', OLD.EMAIL, NEW.EMAIL);

  IF (OLD.PHONE IS DISTINCT FROM NEW.PHONE) THEN
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'Phone', OLD.PHONE, NEW.PHONE);

  IF (OLD.MOBILE IS DISTINCT FROM NEW.MOBILE) THEN
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'Mobile', OLD.MOBILE, NEW.MOBILE);

  IF (OLD.WEBSITE IS DISTINCT FROM NEW.WEBSITE) THEN
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'Website', OLD.WEBSITE, NEW.WEBSITE);

  /* Address: any component change logs one entry */
  IF (OLD.ADDRESS1 IS DISTINCT FROM NEW.ADDRESS1 OR
      OLD.ADDRESS2 IS DISTINCT FROM NEW.ADDRESS2 OR
      OLD.CITY IS DISTINCT FROM NEW.CITY OR
      OLD.STATE_CODE IS DISTINCT FROM NEW.STATE_CODE OR
      OLD.POSTAL_CODE IS DISTINCT FROM NEW.POSTAL_CODE) THEN
  BEGIN
    O = TRIM(COALESCE(OLD.ADDRESS1, '') || ' ' || COALESCE(OLD.ADDRESS2, '') || ', ' ||
             COALESCE(OLD.CITY, '') || ' ' || COALESCE(OLD.STATE_CODE, '') || ' ' || COALESCE(OLD.POSTAL_CODE, ''));
    N = TRIM(COALESCE(NEW.ADDRESS1, '') || ' ' || COALESCE(NEW.ADDRESS2, '') || ', ' ||
             COALESCE(NEW.CITY, '') || ' ' || COALESCE(NEW.STATE_CODE, '') || ' ' || COALESCE(NEW.POSTAL_CODE, ''));
    EXECUTE PROCEDURE LOG_CHANGE(NEW.WAVE_ID, 'Address', :O, :N);
  END

  /* Presence in Wave */
  IF (OLD.IN_WAVE IS DISTINCT FROM NEW.IN_WAVE) THEN
    INSERT INTO CUSTOMER_LOG (WAVE_ID, ENTRY_SOURCE, FIELD_NAME, SUMMARY)
    VALUES (NEW.WAVE_ID, 'A', 'In Wave',
            IIF(NEW.IN_WAVE, 'Found in Wave again', 'No longer returned by Wave'));
END^

SET TERM ; ^

/* ---------- Sync upsert (reference; run from the Delphi sync, one row per Wave customer) ----------

   UPDATE OR INSERT INTO CUSTOMER
     (WAVE_ID, NAME, FIRST_NAME, LAST_NAME, EMAIL, PHONE, MOBILE, WEBSITE,
      ADDRESS1, ADDRESS2, CITY, STATE_CODE, POSTAL_CODE, IN_WAVE, SYNCED_AT)
   VALUES
     (:WAVE_ID, :NAME, :FIRST_NAME, :LAST_NAME, :EMAIL, :PHONE, :MOBILE, :WEBSITE,
      :ADDRESS1, :ADDRESS2, :CITY, :STATE_CODE, :POSTAL_CODE, TRUE, CURRENT_TIMESTAMP)
   MATCHING (WAVE_ID);

   Only the Wave-owned columns are listed, so your columns (type, status, AD group,
   user count, notes, logo) are left alone on update. Afterwards, mark missing ones:

   UPDATE CUSTOMER SET IN_WAVE = FALSE
   WHERE IN_WAVE AND SYNCED_AT < :SYNC_START;
*/