Song_Metadata.sql

CREATE TABLE songs (
  song_id INTEGER NOT NULL, 
  path VARCHAR(250) NOT NULL CHECK (TRIM(path) <> ''), 
  PRIMARY KEY (song_id)
);

CREATE TABLE commits (
  commit_id INTEGER NOT NULL, 
  user VARCHAR(24) NOT NULL CHECK (TRIM(user) <> ''), 
  device VARCHAR(48) NOT NULL CHECK (TRIM(device) <> ''), 
  source VARCHAR(24) NOT NULL CHECK (TRIM(source) <> ''), 
  note VARCHAR(140), 
  at DATETIME, 
  PRIMARY KEY (commit_id)
);

CREATE TABLE stats (
  stat_id INTEGER NOT NULL, 
  commit_id INTEGER, 
  song_id INTEGER, 
  rating INTEGER CHECK (rating BETWEEN 0 and 5), 
  play_count INTEGER CHECK (play_count >= 0), 
  last_played DATETIME, 
  modified DATETIME, 
  PRIMARY KEY (stat_id), 
  FOREIGN KEY(commit_id) REFERENCES commits (commit_id), 
  FOREIGN KEY(song_id) REFERENCES songs (song_id)
);

CREATE TABLE songcommits (
  commit_id INTEGER, 
  song_id INTEGER, 
  FOREIGN KEY(commit_id) REFERENCES commits (commit_id), 
  FOREIGN KEY(song_id) REFERENCES songs (song_id)
);