make_database.py

   1
   2 # coding: utf-8
   3
   4 # In[1]:
   5
   6 import configparser
   7 import psycopg2
   8
   9
  10 # In[2]:
  11
  12 field_maps = {'./policy_published/adkim': {'pg_field_name': 'policy_published_adkim',
  13   'pg_table': 'reports',
  14   'pg_type': 'varchar'},
  15  './policy_published/aspf': {'pg_field_name': 'policy_published_aspf',
  16   'pg_table': 'reports',
  17   'pg_type': 'varchar'},
  18  './policy_published/domain': {'pg_field_name': 'policy_published_domain',
  19   'pg_table': 'reports',
  20   'pg_type': 'varchar'},
  21  './policy_published/p': {'pg_field_name': 'policy_published_p',
  22   'pg_table': 'reports',
  23   'pg_type': 'varchar'},
  24  './policy_published/pct': {'pg_field_name': 'policy_published_pct',
  25   'pg_table': 'reports',
  26   'pg_type': 'int'},
  27  './record[{}]/auth_results/dkim/domain': {'pg_field_name': 'auth_results_dkim_domain',
  28   'pg_table': 'report_items',
  29   'pg_type': 'varchar'},
  30  './record[{}]/auth_results/dkim/result': {'pg_field_name': 'auth_results_dkim_result',
  31   'pg_table': 'report_items',
  32   'pg_type': 'varchar'},
  33  './record[{}]/auth_results/spf/domain': {'pg_field_name': 'auth_results_spf_domain',
  34   'pg_table': 'report_items',
  35   'pg_type': 'varchar'},
  36  './record[{}]/auth_results/spf/result': {'pg_field_name': 'auth_results_spf_result',
  37   'pg_table': 'report_items',
  38   'pg_type': 'varchar'},
  39  './record[{}]/identifiers/header_from': {'pg_field_name': 'identifiers_header_from',
  40   'pg_table': 'report_items',
  41   'pg_type': 'varchar'},
  42  './record[{}]/row/count': {'pg_field_name': 'count',
  43   'pg_table': 'report_items',
  44   'pg_type': 'int'},
  45  './record[{}]/row/policy_evaluated/disposition': {'pg_field_name': 'policy_evaluated_disposition',
  46   'pg_table': 'report_items',
  47   'pg_type': 'varchar'},
  48  './record[{}]/row/policy_evaluated/dkim': {'pg_field_name': 'policy_evaluated_dkim',
  49   'pg_table': 'report_items',
  50   'pg_type': 'varchar'},
  51  './record[{}]/row/policy_evaluated/spf': {'pg_field_name': 'policy_evaluated_spf',
  52   'pg_table': 'report_items',
  53   'pg_type': 'varchar'},
  54  './record[{}]/row/source_ip': {'pg_field_name': 'source_ip',
  55   'pg_table': 'report_items',
  56   'pg_type': 'inet'},
  57  './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',
  58   'pg_table': 'reports',
  59   'pg_type': 'timestamptz'},
  60  './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',
  61   'pg_table': 'reports',
  62   'pg_type': 'timestamptz'},
  63  './report_metadata/email': {'pg_field_name': 'report_metadata_email',
  64   'pg_table': 'reports',
  65   'pg_type': 'varchar'},
  66  './report_metadata/org_name': {'pg_field_name': 'report_metadata_org_name',
  67   'pg_table': 'reports',
  68   'pg_type': 'varchar'},
  69  './report_metadata/report_id': {'pg_field_name': 'report_metadata_report_id',
  70   'pg_table': 'reports',
  71   'pg_type': 'varchar'}}
  72
  73
  74 # In[3]:
  75
  76 config = configparser.ConfigParser()
  77 config.read('dmarc.ini')
  78
  79
  80 # In[8]:
  81
  82 conn = psycopg2.connect(host=config['database']['server'],
  83                         database=config['database']['database'],
  84                         user=config['database']['username'],
  85                         password=config['database']['password'])
  86
  87
  88 # In[9]:
  89
  90 with conn.cursor() as cur:
  91     conn.autocommit = True
  92     cur.execute("select exists(select * from information_schema.tables where table_name=%s)", ('report_items',))
  93     if cur.fetchone()[0]:
  94         cur.execute("drop table report_items;")
  95     cur.execute("select exists(select * from information_schema.tables where table_name=%s)", ('reports',))
  96     if cur.fetchone()[0]:
  97         cur.execute("drop table reports;")
  98
  99
 100 # In[10]:
 101
 102 create_report_table_string = 'create table reports (id serial primary key, ' + ', '.join(field_maps[p]['pg_field_name'] + ' ' + field_maps[p]['pg_type']
 103  for p in field_maps if field_maps[p]['pg_table'] == 'reports') + \
 104 ');'
 105 create_report_table_string
 106
 107
 108 # In[11]:
 109
 110 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']
 111  for p in field_maps if field_maps[p]['pg_table'] == 'report_items') + \
 112 ');'
 113 create_report_item_table_string
 114
 115
 116 # In[12]:
 117
 118 with conn.cursor() as cur:
 119     cur.execute(create_report_table_string)
 120     cur.execute(create_report_item_table_string)
 121     cur.execute('create index on reports (report_metadata_date_range_end);')
 122     cur.execute('create index on reports (report_metadata_report_id);')
 123 conn.commit()
 124
 125
 126 # In[ ]:
 127
 128
 129