Uploaded image for project: 'Pegasus'
  1. Pegasus
  2. PM-1533

Database Schema Cleanup

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: master, 5.0.0
    • Component/s: CLI: pegasus-db-admin
    • Labels:
      None

      Description

      Unused table —

      sequences

      Redundant Indices —

      Index("wf_id_KEY", Workflow.wf_id, unique=True)
      Index(
          "UNIQUE_WORKFLOWSTATE",
          Workflowstate.wf_id,
          Workflowstate.state,
          Workflowstate.timestamp,
          unique=True,
      )
      Index("job_id_KEY", Job.job_id, unique=True)
      Index(
          "UNIQUE_JOB_EDGE",
          JobEdge.wf_id,
          JobEdge.parent_exec_job_id,
          JobEdge.child_exec_job_id,
          unique=True,
      )
      Index("job_instance_id_KEY", JobInstance.job_instance_id, unique=True)
      Index(
          "UNIQUE_JOBSTATE",
          Jobstate.job_instance_id,
          Jobstate.state,
          Jobstate.timestamp,
          Jobstate.jobstate_submit_seq,
          unique=True,
      )
      Index("tag_id_KEY", Tag.tag_id, unique=True)
      Index("task_id_KEY", Task.task_id, unique=True)
      Index(
          "UNIQUE_TASK_EDGE",
          TaskEdge.wf_id,
          TaskEdge.parent_abs_task_id,
          TaskEdge.child_abs_task_id,
          unique=True,
      )
      Index("invocation_id_KEY", Invocation.invocation_id, unique=True)
      Index("integrity_id_KEY", IntegrityMetrics.integrity_id, unique=True)
      Index(
          "UNIQUE_MASTER_WORKFLOWSTATE",
          DashboardWorkflowstate.wf_id,
          DashboardWorkflowstate.state,
          DashboardWorkflowstate.timestamp,
          unique=True,
      )
      Index("rc_meta_unique", RCMeta.lfn_id, RCMeta.key, unique=True)


      Missing —

      Job — JobEdge ForeignKeys
      Task — TaskEdge ForeignKeys


      Add Indices —

      Workflowstate.tiimestamp
      Jobstate.jobstate_submit_seq


      Use Unique Constraint over Index —

      Index("wf_uuid_UNIQUE", Workflow.wf_uuid, unique=True)
      Index(
          "UNIQUE_HOST", Host.wf_id, Host.site, Host.hostname, Host.ip, unique=True,
      )
      Index("UNIQUE_JOB", Job.wf_id, Job.exec_job_id, unique=True)
      Index(
          "UNIQUE_JOB_INSTANCE", JobInstance.job_id, JobInstance.job_submit_seq, unique=True,
      )
      Index("UNIQUE_TAG", Tag.job_instance_id, Tag.wf_id, unique=True)
      Index("UNIQUE_TASK", Task.wf_id, Task.abs_task_id, unique=True)
      Index(
          "UNIQUE_INVOCATION",
          Invocation.job_instance_id,
          Invocation.task_submit_seq,
          unique=True,
      )
      Index(
          "UNIQUE_INTEGRITY",
          IntegrityMetrics.job_instance_id,
          IntegrityMetrics.type,
          IntegrityMetrics.file_type,
          unique=True,
      )
      Index("UNIQUE_MASTER_WF_UUID", DashboardWorkflow.wf_uuid, unique=True)
      Index(
          "UNIQUE_ENSEMBLE_WORKFLOW",
          EnsembleWorkflow.ensemble_id,
          EnsembleWorkflow.name,
          unique=True,
      )
      Index("ix_rc_lfn", RCLFN.lfn, unique=True)
      Index("UNIQUE_PFN", RCPFN.lfn_id, RCPFN.pfn, RCPFN.site, unique=True)


      Make sense? —

      Index(
          "UNIQUE_WORKFLOW_META",
          WorkflowMeta.wf_id,
          WorkflowMeta.key,
          WorkflowMeta.value,
          unique=True,
      ) # Remove index or Just use primary_key over all columns
      Index(
          "UNIQUE_TASK_META", TaskMeta.task_id, TaskMeta.key, TaskMeta.value, unique=True,
      ) # Remove index or Just use primary_key over all columns

      Index("UNIQUE_ENSEMBLE", Ensemble.username, Ensemble.name) # Missing unique=True

        Attachments

          Activity

            People

            • Assignee:
              rafsilva Rafael Ferreira Da Silva
              Reporter:
              mayani Rajiv Mayani
            • Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: