-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathbgpstream_sqlite_mgmt.py
executable file
·173 lines (152 loc) · 6.88 KB
/
bgpstream_sqlite_mgmt.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
#
# This file is part of bgpstream
#
# CAIDA, UC San Diego
# bgpstream-info@caida.org
#
# Copyright (C) 2015 The Regents of the University of California.
# Authors: Alistair King, Chiara Orsini
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation; either version 2 of the License, or (at your option) any later
# version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
# details.
#
# You should have received a copy of the GNU General Public License along with
# this program. If not, see <http://www.gnu.org/licenses/>.
#
#!/usr/bin/env python
import sqlite3
import argparse
def create_tables(db_conn):
c = db_conn.cursor()
c.execute('''SELECT name FROM sqlite_master WHERE type='table' AND name='bgp_data' ''')
if len(c.fetchall()) == 0:
c.execute('''CREATE TABLE bgp_data
(collector_id integer,
type_id integer,
file_time timestamp,
file_path text,
ts timestamp default (strftime('%s', 'now')),
PRIMARY KEY(collector_id, type_id, file_time))''')
c.execute('''SELECT name FROM sqlite_master WHERE type='table' AND name='collectors' ''')
if len(c.fetchall()) == 0:
c.execute('''CREATE TABLE collectors
(id integer PRIMARY KEY,
project text,
name text)''')
c.execute('''SELECT name FROM sqlite_master WHERE type='table' AND name='bgp_types' ''')
if len(c.fetchall()) == 0:
c.execute('''CREATE TABLE bgp_types
(id integer PRIMARY KEY,
name text)''')
c.execute("INSERT INTO bgp_types VALUES ('1','ribs')")
c.execute("INSERT INTO bgp_types VALUES ('2','updates')")
c.execute('''SELECT name FROM sqlite_master WHERE type='table' AND name='time_span' ''')
if len(c.fetchall()) == 0:
c.execute('''CREATE TABLE time_span
(collector_id integer,
bgp_type_id integer,
time_span integer,
PRIMARY KEY(collector_id, bgp_type_id))''')
db_conn.commit()
def add_new_bgp_data(db_conn, mrt_file, project, collector, bgp_type, file_time, updates_time_span):
c = db_conn.cursor()
col_id = 0
# get the collector id (or create a new one if it doesn't exist)
c.execute('''SELECT id FROM collectors WHERE project=? AND name=?''',
[project, collector])
res = c.fetchone()
if res is None:
c.execute('''SELECT count(*) FROM collectors''')
col_id = c.fetchone()[0] + 1
c.execute('''INSERT INTO collectors VALUES(?,?,?)''', [col_id, project, collector])
# time span for ribs is constant and set to 120
c.execute('''INSERT OR REPLACE INTO time_span VALUES(?,1,120)''',[col_id])
else:
col_id = res[0]
# Set or update the time span information
c.execute('''SELECT time_span.time_span FROM time_span WHERE collector_id = ? AND bgp_type_id = 2''', [col_id])
res = c.fetchone()
# the policy used is that the largest update time span wins
if res is None or res[0] < updates_time_span:
c.execute('''INSERT OR REPLACE INTO time_span VALUES(?,2,?)''',
[col_id, updates_time_span])
# check/get type id
c.execute('''SELECT id FROM bgp_types WHERE name=? ''', [bgp_type])
res = c.fetchone()
if not res[0]:
print "bgp type " + bgp_type + " not supported!"
db_conn.commit()
return 0
else:
type_id = res[0]
# insert or replace bgp_data information
c.execute('''INSERT OR REPLACE INTO bgp_data
(collector_id, type_id, file_time, file_path)
VALUES(?,?,?,?)''', [col_id, type_id, file_time, mrt_file])
db_conn.commit()
return 1
def list_all_files(db_conn):
c = db_conn.cursor()
c.execute('''SELECT collectors.project, collectors.name,
bgp_types.name, time_span.time_span,
bgp_data.file_time, bgp_data.file_path, bgp_data.ts
FROM collectors JOIN bgp_data JOIN bgp_types JOIN time_span
WHERE bgp_data.collector_id = collectors.id AND
bgp_data.collector_id = time_span.collector_id AND
bgp_data.type_id = bgp_types.id AND
bgp_data.type_id = time_span.bgp_type_id
''')
res = c.fetchall()
print "Files in database: " + str(len(res))
for line in res:
print line
parser = argparse.ArgumentParser()
parser.add_argument("sqlite_db", help="file containing the sqlite database",
type=str)
parser.add_argument("-l","--list_files", help="list the mrt files in the database",
action="store_true")
parser.add_argument("-M","--add_mrt_file", help="path to the mrt file to add to the database",
default=None, action="store",type=str)
parser.add_argument("-p","--proj", help="bgp project",
default=None, action='store',type=str)
parser.add_argument("-c","--coll", help="bgp collector",
default=None, action='store',type=str)
parser.add_argument("-t","--bgp_type", help="bgp type",
default=None, action='store',type=str)
parser.add_argument("-T","--file_time", help="time associated with the mrt file",
default=-1, action='store',type=int)
parser.add_argument("-u","--updates_time_span", help="updates time span",
default=-1, action='store',type=int)
args = parser.parse_args()
# connect to the database
conn = sqlite3.connect(args.sqlite_db)
# create tables (if they do not exist)
create_tables(conn)
if not args.list_files and not args.add_mrt_file:
print "No actions required, creating the database file " + args.sqlite_db
if args.list_files:
# output the list of files
list_all_files(conn)
# Add a new mrt file to the database
if args.add_mrt_file:
action_result = 0
if args.proj and args.coll and args.bgp_type and args.file_time:
if args.bgp_type == "updates" and args.updates_time_span == -1:
print "Could not add mrt file: please provide time span for the current update file"
else:
res = add_new_bgp_data(conn, args.add_mrt_file, args.proj, args.coll,
args.bgp_type, args.file_time, args.updates_time_span)
if res != 1:
"Could not add mrt file: wrong parameters"
else:
print "Could not add mrt file: please provide project, collector, bgp type, and file time"
# # We can also close the connection if we are done with it.
# # Just be sure any changes have been committed or they will be lost.
conn.close()