Source Code for Module backend.server.importlib.backendOracle

  1  # 
  2  # Copyright (c) 2008--2017 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  # 
 16  # Oracle-specific stuff 
 17  # 
 18  # FIXME: need more documentation 
 19  # 
 20   
 21  import sys 
 22  from backend import Backend 
 23  from backendLib import DBint, DBstring, DBdateTime, Table, \ 
 24      TableCollection 
 25  from spacewalk.server import rhnSQL 
 26  from spacewalk.server.rhnSQL.const import ORACLE, POSTGRESQL 
 27  from spacewalk.common.rhnConfig import CFG 
 28  from spacewalk.common import timezone_utils 
 29   
 30   
 31 -class OracleBackend(Backend): 
 32      tables = TableCollection( 
 33          # NOTE: pk = primary keys 
 34          #       attribute = attribute this table links back to 
 35          #       map = mapping from database fields to generic attribute names 
 36          Table('rhnPackageProvides', 
 37                fields={ 
 38                    'package_id': DBint(), 
 39                    'capability_id': DBint(), 
 40                    'sense': DBint(), 
 41                }, 
 42                pk=['package_id', 'capability_id', 'sense'], 
 43                attribute='provides', 
 44                map={'sense': 'flags', }, 
 45                ), 
 46          Table('rhnPackageRequires', 
 47                fields={ 
 48                    'package_id': DBint(), 
 49                    'capability_id': DBint(), 
 50                    'sense': DBint(), 
 51                }, 
 52                pk=['package_id', 'capability_id', 'sense'], 
 53                attribute='requires', 
 54                map={'sense': 'flags', }, 
 55                ), 
 56          Table('rhnPackageConflicts', 
 57                fields={ 
 58                    'package_id': DBint(), 
 59                    'capability_id': DBint(), 
 60                    'sense': DBint(), 
 61                }, 
 62                pk=['package_id', 'capability_id', 'sense'], 
 63                attribute='conflicts', 
 64                map={'sense': 'flags', }, 
 65                ), 
 66          Table('rhnPackageObsoletes', 
 67                fields={ 
 68                    'package_id': DBint(), 
 69                    'capability_id': DBint(), 
 70                    'sense': DBint(), 
 71                }, 
 72                pk=['package_id', 'capability_id', 'sense'], 
 73                attribute='obsoletes', 
 74                map={'sense': 'flags', }, 
 75                ), 
 76          Table('rhnPackageRecommends', 
 77                fields={ 
 78                    'package_id': DBint(), 
 79                    'capability_id': DBint(), 
 80                    'sense': DBint(), 
 81                }, 
 82                pk=['package_id', 'capability_id', 'sense'], 
 83                attribute='recommends', 
 84                map={'sense': 'flags', }, 
 85                ), 
 86          Table('rhnPackageSuggests', 
 87                fields={ 
 88                    'package_id': DBint(), 
 89                    'capability_id': DBint(), 
 90                    'sense': DBint(), 
 91                }, 
 92                pk=['package_id', 'capability_id', 'sense'], 
 93                attribute='suggests', 
 94                map={'sense': 'flags', }, 
 95                ), 
 96          Table('rhnPackageSupplements', 
 97                fields={ 
 98                    'package_id': DBint(), 
 99                    'capability_id': DBint(), 
100                    'sense': DBint(), 
101                }, 
102                pk=['package_id', 'capability_id', 'sense'], 
103                attribute='supplements', 
104                map={'sense': 'flags', }, 
105                ), 
106          Table('rhnPackageEnhances', 
107                fields={ 
108                    'package_id': DBint(), 
109                    'capability_id': DBint(), 
110                    'sense': DBint(), 
111                }, 
112                pk=['package_id', 'capability_id', 'sense'], 
113                attribute='enhances', 
114                map={'sense': 'flags', }, 
115                ), 
116          Table('rhnPackageBreaks', 
117                fields={ 
118                    'package_id': DBint(), 
119                    'capability_id': DBint(), 
120                    'sense': DBint(), 
121                }, 
122                pk=['package_id', 'capability_id', 'sense'], 
123                attribute='breaks', 
124                map={'sense': 'flags', }, 
125                ), 
126          Table('rhnPackagePredepends', 
127                fields={ 
128                    'package_id': DBint(), 
129                    'capability_id': DBint(), 
130                    'sense': DBint(), 
131                }, 
132                pk=['package_id', 'capability_id', 'sense'], 
133                attribute='predepends', 
134                map={'sense': 'flags', }, 
135                ), 
136          Table('rhnPackageChangeLogRec', 
137                fields={ 
138                    'id': DBint(), 
139                    'package_id': DBint(), 
140                    'changelog_data_id': DBint(), 
141                }, 
142                pk=['package_id', 'changelog_data_id'], 
143                attribute='changelog', 
144                sequenceColumn='id', 
145                ), 
146          Table('rhnPackageChangeLogData', 
147                fields={ 
148                    'id': DBint(), 
149                    'name': DBstring(128), 
150                    'text': DBstring(3000), 
151                    'time': DBdateTime() 
152                }, 
153                ), 
154          Table('rhnPackageFile', 
155                fields={ 
156                    'package_id': DBint(), 
157                    'capability_id': DBint(), 
158                    'device': DBint(), 
159                    'inode': DBint(), 
160                    'file_mode': DBint(), 
161                    'username': DBstring(32), 
162                    'groupname': DBstring(32), 
163                    'rdev': DBint(), 
164                    'file_size': DBint(), 
165                    'mtime': DBdateTime(), 
166                    'checksum_id': DBint(), 
167                    'linkto': DBstring(256), 
168                    'flags': DBint(), 
169                    'verifyflags': DBint(), 
170                    'lang': DBstring(32), 
171                }, 
172                pk=['package_id', 'capability_id'], 
173                attribute='files', 
174                severityHash={ 
175                    'mtime': 0, 
176                    'file_size': 4, 
177                }, 
178                ), 
179          Table('rhnPackage', 
180                fields={ 
181                    'id': DBint(), 
182                    'org_id': DBint(), 
183                    'name_id': DBint(), 
184                    'evr_id': DBint(), 
185                    'package_arch_id': DBint(), 
186                    'package_group': DBint(), 
187                    'rpm_version': DBstring(16), 
188                    'description': DBstring(4000), 
189                    'summary': DBstring(4000), 
190                    'package_size': DBint(), 
191                    'payload_size': DBint(), 
192                    'installed_size': DBint(), 
193                    'build_host': DBstring(256), 
194                    'build_time': DBdateTime(), 
195                    'source_rpm_id': DBint(), 
196                    'checksum_id': DBint(), 
197                    'vendor': DBstring(64), 
198                    'payload_format': DBstring(32), 
199                    'path': DBstring(1000), 
200                    'copyright': DBstring(128), 
201                    'cookie': DBstring(128), 
202                    'header_start': DBint(), 
203                    'header_end': DBint(), 
204                    'last_modified': DBdateTime(), 
205                    'multi_arch': DBstring(16), 
206                }, 
207                pk=['org_id', 'name_id', 'evr_id', 'package_arch_id', 
208                    'checksum_id'], 
209                nullable=['org_id'], 
210                severityHash={ 
211                    'path': 1, 
212                    'package_size': 2, 
213                    'build_time': 3, 
214                    'build_host': 3, 
215                    'last_modified': 0.5, 
216                    # rpm got it wrong so now we have to ignore it 
217                    'payload_size': 0, 
218                }, 
219                ), 
220          Table('rhnChannelPackage', 
221                fields={ 
222                    'package_id': DBint(), 
223                    'channel_id': DBint(), 
224                }, 
225                pk=['channel_id', 'package_id'], 
226                ), 
227          Table('rhnErrata', 
228                fields={ 
229                    'id': DBint(), 
230                    'advisory': DBstring(100), 
231                    'advisory_type': DBstring(32), 
232                    'advisory_name': DBstring(100), 
233                    'advisory_rel': DBint(), 
234                    'product': DBstring(64), 
235                    'description': DBstring(4000), 
236                    'synopsis': DBstring(4000), 
237                    'topic': DBstring(4000), 
238                    'solution': DBstring(4000), 
239                    'notes': DBstring(4000), 
240                    'refers_to': DBstring(4000), 
241                    'org_id': DBint(), 
242                    'locally_modified': DBstring(1), 
243                    'severity_id': DBint(), 
244                    'errata_from': DBstring(127), 
245                    # We will treat issue_date and update_date as regular dates 
246                    # with times instead of DBdate types, otherwise we'd have 
247                    # issues with timezones 
248                    'issue_date': DBdateTime(), 
249                    'update_date': DBdateTime(), 
250                    'last_modified': DBdateTime(), 
251                }, 
252                pk=['advisory_name', 'org_id'], 
253                nullable=['org_id'], 
254                defaultSeverity=4, 
255                ), 
256          Table('rhnErrataBugList', 
257                fields={ 
258                    'errata_id': DBint(), 
259                    'bug_id': DBint(), 
260                    'summary': DBstring(4000), 
261                    'href': DBstring(255), 
262                }, 
263                pk=['errata_id', 'bug_id'], 
264                attribute='bugs', 
265                defaultSeverity=4, 
266                ), 
267          Table('rhnCVE', 
268                fields={ 
269                    'id': DBint(), 
270                    'name': DBstring(20), 
271                }, 
272                pk=['name'], 
273                ), 
274          Table('rhnErrataCVE', 
275                fields={ 
276                    'errata_id': DBint(), 
277                    'cve_id': DBint(), 
278                }, 
279                pk=['errata_id', 'cve_id'], 
280                attribute='cve', 
281                defaultSeverity=4, 
282                ), 
283          Table('rhnErrataFile', 
284                fields={ 
285                    'id': DBint(), 
286                    'errata_id': DBint(), 
287                    'type': DBint(), 
288                    'checksum_id': DBint(), 
289                    'filename': DBstring(4000), 
290                }, 
291                pk=['errata_id', 'filename', 'checksum_id'], 
292                attribute='files', 
293                defaultSeverity=4, 
294                sequenceColumn='id', 
295                ), 
296          Table('rhnErrataFilePackage', 
297                fields={ 
298                    'errata_file_id': DBint(), 
299                    'package_id': DBint(), 
300                }, 
301                pk=['errata_file_id', 'package_id'], 
302                ), 
303          Table('rhnErrataFilePackageSource', 
304                fields={ 
305                    'errata_file_id': DBint(), 
306                    'package_id': DBint(), 
307                }, 
308                pk=['errata_file_id', 'package_id'], 
309                ), 
310          Table('rhnErrataFileChannel', 
311                fields={ 
312                    'errata_file_id': DBint(), 
313                    'channel_id': DBint(), 
314                }, 
315                pk=['errata_file_id', 'channel_id'], 
316                ), 
317          Table('rhnErrataKeyword', 
318                fields={ 
319                    'errata_id': DBint(), 
320                    'keyword': DBstring(64), 
321                }, 
322                pk=['errata_id', 'keyword'], 
323                attribute='keywords', 
324                defaultSeverity=4, 
325                ), 
326          Table('rhnErrataPackage', 
327                fields={ 
328                    'errata_id': DBint(), 
329                    'package_id': DBint(), 
330                }, 
331                pk=['errata_id', 'package_id'], 
332                attribute='packages', 
333                defaultSeverity=4, 
334                ), 
335          Table('rhnChannelErrata', 
336                fields={ 
337                    'errata_id': DBint(), 
338                    'channel_id': DBint(), 
339                }, 
340                pk=['errata_id', 'channel_id'], 
341                attribute='channels', 
342                defaultSeverity=4, 
343                ), 
344          Table('rhnChannel', 
345                fields={ 
346                    'id': DBint(), 
347                    'parent_channel': DBint(), 
348                    'org_id': DBint(), 
349                    'channel_arch_id': DBint(), 
350                    'label': DBstring(128), 
351                    'basedir': DBstring(256), 
352                    'name': DBstring(256), 
353                    'summary': DBstring(500), 
354                    'description': DBstring(4000), 
355                    'product_name_id': DBint(), 
356                    'gpg_key_url': DBstring(256), 
357                    'gpg_key_id': DBstring(14), 
358                    'gpg_key_fp': DBstring(50), 
359                    'end_of_life': DBdateTime(), 
360                    'receiving_updates': DBstring(1), 
361                    'last_modified': DBdateTime(), 
362                    'channel_product_id': DBint(), 
363                    'checksum_type_id': DBint(), 
364                    'channel_access': DBstring(10), 
365                }, 
366                pk=['label'], 
367                severityHash={ 
368                    'channel_product_id': 0, 
369                }, 
370                ), 
371          Table('rhnChannelFamily', 
372                fields={ 
373                    'id': DBint(), 
374                    'name': DBstring(128), 
375                    'label': DBstring(128), 
376                    'product_url': DBstring(128), 
377                }, 
378                pk=['label'], 
379                defaultSeverity=4, 
380                ), 
381          Table('rhnDistChannelMap', 
382                fields={ 
383                    'os': DBstring(64), 
384                    'release': DBstring(64), 
385                    'channel_arch_id': DBint(), 
386                    'channel_id': DBint(), 
387                    'org_id': DBint(), 
388                }, 
389                pk=['release', 'channel_arch_id', 'org_id'], 
390                attribute='dists', 
391                defaultSeverity=4, 
392                ), 
393          Table('rhnReleaseChannelMap', 
394                fields={ 
395                    'product': DBstring(64), 
396                    'version': DBstring(64), 
397                    'release': DBstring(64), 
398                    'channel_arch_id': DBint(), 
399                    'channel_id': DBint() 
400                }, 
401                pk=['product', 'version', 'release', 'channel_arch_id', 'channel_id'], 
402                attribute='release', 
403                defaultSeverity=4, 
404                ), 
405          Table('rhnChannelTrust', 
406                fields={ 
407                    'channel_id': DBint(), 
408                    'org_trust_id': DBint(), 
409                }, 
410                pk=['channel_id', 'org_trust_id'], 
411                attribute='trust_list', 
412                defaultSeverity=4, 
413                ), 
414          Table('rhnChannelFamilyMembers', 
415                fields={ 
416                    'channel_id': DBint(), 
417                    'channel_family_id': DBint(), 
418                }, 
419                pk=['channel_id', 'channel_family_id'], 
420                attribute='families', 
421                defaultSeverity=4, 
422                ), 
423          Table('rhnPackageSource', 
424                fields={ 
425                    'id': DBint(), 
426                    'org_id': DBint(), 
427                    'source_rpm_id': DBint(), 
428                    'package_group': DBint(), 
429                    'rpm_version': DBstring(16), 
430                    'payload_size': DBint(), 
431                    'build_host': DBstring(256), 
432                    'build_time': DBdateTime(), 
433                    'path': DBstring(1000), 
434                    'package_size': DBint(), 
435                    'checksum_id': DBint(), 
436                    'sigchecksum_id': DBint(), 
437                    'vendor': DBstring(64), 
438                    'cookie': DBstring(128), 
439                    'last_modified': DBdateTime(), 
440                }, 
441                pk=['source_rpm_id', 'org_id', 
442                    'sigchecksum_id', 'checksum_id'], 
443                nullable=['org_id'], 
444                severityHash={ 
445                    'path': 1, 
446                    'file_size': 2, 
447                    'build_host': 3, 
448                    'build_time': 3, 
449                    # rpm got it wrong so now we have to ignore it 
450                    'payload_size': 0, 
451                    'last_modified': 0.5, 
452                }, 
453                ), 
454          Table('rhnServerArch', 
455                fields={ 
456                    'id': DBint(), 
457                    'label': DBstring(64), 
458                    'name': DBstring(64), 
459                    'arch_type_id': DBint(), 
460                }, 
461                pk=['label'], 
462                ), 
463          Table('rhnPackageArch', 
464                fields={ 
465                    'id': DBint(), 
466                    'label': DBstring(64), 
467                    'name': DBstring(64), 
468                    'arch_type_id': DBint(), 
469                }, 
470                pk=['label'], 
471                ), 
472          Table('rhnChannelArch', 
473                fields={ 
474                    'id': DBint(), 
475                    'label': DBstring(64), 
476                    'name': DBstring(64), 
477                    'arch_type_id': DBint(), 
478                }, 
479                pk=['label'], 
480                ), 
481          Table('rhnCPUArch', 
482                fields={ 
483                    'id': DBint(), 
484                    'label': DBstring(64), 
485                    'name': DBstring(64), 
486                }, 
487                pk=['label'], 
488                ), 
489          Table('rhnServerPackageArchCompat', 
490                fields={ 
491                    'server_arch_id': DBint(), 
492                    'package_arch_id': DBint(), 
493                    'preference': DBint(), 
494                }, 
495                pk=['server_arch_id', 'package_arch_id', 'preference'], 
496                ), 
497          Table('rhnServerChannelArchCompat', 
498                fields={ 
499                    'server_arch_id': DBint(), 
500                    'channel_arch_id': DBint(), 
501                }, 
502                pk=['server_arch_id', 'channel_arch_id'], 
503                ), 
504          Table('rhnChannelPackageArchCompat', 
505                fields={ 
506                    'channel_arch_id': DBint(), 
507                    'package_arch_id': DBint(), 
508                }, 
509                pk=['channel_arch_id', 'package_arch_id'], 
510                ), 
511          Table('rhnServerServerGroupArchCompat', 
512                fields={ 
513                    'server_arch_id': DBint(), 
514                    'server_group_type': DBint(), 
515                }, 
516                pk=['server_arch_id', 'server_group_type'], 
517                ), 
518          Table('rhnKickstartableTree', 
519                fields={ 
520                    'id': DBint(), 
521                    'org_id': DBint(), 
522                    'base_path': DBstring(256), 
523                    'channel_id': DBint(), 
524                    'label': DBstring(64), 
525                    'boot_image': DBstring(128), 
526                    'kstree_type': DBint(), 
527                    'install_type': DBint(), 
528                    'last_modified': DBdateTime() 
529                }, 
530                pk=['label', 'org_id'], 
531                nullable=['org_id'], 
532                ), 
533          Table('rhnKSTreeType', 
534                # not used at the moment 
535                fields={ 
536                    'id': DBint(), 
537                    'label': DBstring(32), 
538                    'name': DBstring(64), 
539                }, 
540                pk=['label'], 
541                ), 
542          Table('rhnKSInstallType', 
543                # not used at the moment 
544                fields={ 
545                    'id': DBint(), 
546                    'label': DBstring(32), 
547                    'name': DBstring(64), 
548                }, 
549                pk=['label'], 
550                ), 
551          Table('rhnKSTreeFile', 
552                fields={ 
553                    'kstree_id': DBint(), 
554                    'relative_filename': DBstring(256), 
555                    'checksum_id': DBint(), 
556                    'file_size': DBint(), 
557                    'last_modified': DBdateTime() 
558                }, 
559                pk=['kstree_id', 'relative_filename', 'checksum_id'], 
560                attribute='files', 
561                map={ 
562                    'relative_filename': 'relative_path', 
563                }, 
564                ), 
565   
566          Table('rhnProductName', 
567                fields={ 
568                    'id': DBint(), 
569                    'label': DBstring(128), 
570                    'name': DBstring(128), 
571                }, 
572                pk=['id', 'label', 'name'], 
573                ), 
574          Table('rhnContentSource', 
575                fields={ 
576                    'id': DBint(), 
577                    'org_id': DBint(), 
578                    'label': DBstring(128), 
579                    'source_url': DBstring(2048), 
580                    'type_id': DBint(), 
581                }, 
582                pk=['label', 'org_id', 'type_id'], 
583                nullable=['org_id'], 
584                ), 
585          Table('rhnContentSourceSsl', 
586                fields={ 
587                    'content_source_id': DBint(), 
588                    'ssl_ca_cert_id': DBint(), 
589                    'ssl_client_cert_id': DBint(), 
590                    'ssl_client_key_id': DBint() 
591                }, 
592                attribute='ssl-sets', 
593                pk=['content_source_id', 'ssl_ca_cert_id', 'ssl_client_cert_id', 'ssl_client_key_id'], 
594                nullable=['ssl_client_cert_id', 'ssl_client_key_id'], 
595                ), 
596      ) 
597   
598 -    def __init__(self): 
599          Backend.__init__(self, rhnSQL) 
600   
601 -    def setSessionTimeZoneToLocalTimeZone(self): 
602          sth = self.dbmodule.prepare("alter session set time_zone = '%s'" 
603                                      % timezone_utils.get_utc_offset()) 
604          sth.execute() 
605   
606 -    def init(self): 
607          """ 
608          Override parent to do explicit setting of the date format. (Oracle 
609          specific) 
610          """ 
611          # Set date format 
612          self.setSessionTimeZoneToLocalTimeZone() 
613          self.setDateFormat("YYYY-MM-DD HH24:MI:SS") 
614          return Backend.init(self) 
615   
616   
617 -class PostgresqlBackend(OracleBackend): 
618   
619      """ 
620      PostgresqlBackend specific implementation. The bulk of the OracleBackend 
621      is not actually Oracle specific, so we'll re-use as much as we can and just 
622      avoid the few bits that are. 
623      """ 
624   
625 -    def setSessionTimeZoneToLocalTimeZone(self): 
626          sth = self.dbmodule.prepare("set session time zone '%s'" 
627                                      % timezone_utils.get_utc_offset()) 
628          sth.execute() 
629   
630 -    def init(self): 
631          """ 
632          Avoid the Oracle specific stuff here in parent method. 
633          """ 
634          self.setSessionTimeZoneToLocalTimeZone() 
635          return Backend.init(self) 
636   
637      # Postgres doesn't support autonomous transactions. We could use 
638      # dblink_exec like we do in other stored procedures to open a new 
639      # connection to the db and do our inserts there, but there are a lot of 
640      # capabilities and opening several million connections to the db in the 
641      # middle of a sat-sync is slow. Instead we keep open a secondary db 
642      # connection which we only use here, so we can directly commit to that 
643      # instead of opening a new connection for each insert. 
644 -    def processCapabilities(self, capabilityHash): 
645          # must lock the table to keep rhnpush or whomever from causing 
646          # this transaction to fail 
647          lock_sql = "lock table rhnPackageCapability in exclusive mode" 
648          sql = "select lookup_package_capability_fast(:name, :version) as id from dual" 
649          try: 
650              self.dbmodule.execute_secondary(lock_sql) 
651              h = self.dbmodule.prepare_secondary(sql) 
652              for name, version in capabilityHash.keys(): 
653                  ver = version 
654                  if version == '': 
655                      ver = None 
656                  h.execute(name=name, version=ver) 
657                  row = h.fetchone_dict() 
658                  capabilityHash[(name, version)] = row['id'] 
659              self.dbmodule.commit_secondary()  # commit also unlocks the table 
660          except Exception: 
661              e = sys.exc_info()[1] 
662              self.dbmodule.execute_secondary("rollback") 
663              raise e 
664   
665      # Same as processCapabilities 
666 -    def lookupChecksums(self, checksumHash): 
667          if not checksumHash: 
668              return 
669          # must lock the table to keep rhnpush or whomever from causing 
670          # this transaction to fail 
671          lock_sql = "lock table rhnChecksum in exclusive mode" 
672          sql = "select lookup_checksum_fast(:ctype, :csum) id from dual" 
673          try: 
674              self.dbmodule.execute_secondary(lock_sql) 
675              h = self.dbmodule.prepare_secondary(sql) 
676              for k in checksumHash.keys(): 
677                  ctype, csum = k 
678                  if csum != '': 
679                      h.execute(ctype=ctype, csum=csum) 
680                      row = h.fetchone_dict() 
681                      if row: 
682                          checksumHash[k] = row['id'] 
683              self.dbmodule.commit_secondary()  # commit also unlocks the table 
684          except Exception: 
685              e = sys.exc_info()[1] 
686              self.dbmodule.execute_secondary("rollback") 
687              raise e 
688   
689   
690 -def SQLBackend(): 
691      if CFG.DB_BACKEND == ORACLE: 
692          backend = OracleBackend() 
693      elif CFG.DB_BACKEND == POSTGRESQL: 
694          backend = PostgresqlBackend() 
695      backend.init() 
696      return backend 
697