I have 8 tables and this query:
select u.users_id, m.name as mandant_name, u.login_name, u.password, u.first_name, u.last_name, u.creation_date, g.name as groups_name, ae.acl_entry_id, a.name as acl_name, p.name as permission_name
from mandant m, users_2_groups u2g, groups g, users u, permission p, acl a, acl_entry ae, groups_2_acl_entry g2ae
where m.mandant_id = u.mandant_id and
u2g.groups_id = g.groups_id and
u2g.users_id = u.users_id and
g2ae.groups_id = g.groups_id and
g2ae.acl_entry_id = ae.acl_entry_id and
ae.acl_id = a.acl_id and
ae.permission_id = p.permission_id
I'm not using JOIN for get this information. would be JOIN a better sql programming style? faster?
Best Regards, Rafal
sql script:
DROP TABLE groups_2_acl_entry; DROP TABLE users_2_groups; DROP TABLE groups; DROP TABLE users; DROP TABLE acl_entry; DROP TABLE permission; DROP TABLE acl; DROP TABLE language; DROP TABLE mandant; DROP TABLE license; DROP TABLE firm;
CREATE TABLE firm ( firm_id numeric(20, 0) NOT NULL, name varchar(40) NOT NULL, CONSTRAINT firm_pkey PRIMARY KEY (firm_id) ) WITH OIDS;
INSERT INTO firm VALUES (1, 'polonium');
CREATE TABLE license ( license_id numeric(20, 0) NOT NULL, key varchar(100) NOT NULL, creation_date timestamp NOT NULL, valid_from timestamp NOT NULL, expired timestamp, CONSTRAINT license_pkey PRIMARY KEY (license_id) ) WITH OIDS;
INSERT INTO license VALUES (1, 'NOT AT THIS TIME - SHOULD BE GENERATED', now(), now(), NULL);
CREATE TABLE mandant (
mandant_id numeric(20, 0) NOT NULL,
firm_id numeric(20, 0) NOT NULL,
license_id numeric(20, 0) NOT NULL,
parent_id numeric(20, 0),
name varchar(20) NOT NULL,
creation_date timestamp NOT NULL,
CONSTRAINT mandant_pkey PRIMARY KEY (mandant_id),
CONSTRAINT fk_firm FOREIGN KEY (firm_id) REFERENCES firm (firm_id),
CONSTRAINT fk_license FOREIGN KEY (license_id) REFERENCES license (license_id),
CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES mandant (mandant_id)
) WITH OIDS;
INSERT INTO mandant VALUES (1, 1, 1, NULL, 'polonium', now());
CREATE TABLE language ( language_id int2 NOT NULL, lang_short char(2) NOT NULL, lang_long varchar(20) NOT NULL, CONSTRAINT language_pkey PRIMARY KEY (language_id) ) WITH OIDS;
CREATE UNIQUE INDEX language_lang_short_idx ON language (lang_short); CREATE UNIQUE INDEX language_lang_idx ON language (lang_short, lang_long);
INSERT INTO language VALUES (1, 'de', 'deutsch'); INSERT INTO language VALUES (2, 'en', 'english');
CREATE TABLE acl ( acl_id int2 NOT NULL, name varchar(20) NOT NULL, description varchar(200), CONSTRAINT acl_pkey PRIMARY KEY (acl_id) ) WITH OIDS;
CREATE UNIQUE INDEX acl_name_idx ON acl (name);
INSERT INTO acl VALUES (1, 'mmcms.access', 'acl for login module'); INSERT INTO acl VALUES (2, 'mmcms.system', 'acl for system module'); INSERT INTO acl VALUES (3, 'mmcms.admin', 'acl for admin module'); INSERT INTO acl VALUES (4, 'mmcms.category', 'acl for category module'); INSERT INTO acl VALUES (5, 'mmcms.context', 'acl for context module');
CREATE TABLE permission ( permission_id int2 NOT NULL, name varchar(20) NOT NULL, description varchar(200), CONSTRAINT permission_pkey PRIMARY KEY (permission_id) ) WITH OIDS;
CREATE UNIQUE INDEX permission_name_idx ON permission (name);
INSERT INTO permission VALUES (1, 'access', 'access permission'); INSERT INTO permission VALUES (2, 'read', 'read permission'); INSERT INTO permission VALUES (3, 'write', 'write permission'); INSERT INTO permission VALUES (4, 'execute', 'execute permission'); INSERT INTO permission VALUES (5, 'modify', 'modify permission'); INSERT INTO permission VALUES (6, 'list', 'list permission');
CREATE TABLE acl_entry (
acl_entry_id int2 NOT NULL,
acl_id int2 NOT NULL,
permission_id int2 NOT NULL,
CONSTRAINT acl_entry_pkey PRIMARY KEY (acl_entry_id),
CONSTRAINT fk_acl FOREIGN KEY (acl_id) REFERENCES acl (acl_id),
CONSTRAINT fk_permission FOREIGN KEY (permission_id) REFERENCES permission (permission_id)
) WITH OIDS;
-- acl_entry for mmcms.access acl with access permission
-- acl 'mmcms.access' has 'access' permission INSERT INTO acl_entry VALUES (1, 1, 1); -- acl 'mmcms.system' has 'read' permission INSERT INTO acl_entry VALUES (2, 2, 2); -- acl 'mmcms.system' has 'write' permission INSERT INTO acl_entry VALUES (3, 2, 3); -- acl 'mmcms.admin' has 'read' permission INSERT INTO acl_entry VALUES (4, 3, 2); -- acl 'mmcms.admin' has 'write' permission INSERT INTO acl_entry VALUES (5, 3, 3);
CREATE TABLE users (
users_id numeric(20, 0) NOT NULL,
mandant_id numeric(20, 0) NOT NULL,
language_id int2 NOT NULL,
login_name varchar(50) NOT NULL,
password varchar(15) NOT NULL,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL,
creation_date timestamp NOT NULL,
last_login_date timestamp,
status int2 NOT NULL,
CONSTRAINT users_pkey PRIMARY KEY (users_id),
CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant (mandant_id),
CONSTRAINT fk_language FOREIGN KEY (language_id) REFERENCES language (language_id)
) WITH OIDS;
CREATE UNIQUE INDEX users_login_name_idx ON users (login_name);
INSERT INTO users VALUES (1, 1, 1, '[EMAIL PROTECTED]', 'test', 'Rafal', 'Kedziorski', now(), NULL, 0);
CREATE TABLE groups (
groups_id numeric(20, 0) NOT NULL,
mandant_id numeric(20, 0) NOT NULL,
name varchar(20) NOT NULL,
description varchar(200) NOT NULL,
creation_date timestamp NOT NULL,
CONSTRAINT groups_pkey PRIMARY KEY (groups_id),
CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant (mandant_id)
) WITH OIDS;
CREATE UNIQUE INDEX groups_name_idx ON groups (mandant_id, name);
-- every mandant should have own access group INSERT INTO groups VALUES (1, 1, 'access', 'access group', now()); INSERT INTO groups VALUES (2, 1, 'system', 'system group', now()); INSERT INTO groups VALUES (3, 1, 'admin', 'admin group', now());
CREATE TABLE users_2_groups (
users_2_groups_id numeric(20, 0) NOT NULL,
users_id numeric(20, 0) NOT NULL,
groups_id numeric(20, 0) NOT NULL,
valid_from timestamp NOT NULL,
expired timestamp,
CONSTRAINT users_2_groups_pkey PRIMARY KEY (users_2_groups_id),
CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups (groups_id),
CONSTRAINT fk_users FOREIGN KEY (users_id) REFERENCES users (users_id)
) WITH OIDS;
CREATE UNIQUE INDEX users_2_groups_usersgroups__idx ON users_2_groups (users_id, groups_id);
INSERT INTO users_2_groups VALUES (1, 1, 1, now(), NULL); INSERT INTO users_2_groups VALUES (2, 1, 2, now(), NULL); INSERT INTO users_2_groups VALUES (3, 1, 3, now(), NULL);
CREATE TABLE groups_2_acl_entry (
groups_2_acl_entry_id numeric(20, 0) NOT NULL,
groups_id numeric(20, 0) NOT NULL,
acl_entry_id int2 NOT NULL,
CONSTRAINT groups_2_acl_entry_pkey PRIMARY KEY (groups_2_acl_entry_id),
CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups (groups_id),
CONSTRAINT fk_acl_entry FOREIGN KEY (acl_entry_id) REFERENCES acl_entry (acl_entry_id)
) WITH OIDS;
INSERT INTO groups_2_acl_entry VALUES (1, 1, 1); INSERT INTO groups_2_acl_entry VALUES (2, 2, 2); INSERT INTO groups_2_acl_entry VALUES (3, 2, 3); INSERT INTO groups_2_acl_entry VALUES (4, 3, 4); INSERT INTO groups_2_acl_entry VALUES (5, 3, 5);
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
