Source Code for Module backend.server.rhnSQL.sql_table

  1  # 
  2  # Copyright (c) 2008--2016 Red Hat, Inc. 
  3  # 
  4  # This software is licensed to you under the GNU General Public License, 
  5  # version 2 (GPLv2). There is NO WARRANTY for this software, express or 
  6  # implied, including the implied warranties of MERCHANTABILITY or FITNESS 
  7  # FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 
  8  # along with this software; if not, see 
  9  # http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. 
 10  # 
 11  # Red Hat trademarks are not licensed under GPLv2. No permission is 
 12  # granted to use or replicate Red Hat trademarks that are incorporated 
 13  # in this software or its documentation. 
 14  # 
 15  # classes definitions for working with a sql table 
 16  # 
 17  # 
 18   
 19  import string 
 20   
 21  from rhn.UserDictCase import UserDictCase 
 22  from spacewalk.common.rhnException import rhnException 
 23   
 24  import sql_base 
 25  import sql_lib 
 26   
 27   
 28  # A class to handle row updates transparently 
 29 -class RowData(UserDictCase): 
 30   
 31 -    def __init__(self, dict, db, sql, rowid, cache=None): 
 32          UserDictCase.__init__(self, dict) 
 33          if not isinstance(db, sql_base.Database): 
 34              raise TypeError("Second argument needs to be a database handle") 
 35          self.__db = db 
 36          self.__sql = sql 
 37          self.__rowid = rowid 
 38          self.__cache = cache 
 39   
 40      # now the real function that supports updating 
 41 -    def __setitem__(self, key, value): 
 42          sql = self.__sql % key 
 43          h = self.__db.prepare(sql) 
 44          h.execute(new_val=value, row_id=self.__rowid) 
 45          # keep self.data in sync 
 46          self.data[key] = value 
 47          if self.__cache:  # maintain cache consistency 
 48              try: 
 49                  self.__cache[self.__rowid][key] = value 
 50              except: 
 51                  pass 
 52   
 53   
 54  # A class to handle operations on a table. 
 55  # 
 56  # While this class allows you to perform queries and updates on a row 
 57  # within a table, it is recommended you use the Row class if you ever 
 58  # need to touch a single row of data. On the other hand, if you need 
 59  # to jump a lot in the table from one row to another this class is 
 60  # more efficient because it works as a hash of hashes, if you will... 
 61  # 
 62  # Some day we'll figure out how to reduce confusion... 
 63 -class Table: 
 64   
 65 -    def __init__(self, db, table, hashid, cache=False): 
 66          if not table or not isinstance(table, str): 
 67              raise rhnException("First argument needs to be a table name", 
 68                                 table) 
 69          self.__table = table 
 70          if not hashid or not isinstance(hashid, str): 
 71              raise rhnException("Second argument needs to be the name of the unique index column", 
 72                                 hashid) 
 73          self.__hashid = hashid 
 74          if not isinstance(db, sql_base.Database): 
 75              raise rhnException("Argument db is not a database instance", db) 
 76          self.__db = db 
 77          self.__cache = None 
 78          if cache: 
 79              self.__cache = {} 
 80   
 81 -    def set_cache(self, value): 
 82          if not value: 
 83              self.__cache = None 
 84              return 
 85          if self.__cache is not None:  # already enabled 
 86              return 
 87          self.__cache = {} 
 88   
 89      # insert row(s) into the table 
 90 -    def insert(self, rows): 
 91          # insert a single row into the table 
 92          def insert_row(row, self=self): 
 93              if self.__cache is not None: 
 94                  self.__cache[row[self.__hashid]] = row 
 95              return self.__setitem__(None, row) 
 96          if isinstance(rows, dict) or isinstance(rows, UserDictCase): 
 97              return insert_row(rows) 
 98          if isinstance(rows, list): 
 99              for x in rows: 
