API tools faq
paste
Advertisement
vojtarek

db schema

vojtarek
Oct 24th, 2011
399
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 34.99 KB | None | 0 0
raw download clone embed print report
  1. --
  2. -- PostgreSQL database dump
  3. --
  4.  
  5. SET client_encoding = 'UTF8';
  6. SET standard_conforming_strings = off;
  7. SET check_function_bodies = FALSE;
  8. SET client_min_messages = warning;
  9. SET escape_string_warning = off;
  10.  
  11. --
  12. -- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: meco
  13. --
  14.  
  15. CREATE PROCEDURAL LANGUAGE plpgsql;
  16.  
  17.  
  18. ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO meco;
  19.  
  20. SET search_path = public, pg_catalog;
  21.  
  22. --
  23. -- Name: create_stats(); Type: FUNCTION; Schema: public; Owner: meco
  24. --
  25.  
  26. CREATE FUNCTION create_stats() RETURNS void
  27.     AS $$DECLARE
  28.   mrow RECORD;
  29. BEGIN
  30.   -- count documents
  31.   PERFORM insert_log('Total Documents', NULL,
  32.     (SELECT COUNT(*) FROM documents));
  33.   FOR mrow IN SELECT LANGUAGE, COUNT(*) FROM DOCUMENTS GROUP BY LANGUAGE LOOP
  34.      PERFORM insert_log('Total Documents By Lang', mrow.LANGUAGE, mrow.COUNT);
  35.   END LOOP;
  36.  
  37.   -- analyzed
  38.   PERFORM insert_log('Analyzed Documents', NULL,
  39.     (SELECT COUNT(*) FROM documents WHERE _analyzed=TRUE));
  40.  
  41.   -- medisys temporarly
  42.   PERFORM insert_log('Medisys crawling', 'to download may-june',
  43.     (SELECT COUNT(*) FROM documents d WHERE
  44.      source_id IN (1, 1677, 1676) -- from medisys
  45.     AND id < 27240254 -- problematic sources
  46.     AND guid !~ '.*_'
  47.     AND pubDate BETWEEN '1.5.2011' AND '30.6.2011'
  48.     AND NOT EXISTS ( -- and downloaded
  49.       SELECT 1 FROM documents dd
  50.       WHERE dd.guid=(d.guid || '_') AND dd.id > 27240254)));
  51.   PERFORM insert_log('Medisys crawling', 'to download',
  52.     (SELECT COUNT(*) FROM documents d WHERE
  53.      source_id IN (1, 1677, 1676) -- from medisys
  54.     AND id < 27240254 -- problematic sources
  55.     AND guid !~ '.*_'
  56.     AND NOT EXISTS ( -- and downloaded
  57.       SELECT 1 FROM documents dd
  58.       WHERE dd.guid=(d.guid || '_') AND dd.id > 27240254)));
  59.   PERFORM insert_log('Medisys crawling', 'downloaded',
  60.     (SELECT COUNT(*) FROM documents d WHERE
  61.      source_id IN (1, 1677, 1676) -- from medisys
  62.      AND id >= 27240254
  63.     AND guid ~ '.*_'));
  64.   PERFORM insert_log('Medisys crawling', 'can not be downloaded (404)',
  65.     (SELECT COUNT(*) FROM documents d WHERE
  66.      source_id IN (1, 1677, 1676) -- from medisys
  67.     AND id < 27240254 -- problematic sources
  68.     AND guid ~ '.*_'));
  69.  
  70.   -- stanford
  71.   FOR mrow IN SELECT LANGUAGE, COUNT(*) FROM DOCUMENTS WHERE _stanford=TRUE GROUP BY LANGUAGE LOOP
  72.      PERFORM insert_log('Stanford parser', mrow.LANGUAGE, mrow.COUNT);
  73.   END LOOP;
  74.  
  75.  
  76.   PERFORM insert_log('Affected organism', NULL,
  77.      (SELECT COUNT(*) FROM documents WHERE _affected=TRUE));
  78.  
  79.  
  80.   PERFORM insert_log('OpenCalais', NULL,
  81.      (SELECT COUNT(*) FROM documents WHERE _calaised = TRUE AND LANGUAGE!='de'));
  82.  
  83.   PERFORM insert_log('Alchemized', NULL,
  84.      (SELECT COUNT(*) FROM documents WHERE _calaised = TRUE AND LANGUAGE='de'));
  85.  
  86.  
  87.   -- sources by categories
  88.   FOR mrow IN SELECT COUNT(*), LOWER(sources.category) AS category_ FROM documents, sources
  89.     WHERE sources.id=documents.source_id GROUP BY LOWER(sources.category) LOOP
  90.      PERFORM insert_log('Total Documents by Categories', mrow.category_, mrow.COUNT);
  91.   END LOOP;
  92.  
  93.   -- sections
  94.   FOR mrow IN SELECT COUNT(*), LOWER(SECTION) AS SECTION FROM documents, sources
  95.     WHERE sources.id=documents.source_id GROUP BY LOWER(SECTION) LOOP
  96.      PERFORM insert_log('Total Documents by Sections', mrow.SECTION, mrow.COUNT);
  97.   END LOOP;
  98.  
  99.  
  100.   -- remove old entries
  101.   DELETE FROM logs WHERE TIMESTAMP < (NOW() - INTERVAL '1 year');
  102. END
  103.  
  104.  
  105.  
  106.  
  107.  
  108.  
  109.  
  110.  
  111.  
  112. $$
  113.     LANGUAGE plpgsql;
  114.  
  115.  
  116. ALTER FUNCTION public.create_stats() OWNER TO meco;
  117.  
  118. --
  119. -- Name: create_tsvector(character varying, character varying, text); Type: FUNCTION; Schema: public; Owner: meco
  120. --
  121.  
  122. CREATE FUNCTION create_tsvector(LANGUAGE CHARACTER VARYING, title CHARACTER VARYING, text text) RETURNS tsvector
  123.     AS $_$select * FROM to_tsvector(langtolong($1), COALESCE($2, '')||COALESCE($3, '')) LIMIT 1;$_$
  124.     LANGUAGE SQL;
  125.  
  126.  
  127. ALTER FUNCTION public.create_tsvector(LANGUAGE CHARACTER VARYING, title CHARACTER VARYING, text text) OWNER TO meco;
  128.  
  129. --
  130. -- Name: FUNCTION create_tsvector(language character varying, title character varying, text text); Type: COMMENT; Schema: public; Owner: meco
  131. --
  132.  
  133. COMMENT ON FUNCTION create_tsvector(LANGUAGE CHARACTER VARYING, title CHARACTER VARYING, text text) IS 'Example usage:
  134. select * from create_tsvector(''en'', ''I\\''m in Prague'', ''Hello from prague!'')';
  135.  
  136.  
  137. --
  138. -- Name: insert_entity(character varying, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: meco
  139. --
  140.  
  141. CREATE FUNCTION insert_entity(par_name CHARACTER VARYING, par_enttype_id INTEGER, par_reference CHARACTER VARYING, par_coordinates CHARACTER VARYING) RETURNS INTEGER
  142.     AS $$DECLARE
  143.   already_id INTEGER;
  144. BEGIN
  145.   -- already in db?
  146.   IF par_coordinates IS NULL THEN
  147.     SELECT id INTO already_id FROM entities WHERE name=par_name AND coordinates IS NULL;
  148.   ELSE
  149.     SELECT id INTO already_id FROM entities WHERE name=par_name AND coordinates=par_coordinates;
  150.   END IF;
  151.  
  152.   IF already_id IS NULL THEN
  153.     -- have to insert it
  154.     INSERT INTO entities (name, enttype_id, reference, normalized_entity, coordinates)
  155.            VALUES (par_name, par_enttype_id, par_reference, LOWER(btrim(par_name, E' #\n\r\t')),
  156.                    par_coordinates)
  157.            RETURNING id INTO already_id;
  158.   END IF;
  159.  
  160.   RETURN already_id;
  161. END$$
  162.     LANGUAGE plpgsql;
  163.  
  164.  
  165. ALTER FUNCTION public.insert_entity(par_name CHARACTER VARYING, par_enttype_id INTEGER, par_reference CHARACTER VARYING, par_coordinates CHARACTER VARYING) OWNER TO meco;
  166.  
  167. --
  168. -- Name: insert_log(character varying, character varying, bigint); Type: FUNCTION; Schema: public; Owner: meco
  169. --
  170.  
  171. CREATE FUNCTION insert_log(type_ CHARACTER VARYING, param_ CHARACTER VARYING, value_ BIGINT) RETURNS void
  172.     AS $_$BEGIN
  173.     INSERT INTO logs (TIMESTAMP, TYPE, param, VALUE) VALUES (CURRENT_TIMESTAMP, $1, $2, $3);
  174. END;$_$
  175.     LANGUAGE plpgsql;
  176.  
  177.  
  178. ALTER FUNCTION public.insert_log(type_ CHARACTER VARYING, param_ CHARACTER VARYING, value_ BIGINT) OWNER TO meco;
  179.  
  180. --
  181. -- Name: inserting_document_f(); Type: FUNCTION; Schema: public; Owner: meco
  182. --
  183.  
  184. CREATE FUNCTION inserting_document_f() RETURNS TRIGGER
  185.     AS $$BEGIN
  186.     IF NEW.TIMESTAMP IS NULL THEN
  187.         NEW.TIMESTAMP := CURRENT_TIMESTAMP;
  188.     END IF;
  189.     NEW.fts := to_tsvector(langtolong(NEW.LANGUAGE), SUBSTRING(COALESCE(NEW.title, '')||COALESCE(NEW.text, ''), 1, 500000));
  190.     RETURN NEW;
  191. END;$$
  192.     LANGUAGE plpgsql;
  193.  
  194.  
  195. ALTER FUNCTION public.inserting_document_f() OWNER TO meco;
  196.  
  197. --
  198. -- Name: inserting_sources_f(); Type: FUNCTION; Schema: public; Owner: meco
  199. --
  200.  
  201. CREATE FUNCTION inserting_sources_f() RETURNS TRIGGER
  202.     AS $$BEGIN
  203. IF NEW.normalized_section IS NULL THEN
  204.     NEW.normalized_section := LOWER( NEW.SECTION );
  205. END IF;
  206. RETURN NEW;
  207. END;$$
  208.     LANGUAGE plpgsql;
  209.  
  210.  
  211. ALTER FUNCTION public.inserting_sources_f() OWNER TO meco;
  212.  
  213. --
  214. -- Name: inserting_terms_f(character varying); Type: FUNCTION; Schema: public; Owner: meco
  215. --
  216.  
  217. CREATE FUNCTION inserting_terms_f(_term CHARACTER VARYING) RETURNS INTEGER
  218.     AS $$DECLARE
  219.   already_id INTEGER;
  220. BEGIN
  221.   -- already in db?
  222.   SELECT id INTO already_id FROM terms WHERE term=_term;
  223.  
  224.   IF already_id IS NULL THEN
  225.     -- have to insert it
  226.     INSERT INTO terms (term)
  227.            VALUES (_term)
  228.            RETURNING id INTO already_id;
  229.   END IF;
  230.  
  231.   RETURN already_id;
  232. END$$
  233.     LANGUAGE plpgsql;
  234.  
  235.  
  236. ALTER FUNCTION public.inserting_terms_f(_term CHARACTER VARYING) OWNER TO meco;
  237.  
  238. --
  239. -- Name: langtolong(character varying); Type: FUNCTION; Schema: public; Owner: meco
  240. --
  241.  
  242. CREATE FUNCTION langtolong(CHARACTER VARYING) RETURNS regconfig
  243.     AS $_$SELECT LANGUAGE FROM langs WHERE lang=$1 LIMIT 1;$_$
  244.     LANGUAGE SQL IMMUTABLE;
  245.  
  246.  
  247. ALTER FUNCTION public.langtolong(CHARACTER VARYING) OWNER TO meco;
  248.  
  249. SET default_tablespace = '';
  250.  
  251. SET default_with_oids = FALSE;
  252.  
  253. --
  254. -- Name: authors; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  255. --
  256.  
  257. CREATE TABLE authors (
  258.     id INTEGER NOT NULL,
  259.     guid CHARACTER VARYING(64) NOT NULL,
  260.     name CHARACTER VARYING(511),
  261.     location CHARACTER VARYING(511),
  262.     description CHARACTER VARYING(511),
  263.     occupation CHARACTER VARYING(511),
  264.     gps CHARACTER VARYING(32),
  265.     gps_process BOOLEAN DEFAULT FALSE
  266. );
  267.  
  268.  
  269. ALTER TABLE public.authors OWNER TO meco;
  270.  
  271. --
  272. -- Name: documents; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  273. --
  274.  
  275. CREATE TABLE documents (
  276.     id INTEGER NOT NULL,
  277.     LANGUAGE CHARACTER VARYING(2),
  278.     source_id INTEGER,
  279.     author_id INTEGER,
  280.     "timestamp" TIMESTAMP WITHOUT TIME zone,
  281.     guid CHARACTER VARYING(511),
  282.     link CHARACTER VARYING(2047),
  283.     title CHARACTER VARYING(2047),
  284.     text text,
  285.     georss_lat CHARACTER VARYING(31),
  286.     georss_lon CHARACTER VARYING(31),
  287.     html_description text,
  288.     html_keywords text,
  289.     pubdate DATE,
  290.     pubtime TIME WITHOUT TIME zone,
  291.     sentences text,
  292.     termvector text,
  293.     _calaised BOOLEAN DEFAULT FALSE,
  294.     _analyzed BOOLEAN DEFAULT FALSE,
  295.     _relevance INTEGER DEFAULT (-1),
  296.     _stanford BOOLEAN,
  297.     fts tsvector,
  298.     _affected BOOLEAN DEFAULT FALSE,
  299.     georss_name CHARACTER VARYING(255)
  300. );
  301.  
  302.  
  303. ALTER TABLE public.documents OWNER TO meco;
  304.  
  305. --
  306. -- Name: COLUMN documents._calaised; Type: COMMENT; Schema: public; Owner: meco
  307. --
  308.  
  309. COMMENT ON COLUMN documents._calaised IS 'True if document was analyzed by OpenCalais.';
  310.  
  311.  
  312. --
  313. -- Name: COLUMN documents._analyzed; Type: COMMENT; Schema: public; Owner: meco
  314. --
  315.  
  316. COMMENT ON COLUMN documents._analyzed IS 'true if was analyzed by analyzer';
  317.  
  318.  
  319. --
  320. -- Name: COLUMN documents._relevance; Type: COMMENT; Schema: public; Owner: meco
  321. --
  322.  
  323. COMMENT ON COLUMN documents._relevance IS 'the implicit value is -1';
  324.  
  325.  
  326. --
  327. -- Name: COLUMN documents.fts; Type: COMMENT; Schema: public; Owner: meco
  328. --
  329.  
  330. COMMENT ON COLUMN documents.fts IS 'full text search';
  331.  
  332.  
  333. --
  334. -- Name: sources; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  335. --
  336.  
  337. CREATE TABLE sources (
  338.     id INTEGER NOT NULL,
  339.     link CHARACTER VARYING(2047),
  340.     LANGUAGE CHARACTER VARYING(2),
  341.     etag CHARACTER VARYING(63),
  342.     modified CHARACTER VARYING(63),
  343.     SECTION CHARACTER VARYING(200) NOT NULL,
  344.     _stahovak BOOLEAN DEFAULT FALSE,
  345.     _follow BOOLEAN DEFAULT FALSE,
  346.     normalized_section CHARACTER VARYING(200) NOT NULL,
  347.     category CHARACTER VARYING(200) NOT NULL
  348. );
  349.  
  350.  
  351. ALTER TABLE public.sources OWNER TO meco;
  352.  
  353. --
  354. -- Name: COLUMN sources._stahovak; Type: COMMENT; Schema: public; Owner: meco
  355. --
  356.  
  357. COMMENT ON COLUMN sources._stahovak IS 'True if should be downloaded by stahovak';
  358.  
  359.  
  360. --
  361. -- Name: COLUMN sources._follow; Type: COMMENT; Schema: public; Owner: meco
  362. --
  363.  
  364. COMMENT ON COLUMN sources._follow IS 'True if needed follow links of rss.';
  365.  
  366.  
  367. --
  368. -- Name: sources_twitter; Type: VIEW; Schema: public; Owner: meco
  369. --
  370.  
  371. CREATE VIEW sources_twitter AS
  372.     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[]));
  373.  
  374.  
  375. ALTER TABLE public.sources_twitter OWNER TO meco;
  376.  
  377. --
  378. -- Name: VIEW sources_twitter; Type: COMMENT; Schema: public; Owner: meco
  379. --
  380.  
  381. COMMENT ON VIEW sources_twitter IS 'Vybere všechny sources, které náleží twitteru.';
  382.  
  383.  
  384. --
  385. -- Name: Problems; Type: VIEW; Schema: public; Owner: meco
  386. --
  387.  
  388. CREATE VIEW "Problems" AS
  389.     (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);
  390.  
  391.  
  392. ALTER TABLE public."Problems" OWNER TO meco;
  393.  
  394. --
  395. -- Name: VIEW "Problems"; Type: COMMENT; Schema: public; Owner: meco
  396. --
  397.  
  398. COMMENT ON VIEW "Problems" IS 'Detekce problému. Velmi pomalé!';
  399.  
  400.  
  401. --
  402. -- Name: StanfordProgress; Type: VIEW; Schema: public; Owner: meco
  403. --
  404.  
  405. CREATE VIEW "StanfordProgress" AS
  406.     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;
  407.  
  408.  
  409. ALTER TABLE public."StanfordProgress" OWNER TO meco;
  410.  
  411. --
  412. -- Name: VIEW "StanfordProgress"; Type: COMMENT; Schema: public; Owner: meco
  413. --
  414.  
  415. COMMENT ON VIEW "StanfordProgress" IS 'Progress of stanford parser.';
  416.  
  417.  
  418. --
  419. -- Name: authors_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  420. --
  421.  
  422. CREATE SEQUENCE authors_id_seq
  423.     INCREMENT BY 1
  424.     NO MAXVALUE
  425.     NO MINVALUE
  426.     CACHE 1;
  427.  
  428.  
  429. ALTER TABLE public.authors_id_seq OWNER TO meco;
  430.  
  431. --
  432. -- Name: authors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  433. --
  434.  
  435. ALTER SEQUENCE authors_id_seq OWNED BY authors.id;
  436.  
  437.  
  438. --
  439. -- Name: entities; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  440. --
  441.  
  442. CREATE TABLE entities (
  443.     id INTEGER NOT NULL,
  444.     name CHARACTER VARYING(255),
  445.     enttype_id INTEGER,
  446.     reference CHARACTER VARYING(255),
  447.     normalized_entity CHARACTER VARYING(255),
  448.     coordinates CHARACTER VARYING(24)
  449. );
  450.  
  451.  
  452. ALTER TABLE public.entities OWNER TO meco;
  453.  
  454. --
  455. -- Name: COLUMN entities.coordinates; Type: COMMENT; Schema: public; Owner: meco
  456. --
  457.  
  458. COMMENT ON COLUMN entities.coordinates IS 'in form latitude - longitude';
  459.  
  460.  
  461. --
  462. -- Name: enttypes; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  463. --
  464.  
  465. CREATE TABLE enttypes (
  466.     id INTEGER NOT NULL,
  467.     TYPE CHARACTER VARYING(255),
  468.     reference CHARACTER VARYING(255),
  469.     parent CHARACTER VARYING(255)
  470. );
  471.  
  472.  
  473. ALTER TABLE public.enttypes OWNER TO meco;
  474.  
  475. --
  476. -- Name: instances; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  477. --
  478.  
  479. CREATE TABLE instances (
  480.     id INTEGER NOT NULL,
  481.     entity_id INTEGER,
  482.     item_id INTEGER,
  483.     exact CHARACTER VARYING(255),
  484.     offset_ INTEGER,
  485.     LENGTH INTEGER,
  486.     sid INTEGER,
  487.     begintoken INTEGER,
  488.     endtoken INTEGER
  489. );
  490.  
  491.  
  492. ALTER TABLE public.instances OWNER TO meco;
  493.  
  494. --
  495. -- Name: de_twitter_location; Type: VIEW; Schema: public; Owner: meco
  496. --
  497.  
  498. CREATE VIEW de_twitter_location AS
  499.     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;
  500.  
  501.  
  502. ALTER TABLE public.de_twitter_location OWNER TO meco;
  503.  
  504. --
  505. -- Name: VIEW de_twitter_location; Type: COMMENT; Schema: public; Owner: meco
  506. --
  507.  
  508. 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';
  509.  
  510.  
  511. --
  512. -- Name: de_twitter_location_with_MedicalCondition; Type: VIEW; Schema: public; Owner: meco
  513. --
  514.  
  515. CREATE VIEW "de_twitter_location_with_MedicalCondition" AS
  516.     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;
  517.  
  518.  
  519. ALTER TABLE public."de_twitter_location_with_MedicalCondition" OWNER TO meco;
  520.  
  521. --
  522. -- Name: VIEW "de_twitter_location_with_MedicalCondition"; Type: COMMENT; Schema: public; Owner: meco
  523. --
  524.  
  525. 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ší".';
  526.  
  527.  
  528. --
  529. -- Name: de_twitter_no_location; Type: VIEW; Schema: public; Owner: meco
  530. --
  531.  
  532. CREATE VIEW de_twitter_no_location AS
  533.     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;
  534.  
  535.  
  536. ALTER TABLE public.de_twitter_no_location OWNER TO meco;
  537.  
  538. --
  539. -- Name: VIEW de_twitter_no_location; Type: COMMENT; Schema: public; Owner: meco
  540. --
  541.  
  542. 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)';
  543.  
  544.  
  545. --
  546. -- Name: diseases; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  547. --
  548.  
  549. CREATE TABLE diseases (
  550.     id INTEGER NOT NULL,
  551.     disease_id INTEGER,
  552.     disease CHARACTER VARYING(255),
  553.     LANGUAGE CHARACTER VARYING(2),
  554.     _new BOOLEAN DEFAULT TRUE
  555. );
  556.  
  557.  
  558. ALTER TABLE public.diseases OWNER TO meco;
  559.  
  560. --
  561. -- Name: diseases_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  562. --
  563.  
  564. CREATE SEQUENCE diseases_id_seq
  565.     INCREMENT BY 1
  566.     NO MAXVALUE
  567.     NO MINVALUE
  568.     CACHE 1;
  569.  
  570.  
  571. ALTER TABLE public.diseases_id_seq OWNER TO meco;
  572.  
  573. --
  574. -- Name: diseases_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  575. --
  576.  
  577. ALTER SEQUENCE diseases_id_seq OWNED BY diseases.id;
  578.  
  579.  
  580. --
  581. -- Name: documents_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  582. --
  583.  
  584. CREATE SEQUENCE documents_id_seq
  585.     INCREMENT BY 1
  586.     NO MAXVALUE
  587.     NO MINVALUE
  588.     CACHE 1;
  589.  
  590.  
  591. ALTER TABLE public.documents_id_seq OWNER TO meco;
  592.  
  593. --
  594. -- Name: documents_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  595. --
  596.  
  597. ALTER SEQUENCE documents_id_seq OWNED BY documents.id;
  598.  
  599.  
  600. --
  601. -- Name: downloadurl; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  602. --
  603.  
  604. CREATE TABLE downloadurl (
  605.     entity_id INTEGER NOT NULL,
  606.     document_id INTEGER
  607. );
  608.  
  609.  
  610. ALTER TABLE public.downloadurl OWNER TO meco;
  611.  
  612. --
  613. -- Name: TABLE downloadurl; Type: COMMENT; Schema: public; Owner: meco
  614. --
  615.  
  616. COMMENT ON TABLE downloadurl IS 'Download url entities';
  617.  
  618.  
  619. --
  620. -- Name: downloadurl_document_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  621. --
  622.  
  623. CREATE SEQUENCE downloadurl_document_id_seq
  624.     INCREMENT BY 1
  625.     NO MAXVALUE
  626.     NO MINVALUE
  627.     CACHE 1;
  628.  
  629.  
  630. ALTER TABLE public.downloadurl_document_id_seq OWNER TO meco;
  631.  
  632. --
  633. -- Name: downloadurl_document_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  634. --
  635.  
  636. ALTER SEQUENCE downloadurl_document_id_seq OWNED BY downloadurl.document_id;
  637.  
  638.  
  639. --
  640. -- Name: downloadurl_entity_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  641. --
  642.  
  643. CREATE SEQUENCE downloadurl_entity_id_seq
  644.     START WITH 1
  645.     INCREMENT BY 1
  646.     NO MAXVALUE
  647.     NO MINVALUE
  648.     CACHE 1;
  649.  
  650.  
  651. ALTER TABLE public.downloadurl_entity_id_seq OWNER TO meco;
  652.  
  653. --
  654. -- Name: downloadurl_entity_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  655. --
  656.  
  657. ALTER SEQUENCE downloadurl_entity_id_seq OWNED BY downloadurl.entity_id;
  658.  
  659.  
  660. --
  661. -- Name: entities_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  662. --
  663.  
  664. CREATE SEQUENCE entities_id_seq
  665.     INCREMENT BY 1
  666.     NO MAXVALUE
  667.     NO MINVALUE
  668.     CACHE 1;
  669.  
  670.  
  671. ALTER TABLE public.entities_id_seq OWNER TO meco;
  672.  
  673. --
  674. -- Name: entities_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  675. --
  676.  
  677. ALTER SEQUENCE entities_id_seq OWNED BY entities.id;
  678.  
  679.  
  680. --
  681. -- Name: enttypes_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  682. --
  683.  
  684. CREATE SEQUENCE enttypes_id_seq
  685.     INCREMENT BY 1
  686.     NO MAXVALUE
  687.     NO MINVALUE
  688.     CACHE 1;
  689.  
  690.  
  691. ALTER TABLE public.enttypes_id_seq OWNER TO meco;
  692.  
  693. --
  694. -- Name: enttypes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  695. --
  696.  
  697. ALTER SEQUENCE enttypes_id_seq OWNED BY enttypes.id;
  698.  
  699.  
  700. --
  701. -- Name: instances_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  702. --
  703.  
  704. CREATE SEQUENCE instances_id_seq
  705.     INCREMENT BY 1
  706.     NO MAXVALUE
  707.     NO MINVALUE
  708.     CACHE 1;
  709.  
  710.  
  711. ALTER TABLE public.instances_id_seq OWNER TO meco;
  712.  
  713. --
  714. -- Name: instances_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  715. --
  716.  
  717. ALTER SEQUENCE instances_id_seq OWNED BY instances.id;
  718.  
  719.  
  720. --
  721. -- Name: langs; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  722. --
  723.  
  724. CREATE TABLE langs (
  725.     lang CHARACTER VARYING(12) NOT NULL,
  726.     LANGUAGE regconfig
  727. );
  728.  
  729.  
  730. ALTER TABLE public.langs OWNER TO meco;
  731.  
  732. --
  733. -- Name: TABLE langs; Type: COMMENT; Schema: public; Owner: meco
  734. --
  735.  
  736. 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.';
  737.  
  738.  
  739. --
  740. -- Name: logs; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  741. --
  742.  
  743. CREATE TABLE logs (
  744.     "timestamp" TIMESTAMP WITHOUT TIME zone NOT NULL,
  745.     TYPE CHARACTER VARYING(64) NOT NULL,
  746.     VALUE BIGINT NOT NULL,
  747.     param CHARACTER VARYING(64)
  748. );
  749.  
  750.  
  751. ALTER TABLE public.logs OWNER TO meco;
  752.  
  753. --
  754. -- Name: logs_value_seq; Type: SEQUENCE; Schema: public; Owner: meco
  755. --
  756.  
  757. CREATE SEQUENCE logs_value_seq
  758.     START WITH 1
  759.     INCREMENT BY 1
  760.     NO MAXVALUE
  761.     NO MINVALUE
  762.     CACHE 1;
  763.  
  764.  
  765. ALTER TABLE public.logs_value_seq OWNER TO meco;
  766.  
  767. --
  768. -- Name: logs_value_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  769. --
  770.  
  771. ALTER SEQUENCE logs_value_seq OWNED BY logs.VALUE;
  772.  
  773.  
  774. --
  775. -- Name: prefered_sources; Type: VIEW; Schema: public; Owner: meco
  776. --
  777.  
  778. CREATE VIEW prefered_sources AS
  779.     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));
  780.  
  781.  
  782. ALTER TABLE public.prefered_sources OWNER TO meco;
  783.  
  784. --
  785. -- Name: pubtypes_sources; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  786. --
  787.  
  788. CREATE TABLE pubtypes_sources (
  789.     source_id INTEGER,
  790.     pubtype CHARACTER VARYING(32)
  791. );
  792.  
  793.  
  794. ALTER TABLE public.pubtypes_sources OWNER TO meco;
  795.  
  796. --
  797. -- Name: sources_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  798. --
  799.  
  800. CREATE SEQUENCE sources_id_seq
  801.     INCREMENT BY 1
  802.     NO MAXVALUE
  803.     NO MINVALUE
  804.     CACHE 1;
  805.  
  806.  
  807. ALTER TABLE public.sources_id_seq OWNER TO meco;
  808.  
  809. --
  810. -- Name: sources_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  811. --
  812.  
  813. ALTER SEQUENCE sources_id_seq OWNED BY sources.id;
  814.  
  815.  
  816. --
  817. -- Name: stat_count_doc_alchemized; Type: VIEW; Schema: public; Owner: meco
  818. --
  819.  
  820. CREATE VIEW stat_count_doc_alchemized AS
  821.     SELECT COUNT(*) AS COUNT FROM documents WHERE (((documents.LANGUAGE)::text = 'de'::text) AND (documents._calaised = TRUE));
  822.  
  823.  
  824. ALTER TABLE public.stat_count_doc_alchemized OWNER TO meco;
  825.  
  826. --
  827. -- Name: VIEW stat_count_doc_alchemized; Type: COMMENT; Schema: public; Owner: meco
  828. --
  829.  
  830. COMMENT ON VIEW stat_count_doc_alchemized IS 'Počet dokumentů anotovaných AlchemyAPI.';
  831.  
  832.  
  833. --
  834. -- Name: stat_count_doc_analyzed; Type: VIEW; Schema: public; Owner: meco
  835. --
  836.  
  837. CREATE VIEW stat_count_doc_analyzed AS
  838.     SELECT COUNT(*) AS COUNT FROM documents WHERE (documents._analyzed = TRUE);
  839.  
  840.  
  841. ALTER TABLE public.stat_count_doc_analyzed OWNER TO meco;
  842.  
  843. --
  844. -- Name: VIEW stat_count_doc_analyzed; Type: COMMENT; Schema: public; Owner: meco
  845. --
  846.  
  847. COMMENT ON VIEW stat_count_doc_analyzed IS 'Počet analyzovaných dokumentů.';
  848.  
  849.  
  850. --
  851. -- Name: stat_count_doc_per_source; Type: VIEW; Schema: public; Owner: meco
  852. --
  853.  
  854. CREATE VIEW stat_count_doc_per_source AS
  855.     SELECT COUNT(1) AS pocet, sources.SECTION FROM documents, sources WHERE (documents.source_id = sources.id) GROUP BY sources.SECTION ORDER BY COUNT(1);
  856.  
  857.  
  858. ALTER TABLE public.stat_count_doc_per_source OWNER TO meco;
  859.  
  860. --
  861. -- Name: VIEW stat_count_doc_per_source; Type: COMMENT; Schema: public; Owner: meco
  862. --
  863.  
  864. COMMENT ON VIEW stat_count_doc_per_source IS 'pocet dokumentů z každého fóra';
  865.  
  866.  
  867. --
  868. -- Name: stat_count_doc_stanford; Type: VIEW; Schema: public; Owner: meco
  869. --
  870.  
  871. CREATE VIEW stat_count_doc_stanford AS
  872.     SELECT COUNT(*) AS COUNT FROM documents WHERE (documents._stanford = TRUE);
  873.  
  874.  
  875. ALTER TABLE public.stat_count_doc_stanford OWNER TO meco;
  876.  
  877. --
  878. -- Name: symptoms; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  879. --
  880.  
  881. CREATE TABLE symptoms (
  882.     id INTEGER NOT NULL,
  883.     symptom CHARACTER VARYING(255),
  884.     LANGUAGE CHARACTER VARYING(2),
  885.     _new BOOLEAN DEFAULT TRUE
  886. );
  887.  
  888.  
  889. ALTER TABLE public.symptoms OWNER TO meco;
  890.  
  891. --
  892. -- Name: symptoms_id_seq; Type: SEQUENCE; Schema: public; Owner: meco
  893. --
  894.  
  895. CREATE SEQUENCE symptoms_id_seq
  896.     INCREMENT BY 1
  897.     NO MAXVALUE
  898.     NO MINVALUE
  899.     CACHE 1;
  900.  
  901.  
  902. ALTER TABLE public.symptoms_id_seq OWNER TO meco;
  903.  
  904. --
  905. -- Name: symptoms_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  906. --
  907.  
  908. ALTER SEQUENCE symptoms_id_seq OWNED BY symptoms.id;
  909.  
  910.  
  911. --
  912. -- Name: terms; Type: TABLE; Schema: public; Owner: meco; Tablespace:
  913. --
  914.  
  915. CREATE TABLE terms (
  916.     term CHARACTER VARYING(255) NOT NULL,
  917.     id INTEGER NOT NULL
  918. );
  919.  
  920.  
  921. ALTER TABLE public.terms OWNER TO meco;
  922.  
  923. --
  924. -- Name: term_form_seq; Type: SEQUENCE; Schema: public; Owner: meco
  925. --
  926.  
  927. CREATE SEQUENCE term_form_seq
  928.     START WITH 1
  929.     INCREMENT BY 1
  930.     NO MAXVALUE
  931.     NO MINVALUE
  932.     CACHE 1;
  933.  
  934.  
  935. ALTER TABLE public.term_form_seq OWNER TO meco;
  936.  
  937. --
  938. -- Name: term_form_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  939. --
  940.  
  941. ALTER SEQUENCE term_form_seq OWNED BY terms.term;
  942.  
  943.  
  944. --
  945. -- Name: term_number_seq; Type: SEQUENCE; Schema: public; Owner: meco
  946. --
  947.  
  948. CREATE SEQUENCE term_number_seq
  949.     INCREMENT BY 1
  950.     NO MAXVALUE
  951.     NO MINVALUE
  952.     CACHE 1;
  953.  
  954.  
  955. ALTER TABLE public.term_number_seq OWNER TO meco;
  956.  
  957. --
  958. -- Name: term_number_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: meco
  959. --
  960.  
  961. ALTER SEQUENCE term_number_seq OWNED BY terms.id;
  962.  
  963.  
  964. --
  965. -- Name: id; Type: DEFAULT; Schema: public; Owner: meco
  966. --
  967.  
  968. ALTER TABLE authors ALTER COLUMN id SET DEFAULT NEXTVAL('authors_id_seq'::regclass);
  969.  
  970.  
  971. --
  972. -- Name: id; Type: DEFAULT; Schema: public; Owner: meco
  973. --
  974.  
  975. ALTER TABLE diseases ALTER COLUMN id SET DEFAULT NEXTVAL('diseases_id_seq'::regclass);
  976.  
  977.  
  978. --
  979. -- Name: id; Type: DEFAULT; Schema: public; Owner: meco
  980. --
  981.  
  982. ALTER TABLE documents ALTER COLUMN id SET DEFAULT NEXTVAL('documents_id_seq'::regclass);
  983.  
  984.  
  985. --
  986. -- Name: entity_id; Type: DEFAULT; Schema: public; Owner: meco
  987. --
  988.  
  989. ALTER TABLE downloadurl ALTER COLUMN entity_id SET DEFAULT NEXTVAL('downloadurl_entity_id_seq'::regclass);
  990.  
  991.  
  992. --
  993. -- Name: document_id; Type: DEFAULT; Schema: public; Owner: meco
  994. --
  995.  
  996. ALTER TABLE downloadurl ALTER COLUMN document_id SET DEFAULT NEXTVAL('downloadurl_document_id_seq'::regclass);
  997.  
  998.  
  999. --
  1000. -- Name: id; Type: DEFAULT; Schema: public; Owner: meco
  1001. --
  1002.  
  1003. ALTER TABLE entities ALTER COLUMN id SET DEFAULT NEXTVAL('entities_id_seq'::regclass);
  1004.  
  1005.  
  1006. --
  1007. -- Name: id; Type: DEFAULT; Schema: public; Owner: meco
  1008. --
  1009.  
  1010. ALTER TABLE enttypes ALTER COLUMN id SET DEFAULT NEXTVAL('enttypes_id_seq'::regclass);
  1011.  
  1012.  
  1013. --
  1014. -- Name: id; Type: DEFAULT; Schema: public; Owner: meco
  1015. --
  1016.  
  1017. ALTER TABLE instances ALTER COLUMN id SET DEFAULT NEXTVAL('instances_id_seq'::regclass);
  1018.  
  1019.  
  1020. --
  1021. -- Name: id; Type: DEFAULT; Schema: public; Owner: meco
  1022. --
  1023.  
  1024. ALTER TABLE sources ALTER COLUMN id SET DEFAULT NEXTVAL('sources_id_seq'::regclass);
  1025.  
  1026.  
  1027. --
  1028. -- Name: id; Type: DEFAULT; Schema: public; Owner: meco
  1029. --
  1030.  
  1031. ALTER TABLE symptoms ALTER COLUMN id SET DEFAULT NEXTVAL('symptoms_id_seq'::regclass);
  1032.  
  1033.  
  1034. --
  1035. -- Name: id; Type: DEFAULT; Schema: public; Owner: meco
  1036. --
  1037.  
  1038. ALTER TABLE terms ALTER COLUMN id SET DEFAULT NEXTVAL('term_number_seq'::regclass);
  1039.  
  1040.  
  1041. --
  1042. -- Name: authors_guid_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1043. --
  1044.  
  1045. ALTER TABLE ONLY authors
  1046.     ADD CONSTRAINT authors_guid_key UNIQUE (guid);
  1047.  
  1048.  
  1049. --
  1050. -- Name: authors_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1051. --
  1052.  
  1053. ALTER TABLE ONLY authors
  1054.     ADD CONSTRAINT authors_pkey PRIMARY KEY (id);
  1055.  
  1056.  
  1057. --
  1058. -- Name: diseases_disease_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1059. --
  1060.  
  1061. ALTER TABLE ONLY diseases
  1062.     ADD CONSTRAINT diseases_disease_key UNIQUE (disease);
  1063.  
  1064.  
  1065. --
  1066. -- Name: diseases_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1067. --
  1068.  
  1069. ALTER TABLE ONLY diseases
  1070.     ADD CONSTRAINT diseases_pkey PRIMARY KEY (id);
  1071.  
  1072.  
  1073. --
  1074. -- Name: documents_guid_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1075. --
  1076.  
  1077. ALTER TABLE ONLY documents
  1078.     ADD CONSTRAINT documents_guid_key UNIQUE (guid);
  1079.  
  1080.  
  1081. --
  1082. -- Name: documents_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1083. --
  1084.  
  1085. ALTER TABLE ONLY documents
  1086.     ADD CONSTRAINT documents_pkey PRIMARY KEY (id);
  1087.  
  1088.  
  1089. --
  1090. -- Name: entities_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1091. --
  1092.  
  1093. ALTER TABLE ONLY entities
  1094.     ADD CONSTRAINT entities_pkey PRIMARY KEY (id);
  1095.  
  1096.  
  1097. --
  1098. -- Name: enttypes_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1099. --
  1100.  
  1101. ALTER TABLE ONLY enttypes
  1102.     ADD CONSTRAINT enttypes_pkey PRIMARY KEY (id);
  1103.  
  1104.  
  1105. --
  1106. -- Name: enttypes_type_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1107. --
  1108.  
  1109. ALTER TABLE ONLY enttypes
  1110.     ADD CONSTRAINT enttypes_type_key UNIQUE (TYPE);
  1111.  
  1112.  
  1113. --
  1114. -- Name: instances_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1115. --
  1116.  
  1117. ALTER TABLE ONLY instances
  1118.     ADD CONSTRAINT instances_pkey PRIMARY KEY (id);
  1119.  
  1120.  
  1121. --
  1122. -- Name: langs_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1123. --
  1124.  
  1125. ALTER TABLE ONLY langs
  1126.     ADD CONSTRAINT langs_pkey PRIMARY KEY (lang);
  1127.  
  1128.  
  1129. --
  1130. -- Name: name_coords_uniq; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1131. --
  1132.  
  1133. ALTER TABLE ONLY entities
  1134.     ADD CONSTRAINT name_coords_uniq UNIQUE (name, coordinates);
  1135.  
  1136.  
  1137. --
  1138. -- Name: sources_link_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1139. --
  1140.  
  1141. ALTER TABLE ONLY sources
  1142.     ADD CONSTRAINT sources_link_key UNIQUE (link);
  1143.  
  1144.  
  1145. --
  1146. -- Name: sources_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1147. --
  1148.  
  1149. ALTER TABLE ONLY sources
  1150.     ADD CONSTRAINT sources_pkey PRIMARY KEY (id);
  1151.  
  1152.  
  1153. --
  1154. -- Name: symptoms_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1155. --
  1156.  
  1157. ALTER TABLE ONLY symptoms
  1158.     ADD CONSTRAINT symptoms_pkey PRIMARY KEY (id);
  1159.  
  1160.  
  1161. --
  1162. -- Name: terms_pkey; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1163. --
  1164.  
  1165. ALTER TABLE ONLY terms
  1166.     ADD CONSTRAINT terms_pkey PRIMARY KEY (id);
  1167.  
  1168.  
  1169. --
  1170. -- Name: terms_term_key; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1171. --
  1172.  
  1173. ALTER TABLE ONLY terms
  1174.     ADD CONSTRAINT terms_term_key UNIQUE (term);
  1175.  
  1176.  
  1177. --
  1178. -- Name: uniq_symptom; Type: CONSTRAINT; Schema: public; Owner: meco; Tablespace:
  1179. --
  1180.  
  1181. ALTER TABLE ONLY symptoms
  1182.     ADD CONSTRAINT uniq_symptom UNIQUE (symptom);
  1183.  
  1184.  
  1185. --
  1186. -- Name: author_id_index; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1187. --
  1188.  
  1189. CREATE INDEX author_id_index ON documents USING btree (author_id);
  1190.  
  1191.  
  1192. --
  1193. -- Name: documents_analyzed; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1194. --
  1195.  
  1196. CREATE INDEX documents_analyzed ON documents USING btree (_stanford, _calaised, _analyzed);
  1197.  
  1198.  
  1199. --
  1200. -- Name: documents_fts_index; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1201. --
  1202.  
  1203. CREATE INDEX documents_fts_index ON documents USING gin (fts);
  1204.  
  1205.  
  1206. --
  1207. -- Name: documents_language; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1208. --
  1209.  
  1210. CREATE INDEX documents_language ON documents USING btree (LANGUAGE);
  1211.  
  1212.  
  1213. --
  1214. -- Name: documents_pubdate; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1215. --
  1216.  
  1217. CREATE INDEX documents_pubdate ON documents USING btree (pubdate, pubtime);
  1218.  
  1219.  
  1220. --
  1221. -- Name: documents_relevance; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1222. --
  1223.  
  1224. CREATE INDEX documents_relevance ON documents USING btree (_relevance);
  1225.  
  1226.  
  1227. --
  1228. -- Name: entities_enttype_id; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1229. --
  1230.  
  1231. CREATE INDEX entities_enttype_id ON entities USING btree (enttype_id);
  1232.  
  1233.  
  1234. --
  1235. -- Name: entities_normalized_entity; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1236. --
  1237.  
  1238. CREATE INDEX entities_normalized_entity ON entities USING hash (normalized_entity);
  1239.  
  1240.  
  1241. --
  1242. -- Name: entity_index; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1243. --
  1244.  
  1245. CREATE UNIQUE INDEX entity_index ON downloadurl USING btree (entity_id);
  1246.  
  1247.  
  1248. --
  1249. -- Name: instances_entity_id; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1250. --
  1251.  
  1252. CREATE INDEX instances_entity_id ON instances USING btree (entity_id);
  1253.  
  1254.  
  1255. --
  1256. -- Name: instances_item_id; Type: INDEX; Schema: public; Owner: meco; Tablespace:
  1257. --
  1258.  
  1259. CREATE INDEX instances_item_id ON instances USING btree (item_id);
  1260.  
  1261.  
  1262. --
  1263. -- Name: inserting_document_trigger; Type: TRIGGER; Schema: public; Owner: meco
  1264. --
  1265.  
  1266. CREATE TRIGGER inserting_document_trigger
  1267.     BEFORE INSERT ON documents
  1268.     FOR EACH ROW
  1269.     EXECUTE PROCEDURE inserting_document_f();
  1270.  
  1271.  
  1272. --
  1273. -- Name: inserting_trigger; Type: TRIGGER; Schema: public; Owner: meco
  1274. --
  1275.  
  1276. CREATE TRIGGER inserting_trigger
  1277.     BEFORE INSERT ON sources
  1278.     FOR EACH ROW
  1279.     EXECUTE PROCEDURE inserting_sources_f();
  1280.  
  1281.  
  1282. --
  1283. -- Name: authors_fk; Type: FK CONSTRAINT; Schema: public; Owner: meco
  1284. --
  1285.  
  1286. ALTER TABLE ONLY documents
  1287.     ADD CONSTRAINT authors_fk FOREIGN KEY (author_id) REFERENCES authors(id) ON UPDATE SET NULL ON DELETE SET NULL;
  1288.  
  1289.  
  1290. --
  1291. -- Name: doc_fk; Type: FK CONSTRAINT; Schema: public; Owner: meco
  1292. --
  1293.  
  1294. ALTER TABLE ONLY downloadurl
  1295.     ADD CONSTRAINT doc_fk FOREIGN KEY (document_id) REFERENCES documents(id) ON UPDATE CASCADE ON DELETE CASCADE;
  1296.  
  1297.  
  1298. --
  1299. -- Name: entity_fk; Type: FK CONSTRAINT; Schema: public; Owner: meco
  1300. --
  1301.  
  1302. ALTER TABLE ONLY downloadurl
  1303.     ADD CONSTRAINT entity_fk FOREIGN KEY (entity_id) REFERENCES entities(id) ON UPDATE CASCADE ON DELETE CASCADE;
  1304.  
  1305.  
  1306. --
  1307. -- Name: fk_entities_enttypes; Type: FK CONSTRAINT; Schema: public; Owner: meco
  1308. --
  1309.  
  1310. ALTER TABLE ONLY entities
  1311.     ADD CONSTRAINT fk_entities_enttypes FOREIGN KEY (enttype_id) REFERENCES enttypes(id);
  1312.  
  1313.  
  1314. --
  1315. -- Name: fk_instances_documents; Type: FK CONSTRAINT; Schema: public; Owner: meco
  1316. --
  1317.  
  1318. ALTER TABLE ONLY instances
  1319.     ADD CONSTRAINT fk_instances_documents FOREIGN KEY (item_id) REFERENCES documents(id) ON UPDATE CASCADE ON DELETE CASCADE;
  1320.  
  1321.  
  1322. --
  1323. -- Name: fk_instances_entities; Type: FK CONSTRAINT; Schema: public; Owner: meco
  1324. --
  1325.  
  1326. ALTER TABLE ONLY instances
  1327.     ADD CONSTRAINT fk_instances_entities FOREIGN KEY (entity_id) REFERENCES entities(id) ON UPDATE CASCADE ON DELETE CASCADE;
  1328.  
  1329.  
  1330. --
  1331. -- Name: fk_pubtypes_sources_sources; Type: FK CONSTRAINT; Schema: public; Owner: meco
  1332. --
  1333.  
  1334. ALTER TABLE ONLY pubtypes_sources
  1335.     ADD CONSTRAINT fk_pubtypes_sources_sources FOREIGN KEY (source_id) REFERENCES sources(id);
  1336.  
  1337.  
  1338. --
  1339. -- Name: source_fk; Type: FK CONSTRAINT; Schema: public; Owner: meco
  1340. --
  1341.  
  1342. ALTER TABLE ONLY documents
  1343.     ADD CONSTRAINT source_fk FOREIGN KEY (source_id) REFERENCES sources(id) ON UPDATE CASCADE ON DELETE SET NULL;
  1344.  
  1345.  
  1346. --
  1347. -- Name: public; Type: ACL; Schema: -; Owner: ischmidt
  1348. --
  1349.  
  1350. REVOKE ALL ON SCHEMA public FROM PUBLIC;
  1351. REVOKE ALL ON SCHEMA public FROM ischmidt;
  1352. GRANT ALL ON SCHEMA public TO ischmidt;
  1353. GRANT ALL ON SCHEMA public TO PUBLIC;
  1354.  
  1355.  
  1356. --
  1357. -- Name: inserting_document_f(); Type: ACL; Schema: public; Owner: meco
  1358. --
  1359.  
  1360. REVOKE ALL ON FUNCTION inserting_document_f() FROM PUBLIC;
  1361. REVOKE ALL ON FUNCTION inserting_document_f() FROM meco;
  1362. GRANT ALL ON FUNCTION inserting_document_f() TO meco;
  1363. GRANT ALL ON FUNCTION inserting_document_f() TO PUBLIC;
  1364.  
  1365.  
  1366. --
  1367. -- PostgreSQL database dump complete
  1368. --
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!