{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import configparser\n",
    "import psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "field_maps = {'./policy_published/adkim': {'pg_field_name': 'policy_published_adkim',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'varchar'},\n",
    " './policy_published/aspf': {'pg_field_name': 'policy_published_aspf',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'varchar'},\n",
    " './policy_published/domain': {'pg_field_name': 'policy_published_domain',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'varchar'},\n",
    " './policy_published/p': {'pg_field_name': 'policy_published_p',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'varchar'},\n",
    " './policy_published/pct': {'pg_field_name': 'policy_published_pct',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'int'},\n",
    " './record[{}]/auth_results/dkim/domain': {'pg_field_name': 'auth_results_dkim_domain',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'varchar'},\n",
    " './record[{}]/auth_results/dkim/result': {'pg_field_name': 'auth_results_dkim_result',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'varchar'},\n",
    " './record[{}]/auth_results/spf/domain': {'pg_field_name': 'auth_results_spf_domain',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'varchar'},\n",
    " './record[{}]/auth_results/spf/result': {'pg_field_name': 'auth_results_spf_result',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'varchar'},\n",
    " './record[{}]/identifiers/header_from': {'pg_field_name': 'identifiers_header_from',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'varchar'},\n",
    " './record[{}]/row/count': {'pg_field_name': 'count',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'int'},\n",
    " './record[{}]/row/policy_evaluated/disposition': {'pg_field_name': 'policy_evaluated_disposition',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'varchar'},\n",
    " './record[{}]/row/policy_evaluated/dkim': {'pg_field_name': 'policy_evaluated_dkim',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'varchar'},\n",
    " './record[{}]/row/policy_evaluated/spf': {'pg_field_name': 'policy_evaluated_spf',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'varchar'},\n",
    " './record[{}]/row/source_ip': {'pg_field_name': 'source_ip',\n",
    "  'pg_table': 'report_items',\n",
    "  'pg_type': 'inet'},\n",
    " './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'timestamptz'},\n",
    " './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'timestamptz'},\n",
    " './report_metadata/email': {'pg_field_name': 'report_metadata_email',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'varchar'},\n",
    " './report_metadata/org_name': {'pg_field_name': 'report_metadata_org_name',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'varchar'},\n",
    " './report_metadata/report_id': {'pg_field_name': 'report_metadata_report_id',\n",
    "  'pg_table': 'reports',\n",
    "  'pg_type': 'varchar'}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['dmarc.ini']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('dmarc.ini')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(host=config['database']['server'],\n",
    "                        database=config['database']['database'], \n",
    "                        user=config['database']['username'], \n",
    "                        password=config['database']['password'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with conn.cursor() as cur:\n",
    "    conn.autocommit = True\n",
    "    cur.execute(\"select exists(select * from information_schema.tables where table_name=%s)\", ('report_items',))\n",
    "    if cur.fetchone()[0]:\n",
    "        cur.execute(\"drop table report_items;\")\n",
    "    cur.execute(\"select exists(select * from information_schema.tables where table_name=%s)\", ('reports',))\n",
    "    if cur.fetchone()[0]:\n",
    "        cur.execute(\"drop table reports;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'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);'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_report_table_string = 'create table reports (id serial primary key, ' + \\\n",
    "', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] \n",
    " for p in field_maps if field_maps[p]['pg_table'] == 'reports') + \\\n",
    "');'\n",
    "create_report_table_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'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);'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_report_item_table_string = 'create table report_items (id serial primary key, ' + \\\n",
    "  'report_id integer references reports, ' + \\\n",
    "', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] \n",
    " for p in field_maps if field_maps[p]['pg_table'] == 'report_items') + \\\n",
    "');'\n",
    "create_report_item_table_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with conn.cursor() as cur:\n",
    "    cur.execute(create_report_table_string)\n",
    "    cur.execute(create_report_item_table_string)\n",
    "    cur.execute('create index on reports (report_metadata_date_range_end);')\n",
    "    cur.execute('create index on reports (report_metadata_report_id);')\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2+"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}