#3388 postgresql hub: date_part instead of EXTRACT
Merged 2 months ago by tkopecek. Opened 2 months ago by jcupova.
jcupova/koji issue-3366  into  master

file modified
+25 -26
@@ -128,11 +128,11 @@ 

          ('task.id', 'id'),

          ('task.state', 'state'),

          ('task.create_time', 'create_time'),

-         ('EXTRACT(EPOCH FROM create_time)', 'create_ts'),

+         ("date_part('epoch', create_time)", 'create_ts'),

          ('task.start_time', 'start_time'),

-         ('EXTRACT(EPOCH FROM task.start_time)', 'start_ts'),

+         ("date_part('epoch', task.start_time)", 'start_ts'),

          ('task.completion_time', 'completion_time'),

-         ('EXTRACT(EPOCH FROM completion_time)', 'completion_ts'),

+         ("date_part('epoch', completion_time)", 'completion_ts'),

          ('task.channel_id', 'channel_id'),

          ('task.host_id', 'host_id'),

          ('task.parent', 'parent'),
@@ -2986,7 +2986,7 @@ 

          ('repo.task_id', 'task_id'),

          ('repo.create_event', 'create_event'),

          ('events.time', 'creation_time'),  # for compatibility with getRepo

-         ('EXTRACT(EPOCH FROM events.time)', 'create_ts'),

+         ("date_part('epoch', events.time)", 'create_ts'),

          ('repo.tag_id', 'tag_id'),

          ('tag.name', 'tag_name'),

          ('repo.dist', 'dist'),
@@ -3076,7 +3076,7 @@ 

          ('repo.state', 'state'),

          ('repo.task_id', 'task_id'),

          ('repo.create_event', 'create_event'),

-         ('EXTRACT(EPOCH FROM events.time)', 'create_ts'),

+         ("date_part('epoch', events.time)", 'create_ts'),

          ('repo.tag_id', 'tag_id'),

          ('repo.dist', 'dist'),

          ('tag.name', 'tag_name'),
@@ -4432,9 +4432,9 @@ 

                ('package.name', 'name'),

                ('volume.id', 'volume_id'), ('volume.name', 'volume_name'),

                ("package.name || '-' || build.version || '-' || build.release", 'nvr'),

-               ('EXTRACT(EPOCH FROM events.time)', 'creation_ts'),

-               ('EXTRACT(EPOCH FROM build.start_time)', 'start_ts'),

-               ('EXTRACT(EPOCH FROM build.completion_time)', 'completion_ts'),

+               ("date_part('epoch', events.time)", 'creation_ts'),

+               ("date_part('epoch', build.start_time)", 'start_ts'),

+               ("date_part('epoch', build.completion_time)", 'completion_ts'),

                ('users.id', 'owner_id'), ('users.name', 'owner_name'),

                ('build.cg_id', 'cg_id'),

                ('build.source', 'source'),
@@ -5681,9 +5681,9 @@ 

                ('repo.id', 'repo_id'), ('repo.state', 'repo_state'),

                ('tag.id', 'tag_id'), ('tag.name', 'tag_name'),

                ('create_events.id', 'create_event_id'), ('create_events.time', 'create_event_time'),

-               ('EXTRACT(EPOCH FROM create_events.time)', 'create_ts'),

+               ("date_part('epoch', create_events.time)", 'create_ts'),

                ('retire_events.id', 'retire_event_id'), ('retire_events.time', 'retire_event_time'),

-               ('EXTRACT(EPOCH FROM retire_events.time)', 'retire_ts'),

+               ("date_part('epoch', retire_events.time)", 'retire_ts'),

                ('repo_create.id', 'repo_create_event_id'),

                ('repo_create.time', 'repo_create_event_time')]

  
