# HG changeset patch
# User Pascal Volk <neverseen@users.sourceforge.net>
# Date 1250839800 0
# Node ID 626c008a4a04b393ff6bd5fffb65a1bb8cd833a4
# Parent  30abf0abf8f82d1b8389cca9d605ea6b399c50cb
Added update SQL scripts for Dovecot v1.2.x

diff -r 30abf0abf8f8 -r 626c008a4a04 update_tables_0.5.x_for_dovecot-1.2.x.pgsql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/update_tables_0.5.x_for_dovecot-1.2.x.pgsql	Fri Aug 21 07:30:00 2009 +0000
@@ -0,0 +1,19 @@
+-- ---
+-- with Dovecot v1.2.x the service managesieve was renamed to sieve
+-- ---
+ALTER TABLE users RENAME managesieve TO sieve;
+
+DROP VIEW dovecot_password;
+CREATE OR REPLACE VIEW dovecot_password AS
+    SELECT local_part || '@' || domain_name.domainname AS "user",
+           passwd AS "password", smtp, pop3, imap, sieve
+      FROM users
+           LEFT JOIN domain_name USING (gid)
+     WHERE domain_name.is_primary;
+
+-- ---
+-- Change the user name to the name of your dovecot database user.
+-- ---
+GRANT SELECT ON dovecot_password TO dovecot;
+--                               ---^
+
diff -r 30abf0abf8f8 -r 626c008a4a04 update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql	Fri Aug 21 07:30:00 2009 +0000
@@ -0,0 +1,51 @@
+-- ---
+-- Clean out the old stuff
+-- ---
+DROP TYPE dovecotpassword CASCADE;
+
+-- ---
+-- Data type for function dovecotpassword(varchar, varchar)
+-- ---
+CREATE TYPE dovecotpassword AS (
+    userid    varchar(320),
+    password  varchar(74),
+    smtp      boolean,
+    pop3      boolean,
+    imap      boolean,
+    sieve     boolean
+);
+
+-- ---
+-- Parameters (from login name [localpart@the_domain]):
+--      varchar localpart
+--      varchar the_domain
+-- Returns: dovecotpassword records
+--
+-- Required access privileges for your dovecot database user:
+--      GRANT SELECT ON users, domain_name TO dovecot;
+--
+-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
+-- ---
+CREATE OR REPLACE FUNCTION dovecotpassword(
+    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
+AS $$
+    DECLARE
+        record dovecotpassword;
+        userid varchar(320) := localpart || '@' || the_domain;
+    BEGIN
+        FOR record IN
+            SELECT userid, passwd, smtp, pop3, imap, sieve
+              FROM users
+             WHERE gid = (SELECT gid
+                            FROM domain_name
+                           WHERE domainname = the_domain)
+               AND local_part = localpart
+            LOOP
+                RETURN NEXT record;
+            END LOOP;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+