OC4IDS_Database_Delete_Collections.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wlYslPD1uRp2"
      },
      "source": [
        "# OC4IDS Database - Delete Collections\n",
        "\n",
        "Use this notebook to delete collections from the database."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "z4-iWuZRhoEe"
      },
      "source": [
        "Enter database credentials.\n",
        "\n",
        "> **Helpdesk analysts:** See CRM issue #6335."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "IVy70_JfeV9C",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "import getpass\n",
        "\n",
        "user = 'postgres'\n",
        "password = getpass.getpass('Password:')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "femBJjzTsQtO"
      },
      "source": [
        "Connect notebook to database:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bIYcnkQ7rl6B",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "connection_string = 'postgresql://' + user + ':' + password + '@oc4ids-database-2.cuujgua4wses.us-east-1.rds.amazonaws.com/postgres'\n",
        "\n",
        "%load_ext sql\n",
        "%sql $connection_string"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aCv8jlj3NC4o"
      },
      "source": [
        "Get project count for each collection:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gXMJoFurNGNW",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select collection_id, source_id, data_version, count(*) from projects join collection on projects.collection_id = collection.id group by collection_id, source_id, data_version order by collection_id desc;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "S9vE3ACTNHy7"
      },
      "source": [
        "Set collection ids to delete:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AGeQ-bugNird",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "collection_ids = []  # list of collection_ids \n",
        "collection_ids = tuple(collection_ids)  # convert list to tuple for use in sql queries"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mmFsjhU9OFCW"
      },
      "source": [
        "Do you really want to delete these collections?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "UC55SqEJNlhq",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%sql select * from collection where id in :collection_ids"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "K-SV0zUTNaD4"
      },
      "source": [
        "Delete collections and data:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "u7bU7-CcLmbt",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%sql delete from collection where id in :collection_ids\n",
        "\n",
        "%sql delete from projects where collection_id in :collection_ids\n",
        "\n",
        "%sql delete from collection_check where collection_id in :collection_ids\n",
        "\n",
        "%sql delete from field_counts where collection_id in :collection_ids\n",
        "\n",
        "%sql delete from project_fields where collection_id in :collection_ids"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "306V90ZjNe_S"
      },
      "source": [
        "Check project counts after deletion:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8S09Zz0gL1sv",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select collection_id, source_id, data_version, count(*) from projects join collection on projects.collection_id = collection.id group by collection_id, source_id, data_version order by collection_id desc;"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "name": "OC4IDS Database - Delete Collections",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}