Denormalized tables `types` and `type_names` in `.chiseld.db`

[honzasp]

The database .chiseld.db contains two tables, types and type_names, that contain a row for every user-defined ChiselEntity:

CREATE TABLE `types` (
    `type_id` integer PRIMARY KEY AUTOINCREMENT,
    `backing_table` text UNIQUE,
    `api_version` text UNIQUE
);
CREATE TABLE `type_names` (
    `type_id` integer,
    `name` text UNIQUE,
    FOREIGN KEY (`type_id`) REFERENCES `types` (`type_id`) ON DELETE CASCADE
);

with data that look like:

sqlite> select * from types;
1|ty_Book_DB9DFA65892B4E02805E2FBC012FD911|
2|ty_Book_90AB39185CE34CE99891366B8AD5F4D7|
3|ty_Author_49FB41771CE04E908A6E1EE353066980|
4|ty_Author_83BE57E2356141F0AA021A3FE5D5A86D|
5|ty_Human_D665CCAFB4BD4260972AA6CE2C7730BD|

sqlite> select * from type_names;
1|dev.Book
2|main.Book
3|dev.Author
4|main.Author
5|dev.Human

We should probably normalize this into one table by dropping type_names and adding columns name and api_version to types. We should not be parsing strings from type_names.name to get the name and the api version.

This is related to #1601

[glommer]

the reason we parse strings is that we want combinations of columns, and not columns, to be unique. Field names have to be unique within a model, but not globally unique. sqlite doesn't support a group of columns being unique, so we resorted to that.

[honzasp]

I think that SQLite does support unique index with multiple columns: https://www.sqlite.org/lang_createindex.html

sqlite> create table types (name text, api_version text);
sqlite> create unique index i on types (name, api_version);
sqlite> insert into types (name, api_version) values ('Book', 'dev');
sqlite> insert into types (name, api_version) values ('Book', 'dev');
Error: UNIQUE constraint failed: types.name, types.api_version

[honzasp]

Or we could simply use (api_version, name) as the primary key in the types table, so that we don't have to keep track of type ids in chiseld.