Show
Ignore:
Timestamp:
05/20/09 23:49:08 (5 years ago)
Author:
Theo Schlossnagle <jesus@omniti.com>
git-committer:
Theo Schlossnagle <jesus@omniti.com> 1242863348 +0000
git-parent:

[5b63423e3ea22c19a8187dfcce162d43e0159643]

git-author:
Theo Schlossnagle <jesus@omniti.com> 1242863348 +0000
Message:

update the schema, remove unnecessary crap

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/reconnoiter_ddl_dump.sql

    ra647406 r6adb5a7  
    1010 
    1111-- 
    12 -- Name: iep; Type: SCHEMA; Schema: -; Owner: iepuser 
    13 -- 
    14  
    15 CREATE SCHEMA iep; 
    16  
    17  
    18 ALTER SCHEMA iep OWNER TO iepuser; 
    19  
    20 -- 
    21 -- Name: otools; Type: SCHEMA; Schema: -; Owner: postgres 
    22 -- 
    23  
    24 CREATE SCHEMA otools; 
    25  
    26  
    27 ALTER SCHEMA otools OWNER TO postgres; 
    28  
    29 -- 
    3012-- Name: prism; Type: SCHEMA; Schema: -; Owner: prism 
    3113-- 
     
    4527ALTER SCHEMA stratcon OWNER TO stratcon; 
    4628 
    47 -- 
    48 -- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: omniti 
    49 -- 
    50  
    51 CREATE PROCEDURAL LANGUAGE plpgsql; 
    52  
    53  
    54 ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO omniti; 
    55  
    56 SET search_path = iep, pg_catalog; 
     29SET search_path = prism, pg_catalog; 
    5730 
    5831SET default_tablespace = ''; 
    5932 
    6033SET default_with_oids = false; 
    61  
    62 -- 
    63 -- Name: results; Type: TABLE; Schema: iep; Owner: iepuser; Tablespace:  
    64 -- 
    65  
    66 CREATE TABLE results ( 
    67     uuid character varying NOT NULL, 
    68     value double precision NOT NULL, 
    69     start character varying, 
    70     name character varying NOT NULL, 
    71     type character varying, 
    72     "time" character varying NOT NULL 
    73 ); 
    74  
    75  
    76 ALTER TABLE iep.results OWNER TO iepuser; 
    77  
    78 SET search_path = otools, pg_catalog; 
    79  
    80 -- 
    81 -- Name: table_growth; Type: TABLE; Schema: otools; Owner: postgres; Tablespace:  
    82 -- 
    83  
    84 CREATE TABLE table_growth ( 
    85     table_owner text NOT NULL, 
    86     schema_name text NOT NULL, 
    87     table_name text NOT NULL, 
    88     actual_size numeric NOT NULL, 
    89     growth_size numeric NOT NULL, 
    90     sum_flag smallint NOT NULL, 
    91     capture_time date NOT NULL 
    92 ); 
    93  
    94  
    95 ALTER TABLE otools.table_growth OWNER TO postgres; 
    96  
    97 SET search_path = prism, pg_catalog; 
    9834 
    9935-- 
     
    171107ALTER TABLE prism.saved_worksheets_dep OWNER TO reconnoiter; 
    172108 
    173 SET search_path = public, pg_catalog; 
    174  
    175 -- 
    176 -- Name: foo; Type: TABLE; Schema: public; Owner: omniti; Tablespace:  
    177 -- 
    178  
    179 CREATE TABLE foo ( 
    180     a timestamp with time zone 
    181 ); 
    182  
    183  
    184 ALTER TABLE public.foo OWNER TO omniti; 
    185  
    186 SET default_with_oids = true; 
    187  
    188 -- 
    189 -- Name: pga_diagrams; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    190 -- 
    191  
    192 CREATE TABLE pga_diagrams ( 
    193     diagramname character varying(64) NOT NULL, 
    194     diagramtables text, 
    195     diagramlinks text 
    196 ); 
    197  
    198  
    199 ALTER TABLE public.pga_diagrams OWNER TO reconnoiter; 
    200  
    201 -- 
    202 -- Name: pga_forms; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    203 -- 
    204  
    205 CREATE TABLE pga_forms ( 
    206     formname character varying(64) NOT NULL, 
    207     formsource text 
    208 ); 
    209  
    210  
    211 ALTER TABLE public.pga_forms OWNER TO reconnoiter; 
    212  
    213 -- 
    214 -- Name: pga_graphs; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    215 -- 
    216  
    217 CREATE TABLE pga_graphs ( 
    218     graphname character varying(64) NOT NULL, 
    219     graphsource text, 
    220     graphcode text 
    221 ); 
    222  
    223  
    224 ALTER TABLE public.pga_graphs OWNER TO reconnoiter; 
    225  
    226 -- 
    227 -- Name: pga_images; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    228 -- 
    229  
    230 CREATE TABLE pga_images ( 
    231     imagename character varying(64) NOT NULL, 
    232     imagesource text 
    233 ); 
    234  
    235  
    236 ALTER TABLE public.pga_images OWNER TO reconnoiter; 
    237  
    238 -- 
    239 -- Name: pga_layout; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    240 -- 
    241  
    242 CREATE TABLE pga_layout ( 
    243     tablename character varying(64) NOT NULL, 
    244     nrcols smallint, 
    245     colnames text, 
    246     colwidth text 
    247 ); 
    248  
    249  
    250 ALTER TABLE public.pga_layout OWNER TO reconnoiter; 
    251  
    252 -- 
    253 -- Name: pga_queries; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    254 -- 
    255  
    256 CREATE TABLE pga_queries ( 
    257     queryname character varying(64) NOT NULL, 
    258     querytype character(1), 
    259     querycommand text, 
    260     querytables text, 
    261     querylinks text, 
    262     queryresults text, 
    263     querycomments text 
    264 ); 
    265  
    266  
    267 ALTER TABLE public.pga_queries OWNER TO reconnoiter; 
    268  
    269 -- 
    270 -- Name: pga_reports; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    271 -- 
    272  
    273 CREATE TABLE pga_reports ( 
    274     reportname character varying(64) NOT NULL, 
    275     reportsource text, 
    276     reportbody text, 
    277     reportprocs text, 
    278     reportoptions text 
    279 ); 
    280  
    281  
    282 ALTER TABLE public.pga_reports OWNER TO reconnoiter; 
    283  
    284 -- 
    285 -- Name: pga_scripts; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    286 -- 
    287  
    288 CREATE TABLE pga_scripts ( 
    289     scriptname character varying(64) NOT NULL, 
    290     scriptsource text 
    291 ); 
    292  
    293  
    294 ALTER TABLE public.pga_scripts OWNER TO reconnoiter; 
    295  
    296 SET default_with_oids = false; 
    297  
    298 -- 
    299 -- Name: varnish_huh; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    300 -- 
    301  
    302 CREATE TABLE varnish_huh ( 
    303     sid integer, 
    304     whence timestamp with time zone, 
    305     name text, 
    306     value numeric 
    307 ); 
    308  
    309  
    310 ALTER TABLE public.varnish_huh OWNER TO reconnoiter; 
    311  
    312 -- 
    313 -- Name: varnish_huh2; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:  
    314 -- 
    315  
    316 CREATE TABLE varnish_huh2 ( 
    317     sid integer, 
    318     whence timestamp with time zone, 
    319     name text, 
    320     value numeric 
    321 ); 
    322  
    323  
    324 ALTER TABLE public.varnish_huh2 OWNER TO reconnoiter; 
    325  
    326 -- 
    327 -- Name: x; Type: TABLE; Schema: public; Owner: postgres; Tablespace:  
    328 -- 
    329  
    330 CREATE TABLE x ( 
    331     sid integer, 
    332     whence timestamp with time zone, 
    333     name text, 
    334     value numeric 
    335 ); 
    336  
    337  
    338 ALTER TABLE public.x OWNER TO postgres; 
    339  
    340109SET search_path = stratcon, pg_catalog; 
    341110 
     
    650419 
    651420ALTER TABLE stratcon.rollup_runner OWNER TO reconnoiter; 
    652  
    653 -- 
    654 -- Name: test6; Type: TABLE; Schema: stratcon; Owner: postgres; Tablespace:  
    655 -- 
    656  
    657 CREATE TABLE test6 ( 
    658     rollup_time timestamp without time zone, 
    659     duration character varying(10) 
    660 ); 
    661  
    662  
    663 ALTER TABLE stratcon.test6 OWNER TO postgres; 
    664  
    665 SET search_path = otools, pg_catalog; 
    666  
    667 -- 
    668 -- Name: collect_table_growth(); Type: FUNCTION; Schema: otools; Owner: postgres 
    669 -- 
    670  
    671 CREATE FUNCTION collect_table_growth() RETURNS void 
    672     AS $$ 
    673     insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time)  
    674     select pg_get_userbyid(c.relowner) AS table_owner, n.nspname AS schema_name, c.relname AS table_name, pg_total_relation_size(c.oid), 0, 0, current_date  
    675     from pg_class c JOIN pg_namespace n ON (c.relnamespace=n.oid) where relkind = 'r' and reltuples > 10000; 
    676 $$ 
    677     LANGUAGE sql; 
    678  
    679  
    680 ALTER FUNCTION otools.collect_table_growth() OWNER TO postgres; 
    681  
    682 -- 
    683 -- Name: quote_nullable(numeric); Type: FUNCTION; Schema: otools; Owner: postgres 
    684 -- 
    685  
    686 CREATE FUNCTION quote_nullable(numeric) RETURNS text 
    687     AS $_$ 
    688 SELECT CASE WHEN $1 IS NULL THEN 'NULL' ELSE $1::text END; 
    689 $_$ 
    690     LANGUAGE sql; 
    691  
    692  
    693 ALTER FUNCTION otools.quote_nullable(numeric) OWNER TO postgres; 
    694  
    695 -- 
    696 -- Name: quote_nullable(text); Type: FUNCTION; Schema: otools; Owner: postgres 
    697 -- 
    698  
    699 CREATE FUNCTION quote_nullable(text) RETURNS text 
    700     AS $_$ 
    701 SELECT CASE WHEN $1 IS NULL THEN 'NULL' ELSE quote_literal($1) END; 
    702 $_$ 
    703     LANGUAGE sql; 
    704  
    705  
    706 ALTER FUNCTION otools.quote_nullable(text) OWNER TO postgres; 
    707  
    708 -- 
    709 -- Name: quote_nullable(timestamp with time zone); Type: FUNCTION; Schema: otools; Owner: postgres 
    710 -- 
    711  
    712 CREATE FUNCTION quote_nullable(timestamp with time zone) RETURNS text 
    713     AS $_$ 
    714 SELECT CASE WHEN $1 IS NULL THEN 'NULL' ELSE quote_literal($1) END; 
    715 $_$ 
    716     LANGUAGE sql; 
    717  
    718  
    719 ALTER FUNCTION otools.quote_nullable(timestamp with time zone) OWNER TO postgres; 
    720  
    721 -- 
    722 -- Name: summarize_table_growth(); Type: FUNCTION; Schema: otools; Owner: postgres 
    723 -- 
    724  
    725 CREATE FUNCTION summarize_table_growth() RETURNS void 
    726     AS $$ 
    727 declare 
    728     v_sql text; 
    729 begin 
    730  
    731 -- Daily summarization 
    732 IF to_char(current_date,'dd') <> '01' THEN 
    733     insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
    734     select a.table_owner, a.schema_name, a.table_name, a.actual_size, a.actual_size-coalesce(b.actual_size,0) AS table_growth, 1, a.capture_time 
    735     from otools.table_growth a  
    736         left join otools.table_growth b  
    737             on (a.table_owner=b.table_owner and a.table_name=b.table_name and a.schema_name=b.schema_name and b.capture_time = current_date -1)  
    738     where  
    739         a.sum_flag=0 and a.capture_time = current_date; 
    740     -- now remove older rows 
    741     delete from otools.table_growth where sum_flag = 0; 
    742 END IF; 
    743  
    744 -- Monthly summarization 
    745 IF to_char(current_date,'dd') = '01' THEN 
    746     insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
    747     select a.table_owner, a.schema_name, a.table_name, max(actual_size), sum(growth_size), 2, (current_date - '1 month'::interval)  
    748     from otools.table_growth a  
    749     where sum_flag=1 and capture_time between (current_date - '1 month'::interval) and current_date  
    750     group by table_owner, schema_name, table_name; 
    751     -- now remove older rows 
    752     delete from otools.table_growth where sum_flag = 1; 
    753 END IF; 
    754  
    755 end  
    756 $$ 
    757     LANGUAGE plpgsql; 
    758  
    759  
    760 ALTER FUNCTION otools.summarize_table_growth() OWNER TO postgres; 
    761421 
    762422SET search_path = prism, pg_catalog; 
     
    975635 
    976636ALTER FUNCTION prism.trig_update_tsvector_saved_graphs() OWNER TO reconnoiter; 
    977  
    978 SET search_path = public, pg_catalog; 
    979  
    980 -- 
    981 -- Name: date_hour(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: reconnoiter 
    982 -- 
    983  
    984 CREATE FUNCTION date_hour(timestamp with time zone) RETURNS timestamp with time zone 
    985     AS $_$ 
    986  SELECT date_trunc('hour',$1); 
    987 $_$ 
    988     LANGUAGE sql IMMUTABLE STRICT; 
    989  
    990  
    991 ALTER FUNCTION public.date_hour(timestamp with time zone) OWNER TO reconnoiter; 
    992637 
    993638SET search_path = stratcon, pg_catalog; 
     
    25632208ALTER TABLE stratcon.seq_sid OWNER TO reconnoiter; 
    25642209 
    2565 SET search_path = iep, pg_catalog; 
    2566  
    2567 -- 
    2568 -- Name: pkeyresults; Type: CONSTRAINT; Schema: iep; Owner: iepuser; Tablespace:  
    2569 -- 
    2570  
    2571 ALTER TABLE ONLY results 
    2572     ADD CONSTRAINT pkeyresults PRIMARY KEY (uuid, "time", value, name); 
    2573  
    2574  
    25752210SET search_path = prism, pg_catalog; 
    25762211 
     
    26212256ALTER TABLE ONLY saved_graphs 
    26222257    ADD CONSTRAINT unq_saved_graphs_title UNIQUE (title); 
    2623  
    2624  
    2625 SET search_path = public, pg_catalog; 
    2626  
    2627 -- 
    2628 -- Name: pga_diagrams_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:  
    2629 -- 
    2630  
    2631 ALTER TABLE ONLY pga_diagrams 
    2632     ADD CONSTRAINT pga_diagrams_pkey PRIMARY KEY (diagramname); 
    2633  
    2634  
    2635 -- 
    2636 -- Name: pga_forms_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:  
    2637 -- 
    2638  
    2639 ALTER TABLE ONLY pga_forms 
    2640     ADD CONSTRAINT pga_forms_pkey PRIMARY KEY (formname); 
    2641  
    2642  
    2643 -- 
    2644 -- Name: pga_graphs_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:  
    2645 -- 
    2646  
    2647 ALTER TABLE ONLY pga_graphs 
    2648     ADD CONSTRAINT pga_graphs_pkey PRIMARY KEY (graphname); 
    2649  
    2650  
    2651 -- 
    2652 -- Name: pga_images_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:  
    2653 -- 
    2654  
    2655 ALTER TABLE ONLY pga_images 
    2656     ADD CONSTRAINT pga_images_pkey PRIMARY KEY (imagename); 
    2657  
    2658  
    2659 -- 
    2660 -- Name: pga_layout_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:  
    2661 -- 
    2662  
    2663 ALTER TABLE ONLY pga_layout 
    2664     ADD CONSTRAINT pga_layout_pkey PRIMARY KEY (tablename); 
    2665  
    2666  
    2667 -- 
    2668 -- Name: pga_queries_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:  
    2669 -- 
    2670  
    2671 ALTER TABLE ONLY pga_queries 
    2672     ADD CONSTRAINT pga_queries_pkey PRIMARY KEY (queryname); 
    2673  
    2674  
    2675 -- 
    2676 -- Name: pga_reports_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:  
    2677 -- 
    2678  
    2679 ALTER TABLE ONLY pga_reports 
    2680     ADD CONSTRAINT pga_reports_pkey PRIMARY KEY (reportname); 
    2681  
    2682  
    2683 -- 
    2684 -- Name: pga_scripts_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:  
    2685 -- 
    2686  
    2687 ALTER TABLE ONLY pga_scripts 
    2688     ADD CONSTRAINT pga_scripts_pkey PRIMARY KEY (scriptname); 
    26892258 
    26902259 
     
    30762645GRANT ALL ON SCHEMA prism TO prism; 
    30772646GRANT ALL ON SCHEMA prism TO stratcon; 
    3078  
    3079  
    3080 -- 
    3081 -- Name: public; Type: ACL; Schema: -; Owner: postgres 
    3082 -- 
    3083  
    3084 REVOKE ALL ON SCHEMA public FROM PUBLIC; 
    3085 REVOKE ALL ON SCHEMA public FROM postgres; 
    3086 GRANT ALL ON SCHEMA public TO postgres; 
    3087 GRANT ALL ON SCHEMA public TO PUBLIC; 
    30882647 
    30892648