Beispiel #1
0
def count_removed_resources(destination, channel_id):
    """
    Queries the destination db to get the leaf node content_ids.
    Subtract available leaf nodes count on default db by available
    leaf nodes based on destination db leaf node content_ids.
    """
    bridge = Bridge(app_name=CONTENT_APP_NAME, sqlite_file_path=destination)
    connection = bridge.get_connection()
    ContentNodeTable = bridge.get_table(ContentNode)
    resource_node_ids_statement = (select([ContentNodeTable.c.id]).where(
        and_(
            ContentNodeTable.c.channel_id == channel_id,
            ContentNodeTable.c.kind != content_kinds.TOPIC,
        )).limit(batch_size))

    i = 0

    resource_node_ids = [
        coerce_key(cid[0]) for cid in connection.execute(
            resource_node_ids_statement.offset(i)).fetchall()
    ]

    content_ids_after_upgrade = set()

    # Batch the query here, as passing too many uuids into Django could cause
    # the a SQL query too large error. This will happen around about 30000+ uuids.
    # Could probably batch at 10000 rather than 1000 - but using 1000 to be defensive.

    while resource_node_ids:
        content_ids_after_upgrade.update((ContentNode.objects.filter_by_uuids(
            resource_node_ids,
            validate=False).exclude(kind=content_kinds.TOPIC).filter(
                available=True,
                channel_id=channel_id).values_list("content_id",
                                                   flat=True).distinct()))

        i += batch_size
        resource_node_ids = [
            coerce_key(cid[0]) for cid in connection.execute(
                resource_node_ids_statement.offset(i)).fetchall()
        ]

    total_resources_after_upgrade = len(content_ids_after_upgrade)

    return (ContentNode.objects.filter(
        channel_id=channel_id, available=True).exclude(
            kind=content_kinds.TOPIC).values("content_id").distinct().count() -
            total_resources_after_upgrade)
Beispiel #2
0
def update_on_device_resources():
    """
    Function to set on_device_resource on all topic trees to account for
    those that were imported before annotations were performed
    """
    bridge = Bridge(app_name=KolibriContentConfig.label)

    ContentNodeClass = bridge.get_class(ContentNode)

    ContentNodeTable = bridge.get_table(ContentNode)

    connection = bridge.get_connection()

    child = ContentNodeTable.alias()

    logger.info("Updating on_device_resource on existing channels")

    # start a transaction

    trans = connection.begin()

    # Update all leaf ContentNodes to have on_device_resource to 1 or 0
    connection.execute(
        ContentNodeTable.update()
        .where(
            # That are not topics
            ContentNodeTable.c.kind
            != content_kinds.TOPIC
        )
        .values(on_device_resources=cast(ContentNodeTable.c.available, Integer()))
    )

    # Expression to capture all available child nodes of a contentnode
    available_nodes = select([child.c.available]).where(
        and_(
            child.c.available == True,  # noqa
            ContentNodeTable.c.id == child.c.parent_id,
        )
    )

    # Expression that sums the total number of coach contents for each child node
    # of a contentnode
    on_device_num = select([func.sum(child.c.on_device_resources)]).where(
        and_(
            child.c.available == True,  # noqa
            ContentNodeTable.c.id == child.c.parent_id,
        )
    )

    for channel_id in ChannelMetadata.objects.all().values_list("id", flat=True):

        node_depth = (
            bridge.session.query(func.max(ContentNodeClass.level))
            .filter_by(channel_id=channel_id)
            .scalar()
        )

        # Go from the deepest level to the shallowest
        for level in range(node_depth, 0, -1):

            # Only modify topic availability here
            connection.execute(
                ContentNodeTable.update()
                .where(
                    and_(
                        ContentNodeTable.c.level == level - 1,
                        ContentNodeTable.c.channel_id == channel_id,
                        ContentNodeTable.c.kind == content_kinds.TOPIC,
                    )
                )
                # Because we have set availability to False on all topics as a starting point
                # we only need to make updates to topics with available children.
                .where(exists(available_nodes))
                .values(on_device_resources=on_device_num)
            )

    # commit the transaction
    trans.commit()

    bridge.end()
