TXT42NUC

1use crowdrise;
2drop table if exists all_files;
3CREATE TABLE all_files
4(
5    file_name VARCHAR(190) PRIMARY KEY NOT NULL
6);
7drop table if exists charity;
8CREATE TABLE charity
9(
10    url VARCHAR(190) PRIMARY KEY NOT NULL,
11    name TEXT,
12    money_raised DECIMAL(15,4),
13    started DATE,
14    has_video TINYINT(1),
15    num_events INT(11),
16    site VARCHAR(190),
17    mission_html TEXT,
18    ein VARCHAR(190),
19    location VARCHAR(190)
20);
21drop table if exists charity_event;
22CREATE TABLE charity_event
23(
24    charity VARCHAR(190) NOT NULL,
25    event VARCHAR(190) NOT NULL,
26    CONSTRAINT `PRIMARY` PRIMARY KEY (charity, event)
27);
28drop table if exists donation;
29CREATE TABLE donation
30(
31    url VARCHAR(190) NOT NULL,
32    id INT(11) NOT NULL,
33    name VARCHAR(190),
34    date DATETIME,
35    amount DECIMAL(15,4),
36    username VARCHAR(190),
37    message TEXT,
38    CONSTRAINT `PRIMARY` PRIMARY KEY (url, id)
39);
40drop table if exists event;
41CREATE TABLE event
42(
43    url VARCHAR(190) PRIMARY KEY NOT NULL,
44    story TEXT,
45    goal DECIMAL(15,4),
46    deadline_to_give DATETIME,
47    has_video TINYINT(1),
48    amount_raised DECIMAL(15,4),
49    event_date DATETIME
50);
51drop table if exists front_page_redirect;
52CREATE TABLE front_page_redirect
53(
54    url VARCHAR(190) PRIMARY KEY NOT NULL
55);
56drop table if exists funding_trend;
57CREATE TABLE funding_trend
58(
59    url VARCHAR(190) NOT NULL,
60    username VARCHAR(190) NOT NULL,
61    amount_raised DECIMAL(15,4),
62    goal DECIMAL(15,4),
63    scrape_time DATETIME NOT NULL,
64    type VARCHAR(190) NOT NULL,
65    CONSTRAINT `PRIMARY` PRIMARY KEY (url, username, type)
66);
67drop table if exists fundraiser;
68CREATE TABLE fundraiser
69(
70    url VARCHAR(190) PRIMARY KEY NOT NULL,
71    true_url VARCHAR(190),
72    base_true_url VARCHAR(190),
73    username VARCHAR(190),
74    fundraiser_id INT(11),
75    story TEXT,
76    project_id INT(11),
77    benefiting VARCHAR(190),
78    parent_organizer_username VARCHAR(190),
79    about TEXT,
80    member_type VARCHAR(190),
81    campaign_id INT(11),
82    direct_to_organizer TINYINT(1),
83    total_raised DECIMAL(15,4),
84    team_total_raised DECIMAL(15,4),
85    is_organizer TINYINT(1),
86    project_name TEXT,
87    owner_username VARCHAR(190),
88    event_date DATETIME,
89    parent_organizer_fundraiser_url VARCHAR(190),
90    event VARCHAR(190),
91    project_username VARCHAR(190),
92    num_comments INT(11)
93);
94drop table if exists html;
95CREATE TABLE html
96(
97    url VARCHAR(190) PRIMARY KEY NOT NULL,
98    last_scrape DATETIME,
99    html MEDIUMBLOB
100);
101drop table if exists html_old;
102CREATE TABLE html_old
103(
104    url VARCHAR(190) PRIMARY KEY NOT NULL,
105    last_scrape DATETIME
106);
107drop table if exists special_user;
108CREATE TABLE special_user
109(
110    url VARCHAR(190) PRIMARY KEY NOT NULL
111);
112drop table if exists team;
113CREATE TABLE team
114(
115    fundraiser_url VARCHAR(190) NOT NULL,
116    username VARCHAR(190) NOT NULL,
117    amount_raised DECIMAL(15,4),
118    goal DECIMAL(15,4),
119    fundraiser_id INT(11),
120    project_id INT(11),
121    charity_id INT(11),
122    CONSTRAINT `PRIMARY` PRIMARY KEY (fundraiser_url, username)
123);
124drop table if exists updated_urls;
125CREATE TABLE updated_urls
126(
127    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
128    url TEXT
129);
130drop table if exists user;
131CREATE TABLE user
132(
133    url VARCHAR(190) PRIMARY KEY NOT NULL,
134    username VARCHAR(190),
135    impact_points FLOAT,
136    about TEXT,
137    money_raised DECIMAL(15,4),
138    number_of_projects INT(11),
139    join_date DATE,
140    donate_link VARCHAR(190),
141    location VARCHAR(190),
142    full_name TEXT
143);
144drop table if exists user_project;
145CREATE TABLE user_project
146(
147    username VARCHAR(190) NOT NULL,
148    project VARCHAR(190) NOT NULL,
149    CONSTRAINT `PRIMARY` PRIMARY KEY (username, project)
150);
151CREATE INDEX idx_donation_date ON donation (date);
152CREATE UNIQUE INDEX base_true_url_UNIQUE ON fundraiser (base_true_url);
153CREATE INDEX idx_fundraiser_base_true_url ON fundraiser (base_true_url);
154CREATE INDEX url_key ON fundraiser (url);
155CREATE INDEX username_key ON fundraiser (username);
156CREATE INDEX html_last_scrape_index ON html (last_scrape);
157CREATE INDEX ix_html_81736358b1645103 ON html (url);
158CREATE INDEX team_goal_index ON team (goal);
159CREATE INDEX user_project_fundraiser_url_fkey_idx ON user_project (project);