3 # implements support for geni records stored in db tables
5 # TODO: Use existing PLC database methods? or keep this separate?
9 from pg import DB, ProgrammingError
13 GENI_TABLE_PREFIX = "geni$"
16 def __init__(self, create=False, hrn="unspecified.default.registry", cninfo=None):
17 global GENI_TABLE_PREFIX
21 # pgsql doesn't like table names with "." in them, to replace it with "$"
22 self.tablename = GENI_TABLE_PREFIX + self.hrn.replace(".", "$")
24 # establish a connection to the pgsql server
25 self.cnx = DB(cninfo['dbname'], cninfo['address'], port=cninfo['port'], user=cninfo['user'], passwd=cninfo['password'])
27 # if asked to create the table, then create it
32 tableList = self.cnx.get_tables()
33 if 'public.' + self.tablename in tableList:
35 if 'public."' + self.tablename + '"' in tableList:
40 querystr = "CREATE TABLE " + self.tablename + " ( \
47 # IF EXISTS doenst exist in postgres < 8.2
49 self.cnx.query('DROP TABLE IF EXISTS ' + self.tablename)
50 except ProgrammingError:
52 self.cnx.query('DROP TABLE ' + self.tablename)
53 except ProgrammingError:
56 self.cnx.query(querystr)
58 def remove(self, record):
59 query_str = "DELETE FROM " + self.tablename + " WHERE key = '" + record.get_key() + "'"
60 self.cnx.query(query_str)
62 def insert(self, record):
63 fieldnames = ["key"] + record.get_field_names()
64 fieldvals = record.get_field_value_strings(fieldnames)
65 query_str = "INSERT INTO " + self.tablename + \
66 "(" + ",".join(fieldnames) + ") " + \
67 "VALUES(" + ",".join(fieldvals) + ")"
69 self.cnx.query(query_str)
71 def update(self, record):
72 names = record.get_field_names()
75 val = record.get_field_value_string(name)
76 pairs.append(name + " = " + val)
77 update = ", ".join(pairs)
79 query_str = "UPDATE " + self.tablename+ " SET " + update + " WHERE key = '" + record.get_key() + "'"
81 self.cnx.query(query_str)
83 def find_dict(self, type, value, searchfield):
84 query_str = "SELECT * FROM " + self.tablename + " WHERE " + searchfield + " = '" + str(value) + "'"
85 dict_list = self.cnx.query(query_str).dictresult()
87 for dict in dict_list:
88 if (type=="*") or (dict['type'] == type):
89 result_dict_list.append(dict)
90 return result_dict_list
92 def find(self, type, value, searchfield):
93 result_dict_list = self.find_dict(type, value, searchfield)
95 for dict in result_dict_list:
96 result_rec_list.append(GeniRecord(dict=dict))
97 return result_rec_list
99 def resolve_dict(self, type, hrn):
100 return self.find_dict(type, hrn, "name")
102 def resolve(self, type, hrn):
103 return self.find(type, hrn, "name")
106 query_str = "SELECT * FROM " + self.tablename
107 result_dict_list = self.cnx.query(query_str).dictresult()
108 return result_dict_list
111 result_dict_list = self.list_dict()
113 for dict in result_dict_list:
114 result_rec_list.append(GeniRecord(dict=dict))
115 return result_rec_list
117 def set_geni_table_prefix(x):
118 global GENI_TABLE_PREFIX
120 GENI_TABLE_PREFIX = x
122 def geni_records_purge(cninfo):
123 global GENI_TABLE_PREFIX
125 cnx = DB(cninfo['dbname'], cninfo['address'], port=cninfo['port'], user=cninfo['user'], passwd=cninfo['password'])
126 tableList = cnx.get_tables()
127 for table in tableList:
128 if table.startswith(GENI_TABLE_PREFIX) or \
129 table.startswith('public.' + GENI_TABLE_PREFIX) or \
130 table.startswith('public."' + GENI_TABLE_PREFIX):
131 report.trace("dropping table " + table)
132 cnx.query("DROP TABLE " + table)