geni/util/genitable.py

   1 # genitable.py
   2 #
   3 # implements support for geni records stored in db tables
   4 #
   5 # TODO: Use existing PLC database methods? or keep this separate?
   6
   7 import report
   8
   9 from pg import DB, ProgrammingError
  10 from gid import *
  11 from record import *
  12 from geni.util.debug import *
  13
  14 GENI_TABLE_PREFIX = "geni$"
  15
  16 class GeniTable():
  17     def __init__(self, create=False, hrn="unspecified.default.registry", cninfo=None):
  18         global GENI_TABLE_PREFIX
  19
  20         self.hrn = hrn
  21
  22         # pgsql doesn't like table names with "." in them, to replace it with "$"
  23         self.tablename = GENI_TABLE_PREFIX + self.hrn.replace(".", "$")
  24
  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'])
  27
  28         # if asked to create the table, then create it
  29         if create:
  30             self.create()
  31
  32     def exists(self):
  33         tableList = self.cnx.get_tables()
  34         if 'public.' + self.tablename in tableList:
  35             return True
  36         if 'public."' + self.tablename + '"' in tableList:
  37             return True
  38         return False
  39
  40     def create(self):
  41
  42         querystr = "CREATE TABLE " + self.tablename + " ( \
  43                 key text, \
  44                 name text, \
  45                 gid text, \
  46                 type text, \
  47                 pointer integer);"
  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
  52         try:
  53             self.cnx.query('DROP TABLE IF EXISTS ' + self.tablename)
  54         except ProgrammingError:
  55             try:
  56                 self.cnx.query('DROP TABLE ' + self.tablename)
  57             except ProgrammingError:
  58                 pass
  59
  60         self.cnx.query(querystr)
  61         for index in indexes:
  62             self.cnx.query(index)
  63
  64     def remove(self, record):
  65         query_str = "DELETE FROM " + self.tablename + " WHERE key = '" + record.get_key() + "'"
  66         self.cnx.query(query_str)
  67
  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) + ")"
  74         #print query_str
  75         self.cnx.query(query_str)
  76
  77     def update(self, record):
  78         names = record.get_field_names()
  79         pairs = []
  80         for name in names:
  81            val = record.get_field_value_string(name)
  82            pairs.append(name + " = " + val)
  83         update = ", ".join(pairs)
  84
  85         query_str = "UPDATE " + self.tablename+ " SET " + update + " WHERE key = '" + record.get_key() + "'"
  86         #print query_str
  87         self.cnx.query(query_str)
  88
  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()
  92         result_dict_list = []
  93         for dict in dict_list:
  94            if (type=="*") or (dict['type'] == type):
  95                result_dict_list.append(dict)
  96         return result_dict_list
  97
  98     def find(self, type, value, searchfield):
  99         result_dict_list = self.find_dict(type, value, searchfield)
 100         result_rec_list = []
 101         for dict in result_dict_list:
 102             result_rec_list.append(GeniRecord(dict=dict))
 103         return result_rec_list
 104
 105     def resolve_dict(self, type, hrn):
 106         return self.find_dict(type, hrn, "name")
 107
 108     def resolve(self, type, hrn):
 109         return self.find(type, hrn, "name")
 110
 111     def list_dict(self):
 112         query_str = "SELECT * FROM " + self.tablename
 113         result_dict_list = self.cnx.query(query_str).dictresult()
 114         return result_dict_list
 115
 116     def list(self):
 117         result_dict_list = self.list_dict()
 118         result_rec_list = []
 119         for dict in result_dict_list:
 120             result_rec_list.append(GeniRecord(dict=dict))
 121         return result_rec_list
 122
 123 def set_geni_table_prefix(x):
 124     global GENI_TABLE_PREFIX
 125
 126     GENI_TABLE_PREFIX = x
 127
 128 def geni_records_purge(cninfo):
 129     global GENI_TABLE_PREFIX
 130
 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)