0b78740a6965a156b11ebc19db4d9f278c987dd8
[sfa.git] / sfa / util / table.py
1 ### $Id$
2 ### $URL$
3 #
4 # implements support for SFA records stored in db tables
5 #
6 # TODO: Use existing PLC database methods? or keep this separate?
7
8 import report
9 import pgdb
10
11 from sfa.util.PostgreSQL import *
12 from sfa.trust.gid import *
13 from sfa.util.record import *
14 from sfa.util.debug import *
15 from sfa.util.config import *
16 from sfa.util.filter import *
17
18 class SfaTable(list):
19
20     SFA_TABLE_PREFIX = "sfa"
21
22     def __init__(self, record_filter = None):
23
24         # pgsql doesn't like table names with "." in them, to replace it with "$"
25         self.tablename = SfaTable.SFA_TABLE_PREFIX
26         self.config = Config()
27         self.db = PostgreSQL(self.config)
28
29         if record_filter:
30             records = self.find(record_filter)
31             for record in reocrds:
32                 self.append(record)             
33
34     def exists(self):
35         sql = "SELECT * from pg_tables"
36         tables = self.db.selectall(sql)
37         tables = filter(lambda row: row['tablename'].startswith(self.SFA_TABLE_PREFIX), tables)
38         if tables:
39             return True
40         return False
41     def db_fields(self, obj=None):
42         
43         db_fields = self.db.fields(self.SFA_TABLE_PREFIX)
44         return dict( [ (key,value) for (key, value) in obj.items() \
45                         if key in db_fields and
46                         self.is_writable(key, value, SfaRecord.fields)] )      
47
48     @staticmethod
49     def is_writable (key,value,dict):
50         # if not mentioned, assume it's writable (e.g. deleted ...)
51         if key not in dict: return True
52         # if mentioned but not linked to a Parameter object, idem
53         if not isinstance(dict[key], Parameter): return True
54         # if not marked ro, it's writable
55         if not dict[key].ro: return True
56
57         return False
58
59
60     def create(self):
61         
62         querystr = "CREATE TABLE " + self.tablename + " ( \
63                 record_id serial PRIMARY KEY , \
64                 hrn text NOT NULL, \
65                 authority text NOT NULL, \
66                 peer_authority text, \
67                 gid text, \
68                 type text NOT NULL, \
69                 pointer integer, \
70                 date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, \
71                 last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP);"
72         template = "CREATE INDEX %s_%s_idx ON %s (%s);"
73         indexes = [template % ( self.tablename, field, self.tablename, field) \
74                    for field in ['hrn', 'type', 'authority', 'peer_authority', 'pointer']]
75         # IF EXISTS doenst exist in postgres < 8.2
76         try:
77             self.db.do('DROP TABLE IF EXISTS ' + self.tablename)
78         except:
79             try:
80                 self.db.do('DROP TABLE' + self.tablename)
81             except:
82                 pass
83          
84         self.db.do(querystr)
85         for index in indexes:
86             self.db.do(index)
87         
88         sefl.db.commit()
89     
90     def remove(self, record):
91         query_str = "DELETE FROM %s WHERE record_id = %s" % \
92                     (self.tablename, record['record_id']) 
93         self.db.do(query_str)
94         
95         # if this is a site, remove all records where 'authority' == the 
96         # site's hrn
97         if record['type'] == 'authority':
98             sql = " DELETE FROM %s WHERE authority = %s" % \
99                     (self.tablename, record['hrn'])
100             self.db.do(sql)
101             self.db.commit() 
102
103     def insert(self, record):
104         db_fields = self.db_fields(record)
105         keys = db_fields.keys()
106         values = [self.db.param(key, value) for (key, value) in db_fields.items()]
107         query_str = "INSERT INTO " + self.tablename + \
108                        "(" + ",".join(keys) + ") " + \
109                        "VALUES(" + ",".join(values) + ")"
110         self.db.do(query_str, db_fields)
111         self.db.commit()
112         result = self.find({'hrn': record['hrn'], 'type': record['type'], 'peer_authority': record['peer_authority']})
113         if not result:
114             record_id = None
115         elif isinstance(result, list):
116             record_id = result[0]['record_id']
117         else:
118             record_id = result['record_id']
119
120         return record_id
121
122     def update(self, record):
123         db_fields = self.db_fields(record)
124         keys = db_fields.keys()
125         values = [self.db.param(key, value) for (key, value) in db_fields.items()]
126         columns = ["%s = %s" % (key, value) for (key, value) in zip(keys, values)]
127         query_str = "UPDATE %s SET %s WHERE record_id = %s" % \
128                     (self.tablename, ", ".join(columns), record['record_id'])
129         self.db.do(query_str, db_fields)
130         self.db.commit()
131
132     def quote_string(self, value):
133         return str(self.db.quote(value))
134
135     def quote(self, value):
136         return self.db.quote(value)
137
138     def find(self, record_filter = None, columns=None):
139         if not columns:
140             columns = "*"
141         else:
142             columns = ",".join(columns)
143         sql = "SELECT %s FROM %s WHERE True " % (columns, self.tablename)
144         
145         if isinstance(record_filter, (list, tuple, set)):
146             ints = filter(lambda x: isinstance(x, (int, long)), record_filter)
147             strs = filter(lambda x: isinstance(x, StringTypes), record_filter)
148             record_filter = Filter(SfaRecord.all_fields, {'record_id': ints, 'hrn': strs})
149             sql += "AND (%s) %s " % record_filter.sql("OR") 
150         elif isinstance(record_filter, dict):
151             record_filter = Filter(SfaRecord.all_fields, record_filter)        
152             sql += " AND (%s) %s" % record_filter.sql("AND")
153         elif isinstance(record_filter, StringTypes):
154             record_filter = Filter(SfaRecord.all_fields, {'hrn':[record_filter]})    
155             sql += " AND (%s) %s" % record_filter.sql("AND")
156         elif isinstance(record_filter, int):
157             record_filter = Filter(SfaRecord.all_fields, {'record_id':[record_filter]})    
158             sql += " AND (%s) %s" % record_filter.sql("AND")
159
160         results = self.db.selectall(sql)
161         if isinstance(results, dict):
162             results = [results]
163         return results
164
165     def findObjects(self, record_filter = None, columns=None):
166         
167         results = self.find(record_filter, columns) 
168         result_rec_list = []
169         for result in results:
170             if result['type'] in ['authority']:
171                 result_rec_list.append(AuthorityRecord(dict=result))
172             elif result['type'] in ['node']:
173                 result_rec_list.append(NodeRecord(dict=result))
174             elif result['type'] in ['slice']:
175                 result_rec_list.append(SliceRecord(dict=result))
176             elif result['type'] in ['user']:
177                 result_rec_list.append(UserRecord(dict=result))
178             else:
179                 result_rec_list.append(SfaRecord(dict=result))
180         return result_rec_list
181
182
183     def drop(self):
184         try:
185             self.db.do('DROP TABLE IF EXISTS ' + self.tablename)
186         except:
187             try:
188                 self.db.do('DROP TABLE ' + self.tablename)
189             except:
190                 pass
191     
192     def sfa_records_purge(self):
193         self.drop()