Just updated the nightly ML-based estimation code to read from the database.

Next step: Update CSV dumping code to dump this data nightly.

Just updated the nightly cron job so that it inserts into the DB.

Next step: Update nightly ML-based estimation code to read from DB.

Next next step: Update CSV dumping code to dump this data nightly.

Next next step: Migrate nightly CSV-writing code to write to this.

Next next next step: Create a new CSV writer from this DB.

Migrated existing CSVs.

Next step: Migrate all CSV reading code to this.

Next next step: Migrate nightly CSV-writing code to write to this.

Next next next step: Create a new CSV writer from this DB.

Table created (er, I lost half an hour to the fact that utc_timestamp is a
reserved word in SQL apparently, so I struggled to figure out why I couldn't use
it as a column key).

Next step: Migrate existing CSVs into the Flickr DB.

Next next step: Migrate all code that uses those CSVs to jam this into the DB.

I like the 2nd table.

ACK, in progress

Mike, right now we have two tables: "simple" which stores linkback results, and
"complex" which stores the results of Google and Yahoo API queries for
CC-licensed content (which we never ever use).

I was thinking of adding a third table whose structure mirrors "simple", but
instead I could be convinced to jam the Flickr data into "simple".

The structure of simple is:

simple (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    license_uri VARCHAR(255)  NOT NULL,
    search_engine VARCHAR(255) NOT NULL,
    count INT NOT NULL,
    timestamp DATETIME NOT NULL,
    country VARCHAR(255),
    language VARCHAR(255)
);

We could jam the Flickr data in by setting search_engine='Flickr', and
country=NULL and langauge=NULL. One reason I don't like that approach is that
the Flickr results are more canonical than the search engine ones. For that
reason, I favor a second table (in the same database):

site_specific (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        license_uri VARCHAR(255)  NOT NULL,
        site VARCHAR(255) NOT NULL,
        count INT NOT NULL,
        timestamp DATETIME NOT NULL,
);

ML, feel free to write "don't care", but I wondered: when you wrote "added to
the main stats database," did you mean adding it to the same table? If you don't
mean that, I'll quickly start a fresh separate table.

Writes Mike:

> Flickr data should be added to the main stats database (keyed with 2.0
> generic URIs), as should any other site specific data we can gather in
> the future.  We can regenerate historical estimates at any time based on
> the latest historical data (possible some for other sites will turn up).