-- upgrade an installation from 3.2.2 to 3.2.4
-- Ben Adida (ben@mit.edu)
--

-- You should make sure you have PL/TCL enabled
-- before you run this SQL script. If you enable OpenACS Search
-- in your parameter file, you will then be able to do full-text search
-- in the bboards

-- \i rank-for-search.sql

--
-- bboard.sql
--

drop function bboard_contains (varchar, varchar, varchar, varchar, varchar);
create function bboard_contains (varchar, varchar, varchar, varchar, varchar)
returns integer as '
declare
  email alias for $1;
  user_name alias for $2;
  one_line alias for $3;
  message alias for $4;
  space_sep_list_untrimmed alias for $5;
  space_sep_list        varchar(32000);
  upper_indexed_stuff   varchar(32000);
  -- if you call this var START you get hosed royally
  first_space           integer;
  score                 integer;
BEGIN 
  space_sep_list := upper(ltrim(rtrim(space_sep_list_untrimmed)));
  upper_indexed_stuff := upper(email || user_name || one_line || substr(message,30000));
  score := 0;
  IF space_sep_list is null or upper_indexed_stuff is null THEN
    RETURN score;  
  END IF;
  LOOP
   first_space := position(space_sep_list in '' '');
   IF first_space = 0 THEN
     -- one token or maybe end of list
     IF position(upper_indexed_stuff in space_sep_list) <> 0 THEN
        RETURN score+10;
     END IF;
     RETURN score;
   ELSE
   -- first_space <> 0
     IF position(upper_indexed_stuff in substr(space_sep_list,1,first_space-1)) <> 0 THEN
        score := score + 10;
     END IF;
   END IF;
    space_sep_list := substr(space_sep_list,first_space+1);
  END LOOP;  
END;
' language 'plpgsql';

-- Part of a series of security fixes
-- (BMA, spec'ed by aD)
create function bboard_user_can_view_topic_p (integer,integer)	
returns char AS '
DECLARE
	v_user_id alias for $1;
	v_topic_id alias for $2;
	v_read_access varchar(16);	     
	v_group_id    integer;
	v_count       integer;
BEGIN
	select read_access, group_id into v_read_access, v_group_id
	from bboard_topics
	where topic_id = v_topic_id;

	IF v_read_access = ''any'' or v_read_access = ''public'' THEN
	RETURN ''t'';
	END IF;

	select count(*) into v_count
	from user_group_map
	where user_id = v_user_id
	and group_id = v_group_id;      

	IF v_count > 0 THEN
	RETURN ''t'';
	END IF;

	RETURN ''f'';
END;
' language 'plpgsql';


--
-- New-ticket
--

-- The custom fields table
alter table ticket_projects_fields add view_in_list char(1) check (view_in_list in ('t','f'));
alter table ticket_projects_fields add field_vals varchar(4000);

-- a unique index 
create unique index ticket_project_field_name_indx on ticket_projects_fields(project_id, field_name);

-- a proc to pull out a custom field
-- for custom field fetching
create function ticket_fetch_custom_field (integer, integer, varchar)
returns char
as '
DECLARE
	v_msg_id alias for $1;
	v_project_id alias for $2;
	v_field alias for $3;
	v_field_id integer;
BEGIN
	select field_id into v_field_id from ticket_projects_fields where project_id= v_project_id and field_name= v_field;

	if v_field_id is null then return null; end if;

	return field_val from ticket_projects_field_vals where field_id = v_field_id and project_id=v_project_id and issue_id= v_msg_id;
END;
' language 'plpgsql';


-- who can close an issue
create function ticket_user_can_close_issue_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_msg_id alias for $2;
	msg_closeable record;
BEGIN
	if ad_admin_group_member_p(''bits'','''', v_user_id) = ''t''
	then return ''t'';
	end if;

	select into msg_closeable
               count(ti.msg_id) as chk,
               max(ti.project_id) as pid 
          from ticket_issues ti
         where ti.user_id = v_user_id 
           and ti.msg_id  = v_msg_id
           and 0 < (select count(*) 
                      from user_group_map 
                     where user_id= v_user_id 
                       and (role=''internal'' or role=''administrator'') 
                       and group_id = (select tm.team_id 
                                from ticket_project_teams tm
                               where parent_project_p(tm.project_id,ti.project_id)=''t''));

	IF msg_closeable.chk > 0
	THEN return(''t'');
	ELSE return(''f'');
	END IF;
END;
' language 'plpgsql';	


--
-- Postgres.sql
-- 

-- Mimic Oracle's user_tab_columns table (thanks Ken Mayer!)

CREATE VIEW user_tab_columns AS
SELECT upper(c.relname) AS table_name, 
       upper(a.attname) AS column_name,
       CASE WHEN (t.typprtlen > 0)
       THEN t.typprtlen
       ELSE (a.atttypmod - 4)
       END AS data_length
FROM pg_class c, pg_attribute a, pg_type t
WHERE (a.attrelid = c.oid) AND (a.atttypid = t.oid) AND (a.attnum > 0);


--
-- Pull Down Menus
--

create function pdm_count_items_like(char)
returns integer
as '
DECLARE
	v_sortkey alias for $1;
BEGIN
	return count(*) from pdm_menu_items where sort_key like v_sortkey||''__'';
END;
' language 'plpgsql';

create function pdm_parent_label(integer, char)
returns char
as '
DECLARE
	v_menu_id alias for $1;
	v_sortkey alias for $2;
BEGIN
	return label from pdm_menu_items where menu_id= v_menu_id and sort_key= substr(v_sortkey,1, length(v_sortkey)-2);
END;
' language 'plpgsql';


--
-- file storage
--

-- This function now returns text instead of varchar.
drop function fs_connect_by(integer);
create function fs_connect_by(integer) returns text as '
declare
	id              alias for $1;
        pid             integer;
BEGIN
        select into pid parent_id from fs_files where id = file_id;
	IF pid is null
	THEN 
		return fs_gen_key(id);
	ELSE
		return fs_connect_by(pid) || ''/'' || fs_gen_key(id);
	END IF;	
END;
' language 'plpgsql';