1 -- SQL script for upgrading the local database to the format expected for LBS
4 -- This script should be loaded after connecting to the database to be
7 -- Database schema changes: the size column was added to the segments table,
8 -- and the segments_used table was added. Rather than upgrade the segments
9 -- table in-place, we create a new table and then rename it over the old
11 create table segments_new (
12 segmentid integer primary key,
13 segment text unique not null,
19 create table segments_used (
20 snapshotid integer not null,
21 segmentid integer not null,
25 -- Compute the size of each of the segments, if possible, based on our
26 -- knowledge of the objects stored in them.
27 insert into segments_new
28 select segmentid, segment, path, checksum, size
30 (select segmentid, segment, path, checksum from segments)
32 (select segmentid, sum(size) as size from block_index group by segmentid)
36 alter table segments_new rename to segments;
38 -- Populate the segments_used table based upon data contained in
39 -- snapshot_contents--this is roughly the same calculation that is actually
40 -- done, only now this calculation is done when a snapshot is created.
41 insert into segments_used
42 select snapshotid, segmentid, cast(used as real) / size as utilization
44 (select snapshotid, segmentid, sum(size) as used
45 from snapshot_contents join block_index using (blockid)
46 group by snapshotid, segmentid)
48 (select segmentid, size from segments)
51 -- The snapshot_contents table is obsolete.
52 drop table snapshot_contents;