# HG changeset patch
# User David Demelier <markand@malikania.fr>
# Date 1624441470 -7200
# Node ID 8c408176d2b1a585d61d90b0b52cf453ebf66d60
# Parent  566bc028cdcbc6d4010961a8e57cfeb371b8f0e5
scid: past jobs are not listed for new workers

diff -r 566bc028cdcb -r 8c408176d2b1 db.c
--- a/db.c	Wed Jun 16 13:25:42 2021 +0200
+++ b/db.c	Wed Jun 23 11:44:30 2021 +0200
@@ -351,7 +351,7 @@
 		.ctx = ctx
 	};
 
-	return list(&sel, CHAR(sql_job_todo), "iz", worker_id, jobsz);
+	return list(&sel, CHAR(sql_job_todo), "iiz", worker_id, worker_id, jobsz);
 }
 
 int
diff -r 566bc028cdcb -r 8c408176d2b1 sql/init.sql
--- a/sql/init.sql	Wed Jun 16 13:25:42 2021 +0200
+++ b/sql/init.sql	Wed Jun 23 11:44:30 2021 +0200
@@ -3,19 +3,22 @@
 	name TEXT NOT NULL,
 	desc TEXT NOT NULL,
 	url TEXT NOT NULL,
-	script TEXT NOT NULL
+	script TEXT NOT NULL,
+	date INTEGER NOT NULL DEFAULT (strftime('%s','now'))
 );
 
 CREATE TABLE IF NOT EXISTS worker(
 	id INTEGER PRIMARY KEY AUTOINCREMENT,
 	name TEXT NOT NULL,
-	desc TEXT NOT NULL
+	desc TEXT NOT NULL,
+	date INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
 );
 
 CREATE TABLE IF NOT EXISTS job(
 	id INTEGER PRIMARY KEY AUTOINCREMENT,
 	tag TEXT NOT NULL UNIQUE,
-	project_id INTEGER NOT NULL REFERENCES project (id)
+	project_id INTEGER NOT NULL REFERENCES project (id),
+	date INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
 );
 
 CREATE TABLE IF NOT EXISTS jobresult(
@@ -24,5 +27,6 @@
 	worker_id INTEGER NOT NULL REFERENCES worker (id),
 	status INTEGER DEFAULT 0,
 	retcode INTEGER DEFAULT 0,
-	console TEXT DEFAULT NULL
+	console TEXT DEFAULT NULL,
+	date INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
 );
diff -r 566bc028cdcb -r 8c408176d2b1 sql/job-todo.sql
--- a/sql/job-todo.sql	Wed Jun 16 13:25:42 2021 +0200
+++ b/sql/job-todo.sql	Wed Jun 23 11:44:30 2021 +0200
@@ -1,11 +1,39 @@
-     SELECT job.id
-          , job.tag
-          , job.project_id
-       FROM job
-      WHERE job.id
-     NOT IN (
-            SELECT jobresult.job_id
-              FROM jobresult
-             WHERE jobresult.worker_id = ?
-     )
-      LIMIT ?
+--
+-- job-todo.sql -- list jobs to perform for a worker
+--
+-- Copyright (c) 2021 David Demelier <markand@malikania.fr>
+--
+-- Permission to use, copy, modify, and/or distribute this software for any
+-- purpose with or without fee is hereby granted, provided that the above
+-- copyright notice and this permission notice appear in all copies.
+--
+-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
+-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
+-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
+-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
+-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
+-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
+-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+--
+
+--
+-- This request won't list jobs that were created before the worker itself
+-- otherwise when adding a new worker it would need to run potentially a very
+-- high number of jobs
+--
+SELECT job.id
+     , job.tag
+     , job.project_id
+  FROM job
+ WHERE job.id
+NOT IN (
+       SELECT jobresult.job_id
+         FROM jobresult
+        WHERE jobresult.worker_id = ?
+       )
+   AND job.date >= (
+                 SELECT worker.date
+                   FROM worker
+                  WHERE worker.id = ?
+                 )
+ LIMIT ?