1 |
DROP TABLE IF EXISTS feeds; |
2 |
CREATE TABLE feeds ( |
3 |
|
4 |
# internal to Reblog |
5 |
id INT UNSIGNED NOT NULL AUTO_INCREMENT, |
6 |
url TEXT NOT NULL DEFAULT '', |
7 |
|
8 |
# http://feedparser.org/docs/reference-feed-title.html |
9 |
title VARCHAR(255) NOT NULL DEFAULT '', |
10 |
|
11 |
# http://feedparser.org/docs/reference-feed-link.html |
12 |
link VARCHAR(255) DEFAULT NULL, |
13 |
|
14 |
# http://feedparser.org/docs/reference-feed-tagline.html |
15 |
description VARCHAR(255) DEFAULT NULL, |
16 |
|
17 |
`timestamp` TIMESTAMP(14) NOT NULL, |
18 |
|
19 |
# internal to Reblog, necessary? |
20 |
xml TEXT DEFAULT NULL, |
21 |
|
22 |
PRIMARY KEY (id), |
23 |
FULLTEXT(url, title, link, description) |
24 |
|
25 |
) TYPE=MyISAM; |
26 |
|
27 |
DROP TABLE IF EXISTS feeds_userdata; |
28 |
CREATE TABLE feeds_userdata ( |
29 |
|
30 |
# key columns |
31 |
feed_id INT UNSIGNED NOT NULL DEFAULT 0, |
32 |
user_id INT UNSIGNED NOT NULL DEFAULT 0, |
33 |
label VARCHAR(64) NOT NULL DEFAULT '', |
34 |
|
35 |
# value columns - application code figures out how |
36 |
# to interpret these, based on `label` column above |
37 |
value_numeric INT, |
38 |
value_short VARCHAR(255), |
39 |
value_long TEXT, |
40 |
|
41 |
`timestamp` TIMESTAMP(14) NOT NULL, |
42 |
|
43 |
INDEX user_feed (user_id, feed_id), |
44 |
INDEX user_label (user_id, label), |
45 |
INDEX user_label_feed (user_id, feed_id, label), |
46 |
INDEX user_label_numeric (user_id, label, value_numeric), |
47 |
INDEX user_label_short (user_id, label, value_short) |
48 |
|
49 |
) TYPE=InnoDB; |
50 |
|
51 |
DROP TABLE IF EXISTS items; |
52 |
CREATE TABLE items ( |
53 |
|
54 |
# internal to Reblog |
55 |
id INT UNSIGNED NOT NULL AUTO_INCREMENT, |
56 |
feed_id INT UNSIGNED NOT NULL DEFAULT 0, |
57 |
|
58 |
# http://feedparser.org/docs/reference-entry-id.html |
59 |
guid VARCHAR(255) NOT NULL DEFAULT '', |
60 |
|
61 |
# http://feedparser.org/docs/reference-entry-link.html |
62 |
link TEXT, |
63 |
|
64 |
# http://feedparser.org/docs/reference-entry-title.html |
65 |
title TEXT, |
66 |
|
67 |
# http://feedparser.org/docs/reference-entry-summary.html |
68 |
content TEXT, |
69 |
|
70 |
# http://feedparser.org/docs/reference-entry-author.html |
71 |
author VARCHAR(255), |
72 |
|
73 |
# http://feedparser.org/docs/reference-entry-category.html |
74 |
category TEXT, |
75 |
|
76 |
# http://feedparser.org/docs/reference-entry-modified.html |
77 |
modified DATETIME, |
78 |
|
79 |
`timestamp` TIMESTAMP(14) NOT NULL, |
80 |
|
81 |
PRIMARY KEY (id), |
82 |
UNIQUE KEY feed_id_guid (feed_id, guid), |
83 |
FULLTEXT(link, title, content, author, category) |
84 |
|
85 |
) TYPE=MyISAM; |
86 |
|
87 |
DROP TABLE IF EXISTS items_userdata; |
88 |
CREATE TABLE items_userdata ( |
89 |
|
90 |
# key columns |
91 |
item_id INT UNSIGNED NOT NULL DEFAULT 0, |
92 |
user_id INT UNSIGNED NOT NULL DEFAULT 0, |
93 |
label VARCHAR(64) NOT NULL DEFAULT '', |
94 |
|
95 |
# value columns - application code figures out how |
96 |
# to interpret these, based on `label` column above |
97 |
value_numeric INT, |
98 |
value_short VARCHAR(255), |
99 |
value_long TEXT, |
100 |
|
101 |
# for kicks |
102 |
`timestamp` TIMESTAMP(14) NOT NULL, |
103 |
|
104 |
INDEX user_item (user_id, item_id), |
105 |
INDEX user_label (user_id, label), |
106 |
INDEX user_label_item (user_id, item_id, label), |
107 |
INDEX user_label_numeric (user_id, label, value_numeric), |
108 |
INDEX user_label_short (user_id, label, value_short) |
109 |
|
110 |
) TYPE=InnoDB; |