Beispiel #3
0
def get_automatically_updated_resources(destination, channel_id):
    """
    Queries the destination db to get the leaf node ids, where local file objects are unavailable.
    Get the available node ids related to those missing file objects.
    """
    bridge = Bridge(app_name=CONTENT_APP_NAME, sqlite_file_path=destination)
    connection = bridge.get_connection()
    ContentNodeTable = bridge.get_table(ContentNode)
    # SQL Alchemy reference to the file table - a mapping from
    # contentnodes to the files that they use
    FileTable = bridge.get_table(File)
    # SQL Alchemy reference to the localfile table which tracks
    # information about the files on disk, such as availability
    LocalFileTable = bridge.get_table(LocalFile)
    # get unavailable local file ids on the destination db
    unavailable_local_file_ids_statement = select([
        LocalFileTable.c.id
    ]).where(LocalFileTable.c.available == False  # noqa
             )
    # get the Contentnode ids where File objects are missing in the destination db
    contentnode_ids_statement = (
        select([FileTable.c.contentnode_id]).where(
            and_(
                FileTable.c.local_file_id.in_(
                    unavailable_local_file_ids_statement),
                FileTable.c.supplementary == False,  # noqa
                or_(*(FileTable.c.preset == preset
                      for preset in renderable_files_presets)),
            )).limit(batch_size))

    i = 0

    updated_resource_ids = set()

    updated_resource_content_ids = set()

    contentnode_ids = [
        coerce_key(cid[0]) for cid in connection.execute(
            contentnode_ids_statement.offset(i)).fetchall()
    ]

    while contentnode_ids:
        # Exclude topics from here to prevent erroneous imports of their children
        # This should already be excluded as we are filtering to renderable files
        # so this is more of a sanity check
        for c in (ContentNode.objects.filter_by_uuids(
                contentnode_ids, validate=False).filter(
                    available=True, channel_id=channel_id).exclude(
                        kind=content_kinds.TOPIC).values_list(
                            "id", "content_id")):
            updated_resource_ids.add(c[0])
            updated_resource_content_ids.add(c[1])

        i += batch_size

        contentnode_ids = [
            coerce_key(cid[0]) for cid in connection.execute(
                contentnode_ids_statement.offset(i)).fetchall()
        ]

    # Do this after we have fetched all the ids and made them unique
    # otherwise, because we are getting our ids from the File table, we could
    # end up with a duplicate count of file sizes

    updated_resources_total_size = 0

    i = 0

    # Coerce to lists
    updated_resource_ids = list(updated_resource_ids)
    updated_resource_content_ids = list(updated_resource_content_ids)

    ids_batch = updated_resource_ids[i:i + batch_size]

    while ids_batch:

        contentnode_filter_expression = filter_by_uuids(
            ContentNodeTable.c.id, ids_batch, vendor=bridge.engine.name)

        # This does the first step in the many to many lookup for File
        updated_resources_total_size += connection.execute(
            select([func.sum(LocalFileTable.c.file_size)]).where(
                LocalFileTable.c.id.in_(
                    select([LocalFileTable.c.id]).select_from(
                        # and LocalFile.
                        LocalFileTable.join(
                            FileTable.join(
                                ContentNodeTable,
                                FileTable.c.contentnode_id ==
                                ContentNodeTable.c.id,
                            ),  # This does the actual correlation between file and local file
                            FileTable.c.local_file_id == LocalFileTable.c.id,
                        )).
                    where(
                        and_(
                            # Filter only for files that are unavailable so we show
                            # the import size
                            LocalFileTable.c.available == False,  # noqa
                            contentnode_filter_expression,
                        ))))).fetchone()[0]

        i += batch_size

        ids_batch = updated_resource_ids[i:i + batch_size]

    return (
        updated_resource_ids,
        updated_resource_content_ids,
        updated_resources_total_size,
    )
