HSQL Database sample

DROP TABLE ALBUM IF EXISTS CASCADE;
DROP TABLE ALBUM_ARTISTS IF EXISTS CASCADE;
DROP TABLE ALBUM_COMMENTS IF EXISTS CASCADE;
DROP TABLE ALBUM_TRACKS IF EXISTS CASCADE;
DROP TABLE ARTIST IF EXISTS CASCADE;
DROP TABLE TRACK IF EXISTS CASCADE;
DROP TABLE TRACK_ARTISTS IF EXISTS CASCADE;
DROP TABLE TRACK_COMMENTS IF EXISTS CASCADE;
CREATE TABLE ALBUM (
  ALBUM_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
  TITLE VARCHAR(255) NOT NULL,
  NUMDISCS INTEGER, ADDED DATE
);
CREATE INDEX ALBUM_TITLE ON ALBUM(TITLE);
CREATE TABLE ALBUM_ARTISTS (
  ALBUM_ID INTEGER NOT NULL,
  ARTIST_ID INTEGER NOT NULL,
  PRIMARY KEY(ALBUM_ID,ARTIST_ID),
  CONSTRAINT ALBUM_ARTISTS_FK_ALBUM_ID FOREIGN KEY(ALBUM_ID) REFERENCES ALBUM(ALBUM_ID)
);
CREATE TABLE ALBUM_COMMENTS (
  ALBUM_ID INTEGER NOT NULL,
  COMMENT VARCHAR(255),
  CONSTRAINT ALBUM_COMMENTS_FK_ALBUM_ID FOREIGN KEY(ALBUM_ID) REFERENCES ALBUM(ALBUM_ID) );
CREATE TABLE ALBUM_TRACKS (
  ALBUM_ID INTEGER NOT NULL,
  TRACK_ID INTEGER,
  DISC INTEGER, POSITIONONDISC INTEGER,
  LIST_POS INTEGER NOT NULL,
  PRIMARY KEY(ALBUM_ID,LIST_POS),
  CONSTRAINT ALBUM_TRACKS_FK_ALBUM_ID FOREIGN KEY(ALBUM_ID) REFERENCES ALBUM(ALBUM_ID)
);
CREATE TABLE ARTIST (
  ARTIST_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
  NAME VARCHAR(255) NOT NULL,
  ACTUALARTIST INTEGER,
  CONSTRAINT ARTIST_UQ_NAME UNIQUE(NAME),
  CONSTRAINT ARTIST_FK_ARTIST_ID FOREIGN KEY(ACTUALARTIST) REFERENCES ARTIST(ARTIST_ID)
);
CREATE INDEX ARTIST_NAME ON ARTIST(NAME);
CREATE TABLE TRACK (
  TRACK_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
  TITLE VARCHAR(255) NOT NULL,
  FILEPATH VARCHAR(255) NOT NULL,
  PLAYTIME TIME,
  ADDED DATE,
  VOLUME SMALLINT NOT NULL
);
CREATE INDEX TRACK_TITLE ON TRACK(TITLE);
CREATE TABLE TRACK_ARTISTS (
  TRACK_ID INTEGER NOT NULL,
  ARTIST_ID INTEGER NOT NULL,
  PRIMARY KEY(TRACK_ID,ARTIST_ID),
  CONSTRAINT TRACK_ARTISTS_FK_ARTIST_ID FOREIGN KEY(ARTIST_ID) REFERENCES ARTIST(ARTIST_ID),
  CONSTRAINT TRACK_ARTISTS_FK_TRACK_ID FOREIGN KEY(TRACK_ID) REFERENCES TRACK(TRACK_ID)
);
CREATE TABLE TRACK_COMMENTS (
  TRACK_ID INTEGER NOT NULL,
  COMMENT VARCHAR(255),
  CONSTRAINT TRACK_COMMENTS_FK_TRACK_ID FOREIGN KEY(TRACK_ID) REFERENCES TRACK(TRACK_ID)
);
ALTER TABLE ALBUM_ARTISTS ADD CONSTRAINT ALBUM_ARTIST_FK_ARTIST_ID FOREIGN KEY(ARTIST_ID) REFERENCES ARTIST(ARTIST_ID);
ALTER TABLE ALBUM_TRACKS ADD CONSTRAINT ALBUM_TRACKS_FK_TRACK_ID FOREIGN KEY(TRACK_ID) REFERENCES TRACK(TRACK_ID);
ALTER TABLE ALBUM ALTER COLUMN ALBUM_ID RESTART WITH 1; ALTER TABLE ARTIST ALTER COLUMN ARTIST_ID RESTART WITH 1;
ALTER TABLE TRACK ALTER COLUMN TRACK_ID RESTART WITH 1;