KWWmMgYj

1# Copyright (c) 2013 Georgios Gousios
2# MIT-licensed
3# Edited to handle two new tables: postLinks and tags that are present in the 2014 dataset
4drop database if exists coffeeflow;
5
6create database coffeeflow DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
7
8use coffeeflow;
9
10create table badges (
11  Id INT NOT NULL PRIMARY KEY,
12  UserId INT,
13  Name VARCHAR(50),
14  Date DATETIME
15);
16
17CREATE TABLE comments (
18    Id INT NOT NULL PRIMARY KEY,
19    PostId INT NOT NULL,
20    Score INT NOT NULL DEFAULT 0,
21    Text TEXT,
22    CreationDate DATETIME,
23    UserId INT DEFAULT NULL
24);
25
26CREATE TABLE post_history (
27    Id INT NOT NULL PRIMARY KEY,
28    PostHistoryTypeId SMALLINT NOT NULL,
29    PostId INT NOT NULL,
30    RevisionGUID VARCHAR(36),
31    CreationDate DATETIME,
32    UserId INT DEFAULT NULL,
33    Text TEXT
34);
35CREATE TABLE post_links (
36  Id INT NOT NULL PRIMARY KEY,
37  CreationDate DATETIME DEFAULT NULL,
38  PostId INT NOT NULL,
39  RelatedPostId INT NOT NULL,
40  LinkTypeId INT DEFAULT NULL
41);
42
43
44CREATE TABLE posts (
45    Id INT NOT NULL PRIMARY KEY,
46    PostTypeId SMALLINT,
47    AcceptedAnswerId INT,
48    ParentId INT,
49    Score INT NULL,
50    ViewCount INT NULL,
51    Body text NULL,
52    OwnerUserId INT DEFAULT NULL,
53    LastEditorUserId INT,
54    LastEditDate DATETIME,
55    LastActivityDate DATETIME,
56    Title varchar(256) DEFAULT "",
57    Tags VARCHAR(256),
58    AnswerCount INT DEFAULT 0,
59    CommentCount INT NOT NULL DEFAULT 0,
60    FavoriteCount INT DEFAULT 0,
61    CreationDate DATETIME
62);
63
64CREATE TABLE tags (
65  Id INT NOT NULL PRIMARY KEY,
66  TagName VARCHAR(50) CHARACTER SET latin1 DEFAULT NULL,
67  Count INT DEFAULT NULL,
68  ExcerptPostId INT DEFAULT NULL,
69  WikiPostId INT DEFAULT NULL
70);
71
72
73CREATE TABLE users (
74    Id INT NOT NULL PRIMARY KEY,
75    Reputation INT NOT NULL,
76    CreationDate DATETIME,
77    DisplayName VARCHAR(50) NULL,
78    LastAccessDate  DATETIME,
79    Views INT DEFAULT 0,
80    WebsiteUrl VARCHAR(256) NULL,
81    Location VARCHAR(256) NULL,
82    AboutMe TEXT NULL,
83    Age INT,
84    UpVotes INT,
85    DownVotes INT,
86    EmailHash VARCHAR(32)
87);
88
89CREATE TABLE votes (
90    Id INT NOT NULL PRIMARY KEY,
91    PostId INT NOT NULL,
92    VoteTypeId SMALLINT,
93    CreationDate DATETIME
94);
95
96load xml infile '/var/lib/mysql/amazingfiles/Badges.xml'
97into table badges
98rows identified by '<row>';
99
100load xml infile '/var/lib/mysql/amazingfiles/Comments.xml'
101into table comments
102rows identified by '<row>';
103
104load xml infile '/var/lib/mysql/amazingfiles/PostHistory.xml'
105into table post_history
106rows identified by '<row>';
107
108load xml infile '/var/lib/mysql/amazingfiles/PostLinks.xml'
109into table post_links
110rows identified BY '<row>';
111
112load xml infile '/var/lib/mysql/amazingfiles/Posts.xml'
113into table posts
114rows identified by '<row>';
115
116load xml infile '/var/lib/mysql/amazingfiles/Tags.xml'
117into table tags
118rows identified BY '<row>';
119
120load xml infile '/var/lib/mysql/amazingfiles/Users.xml'
121into table users
122rows identified by '<row>';
123
124load xml infile '/var/lib/mysql/amazingfiles/Votes.xml'
125into table votes
126rows identified by '<row>';
127
128create index badges_idx_1 on badges(UserId);
129
130create index comments_idx_1 on comments(PostId);
131create index comments_idx_2 on comments(UserId);
132
133create index post_history_idx_1 on post_history(PostId);
134create index post_history_idx_2 on post_history(UserId);
135
136create index posts_idx_1 on posts(AcceptedAnswerId);
137create index posts_idx_2 on posts(ParentId);
138create index posts_idx_3 on posts(OwnerUserId);
139create index posts_idx_4 on posts(LastEditorUserId);
140
141create index votes_idx_1 on votes(PostId);