def test_subrequests_body_have_utf8_charset(self):
request = {"path": "/", "body": {"json": "😂"}}
self.post({"requests": [request]})
(subrequest, ) = self.request.invoke_subrequest.call_args[0]
self.assertIn("charset=utf-8", subrequest.headers["Content-Type"])
wanted = {"json": "😂"}
self.assertEqual(subrequest.body.decode("utf8"), json.dumps(wanted))
def test_subrequests_body_have_utf8_charset(self):
request = {'path': '/', 'body': {'json': "😂"}}
self.post({'requests': [request]})
subrequest, = self.request.invoke_subrequest.call_args[0]
self.assertIn('charset=utf-8', subrequest.headers['Content-Type'])
wanted = {"json": "😂"}
self.assertEqual(subrequest.body.decode('utf8'), json.dumps(wanted))
def _build_pagination_token(self, sorting, last_record, offset):
"""Build a pagination token.
It is a base64 JSON object with the sorting fields values of
the last_record.
"""
nonce = 'pagination-token-{}'.format(uuid4())
if self.request.method.lower() == 'delete':
registry = self.request.registry
validity = registry.settings['pagination_token_validity_seconds']
registry.cache.set(nonce, '', validity)
token = {
'last_record': {},
'offset': offset,
'nonce': nonce,
}
for field, _ in sorting:
last_value = find_nested_value(last_record, field, MISSING)
if last_value is not MISSING:
token['last_record'][field] = last_value
return encode64(json.dumps(token))
def test_subrequests_body_have_utf8_charset(self):
request = {"path": "/", "body": {"json": u"😂"}}
self.post({"requests": [request]})
subrequest, = self.request.invoke_subrequest.call_args[0]
self.assertIn("charset=utf-8", subrequest.headers["Content-Type"])
wanted = {"json": u"😂"}
self.assertEqual(subrequest.body.decode("utf8"), json.dumps(wanted))
def create(self, collection_id, parent_id, record, id_generator=None,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
auth=None, ignore_conflict=False):
id_generator = id_generator or self.id_generator
record = {**record}
if id_field in record:
# Raise unicity error if record with same id already exists.
try:
existing = self.get(collection_id, parent_id, record[id_field])
if ignore_conflict:
return existing
raise exceptions.UnicityError(id_field, existing)
except exceptions.RecordNotFoundError:
pass
else:
record[id_field] = id_generator()
# Remove redundancy in data field
query_record = {**record}
query_record.pop(id_field, None)
query_record.pop(modified_field, None)
query = """
WITH delete_potential_tombstone AS (
DELETE FROM deleted
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
)
INSERT INTO records (id, parent_id, collection_id, data, last_modified)
VALUES (:object_id, :parent_id,
:collection_id, (:data)::JSONB,
from_epoch(:last_modified))
%(on_conflict)s
RETURNING id, as_epoch(last_modified) AS last_modified;
"""
safe_holders = {"on_conflict": ""}
if ignore_conflict:
# We use DO UPDATE so that the RETURNING clause works
# but we don't update anything and keep the previous
# last_modified value already stored.
safe_holders["on_conflict"] = """
ON CONFLICT (id, parent_id, collection_id) DO UPDATE
SET last_modified = EXCLUDED.last_modified
"""
placeholders = dict(object_id=record[id_field],
parent_id=parent_id,
collection_id=collection_id,
last_modified=record.get(modified_field),
data=json.dumps(query_record))
with self.client.connect() as conn:
result = conn.execute(query % safe_holders, placeholders)
inserted = result.fetchone()
record[modified_field] = inserted['last_modified']
return record
def test_subrequests_body_are_json_serialized(self):
request = {'path': '/', 'body': {'json': 'payload'}}
self.post({'requests': [request]})
wanted = {"json": "payload"}
subrequest, = self.request.invoke_subrequest.call_args[0]
self.assertEqual(subrequest.body.decode('utf8'),
json.dumps(wanted))
def _build_pagination_token(self, sorting, last_record, offset):
"""Build a pagination token.
It is a base64 JSON object with the sorting fields values of
the last_record.
"""
nonce = "pagination-token-{}".format(uuid4())
if self.request.method.lower() == "delete":
registry = self.request.registry
validity = registry.settings["pagination_token_validity_seconds"]
registry.cache.set(nonce, "", validity)
token = {
'last_record': {},
'offset': offset,
'nonce': nonce,
}
for field, _ in sorting:
last_value = find_nested_value(last_record, field)
if last_value is not None:
token['last_record'][field] = last_value
return encode64(json.dumps(token))
def update(self,
collection_id,
parent_id,
object_id,
record,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
auth=None):
# Remove redundancy in data field
query_record = {**record}
query_record.pop(id_field, None)
query_record.pop(modified_field, None)
query_create = """
WITH delete_potential_tombstone AS (
DELETE FROM deleted
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
)
INSERT INTO records (id, parent_id, collection_id, data, last_modified)
VALUES (:object_id, :parent_id,
:collection_id, (:data)::JSONB,
from_epoch(:last_modified))
RETURNING as_epoch(last_modified) AS last_modified;
"""
query_update = """
UPDATE records SET data=(:data)::JSONB,
last_modified=from_epoch(:last_modified)
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
RETURNING as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(object_id=object_id,
parent_id=parent_id,
collection_id=collection_id,
last_modified=record.get(modified_field),
data=json.dumps(query_record))
record = {**record, id_field: object_id}
with self.client.connect() as conn:
# Create or update ?
query = """
SELECT id FROM records
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id;
"""
result = conn.execute(query, placeholders)
query = query_update if result.rowcount > 0 else query_create
result = conn.execute(query, placeholders)
updated = result.fetchone()
record[modified_field] = updated['last_modified']
return record
def create(
self,
resource_name,
parent_id,
obj,
id_generator=None,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
):
id_generator = id_generator or self.id_generator
# This is very inefficient, but memory storage is not used in production.
# The serialization provides the necessary consistency with other
# backends implementation, and the deserialization creates a deep
# copy of the passed object.
obj = json.loads(json.dumps(obj))
if id_field in obj:
# Raise unicity error if object with same id already exists.
try:
existing = self.get(resource_name, parent_id, obj[id_field])
raise exceptions.UnicityError(id_field, existing)
except exceptions.ObjectNotFoundError:
pass
else:
obj[id_field] = id_generator()
self.set_object_timestamp(resource_name,
parent_id,
obj,
modified_field=modified_field)
_id = obj[id_field]
self._store[parent_id][resource_name][_id] = obj
self._cemetery[parent_id][resource_name].pop(_id, None)
return obj
def test_subrequests_body_are_json_serialized(self):
request = {'path': '/', 'body': {'json': 'payload'}}
self.post({'requests': [request]})
wanted = {"json": "payload"}
subrequest, = self.request.invoke_subrequest.call_args[0]
self.assertEqual(subrequest.body.decode('utf8'),
json.dumps(wanted))
def test_raises_bad_request_if_token_has_bad_data_structure(self):
invalid_token = json.dumps([[('last_modified', 0, '>')]])
self.validated['querystring'] = {
'_since': 123,
'_limit': 20,
'_token': b64encode(invalid_token.encode('ascii')).decode('ascii')
}
self.assertRaises(HTTPBadRequest, self.resource.collection_get)
def test_raises_bad_request_if_token_has_bad_data_structure(self):
invalid_token = json.dumps([[("last_modified", 0, ">")]])
self.validated["querystring"] = {
"_since": 123,
"_limit": 20,
"_token": b64encode(invalid_token.encode("ascii")).decode("ascii"),
}
self.assertRaises(HTTPBadRequest, self.resource.collection_get)
def test_raises_bad_request_if_token_has_bad_data_structure(self):
invalid_token = json.dumps([[("last_modified", 0, ">")]])
self.validated["querystring"] = {
"_since": 123,
"_limit": 20,
"_token": b64encode(invalid_token.encode("ascii")).decode("ascii"),
}
self.assertRaises(HTTPBadRequest, self.resource.plural_get)
def test_subrequests_body_have_utf8_charset(self):
request = {'path': '/', 'body': {'json': u"😂"}}
self.post({'requests': [request]})
subrequest, = self.request.invoke_subrequest.call_args[0]
self.assertIn('charset=utf-8', subrequest.headers['Content-Type'])
wanted = {"json": u"😂"}
self.assertEqual(subrequest.body.decode('utf8'),
json.dumps(wanted))
def _format_conditions(self, filters, id_field, modified_field, prefix="filters"):
"""Format the filters list in SQL, with placeholders for safe escaping.
.. note::
All conditions are combined using AND.
.. note::
Field name and value are escaped as they come from HTTP API.
:returns: A SQL string with placeholders, and a dict mapping
placeholders to actual values.
:rtype: tuple
"""
operators = {COMPARISON.EQ: "=", COMPARISON.NOT: "<>", COMPARISON.IN: "IN", COMPARISON.EXCLUDE: "NOT IN"}
conditions = []
holders = {}
for i, filtr in enumerate(filters):
value = filtr.value
if filtr.field == id_field:
sql_field = "id"
elif filtr.field == modified_field:
sql_field = "as_epoch(last_modified)"
else:
# Safely escape field name
field_holder = "%s_field_%s" % (prefix, i)
holders[field_holder] = filtr.field
# JSON operator ->> retrieves values as text.
# If field is missing, we default to ''.
sql_field = "coalesce(data->>:%s, '')" % field_holder
if isinstance(value, (int, float)) and value not in (True, False):
sql_field = "(data->>:%s)::numeric" % field_holder
if filtr.operator not in (COMPARISON.IN, COMPARISON.EXCLUDE):
# For the IN operator, let psycopg escape the values list.
# Otherwise JSON-ify the native value (e.g. True -> 'true')
if not isinstance(filtr.value, six.string_types):
value = json.dumps(filtr.value).strip('"')
else:
value = tuple(value)
# WHERE field IN (); -- Fails with syntax error.
if len(value) == 0:
value = (None,)
# Safely escape value
value_holder = "%s_value_%s" % (prefix, i)
holders[value_holder] = value
sql_operator = operators.setdefault(filtr.operator, filtr.operator.value)
cond = "%s %s :%s" % (sql_field, sql_operator, value_holder)
conditions.append(cond)
safe_sql = " AND ".join(conditions)
return safe_sql, holders
def test_every_available_migration(self):
"""Test every migration available in kinto.core code base since
version 1.6.
Records migration test is currently very naive, and should be
elaborated along future migrations.
"""
self._delete_everything()
# Install old schema
with self.storage.client.connect() as conn:
here = os.path.abspath(os.path.dirname(__file__))
filepath = 'schema/postgresql-storage-1.6.sql'
with open(os.path.join(here, filepath)) as f:
old_schema = f.read()
conn.execute(old_schema)
# Create a sample record using some code that is compatible with the
# schema in place in cliquet 1.6.
with self.storage.client.connect() as conn:
before = {'drink': 'cacao'}
query = """
INSERT INTO records (user_id, resource_name, data)
VALUES (:user_id, :resource_name, (:data)::JSON)
RETURNING id, as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(user_id='jean-louis',
resource_name='test',
data=json.dumps(before))
result = conn.execute(query, placeholders)
inserted = result.fetchone()
before['id'] = str(inserted['id'])
before['last_modified'] = inserted['last_modified']
# In cliquet 1.6, version = 1.
version = self.storage._get_installed_version()
self.assertEqual(version, 1)
# Run every migrations available.
self.storage.initialize_schema()
# Version matches current one.
version = self.storage._get_installed_version()
self.assertEqual(version, self.version)
# Check that previously created record is still here
migrated, count = self.storage.get_all('test', 'jean-louis')
self.assertEqual(migrated[0], before)
# Check that new records can be created
r = self.storage.create('test', ',jean-louis', {'drink': 'mate'})
# And deleted
self.storage.delete('test', ',jean-louis', r['id'])
def send_alert(request, message=None, url=None, code="soft-eol"):
"""Helper to add an Alert header to the response.
:param code: The type of error 'soft-eol', 'hard-eol'
:param message: The description message.
:param url: The URL for more information, default to the documentation url.
"""
if url is None:
url = request.registry.settings["project_docs"]
request.response.headers["Alert"] = json.dumps({"code": code, "message": message, "url": url})
def update(self, collection_id, parent_id, object_id, record,
unique_fields=None, id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
auth=None):
query_create = """
WITH delete_potential_tombstone AS (
DELETE FROM deleted
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
)
INSERT INTO records (id, parent_id, collection_id, data, last_modified)
VALUES (:object_id, :parent_id,
:collection_id, (:data)::JSONB,
from_epoch(:last_modified))
RETURNING as_epoch(last_modified) AS last_modified;
"""
query_update = """
UPDATE records SET data=(:data)::JSONB,
last_modified=from_epoch(:last_modified)
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
RETURNING as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(object_id=object_id,
parent_id=parent_id,
collection_id=collection_id,
last_modified=record.get(modified_field),
data=json.dumps(record))
record = record.copy()
record[id_field] = object_id
with self.client.connect() as conn:
# Check that it does violate the resource unicity rules.
self._check_unicity(conn, collection_id, parent_id, record,
unique_fields, id_field, modified_field)
# Create or update ?
query = """
SELECT id FROM records
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id;
"""
result = conn.execute(query, placeholders)
query = query_update if result.rowcount > 0 else query_create
result = conn.execute(query, placeholders)
updated = result.fetchone()
record[modified_field] = updated['last_modified']
return record
def send_alert(request, message=None, url=None, code="soft-eol"):
"""Helper to add an Alert header to the response.
:param code: The type of error 'soft-eol', 'hard-eol'
:param message: The description message.
:param url: The URL for more information, default to the documentation url.
"""
if url is None:
url = request.registry.settings["project_docs"]
request.response.headers["Alert"] = json.dumps({"code": code, "message": message, "url": url})
def delete(
self,
resource_name,
parent_id,
object_id,
id_field=DEFAULT_ID_FIELD,
with_deleted=True,
modified_field=DEFAULT_MODIFIED_FIELD,
deleted_field=DEFAULT_DELETED_FIELD,
last_modified=None,
):
if with_deleted:
query = """
UPDATE objects
SET deleted=TRUE,
data=(:deleted_data)::JSONB,
last_modified=from_epoch(:last_modified)
WHERE id = :object_id
AND parent_id = :parent_id
AND resource_name = :resource_name
AND NOT deleted
RETURNING as_epoch(last_modified) AS last_modified;
"""
else:
query = """
DELETE FROM objects
WHERE id = :object_id
AND parent_id = :parent_id
AND resource_name = :resource_name
AND NOT deleted
RETURNING as_epoch(last_modified) AS last_modified;
"""
deleted_data = json.dumps(dict([(deleted_field, True)]))
placeholders = dict(
object_id=object_id,
parent_id=parent_id,
resource_name=resource_name,
last_modified=last_modified,
deleted_data=deleted_data,
)
with self.client.connect() as conn:
result = conn.execute(query, placeholders)
if result.rowcount == 0:
raise exceptions.ObjectNotFoundError(object_id)
updated = result.fetchone()
obj = {}
obj[modified_field] = updated["last_modified"]
obj[id_field] = object_id
obj[deleted_field] = True
return obj
def test_every_available_migration(self):
"""Test every migration available in kinto.core code base since
version 1.6.
Records migration test is currently very naive, and should be
elaborated along future migrations.
"""
self._delete_everything()
# Install old schema
with self.storage.client.connect() as conn:
here = os.path.abspath(os.path.dirname(__file__))
filepath = 'schema/postgresql-storage-1.6.sql'
old_schema = open(os.path.join(here, filepath)).read()
conn.execute(old_schema)
# Create a sample record using some code that is compatible with the
# schema in place in cliquet 1.6.
with self.storage.client.connect() as conn:
before = {'drink': 'cacao'}
query = """
INSERT INTO records (user_id, resource_name, data)
VALUES (:user_id, :resource_name, (:data)::JSON)
RETURNING id, as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(user_id='jean-louis',
resource_name='test',
data=json.dumps(before))
result = conn.execute(query, placeholders)
inserted = result.fetchone()
before['id'] = six.text_type(inserted['id'])
before['last_modified'] = inserted['last_modified']
# In cliquet 1.6, version = 1.
version = self.storage._get_installed_version()
self.assertEqual(version, 1)
# Run every migrations available.
self.storage.initialize_schema()
# Version matches current one.
version = self.storage._get_installed_version()
self.assertEqual(version, self.version)
# Check that previously created record is still here
migrated, count = self.storage.get_all('test', 'jean-louis')
self.assertEqual(migrated[0], before)
# Check that new records can be created
r = self.storage.create('test', ',jean-louis', {'drink': 'mate'})
# And deleted
self.storage.delete('test', ',jean-louis', r['id'])
def _build_pagination_token(self, sorting, last_record, offset):
"""Build a pagination token.
It is a base64 JSON object with the sorting fields values of
the last_record.
"""
token = {'last_record': {}, 'offset': offset}
for field, _ in sorting:
token['last_record'][field] = last_record[field]
return encode64(json.dumps(token))
def _build_pagination_token(self, sorting, last_record, offset):
"""Build a pagination token.
It is a base64 JSON object with the sorting fields values of
the last_record.
"""
token = {"last_record": {}, "offset": offset}
for field, _ in sorting:
token["last_record"][field] = last_record[field]
return encode64(json.dumps(token))
def validate_request_call(self, op, **kargs):
params = unmarshal_request(self.request, op)
response = self.app.request(op.path_name.format_map(params),
body=json.dumps(
self.request.json()).encode(),
method=op.http_method.upper(),
headers=self.headers,
**kargs)
schema = self.spec.deref(op.op_spec["responses"][str(
response.status_code)])
casted_resp = self.cast_bravado_response(response)
validate_response(schema, op, casted_resp)
return response
def set(self, key, value, ttl=None):
if ttl is None:
logger.warning("No TTL for cache key %r" % key)
query = """
INSERT INTO cache (key, value, ttl)
VALUES (:key, :value, sec2ttl(:ttl))
ON CONFLICT (key) DO UPDATE
SET value = :value,
ttl = sec2ttl(:ttl);
"""
value = json.dumps(value)
with self.client.connect() as conn:
conn.execute(query,
dict(key=self.prefix + key, value=value, ttl=ttl))
def set(self, key, value, ttl=None):
query = """
WITH upsert AS (
UPDATE cache SET value = :value, ttl = sec2ttl(:ttl)
WHERE key=:key
RETURNING *)
INSERT INTO cache (key, value, ttl)
SELECT :key, :value, sec2ttl(:ttl)
WHERE NOT EXISTS (SELECT * FROM upsert)
"""
value = json.dumps(value)
with self.client.connect() as conn:
conn.execute(query,
dict(key=self.prefix + key, value=value, ttl=ttl))
def set(self, key, value, ttl=None):
query = """
WITH upsert AS (
UPDATE cache SET value = :value, ttl = sec2ttl(:ttl)
WHERE key=:key
RETURNING *)
INSERT INTO cache (key, value, ttl)
SELECT :key, :value, sec2ttl(:ttl)
WHERE NOT EXISTS (SELECT * FROM upsert)
"""
value = json.dumps(value)
with self.client.connect() as conn:
conn.execute(query, dict(key=self.prefix + key,
value=value, ttl=ttl))
def create(self,
collection_id,
parent_id,
record,
id_generator=None,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
auth=None):
id_generator = id_generator or self.id_generator
record = record.copy()
if id_field in record:
# Raise unicity error if record with same id already exists.
try:
existing = self.get(collection_id, parent_id, record[id_field])
raise exceptions.UnicityError(id_field, existing)
except exceptions.RecordNotFoundError:
pass
else:
record[id_field] = id_generator()
# Remove redundancy in data field
query_record = record.copy()
query_record.pop(id_field, None)
query_record.pop(modified_field, None)
query = """
WITH delete_potential_tombstone AS (
DELETE FROM deleted
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
)
INSERT INTO records (id, parent_id, collection_id, data, last_modified)
VALUES (:object_id, :parent_id,
:collection_id, (:data)::JSONB,
from_epoch(:last_modified))
RETURNING id, as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(object_id=record[id_field],
parent_id=parent_id,
collection_id=collection_id,
last_modified=record.get(modified_field),
data=json.dumps(query_record))
with self.client.connect() as conn:
result = conn.execute(query, placeholders)
inserted = result.fetchone()
record[modified_field] = inserted['last_modified']
return record
def set(self, key, value, ttl):
if isinstance(value, bytes):
raise TypeError("a string-like object is required, not 'bytes'")
query = """
INSERT INTO cache (key, value, ttl)
VALUES (:key, :value, sec2ttl(:ttl))
ON CONFLICT (key) DO UPDATE
SET value = :value,
ttl = sec2ttl(:ttl);
"""
value = json.dumps(value)
with self.client.connect() as conn:
conn.execute(query, dict(key=self.prefix + key,
value=value, ttl=ttl))
def send_alert(request, message=None, url=None, code='soft-eol'):
"""Helper to add an Alert header to the response.
:param code: The type of error 'soft-eol', 'hard-eol'
:param message: The description message.
:param url: The URL for more information, default to the documentation url.
"""
if url is None:
url = request.registry.settings['project_docs']
request.response.headers['Alert'] = json.dumps({
'code': code,
'message': message,
'url': url
})
def set(self, key, value, ttl):
if isinstance(value, bytes):
raise TypeError("a string-like object is required, not 'bytes'")
query = """
INSERT INTO cache (key, value, ttl)
VALUES (:key, :value, sec2ttl(:ttl))
ON CONFLICT (key) DO UPDATE
SET value = :value,
ttl = sec2ttl(:ttl);
"""
value = json.dumps(value)
with self.client.connect() as conn:
conn.execute(query,
dict(key=self.prefix + key, value=value, ttl=ttl))
def send_alert(request, message=None, url=None, code='soft-eol'):
"""Helper to add an Alert header to the response.
:param code: The type of error 'soft-eol', 'hard-eol'
:param message: The description message.
:param url: The URL for more information, default to the documentation url.
"""
if url is None:
url = request.registry.settings['project_docs']
request.response.headers['Alert'] = json.dumps({
'code': code,
'message': message,
'url': url
})
def update(self, collection_id, parent_id, object_id, record,
unique_fields=None, id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
auth=None):
query_create = """
INSERT INTO records (id, parent_id, collection_id, data, last_modified)
VALUES (:object_id, :parent_id,
:collection_id, (:data)::JSONB,
from_epoch(:last_modified))
RETURNING as_epoch(last_modified) AS last_modified;
"""
query_update = """
UPDATE records SET data=(:data)::JSONB,
last_modified=from_epoch(:last_modified)
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
RETURNING as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(object_id=object_id,
parent_id=parent_id,
collection_id=collection_id,
last_modified=record.get(modified_field),
data=json.dumps(record))
record = record.copy()
record[id_field] = object_id
with self.client.connect() as conn:
# Check that it does violate the resource unicity rules.
self._check_unicity(conn, collection_id, parent_id, record,
unique_fields, id_field, modified_field)
# Create or update ?
query = """
SELECT id FROM records
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id;
"""
result = conn.execute(query, placeholders)
query = query_update if result.rowcount > 0 else query_create
result = conn.execute(query, placeholders)
updated = result.fetchone()
record[modified_field] = updated['last_modified']
return record
def test_migration_12_clean_tombstones(self):
self._delete_everything()
postgresql_storage.Storage.schema_version = 11
self.storage.initialize_schema()
# Set the schema version back to 11 in the base as well
with self.storage.client.connect() as conn:
query = """
UPDATE metadata SET value = '11'
WHERE name = 'storage_schema_version';
"""
conn.execute(query)
r = self.storage.create('test', 'jean-louis', {'drink': 'mate'})
self.storage.delete('test', 'jean-louis', r['id'])
# Insert back the record without removing the tombstone.
with self.storage.client.connect() as conn:
query = """
INSERT INTO records (id, parent_id, collection_id,
data, last_modified)
VALUES (:id, :parent_id, :collection_id,
(:data)::JSONB, from_epoch(:last_modified));
"""
placeholders = dict(id=r['id'],
collection_id='test',
parent_id='jean-louis',
data=json.dumps({'drink': 'mate'}),
last_modified=1468400666777)
conn.execute(query, placeholders)
records, count = self.storage.get_all('test',
'jean-louis',
include_deleted=True)
# Check that we have the tombstone
assert len(records) == 2
assert count == 1
# Execute the 011 to 012 migration
postgresql_storage.Storage.schema_version = 12
self.storage.initialize_schema()
# Check that the rotted tombstone have been removed.
records, count = self.storage.get_all('test',
'jean-louis',
include_deleted=True)
# Only the record remains.
assert len(records) == 1
assert count == 1
def http_error(httpexception,
errno=None,
code=None,
error=None,
message=None,
info=None,
details=None):
"""Return a JSON formated response matching the error HTTP API.
:param httpexception: Instance of :mod:`~pyramid:pyramid.httpexceptions`
:param errno: stable application-level error number (e.g. 109)
:param code: matches the HTTP status code (e.g 400)
:param error: string description of error type (e.g. "Bad request")
:param message: context information (e.g. "Invalid request parameters")
:param info: information about error (e.g. URL to troubleshooting)
:param details: additional structured details (conflicting record)
:returns: the formatted response object
:rtype: pyramid.httpexceptions.HTTPException
"""
errno = errno or ERRORS.UNDEFINED
if isinstance(errno, Enum):
errno = errno.value
# Track error number for request summary
logger.bind(errno=errno)
body = {
"code": code or httpexception.code,
"errno": errno,
"error": error or httpexception.title
}
if message is not None:
body['message'] = message
if info is not None:
body['info'] = info
if details is not None:
body['details'] = details
response = httpexception
response.body = json.dumps(body).encode("utf-8")
response.content_type = 'application/json'
return response
def test_migration_12_clean_tombstones(self):
self._delete_everything()
postgresql_storage.Storage.schema_version = 11
self.storage.initialize_schema()
# Set the schema version back to 11 in the base as well
with self.storage.client.connect() as conn:
query = """
UPDATE metadata SET value = '11'
WHERE name = 'storage_schema_version';
"""
conn.execute(query)
r = self.storage.create('test', 'jean-louis', {'drink': 'mate'})
self.storage.delete('test', 'jean-louis', r['id'])
# Insert back the record without removing the tombstone.
with self.storage.client.connect() as conn:
query = """
INSERT INTO records (id, parent_id, collection_id,
data, last_modified)
VALUES (:id, :parent_id, :collection_id,
(:data)::JSONB, from_epoch(:last_modified));
"""
placeholders = dict(id=r['id'],
collection_id='test',
parent_id='jean-louis',
data=json.dumps({'drink': 'mate'}),
last_modified=1468400666777)
conn.execute(query, placeholders)
records, count = self.storage.get_all('test', 'jean-louis',
include_deleted=True)
# Check that we have the tombstone
assert len(records) == 2
assert count == 1
# Execute the 011 to 012 migration
postgresql_storage.Storage.schema_version = 12
self.storage.initialize_schema()
# Check that the rotted tombstone have been removed.
records, count = self.storage.get_all('test', 'jean-louis',
include_deleted=True)
# Only the record remains.
assert len(records) == 1
assert count == 1
def create(
self,
collection_id,
parent_id,
record,
id_generator=None,
unique_fields=None,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
auth=None,
):
id_generator = id_generator or self.id_generator
record = record.copy()
record_id = record.setdefault(id_field, id_generator())
query = """
WITH delete_potential_tombstone AS (
DELETE FROM deleted
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
)
INSERT INTO records (id, parent_id, collection_id, data, last_modified)
VALUES (:object_id, :parent_id,
:collection_id, (:data)::JSONB,
from_epoch(:last_modified))
RETURNING id, as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(
object_id=record_id,
parent_id=parent_id,
collection_id=collection_id,
last_modified=record.get(modified_field),
data=json.dumps(record),
)
with self.client.connect() as conn:
# Check that it does violate the resource unicity rules.
self._check_unicity(
conn, collection_id, parent_id, record, unique_fields, id_field, modified_field, for_creation=True
)
result = conn.execute(query, placeholders)
inserted = result.fetchone()
record[modified_field] = inserted["last_modified"]
return record
def update(self,
collection_id,
parent_id,
object_id,
record,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
auth=None):
# Remove redundancy in data field
query_record = {**record}
query_record.pop(id_field, None)
query_record.pop(modified_field, None)
query = """
WITH delete_potential_tombstone AS (
DELETE FROM deleted
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
)
INSERT INTO records (id, parent_id, collection_id, data, last_modified)
VALUES (:object_id, :parent_id,
:collection_id, (:data)::JSONB,
from_epoch(:last_modified))
ON CONFLICT (id, parent_id, collection_id) DO UPDATE
SET data=(:data)::JSONB,
last_modified = GREATEST(from_epoch(:last_modified),
EXCLUDED.last_modified)
RETURNING as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(object_id=object_id,
parent_id=parent_id,
collection_id=collection_id,
last_modified=record.get(modified_field),
data=json.dumps(query_record))
with self.client.connect() as conn:
result = conn.execute(query, placeholders)
updated = result.fetchone()
record = {**record, id_field: object_id}
record[modified_field] = updated['last_modified']
return record
def update(
self,
resource_name,
parent_id,
object_id,
obj,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
):
# Remove redundancy in data field
query_object = {**obj}
query_object.pop(id_field, None)
query_object.pop(modified_field, None)
query = """
INSERT INTO objects (id, parent_id, resource_name, data, last_modified, deleted)
VALUES (:object_id, :parent_id,
:resource_name, (:data)::JSONB,
from_epoch(:last_modified),
FALSE)
ON CONFLICT (id, parent_id, resource_name) DO UPDATE
SET data = (:data)::JSONB,
deleted = FALSE,
last_modified = GREATEST(from_epoch(:last_modified),
EXCLUDED.last_modified)
RETURNING as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(
object_id=object_id,
parent_id=parent_id,
resource_name=resource_name,
last_modified=obj.get(modified_field),
data=json.dumps(query_object),
)
with self.client.connect() as conn:
result = conn.execute(query, placeholders)
updated = result.fetchone()
obj = {**obj, id_field: object_id}
obj[modified_field] = updated["last_modified"]
return obj
def http_error(httpexception, errno=None,
code=None, error=None, message=None, info=None, details=None):
"""Return a JSON formated response matching the error protocol.
:param httpexception: Instance of :mod:`~pyramid:pyramid.httpexceptions`
:param errno: stable application-level error number (e.g. 109)
:param code: matches the HTTP status code (e.g 400)
:param error: string description of error type (e.g. "Bad request")
:param message: context information (e.g. "Invalid request parameters")
:param info: information about error (e.g. URL to troubleshooting)
:param details: additional structured details (conflicting record)
:returns: the formatted response object
:rtype: pyramid.httpexceptions.HTTPException
"""
errno = errno or ERRORS.UNDEFINED
if isinstance(errno, Enum):
errno = errno.value
# Track error number for request summary
logger.bind(errno=errno)
body = {
"code": code or httpexception.code,
"errno": errno,
"error": error or httpexception.title
}
if message is not None:
body['message'] = message
if info is not None:
body['info'] = info
if details is not None:
body['details'] = details
response = httpexception
response.body = json.dumps(body).encode("utf-8")
response.content_type = 'application/json'
return response
def update(
self,
resource_name,
parent_id,
object_id,
obj,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
):
# This is very inefficient, but memory storage is not used in production.
# The serialization provides the necessary consistency with other
# backends implementation, and the deserialization creates a deep
# copy of the passed object.
obj = json.loads(json.dumps(obj))
obj[id_field] = object_id
self.set_object_timestamp(resource_name, parent_id, obj, modified_field=modified_field)
self._store[parent_id][resource_name][object_id] = obj
self._cemetery[parent_id][resource_name].pop(object_id, None)
return obj
def _build_pagination_token(self, sorting, last_object, offset):
"""Build a pagination token.
It is a base64 JSON object with the sorting fields values of
the last_object.
"""
nonce = f"pagination-token-{uuid4()}"
if self.request.method.lower() == "delete":
registry = self.request.registry
validity = registry.settings["pagination_token_validity_seconds"]
registry.cache.set(nonce, "", validity)
token = {"last_object": {}, "offset": offset, "nonce": nonce}
for field, _ in sorting:
last_value = find_nested_value(last_object, field, MISSING)
if last_value is not MISSING:
token["last_object"][field] = last_value
return encode64(json.dumps(token))
def create(self, collection_id, parent_id, record, id_generator=None,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
auth=None):
id_generator = id_generator or self.id_generator
record = record.copy()
if id_field in record:
# Raise unicity error if record with same id already exists.
try:
existing = self.get(collection_id, parent_id, record[id_field])
raise exceptions.UnicityError(id_field, existing)
except exceptions.RecordNotFoundError:
pass
else:
record[id_field] = id_generator()
query = """
WITH delete_potential_tombstone AS (
DELETE FROM deleted
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
)
INSERT INTO records (id, parent_id, collection_id, data, last_modified)
VALUES (:object_id, :parent_id,
:collection_id, (:data)::JSONB,
from_epoch(:last_modified))
RETURNING id, as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(object_id=record[id_field],
parent_id=parent_id,
collection_id=collection_id,
last_modified=record.get(modified_field),
data=json.dumps(record))
with self.client.connect() as conn:
result = conn.execute(query, placeholders)
inserted = result.fetchone()
record[modified_field] = inserted['last_modified']
return record
def create(self, collection_id, parent_id, record, id_generator=None,
unique_fields=None, id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
auth=None):
id_generator = id_generator or self.id_generator
record = record.copy()
record_id = record.setdefault(id_field, id_generator())
query = """
WITH delete_potential_tombstone AS (
DELETE FROM deleted
WHERE id = :object_id
AND parent_id = :parent_id
AND collection_id = :collection_id
)
INSERT INTO records (id, parent_id, collection_id, data, last_modified)
VALUES (:object_id, :parent_id,
:collection_id, (:data)::JSONB,
from_epoch(:last_modified))
RETURNING id, as_epoch(last_modified) AS last_modified;
"""
placeholders = dict(object_id=record_id,
parent_id=parent_id,
collection_id=collection_id,
last_modified=record.get(modified_field),
data=json.dumps(record))
with self.client.connect() as conn:
# Check that it does violate the resource unicity rules.
self._check_unicity(conn, collection_id, parent_id, record,
unique_fields, id_field, modified_field,
for_creation=True)
result = conn.execute(query, placeholders)
inserted = result.fetchone()
record[modified_field] = inserted['last_modified']
return record
def set(self, key, value, ttl):
if isinstance(value, bytes):
raise TypeError("a string-like object is required, not 'bytes'")
value = json.dumps({"value": value, "ttl": ceil(time() + ttl)})
self._client.set(self.prefix + key, value, int(ttl))
def get_all(self, collection_id, parent_id, filters=None, sorting=None,
pagination_rules=None, limit=None, include_deleted=False,
id_field=DEFAULT_ID_FIELD,
modified_field=DEFAULT_MODIFIED_FIELD,
deleted_field=DEFAULT_DELETED_FIELD,
auth=None):
query = """
WITH total_filtered AS (
SELECT COUNT(id) AS count
FROM records
WHERE parent_id = :parent_id
AND collection_id = :collection_id
%(conditions_filter)s
),
collection_filtered AS (
SELECT id, last_modified, data
FROM records
WHERE parent_id = :parent_id
AND collection_id = :collection_id
%(conditions_filter)s
LIMIT %(max_fetch_size)s
),
fake_deleted AS (
SELECT (:deleted_field)::JSONB AS data
),
filtered_deleted AS (
SELECT id, last_modified, fake_deleted.data AS data
FROM deleted, fake_deleted
WHERE parent_id = :parent_id
AND collection_id = :collection_id
%(conditions_filter)s
%(deleted_limit)s
),
all_records AS (
SELECT * FROM filtered_deleted
UNION ALL
SELECT * FROM collection_filtered
),
paginated_records AS (
SELECT DISTINCT id
FROM all_records
%(pagination_rules)s
)
SELECT total_filtered.count AS count_total,
a.id, as_epoch(a.last_modified) AS last_modified, a.data
FROM paginated_records AS p JOIN all_records AS a ON (a.id = p.id),
total_filtered
%(sorting)s
%(pagination_limit)s;
"""
deleted_field = json.dumps(dict([(deleted_field, True)]))
# Unsafe strings escaped by PostgreSQL
placeholders = dict(parent_id=parent_id,
collection_id=collection_id,
deleted_field=deleted_field)
# Safe strings
safeholders = defaultdict(six.text_type)
safeholders['max_fetch_size'] = self._max_fetch_size
if filters:
safe_sql, holders = self._format_conditions(filters,
id_field,
modified_field)
safeholders['conditions_filter'] = 'AND %s' % safe_sql
placeholders.update(**holders)
if not include_deleted:
safeholders['deleted_limit'] = 'LIMIT 0'
if sorting:
sql, holders = self._format_sorting(sorting, id_field,
modified_field)
safeholders['sorting'] = sql
placeholders.update(**holders)
if pagination_rules:
sql, holders = self._format_pagination(pagination_rules, id_field,
modified_field)
safeholders['pagination_rules'] = 'WHERE %s' % sql
placeholders.update(**holders)
if limit:
assert isinstance(limit, six.integer_types) # asserted in resource
safeholders['pagination_limit'] = 'LIMIT %s' % limit
with self.client.connect(readonly=True) as conn:
result = conn.execute(query % safeholders, placeholders)
retrieved = result.fetchmany(self._max_fetch_size)
if not len(retrieved):
return [], 0
count_total = retrieved[0]['count_total']
records = []
for result in retrieved:
record = result['data']
record[id_field] = result['id']
record[modified_field] = result['last_modified']
records.append(record)
return records, count_total
def set(self, key, value, ttl):
if isinstance(value, bytes):
raise TypeError("a string-like object is required, not 'bytes'")
value = json.dumps({"value": value, "ttl": ceil(time() + ttl)})
self._client.set(self.prefix + key, value, int(ttl))
def test_subrequests_body_are_json_serialized(self):
request = {"path": "/", "body": {"json": "payload"}}
self.post({"requests": [request]})
wanted = {"json": "payload"}
(subrequest, ) = self.request.invoke_subrequest.call_args[0]
self.assertEqual(subrequest.body.decode("utf8"), json.dumps(wanted))
def test_list_of_homogeneous_values_are_serialized_as_string(self):
list_values = ['life', 'of', 'pi', 3.14]
logged = self.renderer(self.logger, 'info', {'params': list_values})
log = json.loads(logged)
self.assertEqual(log['Fields']['params'], json.dumps(list_values))
def set(self, key, value, ttl=None):
value = json.dumps(value)
if ttl:
self._client.psetex(self.prefix + key, int(ttl * 1000), value)
else:
self._client.set(self.prefix + key, value)
def _format_conditions(self, filters, id_field, modified_field,
prefix='filters'):
"""Format the filters list in SQL, with placeholders for safe escaping.
.. note::
All conditions are combined using AND.
.. note::
Field name and value are escaped as they come from HTTP API.
:returns: A SQL string with placeholders, and a dict mapping
placeholders to actual values.
:rtype: tuple
"""
operators = {
COMPARISON.EQ: '=',
COMPARISON.NOT: '<>',
COMPARISON.IN: 'IN',
COMPARISON.EXCLUDE: 'NOT IN',
COMPARISON.LIKE: 'ILIKE',
}
conditions = []
holders = {}
for i, filtr in enumerate(filters):
value = filtr.value
is_like_query = filtr.operator == COMPARISON.LIKE
if filtr.field == id_field:
sql_field = 'id'
if isinstance(value, int):
value = str(value)
elif filtr.field == modified_field:
sql_field = 'as_epoch(last_modified)'
else:
column_name = "data"
# Subfields: ``person.name`` becomes ``data->person->>name``
subfields = filtr.field.split('.')
for j, subfield in enumerate(subfields):
# Safely escape field name
field_holder = '{}_field_{}_{}'.format(prefix, i, j)
holders[field_holder] = subfield
# Use ->> to convert the last level to text if
# needed for LIKE query. (Other queries do JSONB comparison.)
column_name += "->>" if j == len(subfields) - 1 and is_like_query else "->"
column_name += ":{}".format(field_holder)
# If the field is missing, column_name will produce
# NULL. NULL has strange properties with comparisons
# in SQL -- NULL = anything => NULL, NULL <> anything => NULL.
# We generally want missing fields to be treated as a
# special value that compares as different from
# everything, including JSON null. Do this on a
# per-operator basis.
null_false_operators = (
# NULLs aren't EQ to anything (definitionally).
COMPARISON.EQ,
# So they can't match anything in an INCLUDE.
COMPARISON.IN,
# Nor can they be LIKE anything.
COMPARISON.LIKE,
)
null_true_operators = (
# NULLs are automatically not equal to everything.
COMPARISON.NOT,
# Thus they can never be excluded.
COMPARISON.EXCLUDE,
# Match Postgres's default sort behavior
# (NULLS LAST) by allowing NULLs to
# automatically be greater than everything.
COMPARISON.GT, COMPARISON.MIN,
)
if filtr.operator in null_false_operators:
sql_field = "{} IS NOT NULL AND {}".format(column_name, column_name)
elif filtr.operator in null_true_operators:
sql_field = "{} IS NULL OR {}".format(column_name, column_name)
else:
# No need to check for LT and MAX because NULL < foo
# is NULL, which is falsy in SQL.
sql_field = column_name
string_field = filtr.field in (id_field, modified_field) or is_like_query
if not string_field:
# JSONB-ify the value.
if filtr.operator not in (COMPARISON.IN, COMPARISON.EXCLUDE):
value = json.dumps(value)
else:
value = [json.dumps(v) for v in value]
if filtr.operator in (COMPARISON.IN, COMPARISON.EXCLUDE):
value = tuple(value)
# WHERE field IN (); -- Fails with syntax error.
if len(value) == 0:
value = (None,)
if is_like_query:
# Operand should be a string.
# Add implicit start/end wildchars if none is specified.
if "*" not in value:
value = "*{}*".format(value)
value = value.replace("*", "%")
if filtr.operator == COMPARISON.HAS:
operator = 'IS NOT NULL' if filtr.value else 'IS NULL'
cond = "{} {}".format(sql_field, operator)
else:
# Safely escape value
value_holder = '{}_value_{}'.format(prefix, i)
holders[value_holder] = value
sql_operator = operators.setdefault(filtr.operator,
filtr.operator.value)
cond = "{} {} :{}".format(sql_field, sql_operator, value_holder)
conditions.append(cond)
safe_sql = ' AND '.join(conditions)
return safe_sql, holders
def test_raises_bad_request_if_token_has_bad_data_structure(self):
invalid_token = json.dumps([[('last_modified', 0, '>')]])
self.resource.request.GET = {
'_since': '123', '_limit': '20',
'_token': b64encode(invalid_token.encode('ascii')).decode('ascii')}
self.assertRaises(HTTPBadRequest, self.resource.collection_get)
def test_objects_values_are_serialized_as_string(self):
querystring = {'_sort': 'name'}
logged = self.renderer(self.logger, 'info', {'params': querystring})
log = json.loads(logged)
self.assertEqual(log['Fields']['params'], json.dumps(querystring))
def test_subrequests_body_are_json_serialized(self):
request = {"path": "/", "body": {"json": "payload"}}
self.post({"requests": [request]})
wanted = {"json": "payload"}
subrequest, = self.request.invoke_subrequest.call_args[0]
self.assertEqual(subrequest.body.decode("utf8"), json.dumps(wanted))
def _format_conditions(self, filters, id_field, modified_field,
prefix='filters'):
"""Format the filters list in SQL, with placeholders for safe escaping.
.. note::
All conditions are combined using AND.
.. note::
Field name and value are escaped as they come from HTTP API.
:returns: A SQL string with placeholders, and a dict mapping
placeholders to actual values.
:rtype: tuple
"""
operators = {
COMPARISON.EQ: '=',
COMPARISON.NOT: '<>',
COMPARISON.IN: 'IN',
COMPARISON.EXCLUDE: 'NOT IN',
COMPARISON.LIKE: 'ILIKE',
}
conditions = []
holders = {}
for i, filtr in enumerate(filters):
value = filtr.value
if filtr.field == id_field:
sql_field = 'id'
elif filtr.field == modified_field:
sql_field = 'as_epoch(last_modified)'
else:
sql_field = "data"
# Subfields: ``person.name`` becomes ``data->person->>name``
subfields = filtr.field.split('.')
for j, subfield in enumerate(subfields):
# Safely escape field name
field_holder = '%s_field_%s_%s' % (prefix, i, j)
holders[field_holder] = subfield
# Use ->> to convert the last level to text.
sql_field += "->>" if j == len(subfields) - 1 else "->"
sql_field += ":%s" % field_holder
# If field is missing, we default to ''.
sql_field = "coalesce(%s, '')" % sql_field
# Cast when comparing to number (eg. '4' < '12')
if isinstance(value, (int, float)) and \
value not in (True, False):
sql_field += "::numeric"
if filtr.operator not in (COMPARISON.IN, COMPARISON.EXCLUDE):
# For the IN operator, let psycopg escape the values list.
# Otherwise JSON-ify the native value (e.g. True -> 'true')
if not isinstance(filtr.value, six.string_types):
value = json.dumps(filtr.value).strip('"')
else:
value = tuple(value)
# WHERE field IN (); -- Fails with syntax error.
if len(value) == 0:
value = (None,)
if filtr.operator == COMPARISON.LIKE:
value = '%{0}%'.format(value)
# Safely escape value
value_holder = '%s_value_%s' % (prefix, i)
holders[value_holder] = value
sql_operator = operators.setdefault(filtr.operator,
filtr.operator.value)
cond = "%s %s :%s" % (sql_field, sql_operator, value_holder)
conditions.append(cond)
safe_sql = ' AND '.join(conditions)
return safe_sql, holders
