sqlite schema

[edsu]

Part of the proposed functionality for twarc's utilities is for them to allow users to convert collected JSON and storing in a SQLite database. The goal would be to allow v1 and v2 JSON to be written to a single representation and then to have various utilities use that rather than the raw JSON.

If we are going to go down this route it might be useful to sketch out what this schema might look like. We'll want to balance the benefits of SQL (being able to query tweet properties) while storing some data which doesn't need to be queried directly as a JSON blob? It might be useful to look at @simonw's twitter-to-sqlite package as reference and possible integration point.

[igorbrigadir]

Thinking out loud:

The goal would be to allow v1 and v2 JSON to be written to a single representation

This should be possible - i always wanted to do a v2 -> v1.1 and v1.1 -> v2 converter. A bunch of old tools would suddenly work again.

We'll want to balance the benefits of SQL (being able to query tweet properties) while storing some data which doesn't need to be queried directly as a JSON blob?

I have some unfinished thoughts on this: I'm very pro having an SQL structured set of tables for querying data. However, I also think we should store the original timestamped API request responses as an "even log" - this "log" of requests can be parsed and re-parsed into various formats later (this comes from my own past experience so i din't know how useful it is for others)