feeder: update public.dates atomically (#44244)

This commit is contained in:
Benjamin Dauvergne 2020-06-19 11:39:22 +02:00
parent 579a118a5f
commit 6e4cef6d5c
2 changed files with 58 additions and 7 deletions

View File

@ -1,3 +1,20 @@
# wcs_olap
# Copyright (C) 2020 Entr'ouvert
#
# This program is free software: you can redistribute it and/or modify it
# under the terms of the GNU Affero General Public License as published
# by the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
import datetime
import json
import pytest
import pathlib
@ -96,6 +113,34 @@ def test_wcs_fixture(wcs, postgres_db, tmpdir, olap_cmd, caplog):
assert list(c.fetchall()) == expected_schema
# verify dates table
with postgres_db.conn() as conn:
with conn.cursor() as c:
c.execute('SELECT MIN(date) FROM public.dates')
assert c.fetchone()[0] == datetime.date(2010, 1, 1)
# delete some dates
with postgres_db.conn() as conn:
with conn.cursor() as c:
c.execute("DELETE FROM public.dates WHERE date < '2011-01-01' OR date > '2017-01-01'")
c.execute('COMMIT')
c.execute('SELECT MIN(date), MAX(date) FROM public.dates')
assert c.fetchone()[0:2] == (datetime.date(2011, 1, 1), datetime.date(2017, 1, 1))
# recreate missing dates
olap_cmd()
with postgres_db.conn() as conn:
with conn.cursor() as c:
c.execute('SELECT MIN(date), MAX(date) FROM public.dates')
last_date = (datetime.date.today() + datetime.timedelta(days=150)).replace(month=12, day=31)
assert c.fetchone()[0:2] == (datetime.date(2011, 1, 1), last_date)
c.execute('''SELECT COUNT(*) FROM
GENERATE_SERIES(%s::date, %s::date, '1 day'::interval) AS series(date)
FULL OUTER JOIN public.dates AS dates ON series.date = dates.date WHERE dates.date IS NULL OR series.date IS NULL''',
vars=[datetime.date(2011, 1, 1), last_date])
assert c.fetchone()[0] == 0
# verify JSON schema
with (olap_cmd.model_dir / 'olap.model').open() as fd, \
(pathlib.Path(__file__).parent / 'olap.model').open() as fd2:

View File

@ -345,16 +345,22 @@ class WcsOlapFeeder(object):
self.ex('DROP TABLE IF EXISTS %s CASCADE;' % tablename)
def do_dates_table(self):
self.ex("DROP TABLE IF EXISTS public.dates")
last_date = datetime.datetime.today().replace(month=12, day=31)
self.ex('''
CREATE TABLE public.dates AS (SELECT
self.ex('CREATE TABLE IF NOT EXISTS public.dates (date date, day text, month text)')
self.ex('SELECT MIN(date) FROM public.dates')
max_date = self.cur.fetchone()[0]
first_date = max_date or datetime.date(2010, 1, 1)
last_date = (datetime.date.today() + datetime.timedelta(days=150)).replace(month=12, day=31)
self.ex('''INSERT INTO public.dates SELECT
the_date.the_date::date AS date,
to_char(the_date.the_date, 'TMday') AS day,
to_char(the_date.the_date, 'TMmonth') AS month
FROM
generate_series('2010-01-01'::date, '%s'::date, '1 day'::interval)
AS the_date(the_date));''' % last_date.strftime('%Y-%m-%d'))
FROM generate_series(%s::date, %s::date, '1 day'::interval)
AS the_date(the_date)
LEFT JOIN public.dates AS dates
ON the_date.the_date = dates.date
WHERE dates.date IS NULL''',
vars=[first_date, last_date])
def create_table(self, name, columns, inherits=None, comment=None):
sql = 'CREATE TABLE %s' % quote(name)