-- 
-- MySQL database dump
-- 
-- 2012-10

-- SET statement_timeout = 0;
SET interactive_timeout = 0;
-- SET client_encoding = 'UTF8';

-- SET standard_conforming_strings = off;
-- SET check_function_bodies = false;
-- SET client_min_messages = warning;
-- SET escape_string_warning = off;


CREATE DATABASE IF NOT EXISTS `kc`;
USE `kc`;

-- ALTER SCHEMA kc OWNER TO postgres;
-- SET search_path = kc, pg_catalog;

-- 
-- TOC entry 166 (class 1255 OID 25355)
-- Dependencies: 6
-- Name: renew_kc_db(); Type: FUNCTION; Schema: kc; Owner: postgres
-- 

DELIMITER $$
CREATE PROCEDURE renew_kc_db()
    LANGUAGE sql
		BEGIN
            ALTER TABLE extracted AUTO_INCREMENT=1; 
            ALTER TABLE predicate AUTO_INCREMENT=1;
            ALTER TABLE term_eq AUTO_INCREMENT=1;
			ALTER TABLE type AUTO_INCREMENT=1;
			
			DELETE FROM `document`;
			DELETE FROM `extracted`;
			DELETE FROM `predicate`;
			DELETE FROM `term_eq`;
			DELETE FROM `type`;
			DELETE FROM `kc`;
			DELETE FROM `maptable`;
			
		END$$
DELIMITER ;

-- SET default_tablespace = '';
-- SET default_with_oids = false;
-- 
-- TOC entry 141 (class 1259 OID 25356)
-- Dependencies: 1809 6
-- Name: document; Type: TABLE; Schema: kc; Owner: postgres; Tablespace:
-- 
CREATE TABLE `document` (
`url` varchar(100) NOT NULL PRIMARY KEY,
`uri` varchar(100),
`local` boolean DEFAULT true
);
-- ALTER TABLE kc.document OWNER TO postgres;

-- 
-- TOC entry 144 (class 1259 OID 25373)
-- Dependencies: 1812 1813 6
-- Name: kc; Type: TABLE; Schema: kc; Owner: postgres; Tablespace:
-- 
CREATE TABLE `kc` (
`rdf_id` varchar(50) NOT NULL PRIMARY KEY,
`name` varchar(50),
`global` boolean DEFAULT false,
`term_equipment` text,
`type` varchar(1),
CONSTRAINT kc_type_check CHECK (type='C' OR type='P' OR type='I')
);
-- ALTER TABLE kc.kc OWNER TO postgres;

-- 
-- TOC entry 143 (class 1259 OID 25365)
-- Dependencies: 1810 6
-- Name: extracted; Type: TABLE; Schema: kc; Owner: postgres; Tablespace:
-- 
CREATE TABLE `extracted` (
`document` varchar(100) NOT NULL,
`kc` varchar(50) NOT NULL,
`weight` double DEFAULT 1,
`code` int,
`note` varchar(200),
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
CONSTRAINT `extracted_unique_doc_kc` UNIQUE (`document`, `kc`),
FOREIGN KEY (`document`) REFERENCES document(`url`) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (`kc`) REFERENCES kc(`rdf_id`) ON UPDATE CASCADE ON DELETE CASCADE
);
-- ALTER TABLE kc.extracted OWNER TO postgres;


CREATE TABLE `predicate` (
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`belong_kc` varchar(50),
`p1` varchar(50),
`pn` varchar(50),
`value` varchar(100),
`path_length` int,
`value_length` int,
`value_type` varchar(50),
`value_kc` varchar(50),
`confidence` double,
`value_text` mediumtext,
FOREIGN KEY (`belong_kc`) REFERENCES kc(`rdf_id`) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (`p1`) REFERENCES kc(`rdf_id`) ON UPDATE CASCADE,
FOREIGN KEY (`pn`) REFERENCES kc(`rdf_id`) ON UPDATE CASCADE,
FOREIGN KEY (`value_kc`) REFERENCES kc(`rdf_id`) ON UPDATE CASCADE
);
-- ALTER TABLE kc.predicate OWNER TO postgres;

-- 
-- TOC entry 145 (class 1259 OID 25381)
-- Dependencies: 6
-- Name: maptable; Type: TABLE; Schema: kc; Owner: postgres; Tablespace:
-- 
CREATE TABLE `maptable` (
`global` int NOT NULL,
`simple` int NOT NULL,
CONSTRAINT `maptable_pkey` PRIMARY KEY (`global`, `simple`),
FOREIGN KEY (`global`) REFERENCES predicate(`id`) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (`simple`) REFERENCES predicate(`id`) ON UPDATE CASCADE ON DELETE CASCADE
);
-- ALTER TABLE kc.maptable OWNER TO postgres;
-- 
-- TOC entry 147 (class 1259 OID 25386)
-- Dependencies: 6
-- Name: predicate; Type: TABLE; Schema: kc; Owner: postgres; Tablespace:
-- 


-- 
-- TOC entry 149 (class 1259 OID 25395)
-- Dependencies: 1815 6
-- Name: term_eq; Type: TABLE; Schema: kc; Owner: postgres; Tablespace:
-- 
CREATE TABLE `term_eq` (
`term` varchar(50) NOT NULL,
`kc` varchar(50) NOT NULL,
`relevance` double,
`frequency` int DEFAULT 1,
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
CONSTRAINT `term_eq_term_kc_unique` UNIQUE (`term`, `kc`),
FOREIGN KEY (`kc`) REFERENCES kc(`rdf_id`) ON UPDATE CASCADE ON DELETE CASCADE
);
-- ALTER TABLE kc.term_eq OWNER TO postgres;

-- TOC entry 153 (class 1259 OID 25491)
-- Dependencies: 6
-- Name: term_form; Type: TABLE; Schema: kc; Owner: postgres; Tablespace:
-- 
CREATE TABLE `term_form` (
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`term` int,
`transf_name` varchar(100) NOT NULL,
`transf_value` varchar(50) NOT NULL,
FOREIGN KEY (`term`) REFERENCES term_eq(`id`) ON UPDATE CASCADE ON DELETE CASCADE
);
-- ALTER TABLE kc.term_form OWNER TO postgres;

-- TOC entry 151 (class 1259 OID 25405)
-- Dependencies: 1817 6
-- Name: type; Type: TABLE; Schema: kc; Owner: postgres; Tablespace:
-- 
CREATE TABLE `type` (
`class` varchar(50) NOT NULL,
`instance` varchar(50) NOT NULL,
`is_type` boolean DEFAULT true,
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
CONSTRAINT `type_class_instance_unique` UNIQUE (`class`, `instance`),
FOREIGN KEY (`class`) REFERENCES kc(`rdf_id`) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (`instance`) REFERENCES kc(`rdf_id`) ON UPDATE CASCADE ON DELETE CASCADE
);
-- ALTER TABLE kc.type OWNER TO postgres;


-- TOC entry 1836 (class 1259 OID 25433)
-- Dependencies: 151 151
-- Name: class_instance_index; Type: INDEX; Schema: kc; Owner: postgres; Tablespace:
-- 
CREATE INDEX class_instance_index ON type (`class`, `instance`) USING BTREE;
 
 
-- 
-- MySQL database dump complete
-- 