1package App::Netdisco::DB::Result::Virtual::TastyJobs;
2
3use strict;
4use warnings;
5
6use base 'DBIx::Class::Core';
7
8__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
9
10__PACKAGE__->table('tasty_jobs');
11__PACKAGE__->result_source_instance->is_virtual(1);
12__PACKAGE__->result_source_instance->view_definition(<<ENDSQL
13  WITH my_jobs AS
14    (SELECT admin.* FROM admin
15       LEFT OUTER JOIN device_skip ds
16         ON (ds.backend = ? AND admin.device = ds.device
17             AND admin.action = ANY (ds.actionset))
18      WHERE admin.status = 'queued'
19        AND ds.device IS NULL)
20
21  SELECT my_jobs.*,
22         CASE WHEN ( (my_jobs.username IS NOT NULL AND ((ds.deferrals IS NULL AND ds.last_defer IS NULL)
23                                                        OR my_jobs.entered > ds.last_defer))
24                    OR (my_jobs.action = ANY (string_to_array(btrim(?, '{"}'), '","'))) )
25              THEN 100
26              ELSE 0
27          END AS job_priority
28    FROM my_jobs
29
30    LEFT OUTER JOIN device_skip ds
31      ON (ds.backend = ? AND ds.device = my_jobs.device)
32
33   WHERE ds.deferrals < ?
34      OR (my_jobs.username IS NOT NULL AND (ds.last_defer IS NULL
35                                            OR my_jobs.entered > ds.last_defer))
36      OR (ds.deferrals IS NULL AND ds.last_defer IS NULL)
37      OR ds.last_defer <= ( LOCALTIMESTAMP - ?::interval )
38
39   ORDER BY job_priority DESC,
40            ds.deferrals ASC NULLS FIRST,
41            ds.last_defer ASC NULLS LAST,
42            device_key DESC NULLS LAST,
43            random()
44   LIMIT ?
45ENDSQL
46);
47
48__PACKAGE__->add_columns(
49  "job",
50  { data_type => "integer", is_nullable => 0, },
51  "entered",
52  { data_type => "timestamp", is_nullable => 1 },
53  "started",
54  { data_type => "timestamp", is_nullable => 1 },
55  "finished",
56  { data_type => "timestamp", is_nullable => 1 },
57  "device",
58  { data_type => "inet", is_nullable => 1 },
59  "port",
60  { data_type => "text", is_nullable => 1 },
61  "action",
62  { data_type => "text", is_nullable => 1 },
63  "subaction",
64  { data_type => "text", is_nullable => 1 },
65  "status",
66  { data_type => "text", is_nullable => 1 },
67  "username",
68  { data_type => "text", is_nullable => 1 },
69  "userip",
70  { data_type => "inet", is_nullable => 1 },
71  "log",
72  { data_type => "text", is_nullable => 1 },
73  "debug",
74  { data_type => "boolean", is_nullable => 1 },
75  "device_key",
76  { data_type => "text", is_nullable => 1 },
77  "job_priority",
78  { data_type => "integer", is_nullable => 1 },
79);
80
81__PACKAGE__->set_primary_key("job");
82
831;
84