After creation tables do not follow the CREATE TABLE

[bjcairns]

When loading tidied tables, the tables are not stored in the form pre-specified by the CREATE TABLE SQL statements. Variable order and type are incorrect. For example:

db <- ukbschema::create_schema_db(path = tempdir())

The table encvalues is expected to have structure:

CREATE TABLE encvalues(
  "encoding_id" INTEGER,
  "code_id" INTEGER,
  "parent_id" INTEGER,
  "type" TEXT,
  "value" TEXT,
  "meaning" TEXT,
  "selectable" INTEGER,
  "showcase_order" INTEGER,
  PRIMARY KEY ("encoding_id", "code_id")
);

But then

$ sqlite3 ukb-schema-2019-07-11.sqlite ".schema encvalues"
CREATE TABLE `encvalues` (
  `encoding_id` REAL,
  `value` TEXT,
  `meaning` TEXT,
  `showcase_order` REAL,
  `parent_id` INTEGER,
  `selectable` INTEGER,
  `type` TEXT,
  `code_id` INTEGER
);

[bjcairns]

There were two problems here:

1. Only the first CREATE TABLE statement was being executed. This was fixed by loading the statements, splitting by ";", and calling DBI::dbSendStatement() for each one.
2. It then became apparent that in tidy_schemas(), the missing category 119 was being added with parent_id before that variable was created by joining categories and catbrowse, resulting in extra parent_id.x and parent_id.y fields from the join.