-- Locatefs db schema
-- version 0.99

create table tag (tagkey INTEGER NOT NULL,
	tag TEXT NOT NULL UNIQUE, 
	eqClass INTEGER REFERENCES tag (tagkey) DEFAULT -1,
	PRIMARY KEY (tagkey));

create table volume (volumekey INTEGER,
	name TEXT NOT NULL UNIQUE,
	prefix TEXT,
	type INTEGER REFERENCES volumeType (typekey) DEFAULT 1, -- 1 is Unknow
	timeEnter DATE,
	PRIMARY KEY (volumekey));

create table volumeType (typekey INTEGER NOT NULL,
	name TEXT UNIQUE,
	PRIMARY KEY (typekey));

create table dir (dirkey INTEGER,
-- missing here is rights, owner, group, atime, mtime, ctime...
	size INT DEFAULT -1,
	name TEXT NOT NULL,
	lft INTEGER DEFAULT -1,
	rgt INTEGER DEFAULT -1,
	volumekey INTEGER REFERENCES volume (volumekey),
	PRIMARY KEY (dirkey));

create index idirvk on dir (volumekey);

create table childof (dirkey INTEGER REFERENCES dir (dirkey),
	parent INTEGER REFERENCES dir (dirkey),
	PRIMARY KEY (dirkey,parent));

create table subdirof (dirkey INTEGER REFERENCES dir (dirkey),
	parent, INTEGER REFERENCES dir (dirkey),
	PRIMARY KEY (dirkey,parent));

create index ichildofd on childof (dirkey);
create index ichildofp on childof (parent);

create table file (filekey INTEGER,
-- missing here is rights, owner, group, atime, mtime, ctime...
	size INT,
	name TEXT NOT NULL,
	dirkey INTEGER REFERENCES dir (dirkey),
	UNIQUE (name,dirkey),
	PRIMARY KEY (filekey));

create index ifiledk on file (dirkey);

create table fileCksum (filekey INTEGER REFERENCES file (filekey),
	cksum INT NOT NULL,
	PRIMARY KEY (filekey));


insert into volumeType (name) values ('Unknown');
insert into volumeType (name) values ('HD');
insert into volumeType (name) values ('NFS');
insert into volumeType (name) values ('Network Mount');
insert into volumeType (name) values ('Floppy');
insert into volumeType (name) values ('Zip100');
insert into volumeType (name) values ('Cd');
insert into volumeType (name) values ('Cd-Rom');
insert into volumeType (name) values ('Cd-R');
insert into volumeType (name) values ('Cd-RW');
insert into volumeType (name) values ('Dvd');
insert into volumeType (name) values ('USB');
insert into volumeType (name) values ('USB Stick');
insert into volumeType (name) values ('USB Camera');
insert into volumeType (name) values ('Ipod');