1# dialects/postgresql/hstore.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7
8import re
9
10from .array import ARRAY
11from ... import types as sqltypes
12from ... import util
13from ...sql import functions as sqlfunc
14from ...sql import operators
15
16
17__all__ = ("HSTORE", "hstore")
18
19idx_precedence = operators._PRECEDENCE[operators.json_getitem_op]
20
21GETITEM = operators.custom_op(
22 "->",
23 precedence=idx_precedence,
24 natural_self_precedent=True,
25 eager_grouping=True,
26)
27
28HAS_KEY = operators.custom_op(
29 "?",
30 precedence=idx_precedence,
31 natural_self_precedent=True,
32 eager_grouping=True,
33)
34
35HAS_ALL = operators.custom_op(
36 "?&",
37 precedence=idx_precedence,
38 natural_self_precedent=True,
39 eager_grouping=True,
40)
41
42HAS_ANY = operators.custom_op(
43 "?|",
44 precedence=idx_precedence,
45 natural_self_precedent=True,
46 eager_grouping=True,
47)
48
49CONTAINS = operators.custom_op(
50 "@>",
51 precedence=idx_precedence,
52 natural_self_precedent=True,
53 eager_grouping=True,
54)
55
56CONTAINED_BY = operators.custom_op(
57 "<@",
58 precedence=idx_precedence,
59 natural_self_precedent=True,
60 eager_grouping=True,
61)
62
63
64class HSTORE(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine):
65 """Represent the PostgreSQL HSTORE type.
66
67 The :class:`.HSTORE` type stores dictionaries containing strings, e.g.::
68
69 data_table = Table('data_table', metadata,
70 Column('id', Integer, primary_key=True),
71 Column('data', HSTORE)
72 )
73
74 with engine.connect() as conn:
75 conn.execute(
76 data_table.insert(),
77 data = {"key1": "value1", "key2": "value2"}
78 )
79
80 :class:`.HSTORE` provides for a wide range of operations, including:
81
82 * Index operations::
83
84 data_table.c.data['some key'] == 'some value'
85
86 * Containment operations::
87
88 data_table.c.data.has_key('some key')
89
90 data_table.c.data.has_all(['one', 'two', 'three'])
91
92 * Concatenation::
93
94 data_table.c.data + {"k1": "v1"}
95
96 For a full list of special methods see
97 :class:`.HSTORE.comparator_factory`.
98
99 .. container:: topic
100
101 **Detecting Changes in HSTORE columns when using the ORM**
102
103 For usage with the SQLAlchemy ORM, it may be desirable to combine the
104 usage of :class:`.HSTORE` with :class:`.MutableDict` dictionary now
105 part of the :mod:`sqlalchemy.ext.mutable` extension. This extension
106 will allow "in-place" changes to the dictionary, e.g. addition of new
107 keys or replacement/removal of existing keys to/from the current
108 dictionary, to produce events which will be detected by the unit of
109 work::
110
111 from sqlalchemy.ext.mutable import MutableDict
112
113 class MyClass(Base):
114 __tablename__ = 'data_table'
115
116 id = Column(Integer, primary_key=True)
117 data = Column(MutableDict.as_mutable(HSTORE))
118
119 my_object = session.query(MyClass).one()
120
121 # in-place mutation, requires Mutable extension
122 # in order for the ORM to detect
123 my_object.data['some_key'] = 'some value'
124
125 session.commit()
126
127 When the :mod:`sqlalchemy.ext.mutable` extension is not used, the ORM
128 will not be alerted to any changes to the contents of an existing
129 dictionary, unless that dictionary value is re-assigned to the
130 HSTORE-attribute itself, thus generating a change event.
131
132 .. seealso::
133
134 :class:`.hstore` - render the PostgreSQL ``hstore()`` function.
135
136
137 """
138
139 __visit_name__ = "HSTORE"
140 hashable = False
141 text_type = sqltypes.Text()
142
143 def __init__(self, text_type=None):
144 """Construct a new :class:`.HSTORE`.
145
146 :param text_type: the type that should be used for indexed values.
147 Defaults to :class:`_types.Text`.
148
149 .. versionadded:: 1.1.0
150
151 """
152 if text_type is not None:
153 self.text_type = text_type
154
155 class Comparator(
156 sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator
157 ):
158 """Define comparison operations for :class:`.HSTORE`."""
159
160 def has_key(self, other):
161 """Boolean expression. Test for presence of a key. Note that the
162 key may be a SQLA expression.
163 """
164 return self.operate(HAS_KEY, other, result_type=sqltypes.Boolean)
165
166 def has_all(self, other):
167 """Boolean expression. Test for presence of all keys in jsonb"""
168 return self.operate(HAS_ALL, other, result_type=sqltypes.Boolean)
169
170 def has_any(self, other):
171 """Boolean expression. Test for presence of any key in jsonb"""
172 return self.operate(HAS_ANY, other, result_type=sqltypes.Boolean)
173
174 def contains(self, other, **kwargs):
175 """Boolean expression. Test if keys (or array) are a superset
176 of/contained the keys of the argument jsonb expression.
177
178 kwargs may be ignored by this operator but are required for API
179 conformance.
180 """
181 return self.operate(CONTAINS, other, result_type=sqltypes.Boolean)
182
183 def contained_by(self, other):
184 """Boolean expression. Test if keys are a proper subset of the
185 keys of the argument jsonb expression.
186 """
187 return self.operate(
188 CONTAINED_BY, other, result_type=sqltypes.Boolean
189 )
190
191 def _setup_getitem(self, index):
192 return GETITEM, index, self.type.text_type
193
194 def defined(self, key):
195 """Boolean expression. Test for presence of a non-NULL value for
196 the key. Note that the key may be a SQLA expression.
197 """
198 return _HStoreDefinedFunction(self.expr, key)
199
200 def delete(self, key):
201 """HStore expression. Returns the contents of this hstore with the
202 given key deleted. Note that the key may be a SQLA expression.
203 """
204 if isinstance(key, dict):
205 key = _serialize_hstore(key)
206 return _HStoreDeleteFunction(self.expr, key)
207
208 def slice(self, array):
209 """HStore expression. Returns a subset of an hstore defined by
210 array of keys.
211 """
212 return _HStoreSliceFunction(self.expr, array)
213
214 def keys(self):
215 """Text array expression. Returns array of keys."""
216 return _HStoreKeysFunction(self.expr)
217
218 def vals(self):
219 """Text array expression. Returns array of values."""
220 return _HStoreValsFunction(self.expr)
221
222 def array(self):
223 """Text array expression. Returns array of alternating keys and
224 values.
225 """
226 return _HStoreArrayFunction(self.expr)
227
228 def matrix(self):
229 """Text array expression. Returns array of [key, value] pairs."""
230 return _HStoreMatrixFunction(self.expr)
231
232 comparator_factory = Comparator
233
234 def bind_processor(self, dialect):
235 if util.py2k:
236 encoding = dialect.encoding
237
238 def process(value):
239 if isinstance(value, dict):
240 return _serialize_hstore(value).encode(encoding)
241 else:
242 return value
243
244 else:
245
246 def process(value):
247 if isinstance(value, dict):
248 return _serialize_hstore(value)
249 else:
250 return value
251
252 return process
253
254 def result_processor(self, dialect, coltype):
255 if util.py2k:
256 encoding = dialect.encoding
257
258 def process(value):
259 if value is not None:
260 return _parse_hstore(value.decode(encoding))
261 else:
262 return value
263
264 else:
265
266 def process(value):
267 if value is not None:
268 return _parse_hstore(value)
269 else:
270 return value
271
272 return process
273
274
275class hstore(sqlfunc.GenericFunction):
276 """Construct an hstore value within a SQL expression using the
277 PostgreSQL ``hstore()`` function.
278
279 The :class:`.hstore` function accepts one or two arguments as described
280 in the PostgreSQL documentation.
281
282 E.g.::
283
284 from sqlalchemy.dialects.postgresql import array, hstore
285
286 select(hstore('key1', 'value1'))
287
288 select(
289 hstore(
290 array(['key1', 'key2', 'key3']),
291 array(['value1', 'value2', 'value3'])
292 )
293 )
294
295 .. seealso::
296
297 :class:`.HSTORE` - the PostgreSQL ``HSTORE`` datatype.
298
299 """
300
301 type = HSTORE
302 name = "hstore"
303 inherit_cache = True
304
305
306class _HStoreDefinedFunction(sqlfunc.GenericFunction):
307 type = sqltypes.Boolean
308 name = "defined"
309 inherit_cache = True
310
311
312class _HStoreDeleteFunction(sqlfunc.GenericFunction):
313 type = HSTORE
314 name = "delete"
315 inherit_cache = True
316
317
318class _HStoreSliceFunction(sqlfunc.GenericFunction):
319 type = HSTORE
320 name = "slice"
321 inherit_cache = True
322
323
324class _HStoreKeysFunction(sqlfunc.GenericFunction):
325 type = ARRAY(sqltypes.Text)
326 name = "akeys"
327 inherit_cache = True
328
329
330class _HStoreValsFunction(sqlfunc.GenericFunction):
331 type = ARRAY(sqltypes.Text)
332 name = "avals"
333 inherit_cache = True
334
335
336class _HStoreArrayFunction(sqlfunc.GenericFunction):
337 type = ARRAY(sqltypes.Text)
338 name = "hstore_to_array"
339 inherit_cache = True
340
341
342class _HStoreMatrixFunction(sqlfunc.GenericFunction):
343 type = ARRAY(sqltypes.Text)
344 name = "hstore_to_matrix"
345 inherit_cache = True
346
347
348#
349# parsing. note that none of this is used with the psycopg2 backend,
350# which provides its own native extensions.
351#
352
353# My best guess at the parsing rules of hstore literals, since no formal
354# grammar is given. This is mostly reverse engineered from PG's input parser
355# behavior.
356HSTORE_PAIR_RE = re.compile(
357 r"""
358(
359 "(?P<key> (\\ . | [^"])* )" # Quoted key
360)
361[ ]* => [ ]* # Pair operator, optional adjoining whitespace
362(
363 (?P<value_null> NULL ) # NULL value
364 | "(?P<value> (\\ . | [^"])* )" # Quoted value
365)
366""",
367 re.VERBOSE,
368)
369
370HSTORE_DELIMITER_RE = re.compile(
371 r"""
372[ ]* , [ ]*
373""",
374 re.VERBOSE,
375)
376
377
378def _parse_error(hstore_str, pos):
379 """format an unmarshalling error."""
380
381 ctx = 20
382 hslen = len(hstore_str)
383
384 parsed_tail = hstore_str[max(pos - ctx - 1, 0) : min(pos, hslen)]
385 residual = hstore_str[min(pos, hslen) : min(pos + ctx + 1, hslen)]
386
387 if len(parsed_tail) > ctx:
388 parsed_tail = "[...]" + parsed_tail[1:]
389 if len(residual) > ctx:
390 residual = residual[:-1] + "[...]"
391
392 return "After %r, could not parse residual at position %d: %r" % (
393 parsed_tail,
394 pos,
395 residual,
396 )
397
398
399def _parse_hstore(hstore_str):
400 """Parse an hstore from its literal string representation.
401
402 Attempts to approximate PG's hstore input parsing rules as closely as
403 possible. Although currently this is not strictly necessary, since the
404 current implementation of hstore's output syntax is stricter than what it
405 accepts as input, the documentation makes no guarantees that will always
406 be the case.
407
408
409
410 """
411 result = {}
412 pos = 0
413 pair_match = HSTORE_PAIR_RE.match(hstore_str)
414
415 while pair_match is not None:
416 key = pair_match.group("key").replace(r"\"", '"').replace("\\\\", "\\")
417 if pair_match.group("value_null"):
418 value = None
419 else:
420 value = (
421 pair_match.group("value")
422 .replace(r"\"", '"')
423 .replace("\\\\", "\\")
424 )
425 result[key] = value
426
427 pos += pair_match.end()
428
429 delim_match = HSTORE_DELIMITER_RE.match(hstore_str[pos:])
430 if delim_match is not None:
431 pos += delim_match.end()
432
433 pair_match = HSTORE_PAIR_RE.match(hstore_str[pos:])
434
435 if pos != len(hstore_str):
436 raise ValueError(_parse_error(hstore_str, pos))
437
438 return result
439
440
441def _serialize_hstore(val):
442 """Serialize a dictionary into an hstore literal. Keys and values must
443 both be strings (except None for values).
444
445 """
446
447 def esc(s, position):
448 if position == "value" and s is None:
449 return "NULL"
450 elif isinstance(s, util.string_types):
451 return '"%s"' % s.replace("\\", "\\\\").replace('"', r"\"")
452 else:
453 raise ValueError(
454 "%r in %s position is not a string." % (s, position)
455 )
456
457 return ", ".join(
458 "%s=>%s" % (esc(k, "key"), esc(v, "value")) for k, v in val.items()
459 )