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.1.2.1 -r1.1.2.2
--- openacs-4/packages/forums/sql/oracle/forums-reading-info-package-create.sql	23 Apr 2008 16:04:33 -0000	1.1.2.1
+++ openacs-4/packages/forums/sql/oracle/forums-reading-info-package-create.sql	24 May 2008 10:10:28 -0000	1.1.2.2
@@ -50,7 +50,7 @@
 	);
 
 -- recount reading_info_user from reading_info		
---	procedure repair_reading_info ();
+	procedure repair_reading_info;
 
 end forum_reading_info;
 /
@@ -65,78 +65,103 @@
 	        p_message_id in forums_messages.message_id%TYPE
 	) 
 	is
-		v_forum_id	integer;
+		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
-	    select forum_id into v_forum_id from forums_messages where message_id = p_message_id;
+
+        --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;
 	          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;
+
+
 	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 forums_reading_info%ROWTYPE;
-	begin
-	    for v_message in (select message_id
+
+        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
-		begin
-			select * into v_read_p from forums_reading_info where user_id = p_user_id and root_message_id  = v_message.message_id;
-		exception
-		when others then
-                 if sql%notfound then  -- check for 'no data found'
-	            insert into forums_reading_info 
-        	    (root_message_id,user_id) 
-	            values 
-        	    (v_message.message_id,p_user_id);
+            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)
+                    values
+                    (v_message.message_id,p_user_id);
                  end if;
-		end;
-	    end loop;
-	end user_add_forum; 
+            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;
 
+
 -- 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 varchar(1);
-		v_exists varchar(1);
-	begin
-		select forum_id into v_forum_id from forums_messages where message_id = p_root_message_id;
-		begin
-			select 1 into v_read_p from forums_reading_info where user_id = p_user_id and root_message_id = p_root_message_id;
-			insert into forums_reading_info (root_message_id,user_id) values (p_root_message_id,p_user_id);
-		exception
-		when others then
-                 if sql%notfound then  -- check for 'no data found'
-			insert into forums_reading_info (root_message_id,user_id) values (p_root_message_id,p_user_id);
-		 	begin
-	  		    	INSERT INTO forums_reading_info_user(forum_id,user_id,threads_read) VALUES (v_forum_id,p_user_id,1);			 
-		 		SELECT 1 into v_exists FROM forums_reading_info_user WHERE forum_id=v_forum_id AND user_id=p_user_id;
-			exception
-			when others then
-                	 if sql%notfound then  -- check for 'no data found'
-	  			UPDATE forums_reading_info_user SET threads_read=threads_read+1 WHERE forum_id=v_forum_id AND user_id=p_user_id;
-			 end if;
-			end;
-		 end if;
-  		end;
-	end user_add_msg;
+        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;
+                v_exists 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) 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;
+
+                         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,
@@ -146,18 +171,25 @@
 	    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
+
+  	      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;
@@ -172,20 +204,36 @@
 		v_target_forum_id 		forums_forums.forum_id%TYPE;
 		v_users             		forums_reading_info%ROWTYPE;
 	begin
-		select forum_id into v_target_forum_id from forums_messages where message_id = p_target_message_id;
+            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
@@ -197,17 +245,52 @@
 		v_target_forum_id 			forums_forums.forum_id%TYPE;
 		v_users             			forums_reading_info%ROWTYPE;
 	begin
-		select forum_id into v_target_forum_id from forums_messages where message_id = p_target_message_id;
+            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
+
 			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
