Index: openacs-4/packages/news-aggregator/sql/postgresql/upgrade/upgrade-0.5d-0.6d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news-aggregator/sql/postgresql/upgrade/Attic/upgrade-0.5d-0.6d.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news-aggregator/sql/postgresql/upgrade/upgrade-0.5d-0.6d.sql 26 Nov 2005 11:12:47 -0000 1.1.2.1 @@ -0,0 +1,210 @@ +---------------- +-- +-- Aggregators +-- +---------------- + +create table na_aggregators ( + aggregator_id integer + constraint na_aggregators_pk + primary key + constraint na_aggregators_aid_fk + references acs_objects(object_id) + on delete cascade, + aggregator_name varchar(100) + constraint na_aggregators_name_nn + not null, + description text, + package_id integer + constraint na_aggregators_pid_fk + references apm_packages(package_id) + constraint na_aggregators_pid_nn + not null, + maintainer_id integer + constraint na_aggregators_mid_fk + references users(user_id) + constraint na_aggregators_mid_nn + not null, + public_p boolean default true, + number_shown integer + default '100', + aggregator_bottom integer +); + + +select acs_object_type__create_type ( + 'na_aggregator', -- object_type + 'Aggregator', -- pretty_name + 'Aggregators', -- pretty_plural + 'acs_object', -- supertype + 'na_aggregators', -- table_name + 'aggregator_id', -- id_column + null, -- package_name + 'f', -- abstract_p + null, -- type_extension_table + 'na_aggregator.name' -- name_method +); + +-- alter table na_sources drop column package_id; +-- alter table na_sources drop column owner_id; +alter table na_sources add column last_modified_stamp timestamptz; +alter table na_sources add column last_scan_ok_p boolean default true; +alter table na_sources add column stacktrace text; +alter table na_sources add column rss_source text; +alter table na_sources add column listed_p boolean default true; + +alter table na_items add column guid varchar(500); +alter table na_items add column original_guid varchar(500); +alter table na_items add column permalink_p boolean default true; +alter table na_items add column content_encoded text; + +create index na_items_guid_idx on na_items(guid); +create index na_items_source_id_idx on na_items(source_id); + +create table na_saved_items ( + item_id integer + constraint na_saved_items_iid_fk + references na_items(item_id), + aggregator_id integer + constraint na_saved_items_aid_fk + references na_aggregators(aggregator_id), + constraint na_saved_items_pk primary key(item_id, aggregator_id) +); + +create table na_purges ( + purge_id integer + constraint na_purges_pk + primary key, + top integer + constraint na_purges_top_nn + not null, + bottom integer + constraint na_purges_bottom_nn + not null, + aggregator_id integer + constraint na_purges_aid_fk + references na_aggregators(aggregator_id) + constraint na_purges_aid_nn + not null, + purge_date timestamptz + default current_timestamp +); + +create sequence na_purges_seq; + + +---------------- +-- +-- Subscriptions +-- +---------------- + +create table na_subscriptions ( + aggregator_id integer + constraint na_subscriptions_aid_fk + references na_aggregators(aggregator_id), + source_id integer + constraint na_subscriptions_sid_fk + references na_sources(source_id), + creation_date timestamptz + default current_timestamp, + constraint na_subscriptions_pk primary key (aggregator_id, source_id) +); + +create index na_subscriptions_aid_idx on na_subscriptions(aggregator_id); + +------------------- +-- +-- User Preferences +-- +------------------- + +create table na_user_preferences ( + user_id integer + constraint na_user_prefs_uid_pk + primary key + constraint na_user_prefs_uid_fk + references users(user_id), + default_aggregator integer + constraint na_user_prefs_default_fk + references na_aggregators(aggregator_id) +); + + +----------------------- +-- +-- Pre-subscribed Feeds +-- +----------------------- + +-- source_id, package_id + +create table na_presubscribed_feeds ( + source_id integer + constraint na_presubscribed_feeds_sid_fk + references na_sources(source_id) + constraint na_presubscribed_feeds_sid_nn + not null, + package_id integer + constraint na_presubscribed_feeds_pid_fk + references apm_packages(package_id) + constraint na_presubscribed_feeds_pid_nn + not null +); + +create sequence na_presubscribed_feeds_seq; + + +------------------- +-- +-- Weblogs +-- +------------------- + +select acs_object_type__create_type ( + 'na_weblog', -- object_type + 'News Aggregator Weblog', -- pretty_name + 'News Aggregator Weblogs', -- pretty_plural + 'acs_object', -- supertype + 'na_weblogs', -- table_name + 'weblog_id', -- id_column + 'news-aggregator', -- package_name + 'f', -- abstract_p + null, -- type_extension_table + 'na_weblog__name' -- name_method +); + +create table na_weblogs ( + weblog_id integer + constraint na_weblogs_weblog_id_fk + references acs_objects(object_id) + constraint na_weblogs_weblog_id_nn + not null, + package_id integer + constraint na_weblogs_pid_fk + references apm_packages(package_id) + constraint na_weblogs_pid_nn + not null, + user_id integer + constraint na_weblogs_uid_fk + references users(user_id) + constraint na_weblogs_uid_nn + not null, + weblog_name varchar(100) + constraint na_weblogs_name_nn + not null, + blog_type varchar(100) + constraint na_weblogs_blog_type_nn + not null, + base_url varchar(500) + constraint na_weblogs_base_url_nn + not null, + server varchar(500), + port integer, + path varchar(100), + blogid integer, + username varchar(200), + password varchar(200) +); + +\i ../news-aggregator-packages-create.sql