NNTPGrab

CREATE LANGUAGE plpgsql;

CREATE TABLE row_counts (relname text PRIMARY KEY, reltuples numeric);

CLUSTER row_counts USING row_counts_pkey;

CREATE OR REPLACE FUNCTION count_trig()

    RETURNS TRIGGER AS

    $$

       DECLARE

    BEGIN

    IF TG_OP = 'INSERT' THEN

       EXECUTE 'UPDATE row_counts set reltuples=reltuples +1 where relname = ''' || TG_RELNAME || '''';

       RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN

       EXECUTE 'UPDATE row_counts set reltuples=reltuples -1 where relname = ''' || TG_RELNAME || '''';

       RETURN OLD;

    END IF;

    END;

 $$

 LANGUAGE 'plpgsql';

CREATE TABLE sph_counter (counter_id INTEGER NOT NULL PRIMARY KEY, max_doc_id BIGINT NOT NULL);

INSERT INTO sph_counter VALUES (1,0);

CREATE TABLE newsgroups

 (

         newsgroup_id                    SERIAL NOT NULL PRIMARY KEY,

         newsgroup                       VARCHAR(256) NOT NULL,

         indexing_since                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

         oldest_file_stamp               TIMESTAMP,

         newest_file_stamp               TIMESTAMP,

         last_checked                    TIMESTAMP,

         prev_last_checked               TIMESTAMP,

         bytes_transferred               BIGINT NOT NULL DEFAULT 0,

         last_article_id                 BIGINT NOT NULL DEFAULT 0,

         number_of_files                 BIGINT NOT NULL DEFAULT 0,

         number_of_files_unsorted        BIGINT NOT NULL DEFAULT 0

 );

ALTER TABLE newsgroups ADD CONSTRAINT newsgroups_newsgroup UNIQUE (newsgroup);

CREATE SEQUENCE collection_id_seq NO CYCLE;

CREATE TABLE collections

 (

         collection_id           BIGINT NOT NULL PRIMARY KEY DEFAULT NEXTVAL('collection_id_seq'),

         newsgroup_id            INT NOT NULL REFERENCES newsgroups,

         stamp                   TIMESTAMP NOT NULL,

         hash                    BIGINT NOT NULL,

         collection_name         VARCHAR(256) NOT NULL,

         display_collection_name VARCHAR(256) NOT NULL,

         poster                  VARCHAR(128) NOT NULL,

         total_size              BIGINT NOT NULL,

         num_parts_found         INT NOT NULL,

         num_parts_expected      INT NOT NULL,

         num_regular_files       INT NOT NULL,

         num_par2_files          INT NOT NULL,

         stamp_changed_in_db     TIMESTAMP NOT NULL

 );

CREATE INDEX collections_hash_idx ON collections(hash);

CLUSTER collections USING collections_pkey;

CREATE TABLE files_in_collections

 (

         collection_id           BIGINT NOT NULL REFERENCES collections,

         file_id                 BIGINT NOT NULL,

         stamp                   TIMESTAMP NOT NULL,

                                 PRIMARY KEY (collection_id, file_id)

 );

CREATE TABLE files_in_collections_unsorted (PRIMARY KEY (file_id)) INHERITS (files_in_collections);

CREATE INDEX files_in_collections_unsorted_stamp_idx ON files_in_collections_unsorted(stamp);

CLUSTER files_in_collections_unsorted USING files_in_collections_unsorted_pkey;

ALTER TABLE files_in_collections_unsorted ADD FOREIGN KEY (file_id) REFERENCES files_unsorted;

ALTER TABLE files_in_collections_unsorted NO INHERIT files_in_collections;

CREATE TRIGGER files_in_collections_unsorted_count_delete_trig AFTER DELETE on files_in_collections_unsorted FOR EACH ROW EXECUTE PROCEDURE count_trig();

CREATE TRIGGER files_in_collections_unsorted_count_insert_trig AFTER INSERT on files_in_collections_unsorted FOR EACH ROW EXECUTE PROCEDURE count_trig();

INSERT INTO row_counts VALUES('files_in_collections_unsorted', 0);

CREATE SEQUENCE file_id_seq NO CYCLE;

CREATE TYPE file_type AS ENUM ('Unknown', 'RAR', 'PAR', 'PAR2', 'SFV', 'NFO', 'NZB', 'Other');

CREATE TABLE files

 (

         file_id                 BIGINT NOT NULL PRIMARY KEY DEFAULT NEXTVAL('file_id_seq'),

         stamp                   TIMESTAMP NOT NULL,

         -- collection_id                BIGINT NOT NULL REFERENCES collections,

         newsgroup_id            INT NOT NULL REFERENCES newsgroups,

         hash                    BIGINT NOT NULL,

         subject                 VARCHAR(256) NOT NULL,

         poster                  VARCHAR(128) NOT NULL,

         filename                VARCHAR(256) NOT NULL,

         file_type               file_type NOT NULL DEFAULT 'Unknown',

         file_size               BIGINT NOT NULL,

         num_parts_found         INT NOT NULL,

         num_parts_expected      INT NOT NULL,

         stamp_changed_in_db     TIMESTAMP NOT NULL

 );

CREATE TRIGGER files_count_delete_trig AFTER DELETE on files FOR EACH ROW EXECUTE PROCEDURE count_trig();

CREATE TRIGGER files_count_insert_trig AFTER INSERT on files FOR EACH ROW EXECUTE PROCEDURE count_trig();

INSERT INTO row_counts VALUES('files', 0);

CREATE TABLE files_unsorted (PRIMARY KEY (file_id)) INHERITS (files);

ALTER TABLE files_unsorted ADD FOREIGN KEY (newsgroup_id) REFERENCES newsgroups;

ALTER TABLE files_unsorted NO INHERIT files;

CREATE INDEX files_unsorted_hash_idx ON files_unsorted(hash);

CREATE INDEX files_unsorted_newsgroup_idx ON files_unsorted(newsgroup_id);

CREATE INDEX files_unsorted_stamp_changed_idx ON files_unsorted(stamp_changed_in_db);

CREATE INDEX files_unsorted_stamp_idx ON files_unsorted(stamp);

CLUSTER files_unsorted USING files_unsorted_newsgroup_idx;

CREATE TRIGGER files_unsorted_count_delete_trig AFTER DELETE on files_unsorted FOR EACH ROW EXECUTE PROCEDURE count_trig();

CREATE TRIGGER files_unsorted_count_insert_trig AFTER INSERT on files_unsorted FOR EACH ROW EXECUTE PROCEDURE count_trig();

INSERT INTO row_counts VALUES('files_unsorted', 0);

CREATE TABLE file_parts

      (

              file_id                 BIGINT NOT NULL PRIMARY KEY REFERENCES files,

              stamp                   TIMESTAMP NOT NULL,

              parts                   TEXT NOT NULL

      );

CREATE TRIGGER file_parts_count_delete_trig AFTER DELETE on file_parts FOR EACH ROW EXECUTE PROCEDURE count_trig();

CREATE TRIGGER file_parts_count_insert_trig AFTER INSERT on file_parts FOR EACH ROW EXECUTE PROCEDURE count_trig();

INSERT INTO row_counts VALUES('file_parts', 0);

CREATE TABLE file_parts_unsorted (PRIMARY KEY (file_id)) INHERITS (file_parts);

CREATE INDEX file_parts_unsorted_stamp_idx ON file_parts_unsorted(stamp);

CLUSTER file_parts_unsorted USING file_parts_unsorted_pkey;

ALTER TABLE file_parts_unsorted ADD FOREIGN KEY (file_id) REFERENCES files_unsorted;

ALTER TABLE file_parts_unsorted NO INHERIT file_parts;

CREATE TRIGGER file_parts_unsorted_count_delete_trig AFTER DELETE on file_parts_unsorted FOR EACH ROW EXECUTE PROCEDURE count_trig();

CREATE TRIGGER file_parts_unsorted_count_insert_trig AFTER INSERT on file_parts_unsorted FOR EACH ROW EXECUTE PROCEDURE count_trig();

INSERT INTO row_counts VALUES('file_parts_unsorted', 0);

-- CREATE TABLE files_in_newsgroups

--    (

--            file_id                 BIGINT NOT NULL REFERENCES files,

--            stamp                   TIMESTAMP NOT NULL,

--            newsgroup_id            INT NOT NULL REFERENCES newsgroups,

--                                    PRIMARY KEY(file_id, newsgroup_id)

--    );

CREATE TABLE statistics

      (

              statistic_id            BIGSERIAL NOT NULL PRIMARY KEY,

              stamp                   TIMESTAMP NOT NULL,

              query                   VARCHAR(128) NOT NULL,

              number_of_hits          INT NOT NULL,

              number_of_results       INT NOT NULL,

              time_needed_for_sphinx  REAL NOT NULL,

              time_needed_for_db      REAL NOT NULL,

              time_needed_for_php     REAL NOT NULL

      );

CREATE TABLE newsgroup_statistics

      (

              newsgroup_id            BIGINT NOT NULL REFERENCES newsgroups,

              day                     DATE NOT NULL,

              num_files               BIGINT NOT NULL,

              PRIMARY KEY(newsgroup_id, day)

      );

-- procedure to create a specific partition

-- the first argument is the name of the base table which needs to be partitioned

-- the second argument is the name of the primary key of the base table

-- the third argument is the name of the foreign key (can be NULL)

-- the fourth argument is the name of the table which needs to be references to (can be NULL)

-- the fifth argument is a stamp containing the week for which a partition needs to be made

CREATE OR REPLACE FUNCTION create_partition(VARCHAR(64), VARCHAR(64), VARCHAR(64), VARCHAR(64), TIMESTAMP WITH TIME ZONE) RETURNS VOID AS $$

DECLARE

      v_base_table            ALIAS FOR $1;

      v_base_table_pk         ALIAS FOR $2;

      v_reference_fk          ALIAS FOR $3;

      v_reference_table       ALIAS FOR $4;

      v_reference_partition   VARCHAR(64);

      v_partition_name        VARCHAR(64);

      v_date_from_incoming    ALIAS FOR $5;

      v_date_from             TIMESTAMP;

      v_date_to               TIMESTAMP;

      -- used just for checking existence of the partitions

      v_exists                BOOLEAN;

BEGIN

      IF (v_base_table IS NULL) THEN

              RAISE EXCEPTION 'Missing argument base_table';

      END IF;

        IF (v_base_table_pk IS NULL) THEN

                RAISE EXCEPTION 'Missing argument base_table_pk';

        END IF;

      IF (v_date_from_incoming IS NULL) THEN

                RAISE EXCEPTION 'Missing argument date_from';

        END IF;

      v_date_from := date_trunc('week', v_date_from_incoming);

      v_date_to := v_date_from + '1 week';

      v_partition_name := v_base_table || '_' || TO_CHAR(v_date_from, 'IYYY_IW');

      SELECT COUNT(*) = 1 INTO v_exists FROM pg_tables WHERE schemaname = 'public' AND tablename = v_partition_name;

      IF (NOT v_exists) THEN

              EXECUTE 'CREATE TABLE ' || v_partition_name || ' (PRIMARY KEY (' || v_base_table_pk || '), CHECK (stamp >= ''' || v_date_from || ''' AND stamp < ''' || v_date_to || ''')) INHERITS (' || v_base_table || ')';

              -- if you need to create indexes / foreign keys / whatever on the partition, you may do it here

              IF (v_reference_table IS NOT NULL) THEN

                      v_reference_partition := v_reference_table || TO_CHAR(v_date_from, '_IYYY_IW');

                      EXECUTE 'ALTER TABLE ' || v_partition_name || ' ADD FOREIGN KEY (' || v_reference_fk || ') REFERENCES ' || v_reference_partition || ' (' || v_reference_fk || ')';

              END IF;

              EXECUTE 'CREATE INDEX ' || v_partition_name || '_stamp_idx ON ' || v_partition_name || '(stamp)';

              IF (v_base_table = 'files') THEN

                      EXECUTE 'CREATE INDEX ' || v_partition_name || '_hash_idx ON ' || v_partition_name || '(hash)';

                      EXECUTE 'CREATE INDEX ' || v_partition_name || '_newsgroup_idx ON ' || v_partition_name || '(newsgroup_id)';

                      EXECUTE 'CREATE INDEX ' || v_partition_name || '_stamp_changed_idx ON ' || v_partition_name || '(stamp_changed_in_db)';

                      EXECUTE 'ALTER TABLE ' || v_partition_name || ' ADD FOREIGN KEY (newsgroup_id) REFERENCES newsgroups';

                      EXECUTE 'CLUSTER ' || v_partition_name || ' USING ' || v_partition_name || '_newsgroup_idx';

              END IF;

              IF (v_base_table = 'file_parts') THEN

                      EXECUTE 'CLUSTER ' || v_partition_name || ' USING ' || v_partition_name || '_stamp_idx';

              END IF;

              IF (v_base_table = 'files_in_collections') THEN

                      EXECUTE 'CLUSTER ' || v_partition_name || ' USING ' || v_partition_name || '_stamp_idx';

              END IF;

              EXECUTE 'CREATE TRIGGER ' || v_partition_name || '_count_delete_trig AFTER DELETE on ' || v_partition_name || ' FOR EACH ROW EXECUTE PROCEDURE count_trig()';

              EXECUTE 'CREATE TRIGGER ' || v_partition_name || '_count_insert_trig AFTER INSERT on ' || v_partition_name || ' FOR EACH ROW EXECUTE PROCEDURE count_trig()';

              EXECUTE 'INSERT INTO row_counts VALUES(''' || v_partition_name || ''', 0)';

      END IF;

END;

$$ LANGUAGE plpgsql;

-- procedure that manages the partitions

-- the first argument is the name of the base table which needs to be partitioned

-- the second argument is the name of the primary key of the base table

-- the third argument is the name of the foreign key (can be NULL)

-- the fourth argument is the name of the table which needs to be references to (can be NULL)

CREATE OR REPLACE FUNCTION manage_partitions(VARCHAR(64), VARCHAR(64), VARCHAR(64), VARCHAR(64)) RETURNS void AS $$

DECLARE

      -- name of the next partition (and interval boundaries)

      v_base_table            ALIAS FOR $1;

      v_base_table_pk         ALIAS FOR $2;

      v_reference_fk          ALIAS FOR $3;

      v_reference_table       ALIAS FOR $4;

        v_reference_partition VARCHAR(64);

      v_partition_name        VARCHAR(64);

      v_date_from             TIMESTAMP;

      v_date_to               TIMESTAMP;

      v_current_date          TIMESTAMP;

      v_offset                INTERVAL;

      -- used to prepare the partitioning trigger

      v_trigger               TEXT := '';

      v_trigger_upd           TEXT := '';

      -- used just for checking existence of the partitions

      v_exists                BOOLEAN;

      v_num                   INTEGER;

BEGIN

      IF (v_base_table IS NULL) THEN

              RAISE EXCEPTION 'Missing argument base_table';

      END IF;

      IF (v_base_table_pk IS NULL) THEN

              RAISE EXCEPTION 'Missing argument base_table_pk';

      END IF;

      v_current_date := current_timestamp;

      -- purge the very old partitions if they exist

      v_date_from := date_trunc('week', v_current_date - '507 days'::interval);

      v_partition_name := v_base_table || '_' || TO_CHAR(v_date_from, 'IYYY_IW');

      SELECT COUNT(*) = 1 INTO v_exists FROM pg_tables WHERE schemaname = 'public' AND tablename = v_partition_name;

      IF (v_exists) THEN

              RAISE NOTICE 'Now trying to drop partition %', v_partition_name;

              BEGIN

                      EXECUTE 'DROP TABLE ' || v_partition_name;

                      EXECUTE 'DELETE FROM row_counts WHERE relname=''' || v_partition_name || '''';

                      IF (v_base_table = 'files') THEN

                              EXECUTE 'DELETE FROM newsgroup_statistics WHERE day < ''' || v_date_from || '''';

                      END IF;

              EXCEPTION

                      WHEN dependent_objects_still_exist THEN

                              RAISE NOTICE 'Unable to drop partition due to referential integrity. Please try again later';

              END;

      END IF;

      -- create partitions for 2 weeks ago, 1 week ago, current week and next week

      FOR i IN -2..1 LOOP

              -- create a partition for this week

              v_offset := '1 week'::interval * i;

              v_date_from := date_trunc('week', v_current_date + v_offset);

              v_partition_name := v_base_table || '_' || TO_CHAR(v_date_from, 'IYYY_IW');

              SELECT COUNT(*) = 1 INTO v_exists FROM pg_tables WHERE schemaname = 'public' AND tablename = v_partition_name;

              IF (NOT v_exists) THEN

                      EXECUTE 'SELECT create_partition($1, $2, $3, $4, $5)' USING v_base_table, v_base_table_pk, v_reference_fk, v_reference_table, v_date_from;

              END IF;

      END LOOP;

      -- for optimalisation purposes we add the trigger in a special order

      -- the current week needs to be handled first, followed by next week, previous week, two weeks ago and the rest needs to head to 'unsorted'

      v_date_from := date_trunc('week', v_current_date);

      v_date_to := v_date_from + '1 week';

      v_partition_name := v_base_table || '_' || TO_CHAR(v_date_from, 'IYYY_IW');

      v_trigger := 'IF (NEW.stamp >= ''' || v_date_from || ''' AND NEW.stamp < ''' || v_date_to || ''') THEN INSERT INTO ' || v_partition_name || ' VALUES (NEW.*);';

      -- next week

      v_date_from := date_trunc('week', v_current_date + '1 week'::interval);

      v_date_to := v_date_from + '1 week';

      v_partition_name := v_base_table || '_' || TO_CHAR(v_date_from, 'IYYY_IW');

      v_trigger := v_trigger || ' ELSIF (NEW.stamp >= ''' || v_date_from || ''' AND NEW.stamp < ''' || v_date_to || ''') THEN INSERT INTO ' || v_partition_name || ' VALUES (NEW.*);';

      -- build triggers for all earlier weeks

      v_num = 1;

      LOOP

              v_offset := '1 week'::interval * v_num;

              v_date_from := date_trunc('week', v_current_date - v_offset);

              v_date_to := v_date_from + '1 week';

              v_partition_name := v_base_table || '_' || TO_CHAR(v_date_from, 'IYYY_IW');

              SELECT COUNT(*) = 1 INTO v_exists FROM pg_tables WHERE schemaname = 'public' AND tablename = v_partition_name;

              IF (NOT v_exists) THEN

                      EXIT;

              END IF;

              v_trigger := v_trigger || ' ELSIF (NEW.stamp >= ''' || v_date_from || ''' AND NEW.stamp < ''' || v_date_to || ''') THEN INSERT INTO ' || v_partition_name || ' VALUES (NEW.*);';

              v_num := v_num + 1;

      END LOOP;

      -- everything else

        v_trigger := v_trigger || ' ELSE INSERT INTO ' || v_base_table || '_unsorted VALUES (NEW.*); END IF;';

      -- prepare a new version of the trigger procedure distributing rows into partitions

      v_trigger := 'CREATE OR REPLACE FUNCTION partitioning_trigger_' || v_base_table || '() RETURNS trigger AS $t$ BEGIN ' || v_trigger || ' RETURN NULL; END; $t$ LANGUAGE plpgsql;';

      EXECUTE v_trigger;

      -- drop the old trigger first

      EXECUTE 'DROP TRIGGER IF EXISTS partition_rows_' || v_base_table || ' ON ' || v_base_table;

      -- create a new trigger

      EXECUTE 'CREATE TRIGGER partition_rows_' || v_base_table || ' BEFORE INSERT ON ' || v_base_table || ' FOR EACH ROW EXECUTE PROCEDURE partitioning_trigger_' || v_base_table || '()';

END;

$$ LANGUAGE plpgsql;

-- procedure to update statistics in the newsgroups table

-- the first argument is the newsgroup_id

-- the second argument is the last received article id

-- the third argument is the number of bytes transferred in this run

-- the fourth argument is the number of added files in this run

CREATE OR REPLACE FUNCTION update_newsgroup_info(INT, BIGINT, BIGINT, INT) RETURNS void AS $$

DECLARE

      v_newsgroup_id                  ALIAS FOR $1;

      v_last_article_id               ALIAS FOR $2;

      v_bytes_transferred             ALIAS FOR $3;

      v_num_files_added               ALIAS FOR $4;

      v_oldest_partition              VARCHAR(64);

      v_newest_partition              VARCHAR(64);

      -- calculated values

      v_record                        RECORD;

      v_min_files_stamp               TIMESTAMP;

      v_max_files_stamp               TIMESTAMP;

      v_count_files                   BIGINT;

      v_min_files_unsorted_stamp      TIMESTAMP;

      v_max_files_unsorted_stamp      TIMESTAMP;

      v_count_files_unsorted          BIGINT;

      v_offset                        INTEGER;

      v_partition_name                VARCHAR(64);

BEGIN

      IF (v_newsgroup_id IS NULL) THEN

              RAISE EXCEPTION 'Missing argument v_newsgroup_id';

      END IF;

      IF (v_last_article_id IS NULL) THEN

              RAISE EXCEPTION 'Missing argument v_last_article_id';

      END IF;

      IF (v_bytes_transferred IS NULL) THEN

              RAISE EXCEPTION 'Missing argument v_bytes_transferred';

      END IF;

      IF (v_num_files_added IS NULL) THEN

              RAISE EXCEPTION 'Missing argument v_num_files_added';

      END IF;

      -- If no new data has been received, only update the last_checked stamp

      IF (v_bytes_transferred = 0) THEN

              UPDATE newsgroups SET last_checked=CURRENT_TIMESTAMP WHERE newsgroup_id=v_newsgroup_id;

              RETURN;

      END IF;

      -- Find out the min(stamp), max(stamp) and count(*) of the unsorted partition

      SELECT min(stamp) AS min, max(stamp) AS max, count(*) AS count INTO v_record FROM files_unsorted WHERE newsgroup_id = v_newsgroup_id;

      --v_min_files_unsorted_stamp := v_record.min;

      --v_max_files_unsorted_stamp := v_record.max;

      v_count_files_unsorted := v_record.count;

      v_min_files_unsorted_stamp := NULL;

      v_max_files_unsorted_stamp := NULL;

      -- Find out the min(stamp) of the files table

      v_offset := 0;

      LOOP

              SELECT tablename INTO v_record FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'files_20%' ORDER BY tablename LIMIT 1 OFFSET v_offset;

              v_partition_name := v_record.tablename;

              EXECUTE 'SELECT MIN(stamp) FROM ' || v_partition_name || ' WHERE newsgroup_id = ' || v_newsgroup_id INTO v_min_files_stamp;

              EXIT WHEN v_min_files_stamp IS NOT NULL;

              v_offset := v_offset + 1;

        END LOOP;

      -- Find out the max(stamp) of the files table

        v_offset := 0;

        LOOP

                SELECT tablename INTO v_record FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'files_20%' ORDER BY tablename DESC LIMIT 1 OFFSET v_offset;

              v_partition_name := v_record.tablename;

              EXECUTE 'SELECT MAX(stamp) FROM ' || v_partition_name || ' WHERE newsgroup_id = ' || v_newsgroup_id INTO v_max_files_stamp;

                EXIT WHEN v_max_files_stamp IS NOT NULL;

                v_offset := v_offset + 1;

        END LOOP;

      UPDATE newsgroups SET 

                      last_article_id=v_last_article_id, 

                      bytes_transferred=bytes_transferred + v_bytes_transferred,

                      last_checked=CURRENT_TIMESTAMP,

                      prev_last_checked=LEAST(last_checked, current_timestamp - interval '2 hours'),

                      oldest_file_stamp=least(cast(v_min_files_stamp as timestamp), cast(v_min_files_unsorted_stamp as timestamp)),

                      newest_file_stamp=greatest(cast(v_max_files_stamp as timestamp), cast(v_max_files_unsorted_stamp as timestamp)),

                      number_of_files=number_of_files + v_num_files_added,

                      number_of_files_unsorted=v_count_files_unsorted 

              WHERE newsgroup_id=v_newsgroup_id;

END;

$$ LANGUAGE plpgsql;

-- procedure to update newsgroup statistics (used to generate graphs)

-- the first argument is the day for which the statistics need to be updated

CREATE OR REPLACE FUNCTION update_newsgroup_statistics(DATE) RETURNS INTEGER AS $$

DECLARE

      v_stamp                         ALIAS FOR $1;

      v_record                        RECORD;

      v_count                         INTEGER;

      v_exists                        BOOLEAN;

BEGIN

      IF (v_stamp IS NULL) THEN

              RAISE EXCEPTION 'Missing argument v_stamp';

      END IF;

      v_count := 0;

      FOR v_record IN SELECT newsgroup_id, count(*) AS num FROM files WHERE stamp >= v_stamp AND stamp < v_stamp + interval '1 day' GROUP by newsgroup_id LOOP

              -- Do we already have statistics for this day?

              SELECT COUNT(*) = 1 INTO v_exists FROM newsgroup_statistics WHERE newsgroup_id=v_record.newsgroup_id AND day=v_stamp;

              IF (v_exists) THEN

                      UPDATE newsgroup_statistics SET num_files=v_record.num WHERE newsgroup_id=v_record.newsgroup_id AND day=v_stamp;

              ELSE

                      INSERT INTO newsgroup_statistics (newsgroup_id, day, num_files) VALUES (v_record.newsgroup_id, v_stamp, v_record.num);

              END IF;

              v_count := v_count + 1;

      END LOOP;

      RETURN v_count;

END;

$$ LANGUAGE plpgsql;

-- procedure to update ALL newsgroup statistics

CREATE OR REPLACE FUNCTION update_all_newsgroup_statistics() RETURNS INTEGER AS $$

DECLARE

      v_stamp                         DATE;

      v_count                         INTEGER;

BEGIN

      -- Find out the oldest file stamp

      SELECT min(oldest_file_stamp) INTO v_stamp FROM newsgroups;

      IF (v_stamp IS NULL) THEN

              RETURN 0;

      END IF;

      v_count := 0;

      -- Loop through all the days from oldest_file_stamp to current_timestamp

      LOOP

              PERFORM update_newsgroup_statistics(v_stamp);

              v_count := v_count + 1;

              -- Stop when we've reached today

              EXIT WHEN v_stamp = date_trunc('day', current_timestamp);

              v_stamp := CAST((CAST(v_stamp AS TIMESTAMP) + INTERVAL '1 day') AS DATE);

      END LOOP;

      RETURN v_count;

END;

$$ LANGUAGE plpgsql;

-- SELECT manage_partitions('collections', 'collection_id', NULL, NULL);

-- SELECT manage_partitions('collections_in_newsgroups', 'collection_id', 'collection_id', 'collections');

-- SELECT manage_partitions('files', 'file_id', 'collection_id', 'collections');

SELECT manage_partitions('files', 'file_id', NULL, NULL);

SELECT manage_partitions('file_parts', 'file_id', 'file_id', 'files');

SELECT manage_partitions('files_in_collections', 'file_id', 'file_id', 'files');