Revision 1818

online_search/indexer/tables_pgsql.sql (revision 1818)
1 1 
CREATE LANGUAGE plpgsql;
2 
CREATE TYPE file_type AS ENUM ('Unknown', 'RAR', 'PAR', 'PAR2', 'SFV', 'NFO', 'NZB', 'Other');
3 2 

                		
              

              

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


                		
              

              

                
                4
                
                  
CLUSTER row_counts USING row_counts_pkey;


                		
              

              

                
                5
                
                  

                		
              

              

                5
                6
                
                  
CREATE OR REPLACE FUNCTION count_trig()


                		
              

              

                6
                7
                
                  
       RETURNS TRIGGER AS


                		
              

              

                7
                8
                
                  
       $$


                		
              

              

                ...
                ...
                
              

              

                40
                41
                
                  
CREATE TABLE collections


                		
              

              

                41
                42
                
                  
       (


                		
              

              

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


                		
              

              

                
                44
                
                  
               newsgroup_id            INT NOT NULL REFERENCES newsgroups,


                		
              

              

                43
                45
                
                  
               stamp                   TIMESTAMP NOT NULL,


                		
              

              

                44
                46
                
                  
               hash                    BIGINT NOT NULL,


                		
              

              

                45
                47
                
                  
               collection_name         VARCHAR(256) NOT NULL,


                		
              

              

                ...
                ...
                
              

              

                47
                49
                
                  
               poster                  VARCHAR(128) NOT NULL,


                		
              

              

                48
                50
                
                  
               total_size              BIGINT NOT NULL,


                		
              

              

                49
                51
                
                  
               num_parts_found         INT NOT NULL,


                		
              

              

                50
                
                
                  
               num_parts_expected      INT NOT NULL


                		
              

              

                
                52
                
                  
               num_parts_expected      INT NOT NULL,


                		
              

              

                
                53
                
                  
               num_regular_files       INT NOT NULL,


                		
              

              

                
                54
                
                  
               num_par2_files          INT NOT NULL,


                		
              

              

                
                55
                
                  
               stamp_changed_in_db     TIMESTAMP NOT NULL


                		
              

              

                51
                56
                
                  
       );


                		
              

              

                
                57
                
                  
CREATE INDEX collections_hash_idx ON collections(hash);


                		
              

              

                
                58
                
                  
CLUSTER collections USING collections_pkey;


                		
              

              

                52
                59
                
                  

                		
              

              

                53
                
                
                  
CREATE TABLE collections_in_newsgroups


                		
              

              

                
                60
                
                  
CREATE TABLE files_in_collections


                		
              

              

                54
                61
                
                  
       (


                		
              

              

                55
                62
                
                  
               collection_id           BIGINT NOT NULL REFERENCES collections,


                		
              

              

                
                63
                
                  
               file_id                 BIGINT NOT NULL,


                		
              

              

                56
                64
                
                  
               stamp                   TIMESTAMP NOT NULL,


                		
              

              

                57
                
                
                  
               newsgroup_id            INT NOT NULL REFERENCES newsgroups,


                		
              

              

                58
                
                
                  
                                       PRIMARY KEY(collection_id, newsgroup_id)


                		
              

              

                
                65
                
                  
                                       PRIMARY KEY (collection_id, file_id)


                		
              

              

                59
                66
                
                  
       );


                		
              

              

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


                		
              

              

                
                68
                
                  
CREATE INDEX files_in_collections_unsorted_stamp_idx ON files_in_collections_unsorted(stamp);


                		
              

              

                
                69
                
                  
CLUSTER files_in_collections_unsorted USING files_in_collections_unsorted_pkey;


                		
              

              

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


                		
              

              

                
                71
                
                  
ALTER TABLE files_in_collections_unsorted NO INHERIT files_in_collections;


                		
              

              

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


                		
              

              

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


                		
              

              

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


                		
              

              

                60
                75
                
                  

                		
              

              

                61
                76
                
                  
CREATE SEQUENCE file_id_seq NO CYCLE;


                		
              

              

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


                		
              

              

                ...
                ...
                
              

              

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


                		
              

              

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


                		
              

              

                113
                128
                
                  

                		
              

              

                114
                
                
                  
CREATE TABLE files_in_newsgroups


                		
              

              

                115
                
                
                  
       (


                		
              

              

                116
                
                
                  
               file_id                 BIGINT NOT NULL REFERENCES files,


                		
              

              

                117
                
                
                  
               stamp                   TIMESTAMP NOT NULL,


                		
              

              

                118
                
                
                  
               newsgroup_id            INT NOT NULL REFERENCES newsgroups,


                		
              

              

                119
                
                
                  
                                       PRIMARY KEY(file_id, newsgroup_id)


                		
              

              

                120
                
                
                  
       );


                		
              

              

                
                129
                
                  
-- CREATE TABLE files_in_newsgroups


                		
              

              

                
                130
                
                  
--     (


                		
              

              

                
                131
                
                  
--             file_id                 BIGINT NOT NULL REFERENCES files,


                		
              

              

                
                132
                
                  
--             stamp                   TIMESTAMP NOT NULL,


                		
              

              

                
                133
                
                  
--             newsgroup_id            INT NOT NULL REFERENCES newsgroups,


                		
              

              

                
                134
                
                  
--                                     PRIMARY KEY(file_id, newsgroup_id)


                		
              

              

                
                135
                
                  
--     );


                		
              

              

                121
                136
                
                  

                		
              

              

                122
                137
                
                  
CREATE TABLE statistics


                		
              

              

                123
                138
                
                  
       (


                		
              

              

                ...
                ...
                
              

              

                203
                218
                
                  
               IF (v_base_table = 'file_parts') THEN


                		
              

              

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


                		
              

              

                205
                220
                
                  
               END IF;


                		
              

              

                
                221
                
                  
               IF (v_base_table = 'files_in_collections') THEN


                		
              

              

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


                		
              

              

                
                223
                
                  
               END IF;


                		
              

              

                206
                224
                
                  

                		
              

              

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


                		
              

              

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


                		
              

              

                ...
                ...
                
              

              

                264
                282
                
                  
               BEGIN


                		
              

              

                265
                283
                
                  
                       EXECUTE 'DROP TABLE ' || v_partition_name;


                		
              

              

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


                		
              

              

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


                		
              

              

                
                285
                
                  

                		
              

              

                
                286
                
                  
                       IF (v_base_table = 'files') THEN


                		
              

              

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


                		
              

              

                
                288
                
                  
                       END IF;


                		
              

              

                268
                289
                
                  
               EXCEPTION


                		
              

              

                269
                290
                
                  
                       WHEN dependent_objects_still_exist THEN


                		
              

              

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


                		
              

              

                ...
                ...
                
              

              

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


                		
              

              

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


                		
              

              

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


                		
              

              

                529
                
                
                  
-- SELECT manage_partitions('files_in_newsgroups', 'file_id', 'file_id', 'files');


                		
              

              

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


                		
              

              

                530
                551
                
                  

                		
              

              

                531
                552
                
                  

                		
              

            
          

        

        
Also available in: Unified diff