-- create basic tables needed by rubyrdf
-- usage: psql rdfweb1 < conf/scutterdb.sql
-- do 'drop table ...' to destroy stored data.

DROP TABLE triples;
DROP TABLE resources;
DROP VIEW simple;

CREATE TABLE "triples" (
	"subject" integer,
	"predicate" integer,
	"object" integer,
	"assertid" character varying,
	"personid" character varying,
	"isresource" boolean
);

CREATE TABLE "resources" (
	"keyhash" integer,
	"value" character varying );

CREATE INDEX sub_index ON triples USING btree (subject);
CREATE INDEX pred_index ON triples USING btree (predicate);
CREATE INDEX obj_index ON triples USING btree (object);

CREATE UNIQUE INDEX resources_key_key ON resources USING btree ("keyhash");

-- Note that DanBri and Libby's code disagree on personid vs assertid  usage.
-- Dan is using assertid for provenance tagging, typically by document  src.
CREATE UNIQUE INDEX OnePSO ON triples USING btree (predicate, subject, object, assertid);

CREATE INDEX res_key_index ON resources USING btree ("keyhash");
CREATE INDEX res_val_index ON resources USING btree (value);

CREATE VIEW simple AS SELECT r1.value AS p, r2.value AS s, r3.value AS o, r1.keyhash AS pcode, r2.keyhash AS scode, r3.keyhash AS ocode  from resources r1, resources r2, resources r3, triples t WHERE r1.keyhash=t.predicate AND r2.keyhash=t.subject AND r3.keyhash=t.object;