github.com/marinho/drone@v0.2.1-0.20140504195434-d3ba962e89a7/pkg/database/schema/schema.sql (about)

     1  DROP TABLE IF EXISTS builds;
     2  DROP TABLE IF EXISTS commits;
     3  DROP TABLE IF EXISTS repos;
     4  DROP TABLE IF EXISTS members;
     5  DROP TABLE IF EXISTS teams;
     6  DROP TABLE IF EXISTS users;
     7  DROP TABLE IF EXISTS settings;
     8  
     9  CREATE TABLE users (
    10  	 id       INTEGER PRIMARY KEY AUTOINCREMENT
    11  	,email    VARCHAR(255) UNIQUE
    12  	,password VARCHAR(255)
    13  	,token    VARCHAR(255) UNIQUE
    14  	,name     VARCHAR(255)
    15  	,gravatar VARCHAR(255)
    16  	,created  TIMESTAMP
    17  	,updated  TIMESTAMP
    18  	,admin    BOOLEAN
    19  
    20  	,github_login      VARCHAR(255)
    21  	,github_token      VARCHAR(255)
    22  
    23  	,bitbucket_login   VARCHAR(255)
    24  	,bitbucket_token   VARCHAR(255)
    25  	,bitbucket_secret  VARCHAR(255)
    26  );
    27  
    28  CREATE TABLE teams (
    29  	 id        INTEGER PRIMARY KEY AUTOINCREMENT
    30  	,slug      VARCHAR(255) UNIQUE
    31  	,name      VARCHAR(255) UNIQUE
    32  	,email     VARCHAR(255)
    33  	,gravatar  VARCHAR(255)
    34  	,created   TIMESTAMP
    35  	,updated   TIMESTAMP
    36  );
    37  
    38  CREATE TABLE members (
    39  	 id      INTEGER PRIMARY KEY AUTOINCREMENT
    40  	,team_id INTEGER
    41  	,user_id INTEGER
    42  	,role    INTEGER
    43  );
    44  
    45  CREATE TABLE repos (
    46  	 id            INTEGER PRIMARY KEY AUTOINCREMENT
    47  	,slug          VARCHAR(1024) UNIQUE
    48  	,host          VARCHAR(255)
    49  	,owner         VARCHAR(255)
    50  	,name          VARCHAR(255)
    51  	,private       BOOLEAN
    52  	,disabled      BOOLEAN
    53  	,disabled_pr   BOOLEAN
    54  	,privileged    BOOLEAN
    55  	,timeout       INTEGER
    56  
    57  	,scm         VARCHAR(25)
    58  	,url         VARCHAR(1024)
    59  	,username    VARCHAR(255)
    60  	,password    VARCHAR(255)
    61  	,public_key  VARCHAR(1024)
    62  	,private_key VARCHAR(1024)
    63  	,params      VARCHAR(2000)
    64  
    65  	,created     TIMESTAMP
    66  	,updated     TIMESTAMP
    67  	,user_id     INTEGER
    68  	,team_id     INTEGER
    69  );
    70  
    71  CREATE TABLE commits (
    72  	 id           INTEGER PRIMARY KEY AUTOINCREMENT
    73  	,repo_id      INTEGER
    74  	,status       VARCHAR(255)
    75  	,started      TIMESTAMP
    76  	,finished     TIMESTAMP
    77  	,duration     INTEGER
    78  	,hash         VARCHAR(255)
    79  	,branch       VARCHAR(255)
    80  	,pull_request VARCHAR(255)
    81  	,author       VARCHAR(255)
    82  	,gravatar     VARCHAR(255)
    83  	,timestamp    VARCHAR(255)
    84  	,message      VARCHAR(255)
    85  	,created      TIMESTAMP
    86  	,updated      TIMESTAMP
    87  );
    88  
    89  CREATE TABLE builds (
    90  	 id        INTEGER PRIMARY KEY AUTOINCREMENT
    91  	,commit_id INTEGER
    92  	,slug      VARCHAR(255)
    93  	,status    VARCHAR(255)
    94  	,started   TIMESTAMP
    95  	,finished  TIMESTAMP
    96  	,duration  INTEGER
    97  	,created   TIMESTAMP
    98  	,updated   TIMESTAMP
    99  	,stdout    BLOB
   100  );
   101  
   102  CREATE TABLE settings (
   103       id               INTEGER PRIMARY KEY
   104      ,github_key       VARCHAR(255)
   105      ,github_secret    VARCHAR(255)
   106      ,github_domain    VARCHAR(255)
   107      ,github_apiurl    VARCHAR(255)
   108      ,bitbucket_key    VARCHAR(255)
   109      ,bitbucket_secret VARCHAR(255)
   110      ,smtp_server      VARCHAR(1024)
   111      ,smtp_port        VARCHAR(5)
   112      ,smtp_address     VARCHAR(1024)
   113      ,smtp_username    VARCHAR(1024)
   114      ,smtp_password    VARCHAR(1024)
   115      ,hostname         VARCHAR(1024)
   116      ,scheme           VARCHAR(5)
   117      ,open_invitations BOOLEAN
   118  );
   119  
   120  CREATE UNIQUE INDEX member_uix       ON members  (team_id, user_id);
   121  CREATE UNIQUE INDEX commits_uix      ON commits  (repo_id, hash, branch);
   122  
   123  CREATE INDEX member_team_ix          ON members (team_id);
   124  CREATE INDEX member_user_ix          ON members (user_id);
   125  CREATE INDEX repo_team_ix            ON repos   (team_id);
   126  CREATE INDEX repo_user_ix            ON repos   (user_id);
   127  CREATE INDEX commits_repo_ix         ON commits (repo_id);
   128  CREATE INDEX commits_repo_branch_ix  ON commits (repo_id, branch);
   129  CREATE INDEX builds_commit_ix        ON builds  (commit_id);
   130  CREATE INDEX builds_commit_slug_ix   ON builds  (commit_id, slug);