added some rudimentary install docu, renamed test.sql to create.sql and schema.sql

1 files changed, 44 insertions, 0 deletions

diff --git a/schema.sql b/schema.sql
new file mode 100644
index 0000000..d48c890
--- /dev/null
+++ b/schema.sql
@@ -0,0 +1,44 @@
+CREATE TABLE dir (
+	id SERIAL PRIMARY KEY,
+	parent_id INTEGER REFERENCES dir( id ),
+	name TEXT,
+	path TEXT,
+	isdir BOOL,
+	UNIQUE( name, parent_id ),
+	UNIQUE( path ),
+	mode INTEGER NOT NULL DEFAULT 0,
+	uid INTEGER NOT NULL DEFAULT 0,
+	gid INTEGER NOT NULL DEFAULT 0,
+	ctime TIMESTAMP WITH TIME ZONE,
+	mtime TIMESTAMP WITH TIME ZONE,
+	atime TIMESTAMP WITH TIME ZONE,
+	size INTEGER DEFAULT 0
+);
+
+CREATE TABLE data (
+	id INTEGER,
+	FOREIGN KEY( id ) REFERENCES dir( id ),
+	data BYTEA
+);
+
+-- create an index for fast data access
+CREATE INDEX data_id_idx ON data( id );
+
+-- create an index on the parent_id for
+-- directory listings
+CREATE INDEX dir_parent_id_idx ON dir( parent_id );
+
+-- make sure file entries always get a data
+-- section in the separate table
+CREATE OR REPLACE RULE "dir_insert" AS ON
+	INSERT TO dir WHERE NEW.isdir = false
+	DO ALSO INSERT INTO data( id )
+	VALUES ( currval( 'dir_id_seq' ) );
+
+-- garbage collect deleted file entries
+CREATE OR REPLACE RULE "dir_remove" AS ON
+	DELETE TO dir WHERE OLD.isdir = false
+	DO ALSO DELETE FROM data WHERE id=OLD.id;	
+	
+-- self-referencing anchor for root directory
+INSERT INTO dir values( 0, 0, '/', '/', true );