mirror of
https://github.com/opensolutions/ViMbAdmin.git
synced 2026-04-26 16:55:59 +03:00
[GH-ISSUE #211] VIMbAdmin PotgreSQL database #166
Labels
No labels
bug
feature
feature
improvement
improvement
pull-request
No milestone
No project
No assignees
1 participant
Notifications
Due date
No due date set.
Dependencies
No dependencies set.
Reference
starred/ViMbAdmin-opensolutions#166
Loading…
Add table
Add a link
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @ghost on GitHub (Oct 26, 2016).
Original GitHub issue: https://github.com/opensolutions/ViMbAdmin/issues/211
Hello,
I am very interested in VIMbAdmin to ease management of Postfix.For all projects, I always use PostgreSQL. Following instructions, the following error is returned:
File
/srv/vimbadmin/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php
Line
75
Message
An exception occurred while executing 'SELECT COUNT(a0_.id) AS sclr0 FROM admin a0_': SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "admin" does not exist LINE 1: SELECT COUNT(a0_.id) AS sclr0 FROM admin a0_ ^
Code
0
Am I supposed to create the SQL database myself? Using:
https://github.com/opensolutions/ViMbAdmin/blob/master/data/vagrant-base.sql
and migrate to PostgreSQL?
Kind regards,
Kellogs
@ghost commented on GitHub (Oct 26, 2016):
I could create the database by hand running the following code:
./bin/doctrine2-cli.php orm:schema-tool:create --dump-sql
CREATE TABLE alias_pref (id BIGINT NOT NULL, attribute VARCHAR(255) NOT NULL, ix INT DEFAULT 0 NOT NULL, op VARCHAR(2) DEFAULT ':=' NOT NULL, value TEXT NOT NULL, expire BIGINT DEFAULT 0 NOT NULL, Alias_id BIGINT DEFAULT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_3279E911A72028B4 ON alias_pref (Alias_id);
CREATE INDEX IX_AliasPreference_1 ON alias_pref (Alias_id, attribute, ix);
CREATE TABLE domain (id BIGINT NOT NULL, domain VARCHAR(255) NOT NULL, description VARCHAR(255) DEFAULT NULL, max_aliases INT DEFAULT 0 NOT NULL, alias_count BIGINT DEFAULT 0 NOT NULL, max_mailboxes INT DEFAULT 0 NOT NULL, mailbox_count BIGINT DEFAULT 0 NOT NULL, max_quota BIGINT DEFAULT 0 NOT NULL, quota BIGINT DEFAULT 0 NOT NULL, transport VARCHAR(255) DEFAULT 'virtual' NOT NULL, backupmx BOOLEAN DEFAULT 'false' NOT NULL, active BOOLEAN DEFAULT 'true' NOT NULL, homedir VARCHAR(255) DEFAULT NULL, maildir VARCHAR(255) DEFAULT NULL, uid INT DEFAULT NULL, gid INT DEFAULT NULL, created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, modified TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX IX_Domain_1 ON domain (domain);
CREATE TABLE log (id BIGINT NOT NULL, action VARCHAR(100) NOT NULL, data TEXT DEFAULT NULL, timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, Admin_id BIGINT DEFAULT NULL, Domain_id BIGINT DEFAULT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_8F3F68C59D5DE046 ON log (Admin_id);
CREATE INDEX IDX_8F3F68C593AE8C46 ON log (Domain_id);
CREATE TABLE admin_pref (id BIGINT NOT NULL, attribute VARCHAR(255) NOT NULL, ix INT DEFAULT 0 NOT NULL, op VARCHAR(2) DEFAULT ':=' NOT NULL, value TEXT NOT NULL, expire BIGINT DEFAULT 0 NOT NULL, Admin_id BIGINT DEFAULT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_814C1AD19D5DE046 ON admin_pref (Admin_id);
CREATE UNIQUE INDEX IX_AdminPreference_1 ON admin_pref (Admin_id, attribute, ix);
CREATE TABLE mailbox (id BIGINT NOT NULL, username VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, name VARCHAR(255) DEFAULT NULL, alt_email VARCHAR(255) DEFAULT NULL, quota BIGINT DEFAULT 0 NOT NULL, local_part VARCHAR(255) NOT NULL, active BOOLEAN DEFAULT 'false' NOT NULL, access_restriction VARCHAR(100) DEFAULT 'ALL' NOT NULL, homedir VARCHAR(255) DEFAULT NULL, maildir VARCHAR(255) DEFAULT NULL, uid BIGINT DEFAULT NULL, gid BIGINT DEFAULT NULL, homedir_size BIGINT DEFAULT NULL, maildir_size BIGINT DEFAULT NULL, size_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, delete_pending BOOLEAN DEFAULT 'false', created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, modified TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, Domain_id BIGINT DEFAULT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_A69FE20B93AE8C46 ON mailbox (Domain_id);
CREATE TABLE archive (id BIGINT NOT NULL, username VARCHAR(255) NOT NULL, status VARCHAR(255) NOT NULL, archived_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, status_changed_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, homedir_server VARCHAR(255) DEFAULT NULL, homedir_file VARCHAR(255) DEFAULT NULL, homedir_orig_size BIGINT DEFAULT NULL, homedir_size BIGINT DEFAULT NULL, maildir_server VARCHAR(255) DEFAULT NULL, maildir_file VARCHAR(255) DEFAULT NULL, maildir_orig_size BIGINT DEFAULT NULL, maildir_size BIGINT DEFAULT NULL, data TEXT NOT NULL, Domain_id BIGINT DEFAULT NULL, Admin_id BIGINT DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_D5FC5D9CF85E0677 ON archive (username);
CREATE INDEX IDX_D5FC5D9C93AE8C46 ON archive (Domain_id);
CREATE INDEX IDX_D5FC5D9C9D5DE046 ON archive (Admin_id);
CREATE TABLE mailbox_pref (id BIGINT NOT NULL, attribute VARCHAR(255) NOT NULL, ix INT DEFAULT 0 NOT NULL, op VARCHAR(2) DEFAULT ':=' NOT NULL, value TEXT NOT NULL, expire BIGINT DEFAULT 0 NOT NULL, Mailbox_id BIGINT DEFAULT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_F9C4B42A29B1361C ON mailbox_pref (Mailbox_id);
CREATE INDEX IX_MailboxPreference_1 ON mailbox_pref (Mailbox_id, attribute, ix);
CREATE TABLE admin (id BIGINT NOT NULL, username VARCHAR(255) DEFAULT NULL, password VARCHAR(255) NOT NULL, super BOOLEAN DEFAULT 'false' NOT NULL, active BOOLEAN DEFAULT 'true' NOT NULL, created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, modified TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX IX_Username_1 ON admin (username);
CREATE TABLE domain_admins (Admin_id BIGINT NOT NULL, Domain_id BIGINT NOT NULL, PRIMARY KEY(Admin_id, Domain_id));
CREATE INDEX IDX_CD8319C69D5DE046 ON domain_admins (Admin_id);
CREATE INDEX IDX_CD8319C693AE8C46 ON domain_admins (Domain_id);
CREATE TABLE directory_entry (id BIGINT NOT NULL, mailbox_id BIGINT NOT NULL, businessCategory VARCHAR(255) DEFAULT NULL, carLicense VARCHAR(255) DEFAULT NULL, departmentNumber VARCHAR(255) DEFAULT NULL, displayName VARCHAR(255) DEFAULT NULL, employeeNumber VARCHAR(255) DEFAULT NULL, employeeType VARCHAR(255) DEFAULT NULL, homePhone VARCHAR(255) DEFAULT NULL, homePostalAddress TEXT DEFAULT NULL, initials VARCHAR(10) DEFAULT NULL, jpegPhoto TEXT DEFAULT NULL, labeledURI TEXT DEFAULT NULL, mail VARCHAR(255) DEFAULT NULL, manager VARCHAR(255) DEFAULT NULL, mobile VARCHAR(255) DEFAULT NULL, o VARCHAR(255) DEFAULT NULL, pager VARCHAR(255) DEFAULT NULL, preferredLanguage VARCHAR(255) DEFAULT NULL, roomNumber VARCHAR(255) DEFAULT NULL, secretary VARCHAR(255) DEFAULT NULL, personalTitle VARCHAR(255) DEFAULT NULL, sn VARCHAR(255) DEFAULT NULL, ou VARCHAR(255) DEFAULT NULL, title VARCHAR(255) DEFAULT NULL, facsimileTelephoneNumber VARCHAR(255) DEFAULT NULL, givenName VARCHAR(255) DEFAULT NULL, telephoneNumber VARCHAR(255) DEFAULT NULL, vimb_created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, vimb_update TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_6553C92D66EC35CC ON directory_entry (mailbox_id);
COMMENT ON COLUMN directory_entry.jpegPhoto IS '(DC2Type:object)';
CREATE TABLE domain_pref (id BIGINT NOT NULL, attribute VARCHAR(255) NOT NULL, ix INT DEFAULT 0 NOT NULL, op VARCHAR(2) DEFAULT ':=' NOT NULL, value TEXT NOT NULL, expire BIGINT DEFAULT 0 NOT NULL, Domain_id BIGINT DEFAULT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_C89B55693AE8C46 ON domain_pref (Domain_id);
CREATE INDEX IX_DomainPreference_1 ON domain_pref (Domain_id, attribute, ix);
CREATE TABLE alias (id BIGINT NOT NULL, address VARCHAR(255) NOT NULL, goto TEXT NOT NULL, active BOOLEAN DEFAULT 'true' NOT NULL, created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, modified TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, Domain_id BIGINT DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_E16C6B94D4E6F81 ON alias (address);
CREATE INDEX IDX_E16C6B9493AE8C46 ON alias (Domain_id);
CREATE TABLE remember_me (id BIGINT NOT NULL, userhash VARCHAR(40) NOT NULL, ckey VARCHAR(40) NOT NULL, original_ip VARCHAR(40) NOT NULL, expires TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, last_used TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, Admin_id BIGINT DEFAULT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_7AF2A7289D5DE046 ON remember_me (Admin_id);
CREATE TABLE dbversion (id INT NOT NULL, version INT NOT NULL, name VARCHAR(255) NOT NULL, applied_on TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, PRIMARY KEY(id));
CREATE SEQUENCE alias_pref_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE domain_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE log_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE admin_pref_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE mailbox_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE archive_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE mailbox_pref_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE admin_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE directory_entry_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE domain_pref_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE alias_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE remember_me_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE dbversion_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
ALTER TABLE alias_pref ADD CONSTRAINT FK_3279E911A72028B4 FOREIGN KEY (Alias_id) REFERENCES alias (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE log ADD CONSTRAINT FK_8F3F68C59D5DE046 FOREIGN KEY (Admin_id) REFERENCES admin (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE log ADD CONSTRAINT FK_8F3F68C593AE8C46 FOREIGN KEY (Domain_id) REFERENCES domain (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE admin_pref ADD CONSTRAINT FK_814C1AD19D5DE046 FOREIGN KEY (Admin_id) REFERENCES admin (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE mailbox ADD CONSTRAINT FK_A69FE20B93AE8C46 FOREIGN KEY (Domain_id) REFERENCES domain (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE archive ADD CONSTRAINT FK_D5FC5D9C93AE8C46 FOREIGN KEY (Domain_id) REFERENCES domain (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE archive ADD CONSTRAINT FK_D5FC5D9C9D5DE046 FOREIGN KEY (Admin_id) REFERENCES admin (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE mailbox_pref ADD CONSTRAINT FK_F9C4B42A29B1361C FOREIGN KEY (Mailbox_id) REFERENCES mailbox (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE domain_admins ADD CONSTRAINT FK_CD8319C69D5DE046 FOREIGN KEY (Admin_id) REFERENCES admin (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE domain_admins ADD CONSTRAINT FK_CD8319C693AE8C46 FOREIGN KEY (Domain_id) REFERENCES domain (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE directory_entry ADD CONSTRAINT FK_6553C92D66EC35CC FOREIGN KEY (mailbox_id) REFERENCES mailbox (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE domain_pref ADD CONSTRAINT FK_C89B55693AE8C46 FOREIGN KEY (Domain_id) REFERENCES domain (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE alias ADD CONSTRAINT FK_E16C6B9493AE8C46 FOREIGN KEY (Domain_id) REFERENCES domain (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE remember_me ADD CONSTRAINT FK_7AF2A7289D5DE046 FOREIGN KEY (Admin_id) REFERENCES admin (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
@ghost commented on GitHub (Oct 26, 2016):
Works as expected. Great! But why is PostgreSQL database not populated automatically?
@barryo commented on GitHub (Oct 26, 2016):
https://github.com/opensolutions/ViMbAdmin/wiki/Install-Database-PostgreSQL
Additions to this documentation are welcome.
@PhrozenByte commented on GitHub (Oct 26, 2016):
PostgreSQL is already mentioned in the installation instructions: https://github.com/opensolutions/ViMbAdmin/wiki/Installation Everything else, including executing
orm:schema-tool:create, is part of the normal installation process.