mVET21J4

1DROP TABLE IF EXISTS Country CASCADE;
2CREATE TABLE Country (
3 
4    country_code varchar(255),
5    country varchar(255),
6    PRIMARY KEY (country_code)
7);
8
9DROP TABLE IF EXISTS Payment CASCADE;
10CREATE TABLE Payment (
11 
12    id_payment serial,
13    price money,
14    weekly_price money,
15    monthly_price money,
16    security_deposit money,
17    cleaning_fee money,
18    PRIMARY KEY (id_payment)
19);
20
21DROP TABLE IF EXISTS Characteristic CASCADE;
22CREATE TABLE Characteristic (
23 
24    id_characteristic serial,
25    property_type varchar(255),
26    accommodates INT,
27    bathrooms REAL,
28    bedrooms INT,
29    beds INT,
30    square_feet INT,
31    minimum_nights INT,
32    maximum_nights INT,
33	amenities text,
34    PRIMARY KEY (id_characteristic)
35);
36
37DROP TABLE IF EXISTS Host CASCADE;
38CREATE TABLE Host (
39 
40    host_id serial,
41    host_url varchar(255),
42    host_name varchar(255),
43    host_since DATE,
44    host_about TEXT,
45    host_response_time varchar(255),
46    host_response_rate varchar(255),
47    host_is_superhost BOOLEAN,
48    host_picture_url varchar(255),
49    host_listings_count INT,
50    host_identity_verified BOOLEAN,
51    PRIMARY KEY (host_id)
52);
53
54DROP TABLE IF EXISTS Media CASCADE;
55CREATE TABLE Media (
56 
57    id_verification serial,
58    media varchar(255),
59    PRIMARY KEY (id_verification)
60);
61 
62DROP TABLE IF EXISTS HostMedia CASCADE;
63CREATE TABLE HostMedia (
64 
65    host_id INT,
66    id_verification INT,
67    PRIMARY key(host_id, id_verification),
68    FOREIGN KEY (host_id) REFERENCES Host (host_id),
69    FOREIGN KEY (id_verification) REFERENCES Media (id_verification)
70);
71
72DROP TABLE IF EXISTS Reviewer CASCADE;
73CREATE TABLE Reviewer (
74 
75    id_reviewer serial,
76	aux_id int,
77    reviewer_name varchar(255),
78    PRIMARY KEY (id_reviewer)
79);
80 
81DROP TABLE IF EXISTS Amenitie CASCADE;
82CREATE TABLE Amenitie (
83 
84    id_amenitie serial,
85    amenitie varchar(255),
86    PRIMARY KEY (id_amenitie)
87);
88 
89DROP TABLE IF EXISTS CharacteristicAmenitie CASCADE;
90CREATE TABLE CharacteristicAmenitie (
91 
92    id_amenitie INT,
93    id_characteristic INT,
94    PRIMARY KEY (id_amenitie, id_characteristic),
95    FOREIGN KEY (id_amenitie) REFERENCES Amenitie (id_amenitie),
96    FOREIGN KEY (id_characteristic) REFERENCES Characteristic (id_characteristic)
97);
98
99DROP TABLE IF EXISTS Place CASCADE;
100CREATE TABLE Place (
101 
102    id_place serial,
103    street varchar(255),
104    neighbourhood varchar(255),
105    city varchar(255),
106    state varchar(255),
107    zipcode varchar(255),
108    country_code varchar(255),
109    PRIMARY KEY (id_place),
110    FOREIGN KEY (country_code) REFERENCES Country (country_code)
111);
112 
113DROP TABLE IF EXISTS Apartment CASCADE;
114CREATE TABLE Apartment (
115 
116    id_apartment serial,
117    url varchar(255),
118    NAME varchar(255),
119    description TEXT,
120    picture_url varchar(255),
121    id_place INT,
122    id_payment INT,
123    id_characteristic INT,
124    host_id INT,
125    PRIMARY KEY (id_apartment),
126    FOREIGN KEY (id_place) REFERENCES Place (id_place),
127    FOREIGN KEY (id_payment) REFERENCES Payment (id_payment),
128    FOREIGN KEY (id_characteristic) REFERENCES Characteristic (id_characteristic),
129    FOREIGN KEY (host_id) REFERENCES Host (host_id)
130);
131 
132DROP TABLE IF EXISTS Review CASCADE;
133CREATE TABLE Review (
134 
135    id_review serial,
136    id_reviewer INT,
137    id_apartment INT,
138    date_review DATE,
139    comments TEXT,
140    PRIMARY KEY (id_review),
141    FOREIGN KEY (id_reviewer) REFERENCES Reviewer (id_reviewer),
142    FOREIGN KEY (id_apartment) REFERENCES Apartment (id_apartment)
143);
144 
145 
146
147
148DROP TABLE IF EXISTS ImportA;
149CREATE TABLE ImportA (
150 
151    id varchar(255),
152    url varchar(255),
153    NAME varchar(255),
154    description TEXT,
155    picture_url varchar(255),
156    street varchar(255),
157    neighbourhood varchar(255),
158    city varchar(255),
159    state varchar(255),
160    zipcode varchar(255),
161    country_code varchar(255),
162    country varchar(255),
163    property_type varchar(255),
164    accommodates varchar(255),
165    bathrooms varchar(255),
166    bedrooms varchar(255),
167    beds varchar(255),
168    amenities TEXT,
169    square_feet varchar(255),
170    price varchar(255),
171    weekly_price varchar(255),
172    monthly_price varchar(255),
173    security_deposit varchar(255),
174    cleaning_fee varchar(255),
175    minimum_nights varchar(255),
176    maximum_nights varchar(255)
177);
178 
179DROP TABLE IF EXISTS ImportH;
180CREATE TABLE ImportH (
181 
182    url varchar(255),
183    NAME varchar(255),
184    description TEXT,
185    picture_url varchar(255),
186    host_id  varchar(255),
187    host_url varchar(255),
188    host_name varchar(255),
189    host_since varchar(255),
190    host_about TEXT,
191    host_response_time varchar(255),
192    host_response_rate varchar(255),
193    host_is_superhost varchar(255),
194    host_picture_url varchar(255),
195    host_listings_count varchar(255),
196    host_verifications varchar(255),
197    host_identity_verified varchar(255)
198);
199 
200DROP TABLE IF EXISTS ImportR;
201CREATE TABLE ImportR (
202 
203    id varchar(255),
204    url varchar(255),
205    NAME varchar(255),
206    description TEXT,
207    picture_url varchar(255),
208    street varchar(255),
209    neighbourhood varchar(255),
210    city varchar(255),
211    date_review varchar(255),
212    reviewer_id varchar(255),
213    reviewer_name varchar(255),
214    comments TEXT
215);
216 
217COPY ImportA FROM '/Users/Shared/BDD/apartments.csv' CSV HEADER DELIMITER ',';
218COPY ImportH FROM '/Users/Shared/BDD/hosts.csv' CSV HEADER DELIMITER ',';
219COPY ImportR FROM '/Users/Shared/BDD/review.csv' CSV HEADER DELIMITER ',';
220
221
222
223
224INSERT INTO Country (country_code, country)
225SELECT DISTINCT country_code, country
226FROM ImportA;
227
228INSERT INTO Payment (price, weekly_price, monthly_price, security_deposit, cleaning_fee)
229SELECT DISTINCT cast(price AS money), cast(weekly_price AS money), cast(monthly_price AS money), cast(security_deposit AS money), cast(cleaning_fee AS money)
230FROM ImportA;
231 
232INSERT INTO Characteristic (property_type, accommodates, bathrooms, bedrooms, beds, square_feet, minimum_nights, maximum_nights, amenities)
233SELECT DISTINCT property_type, cast(accommodates AS INT), cast(bathrooms AS REAL), cast(bedrooms AS INT), cast(beds AS INT), cast(square_feet AS INT), cast(minimum_nights AS INT), cast(maximum_nights AS INT), cast(amenities as text)
234FROM ImportA;
235 
236INSERT INTO Host (host_url, host_name, host_since, host_about, host_response_time, host_response_rate, host_is_superhost, host_picture_url, host_listings_count, host_identity_verified)
237SELECT DISTINCT host_url, host_name, cast(host_since AS DATE), cast(host_about AS TEXT), host_response_time, host_response_rate, cast(host_is_superhost AS BOOLEAN), host_picture_url, cast(host_listings_count AS INT), cast(host_identity_verified AS BOOLEAN)
238FROM ImportH;
239 
240INSERT INTO Place (street, neighbourhood, city, state, zipcode, country_code)
241SELECT DISTINCT I.street, I.neighbourhood, I.city, I.state, I.zipcode, C.country_code
242FROM ImportA AS I, Country AS C;
243 
244INSERT INTO Reviewer (reviewer_name, aux_id)
245SELECT DISTINCT reviewer_name, cast(reviewer_id as int)
246FROM ImportR;
247
248insert into Amenitie (amenitie)
249select distinct (trim ('{""}' FROM REGEXP_SPLIT_TO_TABLE(amenities, ','))) 
250from ImportA;
251
252insert into Apartment (url, name, description, picture_url, id_place, id_payment, id_characteristic, host_id)
253select q.url, q.name, cast(q.description as text), q.picture_url, q.id_place, q.id_payment, q.id_characteristic, q.host_id
254from (
255select a.url, a.name, a.description, a.picture_url, Host.host_id, pl.id_place, p.id_payment, c.id_characteristic
256from ImportA as a, ImportH as h, Host, Payment as p, Place as pl, Characteristic as c
257where Host.host_url = h.host_url and h.url = a.url
258and pl.street is not distinct from a.street and pl.neighbourhood is not distinct from a.neighbourhood
259and pl.city is not distinct from a.city and pl.state is not distinct from a.state and pl.zipcode is not distinct from a.zipcode
260and pl.country_code is not distinct from a.country_code
261and p.price is not distinct from cast(a.price as money) and p.weekly_price is not distinct from cast(a.weekly_price as money)
262and p.monthly_price is not distinct from cast(a.monthly_price as money) 
263and p.security_deposit is not distinct from cast(a.security_deposit as money)
264and p.cleaning_fee is not distinct from cast(a.cleaning_fee as money)
265and c.property_type is not distinct from a.property_type and c.accommodates is not distinct from cast(a.accommodates as int)
266and c.bathrooms is not distinct from cast(a.bathrooms as real) and c.bedrooms is not distinct from cast(a.bedrooms as int)
267and c.beds is not distinct from cast(a.beds as int) and c.square_feet is not distinct from cast(a.square_feet as int)
268and c.minimum_nights is not distinct from cast(a.minimum_nights as int) 
269and c.maximum_nights is not distinct from cast(a.maximum_nights as int) and c.amenities is not distinct from cast(a.amenities as text)
270) as q;
271
272alter table Characteristic drop column amenities;
273
274insert into Media (media)
275select distinct q3.otp
276from (
277select distinct replace(q2.aux2, ' ', '') as otp
278from (select distinct (trim('[]' from REGEXP_SPLIT_TO_TABLE(q.aux, ','))) as aux2 
279from (select replace(host_verifications, '''', '') as aux from ImportH) as q) as q2
280) as q3;
281
282insert into CharacteristicAmenitie (id_amenitie, id_characteristic)
283select distinct q2.id_amenitie, q2.id_characteristic 
284from (
285select a.id_amenitie, ap.id_characteristic from Amenitie as a, Apartment as ap,
286(select distinct (trim ('{""}' FROM REGEXP_SPLIT_TO_TABLE(amenities, ','))) as amenitie, url from ImportA) as q
287where ap.url = q.url and a.amenitie = q.amenitie
288) as q2;
289
290insert into HostMedia (host_id, id_verification)
291select distinct qry.host_id, qry.id_verification
292from (
293select h.host_id, m.id_verification from Host as h, Media as m,
294(select distinct replace(q2.aux2, ' ', '') as otp, q2.host_url
295from (select distinct (trim('[]' from REGEXP_SPLIT_TO_TABLE(q.aux, ','))) as aux2, q.host_url 
296from (select replace(host_verifications, '''', '') as aux, host_url from ImportH) as q) as q2) as q3
297where h.host_url = q3.host_url and q3.otp = m.media
298) as qry;
299
300insert into Review (id_reviewer, id_apartment, date_review, comments)
301select distinct q.id_reviewer, q.id_apartment, cast(q.date_review as date), q.comments
302from (
303select r.id_reviewer, a.id_apartment, i.date_review, i.comments 
304from Reviewer as r, Apartment as a, ImportR as i
305where i.url = a.url and r.aux_id = cast(i.reviewer_id as int)
306) as q;
307
308alter table Reviewer drop column aux_id;