#13279 new enhancement

Make cast method more robust

Reported by:	Ryan J Ollos	Owned by:
Priority:	normal	Milestone:	next-major-releases
Component:	database backend	Version:
Severity:	normal	Keywords:
Cc:		Branch:
Release Notes:
API Changes:
Internal Changes:

Description

Copied from 13078#comment:7:

Another solution is using CASE ... END and regular expression operator. I think we could make CAST(... AS int) in PostgreSQL robust and same behavior in other databases.
trac=> SELECT (CASE
trac(>         WHEN value IS NULL THEN NULL
trac(>         WHEN value ~ '[-+]?[0-9]+$' THEN CAST(value AS int)
trac(>         ELSE 0 END) AS cast,
trac->        value
trac-> FROM unnest(ARRAY[NULL, '42', '+123', '-789', 'text', '']) AS value
trac-> ;
 cast | value
------+-------
      |
   42 | 42
  123 | +123
 -789 | -789
    0 | text
    0 |
(6 rows)
trac/db/postgres_backend.py
diff --git a/trac/db/postgres_backend.py b/trac/db/postgres_backend.py
index 3b5c9e92c..3775a9aa7 100644
a b class PostgreSQLConnection(ConnectionBase, ConnectionWrapper):

272 272

273 273 def cast(self, column, type):

274 274 # Temporary hack needed for the union of selects in the search module

275 return 'CAST(%s AS %s)' % (column, _type_map.get(type, type))

275 if type in ('int', 'int64'):

276 fmt = "(CASE WHEN %(column)s IS NULL THEN NULL " \

277 "WHEN %(column)s ~ '^[-+]?[0-9]+$' " \

278 "THEN CAST(%(column)s AS %(type)s)" \

279 "ELSE 0 END)"

280 else:

281 fmt = 'CAST(%(column)s AS %(type)s)'

282 return fmt % {'column': column, 'type': _type_map.get(type, type)}

276 283

277 284 def concat(self, *args):

278 285 return '||'.join(args)

Change History (0)

Modify Ticket

Change Properties

Summary:
Description:	Copied from [ticket:13078#comment:7]: > Another solution is using `CASE ... END` and regular expression operator. I think we could make `CAST(... AS int)` in PostgreSQL robust and same behavior in other databases. > > {{{ > trac=> SELECT (CASE > trac(> WHEN value IS NULL THEN NULL > trac(> WHEN value ~ '[-+]?[0-9]+$' THEN CAST(value AS int) > trac(> ELSE 0 END) AS cast, > trac-> value > trac-> FROM unnest(ARRAY[NULL, '42', '+123', '-789', 'text', '']) AS value > trac-> ; > cast \| value > ------+------- > \| > 42 \| 42 > 123 \| +123 > -789 \| -789 > 0 \| text > 0 \| > (6 rows) > > }}} > > {{{#!diff > diff --git a/trac/db/postgres_backend.py b/trac/db/postgres_backend.py > index 3b5c9e92c..3775a9aa7 100644 > --- a/trac/db/postgres_backend.py > +++ b/trac/db/postgres_backend.py > @@ -272,7 +272,14 @@ class PostgreSQLConnection(ConnectionBase, ConnectionWrapper): > > def cast(self, column, type): > # Temporary hack needed for the union of selects in the search module > - return 'CAST(%s AS %s)' % (column, _type_map.get(type, type)) > + if type in ('int', 'int64'): > + fmt = "(CASE WHEN %(column)s IS NULL THEN NULL " \ > + "WHEN %(column)s ~ '^[-+]?[0-9]+$' " \ > + "THEN CAST(%(column)s AS %(type)s)" \ > + "ELSE 0 END)" > + else: > + fmt = 'CAST(%(column)s AS %(type)s)' > + return fmt % {'column': column, 'type': _type_map.get(type, type)} > > def concat(self, *args): > return '\|\|'.join(args) > }}} You may use WikiFormatting here.
Type:		Priority:
Milestone:		Component:
Version:		Severity:
Keywords:		Cc:	Set your email in Preferences
Branch:
Release Notes:
API Changes:
Internal Changes:

Action

leave as new The ticket will remain with no owner.

unassign The ticket will be disowned.

resolve as The resolution will be set. Next status will be 'closed'.

assign The owner will be changed from (none) to anonymous. Next status will be 'assigned'.

Your email or username:

E-mail address and name can be saved in the Preferences .

You may use WikiFormatting here.

Download in other formats:

Context Navigation

#13279 new enhancement

Make cast method more robust

Description

trac/db/postgres_backend.py

Attachments (0)

Change History (0)

Modify Ticket

Add Comment

by anonymous

Download in other formats:

                class PostgreSQLConnection(ConnectionBase, ConnectionWrapper):
     def cast(self, column, type):
         # Temporary hack needed for the union of selects in the search module
+        return 'CAST(%s AS %s)' % (column, _type_map.get(type, type))
+        if type in ('int', 'int64'):
+            fmt = "(CASE WHEN %(column)s IS NULL THEN NULL " \
+                  "WHEN %(column)s ~ '^[-+]?[0-9]+$' " \
+                  "THEN CAST(%(column)s AS %(type)s)" \
+                  "ELSE 0 END)"
+        else:
+            fmt = 'CAST(%(column)s AS %(type)s)'
+        return fmt % {'column': column, 'type': _type_map.get(type, type)}
     def concat(self, *args):
         return '||'.join(args)