update inconsistent meta, init and log schemas

[joethorley]

in particular

1. read in data
2. drop table
3. repopulate

[sebdalgarno]

There is a legacy database (e.g. old Quesnel-exploit) that has a MetaData table in slightly different format. readwritesqlite will not read the metadata.

Here is some code to update the metadata table so readwritesqlite can recognise

### use DBI so that readwrimtesqlite does not automatically create an empty metadata table
conn <- DBI::dbConnect(drv = RSQLite::SQLite(), "<path to db>")
meta <- DBI::dbReadTable(conn = conn, "MetaData")

meta <- meta %>%
  dplyr::filter(tolower(DataTable) != DataTable) %>%
  dplyr::rename(TableMeta = DataTable,
                ColumnMeta = DataColumn,
                MetaMeta = DataUnits,
                DescriptionMeta = DataDescription) %>%
  dplyr::mutate(TableMeta = toupper(TableMeta),
                ColumnMeta = toupper(ColumnMeta),
                MetaMeta = gsub("unit", "units", MetaMeta))

DBI::dbGetQuery(conn,
                "CREATE TABLE readwritesqlite_meta (
  TableMeta TEXT NOT NULL,
  ColumnMeta TEXT NOT NULL,
  MetaMeta TEXT,
  DescriptionMeta TEXT,
  PRIMARY KEY(TableMeta, ColumnMeta)
);")

DBI::dbWriteTable(conn, "readwritesqlite_meta", meta, append = TRUE)