-
Notifications
You must be signed in to change notification settings - Fork 38
/
Copy pathlti4-tables-oracle.sql
140 lines (119 loc) · 4.63 KB
/
lti4-tables-oracle.sql
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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
CREATE TABLE lti2_consumer (
consumer_pk number GENERATED ALWAYS AS IDENTITY,
name varchar(50) NOT NULL,
consumer_key varchar(255) DEFAULT NULL,
secret varchar(1024) DEFAULT NULL,
platform_id varchar(255) DEFAULT NULL,
client_id varchar(255) DEFAULT NULL,
deployment_id varchar(255) DEFAULT NULL,
public_key clob DEFAULT NULL,
lti_version varchar(12) DEFAULT NULL,
signature_method varchar(15) DEFAULT 'HMAC-SHA1' NOT NULL,
consumer_name varchar(255) DEFAULT NULL,
consumer_version varchar(255) DEFAULT NULL,
consumer_guid varchar(1024) DEFAULT NULL,
profile clob DEFAULT NULL,
tool_proxy clob DEFAULT NULL,
settings clob DEFAULT NULL,
protected number(1) NOT NULL,
enabled number(1) NOT NULL,
enable_from timestamp DEFAULT NULL,
enable_until timestamp DEFAULT NULL,
last_access date DEFAULT NULL,
created timestamp NOT NULL,
updated timestamp NOT NULL,
CONSTRAINT lti2_consumer_PK PRIMARY KEY (consumer_pk)
);
CREATE UNIQUE INDEX lti2_consumer_consumer_key_UNIQUE
ON lti2_consumer (consumer_key);
CREATE UNIQUE INDEX lti2_consumer_platform_UNIQUE
ON lti2_consumer (platform_id, client_id, deployment_id);
CREATE TABLE lti2_nonce (
consumer_pk number NOT NULL,
value varchar(50) NOT NULL,
expires timestamp NOT NULL,
CONSTRAINT lti2_nonce_PK PRIMARY KEY (consumer_pk, value)
);
ALTER TABLE lti2_nonce
ADD CONSTRAINT lti2_nonce_lti2_consumer_FK1 FOREIGN KEY (consumer_pk)
REFERENCES lti2_consumer (consumer_pk);
CREATE TABLE lti2_access_token (
consumer_pk number NOT NULL,
scopes clob NOT NULL,
token varchar(2000) NOT NULL,
expires timestamp NOT NULL,
created timestamp NOT NULL,
updated timestamp NOT NULL,
CONSTRAINT lti2_access_token_PK PRIMARY KEY (consumer_pk)
);
ALTER TABLE lti2_access_token
ADD CONSTRAINT lti2_access_token_lti2_consumer_FK1 FOREIGN KEY (consumer_pk)
REFERENCES lti2_consumer (consumer_pk);
CREATE TABLE lti2_context (
context_pk number GENERATED ALWAYS AS IDENTITY,
consumer_pk number NOT NULL,
title varchar(255) DEFAULT NULL,
lti_context_id varchar(255) DEFAULT NULL,
type varchar(50) DEFAULT NULL,
settings clob DEFAULT NULL,
created timestamp NOT NULL,
updated timestamp NOT NULL,
CONSTRAINT lti2_context_PK PRIMARY KEY (context_pk)
);
ALTER TABLE lti2_context
ADD CONSTRAINT lti2_context_lti2_consumer_FK1 FOREIGN KEY (consumer_pk)
REFERENCES lti2_consumer (consumer_pk);
CREATE INDEX lti2_context_consumer_id_IDX
ON lti2_context (consumer_pk ASC);
CREATE TABLE lti2_resource_link (
resource_link_pk number GENERATED ALWAYS AS IDENTITY,
context_pk number DEFAULT NULL,
consumer_pk number DEFAULT NULL,
title varchar(255) DEFAULT NULL,
lti_resource_link_id varchar(255) NOT NULL,
settings clob DEFAULT NULL,
primary_resource_link_pk number DEFAULT NULL,
share_approved number(1) DEFAULT NULL,
created timestamp NOT NULL,
updated timestamp NOT NULL,
CONSTRAINT lti2_resource_link_PK PRIMARY KEY (resource_link_pk)
);
ALTER TABLE lti2_resource_link
ADD CONSTRAINT lti2_resource_link_lti2_consumer_FK1 FOREIGN KEY (consumer_pk)
REFERENCES lti2_consumer (consumer_pk);
ALTER TABLE lti2_resource_link
ADD CONSTRAINT lti2_resource_link_lti2_context_FK1 FOREIGN KEY (context_pk)
REFERENCES lti2_context (context_pk);
ALTER TABLE lti2_resource_link
ADD CONSTRAINT lti2_resource_link_lti2_resource_link_FK1 FOREIGN KEY (primary_resource_link_pk)
REFERENCES lti2_resource_link (resource_link_pk);
CREATE INDEX lti2_resource_link_consumer_pk_IDX
ON lti2_resource_link (consumer_pk ASC);
CREATE INDEX lti2_resource_link_context_pk_IDX
ON lti2_resource_link (context_pk ASC);
CREATE TABLE lti2_user_result (
user_result_pk number GENERATED ALWAYS AS IDENTITY,
resource_link_pk number NOT NULL,
lti_user_id varchar(255) NOT NULL,
lti_result_sourcedid varchar(1024) NOT NULL,
created timestamp NOT NULL,
updated timestamp NOT NULL,
CONSTRAINT lti2_user_result_PK PRIMARY KEY (user_result_pk)
);
ALTER TABLE lti2_user_result
ADD CONSTRAINT lti2_user_result_lti2_resource_link_FK1 FOREIGN KEY (resource_link_pk)
REFERENCES lti2_resource_link (resource_link_pk);
CREATE INDEX lti2_user_result_resource_link_pk_IDX
ON lti2_user_result (resource_link_pk ASC);
CREATE TABLE lti2_share_key (
share_key_id varchar(32) NOT NULL,
resource_link_pk number NOT NULL,
auto_approve number(1) NOT NULL,
expires timestamp NOT NULL,
CONSTRAINT lti2_share_key_PK PRIMARY KEY (share_key_id)
);
ALTER TABLE lti2_share_key
ADD CONSTRAINT lti2_share_key_lti2_resource_link_FK1 FOREIGN KEY (resource_link_pk)
REFERENCES lti2_resource_link (resource_link_pk);
CREATE INDEX lti2_share_key_resource_link_pk_IDX
ON lti2_share_key (resource_link_pk ASC);