make_database.ipynb

   1 {
   2  "cells": [
   3   {
   4    "cell_type": "code",
   5    "execution_count": 1,
   6    "metadata": {
   7     "collapsed": true
   8    },
   9    "outputs": [],
  10    "source": [
  11     "import configparser\n",
  12     "import psycopg2"
  13    ]
  14   },
  15   {
  16    "cell_type": "code",
  17    "execution_count": 2,
  18    "metadata": {
  19     "collapsed": true
  20    },
  21    "outputs": [],
  22    "source": [
  23     "field_maps = {'./policy_published/adkim': {'pg_field_name': 'policy_published_adkim',\n",
  24     "  'pg_table': 'reports',\n",
  25     "  'pg_type': 'varchar'},\n",
  26     " './policy_published/aspf': {'pg_field_name': 'policy_published_aspf',\n",
  27     "  'pg_table': 'reports',\n",
  28     "  'pg_type': 'varchar'},\n",
  29     " './policy_published/domain': {'pg_field_name': 'policy_published_domain',\n",
  30     "  'pg_table': 'reports',\n",
  31     "  'pg_type': 'varchar'},\n",
  32     " './policy_published/p': {'pg_field_name': 'policy_published_p',\n",
  33     "  'pg_table': 'reports',\n",
  34     "  'pg_type': 'varchar'},\n",
  35     " './policy_published/pct': {'pg_field_name': 'policy_published_pct',\n",
  36     "  'pg_table': 'reports',\n",
  37     "  'pg_type': 'int'},\n",
  38     " './record[{}]/auth_results/dkim/domain': {'pg_field_name': 'auth_results_dkim_domain',\n",
  39     "  'pg_table': 'report_items',\n",
  40     "  'pg_type': 'varchar'},\n",
  41     " './record[{}]/auth_results/dkim/result': {'pg_field_name': 'auth_results_dkim_result',\n",
  42     "  'pg_table': 'report_items',\n",
  43     "  'pg_type': 'varchar'},\n",
  44     " './record[{}]/auth_results/spf/domain': {'pg_field_name': 'auth_results_spf_domain',\n",
  45     "  'pg_table': 'report_items',\n",
  46     "  'pg_type': 'varchar'},\n",
  47     " './record[{}]/auth_results/spf/result': {'pg_field_name': 'auth_results_spf_result',\n",
  48     "  'pg_table': 'report_items',\n",
  49     "  'pg_type': 'varchar'},\n",
  50     " './record[{}]/identifiers/header_from': {'pg_field_name': 'identifiers_header_from',\n",
  51     "  'pg_table': 'report_items',\n",
  52     "  'pg_type': 'varchar'},\n",
  53     " './record[{}]/row/count': {'pg_field_name': 'count',\n",
  54     "  'pg_table': 'report_items',\n",
  55     "  'pg_type': 'int'},\n",
  56     " './record[{}]/row/policy_evaluated/disposition': {'pg_field_name': 'policy_evaluated_disposition',\n",
  57     "  'pg_table': 'report_items',\n",
  58     "  'pg_type': 'varchar'},\n",
  59     " './record[{}]/row/policy_evaluated/dkim': {'pg_field_name': 'policy_evaluated_dkim',\n",
  60     "  'pg_table': 'report_items',\n",
  61     "  'pg_type': 'varchar'},\n",
  62     " './record[{}]/row/policy_evaluated/spf': {'pg_field_name': 'policy_evaluated_spf',\n",
  63     "  'pg_table': 'report_items',\n",
  64     "  'pg_type': 'varchar'},\n",
  65     " './record[{}]/row/source_ip': {'pg_field_name': 'source_ip',\n",
  66     "  'pg_table': 'report_items',\n",
  67     "  'pg_type': 'inet'},\n",
  68     " './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',\n",
  69     "  'pg_table': 'reports',\n",
  70     "  'pg_type': 'timestamptz'},\n",
  71     " './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',\n",
  72     "  'pg_table': 'reports',\n",
  73     "  'pg_type': 'timestamptz'},\n",
  74     " './report_metadata/email': {'pg_field_name': 'report_metadata_email',\n",
  75     "  'pg_table': 'reports',\n",
  76     "  'pg_type': 'varchar'},\n",
  77     " './report_metadata/org_name': {'pg_field_name': 'report_metadata_org_name',\n",
  78     "  'pg_table': 'reports',\n",
  79     "  'pg_type': 'varchar'},\n",
  80     " './report_metadata/report_id': {'pg_field_name': 'report_metadata_report_id',\n",
  81     "  'pg_table': 'reports',\n",
  82     "  'pg_type': 'varchar'}}"
  83    ]
  84   },
  85   {
  86    "cell_type": "code",
  87    "execution_count": 3,
  88    "metadata": {
  89     "collapsed": false
  90    },
  91    "outputs": [
  92     {
  93      "data": {
  94       "text/plain": [
  95        "['dmarc.ini']"
  96       ]
  97      },
  98      "execution_count": 3,
  99      "metadata": {},
 100      "output_type": "execute_result"
 101     }
 102    ],
 103    "source": [
 104     "config = configparser.ConfigParser()\n",
 105     "config.read('dmarc.ini')"
 106    ]
 107   },
 108   {
 109    "cell_type": "code",
 110    "execution_count": 8,
 111    "metadata": {
 112     "collapsed": false
 113    },
 114    "outputs": [],
 115    "source": [
 116     "conn = psycopg2.connect(host=config['database']['server'],\n",
 117     "                        database=config['database']['database'], \n",
 118     "                        user=config['database']['username'], \n",
 119     "                        password=config['database']['password'])"
 120    ]
 121   },
 122   {
 123    "cell_type": "code",
 124    "execution_count": 9,
 125    "metadata": {
 126     "collapsed": false
 127    },
 128    "outputs": [],
 129    "source": [
 130     "with conn.cursor() as cur:\n",
 131     "    conn.autocommit = True\n",
 132     "    cur.execute(\"select exists(select * from information_schema.tables where table_name=%s)\", ('report_items',))\n",
 133     "    if cur.fetchone()[0]:\n",
 134     "        cur.execute(\"drop table report_items;\")\n",
 135     "    cur.execute(\"select exists(select * from information_schema.tables where table_name=%s)\", ('reports',))\n",
 136     "    if cur.fetchone()[0]:\n",
 137     "        cur.execute(\"drop table reports;\")"
 138    ]
 139   },
 140   {
 141    "cell_type": "code",
 142    "execution_count": 10,
 143    "metadata": {
 144     "collapsed": false
 145    },
 146    "outputs": [
 147     {
 148      "data": {
 149       "text/plain": [
 150        "'create table reports (id serial primary key, policy_published_domain varchar, policy_published_pct int, report_metadata_report_id varchar, report_metadata_date_range_end timestamptz, report_metadata_org_name varchar, report_metadata_date_range_begin timestamptz, policy_published_aspf varchar, report_metadata_email varchar, policy_published_p varchar, policy_published_adkim varchar);'"
 151       ]
 152      },
 153      "execution_count": 10,
 154      "metadata": {},
 155      "output_type": "execute_result"
 156     }
 157    ],
 158    "source": [
 159     "create_report_table_string = 'create table reports (id serial primary key, ' + \\\n",
 160     "', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] \n",
 161     " for p in field_maps if field_maps[p]['pg_table'] == 'reports') + \\\n",
 162     "');'\n",
 163     "create_report_table_string"
 164    ]
 165   },
 166   {
 167    "cell_type": "code",
 168    "execution_count": 11,
 169    "metadata": {
 170     "collapsed": false
 171    },
 172    "outputs": [
 173     {
 174      "data": {
 175       "text/plain": [
 176        "'create table report_items (id serial primary key, report_id integer references reports, auth_results_dkim_domain varchar, policy_evaluated_dkim varchar, policy_evaluated_spf varchar, policy_evaluated_disposition varchar, source_ip inet, auth_results_dkim_result varchar, count int, auth_results_spf_result varchar, auth_results_spf_domain varchar, identifiers_header_from varchar);'"
 177       ]
 178      },
 179      "execution_count": 11,
 180      "metadata": {},
 181      "output_type": "execute_result"
 182     }
 183    ],
 184    "source": [
 185     "create_report_item_table_string = 'create table report_items (id serial primary key, ' + \\\n",
 186     "  'report_id integer references reports, ' + \\\n",
 187     "', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] \n",
 188     " for p in field_maps if field_maps[p]['pg_table'] == 'report_items') + \\\n",
 189     "');'\n",
 190     "create_report_item_table_string"
 191    ]
 192   },
 193   {
 194    "cell_type": "code",
 195    "execution_count": 12,
 196    "metadata": {
 197     "collapsed": true
 198    },
 199    "outputs": [],
 200    "source": [
 201     "with conn.cursor() as cur:\n",
 202     "    cur.execute(create_report_table_string)\n",
 203     "    cur.execute(create_report_item_table_string)\n",
 204     "    cur.execute('create index on reports (report_metadata_date_range_end);')\n",
 205     "    cur.execute('create index on reports (report_metadata_report_id);')\n",
 206     "conn.commit()"
 207    ]
 208   },
 209   {
 210    "cell_type": "code",
 211    "execution_count": null,
 212    "metadata": {
 213     "collapsed": true
 214    },
 215    "outputs": [],
 216    "source": []
 217   }
 218  ],
 219  "metadata": {
 220   "kernelspec": {
 221    "display_name": "Python 3",
 222    "language": "python",
 223    "name": "python3"
 224   },
 225   "language_info": {
 226    "codemirror_mode": {
 227     "name": "ipython",
 228     "version": 3
 229    },
 230    "file_extension": ".py",
 231    "mimetype": "text/x-python",
 232    "name": "python",
 233    "nbconvert_exporter": "python",
 234    "pygments_lexer": "ipython3",
 235    "version": "3.5.2+"
 236   }
 237  },
 238  "nbformat": 4,
 239  "nbformat_minor": 0
 240 }