Differential D4881 Diff 17338 swh/scheduler/sql/30-schema.sql

Changeset View

Standalone View

swh/scheduler/sql/30-schema.sql

create table dbversion		create table dbversion
(		(
version int primary key,		version int primary key,
release timestamptz not null,		release timestamptz not null,
description text not null		description text not null
);		);

comment on table dbversion is 'Schema update tracking';		comment on table dbversion is 'Schema update tracking';
comment on column dbversion.version is 'SQL schema version';		comment on column dbversion.version is 'SQL schema version';
comment on column dbversion.release is 'Version deployment timestamp';		comment on column dbversion.release is 'Version deployment timestamp';
comment on column dbversion.description is 'Version description';		comment on column dbversion.description is 'Version description';

insert into dbversion (version, release, description)		insert into dbversion (version, release, description)
values (23, now(), 'Work In Progress');		values (24, now(), 'Work In Progress');

create table task_type (		create table task_type (
type text primary key,		type text primary key,
description text not null,		description text not null,
backend_name text not null,		backend_name text not null,
default_interval interval,		default_interval interval,
min_interval interval,		min_interval interval,
max_interval interval,		max_interval interval,
▲ Show 20 Lines • Show All 117 Lines • ▼ Show 20 Lines	create table if not exists listed_origins (

-- time-based information		-- time-based information
first_seen timestamptz not null default now(),		first_seen timestamptz not null default now(),
last_seen timestamptz not null,		last_seen timestamptz not null,

-- potentially provided by the lister		-- potentially provided by the lister
last_update timestamptz,		last_update timestamptz,

-- visit scheduling information
last_scheduled timestamptz,

primary key (lister_id, url, visit_type)		primary key (lister_id, url, visit_type)
);		);

comment on table listed_origins is 'Origins known to the origin visit scheduler';		comment on table listed_origins is 'Origins known to the origin visit scheduler';
comment on column listed_origins.lister_id is 'Lister instance which owns this origin';		comment on column listed_origins.lister_id is 'Lister instance which owns this origin';
comment on column listed_origins.url is 'URL of the origin listed';		comment on column listed_origins.url is 'URL of the origin listed';
comment on column listed_origins.visit_type is 'Type of the visit which should be scheduled for the given url';		comment on column listed_origins.visit_type is 'Type of the visit which should be scheduled for the given url';
comment on column listed_origins.extra_loader_arguments is 'Extra arguments that should be passed to the loader for this origin';		comment on column listed_origins.extra_loader_arguments is 'Extra arguments that should be passed to the loader for this origin';

comment on column listed_origins.enabled is 'Whether this origin has been seen during the last listing, and visits should be scheduled.';		comment on column listed_origins.enabled is 'Whether this origin has been seen during the last listing, and visits should be scheduled.';
comment on column listed_origins.first_seen is 'Time at which the origin was first seen by a lister';		comment on column listed_origins.first_seen is 'Time at which the origin was first seen by a lister';
comment on column listed_origins.last_seen is 'Time at which the origin was last seen by the lister';		comment on column listed_origins.last_seen is 'Time at which the origin was last seen by the lister';

comment on column listed_origins.last_update is 'Time of the last update to the origin recorded by the remote';		comment on column listed_origins.last_update is 'Time of the last update to the origin recorded by the remote';

comment on column listed_origins.last_scheduled is 'Time when this origin was scheduled to be visited last';

create table origin_visit_stats (		create table origin_visit_stats (
url text not null,		url text not null,
visit_type text not null,		visit_type text not null,
last_eventful timestamptz,		last_eventful timestamptz,
last_uneventful timestamptz,		last_uneventful timestamptz,
last_failed timestamptz,		last_failed timestamptz,
last_notfound timestamptz,		last_notfound timestamptz,
		-- visit scheduling information
		last_scheduled timestamptz,
		-- last snapshot resulting from an eventful visit
last_snapshot bytea,		last_snapshot bytea,

primary key (url, visit_type)		primary key (url, visit_type)
);		);

comment on column origin_visit_stats.url is 'Origin URL';		comment on column origin_visit_stats.url is 'Origin URL';
comment on column origin_visit_stats.visit_type is 'Type of the visit for the given url';		comment on column origin_visit_stats.visit_type is 'Type of the visit for the given url';
comment on column origin_visit_stats.last_eventful is 'Date of the last eventful event';		comment on column origin_visit_stats.last_eventful is 'Date of the last eventful event';
comment on column origin_visit_stats.last_uneventful is 'Date of the last uneventful event';		comment on column origin_visit_stats.last_uneventful is 'Date of the last uneventful event';
comment on column origin_visit_stats.last_failed is 'Date of the last failed event';		comment on column origin_visit_stats.last_failed is 'Date of the last failed event';
comment on column origin_visit_stats.last_notfound is 'Date of the last notfound event';		comment on column origin_visit_stats.last_notfound is 'Date of the last notfound event';
		comment on column origin_visit_stats.last_scheduled is 'Time when this origin was scheduled to be visited last';
comment on column origin_visit_stats.last_snapshot is 'sha1_git of the last visit snapshot';		comment on column origin_visit_stats.last_snapshot is 'sha1_git of the last visit snapshot';