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