from sqlite3 import Connection, connect

GET = '''
with recursive rec( version_id ) as
  ( select version.id from version
    join package on package.id = package_id
    where name = ? and number = ?
    union
    select depends_on from depends join rec on rec.version_id =
    depends.version_id ) select name,
    number from package join version on package.id = package_id join
    rec on rec.version_id = version.id where name != ?
'''

def list_required( db: Connection, pkg: str,
                   ver: str ) -> list[ tuple[ str, str ] ]:
    return list( db.execute( GET, ( pkg, ver, pkg ) ) )