@@ -8108,8 +8108,8 @@ 

      common_joined_fields = {

          'creator.name': 'creator_name',

          'revoker.name': 'revoker_name',

-         'EXTRACT(EPOCH FROM ev1.time) AS create_ts': 'create_ts',

-         'EXTRACT(EPOCH FROM ev2.time) AS revoke_ts': 'revoke_ts',

+         "date_part('epoch', ev1.time) AS create_ts": 'create_ts',

+         "date_part('epoch', ev2.time) AS revoke_ts": 'revoke_ts',

      }

      table_fields = {

          'user_perms': ['user_id', 'perm_id'],
@@ -8311,8 +8311,8 @@ 

                  clauses.append('ev1.time > %(after)s OR ev2.time > %(after)s')

                  fields['ev1.time > %(after)s'] = '_created_after'

                  fields['ev2.time > %(after)s'] = '_revoked_after'

-                 # clauses.append('EXTRACT(EPOCH FROM ev1.time) > %(after)s OR '

-                 #                'EXTRACT(EPOCH FROM ev2.time) > %(after)s')

+                 # clauses.append("date_part('epoch', ev1.time) > %(after)s OR "

+                 #                "date_part('epoch', ev2.time) > %(after)s")

              elif arg == 'afterEvent':

                  data['afterEvent'] = value

                  c_test = '%s.create_event > %%(afterEvent)i' % table
@@ -8325,8 +8325,8 @@ 

                      value = datetime.datetime.fromtimestamp(value).isoformat(' ')

                  data['before'] = value

                  clauses.append('ev1.time < %(before)s OR ev2.time < %(before)s')

-                 # clauses.append('EXTRACT(EPOCH FROM ev1.time) < %(before)s OR '

-                 #                'EXTRACT(EPOCH FROM ev2.time) < %(before)s')

+                 # clauses.append("date_part('epoch', ev1.time) < %(before)s OR "

+                 #                "date_part('epoch', ev2.time) < %(before)s")

                  fields['ev1.time < %(before)s'] = '_created_before'

                  fields['ev2.time < %(before)s'] = '_revoked_before'

              elif arg == 'beforeEvent':
@@ -8527,7 +8527,7 @@ 

          event_id2 = (_fetchSingle(q, {'archive_ids': build_archive_ids}) or (0,))[0] or 0

          event_id = max(event_id, event_id2)

      if event_id:

-         q = """SELECT EXTRACT(EPOCH FROM get_event_time(%(event_id)i))"""

+         q = """SELECT date_part('epoch', get_event_time(%(event_id)i))"""

          ret['last_used'] = _singleValue(q, locals())

      else:

          ret['last_used'] = None
@@ -10916,8 +10916,7 @@ 

          """

          fields = ('id', 'ts')

          values = {'id': id}

-         q = """SELECT id, EXTRACT(EPOCH FROM time) FROM events

-                 WHERE id = %(id)i"""

+         q = """SELECT id, date_part('epoch', time) FROM events WHERE id = %(id)i"""

          return _singleRow(q, values, fields, strict=True)

  

      def getLastEvent(self, before=None):
@@ -10939,13 +10938,13 @@ 

          """

          fields = ('id', 'ts')

          values = {}

-         q = """SELECT id, EXTRACT(EPOCH FROM time) FROM events"""

+         q = """SELECT id, date_part('epoch', time) FROM events"""

          if before is not None:

              if not isinstance(before, NUMERIC_TYPES):

                  raise koji.GenericError('Invalid type for before: %s' % type(before))

              # use the repr() conversion because it retains more precision than the

              # string conversion

-             q += """ WHERE EXTRACT(EPOCH FROM time) < %(before)r"""

+             q += """ WHERE date_part('epoch', time) < %(before)r"""

              values['before'] = before

          q += """ ORDER BY id DESC LIMIT 1"""

          return _singleRow(q, values, fields, strict=True)
@@ -11970,9 +11969,9 @@ 

                    ('build.extra', 'extra'),

                    ('events.id', 'creation_event_id'), ('events.time', 'creation_time'),

                    ('build.task_id', 'task_id'),

-                   ('EXTRACT(EPOCH FROM events.time)', 'creation_ts'),

-                   ('EXTRACT(EPOCH FROM build.start_time)', 'start_ts'),

-                   ('EXTRACT(EPOCH FROM build.completion_time)', 'completion_ts'),

+                   ("date_part('epoch', events.time)", 'creation_ts'),

+                   ("date_part('epoch', build.start_time)", 'start_ts'),

+                   ("date_part('epoch', build.completion_time)", 'completion_ts'),

                    ('package.id', 'package_id'), ('package.name', 'package_name'),

                    ('package.name', 'name'),

                    ('volume.id', 'volume_id'), ('volume.name', 'volume_name'),
@@ -12113,7 +12112,7 @@ 

          if not packageID:

              return None

          st_complete = koji.BUILD_STATES['COMPLETE']

-         query = """SELECT EXTRACT(epoch FROM avg(build.completion_time - events.time))

+         query = """SELECT date_part('epoch', avg(build.completion_time - events.time))

                       FROM build

                       JOIN events ON build.create_event = events.id

                       WHERE build.pkg_id = %(packageID)i
@@ -12802,7 +12801,7 @@ 

              id = get_tag_id(tag, strict=True)

  

          fields = ['repo.id', 'repo.state', 'repo.task_id', 'repo.create_event', 'events.time',

-                   'EXTRACT(EPOCH FROM events.time)', 'repo.dist']

+                   "date_part('epoch', events.time)", 'repo.dist']

          aliases = ['id', 'state', 'task_id', 'create_event', 'creation_time', 'create_ts', 'dist']

          joins = ['events ON repo.create_event = events.id']

          clauses = ['repo.tag_id = %(id)i']

