coder · kylecarbs · Jun 20, 2023 · Jun 17, 2023 · Jun 17, 2023 · Jun 20, 2023
diff --git a/.github/workflows/ci.yaml b/.github/workflows/ci.yaml
@@ -153,7 +153,7 @@ jobs:
 
       - name: Install sqlc
         run: |
-          curl -sSL https://github.com/kyleconroy/sqlc/releases/download/v1.17.2/sqlc_1.17.2_linux_amd64.tar.gz | sudo tar -C /usr/bin -xz sqlc
+          curl -sSL https://github.com/kyleconroy/sqlc/releases/download/v1.18.0/sqlc_1.18.0_linux_amd64.tar.gz | sudo tar -C /usr/bin -xz sqlc
 
       - name: go install tools
         run: |

diff --git a/cli/testdata/coder_list_--output_json.golden b/cli/testdata/coder_list_--output_json.golden
@@ -35,7 +35,9 @@
         "file_id": "[workspace build file ID]",
         "tags": {
           "scope": "organization"
-        }
+        },
+        "queue_position": 0,
+        "queue_size": 0
       },
       "reason": "initiator",
       "resources": [],

diff --git a/coderd/apidoc/docs.go b/coderd/apidoc/docs.go
diff --git a/coderd/apidoc/swagger.json b/coderd/apidoc/swagger.json
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1087,6 +1087,11 @@ func (q *querier) GetProvisionerJobsByIDs(ctx context.Context, ids []uuid.UUID)
 	return q.db.GetProvisionerJobsByIDs(ctx, ids)
 }
 
