-- Script Date: 9/23/2019 3:17 PM  - ErikEJ.SqlCeScripting version 3.5.2.81
-- Database information:
-- Database: System.App.Web.Spectrometry\Data\DataSets.s3db
-- ServerVersion: 3.27.2
-- DatabaseSize: 13273.531 MB
-- Created: 5/15/2019 9:32 PM

-- User Table information:
-- Number of tables: 11
-- AlgorithmSet: -1 row(s)
-- ConceptSet: -1 row(s)
-- DataSetSet: -1 row(s)
-- ImageAnnotationSet: -1 row(s)
-- ImageSet: -1 row(s)
-- LinkSet: -1 row(s)
-- LogSet: -1 row(s)
-- PipelineSet: -1 row(s)
-- SampleSet: -1 row(s)
-- SpectrumSet: -1 row(s)
-- TaskSet: -1 row(s)

SELECT 1;
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE [SpectrumSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [FilePath] nvarchar(500) NULL COLLATE NOCASE
, [Digest] nvarchar(100) NULL COLLATE NOCASE
, [Predict] nvarchar(100) NULL COLLATE NOCASE
, [Category] smallint NULL
, [PredictedCategory] smallint NULL
, [Sequence] varbinary(2147483647) NULL COLLATE NOCASE
, [Thumbnail] varbinary(2147483647) NULL COLLATE NOCASE
, [DataType] nvarchar(20) NULL COLLATE NOCASE
, [Type] int NULL
, [Metadata] text NULL
, [Timestamp] timestamp NULL
, [Source] nvarchar(100) NULL COLLATE NOCASE
, [Topic] nvarchar(100) NULL COLLATE NOCASE
, [XAxisMeaning] nvarchar(50) NULL COLLATE NOCASE
, [XAxisUnit] nvarchar(20) NULL COLLATE NOCASE
, [ExternId] bigint NULL
);
CREATE TABLE [SampleSet] (
  [Id] int identity NOT NULL
, [FilePath] nvarchar(500) NULL COLLATE NOCASE
, [Digest] nvarchar(100) NULL COLLATE NOCASE
, [Predict] nvarchar(100) NULL COLLATE NOCASE
, [Category] smallint NULL
, [PredictedCategory] smallint NULL
, [Sequence] varbinary(2147483647) NULL COLLATE NOCASE
, [Thumbnail] varbinary(2147483647) NULL COLLATE NOCASE
, [DataType] nvarchar(20) NULL COLLATE NOCASE
);
CREATE TABLE [PipelineSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [Name] text NULL
, [Reference] text NULL
, [InputCode] text NULL
, [Description] text NULL
, [Metadata] text NULL
, [PublishDate] date NULL
, [Timestamp] date NULL
, [TrainTemplate] text NULL
, [TestTemplate] text NULL
);
CREATE TABLE [LogSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [Operator] nvarchar(60) NULL COLLATE NOCASE
, [Device] nvarchar(60) NULL COLLATE NOCASE
, [Location] nvarchar(60) NULL COLLATE NOCASE
, [Message] text NULL
, [Subject] nvarchar(60) NULL COLLATE NOCASE
, [Timestamp] timestamp NULL
, [Operation] nvarchar(60) NULL COLLATE NOCASE
, [ExternId] bigint NULL
, [Type] bigint NULL
);
CREATE TABLE [ImageSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [FilePath] nvarchar(500) NULL COLLATE NOCASE
, [Digest] nvarchar(100) NULL COLLATE NOCASE
, [Predict] nvarchar(100) NULL COLLATE NOCASE
, [Category] smallint NULL
, [PredictedCategory] smallint NULL
, [Sequence] varbinary(2147483647) NULL COLLATE NOCASE
, [Thumbnail] varbinary(2147483647) NULL COLLATE NOCASE
, [DataType] nvarchar(20) NULL COLLATE NOCASE
, [Type] int NULL
, [Metadata] text NULL
);
CREATE TABLE [ImageAnnotationSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [Label] nvarchar(400) NULL COLLATE NOCASE
, [LabelCode] nvarchar(400) NULL COLLATE NOCASE
, [Annotation] text NULL
, [Name] nvarchar(200) NULL COLLATE NOCASE
, [Description] nvarchar(400) NULL COLLATE NOCASE
, [TimeStamp] datetime NULL
, [Editor] nvarchar(100) NULL COLLATE NOCASE
, [ImageFileName] nvarchar(200) NULL COLLATE NOCASE
, [ImageFilePath] nvarchar(400) NULL COLLATE NOCASE
, [Thumbnail] text NULL
, [Link] nvarchar(200) NULL COLLATE NOCASE
, [Assign] nvarchar(100) NULL COLLATE NOCASE
, [CheckSum] nvarchar(100) NULL COLLATE NOCASE
, [Comment] nvarchar(400) NULL COLLATE NOCASE
, [Deleted] bit NULL
, [Access] int NULL
);
CREATE TABLE [ExperimentSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [Name] text NULL
, [Code] text NULL
, [Object] text NULL
, [Topic] text NULL
, [SOP] text NULL
, [Device] text NULL
, [Modality] int NULL
, [Samples] bigint NULL
, [XLabels] text NULL
, [YLabels] text NULL
, [YLabelSamples] text NULL
, [Timestamp] timestamp NULL
, [FilePath] text NULL
, [Url] text NULL
);
CREATE TABLE [ConceptSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [Code] text NULL
, [CodingSystem] text NULL
, [Url] text NULL
, [Hierachy] text NULL
, [Literal] text NULL
, [Alias1] text NULL
, [Alias2] text NULL
, [Description] text NULL
, [Metadata] text NULL
, [Image] text NULL
);
CREATE TABLE [AssociationSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [Code1] text NULL
, [Code2] text NULL
, [Entity1] text NULL
, [Entity2] text NULL
, [Name] text NULL
, [Code] text NULL
, [CodingSystem] text NULL
, [Strength] text NULL
, [Description] text NULL
, [Metadata] text NULL
, [Image] text NULL
, [Url] text NULL
, [Evidence] text NULL
, [Version] text NULL
);
CREATE TABLE [AlgorithmSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [Source] nvarchar(200) NULL COLLATE NOCASE
, [Name] nvarchar(100) NULL COLLATE NOCASE
, [Reference] text NULL
, [Tag] nvarchar(50) NULL COLLATE NOCASE
, [Category] nvarchar(50) NULL COLLATE NOCASE
, [Url] nvarchar(200) NULL COLLATE NOCASE
, [Description] nvarchar(400) NULL COLLATE NOCASE
, [Metadata] text NULL
, [Code] text NULL
, [Implementation] nvarchar(30) NULL COLLATE NOCASE
, [PublishDate] date NULL
, [Timestamp] date NULL
, [InputCode] nvarchar(30) NULL COLLATE NOCASE
);
CREATE TABLE [AccountSet] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, [User] nvarchar(60) NULL COLLATE NOCASE
, [Password] nvarchar(60) NULL COLLATE NOCASE
, [Device] nvarchar(60) NULL COLLATE NOCASE
, [Role] nvarchar(60) NULL COLLATE NOCASE
);

