--
-- 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 `map`;
USE `map`;


-- SET search_path = map, pg_catalog;


--
-- TOC entry 175 (class 1255 OID 20413)
-- Dependencies: 6
-- Name: renew_map_db(); Type: FUNCTION; Schema: map; Owner: -
--

DELIMITER $$
CREATE PROCEDURE renew_map_db()
    LANGUAGE sql
		BEGIN
            ALTER TABLE configuration AUTO_INCREMENT=1; 
            ALTER TABLE mapping AUTO_INCREMENT=1;
            ALTER TABLE mapping_set AUTO_INCREMENT=1;
			
			DELETE FROM `mapping`;
			DELETE FROM `mapping_set`;
			DELETE FROM `configuration`;
			DELETE FROM `hmatch_user`;
			DELETE FROM `relation`;
			
		END$$
DELIMITER ;


--
-- TOC entry 153 (class 1259 OID 20414)
-- Dependencies: 6
-- Name: configuration_id_seq; Type: SEQUENCE; Schema: map; Owner: -
--

-- SET default_tablespace = '';
-- SET default_with_oids = false;

--
-- TOC entry 154 (class 1259 OID 20416)
-- Dependencies: 1836 6
-- Name: configuration; Type: TABLE; Schema: map; Owner: -; Tablespace: 
--

CREATE TABLE `configuration` (
    `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`matcher` varchar(50),
    `map_cardinality` char(3),
    `l_treshold` double,
    `l_use_wn` boolean,
    `l_wn_metric` varchar(50),
    `l_use_stringmatch` boolean,
    `l_string_metric` varchar(50),
    `c_treshold` double,
    `c_l_weight` double,
    `c_inheritance` boolean,
    `c_match_model` varchar(50),
    `c_empty_context_strategy` varchar(50),
    `c_match_strategy` varchar(50),
    `i_treshold` double,
    `i_use_reasoner` boolean,
    `i_reasoner` varchar(50),
    `i_maximize_undef_weights_prop` boolean,
    `i_use_match_selector` boolean
);


--
-- TOC entry 155 (class 1259 OID 20423)
-- Dependencies: 6
-- Name: hmatch_user; Type: TABLE; Schema: map; Owner: -; Tablespace: 
--

CREATE TABLE `hmatch_user` (
    `username` varchar(16) NOT NULL PRIMARY KEY,
    `password` varchar(32),
    `name` varchar(50),
    `default_configuration` int, 
	FOREIGN KEY (`default_configuration`) REFERENCES configuration(`id`) ON UPDATE CASCADE
);



--
-- TOC entry 159 (class 1259 OID 20442)
-- Dependencies: 1840 1841 6 483
-- Name: mapping_set; Type: TABLE; Schema: map; Owner: -; Tablespace: 
--

CREATE TABLE `mapping_set` (
    `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`source` varchar(100) NOT NULL,
    `target` varchar(100) NOT NULL,
    `source_connectionstring` varchar(200),
    `target_connectionstring` varchar(200),
    -- `creation_timestamp` timestamp without time zone DEFAULT now() NOT NULL,
	`creation_timestamp` timestamp DEFAULT now() NOT NULL,
    `comment` varchar(200),
	`type` varchar(30),
    `affinity` double,
    `hmatch_user` varchar(16),
    `configuration` int,
	CONSTRAINT `map_set_type_domain_check` CHECK (type='document' OR type='knowledge_chunk' OR type='mapping_set' OR type='property'),
	FOREIGN KEY (`configuration`) REFERENCES configuration(`id`) ON UPDATE CASCADE,
	FOREIGN KEY (`hmatch_user`) REFERENCES hmatch_user(`username`) ON UPDATE CASCADE
);


--
-- TOC entry 160 (class 1259 OID 20450)
-- Dependencies: 6
-- Name: relation; Type: TABLE; Schema: map; Owner: -; Tablespace: 
--

CREATE TABLE `relation` (
    `symbol` varchar(100) NOT NULL PRIMARY KEY,
    `description` varchar(200)
);



--
-- TOC entry 157 (class 1259 OID 20431)
-- Dependencies: 1837 1838 1839 6
-- Name: mapping; Type: TABLE; Schema: map; Owner: -; Tablespace: 
--

CREATE TABLE `mapping` (
    `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	`source_element` varchar(100) NOT NULL,
    `target_element` varchar(100) NOT NULL,
    -- relation character varying DEFAULT '='::character varying,
	`relation` varchar(100) DEFAULT '=',
    `comment` varchar(200),
    `similarity` double NOT NULL,
    `confidence` double,
    `is_user_valid` boolean DEFAULT false,
    `belongs` int,
    `has_matching_axioms` int,
	FOREIGN KEY (`belongs`) REFERENCES mapping_set(`id`) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (`has_matching_axioms`) REFERENCES mapping_set(`id`) ON UPDATE CASCADE ON DELETE SET NULL,
	FOREIGN KEY (`relation`) REFERENCES relation(`symbol`)
);




--
-- MySQL database dump complete
--

