Welcome to the CHICKEN Scheme pasting service

-- Projects have documents that have text fragments that can receive tags
-- Both projects and documents have authors

CREATE TABLE authors (
       authorID INTEGER PRIMARY KEY,
       name TEXT
);

CREATE TABLE projects (
       projectID INTEGER PRIMARY KEY,
       title TEXT NOT NULL,
       description TEXT,
       creation TEXT,
       modification TEXT
);

CREATE TABLE documents (
       documentID INTEGER PRIMARY KEY,
       title TEXT NOT NULL,
       journal TEXT,
       book TEXT,
       edition INTEGER,
       volume INTEGER,
       issue INTEGER,
       pages INTEGER,
       editor TEXT,
       year INTEGER,       
       city TEXT,
       url TEXT,
       doi TEXT,
       isbn TEXT,
       creation TEXT,
       modification TEXT
);

CREATE TABLE fragments (
       fragmentID INTEGER PRIMARY KEY,
       content TEXT,
       description TEXT,
       position INTEGER,
       creation TEXT,
       modification TEXT 
);

CREATE TABLE tags (
       tagID INTEGER PRIMARY KEY,
       type TEXT,
       content TEXT,
       description TEXT,
       offset INTEGER, -- number of characters from fragment start
       length INTEGER,
       creation TEXT,
       modification TEXT 
);

CREATE TABLE project_authors (
       projectID INTEGER NOT NULL,
       authorID INTEGER NOT NULL
);

CREATE TABLE project_documents (
       projectID INTEGER NOT NULL,
       documentID INTEGER NOT NULL
);

CREATE TABLE document_authors (
       documentID INTEGER NOT NULL,
       authorID INTEGER NOT NULL
);

CREATE TABLE document_fragments (
       documentID INTEGER NOT NULL,
       fragmentID INTEGER NOT NULL
);

CREATE TABLE fragment_tags (
       fragmentID INTEGER NOT NULL,
       tagID INTEGER NOT NULL
);