refeed/trunk/install.mysql

DROP TABLE IF EXISTS feeds;
CREATE TABLE feeds (
    
    # internal to Reblog
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    url             TEXT NOT NULL DEFAULT '',
    
    # http://feedparser.org/docs/reference-feed-title.html
    title           VARCHAR(255) NOT NULL DEFAULT '',
    
    # http://feedparser.org/docs/reference-feed-link.html
    link            VARCHAR(255) DEFAULT NULL,
    
    # http://feedparser.org/docs/reference-feed-tagline.html
    description     VARCHAR(255) DEFAULT NULL,
    
    `timestamp`     TIMESTAMP(14) NOT NULL,

    # internal to Reblog, necessary?
    xml             TEXT DEFAULT NULL,
    
    PRIMARY KEY (id),
    FULLTEXT(url, title, link, description)

) TYPE=MyISAM;

DROP TABLE IF EXISTS feeds_userdata;
CREATE TABLE feeds_userdata (

    # key columns
    feed_id         INT UNSIGNED NOT NULL DEFAULT 0,
    user_id         INT UNSIGNED NOT NULL DEFAULT 0,
    label           VARCHAR(64) NOT NULL DEFAULT '',
    
    # value columns - application code figures out how
    # to interpret these, based on `label` column above
    value_numeric   INT,
    value_short     VARCHAR(255),
    value_long      TEXT,

    `timestamp`     TIMESTAMP(14) NOT NULL,
    
    INDEX user_feed (user_id, feed_id),
    INDEX user_label (user_id, label),
    INDEX user_label_feed (user_id, feed_id, label),
    INDEX user_label_numeric (user_id, label, value_numeric),
    INDEX user_label_short (user_id, label, value_short)

) TYPE=InnoDB;

DROP TABLE IF EXISTS items;
CREATE TABLE items (
    
    # internal to Reblog
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    feed_id         INT UNSIGNED NOT NULL DEFAULT 0,

    # http://feedparser.org/docs/reference-entry-id.html
    guid            VARCHAR(255) NOT NULL DEFAULT '',
    
    # http://feedparser.org/docs/reference-entry-link.html
    link            TEXT,
    
    # http://feedparser.org/docs/reference-entry-title.html
    title           TEXT,
    
    # http://feedparser.org/docs/reference-entry-summary.html
    content         TEXT,

    # http://feedparser.org/docs/reference-entry-author.html
    author          VARCHAR(255),

    # http://feedparser.org/docs/reference-entry-category.html
    category        TEXT,

    # http://feedparser.org/docs/reference-entry-modified.html
    modified        DATETIME,

    `timestamp`     TIMESTAMP(14) NOT NULL,
    
    PRIMARY KEY (id),
    UNIQUE KEY feed_id_guid (feed_id, guid),
    FULLTEXT(link, title, content, author, category)

) TYPE=MyISAM;

DROP TABLE IF EXISTS items_userdata;
CREATE TABLE items_userdata (

    # key columns
    item_id         INT UNSIGNED NOT NULL DEFAULT 0,
    user_id         INT UNSIGNED NOT NULL DEFAULT 0,
    label           VARCHAR(64) NOT NULL DEFAULT '',
    
    # value columns - application code figures out how
    # to interpret these, based on `label` column above
    value_numeric   INT,
    value_short     VARCHAR(255),
    value_long      TEXT,

    # for kicks
    `timestamp`     TIMESTAMP(14) NOT NULL,
    
    INDEX user_item (user_id, item_id),
    INDEX user_label (user_id, label),
    INDEX user_label_item (user_id, item_id, label),
    INDEX user_label_numeric (user_id, label, value_numeric),
    INDEX user_label_short (user_id, label, value_short)

) TYPE=InnoDB;
1	dpavlin	2	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;