wLUrwwTQ

1--Migração
2drop table if exists application_histories;
3drop table if exists application_values_histories;
4DROP FUNCTION IF EXISTS on_update_current_timestamp_collected_at();
5DROP FUNCTION IF EXISTS on_update_current_timestamp_created_at();
6--drop trigger on_update_current_timestamp on application_values;
7--drop trigger on_update_current_timestamp on collects;
8--drop trigger on_update_current_timestamp on applications;
9
10ALTER TABLE satellite_image_fields ALTER COLUMN sat_image_id type bigint;
11ALTER TABLE satellite_image_fields ADD CONSTRAINT satellite_image_fields_sat_image_id_foreign FOREIGN KEY(sat_image_id) REFERENCES satellite_images(id) ON UPDATE RESTRICT ON DELETE CASCADE;
12
13ALTER TABLE messages_users ALTER COLUMN message_id type bigint;
14ALTER TABLE messages_users ADD CONSTRAINT messages_users_message_id_foreign FOREIGN KEY(message_id) REFERENCES messages(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
15
16ALTER TABLE critical_areas alter column satellite_image_id type bigint;
17ALTER TABLE critical_areas ADD CONSTRAINT satellite_images_satellite_image_id_foreign FOREIGN KEY(satellite_image_id) REFERENCES satellite_images(id) ON UPDATE RESTRICT ON DELETE CASCADE;
18
19--Queries de geração 
20select 'ALTER TABLE ' || table_name || ' alter column ' || column_name || ' type timestamp(0) without time zone;'
21from information_schema.columns
22where table_catalog = 'geodataprod'
23and table_schema = 'public'
24and data_type like 'timestamp%';
25
26select 'drop index ' || indexname || ';' FROM pg_indexes WHERE schemaname = 'public' and indexname not like '%primary';
27select 'alter index ' || indexname || ' rename to ' || tablename || '_pkey;' FROM pg_indexes WHERE schemaname = 'public' and indexname like '%primary';
28
29--mudança na foreign de application_values
30ALTER TABLE application_values DROP CONSTRAINT IF EXISTS application_values_field_id_foreign;
31
32ALTER TABLE application_values ADD CONSTRAINT application_values_field_id_foreign FOREIGN KEY (field_id)
33      REFERENCES fields (id) MATCH SIMPLE
34      ON UPDATE RESTRICT ON DELETE CASCADE;
35
36ALTER TABLE customers add CONSTRAINT customers_cnpj_unique UNIQUE (cnpj);
37ALTER TABLE customers add CONSTRAINT customers_cpf_unique UNIQUE (cpf);
38ALTER TABLE collection_profiles add CONSTRAINT collection_profiles_priority_unique UNIQUE (priority);
39
40ALTER TABLE users ADD COLUMN confirmed_at timestamp(0) without time zone;
41update users set confirmed_at = now();
42
43--Relatórios de fertilidade
44CREATE TABLE fertility_config (
45  id serial,
46  name character varying(150) NOT NULL,
47  created_at timestamp without time zone,
48  updated_at timestamp without time zone,
49  CONSTRAINT fertility_config_pkey PRIMARY KEY (id)
50)
51WITH (
52  OIDS=FALSE
53);
54
55CREATE TABLE fertility_ranges (
56  id serial,
57  element_id integer NOT NULL,
58  fertility_config_id integer NOT NULL,
59  min double precision NOT NULL,
60  max double precision NOT NULL,
61  value double precision NOT NULL,
62  gradient_type integer NOT NULL,
63  created_at timestamp without time zone,
64  updated_at timestamp without time zone,
65  CONSTRAINT fertility_ranges_pkey PRIMARY KEY (id),
66  CONSTRAINT fertility_ranges_element_id_foreign FOREIGN KEY (element_id)
67      REFERENCES elements (id) MATCH SIMPLE
68      ON UPDATE NO ACTION ON DELETE NO ACTION,
69  CONSTRAINT fertility_ranges_fertility_config_id_foreign FOREIGN KEY (fertility_config_id)
70      REFERENCES fertility_config (id) MATCH SIMPLE
71      ON UPDATE NO ACTION ON DELETE NO ACTION
72)
73WITH (
74  OIDS=FALSE
75);
76
77
78ALTER TABLE fertility_ranges DROP CONSTRAINT fertility_ranges_fertility_config_id_foreign;
79ALTER TABLE fertility_ranges ADD CONSTRAINT fertility_ranges_fertility_config_id_foreign FOREIGN KEY (fertility_config_id) REFERENCES fertility_config (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;
80
81ALTER TABLE crops ADD fertility_config_id integer NULL;
82ALTER TABLE crops DROP CONSTRAINT IF EXISTS crops_fertility_config_id_foreign;
83ALTER TABLE crops ADD CONSTRAINT crops_fertility_config_id_foreign FOREIGN KEY (fertility_config_id) REFERENCES fertility_config (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;
84ALTER TABLE customers alter column logo_url type varchar(250);
85
86insert into notification_type (id, name) values (8, 'critical_areas');
87
88ALTER TABLE critical_areas ADD COLUMN area_breaks varchar(250);
89
90ALTER TABLE fertility_config ALTER COLUMN created_at TYPE timestamp(0) without time zone;
91ALTER TABLE fertility_config ALTER COLUMN updated_at TYPE timestamp(0) without time zone;
92
93--Inlui uma nova coluna na tabela de produtos a fim de categorizar os produtos
94ALTER TABLE products ADD product_type_id INT NULL;
95
96insert into notification_type (id, name) values (8, 'critical_areas');
97
98ALTER TABLE critical_areas ADD COLUMN area_breaks varchar(250);