/
device_snap_sql.go
69 lines (62 loc) · 2.47 KB
/
device_snap_sql.go
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
// -*- Mode: Go; indent-tabs-mode: t -*-
/*
* This file is part of the IoT Device Twin Service
* Copyright 2019 Canonical Ltd.
*
* This program is free software: you can redistribute it and/or modify it
* under the terms of the GNU Affero General Public License version 3, as
* published by the Free Software Foundation.
*
* This program is distributed in the hope that it will be useful, but WITHOUT
* ANY WARRANTY; without even the implied warranties of MERCHANTABILITY,
* SATISFACTORY QUALITY, or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package postgres
const createDeviceSnapTableSQL = `
CREATE TABLE IF NOT EXISTS device_snap (
id serial primary key,
created timestamp default current_timestamp,
modified timestamp default current_timestamp,
device_id int references device not null,
name varchar(200) not null,
installed_size int default 0,
installed_date timestamp default current_timestamp,
status varchar(200) default '',
channel varchar(200) default '',
confinement varchar(200) default '',
version varchar(200) default '',
revision int default 0,
devmode bool default false,
config text default ''
)
`
const createDeviceSnapIndexSQL = "CREATE UNIQUE INDEX IF NOT EXISTS device_snap_idx ON device_snap (device_id, name)"
const upsertDeviceSnapSQL = `
INSERT INTO device_snap(device_id, name, installed_size, installed_date, status, channel, confinement, version, revision, devmode, config)
VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
ON CONFLICT (device_id, name)
DO
UPDATE
SET installed_size = EXCLUDED.installed_size,
installed_date = EXCLUDED.installed_date,
status = EXCLUDED.status,
channel = EXCLUDED.channel,
confinement = EXCLUDED.confinement,
version = EXCLUDED.version,
revision = EXCLUDED.revision,
devmode = EXCLUDED.devmode,
config = EXCLUDED.config,
modified = current_timestamp
RETURNING id;
`
const listDeviceSnapSQL = `
select id, created, modified, device_id, name, installed_size, installed_date, status, channel, confinement, version, revision, devmode, config
from device_snap
where device_id=$1
order by name`
const deleteDeviceSnapSQL = `
delete from device_snap where device_id=$1`