X-Git-Url: https://git.njae.me.uk/?a=blobdiff_plain;f=make_database.ipynb;fp=make_database.ipynb;h=895bed8f6ff58a682113ff62c2dc1d70c063e8fc;hb=4ab690d3677c721d3d7770cd583dedc90f0a061c;hp=0000000000000000000000000000000000000000;hpb=cbc82ab0d7821ee263e1eabf32b1f932810775a2;p=dmarc.git

diff --git a/make_database.ipynb b/make_database.ipynb
new file mode 100644
index 0000000..895bed8
--- /dev/null
+++ b/make_database.ipynb
@@ -0,0 +1,238 @@
+{
+ "cells": [
+  {
+   "cell_type": "code",
+   "execution_count": 22,
+   "metadata": {
+    "collapsed": true
+   },
+   "outputs": [],
+   "source": [
+    "import configparser\n",
+    "import psycopg2"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 23,
+   "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': 'timestamp'},\n",
+    " './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',\n",
+    "  'pg_table': 'reports',\n",
+    "  'pg_type': 'timestamp'},\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": 24,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "['dmarc.ini']"
+      ]
+     },
+     "execution_count": 24,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "config = configparser.ConfigParser()\n",
+    "config.read('dmarc.ini')"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 25,
+   "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": 26,
+   "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": 27,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "'create table reports (id serial primary key, policy_published_pct int, policy_published_adkim varchar, report_metadata_org_name varchar, policy_published_aspf varchar, policy_published_domain varchar, report_metadata_date_range_end timestamp, policy_published_p varchar, report_metadata_report_id varchar, report_metadata_email varchar, report_metadata_date_range_begin timestamp);'"
+      ]
+     },
+     "execution_count": 27,
+     "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": 28,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "'create table report_items (id serial primary key, report_id integer references reports, auth_results_spf_domain varchar, source_ip inet, policy_evaluated_spf varchar, count int, auth_results_dkim_result varchar, auth_results_dkim_domain varchar, policy_evaluated_disposition varchar, identifiers_header_from varchar, policy_evaluated_dkim varchar, auth_results_spf_result varchar);'"
+      ]
+     },
+     "execution_count": 28,
+     "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": 29,
+   "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",
+    "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.4.3+"
+  }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 0
+}