db structure

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = FALSE;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: meco
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO meco;

SET search_path = public, pg_catalog;

--
-- Name: create_stats(); Type: FUNCTION; Schema: public; Owner: meco
--

CREATE FUNCTION create_stats() RETURNS void
    AS $$DECLARE
  mrow RECORD;
BEGIN
  -- count documents
  PERFORM insert_log('Total Documents', NULL,
    (SELECT COUNT(*) FROM documents));
  FOR mrow IN SELECT LANGUAGE, COUNT(*) FROM DOCUMENTS GROUP BY LANGUAGE LOOP
     PERFORM insert_log('Total Documents By Lang', mrow.LANGUAGE, mrow.COUNT);
  END LOOP;

  -- analyzed
  PERFORM insert_log('Analyzed Documents', NULL,
    (SELECT COUNT(*) FROM documents WHERE _analyzed=TRUE));

  -- medisys temporarly
  PERFORM insert_log('Medisys crawling', 'to download',
    (SELECT COUNT(*) FROM documents d WHERE
     source_id IN (1, 1677, 1676) -- from medisys
    AND id < 27240254 -- problematic sources
    AND guid !~ '.*_'
    AND NOT EXISTS ( -- and downloaded
      SELECT 1 FROM documents dd
      WHERE dd.guid=(d.guid || '_') AND dd.id > 27240254)));
  PERFORM insert_log('Medisys crawling', 'downloaded',
    (SELECT COUNT(*) FROM documents d WHERE
     source_id IN (1, 1677, 1676) -- from medisys
     AND id >= 27240254
    AND guid ~ '.*_'));
  PERFORM insert_log('Medisys crawling', 'can not be downloaded (404)',
    (SELECT COUNT(*) FROM documents d WHERE
     source_id IN (1, 1677, 1676) -- from medisys
    AND id < 27240254 -- problematic sources
    AND guid ~ '.*_'));

  -- stanford
  FOR mrow IN SELECT LANGUAGE, COUNT(*) FROM DOCUMENTS WHERE _stanford=TRUE GROUP BY LANGUAGE LOOP
     PERFORM insert_log('Stanford parser', mrow.LANGUAGE, mrow.COUNT);
  END LOOP;


  PERFORM insert_log('Affected organism', NULL,
     (SELECT COUNT(*) FROM documents WHERE _affected=TRUE));


  PERFORM insert_log('OpenCalais', NULL,
     (SELECT COUNT(*) FROM documents WHERE _calaised = TRUE AND LANGUAGE!='de'));

  PERFORM insert_log('Alchemized', NULL,
     (SELECT COUNT(*) FROM documents WHERE _calaised = TRUE AND LANGUAGE='de'));



  -- sections
  FOR mrow IN SELECT COUNT(*), LOWER(SECTION) AS SECTION FROM documents, sources
    WHERE sources.id=documents.source_id GROUP BY LOWER(SECTION) LOOP
     PERFORM insert_log('Total Documents by Sections', mrow.SECTION, mrow.COUNT);
  END LOOP;
END









$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.create_stats() OWNER TO meco;

--
-- Name: create_tsvector(character varying, character varying, text); Type: FUNCTION; Schema: public; Owner: meco
--

CREATE FUNCTION create_tsvector(LANGUAGE CHARACTER VARYING, title CHARACTER VARYING, text text) RETURNS tsvector
    AS $_$select * FROM to_tsvector(langtolong($1), COALESCE($2, '')||COALESCE($3, '')) LIMIT 1;$_$
    LANGUAGE SQL;


ALTER FUNCTION public.create_tsvector(LANGUAGE CHARACTER VARYING, title CHARACTER VARYING, text text) OWNER TO meco;

--
-- Name: FUNCTION create_tsvector(language character varying, title character varying, text text); Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON FUNCTION create_tsvector(LANGUAGE CHARACTER VARYING, title CHARACTER VARYING, text text) IS 'Example usage:
select * from create_tsvector(''en'', ''I\\''m in Prague'', ''Hello from prague!'')';


