<commit_before>/*Note to future maintainer: this DB isn't normalized very well...*/

/*
 This app uses a relational database with this schema to store users,
 user groups, analyses, 

 Workflows and modules are read from yml files
 Collection and sample attributes are read from hdf5 files
 
 SQLite has built in primary keys, (rowid)
 */

 /*
 TODO: date modified in schema with triggers
 */
create table if not exists Users (
    id integer primary key,
    email text not null unique,
    name text,
    password text not null,
    admin integer not null
);

/*
 Multiple collections can be attached to an Analysis
 these analyses are stored in the hdf5 file of the collection as a list
 in a veritable Gertrude Stein's Muenster of properly normalized SQL db
 and poorly-implemented homebrew document-oriented db
 */
create table if not exists Analyses (
    id integer primary key,
    name text,
    description text,
    createdBy integer,
    owner integer,
    groupPermissions text,
    allPermissions text,
    userGroup integer
);

/*
 This table contains group id's and metadata 
 */
create table if not exists UserGroups(
    id integer primary key,
    createdBy integer, /* keep in mind that any group admin can delete group */
    name text,
    description text
);

/*
 Workflows are constructed from workflow modules and defined by yaml files. This is 
 just used to keep track of them
 */
create table if not exists Workflows(
    id integer primary key,
    name text,
    description text,
    owner text,
    createdBy integer,
    userGroup integer,
    groupPermissions text,
    allPermissions text
);

create table if not exists JobServerTokens(
    id integer primary key,
    value text
);

create table if not exists Invitations(
    id integer primary key,
    createdBy integer,
    value text
);
/*
 This table contains a mapping of user ids to groups
 */
create table if not exists GroupMemberships(
    userId integer,
    groupId integer,
    groupAdmin integer, /* Whether user is admin of this group */
    unique(userId, groupId)
);

/*
 Maps analysis ids to groups
 */
create table if not exists AnalysisMemberships(
    analysisId integer,
    groupId integer,
    unique(analysisId, groupId)
);

/*
 Maps workflows to analyses
 */
create table if not exists WorkflowMemberships(
    workflowId integer,
    analysisId integer,
    unique(workflowId, analysisId)
);

/*
 Maps collections to analyses
*/
create table if not exists CollectionMemberships(
    collectionId integer,
    analysisId integer,
    unique(collectionId, analysisId)
);
<commit_msg>Addition of SampleGroup abstraction to db<commit_after>/*Note to future maintainer: this DB isn't normalized very well...*/

/*
 This app uses a relational database with this schema to store users,
 user groups, analyses, 

 Workflows and modules are read from yml files
 Collection and sample attributes are read from hdf5 files
 
 SQLite has built in primary keys, (rowid)
 */

 /*
 TODO: date modified in schema with triggers
 */
create table if not exists Users (
    id integer primary key,
    email text not null unique,
    name text,
    password text not null,
    admin integer not null
);

/*
 Multiple collections can be attached to an Analysis
 these analyses are stored in the hdf5 file of the collection as a list
 in a veritable Gertrude Stein's Muenster of properly normalized SQL db
 and poorly-implemented homebrew document-oriented db
 */
create table if not exists Analyses (
    id integer primary key,
    name text,
    description text,
    createdBy integer,
    owner integer,
    groupPermissions text,
    allPermissions text,
    userGroup integer
);

/*
  Samples can be grouped while remaining distinct. A collection is a concatenation/merging
  of samples following a particular order.
 */
create table if not exists SampleGroups(
    id integer primary key,
    name text,
    description text,
    createdBy integer,
    owner integer,
    groupPermissions text,
    allPermissions text,
    userGroup integer
);

/*
 This table contains group id's and metadata 
 */
create table if not exists UserGroups(
    id integer primary key,
    createdBy integer, /* keep in mind that any group admin can delete group */
    name text,
    description text
);

/*
 Workflows are constructed from workflow modules and defined by yaml files. This is 
 just used to keep track of them
 */
create table if not exists Workflows(
    id integer primary key,
    name text,
    description text,
    owner text,
    createdBy integer,
    userGroup integer,
    groupPermissions text,
    allPermissions text
);

create table if not exists JobServerTokens(
    id integer primary key,
    value text
);

create table if not exists Invitations(
    id integer primary key,
    createdBy integer,
    value text
);
/*
 This table contains a mapping of user ids to groups
 */
create table if not exists GroupMemberships(
    userId integer,
    groupId integer,
    groupAdmin integer, /* Whether user is admin of this group */
    unique(userId, groupId)
);

/*
 Maps analysis ids to groups
 */
create table if not exists AnalysisMemberships(
    analysisId integer,
    groupId integer,
    unique(analysisId, groupId)
);

/*
 Maps workflows to analyses
 */
create table if not exists WorkflowMemberships(
    workflowId integer,
    analysisId integer,
    unique(workflowId, analysisId)
);

/*
 Maps collections to analyses
*/
create table if not exists CollectionMemberships(
    collectionId integer,
    analysisId integer,
    unique(collectionId, analysisId)
);

create table if not exists SampleGroupMemberships(
    sampleId integer,
    sampleGroupId integer,
    unique(sampleId, sampleGroupId)
);
{'fraction_non_alphanumeric': 0.05433263452131377, 'fraction_numerical': 0.0006988120195667365, 'mean_word_length': 3.3142426525998494, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1967608', 'n_tokens_mistral': 1512, 'n_tokens_neox': 1458, 'n_words': 711}