CREATE TABLE file (
  id         INTEGER PRIMARY KEY,
  path       TEXT,
  size       INTEGER,
  lastaccess INTEGER,  // timestamp: last fileaccess ??? need lastmodity here i think
  createdate INTEGER);

CREATE TABLE track (
  id          INTEGER PRIMARY KEY,
  file        INTEGER,  -> file.id
  insertdate  INTEGER,  // timestamp: inserted into database
  
  artist      INTEGER,  -> artist.id
  album       INTEGER,  -> album.id
  title       TEXT,
  tracknumber INTEGER,
  year        INTEGER,
  genre       INTEGER,  -> genre.id
  comment     TEXT,
  
  discnumber  INTEGER,  // ???
  albumartist TEXT,     // ???
  
  note        TEXT,
  offset      INTEGER,
  length      INTEGER,

  // better be filebased?
  bitrate     INTEGER,
  samplerate  INTEGER,
  trackgain   INTEGER,  // ???
  albumgain   INTEGER,  // ???

  flags       INTEGER); // shuffled, temporary, ...

CREATE TABLE artist (
  id   INTEGER PRIMARY KEY,
  name TEXT);

CREATE TABLE album (
  id     INTEGER PRIMARY KEY,
  name   TEXT);

CREATE TABLE genre (
  id   INTEGER PRIMARY KEY,
  name TEXT);
  
CREATE TABLE extendedtag_id3v2 (
  track       INTEGER,  -> track.id
  type        INTEGER,  // ID3v2, APE, ... 
  frame       INTEGER,  -> frame.id
  data        TEXT);
  
CREATE TABLE id3v2frame (
  id        INTEGER PRIMARY KEY,
  signature TEXT,
  name      TEXT);

CREATE TABLE statistic (
  track       INTEGER,  -> track.id
  lastplayed  INTEGER,  // timestamp: track has last _started_ playing
  playcount   INTEGER,  // counts how often this track has _started_ playing
  finishcount INTEGER,  // counts how often this track has _finished_ playing (how to handle seeking??)
  skipcount   INTEGER); // counts how often this track has _skipped_ playing

CREATE TABLE autorating (
  track INTEGER,  -> track.id
  day1  INTEGER,
  day2  INTEGER,
  day3  INTEGER,
  day4  INTEGER,
  day5  INTEGER,
  day6  INTEGER,
  day7  INTEGER,
  hour1 INTEGER,  // 00-03
  hour2 INTEGER,  // 03-06
  hour3 INTEGER,  // 06-09
  hour4 INTEGER,  // 09-12
  hour5 INTEGER,  // 12-15
  hour6 INTEGER,  // 15-18
  hour7 INTEGER,  // 18-21
  hour8 INTEGER); // 21-00

CREATE TABLE playlist (
  id       INTEGER PRIMARY KEY,
  name     TEXT,
  source   TEXT,     // query or url dependent on category. metaformat ???
  category INTEGER,  // dynamic, manual, stream
  flags    INTEGER); // temporary, ...

CREATE TABLE playlist_track (
  playlist INTEGER, -> playlist.id
  track    INTEGER, -> track.id
  position INTEGER);

CREATE TEMPORARY TABLE history (
  track    INTEGER, -> track.id
  position INTEGER);
  
CREATE TABLE keyword (
  id   INTEGER PRIMARY KEY,
  name TEXT);
  
CREATE TABLE keyword_track (
  keyword  INTEGER, -> keyword.id
  track    INTEGER, -> track.id);