~sircmpwn/builds.sr.ht

builds.sr.ht/worker/database.go -rw-r--r-- 5.0 KiB
9175cd35Eli Schwartz Revert "images/archlinux: skip compression for makepkg" 2 days ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
package main

import (
	"database/sql"
	"time"
)

type Job struct {
	db *sql.DB

	Id         int
	Created    time.Time
	Updated    time.Time
	Manifest   string
	OwnerId    int
	JobGroupId *int
	Note       *string
	Status     string
	Runner     *string
	Tags       *string
	Secrets    bool
	Image      string

	Username string
}

type Secret struct {
	Id         int
	UserId     int
	Created    time.Time
	Updated    time.Time
	Uuid       string
	Name       *string
	SecretType string
	Secret     []byte
	Path       *string
	Mode       *int
}

type JobGroup struct {
	db *sql.DB

	Id       int
	OwnerId  int
	Created  time.Time
	Updated  time.Time
	Note     *string
	Complete bool

	Jobs []*Job
}

type Trigger struct {
	Id        int
	Created   time.Time
	Updated   time.Time
	Action    string
	Condition string
	Details   string
}

func GetJob(db *sql.DB, id int) (*Job, error) {
	row := db.QueryRow(`
		SELECT
			"job"."id", "job"."created", "job"."updated", "job"."manifest",
			"job"."owner_id", "job"."job_group_id", "job"."note",
			"job"."status", "job"."runner", "job"."tags", "job"."secrets",
			"job"."image", "user".username
		FROM "job"
		JOIN "user" ON "job"."owner_id" = "user"."id"
		WHERE "job"."id" = $1;
	`, id)
	var job Job
	job.db = db
	if err := row.Scan(
		&job.Id, &job.Created, &job.Updated, &job.Manifest, &job.OwnerId,
		&job.JobGroupId, &job.Note, &job.Status, &job.Runner, &job.Tags,
		&job.Secrets, &job.Image, &job.Username); err != nil {

		return nil, err
	}
	return &job, nil
}

func GetSecret(db *sql.DB, uuid string) (*Secret, error) {
	row := db.QueryRow(`
		SELECT
			"id", "user_id", "created", "updated", "uuid",
			"name", "secret_type", "secret", "path", "mode"
		FROM "secret" WHERE "uuid" = $1;
	`, uuid)
	var secret Secret
	if err := row.Scan(
		&secret.Id, &secret.UserId, &secret.Created, &secret.Updated,
		&secret.Uuid, &secret.Name, &secret.SecretType, &secret.Secret,
		&secret.Path, &secret.Mode); err != nil {

		return nil, err
	}
	return &secret, nil
}

func GetJobGroup(db *sql.DB, jobGroupId int) (*JobGroup, error) {
	row := db.QueryRow(`
		SELECT
			jg.id, jg.created, jg.updated, jg.owner_id, jg.note,
			sum(CASE WHEN j.status not in ('pending', 'queued')
				THEN 1 ELSE 0 END) = count(j) as complete
		FROM job_group jg
		JOIN job j ON j.job_group_id = jg.id
		WHERE jg.id = $1
		GROUP BY jg.id;
	`, jobGroupId)
	var jg JobGroup
	jg.db = db
	if err := row.Scan(&jg.Id, &jg.Created, &jg.Updated, &jg.OwnerId, &jg.Note,
		&jg.Complete); err != nil {

		return nil, err
	}
	return &jg, nil
}

func (job *Job) SetRunner(runner string) error {
	_, err := job.db.Exec(`UPDATE "job" SET "runner" = $2 WHERE "id" = $1`,
		job.Id, runner)
	if err == nil {
		_runner := runner
		job.Runner = &_runner
	}
	return err
}

func (job *Job) SetStatus(status string) error {
	_, err := job.db.Exec(`UPDATE "job" SET "status" = $2 WHERE "id" = $1`,
		job.Id, status)
	if err == nil {
		job.Status = status
		_, err = job.db.Exec(`UPDATE "job" SET "updated" = $2 WHERE "id" = $1`,
			job.Id, time.Now().UTC())
	}
	return err
}

func (job *Job) SetTaskStatus(name, status string) error {
	_, err := job.db.Exec(`
		UPDATE "task"
		SET "status" = $3
		WHERE "job_id" = $1 AND "name" = $2
	`, job.Id, name, status)
	return err
}

func (job *Job) GetTaskStatus(name string) (string, error) {
	row := job.db.QueryRow(`
		SELECT "status" FROM "task" WHERE "job_id" = $1 AND "name" = $2
	`, job.Id, name)
	var status string
	if err := row.Scan(&status); err != nil {
		return "", err
	}
	return status, nil
}

func (job *Job) InsertArtifact(path string, name string,
	url string, size int64) error {

	_, err := job.db.Exec(`
		INSERT INTO
		"artifact" (created, job_id, path, name, url, size)
		VALUES (NOW() AT TIME ZONE 'UTC',
			$1, $2, $3, $4, $5)
	`, job.Id, path, name, url, size)
	return err
}

func (jg *JobGroup) GetTriggers() ([]*Trigger, error) {
	rows, err := jg.db.Query(`
		SELECT
			id, created, updated, details, condition, trigger_type
		FROM trigger
		WHERE job_group_id = $1;
	`, jg.Id)
	if err != nil {
		return nil, err
	}
	var triggers []*Trigger
	for rows.Next() {
		trigger := &Trigger{}
		if err := rows.Scan(&trigger.Id, &trigger.Created, &trigger.Updated,
			&trigger.Details, &trigger.Condition, &trigger.Action); err != nil {

			return nil, err
		}
		triggers = append(triggers, trigger)
	}
	return triggers, nil
}

func (jg *JobGroup) GetJobs() error {
	rows, err := jg.db.Query(`
		SELECT
			"job"."id", "job"."created", "job"."updated", "job"."manifest",
			"job"."owner_id", "job"."job_group_id", "job"."note",
			"job"."status", "job"."runner", "job"."tags", "job"."secrets",
			"job".image, "user".username
		FROM "job"
		JOIN "user" ON "job"."owner_id" = "user"."id"
		WHERE "job"."job_group_id" = $1;
	`, jg.Id)
	if err != nil {
		return err
	}
	var jobs []*Job
	for rows.Next() {
		job := &Job{}
		if err := rows.Scan(
			&job.Id, &job.Created, &job.Updated, &job.Manifest, &job.OwnerId,
			&job.JobGroupId, &job.Note, &job.Status, &job.Runner, &job.Tags,
			&job.Secrets, &job.Image, &job.Username); err != nil {

			return err
		}
		jobs = append(jobs, job)
	}
	jg.Jobs = jobs
	return nil
}