Writes a collection of path info records for specified clients.

def _MultiWritePathInfos(self, path_infos, connection=None):
    """Writes a collection of path info records for specified clients."""
    query = ""

    path_info_count = 0
    path_info_values = []

    parent_path_info_count = 0
    parent_path_info_values = []

    has_stat_entries = False
    has_hash_entries = False

    for client_id, client_path_infos in iteritems(path_infos):
      for path_info in client_path_infos:
        path = mysql_utils.ComponentsToPath(path_info.components)

        path_info_values.append(db_utils.ClientIDToInt(client_id))
        path_info_values.append(int(path_info.path_type))
        path_info_values.append(path_info.GetPathID().AsBytes())
        path_info_values.append(path)
        path_info_values.append(bool(path_info.directory))
        path_info_values.append(len(path_info.components))

        if path_info.HasField("stat_entry"):
          path_info_values.append(path_info.stat_entry.SerializeToString())
          has_stat_entries = True
        else:
          path_info_values.append(None)
        if path_info.HasField("hash_entry"):
          path_info_values.append(path_info.hash_entry.SerializeToString())
          path_info_values.append(path_info.hash_entry.sha256.AsBytes())
          has_hash_entries = True
        else:
          path_info_values.append(None)
          path_info_values.append(None)

        path_info_count += 1

        # TODO(hanuszczak): Implement a trie in order to avoid inserting
        # duplicated records.
        for parent_path_info in path_info.GetAncestors():
          path = mysql_utils.ComponentsToPath(parent_path_info.components)

          parent_path_info_values.append(db_utils.ClientIDToInt(client_id))
          parent_path_info_values.append(int(parent_path_info.path_type))
          parent_path_info_values.append(parent_path_info.GetPathID().AsBytes())
          parent_path_info_values.append(path)
          parent_path_info_values.append(len(parent_path_info.components))

          parent_path_info_count += 1

    query += """
    CREATE TEMPORARY TABLE client_path_infos(
      client_id BIGINT UNSIGNED NOT NULL,
      path_type INT UNSIGNED NOT NULL,
      path_id BINARY(32) NOT NULL,
      path TEXT NOT NULL,
      directory BOOLEAN NOT NULL,
      depth INT NOT NULL,
      stat_entry MEDIUMBLOB NULL,
      hash_entry MEDIUMBLOB NULL,
      sha256 BINARY(32) NULL,
      timestamp TIMESTAMP(6) NOT NULL DEFAULT now(6)
    );"""

    if path_info_count > 0:
      query += """
      INSERT INTO client_path_infos(client_id, path_type, path_id,
                                    path, directory, depth,
                                    stat_entry, hash_entry, sha256)
      VALUES {};
      """.format(mysql_utils.Placeholders(num=9, values=path_info_count))

      query += """
      INSERT INTO client_paths(client_id, path_type, path_id, path,
                               directory, depth)
           SELECT client_id, path_type, path_id, path, directory, depth
             FROM client_path_infos
      ON DUPLICATE KEY UPDATE
        client_paths.directory = client_paths.directory OR VALUES(client_paths.directory),
        client_paths.timestamp = now(6);
      """

    if parent_path_info_count > 0:
      placeholders = ["(%s, %s, %s, %s, TRUE, %s)"] * parent_path_info_count

      query += """
      INSERT INTO client_paths(client_id, path_type, path_id, path,
                               directory, depth)
      VALUES {}
      ON DUPLICATE KEY UPDATE
        directory = TRUE,
        timestamp = now();
      """.format(", ".join(placeholders))

    if has_stat_entries:
      query += """
      INSERT INTO client_path_stat_entries(client_id, path_type, path_id,
                                           stat_entry, timestamp)
           SELECT client_id, path_type, path_id, stat_entry, timestamp
             FROM client_path_infos
            WHERE stat_entry IS NOT NULL;
      """

      query += """
      UPDATE client_paths, client_path_infos
         SET client_paths.last_stat_entry_timestamp = client_path_infos.timestamp
       WHERE client_paths.client_id = client_path_infos.client_id
         AND client_paths.path_type = client_path_infos.path_type
         AND client_paths.path_id = client_path_infos.path_id
         AND client_path_infos.stat_entry IS NOT NULL;
      """

    if has_hash_entries:
      query += """
      INSERT INTO client_path_hash_entries(client_id, path_type, path_id,
                                           hash_entry, sha256, timestamp)
           SELECT client_id, path_type, path_id, hash_entry, sha256, timestamp
             FROM client_path_infos
            WHERE hash_entry IS NOT NULL;
      """

      query += """
      UPDATE client_paths, client_path_infos
         SET client_paths.last_hash_entry_timestamp = client_path_infos.timestamp
       WHERE client_paths.client_id = client_path_infos.client_id
         AND client_paths.path_type = client_path_infos.path_type
         AND client_paths.path_id = client_path_infos.path_id
         AND client_path_infos.hash_entry IS NOT NULL;
      """

    try:
      with contextlib.closing(connection.cursor()) as cursor:
        cursor.execute(query, path_info_values + parent_path_info_values)
    finally:
      # Drop the temporary table in a separate cursor. This ensures that
      # even if the previous cursor.execute fails mid-way leaving the
      # temporary table created (as table creation can't be rolled back), the
      # table would still be correctly dropped.
      #
      # This is important since connections are reused in the MySQL connection
      # pool.
      with contextlib.closing(connection.cursor()) as cursor:
        cursor.execute("DROP TEMPORARY TABLE IF EXISTS client_path_infos")