
--------------------------------------------------------------
-- The plasma view for ADLink
--------------------------------------------------------------
CREATE VIEW Pl_ADLink AS
  SELECT
    a.*,
    m.sourceOID,
    m.sourceOVer
  FROM
    ADLink a, ManyPtrTable m
  WHERE
    m.targetOID=a.OID AND m.targetOVer=a.OVer AND
      m.classAttrib='ADViewRoot.plasma';



--------------------------------------------------------------
-- FilesOut (FHCFilesOut) information for each part version
--------------------------------------------------------------
CREATE VIEW FOForVersions AS
  SELECT
    a.OVer,
    a.OID,
    a.osrelUserId,
    a.osrelFileId,
    o.osrelWorkAreaId,
    t.OVer as versionOVer,
    t.OID as versionOID,
    t.versionId,
    c.OID as contextOID,
    c.OID as contextOVer
  FROM
    TCVersionClass t
      LEFT OUTER JOIN FHCFilesOut a ON a.osrelFileId=t.osrelFileId
      LEFT OUTER JOIN FHCOVersion o ON o.OID=a.osrelOVersionId
      LEFT OUTER JOIN FHCOVersion c ON c.osrelWorkAreaId=o.osrelWorkAreaId;


----------------------------------------------------------
-- parts visible where contextName is a release tip
-- (context is a release name)
----------------------------------------------------------
CREATE VIEW releaseTipParts AS
  SELECT
    r.name as contextName,
    r.pTipOVer as contextOID,
    r.name as contextRelease,
    r.name as releaseName,
    r.oid  as osrelReleaseId,
    r.oid  as releaseId,
    n.oid,
    n.over
  FROM
    NuVersions n,
    FHCRelease r
  WHERE
    n.contextId=r.oid;

----------------------------------------------------------
-- deltas for contexts along the release trunk
----------------------------------------------------------
CREATE VIEW trunkDelta AS
  SELECT
    o.SID as contextName,
    o.OID as contextOID,
    r.name as contextRelease,
    r.name as releaseName,
    r.OID  as osrelReleaseId,
    r.OID as releaseId,
    v.oid,
    v.previousOVer as OVer
  FROM
    FHCOVersion o,
    FHCRelease r,
    TCVersionClass v
  WHERE
    -- given an OVer on the release trunk
    o.osrelWorkAreaId is null AND
    r.oid = o.osrelReleaseId AND

    -- find a version record beyond this point
    v.contextId = r.oid AND
    v.sidSequence > o.sidSequence AND

    -- which has a predecessor at or before this point
    v.previousSidSequenc <= o.sidSequence;

----------------------------------------------------------
-- parts visible for contexts along the release trunk
-- (context is a release version)
----------------------------------------------------------
CREATE VIEW trunkParts AS
    -- Release tips which are not to be replaced by a delta
    SELECT
      o.SID as contextName,
      o.OID as contextOID,
      r.contextRelease,
      r.releaseName,
      r.osrelReleaseId,
      r.releaseId,
      r.OID,
      r.OVer
    FROM
      FHCOVersion o,
      ReleaseTipParts r
    WHERE
      o.osrelReleaseId = r.releaseId AND
      o.osrelWorkAreaid is null AND
      r.OID not in (select OID from trunkDelta d
                               where d.contextOID = o.OID)
  UNION ALL
    -- deltas which are non null
    SELECT
      t.contextName,
      t.contextOID,
      t.contextRelease,
      t.releaseName,
      t.osrelReleaseId,
      t.releaseId,
      t.OID,
      t.OVer
    FROM
      trunkDelta t
    WHERE
      t.OVer is not null;

----------------------------------------------------------
-- Redundant copy (for performance)
----------------------------------------------------------

CREATE VIEW trunkPartscopy2 AS
    -- Release tips which are not to be replaced by a delta
    SELECT
      o.SID as contextName,
      o.OID as contextOID,
      r.contextRelease,
      r.releaseName,
      r.osrelReleaseId,
      r.releaseId,
      r.OID,
      r.OVer
    FROM
      FHCOVersion o,
      ReleaseTipParts r
    WHERE
      o.osrelReleaseId = r.releaseId AND
      o.osrelWorkAreaid is null AND
      r.OID not in (select OID from trunkDelta d
                               where d.contextOID = o.OID)
  UNION ALL
    -- deltas which are non null
    SELECT
      t.contextName,
      t.contextOID,
      t.contextRelease,
      t.releaseName,
      t.osrelReleaseId,
      t.releaseId,
      t.OID,
      t.OVer
    FROM
      trunkDelta t
    WHERE
      t.OVer is not null;

