#!/usr/bin/env python3 """RSS/Atom-feed: migratie op een bestaande database, aan/uit, sleutels, inhoud en headers.""" import http.cookiejar import json import os import shutil import signal import socket import sqlite3 import subprocess import tempfile import time import urllib.error import urllib.request import xml.etree.ElementTree as ET from datetime import date, datetime, timedelta from pathlib import Path WEB = Path(__file__).resolve().parents[1] ARTIFACTS = WEB / 'tests/artifacts' ARTIFACTS.mkdir(exist_ok=True) ATOM = '{http://www.w3.org/2005/Atom}' checks = 0 def check(value, label): global checks checks += 1 assert value, label # Het schema van vóór de feed: zo staat de productiedatabase er nu bij. OLD_SCHEMA = ''' CREATE TABLE users (id TEXT PRIMARY KEY, name TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('admin','user')), theme TEXT NOT NULL DEFAULT 'system' CHECK(theme IN ('system','light','dark')), font TEXT NOT NULL DEFAULT 'serif' CHECK(font IN ('serif','sans','rounded')), created INTEGER NOT NULL); CREATE TABLE sessions (id TEXT PRIMARY KEY, payload TEXT NOT NULL, expires INTEGER NOT NULL); CREATE TABLE days (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT NOT NULL UNIQUE, title TEXT NOT NULL, created INTEGER NOT NULL, updated INTEGER NOT NULL); CREATE TABLE stories (id INTEGER PRIMARY KEY AUTOINCREMENT, day_id INTEGER NOT NULL REFERENCES days(id) ON DELETE CASCADE, position INTEGER NOT NULL CHECK(position BETWEEN 1 AND 3), title TEXT NOT NULL, created INTEGER NOT NULL, updated INTEGER NOT NULL, UNIQUE(day_id, position)); CREATE TABLE paragraphs (id INTEGER PRIMARY KEY AUTOINCREMENT, story_id INTEGER NOT NULL REFERENCES stories(id) ON DELETE CASCADE, position INTEGER NOT NULL CHECK(position >= 1), body TEXT NOT NULL, UNIQUE(story_id, position)); CREATE TABLE reads (user_id TEXT NOT NULL REFERENCES users(id), day_id INTEGER NOT NULL REFERENCES days(id) ON DELETE CASCADE, read_at INTEGER NOT NULL, PRIMARY KEY(user_id, day_id)); CREATE TABLE api_keys (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL REFERENCES users(id), name TEXT NOT NULL, token_hash TEXT NOT NULL UNIQUE, token_prefix TEXT NOT NULL, token_cipher TEXT NOT NULL DEFAULT '', created INTEGER NOT NULL, revoked INTEGER); CREATE TABLE wiki_categories (id INTEGER PRIMARY KEY AUTOINCREMENT, slug TEXT NOT NULL UNIQUE, title TEXT NOT NULL, position INTEGER NOT NULL, created INTEGER NOT NULL, updated INTEGER NOT NULL); CREATE TABLE wiki_pages (id INTEGER PRIMARY KEY AUTOINCREMENT, category_id INTEGER NOT NULL REFERENCES wiki_categories(id), slug TEXT NOT NULL UNIQUE, title TEXT NOT NULL, content TEXT NOT NULL, position INTEGER NOT NULL, created INTEGER NOT NULL, updated INTEGER NOT NULL); ''' # PHP heeft altijd de tijdzonedatabase; de CI-image van Python niet per se. today = date.fromisoformat(subprocess.check_output(['php', '-r', "echo (new DateTime('now', new DateTimeZone('Europe/Amsterdam')))->format('Y-m-d');"], text=True).strip()) past = [(today - timedelta(days=n)).isoformat() for n in (2, 1, 0)] future = (today + timedelta(days=3)).isoformat() with tempfile.TemporaryDirectory(prefix='feed-', dir=ARTIFACTS) as tmp: root = Path(tmp) for name in ['api.php', 'index.php', 'feed.php', 'router.php']: shutil.copy(WEB / name, root / name) shutil.copytree(WEB / 'lib', root / 'lib') (root / 'assets').mkdir() shutil.copy(WEB / 'assets/favicon.svg', root / 'assets/favicon.svg') (root / 'data').mkdir() dbfile = root / 'data/verhalenboek.sqlite' with sqlite3.connect(dbfile) as db: db.executescript(OLD_SCHEMA) now = int(time.time()) db.execute("INSERT INTO users VALUES ('local:test_user_1','test_user_1','admin','system','serif',?)", (now,)) db.execute("INSERT INTO days (date,title,created,updated) VALUES ('2020-01-01','Oude dag',?,?)", (now, now)) db.execute("INSERT INTO stories (day_id,position,title,created,updated) VALUES (1,1,'Oud verhaal',?,?)", (now, now)) db.execute("INSERT INTO paragraphs (story_id,position,body) VALUES (1,1,'Bewaard.')") with socket.socket() as sock: sock.bind(('127.0.0.1', 0)) port = sock.getsockname()[1] base = f'http://127.0.0.1:{port}' log = (root / 'server.log').open('w') process = subprocess.Popen(['php', '-d', 'opcache.enable=0', '-S', f'127.0.0.1:{port}', '-t', str(root), str(root / 'router.php')], stdout=log, stderr=log, start_new_session=True) class Client: def __init__(self, token=None): self.token = token self.opener = urllib.request.build_opener(urllib.request.HTTPCookieProcessor(http.cookiejar.CookieJar())) self.csrf = None def request(self, method, path, body=None, expected=200, headers=None): h = {'Accept': 'application/json', **(headers or {})} data = None if body is not None: data = json.dumps(body).encode() h['Content-Type'] = 'application/json' if self.token: h['Authorization'] = 'Bearer ' + self.token elif method not in ('GET', 'HEAD'): h['X-CSRF-Token'] = self.csrf or '' try: response = self.opener.open(urllib.request.Request(base + '/api.php' + path, data=data, headers=h, method=method)) except urllib.error.HTTPError as error: response = error with response: payload = json.loads(response.read().decode() or 'null') check(response.status == expected, f'{method} {path}: verwacht {expected}, kreeg {response.status} {payload}') return payload def me(self): data = self.request('GET', '/me') self.csrf = data['csrf'] return data def feed(url, expected=200, method='GET'): try: response = urllib.request.urlopen(urllib.request.Request(url, method=method)) except urllib.error.HTTPError as error: response = error with response: body = response.read().decode() headers = {k.lower(): v for k, v in response.headers.items()} check(response.status == expected, f'feed {url[-20:]}: verwacht {expected}, kreeg {response.status}') return body, headers try: for _ in range(100): try: urllib.request.urlopen(base + '/index.php').close() break except urllib.error.URLError: time.sleep(0.05) # Migratie: de eerste request maakt de nieuwe tabellen en laat bestaande data staan. admin = Client() me = admin.me() with sqlite3.connect(dbfile) as db: tables = {row[0] for row in db.execute("SELECT name FROM sqlite_master WHERE type='table'")} old_day = db.execute("SELECT title FROM days WHERE date='2020-01-01'").fetchone() check({'app_settings', 'feed_tokens'} <= tables, 'Eerste request maakt app_settings en feed_tokens') check(old_day == ('Oude dag',), 'Bestaande dagen blijven staan na de migratie') check(me['feed'] is False, 'De feed staat standaard uit') check(admin.request('GET', '/days/2020-01-01')['stories'][0]['paragraphs'] == ['Bewaard.'], 'Oude dag leest nog') key = admin.request('POST', '/keys', {'name': 'Annie'}, expected=201)['key']['token'] annie = Client(key) ua = {'User-Agent': 'Annie-Verhalenboek/1.0'} for d in past + [future]: annie.request('POST', '/days', {'date': d, 'title': 'Titel ' + d, 'stories': [ {'title': 'Eerste ' + d, 'paragraphs': ['Regel een & twee.\nNieuwe regel.', 'Tweede alinea.']}, {'title': 'Tweede ' + d, 'paragraphs': ['Midden.']}, {'title': 'Derde ' + d, 'paragraphs': ['Slot.']}, ]}, expected=201, headers=ua) check('missing' in annie.request('GET', '/days/gaps', headers=ua), 'Annie: /days/gaps werkt met eigen User-Agent') check(annie.request('GET', '/days/' + past[0], headers=ua)['complete'] is True, 'Annie: GET /days/DATUM werkt') annie.request('POST', '/wiki/categories', {'slug': 'geheim', 'title': 'Wiki'}, expected=201) annie.request('POST', '/wiki/pages', {'slug': 'wiki-marker', 'title': 'WIKIMARKER', 'category': 'geheim', 'content': 'WIKIMARKER inhoud'}, expected=201) # Uit: alles 404 en niets zichtbaar. reader = Client() reader.me() switched = reader.request('POST', '/session/switch', {'user': 'test_user_2'}) reader.csrf = switched['csrf'] reader.request('GET', '/me/feed', expected=404) reader.request('POST', '/me/feed', expected=404) reader.request('PUT', '/settings/feed', {'enabled': True}, expected=403) annie.request('PUT', '/settings/feed', {'enabled': True}, expected=403) annie.request('GET', '/settings/feed', expected=403) feed(base + '/feed.php?token=vbf_' + 'ab' * 32, expected=404) check('application/atom+xml' not in urllib.request.urlopen(base + '/index.php').read().decode(), 'Geen autodiscovery als de feed uit staat') # Aan. state = admin.request('PUT', '/settings/feed', {'enabled': True}) check(state == {'enabled': True, 'tokens': 0}, 'Beheerder zet de feed aan') admin.request('PUT', '/settings/feed', {'enabled': 'ja'}, expected=422) check(reader.me()['feed'] is True, '/me meldt dat de feed aan staat') check(reader.request('GET', '/me/feed')['token'] is None, 'Nog geen feed-sleutel') created = reader.request('POST', '/me/feed', expected=201)['token'] url = created['url'] check(url.startswith(base + '/feed.php?token=vbf_') and created['prefix'] == url.split('token=')[1][:12], 'Feed-adres gebruikt de basis-URL') check(reader.request('GET', '/me/feed')['token']['url'] == url, 'Feed-adres kan opnieuw worden getoond') with sqlite3.connect(dbfile) as db: reads_before = db.execute('SELECT COUNT(*) FROM reads').fetchone()[0] sessions_before = db.execute('SELECT COUNT(*) FROM sessions').fetchone()[0] stored = db.execute("SELECT token_hash, token_cipher FROM feed_tokens WHERE user_id='local:test_user_2'").fetchone() token = url.split('token=')[1] check(token not in json.dumps(stored), 'De sleutel staat niet leesbaar in de database') body, headers = feed(url) check(headers.get('content-type', '').startswith('application/atom+xml'), 'Content-Type is Atom') check('private' in headers.get('cache-control', '') and 'public' not in headers.get('cache-control', ''), 'Cache-Control: private') check('noindex' in headers.get('x-robots-tag', ''), 'X-Robots-Tag: noindex') check('set-cookie' not in headers, 'De feed zet geen sessiecookie') tree = ET.fromstring(body) entries = tree.findall(ATOM + 'entry') dates = [e.find(ATOM + 'id').text.rsplit('/', 1)[1] for e in entries] check(dates == list(reversed(past)) + ['2020-01-01'], f'Eén item per dag, nieuwste eerst, geen toekomst: {dates}') check(len(set(dates)) == len(dates), 'Geen dubbele dagen') first = entries[0] content = first.find(ATOM + 'content') check(content.get('type') == 'html', 'Inhoud is HTML') html = content.text check('

