0xRphyTq

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