· 9 years ago · Oct 09, 2016, 08:46 AM
1
2
3## Database Deployment for Scenario one view
4### Apply Sharding DB changes
5
6```sql
7ALTER TYPE user_search.scenario_category ADD VALUE IF NOT EXISTS 'misc';
8```
9
10### Apply Core DB changes
11
12```sql
13ALTER TYPE scenario_category ADD VALUE IF NOT EXISTS 'misc';
14CREATE TYPE scenario_tag AS ENUM ('category/food', 'category/sport', 'category/movie');
15ALTER TABLE scenarios ADD COLUMN tag scenario_tag;
16ALTER TYPE scenario ADD ATTRIBUTE tag scenario_tag;
17
18
19---
20
21CREATE OR REPLACE FUNCTION insert_scenario(
22 category_ scenario_category,
23 picture_identifier_ varchar,
24 picture_width_ integer,
25 picture_height_ integer,
26 index_ integer,
27 status_ status,
28 tag_ scenario_tag
29 ) RETURNS SETOF scenario AS
30$$
31BEGIN
32 RETURN QUERY
33 INSERT INTO scenarios
34 (category, picture_identifier, picture_width, picture_height, index, status, tag)
35 VALUES
36 (category_, picture_identifier_, picture_width_, picture_height_, index_, status_, tag_)
37 RETURNING
38 id, null::varchar(1000) as name, null::translation_language as language, category,
39 picture_identifier, picture_width, picture_height,
40 index, created_time, updated_time, status, tag;
41END;
42$$ LANGUAGE PLPGSQL;
43
44
45CREATE OR REPLACE FUNCTION update_scenario(
46 id_ integer,
47 category_ scenario_category,
48 picture_identifier_ varchar,
49 picture_width_ integer,
50 picture_height_ integer,
51 index_ integer,
52 status_ status,
53 tag_ scenario_tag
54 ) RETURNS SETOF scenario AS
55$$
56BEGIN
57 UPDATE
58 scenarios
59 SET
60 category = category_,
61 picture_identifier = picture_identifier_,
62 picture_width = picture_width_,
63 picture_height = picture_height_,
64 index = index_,
65 status = status_,
66 tag = tag_
67 WHERE
68 id = id_;
69 RETURN QUERY SELECT * FROM select_scenarios_by_ids(ARRAY[id_]::integer[], 'en-US'::translation_language);
70END;
71$$ LANGUAGE PLPGSQL;
72
73
74
75----
76
77
78CREATE OR REPLACE FUNCTION select_scenarios(category_ scenario_category, language_ translation_language) RETURNS SETOF scenario AS
79$$
80BEGIN
81 RETURN QUERY SELECT s.id, st.name, st.language, s.category,
82 s.picture_identifier, s.picture_width, s.picture_height,
83 s.index, s.created_time, s.updated_time, s.status, s.tag
84 FROM
85 scenarios AS s, scenario_translations AS st
86 WHERE
87 st.scenario_id = s.id
88 AND
89 s.category = category_
90 AND
91 s.status = 'default'
92 AND
93 st.language = language_
94 ORDER BY
95 s.index ASC, s.id ASC;
96END;
97$$ LANGUAGE PLPGSQL;
98
99
100
101CREATE OR REPLACE FUNCTION select_scenarios_by_ids(scenario_ids_ integer[], language_ translation_language) RETURNS SETOF scenario AS
102$$
103BEGIN
104 RETURN QUERY SELECT s.id, st.name, st.language, s.category,
105 s.picture_identifier, s.picture_width, s.picture_height,
106 s.index, s.created_time, s.updated_time, s.status, s.tag
107 FROM
108 scenarios AS s, scenario_translations AS st
109 WHERE
110 st.scenario_id = s.id
111 AND
112 s.id = ANY(scenario_ids_)
113 AND
114 (s.status = 'default' OR s.status = 'hidden')
115 AND
116 st.language = language_
117 ORDER BY
118 s.index ASC, s.id ASC;
119END;
120$$ LANGUAGE PLPGSQL;
121
122
123CREATE OR REPLACE FUNCTION select_scenarios_for_moderation(
124 category_ scenario_category,
125 status_ status,
126 language_ translation_language,
127 limit_ integer,
128 offset_ integer) RETURNS SETOF scenario AS
129$$
130BEGIN
131 RETURN QUERY SELECT s.id, st.name, st.language, s.category,
132 s.picture_identifier, s.picture_width, s.picture_height,
133 s.index, s.created_time, s.updated_time, s.status, s.tag
134 FROM
135 scenarios AS s, scenario_translations AS st
136 WHERE
137 st.scenario_id = s.id
138 AND
139 s.category = category_
140 AND
141 s.status = status_
142 AND
143 st.language = language_
144 ORDER BY
145 s.index ASC, s.id DESC
146 LIMIT limit_ OFFSET offset_;
147END;
148$$ LANGUAGE PLPGSQL;
149
150
151
152CREATE OR REPLACE FUNCTION insert_scenario(
153 category_ scenario_category,
154 picture_identifier_ varchar,
155 picture_width_ integer,
156 picture_height_ integer,
157 index_ integer,
158 status_ status,
159 tag_ scenario_tag
160 ) RETURNS SETOF scenario AS
161$$
162BEGIN
163 RETURN QUERY
164 INSERT INTO scenarios
165 (category, picture_identifier, picture_width, picture_height, index, status, tag)
166 VALUES
167 (category_, picture_identifier_, picture_width_, picture_height_, index_, status_, tag_)
168 RETURNING
169 id, null::varchar(1000) as name, null::translation_language as language, category,
170 picture_identifier, picture_width, picture_height,
171 index, created_time, updated_time, status, tag;
172END;
173$$ LANGUAGE PLPGSQL;
174
175----
176
177CREATE OR REPLACE FUNCTION update_scenario(
178 id_ integer,
179 category_ scenario_category,
180 picture_identifier_ varchar,
181 picture_width_ integer,
182 picture_height_ integer,
183 index_ integer,
184 status_ status,
185 tag_ scenario_tag
186 ) RETURNS SETOF scenario AS
187$$
188BEGIN
189 UPDATE
190 scenarios
191 SET
192 category = category_,
193 picture_identifier = picture_identifier_,
194 picture_width = picture_width_,
195 picture_height = picture_height_,
196 index = index_,
197 status = status_,
198 tag = tag_
199 WHERE
200 id = id_;
201 RETURN QUERY SELECT * FROM select_scenarios_by_ids(ARRAY[id_]::integer[], 'en-US'::translation_language);
202END;
203$$ LANGUAGE PLPGSQL;
204
205----
206
207CREATE OR REPLACE FUNCTION insert_scenario_translation(
208 scenario_id_ integer,
209 name_ varchar,
210 language_ translation_language) RETURNS SETOF scenario AS
211$$
212BEGIN
213 INSERT INTO scenario_translations
214 (scenario_id, name, language)
215 VALUES
216 (scenario_id_, name_, language_);
217 RETURN QUERY SELECT * FROM select_scenarios_by_ids(ARRAY[scenario_id_]::integer[], language_);
218END;
219$$ LANGUAGE PLPGSQL;
220
221----
222
223CREATE OR REPLACE FUNCTION update_scenario_translation(
224 scenario_id_ integer,
225 name_ varchar,
226 language_ translation_language) RETURNS SETOF scenario AS
227$$
228BEGIN
229 UPDATE
230 scenario_translations
231 SET
232 name = name_
233 WHERE
234 scenario_id = scenario_id_
235 AND
236 language = language_;
237 RETURN QUERY SELECT * FROM select_scenarios_by_ids(ARRAY[scenario_id_]::integer[], language_);
238END;
239$$ LANGUAGE PLPGSQL;
240
241----
242
243CREATE OR REPLACE FUNCTION select_scenarios_by_keyword(
244 keyword_ varchar,
245 status_ status,
246 language_ translation_language,
247 limit_ integer,
248 offset_ integer) RETURNS SETOF scenario AS
249$$
250DECLARE
251 sids integer[];
252BEGIN
253 RETURN QUERY
254 WITH sids AS (
255 SELECT DISTINCT scenario_id FROM scenarios as s, scenario_translations as st
256 WHERE
257 st.scenario_id = s.id
258 AND
259 s.status = status_
260 AND
261 (
262 CASE WHEN keyword_ ~ E'^\\d+$' THEN s.id = keyword_::integer ELSE s.id = 0 END
263 OR
264 st.name like '%' || keyword_ || '%'
265 )
266 ORDER BY
267 scenario_id ASC
268 LIMIT limit_ OFFSET offset_
269 ) SELECT * FROM select_scenarios_by_ids(ARRAY(SELECT * FROM sids), language_);
270END;
271$$ LANGUAGE PLPGSQL;
272
273
274
275
276
277
278
279CREATE OR REPLACE FUNCTION update_user(
280 id_ integer,
281 name_ varchar(50),
282 description_ varchar(1000),
283 industry_ varchar(100),
284 company_ varchar(100),
285 department_ varchar(100),
286 school_ varchar(100),
287 major_ varchar(100),
288 hometown_ varchar(100),
289 hangouts_ varchar(1000),
290 gender_ gender,
291 looking_for_gender_ gender,
292 intent_ intent,
293 search_radius_ integer,
294 search_min_age_ integer,
295 search_max_age_ integer,
296 hide_contacts_ boolean,
297 hide_mutual_contacts_ boolean,
298 email_ varchar(50),
299 country_code_ integer,
300 mobile_number_ varchar(32),
301 password_ varchar(60),
302 birthdate_ date,
303 preview_push_message_ boolean,
304 work_active_ boolean,
305 study_active_ boolean,
306 show_moment_likes_ boolean,
307 scenario_ids_ integer[],
308 scenario_category_ scenario_category,
309 scenario_expires_time_ timestamp) RETURNS SETOF lon_lat_user AS
310$$
311DECLARE
312 r1 users_full%rowtype;
313 llu lon_lat_user;
314 text_columns_updated boolean;
315BEGIN
316
317 /*
318 We split the update in two so that we dont update the text fields unless they really changed
319 */
320 UPDATE users SET
321 name = name_,
322 description = description_,
323 industry = industry_,
324 company = company_,
325 department = department_,
326 school = school_,
327 major = major_,
328 hometown = hometown_,
329 hangouts = hangouts_,
330 gender = gender_,
331 looking_for_gender = looking_for_gender_,
332 intent = intent_,
333 search_radius = search_radius_,
334 search_min_age = search_min_age_,
335 search_max_age = search_max_age_,
336 hide_contacts = hide_contacts_,
337 hide_mutual_contacts = hide_mutual_contacts_,
338 email = safe_email(email_),
339 country_code = country_code_,
340 mobile_number = safe_mobile_number(mobile_number_),
341 birthdate = birthdate_,
342 preview_push_message = preview_push_message_,
343 show_moment_likes = show_moment_likes_,
344 work_active = work_active_,
345 study_active = study_active_,
346 scenario_ids = scenario_ids_,
347 scenario_category = scenario_category_,
348 scenario_expires_time = scenario_expires_time_,
349 updated_time = current_timestamp at time zone 'UTC'
350 WHERE
351 id = id_
352 AND
353 (
354 name != name_
355 OR description != description_
356 OR industry != industry_
357 OR company != company_
358 OR department != department_
359 OR school != school_
360 OR major != major_
361 OR hometown != hometown_
362 OR hangouts != hangouts_
363 OR gender != gender_
364 OR looking_for_gender != looking_for_gender_
365 OR intent != intent_
366 OR search_radius != search_radius_
367 OR search_min_age != search_min_age_
368 OR search_max_age != search_max_age_
369 OR hide_contacts != hide_contacts_
370 OR hide_mutual_contacts != hide_mutual_contacts_
371 OR email != safe_email(email_)
372 OR country_code != country_code_
373 OR mobile_number != safe_mobile_number(mobile_number_)
374 OR birthdate != birthdate_
375 OR preview_push_message != preview_push_message_
376 OR work_active != work_active_
377 OR study_active != study_active_
378 OR show_moment_likes != show_moment_likes_
379 OR COALESCE(scenario_ids, '{}'::integer[]) <> COALESCE(scenario_ids_, '{}'::integer[])
380 OR (scenario_category IS NULL AND scenario_category_ IS NOT NULL)
381 OR
382 (
383 scenario_category IS NOT NULL
384 AND
385 (scenario_category_ IS NULL OR scenario_category != scenario_category_)
386 )
387 OR COALESCE(scenario_expires_time, current_timestamp) != COALESCE(scenario_expires_time_, current_timestamp)
388 );
389
390 SELECT * INTO r1 FROM users_full WHERE id = id_;
391 RETURN NEXT user_to_lon_lat_user(r1, 0);
392END;
393$$ LANGUAGE PLPGSQL;
394
395
396
397CREATE OR REPLACE FUNCTION update_user_with_password(
398 id_ integer,
399 name_ varchar(50),
400 description_ varchar(1000),
401 industry_ varchar(100),
402 company_ varchar(100),
403 department_ varchar(100),
404 school_ varchar(100),
405 major_ varchar(100),
406 hometown_ varchar(100),
407 hangouts_ varchar(1000),
408 gender_ gender,
409 looking_for_gender_ gender,
410 intent_ intent,
411 search_radius_ integer,
412 search_min_age_ integer,
413 search_max_age_ integer,
414 hide_contacts_ boolean,
415 hide_mutual_contacts_ boolean,
416 email_ varchar(50),
417 country_code_ integer,
418 mobile_number_ varchar(32),
419 password_ varchar(60),
420 birthdate_ date,
421 preview_push_message_ boolean,
422 work_active_ boolean,
423 study_active_ boolean,
424 show_moment_likes_ boolean,
425 scenario_ids_ integer[],
426 scenario_category_ scenario_category,
427 scenario_expires_time_ timestamp) RETURNS SETOF lon_lat_user AS
428$$
429DECLARE
430 r1 users_full%rowtype;
431 llu lon_lat_user;
432BEGIN
433 UPDATE users SET
434 name = name_,
435 description = description_,
436 industry = industry_,
437 company = company_,
438 department = department_,
439 school = school_,
440 major = major_,
441 hometown = hometown_,
442 hangouts = hangouts_,
443 gender = gender_,
444 looking_for_gender = looking_for_gender_,
445 intent = intent_,
446 search_radius = search_radius_,
447 search_min_age = search_min_age_,
448 search_max_age = search_max_age_,
449 hide_contacts = hide_contacts_,
450 hide_mutual_contacts = hide_mutual_contacts_,
451 email = safe_email(email_),
452 country_code = country_code_,
453 mobile_number = safe_mobile_number(mobile_number_),
454 password = password_,
455 birthdate = birthdate_,
456 preview_push_message = preview_push_message_,
457 show_moment_likes = show_moment_likes_,
458 work_active = work_active_,
459 study_active = study_active_,
460 scenario_ids = scenario_ids_,
461 scenario_category = scenario_category_,
462 scenario_expires_time = scenario_expires_time_,
463 updated_time = current_timestamp at time zone 'UTC'
464 WHERE
465 id = id_
466 AND
467 (
468 name != name_
469 OR description != description_
470 OR industry != industry_
471 OR company != company_
472 OR department != department_
473 OR school != school_
474 OR major != major_
475 OR hometown != hometown_
476 OR hangouts != hangouts_
477 OR gender != gender_
478 OR looking_for_gender != looking_for_gender_
479 OR intent != intent_
480 OR search_radius != search_radius_
481 OR search_min_age != search_min_age_
482 OR search_max_age != search_max_age_
483 OR hide_contacts != hide_contacts_
484 OR hide_mutual_contacts != hide_mutual_contacts_
485 OR email != safe_email(email_)
486 OR country_code != country_code_
487 OR mobile_number != safe_mobile_number(mobile_number_)
488 OR password != password_
489 OR birthdate != birthdate_
490 OR preview_push_message != preview_push_message_
491 OR work_active != work_active_
492 OR study_active != study_active_
493 OR show_moment_likes != show_moment_likes_
494 OR COALESCE(scenario_ids, '{}'::integer[]) <> COALESCE(scenario_ids_, '{}'::integer[])
495 OR (scenario_category IS NULL AND scenario_category_ IS NOT NULL)
496 OR
497 (
498 scenario_category IS NOT NULL
499 AND
500 (scenario_category_ IS NULL OR scenario_category != scenario_category_)
501 )
502 OR COALESCE(scenario_expires_time, current_timestamp) <> COALESCE(scenario_expires_time_, current_timestamp)
503 );
504
505 SELECT * INTO r1 FROM users_full WHERE id = id_;
506 RETURN NEXT user_to_lon_lat_user(r1, 0);
507END;
508$$ LANGUAGE PLPGSQL;
509
510CREATE OR REPLACE FUNCTION select_scenarios(category_ scenario_category, language_ translation_language) RETURNS SETOF scenario AS
511$$
512BEGIN
513 RETURN QUERY SELECT s.id, st.name, st.language, s.category,
514 s.picture_identifier, s.picture_width, s.picture_height,
515 s.index, s.created_time, s.updated_time, s.status, s.tag
516 FROM
517 scenarios AS s, scenario_translations AS st
518 WHERE
519 st.scenario_id = s.id
520 AND
521 s.category = category_
522 AND
523 s.status = 'default'
524 AND
525 st.language = language_
526 ORDER BY
527 s.index ASC, s.id ASC;
528END;
529$$ LANGUAGE PLPGSQL;
530
531
532CREATE OR REPLACE FUNCTION select_scenarios_for_moderation(
533 category_ scenario_category,
534 status_ status,
535 language_ translation_language,
536 limit_ integer,
537 offset_ integer) RETURNS SETOF scenario AS
538$$
539BEGIN
540 RETURN QUERY SELECT s.id, st.name, st.language, s.category,
541 s.picture_identifier, s.picture_width, s.picture_height,
542 s.index, s.created_time, s.updated_time, s.status, s.tag
543 FROM
544 scenarios AS s, scenario_translations AS st
545 WHERE
546 st.scenario_id = s.id
547 AND
548 s.category = category_
549 AND
550 s.status = status_
551 AND
552 st.language = language_
553 ORDER BY
554 s.index ASC, s.id DESC
555 LIMIT limit_ OFFSET offset_;
556END;
557$$ LANGUAGE PLPGSQL;
558
559
560CREATE OR REPLACE FUNCTION insert_scenario(
561 category_ scenario_category,
562 picture_identifier_ varchar,
563 picture_width_ integer,
564 picture_height_ integer,
565 index_ integer,
566 status_ status,
567 tag_ scenario_tag
568 ) RETURNS SETOF scenario AS
569$$
570BEGIN
571 RETURN QUERY
572 INSERT INTO scenarios
573 (category, picture_identifier, picture_width, picture_height, index, status, tag)
574 VALUES
575 (category_, picture_identifier_, picture_width_, picture_height_, index_, status_, tag_)
576 RETURNING
577 id, null::varchar(1000) as name, null::translation_language as language, category,
578 picture_identifier, picture_width, picture_height,
579 index, created_time, updated_time, status, tag;
580END;
581$$ LANGUAGE PLPGSQL;
582
583
584CREATE OR REPLACE FUNCTION update_scenario(
585 id_ integer,
586 category_ scenario_category,
587 picture_identifier_ varchar,
588 picture_width_ integer,
589 picture_height_ integer,
590 index_ integer,
591 status_ status,
592 tag_ scenario_tag
593 ) RETURNS SETOF scenario AS
594$$
595BEGIN
596 UPDATE
597 scenarios
598 SET
599 category = category_,
600 picture_identifier = picture_identifier_,
601 picture_width = picture_width_,
602 picture_height = picture_height_,
603 index = index_,
604 status = status_,
605 tag = tag_
606 WHERE
607 id = id_;
608 RETURN QUERY SELECT * FROM select_scenarios_by_ids(ARRAY[id_]::integer[], 'en-US'::translation_language);
609END;
610$$ LANGUAGE PLPGSQL;
611
612```