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
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
|
# $Id$
import MySQLdb, rpki.x509
def connect(cfg, section="sql"):
"""Connect to a MySQL database using connection parameters from an
rpki.config.parser object.
"""
return MySQLdb.connect(user = cfg.get(section, "sql-username"),
db = cfg.get(section, "sql-database"),
passwd = cfg.get(section, "sql-password"))
class template(object):
"""SQL template generator."""
def __init__(self, table_name, *columns):
index_column = columns[0]
data_columns = columns[1:]
self.table = table_name
self.index = index_column
self.columns = columns
self.select = "SELECT %s FROM %s" % (", ".join(columns), table_name)
self.insert = "INSERT %s (%s) VALUES (%s)" % (table_name, ", ".join(data_columns), ", ".join("%(" + s + ")s" for s in data_columns))
self.update = "UPDATE %s SET %s WHERE %s = %%(%s)s" % (table_name, ", ".join(s + " = %(" + s + ")s" for s in data_columns), index_column, index_column)
self.delete = "DELETE FROM %s WHERE %s = %%s" % (table_name, index_column)
## @var sql_cache
# Cache of objects pulled from SQL.
sql_cache = {}
def sql_cache_clear():
"""Clear the object cache."""
sql_cache = {}
def sql_cache_assert_pristine():
"""Assert that there are no dirty objects in the cache."""
dirty = [obj for obj in sql_cache.values() if obj.sql_dirty]
assert not dirty, "Dirty objects in SQL cache: %s" % dirty
def fetch_column(cur, *query):
"""Pull a single column from SQL, return it as a list."""
cur.execute(*query)
return [x[0] for x in cur.fetchall()]
class sql_persistant(object):
"""Mixin for persistant class that needs to be stored in SQL.
"""
## @var sql_in_db
# Whether this object is already in SQL or not.
sql_in_db = False
## @var sql_dirty
# Whether this object has been modified and needs to be written back
# to SQL.
sql_dirty = False
@classmethod
def sql_fetch(cls, db, cur, id):
results = cls.sql_fetch_where(db, cur, "%s = %s" % (cls.sql_template.index, id))
assert len(results) <= 1
if len(results) == 0:
return None
elif len(results) == 1:
return results[0]
else:
raise rpki.exceptions.DBConsistancyError, "Database contained multiple matches for %s.%s" % (cls.__name__, id)
@classmethod
def sql_fetch_all(cls, db, cur):
return cls.sql_fetch_where(db, cur, None)
@classmethod
def sql_fetch_where(cls, db, cur, where):
if where is None:
cur.execute(cls.sql_template.select)
else:
cur.execute(cls.sql_template.select + " WHERE " + where)
results = []
for row in cur.fetchall():
key = (cls, row[0])
if key in sql_cache:
results.append(sql_cache[key])
else:
results.append(cls.sql_init(db, cur, row, key))
return results
@classmethod
def sql_init(cls, db, cur, row, key):
self = cls()
self.sql_decode(dict(zip(cls.sql_template.columns, row)))
sql_cache[key] = self
self.sql_dirty = False
self.sql_in_db = True
self.sql_fetch_hook(db, cur)
return self
def sql_store(self, db, cur):
if not self.sql_in_db:
cur.execute(self.sql_template.insert, self.sql_encode())
setattr(self, self.sql_template.index, cur.lastrowid)
sql_cache[(self.__class__, cur.lastrowid)] = self
self.sql_insert_hook(db, cur)
elif self.sql_dirty:
cur.execute(self.sql_template.update, self.sql_encode())
self.sql_update_hook(db, cur)
key = (self.__class__, getattr(self, self.sql_template.index))
assert key in sql_cache and sql_cache[key] == self
self.sql_dirty = False
self.sql_in_db = True
def sql_delete(self, db, cur):
if self.sql_in_db:
id = getattr(self, self.sql_template.index)
cur.execute(self.sql_template.delete, id)
self.sql_delete_hook(db, cur)
key = (self.__class__, id)
if sql_cache.get(key) == self:
del sql_cache[key]
self.sql_in_db = False
self.sql_dirty = False
def sql_encode(self):
"""Convert object attributes into a dict for use with canned SQL
queries. This is a default version that assumes a one-to-one
mapping between column names in SQL and attribute names in Python,
with no datatype conversion. If you need something fancier,
override this.
"""
return dict((a, getattr(self, a)) for a in self.sql_template.columns)
def sql_decode(self, vals):
"""Initialize an object with values returned by self.sql_fetch().
This is a default version that assumes a one-to-one mapping
between column names in SQL and attribute names in Python, with no
datatype conversion. If you need something fancier, override this.
"""
for a in self.sql_template.columns:
setattr(self, a, vals[a])
def sql_fetch_hook(self, db, cur):
"""Customization hook."""
pass
def sql_insert_hook(self, db, cur):
"""Customization hook."""
pass
def sql_update_hook(self, db, cur):
"""Customization hook."""
self.sql_delete_hook(db, cur)
self.sql_insert_hook(db, cur)
def sql_delete_hook(self, db, cur):
"""Customization hook."""
pass
# Some persistant objects are defined in rpki.left_right, since
# they're also left-right PDUs. The rest are defined below, for now.
class ca_obj(sql_persistant):
"""Internal CA object."""
sql_template = template("ca", "ca_id", "last_crl_sn", "next_crl_update", "last_issued_sn", "last_manifest_sn", "next_manifest_update", "sia_uri", "parent_id")
class ca_detail_obj(sql_persistant):
"""Internal CA detail object."""
sql_template = template("ca", "ca_detail_id", "private_key_id", "public_key", "latest_ca_cert", "manifest_private_key_id",
"manifest_public_key", "latest_manifest_cert", "latest_manifest", "latest_crl", "status", "ca_id")
def sql_decode(self, vals):
sql_persistant.sql_decode(self, vals)
self.private_key_id = rpki.x509.RSA_Keypair(DER = self.private_key_id)
assert self.public_key is None or self.private_key_id.get_public_DER() == self.public_key
self.latest_ca_cert = rpki.x509.X509(DER = self.latest_ca_cert)
self.manifest_private_key_id = rpki.x509.RSA_Keypair(DER = self.manifest_private_key_id)
assert self.manifest_public_key is None or self.manifest_private_key_id.get_public_DER() == self.manifest_public_key
self.manifest_cert = rpki.x509.X509(DER = self.manifest_cert)
# todo: manifest, crl
def sql_encode(self):
d = sql_persistant.sql_encode(self)
d["private_key_id"] = self.private_key_id.get_DER()
d["latest_ca_cert"] = self.latest_ca_cert.get_DER()
d["manifest_private_key_id"] = self.manifest_private_key_id.get_DER()
d["manifest_cert"] = self.manifest_cert.get_DER()
return d
@classmethod
def sql_fetch_active(cls, db, cur, ca_id):
hits = cls.sql_fetch_where(db, cur, "ca_id = %s AND status = 'active'" % ca_id)
assert len(hits) < 2, "Found more than one 'active' ca_detail record, this should not happen!"
if hits:
return hits[0]
else:
return None
class child_cert_obj(sql_persistant):
"""Certificate that has been issued to a child."""
sql_template = template("child_cert", "child_cert_id", "cert", "child_id", "ca_detail_id")
def sql_decode(self, vals):
sql_persistant.sql_decode(self, vals)
self.cert = rpki.x509.X509(DER = self.cert)
def sql_encode(self):
d = sql_persistant.sql_encode(self)
d["cert"] = self.cert.get_DER()
return d
|
