[25106] users/jberry/mpwa/doc/schema.sql

[source_changes at macosforge.org]

Revision: 25106
          http://trac.macosforge.org/projects/macports/changeset/25106
Author:   jberry at macports.org
Date:     2007-05-13 21:30:12 -0700 (Sun, 13 May 2007)

Log Message:
-----------
mpwa: Flesh out schema some more.
 * Add Comments for ports and port_pkgs.
 * Add status reports for port_pkgs, to deal with logging of things like build failures
 * Add votes for/against portpkgs
 * Add download counts for all files, and also generically for portpkgs to avoid a join

Modified Paths:
--------------
    users/jberry/mpwa/doc/schema.sql

Modified: users/jberry/mpwa/doc/schema.sql
===================================================================
--- users/jberry/mpwa/doc/schema.sql	2007-05-14 03:20:47 UTC (rev 25105)
+++ users/jberry/mpwa/doc/schema.sql	2007-05-14 04:30:12 UTC (rev 25106)
@@ -48,7 +48,11 @@
     
     epoch           varchar(32),
     version         varchar(32),
-    revision        varchar(32)
+    revision        varchar(32),
+    
+    votes_for       int not null,
+    votes_against   int not null,
+    download_count  int not null
                                     
     -- one-many association for variants
     -- many-many association for tags through PkgTagAssoc
@@ -69,7 +73,9 @@
     length              bigint not null,
     mime_type           varchar(63),
     md5                 varchar(32),
-    sha256              varchar(64)
+    sha256              varchar(64),
+    
+    download_count      int not null
 );
 create index portpkgfile_pkgid on Port_Pkg_Files(port_pkg_id);
 
@@ -87,44 +93,44 @@
 -- A tag which may be attached to various items through Ports_Tags, Port_Pkgs_Tags
 drop table if exists Tags;
 create table Tags (
-    id              bigint not null primary key auto_increment,
-    name            varchar(31)
+    id                  bigint not null primary key auto_increment,
+    name                varchar(31)
 );
 create unique index tagNames on Tags(name);
        
 -- many-many relationship between PortPkg and Tag
 drop table if exists Port_Pkgs_Tags;
 create table Port_Pkgs_Tags (
-    port_pkg_id     bigint not null,
-    tag_id          bigint not null
+    port_pkg_id         bigint not null,
+    tag_id              bigint not null
 );
 
 -- many-many relationship between Port and Tag
 drop table if exists Ports_Tags;
 create table Ports_Tags (
-    port_id         bigint not null,
-    tag_id          bigint not null
+    port_id             bigint not null,
+    tag_id              bigint not null
 );
 
 -- Variant available to a PortPkg
 drop table if exists Variants;
 create table Variants (
-    id              bigint not null primary key auto_increment,
-    port_pkg_id     bigint not null,
+    id                  bigint not null primary key auto_increment,
+    port_pkg_id         bigint not null,
     
-    name            varchar(63),
-    description     text
+    name                varchar(63),
+    description         text
     
     -- many-many association for dependencies through Dependencies_Variants
     
     -- conflicts expr?
 );
 
--- A dependency onto onther port
+-- A dependency onto another port (not complete)
 drop table if exists Dependencies;
 create table Dependencies (
-    id              bigint not null primary key auto_increment,
-    expression      text                -- textual? dependency expression
+    id                  bigint not null primary key auto_increment,
+    expression          text                -- textual? dependency expression
     
     -- can we point directly to the target port (or portpkg, or porturl???)
     -- that would make it much easier to determine reverse dependencies.
@@ -134,21 +140,64 @@
 -- many-one relationship from Dependency to PortPkg
 drop table if exists Dependencies_Port_Pkgs;
 create table Dependencies_Port_Pkgs (
-    package_id      bigint not null,
-    dependency_id   bigint not null
+    package_id          bigint not null,
+    dependency_id       bigint not null
 );
 
 -- many-one relationship from Variant to Dependency
 drop table if exists Dependencies_Variants;
 create table Dependencies_Variants (
-    variant_id      bigint not null,
-    dependency_id   bigint not null
+    variant_id          bigint not null,
+    dependency_id       bigint not null
 );
 
 
+-- Human comments on ports and portpkgs
+drop table if exists Comments;
+create table Comments (
+    id                  bigint not null primary key auto_increment,
+    commenter_id        bigint not null, -- many-one: Person
+    comment             text,
+    comment_at          timestamp not null
+);
+
+
+-- many-one relationship from Comment to Port
+drop table if exists Comments_Ports;
+create table Comments_Ports (
+    comment_id          bigint not null,
+    port_id             bigint not null
+);
+
+
+-- many-one relationship from Comment to PortPkg
+drop table if exists Comments_Port_Pkgs;
+create table Comments_Port_Pkgs (
+    comment_id          bigint not null,
+    port_pkg_id         bigint not null
+);
+
+
+-- Status reports on port_pkgs, perhaps machine submitted
+drop table if exists Status_Reports;
+create table Status_Reports (
+    id                  bigint not null primary key auto_increment,
+    reporter_id         bigint not null, -- many-one: Person
+    report_type         int,
+    status              int,
+    report              text,
+    report_at           timestamp not null
+);
+
+
+-- many-one relationship from Status_Report to Port_Pkg
+drop table if exists Status_Reports_Port_Pkgs;
+create table Status_Reports_Port_Pkgs (
+    status_report_id    bigint not null,
+    port_pkg_id         bigint not null
+);
+
+
+
 -- Missing components:
 -- ==================================
--- Votes of confidence for/against portpkgs
--- Build status reports on portpkgs
--- Build logs against portpkgs
--- Comments on portpkgs, ports?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.macosforge.org/pipermail/macports-changes/attachments/20070513/67a5b686/attachment.html