file modified
+2 -2
@@ -101,8 +101,8 @@ 

              'master': 'master',

              'start_time': 'start_time',

              'update_time': 'update_time',

-             'EXTRACT(EPOCH FROM start_time)': 'start_ts',

-             'EXTRACT(EPOCH FROM update_time)': 'update_ts',

+             "date_part('epoch', start_time)": 'start_ts',

+             "date_part('epoch', update_time)": 'update_ts',

              'user_id': 'user_id',

          }

          # sort for stability (unittests)

@@ -12,7 +12,7 @@ 

  

      def setUp(self):

          self.QueryProcessor = mock.patch('kojihub.QueryProcessor',

-                 side_effect=self.getQuery).start()

+                                          side_effect=self.getQuery).start()

          self.queries = []

  

      def getQuery(self, *args, **kwargs):
@@ -33,7 +33,7 @@ 

          str(query)

          self.assertEqual(query.tables, ['repo'])

          columns = ['repo.id', 'repo.state', 'repo.task_id', 'repo.create_event',

-                    'EXTRACT(EPOCH FROM events.time)', 'repo.tag_id', 'repo.dist', 'tag.name']

+                    "date_part('epoch', events.time)", 'repo.tag_id', 'repo.dist', 'tag.name']

          self.assertEqual(set(query.columns), set(columns))

          self.assertEqual(query.clauses, ['repo.state != %(st_deleted)s'])

          self.assertEqual(query.joins, ['tag ON repo.tag_id=tag.id',

@@ -60,14 +60,14 @@ 

          qp = QP(**kwargs)

          self.assertEqual(qp.tables, ['build'])

          self.assertEqual(qp.columns, ['build.id', 'build.completion_time',

-                                       'EXTRACT(EPOCH FROM build.completion_time)',

+                                       "date_part('epoch', build.completion_time)",

                                        'events.id', 'events.time',

-                                       'EXTRACT(EPOCH FROM events.time)', 'build.epoch',

+                                       "date_part('epoch', events.time)", 'build.epoch',

                                        'build.extra', 'package.name',

                                        "package.name || '-' || build.version || '-' || "

                                        "build.release", 'users.id', 'users.name', 'package.id',

                                        'package.name', 'build.release', 'build.source',

-                                       'build.start_time', 'EXTRACT(EPOCH FROM build.start_time)',

+                                       'build.start_time', "date_part('epoch', build.start_time)",

                                        'build.state', 'build.task_id', 'build.version',

                                        'volume.id', 'volume.name'])

          self.assertEqual(qp.clauses, ['package.id = %(packageID)i'])

file modified
+2 -2
@@ -102,7 +102,7 @@ 

          str(query)

          self.assertEqual(query.tables, ['repo'])

          columns = ['repo.id', 'repo.state', 'repo.task_id', 'repo.create_event',

-                    'EXTRACT(EPOCH FROM events.time)', 'repo.dist', 'events.time']

+                    "date_part('epoch', events.time)", 'repo.dist', 'events.time']

          self.assertEqual(set(query.columns), set(columns))

          self.assertEqual(query.joins, ['events ON repo.create_event = events.id'])

          self.assertEqual(query.clauses, ['repo.dist is false', 'repo.state = %(state)s',
@@ -116,7 +116,7 @@ 

          str(query)

          self.assertEqual(query.tables, ['repo'])

          columns = ['repo.id', 'repo.state', 'repo.task_id', 'repo.create_event',

-                    'EXTRACT(EPOCH FROM events.time)', 'repo.dist', 'events.time']

+                    "date_part('epoch', events.time)", 'repo.dist', 'events.time']

          self.assertEqual(set(query.columns), set(columns))

          self.assertEqual(query.joins, ['events ON repo.create_event = events.id'])

          self.assertEqual(query.clauses, ['create_event <= %(event)i', 'repo.dist is true',

rebased onto a8379ab

2 months ago

Metadata Update from @tkopecek:
- Pull-request tagged with: testing-ready

2 months ago

Metadata Update from @jobrauer:
- Pull-request tagged with: testing-done

2 months ago

Commit 7d6fa96 fixes this pull-request

Pull-Request has been merged by tkopecek

2 months ago