Review of database constraints in the stager schema

Purpose

Investigate which database constraints can be enforced in the current stager schema and track software failures when such constraints are applied.

Constraints are classified as FK and NOT NULL. A Class/ER Diagram of the stager schema can be found here.

Foreign keys

Color codes:

  • Blue for non-enforceable constraints by design
  • Red for currently non-enforceable constraints because of software bugs
  • Black for theoretically enforceable constraints, either as FKs or as triggers. To be then tested whether the software breaks
  • Green for enforced and working constraints

Relationship (n->1) Nullable Notes
SvcClass <-> DiskPool - n-n relationship, constraints enforced in the extra table
SvcClass -> FileClass Y To be implemented
MigrationRouting -> TapePool - Enforced from 2.1.12
MigrationRouting -> FileClass - Enforced from 2.1.12
*Request -> SvcClass Y Different requests exist as different entities. File requests should be merged, then a constraint can be enforced
*Request -> Client Y See above. The Client table could be merged with a new FileRequest table
StageDiskCopyReplicaRequest -> DiskCopy N  
SubRequest -> *Request - See above. Case of object inheritance mapped to a database schema...
SubRequest -> CastorFile Y The request is inserted by the Request handler without verification of the CastorFile. Trigger needed, link must be respected if status > 3
SubRequest -> DiskCopy Y Same as above + link established only on job oriented requests (get, put, update)
SubRequest -> SubRequest Y SR.parent relationship. Not null only if status = 5, requires refactoring of the stager without fillRep/Obj API
CastorFile -> FileClass N Enforced from 2.1.8-3
CastorFile -> SvcClass N Enforced from 2.1.8-3
DiskCopy -> CastorFile N Enforced from 2.1.8-3
DiskCopy -> FileSystem Y Diskcopies may exist which do not belong to any filesystem if in status 1, 2, 5. Trigger needed
FileSystem -> DiskServer N Enforced from 2.1.8-3
FileSystem -> DiskPool Y Should be dropped. See below
DiskServer -> DiskPool Y When a diskserver is inserted for the first time, it does not belong to any pool. A DS in status 0 MUST belong to a pool. Trigger/check constraint needed
RecallJob -> CastorFile N Enforced from 2.1.13
RecallJob -> RecallMount N Enforced from 2.1.13
RecallMount -> RecallGroup N Enforced from 2.1.13
RecallUser -> RecallGroup N Enforced from 2.1.13
MigrationMount -> TapePool N Enforced from 2.1.12
MigrationJob -> MigrationMount N Enforced from 2.1.13
MigrationJob -> TapePool N Enforced from 2.1.12
MigrationJob -> CastorFile N Enforced from 2.1.12
MigratedSegment -> CastorFile N Enforced from 2.1.12
WhiteList -> SvcClass - Case of svcClass = '*' in the B/W list
BlackList -> SvcClass - Same as above
FileSystemsToCheck -> FileSystem N  
Accounting -> DiskPool N  
!SvcClass -> GcPolicy N Precisely, SvcClass.gcPolicy -> GcPolicy.name. A SvcClass must have a gcPolicy. Enforced from 2.1.13
DrainingFS -> FileSystem N  
DrainingFS -> SvcClass N  
DrainingDCS -> FileSystem N  

Not null

NOT NULL constraints are relevant on indexed fields. A current snapshot of all existing indexes follows (to be cleaned up yet).

TABLENAME COL NULLABLE

QUERYPARAMETER  QUERY Y
SEGMENT  COPY Y
TYPE2OBJ  TYPE N
DISKSERVER  NAME Y
CASTORFILE  FILEIDNSHOST -
CASTORFILE  LASTKNOWNFILENAME Y
FILECLASS  NAME Y
NBTAPECOPIESINFS  FSSTREAM -
CASTORFILE  SVCCLASS Y
DISKCOPY  CASTORFILE Y
DISKCOPY  FILESYSTEM Y
TAPECOPY  STATUS Y
FILESYSTEM  DISKPOOL Y
FILESYSTEM  DISKSERVER N
DISKCOPY  STATUS Y
DISKCOPY  GCWEIGHT Y
TAPECOPY  CASTORFILE Y
GCFILE  REQUEST Y
SEGMENT  TAPE Y
TAPE  VIDSIDETPMODE -
SVCCLASS  NAME N
NBTAPECOPIESINFS  STREAM Y
FILESYSTEM  RATE -

-- GiuseppeLoPresti - 05 Nov 2008

Edit | Attach | Watch | Print version | History: r21 < r20 < r19 < r18 < r17 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r21 - 2012-08-06 - GiuseppeLoPresti
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    DataManagement All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2023 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback