3 # implements support for geni records stored in db tables
5 # TODO: Use existing PLC database methods? or keep this separate?
12 from pg import DB, ProgrammingError
14 from sfa.trust.gid import *
15 from sfa.util.record import *
16 from sfa.util.debug import *
20 GENI_TABLE_PREFIX = "sfa$"
22 def __init__(self, create=False, hrn="unspecified.default.registry", cninfo=None):
26 # pgsql doesn't like table names with "." in them, to replace it with "$"
27 self.tablename = GeniTable.GENI_TABLE_PREFIX + self.hrn.replace(".", "$")
29 # establish a connection to the pgsql server
30 self.cnx = DB(cninfo['dbname'], cninfo['address'], port=cninfo['port'], user=cninfo['user'], passwd=cninfo['password'])
32 # if asked to create the table, then create it
37 tableList = self.cnx.get_tables()
38 if 'public.' + self.tablename in tableList:
40 if 'public."' + self.tablename + '"' in tableList:
46 querystr = "CREATE TABLE " + self.tablename + " ( \
52 date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, \
53 last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP);"
54 template = "CREATE INDEX %s_%s_idx ON %s (%s);"
55 indexes = [template % ( self.tablename, field, self.tablename, field) \
56 for field in ['key', 'hrn', 'type','pointer']]
57 # IF EXISTS doenst exist in postgres < 8.2
59 self.cnx.query('DROP TABLE IF EXISTS ' + self.tablename)
60 except ProgrammingError:
62 self.cnx.query('DROP TABLE ' + self.tablename)
63 except ProgrammingError:
66 self.cnx.query(querystr)
70 def remove(self, record):
71 query_str = "DELETE FROM " + self.tablename + " WHERE key = '" + record.get_key() + "'"
72 self.cnx.query(query_str)
74 def insert(self, record):
75 dont_insert = ['date_created', 'last_updated']
76 fields = [field for field in record.fields.keys() if field not in dont_insert]
77 fieldnames = ["key", "pointer"] + fields
78 fieldvals = record.get_field_value_strings(fieldnames)
79 query_str = "INSERT INTO " + self.tablename + \
80 "(" + ",".join(fieldnames) + ") " + \
81 "VALUES(" + ",".join(fieldvals) + ")"
83 self.cnx.query(query_str)
85 def update(self, record):
86 dont_update = ['date_created', 'last_updated']
87 names = record.get_field_names()
88 names = [name for name in names if name not in dont_update]
91 val = record.get_field_value_string(name)
92 pairs.append(name + " = " + val)
93 update = ", ".join(pairs)
95 query_str = "UPDATE " + self.tablename+ " SET " + update + " WHERE key = '" + record.get_key() + "'"
97 self.cnx.query(query_str)
99 def find_dict(self, type, value, searchfield):
100 query_str = "SELECT * FROM " + self.tablename + " WHERE " + searchfield + " = '" + str(value) + "'"
101 dict_list = self.cnx.query(query_str).dictresult()
102 result_dict_list = []
103 for dict in dict_list:
104 if (type=="*") or (dict['type'] == type):
105 result_dict_list.append(dict)
106 return result_dict_list
108 def find(self, type, value, searchfield):
109 result_dict_list = self.find_dict(type, value, searchfield)
111 for result in result_dict_list:
112 if result['type'] in ['authority']:
113 result_rec_list.append(AuthorityRecord(dict=result))
114 elif result['type'] in ['node']:
115 result_rec_list.append(NodeRecord(dict=result))
116 elif result['type'] in ['slice']:
117 result_rec_list.append(SliceRecord(dict=result))
118 elif result['type'] in ['user']:
119 result_rec_list.append(UserRecord(dict=result))
121 result_rec_list.append(GeniRecord(dict=result))
122 return result_rec_list
124 def resolve_dict(self, type, hrn):
125 return self.find_dict(type, hrn, "hrn")
127 def resolve(self, type, hrn):
128 return self.find(type, hrn, "hrn")
131 query_str = "SELECT * FROM " + self.tablename
132 result_dict_list = self.cnx.query(query_str).dictresult()
133 return result_dict_list
136 result_dict_list = self.list_dict()
138 for dict in result_dict_list:
139 result_rec_list.append(GeniRecord(dict=dict).as_dict())
140 return result_rec_list
143 def geni_records_purge(cninfo):
145 cnx = DB(cninfo['dbname'], cninfo['address'],
146 port=cninfo['port'], user=cninfo['user'], passwd=cninfo['password'])
147 tableList = cnx.get_tables()
148 for table in tableList:
149 if table.startswith(GeniTable.GENI_TABLE_PREFIX) or \
150 table.startswith('public.' + GeniTable.GENI_TABLE_PREFIX) or \
151 table.startswith('public."' + GeniTable.GENI_TABLE_PREFIX):
152 report.trace("dropping table " + table)
153 cnx.query("DROP TABLE " + table)