sfa/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 ### $Id$
   8 ### $URL$
   9
  10 import report
  11
  12 from pg import DB, ProgrammingError
  13
  14 from sfa.trust.gid import *
  15 from sfa.util.record import *
  16 from sfa.util.debug import *
  17
  18 class GeniTable:
  19
  20     GENI_TABLE_PREFIX = "sfa"
  21
  22     def __init__(self, create=False, cninfo=None):
  23
  24         self.tablename = GeniTable.GENI_TABLE_PREFIX
  25
  26         # establish a connection to the pgsql server
  27         self.cnx = DB(cninfo['dbname'], cninfo['address'], port=cninfo['port'], user=cninfo['user'], passwd=cninfo['password'])
  28
  29         # if asked to create the table, then create it
  30         if create:
  31             self.create()
  32
  33     def exists(self):
  34         tableList = self.cnx.get_tables()
  35         if 'public.' + self.tablename in tableList:
  36             return True
  37         if 'public."' + self.tablename + '"' in tableList:
  38             return True
  39         return False
  40
  41     def create(self):
  42         seln't like table names with "." in them, to
  43
  44         # pgsql doesn't like table names with "." in them, to.hrn = hrn
  45
  46         querystr = "CREATE TABLE " + self.tablename + " ( \
  47                 key text, \
  48                 authority text, \
  49                 hrn text, \
  50                 gid text, \
  51                 type text, \
  52                 pointer integer, \
  53                 date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, \
  54                 last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP);"
  55         template = "CREATE INDEX %s_%s_idx ON %s (%s);"
  56         indexes = [template % ( self.tablename, field, self.tablename, field) \
  57                    for field in ['key', 'authority', 'hrn', 'type','pointer']]
  58         # IF EXISTS doenst exist in postgres < 8.2
  59         try:
  60             self.cnx.query('DROP TABLE IF EXISTS ' + self.tablename)
  61         except ProgrammingError:
  62             try:
  63                 self.cnx.query('DROP TABLE ' + self.tablename)
  64             except ProgrammingError:
  65                 pass
  66
  67         self.cnx.query(querystr)
  68         for index in indexes:
  69             self.cnx.query(index)
  70
  71     def remove(self, record):
  72         query_str = "DELETE FROM " + self.tablename + " WHERE key = '" + record.get_key() + "'"
  73         self.cnx.query(query_str)
  74
  75     def insert(self, record):
  76         dont_insert = ['date_created', 'last_updated']
  77         fields = [field for field in  record.fields.keys() if field not in dont_insert]
  78         fieldnames = ["key", "pointer"] + fields
  79         fieldvals = record.get_field_value_strings(fieldnames)
  80         query_str = "INSERT INTO " + self.tablename + \
  81                        "(" + ",".join(fieldnames) + ") " + \
  82                        "VALUES(" + ",".join(fieldvals) + ")"
  83         #print query_str
  84         self.cnx.query(query_str)
  85
  86     def update(self, record):
  87         dont_update = ['date_created', 'last_updated']
  88         names = record.get_field_names()
  89         names = [name for name in names if name not in dont_update]
  90         pairs = []
  91         for name in names:
  92            val = record.get_field_value_string(name)
  93            pairs.append(name + " = " + val)
  94         update = ", ".join(pairs)
  95
  96         query_str = "UPDATE " + self.tablename+ " SET " + update + " WHERE key = '" + record.get_key() + "'"
  97         #print query_str
  98         self.cnx.query(query_str)
  99
 100     def find_dict(self, type, value, searchfield):
 101         query_str = "SELECT * FROM " + self.tablename + " WHERE " + searchfield + " = '" + str(value) + "'"
 102         dict_list = self.cnx.query(query_str).dictresult()
 103         result_dict_list = []
 104         for dict in dict_list:
 105            if (type=="*") or (dict['type'] == type):
 106                result_dict_list.append(dict)
 107         return result_dict_list
 108
 109     def find(self, type, value, searchfield):
 110         result_dict_list = self.find_dict(type, value, searchfield)
 111         result_rec_list = []
 112         for result in result_dict_list:
 113             if result['type'] in ['authority']:
 114                 result_rec_list.append(AuthorityRecord(dict=result))
 115             elif result['type'] in ['node']:
 116                 result_rec_list.append(NodeRecord(dict=result))
 117             elif result['type'] in ['slice']:
 118                 result_rec_list.append(SliceRecord(dict=result))
 119             elif result['type'] in ['user']:
 120                 result_rec_list.append(UserRecord(dict=result))
 121             else:
 122                 result_rec_list.append(GeniRecord(dict=result))
 123         return result_rec_list
 124
 125     def resolve_dict(self, type, hrn):
 126         return self.find_dict(type, hrn, "hrn")
 127
 128     def resolve(self, type, hrn):
 129         return self.find(type, hrn, "hrn")
 130
 131     def list_dict(self):
 132         query_str = "SELECT * FROM " + self.tablename
 133         result_dict_list = self.cnx.query(query_str).dictresult()
 134         return result_dict_list
 135
 136     def list(self):
 137         result_dict_list = self.list_dict()
 138         result_rec_list = []
 139         for dict in result_dict_list:
 140             result_rec_list.append(GeniRecord(dict=dict).as_dict())
 141         return result_rec_list
 142
 143     @staticmethod
 144     def geni_records_purge(cninfo):
 145
 146         cnx = DB(cninfo['dbname'], cninfo['address'],
 147                  port=cninfo['port'], user=cninfo['user'], passwd=cninfo['password'])
 148         tableList = cnx.get_tables()
 149         for table in tableList:
 150             if table.startswith(GeniTable.GENI_TABLE_PREFIX) or \
 151                     table.startswith('public.' + GeniTable.GENI_TABLE_PREFIX) or \
 152                     table.startswith('public."' + GeniTable.GENI_TABLE_PREFIX):
 153                 report.trace("dropping table " + table)
 154                 cnx.query("DROP TABLE " + table)