{"action":"opened","issue":{"url":"https://api.github.com/repos/t3chguy/Roundcube-SQL-Global-Address-Books/issues/4","labels_url":"https://api.github.com/repos/t3chguy/Roundcube-SQL-Global-Address-Books/issues/4/labels{/name}","comments_url":"https://api.github.com/repos/t3chguy/Roundcube-SQL-Global-Address-Books/issues/4/comments","events_url":"https://api.github.com/repos/t3chguy/Roundcube-SQL-Global-Address-Books/issues/4/events","html_url":"https://github.com/t3chguy/Roundcube-SQL-Global-Address-Books/issues/4","id":59333311,"number":4,"title":"SQL template: create VIEW in PostgreSQL database for iRedMail","user":{"login":"zhanghuangbin","id":51755,"avatar_url":"https://avatars.githubusercontent.com/u/51755?v=3","gravatar_id":"","url":"https://api.github.com/users/zhanghuangbin","html_url":"https://github.com/zhanghuangbin","followers_url":"https://api.github.com/users/zhanghuangbin/followers","following_url":"https://api.github.com/users/zhanghuangbin/following{/other_user}","gists_url":"https://api.github.com/users/zhanghuangbin/gists{/gist_id}","starred_url":"https://api.github.com/users/zhanghuangbin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zhanghuangbin/subscriptions","organizations_url":"https://api.github.com/users/zhanghuangbin/orgs","repos_url":"https://api.github.com/users/zhanghuangbin/repos","events_url":"https://api.github.com/users/zhanghuangbin/events{/privacy}","received_events_url":"https://api.github.com/users/zhanghuangbin/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-02-28T08:50:50Z","updated_at":"2015-02-28T08:50:50Z","closed_at":null,"body":"Here's SQL template used to create VIEW in PostgreSQL for iRedMail:\r\n\r\n```sql\r\n-- RHEL/CentOS 6.\r\n-- CREATE LANGUAGE plpgsql;                                                           \r\n-- \\i /usr/share/pgsql/contrib/dblink.sql;\r\n\r\n-- RHEL/CentOS 7\r\n-- CREATE EXTENSION dblink;\r\n\r\nCREATE VIEW global_addressbook AS\r\n    SELECT * FROM dblink('host=127.0.0.1 port=5432 user=xxx password=xxxx dbname=xxx', 'SELECT extract(epoch FROM created), name, username, domain FROM mailbox WHERE active=1')\r\n    AS global_addressbook (\"ID\" BIGINT, name VARCHAR(255), email VARCHAR(255), domain VARCHAR(255));\r\n\r\nALTER TABLE global_addressbook OWNER TO roundcube;\r\n```\r\n\r\nNotes:\r\n\r\n* if you're running RHEL/CentOS 6 or 7, don't forget to uncomment the first lines to create dblink function.\r\n* of course you should replace `xxx` in above sample SQL command by the real SQL username/password/database name.\r\n* on iRedMail server, it's better to use SQL user `vmail` which has read-only permission to `vmail` database. Don't use `vmailadmin` or `root` user."}}