100                  insert_row(x) 
101              return None 
102          raise rhnException("Invalid data %s passed" % type(rows), rows) 
103   
104      # select from the whole table all the entries that match the 
105      # valuies of the hash provided (kind of a complex select) 
106 -    def select(self, row): 
107          if not isinstance(row, dict) and not isinstance(row, UserDictCase): 
108              raise rhnException("Expecting hash argument. %s is invalid" % type(row), 
109                                 row) 
110          if row == {}: 
111              raise rhnException("The hash argument is empty", row) 
112          keys = list(row.keys()) 
113          # Sort the list of keys, to always get the same list of arguments 
114          keys.sort() 
115          args = [] 
116          for col in keys: 
117              if row[col] in (None, ''): 
118                  clause = "%s is null" % col 
119              else: 
120                  clause = "%s = :%s" % (col, col) 
121              args.append(clause) 
122          sql = "select * from %s where " % self.__table 
123          cursor = self.__db.prepare(sql + string.join(args, " and ")) 
124          cursor.execute(**row) 
125          rows = cursor.fetchall_dict() 
126          if rows is None: 
127              return None 
128          # fill up the cache 
129          if self.__cache is not None: 
130              for row in rows: 
131                  self.__cache[row[self.__hashid]] = row 
132          return [UserDictCase(a) for a in rows] 
133   
134      # print it out 
135 -    def __repr__(self): 
136          return "<%s> instance for table `%s' keyed on `%s'" % ( 
137              self.__class__, self.__table, self.__hashid) 
138   
139      # make this table look like a dictionary 
140 -    def __getitem__(self, key): 
141          if self.__cache and key in self.__cache: 
142              return self.__cache[key] 
143          h = self.__db.prepare("select * from %s where %s = :p1" % ( 
144              self.__table, self.__hashid)) 
145          h.execute(p1=key) 
146          ret = h.fetchone_dict() 
147          if ret is None: 
148              if self.__cache is not None: 
149                  self.__cache[key] = None 
150              return None 
151          xret = UserDictCase(ret) 
152          if self.__cache is not None: 
153              self.__cache[key] = xret 
154          return xret 
155   
156      # this one is pretty much like __getitem__, but returns a nice 
157      # reference to a RowData instance that allows the returned hash to 
158      # be modified. 
159 -    def get(self, key): 
160          ret = self.__getitem__(key) 
161          if self.__cache and key in self.__cache: 
162              del self.__cache[key] 
163          sql = "update %s set %%s = :new_val where %s = :row_id" % ( 
164              self.__table, self.__hashid) 
165          return RowData(ret, self.__db, sql, key, self.__cache) 
166   
167      # database insertion, dictionary style (pass in the hash with the 
168      # values for all columns except the one that functions as the 
169      # primary key identifier 
170 -    def __setitem__(self, key, value): 
171          if not isinstance(value, dict) and not isinstance(value, UserDictCase): 
172              raise TypeError("Expected value to be a hash") 
173          if self.__hashid in value:  # we don't need that 
174              if key is None: 
175                  key = value[self.__hashid] 
176              del value[self.__hashid] 
177   
178          if key is None: 
179              raise KeyError("Can not insert entry with NULL key") 
180          items = list(value.items()) 
181          if items == []:  # quick check for noop 
182              return 
183          sql = None 
184          if self.has_key(key): 
185              sql, pdict = sql_lib.build_sql_update(self.__table, self.__hashid, items) 
186          else: 
187              sql, pdict = sql_lib.build_sql_insert(self.__table, self.__hashid, items) 
188          # import the value of the hash key 
189          pdict["p0"] = key 
190          h = self.__db.prepare(sql) 
191          h.execute(**pdict) 
192          try: 
193              value[self.__hashid] = key 
194              self.__cache[key] = value 
195          except: 
196              pass 
197   
198      # length 
199 -    def __len__(self): 
200          h = self.__db.prepare("select count(*) as ID from %s" % self.__table) 
201          h.execute() 
202          row = h.fetchone_dict() 
203          if row is None: 
204              return 0 
205          return int(row["id"]) 
206   
207      # delete an entry by the key 
208 -    def __delitem__(self, key): 
209          h = self.__db.prepare("delete from %s where %s = :p1" % ( 
210              self.__table, self.__hashid)) 
211          h.execute(p1=key) 
212          try: 
213              del self.__cache[key] 
214          except: 
215              pass 
216          return 0 
217   
218      # get all keys 
219 -    def keys(self): 
220          h = self.__db.prepare("select %s NAME from %s" % ( 
221              self.__hashid, self.__table)) 
222          h.execute() 
223          data = h.fetchall_dict() 
224          if data is None: 
225              return [] 
226          return [a["name"] for a in data] 
227   
228      # has_key 
229      # if we're caching, fetch the row and cache it; else, fetch the 
230      # smaller value 
231 -    def has_key(self, key): 
232          if self.__cache is not None: 
233              h = self.__db.prepare("select * from %s where %s = :p1" % 
234                                    (self.__table, self.__hashid)) 
235          else: 
236              h = self.__db.prepare("select %s from %s where %s = :p1" % 
237                                    (self.__hashid, self.__table, self.__hashid)) 
238          h.execute(p1=key) 
239          row = h.fetchone_dict() 
240          if not row: 
241              return 0 
242          # stuff it in the cache if we need to do so 
243          if self.__cache is not None: 
244              self.__cache[key] = row 
245          # XXX: can this thing fail in any other way? 
246          return 1 
247   
248      # flush the cache. if cache is off, then noop 
249 -    def flush(self): 
250          if self.__cache is not None:  # avoid turning caching on when flushing 
251              self.__cache = {} 
252   
253      # passthrough commit 
254 -    def commit(self): 
255          return self.__db.commit() 
256   
257      # passthrough rollback 
258 -    def rollback(self): 
259          self.flush() 
260          return self.__db.rollback() 
261   
262 -    def printcache(self): 
263          print(self.__cache) 
264          return 
265