Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create MS SQL Server tables (lti2_*) for LTI-Tool-Provider-Library-PHP https://github.com/IMSGlobal/LTI-Tool-Provider-Library-PHP
-- creates the tables necessary to use the LTI provider library for SQL Server (tested against SQL Server 12.0.5540.0 (2014 Service Pack 2)
-- see [https://github.com/IMSGlobal/LTI-Tool-Provider-Library-PHP](LTI-Tool-Provider-Library-PHP)
CREATE TABLE lti2_consumer
(
consumer_pk INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
consumer_key256 VARCHAR(256) NOT NULL,
consumer_key TEXT NULL,
secret VARCHAR(1024) NOT NULL,
lti_version VARCHAR(10) NULL,
consumer_name VARCHAR(255) NULL,
consumer_version VARCHAR(255) NULL,
consumer_guid VARCHAR(1024) NULL,
profile TEXT NULL,
tool_proxy TEXT NULL,
settings TEXT NULL,
protected TINYINT NOT NULL,
enabled TINYINT NOT NULL,
enable_from DATETIME NULL,
enable_until DATETIME NULL,
last_access DATE NULL,
created DATETIME NOT NULL,
updated DATETIME NOT NULL
);
CREATE UNIQUE INDEX lti2_consumer_consumer_key_UNIQUE
ON lti2_consumer (consumer_key256);
CREATE TABLE lti2_context
(
context_pk INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
consumer_pk INT NOT NULL,
lti_context_id VARCHAR(255) NOT NULL,
settings TEXT NULL,
created DATETIME NOT NULL,
updated DATETIME NOT NULL,
CONSTRAINT lti2_context_lti2_consumer_FK1
FOREIGN KEY (consumer_pk) REFERENCES LTI_APPS.dbo.lti2_consumer (consumer_pk)
);
CREATE INDEX lti2_context_consumer_id_IDX
ON lti2_context (consumer_pk);
CREATE TABLE lti2_nonce
(
consumer_pk INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
value VARCHAR(32) NOT NULL,
expires DATETIME NOT NULL,
CONSTRAINT lti2_nonce_lti2_consumer_FK1
FOREIGN KEY (consumer_pk) REFERENCES LTI_APPS.dbo.lti2_consumer (consumer_pk)
);
CREATE TABLE lti2_resource_link
(
resource_link_pk INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
context_pk INT NULL,
consumer_pk INT NULL,
lti_resource_link_id VARCHAR(255) NOT NULL,
settings TEXT NULL,
primary_resource_link_pk INT NULL,
share_approved TINYINT NULL,
created DATETIME NOT NULL,
updated DATETIME NOT NULL,
CONSTRAINT lti2_resource_link_lti2_context_FK1
FOREIGN KEY (context_pk) REFERENCES LTI_APPS.dbo.lti2_context (context_pk),
CONSTRAINT lti2_resource_link_lti2_resource_link_FK1
FOREIGN KEY (primary_resource_link_pk) REFERENCES LTI_APPS.dbo.lti2_resource_link (resource_link_pk)
);
CREATE INDEX lti2_resource_link_consumer_pk_IDX
ON lti2_resource_link (consumer_pk);
CREATE INDEX lti2_resource_link_context_pk_IDX
ON lti2_resource_link (context_pk);
CREATE INDEX lti2_resource_link_lti2_resource_link_FK1
ON lti2_resource_link (primary_resource_link_pk);
create table lti2_share_key
(
share_key_id varchar(32) not null
primary key,
resource_link_pk int not null,
auto_approve tinyint not null,
expires datetime not null,
constraint lti2_share_key_lti2_resource_link_FK1
foreign key (resource_link_pk) references LTI_APPS.dbo.lti2_resource_link (resource_link_pk)
)
;
create index lti2_share_key_resource_link_pk_IDX
on lti2_share_key (resource_link_pk)
;
CREATE TABLE lti2_tool_proxy
(
tool_proxy_pk INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
tool_proxy_id VARCHAR(32) NOT NULL,
consumer_pk INT NOT NULL,
tool_proxy TEXT NOT NULL,
created DATETIME NOT NULL,
updated DATETIME NOT NULL,
CONSTRAINT lti2_tool_proxy_tool_proxy_id_UNIQUE
UNIQUE (tool_proxy_id),
CONSTRAINT lti2_tool_proxy_lti2_consumer_FK1
FOREIGN KEY (consumer_pk) REFERENCES LTI_APPS.dbo.lti2_consumer (consumer_pk)
);
CREATE INDEX lti2_tool_proxy_consumer_id_IDX
ON lti2_tool_proxy (consumer_pk);
CREATE TABLE lti2_user_result
(
user_pk INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
resource_link_pk INT NOT NULL,
lti_user_id VARCHAR(255) NOT NULL,
lti_result_sourcedid VARCHAR(1024) NOT NULL,
created DATETIME NOT NULL,
updated DATETIME NOT NULL,
CONSTRAINT lti2_user_result_lti2_resource_link_FK1
FOREIGN KEY (resource_link_pk) REFERENCES LTI_APPS.dbo.lti2_resource_link (resource_link_pk)
);
CREATE INDEX lti2_user_result_resource_link_pk_IDX
ON lti2_user_result (resource_link_pk);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment