# Appendix A: GDPR SQL scripts

Use the following SQL scripts when running a version of Apteco software earlier than Q1 2018. They're essentially in pairs: one to provide a count and one to execute the action.

---

## Cascade scripts

`CS_GDPR_Anonymise_Count.sql`

- **Action**: Doesn't remove any records. Retrieves a distinct and non-distinct count of communications records to anonymise for a given Unique Reference Number (URN) in the Cascade database.
- **Usage**: Supply a URN in line 3 of the script.

`CS_GDPR_Remove_Count.sql`

- **Action**: Doesn't remove any records. Retrieves a distinct and non-distinct count of communications records to remove for a given URN in the Cascade database.
- **Usage**: Supply a URN in line 3 of the script.

`CS_GDPR_Remove_Execute.sql`

- **Action**: Caution: removes records. Retrieves a distinct and non-distinct count of communications records to remove for a given URN in the Cascade database, then removes them.
- **Usage**: Supply a URN in line 3 of the script.

---

## Facebook scripts

`FB_GDPR_Anonymise_Count.sql`

- **Action**: Doesn't remove any records. Retrieves a distinct and non-distinct count of Facebook users and attributes to anonymise for a given firstname/lastname combination in the Facebook Responses database.
- **Usage**: Supply values for the `FirstName` and `LastName` variables in lines 3 and 4 of the script.

`FB_GDPR_Anonymise_Execute.sql`

- **Action**: Caution: anonymises and deletes data. For a given firstname/lastname combination, sets the following table column combinations to `NULL`/delete in the Facebook Responses database.

  | Table Name | Column Name | Action |
  | --- | --- | --- |
  | `FacebookUsers` | `first_name` | Null |
  |  | `last_name` | Null |
  |  | `link` | Null |
  |  | `name` | Null |
  | `FacebookUserAttributes` | `attribute_value` | Null |
  | `FacebookEventRSVP` | `event_rsvp` | Delete |
  | `FacebookComment` | `source` | Delete |
  | `FacebookLike` | `like` | Delete |
  | `FacebookSharedPost` | `sharedpost` | Delete |
- **Usage**: Supply values for the `FirstName` and `LastName` variables in lines 3 and 4 of the script.

`FB_GDPR_Remove_Count.sql`

- **Action**: Doesn't remove any records. Retrieves a distinct and non-distinct count of Facebook users and attributes for a given firstname/lastname combination.
- **Usage**: Supply values for the `FirstName` and `LastName` variables in lines 3 and 4 of the script.

`FB_GDPR_Remove_Execute.sql`

- **Action**: Caution: deletes data. Sets the following table/column combinations to `NULL`/delete in the Facebook Responses database.

  | Table Name | Column Name | Action |
  | --- | --- | --- |
  | `FacebookUsers` | `first_name` | Delete |
  |  | `last_name` | Delete |
  |  | `link` | Delete |
  |  | `name` | Delete |
  | `FacebookUserAttributes` | `attribute_value` | Delete |
  | `FacebookEventRSVP` | `event_rsvp` | Delete |
  | `FacebookComment` | `source` | Delete |
  | `FacebookLike` | `like` | Delete |
  | `FacebookSharedPost` | `sharedpost` | Delete |
- **Usage**: Supply values for the `FirstName` and `LastName` variables in lines 3 and 4 of the script.

---

## PeopleStage scripts

`PS_GDPR_Anonymise_Count.sql`

- **Action**: Doesn't remove any records. Retrieves distinct and non-distinct counts of records that would be anonymised for a given URN and Email Address from the Communications, CommunicationsTrackingHistory, EmailResponse, EmailResponseDetails, and LiveData tables.
- **Usage**: Supply values for the `Urn` and `Email` variables in lines 6 and 7 of the script.

`PS_GDPR_Anonymise_Execute.sql`

- **Action**: Caution: removes data. For a given URN and Email Address combination, performs the following table/column actions.

  | Table Name | Column Name | Action |
  | --- | --- | --- |
  | `CommunicationsTrackingHistory` | `ContentItemValue` | Null |
  | `EmailResponseDetails` | `Value` | Delete |
  | `EmailResponse` | `URN` | Delete |
  |  | `Email` | Delete |
  | `StateHistoryExternalData` | `ColumnValue` | Delete |
- **Usage**: Supply values for the `Urn` and `Email` variables in lines 6 and 7 of the script.

`PS_GDPR_Remove_Count.sql`

- **Action**: Doesn't remove any records. Retrieves distinct and non-distinct counts of records it would remove for a given URN and Email Address.
- **Usage**: Supply values for the `Urn` and `Email` variables in lines 6 and 7 of the script.

`PS_GDPR_Remove_Execute.sql`

- **Action**: Caution: removes data. For a given URN and Email Address combination, performs the following table/column actions.

  | Table Name | Column Name | Action |
  | --- | --- | --- |
  | `StateHistory` | All items | Delete |
  | `Pool` | All items | Delete |
  | `JourneyHistory` | All items | Delete |
  | `Communications` | All items | Delete |
  | `CommunicationsTrackingHistory` | All items | Delete |
  | `EmailResponseDetails` | All items | Delete |
  | `EmailResponse` | All items | Delete |
  | `StateHistoryExternalData` | All items | Delete |
- **Usage**: Supply values for the `Urn` and `Email` variables in lines 6 and 7 of the script.

---

## Twitter scripts

`TW_GDPR_Anonymise_Count.sql`

- **Action**: Doesn't remove any records. Retrieves distinct and non-distinct counts of records that would be anonymised for a given Twitter FullName and TwitterUsername from the user table of the Twitter response database.
- **Usage**: Supply values for the `FullName` and `TwitterUsername` variables in lines 3 and 4 of the script.

`TW_GDPR_Anonymise_Execute.sql`

- **Action**: Caution: removes data and records from the Twitter response database.

  | Table Name | Column Name | Action |
  | --- | --- | --- |
  | `user` | `screen_name` | Null |
  |  | `name` | Null |
  |  | `description` | Null |
  |  | `url` | Null |
  |  | `profile_image_url` | Null |
  |  | `profile_image_url_https` | Null |
  | `tweet` | All items | Delete |
  | `retweet` | All items | Delete |
  | `mention_reply` | All items | Delete |
  | `follower` | All items | Delete |
  | `direct_message` | All items | Delete |
- **Usage**: Supply values for the `FullName` and `TwitterUsername` variables in lines 3 and 4 of the script.

`TW_GDPR_Remove_Count.sql`

- **Action**: Doesn't remove any records. Retrieves distinct and non-distinct counts of records it would remove for a given Twitter FullName and TwitterUsername.
- **Usage**: Supply values for the `FullName` and `TwitterUsername` variables in lines 3 and 4 of the script.

`TW_GDPR_Remove_Execute.sql`

- **Action**: Caution: removes records from the Twitter response database.

  | Table Name | Column Name | Action |
  | --- | --- | --- |
  | `user` | All items | Null |
  | `tweet` | All items | Delete |
  | `retweet` | All items | Delete |
  | `mention_reply` | All items | Delete |
  | `follower` | All items | Delete |
  | `direct_message` | All items | Delete |
- **Usage**: Supply values for the `FullName` and `TwitterUsername` variables in lines 3 and 4 of the script.