Centarra/panel2 — pull request #32 by nasonfish (2014-05-26 02:55:32+00:00)
Title: permissions: fix database schema issues, add permissions to DNS zones

Looks like I mixed up the schema a bit, since a child's columns were being thrown in right with the parent's.

To fix:
```sql
CREATE TABLE permission_base_temp (
id INTEGER NOT NULL,
user_id INTEGER,
type VARCHAR(255),
granter_id INTEGER,
modify BOOLEAN,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES user (id),
FOREIGN KEY(granter_id) REFERENCES user (id),
CHECK (modify IN (0, 1))
);
CREATE TABLE service_permission (
perm_id INTEGER NOT NULL,
base_id INTEGER,
service_id INTEGER,
PRIMARY KEY (perm_id),
FOREIGN KEY(base_id) REFERENCES permission_base (id),
FOREIGN KEY(service_id) REFERENCES services (id)
);
INSERT INTO service_permission (base_id, service_id) SELECT id, service_id FROM permission_base;
INSERT INTO permission_base_temp (id, user_id, type, granter_id, modify) SELECT id, user_id, type, granter_id, modify FROM permission_base;
--- quickly check if we've gotten everything right by comparing:
SELECT * from permission_base; SELECT * FROM service_permission; SELECT * FROM service_permission_temp;
-- we check if the things in them match the original - it's worked for me and I assume you have backups, however.
DROP TABLE permission_base;
ALTER TABLE permission_base_temp RENAME TO permission_base;
-- now run python create-all.py to do the rest.
```

There's also a concurrent PR for the vps module too, I'll just push it when this is accepted.