Prototype 'guess' operation

[jamesaoverton]

VALVE will be much easier to use once we can provide a TSV file and automatically fill the 'column' table with reasonable guesses. Let's design that feature.

Assume that we start with the usual VALVE special tables, working properly. The user provides the path to a new TSV file to add (not a special table, just a data table). Assume that this file could be quite large. Here's my first pass at the next steps:

• set a sample size N: default 10,000
• set an error rate X: default 10%
• set an "enum" size E: default 10
• try to read the file as TSV, or fail
• read a sample of N rows into memory
  • random sample based on a fixed seed, so it should be repeatable
  • if the table is smaller than the sample size, read the whole thing into memory
• normalize the column names
  • remove spaces, punctuation, etc.
  • keep the TSV column names as the column labels
• for each column
  • guess the nulltype
    • if more than X% of cells are empty strings, set nulltype 'empty'
    • otherwise do not set a nulltype
    • future work: check for other nulltypes such as "N/A" or "NULL"
  • get the distinct values, guess the datatype and structure
    • if less than X% of values are duplicates
      • if this is the first column, set structure 'primary'
      • otherwise set structure 'unique'
    • check the distinct values for the other columns in this table
      • if all but X% of values are in another column, set structure from()
      • tiebreaker: prefer to reuse a 'structure' value already defined in the 'column' table
    • check the distinct values for the other columns in other tables already loaded
      • if all but X% of values are in another column, set structure from()
      • tiebreaker: prefer to reuse a 'structure' value already defined in the 'column' table
    • check all the datatypes to find the most specific match
      • pick the most-specific datatype (lowest in the tree of datatypes)
      • tiebreaker: prefer in() datatypes
      • tiebreaker: pick the best-matching datatype (lowest percentage of errors)
      • future work: have a built-in set of datatypes to suggest?
• add rows to 'table', 'column', 'datatype' as required
• load the new TSV using the updated configuration

I'm still fuzzy on which of the rules should have priority.

[lmcmicu]

Note that since from() structures always refer to an external table, we cannot "check the distinct values for the other columns in this table" in the algorithm specified above (but the heading "check the distinct values for the other columns in other tables already loaded" is fine).