schema of dB with my pragma edits afterwards. The attached is a compilation of recent tweets with the word SQLite. It Is a nonsensitive representative of my dataset. What want is to separate into different external databases based on user_id_str. I'm annoyed because it feels like I'm doing it right.
Code: Select all
CREATE TABLE users(
id integer not null,
id_str text not null,
name text,
username text not null,
bio text,
location text,
url text,
join_date text not null,
join_time text not null,
tweets integer,
following integer,
followers integer,
likes integer,
media integer,
private integer not null,
verified integer not null,
profile_image_url text not null,
background_image text,
hex_dig text not null,
time_update integer not null,
CONSTRAINT users_pk PRIMARY KEY (id, hex_dig)
);
CREATE TABLE tweets (
id integer not null,
id_str text not null,
tweet text default '',
conversation_id text not null,
created_at integer not null,
date text not null,
time text not null,
timezone text not null,
place text default '',
replies_count integer,
likes_count integer,
retweets_count integer,
user_id integer not null,
user_id_str text not null,
screen_name text not null,
name text default '',
link text,
mentions text,
hashtags text,
cashtags text,
urls text,
photos text,
quote_url text,
video integer,
geo text,
near text,
source text,
time_update integer not null,
PRIMARY KEY (id)
);
CREATE TABLE retweets(
user_id integer not null,
username text not null,
tweet_id integer not null,
retweet_id integer not null,
retweet_date integer not null,
CONSTRAINT retweets_pk PRIMARY KEY(user_id, tweet_id),
CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES users(id),
CONSTRAINT tweet_id_fk FOREIGN KEY(tweet_id) REFERENCES tweets(id)
);
CREATE TABLE replies(
tweet_id integer not null,
user_id integer not null,
username text not null,
CONSTRAINT replies_pk PRIMARY KEY (user_id, tweet_id),
CONSTRAINT tweet_id_fk FOREIGN KEY (tweet_id) REFERENCES tweets(id)
);
CREATE TABLE favorites(
user_id integer not null,
tweet_id integer not null,
CONSTRAINT favorites_pk PRIMARY KEY (user_id, tweet_id),
CONSTRAINT user_id_fk FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT tweet_id_fk FOREIGN KEY (tweet_id) REFERENCES tweets(id)
);
CREATE TABLE followers (
id integer not null,
follower_id integer not null,
CONSTRAINT followers_pk PRIMARY KEY (id, follower_id),
CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES users(id),
CONSTRAINT follower_id_fk FOREIGN KEY(follower_id) REFERENCES users(id)
);
CREATE TABLE following (
id integer not null,
following_id integer not null,
CONSTRAINT following_pk PRIMARY KEY (id, following_id),
CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES users(id),
CONSTRAINT following_id_fk FOREIGN KEY(following_id) REFERENCES users(id)
);
CREATE TABLE followers_names (
user text not null,
time_update integer not null,
follower text not null,
PRIMARY KEY (user, follower)
);
CREATE TABLE following_names (
user text not null,
time_update integer not null,
follows text not null,
PRIMARY KEY (user, follows)
);
CREATE TABLE related_users (
related_users not null,
notes not null,
PRIMARY KEY (related_users)
);
CREATE TABLE media(
media_id not null,
found_in_tweets not null,
found_in_users not null,
file_size
hash_CRC32
hash_MD5
hash_SHA1
PRIMARY KEY (id)
);
COMMIT;
PRAGMA auto_vacuum = 1;
PRAGMA journal_mode=WAL;
PRAGMA synchronous = 0;
COMMIT;