# Database schema

The database contains one table that tracks the learning progress for
each entry we desire to learn.

```
CREATE TABLE learn (
        word TEXT UNIQUE PRIMARY KEY,
        steno TEXT NOT NULL,
        goods INTEGER NOT NULL,
        interval REAL NOT NULL,
        next REAL NOT NULL);

CREATE INDEX learn_steno_idx ON learn (steno);
CREATE INDEX learn_next_idx ON learn (next);
```

The `interval` is a number of seconds.  `next` is also a number of
seconds, based on posix time.  It indicates when this word will next
be available to be practiced.  Each time we successfully write a word,
the interval will be increased.  When it is wrong (or slow), the
interval can be reduced.

The value `goods` indicates how many times the word has been written
correctly by the user.  This can be used for interoperation with
typey-type, or to gather statistics (beyond what is in interval).  For
example, a word that has been written multiple times but still has a
short interval is probably difficult.

There is also a table of words that haven't been learned.  These are
organized by what list they come from and will be gradually introduced
when the user still wishes to practice but any words already known
have next values in the future.

```
CREATE TABLE list (
        id INTEGER PRIMARY KEY,
        name TEXT UNIQUE NOT NULL);
CREATE TABLE lesson (
        word TEXT NOT NULL,
        steno TEXT NOT NULL,
        listid INTEGER REFERENCES list (id) NOT NULL,
        seq INTEGER NOT NULL,
        UNIQUE (word, listid));
CREATE TABLE schema (version TEXT NOT NULL);
```