corp/sql/corp-shema.sql

\connect - dpavlin
CREATE SEQUENCE "news_id_seq" start 3297 increment 1 maxvalue 2147483647 minvalue 1  cache 51 ;
SELECT nextval ('"news_id_seq"');
CREATE SEQUENCE "sections_id_seq" start 67 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"sections_id_seq"');
CREATE SEQUENCE "paragraphs_id_seq" start 3536 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"paragraphs_id_seq"');
CREATE SEQUENCE "community_picseq" start 3 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"community_picseq"');
CREATE SEQUENCE "picseq" start 5 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"picseq"');
CREATE SEQUENCE "analysts_num_seq" start 20 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"analysts_num_seq"');
CREATE SEQUENCE "open_position_locations_id_seq" start 3 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"open_position_locations_id_seq"');
CREATE SEQUENCE "open_positions_num_seq" start 9 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"open_positions_num_seq"');
CREATE TABLE "products" (
        "brand" character varying(200),
        "form" character varying(200),
        "generic" character varying(200),
        "description" text,
        "type" character varying(4),
        "atc" text,
        "town" text,
        "smpc" int4,
        "have_smpc" bool DEFAULT 'f',
        "tm" text,
        CONSTRAINT "products_brand" CHECK (((brand <> ''::"varchar") AND (brand NOTNULL)))
);
CREATE TABLE "menu" (
        "section" text,
        "item" text,
        "url" text,
        "file" text,
        "num" int4 DEFAULT 0,
        "visible" bool
);
CREATE TABLE "worldwide" (
        "num" int4,
        "name" text,
        "type" text,
        "note" text,
        "street" text,
        "town" text,
        "country" text,
        "phone" text,
        "fax" text,
        "email" text,
        "website" text
);
CREATE TABLE "sections" (
        "id" int4 DEFAULT nextval('sections_id_seq'::text) NOT NULL,
        "section" float8,
        "title" text
);
CREATE TABLE "paragraphs" (
        "id" int4 DEFAULT nextval('paragraphs_id_seq'::text) NOT NULL,
        "smpc" int4 NOT NULL,
        "section_id" int4 NOT NULL,
        "html" bool,
        "body" text
);
CREATE TABLE "emails" (
        "referer" text,
        "email" text
);
CREATE TABLE "history" (
        "num" int4 NOT NULL,
        "year" character varying(100),
        "description" text
);
CREATE TABLE "countries" (
        "iso" character varying(2),
        "name" text
);
CREATE TABLE "stocks" (
        "date" timestamp,
        "type" character varying(10),
        "current" float8,
        "change" float8,
        "high" float8,
        "low" float8,
        "open" float8,
        "insert_date" timestamp DEFAULT now()
);
CREATE TABLE "vet_type" (
        "type" character varying(2) NOT NULL,
        "description" text
);
CREATE TABLE "vet_animal" (
        "animal" character varying(2) NOT NULL,
        "name" text
);
CREATE TABLE "vet_products" (
        "brand" text,
        "form" text,
        "composition" text,
        "description" text,
        "smpc" int4 NOT NULL,
        "type" character varying(2) NOT NULL,
        "have_smpc" bool,
        CONSTRAINT "vet_products_brand" CHECK (((brand <> ''::text) AND (brand NOTNULL)))
);
CREATE TABLE "vet_animal_product" (
        "animal" character varying(2) NOT NULL,
        "smpc" int4 NOT NULL
);
CREATE TABLE "wap_ua" (
        "ua" text,
        "referer" text,
        "url" text,
        "accept" text,
        "date" timestamp DEFAULT now()
);
CREATE TABLE "prod_type" (
        "type" character varying(4) NOT NULL,
        "description" text
);
CREATE TABLE "analysts" (
        "num" int4 DEFAULT nextval('analysts_num_seq'::text) NOT NULL,
        "company" text,
        "analyst" text,
        "email" text
);
CREATE TABLE "stocks_order" (
        "type" character varying(10) NOT NULL,
        "ordinal" int4 NOT NULL
);
CREATE TABLE "open_position_locations" (
        "id" int4 DEFAULT nextval('open_position_locations_id_seq'::text) NOT NULL,
        "location" character varying(250),
        "email" character varying(250)
);
CREATE TABLE "open_positions" (
        "num" int4 DEFAULT nextval('open_positions_num_seq'::text) NOT NULL,
        "title" text,
        "job_code" text,
        "department" text,
        "location_id" int4,
        "description" text,
        "edu_req" text,
        "qual_req" text,
        "visible" bool DEFAULT 'f',
        "expiration" date NOT NULL
);
CREATE TABLE "news" (
        "id" int4 DEFAULT nextval('news_id_seq'::text) NOT NULL,
        "title" text NOT NULL,
        "town_date" text,
        "body" text,
        "more" text,
        "pdf" text,
        "date" date,
        "type" character,
        "pic" character varying(20),
        "origpic" character varying(20),
        "visible" bool DEFAULT 't',
        "priority" int4 DEFAULT 0,
        CONSTRAINT "news_type" CHECK (((((((("type" = 'n'::bpchar) OR ("type" = 'c'::bpchar)) OR ("type" = 'e'::bpchar)) OR ("type" = 'r'::bpchar)) OR ("type" = 'i'::bpchar)) OR ("type" = 'a'::bpchar)) OR ("type" = 'p'::bpchar)))
);
\connect - postgres
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
\connect - dpavlin
CREATE  INDEX "ind_sections_section" on "sections" using btree ( "section" "float8_ops" );
CREATE  INDEX "ind_paragraphs_smpc" on "paragraphs" using btree ( "smpc" "int4_ops" );
CREATE  INDEX "ind_paragraphs_section_id" on "paragraphs" using btree ( "section_id" "int4_ops" );
CREATE UNIQUE INDEX "analysts_num_key" on "analysts" using btree ( "num" "int4_ops" );
CREATE  INDEX "ind_stocks_order_type" on "stocks_order" using btree ( "type" "varchar_ops" );
CREATE UNIQUE INDEX "sections_id_key" on "sections" using btree ( "id" "int4_ops" );
CREATE UNIQUE INDEX "paragraphs_id_key" on "paragraphs" using btree ( "id" "int4_ops" );
CREATE  INDEX "ind_products_smpc" on "products" using btree ( "smpc" "int4_ops" );
CREATE  INDEX "ind_history_num" on "history" using btree ( "num" "int4_ops" );
CREATE  INDEX "ind_stocks_order_ordinal" on "stocks_order" using btree ( "ordinal" "int4_ops" );
CREATE UNIQUE INDEX "open_position_locations_id_key" on "open_position_locations" using btree ( "id" "int4_ops" );
CREATE UNIQUE INDEX "ind_countries_iso" on "countries" using btree ( "iso" "varchar_ops" );
CREATE  INDEX "ind_stocks_type" on "stocks" using btree ( "type" "varchar_ops" );
CREATE  INDEX "ind_stocks_date" on "stocks" using btree ( "date" "timestamp_ops" );
CREATE UNIQUE INDEX "ind_vet_type_type" on "vet_type" using btree ( "type" "varchar_ops" );
CREATE UNIQUE INDEX "ind_vet_animal_animal" on "vet_animal" using btree ( "animal" "varchar_ops" );
CREATE UNIQUE INDEX "menu_num" on "menu" using btree ( "num" "int4_ops" );
CREATE UNIQUE INDEX "open_positions_num_key" on "open_positions" using btree ( "num" "int4_ops" );
1	dpavlin	1.1	\connect - dpavlin
2	dpavlin	1.2	CREATE SEQUENCE "news_id_seq" start 3297 increment 1 maxvalue 2147483647 minvalue 1 cache 51 ;
3			SELECT nextval ('"news_id_seq"');
4			CREATE SEQUENCE "sections_id_seq" start 67 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
5			SELECT nextval ('"sections_id_seq"');
6			CREATE SEQUENCE "paragraphs_id_seq" start 3536 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
7			SELECT nextval ('"paragraphs_id_seq"');
8			CREATE SEQUENCE "community_picseq" start 3 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
9			SELECT nextval ('"community_picseq"');
10			CREATE SEQUENCE "picseq" start 5 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
11			SELECT nextval ('"picseq"');
12			CREATE SEQUENCE "analysts_num_seq" start 20 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
13			SELECT nextval ('"analysts_num_seq"');
14			CREATE SEQUENCE "open_position_locations_id_seq" start 3 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
15			SELECT nextval ('"open_position_locations_id_seq"');
16			CREATE SEQUENCE "open_positions_num_seq" start 9 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
17			SELECT nextval ('"open_positions_num_seq"');
18	dpavlin	1.1	CREATE TABLE "products" (
19			"brand" character varying(200),
20			"form" character varying(200),
21			"generic" character varying(200),
22			"description" text,
23			"type" character varying(4),
24			"atc" text,
25			"town" text,
26	dpavlin	1.2	"smpc" int4,
27			"have_smpc" bool DEFAULT 'f',
28	dpavlin	1.1	"tm" text,
29			CONSTRAINT "products_brand" CHECK (((brand <> ''::"varchar") AND (brand NOTNULL)))
30			);
31			CREATE TABLE "menu" (
32			"section" text,
33			"item" text,
34			"url" text,
35			"file" text,
36	dpavlin	1.2	"num" int4 DEFAULT 0,
37			"visible" bool
38	dpavlin	1.1	);
39			CREATE TABLE "worldwide" (
40	dpavlin	1.2	"num" int4,
41	dpavlin	1.1	"name" text,
42			"type" text,
43			"note" text,
44			"street" text,
45			"town" text,
46			"country" text,
47			"phone" text,
48			"fax" text,
49			"email" text,
50			"website" text
51			);
52			CREATE TABLE "sections" (
53	dpavlin	1.2	"id" int4 DEFAULT nextval('sections_id_seq'::text) NOT NULL,
54			"section" float8,
55	dpavlin	1.1	"title" text
56			);
57			CREATE TABLE "paragraphs" (
58	dpavlin	1.2	"id" int4 DEFAULT nextval('paragraphs_id_seq'::text) NOT NULL,
59			"smpc" int4 NOT NULL,
60			"section_id" int4 NOT NULL,
61			"html" bool,
62	dpavlin	1.1	"body" text
63			);
64			CREATE TABLE "emails" (
65			"referer" text,
66			"email" text
67			);
68			CREATE TABLE "history" (
69	dpavlin	1.2	"num" int4 NOT NULL,
70	dpavlin	1.1	"year" character varying(100),
71			"description" text
72			);
73			CREATE TABLE "countries" (
74			"iso" character varying(2),
75			"name" text
76			);
77			CREATE TABLE "stocks" (
78	dpavlin	1.2	"date" timestamp,
79	dpavlin	1.1	"type" character varying(10),
80	dpavlin	1.2	"current" float8,
81			"change" float8,
82			"high" float8,
83			"low" float8,
84			"open" float8,
85			"insert_date" timestamp DEFAULT now()
86			);
87	dpavlin	1.1	CREATE TABLE "vet_type" (
88			"type" character varying(2) NOT NULL,
89			"description" text
90			);
91			CREATE TABLE "vet_animal" (
92			"animal" character varying(2) NOT NULL,
93			"name" text
94			);
95			CREATE TABLE "vet_products" (
96			"brand" text,
97			"form" text,
98			"composition" text,
99			"description" text,
100	dpavlin	1.2	"smpc" int4 NOT NULL,
101	dpavlin	1.1	"type" character varying(2) NOT NULL,
102	dpavlin	1.2	"have_smpc" bool,
103	dpavlin	1.1	CONSTRAINT "vet_products_brand" CHECK (((brand <> ''::text) AND (brand NOTNULL)))
104			);
105			CREATE TABLE "vet_animal_product" (
106			"animal" character varying(2) NOT NULL,
107	dpavlin	1.2	"smpc" int4 NOT NULL
108	dpavlin	1.1	);
109			CREATE TABLE "wap_ua" (
110			"ua" text,
111			"referer" text,
112			"url" text,
113			"accept" text,
114	dpavlin	1.2	"date" timestamp DEFAULT now()
115	dpavlin	1.1	);
116			CREATE TABLE "prod_type" (
117			"type" character varying(4) NOT NULL,
118			"description" text
119			);
120			CREATE TABLE "analysts" (
121	dpavlin	1.2	"num" int4 DEFAULT nextval('analysts_num_seq'::text) NOT NULL,
122	dpavlin	1.1	"company" text,
123			"analyst" text,
124			"email" text
125			);
126			CREATE TABLE "stocks_order" (
127			"type" character varying(10) NOT NULL,
128	dpavlin	1.2	"ordinal" int4 NOT NULL
129	dpavlin	1.1	);
130			CREATE TABLE "open_position_locations" (
131	dpavlin	1.2	"id" int4 DEFAULT nextval('open_position_locations_id_seq'::text) NOT NULL,
132	dpavlin	1.1	"location" character varying(250),
133			"email" character varying(250)
134			);
135			CREATE TABLE "open_positions" (
136	dpavlin	1.2	"num" int4 DEFAULT nextval('open_positions_num_seq'::text) NOT NULL,
137	dpavlin	1.1	"title" text,
138			"job_code" text,
139			"department" text,
140	dpavlin	1.2	"location_id" int4,
141	dpavlin	1.1	"description" text,
142			"edu_req" text,
143			"qual_req" text,
144	dpavlin	1.2	"visible" bool DEFAULT 'f',
145	dpavlin	1.1	"expiration" date NOT NULL
146			);
147			CREATE TABLE "news" (
148	dpavlin	1.2	"id" int4 DEFAULT nextval('news_id_seq'::text) NOT NULL,
149	dpavlin	1.1	"title" text NOT NULL,
150			"town_date" text,
151			"body" text,
152			"more" text,
153			"pdf" text,
154			"date" date,
155	dpavlin	1.2	"type" character,
156	dpavlin	1.1	"pic" character varying(20),
157			"origpic" character varying(20),
158	dpavlin	1.2	"visible" bool DEFAULT 't',
159			"priority" int4 DEFAULT 0,
160	dpavlin	1.1	CONSTRAINT "news_type" CHECK (((((((("type" = 'n'::bpchar) OR ("type" = 'c'::bpchar)) OR ("type" = 'e'::bpchar)) OR ("type" = 'r'::bpchar)) OR ("type" = 'i'::bpchar)) OR ("type" = 'a'::bpchar)) OR ("type" = 'p'::bpchar)))
161			);
162	dpavlin	1.2	\connect - postgres
163			CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C';
164			CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
165			\connect - dpavlin
166	dpavlin	1.1	CREATE INDEX "ind_sections_section" on "sections" using btree ( "section" "float8_ops" );
167			CREATE INDEX "ind_paragraphs_smpc" on "paragraphs" using btree ( "smpc" "int4_ops" );
168			CREATE INDEX "ind_paragraphs_section_id" on "paragraphs" using btree ( "section_id" "int4_ops" );
169	dpavlin	1.2	CREATE UNIQUE INDEX "analysts_num_key" on "analysts" using btree ( "num" "int4_ops" );
170			CREATE INDEX "ind_stocks_order_type" on "stocks_order" using btree ( "type" "varchar_ops" );
171			CREATE UNIQUE INDEX "sections_id_key" on "sections" using btree ( "id" "int4_ops" );
172	dpavlin	1.1	CREATE UNIQUE INDEX "paragraphs_id_key" on "paragraphs" using btree ( "id" "int4_ops" );
173	dpavlin	1.2	CREATE INDEX "ind_products_smpc" on "products" using btree ( "smpc" "int4_ops" );
174	dpavlin	1.1	CREATE INDEX "ind_history_num" on "history" using btree ( "num" "int4_ops" );
175	dpavlin	1.2	CREATE INDEX "ind_stocks_order_ordinal" on "stocks_order" using btree ( "ordinal" "int4_ops" );
176			CREATE UNIQUE INDEX "open_position_locations_id_key" on "open_position_locations" using btree ( "id" "int4_ops" );
177	dpavlin	1.1	CREATE UNIQUE INDEX "ind_countries_iso" on "countries" using btree ( "iso" "varchar_ops" );
178			CREATE INDEX "ind_stocks_type" on "stocks" using btree ( "type" "varchar_ops" );
179			CREATE INDEX "ind_stocks_date" on "stocks" using btree ( "date" "timestamp_ops" );
180			CREATE UNIQUE INDEX "ind_vet_type_type" on "vet_type" using btree ( "type" "varchar_ops" );
181			CREATE UNIQUE INDEX "ind_vet_animal_animal" on "vet_animal" using btree ( "animal" "varchar_ops" );
182	dpavlin	1.2	CREATE UNIQUE INDEX "menu_num" on "menu" using btree ( "num" "int4_ops" );
183	dpavlin	1.1	CREATE UNIQUE INDEX "open_positions_num_key" on "open_positions" using btree ( "num" "int4_ops" );