DiscvrLabKeyModules/Studies/resources/schemas/dbscripts/sqlserver/studies-23.001-23.002.sql at ee17b5c957cbf7e3b24f542275f73f12486c57f0 · BimberLab/DiscvrLabKeyModules · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
CREATE TABLE studies.studies (
    rowid int identity(1,1),
    studyName varchar(1000),
    label varchar(1000),
    category varchar(1000),
    description varchar(4000),

    lsid entityid,
    container entityid,
    created datetime,
    createdby int,
    modified datetime,
    modifiedby int,

    CONSTRAINT PK_studies PRIMARY KEY (rowid)
);

CREATE TABLE studies.studyCohorts (
    rowid int identity(1,1),
    studyId int,
    cohortName varchar(4000),
    label varchar(4000),
    category varchar(4000),
    description varchar(4000),
    isControlGroup bit default 0,
    sortOrder int,

    lsid entityid,
    container entityid,
    created datetime,
    createdby int,
    modified datetime,
    modifiedby int,

    CONSTRAINT PK_studyCohorts PRIMARY KEY (rowid)
);

CREATE TABLE studies.anchorEvents (
    rowid int identity(1,1),
    studyId int,
    label varchar(4000),
    description varchar(4000),
    eventProviderName varchar(1000),

    container entityid,
    created datetime,
    createdby int,
    modified datetime,
    modifiedby int,

    CONSTRAINT PK_anchorEvents PRIMARY KEY (rowid)
);

CREATE TABLE studies.expectedTimepoints (
    rowid int identity(1,1),
    studyId int,
    cohortId int,
    label varchar(4000),
    labelShort varchar(100),
    description varchar(4000),
    numericLabel int,
    anchorEvent int,
    rangeMin int,
    rangeMax int,

    container entityid,
    created datetime,
    createdby int,
    modified datetime,
    modifiedby int,

    CONSTRAINT PK_expectedTimepoints PRIMARY KEY (rowid)
);

CREATE TABLE studies.timepointToDate (
    rowid int identity(1,1),
    subjectId varchar(4000),
    timepointId int,
    dateMin datetime,
    dateMax datetime,
    isManualOverride bit default 0,

    container entityid,
    created datetime,
    createdby int,
    modified datetime,
    modifiedby int,

    CONSTRAINT PK_timepointToDate PRIMARY KEY (rowid)
);