Make database schema part of the API

[fkromer]

As response to your request in the 5.0a2 release notes:

The database schema is guaranteed to change in the future, to support new features. I’m looking for opinions about making the schema part of the public API to coverage.py or not.

It would be great if you'd make the schema part of the API (or at least an indicator about the schema version).

[nedbat]

Help me understand how you would use this. By "schema" do you just mean a version number? Or literally the actual schema?

[fkromer]

TL;DR The database schema version provided with the database works for me. Some additional schema (dump, diagram or description) documentation would be nice.

In my opinion it would be best to provide an API for the database access because it's the only way to provide "meaning" about the db schema. The schema or schema version (without additional documentation) provides information about the data structure only, users have to guess about the "meaning" of the data (right now obvious but probably not in the future) and implement it into their db wrappers.

By providing documentation of the database schema (dump, xml, ...) to users they don't have to explore an actual database on their own (that's what I did). There are libraries which can generate code from schema (dumps, xml representation of tables) as well which could be useful.

By providing a schema version number to users they may implement version agnostic db wrappers. If the schema version is provided via the database itself (right now) access to it must kept constant over all coming SQLite database schema versions.

[fkromer]

Description of the coveragepy v5.03a database schemas: Who tests what is here: Coverage.py 5.0a3

[nedbat]

OK, just to be clear, you are asking for a function that would return the number of the schema, for example: 2

[fkromer]

Yes.

[nedbat]

@fkromer It seems to me that the reason to get the schema from the API is so you can then access the data in the database. Why not just read the schema from the database? I can promise that this will always give you the value you want:

select version from coverage_schema

[fkromer]

I am totally fine with that because I am not interacting with coveragepy but it's database only. As long as other tools don't integrate with coveragepy directly (pytest-cov, etc.) and don't depend on the info about the schema version from coveragepy directly there is no need for a function I guess. In case this question would pop up again later.

[strichter]

I personally do not care about the schema. sqldata should have all the functions. It is really easy to mess up the analysis, so I rather just use APIs.

[nedbat]

I've been working on using the dynamic contexts to measure the Open edX codebase. The data with the current schema is prohibitively large (650Mb SQLite file). We have a pull request to change the schema to store ranges of lines instead of individual lines: #823. This shrinks the database to about 60% of its current size (though it might still be too large). Now I'm wondering if even more compact storage will be needed (bitmaps? Non-SQLite?)

This work highlights the fact that the internal data storage might be subject to necessary changes, and so maybe we should consider the .coverage file schema to be private.

If you are using the database directly, there are a few options: 1) keep doing that, and deal with the schema changes that happen; 2) use the data api; 3) coverage.py could have a "sql report" command which produced the current 3rd-normal-form sql output, no matter how tricky and compact the internal schema became.

Thoughts?

[jhermann]

Your database is no one's business, and on the code level you are way more flexible to ensure backwards compatibility etc. Your strategy is sound, and while maybe not as convenient, it solves the needs of people wanting the raw data.

[simonw]

You could define a public, documented subset of the schema using SQL views. That way you can make any changes you like to the schema itself as long as you update the view definitions when you make those changes.

This also means you can be much more careful in the design of that public API - you can deliberately only expose a sensible, common subset of the data in the views which is designed to be easy to keep stable in the future.

[nedbat]

I toyed with using views as the public API, but I'm not sure they will be powerful enough. As an example: today the schema has a "line" table with a row for each line executed:

CREATE TABLE line (
    -- If recording lines, a row per context per line executed.
    file_id integer,            -- foreign key to `file`.
    context_id integer,         -- foreign key to `context`.
    lineno integer,             -- the line number.
    unique(file_id, context_id, lineno)
);

If a file has 100 lines in it that are executed, this will result in 100 rows that differ only in the lineno column. Good third-normal form, but bulky.
So I'm thinking of storing all 100 lines in a single row. The line numbers would be bits in a bitmap: line N would be represented by the Nth bit in the bytestring being set.

create table linemap (
    file_id integer,            -- foreign key to `file`.
    context_id integer,         -- foreign key to `context`.
    linemap text,               -- bits: Nth is the Nth line.
    unique(file_id, context_id)
);

Very compact, one row per file (per context). But how do you query it? It's not too bad with a custom SQLite function. BUT: I can't make a view that looks like the bulky line table from the compact linemap table, or if I can, it's a computational nightmare.