-- IF YOU UPDATE THIS FILE, DON'T FORGET TO INCREMENT THE Schema.VERSION!
-- This are basic tables that must be created prior
-- to creation of tables containing elements.

-- Container path table (see ContainerDao)
CREATE TABLE IF NOT EXISTS CONTAINERS(
	ID INT AUTO_INCREMENT PRIMARY KEY,
	PATH VARCHAR NOT NULL UNIQUE
);

-- Files table (see FileDao)
CREATE TABLE IF NOT EXISTS FILES(
	ID INT AUTO_INCREMENT PRIMARY KEY,
	PATH VARCHAR NOT NULL,
	TIMESTAMP BIGINT NOT NULL,
	CONTAINER_ID INT NOT NULL,
	FOREIGN KEY(CONTAINER_ID) REFERENCES CONTAINERS(ID) ON UPDATE CASCADE ON DELETE CASCADE,
);

CREATE INDEX IF NOT EXISTS IDX_FILES_PATH_CONTAINER ON FILES(PATH, CONTAINER_ID);
CREATE INDEX IF NOT EXISTS IDX_FILES_CONTAINER_ID ON FILES(CONTAINER_ID);
CREATE INDEX IF NOT EXISTS IDX_FILES_PATH ON FILES(PATH);