Convert the table and column descriptions of a `Dataset` into specifications for the
    DB schema.

    :param ds:
    :return: A pair (tables, reference_tables).

def schema(ds):
    """
    Convert the table and column descriptions of a `Dataset` into specifications for the
    DB schema.

    :param ds:
    :return: A pair (tables, reference_tables).
    """
    tables, ref_tables = {}, {}
    table_lookup = {t.url.string: t for t in ds.tables if ds.get_tabletype(t)}
    for table in table_lookup.values():
        spec = TableSpec(ds.get_tabletype(table))
        spec.primary_key = [
            c for c in table.tableSchema.columns if
            c.propertyUrl and c.propertyUrl.uri == term_uri('id')][0].name
        # Map the column name to the default:
        if spec.name in PROPERTY_URL_TO_COL:
            spec.primary_key = PROPERTY_URL_TO_COL[spec.name][term_uri('id')]
        for c in table.tableSchema.columns:
            if c.propertyUrl and c.propertyUrl.uri == term_uri('source'):
                # A column referencing sources is replaced by an association table.
                otype = ds.get_tabletype(table).replace('Table', '')
                ref_tables[ds.get_tabletype(table)] = TableSpec(
                    '{0}Source'.format(otype),  # The name of the association table.
                    [ColSpec(otype + '_ID'), ColSpec('Source_ID'), ColSpec('Context')],
                    [
                        (  # The foreign key to the referencing object:
                            ['dataset_ID', otype + '_ID'],
                            ds.get_tabletype(table),
                            ['dataset_ID', spec.primary_key]),
                        (  # The foreign key to the referenced source:
                            ['dataset_ID', 'Source_ID'],
                            'SourceTable',
                            ['dataset_ID', 'ID']),
                    ],
                    c.name)
            else:
                cname = c.header
                if c.propertyUrl and spec.name in PROPERTY_URL_TO_COL:
                    if c.propertyUrl.uri in PROPERTY_URL_TO_COL[spec.name]:
                        cname = PROPERTY_URL_TO_COL[spec.name][c.propertyUrl.uri]
                spec.columns.append(ColSpec(
                    cname,
                    c.datatype.base if c.datatype else c.datatype,
                    c.separator,
                    cname == spec.primary_key,
                    cldf_name=c.header))
        for fk in table.tableSchema.foreignKeys:
            if fk.reference.schemaReference:
                # We only support Foreign Key references between tables!
                continue  # pragma: no cover
            ref = table_lookup[fk.reference.resource.string]
            ref_type = ds.get_tabletype(ref)
            if ref_type:
                colRefs = sorted(fk.columnReference)
                if spec.name in PROPERTY_URL_TO_COL:
                    # Must map foreign keys
                    colRefs = []
                    for c in sorted(fk.columnReference):
                        c = ds[spec.name, c]
                        if c.propertyUrl and c.propertyUrl.uri in PROPERTY_URL_TO_COL[spec.name]:
                            colRefs.append(PROPERTY_URL_TO_COL[spec.name][c.propertyUrl.uri])
                        else:
                            colRefs.append(c.header)
                rcolRefs = sorted(fk.reference.columnReference)
                if ref_type in PROPERTY_URL_TO_COL:
                    # Must map foreign key targets!
                    rcolRefs = []
                    for c in sorted(fk.reference.columnReference):
                        c = ds[ref_type, c]
                        if c.propertyUrl and c.propertyUrl.uri in PROPERTY_URL_TO_COL[ref_type]:
                            rcolRefs.append(PROPERTY_URL_TO_COL[ref_type][c.propertyUrl.uri])
                        else:
                            rcolRefs.append(c.header)
                spec.foreign_keys.append((
                    tuple(['dataset_ID'] + colRefs),
                    ds.get_tabletype(table_lookup[fk.reference.resource.string]),
                    tuple(['dataset_ID'] + rcolRefs)))
        tables[spec.name] = spec

    # must determine the order in which tables must be created!
    ordered = OrderedDict()
    i = 0
    #
    # We loop through the tables repeatedly, and whenever we find one, which has all
    # referenced tables already in ordered, we move it from tables to ordered.
    #
    while tables and i < 100:
        i += 1
        for table in list(tables.keys()):
            if all(ref[1] in ordered for ref in tables[table].foreign_keys):
                # All referenced tables are already created.
                ordered[table] = tables.pop(table)
                break
    if tables:  # pragma: no cover
        raise ValueError('there seem to be cyclic dependencies between the tables')

    return list(ordered.values()), ref_tables