summaryrefslogtreecommitdiff
path: root/glance/db/sqlalchemy/migrate_repo/versions/045_sqlite_upgrade.sql
blob: 0e848ccefbf357285e7b75598e29511aa986d16b (plain)
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
CREATE TEMPORARY TABLE images_backup (
    id VARCHAR(36) NOT NULL,
    name VARCHAR(255),
    size INTEGER,
    status VARCHAR(30) NOT NULL,
    is_public BOOLEAN NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    deleted_at DATETIME,
    deleted BOOLEAN NOT NULL,
    disk_format VARCHAR(20),
    container_format VARCHAR(20),
    checksum VARCHAR(32),
    owner VARCHAR(255),
    min_disk INTEGER NOT NULL,
    min_ram INTEGER NOT NULL,
    protected BOOLEAN DEFAULT 0 NOT NULL,
    virtual_size INTEGER,
    PRIMARY KEY (id),
    CHECK (is_public IN (0, 1)),
    CHECK (deleted IN (0, 1)),
    CHECK (protected IN (0, 1))
);

INSERT INTO images_backup
    SELECT id,
        name,
        size,
        status,
        is_public,
        created_at,
        updated_at,
        deleted_at,
        deleted,
        disk_format,
        container_format,
        checksum,
        owner,
        min_disk,
        min_ram,
        protected,
        virtual_size
    FROM images;

DROP TABLE images;

CREATE TABLE images (
    id VARCHAR(36) NOT NULL,
    name VARCHAR(255),
    size INTEGER,
    status VARCHAR(30) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    deleted_at DATETIME,
    deleted BOOLEAN NOT NULL,
    disk_format VARCHAR(20),
    container_format VARCHAR(20),
    checksum VARCHAR(32),
    owner VARCHAR(255),
    min_disk INTEGER NOT NULL,
    min_ram INTEGER NOT NULL,
    protected BOOLEAN DEFAULT 0 NOT NULL,
    virtual_size INTEGER,
    visibility VARCHAR(9) DEFAULT 'shared' NOT NULL,
    PRIMARY KEY (id),
    CHECK (deleted IN (0, 1)),
    CHECK (protected IN (0, 1)),
    CONSTRAINT image_visibility CHECK (visibility IN ('private', 'public', 'shared', 'community'))
);

CREATE INDEX checksum_image_idx ON images (checksum);
CREATE INDEX visibility_image_idx ON images (visibility);
CREATE INDEX ix_images_deleted ON images (deleted);
CREATE INDEX owner_image_idx ON images (owner);
CREATE INDEX created_at_image_idx ON images (created_at);
CREATE INDEX updated_at_image_idx ON images (updated_at);

-- Copy over all the 'public' rows

INSERT INTO images (
    id,
    name,
    size,
    status,
    created_at,
    updated_at,
    deleted_at,
    deleted,
    disk_format,
    container_format,
    checksum,
    owner,
    min_disk,
    min_ram,
    protected,
    virtual_size
    )
    SELECT id,
        name,
        size,
        status,
        created_at,
        updated_at,
        deleted_at,
        deleted,
        disk_format,
        container_format,
        checksum,
        owner,
        min_disk,
        min_ram,
        protected,
        virtual_size
    FROM images_backup
    WHERE is_public=1;


UPDATE images SET visibility='public';

-- Now copy over the 'private' rows

INSERT INTO images (
    id,
    name,
    size,
    status,
    created_at,
    updated_at,
    deleted_at,
    deleted,
    disk_format,
    container_format,
    checksum,
    owner,
    min_disk,
    min_ram,
    protected,
    virtual_size
    )
    SELECT id,
        name,
        size,
        status,
        created_at,
        updated_at,
        deleted_at,
        deleted,
        disk_format,
        container_format,
        checksum,
        owner,
        min_disk,
        min_ram,
        protected,
        virtual_size
    FROM images_backup
    WHERE is_public=0;

UPDATE images SET visibility='private' WHERE visibility='shared';
UPDATE images SET visibility='shared' WHERE visibility='private' AND id IN (SELECT DISTINCT image_id FROM image_members WHERE deleted != 1);

DROP TABLE images_backup;