from sqlite3 import Connection, connect, Cursor

def list_packages( db: Connection ) -> Cursor:
    return db.execute( 'select name, count( number ) from ' + \
                       'package left join version ' + \
                       'on package_id = package.id group by name' )

def list_leaves( db: Connection ) -> Cursor:
    return db.execute( 'select name, number from ' + \
                       'package join version ' + \
                       'on package_id = package.id ' + \
                       'where version.id not in ' + \
                       '( select depends_on from depends )' )

def sum_depends( db: Connection ) -> Cursor:
    return db.execute( 'select name, number, ' + \
                       '( select count(*) from depends where ' + \
                       '  depends_on = version.id ) from ' + \
                       'package join version ' + \
                       'on package_id = package.id' )

import run_tests