CREATE VIEW trunkPartscopy AS
    -- Release tips which are not to be replaced by a delta
    SELECT
      o.SID as contextName,
      o.OID as contextOID,
      r.contextRelease,
      r.releaseName,
      r.osrelReleaseId,
      r.releaseId,
      r.OID,
      r.OVer
    FROM
      FHCOVersion o,
      ReleaseTipParts r
    WHERE
      o.osrelReleaseId = r.releaseId AND
      o.osrelWorkAreaid is null AND
      r.OID not in (select OID from trunkDelta d
                               where d.contextOID = o.OID)
  UNION ALL
    -- deltas which are non null
    SELECT
      t.contextName,
      t.contextOID,
      t.contextRelease,
      t.releaseName,
      t.osrelReleaseId,
      t.releaseId,
      t.OID,
      t.OVer
    FROM
      trunkDelta t
    WHERE
      t.OVer is not null;

----------------------------------------------------------
-- delta for workarea/driver branch tips
----------------------------------------------------------
CREATE VIEW branchTipDelta AS
  SELECT
    w.name as contextName,
    w.pTipOVer as contextOID,
    r.name as contextRelease,
    r.name as releaseName,
    r.OID  as osrelReleaseId,
    r.oid  as releaseId,
    n.oid,
    n.over
  FROM
    NuVersions n,
    FHCWorkarea w,
    FHCRelease r
  WHERE
    w.osrelReleaseId=r.oid AND
    n.contextId=w.oid;

----------------------------------------------------------
-- parts visible for workarea/driver branch tips
-- (context is a workarea/driver name)
----------------------------------------------------------
CREATE VIEW branchTipParts AS
    -- release versions which are not to be replaced by a delta
    SELECT
      w.name as contextName,
      w.pTipOVer as contextOID,
      r.name as contextRelease,
      r.name as releaseName,
      r.OID  as osrelReleaseId,
      r.OID as releaseId,
      t.oid,
      t.over
    FROM
      FHCWorkarea w,
      FHCRelease r,
      FHCOVersion o,
      ReleaseTipParts t
    WHERE
      r.OID = w.osrelReleaseId AND
      r.OID = o.osrelReleaseId AND
      r.OID = t.releaseId AND
      o.osrelWorkAreaid is null AND
      t.OID not in (select OID from trunkDelta d
                               where d.contextOID = o.OID) AND
      o.OID = w.osrelBranchPointId AND
      t.OID not in (select OID from branchTipDelta d
                               where d.contextOID = w.pTipOver)
 UNION ALL
    -- release delta from branch point to tip, unless also on branch
    SELECT
      w.name as contextName,
      w.pTipOVer as contextOID,
      r.name as contextRelease,
      r.name as releaseName,
      r.OID  as osrelReleaseId,
      r.OID as releaseId,
      t.oid,
      t.over
    FROM
      FHCWorkarea w,
      FHCRelease r,
      trunkDelta t
    WHERE
      r.OID=w.osrelReleaseId AND
      t.OVer is not null AND
      t.contextOID = w.osrelBranchPointId AND
      t.OID not in (select OID from branchTipDelta d
                               where d.contextOID = w.pTipOver)
  UNION ALL
    -- branch tip
    SELECT
      b.contextName,
      b.contextOID,
      b.contextRelease,
      b.releaseName,
      b.osrelReleaseId,
      b.releaseId,
      b.oid,
      b.over
    FROM
      branchTipDelta b;

----------------------------------------------------------
-- Redundant copy (for performance)
----------------------------------------------------------

CREATE VIEW branchTipPartscopy AS
    -- release versions which are not to be replaced by a delta
    SELECT
      w.name as contextName,
      w.pTipOVer as contextOID,
      r.name as contextRelease,
      r.name as releaseName,
      r.OID  as osrelReleaseId,
      r.OID as releaseId,
      t.oid,
      t.over
    FROM
      FHCWorkarea w,
      FHCRelease r,
      FHCOVersion o,
      ReleaseTipParts t
    WHERE
      r.OID = w.osrelReleaseId AND
      r.OID = o.osrelReleaseId AND
      r.OID = t.releaseId AND
      o.osrelWorkAreaid is null AND
      t.OID not in (select OID from trunkDelta d
                               where d.contextOID = o.OID) AND
      o.OID = w.osrelBranchPointId AND
      t.OID not in (select OID from branchTipDelta d
                               where d.contextOID = w.pTipOver)
 UNION ALL
    -- release delta from branch point to tip, unless also on branch
    SELECT
      w.name as contextName,
      w.pTipOVer as contextOID,
      r.name as contextRelease,
      r.name as releaseName,
      r.OID  as osrelReleaseId,
      r.OID as releaseId,
      t.oid,
      t.over
    FROM
      FHCWorkarea w,
      FHCRelease r,
      trunkDelta t
    WHERE
      r.OID=w.osrelReleaseId AND
      t.OVer is not null AND
      t.contextOID = w.osrelBranchPointId AND
      t.OID not in (select OID from branchTipDelta d
                               where d.contextOID = w.pTipOver)
  UNION ALL
    -- branch tip
    SELECT
      b.contextName,
      b.contextOID,
      b.contextRelease,
      b.releaseName,
      b.osrelReleaseId,
      b.releaseId,
      b.oid,
      b.over
    FROM
      branchTipDelta b;