--
-- Name: insert_entity(character varying, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: meco
--

CREATE FUNCTION insert_entity(par_name CHARACTER VARYING, par_enttype_id INTEGER, par_reference CHARACTER VARYING, par_coordinates CHARACTER VARYING) RETURNS INTEGER
    AS $$DECLARE
  already_id INTEGER;
BEGIN
  -- already in db?
  IF par_coordinates IS NULL THEN
    SELECT id INTO already_id FROM entities WHERE name=par_name AND coordinates IS NULL;
  ELSE
    SELECT id INTO already_id FROM entities WHERE name=par_name AND coordinates=par_coordinates;
  END IF;

  IF already_id IS NULL THEN
    -- have to insert it
    INSERT INTO entities (name, enttype_id, reference, normalized_entity, coordinates)
           VALUES (par_name, par_enttype_id, par_reference, LOWER(btrim(par_name, E' #\n\r\t')),
                   par_coordinates)
           RETURNING id INTO already_id;
  END IF;

  RETURN already_id;
END$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.insert_entity(par_name CHARACTER VARYING, par_enttype_id INTEGER, par_reference CHARACTER VARYING, par_coordinates CHARACTER VARYING) OWNER TO meco;

--
-- Name: insert_log(character varying, character varying, bigint); Type: FUNCTION; Schema: public; Owner: meco
--

CREATE FUNCTION insert_log(type_ CHARACTER VARYING, param_ CHARACTER VARYING, value_ BIGINT) RETURNS void
    AS $_$BEGIN
    INSERT INTO logs (TIMESTAMP, TYPE, param, VALUE) VALUES (CURRENT_TIMESTAMP, $1, $2, $3);
END;$_$
    LANGUAGE plpgsql;


ALTER FUNCTION public.insert_log(type_ CHARACTER VARYING, param_ CHARACTER VARYING, value_ BIGINT) OWNER TO meco;

--
-- Name: inserting_document_f(); Type: FUNCTION; Schema: public; Owner: meco
--

CREATE FUNCTION inserting_document_f() RETURNS TRIGGER
    AS $$BEGIN
    IF NEW.TIMESTAMP IS NULL THEN
        NEW.TIMESTAMP := CURRENT_TIMESTAMP;
    END IF;
    NEW.fts := to_tsvector(langtolong(NEW.LANGUAGE), SUBSTRING(COALESCE(NEW.title, '')||COALESCE(NEW.text, ''), 1, 500000));
    RETURN NEW;
END;$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.inserting_document_f() OWNER TO meco;

--
-- Name: inserting_sources_f(); Type: FUNCTION; Schema: public; Owner: meco
--

CREATE FUNCTION inserting_sources_f() RETURNS TRIGGER
    AS $$BEGIN
IF NEW.normalized_section IS NULL THEN
    NEW.normalized_section := LOWER( NEW.SECTION );
END IF;
RETURN NEW;
END;$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.inserting_sources_f() OWNER TO meco;

--
-- Name: inserting_terms_f(character varying); Type: FUNCTION; Schema: public; Owner: meco
--

CREATE FUNCTION inserting_terms_f(_term CHARACTER VARYING) RETURNS INTEGER
    AS $$DECLARE
  already_id INTEGER;
BEGIN
  -- already in db?
  SELECT id INTO already_id FROM terms WHERE term=_term;

  IF already_id IS NULL THEN
    -- have to insert it
    INSERT INTO terms (term)
           VALUES (_term)
           RETURNING id INTO already_id;
  END IF;

  RETURN already_id;
END$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.inserting_terms_f(_term CHARACTER VARYING) OWNER TO meco;

--
-- Name: langtolong(character varying); Type: FUNCTION; Schema: public; Owner: meco
--

CREATE FUNCTION langtolong(CHARACTER VARYING) RETURNS regconfig
    AS $_$SELECT LANGUAGE FROM langs WHERE lang=$1 LIMIT 1;$_$
    LANGUAGE SQL IMMUTABLE;


ALTER FUNCTION public.langtolong(CHARACTER VARYING) OWNER TO meco;

SET default_tablespace = '';

SET default_with_oids = FALSE;

--
-- Name: authors; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE authors (
    id INTEGER NOT NULL,
    guid CHARACTER VARYING(64) NOT NULL,
    name CHARACTER VARYING(511),
    location CHARACTER VARYING(511),
    description CHARACTER VARYING(511),
    occupation CHARACTER VARYING(511),
    gps CHARACTER VARYING(32),
    gps_process BOOLEAN DEFAULT FALSE
);


ALTER TABLE public.authors OWNER TO meco;

--
-- Name: documents; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE documents (
    id INTEGER NOT NULL,
    LANGUAGE CHARACTER VARYING(2),
    source_id INTEGER,
    author_id INTEGER,
    "timestamp" TIMESTAMP WITHOUT TIME zone,
    guid CHARACTER VARYING(511),
    link CHARACTER VARYING(2047),
    title CHARACTER VARYING(2047),
    text text,
    georss_lat CHARACTER VARYING(31),
    georss_lon CHARACTER VARYING(31),
    html_description text,
    html_keywords text,
    pubdate DATE,
    pubtime TIME WITHOUT TIME zone,
    sentences text,
    termvector text,
    _calaised BOOLEAN DEFAULT FALSE,
    _analyzed BOOLEAN DEFAULT FALSE,
    _relevance INTEGER DEFAULT (-1),
    _stanford BOOLEAN,
    fts tsvector,
    _affected BOOLEAN DEFAULT FALSE,
    georss_name CHARACTER VARYING(255)
);


ALTER TABLE public.documents OWNER TO meco;

--
-- Name: COLUMN documents._calaised; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON COLUMN documents._calaised IS 'True if document was analyzed by OpenCalais.';


--
-- Name: COLUMN documents._analyzed; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON COLUMN documents._analyzed IS 'true if was analyzed by analyzer';


--
-- Name: COLUMN documents._relevance; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON COLUMN documents._relevance IS 'the implicit value is -1';


--
-- Name: COLUMN documents.fts; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON COLUMN documents.fts IS 'full text search';


--
-- Name: sources; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE sources (
    id INTEGER NOT NULL,
    link CHARACTER VARYING(2047),
    LANGUAGE CHARACTER VARYING(2),
    etag CHARACTER VARYING(63),
    modified CHARACTER VARYING(63),
    SECTION CHARACTER VARYING(200),
    _stahovak BOOLEAN DEFAULT FALSE,
    _follow BOOLEAN DEFAULT FALSE,
    normalized_section CHARACTER VARYING(200)
);


ALTER TABLE public.sources OWNER TO meco;

--
-- Name: COLUMN sources._stahovak; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON COLUMN sources._stahovak IS 'True if should be downloaded by stahovak';


--
-- Name: COLUMN sources._follow; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON COLUMN sources._follow IS 'True if needed follow links of rss.';


--
-- Name: sources_twitter; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW sources_twitter AS
    SELECT sources.id, sources.link, sources.LANGUAGE, sources.etag, sources.modified, sources.SECTION, sources._stahovak, sources._follow FROM sources WHERE ((sources.SECTION)::text = ANY ((ARRAY['TWITTER'::CHARACTER VARYING, 'Twitter'::CHARACTER VARYING, 'twitter'::CHARACTER VARYING])::text[]));


ALTER TABLE public.sources_twitter OWNER TO meco;

--
-- Name: VIEW sources_twitter; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON VIEW sources_twitter IS 'Vybere všechny sources, které náleží twitteru.';


--
-- Name: Problems; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW "Problems" AS
    (SELECT COUNT(1) AS COUNT, 'Autori bez dokumentu' AS popis FROM authors WHERE (NOT (EXISTS (SELECT 1 FROM documents WHERE (documents.author_id = authors.id)))) UNION SELECT COUNT(1) AS COUNT, 'Tweety delsi 140 znaku' AS popis FROM documents WHERE ((documents.source_id IN (SELECT sources_twitter.id FROM sources_twitter)) AND (LENGTH(documents.text) > 140))) UNION SELECT COUNT(1) AS COUNT, 'Dokumenty s exotickymi jazyky' AS popis FROM documents WHERE (langtolong(documents.LANGUAGE) IS NULL);


ALTER TABLE public."Problems" OWNER TO meco;

--
-- Name: VIEW "Problems"; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON VIEW "Problems" IS 'Detekce problému. Velmi pomalé!';


--
-- Name: StanfordProgress; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW "StanfordProgress" AS
    SELECT documents.LANGUAGE, 'Stanford successfully', COUNT(*) AS COUNT FROM documents WHERE ((documents._stanford = TRUE) AND (documents.sentences IS NOT NULL)) GROUP BY documents.LANGUAGE UNION SELECT documents.LANGUAGE, 'Analyzed by Stanford', COUNT(*) AS COUNT FROM documents WHERE (documents._stanford = TRUE) GROUP BY documents.LANGUAGE;


ALTER TABLE public."StanfordProgress" OWNER TO meco;

--
-- Name: VIEW "StanfordProgress"; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON VIEW "StanfordProgress" IS 'Progress of stanford parser.';


--
-- Name: authors_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE authors_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.authors_id_seq OWNER TO meco;

--
-- Name: authors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE authors_id_seq OWNED BY authors.id;


--
-- Name: entities; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE entities (
    id INTEGER NOT NULL,
    name CHARACTER VARYING(255),
    enttype_id INTEGER,
    reference CHARACTER VARYING(255),
    normalized_entity CHARACTER VARYING(255),
    coordinates CHARACTER VARYING(24)
);


ALTER TABLE public.entities OWNER TO meco;

--
-- Name: COLUMN entities.coordinates; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON COLUMN entities.coordinates IS 'in form latitude - longitude';


--
-- Name: enttypes; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE enttypes (
    id INTEGER NOT NULL,
    TYPE CHARACTER VARYING(255),
    reference CHARACTER VARYING(255),
    parent CHARACTER VARYING(255)
);


ALTER TABLE public.enttypes OWNER TO meco;

--
-- Name: instances; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE instances (
    id INTEGER NOT NULL,
    entity_id INTEGER,
    item_id INTEGER,
    exact CHARACTER VARYING(255),
    offset_ INTEGER,
    LENGTH INTEGER,
    sid INTEGER,
    begintoken INTEGER,
    endtoken INTEGER
);


ALTER TABLE public.instances OWNER TO meco;

--
-- Name: de_twitter_location; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW de_twitter_location AS
    SELECT DISTINCT d.id, d.text, e.name AS entity, et.TYPE AS entity_type FROM (((documents d JOIN instances i ON ((i.item_id = d.id))) JOIN entities e ON ((i.entity_id = e.id))) JOIN enttypes et ON ((e.enttype_id = et.id))) WHERE (((((d.LANGUAGE)::text = 'de'::text) AND (d._calaised = TRUE)) AND (d.source_id IN (SELECT sources_twitter.id FROM sources_twitter))) AND (et.id = ANY (ARRAY[7, 10, 11, 14, 17, 18, 49]))) ORDER BY d.id, d.text, e.name, et.TYPE;


ALTER TABLE public.de_twitter_location OWNER TO meco;

--
-- Name: VIEW de_twitter_location; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON VIEW de_twitter_location IS 'vyber všechny německé tweety a k nim entity s takovým typem, že se jedná nějakým způsobem o lokaci';


--
-- Name: de_twitter_location_with_MedicalCondition; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW "de_twitter_location_with_MedicalCondition" AS
    SELECT DISTINCT d.id, d.text AS tweet, e.name AS entity, et.TYPE AS entity_type FROM (((documents d JOIN instances i ON ((i.item_id = d.id))) JOIN entities e ON ((i.entity_id = e.id))) JOIN enttypes et ON ((e.enttype_id = et.id))) WHERE ((((((d.LANGUAGE)::text = 'de'::text) AND (d._calaised = TRUE)) AND (d.source_id IN (SELECT sources_twitter.id FROM sources_twitter))) AND (et.id = ANY (ARRAY[7, 10, 11, 14, 17, 18, 49]))) AND (EXISTS (SELECT dd.id, dd.LANGUAGE, dd.source_id, dd.author_id, dd."timestamp", dd.guid, dd.link, dd.title, dd.text, dd.georss_lat, dd.georss_lon, dd.html_description, dd.html_keywords, dd.pubdate, dd.pubtime, dd.sentences, dd.termvector, dd._calaised, dd._analyzed, dd._relevance, dd._stanford, ii.id, ii.entity_id, ii.item_id, ii.exact, ii.offset_, ii.LENGTH, ii.sid, ii.begintoken, ii.endtoken, ee.id, ee.name, ee.enttype_id, ee.reference, ee.normalized_entity, eet.id, eet.TYPE, eet.reference FROM (((documents dd JOIN instances ii ON ((ii.item_id = dd.id))) JOIN entities ee ON ((ii.entity_id = ee.id))) JOIN enttypes eet ON ((ee.enttype_id = eet.id))) WHERE ((dd.id = d.id) AND (eet.id = 13))))) ORDER BY d.id, d.text, e.name, et.TYPE;


ALTER TABLE public."de_twitter_location_with_MedicalCondition" OWNER TO meco;

--
-- Name: VIEW "de_twitter_location_with_MedicalCondition"; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON VIEW "de_twitter_location_with_MedicalCondition" IS 'Vyber všechny německé tweety s nějakou tou entitou typu různých Location. Další podmínka je výskyt entity typu 13 MedicalCondition, což znamená, že tweet je "relevantnější".';


--
-- Name: de_twitter_no_location; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW de_twitter_no_location AS
    SELECT d.id, d.text FROM documents d WHERE (((NOT (d.id IN (SELECT de_twitter_location.id FROM de_twitter_location))) AND ((d.LANGUAGE)::text = 'de'::text)) AND (d.source_id IN (SELECT sources_twitter.id FROM sources_twitter))) ORDER BY d.id;


ALTER TABLE public.de_twitter_no_location OWNER TO meco;

--
-- Name: VIEW de_twitter_no_location; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON VIEW de_twitter_no_location IS 'Zeptá se de_twitter_location na twíty s lokací a vybere všechny ostatní (tj. twíty německé bez lokace)';


--
-- Name: diseases; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE diseases (
    id INTEGER NOT NULL,
    disease_id INTEGER,
    disease CHARACTER VARYING(255),
    LANGUAGE CHARACTER VARYING(2),
    _new BOOLEAN DEFAULT TRUE
);


ALTER TABLE public.diseases OWNER TO meco;

--
-- Name: diseases_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE diseases_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.diseases_id_seq OWNER TO meco;

--
-- Name: diseases_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE diseases_id_seq OWNED BY diseases.id;


--
-- Name: documents_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE documents_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.documents_id_seq OWNER TO meco;

--
-- Name: documents_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE documents_id_seq OWNED BY documents.id;


--
-- Name: downloadurl; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE downloadurl (
    entity_id INTEGER NOT NULL,
    document_id INTEGER
);


ALTER TABLE public.downloadurl OWNER TO meco;

--
-- Name: TABLE downloadurl; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON TABLE downloadurl IS 'Download url entities';


--
-- Name: downloadurl_document_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE downloadurl_document_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.downloadurl_document_id_seq OWNER TO meco;

--
-- Name: downloadurl_document_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE downloadurl_document_id_seq OWNED BY downloadurl.document_id;


--
-- Name: downloadurl_entity_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE downloadurl_entity_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.downloadurl_entity_id_seq OWNER TO meco;

--
-- Name: downloadurl_entity_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE downloadurl_entity_id_seq OWNED BY downloadurl.entity_id;


--
-- Name: entities_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE entities_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.entities_id_seq OWNER TO meco;

--
-- Name: entities_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE entities_id_seq OWNED BY entities.id;


--
-- Name: enttypes_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE enttypes_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.enttypes_id_seq OWNER TO meco;

--
-- Name: enttypes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE enttypes_id_seq OWNED BY enttypes.id;


--
-- Name: instances_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE instances_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.instances_id_seq OWNER TO meco;

--
-- Name: instances_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE instances_id_seq OWNED BY instances.id;


--
-- Name: langs; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE langs (
    lang CHARACTER VARYING(12) NOT NULL,
    LANGUAGE regconfig
);


ALTER TABLE public.langs OWNER TO meco;

--
-- Name: TABLE langs; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON TABLE langs IS 'Stahovane jazyky. Ktere zde nejsou, budou zahozeny. K jazykove zkratce je nutno pripojit i spravny "regconfig" -- plny nazev pro FULL TEXT SEARCH.';


--
-- Name: logs; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE logs (
    "timestamp" TIMESTAMP WITHOUT TIME zone NOT NULL,
    TYPE CHARACTER VARYING(64) NOT NULL,
    VALUE BIGINT NOT NULL,
    param CHARACTER VARYING(64)
);


ALTER TABLE public.logs OWNER TO meco;

--
-- Name: logs_value_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE logs_value_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.logs_value_seq OWNER TO meco;

--
-- Name: logs_value_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE logs_value_seq OWNED BY logs.VALUE;


--
-- Name: prefered_sources; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW prefered_sources AS
    SELECT sources.id, sources.link, sources.LANGUAGE, sources.etag, sources.modified, sources.SECTION, sources._stahovak, sources._follow, sources.normalized_section FROM sources WHERE ((((sources.SECTION)::text = 'Medisys'::text) OR ((sources.SECTION)::text = 'medworm'::text)) OR (sources.id = 8793));


ALTER TABLE public.prefered_sources OWNER TO meco;

--
-- Name: pubtypes_sources; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE pubtypes_sources (
    source_id INTEGER,
    pubtype CHARACTER VARYING(32)
);


ALTER TABLE public.pubtypes_sources OWNER TO meco;

--
-- Name: sources_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE sources_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.sources_id_seq OWNER TO meco;

--
-- Name: sources_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE sources_id_seq OWNED BY sources.id;


--
-- Name: stat_count_doc_alchemized; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW stat_count_doc_alchemized AS
    SELECT COUNT(*) AS COUNT FROM documents WHERE (((documents.LANGUAGE)::text = 'de'::text) AND (documents._calaised = TRUE));


ALTER TABLE public.stat_count_doc_alchemized OWNER TO meco;

--
-- Name: VIEW stat_count_doc_alchemized; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON VIEW stat_count_doc_alchemized IS 'Počet dokumentů anotovaných AlchemyAPI.';


--
-- Name: stat_count_doc_analyzed; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW stat_count_doc_analyzed AS
    SELECT COUNT(*) AS COUNT FROM documents WHERE (documents._analyzed = TRUE);


ALTER TABLE public.stat_count_doc_analyzed OWNER TO meco;

--
-- Name: VIEW stat_count_doc_analyzed; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON VIEW stat_count_doc_analyzed IS 'Počet analyzovaných dokumentů.';


--
-- Name: stat_count_doc_per_source; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW stat_count_doc_per_source AS
    SELECT COUNT(1) AS pocet, sources.SECTION FROM documents, sources WHERE (documents.source_id = sources.id) GROUP BY sources.SECTION ORDER BY COUNT(1);


ALTER TABLE public.stat_count_doc_per_source OWNER TO meco;

--
-- Name: VIEW stat_count_doc_per_source; Type: COMMENT; Schema: public; Owner: meco
--

COMMENT ON VIEW stat_count_doc_per_source IS 'pocet dokumentů z každého fóra';


--
-- Name: stat_count_doc_stanford; Type: VIEW; Schema: public; Owner: meco
--

CREATE VIEW stat_count_doc_stanford AS
    SELECT COUNT(*) AS COUNT FROM documents WHERE (documents._stanford = TRUE);


ALTER TABLE public.stat_count_doc_stanford OWNER TO meco;

--
-- Name: symptoms; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE symptoms (
    id INTEGER NOT NULL,
    symptom CHARACTER VARYING(255),
    LANGUAGE CHARACTER VARYING(2),
    _new BOOLEAN DEFAULT TRUE
);


ALTER TABLE public.symptoms OWNER TO meco;

--
-- Name: symptoms_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE symptoms_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.symptoms_id_seq OWNER TO meco;

--
-- Name: symptoms_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE symptoms_id_seq OWNED BY symptoms.id;


--
-- Name: terms; Type: TABLE; Schema: public; Owner: meco; Tablespace:
--

CREATE TABLE terms (
    term CHARACTER VARYING(255) NOT NULL,
    id INTEGER NOT NULL
);


ALTER TABLE public.terms OWNER TO meco;

--
-- Name: term_form_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE term_form_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.term_form_seq OWNER TO meco;

--
-- Name: term_form_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE term_form_seq OWNED BY terms.term;


--
-- Name: term_number_seq; Type: SEQUENCE; Schema: public; Owner: meco
--

CREATE SEQUENCE term_number_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.term_number_seq OWNER TO meco;

--
-- Name: term_number_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
--

ALTER SEQUENCE term_number_seq OWNED BY terms.id;


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE authors ALTER COLUMN id SET DEFAULT NEXTVAL('authors_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE diseases ALTER COLUMN id SET DEFAULT NEXTVAL('diseases_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE documents ALTER COLUMN id SET DEFAULT NEXTVAL('documents_id_seq'::regclass);


--
-- Name: entity_id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE downloadurl ALTER COLUMN entity_id SET DEFAULT NEXTVAL('downloadurl_entity_id_seq'::regclass);


--
-- Name: document_id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE downloadurl ALTER COLUMN document_id SET DEFAULT NEXTVAL('downloadurl_document_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE entities ALTER COLUMN id SET DEFAULT NEXTVAL('entities_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE enttypes ALTER COLUMN id SET DEFAULT NEXTVAL('enttypes_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE instances ALTER COLUMN id SET DEFAULT NEXTVAL('instances_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE sources ALTER COLUMN id SET DEFAULT NEXTVAL('sources_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE symptoms ALTER COLUMN id SET DEFAULT NEXTVAL('symptoms_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: meco
--

ALTER TABLE terms ALTER COLUMN id SET DEFAULT NEXTVAL('term_number_seq'::regclass);


--
-- Name: authors_guid_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY authors
    ADD CONSTRAINT authors_guid_key UNIQUE (guid);


--
-- Name: authors_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY authors
    ADD CONSTRAINT authors_pkey PRIMARY KEY (id);


--
-- Name: diseases_disease_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY diseases
    ADD CONSTRAINT diseases_disease_key UNIQUE (disease);


--
-- Name: diseases_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY diseases
    ADD CONSTRAINT diseases_pkey PRIMARY KEY (id);


--
-- Name: documents_guid_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY documents
    ADD CONSTRAINT documents_guid_key UNIQUE (guid);


--
-- Name: documents_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY documents
    ADD CONSTRAINT documents_pkey PRIMARY KEY (id);


--
-- Name: entities_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY entities
    ADD CONSTRAINT entities_pkey PRIMARY KEY (id);


--
-- Name: enttypes_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY enttypes
    ADD CONSTRAINT enttypes_pkey PRIMARY KEY (id);


--
-- Name: enttypes_type_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY enttypes
    ADD CONSTRAINT enttypes_type_key UNIQUE (TYPE);


--
-- Name: instances_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY instances
    ADD CONSTRAINT instances_pkey PRIMARY KEY (id);


--
-- Name: langs_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY langs
    ADD CONSTRAINT langs_pkey PRIMARY KEY (lang);


--
-- Name: name_coords_uniq; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY entities
    ADD CONSTRAINT name_coords_uniq UNIQUE (name, coordinates);


--
-- Name: sources_link_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY sources
    ADD CONSTRAINT sources_link_key UNIQUE (link);


--
-- Name: sources_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY sources
    ADD CONSTRAINT sources_pkey PRIMARY KEY (id);


--
-- Name: symptoms_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY symptoms
    ADD CONSTRAINT symptoms_pkey PRIMARY KEY (id);


--
-- Name: terms_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY terms
    ADD CONSTRAINT terms_pkey PRIMARY KEY (id);


--
-- Name: terms_term_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY terms
    ADD CONSTRAINT terms_term_key UNIQUE (term);


--
-- Name: uniq_symptom; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
--

ALTER TABLE ONLY symptoms
    ADD CONSTRAINT uniq_symptom UNIQUE (symptom);


--
-- Name: author_id_index; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX author_id_index ON documents USING btree (author_id);


--
-- Name: documents_analyzed; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX documents_analyzed ON documents USING btree (_stanford, _calaised, _analyzed);


--
-- Name: documents_fts_index; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX documents_fts_index ON documents USING gin (fts);


--
-- Name: documents_language; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX documents_language ON documents USING btree (LANGUAGE);


--
-- Name: documents_pubdate; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX documents_pubdate ON documents USING btree (pubdate, pubtime);


--
-- Name: documents_relevance; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX documents_relevance ON documents USING btree (_relevance);


--
-- Name: entities_enttype_id; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX entities_enttype_id ON entities USING btree (enttype_id);


--
-- Name: entities_normalized_entity; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX entities_normalized_entity ON entities USING hash (normalized_entity);


--
-- Name: entity_index; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE UNIQUE INDEX entity_index ON downloadurl USING btree (entity_id);


--
-- Name: instances_entity_id; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX instances_entity_id ON instances USING btree (entity_id);


--
-- Name: instances_item_id; Type: INDEX; Schema: public; Owner: meco; Tablespace:
--

CREATE INDEX instances_item_id ON instances USING btree (item_id);


--
-- Name: inserting_document_trigger; Type: TRIGGER; Schema: public; Owner: meco
--

CREATE TRIGGER inserting_document_trigger
    BEFORE INSERT ON documents
    FOR EACH ROW
    EXECUTE PROCEDURE inserting_document_f();


--
-- Name: inserting_trigger; Type: TRIGGER; Schema: public; Owner: meco
--

CREATE TRIGGER inserting_trigger
    BEFORE INSERT ON sources
    FOR EACH ROW
    EXECUTE PROCEDURE inserting_sources_f();


--
-- Name: authors_fk; Type: FK CONSTRAINT; Schema: public; Owner: meco
--

ALTER TABLE ONLY documents
    ADD CONSTRAINT authors_fk FOREIGN KEY (author_id) REFERENCES authors(id) ON UPDATE SET NULL ON DELETE SET NULL;


--
-- Name: doc_fk; Type: FK CONSTRAINT; Schema: public; Owner: meco
--

ALTER TABLE ONLY downloadurl
    ADD CONSTRAINT doc_fk FOREIGN KEY (document_id) REFERENCES documents(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: entity_fk; Type: FK CONSTRAINT; Schema: public; Owner: meco
--

ALTER TABLE ONLY downloadurl
    ADD CONSTRAINT entity_fk FOREIGN KEY (entity_id) REFERENCES entities(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: fk_entities_enttypes; Type: FK CONSTRAINT; Schema: public; Owner: meco
--

ALTER TABLE ONLY entities
    ADD CONSTRAINT fk_entities_enttypes FOREIGN KEY (enttype_id) REFERENCES enttypes(id);


--
-- Name: fk_instances_documents; Type: FK CONSTRAINT; Schema: public; Owner: meco
--

ALTER TABLE ONLY instances
    ADD CONSTRAINT fk_instances_documents FOREIGN KEY (item_id) REFERENCES documents(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: fk_instances_entities; Type: FK CONSTRAINT; Schema: public; Owner: meco
--

ALTER TABLE ONLY instances
    ADD CONSTRAINT fk_instances_entities FOREIGN KEY (entity_id) REFERENCES entities(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: fk_pubtypes_sources_sources; Type: FK CONSTRAINT; Schema: public; Owner: meco
--

ALTER TABLE ONLY pubtypes_sources
    ADD CONSTRAINT fk_pubtypes_sources_sources FOREIGN KEY (source_id) REFERENCES sources(id);


--
-- Name: source_fk; Type: FK CONSTRAINT; Schema: public; Owner: meco
--

ALTER TABLE ONLY documents
    ADD CONSTRAINT source_fk FOREIGN KEY (source_id) REFERENCES sources(id) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- Name: public; Type: ACL; Schema: -; Owner: ischmidt
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM ischmidt;
GRANT ALL ON SCHEMA public TO ischmidt;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- Name: inserting_document_f(); Type: ACL; Schema: public; Owner: meco
--

REVOKE ALL ON FUNCTION inserting_document_f() FROM PUBLIC;
REVOKE ALL ON FUNCTION inserting_document_f() FROM meco;
GRANT ALL ON FUNCTION inserting_document_f() TO meco;
GRANT ALL ON FUNCTION inserting_document_f() TO PUBLIC;


--
-- PostgreSQL database dump complete
--