+// TODO: we need to add a provisioner job resource
+func (q *querier) GetProvisionerJobsByIDsWithQueuePosition(ctx context.Context, ids []uuid.UUID) ([]database.GetProvisionerJobsByIDsWithQueuePositionRow, error) {
+	return q.db.GetProvisionerJobsByIDsWithQueuePosition(ctx, ids)
+}
+
 // TODO: We need to create a ProvisionerJob resource type
 func (q *querier) GetProvisionerJobsCreatedAfter(ctx context.Context, createdAt time.Time) ([]database.ProvisionerJob, error) {
 	// if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {

diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -2051,6 +2051,38 @@ func (q *fakeQuerier) GetProvisionerJobsByIDs(_ context.Context, ids []uuid.UUID
 	return jobs, nil
 }
 
+func (q *fakeQuerier) GetProvisionerJobsByIDsWithQueuePosition(_ context.Context, ids []uuid.UUID) ([]database.GetProvisionerJobsByIDsWithQueuePositionRow, error) {
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	jobs := make([]database.GetProvisionerJobsByIDsWithQueuePositionRow, 0)
+	queuePosition := int64(1)
+	for _, job := range q.provisionerJobs {
+		for _, id := range ids {
+			if id == job.ID {
+				job := database.GetProvisionerJobsByIDsWithQueuePositionRow{
+					ProvisionerJob: job,
+				}
+				if !job.ProvisionerJob.StartedAt.Valid {
+					job.QueuePosition = queuePosition
+				}
+				jobs = append(jobs, job)
+				break
+			}
+		}
+		if !job.StartedAt.Valid {
+			queuePosition++
+		}
+	}
+	for _, job := range jobs {
+		if !job.ProvisionerJob.StartedAt.Valid {
+			// Set it to the max position!
+			job.QueueSize = queuePosition
+		}
+	}
+	return jobs, nil
+}
+
 func (q *fakeQuerier) GetProvisionerJobsCreatedAfter(_ context.Context, after time.Time) ([]database.ProvisionerJob, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()

diff --git a/coderd/database/dbmetrics/dbmetrics.go b/coderd/database/dbmetrics/dbmetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/querier_test.go b/coderd/database/querier_test.go
@@ -5,6 +5,8 @@ package database_test
 import (
 	"context"
 	"database/sql"
+	"encoding/json"
+	"sort"
 	"testing"
 	"time"
 
@@ -314,3 +316,76 @@ func TestDefaultProxy(t *testing.T) {
 	require.NoError(t, err, "get deployment id")
 	require.Equal(t, depID, found)
 }
+
+func TestQueuePosition(t *testing.T) {
+	t.Parallel()
+
+	if testing.Short() {
+		t.SkipNow()
+	}
+	sqlDB := testSQLDB(t)
+	err := migrations.Up(sqlDB)
+	require.NoError(t, err)
+	db := database.New(sqlDB)
+	ctx := testutil.Context(t, testutil.WaitLong)
+
+	org := dbgen.Organization(t, db, database.Organization{})
+	jobCount := 10
+	jobs := []database.ProvisionerJob{}
+	jobIDs := []uuid.UUID{}
+	for i := 0; i < jobCount; i++ {
+		job := dbgen.ProvisionerJob(t, db, database.ProvisionerJob{
+			OrganizationID: org.ID,
+			Tags:           database.StringMap{},
+		})
+		jobs = append(jobs, job)
+		jobIDs = append(jobIDs, job.ID)
+
+		// We need a slight amount of time between each insertion to ensure that
+		// the queue position is correct... it's sorted by `created_at`.
+		time.Sleep(time.Millisecond)
+	}
+
+	queued, err := db.GetProvisionerJobsByIDsWithQueuePosition(ctx, jobIDs)
+	require.NoError(t, err)
+	require.Len(t, queued, jobCount)
+	sort.Slice(queued, func(i, j int) bool {
+		return queued[i].QueuePosition < queued[j].QueuePosition
+	})
+	// Ensure that the queue positions are correct based on insertion ID!
+	for index, job := range queued {
+		require.Equal(t, job.QueuePosition, int64(index+1))
+		require.Equal(t, job.ProvisionerJob.ID, jobs[index].ID)
+	}
+
+	job, err := db.AcquireProvisionerJob(ctx, database.AcquireProvisionerJobParams{
+		StartedAt: sql.NullTime{
+			Time:  database.Now(),
+			Valid: true,
+		},
+		Types: database.AllProvisionerTypeValues(),
+		WorkerID: uuid.NullUUID{
+			UUID:  uuid.New(),
+			Valid: true,
+		},
+		Tags: json.RawMessage("{}"),
+	})
+	require.NoError(t, err)
+	require.Equal(t, jobs[0].ID, job.ID)
+
+	queued, err = db.GetProvisionerJobsByIDsWithQueuePosition(ctx, jobIDs)
+	require.NoError(t, err)
+	require.Len(t, queued, jobCount)
+	sort.Slice(queued, func(i, j int) bool {
+		return queued[i].QueuePosition < queued[j].QueuePosition
+	})
+	// Ensure that queue positions are updated now that the first job has been acquired!
+	for index, job := range queued {
+		if index == 0 {
+			require.Equal(t, job.QueuePosition, int64(0))
+			continue
+		}
+		require.Equal(t, job.QueuePosition, int64(index))
+		require.Equal(t, job.ProvisionerJob.ID, jobs[index].ID)
+	}
+}
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/provisionerjobs.sql b/coderd/database/queries/provisionerjobs.sql
@@ -47,6 +47,38 @@ FROM
 WHERE
 	id = ANY(@ids :: uuid [ ]);
 
+-- name: GetProvisionerJobsByIDsWithQueuePosition :many
+WITH unstarted_jobs AS (
+    SELECT
+        id, created_at
+    FROM
+        provisioner_jobs
+    WHERE
+        started_at IS NULL
+),
+queue_position AS (
+    SELECT
+        id,
+        ROW_NUMBER() OVER (ORDER BY created_at ASC) AS queue_position
+    FROM
+        unstarted_jobs
+),
+queue_size AS (
+	SELECT COUNT(*) as count FROM unstarted_jobs
+)
+SELECT
+	sqlc.embed(pj),
+    COALESCE(qp.queue_position, 0) AS queue_position,
+    COALESCE(qs.count, 0) AS queue_size
+FROM
+	provisioner_jobs pj
+LEFT JOIN
+	queue_position qp ON qp.id = pj.id
+LEFT JOIN
+	queue_size qs ON TRUE
+WHERE
+	pj.id = ANY(@ids :: uuid [ ]);
+
 -- name: GetProvisionerJobsCreatedAfter :many
 SELECT * FROM provisioner_jobs WHERE created_at > $1;