----------------------------------------------------------
-- deltas for contexts along any driver/workarea branch
----------------------------------------------------------
CREATE VIEW branchDelta AS
  SELECT
    o.SID as contextName,
    o.OID as contextOID,
    r.name as contextRelease,
    r.name as releaseName,
    r.OID  as osrelReleaseId,
    r.OID as releaseId,
    v.oid,
    v.previousOVer as OVer
  FROM
    FHCOVersion o,
    FHCRelease r,
    FHCWorkArea w,
    TCVersionClass v
  WHERE
    -- given an OVer on the release trunk
    o.osrelWorkAreaId = w.oid AND
    o.osrelReleaseId = r.oid AND

    -- find a version record beyond this point
    v.contextId = w.oid AND
    v.sidSequence > o.sidSequence AND

    -- which has a predecessor at or before this point
    v.previousSidSequenc <= o.sidSequence;

----------------------------------------------------------
-- parts visible for contexts along driver/workarea branches
-- (context is a workarea/driver version)
----------------------------------------------------------
CREATE VIEW branchParts AS
    -- Branch tips which are not to be replaced by a delta
    SELECT
      o.SID as contextName,
      o.OID as contextOID,
      b.contextRelease,
      b.releaseName,
      b.osrelReleaseId,
      b.releaseId,
      b.OID,
      b.OVer
    FROM
      FHCOVersion o,
      FHCWorkarea w,
      BranchTipParts b
    WHERE
      o.osrelReleaseId = b.releaseId AND
      o.osrelWorkAreaId = w.oid AND
      b.OID not in (select OID from branchDelta d
                               where d.contextOID = o.OID) AND
      b.contextName=w.name
  UNION ALL
    -- deltas which are non null
    SELECT
      b.contextName,
      b.contextOID,
      b.contextRelease,
      b.releaseName,
      b.osrelReleaseId,
      b.releaseId,
      b.OID,
      b.OVer
    FROM
      branchDelta b
    WHERE
      b.OVer is not null;

----------------------------------------------------------
-- Redundant copy (for performance)
----------------------------------------------------------

CREATE VIEW branchPartscopy AS
    -- Branch tips which are not to be replaced by a delta
    SELECT
      o.SID as contextName,
      o.OID as contextOID,
      b.contextRelease,
      b.releaseName,
      b.osrelReleaseId,
      b.releaseId,
      b.OID,
      b.OVer
    FROM
      FHCOVersion o,
      FHCWorkarea w,
      BranchTipParts b
    WHERE
      o.osrelReleaseId = b.releaseId AND
      o.osrelWorkAreaId = w.oid AND
      b.OID not in (select OID from branchDelta d
                               where d.contextOID = o.OID) AND
      b.contextName=w.name
  UNION ALL
    -- deltas which are non null
    SELECT
      b.contextName,
      b.contextOID,
      b.contextRelease,
      b.releaseName,
      b.osrelReleaseId,
      b.releaseId,
      b.OID,
      b.OVer
    FROM
      branchDelta b
    WHERE
      b.OVer is not null;

----------------------------------------------------------
-- The latest part versions visible from the selected context
-- NOTE: Context is selected by name
-- (all context views)
----------------------------------------------------------
CREATE VIEW PartViewVersions AS
    SELECT * from releaseTipParts
  UNION ALL
    SELECT * from trunkPartscopy
  UNION ALL
    SELECT * from branchTipPartscopy
  UNION ALL
    SELECT * from branchParts;


----------------------------------------------------------
-- The latest part versions visible from the selected OVer
-- NOTE: Context is selected by OVer Id
-- (only the version context views)
----------------------------------------------------------
CREATE VIEW PartViewOIDOVers AS
    SELECT * from trunkParts
  UNION ALL
    SELECT * from branchParts;


