]>
git.wh0rd.org - tt-rss.git/blob - schema/ttrss_schema_mysql.sql
1 drop table if exists ttrss_version
;
2 drop table if exists ttrss_labels
;
3 drop table if exists ttrss_filters
;
4 drop table if exists ttrss_filter_types
;
5 drop table if exists ttrss_filter_actions
;
6 drop table if exists ttrss_user_prefs
;
7 drop table if exists ttrss_prefs
;
8 drop table if exists ttrss_prefs_types
;
9 drop table if exists ttrss_prefs_sections
;
10 drop table if exists ttrss_tags
;
11 drop table if exists ttrss_entry_comments
;
12 drop table if exists ttrss_user_entries
;
13 drop table if exists ttrss_entries
;
14 drop table if exists ttrss_scheduled_updates
;
15 drop table if exists ttrss_feeds
;
16 drop table if exists ttrss_feed_categories
;
17 drop table if exists ttrss_users
;
18 drop table if exists ttrss_themes
;
19 drop table if exists ttrss_sessions
;
23 create table ttrss_themes ( id integer not null primary key auto_increment
,
24 theme_name
varchar ( 200 ) not null ,
25 theme_path
varchar ( 200 ) not null ) TYPE = InnoDB
;
27 insert into ttrss_themes ( theme_name
, theme_path
) values ( 'Old-skool' , 'compat' );
29 create table ttrss_users ( id integer primary key not null auto_increment
,
30 login varchar ( 120 ) not null unique ,
31 pwd_hash
varchar ( 250 ) not null ,
32 last_login datetime
default null ,
33 access_level
integer not null default 0 ,
34 theme_id
integer default null ,
35 email
varchar ( 250 ) not null default '' ,
37 foreign key ( theme_id
) references ttrss_themes ( id )) TYPE = InnoDB
;
39 insert into ttrss_users ( login , pwd_hash
, access_level
) values ( 'admin' ,
40 'SHA1:5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8' , 10 );
42 create table ttrss_feed_categories ( id integer not null primary key auto_increment
,
43 owner_uid
integer not null ,
44 title
varchar ( 200 ) not null ,
45 collapsed bool
not null default false ,
47 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
49 create table ttrss_feeds ( id integer not null auto_increment
primary key ,
50 owner_uid
integer not null ,
51 title
varchar ( 200 ) not null ,
52 cat_id
integer default null ,
53 feed_url
varchar ( 250 ) not null ,
54 icon_url
varchar ( 250 ) not null default '' ,
55 update_interval
integer not null default 0 ,
56 purge_interval
integer not null default 0 ,
57 last_updated datetime
default 0 ,
58 last_error
text not null default '' ,
59 site_url
varchar ( 250 ) not null default '' ,
60 auth_login
varchar ( 250 ) not null default '' ,
61 auth_pass
varchar ( 250 ) not null default '' ,
62 parent_feed
integer default null ,
63 private bool
not null default false ,
64 rtl_content bool
not null default false ,
65 hidden bool
not null default false ,
67 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ,
69 foreign key ( cat_id
) references ttrss_feed_categories ( id ),
71 foreign key ( parent_feed
) references ttrss_feeds ( id ) ON DELETE SET NULL ) TYPE = InnoDB
;
73 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'Footnotes' , 'http://gnomedesktop.org/node/feed' );
74 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'Latest Linux Kernel Versions' , 'http://kernel.org/kdist/rss.xml' );
75 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'RPGDot Newsfeed' ,
76 'http://www.rpgdot.com/team/rss/rss0.xml' );
77 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'Digg.com News' ,
78 'http://digg.com/rss/index.xml' );
79 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'Technocrat.net' ,
80 'http://syndication.technocrat.net/rss' );
82 create table ttrss_entries ( id integer not null primary key auto_increment
,
84 guid
varchar ( 255 ) not null unique ,
86 updated datetime
not null ,
87 content text not null ,
88 content_hash
varchar ( 250 ) not null ,
89 no_orig_date bool
not null default 0 ,
90 date_entered datetime
not null ,
91 num_comments
integer not null default 0 ,
92 comments varchar ( 250 ) not null default '' ,
93 author
varchar ( 250 ) not null default '' ) TYPE = InnoDB
;
95 create table ttrss_user_entries (
96 int_id
integer not null primary key auto_increment
,
97 ref_id
integer not null ,
99 owner_uid
integer not null ,
100 marked bool
not null default 0 ,
102 unread bool
not null default 1 ,
104 foreign key ( ref_id
) references ttrss_entries ( id ) ON DELETE CASCADE ,
106 foreign key ( feed_id
) references ttrss_feeds ( id ) ON DELETE CASCADE ,
108 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
110 create table ttrss_entry_comments ( id integer not null primary key ,
111 ref_id
integer not null ,
112 owner_uid
integer not null ,
113 private bool
not null default 0 ,
114 date_entered datetime
not null ,
116 foreign key ( ref_id
) references ttrss_entries ( id ) ON DELETE CASCADE ,
118 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
120 create table ttrss_filter_types ( id integer primary key ,
121 name varchar ( 120 ) unique not null ,
122 description
varchar ( 250 ) not null unique ) TYPE = InnoDB
;
124 insert into ttrss_filter_types ( id , name , description
) values ( 1 , 'title' , 'Title' );
125 insert into ttrss_filter_types ( id , name , description
) values ( 2 , 'content' , 'Content' );
126 insert into ttrss_filter_types ( id , name , description
) values ( 3 , 'both' ,
128 insert into ttrss_filter_types ( id , name , description
) values ( 4 , 'link' ,
131 create table ttrss_filter_actions ( id integer not null primary key ,
132 name varchar ( 120 ) unique not null ,
133 description
varchar ( 250 ) not null unique ) TYPE = InnoDB
;
135 insert into ttrss_filter_actions ( id , name , description
) values ( 1 , 'filter' ,
138 insert into ttrss_filter_actions ( id , name , description
) values ( 2 , 'catchup' ,
141 insert into ttrss_filter_actions ( id , name , description
) values ( 3 , 'mark' ,
144 create table ttrss_filters ( id integer not null primary key auto_increment
,
145 owner_uid
integer not null ,
146 feed_id
integer default null ,
147 filter_type
integer not null ,
148 reg_exp
varchar ( 250 ) not null ,
149 action_id
integer not null default 1 ,
151 foreign key ( filter_type
) references ttrss_filter_types ( id ) ON DELETE CASCADE ,
153 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ,
155 foreign key ( feed_id
) references ttrss_feeds ( id ) ON DELETE CASCADE ,
157 foreign key ( action_id
) references ttrss_filter_actions ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
159 create table ttrss_labels ( id integer not null primary key auto_increment
,
160 owner_uid
integer not null ,
161 sql_exp
varchar ( 250 ) not null ,
162 description
varchar ( 250 ) not null ,
164 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
166 insert into ttrss_labels ( owner_uid
, sql_exp
, description
) values ( 1 , 'unread = true' ,
169 insert into ttrss_labels ( owner_uid
, sql_exp
, description
) values ( 1 ,
170 'last_read is null and unread = false' , 'Updated articles' );
172 create table ttrss_tags ( id integer primary key auto_increment
,
173 owner_uid
integer not null ,
174 tag_name
varchar ( 250 ) not null ,
175 post_int_id
integer not null ,
177 foreign key ( post_int_id
) references ttrss_user_entries ( int_id
) ON DELETE CASCADE ,
179 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
181 create table ttrss_version ( schema_version
int not null ) TYPE = InnoDB
;
183 insert into ttrss_version
values ( 9 );
185 create table ttrss_prefs_types ( id integer not null primary key ,
186 type_name
varchar ( 100 ) not null ) TYPE = InnoDB
;
188 insert into ttrss_prefs_types ( id , type_name
) values ( 1 , 'bool' );
189 insert into ttrss_prefs_types ( id , type_name
) values ( 2 , 'string' );
190 insert into ttrss_prefs_types ( id , type_name
) values ( 3 , 'integer' );
192 create table ttrss_prefs_sections ( id integer not null primary key ,
193 section_name
varchar ( 100 ) not null ) TYPE = InnoDB
;
195 insert into ttrss_prefs_sections ( id , section_name
) values ( 1 , 'General' );
196 insert into ttrss_prefs_sections ( id , section_name
) values ( 2 , 'Interface' );
197 insert into ttrss_prefs_sections ( id , section_name
) values ( 3 , 'Advanced' );
199 create table ttrss_prefs ( pref_name
varchar ( 250 ) not null primary key ,
200 type_id
integer not null ,
201 section_id
integer not null default 1 ,
202 short_desc
text not null ,
203 help_text
text not null default '' ,
204 def_value
text not null ,
206 foreign key ( type_id
) references ttrss_prefs_types ( id ),
208 foreign key ( section_id
) references ttrss_prefs_sections ( id )) TYPE = InnoDB
;
210 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'ENABLE_FEED_ICONS' , 1 , 'true' , 'Enable icons in feedlist' , 3 );
211 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'PURGE_OLD_DAYS' , 3 , '60' , 'Purge old posts after this number of days (0 - disables)' , 1 );
212 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'UPDATE_POST_ON_CHECKSUM_CHANGE' , 1 , 'true' , 'Update post on checksum change' , 1 );
213 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
, help_text
) values ( 'ENABLE_LABELS' , 1 , 'false' , 'Enable labels' , 3 ,
214 'Experimental support for virtual feeds based on user crafted SQL queries. This feature is highly experimental and at this point not user friendly. Use with caution.' );
216 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'DEFAULT_UPDATE_INTERVAL' , 3 , '30' , 'Default interval between feed updates (in minutes)' , 1 );
217 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'DISPLAY_HEADER' , 1 , 'true' , 'Display header' , 2 );
218 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'DISPLAY_FOOTER' , 1 , 'true' , 'Display footer' , 2 );
219 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'USE_COMPACT_STYLESHEET' , 1 , 'false' , 'Use compact stylesheet by default' , 2 );
220 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
, help_text
) values ( 'DEFAULT_ARTICLE_LIMIT' , 3 , '0' , 'Default article limit' , 2 ,
221 'Default limit for articles to display, any custom number you like (0 - disables).' );
223 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
, help_text
) values ( 'ALLOW_DUPLICATE_POSTS' , 1 , 'true' , 'Allow duplicate posts' , 1 ,
224 'This option is useful when you are reading several planet-type aggregators with partially colliding userbase.
225 When disabled, it forces same posts from different feeds to appear only once.' );
227 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
, help_text
) values ( 'USER_STYLESHEET_URL' , 2 , '' , 'User stylesheet URL' , 2 ,
228 'Link to user stylesheet to override default style, disabled if empty.' );
230 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'ENABLE_FEED_CATS' , 1 , 'false' , 'Enable feed categories' , 2 );
232 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'SHOW_CONTENT_PREVIEW' , 1 , 'true' , 'Show content preview in headlines list' , 2 );
234 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'SHORT_DATE_FORMAT' , 2 , 'M d, G:i' , 'Short date format' , 3 );
235 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'LONG_DATE_FORMAT' , 2 , 'D, M d Y - G:i' , 'Long date format' , 3 );
237 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'HEADLINES_SMART_DATE' , 1 , 'true' , 'Use more accessible date/time format for headlines' , 3 );
239 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
, help_text
) values ( 'COMBINED_DISPLAY_MODE' , 1 , 'false' , 'Combined feed display' , 2 ,
240 'Display expanded list of feed articles, instead of separate displays for headlines and article content' );
242 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'ENABLE_SEARCH_TOOLBAR' , 1 , 'false' , 'Enable search toolbar' , 3 );
244 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'HIDE_READ_FEEDS' , 1 , 'false' , 'Hide feeds with no unread messages' , 2 );
246 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'OPEN_LINKS_IN_NEW_WINDOW' , 1 , 'true' , 'Open article links in new browser window' , 2 );
248 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
, help_text
) values ( 'ON_CATCHUP_SHOW_NEXT_FEED' , 1 , 'false' , 'On catchup show next feed' , 2 ,
249 'When "Mark as read" button is clicked in toolbar, automatically open next feed with unread articles.' );
251 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'FEEDS_SORT_BY_UNREAD' , 1 , 'false' , 'Sort feeds by unread articles count' , 2 );
253 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'EXTENDED_FEEDLIST' , 1 , 'false' , 'Show additional information in feedlist' , 3 );
255 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'MARK_UNREAD_ON_UPDATE' , 1 , 'false' , 'Set articles as unread on update' , 3 );
257 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'REVERSE_HEADLINES' , 1 , 'false' , 'Reverse headline order (oldest first)' , 2 );
259 create table ttrss_user_prefs (
260 owner_uid
integer not null ,
261 pref_name
varchar ( 250 ),
264 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ,
266 foreign key ( pref_name
) references ttrss_prefs ( pref_name
) ON DELETE CASCADE ) TYPE = InnoDB
;
268 create table ttrss_scheduled_updates ( id integer not null primary key auto_increment
,
269 owner_uid
integer not null ,
270 feed_id
integer default null ,
271 entered datetime
not null ,
272 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ,
273 foreign key ( feed_id
) references ttrss_feeds ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
275 create table ttrss_sessions ( id varchar ( 250 ) unique not null primary key ,
277 expire
integer not null ,
279 index ( expire
)) TYPE = InnoDB
;