On Mon, Dec 04, 2006 at 09:59:11PM -0800, Toshio Kuratomi wrote:
create table Collection (
id serial primary key,
name text not null,
version text not null,
status text not null default 'development',
owner integer not null,
publishURLTemplate text null,
From PostgreSQL docs:
NULL
The column is allowed to contain null values. This is the
default.
^^^^^^
This clause is only provided for compatibility with
non-standard SQL databases. Its use is discouraged in new
applications.
pendingURLTemplate text null,
summary text null,
description text null,
unique (name, version),
check (status = 'development' or status = 'active' or status =
'maintanence'
or status = 'EOL' or status = 'rejected')
);
From my point of view the status column is odd. I think you should
use an abbreviation or one char only.
status text not null default 'D'
check ( status IN
('D','A','M','E','R') )
create table Branch (
collectionId integer not null primary key,
branchName varchar(32) not null,
^^^^^^^^^
distTag varchar(32) not null,
^^^^^^
is it right define duplicate tags and branch names?
parentId integer null,
foreign key (parentId) references Collection(id),
foreign key (collectionId) references Collection(id)
);
Hmm.. here I see 1:1 model (PK=FK). Strange. (It usually means that
you should merge the tables to one table only.) Maybe:
create table Branch (
id serial primary key,
branchName varchar(32) not null unique,
distTag varchar(32) not null unique,
parentId integer references Collection(id),
collectionId integer not null references Collection(id)
);
Also, I think there should be defined some FK policy for update and
delete. It means "ON DELETE" and "ON UPDATE" definition for the
references.
create table Package (
id serial primary key,
name text not null unique,
summary text not null,
description text null,
reviewURL text null,
status text not null default 'awaitingreview',
check (status = 'awaitingreview' or status = 'underreview' or status =
'approved' or status = 'denied')
);
IMHO, same problem with the status column. Here it is more terrible,
because this table will be larger.
CHECK( status IN (
'W', -- wait
'R', -- review
'A', -- approved
'D' -- denied
))
-- Permissions for who can make various changes to the code.
-- We want to limit the access that a given person may have to edit the package
--
-- Fields:
-- :id: Primary key
-- :pkgListId: What package in what collection has this value.
-- :acl: The permission being set.
-- :status: Whether this permission is active.
create table PackageACL (
id serial primary key,
packageListingId integer not null,
acl text not null,
status text not null,
foreign key (packageListingId) references PackageListing(id),
check (status = 'awaitingreview' or status = 'approved' or status =
'denied'
or status = 'obsolete'),
check (acl = 'commit' or acl = 'build' or acl =
'watchbugzilla'
or acl = 'watchcommits' or acl = 'approveacls' or acl =
'checkout')
);
-- ACLs that allow a person to do something
--
-- Fields:
-- :packageACLId: Inherit from an ACL record.
-- :userId: User id from the account system.
create table PersonPackageACL (
packageACLId integer primary key,
userId integer not null,
foreign key (packageACLId) references PackageACL (id)
);
Again. 1:1 model (PK=FK).
I think the ACL model should be:
what (ACL), where (package), who (user)
you can use one table only (or two if ACL is group of permissions).
-- ACLs that allow a group to do something
--
-- Fields:
-- :packageACLId: Inherit from an ACL record.
-- :groupId: Group id from the account system.
create table GroupPackagePermissions (
packageACLId integer primary key,
groupId integer not null,
foreign key (PackageACLId) references PackageACL (id)
);
Again. 1:1
-- Log a change to the packageDB.
--
-- Fields:
-- :id: Primary key
-- :userId: Who made the change.
-- :changeTime: Time that the change occurred.
create table Log (
id serial primary key,
userId integer not null,
changeTime timestamp default now() not null
);
When, Who, ... and where is "What"? (type of change)
-- Log a change made to the Package table.
--
-- Fields:
-- :logId: The id of the log entry.
-- :packageId: The package that changed.
-- :action: What happened to the package.
-- :description: Additional information about the change.
create table PackageLog (
logId integer primary key,
packageId integer not null,
action text not null,
description text null,
check (action = 'added' or action = 'removed' or action =
'statuschanged' or
action = 'awaitingreview' or action = 'underreview' or action =
'approved'
or action = 'denied'),
foreign key (logId) references Log(id),
foreign key (packageId) references Package(id)
);
... ah... here is the "What". Again 1:1 (PK=FK).
Don't forget PRIMARY KEY is always UNIQUE.
-- Log changes to packages in collections.
--
-- Fields:
-- :logId: The id of the log entry.
-- :packageListingId: The packageListing that changed.
-- :action: What happened to the package in the collection.
-- :description: Additional information about the change.
create table PackageListingLog (
logId integer primary key,
packageListingId integer not null,
action text not null,
description text null,
check (action = 'added' or action = 'removed' or action =
'awaitingreview'
or action = 'awaitingbranch' or action = 'underreview' or
action = 'approved' or action = 'denied'),
foreign key (logId) references Log (id),
foreign key (packageListingId) references PackageListing(id)
);
Again. 1:1.
-- Log changes to built packages.
--
-- Fields:
-- :logId: The id of the log entry.
-- :packageVersionId: The `PackageVersion` that changed.
-- :action: What happened to the `PackageVersion`.
-- :description: Additional information about the change.
create table PackageVersionLog (
logId integer primary key,
packageVersionId integer not null,
action text not null,
description text null,
check (action = 'added' or action = 'awaitingdevel' or
action = 'awaitingreview' or action = 'awaitingqa' or
action = 'aaitingpublish' or action = 'approved' or action =
'denied' or
action = 'obsolete'),
foreign key (logId) references Log (id),
foreign key (packageVersionId) references PackageVersion(id)
);
Again. 1:1.
-- Log changes to built package ACLs.
--
-- Fields:
-- :logId: The id of the log entry.
-- :packageVersionId: The `PackageACL` that changed.
-- :action: What happened to the ACLs for the package.
-- :description: Additional information about the change.
create table PackageACLLog (
logId integer primary key,
packageACLId integer not null,
action text not null,
description text null,
check (action = 'added' or action = 'awaitingreview'
or action = 'awaitingbranch' or action = 'underreview' or
action = 'approved' or action = 'denied' or action =
'obsolete'),
foreign key (logId) references Log (id),
foreign key (packageACLId) references PackageACL(id)
);
Again. 1:1.
Karel
--
Karel Zak <kzak(a)redhat.com>