Eerste <b>' + past[2] + '

' in html and '

Derde ' + past[2] + '

' in html, 'Alle drie de verhalen, titels geëscaped') check('

Regel een & twee.
\nNieuwe regel.

' in html, 'Alinea’s met regeleinden') check(first.find(ATOM + 'link').get('href') == base + '/#/dag/' + past[2], 'Link naar de dag') check(tree.find(ATOM + 'link[@rel="self"]').get('href') == url, 'Self-link is het feed-adres') check('WIKIMARKER' not in body, 'Geen wiki-inhoud in de feed') check(feed(url, method='HEAD')[0] == '', 'HEAD werkt') etag = headers.get('etag') try: urllib.request.urlopen(urllib.request.Request(url, headers={'If-None-Match': etag})) check(False, '304 verwacht') except urllib.error.HTTPError as error: check(error.code == 304, 'ETag geeft 304') with sqlite3.connect(dbfile) as db: check(db.execute('SELECT COUNT(*) FROM reads').fetchone()[0] == reads_before, 'De feed zet niets op gelezen') check(db.execute('SELECT COUNT(*) FROM sessions').fetchone()[0] == sessions_before, 'De feed maakt geen sessies') check(reader.request('GET', '/days/' + past[2])['read'] is False, 'De dag blijft ongelezen') page = reader.opener.open(base + '/index.php').read().decode() check('type="application/atom+xml"' in page and token in page, 'Autodiscovery-link in de HTML-head voor de eigenaar') # Ongeldig, vernieuwd en ingetrokken. feed(base + '/feed.php', expected=404) feed(base + '/feed.php?token=nee', expected=404) feed(base + '/feed.php?token=' + key, expected=404) renewed = reader.request('POST', '/me/feed', expected=201)['token']['url'] check(renewed != url, 'Vernieuwen geeft een nieuw adres') feed(url, expected=404) feed(renewed) check(admin.request('GET', '/settings/feed')['tokens'] == 1, 'Beheer telt de feed-adressen') # Uit zetten: ook een geldige sleutel geeft 404, en de knop verdwijnt. admin.request('PUT', '/settings/feed', {'enabled': False}) feed(renewed, expected=404) check(reader.me()['feed'] is False, '/me meldt uit') reader.request('GET', '/me/feed', expected=404) check('application/atom+xml' not in reader.opener.open(base + '/index.php').read().decode(), 'Geen autodiscovery als de feed uit staat') admin.request('PUT', '/settings/feed', {'enabled': True}) feed(renewed) reader.request('DELETE', '/me/feed') feed(renewed, expected=404) check(reader.request('GET', '/me/feed')['token'] is None, 'Ingetrokken: geen sleutel meer') feed(base + '/feed.php?token=' + token, method='POST', expected=405) print(f'Feed: {checks} assertions passed.') except Exception: log.flush() print((root / 'server.log').read_text()[-4000:]) raise finally: os.killpg(process.pid, signal.SIGTERM) process.wait(timeout=5) log.close()