load tweets from json in backup into sqlite3

load_tweets.tcl

package require sqlite3

sqlite3 db tweets.sqlite3

db eval {DROP TABLE IF EXISTS tweetjson}
db eval {create table tweetjson (id INTEGER PRIMARY KEY, tweet TEXT)}

foreach srcjs [glob {*.js}] {
	set inchan [open $srcjs]
	gets $inchan
	set json [read $inchan]
	db eval {
		INSERT INTO tweetjson SELECT json_extract(value,'$.id'),value FROM json_each(:json)
	}	
}
# Set up an index and a view for SQLite-compatible timestamp - what twitter ships isn't strictly ISO 8601
# (it is technically "just hanging out") so we need substr(1,19) of it. Fortunately it's all UTC and 
# SQLite lets us index functions.
db eval {
  CREATE INDEX idx_tweet_ts on tweetjson (substr(json_extract(tweet,'$.created_at'),1,19))
}
db eval {
  CREATE VIEW tweet_timestamp as select id,substr(json_extract(tweet,'$.created_at'),1,19) as timestamp from tweetjson
}

# Tip: 
# SELECT * FROM tweetjson,tweet_timestamp USING (id);