--------------------------------------------------------------
-- Context-related Build Event version ID's
--------------------------------------------------------------
CREATE VIEW VersionBEIDs AS
  SELECT DISTINCT
    p.OVer,
    p.OID,
    v.OID as versionOID,
    v.OVer as versionOVer,
    v.versionId,
    p.contextOID
  FROM
    PartViewOIDOVers p,
      FHCVersion f,
      TCVersionClass v,
      ManyPtrTable m
  WHERE
    v.OID=f.OID AND v.OVer=f.OVer AND
        m.classAttrib='ADViewRoot.plasma' AND m.targetOID=f.generatedBy AND
        p.OID=m.sourceOID;


--------------------------------------------------------------
-- Translates for a given Build Event Version
--------------------------------------------------------------
CREATE VIEW TranslatesForBE AS
  SELECT
    b.OVer,
    b.OID,
    l.targetOID,
    l.targetOVer
  FROM
    FHBuildEvent_versn b,
    ManyPtrTable l
  WHERE
    l.sourceOID=b.OID AND
      l.sourceOVer=b.OVer AND
      l.classAttrib='FHBuildEvent_version.translates';


--------------------------------------------------------------
-- Build Event versions for each Part Version
--------------------------------------------------------------
CREATE VIEW BEsForVersions AS
  SELECT
    b.OVer,
    b.OID,
    b.osrelBuilderId,
    b.beTS,
    b.builderRC,
    b.outputFileParms,
    b.buildParms,
    (select count(*) from TranslatesForBE where
       OVer=b.OVer AND OID=b.OID) as numTranslates,
    v.versionOID,
    v.versionOVer,
    v.versionId,
    v.contextOID
  FROM
    VersionBEIDs v,
      FHBuildEvent_versn b
  WHERE
    b.OID=v.OID AND b.OVer=v.OVer;


--------------------------------------------------------------
-- Includes for a given Part Version
--------------------------------------------------------------
CREATE VIEW DependsOnForPart AS
  SELECT
    v.OVer,
    v.OID,
    l.relOID,
    l.relOVer
  FROM
    FHCVersion v,
    ADLink_source l
  WHERE
    l.sourceOID=v.OID AND
      l.sourceOVer=v.OVer AND
      l.classAttr='FHCVersion.dependsOn';


--------------------------------------------------------------
-- The dependency information for a given Part Version
--------------------------------------------------------------
CREATE VIEW InputsForVersions AS
  SELECT
    v.versionId,
    (select count(*) from DependsOnForPart where
       OVer=v.OVer AND OID=v.OID) as numIncludes
  FROM
    TCVersionClass v;


--------------------------------------------------------------
-- Inputs for a build event
--------------------------------------------------------------
CREATE VIEW InputsToBE AS
  SELECT
    t.sourceOVer as parentOVer,
    t.sourceOID as parentOID,
    i.pInput as childOID
  FROM
    ManyPtrTable t
    INNER JOIN Pl_FBrsrdyn i ON i.OID=t.targetOID AND   --FHBTransformedByInfo
          i.sourceOID=t.sourceOID AND i.sourceOVer=t.sourceOVer AND
          i.pBE=t.sourceOID
  WHERE
    t.classAttrib='FHBuildEvent_version.translates';


