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
12 from geni.util.debug import *
14 GENI_TABLE_PREFIX = "geni$"
17 def __init__(self, create=False, hrn="unspecified.default.registry", cninfo=None):
18 global GENI_TABLE_PREFIX
22 # pgsql doesn't like table names with "." in them, to replace it with "$"
23 self.tablename = GENI_TABLE_PREFIX + self.hrn.replace(".", "$")
25 # establish a connection to the pgsql server
26 self.cnx = DB(cninfo['dbname'], cninfo['address'], port=cninfo['port'], user=cninfo['user'], passwd=cninfo['password'])
28 # if asked to create the table, then create it
33 tableList = self.cnx.get_tables()
34 if 'public.' + self.tablename in tableList:
36 if 'public."' + self.tablename + '"' in tableList:
42 querystr = "CREATE TABLE " + self.tablename + " ( \
48 template = "CREATE INDEX %s_%s_idx ON %s (%s);"
49 indexes = [template % ( self.tablename, field, self.tablename, field) \
50 for field in ['name', 'type' ]]
51 # IF EXISTS doenst exist in postgres < 8.2
53 self.cnx.query('DROP TABLE IF EXISTS ' + self.tablename)
54 except ProgrammingError:
56 self.cnx.query('DROP TABLE ' + self.tablename)
57 except ProgrammingError:
60 self.cnx.query(querystr)
64 def remove(self, record):
65 query_str = "DELETE FROM " + self.tablename + " WHERE key = '" + record.get_key() + "'"
66 self.cnx.query(query_str)
68 def insert(self, record):
69 fieldnames = ["key"] + record.get_field_names()
70 fieldvals = record.get_field_value_strings(fieldnames)
71 query_str = "INSERT INTO " + self.tablename + \
72 "(" + ",".join(fieldnames) + ") " + \
73 "VALUES(" + ",".join(fieldvals) + ")"
75 self.cnx.query(query_str)
77 def update(self, record):
78 names = record.get_field_names()
81 val = record.get_field_value_string(name)
82 pairs.append(name + " = " + val)
83 update = ", ".join(pairs)
85 query_str = "UPDATE " + self.tablename+ " SET " + update + " WHERE key = '" + record.get_key() + "'"
87 self.cnx.query(query_str)
89 def find_dict(self, type, value, searchfield):
90 query_str = "SELECT * FROM " + self.tablename + " WHERE " + searchfield + " = '" + str(value) + "'"
91 dict_list = self.cnx.query(query_str).dictresult()
93 for dict in dict_list:
94 if (type=="*") or (dict['type'] == type):
95 result_dict_list.append(dict)
96 return result_dict_list
98 def find(self, type, value, searchfield):
99 result_dict_list = self.find_dict(type, value, searchfield)
101 for dict in result_dict_list:
102 result_rec_list.append(GeniRecord(dict=dict))
103 return result_rec_list
105 def resolve_dict(self, type, hrn):
106 return self.find_dict(type, hrn, "name")
108 def resolve(self, type, hrn):
109 return self.find(type, hrn, "name")
112 query_str = "SELECT * FROM " + self.tablename
113 result_dict_list = self.cnx.query(query_str).dictresult()
114 return result_dict_list
117 result_dict_list = self.list_dict()
119 for dict in result_dict_list:
120 result_rec_list.append(GeniRecord(dict=dict))
121 return result_rec_list
123 def set_geni_table_prefix(x):
124 global GENI_TABLE_PREFIX
126 GENI_TABLE_PREFIX = x
128 def geni_records_purge(cninfo):
129 global GENI_TABLE_PREFIX
131 cnx = DB(cninfo['dbname'], cninfo['address'], port=cninfo['port'], user=cninfo['user'], passwd=cninfo['password'])
132 tableList = cnx.get_tables()
133 for table in tableList:
134 if table.startswith(GENI_TABLE_PREFIX) or \
135 table.startswith('public.' + GENI_TABLE_PREFIX) or \
136 table.startswith('public."' + GENI_TABLE_PREFIX):
137 report.trace("dropping table " + table)
138 cnx.query("DROP TABLE " + table)