Metafilter "Infodump" SQL scripts

askmefi_multijoin.sql

-- Example multi-table implicit JOIN
-- Used here to find AskMe postings given a username or tag

SELECT DISTINCT posttitles_askme.title
FROM tagdata_askme, postdata_askme, usernames, posttitles_askme
WHERE (usernames.userid = postdata_askme.userid)
AND (tagdata_askme.postid = postdata_askme.postid)
AND (tagdata_askme.postid = posttitles_askme.postid)
-- AND tagdata_askme.tagname = 'linux'
AND usernames.name = 'TargetUserNameHere';

infodump_categories.sql

CREATE TABLE categories_askme
(category INT PRIMARY KEY,
description VARCHAR(100),
urlstub VARCHAR(100)
);

CREATE TABLE categories_mefi
(category INT PRIMARY KEY,
description VARCHAR(100),
urlstub VARCHAR(100)
);

CREATE TABLE categories_meta
(category INT PRIMARY KEY,
description VARCHAR(100),
urlstub VARCHAR(100)
);

CREATE TABLE categories_music
(category INT PRIMARY KEY,
description VARCHAR(100),
urlstub VARCHAR(50)
);

infodump_create-mysql.sql

CREATE TABLE commentdata_askme
(commentid int,
postid int,
userid int,
datestamp datetime,
faves int,
bestanswer tinyint);

CREATE TABLE commentdata_mefi
(commentid int,
postid int,
userid int,
datestamp datetime,
faves int,
bestanswer tinyint);

CREATE TABLE commentdata_meta
(commentid int,
postid int,
userid int,
datestamp datetime,
faves int,
bestanswer tinyint);

CREATE TABLE commentdata_music
(commentid int,
postid int,
userid int,
datestamp datetime,
faves int,
bestanswer tinyint);

CREATE TABLE contactdata
(contacter int,
contactee int);

CREATE TABLE favoritesdata
(faveid int,
faver int,
favee int,
type int,
target int,
parent int,
datestamp datetime);

CREATE TABLE postdata_askme
(postid int,
userid int,
datestamp datetime,
category int,
comments int,
favorites int,
deleted tinyint,
reason text);

CREATE TABLE postdata_mefi
(postid int,
userid int,
datestamp datetime,
category int,
comments int,
favorites int,
deleted tinyint,
reason text);

CREATE TABLE postdata_meta
(postid int,
userid int,
datestamp datetime,
category int,
comments int,
favorites int,
deleted tinyint,
reason text);

CREATE TABLE postdata_music
(postid int,
userid int,
datestamp datetime,
category int,
comments int,
favorites int,
deleted tinyint,
reason text);

CREATE TABLE posttitles_askme
(postid int,
title text);

CREATE TABLE posttitles_mefi
(postid int,
title text);

CREATE TABLE posttitles_meta
(postid int,
title text);

CREATE TABLE posttitles_music
(postid int,
title text);

CREATE TABLE tagdata_askme
(tagid int,
linkid int,
linkdate datetime,
tagname text);

CREATE TABLE tagdata_mefi
(tagid int,
linkid int,
linkdate datetime,
tagname text);

CREATE TABLE tagdata_meta
(tagid int,
linkid int,
linkdate datetime,
tagname text);

CREATE TABLE tagdata_music
(tagid int,
linkid int,
linkdate datetime,
tagname text);

CREATE TABLE usernames
(userid int,
joindate datetime,
name text);

infodump_load-mysql.sql

LOAD DATA LOCAL INFILE 'commentdata_askme.txt'
INTO TABLE commentdata_askme
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'commentdata_mefi.txt'
INTO TABLE commentdata_mefi
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'commentdata_meta.txt'
INTO TABLE commentdata_meta
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'commentdata_music.txt'
INTO TABLE commentdata_music
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'contactdata.txt'
INTO TABLE contactdata
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'favoritesdata.txt'
INTO TABLE favoritesdata
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'postdata_askme.txt'
INTO TABLE postdata_askme
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'postdata_mefi.txt'
INTO TABLE postdata_mefi
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'postdata_meta.txt'
INTO TABLE postdata_meta
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'postdata_music.txt'
INTO TABLE postdata_music
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'posttitles_askme.txt'
INTO TABLE posttitles_askme
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'posttitles_mefi.txt'
INTO TABLE posttitles_mefi
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'posttitles_meta.txt'
INTO TABLE posttitles_meta
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'posttitles_music.txt'
INTO TABLE posttitles_music
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'tagdata_askme.txt'
INTO TABLE tagdata_askme
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'tagdata_mefi.txt'
INTO TABLE tagdata_mefi
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'tagdata_meta.txt'
INTO TABLE tagdata_meta
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'tagdata_music.txt'
INTO TABLE tagdata_music
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE 'usernames.txt'
INTO TABLE usernames
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;

Metafilter Infodump SQL.md

What is this stuff?

These are some SQL scripts that are designed to process the output of Metafilter's "Infodump" files.