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@xxxxxxxxxx>
