1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
CREATE OR REPLACE FUNCTION tools.setup_change_polling_for_table_pg(
	target_mv_name TEXT,
	target_schema_name TEXT, 
	target_table_name TEXT) 
RETURNS BOOLEAN AS $$
    -- 
    -- Specify:
    --	 target_mv_name - a table name for the materialized view (optional) 
	--   target_schema_name - name of the schema of the table to be monitored for changes
	--   target_table_name - name of table to be monitored for changes.	
    -- 
    --  1. Check for the existence of two tables where the changes will be recorded.
    --  2. A. Check that the 'materialized view' template table does not already exist.
    --     B. Create a template 'materialized view' table for recording the table contents.
    --     C. Create a history table for recording changes in the 'materialized view' table.    
    --  3. A. Check that a row does not already exist for the 'materialized view' in 
    --        table 'tools.cs_materialized_views'.
    --     B. Insert a row for the 'materialized view' into the 'tools.cs_materialized_views'
    --     table.
    --  4. Record the primary key for the row inserted into 'tools.cs_materialized_views'
    --     into some 'meta' table so that we know which 'id' to pass to the function
    --     tools.cs_refresh_mviews(integer) when we want to carry out a 'polling'.
    --      A. I've defined a function, get_primary_keys() for this purpose that
    --         should work on a table.
    --      B. What to do in the case of a view?  
    --          Use any columns defined in a GROUP BY clause?
    --          Look for columns wrapped by DISTINCT() ?
    --          Look for the primary keys used in the underlying tables?
    --  TODO:
	--		Extract specific database and functional role names uses in statements into variables.
	--			E.g., 'Finance', finance, finance_ro, feed_id    
	--
    --    Where is the schedule for the polling defined?
    --			In the crontab for the script, polling all monitored tables each run.
    --			A better way would be to utilize something like run_schedule field
    --			of table change_polling_meta_info_and_status
    --    Where is the rss_feed defined where changes we'll be reported?
    --			A combination of the automatically populated - at the bottom of
    --			of this script - rss_assigments table, and the manually populated
    --			rss_feeds table.
    --    Where is the function that generates the RSS feed items and 'linked pages'?
    --			It's the python script run from cron:
    --				/PyDBMonitorRSS/src/pydbmonrss/pollingDataChangeRSSEmailer.py
    --				which calls the plperl function: change_polling_record_differences_perl
    -- SAMPLE USAGE
    --   SELECT * FROM tools.setup_change_polling_for_table_pg('like_poll', 'dudes', 'stuff');

    DECLARE
    	mv_schema TEXT := 'matviews';
		mv_name TEXT;    	
		full_mv_name TEXT;
		mv_id INT;
		mv_fields TEXT;
		primary_keys TEXT;
		one_key TEXT;
		mv_query_string TEXT;				
    	full_table_name TEXT;
    	full_history_table_name TEXT;
		target_schema_oid OID;
		target_table_oid OID;		
		sql_command_text TEXT;
		monitored_table_id INT;
		rss_assignment_id INT;
		
    BEGIN		    
		
	    SELECT INTO full_table_name target_schema_name||'.'||target_table_name;
		
	    target_table_oid := tools.get_table_oid(target_schema_name, target_table_name);

		-- If the materialized view label is not specified, create one based on the
		-- schema and name of the target table.
		IF (target_mv_name IS NULL) THEN
			SELECT INTO mv_name 'mv_' ||quote_ident(target_schema_name)||'_'||quote_ident(target_table_name);
		ELSE
			mv_name := target_mv_name;
		END IF;	  

		SELECT INTO full_mv_name quote_ident(mv_schema)||'.'||quote_ident(mv_name);
		
		IF (tools.does_table_exist(mv_schema, mv_name)) THEN
			RAISE EXCEPTION 'MV = % already exists', full_mv_name;
		END IF;						
		
		-- Create the template table for the materialized view.
    	sql_command_text := 'CREATE TABLE ' || full_mv_name || ' AS SELECT * FROM '
    	|| full_table_name;
   		EXECUTE sql_command_text;    
   		-- Add the primary keys from the monitored table on to the materializedview
   		-- template table.
   		sql_command_text := 'SELECT * FROM tools.get_primary_keys(' 
						|| quote_literal(target_schema_name) ||','
						|| quote_literal(target_table_name) ||')'; 		        
    	primary_keys := '';
    	
		FOR one_key IN EXECUTE sql_command_text	LOOP
			BEGIN
				primary_keys := primary_keys || one_key || ',';
			END;
		END LOOP;
		
		IF (primary_keys = '') THEN
			RAISE EXCEPTION 'This table cannot be monitored because it does not have a primary key: %', full_table_name;
		END IF;
		-- TODO
		--    Handle tables with no primary key and only a SERIAL column.
		--    Handle views with DISTINCT or GROUP BY's by converting those into primary keys.
		
		primary_keys := trim(trailing ',' from primary_keys);
	   		
		sql_command_text := 'ALTER TABLE ' || full_mv_name || 
		                    ' ADD PRIMARY KEY (' || primary_keys ||')';		                    
		EXECUTE sql_command_text;                  
		
		-- Give Finance permission to view mv table.
		sql_command_text := 'GRANT ALL ON TABLE '
			|| full_mv_name || ' TO finance';
		EXECUTE sql_command_text; 
            
		-- Create a history table to record complete log of changes to the monitored table
		SELECT INTO full_history_table_name quote_ident(mv_schema)
					||'.'||quote_ident(mv_name)
					||'_hist';
				
		sql_command_text := 'CREATE TABLE ' || full_history_table_name || ' AS SELECT * FROM '
    	|| full_table_name;
   		EXECUTE sql_command_text;		
		-- Add some additional fields to the history table and mark the original values
		-- as version one.
		
		-- Add change_type, char, default value 'O' for 'Original'
		sql_command_text := 'ALTER TABLE ' || full_history_table_name || 
							' ADD COLUMN change_type char(1) DEFAULT '||quote_literal('O');                    
		EXECUTE sql_command_text; 			
		-- Add date/timestamp, default value now()
		sql_command_text := 'ALTER TABLE ' || full_history_table_name || 
							' ADD COLUMN change_time timestamp with time zone DEFAULT now()';		                    
		EXECUTE sql_command_text; 		
		-- Add version number, int, default value 0
		sql_command_text := 'ALTER TABLE ' || full_history_table_name || 
		                    ' ADD COLUMN change_version integer DEFAULT 0';		                    
		EXECUTE sql_command_text; 
		-- Add primary key, that inclues the version field.
		sql_command_text := 'ALTER TABLE ' || full_history_table_name || 
		                    ' ADD PRIMARY KEY (' || primary_keys ||', change_version)';		                    
		EXECUTE sql_command_text; 
		
		-- Give permission to view history table.
		sql_command_text := 'GRANT SELECT ON TABLE '
			|| full_history_table_name || ' TO finance_ro';
		EXECUTE sql_command_text; 
		
   		-- Determine if row/entry already exists in 'tools.cs_materialized_views'
		sql_command_text :=	'SELECT mv_id FROM tools.cs_materialized_views WHERE '
							|| ' mv_schema = ' || quote_literal(mv_schema) 
							|| ' AND mv_name = ' || quote_literal(mv_name);
		EXECUTE sql_command_text INTO mv_id;
        -- IF row/entry DOES NOT already exists in 'tools.cs_materialized_views'
        -- for the target table THEN insert one.
		IF (mv_id IS NULL) THEN
			mv_fields := '';
			mv_query_string := 'SELECT * FROM ' || full_table_name;
			sql_command_text :=	'INSERT INTO tools.cs_materialized_views(mv_schema,'
								|| 'mv_name,mv_fields,mv_query,sort_key,mv_backups)'
								|| ' VALUES ('
								|| quote_literal(mv_schema) || ',' 
								|| quote_literal(mv_name) || ',' 
								|| quote_literal(mv_fields) || ',' 
								|| quote_literal(mv_query_string) || ','
								|| '0' || ','
								|| '1' || ')'
								|| 'RETURNING mv_id';
								            			
			EXECUTE sql_command_text INTO mv_id;
			-- RAISE EXCEPTION 'mv_id for new Row in tools.cs_materialized_views = %', mv_id;
   		ELSE
			RAISE EXCEPTION 'Row already exists in tools.cs_materialized_views. mv_id = %', mv_id;
   		END IF;
   		   		
   		-- Store some Meta information about the monitored table in another table
		-- that will be utilized by our python script that runs from cron. 
		
		sql_command_text := 'INSERT INTO tools.change_polling_meta_info_and_status(
            monitored_table_schema, monitored_table_name, mv_schema, mv_name, 
            mat_views_id, primary_key_list, time_inaugurated, active)
    		VALUES (' || quote_literal(target_schema_name) ||','
    				  || quote_literal(target_table_name)  || ',' 
    				  || quote_literal(mv_schema) || ',' 
    				  || quote_literal(mv_name) || ',' 
    				  || mv_id || ',' 
    				  || quote_literal(primary_keys) 
    				  || ', now(), TRUE)'
    				  || 'RETURNING monitored_table_id';

        EXECUTE sql_command_text INTO monitored_table_id;
        
        sql_command_text := 'INSERT INTO tools.rss_assignments(
            monitored_table_id, feed_id, feed_category, match_filter, 
            output_view_filter) VALUES (' 
        			  || monitored_table_id || ','
			          || 1 || ','
			          || quote_literal('Finance') || ','
			          || quote_literal('*') || ','
					  || quote_literal('*') || 
		              ')'
					  || 'RETURNING assignment_id';
		EXECUTE sql_command_text INTO rss_assignment_id;
		
        RETURN TRUE;
    END;            
    
$$ LANGUAGE 'plpgsql'  SECURITY DEFINER;


ALTER FUNCTION tools.setup_change_polling_for_table_pg(text, text, text) OWNER TO finance
GRANT EXECUTE ON FUNCTION tools.setup_change_polling_for_table_pg(text, text, text) TO slangley;