Schema incorrect since merging #8

[auxesis]

Turns out you can't introduce a new UNIQUE constraint on an existing table that already has a UNIQUE constraint.

This is a problem for Jacaranda, because this is the old schema prior to merging #8:

CREATE TABLE data (date_posted,text,runner,UNIQUE (date_posted))

And this is the new schema created from a dry run of #8, now there are multiple runners:

CREATE TABLE data (date_posted,text,runner,UNIQUE (date_posted,runner))

SQLite doesn't support adding new columns with UNIQUE or PRIMARY KEY constraints to existing tables.

SQLite does permit adding a unique index if your table has no existing UNIQUE constraints.

The effect of this is:

when we run the scraper, only one runner is able to record that it successfully ran, because there is a single primary key: runner

This means that when the scraper gets run by Morph, it will post messages to Slack every day, because it doesn't know that the runner successfully ran the day before.

[auxesis]

I can see two approaches to fixing this:

1. Create a new table with the correct index, read + upgrade the existing data, load the upgraded data into the new tables, and delete the old table. The approach is outlined on sqlitetutorial.com
2. Switch the scraper to use a new table for recording runs. Backfill data into the new table on first load.

I've done a quick spike on the first approach, and it gets pretty complicated pretty quickly.

Doing another spike on the second approach now.

[equivalentideas]

This appears fixed in morph.io now and I've run the scraper and it didn't post, reading from the existing data correctly.

But, the all but the latest post has had the wrong runner applied to it (see [api results](https://api.morph.io/openaustralia/jacaranda/data.json?key=yourkeyuery=select * from "posts")):

[

    {
        "date_posted": "2016-08-29",
        "text": "700 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 6% down from the fortnight before. You shipped 26 commits in the same period. There are now 36.8 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2016-09-12",
        "text": "705 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 3% up from the fortnight before. You shipped 1 commits in the same period. There are now 37.4 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2016-09-26",
        "text": "746 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 6% up from the fortnight before. You shipped 13 commits in the same period. There are now 38 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2016-10-10",
        "text": "728 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 0% down from the fortnight before. There are now 38.6 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2016-10-25",
        "text": "745 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 5% up from the fortnight before. There are now 39.3 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2016-11-09",
        "text": "743 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 1% less than the fortnight before.\n180 people left :scream_cat: That’s 4% more than the fortnight before.\nYou shipped 27 commits in the same period.\nThere are now 39.9 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2016-11-25",
        "text": "678 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 10% less than the fortnight before.\n177 people left :scream_cat: That’s 3% less than the fortnight before.\nThere are now 40.5 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2016-12-10",
        "text": "745 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 9% more than the fortnight before.\n175 people left. That’s 1% less than the fortnight before.\nYou shipped 3 commits in the same period.\nThere are now 41.1 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2016-12-24",
        "text": "639 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 14% less than the fortnight before.\n189 people left. That’s 7% more than the fortnight before.\nThere are now 41.6 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-01-11",
        "text": "552 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 2% less than the fortnight before.\n94 people left. That’s 37% less than the fortnight before.\nYou shipped 69 commits in the same period.\nThere are now 42.2 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-01-25",
        "text": "816 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 47% more than the fortnight before.\n152 people left. That’s 58% more than the fortnight before.\nYou shipped 21 commits in the same period.\nThere are now 42.9 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-02-08",
        "text": "786 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 4% less than the fortnight before.\n170 people left. That’s 10% more than the fortnight before.\nYou shipped 32 commits in the same period.\nThere are now 43.6 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-02-23",
        "text": "842 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 6% more than the fortnight before.\n215 people left. That’s 21% more than the fortnight before.\nYou shipped 59 commits in the same period.\nThere are now 44.3 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-03-09",
        "text": "872 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 2% more than the fortnight before.\n185 people left. That’s 14% less than the fortnight before.\nYou shipped 5 commits in the same period.\nThere are now 45.1 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-03-23",
        "text": "970 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 11% more than the fortnight before.\n286 people left. That’s 54% more than the fortnight before.\nYou shipped 8 commits in the same period.\nThere are now 45.8 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-04-06",
        "text": "894 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 8% less than the fortnight before.\n227 people left. That’s 21% less than the fortnight before.\nYou shipped 11 commits in the same period.\nThere are now 47 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-04-20",
        "text": "1375 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 53% more than the fortnight before.\n262 people left. That’s 10% more than the fortnight before.\nYou shipped 11 commits in the same period.\nThere are now 47.7 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-05-04",
        "text": "883 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 36% less than the fortnight before.\n254 people left. That’s 9% less than the fortnight before.\nYou shipped 12 commits in the same period.\nThere are now 48.4 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-05-18",
        "text": "958 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 8% more than the fortnight before.\n268 people left. That’s 4% more than the fortnight before.\nYou shipped 14 commits in the same period.\nThere are now 49.1 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-06-01",
        "text": "928 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 4% less than the fortnight before.\n294 people left. That’s 6% more than the fortnight before.\nYou shipped 29 commits in the same period.\nThere are now 49.8 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-07-01",
        "text": "794 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 5% less than the fortnight before.\n278 people left. That’s 10% less than the fortnight before.\nYou shipped 10 commits in the same period.\nThere are now 51 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-07-18",
        "text": "859 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 5% more than the fortnight before.\n321 people left. That’s 13% less than the fortnight before.\nThere are now 51.8 thousand PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-08-16",
        "text": "759 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 10% more than the fortnight before.\n\n233 people left. That’s 15% more than the fortnight before.\n\nYou shipped 114 commits in the same period.\n\nThere are now 52,900 PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-08-30",
        "text": "828 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 8% more than the fortnight before.\n\n216 people left. That’s 9% less than the fortnight before.\n\nYou shipped 104 commits in the same period.\n\nThere are now 53,600 PlanningAlerts subscribers! :star2:",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-09-04",
        "text": ":saxophone: 31 new requests were made through Right To Know last fortnight.\n\n:heartbeat: Our contributors helped people with 7 annotations.\n\n:trophy: 3 requests were marked successful!",
        "runner": "RightToKnow::Runner"
    },
    {
        "date_posted": "2017-09-05",
        "text": "711 people signed up for PlanningAlerts last fortnight :revolving_hearts: That’s 13% less than the fortnight before.\n\n232 people left. That’s 5% less than the fortnight before.\n\nYou shipped 100 commits in the same period.\n\nThere are now 53,700 PlanningAlerts subscribers! :star2:",
        "runner": "PlanningAlerts::Runner"
    }

]

Sorry @auxesis that's my fault for not checking the tests covered that parsing!

I think the way to fix this is to add a temporary method to clear up that old data. We should then remove the schema upgrade methods too because they wont be relevant to other users.

[auxesis]

OK no worries @equivalentideas, I'll make another PR to test and fix this.