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:
41 querystr = "CREATE TABLE " + self.tablename + " ( \
47 template = "CREATE INDEX %s_%s_idx ON %s (%s);"
48 indexes = [template % ( self.tablename, field, self.tablename, field) \
49 for field in ['name', 'type' ]]
50 # IF EXISTS doenst exist in postgres < 8.2
52 self.cnx.query('DROP TABLE IF EXISTS ' + self.tablename)
53 except ProgrammingError:
55 self.cnx.query('DROP TABLE ' + self.tablename)
56 except ProgrammingError:
59 self.cnx.query(querystr)
63 def remove(self, record):
64 query_str = "DELETE FROM " + self.tablename + " WHERE key = '" + record.get_key() + "'"
65 self.cnx.query(query_str)
67 def insert(self, record):
68 fieldnames = ["key"] + record.get_field_names()
69 fieldvals = record.get_field_value_strings(fieldnames)
70 query_str = "INSERT INTO " + self.tablename + \
71 "(" + ",".join(fieldnames) + ") " + \
72 "VALUES(" + ",".join(fieldvals) + ")"
74 self.cnx.query(query_str)
76 def update(self, record):
77 names = record.get_field_names()
80 val = record.get_field_value_string(name)
81 pairs.append(name + " = " + val)
82 update = ", ".join(pairs)
84 query_str = "UPDATE " + self.tablename+ " SET " + update + " WHERE key = '" + record.get_key() + "'"
86 self.cnx.query(query_str)
88 def find_dict(self, type, value, searchfield):
89 query_str = "SELECT * FROM " + self.tablename + " WHERE " + searchfield + " = '" + str(value) + "'"
90 dict_list = self.cnx.query(query_str).dictresult()
92 for dict in dict_list:
93 if (type=="*") or (dict['type'] == type):
94 result_dict_list.append(dict)
95 return result_dict_list
97 def find(self, type, value, searchfield):
98 result_dict_list = self.find_dict(type, value, searchfield)
100 for dict in result_dict_list:
101 result_rec_list.append(GeniRecord(dict=dict))
102 return result_rec_list
104 def resolve_dict(self, type, hrn):
105 return self.find_dict(type, hrn, "name")
107 def resolve(self, type, hrn):
108 return self.find(type, hrn, "name")
111 query_str = "SELECT * FROM " + self.tablename
112 result_dict_list = self.cnx.query(query_str).dictresult()
113 return result_dict_list
116 result_dict_list = self.list_dict()
118 for dict in result_dict_list:
119 result_rec_list.append(GeniRecord(dict=dict))
120 return result_rec_list
122 def set_geni_table_prefix(x):
123 global GENI_TABLE_PREFIX
125 GENI_TABLE_PREFIX = x
127 def geni_records_purge(cninfo):
128 global GENI_TABLE_PREFIX
130 cnx = DB(cninfo['dbname'], cninfo['address'], port=cninfo['port'], user=cninfo['user'], passwd=cninfo['password'])
131 tableList = cnx.get_tables()
132 for table in tableList:
133 if table.startswith(GENI_TABLE_PREFIX) or \
134 table.startswith('public.' + GENI_TABLE_PREFIX) or \
135 table.startswith('public."' + GENI_TABLE_PREFIX):
136 report.trace("dropping table " + table)
137 cnx.query("DROP TABLE " + table)