Created
March 1, 2010 12:52
-
-
Save codesnik/318344 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- PostgreSQL database dump | |
| -- | |
| SET statement_timeout = 0; | |
| SET client_encoding = 'UTF8'; | |
| SET standard_conforming_strings = off; | |
| SET check_function_bodies = false; | |
| SET client_min_messages = warning; | |
| SET escape_string_warning = off; | |
| -- | |
| -- Name: import; Type: SCHEMA; Schema: -; Owner: - | |
| -- | |
| CREATE SCHEMA import; | |
| SET search_path = import, pg_catalog; | |
| -- | |
| -- Name: companies_parse_denormalized_fields(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION companies_parse_denormalized_fields() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| company RECORD; | |
| BEGIN | |
| PERFORM import.log_message('Начинаем заполнение таблиц companies_industries и companies_regions_limits'); | |
| TRUNCATE TABLE import.companies_industries; | |
| TRUNCATE TABLE import.companies_regions_limits; | |
| TRUNCATE TABLE import.companies_regions_bold; | |
| TRUNCATE TABLE import.companies_regions; | |
| FOR company IN SELECT oferta_id AS id, industry_str, region_limits, region_bold FROM import.vw_site_firm LOOP | |
| -- 1. industry_str | |
| IF company.industry_str IS NOT NULL AND LENGTH(company.industry_str) > 0 THEN | |
| -- with ancestors | |
| -- чтобы понять этот запрос, смотри сюда: | |
| -- http://www.postgresql.org/docs/8.4/static/queries-with.html | |
| INSERT INTO import.companies_industries (firm_id, industry_id) | |
| SELECT company.id, b.id | |
| FROM | |
| (SELECT DISTINCT id FROM ( | |
| WITH RECURSIVE search_graph(id, link, depth, path, cycle) AS ( | |
| SELECT g.id, g.parent_id, cached_level, | |
| ARRAY[g.id], | |
| false | |
| FROM industries g | |
| WHERE g.id IN ( | |
| SELECT r.r[1]::int4 FROM | |
| regexp_matches(company.industry_str, '[0-9]+', 'g') r | |
| ) | |
| UNION ALL | |
| SELECT g.id, g.parent_id, cached_level, | |
| path || g.id, | |
| g.id = ANY(path) | |
| FROM industries g, search_graph sg | |
| WHERE g.id = sg.link AND NOT cycle | |
| ) | |
| SELECT * FROM search_graph | |
| ) a ) b; | |
| END IF; -- end if for companies industries | |
| -- 2. regions limits | |
| IF company.region_limits IS NOT NULL AND LENGTH(company.region_limits) > 0 THEN | |
| INSERT INTO import.companies_regions_limits(firm_id, region_id, limit_value) | |
| SELECT company_id, region_id, MAX(region_limit) | |
| FROM ( | |
| SELECT company.id AS company_id, | |
| r [ 1 ] ::int4 AS region_id, | |
| r [ 2 ] ::int4 AS region_limit | |
| FROM regexp_matches(company.region_limits, '([0-9]+)=([0-9]+)', 'g') AS r | |
| UNION | |
| SELECT company.id AS company_id, | |
| (SELECT id FROM regions WHERE | |
| lft <= (SELECT lft FROM regions WHERE id = m[1]::int4) AND | |
| rgt >= (SELECT rgt FROM regions WHERE id = m[1]::int4) | |
| ORDER BY lft ASC LIMIT 1 | |
| ), | |
| m[2]::int4 | |
| FROM regexp_matches(company.region_limits, '([0-9]+)=([0-9]+)', 'g') AS m | |
| ) AS t | |
| GROUP BY company_id, region_id; | |
| END IF; -- end if for regions limits | |
| -- 3. regions bold | |
| IF company.region_bold IS NOT NULL AND LENGTH(company.region_bold) > 0 THEN | |
| INSERT INTO import.companies_regions_bold(firm_id, region_id) | |
| SELECT | |
| company.id, r[1]::int4 | |
| FROM | |
| regexp_matches(company.region_bold, '[0-9]+', 'g') AS r | |
| UNION | |
| SELECT | |
| company.id, | |
| (SELECT id FROM regions WHERE | |
| lft <= (SELECT lft FROM regions WHERE id = m[1]::int4) AND | |
| rgt >= (SELECT rgt FROM regions WHERE id = m[1]::int4) | |
| ORDER BY lft ASC LIMIT 1 | |
| ) | |
| FROM regexp_matches(company.region_bold, '[0-9]+', 'g') AS m; | |
| END IF; | |
| END LOOP; | |
| PERFORM import.log_message('Таблицы companies_industries и companies_regions_limits заполнены'); | |
| PERFORM import.log_message('Заполняем таблицу companies_regions'); | |
| INSERT INTO import.companies_regions (company_id, region_id) | |
| SELECT DISTINCT | |
| c.oferta_id AS company_id, p.region_id | |
| FROM | |
| import.vw_site_firm c | |
| INNER JOIN import.vw_site_estate a ON a.oferta_firm_id = c.oferta_id | |
| INNER JOIN cities ci ON a.city_id = ci.id | |
| INNER JOIN provinces p ON ci.province_id = p.id | |
| UNION | |
| SELECT | |
| c.oferta_id AS company_id, roots.id AS region_id | |
| FROM | |
| import.vw_site_firm c | |
| INNER JOIN import.vw_site_estate a ON a.oferta_firm_id = c.oferta_id | |
| INNER JOIN cities ci ON a.city_id = ci.id | |
| INNER JOIN provinces p ON ci.province_id = p.id | |
| INNER JOIN regions r ON p.region_id = r.id | |
| INNER JOIN | |
| ( | |
| SELECT | |
| r.id AS child_id, r.name, | |
| ( | |
| SELECT rr.id FROM regions rr | |
| WHERE rr.lft <= r.lft AND rr.rgt >= r.rgt | |
| ORDER BY rr.lft LIMIT 1 | |
| ) AS id | |
| FROM | |
| regions r | |
| WHERE (r.rgt - r.lft) = 1 | |
| ) roots ON r.id = roots.child_id | |
| INNER JOIN regions AS root_region ON roots.id = root_region.id; | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: company_first_letter_ord(character varying); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION company_first_letter_ord(str character varying) RETURNS smallint | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| l CHAR; | |
| BEGIN | |
| l := substring(trim(lower(str)) from 1 for 1); | |
| IF l = 'ё' THEN | |
| l := 'е'; | |
| END IF; | |
| RETURN ascii(l); | |
| END; | |
| $$; | |
| -- | |
| -- Name: company_title_ordinal(character varying); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION company_title_ordinal(str character varying) RETURNS double precision | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| _norm VARCHAR; | |
| i SMALLINT; | |
| o DOUBLE PRECISION; | |
| sym CHAR; | |
| code SMALLINT; | |
| norm_code SMALLINT; | |
| BEGIN | |
| _norm := substring(regexp_replace(lower(str), '[^A-Za-z0-9а-яА-ЯЁё]', '', 'g') from 1 for 8); | |
| o := 0.0; | |
| FOR i IN 1..length(_norm) LOOP | |
| sym := SUBSTRING(_norm FROM i FOR 1)::CHAR; | |
| code := ascii(sym); | |
| norm_code := 0; | |
| IF code >= 48 AND code <= 57 THEN -- 0-9 | |
| norm_code := code - 47; | |
| END IF; | |
| IF code >= 97 AND code <= 122 THEN -- a-z | |
| norm_code := code - (96 - 9); | |
| END IF; | |
| IF code >= 1072 AND code <= 1105 THEN -- а-я + ё | |
| IF code = 1105 THEN -- ё имеет код больший, чем я, это плохо | |
| code := 1078; | |
| ELSIF code > 1077 THEN | |
| code := code + 1; | |
| END IF; | |
| norm_code := code - (1071 - 35); | |
| END IF; | |
| o := o + norm_code * power(68, (9 - i)); | |
| END LOOP; | |
| RETURN o; | |
| END; | |
| $$; | |
| -- | |
| -- Name: create_company(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION create_company(oferta_firm_id integer) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| firm RECORD; | |
| BEGIN | |
| SELECT * INTO firm FROM import.vw_site_firm WHERE oferta_id = oferta_firm_id; | |
| INSERT INTO companies (id, name, name_rest, synonym, description, | |
| created_at, updated_at, industries_denorm, regions, title_ord, first_letter_ord, | |
| packet, state, oferta_sync_id, oferta_online_flag, oferta_sync_dt, | |
| annonce, has_email, pricelist_actualized_at) VALUES ( | |
| firm.site_id, firm.name, firm.opf_name, firm.alt_name, firm.annotation, | |
| firm.created, firm.modified, | |
| ( | |
| SELECT | |
| array_to_string(array_accum(industry_id), ',') | |
| FROM | |
| import.companies_industries | |
| WHERE | |
| firm_id = firm.oferta_id | |
| GROUP BY firm_id LIMIT 1 | |
| ), -- industries_denorm | |
| ( | |
| SELECT | |
| array_to_string(array_accum(region_id), ',') | |
| FROM | |
| import.companies_regions | |
| WHERE | |
| company_id = firm.oferta_id | |
| GROUP BY company_id LIMIT 1 | |
| ), -- regions | |
| import.company_title_ordinal(firm.name), | |
| import.company_first_letter_ord(firm.name), | |
| firm.package, 'accepted', firm.oferta_id, firm.is_online_edit, NOW(), | |
| substring(firm.annotation from 1 for 250), | |
| CASE WHEN | |
| ( | |
| SELECT | |
| COUNT(*) | |
| FROM | |
| import.vw_site_estate e INNER JOIN | |
| import.vw_site_estate_contact c ON c.estate_id = e.id AND c.contact_type = 3 | |
| WHERE | |
| e.oferta_firm_id = firm.oferta_id | |
| ) > 0 THEN TRUE ELSE FALSE END, -- has_email | |
| (SELECT actualization_date FROM import.vw_site_modified_offer WHERE firm_id = firm.oferta_id LIMIT 1) | |
| ); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: fill_images_to_delete(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION fill_images_to_delete() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| PERFORM import.log_message('Заполняем очередь на удаление картинок'); | |
| TRUNCATE TABLE import.companies_images_to_delete; | |
| DELETE FROM import.vw_site_deleted_firm WHERE site_id IN ( | |
| SELECT f.site_id | |
| FROM import.vw_site_deleted_firm f INNER JOIN | |
| companies c ON c.id = f.site_id | |
| WHERE | |
| c.oferta_online_flag = TRUE | |
| ); | |
| -- логотипы у удаленных компаний | |
| INSERT INTO import.companies_images_to_delete (id, class_name) | |
| SELECT | |
| cl.id, 'CompanyLogo' | |
| FROM | |
| company_logos cl | |
| INNER JOIN companies c ON cl.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm df ON c.id = df.site_id; | |
| -- логотипы, которые удалили в оферте | |
| INSERT INTO import.companies_images_to_delete (id, class_name) | |
| SELECT | |
| cl.id, 'CompanyLogo' | |
| FROM | |
| company_logos cl | |
| INNER JOIN companies c ON cl.company_id = c.id | |
| INNER JOIN import.vw_site_firm f ON c.id = f.site_id | |
| WHERE | |
| f.has_logo = FALSE; | |
| -- картинки товаров | |
| INSERT INTO import.companies_images_to_delete (id, class_name) | |
| SELECT | |
| i.id, 'Image' | |
| FROM | |
| images i | |
| INNER JOIN products p ON i.subject_id = p.id | |
| INNER JOIN companies c ON p.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm df ON c.id = df.site_id | |
| WHERE | |
| i.subject_type = 'Product'; | |
| -- картинки компании | |
| INSERT INTO import.companies_images_to_delete (id, class_name) | |
| SELECT | |
| i.id, 'Image' | |
| FROM | |
| images i | |
| INNER JOIN companies c ON i.subject_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm df ON c.id = df.site_id | |
| WHERE | |
| i.subject_type = 'Company'; | |
| -- картинки витрин | |
| INSERT INTO import.companies_images_to_delete (id, class_name) | |
| SELECT | |
| i.id, 'ShowcaseImage' | |
| FROM | |
| showcase_images i | |
| INNER JOIN showcases s ON i.showcase_id = s.id | |
| INNER JOIN companies c ON s.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm df ON c.id = df.site_id; | |
| PERFORM import.log_message('Очередь на удаление картинок заполнена'); | |
| -- оригинальные прайсы у удаленных компаний | |
| INSERT INTO import.orig_price_to_upload (firm_id, to_delete) | |
| SELECT | |
| c.oferta_sync_id, TRUE | |
| FROM | |
| import.vw_site_deleted_firm f INNER JOIN | |
| companies c ON f.site_id = c.id | |
| WHERE | |
| c.oferta_sync_id IS NOT NULL AND | |
| c.original_pricelist_file_name IS NOT NULL; | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: firm_has_estates(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION firm_has_estates(firm_id integer) RETURNS boolean | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| result BOOLEAN; | |
| BEGIN | |
| IF (SELECT COUNT(*) FROM import.vw_site_estate WHERE oferta_firm_id = firm_id) > 0 THEN | |
| result := TRUE; | |
| ELSE | |
| result := FALSE; | |
| END IF; | |
| RETURN result; | |
| END; | |
| $$; | |
| -- | |
| -- Name: import_init(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION import_init() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| TRUNCATE TABLE import.import_log; | |
| PERFORM import.log_message('Начало импорта'); | |
| PERFORM import.fill_images_to_delete(); | |
| TRUNCATE TABLE import.logo_to_upload; | |
| TRUNCATE TABLE import.orig_price_to_upload; | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: import_start(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION import_start() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| SET CONSTRAINTS ALL DEFERRED; | |
| PERFORM import.process_geography(); | |
| PERFORM import.process_deleted_companies(); | |
| PERFORM import.process_companies(); | |
| PERFORM import.process_deleted_offer(); | |
| PERFORM import.process_rubric_log(); | |
| PERFORM import.process_rubrics(); | |
| PERFORM import.process_offer(); | |
| PERFORM import.process_actualized_offer(); | |
| PERFORM import.process_synonyms(); | |
| SET CONSTRAINTS ALL IMMEDIATE; | |
| PERFORM import.log_message('Основная заливка данных закончена'); | |
| PERFORM import.log_message('Начинаем индексирование компаний и лотов: ' || | |
| (SELECT COUNT(*) FROM search_index_queue) || ' событий в очереди' | |
| ); | |
| PERFORM search.process_queue(); | |
| PERFORM import.log_message('Индексирование закончилось'); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: log_message(character varying); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION log_message(msg character varying) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| INSERT INTO import.import_log(event_date, message) VALUES (clock_timestamp(), msg); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: prerender_rubricator(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION prerender_rubricator() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| region_rubric RECORD; | |
| render1 classes.catalogue_rubric[]; | |
| render2 classes.catalogue_rubric[]; | |
| render3 classes.catalogue_rubric[]; | |
| render4 classes.catalogue_rubric[]; | |
| _row classes.catalogue_rubric; | |
| BEGIN | |
| DELETE FROM prerendered_rubricators; | |
| FOR region_rubric IN SELECT * FROM rubric_statistics LOOP | |
| render1 := ARRAY[]::classes.catalogue_rubric[]; | |
| render2 := ARRAY[]::classes.catalogue_rubric[]; | |
| render3 := ARRAY[]::classes.catalogue_rubric[]; | |
| render4 := ARRAY[]::classes.catalogue_rubric[]; | |
| IF region_rubric.rubric_id > 0 THEN | |
| IF region_rubric.predl_prod_stat > 0 THEN | |
| FOR _row IN SELECT * FROM render_catalogue_rubricator(region_rubric.rubric_id, region_rubric.region_id, 'predl_prod_stat') LOOP | |
| render1 := render1 || _row; | |
| END LOOP; | |
| END IF; | |
| IF region_rubric.spros_stat > 0 THEN | |
| FOR _row IN SELECT * FROM render_catalogue_rubricator(region_rubric.rubric_id, region_rubric.region_id, 'spros_stat') LOOP | |
| render2 := render2 || _row; | |
| END LOOP; | |
| END IF; | |
| IF region_rubric.providers_stat_predl > 0 THEN | |
| FOR _row IN SELECT * FROM render_catalogue_rubricator(region_rubric.rubric_id, region_rubric.region_id, 'providers_stat_predl') LOOP | |
| render3 := render3 || _row; | |
| END LOOP; | |
| END IF; | |
| IF region_rubric.providers_stat_spros > 0 THEN | |
| FOR _row IN SELECT * FROM render_catalogue_rubricator(region_rubric.rubric_id, region_rubric.region_id, 'providers_stat_spros') LOOP | |
| render4 := render4 || _row; | |
| END LOOP; | |
| END IF; | |
| ELSE | |
| FOR _row IN SELECT * FROM render_catalogue_root_rubricator(region_rubric.region_id, 3, 'predl_prod_stat') LOOP | |
| render1 := render1 || _row; | |
| END LOOP; | |
| FOR _row IN SELECT * FROM render_catalogue_root_rubricator(region_rubric.region_id, 2, 'spros_stat') LOOP | |
| render2 := render2 || _row; | |
| END LOOP; | |
| FOR _row IN SELECT * FROM render_catalogue_root_rubricator(region_rubric.region_id, 3, 'providers_stat_predl') LOOP | |
| render3 := render3 || _row; | |
| END LOOP; | |
| FOR _row IN SELECT * FROM render_catalogue_root_rubricator(region_rubric.region_id, 2, 'providers_stat_spros') LOOP | |
| render4 := render4 || _row; | |
| END LOOP; | |
| END IF; | |
| INSERT INTO prerendered_rubricators(rubric_id, region_id, by_predl_prod_stat, by_spros_stat, by_providers_stat_predl, by_providers_stat_spros) | |
| VALUES (region_rubric.rubric_id, region_rubric.region_id, render1, render2, render3, render4); | |
| END LOOP; | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_actualized_offer(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_actualized_offer() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| PERFORM import.log_message('Актуализируем лоты'); | |
| UPDATE price_list_rows p SET | |
| actualization_date = v.actualization_date, | |
| updated_at = clock_timestamp() | |
| FROM | |
| import.vw_site_actualize_offer v | |
| WHERE | |
| v.id = p.id; | |
| PERFORM import.log_message('Актуализация лотов закончена'); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_companies(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_companies() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| company RECORD; | |
| estate RECORD; | |
| new_id INTEGER; | |
| firm RECORD; | |
| BEGIN | |
| PERFORM import.log_message('Начинаем синхронизацию компаний'); | |
| -- предварительные действия | |
| PERFORM import.log_message('Выполняем предварительные действия'); | |
| -- 1. распарсить денормализованные поля из таблицы firm | |
| PERFORM import.companies_parse_denormalized_fields(); | |
| TRUNCATE TABLE import.orig_price_to_upload; | |
| TRUNCATE TABLE import.new_addresses_ids; | |
| TRUNCATE TABLE import.new_companies_ids; | |
| PERFORM setval('addresses_id_seq', (SELECT MAX(id) + 1 FROM addresses)); | |
| PERFORM setval('contacts_id_seq', (SELECT MAX(id) + 1 FROM contacts)); | |
| PERFORM setval('company_sites_id_seq', (SELECT MAX(id) + 1 FROM company_sites)); | |
| PERFORM setval('phones_id_seq', (SELECT MAX(id) + 1 FROM phones)); | |
| PERFORM import.log_message('Закончили с предварительными ласками'); | |
| PERFORM import.log_message('Проставляем IDы для новых компаний'); | |
| FOR company IN | |
| (SELECT oferta_id, name, package FROM "import"."vw_site_firm" WHERE site_id IS NULL) | |
| LOOP | |
| INSERT INTO companies (name, packet, state) | |
| VALUES (company.name, company.package, 'accepted') | |
| RETURNING "id" INTO new_id; | |
| INSERT INTO import.new_companies_ids(oferta_id, site_id) | |
| VALUES (company.oferta_id, new_id); | |
| END LOOP; | |
| -- засунуть в экспорт новые IDы | |
| INSERT INTO export.sync_companies_ids_queue(oferta_id, site_id) | |
| SELECT oferta_id, site_id FROM import.new_companies_ids; | |
| UPDATE import.vw_site_firm f SET | |
| site_id = n.site_id | |
| FROM | |
| import.new_companies_ids n | |
| WHERE | |
| f.oferta_id = n.oferta_id; | |
| PERFORM import.log_message('IDы для новых компаний проставлены'); | |
| PERFORM import.log_message('Массовое обновление компаний'); | |
| FOR firm IN (SELECT oferta_id FROM import.vw_site_firm) LOOP | |
| IF import.firm_has_estates(firm.oferta_id) THEN | |
| BEGIN | |
| PERFORM import.process_firm(firm.oferta_id); | |
| EXCEPTION | |
| WHEN OTHERS THEN | |
| PERFORM import.log_message('ERROR: Ошибка при импорте компании #' || firm.oferta_id); | |
| END; | |
| ELSE | |
| PERFORM import.log_message('ERROR: Компания #' || firm.oferta_id || ' без усадеб'); | |
| END IF; | |
| END LOOP; | |
| PERFORM import.log_message('Закончили обновление компаний'); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_companies_statistics(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_companies_statistics() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| DROP TABLE IF EXISTS _companies_has_prices; | |
| CREATE TEMPORARY TABLE _companies_has_prices AS ( | |
| SELECT | |
| company_id, COUNT(*) AS predl_count | |
| FROM | |
| price_list_rows | |
| WHERE | |
| is_deleted = FALSE AND is_demand = FALSE | |
| GROUP BY company_id | |
| ); | |
| CREATE INDEX "_companies_has_prices_idx" ON "_companies_has_prices" | |
| USING btree ("company_id"); | |
| DROP TABLE IF EXISTS _companies_has_spros; | |
| CREATE TEMPORARY TABLE _companies_has_spros AS ( | |
| SELECT | |
| company_id, COUNT(*) AS spros_count | |
| FROM | |
| price_list_rows | |
| WHERE | |
| is_deleted = FALSE AND is_demand = TRUE | |
| GROUP BY company_id | |
| ); | |
| CREATE INDEX "_companies_has_spros_idx" ON "_companies_has_spros" | |
| USING btree ("company_id"); | |
| UPDATE companies c SET | |
| predl_count = f.predl_count | |
| FROM | |
| ( | |
| SELECT | |
| co.id, ff.predl_count | |
| FROM | |
| companies co LEFT JOIN | |
| _companies_has_prices ff ON co.id = ff.company_id | |
| ) f | |
| WHERE | |
| c.id = f.id; | |
| UPDATE companies c SET | |
| spros_count = f.spros_count | |
| FROM | |
| ( | |
| SELECT | |
| co.id, ff.spros_count | |
| FROM | |
| companies co LEFT JOIN | |
| _companies_has_spros ff ON co.id = ff.company_id | |
| ) f | |
| WHERE | |
| c.id = f.id; | |
| DROP TABLE _companies_has_prices; | |
| DROP TABLE _companies_has_spros; | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_deleted_companies(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_deleted_companies() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| PERFORM import.log_message('Удаляем удаленные компании'); | |
| DELETE FROM import.vw_site_deleted_firm WHERE site_id IN ( | |
| SELECT f.site_id | |
| FROM import.vw_site_deleted_firm f INNER JOIN | |
| companies c ON c.id = f.site_id | |
| WHERE | |
| c.oferta_online_flag = TRUE | |
| ); | |
| -- ставим в очередь на удаление из сфинкса IDы удаленных компаний | |
| INSERT INTO search.sphinx_delete_queue (obj_id, class_name) | |
| SELECT | |
| site_id, 'Company' | |
| FROM | |
| import.vw_site_deleted_firm; | |
| PERFORM import.log_message('Удаляем привязки к пользователям'); | |
| DELETE FROM company_users WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Чистим логотипы'); | |
| DELETE FROM company_logos WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем статистику'); | |
| DELETE FROM company_statistic_logs WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_geo_by_months WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_geo_by_weeks WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_pages_by_months WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_pages_by_weeks WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_referers_by_months WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_referers_by_weeks WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_total_by_days WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_total_by_months WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_total_by_weeks WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_statistic_totals WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем сайты'); | |
| DELETE FROM company_sites WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем строчки'); | |
| -- ставим удаленные строчки в очередь на удаление из сфинкса | |
| INSERT INTO search.sphinx_delete_queue (obj_id, class_name) | |
| SELECT p.id, 'PriceListRow' | |
| FROM | |
| price_list_rows p INNER JOIN import.vw_site_deleted_firm v | |
| ON p.company_id = v.site_id; | |
| DELETE FROM price_list_rows WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем товары'); | |
| -- ставим удаленны товары в очередь на удаление из сфинкса | |
| INSERT INTO search.sphinx_delete_queue (obj_id, class_name) | |
| SELECT p.id, 'Product' | |
| FROM | |
| products p INNER JOIN import.vw_site_deleted_firm v | |
| ON p.company_id = v.site_id; | |
| DELETE FROM products WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем новости'); | |
| DELETE FROM news WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем настройки'); | |
| DELETE FROM company_settings WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем витрины'); | |
| DELETE FROM showcase_order_periods WHERE showcase_order_id IN ( | |
| SELECT o.id | |
| FROM | |
| showcase_orders o | |
| INNER JOIN showcases s ON o.showcase_id = s.id | |
| INNER JOIN companies c ON s.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm f ON c.id = f.site_id | |
| ); | |
| DELETE FROM showcase_order_stats WHERE showcase_order_id IN ( | |
| SELECT o.id | |
| FROM | |
| showcase_orders o | |
| INNER JOIN showcases s ON o.showcase_id = s.id | |
| INNER JOIN companies c ON s.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm f ON c.id = f.site_id | |
| ); | |
| DELETE FROM showcase_orders WHERE showcase_id IN ( | |
| SELECT s.id | |
| FROM | |
| showcases s | |
| INNER JOIN companies c ON s.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm f ON c.id = f.site_id | |
| ); | |
| DELETE FROM showcase_images WHERE showcase_id IN ( | |
| SELECT s.id | |
| FROM | |
| showcases s | |
| INNER JOIN companies c ON s.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm f ON c.id = f.site_id | |
| ); | |
| DELETE FROM showcases WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем привязки к рубрикам'); | |
| -- привязки тоже надо удалять из сфинкса | |
| INSERT INTO search.sphinx_delete_queue (obj_id, class_name) | |
| SELECT | |
| i.id, 'CompanyIndustry' | |
| FROM | |
| company_industries i INNER JOIN | |
| import.vw_site_deleted_firm v ON i.company_id = v.site_id; | |
| DELETE FROM company_industries WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем привязки к регионам'); | |
| -- привязки тоже надо удалять из сфинкса | |
| INSERT INTO search.sphinx_delete_queue (obj_id, class_name) | |
| SELECT | |
| r.id, 'CompanyRegion' | |
| FROM | |
| company_regions r INNER JOIN | |
| import.vw_site_deleted_firm v ON r.company_id = v.site_id; | |
| DELETE FROM company_regions WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| DELETE FROM company_region_commercials WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем из поискового индекса'); | |
| DELETE FROM search_index_companies WHERE company_id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| PERFORM import.log_message('Удаляем адреса'); | |
| DELETE FROM contacts WHERE address_id IN ( | |
| SELECT a.id | |
| FROM | |
| addresses a | |
| INNER JOIN companies c ON a.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm f ON c.id = f.site_id | |
| ); | |
| DELETE FROM phones WHERE address_id IN ( | |
| SELECT a.id | |
| FROM | |
| addresses a | |
| INNER JOIN companies c ON a.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm f ON c.id = f.site_id | |
| ); | |
| DELETE FROM yandex_map_coordinates WHERE address_id IN ( | |
| SELECT a.id | |
| FROM | |
| addresses a | |
| INNER JOIN companies c ON a.company_id = c.id | |
| INNER JOIN import.vw_site_deleted_firm f ON c.id = f.site_id | |
| ); | |
| PERFORM import.log_message('Удаляем сами компании'); | |
| DELETE FROM companies WHERE id IN ( | |
| SELECT site_id FROM import.vw_site_deleted_firm | |
| ); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_deleted_offer(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_deleted_offer() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| PERFORM import.log_message('Удаляем удаленные лоты'); | |
| INSERT INTO search.sphinx_delete_queue (obj_id, class_name) | |
| SELECT | |
| id, 'PriceListRow' | |
| FROM | |
| "import"."vw_site_deleted_offer"; | |
| UPDATE price_list_rows AS p SET | |
| is_deleted = TRUE, | |
| updated_at = NOW() | |
| FROM | |
| "import"."vw_site_deleted_offer" AS d | |
| WHERE p.id = d.id; | |
| PERFORM import.log_message('Закончили удаление удаленных лотов'); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_firm(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_firm(firm_id integer) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| firm RECORD; | |
| company RECORD; | |
| _estate RECORD; | |
| new_address_id INTEGER; | |
| is_new BOOLEAN; | |
| BEGIN | |
| SELECT * INTO firm FROM import.vw_site_firm WHERE oferta_id = firm_id; | |
| SELECT * INTO company FROM companies WHERE id = firm.site_id; | |
| -- фирма должна бы быть в оферте, но её почему-то нет | |
| IF NOT FOUND THEN | |
| PERFORM import.create_company(firm_id); | |
| is_new := TRUE; | |
| ELSE | |
| PERFORM import.update_company(firm_id); | |
| is_new := FALSE; | |
| END IF; | |
| IF (SELECT COUNT(*) FROM import.new_companies_ids WHERE oferta_id = firm_id) > 0 THEN | |
| is_new := TRUE; | |
| END IF; | |
| SELECT * INTO company FROM companies WHERE id = firm.site_id; | |
| IF company.oferta_online_flag = FALSE OR company.oferta_online_flag IS NULL OR is_new = TRUE THEN | |
| -- ставим в очередь на заливку прайса | |
| IF firm.has_original_price = TRUE THEN | |
| PERFORM import.queue_orig_price_to_upload(firm_id); | |
| ELSE | |
| PERFORM import.queue_orig_price_to_delete(firm_id); | |
| END IF; | |
| IF firm.has_logo = TRUE THEN | |
| PERFORM import.queue_logo_to_upload(firm_id); | |
| ELSE | |
| PERFORM import.queue_logo_to_delete(firm_id); | |
| END IF; | |
| -- firm site | |
| DELETE FROM company_sites WHERE company_id = company.id; | |
| INSERT INTO company_sites (company_id, url, position) | |
| SELECT | |
| company.id, 'http://' || url, oid | |
| FROM | |
| import.vw_site_firm_site | |
| WHERE oferta_firm_id = firm.oferta_id; | |
| -- addresses | |
| DELETE FROM phones WHERE address_id IN ( | |
| SELECT id FROM addresses WHERE company_id = company.id | |
| ); | |
| DELETE FROM contacts WHERE address_id IN ( | |
| SELECT id FROM addresses WHERE company_id = company.id | |
| ); | |
| DELETE FROM addresses WHERE company_id = company.id; | |
| FOR _estate IN (SELECT * FROM import.vw_site_estate WHERE oferta_firm_id = firm_id) LOOP | |
| INSERT INTO addresses ( | |
| company_id, name, city_id, street_id, | |
| created_at, updated_at, estate, building, comments, | |
| position, oferta_sync_id) | |
| VALUES ( | |
| company.id, _estate.name, _estate.city_id, _estate.street_id, | |
| NOW(), NOW(), _estate.house, _estate.letter, _estate.address_other, | |
| _estate.oid, _estate.id | |
| ) RETURNING "id" INTO new_address_id; | |
| INSERT INTO phones ( | |
| code, number, address_id, type_id, comment, created_at, updated_at, position) | |
| SELECT | |
| prefix, phone, new_address_id, phone_type, comm, NOW(), NOW(), oid | |
| FROM | |
| import.vw_site_estate_phone | |
| WHERE | |
| estate_id = _estate.id AND | |
| (length(prefix) + length(phone)) = 10; | |
| INSERT INTO contacts ( | |
| contact_type_id, address_id, value, created_at, updated_at, position | |
| ) | |
| SELECT | |
| contact_type - 1, new_address_id, value, NOW(), NOW(), oid | |
| FROM | |
| import.vw_site_estate_contact | |
| WHERE | |
| estate_id = _estate.id AND contact_type IN (3,4); | |
| END LOOP; | |
| -- company regions | |
| PERFORM import.update_company_regions_for_company(company.id); | |
| -- company industries | |
| DELETE FROM company_industries WHERE company_id = company.id; | |
| INSERT INTO company_industries (company_id, industry_id, created_at, updated_at) | |
| SELECT | |
| company.id, ci.industry_id, NOW(), NOW() | |
| FROM | |
| import.companies_industries ci | |
| WHERE | |
| ci.firm_id = firm.oferta_id; | |
| END IF; | |
| -- коммерческие регионы обновляем для всех | |
| PERFORM import.update_company_regions_commercials_for_company(company.id); | |
| -- пакет обновляем для всех | |
| UPDATE companies SET packet = firm.package WHERE id = company.id; | |
| -- ставим в очередь на индексацию | |
| INSERT INTO search_index_queue (class_name, obj_id) VALUES ('Company', company.id); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_geography(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_geography() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| PERFORM import.log_message('Начало синхронизации географических справочников'); | |
| PERFORM import.log_message('Синхронизация типов городов и улиц'); | |
| UPDATE city_types c SET | |
| (id, name, short_name) = (v.id, v.name, v.short_name) | |
| FROM | |
| import.vw_site_city_type v | |
| WHERE | |
| c.id = v.id; | |
| INSERT INTO city_types (id, name, short_name) | |
| SELECT | |
| v.id, v.name, v.short_name | |
| FROM | |
| import.vw_site_city_type v LEFT JOIN | |
| city_types c ON v.id = c.id | |
| WHERE | |
| c.id IS NULL; | |
| UPDATE street_types s SET | |
| (id, name) = (v.id, v.shortname) | |
| FROM import.vw_site_street_type v | |
| WHERE s.id = v.id; | |
| INSERT INTO street_types (id, name) | |
| SELECT v.id, v.shortname | |
| FROM | |
| import.vw_site_street_type v LEFT JOIN | |
| street_types s ON v.id = s.id | |
| WHERE s.id IS NULL; | |
| PERFORM import.log_message('Синхронизация типов городов и улиц закончена'); | |
| PERFORM import.log_message('Синхронизация стран'); | |
| UPDATE countries c SET | |
| (name, phone_code) = (v.name, v.code::int4) | |
| FROM | |
| import.vw_site_country v | |
| WHERE | |
| v.id = c.id; | |
| INSERT INTO countries (id, name, phone_code) | |
| SELECT | |
| v.id, v.name, v.code::int4 | |
| FROM | |
| import.vw_site_country v LEFT JOIN | |
| countries c ON v.id = c.id | |
| WHERE c.id IS NULL; | |
| PERFORM import.log_message('Синхронизация стран закончена'); | |
| PERFORM import.log_message('Синхронизация областей'); | |
| UPDATE provinces p SET | |
| (name, country_id, region_id) = (v.name, v.country_id, COALESCE(v.area_id, p.region_id, 0)) | |
| FROM | |
| import.vw_site_province v | |
| WHERE | |
| p.id = v.id; | |
| INSERT INTO provinces (id, name, country_id, region_id) | |
| SELECT | |
| v.id, v.name, v.country_id, COALESCE(v.area_id, 0) | |
| FROM | |
| import.vw_site_province v LEFT JOIN | |
| provinces p ON v.id = p.id | |
| WHERE | |
| p.id IS NULL; | |
| PERFORM import.log_message('Синхронизация областей закончена'); | |
| PERFORM import.log_message('Синхронизация городов'); | |
| UPDATE cities c SET | |
| (name, type_id, province_head, province_id) = | |
| (v.name, v.city_type_id, v.capital, v.province_id) | |
| FROM | |
| import.vw_site_city v | |
| WHERE v.id = c.id; | |
| INSERT INTO cities (id, name, type_id, province_head, province_id) | |
| SELECT | |
| v.id, v.name, v.city_type_id, v.capital, v.province_id | |
| FROM | |
| import.vw_site_city v LEFT JOIN | |
| cities c ON v.id = c.id | |
| WHERE | |
| c.id IS NULL; | |
| PERFORM import.log_message('Синхронизация городов закончена'); | |
| PERFORM import.log_message('Синхронизация улиц'); | |
| UPDATE streets s SET | |
| (name, type_id, city_id) = (v.name, v.c_street_type_id, v.city_id) | |
| FROM | |
| import.vw_site_street v | |
| WHERE | |
| s.id = v.id; | |
| INSERT INTO streets (id, name, type_id, city_id) | |
| SELECT | |
| v.id, v.name, v.c_street_type_id, v.city_id | |
| FROM | |
| import.vw_site_street v LEFT JOIN | |
| streets s ON v.id = s.id | |
| WHERE | |
| s.id IS NULL; | |
| PERFORM import.log_message('Синхронизация улиц закончена'); | |
| PERFORM import.log_message('Синхронизация геграфических справочников закончена'); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_offer(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_offer() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| PERFORM import.log_message('Начало синхронизации лотов'); | |
| PERFORM import.log_message('Обновляем существующие лоты'); | |
| PERFORM import.log_message('Ставим новые и обновленные лоты в очередь на индексацию'); | |
| -- 1. измененные | |
| INSERT INTO search_index_queue (class_name, obj_id) | |
| SELECT 'PriceListRow', v.id | |
| FROM "import"."vw_site_modified_offer" v | |
| INNER JOIN "price_list_rows" p ON p.id = v.id | |
| WHERE p.title != v.title; | |
| -- 2. новые | |
| INSERT INTO search_index_queue (class_name, obj_id) | |
| SELECT 'PriceListRow', v.id | |
| FROM "import"."vw_site_modified_offer" v | |
| LEFT JOIN "price_list_rows" p ON p.id = v.id | |
| WHERE | |
| p.id IS NULL; | |
| UPDATE "price_list_rows" | |
| SET | |
| ( | |
| company_id, | |
| rubric_id, | |
| is_empty, | |
| currency, | |
| is_deleted, | |
| title, | |
| is_demand, | |
| title_full, | |
| is_exact, | |
| regions, | |
| updated_at, | |
| rubric_l1_id, | |
| rubric_l2_id, | |
| rubric_l3_id, | |
| rubric_l4_id, | |
| price, | |
| price_max, | |
| price_uni, | |
| sort_level, | |
| actualization_date | |
| ) = | |
| ( | |
| COALESCE(c_by_id.id, c_by_of_id.id, "price_list_rows"."company_id"), | |
| v.rubric_id, | |
| CASE | |
| WHEN | |
| v.price_from IS NOT NULL OR v.price_to IS NOT NULL | |
| THEN | |
| FALSE | |
| ELSE | |
| TRUE | |
| END, | |
| v.c_currency_id, | |
| FALSE, -- is_deleted | |
| SUBSTRING(v.title FROM 1 FOR 255), | |
| v.is_demand, | |
| SUBSTRING(v.title FROM 1 FOR 255), | |
| NOT v.is_price_from, -- is exact | |
| '0,' || v.x_city_link, -- regions | |
| clock_timestamp(), | |
| r.rubric_l1_id, | |
| r.rubric_l2_id, | |
| r.rubric_l3_id, | |
| r.rubric_l4_id, | |
| v.price_from, | |
| v.price_to, | |
| v.pricesort, | |
| v.sort_level, | |
| v.actualization_date | |
| ) | |
| FROM | |
| import.vw_site_modified_offer AS v INNER JOIN | |
| import.rubrics_denormalization_tree r ON v.rubric_id = r.rubric_id INNER JOIN | |
| companies c_by_id ON v.site_firm_id = c_by_id.id LEFT JOIN | |
| companies c_by_of_id ON v.firm_id = c_by_of_id.oferta_sync_id | |
| WHERE | |
| price_list_rows.id = v.id; | |
| PERFORM import.log_message('Закончено обновление существующих лотов'); | |
| PERFORM import.log_message('Добавляем новые лоты'); | |
| TRUNCATE TABLE import.new_offer_ids; | |
| INSERT INTO import.new_offer_ids (id) | |
| SELECT v.id | |
| FROM import.vw_site_modified_offer v LEFT JOIN price_list_rows p ON v.id = p.id | |
| WHERE p.id IS NULL; | |
| INSERT INTO price_list_rows (id, | |
| company_id, | |
| rubric_id, | |
| is_empty, | |
| currency, | |
| is_deleted, | |
| title, | |
| is_demand, | |
| title_full, | |
| is_exact, | |
| regions, | |
| created_at, | |
| updated_at, | |
| rubric_l1_id, | |
| rubric_l2_id, | |
| rubric_l3_id, | |
| rubric_l4_id, | |
| price, | |
| price_max, | |
| price_uni, | |
| sort_level, | |
| actualization_date) | |
| SELECT | |
| v.id, | |
| -- COALESCE(c_by_id.id, c_by_of_id.id), -- company_id | |
| COALESCE( | |
| (SELECT id FROM companies WHERE id = v.site_firm_id LIMIT 1), | |
| (SELECT id FROM companies WHERE oferta_sync_id = v.firm_id LIMIT 1) | |
| ), -- company_id | |
| v.rubric_id, | |
| CASE | |
| WHEN | |
| v.price_from IS NOT NULL OR v.price_to IS NOT NULL | |
| THEN | |
| FALSE | |
| ELSE | |
| TRUE | |
| END, -- is_empty | |
| v.c_currency_id, | |
| FALSE, -- is_deleted | |
| SUBSTRING(v.title FROM 1 FOR 255), | |
| v.is_demand, | |
| SUBSTRING(v.title FROM 1 FOR 255), | |
| NOT v.is_price_from, -- is exact | |
| COALESCE('0,' || v.x_city_link, '0'), -- regions | |
| clock_timestamp(), -- created | |
| clock_timestamp(), -- updated | |
| r.rubric_l1_id, | |
| r.rubric_l2_id, | |
| r.rubric_l3_id, | |
| r.rubric_l4_id, | |
| v.price_from, | |
| v.price_to, | |
| v.pricesort, | |
| v.sort_level, | |
| v.actualization_date | |
| FROM | |
| import.vw_site_modified_offer v INNER JOIN | |
| import.rubrics_denormalization_tree r ON v.rubric_id = r.rubric_id INNER JOIN | |
| import.new_offer_ids n ON v.id = n.id /* LEFT JOIN | |
| companies c_by_id ON v.site_firm_id = c_by_id.id LEFT JOIN | |
| companies c_by_of_id ON v.firm_id = c_by_of_id.oferta_sync_id | |
| WHERE | |
| c_by_id.id IS NOT NULL OR c_by_of_id.id IS NOT NULL */ ; | |
| PERFORM import.log_message('Удаляем зомби.'); | |
| DELETE FROM zombie_rows; | |
| INSERT INTO zombie_rows | |
| SELECT p.* | |
| FROM price_list_rows p LEFT JOIN rubrics r ON p.rubric_id = r.id | |
| WHERE r.id IS NULL; | |
| PERFORM import.log_message('Количество зомби = ' || (SELECT COUNT(*)::varchar FROM zombie_rows)); | |
| PERFORM import.log_message('Закончили добавление новых лотов'); | |
| PERFORM import.log_message('Закончили обновление лотов'); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_rubric_log(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_rubric_log() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| log_rec RECORD; | |
| deleted INTEGER; | |
| united INTEGER; | |
| root_rubric_id INTEGER; | |
| BEGIN | |
| SELECT id INTO root_rubric_id FROM import.vw_site_rubric WHERE num_left = 1 LIMIT 1; | |
| deleted := 0; | |
| united := 0; | |
| PERFORM import.log_message('Начинаем обработку лога рубрикатора'); | |
| FOR log_rec IN SELECT * FROM "import"."vw_site_rubric_log" ORDER BY event_date ASC LOOP | |
| -- delete | |
| IF log_rec.event_type IN (20,30,40) THEN | |
| -- 1. cms page rubrics | |
| DELETE FROM cms_page_rubrics WHERE rubric_id = log_rec.rubric_id; | |
| -- 2. news rubrics | |
| DELETE FROM news_rubrics WHERE rubric_id = log_rec.rubric_id; | |
| -- 3. products | |
| UPDATE products SET rubric_l1_id = NULL, rubric_l2_id = NULL, | |
| rubric_l3_id = NULL, rubric_l4_id = NULL, | |
| rubric_id = NULL, updated_at = NOW() | |
| WHERE rubric_l1_id = log_rec.rubric_id OR rubric_l2_id = log_rec.rubric_id OR | |
| rubric_l3_id = log_rec.rubric_id OR rubric_l4_id = log_rec.rubric_id OR | |
| rubric_id = log_rec.rubric_id; | |
| -- 4. showcase order | |
| -- привязываем к коренной рубрике | |
| UPDATE showcase_orders SET rubric_id = root_rubric_id WHERE rubric_id = log_rec.rubric_id; | |
| deleted := deleted + 1; | |
| END IF; | |
| -- union | |
| IF log_rec.event_type IN (22,32,42) THEN | |
| -- 1. cms page rubrics | |
| INSERT INTO cms_page_rubrics (cms_page_id, rubric_id) | |
| SELECT | |
| cms_page_id, log_rec.event_param | |
| FROM cms_page_rubrics | |
| WHERE rubric_id IN (log_rec.rubric_id, log_rec.event_param); | |
| DELETE FROM cms_page_rubrics WHERE rubric_id = log_rec.rubric_id; | |
| -- 2. news rubrics | |
| INSERT INTO news_rubrics (news_id, rubric_id) | |
| SELECT | |
| news_id, log_rec.event_param | |
| FROM news_rubrics | |
| WHERE rubric_id IN (log_rec.rubric_id, log_rec.event_param); | |
| DELETE FROM news_rubrics WHERE rubric_id = log_rec.rubric_id; | |
| -- 3. products | |
| UPDATE products SET rubric_id = log_rec.event_param | |
| WHERE rubric_id = log_rec.rubric_id; | |
| -- 4. showcase orders | |
| UPDATE showcase_orders SET rubric_id = log_rec.event_param | |
| WHERE rubric_id = log_rec.rubric_id; | |
| united := united + 1; | |
| END IF; | |
| END LOOP; | |
| PERFORM import.log_message('Обработано событий удаления: ' || deleted); | |
| PERFORM import.log_message('Обработано событий объединения: ' || united); | |
| PERFORM import.log_message('Обработка лога рубрикатора завершена'); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_rubrics(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_rubrics() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| PERFORM import.log_message('Начинаем заливку рубрикатора'); | |
| -- TRUNCATE нельзя, truncate блокирует всю таблицу в эксклюзивном режиме | |
| DELETE FROM rubrics; | |
| INSERT INTO rubrics ( | |
| id, parent_id, title, | |
| slug, joined_path, old_path, | |
| created_at, updated_at, cached_level, | |
| is_normativka, next_level_normativka, prefix_normativka, | |
| synonym, lft, rgt, is_published) | |
| SELECT | |
| id, parent_id, title, | |
| tag, path, old_url, | |
| created, modified, lev, | |
| is_prodnorm, next_level_prodnorm, prodnorm_prefix, | |
| synonims, num_left, num_right, | |
| CASE | |
| WHEN | |
| ( | |
| (is_prodnorm = TRUE AND prodnorm_position = 2) OR | |
| (is_prodnorm = FALSE) | |
| ) | |
| THEN TRUE | |
| ELSE FALSE | |
| END | |
| FROM | |
| "import"."vw_site_rubric"; | |
| UPDATE rubrics SET title = 'Товары и услуги', joined_path = '/', slug = '' WHERE lft = 1; | |
| PERFORM import.log_message('Заполняем денормализованное дерево'); | |
| TRUNCATE TABLE import.rubrics_denormalization_tree; | |
| INSERT INTO import.rubrics_denormalization_tree ( | |
| rubric_id, | |
| rubric_l1_id, | |
| rubric_l2_id, | |
| rubric_l3_id, | |
| rubric_l4_id | |
| ) SELECT | |
| r.id, | |
| ( | |
| SELECT rubrics.id | |
| FROM rubrics | |
| WHERE | |
| rubrics.lft <= r.lft AND | |
| rubrics.rgt >= r.rgt AND | |
| rubrics.cached_level = 1 | |
| LIMIT 1 | |
| ) AS rubric_l1_id, | |
| ( | |
| SELECT rubrics.id | |
| FROM rubrics | |
| WHERE | |
| rubrics.lft <= r.lft AND | |
| rubrics.rgt >= r.rgt AND | |
| rubrics.cached_level = 2 | |
| LIMIT 1 | |
| ) AS rubric_l2_id, | |
| ( | |
| SELECT rubrics.id | |
| FROM rubrics | |
| WHERE | |
| rubrics.lft <= r.lft AND | |
| rubrics.rgt >= r.rgt AND | |
| rubrics.cached_level = 3 | |
| LIMIT 1 | |
| ) AS rubric_l3_id, | |
| ( | |
| SELECT rubrics.id | |
| FROM rubrics | |
| WHERE | |
| rubrics.lft <= r.lft AND | |
| rubrics.rgt >= r.rgt AND | |
| rubrics.cached_level = 4 | |
| LIMIT 1 | |
| ) AS rubric_l4_id | |
| FROM | |
| rubrics r | |
| WHERE | |
| (r.rgt - r.lft = 1) OR | |
| (r.is_normativka = TRUE); | |
| PERFORM import.log_message('Заливка рубрикатора завершена.'); | |
| UPDATE products | |
| SET rubric_l1_id = null, | |
| rubric_l2_id = null, | |
| rubric_l3_id = NULL, | |
| rubric_l4_id = null, | |
| rubric_id = null, | |
| updated_at = NOW() | |
| WHERE id IN ( | |
| SELECT p.id | |
| FROM products p | |
| LEFT JOIN rubrics r ON p.rubric_id = r.id | |
| WHERE r.id IS NULL | |
| ); | |
| PERFORM import.log_message('Пересчитываем денормализованные привязки к рубрикам у товаров'); | |
| UPDATE products p SET | |
| (rubric_l1_id, rubric_l2_id, rubric_l3_id, rubric_l4_id) = | |
| ( | |
| (SELECT id FROM rubrics WHERE lft <= r.lft AND rgt >= r.rgt AND cached_level = 1 LIMIT 1), | |
| (SELECT id FROM rubrics WHERE lft <= r.lft AND rgt >= r.rgt AND cached_level = 2 LIMIT 1), | |
| (SELECT id FROM rubrics WHERE lft <= r.lft AND rgt >= r.rgt AND cached_level = 3 LIMIT 1), | |
| (SELECT id FROM rubrics WHERE lft <= r.lft AND rgt >= r.rgt AND cached_level = 4 LIMIT 1) | |
| ) | |
| FROM | |
| rubrics r | |
| WHERE | |
| p.rubric_id = r.id; | |
| PERFORM 'Привязки пересчитаны'; | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_rubrics_statistics(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_rubrics_statistics() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| -- 0 | |
| DROP TABLE IF EXISTS _lots_regions; | |
| CREATE TEMPORARY TABLE _lots_regions ( | |
| "id" INTEGER, | |
| "company_id" INTEGER, | |
| "region_id" INTEGER, | |
| "rubric_id" INTEGER, | |
| "rubric_l1_id" INTEGER, | |
| "rubric_l2_id" INTEGER, | |
| "rubric_l3_id" INTEGER, | |
| "rubric_l4_id" INTEGER, | |
| "is_demand" BOOLEAN | |
| ); | |
| DROP TABLE IF EXISTS _lots_predl_stat; | |
| CREATE TEMPORARY TABLE _lots_predl_stat ( | |
| "region_id" INTEGER, | |
| "rubric_id" INTEGER, | |
| "predl_stat" INTEGER | |
| ); | |
| DROP TABLE IF EXISTS _lots_spros_stat; | |
| CREATE TEMPORARY TABLE _lots_spros_stat ( | |
| "region_id" INTEGER, | |
| "rubric_id" INTEGER, | |
| "spros_stat" INTEGER, | |
| "providers_stat" INTEGER | |
| ); | |
| DROP TABLE IF EXISTS _prod_stat; | |
| CREATE TEMPORARY TABLE _prod_stat ( | |
| "region_id" INTEGER, | |
| "rubric_id" INTEGER, | |
| "products_stat" INTEGER | |
| ); | |
| DROP TABLE IF EXISTS _providers_stat; | |
| CREATE TEMPORARY TABLE _providers_stat ( | |
| "region_id" INTEGER, | |
| "rubric_id" INTEGER, | |
| "providers_stat" INTEGER | |
| ); | |
| -- 2 | |
| INSERT INTO _lots_regions(id, company_id, region_id, rubric_id, rubric_l1_id, rubric_l2_id, | |
| rubric_l3_id, rubric_l4_id, is_demand) | |
| SELECT id, | |
| company_id, | |
| region_id::int4, | |
| rubric_id, | |
| rubric_l1_id, | |
| rubric_l2_id, | |
| rubric_l3_id, | |
| rubric_l4_id, | |
| is_demand | |
| FROM | |
| ( | |
| SELECT | |
| p.id, | |
| p.company_id, | |
| regexp_split_to_table(p.regions, ',')::varchar AS region_id, | |
| rubric_id, | |
| rubric_l1_id, | |
| rubric_l2_id, | |
| rubric_l3_id, | |
| rubric_l4_id, | |
| is_demand | |
| FROM price_list_rows p | |
| WHERE is_deleted = FALSE | |
| ) t | |
| WHERE region_id ~ E'\\d+'; | |
| -- 3 | |
| CREATE INDEX "_lots_regions_is_demand" ON "_lots_regions" | |
| USING btree ("is_demand"); | |
| CREATE INDEX "_lots_regions_idx" ON "_lots_regions" | |
| USING btree ("region_id", "rubric_id"); | |
| CREATE INDEX "_lots_regions_idx1" ON "_lots_regions" | |
| USING btree ("region_id", "rubric_l1_id"); | |
| CREATE INDEX "_lots_regions_idx2" ON "_lots_regions" | |
| USING btree ("region_id", "rubric_l2_id"); | |
| CREATE INDEX "_lots_regions_idx3" ON "_lots_regions" | |
| USING btree ("region_id", "rubric_l3_id"); | |
| CREATE INDEX "_lots_regions_idx4" ON "_lots_regions" | |
| USING btree ("region_id", "rubric_l4_id"); | |
| -- 4 | |
| INSERT INTO _lots_predl_stat (region_id, rubric_id, predl_stat) | |
| SELECT | |
| region_id, 0, COUNT(id) AS predl_stat | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = FALSE AND rubric_id IS NOT NULL | |
| GROUP BY | |
| region_id | |
| UNION | |
| SELECT | |
| region_id, rubric_id, COUNT(id) AS predl_stat | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = FALSE AND rubric_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l1_id, COUNT(id) | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = FALSE and rubric_l1_id is not null | |
| GROUP BY | |
| region_id, rubric_l1_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l2_id, COUNT(id) | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = FALSE AND rubric_l2_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l2_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l3_id, COUNT(id) | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = FALSE AND rubric_l3_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l3_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l4_id, COUNT(id) | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = FALSE AND rubric_l4_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l4_id; | |
| CREATE INDEX "_lots_predl_stat_idx" ON "_lots_predl_stat" | |
| USING btree ("region_id", "rubric_id"); | |
| --5 | |
| INSERT INTO _lots_spros_stat (region_id, rubric_id, spros_stat, providers_stat) | |
| SELECT | |
| region_id, 0, COUNT(id) AS spros_stat, COUNT(DISTINCT company_id) AS providers_stat | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = TRUE AND rubric_id IS NOT NULL | |
| GROUP BY | |
| region_id | |
| UNION | |
| SELECT | |
| region_id, rubric_id, COUNT(id) AS spros_stat, COUNT(DISTINCT company_id) AS providers_stat | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = TRUE AND rubric_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l1_id, COUNT(id), COUNT(DISTINCT company_id) | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = TRUE AND rubric_l1_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l1_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l2_id, COUNT(id), COUNT(DISTINCT company_id) | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = TRUE AND rubric_l2_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l2_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l3_id, COUNT(id), COUNT(DISTINCT company_id) | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = TRUE AND rubric_l3_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l3_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l4_id, COUNT(id), COUNT(DISTINCT company_id) | |
| FROM | |
| _lots_regions | |
| WHERE is_demand = TRUE AND rubric_l4_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l4_id; | |
| CREATE INDEX "_lots_spros_stat_idx" ON "_lots_spros_stat" | |
| USING btree ("region_id", "rubric_id"); | |
| -- 6 | |
| DROP TABLE IF EXISTS _products; | |
| CREATE TEMPORARY TABLE _products AS ( | |
| SELECT | |
| p.company_id, p.rubric_id, p.rubric_l1_id, | |
| p.rubric_l2_id, p.rubric_l3_id, p.rubric_l4_id, | |
| c.region_id | |
| FROM | |
| products p INNER JOIN | |
| companies co ON p.company_id = co.id INNER JOIN | |
| company_region_commercials c ON p.company_id = c.company_id | |
| WHERE | |
| co.packet > 0 AND p.rubric_l1_id IS NOT NULL AND | |
| p.state IN ('pending', 'accepted') AND p.is_public = TRUE | |
| ); | |
| INSERT INTO _prod_stat(region_id, rubric_id, products_stat) | |
| SELECT | |
| region_id, 0, COUNT(DISTINCT company_id) AS products_stat | |
| FROM | |
| _products | |
| WHERE rubric_id IS NOT NULL | |
| GROUP BY | |
| region_id | |
| UNION | |
| SELECT | |
| region_id, rubric_id, COUNT(DISTINCT company_id) AS products_stat | |
| FROM | |
| _products | |
| WHERE rubric_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l1_id, COUNT(DISTINCT company_id) | |
| FROM | |
| _products | |
| WHERE rubric_l1_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l1_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l2_id, COUNT(DISTINCT company_id) | |
| FROM | |
| _products | |
| WHERE rubric_l2_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l2_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l3_id, COUNT(DISTINCT company_id) | |
| FROM | |
| _products | |
| WHERE rubric_l3_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l3_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l4_id, COUNT(DISTINCT company_id) | |
| FROM | |
| _products | |
| WHERE rubric_l4_id IS NOT NULL AND rubric_l4_id != rubric_id | |
| GROUP BY | |
| region_id, rubric_l4_id; | |
| -- 7 | |
| DROP TABLE IF EXISTS _products_and_predl; | |
| CREATE TEMPORARY TABLE _products_and_predl AS ( | |
| SELECT company_id, rubric_id, rubric_l1_id, | |
| rubric_l2_id, rubric_l3_id, rubric_l4_id, | |
| region_id | |
| FROM | |
| _lots_regions | |
| WHERE | |
| is_demand = FALSE | |
| UNION ALL | |
| SELECT company_id, rubric_id, rubric_l1_id, | |
| rubric_l2_id, rubric_l3_id, rubric_l4_id, | |
| region_id | |
| FROM | |
| _products | |
| ); | |
| CREATE INDEX "_products_and_predl_idx" ON "_lots_regions" | |
| USING btree ("region_id", "rubric_id"); | |
| INSERT INTO _providers_stat(region_id, rubric_id, providers_stat) | |
| SELECT | |
| region_id, 0, COUNT(DISTINCT company_id) AS providers_stat | |
| FROM | |
| _products_and_predl | |
| WHERE rubric_id IS NOT NULL | |
| GROUP BY | |
| region_id | |
| UNION | |
| SELECT | |
| region_id, rubric_id, COUNT(DISTINCT company_id) AS providers_stat | |
| FROM | |
| _products_and_predl | |
| WHERE rubric_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l1_id, COUNT(DISTINCT company_id) | |
| FROM | |
| _products_and_predl | |
| WHERE rubric_l1_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l1_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l2_id, COUNT(DISTINCT company_id) | |
| FROM | |
| _products_and_predl | |
| WHERE rubric_l2_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l2_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l3_id, COUNT(DISTINCT company_id) | |
| FROM | |
| _products_and_predl | |
| WHERE rubric_l3_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l3_id | |
| UNION | |
| SELECT | |
| region_id, rubric_l4_id, COUNT(DISTINCT company_id) | |
| FROM | |
| _products_and_predl | |
| WHERE rubric_l4_id IS NOT NULL | |
| GROUP BY | |
| region_id, rubric_l4_id; | |
| -- 7 | |
| DELETE FROM rubric_statistics; | |
| INSERT INTO rubric_statistics(region_id, rubric_id, predl_stat, spros_stat, products_stat, predl_prod_stat, providers_stat_predl, providers_stat_spros) | |
| SELECT | |
| COALESCE(offer.region_id, ps.region_id) AS region_id, | |
| COALESCE(offer.rubric_id, ps.rubric_id) AS rubric_id, | |
| COALESCE(predl_stat, 0) AS predl_stat, | |
| COALESCE(spros_stat, 0) AS spros_stat, | |
| COALESCE(products_stat, 0) AS products_stat, | |
| (COALESCE(predl_stat, 0) + COALESCE(products_stat, 0)) AS predl_prod_stat, | |
| COALESCE(providers_stat_predl, 0) AS providers_stat_predl, | |
| COALESCE(providers_stat_spros, 0) AS providers_stat_spros | |
| FROM | |
| ( | |
| SELECT | |
| COALESCE(lp.region_id, ls.region_id) AS region_id, | |
| COALESCE(lp.rubric_id, ls.rubric_id) AS rubric_id, | |
| COALESCE(lp.predl_stat, 0) AS predl_stat, | |
| COALESCE(ls.spros_stat, 0) AS spros_stat, | |
| COALESCE(ps.providers_stat, 0) AS providers_stat_predl, | |
| COALESCE(ls.providers_stat, 0) AS providers_stat_spros | |
| FROM | |
| ( | |
| SELECT | |
| region_id, rubric_id, SUM(predl_stat) AS predl_stat | |
| FROM | |
| _lots_predl_stat | |
| GROUP BY region_id, rubric_id | |
| ) lp INNER JOIN | |
| ( | |
| SELECT | |
| region_id, rubric_id, MAX(providers_stat) AS providers_stat | |
| FROM | |
| _providers_stat | |
| GROUP BY region_id, rubric_id | |
| ) ps ON (lp.region_id = ps.region_id AND lp.rubric_id = ps.rubric_id) FULL JOIN | |
| _lots_spros_stat ls ON (lp.region_id = ls.region_id AND lp.rubric_id = ls.rubric_id) | |
| ) offer | |
| FULL JOIN | |
| ( | |
| SELECT | |
| region_id, rubric_id, MAX(products_stat) AS products_stat | |
| FROM | |
| _prod_stat | |
| GROUP BY region_id, rubric_id | |
| ) ps ON (offer.region_id = ps.region_id AND offer.rubric_id = ps.rubric_id); | |
| DROP TABLE _lots_regions; | |
| DROP TABLE _lots_predl_stat; | |
| DROP TABLE _lots_spros_stat; | |
| DROP TABLE _prod_stat; | |
| DROP TABLE _products; | |
| DROP TABLE _products_and_predl; | |
| DROP TABLE _providers_stat; | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: process_synonyms(); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION process_synonyms() RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| PERFORM import.log_message('Загружаем синонимы'); | |
| DELETE FROM search.synonyms; -- truncate нельзя | |
| INSERT INTO search.synonyms (rubric_id, rubric_level, keyword, synonym) | |
| SELECT | |
| rubric_id, lev, lower(keyword), lower(synonym) | |
| FROM | |
| import.vw_site_all_synonym; | |
| PERFORM import.log_message('Синонимы загружены'); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: queue_logo_to_delete(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION queue_logo_to_delete(oferta_firm_id integer) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| INSERT INTO import.logo_to_upload (firm_id, to_delete) | |
| VALUES (oferta_firm_id, TRUE); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: queue_logo_to_upload(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION queue_logo_to_upload(oferta_firm_id integer) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| INSERT INTO import.logo_to_upload (firm_id, to_delete) | |
| VALUES (oferta_firm_id, FALSE); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: queue_orig_price_to_delete(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION queue_orig_price_to_delete(oferta_firm_id integer) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| INSERT INTO import.orig_price_to_upload (firm_id, to_delete) | |
| VALUES (oferta_firm_id, TRUE); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: queue_orig_price_to_upload(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION queue_orig_price_to_upload(oferta_firm_id integer) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| INSERT INTO import.orig_price_to_upload (firm_id, to_delete) | |
| VALUES (oferta_firm_id, FALSE); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: update_company(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION update_company(firm_id integer) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| firm RECORD; | |
| BEGIN | |
| SELECT * INTO firm FROM import.vw_site_firm WHERE oferta_id = firm_id; | |
| UPDATE companies SET | |
| ( | |
| name, name_rest, synonym, | |
| description, updated_at, industries_denorm, | |
| regions, title_ord, first_letter_ord, | |
| oferta_sync_id, oferta_sync_dt, | |
| annonce, has_email, pricelist_actualized_at, | |
| oferta_online_flag | |
| ) = ( | |
| firm.name, firm.opf_name, firm.alt_name, | |
| firm.annotation, firm.modified, | |
| ( | |
| SELECT | |
| array_to_string(array_accum(industry_id), ',') | |
| FROM | |
| import.companies_industries | |
| WHERE | |
| firm_id = firm.oferta_id | |
| GROUP BY firm_id LIMIT 1 | |
| ), -- industries denorm | |
| ( | |
| SELECT | |
| array_to_string(array_accum(region_id), ',') | |
| FROM | |
| import.companies_regions | |
| WHERE | |
| company_id = firm.oferta_id | |
| GROUP BY company_id LIMIT 1 | |
| ), -- regions | |
| import.company_title_ordinal(firm.name), | |
| import.company_first_letter_ord(firm.name), | |
| firm.oferta_id, NOW(), | |
| substring(firm.annotation from 1 for 250), | |
| CASE WHEN | |
| ( | |
| SELECT | |
| COUNT(*) | |
| FROM | |
| import.vw_site_estate e INNER JOIN | |
| import.vw_site_estate_contact c ON c.estate_id = e.id AND c.contact_type = 3 | |
| WHERE | |
| e.oferta_firm_id = firm.oferta_id | |
| ) > 0 THEN TRUE ELSE FALSE END, -- has email | |
| COALESCE( | |
| (SELECT actualization_date FROM import.vw_site_modified_offer WHERE firm_id = firm.oferta_id LIMIT 1), | |
| (SELECT pricelist_actualized_at FROM companies WHERE oferta_sync_id = firm.oferta_id LIMIT 1), | |
| (SELECT actualization_date FROM price_list_rows WHERE company_id = firm.site_id AND is_deleted = FALSE LIMIT 1) | |
| ), | |
| firm.is_online_edit | |
| ) | |
| WHERE id = firm.site_id AND (oferta_online_flag = FALSE OR oferta_online_flag IS NULL); | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: update_company_regions_commercials_for_company(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION update_company_regions_commercials_for_company(_company_id integer) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| rlimit RECORD; | |
| dep_id INTEGER; | |
| region_root INTEGER; | |
| BEGIN | |
| DELETE FROM company_region_commercials WHERE company_id = _company_id; | |
| INSERT INTO company_region_commercials ( | |
| company_id, | |
| region_id, | |
| department_id, | |
| bold, | |
| region_limit, | |
| created_at, | |
| updated_at | |
| ) | |
| SELECT | |
| f.site_id, | |
| rl.region_id, | |
| COALESCE( | |
| ( | |
| SELECT a.id | |
| FROM | |
| addresses a INNER JOIN | |
| cities c ON a.city_id = c.id INNER JOIN | |
| provinces p ON c.province_id = p.id INNER JOIN | |
| regions r ON p.region_id = r.id | |
| WHERE | |
| a.company_id = f.site_id AND | |
| r.id = rl.region_id | |
| ORDER BY position ASC | |
| LIMIT 1 | |
| ), | |
| (SELECT id FROM addresses WHERE company_id = f.site_id ORDER BY position ASC LIMIT 1) | |
| ) AS department_id, | |
| rb.region_id IS NULL AS bold, | |
| rl.limit_value, | |
| clock_timestamp(), | |
| clock_timestamp() | |
| FROM | |
| import.companies_regions_limits rl LEFT JOIN | |
| import.companies_regions_bold rb ON rl.firm_id = rb.firm_id AND rl.region_id = rb.region_id INNER JOIN | |
| import.vw_site_firm f ON rl.firm_id = f.oferta_id | |
| WHERE | |
| f.site_id = _company_id; | |
| RETURN 1; | |
| END; | |
| $$; | |
| -- | |
| -- Name: FUNCTION update_company_regions_commercials_for_company(_company_id integer); Type: COMMENT; Schema: import; Owner: - | |
| -- | |
| COMMENT ON FUNCTION update_company_regions_commercials_for_company(_company_id integer) IS 'Пересчитывает коммерческие регионы для компании по SITE_ID'; | |
| -- | |
| -- Name: update_company_regions_for_company(integer); Type: FUNCTION; Schema: import; Owner: - | |
| -- | |
| CREATE FUNCTION update_company_regions_for_company(_company_id integer) RETURNS integer | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| DELETE FROM company_regions WHERE company_id = _company_id; | |
| INSERT INTO company_regions (company_id, region_id, department_id, created_at, updated_at) | |
| SELECT DISTINCT | |
| c.id AS company_id, p.region_id, | |
| ( | |
| SELECT ad.id FROM | |
| addresses ad | |
| INNER JOIN cities cii ON ad.city_id = cii.id | |
| INNER JOIN provinces prov ON cii.province_id = prov.id | |
| WHERE | |
| ad.company_id = c.id AND | |
| prov.region_id = p.region_id | |
| ORDER BY position ASC | |
| LIMIT 1 | |
| ) AS department_id, NOW(), NOW() | |
| FROM | |
| companies c | |
| INNER JOIN addresses a ON a.company_id = c.id | |
| INNER JOIN cities ci ON a.city_id = ci.id | |
| INNER JOIN provinces p ON ci.province_id = p.id | |
| WHERE | |
| c.id = _company_id | |
| UNION | |
| SELECT | |
| c.id AS company_id, roots.id AS region_id, | |
| ( | |
| SELECT ad.id | |
| FROM | |
| addresses ad | |
| INNER JOIN cities cii ON ad.city_id = cii.id | |
| INNER JOIN provinces prov ON cii.province_id = prov.id | |
| WHERE | |
| prov.region_id IN | |
| ( | |
| SELECT rr.id FROM regions rr WHERE | |
| (rr.rgt - rr.lft) = 1 AND | |
| rr.id != root_region.id AND | |
| rr.lft >= root_region.lft AND | |
| rr.rgt <= root_region.rgt | |
| ) AND | |
| ad.company_id = c.id | |
| ORDER BY ad.position | |
| LIMIT 1 | |
| ) AS department_id, NOW(), NOW() | |
| FROM | |
| companies c | |
| INNER JOIN addresses a ON a.company_id = c.id | |
| INNER JOIN cities ci ON a.city_id = ci.id | |
| INNER JOIN provinces p ON ci.province_id = p.id | |
| INNER JOIN regions r ON p.region_id = r.id | |
| INNER JOIN | |
| ( | |
| SELECT | |
| r.id AS child_id, r.name, | |
| ( | |
| SELECT rr.id FROM regions rr | |
| WHERE rr.lft <= r.lft AND rr.rgt >= r.rgt | |
| ORDER BY rr.lft LIMIT 1 | |
| ) AS id | |
| FROM | |
| regions r | |
| WHERE (r.rgt - r.lft) = 1 | |
| ) roots ON r.id = roots.child_id | |
| INNER JOIN regions AS root_region ON roots.id = root_region.id | |
| WHERE | |
| c.id = _company_id; | |
| RETURN 1; | |
| END; | |
| $$; | |
| SET default_tablespace = ''; | |
| SET default_with_oids = false; | |
| -- | |
| -- Name: companies_images_to_delete; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE companies_images_to_delete ( | |
| id integer NOT NULL, | |
| class_name character varying(50) | |
| ); | |
| -- | |
| -- Name: TABLE companies_images_to_delete; Type: COMMENT; Schema: import; Owner: - | |
| -- | |
| COMMENT ON TABLE companies_images_to_delete IS 'Сюда вставляются изображения, которые следует удалить перед началом большого импорта'; | |
| -- | |
| -- Name: companies_industries; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE companies_industries ( | |
| firm_id integer NOT NULL, | |
| industry_id integer NOT NULL | |
| ); | |
| -- | |
| -- Name: TABLE companies_industries; Type: COMMENT; Schema: import; Owner: - | |
| -- | |
| COMMENT ON TABLE companies_industries IS 'Сюда перед началом большого импорта вставляются денормализованные данные по привязкам компаний к отраслевому рубрикатору'; | |
| -- | |
| -- Name: companies_regions; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE companies_regions ( | |
| company_id integer NOT NULL, | |
| region_id integer NOT NULL | |
| ); | |
| -- | |
| -- Name: TABLE companies_regions; Type: COMMENT; Schema: import; Owner: - | |
| -- | |
| COMMENT ON TABLE companies_regions IS 'Сюда перед началом большого импорта вставляются привязки компаний к регионам'; | |
| -- | |
| -- Name: companies_regions_bold; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE companies_regions_bold ( | |
| firm_id integer NOT NULL, | |
| region_id integer NOT NULL | |
| ); | |
| -- | |
| -- Name: TABLE companies_regions_bold; Type: COMMENT; Schema: import; Owner: - | |
| -- | |
| COMMENT ON TABLE companies_regions_bold IS 'Сюда перед началом большого импорта вставляются метки жирности лотов компаний в регионах (берется из vw_site_firm.region_bold)'; | |
| -- | |
| -- Name: companies_regions_limits; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE companies_regions_limits ( | |
| firm_id integer NOT NULL, | |
| region_id integer NOT NULL, | |
| limit_value integer NOT NULL | |
| ); | |
| -- | |
| -- Name: TABLE companies_regions_limits; Type: COMMENT; Schema: import; Owner: - | |
| -- | |
| COMMENT ON TABLE companies_regions_limits IS 'Сюда перед началом большого импорта вставляются коммерческие привязки компаний к регионам'; | |
| -- | |
| -- Name: import_log; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE import_log ( | |
| id integer NOT NULL, | |
| event_date timestamp(0) without time zone NOT NULL, | |
| message character varying(500) NOT NULL | |
| ); | |
| -- | |
| -- Name: import_log_id_seq; Type: SEQUENCE; Schema: import; Owner: - | |
| -- | |
| CREATE SEQUENCE import_log_id_seq | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MAXVALUE | |
| NO MINVALUE | |
| CACHE 1; | |
| -- | |
| -- Name: import_log_id_seq; Type: SEQUENCE OWNED BY; Schema: import; Owner: - | |
| -- | |
| ALTER SEQUENCE import_log_id_seq OWNED BY import_log.id; | |
| -- | |
| -- Name: logo_to_upload; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE logo_to_upload ( | |
| firm_id integer NOT NULL, | |
| to_delete boolean DEFAULT false NOT NULL | |
| ); | |
| -- | |
| -- Name: TABLE logo_to_upload; Type: COMMENT; Schema: import; Owner: - | |
| -- | |
| COMMENT ON TABLE logo_to_upload IS 'Очередь на удаление или загрузку логотипов после импорта, разбор идёт в RoR'; | |
| SET default_with_oids = true; | |
| -- | |
| -- Name: new_addresses_ids; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE new_addresses_ids ( | |
| oferta_id integer NOT NULL, | |
| address_id integer NOT NULL | |
| ); | |
| SET default_with_oids = false; | |
| -- | |
| -- Name: new_companies_ids; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE new_companies_ids ( | |
| oferta_id integer NOT NULL, | |
| site_id integer NOT NULL | |
| ); | |
| -- | |
| -- Name: new_offer_ids; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE new_offer_ids ( | |
| id integer NOT NULL | |
| ); | |
| -- | |
| -- Name: orig_price_to_upload; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE orig_price_to_upload ( | |
| firm_id integer NOT NULL, | |
| to_delete boolean DEFAULT false NOT NULL | |
| ); | |
| -- | |
| -- Name: TABLE orig_price_to_upload; Type: COMMENT; Schema: import; Owner: - | |
| -- | |
| COMMENT ON TABLE orig_price_to_upload IS 'Очередь на удаление или загрузку оригинальных прайсов после импорта, разбор идёт в RoR'; | |
| -- | |
| -- Name: rubrics_denormalization_tree; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE rubrics_denormalization_tree ( | |
| rubric_id integer NOT NULL, | |
| rubric_l1_id integer, | |
| rubric_l2_id integer, | |
| rubric_l3_id integer, | |
| rubric_l4_id integer | |
| ); | |
| -- | |
| -- Name: TABLE rubrics_denormalization_tree; Type: COMMENT; Schema: import; Owner: - | |
| -- | |
| COMMENT ON TABLE rubrics_denormalization_tree IS 'Список листовых рубрик с перечислением всех родителей. Используется при обработке лотов.'; | |
| -- | |
| -- Name: vw_site_actualize_offer; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_actualize_offer ( | |
| id integer NOT NULL, | |
| actualization_date timestamp(0) without time zone | |
| ); | |
| -- | |
| -- Name: vw_site_all_synonym; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_all_synonym ( | |
| rubric_id integer NOT NULL, | |
| lev smallint NOT NULL, | |
| keyword character varying(255), | |
| synonym character varying(255) NOT NULL | |
| ); | |
| -- | |
| -- Name: vw_site_city; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_city ( | |
| id integer NOT NULL, | |
| name character varying(255), | |
| province_id integer, | |
| code character varying(5), | |
| capital boolean, | |
| city_type_id integer, | |
| created timestamp(0) without time zone, | |
| modified timestamp(0) without time zone | |
| ); | |
| -- | |
| -- Name: vw_site_city_type; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_city_type ( | |
| id integer NOT NULL, | |
| name character varying(256), | |
| short_name character varying(256), | |
| kladr_code integer, | |
| shortname1 character varying(256) | |
| ); | |
| -- | |
| -- Name: vw_site_country; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_country ( | |
| id integer NOT NULL, | |
| name character varying(128) NOT NULL, | |
| code character varying(10), | |
| created timestamp(0) without time zone, | |
| modified timestamp(0) without time zone | |
| ); | |
| -- | |
| -- Name: vw_site_deleted_firm; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_deleted_firm ( | |
| site_id integer NOT NULL | |
| ); | |
| -- | |
| -- Name: vw_site_deleted_offer; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_deleted_offer ( | |
| id integer NOT NULL, | |
| firm_id integer, | |
| site_firm_id integer | |
| ); | |
| -- | |
| -- Name: vw_site_estate; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_estate ( | |
| id integer NOT NULL, | |
| oferta_firm_id integer, | |
| site_firm_id integer, | |
| oid smallint, | |
| name character varying(256), | |
| city_id integer, | |
| street_id integer, | |
| house character varying(10), | |
| letter character varying(40), | |
| address_other character varying(256), | |
| map_x integer, | |
| map_y integer | |
| ); | |
| -- | |
| -- Name: vw_site_estate_contact; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_estate_contact ( | |
| id integer NOT NULL, | |
| estate_id integer NOT NULL, | |
| oid smallint, | |
| contact_type integer, | |
| value character varying(128) | |
| ); | |
| -- | |
| -- Name: vw_site_estate_phone; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_estate_phone ( | |
| id integer NOT NULL, | |
| estate_id integer NOT NULL, | |
| oid smallint, | |
| prefix character varying(10), | |
| phone character varying(10), | |
| phone_type smallint, | |
| comm character varying(2000) | |
| ); | |
| -- | |
| -- Name: vw_site_firm; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_firm ( | |
| oferta_id integer NOT NULL, | |
| site_id integer, | |
| name character varying(255), | |
| alt_name character varying(255), | |
| opf_name character varying(256), | |
| annotation text, | |
| is_online_edit boolean, | |
| industry_str character varying(4000), | |
| is_new boolean, | |
| package smallint, | |
| region_limits character varying(4000), | |
| region_bold character varying(4000), | |
| created timestamp(0) without time zone, | |
| modified timestamp(0) without time zone, | |
| logo_ext character varying(25), | |
| has_original_price boolean, | |
| has_logo boolean | |
| ); | |
| -- | |
| -- Name: vw_site_firm_site; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_firm_site ( | |
| id integer NOT NULL, | |
| oferta_firm_id integer NOT NULL, | |
| site_firm_id integer, | |
| oid smallint, | |
| url character varying(256) | |
| ); | |
| -- | |
| -- Name: vw_site_industry; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_industry ( | |
| id integer NOT NULL, | |
| parent_id integer, | |
| name character varying(512), | |
| created timestamp(0) without time zone, | |
| modified timestamp(0) without time zone, | |
| handle character varying(25), | |
| target_id integer, | |
| oid smallint, | |
| path character varying(4000) | |
| ); | |
| -- | |
| -- Name: vw_site_modified_city; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_modified_city ( | |
| id integer NOT NULL, | |
| name character varying(255), | |
| province_id integer, | |
| code character varying(5), | |
| capital boolean, | |
| city_type_id integer, | |
| created timestamp(0) without time zone, | |
| modified timestamp(0) without time zone | |
| ); | |
| -- | |
| -- Name: vw_site_modified_country; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_modified_country ( | |
| id integer NOT NULL, | |
| name character varying(128) NOT NULL, | |
| code character varying(10), | |
| created timestamp(0) without time zone, | |
| modified timestamp(0) without time zone | |
| ); | |
| -- | |
| -- Name: vw_site_modified_firm; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_modified_firm ( | |
| oferta_id integer NOT NULL, | |
| site_id integer, | |
| is_new boolean | |
| ); | |
| -- | |
| -- Name: vw_site_modified_offer; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_modified_offer ( | |
| id integer NOT NULL, | |
| firm_id integer, | |
| site_firm_id integer, | |
| rubric_id integer, | |
| price_from double precision, | |
| price_to double precision, | |
| is_price_from boolean, | |
| c_currency_id smallint DEFAULT 3 NOT NULL, | |
| title character varying(255) NOT NULL, | |
| actualization_date timestamp(0) without time zone, | |
| prop_modified timestamp(0) without time zone, | |
| is_demand boolean DEFAULT false NOT NULL, | |
| x_city_link character varying(4000), | |
| sort_level smallint, | |
| pricesort double precision | |
| ); | |
| -- | |
| -- Name: vw_site_modified_province; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_modified_province ( | |
| id integer NOT NULL, | |
| name character varying(255), | |
| country_id integer, | |
| area_id integer, | |
| modified timestamp(0) without time zone | |
| ); | |
| -- | |
| -- Name: vw_site_modified_street; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_modified_street ( | |
| id integer NOT NULL, | |
| city_id integer, | |
| c_street_type_id integer, | |
| name character varying(256), | |
| created timestamp(0) without time zone, | |
| modified timestamp(0) without time zone, | |
| parent_id integer | |
| ); | |
| -- | |
| -- Name: vw_site_province; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_province ( | |
| id integer NOT NULL, | |
| name character varying(255), | |
| country_id integer, | |
| area_id integer, | |
| created timestamp(0) without time zone, | |
| modified timestamp(0) without time zone | |
| ); | |
| -- | |
| -- Name: vw_site_rubric; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_rubric ( | |
| id integer NOT NULL, | |
| parent_id integer, | |
| title character varying(255), | |
| oid integer DEFAULT 0 NOT NULL, | |
| lev integer DEFAULT 0 NOT NULL, | |
| tag character varying(25), | |
| path character varying(255), | |
| modified timestamp(0) without time zone, | |
| created timestamp(0) without time zone, | |
| old_url character varying(2000), | |
| synonims character varying(100), | |
| is_prodnorm boolean DEFAULT false NOT NULL, | |
| num_left integer DEFAULT 0 NOT NULL, | |
| num_right integer DEFAULT 0 NOT NULL, | |
| is_leaf boolean DEFAULT false NOT NULL, | |
| next_level_prodnorm boolean DEFAULT false NOT NULL, | |
| prodnorm_prefix character varying(256), | |
| prodnorm_position smallint DEFAULT 0 NOT NULL | |
| ); | |
| -- | |
| -- Name: vw_site_rubric_industry; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_rubric_industry ( | |
| rubric_id integer NOT NULL, | |
| industry_id integer NOT NULL | |
| ); | |
| -- | |
| -- Name: vw_site_rubric_log; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_rubric_log ( | |
| rubric_id integer NOT NULL, | |
| event_date timestamp(0) without time zone NOT NULL, | |
| event_type smallint NOT NULL, | |
| event_param integer | |
| ); | |
| -- | |
| -- Name: vw_site_street; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_street ( | |
| id integer NOT NULL, | |
| city_id integer, | |
| c_street_type_id integer, | |
| name character varying(256), | |
| created timestamp(0) without time zone, | |
| modified timestamp(0) without time zone, | |
| parent_id integer | |
| ); | |
| -- | |
| -- Name: vw_site_street_type; Type: TABLE; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE vw_site_street_type ( | |
| id integer NOT NULL, | |
| name character varying(256), | |
| created timestamp(0) without time zone, | |
| created_user integer, | |
| modified timestamp(0) without time zone, | |
| modified_user integer, | |
| shortname character varying(256), | |
| kladr_code integer, | |
| shortname1 character varying(256) | |
| ); | |
| -- | |
| -- Name: id; Type: DEFAULT; Schema: import; Owner: - | |
| -- | |
| ALTER TABLE import_log ALTER COLUMN id SET DEFAULT nextval('import_log_id_seq'::regclass); | |
| -- | |
| -- Name: companies_logos_to_delete_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY companies_images_to_delete | |
| ADD CONSTRAINT companies_logos_to_delete_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: import_log_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY import_log | |
| ADD CONSTRAINT import_log_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: modified_city_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_modified_city | |
| ADD CONSTRAINT modified_city_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: new_companies_ids_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY new_companies_ids | |
| ADD CONSTRAINT new_companies_ids_pkey PRIMARY KEY (oferta_id); | |
| -- | |
| -- Name: new_offer_ids_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY new_offer_ids | |
| ADD CONSTRAINT new_offer_ids_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: rubrics_denormalization_tree_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY rubrics_denormalization_tree | |
| ADD CONSTRAINT rubrics_denormalization_tree_pkey PRIMARY KEY (rubric_id); | |
| -- | |
| -- Name: vw_site_actualize_offer_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_actualize_offer | |
| ADD CONSTRAINT vw_site_actualize_offer_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_city_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_city | |
| ADD CONSTRAINT vw_site_city_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_city_type_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_city_type | |
| ADD CONSTRAINT vw_site_city_type_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_country_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_country | |
| ADD CONSTRAINT vw_site_country_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_deleted_firm_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_deleted_firm | |
| ADD CONSTRAINT vw_site_deleted_firm_pkey PRIMARY KEY (site_id); | |
| -- | |
| -- Name: vw_site_deleted_offer_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_deleted_offer | |
| ADD CONSTRAINT vw_site_deleted_offer_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_estate_contact_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_estate_contact | |
| ADD CONSTRAINT vw_site_estate_contact_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_estate_phone_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_estate_phone | |
| ADD CONSTRAINT vw_site_estate_phone_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_estate_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_estate | |
| ADD CONSTRAINT vw_site_estate_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_firm_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_firm | |
| ADD CONSTRAINT vw_site_firm_pkey PRIMARY KEY (oferta_id); | |
| -- | |
| -- Name: vw_site_firm_site_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_firm_site | |
| ADD CONSTRAINT vw_site_firm_site_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_industry_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_industry | |
| ADD CONSTRAINT vw_site_industry_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_modified_country_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_modified_country | |
| ADD CONSTRAINT vw_site_modified_country_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_modified_firm_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_modified_firm | |
| ADD CONSTRAINT vw_site_modified_firm_pkey PRIMARY KEY (oferta_id); | |
| -- | |
| -- Name: vw_site_modified_offer_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_modified_offer | |
| ADD CONSTRAINT vw_site_modified_offer_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_modified_province_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_modified_province | |
| ADD CONSTRAINT vw_site_modified_province_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_modified_street_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_modified_street | |
| ADD CONSTRAINT vw_site_modified_street_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_province_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_province | |
| ADD CONSTRAINT vw_site_province_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_rubric_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_rubric | |
| ADD CONSTRAINT vw_site_rubric_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_street_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_street | |
| ADD CONSTRAINT vw_site_street_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: vw_site_street_type_pkey; Type: CONSTRAINT; Schema: import; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY vw_site_street_type | |
| ADD CONSTRAINT vw_site_street_type_pkey PRIMARY KEY (id); | |
| -- | |
| -- Name: companies_industries_firm_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX companies_industries_firm_id_idx ON companies_industries USING btree (firm_id); | |
| -- | |
| -- Name: companies_regions_bold_firm_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX companies_regions_bold_firm_id_idx ON companies_regions_bold USING btree (firm_id); | |
| -- | |
| -- Name: companies_regions_company_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX companies_regions_company_id_idx ON companies_regions USING btree (company_id); | |
| -- | |
| -- Name: companies_regions_limits_firm_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX companies_regions_limits_firm_id_idx ON companies_regions_limits USING btree (firm_id); | |
| -- | |
| -- Name: vw_site_estate_contact_estate_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX vw_site_estate_contact_estate_id_idx ON vw_site_estate_contact USING btree (estate_id); | |
| -- | |
| -- Name: vw_site_estate_oferta_firm_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX vw_site_estate_oferta_firm_id_idx ON vw_site_estate USING btree (oferta_firm_id); | |
| -- | |
| -- Name: vw_site_estate_phone_estate_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX vw_site_estate_phone_estate_id_idx ON vw_site_estate_phone USING btree (estate_id); | |
| -- | |
| -- Name: vw_site_estate_site_firm_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX vw_site_estate_site_firm_id_idx ON vw_site_estate USING btree (site_firm_id); | |
| -- | |
| -- Name: vw_site_firm_site_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX vw_site_firm_site_id_idx ON vw_site_firm USING btree (site_id); | |
| -- | |
| -- Name: vw_site_modified_firm_site_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX vw_site_modified_firm_site_id_idx ON vw_site_modified_firm USING btree (site_id); | |
| -- | |
| -- Name: vw_site_modified_offer_firm_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX vw_site_modified_offer_firm_id_idx ON vw_site_modified_offer USING btree (firm_id); | |
| -- | |
| -- Name: vw_site_rubric_industry_industry_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX vw_site_rubric_industry_industry_id_idx ON vw_site_rubric_industry USING btree (industry_id); | |
| -- | |
| -- Name: vw_site_rubric_industry_rubric_id_idx; Type: INDEX; Schema: import; Owner: -; Tablespace: | |
| -- | |
| CREATE INDEX vw_site_rubric_industry_rubric_id_idx ON vw_site_rubric_industry USING btree (rubric_id); | |
| -- | |
| -- PostgreSQL database dump complete | |
| -- | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment