Added standalone script to make database tables

[dmarc.git] / make_database.py

diff --git a/make_database.py b/make_database.py
new file mode 100644 (file)
index 0000000..ee955ec
--- /dev/null
+++ b/make_database.py
@@ -0,0 +1,129 @@
+
+# coding: utf-8
+
+# In[1]:
+
+import configparser
+import psycopg2
+
+
+# In[2]:
+
+field_maps = {'./policy_published/adkim': {'pg_field_name': 'policy_published_adkim',
+  'pg_table': 'reports',
+  'pg_type': 'varchar'},
+ './policy_published/aspf': {'pg_field_name': 'policy_published_aspf',
+  'pg_table': 'reports',
+  'pg_type': 'varchar'},
+ './policy_published/domain': {'pg_field_name': 'policy_published_domain',
+  'pg_table': 'reports',
+  'pg_type': 'varchar'},
+ './policy_published/p': {'pg_field_name': 'policy_published_p',
+  'pg_table': 'reports',
+  'pg_type': 'varchar'},
+ './policy_published/pct': {'pg_field_name': 'policy_published_pct',
+  'pg_table': 'reports',
+  'pg_type': 'int'},
+ './record[{}]/auth_results/dkim/domain': {'pg_field_name': 'auth_results_dkim_domain',
+  'pg_table': 'report_items',
+  'pg_type': 'varchar'},
+ './record[{}]/auth_results/dkim/result': {'pg_field_name': 'auth_results_dkim_result',
+  'pg_table': 'report_items',
+  'pg_type': 'varchar'},
+ './record[{}]/auth_results/spf/domain': {'pg_field_name': 'auth_results_spf_domain',
+  'pg_table': 'report_items',
+  'pg_type': 'varchar'},
+ './record[{}]/auth_results/spf/result': {'pg_field_name': 'auth_results_spf_result',
+  'pg_table': 'report_items',
+  'pg_type': 'varchar'},
+ './record[{}]/identifiers/header_from': {'pg_field_name': 'identifiers_header_from',
+  'pg_table': 'report_items',
+  'pg_type': 'varchar'},
+ './record[{}]/row/count': {'pg_field_name': 'count',
+  'pg_table': 'report_items',
+  'pg_type': 'int'},
+ './record[{}]/row/policy_evaluated/disposition': {'pg_field_name': 'policy_evaluated_disposition',
+  'pg_table': 'report_items',
+  'pg_type': 'varchar'},
+ './record[{}]/row/policy_evaluated/dkim': {'pg_field_name': 'policy_evaluated_dkim',
+  'pg_table': 'report_items',
+  'pg_type': 'varchar'},
+ './record[{}]/row/policy_evaluated/spf': {'pg_field_name': 'policy_evaluated_spf',
+  'pg_table': 'report_items',
+  'pg_type': 'varchar'},
+ './record[{}]/row/source_ip': {'pg_field_name': 'source_ip',
+  'pg_table': 'report_items',
+  'pg_type': 'inet'},
+ './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',
+  'pg_table': 'reports',
+  'pg_type': 'timestamptz'},
+ './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',
+  'pg_table': 'reports',
+  'pg_type': 'timestamptz'},
+ './report_metadata/email': {'pg_field_name': 'report_metadata_email',
+  'pg_table': 'reports',
+  'pg_type': 'varchar'},
+ './report_metadata/org_name': {'pg_field_name': 'report_metadata_org_name',
+  'pg_table': 'reports',
+  'pg_type': 'varchar'},
+ './report_metadata/report_id': {'pg_field_name': 'report_metadata_report_id',
+  'pg_table': 'reports',
+  'pg_type': 'varchar'}}
+
+
+# In[3]:
+
+config = configparser.ConfigParser()
+config.read('dmarc.ini')
+
+
+# In[8]:
+
+conn = psycopg2.connect(host=config['database']['server'],
+                        database=config['database']['database'], 
+                        user=config['database']['username'], 
+                        password=config['database']['password'])
+
+
+# In[9]:
+
+with conn.cursor() as cur:
+    conn.autocommit = True
+    cur.execute("select exists(select * from information_schema.tables where table_name=%s)", ('report_items',))
+    if cur.fetchone()[0]:
+        cur.execute("drop table report_items;")
+    cur.execute("select exists(select * from information_schema.tables where table_name=%s)", ('reports',))
+    if cur.fetchone()[0]:
+        cur.execute("drop table reports;")
+
+
+# In[10]:
+
+create_report_table_string = 'create table reports (id serial primary key, ' + ', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] 
+ for p in field_maps if field_maps[p]['pg_table'] == 'reports') + \
+');'
+create_report_table_string
+
+
+# In[11]:
+
+create_report_item_table_string = 'create table report_items (id serial primary key, ' +   'report_id integer references reports, ' + ', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type'] 
+ for p in field_maps if field_maps[p]['pg_table'] == 'report_items') + \
+');'
+create_report_item_table_string
+
+
+# In[12]:
+
+with conn.cursor() as cur:
+    cur.execute(create_report_table_string)
+    cur.execute(create_report_item_table_string)
+    cur.execute('create index on reports (report_metadata_date_range_end);')
+    cur.execute('create index on reports (report_metadata_report_id);')
+conn.commit()
+
+
+# In[ ]:
+
+
+