Beispiel #4
0
def get_new_resources_available_for_import(destination, channel_id):
    """
    Queries the destination db to get leaf nodes.
    Subtract total number of leaf nodes by the count of leaf nodes on default db to get the number of new resources.
    """
    bridge = Bridge(app_name=CONTENT_APP_NAME, sqlite_file_path=destination)
    # SQL Alchemy reference to the content node table
    ContentNodeTable = bridge.get_table(ContentNode)
    # SQL Alchemy reference to the file table - a mapping from
    # contentnodes to the files that they use
    FileTable = bridge.get_table(File)
    # SQL Alchemy reference to the localfile table which tracks
    # information about the files on disk, such as availability
    LocalFileTable = bridge.get_table(LocalFile)
    connection = bridge.get_connection()

    # To efficiently get the node ids of all new nodes in the channel
    # we are going to iterate over the currently existing nodes for the
    # channel in the default database, and cache their existence in the
    # temporary upgrade database by flagging them as 'available' in there
    # We can then read out all of the unavailable ContentNodes in order
    # to get a complete list of the newly available ids.
    # We wrap this all in a transaction so that we can roll it back afterwards
    # but this is mostly just not to leave the upgrade DB in a messy state
    # and could be removed if it becomes a performance concern

    # Create a queryset for the node ids of resources currently in this channel
    # we will slice this later in a while loop in order to efficiently process this
    # this is necessary otherwise we would end up querying tens of thousands of node ids
    # for a large channel, which would then be impossible to pass into an update query
    # for the temporary upgrade DB without causing an excessively large query
    # greater than 1MB, which is the default max for SQLite
    current_resource_node_id_queryset = (ContentNode.objects.filter(
        channel_id=channel_id).exclude(kind=content_kinds.TOPIC).values_list(
            "id", flat=True))

    i = 0

    # start a transaction

    trans = connection.begin()

    # Set everything to False to start with
    connection.execute(ContentNodeTable.update().where(
        ContentNodeTable.c.channel_id == channel_id).values(available=False))

    node_ids = current_resource_node_id_queryset[i:i + batch_size]
    while node_ids:
        # Set everything to False to start with
        connection.execute(ContentNodeTable.update().where(
            and_(
                filter_by_uuids(ContentNodeTable.c.id,
                                node_ids,
                                vendor=bridge.engine.name),
                ContentNodeTable.c.channel_id == channel_id,
            )).values(available=True))
        i += batch_size
        node_ids = current_resource_node_id_queryset[i:i + batch_size]

    renderable_contentnodes = (
        select([FileTable.c.contentnode_id
                ]).where(FileTable.c.supplementary == False)  # noqa
        .where(
            or_(*(FileTable.c.preset == preset
                  for preset in renderable_files_presets))))

    contentnode_filter_expression = and_(
        ContentNodeTable.c.channel_id == channel_id,
        ContentNodeTable.c.kind != content_kinds.TOPIC,
        ContentNodeTable.c.available == False,  # noqa
        ContentNodeTable.c.id.in_(renderable_contentnodes),
    )

    new_resource_nodes_total_size = (
        connection.execute(
            # This does the first step in the many to many lookup for File
            select([func.sum(LocalFileTable.c.file_size)]).where(
                LocalFileTable.c.id.in_(
                    select([LocalFileTable.c.id]).select_from(
                        # and LocalFile.
                        LocalFileTable.join(
                            FileTable.join(
                                ContentNodeTable,
                                FileTable.c.contentnode_id
                                == ContentNodeTable.c.id,
                            ),  # This does the actual correlation between file and local file
                            FileTable.c.local_file_id == LocalFileTable.c.id,
                        )).
                    where(
                        and_(
                            # Filter only for files that are unavailable so we show
                            # the import size
                            LocalFileTable.c.available == False,  # noqa
                            contentnode_filter_expression,
                        ))))).fetchone()[0] or 0)

    new_resource_node_ids_statement = select([ContentNodeTable.c.id]).where(
        and_(
            ContentNodeTable.c.channel_id == channel_id,
            ContentNodeTable.c.kind != content_kinds.TOPIC,
            ContentNodeTable.c.available == False,  # noqa
        ))

    new_resource_node_ids = list(
        coerce_key(c[0]) for c in connection.execute(
            new_resource_node_ids_statement).fetchall())

    trans.rollback()

    # Create a queryset for the content_ids of resources currently in this channel
    # we will slice this later in a while loop in order to efficiently process this
    # this is necessary otherwise we would end up querying tens of thousands of node ids
    # for a large channel, which would then be impossible to pass into an update query
    # for the temporary upgrade DB without causing an excessively large query
    # greater than 1MB, which is the default max for SQLite
    current_resource_content_id_queryset = (ContentNode.objects.filter(
        channel_id=channel_id).exclude(kind=content_kinds.TOPIC).values_list(
            "content_id", flat=True))

    i = 0

    # start a transaction

    trans = connection.begin()

    # Set everything to False to start with
    connection.execute(ContentNodeTable.update().where(
        ContentNodeTable.c.channel_id == channel_id).values(available=False))

    content_ids = current_resource_content_id_queryset[i:i + batch_size]
    while content_ids:
        # Set everything to False to start with
        connection.execute(ContentNodeTable.update().where(
            and_(
                filter_by_uuids(
                    ContentNodeTable.c.content_id,
                    content_ids,
                    vendor=bridge.engine.name,
                ),
                ContentNodeTable.c.channel_id == channel_id,
            )).values(available=True))
        i += batch_size
        content_ids = current_resource_content_id_queryset[i:i + batch_size]

    new_resource_content_ids_statement = (
        select([ContentNodeTable.c.content_id]).where(
            and_(
                ContentNodeTable.c.channel_id == channel_id,
                ContentNodeTable.c.kind != content_kinds.TOPIC,
                ContentNodeTable.c.available == False,  # noqa
            )).distinct())

    new_resource_content_ids = list(
        coerce_key(c[0]) for c in connection.execute(
            new_resource_content_ids_statement).fetchall())

    trans.rollback()

    return (
        new_resource_node_ids,
        new_resource_content_ids,
        new_resource_nodes_total_size,
    )