<commit_before>-- drop all tables
DROP TABLE IF EXISTS production;
DROP TABLE IF EXISTS state;
DROP TABLE IF EXISTS user_role;
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS role;

-- State of a production
-- a classic reference table
CREATE TABLE state (
  id VARCHAR(16) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  next_state VARCHAR(16),
  FOREIGN KEY(next_state) REFERENCES state(id)
);

-- Meta data for a production
-- see http://www.daisy.org/z3986/2005/Z3986-2005.html#PubMed
CREATE TABLE production (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  creator VARCHAR(255),
  subject TEXT,
  description TEXT,
  publisher VARCHAR(255) NOT NULL DEFAULT "Swiss Library for the Blind, Visually Impaired and Print Disabled",
  date DATE NOT NULL,
  type VARCHAR(255),
  format VARCHAR(255),
  identifier VARCHAR(255) NOT NULL UNIQUE,
  source VARCHAR(255),
  language VARCHAR(10) NOT NULL DEFAULT "de",
  rights VARCHAR(64),
  source_date DATE,
  source_edition VARCHAR(255),
  source_publisher VARCHAR(255),
  source_rights VARCHAR(255),
  multimedia_type VARCHAR(25),
  multimedia_content VARCHAR(255),
  narrator VARCHAR(255),
  producer VARCHAR(255),
  produced_date DATE,
  revision VARCHAR(255),
  revision_date DATE,
  revision_description VARCHAR(255),
  total_time VARCHAR(255),
  audio_format VARCHAR(25),
  -- Number of levels
  depth TINYINT,
  -- SBS specific columns
  -- Number of volumes
  volumes TINYINT,
  -- when splitting a production manually we want to specify a
  -- specific sampling_rate and bitrate
  sampling_rate FLOAT,
  bit_rate INTEGER,
  -- initial state is "new"
  state VARCHAR(16) NOT NULL DEFAULT "new",
  -- production number given by the erp system. We should really use
  -- this as the primary key but alas some productions are done
  -- without involving the erp system, so we need to keep our own
  -- primary key
  product_number VARCHAR(255) UNIQUE,
  -- provisional number given to the production by the library system.
  -- There is only a libraryNumber if there is no productNumber, i.e.
  -- if this production is done behind the back of the erp system
  library_number VARCHAR(255) UNIQUE,
  -- The unique id that the library assigns to this production
  library_signature VARCHAR(255) UNIQUE,
  FOREIGN KEY(state) REFERENCES state(id)
);

CREATE TABLE user (
  id VARCHAR(32) PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL
);

CREATE TABLE role (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(255)
);
<commit_msg>sampling rate and bitrate shouldn't go to the db<commit_after>-- drop all tables
DROP TABLE IF EXISTS production;
DROP TABLE IF EXISTS state;
DROP TABLE IF EXISTS user_role;
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS role;

-- State of a production
-- a classic reference table
CREATE TABLE state (
  id VARCHAR(16) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  next_state VARCHAR(16),
  FOREIGN KEY(next_state) REFERENCES state(id)
);

-- Meta data for a production
-- see http://www.daisy.org/z3986/2005/Z3986-2005.html#PubMed
CREATE TABLE production (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  creator VARCHAR(255),
  subject TEXT,
  description TEXT,
  publisher VARCHAR(255) NOT NULL DEFAULT "Swiss Library for the Blind, Visually Impaired and Print Disabled",
  date DATE NOT NULL,
  type VARCHAR(255),
  format VARCHAR(255),
  identifier VARCHAR(255) NOT NULL UNIQUE,
  source VARCHAR(255),
  language VARCHAR(10) NOT NULL DEFAULT "de",
  rights VARCHAR(64),
  source_date DATE,
  source_edition VARCHAR(255),
  source_publisher VARCHAR(255),
  source_rights VARCHAR(255),
  multimedia_type VARCHAR(25),
  multimedia_content VARCHAR(255),
  narrator VARCHAR(255),
  producer VARCHAR(255),
  produced_date DATE,
  revision VARCHAR(255),
  revision_date DATE,
  revision_description VARCHAR(255),
  total_time VARCHAR(255),
  audio_format VARCHAR(25),
  -- Number of levels
  depth TINYINT,
  -- SBS specific columns
  -- Number of volumes
  volumes TINYINT,
  -- initial state is "new"
  state VARCHAR(16) NOT NULL DEFAULT "new",
  -- production number given by the erp system. We should really use
  -- this as the primary key but alas some productions are done
  -- without involving the erp system, so we need to keep our own
  -- primary key
  product_number VARCHAR(255) UNIQUE,
  -- provisional number given to the production by the library system.
  -- There is only a libraryNumber if there is no productNumber, i.e.
  -- if this production is done behind the back of the erp system
  library_number VARCHAR(255) UNIQUE,
  -- The unique id that the library assigns to this production
  library_signature VARCHAR(255) UNIQUE,
  FOREIGN KEY(state) REFERENCES state(id)
);

CREATE TABLE user (
  id VARCHAR(32) PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL
);

CREATE TABLE role (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(255)
);

{'fraction_non_alphanumeric': 0.07898365176285208, 'fraction_numerical': 0.04293874335237345, 'mean_word_length': 4.062811565304088, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, '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': '1112015', 'n_tokens_mistral': 1778, 'n_tokens_neox': 1535, 'n_words': 610}