PSCS Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Group a user's folders into letter groups======
This is useful in VPOP3 Enterprise if you have a large folder with many subfolders and want to group the subfolders into other subfolders based on their first letter

For example, if you have

  * Customers
    * Abbot
    * ACME
    * Boston
    * Bradford
    * Calvin
    * Clark

and want to change it to
  * Customers
    * A
      * Abbot
      * ACME
    * B
      * Boston
      * Bradford
    * C
      * Calvin
      * Clark

At a command prompt in the VPOP3 directory, run 'psql.cmd'

Then copy/paste this into the PSQL prompt:
<code postgresql [enable_line_numbers="true"]>
CREATE OR REPLACE FUNCTION pg_temp.remapfolders(v_username VARCHAR, v_prefix VARCHAR) RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE 
  v_userid INTEGER;
  v_prefixlength INTEGER;
  f RECORD;
  v_letter CHAR;
  v_count INTEGER;
  v_newfolder VARCHAR;
BEGIN
  SELECT usernumber INTO v_userid FROM users.users WHERE username ilike v_username;
  IF v_userid IS NULL THEN
    RAISE EXCEPTION 'No such User';
  END IF;
  IF RIGHT(v_prefix, 1) <> '/' THEN
    v_prefix := v_prefix || '/';
  END IF;
  v_prefixlength := LENGTH(v_prefix);
  v_count := 0;
  FOR f IN 
	SELECT folder,folderid FROM messages.folders WHERE LEFT(folder, v_prefixlength) = v_prefix
  LOOP
  	v_letter := SUBSTRING(f.folder FROM v_prefixlength + 1 FOR 1);
	IF v_letter ~ '[A-Za-z]' THEN
	  v_newfolder := v_prefix || v_letter || '/' || SUBSTRING(f.folder FROM v_prefixlength + 1);
	  RAISE NOTICE 'folder % -> %', f.folder, v_newfolder;
	
	  INSERT INTO messages.folders (userid, folder) VALUES(v_userid, v_prefix || v_letter) ON CONFLICT DO NOTHING;
	  INSERT INTO messages.foldersubscriptions(userid, folder) VALUES(v_userid, v_prefix || v_letter) ON CONFLICT DO NOTHING;
	
	  UPDATE messages.folders SET folder=v_newfolder WHERE folderid=f.folderid;
	  UPDATE messages.foldersubscriptions SET folder=v_newfolder WHERE folder=f.folder AND userid=v_userid;
	  v_count := v_count + 1;
	END IF;
	
  END LOOP;
RETURN v_count;
END;
$$;
</code>

Then, you can run something like:

SELECT pg_temp.remapfolders('sales', 'Customers');

where the first parameter is the username where the folders are, and the second parameter is the prefix of the folders you want to regroup. 

This script only groups folders where the first letter is alphabetic. That could be changed by altering the ''IF v_letter ~ '[A-Za-z]' THEN'' line. For example, if you change it to ''IF v_letter ~ '[A-Za-z0-9]' THEN'', then it will also group folders beginning with numeric digits.
PSCS Wiki

User Tools

Site Tools

Page Tools
