pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Rafal Kedziorski <rafcio@polonium.de>
To: pgsql-sql@postgresql.org
Subject: good style?
Date: Fri, 21 Feb 2003 14:30:22 +0100
Message-ID: <3E5629EE.6000406@polonium.de> (raw )
hi,
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, 'rafcio@polonium.de', '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);
view thread (8+ messages) latest in thread
Message-ID: <3E5629EE.6000406@polonium.de>
Permalink: ../3E5629EE.6000406@polonium.de/
Also on: postgresql.org/message-id/3E5629EE.6000406@polonium.de
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rafcio@polonium.de
Subject: Re: good style?
In-Reply-To: <3E5629EE.6000406@polonium.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox