Skip to content

Instantly share code, notes, and snippets.

@artkirienko

artkirienko/run_on_local_machines.sh

Last active September 23, 2015 15:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save artkirienko/baa82da80c7821a99e05 to your computer and use it in GitHub Desktop.
Save artkirienko/baa82da80c7821a99e05 to your computer and use it in GitHub Desktop.
Download ZIP
Rails import multiple local databases into one global
Raw
run_on_local_machines.sh
# (я решил не использовать grep / sed для анализа и редактирования каждго дампа,
# так как при помощи SQL-запросов я быстрее решу ту же задачу)
# шаг 0
# выгружаем из каждого локально развернутого приложения дамп базы данных
# suo_local - база из которой сгружаем
# db.sql - файл в который выгружаем
pg_dump suo_local -t slots -t conversation_fs -t users -t board_settings -t office_settings \
-t offices -t portal_tickets -t day_schedules -t activities -t register_action_normatives -t service_normatives \
-t eq_tickets -t unloading_events -t classifier_versions -t feedback_forms -t slots_counters -t classifiers \
-t dictionaries -t feedback_question_answers -t changes -t client_infos -t office_checks -t counters \
-t report_parameters -t soms_settings -t system_settings -t documents -t settings -t day_schedule_resources \
-t resources_users -t offices_resources -t creeping_line_patterns --no-privileges --no-acl --no-owner --data-only > db.sql
# на всякий случай
pg_dump suo_local --no-privileges --no-acl --no-owner > db.sql
Raw
run_on_main_server.sh
# предположим, что у нас есть 100 дампов в одном каталоге
# проименованных db1.sql - db100.sql
# идём по ним в цикле
# ВАЖНО! Этот скрипт использует файл: update_data.sql
repl="mydb" # основная база данных приложения на нашем сервере: mydb
# если нужно изменить название, то измените только константу repl
# следуюие две строки автоматически внесут изменения в файл update_data.sql
echo $repl
sed -i.bak 's/mydb/'$repl'/' update_data.sql
rm update_data.sql.bak
# создание лога ошибок загрузки в основную базу: error.log
echo "создание лога ошибок загрузки в основную базу: error.log"
touch error.log
# <--- НАЧАЛО ЦИКЛА --->
echo "Начало работы"
max=3 # количество дампов в директории - не забудьте поменять
for (( i=1; i <= $max; ++i ))
do
# шаг 1
# создание промежуточной базы
echo $i": создание промежуточной базы"
echo "CREATE DATABASE foo WITH TEMPLATE template0;" > create_db_foo.sql
psql -d $repl -f create_db_foo.sql
rm create_db_foo.sql
psql -d foo -f update_tables.sql
# шаг 2
# загружаем данные из i-го дампа в нашу промежуточную базу
echo "Загружаем данные из "$i"-го дампа в нашу промежуточную базу"
psql -d foo -f db$i.sql
# шаг 3
# загружаем в неё расширение для работы с несколькими базами данных в PostgreSQL
echo $i":загружаем в неё расширение для работы с несколькими базами данных в PostgreSQL"
echo "CREATE EXTENSION dblink;" > dblink_db_foo.sql
psql -d foo -f dblink_db_foo.sql
rm dblink_db_foo.sql
# шаг 4
# редактируем данные из i-го дампа в промежуточной базе
echo "Редактируем данные из "$i"-го дампа в промежуточной базе"
psql -d foo -f update_data.sql
# выгружаем из промежуточной базы
echo $i": выгружаем из промежуточной базы"
# выгружаем все таблицы:
pg_dump foo --no-privileges --no-acl --no-owner --data-only > data_ready.sql
# загружаем в основную базу
echo $i":загружаем в основную базу"
psql -d $repl -f data_ready.sql 2>>error.log
# удаляем лишние файлы
echo $i":удаляем лишние файлы"
rm db$i.sql
rm data_ready.sql
# удаляем промежуточную базу
echo $i":удаляем промежуточную базу"
echo "DROP DATABASE foo;" > drop_db_foo.sql
psql -d $repl -f drop_db_foo.sql
rm drop_db_foo.sql
done
echo "обновляем счетчики id"
psql -d $repl -f update_id_seq.sql
# <--- КОНЕЦ ЦИКЛА --->
echo "-----------------------------------"
echo "| НЕ ЗАБУДЬТЕ ПРОВЕРИТЬ error.log |"
echo "| на наличие ошибок при импорте в |"
echo "| основную базу |"
echo "| |"
echo "| ЭТО ВАЖНО! |"
echo "-----------------------------------"
Raw
update_data.sql
-- 'удаление ненужных таблиц'
SELECT 'удаление ненужных таблиц' AS info;
DROP TABLE users_id_seq;
DROP TABLE unloading_events_id_seq;
DROP TABLE system_settings_id_seq;
DROP TABLE soms_settings_id_seq;
DROP TABLE settings_id_seq;
DROP TABLE service_normatives_id_seq;
DROP TABLE report_parameters_id_seq;
DROP TABLE register_action_normatives_id_seq;
DROP TABLE portal_tickets_id_seq;
DROP TABLE offices_id_seq;
DROP TABLE office_settings_id_seq;
DROP TABLE office_checks_id_seq;
DROP TABLE feedback_question_answers_id_seq;
DROP TABLE feedback_forms_id_seq;
DROP TABLE eq_tickets_id_seq;
DROP TABLE documents_id_seq;
DROP TABLE dictionaries_id_seq;
DROP TABLE counters_id_seq;
DROP TABLE conversation_fs_id_seq;
DROP TABLE client_infos_id_seq;
DROP TABLE classifiers_id_seq;
DROP TABLE classifier_versions_id_seq;
DROP TABLE changes_id_seq;
DROP TABLE board_settings_id_seq;
DROP TABLE activities_id_seq;
SELECT 'таблица additional_service_times больше не нужна' AS info;
DROP TABLE additional_service_times;
SELECT 'таблица applicant_types больше не нужна' AS info;
DROP TABLE applicant_types;
--SELECT 'таблица creeping_line_patterns больше не нужна' AS info;
--DROP TABLE creeping_line_patterns;
SELECT 'таблица feedback_questions больше не нужна' AS info;
DROP TABLE feedback_questions;
SELECT 'таблица instance_types больше не нужна' AS info;
DROP TABLE instance_types;
SELECT 'таблица mortgage_types больше не нужна' AS info;
DROP TABLE mortgage_types;
--SELECT 'таблица offices_resources больше не нужна' AS info;
--DROP TABLE offices_resources;
SELECT 'таблица register_actions больше не нужна' AS info;
DROP TABLE register_actions;
SELECT 'таблица reports больше не нужна' AS info;
DROP TABLE reports;
SELECT 'таблица resources больше не нужна' AS info;
DROP TABLE resources;
SELECT 'таблица schema_migrations больше не нужна' AS info;
DROP TABLE schema_migrations;
SELECT 'таблица security_images больше не нужна' AS info;
DROP TABLE security_images;
SELECT 'таблица service_frgus больше не нужна' AS info;
DROP TABLE service_frgus;
SELECT 'таблица services больше не нужна' AS info;
DROP TABLE services;
SELECT 'таблица sources больше не нужна' AS info;
DROP TABLE sources;
SELECT 'таблица supplying_methods больше не нужна' AS info;
DROP TABLE supplying_methods;
-- обновляем все id, которые связаны с офисами
SELECT 'обновляем все id, которые связаны с офисами' AS info;
UPDATE offices SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE slots SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE users SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE board_settings SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE office_settings SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE day_schedules SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE activities SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE slots_counters SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE office_checks SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE counters SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE settings SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
UPDATE offices_resources SET office_id = office_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from offices')
AS t1(id int));
-- проставляем dictionary_id из основной базы
SELECT 'проставляем dictionary_id из основной базы' AS info;
-- UPDATE offices SET dictionary_id = (
-- SELECT id
-- FROM dblink('dbname=mydb', 'select id, keys -> ''code'' AS code FROM dictionaries')
-- AS t1(id int, code VARCHAR) WHERE code = (
-- SELECT keys -> 'code' AS code
-- FROM dictionaries
-- WHERE dictionaries.id = offices.dictionary_id
-- )
-- );
-- обновляем все id, которые связаны с пользователями
SELECT 'обновляем все id, которые связаны с users' AS info;
UPDATE users SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from users')
AS t1(id int));
UPDATE slots SET user_id = user_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from users')
AS t1(id int));
UPDATE feedback_forms SET user_id = user_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from users')
AS t1(id int));
UPDATE activities SET owner_id = owner_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from users')
AS t1(id int));
UPDATE counters SET logged_in = logged_in + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from users')
AS t1(id int))
WHERE logged_in IS NOT NULL;
UPDATE resources_users SET user_id = user_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from users')
AS t1(id int));
-- обновляем все id, которые связаны со slots
SELECT 'обновляем все id, которые связаны со slots' AS info;
UPDATE slots SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from slots')
AS t1(id int));
UPDATE feedback_forms SET slot_id = slot_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from slots')
AS t1(id int));
UPDATE slots_counters SET slot_id = slot_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from slots')
AS t1(id int));
-- обновляем все id, которые связаны с conversation_fs
SELECT 'обновляем все id, которые связаны с conversation_fs' AS info;
UPDATE conversation_fs SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from conversation_fs')
AS t1(id int));
-- обновляем все id, которые связаны с board_settings
SELECT 'обновляем все id, которые связаны с board_settings' AS info;
UPDATE board_settings SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from board_settings')
AS t1(id int));
UPDATE creeping_line_patterns SET board_setting_id = board_setting_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from board_settings')
AS t1(id int));
-- обновляем все id, которые связаны с creeping_line_patterns
SELECT 'обновляем все id, которые связаны с creeping_line_patterns' AS info;
UPDATE creeping_line_patterns SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from creeping_line_patterns')
AS t1(id int));
-- обновляем все id, которые связаны с office_settings
SELECT 'обновляем все id, которые связаны с office_settings' AS info;
UPDATE office_settings SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from office_settings')
AS t1(id int));
-- обновляем все id, которые связаны с portal_tickets
SELECT 'обновляем все id, которые связаны с portal_tickets' AS info;
UPDATE portal_tickets SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from portal_tickets')
AS t1(id int));
-- обвновляем все id, которые связаны с day_schedules
SELECT 'обвновляем все id, которые связаны с day_schedules' AS info;
UPDATE day_schedules SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from day_schedules')
AS t1(id int));
UPDATE day_schedule_resources SET day_schedule_id = day_schedule_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from day_schedules')
AS t1(id int));
-- обвновляем все id, которые связаны с activities
SELECT 'обвновляем все id, которые связаны с activities' AS info;
UPDATE activities SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from activities')
AS t1(id int));
-- обновляем все id, которые связаны с register_action_normatives
SELECT 'обновляем все id, которые связаны с register_action_normatives' AS info;
UPDATE register_action_normatives SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from register_action_normatives')
AS t1(id int));
-- обвновляем все id, которые связаны с service_normatives
SELECT 'обвновляем все id, которые связаны с service_normatives' AS info;
UPDATE service_normatives SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from service_normatives')
AS t1(id int));
-- обновляем все id, которые связаны с eq_tickets
SELECT 'обновляем все id, которые связаны с eq_tickets' AS info;
UPDATE eq_tickets SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from eq_tickets')
AS t1(id int));
-- обновляем все id, которые связаны с unloading_events
SELECT 'обновляем все id, которые связаны с unloading_events' AS info;
UPDATE unloading_events SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from unloading_events')
AS t1(id int));
-- обновляем все id, которые связаны с classifier_versions
SELECT 'обновляем все id, которые связаны с classifier_versions' AS info;
UPDATE classifier_versions SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from classifier_versions')
AS t1(id int));
-- обновляем все id, которые связаны с feedback_forms
SELECT 'обновляем все id, которые связаны с feedback_forms' AS info;
UPDATE feedback_forms SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from feedback_forms')
AS t1(id int));
-- обновляем все id, которые связаны с slots_counters
SELECT 'обновляем все id, которые связаны с slots_counters' AS info;
UPDATE slots_counters SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from slots_counters')
AS t1(id int));
-- под вопросом
-- обновляем все id, которые связаны с classifiers
SELECT 'обновляем все id, которые связаны с classifiers' AS info;
UPDATE classifiers SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from classifiers')
AS t1(id int));
UPDATE classifier_versions SET classifier_id = classifier_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from classifiers')
AS t1(id int));
-- обновляем все id, которые связаны с feedback_question_answers
SELECT 'обновляем все id, которые связаны с feedback_question_answers' AS info;
UPDATE feedback_question_answers SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from feedback_question_answers')
AS t1(id int));
-- обновляем все id, которые связаны с changes
SELECT 'обновляем все id, которые связаны с changes' AS info;
UPDATE changes SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from changes')
AS t1(id int));
-- обновляем все id, которые связаны с client_infos
SELECT 'обновляем все id, которые связаны с client_infos' AS info;
UPDATE client_infos SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from client_infos')
AS t1(id int));
-- обновляем все id, которые связаны с office_checks
SELECT 'обновляем все id, которые связаны с office_checks' AS info;
UPDATE office_checks SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from office_checks')
AS t1(id int));
-- обновляем все id, которые связаны с counters
SELECT 'обновляем все id, которые связаны с counters' AS info;
UPDATE counters SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from counters')
AS t1(id int));
UPDATE slots SET counter_id = counter_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from counters')
AS t1(id int));
UPDATE day_schedules SET schedulable_id = schedulable_id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from counters')
AS t1(id int));
-- обновляем все id, которые связаны с report_parameters
SELECT 'обновляем все id, которые связаны с report_parameters' AS info;
UPDATE report_parameters SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from report_parameters')
AS t1(id int));
-- обновляем все id, которые связаны с soms_settings
SELECT 'обновляем все id, которые связаны с soms_settings' AS info;
UPDATE soms_settings SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from soms_settings')
AS t1(id int));
-- обновляем все id, которые связаны с system_settings
SELECT 'обновляем все id, которые связаны с system_settings' AS info;
UPDATE system_settings SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from system_settings')
AS t1(id int));
-- обновляем все id, которые связаны с documents
SELECT 'обновляем все id, которые связаны с documents' AS info;
UPDATE documents SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from documents')
AS t1(id int));
-- обновляем все id, которые связаны с settings
SELECT 'обновляем все id, которые связаны с settings' AS info;
UPDATE settings SET id = id + (SELECT *
FROM dblink('dbname=mydb', 'select case when max(id) is null then 0 else max(id) end from settings')
AS t1(id int));
-- обновление учетки админа (чтобы избежать дублирования)
-- SELECT 'обновление учетки админа (чтобы избежать дублирования)' AS info;
-- UPDATE users SET email=CONCAT('admin', CAST(id AS VARCHAR),'@admin.ru') WHERE email='admin@admin.ru';
-- обновление email-ов пользователям с дублирующимися email-ами
SELECT 'обновление email-ов пользователям с дублирующимися email-ами' AS info;
UPDATE users SET email=CONCAT('a', CAST(id AS VARCHAR),CAST(email AS VARCHAR)) WHERE
email IN (SELECT email FROM dblink('dbname=mydb', 'select email from users')
AS t1(email VARCHAR));
-- удаление глобальных пользователей
SELECT 'удаление глобальных пользователей';
DELETE FROM users WHERE roles_mask = 4 OR roles_mask = 8 OR roles_mask = 12;
-- обновление типов офисов на центральные
SELECT 'обновление типов офисов на центральные' AS info;
UPDATE offices SET remote_instance=false;
-- 'удаление ненужных таблиц'
SELECT 'таблица dictionaries больше не нужна' AS info;
DROP TABLE dictionaries;
SELECT 'таблица activities больше не нужна' AS info;
DROP TABLE activities;
Raw
update_id_seq.sql
SELECT CASE WHEN max(id) IS NOT NULL THEN setval('activities_id_seq', max(id)) ELSE 0 END FROM activities;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('additional_service_times_id_seq', max(id)) ELSE 0 END FROM additional_service_times;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('applicant_types_id_seq', max(id)) ELSE 0 END FROM applicant_types;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('board_settings_id_seq', max(id)) ELSE 0 END FROM board_settings;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('changes_id_seq', max(id)) ELSE 0 END FROM changes;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('classifier_versions_id_seq', max(id)) ELSE 0 END FROM classifier_versions;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('classifiers_id_seq', max(id)) ELSE 0 END FROM classifiers;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('client_infos_id_seq', max(id)) ELSE 0 END FROM client_infos;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('conversation_fs_id_seq', max(id)) ELSE 0 END FROM conversation;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('counters_id_seq', max(id)) ELSE 0 END FROM counters;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('creeping_line_patterns_id_seq', max(id)) ELSE 0 END FROM creeping_line_patterns;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('day_schedules_id_seq', max(id)) ELSE 0 END FROM day_schedules;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('dictionaries_id_seq', max(id)) ELSE 0 END FROM dictionaries;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('documents_id_seq', max(id)) ELSE 0 END FROM documents;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('eq_tickets_id_seq', max(id)) ELSE 0 END FROM eq_tickets;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('feedback_forms_id_seq', max(id)) ELSE 0 END FROM feedback_forms;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('feedback_question_answers_id_seq', max(id)) ELSE 0 END FROM feedback_question_answers;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('feedback_questions_id_seq', max(id)) ELSE 0 END FROM feedback_questions;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('mortgage_types_id_seq', max(id)) ELSE 0 END FROM mortgage_types;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('office_checks_id_seq', max(id)) ELSE 0 END FROM office_checks;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('office_settings_id_seq', max(id)) ELSE 0 END FROM office_settings;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('offices_id_seq', max(id)) ELSE 0 END FROM offices;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('portal_tickets_id_seq', max(id)) ELSE 0 END FROM portal_tickets;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('register_action_normatives_id_seq', max(id)) ELSE 0 END FROM register_action_normatives;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('register_actions_id_seq', max(id)) ELSE 0 END FROM register_actions;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('report_parameters_id_seq', max(id)) ELSE 0 END FROM report_parameters;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('reports_id_seq', max(id)) ELSE 0 END FROM reports;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('resources_id_seq', max(id)) ELSE 0 END FROM resources;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('security_images_id_seq', max(id)) ELSE 0 END FROM security_images;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('service_frgus_id_seq', max(id)) ELSE 0 END FROM service_frgus;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('service_normatives_id_seq', max(id)) ELSE 0 END FROM service_normatives;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('services_id_seq', max(id)) ELSE 0 END FROM services;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('settings_id_seq', max(id)) ELSE 0 END FROM settings;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('slots_counters_id_seq', max(id)) ELSE 0 END FROM slots_counters;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('slots_id_seq', max(id)) ELSE 0 END FROM slots;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('soms_settings_id_seq', max(id)) ELSE 0 END FROM soms_settings;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('sources_id_seq', max(id)) ELSE 0 END FROM sources;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('supplying_methods_id_seq', max(id)) ELSE 0 END FROM supplying_methods;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('system_settings_id_seq', max(id)) ELSE 0 END FROM system_settings;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('unloading_events_id_seq', max(id)) ELSE 0 END FROM unloading_events;
SELECT CASE WHEN MAX(id) IS NOT NULL THEN setval('users_id_seq', max(id)) ELSE 0 END FROM users;
Raw
update_tables.sql
CREATE EXTENSION hstore;
-- добавить security_images (переносить security_images?)
CREATE TABLE security_images (
id integer,
file character varying(255),
active boolean,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE schema_migrations (
version character varying
);
CREATE TABLE mortgage_types (
id integer,
name character varying(255),
slug character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE activities (
id integer,
trackable_id integer,
trackable_type character varying(255),
owner_id integer,
owner_type character varying(255),
key character varying(255),
parameters text,
recipient_id integer,
recipient_type character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone,
office_id integer
);
CREATE TABLE additional_service_times (
id integer,
service_id integer,
object_count integer,
additional_time integer,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE applicant_types (
id integer,
name character varying(255),
slug character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE board_settings (
id integer,
rows integer,
number_of_columns integer,
header_1 character varying(255),
use_header_1 boolean,
header_2 character varying(255),
use_header_2 boolean,
use_sound_notification boolean,
use_voice_notification boolean,
use_video boolean,
use_creeping_line boolean,
office_id integer,
created_at timestamp without time zone,
updated_at timestamp without time zone,
color_scheme character varying(255),
use_rss boolean,
uuid character varying(255)
);
CREATE TABLE changes (
id integer,
uuid character varying(255),
entity character varying(255),
action character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE classifier_versions (
id integer,
code character varying(255),
name character varying(255),
last_revision character varying(255),
state character varying(255),
classifier_id integer,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE classifiers (
id integer,
code character varying(255),
name character varying(255),
last_revision character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone,
imported_at timestamp without time zone
);
CREATE TABLE client_infos (
id integer,
ip character varying(255),
info text,
created_at timestamp without time zone,
updated_at timestamp without time zone,
client_type character varying(255)
);
CREATE TABLE conversation_fs (
id integer,
service_date timestamp without time zone,
service_id integer,
resource_id integer,
status character varying(255),
region character varying(255),
subject character varying(255),
district character varying(255),
office character varying(255),
personal boolean,
wild_queue boolean,
source_id integer,
start_time timestamp without time zone,
end_time timestamp without time zone,
name character varying(255),
passport_number character varying(255),
passport_series character varying(255),
objects_count integer,
organization_name character varying(255),
ogrn bigint,
inn bigint,
kpp integer,
schedule_id integer,
created_at timestamp without time zone,
updated_at timestamp without time zone,
external_id integer,
duration integer,
service_name character varying(255),
process_out integer,
get_out integer
);
CREATE TABLE counters (
id integer,
name character varying(255),
logged_in integer,
tablo_address character varying(255),
office_id integer,
uuid character varying(255)
);
CREATE TABLE creeping_line_patterns (
id integer,
board_setting_id integer,
pattern character varying(255),
checked boolean,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE day_schedule_resources (
day_schedule_id integer,
resource_id integer
);
CREATE TABLE dictionaries (
id integer,
name text,
keys hstore,
properties hstore,
created_at timestamp without time zone,
updated_at timestamp without time zone,
key text
);
CREATE TABLE documents (
id integer,
code character varying(255),
service_code character varying(255),
name text,
status character varying(255)
);
CREATE TABLE eq_tickets (
id integer,
asvz_id integer,
num character varying(255),
inserted date,
rec_start integer,
rec_end integer,
queue_namer character varying(255),
status character varying(255),
office character varying(255),
rec_date date
);
CREATE TABLE feedback_forms (
id integer,
slot_id integer,
created_at timestamp without time zone,
updated_at timestamp without time zone,
mark integer,
feedback_panel boolean,
user_id integer
);
CREATE TABLE feedback_question_answers (
id integer,
feedback_question_id integer,
feedback_form_id integer,
mark integer,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE feedback_questions (
id integer,
question character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE instance_types (
central boolean
);
CREATE TABLE office_checks (
id integer,
code character varying(255),
value character varying(255),
office_id integer,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE office_settings (
id integer,
interval_between_call integer,
recalls_count integer,
booking_server_url character varying(255),
use_client_counter boolean,
office_without_terminal boolean,
office_id integer,
created_at timestamp without time zone,
updated_at timestamp without time zone,
time_zone character varying(255),
uuid character varying(255),
no_booking boolean,
security_image_id integer,
org_name character varying(255),
footnote character varying(255),
individual_call boolean
);
CREATE TABLE offices (
id integer,
name text,
number integer,
region text,
created_at timestamp without time zone,
updated_at timestamp without time zone,
dictionary_id integer,
no_terminal boolean,
uuid character varying(255),
remote_instance boolean,
rr_booking boolean,
office_asvz boolean,
active boolean
);
CREATE TABLE offices_resources (
resource_id integer,
office_id integer
);
CREATE TABLE portal_tickets_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE register_action_normatives (
id integer,
applicant_type_id integer,
supplying_method_id integer,
calendar_days integer,
work_days integer,
norm_deviation integer,
bad_deviation integer,
created_at timestamp without time zone,
updated_at timestamp without time zone,
register_action_id integer
);
CREATE TABLE register_actions (
id integer,
name character varying(255),
register_actions_id integer,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE report_parameters (
id integer,
name character varying(255),
code character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE reports (
id integer,
name character varying(255),
template_file_name character varying(255),
template_content_type character varying(255),
template_file_size integer,
template_updated_at timestamp without time zone
);
CREATE TABLE resources (
id integer,
name character varying(255),
service_id integer,
position integer,
can_one_day_previous boolean,
service_time integer,
after_service_time integer,
can_record boolean,
how_get text,
payment text,
term text,
recipients text,
reason text,
result text,
documents_push text,
documents_pull text,
information text,
control text,
procedure text,
organizations text,
acts text,
avg_time_fiz integer,
avg_time_jur integer,
norm_deviation integer,
bad_deviation integer,
ul_service_time integer
);
CREATE TABLE resources_users (
resource_id integer,
user_id integer
);
CREATE TABLE service_frgus (
id integer,
name character varying(255),
service_frgus_id integer,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE service_normatives (
id integer,
supplying_method_id integer,
mortgage_type_id integer,
calendar_days integer,
work_days integer,
norm_deviation integer,
bad_deviation integer,
created_at timestamp without time zone,
updated_at timestamp without time zone,
service_frgu_id integer
);
CREATE TABLE services (
id integer,
code character varying(255),
name text,
visible boolean,
status character varying(255),
letter_code character varying(255),
short_name text,
service_time integer,
parent_id integer
);
CREATE TABLE settings (
id integer,
code character varying(255),
val text,
office_id integer
);
CREATE TABLE slots (
id integer,
resource_id integer,
number character varying(255),
status character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone,
user_id integer,
window_number character varying(255),
service_date timestamp without time zone,
pin character varying(255),
start_time timestamp without time zone,
end_time timestamp without time zone,
mark character varying(255),
last_name character varying(255),
first_name character varying(255),
middle_name character varying(255),
denial_reason text,
actual_time_call timestamp without time zone,
counter_id integer,
exact_time boolean,
print_flag integer,
initial_service_date timestamp without time zone,
passport_number character varying(255),
passport_series character varying(255),
objects_count integer,
organization_name character varying(255),
ogrn bigint,
inn bigint,
kpp integer,
office_id integer,
recording_method character varying(255),
book_id character varying(255),
activate_pin_date timestamp without time zone,
service_time integer,
uuid character varying(255),
rank double precision
);
CREATE TABLE slots_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE soms_settings (
id integer,
name character varying(255),
value character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE sources (
id integer,
name character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE supplying_methods (
id integer,
name character varying(255),
slug character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE system_settings (
id integer,
code character varying(255),
value character varying(255),
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE unloading_events (
id integer,
event character varying(255),
event_date date,
state character varying(255),
asvz_office_id character varying(255),
unloading_rows integer,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE users (
id integer,
name character varying(255),
post character varying(255),
note text,
state character varying(255),
email character varying(255),
encrypted_password character varying(255),
reset_password_token character varying(255),
reset_password_sent_at timestamp without time zone,
remember_created_at timestamp without time zone,
sign_in_count integer,
current_sign_in_at timestamp without time zone,
last_sign_in_at timestamp without time zone,
current_sign_in_ip character varying(255),
last_sign_in_ip character varying(255),
roles_mask integer,
last_name character varying(255),
middle_name character varying(255),
first_name character varying(255),
office_id integer,
uuid character varying(255)
);
CREATE TABLE slots_counters (
id integer,
slot_id integer,
office_id integer,
counter_name character varying(255),
start_time timestamp without time zone,
end_time timestamp without time zone,
number character varying(255)
);
-- hele it goes again
CREATE TABLE activities_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE board_settings_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE changes_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE classifier_versions_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE classifiers_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE client_infos_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE conversation_fs_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE counters_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE day_schedules (
id integer,
wday integer,
"from" integer,
till integer,
schedulable_id integer,
exception boolean,
exception_day timestamp without time zone,
resource_id integer,
office_id integer,
uuid character varying(255),
day date,
holiday boolean
);
CREATE TABLE dictionaries_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE documents_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE eq_tickets_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE feedback_forms_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE feedback_question_answers_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE office_checks_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE office_settings_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE offices_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE portal_tickets (
id integer,
custom_id integer,
dept_id integer,
queue_id integer,
number character varying(255),
date character varying(255),
time integer,
duration integer,
houses integer,
applicants integer,
treatment integer,
member_fio character varying(255),
member_info character varying(255)
);
CREATE TABLE register_action_normatives_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE report_parameters_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE service_normatives_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE settings_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE soms_settings_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE system_settings_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE unloading_events_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
CREATE TABLE users_id_seq (
sequence_name name,
last_value bigint,
start_value bigint,
increment_by bigint,
max_value bigint,
min_value bigint,
cache_value bigint,
log_cnt bigint,
is_cycled boolean,
is_called boolean
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment