108.sql
No OneTemporary
Actions

Size

1 KB

Subscribers

None

108.sql
View Options

	-- SWH DB schema upgrade
	-- from_version: 107
	-- to_version: 108
	-- description: Add a new object_counts table to make counters more relevant

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

	CREATE TABLE object_counts (
	object_type text NOT NULL,
	"value" bigint,
	last_update timestamp with time zone
	);

	ALTER TABLE object_counts
	ADD CONSTRAINT object_counts_pkey PRIMARY KEY (object_type);

	CREATE OR REPLACE FUNCTION swh_update_counter(object_type text) RETURNS void
	LANGUAGE plpgsql
	AS $_$
	begin
	execute format('
	insert into object_counts
	(value, last_update, object_type)
	values
	((select count(*) from %1$I), NOW(), %1$L)
	on conflict (object_type) do update set
	value = excluded.value,
	last_update = excluded.last_update',
	object_type);
	return;
	end;
	$_$;

	CREATE OR REPLACE FUNCTION swh_stat_counters() RETURNS SETOF counter
	LANGUAGE sql STABLE
	AS $$
	select object_type as label, value as value
	from object_counts
	where object_type in (
	'content',
	'directory',
	'directory_entry_dir',
	'directory_entry_file',
	'directory_entry_rev',
	'occurrence',
	'occurrence_history',
	'origin',
	'person',
	'entity',
	'entity_history',
	'release',
	'revision',
	'revision_history',
	'skipped_content'
	);
	$$;