--------------------------------------------------------------
-- The parent information for a specified part and its parent
--------------------------------------------------------------
CREATE VIEW ParentsForPart AS
      -- DependsOn relationships
      SELECT DISTINCT
        c.contextOID,
        d.OVer as parentOVer,
        d.OID as parentOID,
        t.typeName as parentType,
        p.name as parentName,
        v.OVer as childOVer,
        v.OID as childOID,
        v.versionId as childId,
        'DependentOf' as typeOfRelation
      FROM
        DependsOnForPart d
        INNER JOIN TCCollectorClass t ON t.OID=d.OID
        INNER JOIN TCVersionClass f ON f.OID=d.OID AND f.OVer=d.OVer
        INNER JOIN TCSourceObject s ON s.OID=f.osrelTCSourceObjct
        INNER JOIN FHCPath p ON p.OID=s.osrelNuPathId
        INNER JOIN PartViewOIDOVers c ON c.OID=d.OID AND c.OVer=d.OVer
        INNER JOIN ADLink l ON l.OVer=d.relOVer AND l.OID=d.relOID
        INNER JOIN TCVersionClass v ON v.OID=l.adTarget
  UNION ALL
      -- InputTo relationships
      SELECT DISTINCT
        b.contextOID,
        b.versionOVer as parentOVer,
        b.versionOID as parentOID,
        t.typeName as parentType,
        p.name as parentName,
        v.OVer as childOVer,
        v.OID as childOID,
        v.versionId as childId,
        'InputTo' as typeOfRelation
      FROM
        BEsForVersions b
        INNER JOIN TCCollectorClass t ON t.OID=b.versionOID
        INNER JOIN TCVersionClass f ON f.OID=b.versionOID AND f.OVer=b.versionOVer
        INNER JOIN TCSourceObject s ON s.OID=f.osrelTCSourceObjct
        INNER JOIN FHCPath p ON p.OID=s.osrelNuPathId
        INNER JOIN PartViewOIDOVers c ON c.OID=f.OID AND c.OVer=f.OVer AND
              c.contextOID=b.contextOID
        INNER JOIN InputsToBE i ON b.OID=i.parentOID AND b.OVer=i.parentOVer
        INNER JOIN TCVersionClass v ON v.OID=i.childOID
  UNION ALL
      -- SiblingOutput relationships
      SELECT DISTINCT
        b.contextOID,
        b.versionOVer as parentOVer,
        b.versionOID as parentOID,
        t.typeName as parentType,
        p.name as parentName,
        v.OVer as childOVer,
        v.OID as childOID,
        v.versionId as childId,
        'SiblingOutput' as typeOfRelation
      FROM
        BEsForVersions b
        INNER JOIN TCCollectorClass t ON t.OID=b.versionOID
        INNER JOIN TCVersionClass f ON f.OID=b.versionOID AND f.OVer=b.versionOVer
        INNER JOIN TCSourceObject s ON s.OID=f.osrelTCSourceObjct
        INNER JOIN FHCPath p ON p.OID=s.osrelNuPathId
        INNER JOIN PartViewOIDOVers c ON c.OID=f.OID AND c.OVer=f.OVer AND
              c.contextOID=b.contextOID
        INNER JOIN BEsForVersions e ON e.contextOID=b.contextOID AND e.OID=b.OID
        INNER JOIN TCVersionClass v ON v.versionId=e.versionId AND v.OID!=f.OID
        INNER JOIN PartViewOIDOVers d ON d.OID=v.OID AND d.OVer=v.OVer AND
              d.contextOID=b.contextOID;


--------------------------------------------------------------
-- Plasma for a specified part
--------------------------------------------------------------
CREATE VIEW PlasmaForVersion AS
  SELECT
    v.OVer as versionOVer,
    v.OID as versionOID,
    m.targetOVer as OVer,
    m.targetOID as OID
  FROM
    TCVersionClass v, ManyPtrTable m
  WHERE
    m.sourceOVer=v.OVer AND m.sourceOID=v.OID AND
      m.classAttrib='ADViewRoot.plasma';


--------------------------------------------------------------
-- FilesOut (FHCFilesOut) information for PartFullView entries
--------------------------------------------------------------
CREATE VIEW FOForWorkArea AS
  SELECT
    f.OID,
    f.osrelUserId,
    f.osrelFileId as fileId,
    w.OID as osrelWorkAreaId
  FROM
    FHCFilesOut f
    INNER JOIN FHCOVersion o ON o.OID=f.osrelOVersionId
    INNER JOIN FHCWorkArea w ON w.OID=o.osrelWorkAreaId;


--------------------------------------------------------------
-- SourceObject info for Part Versions
--------------------------------------------------------------
CREATE VIEW VersionSourceInfo AS
  SELECT
    a.*,
    s.OID as sourceId,
    s.nuDropDate,
    s.nuAddDate,
    s.changeDate,
    s.osrelNuPathId
  FROM
    PartViewVersions a
    INNER JOIN TCVersionClass v ON v.OID=a.OID and v.OVer=a.OVer
    INNER JOIN TCSourceObject s ON s.OID=v.osrelTCSourceObjct;


--------------------------------------------------------------
-- Uncommitted WorkAreas
--------------------------------------------------------------
CREATE VIEW UncommitWAs AS
    -- Track WorkAreas
    SELECT
      w.OID,
      w.name,
      w.pTipOVer as tipOVer,
      t.OID as trackId,
      CAST (NULL as DBOID) as driverId,
      t.state
    FROM
      FHCWorkArea w, FHCTrack t
    WHERE
      t.osrelWorkAreaId=w.OID AND
        t.state not in ('commit','complete','test')
  UNION ALL
    -- Driver WorkAreas
    SELECT
      w.OID,
      w.name,
      w.pTipOVer as tipOVer,
      CAST (NULL as DBOID) as trackId,
      d.OID as driverId,
      d.state
    FROM
      FHCWorkArea w, FHCLevel d
    WHERE
      d.osrelWorkAreaId=w.OID AND
        d.state not in ('commit','complete');

