]>
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_user_prefs
;
6 drop table if exists ttrss_prefs
;
7 drop table if exists ttrss_prefs_types
;
8 drop table if exists ttrss_prefs_sections
;
9 drop table if exists ttrss_tags
;
10 drop table if exists ttrss_entry_comments
;
11 drop table if exists ttrss_user_entries
;
12 drop table if exists ttrss_entries
;
13 drop table if exists ttrss_feeds
;
14 drop table if exists ttrss_feed_categories
;
15 drop table if exists ttrss_users
;
16 drop table if exists ttrss_themes
;
18 create table ttrss_themes ( id integer not null primary key auto_increment
,
19 theme_name
varchar ( 200 ) not null ,
20 theme_path
varchar ( 200 ) not null ) TYPE = InnoDB
;
22 create table ttrss_users ( id integer primary key not null auto_increment
,
23 login varchar ( 120 ) not null unique ,
24 pwd_hash
varchar ( 250 ) not null ,
25 last_login datetime
default null ,
26 access_level
integer not null default 0 ,
27 theme_id
integer default null ,
29 foreign key ( theme_id
) references ttrss_themes ( id )) TYPE = InnoDB
;
31 insert into ttrss_users ( login , pwd_hash
, access_level
) values ( 'admin' , 'password' , 10 );
33 create table ttrss_feed_categories ( id integer not null primary key auto_increment
,
34 owner_uid
integer not null ,
35 title
varchar ( 200 ) not null ,
37 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
39 create table ttrss_feeds ( id integer not null auto_increment
primary key ,
40 owner_uid
integer not null ,
41 title
varchar ( 200 ) not null ,
42 cat_id
integer default null ,
43 feed_url
varchar ( 250 ) not null ,
44 icon_url
varchar ( 250 ) not null default '' ,
45 update_interval
integer not null default 0 ,
46 purge_interval
integer not null default 0 ,
47 last_updated datetime
default '' ,
48 last_error
text not null default '' ,
49 site_url
varchar ( 250 ) not null default '' ,
51 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ,
53 foreign key ( cat_id
) references ttrss_feed_categories ( id )) TYPE = InnoDB
;
55 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'Footnotes' , 'http://gnomedesktop.org/node/feed' );
56 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'Latest Linux Kernel Versions' , 'http://kernel.org/kdist/rss.xml' );
57 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'RPGDot Newsfeed' ,
58 'http://www.rpgdot.com/team/rss/rss0.xml' );
59 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'Digg.com News' ,
60 'http://digg.com/rss/index.xml' );
61 insert into ttrss_feeds ( owner_uid
, title
, feed_url
) values ( 1 , 'Technocrat.net' ,
62 'http://syndication.technocrat.net/rss' );
64 create table ttrss_entries ( id integer not null primary key auto_increment
,
66 guid
varchar ( 255 ) not null unique ,
68 updated datetime
not null ,
69 content text not null ,
70 content_hash
varchar ( 250 ) not null ,
71 no_orig_date bool
not null default 0 ,
72 date_entered datetime
not null ,
73 comments varchar ( 250 ) not null default '' ) TYPE = InnoDB
;
75 create table ttrss_user_entries (
76 int_id
integer not null primary key auto_increment
,
77 ref_id
integer not null ,
79 owner_uid
integer not null ,
80 marked bool
not null default 0 ,
82 unread bool
not null default 1 ,
84 foreign key ( ref_id
) references ttrss_entries ( id ) ON DELETE CASCADE ,
86 foreign key ( feed_id
) references ttrss_feeds ( id ) ON DELETE CASCADE ,
88 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
90 create table ttrss_entry_comments ( id integer not null primary key ,
91 ref_id
integer not null ,
92 owner_uid
integer not null ,
93 private bool
not null default 0 ,
94 date_entered datetime
not null ,
96 foreign key ( ref_id
) references ttrss_entries ( id ) ON DELETE CASCADE ,
98 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
100 create table ttrss_filter_types ( id integer primary key ,
101 name varchar ( 120 ) unique not null ,
102 description
varchar ( 250 ) not null unique ) TYPE = InnoDB
;
104 insert into ttrss_filter_types ( id , name , description
) values ( 1 , 'title' , 'Title' );
105 insert into ttrss_filter_types ( id , name , description
) values ( 2 , 'content' , 'Content' );
106 insert into ttrss_filter_types ( id , name , description
) values ( 3 , 'both' ,
108 insert into ttrss_filter_types ( id , name , description
) values ( 4 , 'link' ,
111 create table ttrss_filters ( id integer not null primary key auto_increment
,
112 owner_uid
integer not null ,
113 feed_id
integer default null ,
114 filter_type
integer not null ,
115 reg_exp
varchar ( 250 ) not null ,
116 description
varchar ( 250 ) not null default '' ,
118 foreign key ( filter_type
) references ttrss_filter_types ( id ) ON DELETE CASCADE ,
120 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ,
122 foreign key ( feed_id
) references ttrss_feeds ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
124 create table ttrss_labels ( id integer not null primary key auto_increment
,
125 owner_uid
integer not null ,
126 sql_exp
varchar ( 250 ) not null ,
127 description
varchar ( 250 ) not null ,
129 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
131 insert into ttrss_labels ( owner_uid
, sql_exp
, description
) values ( 1 , 'unread = true' ,
134 insert into ttrss_labels ( owner_uid
, sql_exp
, description
) values ( 1 ,
135 'last_read is null and unread = false' , 'Updated articles' );
137 create table ttrss_tags ( id integer primary key auto_increment
,
138 owner_uid
integer not null ,
139 tag_name
varchar ( 250 ) not null ,
140 post_int_id
integer not null ,
142 foreign key ( post_int_id
) references ttrss_user_entries ( int_id
) ON DELETE CASCADE ,
144 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ) TYPE = InnoDB
;
146 create table ttrss_version ( schema_version
int not null ) TYPE = InnoDB
;
148 insert into ttrss_version
values ( 2 );
150 create table ttrss_prefs_types ( id integer not null primary key ,
151 type_name
varchar ( 100 ) not null ) TYPE = InnoDB
;
153 insert into ttrss_prefs_types ( id , type_name
) values ( 1 , 'bool' );
154 insert into ttrss_prefs_types ( id , type_name
) values ( 2 , 'string' );
155 insert into ttrss_prefs_types ( id , type_name
) values ( 3 , 'integer' );
157 create table ttrss_prefs_sections ( id integer not null primary key ,
158 section_name
varchar ( 100 ) not null ) TYPE = InnoDB
;
160 insert into ttrss_prefs_sections ( id , section_name
) values ( 1 , 'General' );
161 insert into ttrss_prefs_sections ( id , section_name
) values ( 2 , 'Interface' );
162 insert into ttrss_prefs_sections ( id , section_name
) values ( 3 , 'Advanced' );
164 create table ttrss_prefs ( pref_name
varchar ( 250 ) not null primary key ,
165 type_id
integer not null ,
166 section_id
integer not null default 1 ,
167 short_desc
text not null ,
168 help_text
text not null default '' ,
169 def_value
text not null ,
171 foreign key ( type_id
) references ttrss_prefs_types ( id ),
173 foreign key ( section_id
) references ttrss_prefs_sections ( id )) TYPE = InnoDB
;
175 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'ENABLE_FEED_ICONS' , 1 , 'true' , 'Enable icons in feedlist' , 2 );
176 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 );
177 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 );
178 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'ENABLE_PREFS_CATCHUP_UNCATCHUP' , 1 , 'false' , 'Enable catchup/uncatchup buttons in feed editor' , 2 );
179 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
, help_text
) values ( 'ENABLE_LABELS' , 1 , 'false' , 'Enable labels' , 3 ,
180 '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.' );
182 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 );
183 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'DISPLAY_HEADER' , 1 , 'true' , 'Display header' , 2 );
184 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'DISPLAY_FOOTER' , 1 , 'true' , 'Display footer' , 2 );
185 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 );
186 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 ,
187 'Default limit for articles to display, any custom number you like (0 - disables).' );
189 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
, help_text
) values ( 'DISPLAY_FEEDLIST_ACTIONS' , 1 , 'false' , 'Display feedlist actions' , 2 ,
190 'Display separate dropbox for feedlist actions, if disabled these actions are available in global actions menu.' );
192 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'ENABLE_SPLASH' , 1 , 'false' , 'Enable loading splashscreen' , 2 );
194 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 ,
195 'This option is useful when you are reading several planet-type aggregators with partially colliding userbase.
196 When disabled, it forces same posts from different feeds to appear only once.' );
198 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 ,
199 'Link to user stylesheet to override default style, disabled if empty.' );
201 insert into ttrss_prefs ( pref_name
, type_id
, def_value
, short_desc
, section_id
) values ( 'ENABLE_FEED_CATS' , 1 , 'false' , 'Enable feed categories' , 2 );
204 create table ttrss_user_prefs (
205 owner_uid
integer not null ,
206 pref_name
varchar ( 250 ),
209 foreign key ( owner_uid
) references ttrss_users ( id ) ON DELETE CASCADE ,
211 foreign key ( pref_name
) references ttrss_prefs ( pref_name
) ON DELETE CASCADE ) TYPE = InnoDB
;