Create MS SQL Server tables (lti2_*) for LTI-Tool-Provider-Library-PHP https://github.com/IMSGlobal/LTI-Tool-Provider-Library-PHP

sqlsrv_create_lti2apps.sql

-- 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);