· 7 years ago · Feb 07, 2019, 07:30 PM
1-- First run city_country.sql
2-- Around 8k univers have city_id=0 or country_id=0
3
4set datestyle = DMY;
5
6drop function if exists str_to_date(text);
7create function str_to_date(str text) returns date as $$
8begin
9 return replace(str,'.','/')::date;
10exception
11 when others then
12 return null;
13end;
14$$ language plpgsql;
15
16drop type if exists json_univer1;
17create type json_univer1 as
18(
19 id int, name text, country int, city int
20);
21
22drop type if exists json_univer2;
23create type json_univer2 as
24(
25 id int, graduation int, education_form text,
26 education_status text, faculty int, faculty_name text,
27 chair int, chair_name text
28);
29
30drop table if exists universities cascade;
31create table universities (
32 id integer primary key,
33 title text,
34 country_id integer references dic_country,
35 city_id integer references dic_city
36);
37
38drop table if exists vk_user cascade;
39create table vk_user (
40 id integer primary key,
41 first_name text,
42 last_name text,
43 deactivated text,
44 is_closed boolean,
45 sex integer,
46 bdate date,
47 maiden_name text,
48 city_id integer references dic_city,
49 country_id integer references dic_country,
50 skype text,
51 instagram text,
52 facebook text,
53 livejournal text,
54 twitter text,
55 home_town text,
56 has_photo integer,
57 domain text,
58 has_mobile integer,
59 mobile_phone text,
60 home_phone text,
61 site text,
62 status text,
63 followers_count integer,
64 nickname text,
65 relatives jsonb,
66 relation integer,
67 relation_partner_id integer,
68 political integer,
69 langs text,
70 religion text,
71 inspired_by text,
72 people_main integer,
73 life_main integer,
74 smoking integer,
75 alcohol integer,
76 activities text,
77 interests text,
78 music text,
79 movies text,
80 tv text,
81 books text,
82 games text,
83 about text,
84 quotes text
85);
86
87
88drop table if exists user_to_university;
89create table user_to_university (
90 user_id integer references vk_user,
91 university_id integer references universities,
92 graduation integer,
93 education_form text,
94 education_status text,
95 faculty_id integer,
96 faculty_name text,
97 chair_id int,
98 chair_name text
99);
100create index faculty_id_index on user_to_university (faculty_id);
101create index chair_id_index on user_to_university (chair_id);
102
103
104
105with decode as
106(
107 select
108 (data->>'id')::int as id,
109 (data->>'first_name') as first_name,
110 (data->>'last_name') as last_name,
111 (data->>'deactivated')::text as deactivated,
112 (data->>'is_closed')::boolean as is_closed,
113 (data->>'sex')::int as sex,
114 (data->>'nickname') as nickname,
115 (data->>'maiden_name') as maiden_name,
116 (data->>'domain') as domain,
117 str_to_date((data->>'bdate')) as bdate,
118 (data#>>'{city,id}')::int as city_id,
119 (data#>>'{city,title}') as city_title,
120 (data#>>'{country,id}')::int as country_id,
121 (data#>>'{country,title}') as country_title,
122 (data->>'has_photo')::int as has_photo,
123 (data->>'has_mobile')::int as has_mobile,
124 (data->>'mobile_phone') as mobile_phone,
125 (data->>'home_phone') as home_phone,
126 (data->>'skype') as skype,
127 (data->>'instagram') as instagram,
128 (data->>'facebook') as facebook,
129 (data->>'livejournal') as livejournal,
130 (data->>'twitter') as twitter,
131 (data->>'site') as site,
132 (data->>'status') as status,
133 (data->>'followers_count')::int as followers_count,
134 (data->>'home_town') as home_town,
135 (data->>'relation')::int as relation,
136 (data#>>'{relation_partner,id}')::int as relation_partner_id,
137 (data#>>'{personal,political}')::int as political,
138 (data#>>'{personal,langs}')::text as langs,
139 (data#>>'{personal,religion}') as religion,
140 (data#>>'{personal,inspired_by}') as inspired_by,
141 (data#>>'{personal,people_main}')::int as people_main,
142 (data#>>'{personal,life_main}')::int as life_main,
143 (data#>>'{personal,smoking}')::int as smoking,
144 (data#>>'{personal,alcohol}')::int as alcohol,
145 (data->>'interests') as interests,
146 (data->>'music') as music,
147 (data->>'activities') as activities,
148 (data->>'movies') as movies,
149 (data->>'tv') as tv,
150 (data->>'books') as books,
151 (data->>'games') as games,
152 (data->>'about') as about,
153 (data->'relatives')::jsonb as relatives,
154 (data->>'quotes') as quotes,
155-- JSON
156 (data->'universities')::jsonb as univers_json
157
158 --select * from json_populate_recordset(null::json_univer,(data->'universities')) as univer_json
159 from import.stdin
160),
161
162city as
163(
164 insert into dic_city (id, country_id, title)
165 select city_id, country_id, city_title from decode where city_id is not null on conflict do nothing
166),
167
168vks as
169(
170 insert into vk_user (id, first_name, last_name, deactivated, is_closed, sex,
171 bdate, city_id, country_id, has_photo, has_mobile,
172 mobile_phone, home_phone, skype, instagram, facebook,
173 livejournal, twitter, site, status, followers_count,
174 home_town, relation, relation_partner_id, political,
175 langs, religion, inspired_by, people_main, life_main,
176 smoking, alcohol, interests, music, activities, movies,
177 tv, books, games, about, relatives, quotes)
178 select id, first_name, last_name, deactivated, is_closed, sex,
179 bdate, city_id, country_id, has_photo, has_mobile,
180 mobile_phone, home_phone, skype, instagram, facebook,
181 livejournal, twitter, site, status, followers_count,
182 home_town, relation, relation_partner_id, political,
183 langs, religion, inspired_by, people_main, life_main,
184 smoking, alcohol, interests, music, activities, movies,
185 tv, books, games, about, relatives, quotes
186 from decode
187),
188
189univers as
190(
191 insert into universities (id, title, country_id, city_id)
192 select (jsonb_populate_recordset(null::json_univer1,univers_json)).* from decode
193 on conflict do nothing
194)
195insert into user_to_university (user_id, university_id, graduation, education_form, education_status, faculty_id, faculty_name, chair_id, chair_name)
196 select
197 id,
198 (jsonb_populate_recordset(null::json_univer2,univers_json)).*
199 from decode;