Package DB Schema v3

[Karel Zak]

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 at redhat.com>