CREATE TABLE [ReportSet] (
[Id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[Prediction] nvarchar(100)  NULL,
[Probability] nvarchar(100)  NULL,
[DecisionFunction] nvarchar(100)  NULL,
[DecisionBoundary] text  NULL,
[ReportUrl] nvarchar(200)  NULL,
[Timestamp] timestamp  NULL,
[Operator] nvarchar(100)  NULL,
[Metadata] text  NULL,
[ExternId] INTEGER NULL,
[Image] text NULL,
[Classifier] text NULL,
[Features] text NULL,
[SelectedFeatures] text NULL
);

CREATE TABLE [TextSet] (
[Id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[FilePath] nvarchar(500)  NULL,
[Digest] nvarchar(100)  NULL,
[Predict] nvarchar(100)  NULL,
[Category] smallint  NULL,
[PredictedCategory] smallint  NULL,
[Content] TEXT  NULL,
[Title] TEXT  NULL,
[Timestamp] TIMESTAMP  NULL,
[Url] TEXT  NULL,
[Metadata] TEXT  NULL,
[Label] TEXT  NULL,
[Source] TEXT  NULL
)


CREATE INDEX [SpectrumSet_IDX_EXTERN_ID] ON [SpectrumSet] ([ExternId] ASC);
CREATE INDEX [SpectrumSet_IDX_SPECTRUMSET_DIGEST] ON [SpectrumSet] ([Digest] ASC);
CREATE INDEX [SampleSet_IDX_DIGEST] ON [SampleSet] ([Digest] ASC);
CREATE UNIQUE INDEX [SampleSet_PK_SampleSet] ON [SampleSet] ([Id] ASC);
CREATE INDEX [LogSet_IDX_LOGSET_FK] ON [LogSet] ([ExternId] ASC,[Type] ASC);
CREATE INDEX [ConceptSet_IDX_CONCEPTSET_CodingSystem] ON [ConceptSet] ([CodingSystem] ASC);
COMMIT;