forked from houstondatavis/russian-troll-tweets
-
Notifications
You must be signed in to change notification settings - Fork 1
/
create.psql
67 lines (59 loc) · 2.85 KB
/
create.psql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
DROP SCHEMA rustweets CASCADE;
CREATE SCHEMA rustweets;
CREATE TYPE rustweets.post_type AS ENUM ( 'QUOTE_TWEET', 'RETWEET');
CREATE TYPE rustweets.account_cat AS ENUM (
'Commercial', 'Fearmonger', 'HashtagGamer', 'LeftTroll', 'NewsFeed',
'NonEnglish', 'RightTroll', 'Unknown'
);
CREATE TYPE rustweets.language AS ENUM (
'Albanian', 'Arabic', 'Bengali', 'Bulgarian', 'Catalan',
'Croatian', 'Czech', 'Danish', 'Dutch', 'English',
'Estonian', 'Farsi (Persian)', 'Finnish', 'French', 'German',
'Greek', 'Gujarati', 'Hebrew', 'Hindi', 'Hungarian',
'Icelandic', 'Indonesian', 'Italian', 'Japanese', 'Kannada',
'Korean', 'Kurdish', 'LANGUAGE UNDEFINED', 'Latvian', 'Lithuanian',
'Macedonian', 'Malay', 'Malayalam', 'Norwegian', 'Polish',
'Portuguese', 'Pushto', 'Romanian', 'Russian', 'Serbian',
'Simplified Chinese', 'Slovak', 'Slovenian', 'Somali', 'Spanish',
'Swedish', 'Tagalog (Filipino)', 'Tamil', 'Telugu', 'Thai',
'Traditional Chinese', 'Turkish', 'Ukrainian', 'Urdu', 'Uzbek',
'Vietnamese'
);
CREATE TYPE rustweets.region AS ENUM (
'Afghanistan', 'Austria', 'Azerbaijan', 'Belarus', 'Canada',
'Czech Republic', 'Denmark', 'Egypt', 'Estonia', 'Finland',
'France', 'Germany', 'Greece', 'Hong Kong', 'India',
'Iran, Islamic Republic of', 'Iraq', 'Israel', 'Italy', 'Japan',
'Latvia', 'Malaysia', 'Mexico', 'Russian Federation', 'Samoa',
'Saudi Arabia', 'Serbia', 'Spain', 'Sweden', 'Switzerland',
'Turkey', 'Ukraine', 'United Arab Emirates', 'United Kingdom', 'United States',
'Unknown'
);
-- external_author_id,author,content,region,language,publish_date,harvested_date,following,followers,updates,post_type,account_type,retweet,account_category,new_june_2018,alt_external_id,tweet_id,article_url,tco1_step1,tco2_step1,tco3_step1
CREATE TABLE rustweets.tweets (
external_author_id bigint,
author text, -- not unique to external_author_id
content text,
region rustweets.region,
language rustweets.language,
publish_date timestamp with time zone,
harvested_date timestamp with time zone,
following int,
followers int,
updates int,
post_type rustweets.post_type,
account_type text,
retweet bool,
account_category rustweets.account_cat,
new_june_2018 bool,
alt_external_id bigint,
tweet_id bigint PRIMARY KEY,
article_url text,
tco1_step1 text,
tco2_step1 text,
tco3_step1 text
);
COMMENT ON COLUMN rustweets.tweets.external_author_id IS $$No idea what this is see https://github.com/fivethirtyeight/russian-troll-tweets/issues/32$$;
COMMENT ON COLUMN rustweets.tweets.account_type IS $$Each author has one account type$$;
COMMENT ON COLUMN rustweets.tweets.account_category IS $$Each author has one account category$$;
COMMENT ON COLUMN rustweets.tweets.author IS $$account_name (if the world was sane)$$;