diff options
| author | Andrew Dolgov <[email protected]> | 2021-03-04 08:30:52 +0300 |
|---|---|---|
| committer | Andrew Dolgov <[email protected]> | 2021-03-04 08:30:52 +0300 |
| commit | c0fb0a5ec04c064bb848534185b51c5d1b7bdaaf (patch) | |
| tree | 1023912c25fb14a917d0ff23d21bc6b72c73bef0 /sql | |
| parent | 921569e5da6c3ccbf203506a243cbf499432ec15 (diff) | |
wip for db_migrations for core schema
Diffstat (limited to 'sql')
282 files changed, 3497 insertions, 0 deletions
diff --git a/sql/mysql/migrations/10.sql b/sql/mysql/migrations/10.sql new file mode 100644 index 000000000..e951f482f --- /dev/null +++ b/sql/mysql/migrations/10.sql @@ -0,0 +1,12 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('DIGEST_ENABLE', 1, 'false', 'Enable e-mail digest',1, +'This option enables sending daily digest of new (and unread) headlines on your configured e-mail address'); + +alter table ttrss_feeds add column include_in_digest bool; +update ttrss_feeds set include_in_digest = true; +alter table ttrss_feeds change include_in_digest include_in_digest bool not null; +alter table ttrss_feeds alter column include_in_digest set default true; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('CONFIRM_FEED_CATCHUP', 1, 'true', 'Confirm marking feed as read',3); + +update ttrss_version set schema_version = 10; + diff --git a/sql/mysql/migrations/100.sql b/sql/mysql/migrations/100.sql new file mode 100644 index 000000000..a360dab6c --- /dev/null +++ b/sql/mysql/migrations/100.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_ENABLED_PLUGINS', 2, '', '', 1); + +update ttrss_version set schema_version = 100; + +commit; diff --git a/sql/mysql/migrations/101.sql b/sql/mysql/migrations/101.sql new file mode 100644 index 000000000..47ff26522 --- /dev/null +++ b/sql/mysql/migrations/101.sql @@ -0,0 +1,12 @@ +begin; + +create table ttrss_plugin_storage ( + id integer not null auto_increment primary key, + name varchar(100) not null, + owner_uid integer not null, + content longtext not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +update ttrss_version set schema_version = 101; + +commit; diff --git a/sql/mysql/migrations/102.sql b/sql/mysql/migrations/102.sql new file mode 100644 index 000000000..ccf5ef3b3 --- /dev/null +++ b/sql/mysql/migrations/102.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_REVERSE_HEADLINES', 1, 'false', '', 1); + +update ttrss_version set schema_version = 102; + +commit; diff --git a/sql/mysql/migrations/103.sql b/sql/mysql/migrations/103.sql new file mode 100644 index 000000000..6a93b80b8 --- /dev/null +++ b/sql/mysql/migrations/103.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_entries add column plugin_data longtext; + +update ttrss_version set schema_version = 103; + +commit; diff --git a/sql/mysql/migrations/104.sql b/sql/mysql/migrations/104.sql new file mode 100644 index 000000000..104e4b917 --- /dev/null +++ b/sql/mysql/migrations/104.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set short_desc = 'Hide images in articles' where pref_name = 'STRIP_IMAGES'; + +update ttrss_version set schema_version = 104; + +commit; diff --git a/sql/mysql/migrations/105.sql b/sql/mysql/migrations/105.sql new file mode 100644 index 000000000..a96101784 --- /dev/null +++ b/sql/mysql/migrations/105.sql @@ -0,0 +1,11 @@ +begin; + +alter table ttrss_user_entries add column last_marked datetime; +alter table ttrss_user_entries add column last_published datetime; + +update ttrss_user_entries set last_published = last_read where published = true; +update ttrss_user_entries set last_marked = last_read where marked = true; + +update ttrss_version set schema_version = 105; + +commit; diff --git a/sql/mysql/migrations/106.sql b/sql/mysql/migrations/106.sql new file mode 100644 index 000000000..b819416b9 --- /dev/null +++ b/sql/mysql/migrations/106.sql @@ -0,0 +1,12 @@ +begin; + +update ttrss_prefs set short_desc = 'Do not embed images in articles' where pref_name = 'STRIP_IMAGES'; + +alter table ttrss_feeds add column hide_images bool; +update ttrss_feeds set hide_images = false; +alter table ttrss_feeds change hide_images hide_images bool not null; +alter table ttrss_feeds alter column hide_images set default false; + +update ttrss_version set schema_version = 106; + +commit; diff --git a/sql/mysql/migrations/107.sql b/sql/mysql/migrations/107.sql new file mode 100644 index 000000000..9283d40d9 --- /dev/null +++ b/sql/mysql/migrations/107.sql @@ -0,0 +1,15 @@ +begin; + +alter table ttrss_filters2 add column inverse bool; +update ttrss_filters2 set inverse = false; +alter table ttrss_filters2 change inverse inverse bool not null; +alter table ttrss_filters2 alter column inverse set default false; + +alter table ttrss_filters2_rules add column inverse bool; +update ttrss_filters2_rules set inverse = false; +alter table ttrss_filters2_rules change inverse inverse bool not null; +alter table ttrss_filters2_rules alter column inverse set default false; + +update ttrss_version set schema_version = 107; + +commit; diff --git a/sql/mysql/migrations/108.sql b/sql/mysql/migrations/108.sql new file mode 100644 index 000000000..899be8bb4 --- /dev/null +++ b/sql/mysql/migrations/108.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'false' where pref_name = 'SORT_HEADLINES_BY_FEED_DATE'; + +update ttrss_version set schema_version = 108; + +commit; diff --git a/sql/mysql/migrations/109.sql b/sql/mysql/migrations/109.sql new file mode 100644 index 000000000..77e883c3b --- /dev/null +++ b/sql/mysql/migrations/109.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set short_desc = 'Hide feeds with no unread articles' where pref_name = 'HIDE_READ_FEEDS'; + +update ttrss_version set schema_version = 109; + +commit; diff --git a/sql/mysql/migrations/11.sql b/sql/mysql/migrations/11.sql new file mode 100644 index 000000000..690b978eb --- /dev/null +++ b/sql/mysql/migrations/11.sql @@ -0,0 +1,18 @@ +begin; + +delete FROM ttrss_user_prefs WHERE pref_name = 'DISPLAY_HEADER'; +delete FROM ttrss_user_prefs WHERE pref_name = 'DISPLAY_FOOTER'; +delete FROM ttrss_user_prefs WHERE pref_name = 'ENABLE_SEARCH_TOOLBAR'; +delete FROM ttrss_user_prefs WHERE pref_name = 'USE_COMPACT_STYLESHEET'; + +delete FROM ttrss_prefs WHERE pref_name = 'DISPLAY_HEADER'; +delete FROM ttrss_prefs WHERE pref_name = 'DISPLAY_FOOTER'; +delete FROM ttrss_prefs WHERE pref_name = 'ENABLE_SEARCH_TOOLBAR'; +delete FROM ttrss_prefs WHERE pref_name = 'USE_COMPACT_STYLESHEET'; + +insert into ttrss_themes (theme_name, theme_path) values ('Graycube', 'graycube'); +insert into ttrss_themes (theme_name, theme_path) values ('Default (Compact)', 'compact'); + +update ttrss_version set schema_version = 11; + +commit; diff --git a/sql/mysql/migrations/110.sql b/sql/mysql/migrations/110.sql new file mode 100644 index 000000000..859f7d6dc --- /dev/null +++ b/sql/mysql/migrations/110.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('USER_CSS_THEME', 2, '', 'Select theme', 2, 'Select one of the available CSS themes'); + +update ttrss_version set schema_version = 110; + +commit; diff --git a/sql/mysql/migrations/111.sql b/sql/mysql/migrations/111.sql new file mode 100644 index 000000000..27f287d1a --- /dev/null +++ b/sql/mysql/migrations/111.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'true' where pref_name = 'COMBINED_DISPLAY_MODE'; + +update ttrss_version set schema_version = 111; + +commit; diff --git a/sql/mysql/migrations/112.sql b/sql/mysql/migrations/112.sql new file mode 100644 index 000000000..a6f0b200b --- /dev/null +++ b/sql/mysql/migrations/112.sql @@ -0,0 +1,15 @@ +begin; + +alter table ttrss_filters2 add column order_id integer; +update ttrss_filters2 set order_id = 0; +alter table ttrss_filters2 change order_id order_id integer not null; +alter table ttrss_filters2 alter column order_id set default 0; + +alter table ttrss_filters2 add column title varchar(250); +update ttrss_filters2 set title = ''; +alter table ttrss_filters2 change title title varchar(250) not null; +alter table ttrss_filters2 alter column title set default ''; + +update ttrss_version set schema_version = 112; + +commit; diff --git a/sql/mysql/migrations/113.sql b/sql/mysql/migrations/113.sql new file mode 100644 index 000000000..3c0d00563 --- /dev/null +++ b/sql/mysql/migrations/113.sql @@ -0,0 +1,8 @@ +begin; + +insert into ttrss_filter_actions (id,name,description) values (8, 'stop', + 'Stop / Do nothing'); + +update ttrss_version set schema_version = 113; + +commit; diff --git a/sql/mysql/migrations/114.sql b/sql/mysql/migrations/114.sql new file mode 100644 index 000000000..fd7ce5d2b --- /dev/null +++ b/sql/mysql/migrations/114.sql @@ -0,0 +1,15 @@ +begin; + +alter table ttrss_feeds add column view_settings varchar(250); +update ttrss_feeds set view_settings = ''; +alter table ttrss_feeds change view_settings view_settings varchar(250) not null; +alter table ttrss_feeds alter column view_settings set default ''; + +alter table ttrss_feed_categories add column view_settings varchar(250); +update ttrss_feed_categories set view_settings = ''; +alter table ttrss_feed_categories change view_settings view_settings varchar(250) not null; +alter table ttrss_feed_categories alter column view_settings set default ''; + +update ttrss_version set schema_version = 114; + +commit; diff --git a/sql/mysql/migrations/115.sql b/sql/mysql/migrations/115.sql new file mode 100644 index 000000000..e23f0533a --- /dev/null +++ b/sql/mysql/migrations/115.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_prefs_sections drop column section_name; +alter table ttrss_prefs drop column short_desc; +alter table ttrss_prefs drop column help_text; + +update ttrss_version set schema_version = 115; + +commit; diff --git a/sql/mysql/migrations/116.sql b/sql/mysql/migrations/116.sql new file mode 100644 index 000000000..252ba0e3a --- /dev/null +++ b/sql/mysql/migrations/116.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'false' where pref_name = 'ALLOW_DUPLICATE_POSTS'; + +update ttrss_version set schema_version = 116; + +commit; diff --git a/sql/mysql/migrations/117.sql b/sql/mysql/migrations/117.sql new file mode 100644 index 000000000..da3d056cc --- /dev/null +++ b/sql/mysql/migrations/117.sql @@ -0,0 +1,8 @@ +begin; + +ALTER TABLE ttrss_feeds ADD COLUMN favicon_avg_color VARCHAR(11); +alter table ttrss_feeds alter column favicon_avg_color set default null; + +update ttrss_version set schema_version = 117; + +commit; diff --git a/sql/mysql/migrations/118.sql b/sql/mysql/migrations/118.sql new file mode 100644 index 000000000..add2b0c1f --- /dev/null +++ b/sql/mysql/migrations/118.sql @@ -0,0 +1,16 @@ +begin; + +create table ttrss_error_log( + id integer not null auto_increment primary key, + owner_uid integer, + errno integer not null, + errstr text not null, + filename text not null, + lineno integer not null, + context text not null, + created_at datetime not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +update ttrss_version set schema_version = 118; + +commit; diff --git a/sql/mysql/migrations/119.sql b/sql/mysql/migrations/119.sql new file mode 100644 index 000000000..ddb7d64de --- /dev/null +++ b/sql/mysql/migrations/119.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'Automatic' where pref_name = 'USER_TIMEZONE'; + +update ttrss_version set schema_version = 119; + +commit; diff --git a/sql/mysql/migrations/12.sql b/sql/mysql/migrations/12.sql new file mode 100644 index 000000000..73af4e375 --- /dev/null +++ b/sql/mysql/migrations/12.sql @@ -0,0 +1,12 @@ +alter table ttrss_filters add column action_param varchar(200); + +update ttrss_filters set action_param = ''; + +alter table ttrss_filters change action_param action_param varchar(200) not null; +alter table ttrss_filters alter column action_param set default ''; + +insert into ttrss_filter_actions (id,name,description) values (4, 'tag', + 'Assign tags'); + +update ttrss_version set schema_version = 12; + diff --git a/sql/mysql/migrations/120.sql b/sql/mysql/migrations/120.sql new file mode 100644 index 000000000..34971146e --- /dev/null +++ b/sql/mysql/migrations/120.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_LANGUAGE', 2, '', 2); + +update ttrss_version set schema_version = 120; + +commit; diff --git a/sql/mysql/migrations/121.sql b/sql/mysql/migrations/121.sql new file mode 100644 index 000000000..d9cf51184 --- /dev/null +++ b/sql/mysql/migrations/121.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'false' where pref_name = 'AUTO_ASSIGN_LABELS'; + +update ttrss_version set schema_version = 121; + +commit; diff --git a/sql/mysql/migrations/122.sql b/sql/mysql/migrations/122.sql new file mode 100644 index 000000000..397b97bfc --- /dev/null +++ b/sql/mysql/migrations/122.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_entries add column lang varchar(2); + +update ttrss_version set schema_version = 122; + +commit; diff --git a/sql/mysql/migrations/123.sql b/sql/mysql/migrations/123.sql new file mode 100644 index 000000000..f311d060b --- /dev/null +++ b/sql/mysql/migrations/123.sql @@ -0,0 +1,9 @@ +begin; + +ALTER TABLE ttrss_counters_cache ENGINE=InnoDB DEFAULT CHARSET=UTF8; +ALTER TABLE ttrss_cat_counters_cache ENGINE=InnoDB DEFAULT CHARSET=UTF8; +ALTER TABLE ttrss_feedbrowser_cache ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +update ttrss_version set schema_version = 123; + +commit; diff --git a/sql/mysql/migrations/124.sql b/sql/mysql/migrations/124.sql new file mode 100644 index 000000000..96ae46341 --- /dev/null +++ b/sql/mysql/migrations/124.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_users add column resetpass_token varchar(250); +alter table ttrss_users alter column resetpass_token set default null; + +update ttrss_version set schema_version = 124; + +commit; diff --git a/sql/mysql/migrations/125.sql b/sql/mysql/migrations/125.sql new file mode 100644 index 000000000..9f004d963 --- /dev/null +++ b/sql/mysql/migrations/125.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_users drop column resetpass_token; + +alter table ttrss_users add column resetpass_token varchar(250); +alter table ttrss_users alter column resetpass_token set default null; + +update ttrss_version set schema_version = 125; + +commit; diff --git a/sql/mysql/migrations/126.sql b/sql/mysql/migrations/126.sql new file mode 100644 index 000000000..6ff661e71 --- /dev/null +++ b/sql/mysql/migrations/126.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_enclosures add column width integer not null default 0; +alter table ttrss_enclosures add column height integer not null default 0; + +update ttrss_version set schema_version = 126; + +commit; diff --git a/sql/mysql/migrations/127.sql b/sql/mysql/migrations/127.sql new file mode 100644 index 000000000..2c521d86c --- /dev/null +++ b/sql/mysql/migrations/127.sql @@ -0,0 +1,5 @@ +BEGIN; + +UPDATE ttrss_version SET schema_version = 127; + +COMMIT; diff --git a/sql/mysql/migrations/128.sql b/sql/mysql/migrations/128.sql new file mode 100644 index 000000000..281379bb0 --- /dev/null +++ b/sql/mysql/migrations/128.sql @@ -0,0 +1,13 @@ +BEGIN; + +update ttrss_feeds set last_updated = NULL; +alter table ttrss_feeds modify column last_updated datetime DEFAULT NULL; + +alter table ttrss_feeds add column feed_language varchar(100); +update ttrss_feeds set feed_language = ''; +alter table ttrss_feeds change feed_language feed_language varchar(100) not null; +alter table ttrss_feeds alter column feed_language set default ''; + +UPDATE ttrss_version SET schema_version = 128; + +COMMIT; diff --git a/sql/mysql/migrations/129.sql b/sql/mysql/migrations/129.sql new file mode 100644 index 000000000..2ebec0dc9 --- /dev/null +++ b/sql/mysql/migrations/129.sql @@ -0,0 +1,8 @@ +BEGIN; + +insert into ttrss_filter_actions (id,name,description) values (9, 'plugin', + 'Invoke plugin'); + +UPDATE ttrss_version SET schema_version = 129; + +COMMIT; diff --git a/sql/mysql/migrations/13.sql b/sql/mysql/migrations/13.sql new file mode 100644 index 000000000..ad068c8a2 --- /dev/null +++ b/sql/mysql/migrations/13.sql @@ -0,0 +1,6 @@ +alter table ttrss_filters add column inverse bool; +update ttrss_filters set inverse = false; +alter table ttrss_filters change inverse inverse bool not null; +alter table ttrss_filters alter column inverse set default false; + +update ttrss_version set schema_version = 13; diff --git a/sql/mysql/migrations/130.sql b/sql/mysql/migrations/130.sql new file mode 100644 index 000000000..7b90d4d2c --- /dev/null +++ b/sql/mysql/migrations/130.sql @@ -0,0 +1,7 @@ +BEGIN; + +alter table ttrss_feeds alter column last_updated set default null; + +UPDATE ttrss_version SET schema_version = 130; + +COMMIT; diff --git a/sql/mysql/migrations/131.sql b/sql/mysql/migrations/131.sql new file mode 100644 index 000000000..9316b7f41 --- /dev/null +++ b/sql/mysql/migrations/131.sql @@ -0,0 +1,7 @@ +BEGIN; + +ALTER TABLE ttrss_filters2_rules ADD COLUMN match_on TEXT; + +UPDATE ttrss_version SET schema_version = 131; + +COMMIT; diff --git a/sql/mysql/migrations/132.sql b/sql/mysql/migrations/132.sql new file mode 100644 index 000000000..1f1fb5901 --- /dev/null +++ b/sql/mysql/migrations/132.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_feeds add column last_modified varchar(250); +update ttrss_feeds set last_modified = ''; +alter table ttrss_feeds change last_modified last_modified varchar(250) not null; +alter table ttrss_feeds alter column last_modified set default ''; + +UPDATE ttrss_version SET schema_version = 132; + +commit; diff --git a/sql/mysql/migrations/133.sql b/sql/mysql/migrations/133.sql new file mode 100644 index 000000000..e1693a9cb --- /dev/null +++ b/sql/mysql/migrations/133.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_feeds add column last_unconditional datetime null; + +UPDATE ttrss_version SET schema_version = 133; + +commit; diff --git a/sql/mysql/migrations/134.sql b/sql/mysql/migrations/134.sql new file mode 100644 index 000000000..7bbd7662d --- /dev/null +++ b/sql/mysql/migrations/134.sql @@ -0,0 +1,7 @@ +BEGIN; + +ALTER TABLE ttrss_filters2_rules MODIFY reg_exp text not null; + +UPDATE ttrss_version SET schema_version = 134; + +COMMIT; diff --git a/sql/mysql/migrations/135.sql b/sql/mysql/migrations/135.sql new file mode 100644 index 000000000..6d9d41ba6 --- /dev/null +++ b/sql/mysql/migrations/135.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_filters2 add column last_triggered datetime; +alter table ttrss_filters2 alter column last_triggered set default null; + +update ttrss_version set schema_version = 135; + +commit; diff --git a/sql/mysql/migrations/136.sql b/sql/mysql/migrations/136.sql new file mode 100644 index 000000000..05506a568 --- /dev/null +++ b/sql/mysql/migrations/136.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_archived_feeds add column created datetime; +update ttrss_archived_feeds set created = NOW(); +alter table ttrss_archived_feeds change created created datetime not null; + +update ttrss_version set schema_version = 136; + +commit; diff --git a/sql/mysql/migrations/137.sql b/sql/mysql/migrations/137.sql new file mode 100644 index 000000000..45e4de02f --- /dev/null +++ b/sql/mysql/migrations/137.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_feeds add constraint ttrss_feeds_feed_url_owner_uid_key unique (feed_url(255), owner_uid); + +update ttrss_version set schema_version = 137; + +commit; diff --git a/sql/mysql/migrations/138.sql b/sql/mysql/migrations/138.sql new file mode 100644 index 000000000..6bfc5bcd5 --- /dev/null +++ b/sql/mysql/migrations/138.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DEFAULT_SEARCH_LANGUAGE', 2, '', 2); + +update ttrss_version set schema_version = 138; + +commit; diff --git a/sql/mysql/migrations/139.sql b/sql/mysql/migrations/139.sql new file mode 100644 index 000000000..88dfb69c7 --- /dev/null +++ b/sql/mysql/migrations/139.sql @@ -0,0 +1,13 @@ +begin; + +create table ttrss_app_passwords (id integer not null primary key auto_increment, + title varchar(250) not null, + pwd_hash text not null, + service varchar(100) not null, + created datetime not null, + last_used datetime default null, + owner_uid integer not null references ttrss_users(id) on delete cascade) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +update ttrss_version set schema_version = 139; + +commit; diff --git a/sql/mysql/migrations/14.sql b/sql/mysql/migrations/14.sql new file mode 100644 index 000000000..8e7cc4dd5 --- /dev/null +++ b/sql/mysql/migrations/14.sql @@ -0,0 +1,4 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('CDM_AUTO_CATCHUP', 1, 'false', 'Mark articles as read automatically',2, +'This option enables marking articles as read automatically in combined mode while you scroll article list.'); + +update ttrss_version set schema_version = 14; diff --git a/sql/mysql/migrations/140.sql b/sql/mysql/migrations/140.sql new file mode 100644 index 000000000..91215eff1 --- /dev/null +++ b/sql/mysql/migrations/140.sql @@ -0,0 +1,4 @@ +alter table ttrss_feeds add column last_successful_update datetime; +alter table ttrss_feeds alter column last_successful_update set default null; + +update ttrss_version set schema_version = 140; diff --git a/sql/mysql/migrations/141.sql b/sql/mysql/migrations/141.sql new file mode 100644 index 000000000..03caf23d0 --- /dev/null +++ b/sql/mysql/migrations/141.sql @@ -0,0 +1,9 @@ +create table ttrss_user_prefs2 ( + owner_uid integer not null, + pref_name varchar(250), + profile integer null, + value longtext not null, + foreign key (profile) references ttrss_settings_profiles(id) ON DELETE CASCADE, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +update ttrss_version set schema_version = 141; diff --git a/sql/mysql/migrations/142.sql b/sql/mysql/migrations/142.sql new file mode 100644 index 000000000..7a75f3f52 --- /dev/null +++ b/sql/mysql/migrations/142.sql @@ -0,0 +1,4 @@ +create index ttrss_user_labels2_article_id_idx on ttrss_user_labels2(article_id); +create index ttrss_user_labels2_label_id_idx on ttrss_user_labels2(label_id); + +update ttrss_version set schema_version = 142; diff --git a/sql/mysql/migrations/15.sql b/sql/mysql/migrations/15.sql new file mode 100644 index 000000000..9fa183a09 --- /dev/null +++ b/sql/mysql/migrations/15.sql @@ -0,0 +1,5 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_VIEW_MODE', 2, 'adaptive', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_VIEW_LIMIT', 3, '30', '', 1); + +update ttrss_version set schema_version = 15; diff --git a/sql/mysql/migrations/16.sql b/sql/mysql/migrations/16.sql new file mode 100644 index 000000000..00034df33 --- /dev/null +++ b/sql/mysql/migrations/16.sql @@ -0,0 +1,6 @@ +alter table ttrss_feeds add column auth_pass_encrypted bool; +update ttrss_feeds set auth_pass_encrypted = false; +alter table ttrss_feeds change auth_pass_encrypted auth_pass_encrypted bool not null; +alter table ttrss_feeds alter column auth_pass_encrypted set default false; + +update ttrss_version set schema_version = 16; diff --git a/sql/mysql/migrations/17.sql b/sql/mysql/migrations/17.sql new file mode 100644 index 000000000..f5d39f33d --- /dev/null +++ b/sql/mysql/migrations/17.sql @@ -0,0 +1,13 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_PREFS_ACTIVE_TAB', 2, '', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_INFOBOX_DISABLE_OVERLAY', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('STRIP_UNSAFE_TAGS', 1, 'true', 'Strip unsafe tags from articles', 3, +'Strip all but most common HTML tags when reading articles.'); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('BLACKLISTED_TAGS', 2, 'main, generic, misc', 'Blacklisted tags', 3, +'When auto-detecting tags in articles these tags will not be applied (comma-separated list).'); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_SEARCH_TOOLBAR', 1, 'false', 'Enable search toolbar',2); + +update ttrss_version set schema_version = 17; diff --git a/sql/mysql/migrations/18.sql b/sql/mysql/migrations/18.sql new file mode 100644 index 000000000..e03c96fd0 --- /dev/null +++ b/sql/mysql/migrations/18.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_PREFS_ENABLE_PAGINATION', 2, '', '', 1); + +update ttrss_version set schema_version = 18; diff --git a/sql/mysql/migrations/19.sql b/sql/mysql/migrations/19.sql new file mode 100644 index 000000000..ce727174b --- /dev/null +++ b/sql/mysql/migrations/19.sql @@ -0,0 +1,11 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_PREFS_PUBLISH_KEY', 2, '', '', 1); + +alter table ttrss_user_entries add column published bool; +update ttrss_user_entries set published = false; +alter table ttrss_user_entries change published published bool not null; +alter table ttrss_user_entries alter column published set default false; + +insert into ttrss_filter_actions (id,name,description) values (5, 'publish', + 'Publish article'); + +update ttrss_version set schema_version = 19; diff --git a/sql/mysql/migrations/20.sql b/sql/mysql/migrations/20.sql new file mode 100644 index 000000000..347654512 --- /dev/null +++ b/sql/mysql/migrations/20.sql @@ -0,0 +1,3 @@ +UPDATE ttrss_prefs SET help_text = 'This option is useful when you are reading several planet-type aggregators with partially colliding userbase. When disabled, it forces same posts from different feeds to appear only once.' WHERE pref_name = 'ALLOW_DUPLICATE_POSTS'; + +update ttrss_version set schema_version = 20; diff --git a/sql/mysql/migrations/21.sql b/sql/mysql/migrations/21.sql new file mode 100644 index 000000000..f64d82228 --- /dev/null +++ b/sql/mysql/migrations/21.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('FRESH_ARTICLE_MAX_AGE', 3, '24', 'Maximum age of fresh articles (in hours)',2); + +update ttrss_version set schema_version = 21; diff --git a/sql/mysql/migrations/22.sql b/sql/mysql/migrations/22.sql new file mode 100644 index 000000000..7b4457887 --- /dev/null +++ b/sql/mysql/migrations/22.sql @@ -0,0 +1,8 @@ +create index ttrss_entries_date_entered_index on ttrss_entries(date_entered); + +alter table ttrss_feeds add column cache_images bool; +update ttrss_feeds set cache_images = false; +alter table ttrss_feeds change cache_images cache_images bool not null; +alter table ttrss_feeds alter column cache_images set default false; + +update ttrss_version set schema_version = 22; diff --git a/sql/mysql/migrations/23.sql b/sql/mysql/migrations/23.sql new file mode 100644 index 000000000..fe54e757d --- /dev/null +++ b/sql/mysql/migrations/23.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('DIGEST_CATCHUP', 1, 'false', 'Mark articles in e-mail digest as read',1); + +update ttrss_version set schema_version = 23; diff --git a/sql/mysql/migrations/24.sql b/sql/mysql/migrations/24.sql new file mode 100644 index 000000000..f24e34062 --- /dev/null +++ b/sql/mysql/migrations/24.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('CDM_EXPANDED', 1, 'true', 'Automatically expand articles in combined mode',3); + +update ttrss_version set schema_version = 24; diff --git a/sql/mysql/migrations/25.sql b/sql/mysql/migrations/25.sql new file mode 100644 index 000000000..c3f3d5ea4 --- /dev/null +++ b/sql/mysql/migrations/25.sql @@ -0,0 +1,3 @@ +insert into ttrss_themes (theme_name, theme_path) values ('Three-pane', '3pane'); + +update ttrss_version set schema_version = 25; diff --git a/sql/mysql/migrations/26.sql b/sql/mysql/migrations/26.sql new file mode 100644 index 000000000..14d62b8c0 --- /dev/null +++ b/sql/mysql/migrations/26.sql @@ -0,0 +1,15 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('PURGE_UNREAD_ARTICLES', 1, 'true', 'Purge unread articles',3); + +alter table ttrss_users add column created datetime; +alter table ttrss_users alter column created set default null; + +create table ttrss_enclosures (id serial not null primary key, + content_url text not null, + content_type varchar(250) not null, + post_id integer not null, + title text not null, + duration text not null, + index (post_id), + foreign key (post_id) references ttrss_entries(id) ON DELETE cascade); + +update ttrss_version set schema_version = 26; diff --git a/sql/mysql/migrations/27.sql b/sql/mysql/migrations/27.sql new file mode 100644 index 000000000..fcd6e8509 --- /dev/null +++ b/sql/mysql/migrations/27.sql @@ -0,0 +1,4 @@ +alter table ttrss_feeds add column last_viewed datetime; +alter table ttrss_feeds alter column last_viewed set default null; + +update ttrss_version set schema_version = 27; diff --git a/sql/mysql/migrations/28.sql b/sql/mysql/migrations/28.sql new file mode 100644 index 000000000..ffdf8448e --- /dev/null +++ b/sql/mysql/migrations/28.sql @@ -0,0 +1,3 @@ +UPDATE ttrss_prefs SET def_value = 'main, generic, misc, uncategorized, blog, blogroll, general, news' WHERE pref_name = 'BLACKLISTED_TAGS'; + +update ttrss_version set schema_version = 28; diff --git a/sql/mysql/migrations/29.sql b/sql/mysql/migrations/29.sql new file mode 100644 index 000000000..89b5e94a0 --- /dev/null +++ b/sql/mysql/migrations/29.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('HIDE_READ_SHOWS_SPECIAL', 1, 'true', 'Show special feeds when hiding read feeds',3); + +update ttrss_version set schema_version = 29; diff --git a/sql/mysql/migrations/3.sql b/sql/mysql/migrations/3.sql new file mode 100644 index 000000000..79860a29c --- /dev/null +++ b/sql/mysql/migrations/3.sql @@ -0,0 +1,43 @@ +begin; + +alter table ttrss_entries add column num_comments integer; + +update ttrss_entries set num_comments = 0; + +alter table ttrss_entries change num_comments num_comments integer not null; +alter table ttrss_entries alter column num_comments set default 0; + +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, + 'Display expanded list of feed articles, instead of separate displays for headlines and article content'); + +alter table ttrss_feed_categories add column collapsed bool; + +update ttrss_feed_categories set collapsed = false; + +alter table ttrss_feed_categories change collapsed collapsed bool not null; +alter table ttrss_feed_categories alter column collapsed set default 0; + +alter table ttrss_feeds add column auth_login varchar(250); +alter table ttrss_feeds add column auth_pass varchar(250); + +update ttrss_feeds set auth_login = ''; +update ttrss_feeds set auth_pass = ''; + +alter table ttrss_feeds change auth_login auth_login varchar(250) not null; +alter table ttrss_feeds alter column auth_login set default ''; + +alter table ttrss_feeds change auth_pass auth_pass varchar(250) not null; +alter table ttrss_feeds alter column auth_pass set default ''; + +alter table ttrss_users add column email varchar(250); + +update ttrss_users set email = ''; + +alter table ttrss_users change email email varchar(250) not null; +alter table ttrss_users alter column email set default ''; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_SEARCH_TOOLBAR', 1, 'false', 'Enable search toolbar',2); + +update ttrss_version set schema_version = 3; + +commit; diff --git a/sql/mysql/migrations/30.sql b/sql/mysql/migrations/30.sql new file mode 100644 index 000000000..0cff2018a --- /dev/null +++ b/sql/mysql/migrations/30.sql @@ -0,0 +1,4 @@ +alter table ttrss_feeds add column last_update_started datetime; +alter table ttrss_feeds alter column last_update_started set default null; + +update ttrss_version set schema_version = 30; diff --git a/sql/mysql/migrations/31.sql b/sql/mysql/migrations/31.sql new file mode 100644 index 000000000..94cfc14d8 --- /dev/null +++ b/sql/mysql/migrations/31.sql @@ -0,0 +1,6 @@ +alter table ttrss_feeds add column update_method integer; +update ttrss_feeds set update_method = 0; +alter table ttrss_feeds change update_method update_method integer not null; +alter table ttrss_feeds alter column update_method set default 0; + +update ttrss_version set schema_version = 31; diff --git a/sql/mysql/migrations/32.sql b/sql/mysql/migrations/32.sql new file mode 100644 index 000000000..689e5072c --- /dev/null +++ b/sql/mysql/migrations/32.sql @@ -0,0 +1,6 @@ +alter table ttrss_prefs add column access_level integer; +update ttrss_prefs set access_level = 0; +alter table ttrss_prefs change access_level access_level integer not null; +alter table ttrss_prefs alter column access_level set default 0; + +update ttrss_version set schema_version = 32; diff --git a/sql/mysql/migrations/33.sql b/sql/mysql/migrations/33.sql new file mode 100644 index 000000000..8317dc2de --- /dev/null +++ b/sql/mysql/migrations/33.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('HIDE_FEEDLIST', 1, 'false', 'Hide feedlist',2, 'This option hides feedlist and allows it to be toggled on the fly, useful for small screens.'); + +update ttrss_version set schema_version = 33; diff --git a/sql/mysql/migrations/34.sql b/sql/mysql/migrations/34.sql new file mode 100644 index 000000000..e591a7a96 --- /dev/null +++ b/sql/mysql/migrations/34.sql @@ -0,0 +1,3 @@ +alter table ttrss_feeds change feed_url feed_url text not null; + +update ttrss_version set schema_version = 34; diff --git a/sql/mysql/migrations/35.sql b/sql/mysql/migrations/35.sql new file mode 100644 index 000000000..ad112a021 --- /dev/null +++ b/sql/mysql/migrations/35.sql @@ -0,0 +1 @@ +update ttrss_version set schema_version = 35; diff --git a/sql/mysql/migrations/36.sql b/sql/mysql/migrations/36.sql new file mode 100644 index 000000000..d92da2880 --- /dev/null +++ b/sql/mysql/migrations/36.sql @@ -0,0 +1,9 @@ +alter table ttrss_user_entries add column score integer; +update ttrss_user_entries set score = 0; +alter table ttrss_user_entries change score score integer not null; +alter table ttrss_user_entries alter column score set default 0; + +insert into ttrss_filter_actions (id,name,description) values (6, 'score', + 'Modify score'); + +update ttrss_version set schema_version = 36; diff --git a/sql/mysql/migrations/37.sql b/sql/mysql/migrations/37.sql new file mode 100644 index 000000000..4e764d7ee --- /dev/null +++ b/sql/mysql/migrations/37.sql @@ -0,0 +1,4 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('VFEED_GROUP_BY_FEED', 1, 'false', 'Group headlines in virtual feeds',2, + 'When this option is enabled, headlines in Special feeds and Labels are grouped by feeds'); + +update ttrss_version set schema_version = 37; diff --git a/sql/mysql/migrations/38.sql b/sql/mysql/migrations/38.sql new file mode 100644 index 000000000..df544d03b --- /dev/null +++ b/sql/mysql/migrations/38.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('SYNC_COUNTERS', 1, 'false', 'Prefer more accurate feedlist counters to UI speed',3); + +update ttrss_version set schema_version = 38; diff --git a/sql/mysql/migrations/39.sql b/sql/mysql/migrations/39.sql new file mode 100644 index 000000000..f27db09d6 --- /dev/null +++ b/sql/mysql/migrations/39.sql @@ -0,0 +1,5 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('ENABLE_FLASH_PLAYER', 1, 'true', 'Enable inline MP3 player', 3, 'Enable the Flash-based XSPF Player to play MP3-format podcast enclosures.'); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('STRIP_IMAGES', 1, 'false', 'Do not show images in articles', 2); + +update ttrss_version set schema_version = 39; diff --git a/sql/mysql/migrations/4.sql b/sql/mysql/migrations/4.sql new file mode 100644 index 000000000..6dfc5f82f --- /dev/null +++ b/sql/mysql/migrations/4.sql @@ -0,0 +1,17 @@ +begin; + +alter table ttrss_feeds add column parent_feed integer; +alter table ttrss_feeds add foreign key (parent_feed) references ttrss_feeds(id) on delete set null; + +alter table ttrss_feeds add column private bool; + +update ttrss_feeds set private = false; + +alter table ttrss_feeds change private private bool not null; +alter table ttrss_feeds alter column private set default 0; + +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); + +update ttrss_version set schema_version = 4; + +commit; diff --git a/sql/mysql/migrations/40.sql b/sql/mysql/migrations/40.sql new file mode 100644 index 000000000..5a506cea6 --- /dev/null +++ b/sql/mysql/migrations/40.sql @@ -0,0 +1,3 @@ +update ttrss_prefs set short_desc = 'Enable feed icons' where pref_name = 'ENABLE_FEED_ICONS'; + +update ttrss_version set schema_version = 40; diff --git a/sql/mysql/migrations/41.sql b/sql/mysql/migrations/41.sql new file mode 100644 index 000000000..bf5887336 --- /dev/null +++ b/sql/mysql/migrations/41.sql @@ -0,0 +1,6 @@ +alter table ttrss_feed_categories add column order_id integer; +update ttrss_feed_categories set order_id = 0; +alter table ttrss_feed_categories change order_id order_id integer not null; +alter table ttrss_feed_categories alter column order_id set default 0; + +update ttrss_version set schema_version = 41; diff --git a/sql/mysql/migrations/42.sql b/sql/mysql/migrations/42.sql new file mode 100644 index 000000000..3ae211d07 --- /dev/null +++ b/sql/mysql/migrations/42.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_VIEW_ORDER_BY', 2, 'default', '', 1); + +update ttrss_version set schema_version = 42; diff --git a/sql/mysql/migrations/43.sql b/sql/mysql/migrations/43.sql new file mode 100644 index 000000000..f1344b203 --- /dev/null +++ b/sql/mysql/migrations/43.sql @@ -0,0 +1,3 @@ +alter table ttrss_labels change sql_exp sql_exp text not null; + +update ttrss_version set schema_version = 43; diff --git a/sql/mysql/migrations/44.sql b/sql/mysql/migrations/44.sql new file mode 100644 index 000000000..c17bcc38f --- /dev/null +++ b/sql/mysql/migrations/44.sql @@ -0,0 +1,9 @@ +create table ttrss_counters_cache ( + feed_id integer not null, + owner_uid integer not null, + value integer not null default 0, + foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +); + +update ttrss_version set schema_version = 44; diff --git a/sql/mysql/migrations/45.sql b/sql/mysql/migrations/45.sql new file mode 100644 index 000000000..c4b91cf48 --- /dev/null +++ b/sql/mysql/migrations/45.sql @@ -0,0 +1,3 @@ +update ttrss_prefs set help_text = 'This option enables marking articles as read automatically in combined mode (except for Fresh articles feed) while you scroll article list.' where pref_name = 'CDM_AUTO_CATCHUP'; + +update ttrss_version set schema_version = 45; diff --git a/sql/mysql/migrations/46.sql b/sql/mysql/migrations/46.sql new file mode 100644 index 000000000..8bf25629a --- /dev/null +++ b/sql/mysql/migrations/46.sql @@ -0,0 +1,4 @@ +insert into ttrss_filter_types (id,name,description) values (5, 'date', + 'Article Date'); + +update ttrss_version set schema_version = 46; diff --git a/sql/mysql/migrations/47.sql b/sql/mysql/migrations/47.sql new file mode 100644 index 000000000..a3afd94c5 --- /dev/null +++ b/sql/mysql/migrations/47.sql @@ -0,0 +1,9 @@ +alter table ttrss_filters add column filter_param varchar(200); + +update ttrss_filters set filter_param = ''; + +alter table ttrss_filters change filter_param filter_param varchar(200) not null; +alter table ttrss_filters alter column filter_param set default ''; + +update ttrss_version set schema_version = 47; + diff --git a/sql/mysql/migrations/48.sql b/sql/mysql/migrations/48.sql new file mode 100644 index 000000000..8c42f6e27 --- /dev/null +++ b/sql/mysql/migrations/48.sql @@ -0,0 +1,6 @@ +create table ttrss_feedbrowser_cache ( + feed_url text not null, + subscribers integer not null); + +update ttrss_version set schema_version = 48; + diff --git a/sql/mysql/migrations/49.sql b/sql/mysql/migrations/49.sql new file mode 100644 index 000000000..812f3e65b --- /dev/null +++ b/sql/mysql/migrations/49.sql @@ -0,0 +1,5 @@ +alter table ttrss_counters_cache add column updated datetime; +update ttrss_counters_cache set updated = NOW(); +alter table ttrss_counters_cache change updated updated datetime not null; + +update ttrss_version set schema_version = 49; diff --git a/sql/mysql/migrations/5.sql b/sql/mysql/migrations/5.sql new file mode 100644 index 000000000..1d8b8537b --- /dev/null +++ b/sql/mysql/migrations/5.sql @@ -0,0 +1,12 @@ +begin; + +create table ttrss_scheduled_updates (id integer not null primary key auto_increment, + owner_uid integer not null, + feed_id integer default null, + entered datetime not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE, + foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE) ENGINE=InnoDB; + +update ttrss_version set schema_version = 5; + +commit; diff --git a/sql/mysql/migrations/50.sql b/sql/mysql/migrations/50.sql new file mode 100644 index 000000000..6f49742f4 --- /dev/null +++ b/sql/mysql/migrations/50.sql @@ -0,0 +1,19 @@ +drop table if exists ttrss_counters_cache; + +create table ttrss_counters_cache ( + feed_id integer not null, + owner_uid integer not null, + value integer not null default 0, + updated datetime not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +); + +create table ttrss_cat_counters_cache ( + feed_id integer not null, + owner_uid integer not null, + value integer not null default 0, + updated datetime not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +); + +update ttrss_version set schema_version = 50; diff --git a/sql/mysql/migrations/51.sql b/sql/mysql/migrations/51.sql new file mode 100644 index 000000000..e8fe4c7b8 --- /dev/null +++ b/sql/mysql/migrations/51.sql @@ -0,0 +1,16 @@ +create table ttrss_labels2 (id integer not null primary key auto_increment, + owner_uid integer not null, + caption varchar(250) not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +) ENGINE=InnoDB; + +create table ttrss_user_labels2 (label_id integer not null, + article_id integer not null, + foreign key (label_id) references ttrss_labels2(id) ON DELETE CASCADE, + foreign key (article_id) references ttrss_entries(id) ON DELETE CASCADE +) ENGINE=InnoDB; + +insert into ttrss_filter_actions (id,name,description) values (7, 'label', + 'Assign label'); + +update ttrss_version set schema_version = 51; diff --git a/sql/mysql/migrations/52.sql b/sql/mysql/migrations/52.sql new file mode 100644 index 000000000..4d9b64eb1 --- /dev/null +++ b/sql/mysql/migrations/52.sql @@ -0,0 +1,5 @@ +alter table ttrss_feedbrowser_cache add column title text; +update ttrss_feedbrowser_cache set title = ''; +alter table ttrss_feedbrowser_cache change title title text not null; + +update ttrss_version set schema_version = 52; diff --git a/sql/mysql/migrations/53.sql b/sql/mysql/migrations/53.sql new file mode 100644 index 000000000..3db0cbd81 --- /dev/null +++ b/sql/mysql/migrations/53.sql @@ -0,0 +1,11 @@ +alter table ttrss_labels2 add column fg_color varchar(15); +update ttrss_labels2 set fg_color = ''; +alter table ttrss_labels2 change fg_color fg_color varchar(15) not null; +alter table ttrss_labels2 alter column fg_color set default ''; + +alter table ttrss_labels2 add column bg_color varchar(15); +update ttrss_labels2 set bg_color = ''; +alter table ttrss_labels2 change bg_color bg_color varchar(15) not null; +alter table ttrss_labels2 alter column bg_color set default ''; + +update ttrss_version set schema_version = 53; diff --git a/sql/mysql/migrations/54.sql b/sql/mysql/migrations/54.sql new file mode 100644 index 000000000..28d3f8ee4 --- /dev/null +++ b/sql/mysql/migrations/54.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('ENABLE_OFFLINE_READING', 1, 'false', 'Enable offline reading',1, 'Synchronize new articles for offline reading using Google Gears.'); + +update ttrss_version set schema_version = 54; + +commit; diff --git a/sql/mysql/migrations/55.sql b/sql/mysql/migrations/55.sql new file mode 100644 index 000000000..75f55b3c3 --- /dev/null +++ b/sql/mysql/migrations/55.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_user_entries add column note text; + +update ttrss_version set schema_version = 55; + +commit; diff --git a/sql/mysql/migrations/56.sql b/sql/mysql/migrations/56.sql new file mode 100644 index 000000000..11cd3069c --- /dev/null +++ b/sql/mysql/migrations/56.sql @@ -0,0 +1,16 @@ +begin; + +drop table ttrss_enclosures; + +create table ttrss_enclosures (id serial not null primary key, + content_url text not null, + content_type varchar(250) not null, + post_id integer not null, + title text not null, + duration text not null, + index (post_id), + foreign key (post_id) references ttrss_entries(id) ON DELETE cascade) ENGINE=InnoDB; + +update ttrss_version set schema_version = 56; + +commit; diff --git a/sql/mysql/migrations/57.sql b/sql/mysql/migrations/57.sql new file mode 100644 index 000000000..27a66d5b7 --- /dev/null +++ b/sql/mysql/migrations/57.sql @@ -0,0 +1,6 @@ +alter table ttrss_feeds add column always_display_enclosures boolean; +update ttrss_feeds set always_display_enclosures = false; +alter table ttrss_feeds change always_display_enclosures always_display_enclosures boolean not null; +alter table ttrss_feeds alter column always_display_enclosures set default false; + +update ttrss_version set schema_version = 57; diff --git a/sql/mysql/migrations/58.sql b/sql/mysql/migrations/58.sql new file mode 100644 index 000000000..61173c1d5 --- /dev/null +++ b/sql/mysql/migrations/58.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_API_ACCESS', 1, 'false', 'Enable external API', 3); + +update ttrss_version set schema_version = 58; + +commit; diff --git a/sql/mysql/migrations/59.sql b/sql/mysql/migrations/59.sql new file mode 100644 index 000000000..d51fe8f65 --- /dev/null +++ b/sql/mysql/migrations/59.sql @@ -0,0 +1,13 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_SPECIAL', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_LABELS', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_FEEDLIST', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_UNCAT', 1, 'false', '', 1); + +update ttrss_version set schema_version = 59; + +commit; diff --git a/sql/mysql/migrations/6.sql b/sql/mysql/migrations/6.sql new file mode 100644 index 000000000..b7ee97ccc --- /dev/null +++ b/sql/mysql/migrations/6.sql @@ -0,0 +1,20 @@ +alter table ttrss_entries add column author varchar(250); + +update ttrss_entries set author = ''; + +alter table ttrss_entries change author author varchar(250) not null; +alter table ttrss_entries alter column author set default ''; + +create table ttrss_sessions (id varchar(250) not null primary key, + data text, + expire integer not null, + ip_address varchar(15) not null default '', + index (id), + index (expire)) ENGINE=InnoDB; + +delete from ttrss_prefs where pref_name = 'ENABLE_SPLASH'; + +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); + +update ttrss_version set schema_version = 6; + diff --git a/sql/mysql/migrations/60.sql b/sql/mysql/migrations/60.sql new file mode 100644 index 000000000..6b33e6c87 --- /dev/null +++ b/sql/mysql/migrations/60.sql @@ -0,0 +1,20 @@ +begin; + +alter table ttrss_user_entries change feed_id feed_id integer null; + +create table ttrss_archived_feeds (id integer not null primary key, + owner_uid integer not null, + title varchar(200) not null, + feed_url text not null, + site_url varchar(250) not null default '', + index(owner_uid), + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB; + +alter table ttrss_user_entries add column orig_feed_id integer; +update ttrss_user_entries set orig_feed_id = NULL; + +alter table ttrss_user_entries add FOREIGN KEY (orig_feed_id) REFERENCES ttrss_archived_feeds(id) ON DELETE SET NULL; + +update ttrss_version set schema_version = 60; + +commit; diff --git a/sql/mysql/migrations/61.sql b/sql/mysql/migrations/61.sql new file mode 100644 index 000000000..209a7ba55 --- /dev/null +++ b/sql/mysql/migrations/61.sql @@ -0,0 +1,9 @@ +begin; + +update ttrss_prefs set short_desc = 'Amount of articles to display at once', help_text = '', def_value = '30' where pref_name = 'DEFAULT_ARTICLE_LIMIT'; + +update ttrss_user_prefs set value = '30' where pref_name = 'DEFAULT_ARTICLE_LIMIT' and value = '0'; + +update ttrss_version set schema_version = 61; + +commit; diff --git a/sql/mysql/migrations/62.sql b/sql/mysql/migrations/62.sql new file mode 100644 index 000000000..4cc904b0a --- /dev/null +++ b/sql/mysql/migrations/62.sql @@ -0,0 +1,13 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_ENABLE_CATS', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_SHOW_IMAGES', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_HIDE_READ', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_SORT_FEEDS_UNREAD', 1, 'false', '', 1); + +update ttrss_version set schema_version = 62; + +commit; diff --git a/sql/mysql/migrations/63.sql b/sql/mysql/migrations/63.sql new file mode 100644 index 000000000..26b7126e7 --- /dev/null +++ b/sql/mysql/migrations/63.sql @@ -0,0 +1,18 @@ +begin; + +create table ttrss_settings_profiles(id integer primary key auto_increment, + title varchar(250) not null, + owner_uid integer not null, + index (owner_uid), + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB; + +alter table ttrss_user_prefs add column profile integer; +update ttrss_user_prefs set profile = NULL; + +alter table ttrss_user_prefs add FOREIGN KEY (profile) REFERENCES ttrss_settings_profiles(id) ON DELETE CASCADE; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_THEME_ID', 3, '0', '', 1); + +update ttrss_version set schema_version = 63; + +commit; diff --git a/sql/mysql/migrations/64.sql b/sql/mysql/migrations/64.sql new file mode 100644 index 000000000..51f0be7e2 --- /dev/null +++ b/sql/mysql/migrations/64.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set type_id = 2 where pref_name = '_THEME_ID'; + +update ttrss_version set schema_version = 64; + +commit; diff --git a/sql/mysql/migrations/65.sql b/sql/mysql/migrations/65.sql new file mode 100644 index 000000000..ce21de898 --- /dev/null +++ b/sql/mysql/migrations/65.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set short_desc = 'Default feed update interval' where pref_name = 'DEFAULT_UPDATE_INTERVAL'; + +update ttrss_version set schema_version = 65; + +commit; diff --git a/sql/mysql/migrations/66.sql b/sql/mysql/migrations/66.sql new file mode 100644 index 000000000..f90c0a157 --- /dev/null +++ b/sql/mysql/migrations/66.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_filter_types (id, name, description) values (6, 'author', 'Author'); + +update ttrss_version set schema_version = 66; + +commit; diff --git a/sql/mysql/migrations/67.sql b/sql/mysql/migrations/67.sql new file mode 100644 index 000000000..b501bf4c3 --- /dev/null +++ b/sql/mysql/migrations/67.sql @@ -0,0 +1,5 @@ +alter table ttrss_entries add column date_updated datetime; +update ttrss_entries set date_updated = date_entered; +alter table ttrss_entries change date_updated date_updated datetime not null; + +update ttrss_version set schema_version = 67; diff --git a/sql/mysql/migrations/68.sql b/sql/mysql/migrations/68.sql new file mode 100644 index 000000000..6d56df0f9 --- /dev/null +++ b/sql/mysql/migrations/68.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('USER_TIMEZONE', 2, 'UTC', 'User timezone', 1); + +update ttrss_version set schema_version = 68; + +commit; diff --git a/sql/mysql/migrations/69.sql b/sql/mysql/migrations/69.sql new file mode 100644 index 000000000..3e42fd3fb --- /dev/null +++ b/sql/mysql/migrations/69.sql @@ -0,0 +1,12 @@ +begin; + +create table ttrss_access_keys (id serial not null primary key, + access_key varchar(250) not null, + feed_id varchar(250) not null, + is_cat bool not null default false, + owner_uid integer not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +update ttrss_version set schema_version = 69; + +commit; diff --git a/sql/mysql/migrations/7.sql b/sql/mysql/migrations/7.sql new file mode 100644 index 000000000..bee135989 --- /dev/null +++ b/sql/mysql/migrations/7.sql @@ -0,0 +1,19 @@ +alter table ttrss_feeds add column rtl_content bool; + +update ttrss_feeds set rtl_content = false; + +alter table ttrss_feeds change rtl_content rtl_content bool not null; +alter table ttrss_feeds alter column rtl_content set default false; + +delete from ttrss_user_prefs where pref_name = 'DISPLAY_FEEDLIST_ACTIONS'; +delete from ttrss_prefs where pref_name = 'DISPLAY_FEEDLIST_ACTIONS'; + +delete from ttrss_user_prefs where pref_name = 'ENABLE_PREFS_CATCHUP_UNCATCHUP'; +delete from ttrss_prefs where pref_name = 'ENABLE_PREFS_CATCHUP_UNCATCHUP'; + +alter table ttrss_sessions drop column ip_address; + +alter table ttrss_filters drop column description; + +update ttrss_version set schema_version = 7; + diff --git a/sql/mysql/migrations/70.sql b/sql/mysql/migrations/70.sql new file mode 100644 index 000000000..0c64336d4 --- /dev/null +++ b/sql/mysql/migrations/70.sql @@ -0,0 +1,6 @@ +alter table ttrss_users add column full_name varchar(250); +update ttrss_users set full_name = ''; +alter table ttrss_users change full_name full_name varchar(250) not null; +alter table ttrss_users alter column full_name set default ''; + +update ttrss_version set schema_version = 70; diff --git a/sql/mysql/migrations/71.sql b/sql/mysql/migrations/71.sql new file mode 100644 index 000000000..2061ada2b --- /dev/null +++ b/sql/mysql/migrations/71.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_filter_types (id,name,description) values (7, 'tag', 'Article Tags'); + +update ttrss_version set schema_version = 71; + +commit; diff --git a/sql/mysql/migrations/72.sql b/sql/mysql/migrations/72.sql new file mode 100644 index 000000000..2995afda3 --- /dev/null +++ b/sql/mysql/migrations/72.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_user_entries add column tag_cache text; +update ttrss_user_entries set tag_cache = ''; +alter table ttrss_user_entries change tag_cache tag_cache text not null; + +update ttrss_version set schema_version = 72; + +commit; diff --git a/sql/mysql/migrations/73.sql b/sql/mysql/migrations/73.sql new file mode 100644 index 000000000..051578d22 --- /dev/null +++ b/sql/mysql/migrations/73.sql @@ -0,0 +1,8 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('SORT_HEADLINES_BY_FEED_DATE', 1, 'true', 'Sort headlines by feed date',3, + 'Use feed-specified date to sort headlines instead of local import date.'); + +update ttrss_version set schema_version = 73; + +commit; diff --git a/sql/mysql/migrations/74.sql b/sql/mysql/migrations/74.sql new file mode 100644 index 000000000..d13856ea9 --- /dev/null +++ b/sql/mysql/migrations/74.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_user_entries add column label_cache text; +update ttrss_user_entries set label_cache = ''; +alter table ttrss_user_entries change label_cache label_cache text not null; + +update ttrss_version set schema_version = 74; + +commit; diff --git a/sql/mysql/migrations/75.sql b/sql/mysql/migrations/75.sql new file mode 100644 index 000000000..c9017b9e9 --- /dev/null +++ b/sql/mysql/migrations/75.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_feeds add column order_id integer; +update ttrss_feeds set order_id = 0; +alter table ttrss_feeds change order_id order_id integer not null; +alter table ttrss_feeds alter column order_id set default 0; + +update ttrss_version set schema_version = 75; + +commit; diff --git a/sql/mysql/migrations/76.sql b/sql/mysql/migrations/76.sql new file mode 100644 index 000000000..95c352b45 --- /dev/null +++ b/sql/mysql/migrations/76.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_users add column twitter_oauth longtext; +alter table ttrss_users alter column twitter_oauth set default null; + +update ttrss_version set schema_version = 76; + +commit; diff --git a/sql/mysql/migrations/77.sql b/sql/mysql/migrations/77.sql new file mode 100644 index 000000000..944f31d84 --- /dev/null +++ b/sql/mysql/migrations/77.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('USER_STYLESHEET', 2, '', 'Customize stylesheet', 2, 'Customize CSS stylesheet to your liking'); + +update ttrss_version set schema_version = 77; + +commit; diff --git a/sql/mysql/migrations/78.sql b/sql/mysql/migrations/78.sql new file mode 100644 index 000000000..80e9e782a --- /dev/null +++ b/sql/mysql/migrations/78.sql @@ -0,0 +1,15 @@ +begin; + +delete from ttrss_user_prefs where pref_name in ('HIDE_FEEDLIST', 'SYNC_COUNTERS', 'ENABLE_LABELS', + 'ENABLE_SEARCH_TOOLBAR', 'ENABLE_FEED_ICONS', + 'ENABLE_OFFLINE_READING', 'EXTENDED_FEEDLIST', 'OPEN_LINKS_IN_NEW_WINDOW', + 'USER_STYLESHEET_URL', 'ENABLE_FLASH_PLAYER'); + +delete from ttrss_prefs where pref_name in ('HIDE_FEEDLIST', 'SYNC_COUNTERS', 'ENABLE_LABELS', + 'ENABLE_SEARCH_TOOLBAR', 'ENABLE_FEED_ICONS', + 'ENABLE_OFFLINE_READING', 'EXTENDED_FEEDLIST', 'OPEN_LINKS_IN_NEW_WINDOW', + 'USER_STYLESHEET_URL', 'ENABLE_FLASH_PLAYER'); + +update ttrss_version set schema_version = 78; + +commit; diff --git a/sql/mysql/migrations/79.sql b/sql/mysql/migrations/79.sql new file mode 100644 index 000000000..7e8c31d49 --- /dev/null +++ b/sql/mysql/migrations/79.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_BROWSE_CATS', 1, 'true', '', 1); + +update ttrss_version set schema_version = 79; + +commit; diff --git a/sql/mysql/migrations/8.sql b/sql/mysql/migrations/8.sql new file mode 100644 index 000000000..39100450b --- /dev/null +++ b/sql/mysql/migrations/8.sql @@ -0,0 +1,14 @@ +insert into ttrss_themes (theme_name, theme_path) values ('Old-skool', 'compat'); + +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, + 'When "Mark as read" button is clicked in toolbar, automatically open next feed with unread articles.'); + +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); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('EXTENDED_FEEDLIST', 1, 'false', 'Show additional information in feedlist',2); + +insert into ttrss_filter_actions (id,name,description) values (3, 'mark', + 'Set starred'); + +update ttrss_version set schema_version = 8; + diff --git a/sql/mysql/migrations/80.sql b/sql/mysql/migrations/80.sql new file mode 100644 index 000000000..df2295c42 --- /dev/null +++ b/sql/mysql/migrations/80.sql @@ -0,0 +1,9 @@ +begin; + +update ttrss_prefs set section_id = 3, + short_desc = 'Automatically mark articles as read', + help_text = 'Mark articles as read automatically while you scroll article list.' where pref_name = 'CDM_AUTO_CATCHUP'; + +update ttrss_version set schema_version = 80; + +commit; diff --git a/sql/mysql/migrations/81.sql b/sql/mysql/migrations/81.sql new file mode 100644 index 000000000..800ceede0 --- /dev/null +++ b/sql/mysql/migrations/81.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_filter_actions set description = 'Delete article' where name = 'filter'; + +update ttrss_version set schema_version = 81; + +commit; diff --git a/sql/mysql/migrations/82.sql b/sql/mysql/migrations/82.sql new file mode 100644 index 000000000..80ca24e72 --- /dev/null +++ b/sql/mysql/migrations/82.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('SSL_CERT_SERIAL', 2, '', 'Login with an SSL certificate',3, 'Click to register your SSL client certificate with tt-rss'); + +update ttrss_version set schema_version = 82; + +commit; diff --git a/sql/mysql/migrations/83.sql b/sql/mysql/migrations/83.sql new file mode 100644 index 000000000..e6f9a2a7f --- /dev/null +++ b/sql/mysql/migrations/83.sql @@ -0,0 +1,34 @@ +begin; + +alter table ttrss_feeds add column mark_unread_on_update boolean; +update ttrss_feeds set mark_unread_on_update = false; +alter table ttrss_feeds change mark_unread_on_update mark_unread_on_update boolean not null; +alter table ttrss_feeds alter column mark_unread_on_update set default false; + +alter table ttrss_feeds add column strip_images boolean; +update ttrss_feeds set strip_images = false; +alter table ttrss_feeds change strip_images strip_images boolean not null; +alter table ttrss_feeds alter column strip_images set default false; + +alter table ttrss_feeds add column update_on_checksum_change boolean; +update ttrss_feeds set update_on_checksum_change = false; +alter table ttrss_feeds change update_on_checksum_change update_on_checksum_change boolean not null; +alter table ttrss_feeds alter column update_on_checksum_change set default false; + +DELETE FROM ttrss_user_prefs WHERE pref_name IN ('HIDE_FEEDLIST', 'SYNC_COUNTERS', 'ENABLE_LABELS', 'ENABLE_SEARCH_TOOLBAR', 'ENABLE_FEED_ICONS', 'ENABLE_OFFLINE_READING', 'EXTENDED_FEEDLIST', 'OPEN_LINKS_IN_NEW_WINDOW', 'ENABLE_FLASH_PLAYER', 'HEADLINES_SMART_DATE', 'MARK_UNREAD_ON_UPDATE', 'UPDATE_POST_ON_CHECKSUM_CHANGE'); + +DELETE FROM ttrss_prefs WHERE pref_name IN ('HIDE_FEEDLIST', 'SYNC_COUNTERS', 'ENABLE_LABELS', 'ENABLE_SEARCH_TOOLBAR', 'ENABLE_FEED_ICONS', 'ENABLE_OFFLINE_READING', 'EXTENDED_FEEDLIST', 'OPEN_LINKS_IN_NEW_WINDOW', 'ENABLE_FLASH_PLAYER', 'HEADLINES_SMART_DATE', 'MARK_UNREAD_ON_UPDATE', 'UPDATE_POST_ON_CHECKSUM_CHANGE'); + +alter table ttrss_feeds add column pubsub_state integer; +update ttrss_feeds set pubsub_state = 0; +alter table ttrss_feeds change pubsub_state pubsub_state integer not null; +alter table ttrss_feeds alter column pubsub_state set default 0; + +alter table ttrss_users drop foreign key ttrss_users_ibfk_1; +alter table ttrss_users drop column theme_id; + +drop table ttrss_themes; + +update ttrss_version set schema_version = 83; + +commit; diff --git a/sql/mysql/migrations/84.sql b/sql/mysql/migrations/84.sql new file mode 100644 index 000000000..cc28b7574 --- /dev/null +++ b/sql/mysql/migrations/84.sql @@ -0,0 +1,23 @@ +begin; + +create table ttrss_linked_instances (id integer not null primary key auto_increment, + last_connected timestamp not null, + last_status_in integer not null, + last_status_out integer not null, + access_key varchar(250) not null unique, + access_url text not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_linked_feeds ( + feed_url text not null, + title text not null, + created datetime not null, + updated datetime not null, + instance_id integer not null, + subscribers integer not null, + foreign key (instance_id) references ttrss_linked_instances(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +drop table ttrss_scheduled_updates; + +update ttrss_version set schema_version = 84; + +commit; diff --git a/sql/mysql/migrations/85.sql b/sql/mysql/migrations/85.sql new file mode 100644 index 000000000..70cab0297 --- /dev/null +++ b/sql/mysql/migrations/85.sql @@ -0,0 +1,13 @@ +begin; + +alter table ttrss_feedbrowser_cache add column site_url text; +update ttrss_feedbrowser_cache set site_url = ''; +alter table ttrss_feedbrowser_cache change site_url site_url text not null; + +alter table ttrss_linked_feeds add column site_url text; +update ttrss_linked_feeds set site_url = ''; +alter table ttrss_linked_feeds change site_url site_url text not null; + +update ttrss_version set schema_version = 85; + +commit; diff --git a/sql/mysql/migrations/86.sql b/sql/mysql/migrations/86.sql new file mode 100644 index 000000000..c8979b2af --- /dev/null +++ b/sql/mysql/migrations/86.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_user_entries add column uuid varchar(200); +update ttrss_user_entries set uuid = ''; +alter table ttrss_user_entries change uuid uuid varchar(200) not null; + +update ttrss_version set schema_version = 86; + +commit; diff --git a/sql/mysql/migrations/87.sql b/sql/mysql/migrations/87.sql new file mode 100644 index 000000000..7ef4630d5 --- /dev/null +++ b/sql/mysql/migrations/87.sql @@ -0,0 +1,14 @@ +begin; + +alter table ttrss_filters add column cat_filter bool; +update ttrss_filters set cat_filter = false; +alter table ttrss_filters change cat_filter cat_filter bool not null; +alter table ttrss_filters alter column cat_filter set default false; + +alter table ttrss_filters add column cat_id integer; + +alter table ttrss_filters add FOREIGN KEY (cat_id) REFERENCES ttrss_feed_categories(id) ON DELETE CASCADE; + +update ttrss_version set schema_version = 87; + +commit; diff --git a/sql/mysql/migrations/88.sql b/sql/mysql/migrations/88.sql new file mode 100644 index 000000000..0abb2ab33 --- /dev/null +++ b/sql/mysql/migrations/88.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_users add column salt varchar(250); +update ttrss_users set salt = ''; +alter table ttrss_users change salt salt varchar(250) not null; +alter table ttrss_users alter column salt set default ''; + +update ttrss_version set schema_version = 88; + +commit; diff --git a/sql/mysql/migrations/89.sql b/sql/mysql/migrations/89.sql new file mode 100644 index 000000000..9727a9c42 --- /dev/null +++ b/sql/mysql/migrations/89.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('DIGEST_PREFERRED_TIME', 2, '00:00', 'Try to send digests around specified time', 1, 'Uses UTC timezone'); + +update ttrss_version set schema_version = 89; + +commit; diff --git a/sql/mysql/migrations/9.sql b/sql/mysql/migrations/9.sql new file mode 100644 index 000000000..546adc2d7 --- /dev/null +++ b/sql/mysql/migrations/9.sql @@ -0,0 +1,29 @@ +alter table ttrss_feeds add column hidden bool; +update ttrss_feeds set hidden = false; +alter table ttrss_feeds change hidden hidden bool not null; +alter table ttrss_feeds alter column hidden set default false; + +alter table ttrss_users add column email_digest bool; +update ttrss_users set email_digest = false; +alter table ttrss_users change email_digest email_digest bool not null; +alter table ttrss_users alter column email_digest set default false; + +alter table ttrss_users add column last_digest_sent datetime; +update ttrss_users set last_digest_sent = false; +alter table ttrss_users alter column last_digest_sent set default null; + +alter table ttrss_filters add column enabled bool; +update ttrss_filters set enabled = true; +alter table ttrss_filters change enabled enabled bool not null; +alter table ttrss_filters alter column enabled set default true; + +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); + +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); + +update ttrss_prefs SET section_id = 3 WHERE pref_name = 'ENABLE_SEARCH_TOOLBAR'; +update ttrss_prefs SET section_id = 3 WHERE pref_name = 'ENABLE_FEED_ICONS'; +update ttrss_prefs SET section_id = 3 WHERE pref_name = 'EXTENDED_FEEDLIST'; + +update ttrss_version set schema_version = 9; + diff --git a/sql/mysql/migrations/90.sql b/sql/mysql/migrations/90.sql new file mode 100644 index 000000000..b7935535e --- /dev/null +++ b/sql/mysql/migrations/90.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_PREFS_SHOW_EMPTY_CATS', 1, 'false', '', 1); + +update ttrss_version set schema_version = 90; + +commit; diff --git a/sql/mysql/migrations/91.sql b/sql/mysql/migrations/91.sql new file mode 100644 index 000000000..791d43063 --- /dev/null +++ b/sql/mysql/migrations/91.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set help_text = 'Automatically open next feed with unread articles after marking one as read' where pref_name = 'ON_CATCHUP_SHOW_NEXT_FEED'; + +update ttrss_version set schema_version = 91; + +commit; diff --git a/sql/mysql/migrations/92.sql b/sql/mysql/migrations/92.sql new file mode 100644 index 000000000..8ebe81fe0 --- /dev/null +++ b/sql/mysql/migrations/92.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_feeds add column favicon_last_checked datetime; +alter table ttrss_feeds alter column favicon_last_checked set default null; + +update ttrss_version set schema_version = 92; + +commit; diff --git a/sql/mysql/migrations/93.sql b/sql/mysql/migrations/93.sql new file mode 100644 index 000000000..56b96e3b5 --- /dev/null +++ b/sql/mysql/migrations/93.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_feed_categories add column parent_cat integer; +update ttrss_feed_categories set parent_cat = NULL; + +alter table ttrss_feed_categories add FOREIGN KEY (parent_cat) REFERENCES ttrss_feed_categories(id) ON DELETE SET NULL; + +update ttrss_version set schema_version = 93; + +commit; diff --git a/sql/mysql/migrations/94.sql b/sql/mysql/migrations/94.sql new file mode 100644 index 000000000..0f76c4c5f --- /dev/null +++ b/sql/mysql/migrations/94.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_INCLUDE_CHILDREN', 1, 'false', '', 1); + +update ttrss_version set schema_version = 94; + +commit; diff --git a/sql/mysql/migrations/95.sql b/sql/mysql/migrations/95.sql new file mode 100644 index 000000000..234acd495 --- /dev/null +++ b/sql/mysql/migrations/95.sql @@ -0,0 +1,39 @@ +begin; + +update ttrss_prefs set short_desc = 'Purge articles after this number of days (0 - disables)' +where pref_name = 'PURGE_OLD_DAYS'; + +update ttrss_prefs set section_id = 1 where pref_name = 'ENABLE_API_ACCESS'; + +update ttrss_prefs set section_id = 2 where pref_name = 'CONFIRM_FEED_CATCHUP'; +update ttrss_prefs set section_id = 2 where pref_name = 'CDM_EXPANDED'; +update ttrss_prefs set section_id = 2 where pref_name = 'CDM_AUTO_CATCHUP'; +update ttrss_prefs set section_id = 2 where pref_name = 'SORT_HEADLINES_BY_FEED_DATE'; +update ttrss_prefs set section_id = 2 where pref_name = 'HIDE_READ_SHOWS_SPECIAL'; + +insert into ttrss_prefs_sections (id, section_name) values (4, 'Digest'); + +update ttrss_prefs set section_id = 4 where pref_name = 'DIGEST_ENABLE'; +update ttrss_prefs set section_id = 4 where pref_name = 'DIGEST_PREFERRED_TIME'; +update ttrss_prefs set section_id = 4 where pref_name = 'DIGEST_CATCHUP'; + +alter table ttrss_prefs_sections add column order_id integer; +update ttrss_prefs_sections set order_id = 0; +alter table ttrss_prefs_sections change order_id order_id int not null; + +update ttrss_prefs_sections set order_id = 0 where id = 1; +update ttrss_prefs_sections set order_id = 1 where id = 2; +update ttrss_prefs_sections set order_id = 2 where id = 4; +update ttrss_prefs_sections set order_id = 3 where id = 3; + +update ttrss_prefs set access_level = 1 where pref_name in ('ON_CATCHUP_SHOW_NEXT_FEED', + 'SORT_HEADLINES_BY_FEED_DATE', + 'VFEED_GROUP_BY_FEED', + 'FRESH_ARTICLE_MAX_AGE', + 'CDM_EXPANDED', + 'SHOW_CONTENT_PREVIEW', + 'HIDE_READ_SHOWS_SPECIAL'); + +update ttrss_version set schema_version = 95; + +commit; diff --git a/sql/mysql/migrations/96.sql b/sql/mysql/migrations/96.sql new file mode 100644 index 000000000..4d4e2445c --- /dev/null +++ b/sql/mysql/migrations/96.sql @@ -0,0 +1,38 @@ +begin; + +create table ttrss_filters2(id integer primary key auto_increment, + owner_uid integer not null, + match_any_rule boolean not null default false, + enabled boolean not null default true, + index(owner_uid), + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_filters2_rules(id integer primary key auto_increment, + filter_id integer not null references ttrss_filters2(id) on delete cascade, + reg_exp varchar(250) not null, + filter_type integer not null, + feed_id integer default null, + cat_id integer default null, + cat_filter boolean not null default false, + index (filter_id), + foreign key (filter_id) references ttrss_filters2(id) on delete cascade, + index (filter_type), + foreign key (filter_type) references ttrss_filter_types(id) ON DELETE CASCADE, + index (feed_id), + foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE, + index (cat_id), + foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_filters2_actions(id integer primary key auto_increment, + filter_id integer not null, + action_id integer not null default 1 references ttrss_filter_actions(id) on delete cascade, + action_param varchar(250) not null default '', + index (filter_id), + foreign key (filter_id) references ttrss_filters2(id) on delete cascade, + index (action_id), + foreign key (action_id) references ttrss_filter_actions(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +update ttrss_version set schema_version = 96; + +commit; + diff --git a/sql/mysql/migrations/97.sql b/sql/mysql/migrations/97.sql new file mode 100644 index 000000000..c555b86dc --- /dev/null +++ b/sql/mysql/migrations/97.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_users add column otp_enabled boolean; +update ttrss_users set otp_enabled = false; +alter table ttrss_users change otp_enabled otp_enabled boolean not null; +alter table ttrss_users alter column otp_enabled set default false; + +update ttrss_version set schema_version = 97; + +commit; diff --git a/sql/mysql/migrations/98.sql b/sql/mysql/migrations/98.sql new file mode 100644 index 000000000..1984a4325 --- /dev/null +++ b/sql/mysql/migrations/98.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,access_level) values('AUTO_ASSIGN_LABELS', 1, 'true', 'Assign articles to labels automatically', 3, 1); + +update ttrss_version set schema_version = 98; + +commit; diff --git a/sql/mysql/migrations/99.sql b/sql/mysql/migrations/99.sql new file mode 100644 index 000000000..d7f9e0230 --- /dev/null +++ b/sql/mysql/migrations/99.sql @@ -0,0 +1,12 @@ +begin; + +alter table ttrss_feeds add column cache_content bool; +update ttrss_feeds set cache_content = false; +alter table ttrss_feeds change cache_content cache_content bool not null; +alter table ttrss_feeds alter column cache_content set default false; + +alter table ttrss_entries add column cached_content longtext; + +update ttrss_version set schema_version = 99; + +commit; diff --git a/sql/mysql/schema.sql b/sql/mysql/schema.sql new file mode 100644 index 000000000..a3fbc827d --- /dev/null +++ b/sql/mysql/schema.sql @@ -0,0 +1,421 @@ +SET NAMES utf8; +SET CHARACTER SET utf8; + +drop table if exists ttrss_error_log; +drop table if exists ttrss_plugin_storage; +drop table if exists ttrss_linked_feeds; +drop table if exists ttrss_linked_instances; +drop table if exists ttrss_access_keys; +drop table if exists ttrss_user_labels2; +drop table if exists ttrss_labels2; +drop table if exists ttrss_feedbrowser_cache; +drop table if exists ttrss_version; +drop table if exists ttrss_labels; +drop table if exists ttrss_filters2_actions; +drop table if exists ttrss_filters2_rules; +drop table if exists ttrss_filters2; +drop table if exists ttrss_filters; +drop table if exists ttrss_filter_types; +drop table if exists ttrss_filter_actions; +drop table if exists ttrss_user_prefs; +drop table if exists ttrss_user_prefs2; +drop table if exists ttrss_prefs; +drop table if exists ttrss_prefs_types; +drop table if exists ttrss_prefs_sections; +drop table if exists ttrss_tags; +drop table if exists ttrss_enclosures; +drop table if exists ttrss_settings_profiles; +drop table if exists ttrss_entry_comments; +drop table if exists ttrss_user_entries; +drop table if exists ttrss_entries; +drop table if exists ttrss_scheduled_updates; +drop table if exists ttrss_counters_cache; +drop table if exists ttrss_cat_counters_cache; +drop table if exists ttrss_feeds; +drop table if exists ttrss_archived_feeds; +drop table if exists ttrss_feed_categories; +drop table if exists ttrss_app_passwords; +drop table if exists ttrss_users; +drop table if exists ttrss_themes; +drop table if exists ttrss_sessions; + +begin; + +create table ttrss_users (id integer primary key not null auto_increment, + login varchar(120) not null unique, + pwd_hash varchar(250) not null, + last_login datetime default null, + access_level integer not null default 0, + email varchar(250) not null default '', + full_name varchar(250) not null default '', + email_digest bool not null default false, + last_digest_sent datetime default null, + salt varchar(250) not null default '', + created datetime default null, + twitter_oauth longtext default null, + otp_enabled boolean not null default false, + resetpass_token varchar(250) default null) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +insert into ttrss_users (login,pwd_hash,access_level) values ('admin', + 'SHA1:5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', 10); + +create table ttrss_app_passwords (id integer not null primary key auto_increment, + title varchar(250) not null, + pwd_hash text not null, + service varchar(100) not null, + created datetime not null, + last_used datetime default null, + owner_uid integer not null references ttrss_users(id) on delete cascade) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_feed_categories(id integer not null primary key auto_increment, + owner_uid integer not null, + title varchar(200) not null, + collapsed bool not null default false, + order_id integer not null default 0, + parent_cat integer, + view_settings varchar(250) not null default '', + foreign key (parent_cat) references ttrss_feed_categories(id) ON DELETE SET NULL, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_archived_feeds (id integer not null primary key, + owner_uid integer not null, + created datetime not null, + title varchar(200) not null, + feed_url text not null, + site_url varchar(250) not null default '', + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_counters_cache ( + feed_id integer not null, + owner_uid integer not null, + value integer not null default 0, + updated datetime not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create index ttrss_counters_cache_feed_id_idx on ttrss_counters_cache(feed_id); +create index ttrss_counters_cache_value_idx on ttrss_counters_cache(value); + +create table ttrss_cat_counters_cache ( + feed_id integer not null, + owner_uid integer not null, + value integer not null default 0, + updated datetime not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_feeds (id integer not null auto_increment primary key, + owner_uid integer not null, + title varchar(200) not null, + cat_id integer default null, + feed_url text not null, + icon_url varchar(250) not null default '', + update_interval integer not null default 0, + purge_interval integer not null default 0, + last_updated datetime default null, + last_unconditional datetime default null, + last_error varchar(250) not null default '', + last_modified varchar(250) not null default '', + favicon_avg_color varchar(11) default null, + site_url varchar(250) not null default '', + auth_login varchar(250) not null default '', + auth_pass varchar(250) not null default '', + parent_feed integer default null, + private bool not null default false, + rtl_content bool not null default false, + hidden bool not null default false, + include_in_digest boolean not null default true, + cache_images boolean not null default false, + hide_images boolean not null default false, + cache_content boolean not null default false, + auth_pass_encrypted boolean not null default false, + last_viewed datetime default null, + last_update_started datetime default null, + last_successful_update datetime default null, + always_display_enclosures boolean not null default false, + update_method integer not null default 0, + order_id integer not null default 0, + mark_unread_on_update boolean not null default false, + update_on_checksum_change boolean not null default false, + strip_images boolean not null default false, + view_settings varchar(250) not null default '', + pubsub_state integer not null default 0, + favicon_last_checked datetime default null, + feed_language varchar(100) not null default '', + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE, + foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE SET NULL, + foreign key (parent_feed) references ttrss_feeds(id) ON DELETE SET NULL, + unique(feed_url(255), owner_uid)) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +insert into ttrss_feeds (owner_uid, title, feed_url) values + ((select id from ttrss_users where login = 'admin'), 'Tiny Tiny RSS: Forum', 'https://tt-rss.org/forum/rss.php'); + +create table ttrss_entries (id integer not null primary key auto_increment, + title text not null, + guid varchar(255) not null unique, + link text not null, + updated datetime not null, + content longtext not null, + content_hash varchar(250) not null, + cached_content longtext, + no_orig_date bool not null default 0, + date_entered datetime not null, + date_updated datetime not null, + num_comments integer not null default 0, + plugin_data longtext, + lang varchar(2), + comments varchar(250) not null default '', + author varchar(250) not null default '') ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create index ttrss_entries_date_entered_index on ttrss_entries(date_entered); +create index ttrss_entries_updated_idx on ttrss_entries(updated); + +create table ttrss_user_entries ( + int_id integer not null primary key auto_increment, + ref_id integer not null, + uuid varchar(200) not null, + feed_id int, + orig_feed_id int, + owner_uid integer not null, + marked bool not null default 0, + published bool not null default 0, + tag_cache text not null, + label_cache text not null, + last_read datetime, + score int not null default 0, + note longtext, + last_marked datetime, + last_published datetime, + unread bool not null default 1, + foreign key (ref_id) references ttrss_entries(id) ON DELETE CASCADE, + foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE, + foreign key (orig_feed_id) references ttrss_archived_feeds(id) ON DELETE SET NULL, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create index ttrss_user_entries_unread_idx on ttrss_user_entries(unread); + +create table ttrss_entry_comments (id integer not null primary key, + ref_id integer not null, + owner_uid integer not null, + private bool not null default 0, + date_entered datetime not null, + index (ref_id), + foreign key (ref_id) references ttrss_entries(id) ON DELETE CASCADE, + index (owner_uid), + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_filter_types (id integer primary key, + name varchar(120) unique not null, + description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +insert into ttrss_filter_types (id,name,description) values (1, 'title', 'Title'); +insert into ttrss_filter_types (id,name,description) values (2, 'content', 'Content'); +insert into ttrss_filter_types (id,name,description) values (3, 'both', + 'Title or Content'); +insert into ttrss_filter_types (id,name,description) values (4, 'link', + 'Link'); +insert into ttrss_filter_types (id,name,description) values (5, 'date', + 'Article Date'); +insert into ttrss_filter_types (id,name,description) values (6, 'author', 'Author'); +insert into ttrss_filter_types (id,name,description) values (7, 'tag', 'Article Tags'); + +create table ttrss_filter_actions (id integer not null primary key, + name varchar(120) unique not null, + description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +insert into ttrss_filter_actions (id,name,description) values (1, 'filter', + 'Delete article'); + +insert into ttrss_filter_actions (id,name,description) values (2, 'catchup', + 'Mark as read'); + +insert into ttrss_filter_actions (id,name,description) values (3, 'mark', + 'Set starred'); + +insert into ttrss_filter_actions (id,name,description) values (4, 'tag', + 'Assign tags'); + +insert into ttrss_filter_actions (id,name,description) values (5, 'publish', + 'Publish article'); + +insert into ttrss_filter_actions (id,name,description) values (6, 'score', + 'Modify score'); + +insert into ttrss_filter_actions (id,name,description) values (7, 'label', + 'Assign label'); + +insert into ttrss_filter_actions (id,name,description) values (8, 'stop', + 'Stop / Do nothing'); + +insert into ttrss_filter_actions (id,name,description) values (9, 'plugin', + 'Invoke plugin'); + +create table ttrss_filters2(id integer primary key auto_increment, + owner_uid integer not null, + match_any_rule boolean not null default false, + enabled boolean not null default true, + inverse bool not null default false, + title varchar(250) not null default '', + order_id integer not null default 0, + last_triggered datetime default null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_filters2_rules(id integer primary key auto_increment, + filter_id integer not null references ttrss_filters2(id) on delete cascade, + reg_exp text not null, + inverse bool not null default false, + filter_type integer not null, + feed_id integer default null, + cat_id integer default null, + cat_filter boolean not null default false, + match_on text, + index (filter_id), + foreign key (filter_id) references ttrss_filters2(id) on delete cascade, + index (filter_type), + foreign key (filter_type) references ttrss_filter_types(id) ON DELETE CASCADE, + index (feed_id), + foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE, + index (cat_id), + foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_filters2_actions(id integer primary key auto_increment, + filter_id integer not null, + action_id integer not null default 1 references ttrss_filter_actions(id) on delete cascade, + action_param varchar(250) not null default '', + index (filter_id), + foreign key (filter_id) references ttrss_filters2(id) on delete cascade, + index (action_id), + foreign key (action_id) references ttrss_filter_actions(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_tags (id integer primary key auto_increment, + owner_uid integer not null, + tag_name varchar(250) not null, + post_int_id integer not null, + index (post_int_id), + foreign key (post_int_id) references ttrss_user_entries(int_id) ON DELETE CASCADE, + index (owner_uid), + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_version (schema_version int not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +insert into ttrss_version values (142); + +create table ttrss_enclosures (id integer primary key auto_increment, + content_url text not null, + content_type varchar(250) not null, + post_id integer not null, + title text not null, + duration text not null, + width integer not null default 0, + height integer not null default 0, + foreign key (post_id) references ttrss_entries(id) ON DELETE cascade) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_settings_profiles(id integer primary key auto_increment, + title varchar(250) not null, + owner_uid integer not null, + index (owner_uid), + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_prefs_types (id integer not null primary key, + type_name varchar(100) not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_prefs_sections (id integer not null primary key, + order_id integer not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_prefs (pref_name varchar(250) not null primary key, + type_id integer not null, + section_id integer not null default 1, + access_level integer not null default 0, + def_value text not null, + foreign key (type_id) references ttrss_prefs_types(id), + foreign key (section_id) references ttrss_prefs_sections(id)) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_user_prefs ( + owner_uid integer not null, + pref_name varchar(250), + value longtext not null, + profile integer, + index (profile), + foreign key (profile) references ttrss_settings_profiles(id) ON DELETE CASCADE, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE, + foreign key (pref_name) references ttrss_prefs(pref_name) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_user_prefs2 ( + owner_uid integer not null, + pref_name varchar(250), + profile integer null, + value longtext not null, + foreign key (profile) references ttrss_settings_profiles(id) ON DELETE CASCADE, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_sessions (id varchar(250) not null primary key, + data text, + expire integer not null, + index (expire)) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_feedbrowser_cache ( + feed_url text not null, + site_url text not null, + title text not null, + subscribers integer not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_labels2 (id integer not null primary key auto_increment, + owner_uid integer not null, + caption varchar(250) not null, + fg_color varchar(15) not null default '', + bg_color varchar(15) not null default '', + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_user_labels2 (label_id integer not null, + article_id integer not null, + foreign key (label_id) references ttrss_labels2(id) ON DELETE CASCADE, + foreign key (article_id) references ttrss_entries(id) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create index ttrss_user_labels2_article_id_idx on ttrss_user_labels2(article_id); +create index ttrss_user_labels2_label_id_idx on ttrss_user_labels2(label_id); + +create table ttrss_access_keys (id integer not null primary key auto_increment, + access_key varchar(250) not null, + feed_id varchar(250) not null, + is_cat bool not null default false, + owner_uid integer not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_linked_instances (id integer not null primary key auto_increment, + last_connected datetime not null, + last_status_in integer not null, + last_status_out integer not null, + access_key varchar(250) not null unique, + access_url text not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_linked_feeds ( + feed_url text not null, + site_url text not null, + title text not null, + created datetime not null, + updated datetime not null, + instance_id integer not null, + subscribers integer not null, + foreign key (instance_id) references ttrss_linked_instances(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_plugin_storage ( + id integer not null auto_increment primary key, + name varchar(100) not null, + owner_uid integer not null, + content longtext not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +create table ttrss_error_log( + id integer not null auto_increment primary key, + owner_uid integer, + errno integer not null, + errstr text not null, + filename text not null, + lineno integer not null, + context text not null, + created_at datetime not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8; + +commit; diff --git a/sql/pgsql/migrations/10.sql b/sql/pgsql/migrations/10.sql new file mode 100644 index 000000000..764b4b763 --- /dev/null +++ b/sql/pgsql/migrations/10.sql @@ -0,0 +1,12 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('DIGEST_ENABLE', 1, 'false', 'Enable e-mail digest',1, +'This option enables sending daily digest of new (and unread) headlines on your configured e-mail address'); + +alter table ttrss_feeds add column include_in_digest boolean; +update ttrss_feeds set include_in_digest = true; +alter table ttrss_feeds alter column include_in_digest set not null; +alter table ttrss_feeds alter column include_in_digest set default true; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('CONFIRM_FEED_CATCHUP', 1, 'true', 'Confirm marking feed as read',3); + +update ttrss_version set schema_version = 10; + diff --git a/sql/pgsql/migrations/100.sql b/sql/pgsql/migrations/100.sql new file mode 100644 index 000000000..a360dab6c --- /dev/null +++ b/sql/pgsql/migrations/100.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_ENABLED_PLUGINS', 2, '', '', 1); + +update ttrss_version set schema_version = 100; + +commit; diff --git a/sql/pgsql/migrations/101.sql b/sql/pgsql/migrations/101.sql new file mode 100644 index 000000000..5be859002 --- /dev/null +++ b/sql/pgsql/migrations/101.sql @@ -0,0 +1,11 @@ +begin; + +create table ttrss_plugin_storage ( + id serial not null primary key, + name varchar(100) not null, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + content text not null); + +update ttrss_version set schema_version = 101; + +commit; diff --git a/sql/pgsql/migrations/102.sql b/sql/pgsql/migrations/102.sql new file mode 100644 index 000000000..ccf5ef3b3 --- /dev/null +++ b/sql/pgsql/migrations/102.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_REVERSE_HEADLINES', 1, 'false', '', 1); + +update ttrss_version set schema_version = 102; + +commit; diff --git a/sql/pgsql/migrations/103.sql b/sql/pgsql/migrations/103.sql new file mode 100644 index 000000000..cb91fd871 --- /dev/null +++ b/sql/pgsql/migrations/103.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_entries add column plugin_data text; + +update ttrss_version set schema_version = 103; + +commit; diff --git a/sql/pgsql/migrations/104.sql b/sql/pgsql/migrations/104.sql new file mode 100644 index 000000000..104e4b917 --- /dev/null +++ b/sql/pgsql/migrations/104.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set short_desc = 'Hide images in articles' where pref_name = 'STRIP_IMAGES'; + +update ttrss_version set schema_version = 104; + +commit; diff --git a/sql/pgsql/migrations/105.sql b/sql/pgsql/migrations/105.sql new file mode 100644 index 000000000..11ef679f1 --- /dev/null +++ b/sql/pgsql/migrations/105.sql @@ -0,0 +1,11 @@ +begin; + +alter table ttrss_user_entries add column last_marked timestamp; +alter table ttrss_user_entries add column last_published timestamp; + +update ttrss_user_entries set last_published = last_read where published = true; +update ttrss_user_entries set last_marked = last_read where marked = true; + +update ttrss_version set schema_version = 105; + +commit; diff --git a/sql/pgsql/migrations/106.sql b/sql/pgsql/migrations/106.sql new file mode 100644 index 000000000..6e3dee2c9 --- /dev/null +++ b/sql/pgsql/migrations/106.sql @@ -0,0 +1,12 @@ +begin; + +update ttrss_prefs set short_desc = 'Do not embed images in articles' where pref_name = 'STRIP_IMAGES'; + +alter table ttrss_feeds add column hide_images boolean; +update ttrss_feeds set hide_images = false; +alter table ttrss_feeds alter column hide_images set not null; +alter table ttrss_feeds alter column hide_images set default false; + +update ttrss_version set schema_version = 106; + +commit; diff --git a/sql/pgsql/migrations/107.sql b/sql/pgsql/migrations/107.sql new file mode 100644 index 000000000..a4a537c2f --- /dev/null +++ b/sql/pgsql/migrations/107.sql @@ -0,0 +1,15 @@ +begin; + +alter table ttrss_filters2 add column inverse boolean; +update ttrss_filters2 set inverse = false; +alter table ttrss_filters2 alter column inverse set not null; +alter table ttrss_filters2 alter column inverse set default false; + +alter table ttrss_filters2_rules add column inverse boolean; +update ttrss_filters2_rules set inverse = false; +alter table ttrss_filters2_rules alter column inverse set not null; +alter table ttrss_filters2_rules alter column inverse set default false; + +update ttrss_version set schema_version = 107; + +commit; diff --git a/sql/pgsql/migrations/108.sql b/sql/pgsql/migrations/108.sql new file mode 100644 index 000000000..899be8bb4 --- /dev/null +++ b/sql/pgsql/migrations/108.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'false' where pref_name = 'SORT_HEADLINES_BY_FEED_DATE'; + +update ttrss_version set schema_version = 108; + +commit; diff --git a/sql/pgsql/migrations/109.sql b/sql/pgsql/migrations/109.sql new file mode 100644 index 000000000..77e883c3b --- /dev/null +++ b/sql/pgsql/migrations/109.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set short_desc = 'Hide feeds with no unread articles' where pref_name = 'HIDE_READ_FEEDS'; + +update ttrss_version set schema_version = 109; + +commit; diff --git a/sql/pgsql/migrations/11.sql b/sql/pgsql/migrations/11.sql new file mode 100644 index 000000000..690b978eb --- /dev/null +++ b/sql/pgsql/migrations/11.sql @@ -0,0 +1,18 @@ +begin; + +delete FROM ttrss_user_prefs WHERE pref_name = 'DISPLAY_HEADER'; +delete FROM ttrss_user_prefs WHERE pref_name = 'DISPLAY_FOOTER'; +delete FROM ttrss_user_prefs WHERE pref_name = 'ENABLE_SEARCH_TOOLBAR'; +delete FROM ttrss_user_prefs WHERE pref_name = 'USE_COMPACT_STYLESHEET'; + +delete FROM ttrss_prefs WHERE pref_name = 'DISPLAY_HEADER'; +delete FROM ttrss_prefs WHERE pref_name = 'DISPLAY_FOOTER'; +delete FROM ttrss_prefs WHERE pref_name = 'ENABLE_SEARCH_TOOLBAR'; +delete FROM ttrss_prefs WHERE pref_name = 'USE_COMPACT_STYLESHEET'; + +insert into ttrss_themes (theme_name, theme_path) values ('Graycube', 'graycube'); +insert into ttrss_themes (theme_name, theme_path) values ('Default (Compact)', 'compact'); + +update ttrss_version set schema_version = 11; + +commit; diff --git a/sql/pgsql/migrations/110.sql b/sql/pgsql/migrations/110.sql new file mode 100644 index 000000000..859f7d6dc --- /dev/null +++ b/sql/pgsql/migrations/110.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('USER_CSS_THEME', 2, '', 'Select theme', 2, 'Select one of the available CSS themes'); + +update ttrss_version set schema_version = 110; + +commit; diff --git a/sql/pgsql/migrations/111.sql b/sql/pgsql/migrations/111.sql new file mode 100644 index 000000000..27f287d1a --- /dev/null +++ b/sql/pgsql/migrations/111.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'true' where pref_name = 'COMBINED_DISPLAY_MODE'; + +update ttrss_version set schema_version = 111; + +commit; diff --git a/sql/pgsql/migrations/112.sql b/sql/pgsql/migrations/112.sql new file mode 100644 index 000000000..a9d90d6d4 --- /dev/null +++ b/sql/pgsql/migrations/112.sql @@ -0,0 +1,15 @@ +begin; + +alter table ttrss_filters2 add column order_id integer; +update ttrss_filters2 set order_id = 0; +alter table ttrss_filters2 alter column order_id set not null; +alter table ttrss_filters2 alter column order_id set default 0; + +alter table ttrss_filters2 add column title varchar(250); +update ttrss_filters2 set title = ''; +alter table ttrss_filters2 alter column title set not null; +alter table ttrss_filters2 alter column title set default ''; + +update ttrss_version set schema_version = 112; + +commit; diff --git a/sql/pgsql/migrations/113.sql b/sql/pgsql/migrations/113.sql new file mode 100644 index 000000000..3c0d00563 --- /dev/null +++ b/sql/pgsql/migrations/113.sql @@ -0,0 +1,8 @@ +begin; + +insert into ttrss_filter_actions (id,name,description) values (8, 'stop', + 'Stop / Do nothing'); + +update ttrss_version set schema_version = 113; + +commit; diff --git a/sql/pgsql/migrations/114.sql b/sql/pgsql/migrations/114.sql new file mode 100644 index 000000000..d317ea794 --- /dev/null +++ b/sql/pgsql/migrations/114.sql @@ -0,0 +1,15 @@ +begin; + +alter table ttrss_feeds add column view_settings varchar(250); +update ttrss_feeds set view_settings = ''; +alter table ttrss_feeds alter column view_settings set not null; +alter table ttrss_feeds alter column view_settings set default ''; + +alter table ttrss_feed_categories add column view_settings varchar(250); +update ttrss_feed_categories set view_settings = ''; +alter table ttrss_feed_categories alter column view_settings set not null; +alter table ttrss_feed_categories alter column view_settings set default ''; + +update ttrss_version set schema_version = 114; + +commit; diff --git a/sql/pgsql/migrations/115.sql b/sql/pgsql/migrations/115.sql new file mode 100644 index 000000000..e23f0533a --- /dev/null +++ b/sql/pgsql/migrations/115.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_prefs_sections drop column section_name; +alter table ttrss_prefs drop column short_desc; +alter table ttrss_prefs drop column help_text; + +update ttrss_version set schema_version = 115; + +commit; diff --git a/sql/pgsql/migrations/116.sql b/sql/pgsql/migrations/116.sql new file mode 100644 index 000000000..252ba0e3a --- /dev/null +++ b/sql/pgsql/migrations/116.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'false' where pref_name = 'ALLOW_DUPLICATE_POSTS'; + +update ttrss_version set schema_version = 116; + +commit; diff --git a/sql/pgsql/migrations/117.sql b/sql/pgsql/migrations/117.sql new file mode 100644 index 000000000..da3d056cc --- /dev/null +++ b/sql/pgsql/migrations/117.sql @@ -0,0 +1,8 @@ +begin; + +ALTER TABLE ttrss_feeds ADD COLUMN favicon_avg_color VARCHAR(11); +alter table ttrss_feeds alter column favicon_avg_color set default null; + +update ttrss_version set schema_version = 117; + +commit; diff --git a/sql/pgsql/migrations/118.sql b/sql/pgsql/migrations/118.sql new file mode 100644 index 000000000..161cf4e3c --- /dev/null +++ b/sql/pgsql/migrations/118.sql @@ -0,0 +1,15 @@ +begin; + +create table ttrss_error_log( + id serial not null primary key, + owner_uid integer references ttrss_users(id) ON DELETE SET NULL, + errno integer not null, + errstr text not null, + filename text not null, + lineno integer not null, + context text not null, + created_at timestamp not null); + +update ttrss_version set schema_version = 118; + +commit; diff --git a/sql/pgsql/migrations/119.sql b/sql/pgsql/migrations/119.sql new file mode 100644 index 000000000..ddb7d64de --- /dev/null +++ b/sql/pgsql/migrations/119.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'Automatic' where pref_name = 'USER_TIMEZONE'; + +update ttrss_version set schema_version = 119; + +commit; diff --git a/sql/pgsql/migrations/12.sql b/sql/pgsql/migrations/12.sql new file mode 100644 index 000000000..6de90f1ab --- /dev/null +++ b/sql/pgsql/migrations/12.sql @@ -0,0 +1,15 @@ +begin; + +alter table ttrss_filters add column action_param varchar(200); + +update ttrss_filters set action_param = ''; + +alter table ttrss_filters alter column action_param set not null; +alter table ttrss_filters alter column action_param set default ''; + +insert into ttrss_filter_actions (id,name,description) values (4, 'tag', + 'Assign tags'); + +update ttrss_version set schema_version = 12; + +commit; diff --git a/sql/pgsql/migrations/120.sql b/sql/pgsql/migrations/120.sql new file mode 100644 index 000000000..34971146e --- /dev/null +++ b/sql/pgsql/migrations/120.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_LANGUAGE', 2, '', 2); + +update ttrss_version set schema_version = 120; + +commit; diff --git a/sql/pgsql/migrations/121.sql b/sql/pgsql/migrations/121.sql new file mode 100644 index 000000000..d9cf51184 --- /dev/null +++ b/sql/pgsql/migrations/121.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set def_value = 'false' where pref_name = 'AUTO_ASSIGN_LABELS'; + +update ttrss_version set schema_version = 121; + +commit; diff --git a/sql/pgsql/migrations/122.sql b/sql/pgsql/migrations/122.sql new file mode 100644 index 000000000..397b97bfc --- /dev/null +++ b/sql/pgsql/migrations/122.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_entries add column lang varchar(2); + +update ttrss_version set schema_version = 122; + +commit; diff --git a/sql/pgsql/migrations/123.sql b/sql/pgsql/migrations/123.sql new file mode 100644 index 000000000..999d48bf7 --- /dev/null +++ b/sql/pgsql/migrations/123.sql @@ -0,0 +1,5 @@ +begin; + +update ttrss_version set schema_version = 123; + +commit; diff --git a/sql/pgsql/migrations/124.sql b/sql/pgsql/migrations/124.sql new file mode 100644 index 000000000..96ae46341 --- /dev/null +++ b/sql/pgsql/migrations/124.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_users add column resetpass_token varchar(250); +alter table ttrss_users alter column resetpass_token set default null; + +update ttrss_version set schema_version = 124; + +commit; diff --git a/sql/pgsql/migrations/125.sql b/sql/pgsql/migrations/125.sql new file mode 100644 index 000000000..e56e0299a --- /dev/null +++ b/sql/pgsql/migrations/125.sql @@ -0,0 +1,5 @@ +begin; + +update ttrss_version set schema_version = 125; + +commit; diff --git a/sql/pgsql/migrations/126.sql b/sql/pgsql/migrations/126.sql new file mode 100644 index 000000000..46f391aa5 --- /dev/null +++ b/sql/pgsql/migrations/126.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_enclosures add column height integer not null default 0; +alter table ttrss_enclosures add column width integer not null default 0; + +update ttrss_version set schema_version = 126; + +commit; diff --git a/sql/pgsql/migrations/127.sql b/sql/pgsql/migrations/127.sql new file mode 100644 index 000000000..7895aacc0 --- /dev/null +++ b/sql/pgsql/migrations/127.sql @@ -0,0 +1,8 @@ +BEGIN; + +DROP INDEX ttrss_entries_guid_index; +DROP INDEX ttrss_prefs_pref_name_idx; + +UPDATE ttrss_version SET schema_version = 127; + +COMMIT; diff --git a/sql/pgsql/migrations/128.sql b/sql/pgsql/migrations/128.sql new file mode 100644 index 000000000..3aba67220 --- /dev/null +++ b/sql/pgsql/migrations/128.sql @@ -0,0 +1,13 @@ +BEGIN; + +alter table ttrss_entries add column tsvector_combined tsvector; +create index ttrss_entries_tsvector_combined_idx on ttrss_entries using gin(tsvector_combined); + +alter table ttrss_feeds add column feed_language varchar(100); +update ttrss_feeds set feed_language = ''; +alter table ttrss_feeds alter column feed_language set not null; +alter table ttrss_feeds alter column feed_language set default ''; + +UPDATE ttrss_version SET schema_version = 128; + +COMMIT; diff --git a/sql/pgsql/migrations/129.sql b/sql/pgsql/migrations/129.sql new file mode 100644 index 000000000..2ebec0dc9 --- /dev/null +++ b/sql/pgsql/migrations/129.sql @@ -0,0 +1,8 @@ +BEGIN; + +insert into ttrss_filter_actions (id,name,description) values (9, 'plugin', + 'Invoke plugin'); + +UPDATE ttrss_version SET schema_version = 129; + +COMMIT; diff --git a/sql/pgsql/migrations/13.sql b/sql/pgsql/migrations/13.sql new file mode 100644 index 000000000..bf2a2b438 --- /dev/null +++ b/sql/pgsql/migrations/13.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_filters add column inverse boolean; +update ttrss_filters set inverse = false; +alter table ttrss_filters alter column inverse set not null; +alter table ttrss_filters alter column inverse set default false; + +update ttrss_version set schema_version = 13; + +commit; diff --git a/sql/pgsql/migrations/130.sql b/sql/pgsql/migrations/130.sql new file mode 100644 index 000000000..820f0b6a8 --- /dev/null +++ b/sql/pgsql/migrations/130.sql @@ -0,0 +1,5 @@ +BEGIN; + +UPDATE ttrss_version SET schema_version = 130; + +COMMIT; diff --git a/sql/pgsql/migrations/131.sql b/sql/pgsql/migrations/131.sql new file mode 100644 index 000000000..9b3001221 --- /dev/null +++ b/sql/pgsql/migrations/131.sql @@ -0,0 +1,7 @@ +BEGIN; + +ALTER table ttrss_filters2_rules ADD COLUMN match_on TEXT; + +UPDATE ttrss_version SET schema_version = 131; + +COMMIT; diff --git a/sql/pgsql/migrations/132.sql b/sql/pgsql/migrations/132.sql new file mode 100644 index 000000000..e6b4bd13f --- /dev/null +++ b/sql/pgsql/migrations/132.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_feeds add column last_modified text; +update ttrss_feeds set last_modified = ''; +alter table ttrss_feeds alter column last_modified set not null; +alter table ttrss_feeds alter column last_modified set default ''; + +UPDATE ttrss_version SET schema_version = 132; + +commit; diff --git a/sql/pgsql/migrations/133.sql b/sql/pgsql/migrations/133.sql new file mode 100644 index 000000000..7bf2f9fa6 --- /dev/null +++ b/sql/pgsql/migrations/133.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_feeds add column last_unconditional timestamp null; + +UPDATE ttrss_version SET schema_version = 133; + +commit; diff --git a/sql/pgsql/migrations/134.sql b/sql/pgsql/migrations/134.sql new file mode 100644 index 000000000..036abb126 --- /dev/null +++ b/sql/pgsql/migrations/134.sql @@ -0,0 +1,7 @@ +BEGIN; + +ALTER TABLE ttrss_filters2_rules ALTER COLUMN reg_exp TYPE text; + +UPDATE ttrss_version SET schema_version = 134; + +COMMIT; diff --git a/sql/pgsql/migrations/135.sql b/sql/pgsql/migrations/135.sql new file mode 100644 index 000000000..b1ec70505 --- /dev/null +++ b/sql/pgsql/migrations/135.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_filters2 add column last_triggered timestamp; +alter table ttrss_filters2 alter column last_triggered set default null; + +update ttrss_version set schema_version = 135; + +commit; diff --git a/sql/pgsql/migrations/136.sql b/sql/pgsql/migrations/136.sql new file mode 100644 index 000000000..d74136537 --- /dev/null +++ b/sql/pgsql/migrations/136.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_archived_feeds add column created timestamp; +update ttrss_archived_feeds set created = NOW(); +alter table ttrss_archived_feeds alter column created set not null; + +update ttrss_version set schema_version = 136; + +commit; diff --git a/sql/pgsql/migrations/137.sql b/sql/pgsql/migrations/137.sql new file mode 100644 index 000000000..71b67327e --- /dev/null +++ b/sql/pgsql/migrations/137.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_feeds add constraint ttrss_feeds_feed_url_owner_uid_key unique (feed_url, owner_uid); + +update ttrss_version set schema_version = 137; + +commit; diff --git a/sql/pgsql/migrations/138.sql b/sql/pgsql/migrations/138.sql new file mode 100644 index 000000000..6bfc5bcd5 --- /dev/null +++ b/sql/pgsql/migrations/138.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DEFAULT_SEARCH_LANGUAGE', 2, '', 2); + +update ttrss_version set schema_version = 138; + +commit; diff --git a/sql/pgsql/migrations/139.sql b/sql/pgsql/migrations/139.sql new file mode 100644 index 000000000..b2617390c --- /dev/null +++ b/sql/pgsql/migrations/139.sql @@ -0,0 +1,13 @@ +begin; + +create table ttrss_app_passwords (id serial not null primary key, + title varchar(250) not null, + pwd_hash text not null, + service varchar(100) not null, + created timestamp not null, + last_used timestamp default null, + owner_uid integer not null references ttrss_users(id) on delete cascade); + +update ttrss_version set schema_version = 139; + +commit; diff --git a/sql/pgsql/migrations/14.sql b/sql/pgsql/migrations/14.sql new file mode 100644 index 000000000..8e7cc4dd5 --- /dev/null +++ b/sql/pgsql/migrations/14.sql @@ -0,0 +1,4 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('CDM_AUTO_CATCHUP', 1, 'false', 'Mark articles as read automatically',2, +'This option enables marking articles as read automatically in combined mode while you scroll article list.'); + +update ttrss_version set schema_version = 14; diff --git a/sql/pgsql/migrations/140.sql b/sql/pgsql/migrations/140.sql new file mode 100644 index 000000000..985e30241 --- /dev/null +++ b/sql/pgsql/migrations/140.sql @@ -0,0 +1,4 @@ +alter table ttrss_feeds add column last_successful_update timestamp; +alter table ttrss_feeds alter column last_successful_update set default null; + +update ttrss_version set schema_version = 140; diff --git a/sql/pgsql/migrations/141.sql b/sql/pgsql/migrations/141.sql new file mode 100644 index 000000000..679ca9498 --- /dev/null +++ b/sql/pgsql/migrations/141.sql @@ -0,0 +1,11 @@ +create table ttrss_user_prefs2 ( + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + pref_name varchar(250) not null, + profile integer references ttrss_settings_profiles(id) ON DELETE CASCADE, + value text not null); + +create index ttrss_user_prefs2_owner_uid_index on ttrss_user_prefs2(owner_uid); +create index ttrss_user_prefs2_pref_name_idx on ttrss_user_prefs2(pref_name); +create unique index ttrss_user_prefs2_composite_idx on ttrss_user_prefs2(pref_name, owner_uid, coalesce(profile, -1)); + +update ttrss_version set schema_version = 141; diff --git a/sql/pgsql/migrations/142.sql b/sql/pgsql/migrations/142.sql new file mode 100644 index 000000000..7a75f3f52 --- /dev/null +++ b/sql/pgsql/migrations/142.sql @@ -0,0 +1,4 @@ +create index ttrss_user_labels2_article_id_idx on ttrss_user_labels2(article_id); +create index ttrss_user_labels2_label_id_idx on ttrss_user_labels2(label_id); + +update ttrss_version set schema_version = 142; diff --git a/sql/pgsql/migrations/15.sql b/sql/pgsql/migrations/15.sql new file mode 100644 index 000000000..9fa183a09 --- /dev/null +++ b/sql/pgsql/migrations/15.sql @@ -0,0 +1,5 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_VIEW_MODE', 2, 'adaptive', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_VIEW_LIMIT', 3, '30', '', 1); + +update ttrss_version set schema_version = 15; diff --git a/sql/pgsql/migrations/16.sql b/sql/pgsql/migrations/16.sql new file mode 100644 index 000000000..996e9a5dd --- /dev/null +++ b/sql/pgsql/migrations/16.sql @@ -0,0 +1,6 @@ +alter table ttrss_feeds add column auth_pass_encrypted boolean; +update ttrss_feeds set auth_pass_encrypted = false; +alter table ttrss_feeds alter column auth_pass_encrypted set not null; +alter table ttrss_feeds alter column auth_pass_encrypted set default false; + +update ttrss_version set schema_version = 16; diff --git a/sql/pgsql/migrations/17.sql b/sql/pgsql/migrations/17.sql new file mode 100644 index 000000000..f5d39f33d --- /dev/null +++ b/sql/pgsql/migrations/17.sql @@ -0,0 +1,13 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_PREFS_ACTIVE_TAB', 2, '', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_INFOBOX_DISABLE_OVERLAY', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('STRIP_UNSAFE_TAGS', 1, 'true', 'Strip unsafe tags from articles', 3, +'Strip all but most common HTML tags when reading articles.'); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('BLACKLISTED_TAGS', 2, 'main, generic, misc', 'Blacklisted tags', 3, +'When auto-detecting tags in articles these tags will not be applied (comma-separated list).'); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_SEARCH_TOOLBAR', 1, 'false', 'Enable search toolbar',2); + +update ttrss_version set schema_version = 17; diff --git a/sql/pgsql/migrations/18.sql b/sql/pgsql/migrations/18.sql new file mode 100644 index 000000000..e03c96fd0 --- /dev/null +++ b/sql/pgsql/migrations/18.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_PREFS_ENABLE_PAGINATION', 2, '', '', 1); + +update ttrss_version set schema_version = 18; diff --git a/sql/pgsql/migrations/19.sql b/sql/pgsql/migrations/19.sql new file mode 100644 index 000000000..15c12ca33 --- /dev/null +++ b/sql/pgsql/migrations/19.sql @@ -0,0 +1,11 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_PREFS_PUBLISH_KEY', 2, '', '', 1); + +alter table ttrss_user_entries add column published boolean; +update ttrss_user_entries set published = false; +alter table ttrss_user_entries alter column published set not null; +alter table ttrss_user_entries alter column published set default false; + +insert into ttrss_filter_actions (id,name,description) values (5, 'publish', + 'Publish article'); + +update ttrss_version set schema_version = 19; diff --git a/sql/pgsql/migrations/20.sql b/sql/pgsql/migrations/20.sql new file mode 100644 index 000000000..347654512 --- /dev/null +++ b/sql/pgsql/migrations/20.sql @@ -0,0 +1,3 @@ +UPDATE ttrss_prefs SET help_text = 'This option is useful when you are reading several planet-type aggregators with partially colliding userbase. When disabled, it forces same posts from different feeds to appear only once.' WHERE pref_name = 'ALLOW_DUPLICATE_POSTS'; + +update ttrss_version set schema_version = 20; diff --git a/sql/pgsql/migrations/21.sql b/sql/pgsql/migrations/21.sql new file mode 100644 index 000000000..f64d82228 --- /dev/null +++ b/sql/pgsql/migrations/21.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('FRESH_ARTICLE_MAX_AGE', 3, '24', 'Maximum age of fresh articles (in hours)',2); + +update ttrss_version set schema_version = 21; diff --git a/sql/pgsql/migrations/22.sql b/sql/pgsql/migrations/22.sql new file mode 100644 index 000000000..96e67e4a0 --- /dev/null +++ b/sql/pgsql/migrations/22.sql @@ -0,0 +1,8 @@ +create index ttrss_entries_date_entered_index on ttrss_entries(date_entered); + +alter table ttrss_feeds add column cache_images boolean; +update ttrss_feeds set cache_images = false; +alter table ttrss_feeds alter column cache_images set not null; +alter table ttrss_feeds alter column cache_images set default false; + +update ttrss_version set schema_version = 22; diff --git a/sql/pgsql/migrations/23.sql b/sql/pgsql/migrations/23.sql new file mode 100644 index 000000000..fe54e757d --- /dev/null +++ b/sql/pgsql/migrations/23.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('DIGEST_CATCHUP', 1, 'false', 'Mark articles in e-mail digest as read',1); + +update ttrss_version set schema_version = 23; diff --git a/sql/pgsql/migrations/24.sql b/sql/pgsql/migrations/24.sql new file mode 100644 index 000000000..f24e34062 --- /dev/null +++ b/sql/pgsql/migrations/24.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('CDM_EXPANDED', 1, 'true', 'Automatically expand articles in combined mode',3); + +update ttrss_version set schema_version = 24; diff --git a/sql/pgsql/migrations/25.sql b/sql/pgsql/migrations/25.sql new file mode 100644 index 000000000..c3f3d5ea4 --- /dev/null +++ b/sql/pgsql/migrations/25.sql @@ -0,0 +1,3 @@ +insert into ttrss_themes (theme_name, theme_path) values ('Three-pane', '3pane'); + +update ttrss_version set schema_version = 25; diff --git a/sql/pgsql/migrations/26.sql b/sql/pgsql/migrations/26.sql new file mode 100644 index 000000000..c7c1be691 --- /dev/null +++ b/sql/pgsql/migrations/26.sql @@ -0,0 +1,13 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('PURGE_UNREAD_ARTICLES', 1, 'true', 'Purge unread articles',3); + +alter table ttrss_users add column created timestamp; +alter table ttrss_users alter column created set default null; + +create table ttrss_enclosures (id serial not null primary key, + content_url text not null, + content_type varchar(250) not null, + title text not null, + duration text not null, + post_id integer references ttrss_entries(id) ON DELETE cascade NOT NULL); + +update ttrss_version set schema_version = 26; diff --git a/sql/pgsql/migrations/27.sql b/sql/pgsql/migrations/27.sql new file mode 100644 index 000000000..d0baaebc0 --- /dev/null +++ b/sql/pgsql/migrations/27.sql @@ -0,0 +1,4 @@ +alter table ttrss_feeds add column last_viewed timestamp; +alter table ttrss_feeds alter column last_viewed set default null; + +update ttrss_version set schema_version = 27; diff --git a/sql/pgsql/migrations/28.sql b/sql/pgsql/migrations/28.sql new file mode 100644 index 000000000..ffdf8448e --- /dev/null +++ b/sql/pgsql/migrations/28.sql @@ -0,0 +1,3 @@ +UPDATE ttrss_prefs SET def_value = 'main, generic, misc, uncategorized, blog, blogroll, general, news' WHERE pref_name = 'BLACKLISTED_TAGS'; + +update ttrss_version set schema_version = 28; diff --git a/sql/pgsql/migrations/29.sql b/sql/pgsql/migrations/29.sql new file mode 100644 index 000000000..89b5e94a0 --- /dev/null +++ b/sql/pgsql/migrations/29.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('HIDE_READ_SHOWS_SPECIAL', 1, 'true', 'Show special feeds when hiding read feeds',3); + +update ttrss_version set schema_version = 29; diff --git a/sql/pgsql/migrations/3.sql b/sql/pgsql/migrations/3.sql new file mode 100644 index 000000000..5b23f3366 --- /dev/null +++ b/sql/pgsql/migrations/3.sql @@ -0,0 +1,43 @@ +begin; + +alter table ttrss_entries add column num_comments integer; + +update ttrss_entries set num_comments = 0; + +alter table ttrss_entries alter column num_comments set not null; +alter table ttrss_entries alter column num_comments set default 0; + +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, + 'Display expanded list of feed articles, instead of separate displays for headlines and article content'); + +alter table ttrss_feed_categories add column collapsed boolean; + +update ttrss_feed_categories set collapsed = false; + +alter table ttrss_feed_categories alter column collapsed set not null; +alter table ttrss_feed_categories alter column collapsed set default false; + +alter table ttrss_feeds add column auth_login varchar(250); +alter table ttrss_feeds add column auth_pass varchar(250); + +update ttrss_feeds set auth_login = ''; +update ttrss_feeds set auth_pass = ''; + +alter table ttrss_feeds alter column auth_login set not null; +alter table ttrss_feeds alter column auth_login set default ''; + +alter table ttrss_feeds alter column auth_pass set not null; +alter table ttrss_feeds alter column auth_pass set default ''; + +alter table ttrss_users add column email varchar(250); + +update ttrss_users set email = ''; + +alter table ttrss_users alter column email set not null; +alter table ttrss_users alter column email set default ''; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_SEARCH_TOOLBAR', 1, 'false', 'Enable search toolbar',2); + +update ttrss_version set schema_version = 3; + +commit; diff --git a/sql/pgsql/migrations/30.sql b/sql/pgsql/migrations/30.sql new file mode 100644 index 000000000..d83eee1f1 --- /dev/null +++ b/sql/pgsql/migrations/30.sql @@ -0,0 +1,4 @@ +alter table ttrss_feeds add column last_update_started timestamp; +alter table ttrss_feeds alter column last_update_started set default null; + +update ttrss_version set schema_version = 30; diff --git a/sql/pgsql/migrations/31.sql b/sql/pgsql/migrations/31.sql new file mode 100644 index 000000000..e8d6bdb4a --- /dev/null +++ b/sql/pgsql/migrations/31.sql @@ -0,0 +1,6 @@ +alter table ttrss_feeds add column update_method integer; +update ttrss_feeds set update_method = 0; +alter table ttrss_feeds alter column update_method set not null; +alter table ttrss_feeds alter column update_method set default 0; + +update ttrss_version set schema_version = 31; diff --git a/sql/pgsql/migrations/32.sql b/sql/pgsql/migrations/32.sql new file mode 100644 index 000000000..30565555e --- /dev/null +++ b/sql/pgsql/migrations/32.sql @@ -0,0 +1,6 @@ +alter table ttrss_prefs add column access_level integer; +update ttrss_prefs set access_level = 0; +alter table ttrss_prefs alter column access_level set not null; +alter table ttrss_prefs alter column access_level set default 0; + +update ttrss_version set schema_version = 32; diff --git a/sql/pgsql/migrations/33.sql b/sql/pgsql/migrations/33.sql new file mode 100644 index 000000000..8317dc2de --- /dev/null +++ b/sql/pgsql/migrations/33.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('HIDE_FEEDLIST', 1, 'false', 'Hide feedlist',2, 'This option hides feedlist and allows it to be toggled on the fly, useful for small screens.'); + +update ttrss_version set schema_version = 33; diff --git a/sql/pgsql/migrations/34.sql b/sql/pgsql/migrations/34.sql new file mode 100644 index 000000000..c8733d430 --- /dev/null +++ b/sql/pgsql/migrations/34.sql @@ -0,0 +1,7 @@ +alter table ttrss_feeds rename column feed_url to feed_url_old; +alter table ttrss_feeds add column feed_url text; +update ttrss_feeds set feed_url = feed_url_old; +alter table ttrss_feeds alter column feed_url set not null; +alter table ttrss_feeds drop column feed_url_old; + +update ttrss_version set schema_version = 34; diff --git a/sql/pgsql/migrations/35.sql b/sql/pgsql/migrations/35.sql new file mode 100644 index 000000000..e624d66b3 --- /dev/null +++ b/sql/pgsql/migrations/35.sql @@ -0,0 +1,3 @@ +create function SUBSTRING_FOR_DATE(timestamp, int, int) RETURNS text AS 'SELECT SUBSTRING(CAST($1 AS text), $2, $3)' LANGUAGE 'sql'; + +update ttrss_version set schema_version = 35; diff --git a/sql/pgsql/migrations/36.sql b/sql/pgsql/migrations/36.sql new file mode 100644 index 000000000..1e4a460ee --- /dev/null +++ b/sql/pgsql/migrations/36.sql @@ -0,0 +1,9 @@ +alter table ttrss_user_entries add column score int; +update ttrss_user_entries set score = 0; +alter table ttrss_user_entries alter column score set not null; +alter table ttrss_user_entries alter column score set default 0; + +insert into ttrss_filter_actions (id,name,description) values (6, 'score', + 'Modify score'); + +update ttrss_version set schema_version = 36; diff --git a/sql/pgsql/migrations/37.sql b/sql/pgsql/migrations/37.sql new file mode 100644 index 000000000..4e764d7ee --- /dev/null +++ b/sql/pgsql/migrations/37.sql @@ -0,0 +1,4 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('VFEED_GROUP_BY_FEED', 1, 'false', 'Group headlines in virtual feeds',2, + 'When this option is enabled, headlines in Special feeds and Labels are grouped by feeds'); + +update ttrss_version set schema_version = 37; diff --git a/sql/pgsql/migrations/38.sql b/sql/pgsql/migrations/38.sql new file mode 100644 index 000000000..df544d03b --- /dev/null +++ b/sql/pgsql/migrations/38.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('SYNC_COUNTERS', 1, 'false', 'Prefer more accurate feedlist counters to UI speed',3); + +update ttrss_version set schema_version = 38; diff --git a/sql/pgsql/migrations/39.sql b/sql/pgsql/migrations/39.sql new file mode 100644 index 000000000..f27db09d6 --- /dev/null +++ b/sql/pgsql/migrations/39.sql @@ -0,0 +1,5 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('ENABLE_FLASH_PLAYER', 1, 'true', 'Enable inline MP3 player', 3, 'Enable the Flash-based XSPF Player to play MP3-format podcast enclosures.'); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('STRIP_IMAGES', 1, 'false', 'Do not show images in articles', 2); + +update ttrss_version set schema_version = 39; diff --git a/sql/pgsql/migrations/4.sql b/sql/pgsql/migrations/4.sql new file mode 100644 index 000000000..1f926eaeb --- /dev/null +++ b/sql/pgsql/migrations/4.sql @@ -0,0 +1,17 @@ +begin; + +alter table ttrss_feeds add column parent_feed integer; +alter table ttrss_feeds add foreign key (parent_feed) references ttrss_feeds(id) on delete set null; + +alter table ttrss_feeds add column private boolean; + +update ttrss_feeds set private = false; + +alter table ttrss_feeds alter column private set not null; +alter table ttrss_feeds alter column private set default false; + +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); + +update ttrss_version set schema_version = 4; + +commit; diff --git a/sql/pgsql/migrations/40.sql b/sql/pgsql/migrations/40.sql new file mode 100644 index 000000000..5a506cea6 --- /dev/null +++ b/sql/pgsql/migrations/40.sql @@ -0,0 +1,3 @@ +update ttrss_prefs set short_desc = 'Enable feed icons' where pref_name = 'ENABLE_FEED_ICONS'; + +update ttrss_version set schema_version = 40; diff --git a/sql/pgsql/migrations/41.sql b/sql/pgsql/migrations/41.sql new file mode 100644 index 000000000..bdb87239b --- /dev/null +++ b/sql/pgsql/migrations/41.sql @@ -0,0 +1,6 @@ +alter table ttrss_feed_categories add column order_id int; +update ttrss_feed_categories set order_id = 0; +alter table ttrss_feed_categories alter column order_id set not null; +alter table ttrss_feed_categories alter column order_id set default 0; + +update ttrss_version set schema_version = 41; diff --git a/sql/pgsql/migrations/42.sql b/sql/pgsql/migrations/42.sql new file mode 100644 index 000000000..3ae211d07 --- /dev/null +++ b/sql/pgsql/migrations/42.sql @@ -0,0 +1,3 @@ +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_VIEW_ORDER_BY', 2, 'default', '', 1); + +update ttrss_version set schema_version = 42; diff --git a/sql/pgsql/migrations/43.sql b/sql/pgsql/migrations/43.sql new file mode 100644 index 000000000..52bc365cc --- /dev/null +++ b/sql/pgsql/migrations/43.sql @@ -0,0 +1,7 @@ +alter table ttrss_labels rename column sql_exp to sql_exp_old; +alter table ttrss_labels add column sql_exp text; +update ttrss_labels set sql_exp = sql_exp_old; +alter table ttrss_labels alter column sql_exp set not null; +alter table ttrss_labels drop column sql_exp_old; + +update ttrss_version set schema_version = 43; diff --git a/sql/pgsql/migrations/44.sql b/sql/pgsql/migrations/44.sql new file mode 100644 index 000000000..a88bfede6 --- /dev/null +++ b/sql/pgsql/migrations/44.sql @@ -0,0 +1,6 @@ +create table ttrss_counters_cache ( + feed_id integer not null references ttrss_feeds(id) ON DELETE CASCADE, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + value integer not null default 0); + +update ttrss_version set schema_version = 44; diff --git a/sql/pgsql/migrations/45.sql b/sql/pgsql/migrations/45.sql new file mode 100644 index 000000000..c4b91cf48 --- /dev/null +++ b/sql/pgsql/migrations/45.sql @@ -0,0 +1,3 @@ +update ttrss_prefs set help_text = 'This option enables marking articles as read automatically in combined mode (except for Fresh articles feed) while you scroll article list.' where pref_name = 'CDM_AUTO_CATCHUP'; + +update ttrss_version set schema_version = 45; diff --git a/sql/pgsql/migrations/46.sql b/sql/pgsql/migrations/46.sql new file mode 100644 index 000000000..007824f7a --- /dev/null +++ b/sql/pgsql/migrations/46.sql @@ -0,0 +1,5 @@ + +insert into ttrss_filter_types (id,name,description) values (5, 'date', + 'Article Date'); + +update ttrss_version set schema_version = 46; diff --git a/sql/pgsql/migrations/47.sql b/sql/pgsql/migrations/47.sql new file mode 100644 index 000000000..5dbd6e07e --- /dev/null +++ b/sql/pgsql/migrations/47.sql @@ -0,0 +1,12 @@ +begin; + +alter table ttrss_filters add column filter_param varchar(200); + +update ttrss_filters set filter_param = ''; + +alter table ttrss_filters alter column filter_param set not null; +alter table ttrss_filters alter column filter_param set default ''; + +update ttrss_version set schema_version = 47; + +commit; diff --git a/sql/pgsql/migrations/48.sql b/sql/pgsql/migrations/48.sql new file mode 100644 index 000000000..e3a742ace --- /dev/null +++ b/sql/pgsql/migrations/48.sql @@ -0,0 +1,9 @@ +begin; + +create table ttrss_feedbrowser_cache ( + feed_url text not null primary key, + subscribers integer not null); + +update ttrss_version set schema_version = 48; + +commit; diff --git a/sql/pgsql/migrations/49.sql b/sql/pgsql/migrations/49.sql new file mode 100644 index 000000000..6efd91925 --- /dev/null +++ b/sql/pgsql/migrations/49.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_counters_cache add column updated timestamp; +update ttrss_counters_cache set updated = NOW(); +alter table ttrss_counters_cache alter column updated set not null; + +update ttrss_version set schema_version = 49; + +commit; diff --git a/sql/pgsql/migrations/5.sql b/sql/pgsql/migrations/5.sql new file mode 100644 index 000000000..1292daa3c --- /dev/null +++ b/sql/pgsql/migrations/5.sql @@ -0,0 +1,10 @@ +begin; + +create table ttrss_scheduled_updates (id serial not null primary key, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + feed_id integer default null references ttrss_feeds(id) ON DELETE CASCADE, + entered timestamp not null default NOW()); + +update ttrss_version set schema_version = 5; + +commit; diff --git a/sql/pgsql/migrations/50.sql b/sql/pgsql/migrations/50.sql new file mode 100644 index 000000000..86846242c --- /dev/null +++ b/sql/pgsql/migrations/50.sql @@ -0,0 +1,19 @@ +begin; + +drop table ttrss_counters_cache; + +create table ttrss_counters_cache ( + feed_id integer not null, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + updated timestamp not null, + value integer not null default 0); + +create table ttrss_cat_counters_cache ( + feed_id integer not null, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + updated timestamp not null, + value integer not null default 0); + +update ttrss_version set schema_version = 50; + +commit; diff --git a/sql/pgsql/migrations/51.sql b/sql/pgsql/migrations/51.sql new file mode 100644 index 000000000..dd566072b --- /dev/null +++ b/sql/pgsql/migrations/51.sql @@ -0,0 +1,18 @@ +begin; + +create table ttrss_labels2 (id serial not null primary key, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + caption varchar(250) not null +); + +create table ttrss_user_labels2 ( + label_id integer not null references ttrss_labels2(id) ON DELETE CASCADE, + article_id integer not null references ttrss_entries(id) ON DELETE CASCADE +); + +insert into ttrss_filter_actions (id,name,description) values (7, 'label', + 'Assign label'); + +update ttrss_version set schema_version = 51; + +commit; diff --git a/sql/pgsql/migrations/52.sql b/sql/pgsql/migrations/52.sql new file mode 100644 index 000000000..f7165e795 --- /dev/null +++ b/sql/pgsql/migrations/52.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_feedbrowser_cache add column title text; +update ttrss_feedbrowser_cache set title = ''; +alter table ttrss_feedbrowser_cache alter column title set not null; + +update ttrss_version set schema_version = 52; + +commit; diff --git a/sql/pgsql/migrations/53.sql b/sql/pgsql/migrations/53.sql new file mode 100644 index 000000000..adcf6fdec --- /dev/null +++ b/sql/pgsql/migrations/53.sql @@ -0,0 +1,15 @@ +begin; + +alter table ttrss_labels2 add column fg_color varchar(15); +update ttrss_labels2 set fg_color = ''; +alter table ttrss_labels2 alter column fg_color set not null; +alter table ttrss_labels2 alter column fg_color set default ''; + +alter table ttrss_labels2 add column bg_color varchar(15); +update ttrss_labels2 set bg_color = ''; +alter table ttrss_labels2 alter column bg_color set not null; +alter table ttrss_labels2 alter column bg_color set default ''; + +update ttrss_version set schema_version = 53; + +commit; diff --git a/sql/pgsql/migrations/54.sql b/sql/pgsql/migrations/54.sql new file mode 100644 index 000000000..28d3f8ee4 --- /dev/null +++ b/sql/pgsql/migrations/54.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('ENABLE_OFFLINE_READING', 1, 'false', 'Enable offline reading',1, 'Synchronize new articles for offline reading using Google Gears.'); + +update ttrss_version set schema_version = 54; + +commit; diff --git a/sql/pgsql/migrations/55.sql b/sql/pgsql/migrations/55.sql new file mode 100644 index 000000000..75f55b3c3 --- /dev/null +++ b/sql/pgsql/migrations/55.sql @@ -0,0 +1,7 @@ +begin; + +alter table ttrss_user_entries add column note text; + +update ttrss_version set schema_version = 55; + +commit; diff --git a/sql/pgsql/migrations/56.sql b/sql/pgsql/migrations/56.sql new file mode 100644 index 000000000..d6d57f03a --- /dev/null +++ b/sql/pgsql/migrations/56.sql @@ -0,0 +1,5 @@ +begin; + +update ttrss_version set schema_version = 56; + +commit; diff --git a/sql/pgsql/migrations/57.sql b/sql/pgsql/migrations/57.sql new file mode 100644 index 000000000..3dd7898b9 --- /dev/null +++ b/sql/pgsql/migrations/57.sql @@ -0,0 +1,6 @@ +alter table ttrss_feeds add column always_display_enclosures boolean; +update ttrss_feeds set always_display_enclosures = false; +alter table ttrss_feeds alter column always_display_enclosures set not null; +alter table ttrss_feeds alter column always_display_enclosures set default false; + +update ttrss_version set schema_version = 57; diff --git a/sql/pgsql/migrations/58.sql b/sql/pgsql/migrations/58.sql new file mode 100644 index 000000000..61173c1d5 --- /dev/null +++ b/sql/pgsql/migrations/58.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_API_ACCESS', 1, 'false', 'Enable external API', 3); + +update ttrss_version set schema_version = 58; + +commit; diff --git a/sql/pgsql/migrations/59.sql b/sql/pgsql/migrations/59.sql new file mode 100644 index 000000000..d51fe8f65 --- /dev/null +++ b/sql/pgsql/migrations/59.sql @@ -0,0 +1,13 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_SPECIAL', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_LABELS', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_FEEDLIST', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_UNCAT', 1, 'false', '', 1); + +update ttrss_version set schema_version = 59; + +commit; diff --git a/sql/pgsql/migrations/6.sql b/sql/pgsql/migrations/6.sql new file mode 100644 index 000000000..6c406495d --- /dev/null +++ b/sql/pgsql/migrations/6.sql @@ -0,0 +1,24 @@ +begin; + +alter table ttrss_entries add column author varchar(250); + +update ttrss_entries set author = ''; + +alter table ttrss_entries alter column author set not null; +alter table ttrss_entries alter column author set default ''; + +create table ttrss_sessions (id varchar(250) unique not null primary key, + data text, + expire integer not null, + ip_address varchar(15) not null default ''); + +create index ttrss_sessions_id_index on ttrss_sessions(id); +create index ttrss_sessions_expire_index on ttrss_sessions(expire); + +delete from ttrss_prefs where pref_name = 'ENABLE_SPLASH'; + +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); + +update ttrss_version set schema_version = 6; + +commit; diff --git a/sql/pgsql/migrations/60.sql b/sql/pgsql/migrations/60.sql new file mode 100644 index 000000000..bcf2fdefb --- /dev/null +++ b/sql/pgsql/migrations/60.sql @@ -0,0 +1,18 @@ +begin; + +alter table ttrss_user_entries alter column feed_id drop not null; + +create table ttrss_archived_feeds (id integer not null primary key, + owner_uid integer not null references ttrss_users(id) on delete cascade, + title varchar(200) not null, + feed_url text not null, + site_url varchar(250) not null default ''); + +alter table ttrss_user_entries add column orig_feed_id integer; +update ttrss_user_entries set orig_feed_id = NULL; + +alter table ttrss_user_entries add constraint "$4" FOREIGN KEY (orig_feed_id) REFERENCES ttrss_archived_feeds(id) ON DELETE SET NULL; + +update ttrss_version set schema_version = 60; + +commit; diff --git a/sql/pgsql/migrations/61.sql b/sql/pgsql/migrations/61.sql new file mode 100644 index 000000000..209a7ba55 --- /dev/null +++ b/sql/pgsql/migrations/61.sql @@ -0,0 +1,9 @@ +begin; + +update ttrss_prefs set short_desc = 'Amount of articles to display at once', help_text = '', def_value = '30' where pref_name = 'DEFAULT_ARTICLE_LIMIT'; + +update ttrss_user_prefs set value = '30' where pref_name = 'DEFAULT_ARTICLE_LIMIT' and value = '0'; + +update ttrss_version set schema_version = 61; + +commit; diff --git a/sql/pgsql/migrations/62.sql b/sql/pgsql/migrations/62.sql new file mode 100644 index 000000000..4cc904b0a --- /dev/null +++ b/sql/pgsql/migrations/62.sql @@ -0,0 +1,13 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_ENABLE_CATS', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_SHOW_IMAGES', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_HIDE_READ', 1, 'false', '', 1); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_SORT_FEEDS_UNREAD', 1, 'false', '', 1); + +update ttrss_version set schema_version = 62; + +commit; diff --git a/sql/pgsql/migrations/63.sql b/sql/pgsql/migrations/63.sql new file mode 100644 index 000000000..90bde3154 --- /dev/null +++ b/sql/pgsql/migrations/63.sql @@ -0,0 +1,16 @@ +begin; + +create table ttrss_settings_profiles(id serial not null primary key, + title varchar(250) not null, + owner_uid integer not null references ttrss_users(id) on delete cascade); + +alter table ttrss_user_prefs add column profile integer; +update ttrss_user_prefs set profile = NULL; + +alter table ttrss_user_prefs add constraint "$3" FOREIGN KEY (profile) REFERENCES ttrss_settings_profiles(id) ON DELETE CASCADE; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_THEME_ID', 3, '0', '', 1); + +update ttrss_version set schema_version = 63; + +commit; diff --git a/sql/pgsql/migrations/64.sql b/sql/pgsql/migrations/64.sql new file mode 100644 index 000000000..51f0be7e2 --- /dev/null +++ b/sql/pgsql/migrations/64.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set type_id = 2 where pref_name = '_THEME_ID'; + +update ttrss_version set schema_version = 64; + +commit; diff --git a/sql/pgsql/migrations/65.sql b/sql/pgsql/migrations/65.sql new file mode 100644 index 000000000..ce21de898 --- /dev/null +++ b/sql/pgsql/migrations/65.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set short_desc = 'Default feed update interval' where pref_name = 'DEFAULT_UPDATE_INTERVAL'; + +update ttrss_version set schema_version = 65; + +commit; diff --git a/sql/pgsql/migrations/66.sql b/sql/pgsql/migrations/66.sql new file mode 100644 index 000000000..f90c0a157 --- /dev/null +++ b/sql/pgsql/migrations/66.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_filter_types (id, name, description) values (6, 'author', 'Author'); + +update ttrss_version set schema_version = 66; + +commit; diff --git a/sql/pgsql/migrations/67.sql b/sql/pgsql/migrations/67.sql new file mode 100644 index 000000000..1e2aa4429 --- /dev/null +++ b/sql/pgsql/migrations/67.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_entries add column date_updated timestamp; +update ttrss_entries set date_updated = date_entered; +alter table ttrss_entries alter column date_updated set not null; + +update ttrss_version set schema_version = 67; + +commit; diff --git a/sql/pgsql/migrations/68.sql b/sql/pgsql/migrations/68.sql new file mode 100644 index 000000000..6d56df0f9 --- /dev/null +++ b/sql/pgsql/migrations/68.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('USER_TIMEZONE', 2, 'UTC', 'User timezone', 1); + +update ttrss_version set schema_version = 68; + +commit; diff --git a/sql/pgsql/migrations/69.sql b/sql/pgsql/migrations/69.sql new file mode 100644 index 000000000..cae0ff50f --- /dev/null +++ b/sql/pgsql/migrations/69.sql @@ -0,0 +1,11 @@ +begin; + +create table ttrss_access_keys (id serial not null primary key, + access_key varchar(250) not null, + feed_id varchar(250) not null, + is_cat bool not null default false, + owner_uid integer not null references ttrss_users(id) on delete cascade); + +update ttrss_version set schema_version = 69; + +commit; diff --git a/sql/pgsql/migrations/7.sql b/sql/pgsql/migrations/7.sql new file mode 100644 index 000000000..51437e4fa --- /dev/null +++ b/sql/pgsql/migrations/7.sql @@ -0,0 +1,22 @@ +begin; + +alter table ttrss_feeds add column rtl_content boolean; + +update ttrss_feeds set rtl_content = false; + +alter table ttrss_feeds alter column rtl_content set not null; +alter table ttrss_feeds alter column rtl_content set default false; + +alter table ttrss_sessions drop column ip_address; + +delete from ttrss_user_prefs where pref_name = 'DISPLAY_FEEDLIST_ACTIONS'; +delete from ttrss_prefs where pref_name = 'DISPLAY_FEEDLIST_ACTIONS'; + +delete from ttrss_user_prefs where pref_name = 'ENABLE_PREFS_CATCHUP_UNCATCHUP'; +delete from ttrss_prefs where pref_name = 'ENABLE_PREFS_CATCHUP_UNCATCHUP'; + +alter table ttrss_filters drop column description; + +update ttrss_version set schema_version = 7; + +commit; diff --git a/sql/pgsql/migrations/70.sql b/sql/pgsql/migrations/70.sql new file mode 100644 index 000000000..b970a861c --- /dev/null +++ b/sql/pgsql/migrations/70.sql @@ -0,0 +1,6 @@ +alter table ttrss_users add column full_name varchar(250); +update ttrss_users set full_name = ''; +alter table ttrss_users alter column full_name set not null; +alter table ttrss_users alter column full_name set default ''; + +update ttrss_version set schema_version = 70; diff --git a/sql/pgsql/migrations/71.sql b/sql/pgsql/migrations/71.sql new file mode 100644 index 000000000..2061ada2b --- /dev/null +++ b/sql/pgsql/migrations/71.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_filter_types (id,name,description) values (7, 'tag', 'Article Tags'); + +update ttrss_version set schema_version = 71; + +commit; diff --git a/sql/pgsql/migrations/72.sql b/sql/pgsql/migrations/72.sql new file mode 100644 index 000000000..65e4d2e0a --- /dev/null +++ b/sql/pgsql/migrations/72.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_user_entries add column tag_cache text; +update ttrss_user_entries set tag_cache = ''; +alter table ttrss_user_entries alter column tag_cache set not null; + +update ttrss_version set schema_version = 72; + +commit; diff --git a/sql/pgsql/migrations/73.sql b/sql/pgsql/migrations/73.sql new file mode 100644 index 000000000..051578d22 --- /dev/null +++ b/sql/pgsql/migrations/73.sql @@ -0,0 +1,8 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('SORT_HEADLINES_BY_FEED_DATE', 1, 'true', 'Sort headlines by feed date',3, + 'Use feed-specified date to sort headlines instead of local import date.'); + +update ttrss_version set schema_version = 73; + +commit; diff --git a/sql/pgsql/migrations/74.sql b/sql/pgsql/migrations/74.sql new file mode 100644 index 000000000..22c95c5d3 --- /dev/null +++ b/sql/pgsql/migrations/74.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_user_entries add column label_cache text; +update ttrss_user_entries set label_cache = ''; +alter table ttrss_user_entries alter column label_cache set not null; + +update ttrss_version set schema_version = 74; + +commit; diff --git a/sql/pgsql/migrations/75.sql b/sql/pgsql/migrations/75.sql new file mode 100644 index 000000000..3291d652c --- /dev/null +++ b/sql/pgsql/migrations/75.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_feeds add column order_id integer; +update ttrss_feeds set order_id = 0; +alter table ttrss_feeds alter column order_id set not null; +alter table ttrss_feeds alter column order_id set default 0; + +update ttrss_version set schema_version = 75; + +commit; diff --git a/sql/pgsql/migrations/76.sql b/sql/pgsql/migrations/76.sql new file mode 100644 index 000000000..b7cc12c7a --- /dev/null +++ b/sql/pgsql/migrations/76.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_users add column twitter_oauth text; +alter table ttrss_users alter column twitter_oauth set default null; + +update ttrss_version set schema_version = 76; + +commit; diff --git a/sql/pgsql/migrations/77.sql b/sql/pgsql/migrations/77.sql new file mode 100644 index 000000000..944f31d84 --- /dev/null +++ b/sql/pgsql/migrations/77.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('USER_STYLESHEET', 2, '', 'Customize stylesheet', 2, 'Customize CSS stylesheet to your liking'); + +update ttrss_version set schema_version = 77; + +commit; diff --git a/sql/pgsql/migrations/78.sql b/sql/pgsql/migrations/78.sql new file mode 100644 index 000000000..80e9e782a --- /dev/null +++ b/sql/pgsql/migrations/78.sql @@ -0,0 +1,15 @@ +begin; + +delete from ttrss_user_prefs where pref_name in ('HIDE_FEEDLIST', 'SYNC_COUNTERS', 'ENABLE_LABELS', + 'ENABLE_SEARCH_TOOLBAR', 'ENABLE_FEED_ICONS', + 'ENABLE_OFFLINE_READING', 'EXTENDED_FEEDLIST', 'OPEN_LINKS_IN_NEW_WINDOW', + 'USER_STYLESHEET_URL', 'ENABLE_FLASH_PLAYER'); + +delete from ttrss_prefs where pref_name in ('HIDE_FEEDLIST', 'SYNC_COUNTERS', 'ENABLE_LABELS', + 'ENABLE_SEARCH_TOOLBAR', 'ENABLE_FEED_ICONS', + 'ENABLE_OFFLINE_READING', 'EXTENDED_FEEDLIST', 'OPEN_LINKS_IN_NEW_WINDOW', + 'USER_STYLESHEET_URL', 'ENABLE_FLASH_PLAYER'); + +update ttrss_version set schema_version = 78; + +commit; diff --git a/sql/pgsql/migrations/79.sql b/sql/pgsql/migrations/79.sql new file mode 100644 index 000000000..7e8c31d49 --- /dev/null +++ b/sql/pgsql/migrations/79.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_BROWSE_CATS', 1, 'true', '', 1); + +update ttrss_version set schema_version = 79; + +commit; diff --git a/sql/pgsql/migrations/8.sql b/sql/pgsql/migrations/8.sql new file mode 100644 index 000000000..ab7a7df33 --- /dev/null +++ b/sql/pgsql/migrations/8.sql @@ -0,0 +1,17 @@ +begin; + +insert into ttrss_themes (theme_name, theme_path) values ('Old-skool', 'compat'); + +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, + 'When "Mark as read" button is clicked in toolbar, automatically open next feed with unread articles.'); + +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); + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('EXTENDED_FEEDLIST', 1, 'false', 'Show additional information in feedlist',2); + +insert into ttrss_filter_actions (id,name,description) values (3, 'mark', + 'Set starred'); + +update ttrss_version set schema_version = 8; + +commit; diff --git a/sql/pgsql/migrations/80.sql b/sql/pgsql/migrations/80.sql new file mode 100644 index 000000000..df2295c42 --- /dev/null +++ b/sql/pgsql/migrations/80.sql @@ -0,0 +1,9 @@ +begin; + +update ttrss_prefs set section_id = 3, + short_desc = 'Automatically mark articles as read', + help_text = 'Mark articles as read automatically while you scroll article list.' where pref_name = 'CDM_AUTO_CATCHUP'; + +update ttrss_version set schema_version = 80; + +commit; diff --git a/sql/pgsql/migrations/81.sql b/sql/pgsql/migrations/81.sql new file mode 100644 index 000000000..800ceede0 --- /dev/null +++ b/sql/pgsql/migrations/81.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_filter_actions set description = 'Delete article' where name = 'filter'; + +update ttrss_version set schema_version = 81; + +commit; diff --git a/sql/pgsql/migrations/82.sql b/sql/pgsql/migrations/82.sql new file mode 100644 index 000000000..80ca24e72 --- /dev/null +++ b/sql/pgsql/migrations/82.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('SSL_CERT_SERIAL', 2, '', 'Login with an SSL certificate',3, 'Click to register your SSL client certificate with tt-rss'); + +update ttrss_version set schema_version = 82; + +commit; diff --git a/sql/pgsql/migrations/83.sql b/sql/pgsql/migrations/83.sql new file mode 100644 index 000000000..a89086551 --- /dev/null +++ b/sql/pgsql/migrations/83.sql @@ -0,0 +1,32 @@ +begin; + +alter table ttrss_feeds add column mark_unread_on_update boolean; +update ttrss_feeds set mark_unread_on_update = false; +alter table ttrss_feeds alter column mark_unread_on_update set not null; +alter table ttrss_feeds alter column mark_unread_on_update set default false; + +alter table ttrss_feeds add column strip_images boolean; +update ttrss_feeds set strip_images = false; +alter table ttrss_feeds alter column strip_images set not null; +alter table ttrss_feeds alter column strip_images set default false; + +alter table ttrss_feeds add column update_on_checksum_change boolean; +update ttrss_feeds set update_on_checksum_change = false; +alter table ttrss_feeds alter column update_on_checksum_change set not null; +alter table ttrss_feeds alter column update_on_checksum_change set default false; + +DELETE FROM ttrss_user_prefs WHERE pref_name IN ('HIDE_FEEDLIST', 'SYNC_COUNTERS', 'ENABLE_LABELS', 'ENABLE_SEARCH_TOOLBAR', 'ENABLE_FEED_ICONS', 'ENABLE_OFFLINE_READING', 'EXTENDED_FEEDLIST', 'OPEN_LINKS_IN_NEW_WINDOW', 'ENABLE_FLASH_PLAYER', 'HEADLINES_SMART_DATE', 'MARK_UNREAD_ON_UPDATE', 'UPDATE_POST_ON_CHECKSUM_CHANGE'); + +DELETE FROM ttrss_prefs WHERE pref_name IN ('HIDE_FEEDLIST', 'SYNC_COUNTERS', 'ENABLE_LABELS', 'ENABLE_SEARCH_TOOLBAR', 'ENABLE_FEED_ICONS', 'ENABLE_OFFLINE_READING', 'EXTENDED_FEEDLIST', 'OPEN_LINKS_IN_NEW_WINDOW', 'ENABLE_FLASH_PLAYER', 'HEADLINES_SMART_DATE', 'MARK_UNREAD_ON_UPDATE', 'UPDATE_POST_ON_CHECKSUM_CHANGE'); + +alter table ttrss_feeds add column pubsub_state integer; +update ttrss_feeds set pubsub_state = 0; +alter table ttrss_feeds alter column pubsub_state set not null; +alter table ttrss_feeds alter column pubsub_state set default 0; + +alter table ttrss_users drop column theme_id; +drop table ttrss_themes; + +update ttrss_version set schema_version = 83; + +commit; diff --git a/sql/pgsql/migrations/84.sql b/sql/pgsql/migrations/84.sql new file mode 100644 index 000000000..55a7caf71 --- /dev/null +++ b/sql/pgsql/migrations/84.sql @@ -0,0 +1,22 @@ +begin; + +create table ttrss_linked_instances (id serial not null primary key, + last_connected timestamp not null, + last_status_in integer not null, + last_status_out integer not null, + access_key varchar(250) not null unique, + access_url text not null); + +create table ttrss_linked_feeds ( + feed_url text not null, + title text not null, + created timestamp not null, + updated timestamp not null, + instance_id integer not null references ttrss_linked_instances(id) ON DELETE CASCADE, + subscribers integer not null); + +drop table ttrss_scheduled_updates; + +update ttrss_version set schema_version = 84; + +commit; diff --git a/sql/pgsql/migrations/85.sql b/sql/pgsql/migrations/85.sql new file mode 100644 index 000000000..a0a782280 --- /dev/null +++ b/sql/pgsql/migrations/85.sql @@ -0,0 +1,13 @@ +begin; + +alter table ttrss_feedbrowser_cache add column site_url text; +update ttrss_feedbrowser_cache set site_url = ''; +alter table ttrss_feedbrowser_cache alter column site_url set not null; + +alter table ttrss_linked_feeds add column site_url text; +update ttrss_linked_feeds set site_url = ''; +alter table ttrss_linked_feeds alter column site_url set not null; + +update ttrss_version set schema_version = 85; + +commit; diff --git a/sql/pgsql/migrations/86.sql b/sql/pgsql/migrations/86.sql new file mode 100644 index 000000000..1f311b6d7 --- /dev/null +++ b/sql/pgsql/migrations/86.sql @@ -0,0 +1,9 @@ +begin; + +alter table ttrss_user_entries add column uuid varchar(200); +update ttrss_user_entries set uuid = ''; +alter table ttrss_user_entries alter column uuid set not null; + +update ttrss_version set schema_version = 86; + +commit; diff --git a/sql/pgsql/migrations/87.sql b/sql/pgsql/migrations/87.sql new file mode 100644 index 000000000..86d0c4f61 --- /dev/null +++ b/sql/pgsql/migrations/87.sql @@ -0,0 +1,14 @@ +begin; + +alter table ttrss_filters add column cat_filter boolean; +update ttrss_filters set cat_filter = false; +alter table ttrss_filters alter column cat_filter set not null; +alter table ttrss_filters alter column cat_filter set default false; + +alter table ttrss_filters add column cat_id integer; + +alter table ttrss_filters add constraint "$5" FOREIGN KEY (cat_id) REFERENCES ttrss_feed_categories(id) ON DELETE CASCADE; + +update ttrss_version set schema_version = 87; + +commit; diff --git a/sql/pgsql/migrations/88.sql b/sql/pgsql/migrations/88.sql new file mode 100644 index 000000000..df6ec8fb7 --- /dev/null +++ b/sql/pgsql/migrations/88.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_users add column salt varchar(250); +update ttrss_users set salt = ''; +alter table ttrss_users alter column salt set not null; +alter table ttrss_users alter column salt set default ''; + +update ttrss_version set schema_version = 88; + +commit; diff --git a/sql/pgsql/migrations/89.sql b/sql/pgsql/migrations/89.sql new file mode 100644 index 000000000..9727a9c42 --- /dev/null +++ b/sql/pgsql/migrations/89.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('DIGEST_PREFERRED_TIME', 2, '00:00', 'Try to send digests around specified time', 1, 'Uses UTC timezone'); + +update ttrss_version set schema_version = 89; + +commit; diff --git a/sql/pgsql/migrations/9.sql b/sql/pgsql/migrations/9.sql new file mode 100644 index 000000000..9624403aa --- /dev/null +++ b/sql/pgsql/migrations/9.sql @@ -0,0 +1,33 @@ +begin; + +alter table ttrss_feeds add column hidden boolean; +update ttrss_feeds set hidden = false; +alter table ttrss_feeds alter column hidden set not null; +alter table ttrss_feeds alter column hidden set default false; + +alter table ttrss_users add column email_digest boolean; +update ttrss_users set email_digest = false; +alter table ttrss_users alter column email_digest set not null; +alter table ttrss_users alter column email_digest set default false; + +alter table ttrss_users add column last_digest_sent timestamp; +update ttrss_users set last_digest_sent = NULL; +alter table ttrss_users alter column last_digest_sent set default NULL; + +alter table ttrss_filters add column enabled boolean; +update ttrss_filters set enabled = true; +alter table ttrss_filters alter column enabled set not null; +alter table ttrss_filters alter column enabled set default true; + +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); + +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); + +update ttrss_prefs SET section_id = 3 WHERE pref_name = 'ENABLE_SEARCH_TOOLBAR'; +update ttrss_prefs SET section_id = 3 WHERE pref_name = 'ENABLE_FEED_ICONS'; +update ttrss_prefs SET section_id = 3 WHERE pref_name = 'EXTENDED_FEEDLIST'; + +update ttrss_version set schema_version = 9; + +commit; + diff --git a/sql/pgsql/migrations/90.sql b/sql/pgsql/migrations/90.sql new file mode 100644 index 000000000..b7935535e --- /dev/null +++ b/sql/pgsql/migrations/90.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_PREFS_SHOW_EMPTY_CATS', 1, 'false', '', 1); + +update ttrss_version set schema_version = 90; + +commit; diff --git a/sql/pgsql/migrations/91.sql b/sql/pgsql/migrations/91.sql new file mode 100644 index 000000000..791d43063 --- /dev/null +++ b/sql/pgsql/migrations/91.sql @@ -0,0 +1,7 @@ +begin; + +update ttrss_prefs set help_text = 'Automatically open next feed with unread articles after marking one as read' where pref_name = 'ON_CATCHUP_SHOW_NEXT_FEED'; + +update ttrss_version set schema_version = 91; + +commit; diff --git a/sql/pgsql/migrations/92.sql b/sql/pgsql/migrations/92.sql new file mode 100644 index 000000000..b683d514d --- /dev/null +++ b/sql/pgsql/migrations/92.sql @@ -0,0 +1,8 @@ +begin; + +alter table ttrss_feeds add column favicon_last_checked timestamp; +alter table ttrss_feeds alter column favicon_last_checked set default null; + +update ttrss_version set schema_version = 92; + +commit; diff --git a/sql/pgsql/migrations/93.sql b/sql/pgsql/migrations/93.sql new file mode 100644 index 000000000..0374c0b0a --- /dev/null +++ b/sql/pgsql/migrations/93.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_feed_categories add column parent_cat integer; +update ttrss_feed_categories set parent_cat = NULL; + +alter table ttrss_feed_categories add constraint "$2" FOREIGN KEY (parent_cat) REFERENCES ttrss_feed_categories(id) ON DELETE SET NULL; + +update ttrss_version set schema_version = 93; + +commit; diff --git a/sql/pgsql/migrations/94.sql b/sql/pgsql/migrations/94.sql new file mode 100644 index 000000000..0f76c4c5f --- /dev/null +++ b/sql/pgsql/migrations/94.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_INCLUDE_CHILDREN', 1, 'false', '', 1); + +update ttrss_version set schema_version = 94; + +commit; diff --git a/sql/pgsql/migrations/95.sql b/sql/pgsql/migrations/95.sql new file mode 100644 index 000000000..014463a85 --- /dev/null +++ b/sql/pgsql/migrations/95.sql @@ -0,0 +1,39 @@ +begin; + +update ttrss_prefs set short_desc = 'Purge articles after this number of days (0 - disables)' +where pref_name = 'PURGE_OLD_DAYS'; + +update ttrss_prefs set section_id = 1 where pref_name = 'ENABLE_API_ACCESS'; + +update ttrss_prefs set section_id = 2 where pref_name = 'CONFIRM_FEED_CATCHUP'; +update ttrss_prefs set section_id = 2 where pref_name = 'CDM_EXPANDED'; +update ttrss_prefs set section_id = 2 where pref_name = 'CDM_AUTO_CATCHUP'; +update ttrss_prefs set section_id = 2 where pref_name = 'SORT_HEADLINES_BY_FEED_DATE'; +update ttrss_prefs set section_id = 2 where pref_name = 'HIDE_READ_SHOWS_SPECIAL'; + +insert into ttrss_prefs_sections (id, section_name) values (4, 'Digest'); + +update ttrss_prefs set section_id = 4 where pref_name = 'DIGEST_ENABLE'; +update ttrss_prefs set section_id = 4 where pref_name = 'DIGEST_PREFERRED_TIME'; +update ttrss_prefs set section_id = 4 where pref_name = 'DIGEST_CATCHUP'; + +alter table ttrss_prefs_sections add column order_id integer; +update ttrss_prefs_sections set order_id = 0; +alter table ttrss_prefs_sections alter column order_id set not null; + +update ttrss_prefs_sections set order_id = 0 where id = 1; +update ttrss_prefs_sections set order_id = 1 where id = 2; +update ttrss_prefs_sections set order_id = 2 where id = 4; +update ttrss_prefs_sections set order_id = 3 where id = 3; + +update ttrss_prefs set access_level = 1 where pref_name in ('ON_CATCHUP_SHOW_NEXT_FEED', + 'SORT_HEADLINES_BY_FEED_DATE', + 'VFEED_GROUP_BY_FEED', + 'FRESH_ARTICLE_MAX_AGE', + 'CDM_EXPANDED', + 'SHOW_CONTENT_PREVIEW', + 'HIDE_READ_SHOWS_SPECIAL'); + +update ttrss_version set schema_version = 95; + +commit; diff --git a/sql/pgsql/migrations/96.sql b/sql/pgsql/migrations/96.sql new file mode 100644 index 000000000..e3cdc06be --- /dev/null +++ b/sql/pgsql/migrations/96.sql @@ -0,0 +1,23 @@ +begin; + +create table ttrss_filters2(id serial not null primary key, + owner_uid integer not null references ttrss_users(id) on delete cascade, + match_any_rule boolean not null default false, + enabled boolean not null default true); + +create table ttrss_filters2_rules(id serial not null primary key, + filter_id integer not null references ttrss_filters2(id) on delete cascade, + reg_exp varchar(250) not null, + filter_type integer not null references ttrss_filter_types(id), + feed_id integer references ttrss_feeds(id) on delete cascade default null, + cat_id integer references ttrss_feed_categories(id) on delete cascade default null, + cat_filter boolean not null default false); + +create table ttrss_filters2_actions(id serial not null primary key, + filter_id integer not null references ttrss_filters2(id) on delete cascade, + action_id integer not null default 1 references ttrss_filter_actions(id) on delete cascade, + action_param varchar(250) not null default ''); + +update ttrss_version set schema_version = 96; + +commit; diff --git a/sql/pgsql/migrations/97.sql b/sql/pgsql/migrations/97.sql new file mode 100644 index 000000000..f458ee910 --- /dev/null +++ b/sql/pgsql/migrations/97.sql @@ -0,0 +1,10 @@ +begin; + +alter table ttrss_users add column otp_enabled boolean; +update ttrss_users set otp_enabled = false; +alter table ttrss_users alter column otp_enabled set not null; +alter table ttrss_users alter column otp_enabled set default false; + +update ttrss_version set schema_version = 97; + +commit; diff --git a/sql/pgsql/migrations/98.sql b/sql/pgsql/migrations/98.sql new file mode 100644 index 000000000..1984a4325 --- /dev/null +++ b/sql/pgsql/migrations/98.sql @@ -0,0 +1,7 @@ +begin; + +insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,access_level) values('AUTO_ASSIGN_LABELS', 1, 'true', 'Assign articles to labels automatically', 3, 1); + +update ttrss_version set schema_version = 98; + +commit; diff --git a/sql/pgsql/migrations/99.sql b/sql/pgsql/migrations/99.sql new file mode 100644 index 000000000..846056cd6 --- /dev/null +++ b/sql/pgsql/migrations/99.sql @@ -0,0 +1,12 @@ +begin; + +alter table ttrss_feeds add column cache_content boolean; +update ttrss_feeds set cache_content = false; +alter table ttrss_feeds alter column cache_content set not null; +alter table ttrss_feeds alter column cache_content set default false; + +alter table ttrss_entries add column cached_content text; + +update ttrss_version set schema_version = 99; + +commit; diff --git a/sql/pgsql/schema.sql b/sql/pgsql/schema.sql new file mode 100644 index 000000000..ca7e8241c --- /dev/null +++ b/sql/pgsql/schema.sql @@ -0,0 +1,399 @@ +drop table if exists ttrss_error_log; +drop table if exists ttrss_plugin_storage; +drop table if exists ttrss_linked_feeds; +drop table if exists ttrss_linked_instances; +drop table if exists ttrss_access_keys; +drop table if exists ttrss_user_labels2; +drop table if exists ttrss_labels2; +drop table if exists ttrss_feedbrowser_cache; +drop table if exists ttrss_version; +drop table if exists ttrss_labels; +drop table if exists ttrss_filters2_rules; +drop table if exists ttrss_filters2_actions; +drop table if exists ttrss_filters2; +drop table if exists ttrss_filters; +drop table if exists ttrss_filter_types; +drop table if exists ttrss_filter_actions; +drop table if exists ttrss_user_prefs; +drop table if exists ttrss_user_prefs2; +drop table if exists ttrss_prefs; +drop table if exists ttrss_prefs_types; +drop table if exists ttrss_prefs_sections; +drop table if exists ttrss_tags; +drop table if exists ttrss_enclosures; +drop table if exists ttrss_settings_profiles; +drop table if exists ttrss_entry_comments; +drop table if exists ttrss_user_entries; +drop table if exists ttrss_entries; +drop table if exists ttrss_scheduled_updates; +drop table if exists ttrss_counters_cache; +drop table if exists ttrss_cat_counters_cache; +drop table if exists ttrss_archived_feeds; +drop table if exists ttrss_feeds; +drop table if exists ttrss_feed_categories; +drop table if exists ttrss_app_passwords; +drop table if exists ttrss_users; +drop table if exists ttrss_themes; +drop table if exists ttrss_sessions; +drop function if exists SUBSTRING_FOR_DATE(timestamp, int, int); + +begin; + +create table ttrss_users (id serial not null primary key, + login varchar(120) not null unique, + pwd_hash varchar(250) not null, + last_login timestamp default null, + access_level integer not null default 0, + email varchar(250) not null default '', + full_name varchar(250) not null default '', + email_digest boolean not null default false, + last_digest_sent timestamp default null, + salt varchar(250) not null default '', + twitter_oauth text default null, + otp_enabled boolean not null default false, + resetpass_token varchar(250) default null, + created timestamp default null); + +insert into ttrss_users (login,pwd_hash,access_level) values ('admin', + 'SHA1:5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', 10); + +create table ttrss_app_passwords (id serial not null primary key, + title varchar(250) not null, + pwd_hash text not null, + service varchar(100) not null, + created timestamp not null, + last_used timestamp default null, + owner_uid integer not null references ttrss_users(id) on delete cascade); + +create table ttrss_feed_categories(id serial not null primary key, + owner_uid integer not null references ttrss_users(id) on delete cascade, + collapsed boolean not null default false, + order_id integer not null default 0, + view_settings varchar(250) not null default '', + parent_cat integer references ttrss_feed_categories(id) on delete set null, + title varchar(200) not null); + +create table ttrss_feeds (id serial not null primary key, + owner_uid integer not null references ttrss_users(id) on delete cascade, + title varchar(200) not null, + cat_id integer default null references ttrss_feed_categories(id) on delete set null, + feed_url text not null, + icon_url varchar(250) not null default '', + update_interval integer not null default 0, + purge_interval integer not null default 0, + last_updated timestamp default null, + last_unconditional timestamp default null, + last_error text not null default '', + last_modified text not null default '', + favicon_avg_color varchar(11) default null, + site_url varchar(250) not null default '', + auth_login varchar(250) not null default '', + parent_feed integer default null references ttrss_feeds(id) on delete set null, + private boolean not null default false, + auth_pass varchar(250) not null default '', + hidden boolean not null default false, + include_in_digest boolean not null default true, + rtl_content boolean not null default false, + cache_images boolean not null default false, + hide_images boolean not null default false, + cache_content boolean not null default false, + last_viewed timestamp default null, + last_update_started timestamp default null, + last_successful_update timestamp default null, + update_method integer not null default 0, + always_display_enclosures boolean not null default false, + order_id integer not null default 0, + mark_unread_on_update boolean not null default false, + update_on_checksum_change boolean not null default false, + strip_images boolean not null default false, + view_settings varchar(250) not null default '', + pubsub_state integer not null default 0, + favicon_last_checked timestamp default null, + feed_language varchar(100) not null default '', + auth_pass_encrypted boolean not null default false, + unique(feed_url, owner_uid)); + +create index ttrss_feeds_owner_uid_index on ttrss_feeds(owner_uid); +create index ttrss_feeds_cat_id_idx on ttrss_feeds(cat_id); + +insert into ttrss_feeds (owner_uid, title, feed_url) values + ((select id from ttrss_users where login = 'admin'), 'Tiny Tiny RSS: Forum', 'https://tt-rss.org/forum/rss.php'); + +create table ttrss_archived_feeds (id integer not null primary key, + owner_uid integer not null references ttrss_users(id) on delete cascade, + created timestamp not null, + title varchar(200) not null, + feed_url text not null, + site_url varchar(250) not null default ''); + +create table ttrss_counters_cache ( + feed_id integer not null, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + updated timestamp not null, + value integer not null default 0); + +create index ttrss_counters_cache_feed_id_idx on ttrss_counters_cache(feed_id); +create index ttrss_counters_cache_owner_uid_idx on ttrss_counters_cache(owner_uid); +create index ttrss_counters_cache_value_idx on ttrss_counters_cache(value); + +create table ttrss_cat_counters_cache ( + feed_id integer not null, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + updated timestamp not null, + value integer not null default 0); + +create index ttrss_cat_counters_cache_owner_uid_idx on ttrss_cat_counters_cache(owner_uid); + +create table ttrss_entries (id serial not null primary key, + title text not null, + guid text not null unique, + link text not null, + updated timestamp not null, + content text not null, + content_hash varchar(250) not null, + cached_content text, + no_orig_date boolean not null default false, + date_entered timestamp not null, + date_updated timestamp not null, + num_comments integer not null default 0, + comments varchar(250) not null default '', + plugin_data text, + tsvector_combined tsvector, + lang varchar(2), + author varchar(250) not null default ''); + +-- create index ttrss_entries_title_index on ttrss_entries(title); +create index ttrss_entries_date_entered_index on ttrss_entries(date_entered); +create index ttrss_entries_updated_idx on ttrss_entries(updated); +create index ttrss_entries_tsvector_combined_idx on ttrss_entries using gin(tsvector_combined); + +create table ttrss_user_entries ( + int_id serial not null primary key, + ref_id integer not null references ttrss_entries(id) ON DELETE CASCADE, + uuid varchar(200) not null, + feed_id int references ttrss_feeds(id) ON DELETE CASCADE, + orig_feed_id integer references ttrss_archived_feeds(id) ON DELETE SET NULL, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + marked boolean not null default false, + published boolean not null default false, + tag_cache text not null, + label_cache text not null, + last_read timestamp, + score int not null default 0, + last_marked timestamp, + last_published timestamp, + note text, + unread boolean not null default true); + +-- create index ttrss_user_entries_feed_id_index on ttrss_user_entries(feed_id); +create index ttrss_user_entries_owner_uid_index on ttrss_user_entries(owner_uid); +create index ttrss_user_entries_ref_id_index on ttrss_user_entries(ref_id); +create index ttrss_user_entries_feed_id on ttrss_user_entries(feed_id); +create index ttrss_user_entries_unread_idx on ttrss_user_entries(unread); + +create table ttrss_entry_comments (id serial not null primary key, + ref_id integer not null references ttrss_entries(id) ON DELETE CASCADE, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + private boolean not null default false, + date_entered timestamp not null); + +create index ttrss_entry_comments_ref_id_index on ttrss_entry_comments(ref_id); +-- create index ttrss_entry_comments_owner_uid_index on ttrss_entry_comments(owner_uid); + +create table ttrss_filter_types (id integer not null primary key, + name varchar(120) unique not null, + description varchar(250) not null unique); + +insert into ttrss_filter_types (id,name,description) values (1, 'title', 'Title'); +insert into ttrss_filter_types (id,name,description) values (2, 'content', 'Content'); +insert into ttrss_filter_types (id,name,description) values (3, 'both', + 'Title or Content'); +insert into ttrss_filter_types (id,name,description) values (4, 'link', + 'Link'); +insert into ttrss_filter_types (id,name,description) values (5, 'date', + 'Article Date'); +insert into ttrss_filter_types (id,name,description) values (6, 'author', 'Author'); +insert into ttrss_filter_types (id,name,description) values (7, 'tag', 'Article Tags'); + +create table ttrss_filter_actions (id integer not null primary key, + name varchar(120) unique not null, + description varchar(250) not null unique); + +insert into ttrss_filter_actions (id,name,description) values (1, 'filter', + 'Delete article'); + +insert into ttrss_filter_actions (id,name,description) values (2, 'catchup', + 'Mark as read'); + +insert into ttrss_filter_actions (id,name,description) values (3, 'mark', + 'Set starred'); + +insert into ttrss_filter_actions (id,name,description) values (4, 'tag', + 'Assign tags'); + +insert into ttrss_filter_actions (id,name,description) values (5, 'publish', + 'Publish article'); + +insert into ttrss_filter_actions (id,name,description) values (6, 'score', + 'Modify score'); + +insert into ttrss_filter_actions (id,name,description) values (7, 'label', + 'Assign label'); + +insert into ttrss_filter_actions (id,name,description) values (8, 'stop', + 'Stop / Do nothing'); + +insert into ttrss_filter_actions (id,name,description) values (9, 'plugin', + 'Invoke plugin'); + +create table ttrss_filters2(id serial not null primary key, + owner_uid integer not null references ttrss_users(id) on delete cascade, + match_any_rule boolean not null default false, + inverse boolean not null default false, + title varchar(250) not null default '', + order_id integer not null default 0, + last_triggered timestamp default null, + enabled boolean not null default true); + +create table ttrss_filters2_rules(id serial not null primary key, + filter_id integer not null references ttrss_filters2(id) on delete cascade, + reg_exp text not null, + inverse boolean not null default false, + filter_type integer not null references ttrss_filter_types(id), + feed_id integer references ttrss_feeds(id) on delete cascade default null, + cat_id integer references ttrss_feed_categories(id) on delete cascade default null, + match_on text, + cat_filter boolean not null default false); + +create table ttrss_filters2_actions(id serial not null primary key, + filter_id integer not null references ttrss_filters2(id) on delete cascade, + action_id integer not null default 1 references ttrss_filter_actions(id) on delete cascade, + action_param varchar(250) not null default ''); + +create table ttrss_tags (id serial not null primary key, + tag_name varchar(250) not null, + owner_uid integer not null references ttrss_users(id) on delete cascade, + post_int_id integer references ttrss_user_entries(int_id) ON DELETE CASCADE not null); + +create index ttrss_tags_owner_uid_index on ttrss_tags(owner_uid); +create index ttrss_tags_post_int_id_idx on ttrss_tags(post_int_id); + +create table ttrss_version (schema_version int not null); + +insert into ttrss_version values (142); + +create table ttrss_enclosures (id serial not null primary key, + content_url text not null, + content_type varchar(250) not null, + title text not null, + duration text not null, + width integer not null default 0, + height integer not null default 0, + post_id integer references ttrss_entries(id) ON DELETE cascade NOT NULL); + +create index ttrss_enclosures_post_id_idx on ttrss_enclosures(post_id); + +create table ttrss_settings_profiles(id serial not null primary key, + title varchar(250) not null, + owner_uid integer not null references ttrss_users(id) on delete cascade); + +create table ttrss_prefs_types (id integer not null primary key, + type_name varchar(100) not null); + +create table ttrss_prefs_sections (id integer not null primary key, + order_id integer not null, + section_name varchar(100) not null); + +create table ttrss_prefs (pref_name varchar(250) not null primary key, + type_id integer not null references ttrss_prefs_types(id), + section_id integer not null default 1 references ttrss_prefs_sections(id), + access_level integer not null default 0, + def_value text not null); + +create table ttrss_user_prefs ( + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + pref_name varchar(250) not null references ttrss_prefs(pref_name) ON DELETE CASCADE, + profile integer references ttrss_settings_profiles(id) ON DELETE CASCADE, + value text not null); + +create index ttrss_user_prefs_owner_uid_index on ttrss_user_prefs(owner_uid); +create index ttrss_user_prefs_pref_name_idx on ttrss_user_prefs(pref_name); + +create table ttrss_user_prefs2 ( + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + pref_name varchar(250) not null, + profile integer references ttrss_settings_profiles(id) ON DELETE CASCADE, + value text not null); + +create index ttrss_user_prefs2_owner_uid_index on ttrss_user_prefs2(owner_uid); +create index ttrss_user_prefs2_pref_name_idx on ttrss_user_prefs2(pref_name); +create unique index ttrss_user_prefs2_composite_idx on ttrss_user_prefs2(pref_name, owner_uid, coalesce(profile, -1)); + +create table ttrss_sessions (id varchar(250) not null primary key, + data text, + expire integer not null); + +create index ttrss_sessions_expire_index on ttrss_sessions(expire); + +create function SUBSTRING_FOR_DATE(timestamp, int, int) RETURNS text AS 'SELECT SUBSTRING(CAST($1 AS text), $2, $3)' LANGUAGE 'sql'; + +create table ttrss_feedbrowser_cache ( + feed_url text not null primary key, + title text not null, + site_url text not null, + subscribers integer not null); + +create table ttrss_labels2 (id serial not null primary key, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + fg_color varchar(15) not null default '', + bg_color varchar(15) not null default '', + caption varchar(250) not null +); + +create table ttrss_user_labels2 ( + label_id integer not null references ttrss_labels2(id) ON DELETE CASCADE, + article_id integer not null references ttrss_entries(id) ON DELETE CASCADE +); + +create index ttrss_user_labels2_article_id_idx on ttrss_user_labels2(article_id); +create index ttrss_user_labels2_label_id_idx on ttrss_user_labels2(label_id); + +create table ttrss_access_keys (id serial not null primary key, + access_key varchar(250) not null, + feed_id varchar(250) not null, + is_cat boolean not null default false, + owner_uid integer not null references ttrss_users(id) on delete cascade); + +create table ttrss_linked_instances (id serial not null primary key, + last_connected timestamp not null, + last_status_in integer not null, + last_status_out integer not null, + access_key varchar(250) not null unique, + access_url text not null); + +create table ttrss_linked_feeds ( + feed_url text not null, + site_url text not null, + title text not null, + created timestamp not null, + updated timestamp not null, + instance_id integer not null references ttrss_linked_instances(id) ON DELETE CASCADE, + subscribers integer not null); + +create table ttrss_plugin_storage ( + id serial not null primary key, + name varchar(100) not null, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + content text not null); + +create table ttrss_error_log( + id serial not null primary key, + owner_uid integer references ttrss_users(id) ON DELETE SET NULL, + errno integer not null, + errstr text not null, + filename text not null, + lineno integer not null, + context text not null, + created_at timestamp not null); + +commit; |