Changeset 1032

Show
Ignore:
Timestamp:
12/29/07 22:11:43 (1 year ago)
Author:
samtay
Message:

Added karmaLocks table, members table, screenshots table, comments table

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • sandbox/appdb/sql/Database.sql

    r981 r1032  
     1CREATE TABLE members ( 
     2  memberID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
     3  memberEmail VARCHAR(255) NOT NULL, 
     4  memberPassword VARCHAR(255) NOT NULL, 
     5  joinDate TIMESTAMP NOT NULL, 
     6  userLevel INTEGER UNSIGNED NOT NULL, 
     7  PRIMARY KEY(memberID) 
     8); 
     9 
    110CREATE TABLE repository ( 
    211  repoID INT NOT NULL AUTO_INCREMENT, 
    …… 
    413  repoUri VARCHAR(255) NOT NULL, 
    514  PRIMARY KEY(repoID) 
     15); 
     16 
     17CREATE TABLE comments ( 
     18  commentID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
     19  memberID INTEGER UNSIGNED NOT NULL, 
     20  commentObject INTEGER UNSIGNED NOT NULL, 
     21  commentObjectId INTEGER UNSIGNED NOT NULL, 
     22  commentBody LONGTEXT NOT NULL, 
     23  commentDate TIMESTAMP NOT NULL, 
     24  commentUserIp VARCHAR(45) NOT NULL, 
     25  commentKarma INTEGER UNSIGNED NOT NULL DEFAULT '0', 
     26  PRIMARY KEY(commentID), 
     27  INDEX comments_FKIndex1(memberID), 
     28  FOREIGN KEY(memberID) 
     29    REFERENCES members(memberID) 
     30      ON DELETE NO ACTION 
     31      ON UPDATE NO ACTION 
     32); 
     33 
     34CREATE TABLE karmaLock ( 
     35  karmaLockId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
     36  memberID INTEGER UNSIGNED NOT NULL, 
     37  karmaObject INTEGER UNSIGNED NOT NULL, 
     38  karmaObjectID INTEGER UNSIGNED NOT NULL, 
     39  karmaUserIp VARCHAR(45) NOT NULL, 
     40  KarmaStatus INTEGER UNSIGNED NOT NULL, 
     41  karmaTime TIMESTAMP NOT NULL, 
     42  PRIMARY KEY(karmaLockId), 
     43  INDEX karmaLock_FKIndex1(memberID), 
     44  FOREIGN KEY(memberID) 
     45    REFERENCES members(memberID) 
     46      ON DELETE NO ACTION 
     47      ON UPDATE NO ACTION 
    648); 
    749 
    …… 
    2567); 
    2668 
     69CREATE TABLE screenshots ( 
     70  screenshotID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
     71  appID INT NOT NULL, 
     72  memberID INTEGER UNSIGNED NOT NULL, 
     73  screenshotFile VARCHAR(255) NOT NULL, 
     74  screenshotTime TIMESTAMP NOT NULL, 
     75  screenshotKarma INTEGER UNSIGNED NOT NULL DEFAULT '0', 
     76  PRIMARY KEY(screenshotID), 
     77  INDEX screenshots_FKIndex1(appID), 
     78  INDEX screenshots_FKIndex2(memberID), 
     79  FOREIGN KEY(appID) 
     80    REFERENCES applications(appID) 
     81      ON DELETE NO ACTION 
     82      ON UPDATE NO ACTION, 
     83  FOREIGN KEY(memberID) 
     84    REFERENCES members(memberID) 
     85      ON DELETE NO ACTION 
     86      ON UPDATE NO ACTION 
     87); 
     88 
    2789CREATE TABLE applicationVersions ( 
    2890  appVersionID INT NOT NULL AUTO_INCREMENT, 
     91  memberID INTEGER UNSIGNED NOT NULL, 
    2992  appID INT NOT NULL, 
    3093  majorVersion VARCHAR(20) NOT NULL, 
    …… 
    47110  uninstallerArgs VARCHAR(255) NULL, 
    48111  wdiFile VARCHAR(255) NOT NULL, 
     112  applicationVersionkarma INTEGER UNSIGNED NOT NULL DEFAULT '0', 
    49113  PRIMARY KEY(appVersionID), 
    50114  INDEX ApplcationVersions_FKIndex1(appID), 
     115  INDEX applicationVersions_FKIndex2(memberID), 
    51116  FOREIGN KEY(appID) 
    52117    REFERENCES applications(appID) 
     118      ON DELETE NO ACTION 
     119      ON UPDATE NO ACTION, 
     120  FOREIGN KEY(memberID) 
     121    REFERENCES members(memberID) 
    53122      ON DELETE NO ACTION 
    54123      ON UPDATE NO ACTION 
    55124); 
    56125 
    57 CREATE TABLE applicationRequires ( 
    58   appRequiresID INT NOT NULL AUTO_INCREMENT, 
     126CREATE TABLE applicationCracks ( 
     127  appCracksID INT NOT NULL AUTO_INCREMENT, 
    59128  appVersionID INT NOT NULL, 
    60   requires VARCHAR(45) NOT NULL, 
    61   requiresVersion VARCHAR(20) NULL, 
    62   PRIMARY KEY(appRequiresID), 
    63   INDEX applcationRequires_FKIndex1(appVersionID), 
     129  destination VARCHAR(255) NOT NULL, 
     130  crackUrl VARCHAR(255) NOT NULL, 
     131  crackCheckSum VARCHAR(255) NOT NULL, 
     132  PRIMARY KEY(appCracksID), 
     133  INDEX applicationCracks_FKIndex1(appVersionID), 
    64134  FOREIGN KEY(appVersionID) 
    65135    REFERENCES applicationVersions(appVersionID) 
    …… 
    74144  PRIMARY KEY(appCdID), 
    75145  INDEX applcationCD_FKIndex1(appVersionID), 
    76   FOREIGN KEY(appVersionID) 
    77     REFERENCES applicationVersions(appVersionID) 
    78       ON DELETE NO ACTION 
    79       ON UPDATE NO ACTION 
    80 ); 
    81  
    82 CREATE TABLE applicationCracks ( 
    83   appCracksID INT NOT NULL AUTO_INCREMENT, 
    84   appVersionID INT NOT NULL, 
    85   destination VARCHAR(255) NOT NULL, 
    86   crackUrl VARCHAR(255) NOT NULL, 
    87   crackCheckSum VARCHAR(255) NOT NULL, 
    88   PRIMARY KEY(appCracksID), 
    89   INDEX applicationCracks_FKIndex1(appVersionID), 
    90146  FOREIGN KEY(appVersionID) 
    91147    REFERENCES applicationVersions(appVersionID) 
    …… 
    107163); 
    108164 
     165CREATE TABLE applicationRequires ( 
     166  appRequiresID INT NOT NULL AUTO_INCREMENT, 
     167  appVersionID INT NOT NULL, 
     168  requires VARCHAR(45) NOT NULL, 
     169  requiresVersion VARCHAR(20) NULL, 
     170  PRIMARY KEY(appRequiresID), 
     171  INDEX applcationRequires_FKIndex1(appVersionID), 
     172  FOREIGN KEY(appVersionID) 
     173    REFERENCES applicationVersions(appVersionID) 
     174      ON DELETE NO ACTION 
     175      ON UPDATE NO ACTION 
     176); 
    109177 
     178