Index: openacs-4/packages/forums/forums.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/forums.info,v
diff -u -r1.48 -r1.49
--- openacs-4/packages/forums/forums.info 8 Nov 2017 12:05:55 -0000 1.48
+++ openacs-4/packages/forums/forums.info 10 Nov 2017 17:21:05 -0000 1.49
@@ -9,7 +9,7 @@
f
t
-
+
OpenACS
Online discussion forums.
2017-08-06
@@ -19,7 +19,7 @@
2
#forums.Forums#
-
+
Index: openacs-4/packages/forums/sql/oracle/forums-reading-info-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/forums-reading-info-create.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/forums/sql/oracle/forums-reading-info-create.sql 1 Jun 2007 15:56:57 -0000 1.1
+++ openacs-4/packages/forums/sql/oracle/forums-reading-info-create.sql 10 Nov 2017 17:21:05 -0000 1.2
@@ -31,20 +31,13 @@
);
create index forums_reading_info_user_index on forums_reading_info (user_id);
create index forums_reading_info_forum_idx on forums_reading_info (root_message_id);
+create index forums_reading_info_forum_forum_index on forums_reading_info (forum_id);
-create table forums_reading_info_user (
- forum_id integer
- constraint forums_read_iu_forum_id_fk
- references forums_forums (forum_id) on delete cascade,
- user_id integer
- constraint forums_read_iu_user_id_fk
- references users(user_id) on delete cascade
- constraint forums_read_iu_user_id_nn
- not null,
- threads_read integer
- default 0
- not null,
- constraint forums_reading_info_user_pk primary key (forum_id,user_id)
-);
-
-
+-- this was a sort of materialized view, but consistency checks made
+-- code complicated. Redefined as a view
+create or replace view forums_reading_info_user as
+ select forum_id,
+ user_id,
+ count(*) as threads_read
+ from forums_reading_info
+ group by forum_id, user_id;
Index: openacs-4/packages/forums/sql/oracle/forums-reading-info-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/forums-reading-info-drop.sql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/forums/sql/oracle/forums-reading-info-drop.sql 9 Nov 2008 23:29:26 -0000 1.2
+++ openacs-4/packages/forums/sql/oracle/forums-reading-info-drop.sql 10 Nov 2017 17:21:05 -0000 1.3
@@ -13,5 +13,5 @@
-- Tables to reading information
drop package forum_reading_info;
-drop table forums_reading_info_user;
-drop table forums_reading_info;
\ No newline at end of file
+drop view forums_reading_info_user;
+drop table forums_reading_info;
Index: openacs-4/packages/forums/sql/oracle/forums-reading-info-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/forums-reading-info-package-create.sql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/forums/sql/oracle/forums-reading-info-package-create.sql 9 Nov 2008 23:29:26 -0000 1.2
+++ openacs-4/packages/forums/sql/oracle/forums-reading-info-package-create.sql 10 Nov 2017 17:21:05 -0000 1.3
@@ -28,30 +28,12 @@
p_user_id in users.user_id%TYPE
);
--- move thread to other forum
- procedure move_update (
- p_message_id in forums_messages.message_id%TYPE,
- p_old_forum_id in forums_forums.forum_id%TYPE,
- p_new_forum_id in forums_forums.forum_id%TYPE
- );
-
--- move thread to other thread
- procedure move_thread_th_update (
- p_source_message_id in forums_messages.message_id%TYPE,
- p_source_forum_id in forums_forums.forum_id%TYPE,
- p_target_message_id in forums_messages.message_id%TYPE
- );
-
-- move message to other thread
procedure move_thread_update (
p_source_message_id in forums_messages.message_id%TYPE,
- p_source_old_root_message_id in forums_messages.message_id%TYPE,
p_target_message_id in forums_messages.message_id%TYPE
);
--- recount reading_info_user from reading_info
- procedure repair_reading_info;
-
end forum_reading_info;
/
show errors
@@ -84,7 +66,6 @@
delete from forums_reading_info
where root_message_id = p_message_id and
user_id = v_reading.user_id;
- update forums_reading_info_user set threads_read=threads_read-1 where forum_id=v_forum_id and user_id=v_reading.user_id;
end loop;
@@ -111,14 +92,12 @@
if v_read_p = 0 then
insert into forums_reading_info
- (root_message_id,user_id)
+ (root_message_id,user_id,forum_id)
values
- (v_message.message_id,p_user_id);
+ (v_message.message_id,p_user_id,p_forum_id);
end if;
end loop;
- delete from forums_reading_info_user where forum_id = p_forum_id and user_id = p_user_id;
- insert into forums_reading_info_user (forum_id,user_id,threads_read) VALUES (p_forum_id,p_user_id,(select approved_thread_count from forums_forums where forum_id = p_forum_id));
end user_add_forum;
@@ -130,7 +109,6 @@
is
v_forum_id integer;
v_read_p integer;
- v_exists integer;
begin
begin
select forum_id into v_forum_id from forums_messages where message_id = p_root_message_id;
@@ -143,103 +121,16 @@
if v_read_p = 0 then
- insert into forums_reading_info (root_message_id,user_id) values (p_root_message_id,p_user_id);
- SELECT count(*) into v_exists FROM forums_reading_info_user WHERE forum_id=v_forum_id AND user_id=p_user_id;
+ insert into forums_reading_info (root_message_id,user_id,forum_id)
+ values (p_root_message_id,p_user_id,v_forum_id);
- if v_exists > 0 then
-
- UPDATE forums_reading_info_user SET threads_read=threads_read+1 WHERE forum_id=v_forum_id AND user_id=p_user_id;
-
- else
-
- INSERT INTO forums_reading_info_user(forum_id,user_id,threads_read) VALUES (v_forum_id,p_user_id,1);
-
- end if;
-
end if;
end user_add_msg;
-
-
--- move thread to other forum
- procedure move_update (
- p_message_id in forums_messages.message_id%TYPE,
- p_old_forum_id in forums_forums.forum_id%TYPE,
- p_new_forum_id in forums_forums.forum_id%TYPE
- ) is
- v_users forums_reading_info%ROWTYPE;
- v_threads integer;
- begin
-
- for v_users in (select user_id from forums_reading_info where root_message_id = p_message_id)
-
- loop
- -- down the number of threads read in old forum
- update forums_reading_info_user set threads_read=threads_read-1 where forum_id=p_old_forum_id and user_id=v_users.user_id;
- -- up the number of thread read in new forum
- select count(*) into v_threads from forums_reading_info_user where forum_id = p_new_forum_id and user_id = v_users.user_id;
-
- if v_threads = 0 then
-
- insert into forums_reading_info_user (forum_id,user_id,threads_read)
- values (p_new_forum_id,v_users.user_id,1);
-
- else
-
- update forums_reading_info_user set threads_read = threads_read + 1
- where forum_id = p_new_forum_id and user_id = v_users.user_id;
-
- end if;
-
- end loop;
- end move_update;
-
--- move thread to other thread
- procedure move_thread_th_update (
- p_source_message_id in forums_messages.message_id%TYPE,
- p_source_forum_id in forums_forums.forum_id%TYPE,
- p_target_message_id in forums_messages.message_id%TYPE
- ) is
- v_target_forum_id forums_forums.forum_id%TYPE;
- v_users forums_reading_info%ROWTYPE;
- begin
- begin
- select forum_id into v_target_forum_id from forums_messages where message_id = p_target_message_id;
- exception
- when no_data_found then
- v_target_forum_id := null;
- end;
-
- -- for all users that have read target, but not the source, remove target_info
-
- for v_users in (select user_id from forums_reading_info fri where root_message_id = p_target_message_id and not exists (select 1 from forums_reading_info where root_message_id = p_source_message_id and user_id = fri.user_id))
-
- loop
-
- delete from forums_reading_info where root_message_id = p_target_message_id and user_id = v_users.user_id;
- -- down the number of threads read in target forum
- update forums_reading_info_user set threads_read=threads_read-1 where forum_id = v_target_forum_id and user_id = v_users.user_id;
-
- end loop;
- -- for all users that have read source, down the nummber of thread in source forum and remove reading info four source message since it no longer is root_message_id
-
- for v_users in (select user_id from forums_reading_info where root_message_id = p_source_message_id)
-
- loop
-
- delete from forums_reading_info where root_message_id=p_source_message_id and user_id=v_users.user_id;
-
- update forums_reading_info_user set threads_read=threads_read-1 where forum_id = p_source_forum_id and user_id = v_users.user_id;
-
- end loop;
-
- end move_thread_th_update;
-
-- move message to other thread
procedure move_thread_update (
p_source_message_id in forums_messages.message_id%TYPE,
- p_source_old_root_message_id in forums_messages.message_id%TYPE,
p_target_message_id in forums_messages.message_id%TYPE
) is
v_target_forum_id forums_forums.forum_id%TYPE;
@@ -258,39 +149,10 @@
delete from forums_reading_info where root_message_id = p_target_message_id and user_id = v_users.user_id;
-- down the number of threads read in target forum
-
- update forums_reading_info_user set threads_read = threads_read-1
- where forum_id = v_target_forum_id and user_id = v_users.user_id;
end loop;
end move_thread_update;
- procedure repair_reading_info is
- cursor c1 is
- select user_id, forum_id, count(root_message_id) as threads_read
- from (
- select user_id,
- (select forum_id from forums_messages where message_id = root_message_id) as forum_id,
- root_message_id
- from forums_reading_info
- ) f
- group by forum_id,user_id;
-
- begin
-
- delete from forums_reading_info_user;
-
- for v_users in c1
- loop
-
- insert into forums_reading_info_user (forum_id,user_id,threads_read)
- values
- (v_users.forum_id,v_users.user_id,v_users.threads_read);
-
- end loop;
-
- end repair_reading_info;
-
end forum_reading_info;
/
-show errors
\ No newline at end of file
+show errors
Index: openacs-4/packages/forums/sql/oracle/upgrade/upgrade-1.3.1d2-1.3.1d3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/upgrade/upgrade-1.3.1d2-1.3.1d3.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/oracle/upgrade/upgrade-1.3.1d2-1.3.1d3.sql 10 Nov 2017 17:21:05 -0000 1.1
@@ -0,0 +1,186 @@
+begin;
+
+-- As it comes out, forums has some embedded views counter
+-- feature. This is not used upstream, but it is in some local
+-- installations we know of. As on these table forums_reading_info can
+-- grow very large, there were reports of bad performances. This
+-- update has the goal to optimize and streamline current reading
+-- count implementation. During this, some inconsistency between
+-- oracle and postgres and duplication was found and addressed.
+
+-- data model
+
+drop table forums_reading_info_user;
+
+alter table forums_reading_info
+ add column forum_id integer
+ constraint forum_read_forum_id_fk
+ references forums_forums (forum_id)
+ on delete cascade
+ constraint forums_read_forum_id_nn
+ not null;
+
+create index forums_reading_info_forum_forum_index on forums_reading_info (forum_id);
+
+-- this was a sort of materialized view, but consistency checks made
+-- code complicated. Redefined as a view
+create or replace view forums_reading_info_user as
+ select forum_id,
+ user_id,
+ count(*) as threads_read
+ from forums_reading_info
+ group by forum_id, user_id;
+
+
+-- functions
+
+create or replace package forum_reading_info
+as
+-- remove reading_info for thread (upon new message, upon message deletion, or state change)
+ procedure remove_msg (
+ p_message_id in forums_messages.message_id%TYPE
+ );
+
+-- mark_all_read
+ procedure user_add_forum (
+ p_forum_id in forums_forums.forum_id%TYPE,
+ p_user_id in users.user_id%TYPE
+ );
+
+-- mark message read for user
+ procedure user_add_msg (
+ p_root_message_id in forums_messages.message_id%TYPE,
+ p_user_id in users.user_id%TYPE
+ );
+
+-- move message to other thread
+ procedure move_thread_update (
+ p_source_message_id in forums_messages.message_id%TYPE,
+ p_target_message_id in forums_messages.message_id%TYPE
+ );
+
+end forum_reading_info;
+/
+show errors
+
+
+
+create or replace package body forum_reading_info
+as
+-- remove reading_info for thread (upon new message, upon message deletion, or state change)
+ procedure remove_msg (
+ p_message_id in forums_messages.message_id%TYPE
+ )
+ is
+ v_forum_id forums_messages.forum_id%TYPE;
+ cursor c_reading is select user_id from forums_reading_info where root_message_id = p_message_id;
+
+ begin
+
+ --Exception no_data_found if select into hasn't rows
+ begin
+ select forum_id into v_forum_id from forums_messages where message_id = p_message_id;
+ exception
+ when no_data_found then
+ v_forum_id := null;
+ end;
+
+ for v_reading in c_reading
+ loop
+
+ delete from forums_reading_info
+ where root_message_id = p_message_id and
+ user_id = v_reading.user_id;
+
+ end loop;
+
+
+ end remove_msg;
+
+-- mark_all_read:
+
+ procedure user_add_forum (
+ p_forum_id in forums_forums.forum_id%TYPE,
+ p_user_id in users.user_id%TYPE
+ )
+ is
+ v_message forums_messages_approved%ROWTYPE;
+ v_read_p integer;
+ begin
+
+ for v_message in (select message_id
+ from forums_messages_approved
+ where forum_id = p_forum_id
+ and parent_id is null)
+ loop
+ select count(*) into v_read_p from forums_reading_info where user_id = p_user_id and root_message_id = v_message.message_id;
+
+ if v_read_p = 0 then
+ insert into forums_reading_info
+ (root_message_id,user_id,forum_id)
+ values
+ (v_message.message_id,p_user_id,p_forum_id);
+ end if;
+ end loop;
+
+ end user_add_forum;
+
+
+-- mark message read for user
+ procedure user_add_msg (
+ p_root_message_id in forums_messages.message_id%TYPE,
+ p_user_id in users.user_id%TYPE
+ )
+ is
+ v_forum_id integer;
+ v_read_p integer;
+ begin
+ begin
+ select forum_id into v_forum_id from forums_messages where message_id = p_root_message_id;
+ exception
+ when no_data_found then
+ v_forum_id := null;
+ end;
+
+ select count(*) into v_read_p from forums_reading_info where user_id = p_user_id and root_message_id = p_root_message_id;
+
+ if v_read_p = 0 then
+
+ insert into forums_reading_info (root_message_id,user_id,forum_id)
+ values (p_root_message_id,p_user_id,v_forum_id);
+
+ end if;
+
+ end user_add_msg;
+
+-- move message to other thread
+ procedure move_thread_update (
+ p_source_message_id in forums_messages.message_id%TYPE,
+ p_target_message_id in forums_messages.message_id%TYPE
+ ) is
+ v_target_forum_id forums_forums.forum_id%TYPE;
+ v_users forums_reading_info%ROWTYPE;
+ begin
+ begin
+ select forum_id into v_target_forum_id from forums_messages where message_id = p_target_message_id;
+ exception
+ when no_data_found then
+ v_target_forum_id := null;
+ end;
+
+
+ for v_users in (select user_id from forums_reading_info fri where root_message_id = p_target_message_id and not exists(select 1 from forums_reading_info where root_message_id = p_source_old_root_message_id and user_id = fri.user_id))
+ loop
+
+ delete from forums_reading_info where root_message_id = p_target_message_id and user_id = v_users.user_id;
+ -- down the number of threads read in target forum
+ end loop;
+
+ end move_thread_update;
+
+end forum_reading_info;
+/
+show errors
+
+
+end;
Index: openacs-4/packages/forums/sql/postgresql/forums-reading-info-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-reading-info-create.sql,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/forums/sql/postgresql/forums-reading-info-create.sql 7 Aug 2017 23:48:11 -0000 1.4
+++ openacs-4/packages/forums/sql/postgresql/forums-reading-info-create.sql 10 Nov 2017 17:21:05 -0000 1.5
@@ -12,31 +12,29 @@
default current_timestamp
constraint forum_read_datetime_nn
not null,
- constraint forums_reading_info_pk primary key (root_message_id,user_id)
+ forum_id integer
+ constraint forum_read_forum_id_fk
+ references forums_forums (forum_id)
+ on delete cascade
+ constraint forums_read_forum_id_nn
+ not null,
+ constraint forums_reading_info_pk primary key (root_message_id,user_id)
);
create index forums_reading_info_user_index on forums_reading_info (user_id);
create index forums_reading_info_forum_message_index on forums_reading_info (root_message_id);
+create index forums_reading_info_forum_forum_index on forums_reading_info (forum_id);
-create table forums_reading_info_user (
- forum_id integer
- constraint forums_read_forum_id_fk
- references forums_forums (forum_id) on delete cascade,
- user_id integer
- constraint forums_read_user_id_fk
- references users(user_id) on delete cascade
- constraint forums_read_user_id_nn
- not null,
- threads_read integer
- default 0
- not null,
- constraint forums_reading_info_user_pk primary key (forum_id,user_id)
-);
+create or replace view forums_reading_info_user as
+ select forum_id,
+ user_id,
+ count(*) as threads_read
+ from forums_reading_info
+ group by forum_id, user_id;
--- remove reading_info for thread (upon new message, upon message deletion, or state change)
+-- mark message as unread
-
-- added
select define_function_args('forums_reading_info__remove_msg','message_id');
@@ -47,29 +45,16 @@
p_message_id integer
) RETURNS integer AS $$
DECLARE
- v_forum_id integer;
- v_reading RECORD;
BEGIN
- select forum_id from forums_messages where message_id = p_message_id into v_forum_id;
- for v_reading in select user_id
- from forums_reading_info
- where root_message_id = p_message_id
- loop
- delete from forums_reading_info
- where root_message_id = p_message_id and
- user_id = v_reading.user_id;
- UPDATE forums_reading_info_user SET threads_read=threads_read-1 WHERE forum_id= v_forum_id and user_id = v_reading.user_id;
- end loop;
-
+ delete from forums_reading_info
+ where root_message_id = p_message_id;
return 0;
END;
-
$$ LANGUAGE plpgsql;
--- mark_all_read:
+-- mark all messages in forum as read
-
-- added
select define_function_args('forums_reading_info__user_add_forum','forum_id,user_id');
@@ -81,32 +66,32 @@
p_user_id integer
) RETURNS integer AS $$
DECLARE
- v_message RECORD;
- v_read_p RECORD;
+ v_message_id integer;
BEGIN
- for v_message in select message_id
- from forums_messages_approved
- where forum_id = p_forum_id
- and parent_id is null
- loop
- select into v_read_p * from forums_reading_info where user_id = p_user_id and root_message_id = v_message.message_id;
- if NOT FOUND
- then
- insert into forums_reading_info
- (root_message_id,user_id)
- values
- (v_message.message_id,p_user_id);
- end if;
+ for v_message_id in
+ select message_id
+ from forums_messages_approved m
+ where forum_id = p_forum_id
+ and parent_id is null
+ and not exists (select 1 from forums_reading_info
+ where user_id = p_user_id
+ and root_message_id = m.message_id) loop
+ insert into forums_reading_info (
+ root_message_id,
+ user_id,
+ forum_id
+ ) values (
+ v_message_id,
+ p_user_id,
+ p_forum_id
+ );
end loop;
- delete from forums_reading_info_user where forum_id = p_forum_id and user_id = p_user_id;
- insert into forums_reading_info_user (forum_id,user_id,threads_read) VALUES (p_forum_id,p_user_id,(select approved_thread_count from forums_forums where forum_id = p_forum_id));
return 0;
END;
-
$$ LANGUAGE plpgsql;
--- mark message read for user
+-- mark single message as read by user
-- added
select define_function_args('forums_reading_info__user_add_msg','root_message_id,user_id');
@@ -119,170 +104,58 @@
p_user_id integer
) RETURNS integer AS $$
DECLARE
- v_read_p RECORD;
- v_forum_id integer;
- v_exists boolean;
+ v_forum_id integer;
BEGIN
- select forum_id from forums_messages where message_id = p_root_message_id into v_forum_id;
- select into v_read_p * from forums_reading_info where user_id = p_user_id and root_message_id = p_root_message_id;
- if NOT FOUND
- then
- insert into forums_reading_info
- (root_message_id,user_id)
- values
- (p_root_message_id,p_user_id);
- SELECT EXISTS(SELECT 1 FROM forums_reading_info_user WHERE forum_id=v_forum_id AND user_id=p_user_id) INTO v_exists;
- if v_exists = true then
- UPDATE forums_reading_info_user SET threads_read=threads_read+1 WHERE forum_id=v_forum_id AND user_id=p_user_id;
- else
- INSERT INTO forums_reading_info_user(forum_id,user_id,threads_read) VALUES (v_forum_id,p_user_id,1);
- end if;
+ if NOT exists (select 1 from forums_reading_info
+ where user_id = p_user_id
+ and root_message_id = p_root_message_id) then
+
+ insert into forums_reading_info (
+ root_message_id,
+ user_id,
+ forum_id
+ ) values (
+ p_root_message_id,
+ p_user_id,
+ (select forum_id from forums_messages
+ where message_id = p_root_message_id)
+ );
end if;
return 0;
END;
-
$$ LANGUAGE plpgsql;
--- move thread to other forum
-
--- added
-select define_function_args('forums_message__move_update_reading_info','message_id,old_forum_id,new_forum_id');
-
---
--- procedure forums_message__move_update_reading_info/3
---
-CREATE OR REPLACE FUNCTION forums_message__move_update_reading_info(
- p_message_id integer,
- p_old_forum_id integer,
- p_new_forum_id integer
-) RETURNS integer AS $$
-DECLARE
- v_message record;
- v_users record;
- v_read_p record;
- v_threads integer;
-BEGIN
- raise notice 'updating for message %', p_message_id;
- for v_users in select user_id from forums_reading_info where root_message_id = p_message_id
- loop
- raise notice 'updating for user %', v_users.user_id;
- -- down the number of threads read in old forum
- update forums_reading_info_user set threads_read = threads_read - 1
- where forum_id = p_old_forum_id and user_id = v_users.user_id;
- -- up the number of thread read in new forum
- select count(*) into v_threads from forums_reading_info_user
- where forum_id = p_new_forum_id and user_id = v_users.user_id;
- if v_threads = 0 then
- insert into forums_reading_info_user (forum_id,user_id,threads_read)
- values (p_new_forum_id,v_users.user_id,1);
- else
- update forums_reading_info_user set threads_read = threads_read + 1
- where forum_id = p_new_forum_id and user_id = v_users.user_id;
- end if;
- end loop;
-
- return 1;
-
-END;
-$$ LANGUAGE plpgsql;
-
-
-- move thread to other thread
-
-- added
-select define_function_args('forums_message__move_thread_thread_update_reading_info','source_message_id,source_forum_id,target_message_id');
+select define_function_args('forums_reading_info__move_thread_update','source_message_id,target_message_id');
--
--- procedure forums_message__move_thread_thread_update_reading_info/3
+-- procedure forums_reading_info__move_thread_update/2
--
-CREATE OR REPLACE FUNCTION forums_message__move_thread_thread_update_reading_info(
+CREATE OR REPLACE FUNCTION forums_reading_info__move_thread_update(
p_source_message_id integer,
- p_source_forum_id integer,
p_target_message_id integer
) RETURNS integer AS $$
DECLARE
- v_target_forum_id integer;
- v_users record;
BEGIN
- select forum_id from forums_messages where message_id = p_target_message_id into v_target_forum_id;
- -- for all users that have read target, but not the source, remove target_info
- for v_users in select user_id from forums_reading_info fri where root_message_id = p_target_message_id and not exists(select 1 from forums_reading_info where root_message_id = p_source_message_id and user_id = fri.user_id)
- loop
- delete from forums_reading_info where root_message_id = p_target_message_id and user_id = v_users.user_id;
- -- down the number of threads read in target forum
- update forums_reading_info_user set threads_read = threads_read - 1
- where forum_id = v_target_forum_id and user_id = v_users.user_id;
- end loop;
- -- for all users that have read source, down the nummber of thread in source forum and remove reading info four source message since it no longer is root_message_id
- for v_users in select user_id from forums_reading_info where root_message_id = p_source_message_id
- loop
- delete from forums_reading_info where root_message_id = p_source_message_id and user_id = v_users.user_id;
- update forums_reading_info_user set threads_read = threads_read - 1
- where forum_id = p_source_forum_id and user_id = v_users.user_id;
- end loop;
+ -- for all users that have read target, but not the source, remove
+ -- target_info
+ delete from forums_reading_info i
+ where root_message_id = p_target_message_id
+ and not exists (select 1 from forums_reading_info
+ where root_message_id = p_source_message_id
+ and user_id = i.user_id);
+
+ -- for all users that have read source, remove reading info four
+ -- source message since it no longer is root_message_id
+ delete from forums_reading_info
+ where root_message_id = p_source_message_id;
+
return 1;
-
END;
$$ LANGUAGE plpgsql;
--- move message to other thread
-
--- added
-select define_function_args('forums_message__move_thread_update_reading_info','source_message_id,source_old_root_message_id,target_message_id');
-
---
--- procedure forums_message__move_thread_update_reading_info/3
---
-CREATE OR REPLACE FUNCTION forums_message__move_thread_update_reading_info(
- p_source_message_id integer,
- p_source_old_root_message_id integer,
- p_target_message_id integer
-) RETURNS integer AS $$
-DECLARE
- v_target_forum_id integer;
- v_users record;
-BEGIN
- select forum_id from forums_messages where message_id = p_target_message_id into v_target_forum_id;
- raise notice 'v_target_forum_id %', v_target_forum_id;
- -- for all users that have read target, but not the source, remove target_info
- for v_users in select user_id from forums_reading_info fri where root_message_id = p_target_message_id and not exists(select 1 from forums_reading_info where root_message_id = p_source_old_root_message_id and user_id = fri.user_id)
- loop
- delete from forums_reading_info where root_message_id = p_target_message_id and user_id = v_users.user_id;
- -- down the number of threads read in target forum
- update forums_reading_info_user set threads_read = threads_read - 1
- where forum_id = v_target_forum_id and user_id = v_users.user_id;
- end loop;
- return 1;
-
-END;
-$$ LANGUAGE plpgsql;
-
-
--- recount reading_info_user from reading_info
-
-select define_function_args('forums_message__repair_reading_info','');
-
---
--- procedure forums_message__repair_reading_info/0
---
-CREATE OR REPLACE FUNCTION forums_message__repair_reading_info(
-
-) RETURNS integer AS $$
-DECLARE
-v_users record;
-BEGIN
-delete from forums_reading_info_user;
-for v_users in
-select user_id,(select forum_id from forums_messages where message_id = root_message_id) as forum_id, count(root_message_id) as threads_read from forums_reading_info group by forum_id,user_id
-loop
-insert into forums_reading_info_user (forum_id,user_id,threads_read)
-values
-(v_users.forum_id,v_users.user_id,v_users.threads_read);
-end loop;
-return 1;
-END;
-$$ LANGUAGE plpgsql;
Index: openacs-4/packages/forums/sql/postgresql/forums-reading-info-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-reading-info-drop.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/forums/sql/postgresql/forums-reading-info-drop.sql 4 Jun 2007 12:50:37 -0000 1.1
+++ openacs-4/packages/forums/sql/postgresql/forums-reading-info-drop.sql 10 Nov 2017 17:21:05 -0000 1.2
@@ -9,14 +9,9 @@
-- License version 2 or later. Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html
-drop function forums_message__repair_reading_info ();
-drop function forums_message__move_thread_update_reading_info (integer,integer,integer);
-drop function forums_message__move_thread_thread_update_reading_info (integer,integer, integer);
-drop function forums_message__move_update_reading_info (integer,integer,integer);
+drop function forums_reading_info__move_thread_update (integer,integer);
drop function forums_reading_info__user_add_msg (integer,integer);
drop function forums_reading_info__user_add_forum (integer,integer);
drop function forums_reading_info__remove_msg (integer);
-drop table forums_reading_info_user;
-drop index forums_reading_info_forum_message_index;
-drop index forums_reading_info_user_index ;
-drop table forums_reading_info;
\ No newline at end of file
+drop view forums_reading_info_user;
+drop table forums_reading_info;
Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.3.1d2-1.3.1d3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.3.1d2-1.3.1d3.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.3.1d2-1.3.1d3.sql 10 Nov 2017 17:21:05 -0000 1.1
@@ -0,0 +1,173 @@
+begin;
+
+-- As it comes out, forums has some embedded views counter
+-- feature. This is not used upstream, but it is in some local
+-- installations we know of. As on these table forums_reading_info can
+-- grow very large, there were reports of bad performances. This
+-- update has the goal to optimize and streamline current reading
+-- count implementation. During this, some inconsistency between
+-- oracle and postgres and duplication was found and addressed.
+
+-- data model
+
+drop table forums_reading_info_user;
+
+alter table forums_reading_info
+ add column forum_id integer
+ constraint forum_read_forum_id_fk
+ references forums_forums (forum_id)
+ on delete cascade
+ constraint forums_read_forum_id_nn
+ not null;
+
+create index forums_reading_info_forum_forum_index on forums_reading_info (forum_id);
+
+
+-- this was a sort of materialized view, but consistency checks made
+-- code complicated. Redefined as a view
+create or replace view forums_reading_info_user as
+ select forum_id,
+ user_id,
+ count(*) as threads_read
+ from forums_reading_info
+ group by forum_id, user_id;
+
+
+-- functions
+
+--
+-- procedure forums_reading_info__remove_msg/1
+--
+CREATE OR REPLACE FUNCTION forums_reading_info__remove_msg(
+ p_message_id integer
+) RETURNS integer AS $$
+DECLARE
+BEGIN
+ delete from forums_reading_info
+ where root_message_id = p_message_id;
+ return 0;
+END;
+$$ LANGUAGE plpgsql;
+
+
+--
+-- procedure forums_reading_info__user_add_forum/2
+--
+--
+-- procedure forums_reading_info__user_add_forum/2
+--
+CREATE OR REPLACE FUNCTION forums_reading_info__user_add_forum(
+ p_forum_id integer,
+ p_user_id integer
+) RETURNS integer AS $$
+DECLARE
+ v_message_id integer;
+BEGIN
+ for v_message_id in
+ select message_id
+ from forums_messages_approved m
+ where forum_id = p_forum_id
+ and parent_id is null
+ and not exists (select 1 from forums_reading_info
+ where user_id = p_user_id
+ and root_message_id = m.message_id) loop
+ insert into forums_reading_info (
+ root_message_id,
+ user_id,
+ forum_id
+ ) values (
+ v_message_id,
+ p_user_id,
+ p_forum_id
+ );
+ end loop;
+ return 0;
+END;
+$$ LANGUAGE plpgsql;
+
+
+--
+-- procedure forums_reading_info__user_add_msg/2
+--
+CREATE OR REPLACE FUNCTION forums_reading_info__user_add_msg(
+ p_root_message_id integer,
+ p_user_id integer
+) RETURNS integer AS $$
+DECLARE
+ v_forum_id integer;
+BEGIN
+ if NOT exists (select 1 from forums_reading_info
+ where user_id = p_user_id
+ and root_message_id = p_root_message_id) then
+
+ insert into forums_reading_info (
+ root_message_id,
+ user_id,
+ forum_id
+ ) values (
+ p_root_message_id,
+ p_user_id,
+ (select forum_id from forums_messages
+ where message_id = p_root_message_id)
+ );
+ end if;
+
+ return 0;
+END;
+$$ LANGUAGE plpgsql;
+
+
+-- These functions were defined with a name not conformant with
+-- package notation used to mimick oracle. They resulted also
+-- redundant once we eliminated the forums_reading_info_users table
+
+drop function forums_message__move_update_reading_info(integer, integer, integer);
+delete from acs_function_args
+ where function = upper('forums_message__move_update_reading_info');
+
+delete from acs_function_args
+ where function = upper('forums_message__move_thread_update_reading_info');
+drop function forums_message__move_thread_update_reading_info(integer, integer, integer);
+
+drop function forums_message__move_thread_thread_update_reading_info(integer, integer, integer);
+delete from acs_function_args
+ where function = upper('forums_message__move_thread_thread_update_reading_info');
+
+
+-- move thread to other thread
+
+-- added
+select define_function_args('forums_reading_info__move_thread_update','source_message_id,target_message_id');
+
+--
+-- procedure forums_reading_info__move_thread_update/2
+--
+CREATE OR REPLACE FUNCTION forums_reading_info__move_thread_update(
+ p_source_message_id integer,
+ p_target_message_id integer
+) RETURNS integer AS $$
+DECLARE
+BEGIN
+ -- for all users that have read target, but not the source, remove
+ -- target_info
+ delete from forums_reading_info i
+ where root_message_id = p_target_message_id
+ and not exists (select 1 from forums_reading_info
+ where root_message_id = p_source_message_id
+ and user_id = i.user_id);
+
+ -- for all users that have read source, remove reading info four
+ -- source message since it no longer is root_message_id
+ delete from forums_reading_info
+ where root_message_id = p_source_message_id;
+
+ return 1;
+END;
+$$ LANGUAGE plpgsql;
+
+
+drop function forums_message__repair_reading_info();
+delete from acs_function_args
+ where function = upper('forums_message__repair_reading_info');
+
+end;
Index: openacs-4/packages/forums/tcl/forums-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/tcl/forums-procs.tcl,v
diff -u -r1.28 -r1.29
--- openacs-4/packages/forums/tcl/forums-procs.tcl 8 Nov 2017 12:05:55 -0000 1.28
+++ openacs-4/packages/forums/tcl/forums-procs.tcl 10 Nov 2017 17:21:05 -0000 1.29
@@ -193,10 +193,7 @@
}
ad_proc -public forum::use_ReadingInfo_p {} {
- # this depends on site-wide package parameters implemented in OpenACS core
- # http://openacs.org/bugtracker/openacs/patch?patch%5fnumber=845
- # return [parameter::get_from_package_key -package_key forums -parameter UseReadingInfo]
- return 0
+ return [parameter::get_from_package_key -package_key forums -parameter UseReadingInfo]
}
ad_proc forum::valid_forum_id_p {