From 89b0d17509757b41f64081630b7616e15cbc1e66 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Fri, 30 Aug 2019 14:53:43 +0200 Subject: [PATCH] =?utf8?q?DB-Upgrades=20f=C3=BCr=20Hintergrundjobs=20von?= =?utf8?q?=20Perl=20auf=20SQL=20umgestellt?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Rose-Models dürfen in DB-Upgrade-Scripten nicht verwendet werden, weil die Perl-Strukturdaten (MetaSetup) in dem Moment schon auf dem neuen Stand, die Datenbankstrukturen aber auf dem alten Stand sind. Daher schlagen bei Unterschieden (z.B. eine Spalte soll später noch angelegt werden, sie existiert aber im neuen MetaSetup schon) halt sämliche Operationen fehl. --- ...hange_create_periodic_invoices_to_daily.pl | 23 ------------------- ...ange_create_periodic_invoices_to_daily.sql | 12 ++++++++++ sql/Pg-upgrade2/background_jobs_3.pl | 18 --------------- sql/Pg-upgrade2/background_jobs_3.sql | 12 ++++++++++ .../background_jobs_clean_auth_sessions.pl | 21 ----------------- .../background_jobs_clean_auth_sessions.sql | 12 ++++++++++ sql/Pg-upgrade2/emmvee_background_jobs_2.pl | 18 --------------- sql/Pg-upgrade2/emmvee_background_jobs_2.sql | 12 ++++++++++ .../periodic_invoices_background_job.pl | 18 --------------- .../periodic_invoices_background_job.sql | 6 +++++ sql/Pg-upgrade2/self_test_background_job.pl | 18 --------------- sql/Pg-upgrade2/self_test_background_job.sql | 12 ++++++++++ 12 files changed, 66 insertions(+), 116 deletions(-) delete mode 100644 sql/Pg-upgrade2/background_job_change_create_periodic_invoices_to_daily.pl create mode 100644 sql/Pg-upgrade2/background_job_change_create_periodic_invoices_to_daily.sql delete mode 100755 sql/Pg-upgrade2/background_jobs_3.pl create mode 100644 sql/Pg-upgrade2/background_jobs_3.sql delete mode 100644 sql/Pg-upgrade2/background_jobs_clean_auth_sessions.pl create mode 100644 sql/Pg-upgrade2/background_jobs_clean_auth_sessions.sql delete mode 100644 sql/Pg-upgrade2/emmvee_background_jobs_2.pl create mode 100644 sql/Pg-upgrade2/emmvee_background_jobs_2.sql delete mode 100644 sql/Pg-upgrade2/periodic_invoices_background_job.pl create mode 100644 sql/Pg-upgrade2/periodic_invoices_background_job.sql delete mode 100644 sql/Pg-upgrade2/self_test_background_job.pl create mode 100644 sql/Pg-upgrade2/self_test_background_job.sql diff --git a/sql/Pg-upgrade2/background_job_change_create_periodic_invoices_to_daily.pl b/sql/Pg-upgrade2/background_job_change_create_periodic_invoices_to_daily.pl deleted file mode 100644 index ace35be49..000000000 --- a/sql/Pg-upgrade2/background_job_change_create_periodic_invoices_to_daily.pl +++ /dev/null @@ -1,23 +0,0 @@ -# @tag: background_job_change_create_periodic_invoices_to_daily -# @description: Hintergrundjob zum Erzeugen periodischer Rechnungen täglich ausführen -# @depends: release_3_0_0 -package SL::DBUpgrade2::background_job_change_create_periodic_invoices_to_daily; - -use strict; -use utf8; - -use parent qw(SL::DBUpgrade2::Base); - -use SL::DB::BackgroundJob; - -sub run { - my ($self) = @_; - - foreach my $job (@{ SL::DB::Manager::BackgroundJob->get_all(where => [ package_name => 'CreatePeriodicInvoices' ]) }) { - $job->update_attributes(cron_spec => '0 3 * * *', next_run_at => undef); - } - - return 1; -} - -1; diff --git a/sql/Pg-upgrade2/background_job_change_create_periodic_invoices_to_daily.sql b/sql/Pg-upgrade2/background_job_change_create_periodic_invoices_to_daily.sql new file mode 100644 index 000000000..ac90bb925 --- /dev/null +++ b/sql/Pg-upgrade2/background_job_change_create_periodic_invoices_to_daily.sql @@ -0,0 +1,12 @@ +-- @tag: background_job_change_create_periodic_invoices_to_daily +-- @description: Hintergrundjob zum Erzeugen periodischer Rechnungen täglich ausführen +-- @depends: release_3_0_0 +UPDATE background_jobs +SET cron_spec = '0 3 * * *', + next_run_at = CAST(current_date AS timestamp) + CAST( + (CASE + WHEN extract('hour' FROM current_timestamp) < 3 THEN '3 hours' + ELSE '1 day 3 hours' + END) AS interval + ) +WHERE package_name = 'CreatePeriodicInvoices'; diff --git a/sql/Pg-upgrade2/background_jobs_3.pl b/sql/Pg-upgrade2/background_jobs_3.pl deleted file mode 100755 index 65d07c9ff..000000000 --- a/sql/Pg-upgrade2/background_jobs_3.pl +++ /dev/null @@ -1,18 +0,0 @@ -# @tag: background_jobs_3 -# @description: Backgroundjob Cleanup einrichten -# @depends: emmvee_background_jobs_2 -package SL::DBUpgrade2::background_jobs_3; - -use strict; -use utf8; - -use parent qw(SL::DBUpgrade2::Base); - -use SL::BackgroundJob::BackgroundJobCleanup; - -sub run { - SL::BackgroundJob::BackgroundJobCleanup->create_job; - return 1; -} - -1; diff --git a/sql/Pg-upgrade2/background_jobs_3.sql b/sql/Pg-upgrade2/background_jobs_3.sql new file mode 100644 index 000000000..f3bd7650a --- /dev/null +++ b/sql/Pg-upgrade2/background_jobs_3.sql @@ -0,0 +1,12 @@ +-- @tag: background_jobs_3 +-- @description: Backgroundjob Cleanup einrichten +-- @depends: emmvee_background_jobs_2 +INSERT INTO background_jobs (type, package_name, active, cron_spec, next_run_at) +VALUES ('interval', 'BackgroundJobCleanup', true, '0 3 * * *', + CAST(current_date AS timestamp) + CAST( + (CASE + WHEN extract('hour' FROM current_timestamp) < 3 THEN '3 hours' + ELSE '1 day 3 hours' + END) AS interval + ) +); diff --git a/sql/Pg-upgrade2/background_jobs_clean_auth_sessions.pl b/sql/Pg-upgrade2/background_jobs_clean_auth_sessions.pl deleted file mode 100644 index cab9196e9..000000000 --- a/sql/Pg-upgrade2/background_jobs_clean_auth_sessions.pl +++ /dev/null @@ -1,21 +0,0 @@ -# @tag: background_jobs_clean_auth_sessions -# @description: Hintergrundjob zum Löschen abgelaufener Sessions -# @depends: release_3_1_0 -package SL::DBUpgrade2::background_jobs_clean_auth_sessions; - -use strict; -use utf8; - -use parent qw(SL::DBUpgrade2::Base); - -use SL::BackgroundJob::CleanAuthSessions; - -sub run { - my ($self) = @_; - - SL::BackgroundJob::CleanAuthSessions->create_job; - - return 1; -} - -1; diff --git a/sql/Pg-upgrade2/background_jobs_clean_auth_sessions.sql b/sql/Pg-upgrade2/background_jobs_clean_auth_sessions.sql new file mode 100644 index 000000000..35430a3ef --- /dev/null +++ b/sql/Pg-upgrade2/background_jobs_clean_auth_sessions.sql @@ -0,0 +1,12 @@ +-- @tag: background_jobs_clean_auth_sessions +-- @description: Hintergrundjob zum Löschen abgelaufener Sessions +-- @depends: release_3_1_0 +INSERT INTO background_jobs (type, package_name, active, cron_spec, next_run_at) +VALUES ('interval', 'CleanAuthSessions', true, '30 6 * * *', + CAST(current_date AS timestamp) + CAST( + (CASE + WHEN extract('hour' FROM current_timestamp) < 6 THEN '6 hours 30 minutes' + ELSE '1 day 6 hours 30 minutes' + END) AS interval + ) +); diff --git a/sql/Pg-upgrade2/emmvee_background_jobs_2.pl b/sql/Pg-upgrade2/emmvee_background_jobs_2.pl deleted file mode 100644 index a7b9e6dc3..000000000 --- a/sql/Pg-upgrade2/emmvee_background_jobs_2.pl +++ /dev/null @@ -1,18 +0,0 @@ -# @tag: emmvee_background_jobs_2 -# @description: Hintergrundjobs einrichten -# @depends: emmvee_background_jobs -package SL::DBUpgrade2::emmvee_background_jobs_2; - -use strict; -use utf8; - -use parent qw(SL::DBUpgrade2::Base); - -use SL::BackgroundJob::CleanBackgroundJobHistory; - -sub run { - SL::BackgroundJob::CleanBackgroundJobHistory->create_job; - return 1; -} - -1; diff --git a/sql/Pg-upgrade2/emmvee_background_jobs_2.sql b/sql/Pg-upgrade2/emmvee_background_jobs_2.sql new file mode 100644 index 000000000..a0d617852 --- /dev/null +++ b/sql/Pg-upgrade2/emmvee_background_jobs_2.sql @@ -0,0 +1,12 @@ +-- @tag: emmvee_background_jobs_2 +-- @description: Hintergrundjobs einrichten +-- @depends: emmvee_background_jobs +INSERT INTO background_jobs (type, package_name, active, cron_spec, next_run_at) +VALUES ('interval', 'CleanBackgroundJobHistory', true, '0 3 * * *', + CAST(current_date AS timestamp) + CAST( + (CASE + WHEN extract('hour' FROM current_timestamp) < 3 THEN '3 hours' + ELSE '1 day 3 hours' + END) AS interval + ) +); diff --git a/sql/Pg-upgrade2/periodic_invoices_background_job.pl b/sql/Pg-upgrade2/periodic_invoices_background_job.pl deleted file mode 100644 index 91b3a61ed..000000000 --- a/sql/Pg-upgrade2/periodic_invoices_background_job.pl +++ /dev/null @@ -1,18 +0,0 @@ -# @tag: periodic_invoices_background_job -# @description: Hintergrundjob zum Erzeugen wiederkehrender Rechnungen -# @depends: periodic_invoices -package SL::DBUpgrade2::periodic_invoices_background_job; - -use strict; -use utf8; - -use parent qw(SL::DBUpgrade2::Base); - -use SL::BackgroundJob::CreatePeriodicInvoices; - -sub run { - SL::BackgroundJob::CreatePeriodicInvoices->create_job; - return 1; -} - -1; diff --git a/sql/Pg-upgrade2/periodic_invoices_background_job.sql b/sql/Pg-upgrade2/periodic_invoices_background_job.sql new file mode 100644 index 000000000..74f21af52 --- /dev/null +++ b/sql/Pg-upgrade2/periodic_invoices_background_job.sql @@ -0,0 +1,6 @@ +-- @tag: periodic_invoices_background_job +-- @description: Hintergrundjob zum Erzeugen wiederkehrender Rechnungen +-- @depends: periodic_invoices +INSERT INTO background_jobs (type, package_name, active, cron_spec, next_run_at) +VALUES ('interval', 'CreatePeriodicInvoices', true, '0 3 1 * *', + date_trunc('month', current_date) + CAST('1 month 3 hours' AS interval)); diff --git a/sql/Pg-upgrade2/self_test_background_job.pl b/sql/Pg-upgrade2/self_test_background_job.pl deleted file mode 100644 index 468b79cc7..000000000 --- a/sql/Pg-upgrade2/self_test_background_job.pl +++ /dev/null @@ -1,18 +0,0 @@ -# @tag: self_test_background_job -# @description: Hintergrundjob für tägliche Selbsttests -# @depends: release_2_7_0 -package SL::DBUpgrade2::self_test_background_job; - -use strict; -use utf8; - -use parent qw(SL::DBUpgrade2::Base); - -use SL::BackgroundJob::SelfTest; - -sub run { - SL::BackgroundJob::SelfTest->create_job; - return 1; -} - -1; diff --git a/sql/Pg-upgrade2/self_test_background_job.sql b/sql/Pg-upgrade2/self_test_background_job.sql new file mode 100644 index 000000000..2d9a5e476 --- /dev/null +++ b/sql/Pg-upgrade2/self_test_background_job.sql @@ -0,0 +1,12 @@ +-- @tag: self_test_background_job +-- @description: Hintergrundjob für tägliche Selbsttests +-- @depends: release_2_7_0 +INSERT INTO background_jobs (type, package_name, active, cron_spec, next_run_at) +VALUES ('interval', 'SelfTest', true, '20 2 * * *', + CAST(current_date AS timestamp) + CAST( + (CASE + WHEN extract('hour' FROM current_timestamp) < 2 THEN '2 hours 20 minutes' + ELSE '1 day 2 hours 20 minutes' + END) AS interval + ) +); -- 2.20.1