def createLatest(db, withIndexes = True):
    assert("Instances" in db.tables)
    assert("Nodes" in db.tables)
    assert("UserGroupInstancesCache" in db.tables)
    cu = db.cursor()
    commit = False
    from conary.repository.netrepos.versionops import LATEST_TYPE_ANY,\
         LATEST_TYPE_PRESENT, LATEST_TYPE_NORMAL
    from conary.repository.netrepos.instances import INSTANCE_PRESENT_MISSING,\
        INSTANCE_PRESENT_NORMAL, INSTANCE_PRESENT_HIDDEN
    from conary.trove import TROVE_TYPE_NORMAL, TROVE_TYPE_REDIRECT, \
         TROVE_TYPE_REMOVED

    # LATEST_TYPE_ANY: redirects, removed, and normal
    if "LatestViewAny_sub" not in db.views:
        cu.execute("""
        %(CREATEVIEW)s LatestViewAny_sub AS
        SELECT
            ugi.userGroupId AS userGroupId,
            n.itemId AS itemId,
            n.branchId AS branchId,
            i.flavorId AS flavorId,
            max(n.finalTimestamp) AS finalTimestamp
        FROM UserGroupInstancesCache AS ugi
        JOIN Instances AS i USING(instanceId)
        JOIN Nodes AS n USING(itemId, versionId)
        WHERE i.isPresent = %%(present)d
        GROUP BY ugi.userGroupId, n.itemId, n.branchId, i.flavorId
        """ % db.keywords % {"present" : INSTANCE_PRESENT_NORMAL, })
        db.views["LatestViewAny_sub"] = True
        commit = True
    if "LatestViewAny"  not in db.views:
        cu.execute("""
        %(CREATEVIEW)s LatestViewAny AS
        SELECT
            sub.userGroupId AS userGroupId,
            sub.itemId AS itemId,
            sub.branchId AS branchId,
            sub.flavorId AS flavorId,
            Nodes.versionId AS versionId
        FROM LatestViewAny_sub as sub
        JOIN Nodes USING(itemId, branchId, finalTimestamp)
        JOIN Instances USING(itemId, versionId)
        WHERE Instances.flavorId = sub.flavorId
          AND Instances.isPresent = %%(present)d
        """ % db.keywords % {"present" : INSTANCE_PRESENT_NORMAL})
        db.views["LatestViewAny"] = True
        commit = True

    # LATEST_TYPE_PRESENT: redirects and normal
    if "LatestViewPresent_sub" not in db.views:
        cu.execute("""
        %(CREATEVIEW)s LatestViewPresent_sub AS
        SELECT
            ugi.userGroupId AS userGroupId,
            n.itemId AS itemId,
            n.branchId AS branchId,
            i.flavorId AS flavorId,
            max(n.finalTimestamp) AS finalTimestamp
        FROM UserGroupInstancesCache AS ugi
        JOIN Instances AS i USING(instanceId)
        JOIN Nodes AS n USING(itemId, versionId)
        WHERE i.isPresent = %%(present)d
          AND i.troveType != %%(removed)d
        GROUP BY ugi.userGroupId, n.itemId, n.branchId, i.flavorId
        """ % db.keywords % { "present": INSTANCE_PRESENT_NORMAL,
                              "removed"  : TROVE_TYPE_REMOVED, })
        db.views["LatestViewPresent_sub"] = True
        commit = True
    if "LatestViewPresent"  not in db.views:
        cu.execute("""
        %(CREATEVIEW)s LatestViewPresent AS
        SELECT
            sub.userGroupId AS userGroupId,
            sub.itemId AS itemId,
            sub.branchId AS branchId,
            sub.flavorId AS flavorId,
            Nodes.versionId AS versionId
        FROM LatestViewPresent_sub as sub
        JOIN Nodes USING(itemId, branchId, finalTimestamp)
        JOIN Instances USING(itemId, versionId)
        WHERE Instances.flavorId = sub.flavorId
          AND Instances.isPresent = %%(present)d
          AND Instances.troveType != %%(trove)d
        """ % db.keywords % {"present" : INSTANCE_PRESENT_NORMAL,
                             "trove" : TROVE_TYPE_REMOVED, })
        db.views["LatestViewPresent"] = True
        commit = True

    # LATEST_TYPE_NORMAL: hide branches which end in redirects
    if "LatestViewNormal" not in db.views:
        assert("LatestViewPresent_sub" in db.views)
        cu.execute("""
        %(CREATEVIEW)s LatestViewNormal AS
        SELECT
            sub.userGroupId AS userGroupId,
            sub.itemId AS itemId,
            sub.branchId AS branchId,
            sub.flavorId AS flavorId,
            Nodes.versionId AS versionId
        FROM LatestViewPresent_sub as sub
        JOIN Nodes USING(itemId, branchId, finalTimestamp)
        JOIN Instances USING(itemId, versionId)
        WHERE Instances.flavorId = sub.flavorId
          AND Instances.isPresent = %%(present)d
          AND Instances.troveType = %%(trove)d
        """ % db.keywords % {"present" : INSTANCE_PRESENT_NORMAL,
                             "trove" : TROVE_TYPE_NORMAL,
                             "removed" : TROVE_TYPE_REMOVED, })
        db.views["LatestViewNormal"] = True
        commit = True

    # LatestView is a union of the 3 smaller latest views
    if "LatestView" not in db.views:
        cu.execute("""
        %%(CREATEVIEW)s LatestView AS
        SELECT %d as latestType, userGroupId, itemId, branchId, flavorId, versionId
        FROM LatestViewAny
        UNION ALL
        SELECT %d as latestType, userGroupId, itemId, branchId, flavorId, versionId
        FROM LatestViewPresent
        UNION ALL
        SELECT %d as latestType, userGroupId, itemId, branchId, flavorId, versionId
        FROM LatestViewNormal
        """ % (LATEST_TYPE_ANY,LATEST_TYPE_PRESENT,LATEST_TYPE_NORMAL) % db.keywords)
        db.views["LatestView"] = True
        commit = True

    # Latest, as seen by each usergroup
    if "LatestCache" not in db.tables:
        assert("Items" in db.tables)
        assert("Branches" in db.tables)
        assert("Flavors" in db.tables)
        assert("Versions" in db.tables)
        assert("UserGroups" in db.tables)
        cu.execute("""
        CREATE TABLE LatestCache(
            userGroupId     INTEGER NOT NULL,
            itemId          INTEGER NOT NULL,
            branchId        INTEGER NOT NULL,
            flavorId        INTEGER NOT NULL,
            versionId       INTEGER NOT NULL,
            latestType      INTEGER NOT NULL,
            changed         NUMERIC(14,0) NOT NULL DEFAULT 0,
            CONSTRAINT LC_userGroupId_fk
                FOREIGN KEY (userGroupId) REFERENCES UserGroups(userGroupId)
                ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT LC_itemId_fk
                FOREIGN KEY (itemId) REFERENCES Items(itemId)
                ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT LC_branchId_fk
                FOREIGN KEY (branchId) REFERENCES Branches(branchId)
                ON DELETE RESTRICT ON UPDATE CASCADE,
            CONSTRAINT LC_flavorId_fk
                FOREIGN KEY (flavorId) REFERENCES Flavors(flavorId)
                ON DELETE RESTRICT ON UPDATE CASCADE,
            CONSTRAINT LC_versionId_fk
                FOREIGN KEY (versionId) REFERENCES Versions(versionId)
                ON DELETE CASCADE ON UPDATE CASCADE
        ) %(TABLEOPTS)s""" % db.keywords)
        db.tables["LatestCache"] = []
        commit = True
    if withIndexes:
        # sanity index that isn't very useful as an index due to its size...
        db.createIndex("LatestCache", "LC_userGroupId_uniq",
                       "userGroupId,latestType,itemId,branchId,flavorId",
                       unique=True)
        # create needed FKs
        db.createIndex("LatestCache", "LC_itemId_fk", "itemId")
        db.createIndex("LatestCache", "LC_branchId_fk", "branchId")
        db.createIndex("LatestCache", "LC_flavorId_fk", "flavorId")
        db.createIndex("LatestCache", "LC_versionId_fk", "versionId")
        if createTrigger(db, "LatestCache"):
            commit = True

    if commit:
        db.loadSchema()
dataset/ETHPy150Open sassoftware/conary/conary/server/schema.py/createLatest/original