1
# Copyright 2008-2015 Canonical
3
# This program is free software: you can redistribute it and/or modify
4
# it under the terms of the GNU Affero General Public License as
5
# published by the Free Software Foundation, either version 3 of the
6
# License, or (at your option) any later version.
8
# This program is distributed in the hope that it will be useful,
9
# but WITHOUT ANY WARRANTY; without even the implied warranty of
10
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11
# GNU Affero General Public License for more details.
13
# You should have received a copy of the GNU Affero General Public License
14
# along with this program. If not, see <http://www.gnu.org/licenses/>.
16
# For further info, check http://launchpad.net/filesync-server
18
"""Initializing schema.
20
It has all the create, delete and drop instructions to build the
24
from backends.db.tools.schema import Schema
26
__all__ = ["create_schema"]
30
'''Creates the Schema with all the instructions.'''
31
# We pass this very package to schema, so we need to reimport ourselves
32
from backends.db.schemas import fsync_main as patch_package
33
return Schema(CREATE, DROP, DELETE, patch_package, 'patch_main')
38
CREATE TYPE lifecycle_status as enum('Live', 'Dead');
41
CREATE TABLE StorageUser (
42
id integer NOT NULL PRIMARY KEY,
43
max_average_download_bytes_per_day bigint,
44
status lifecycle_status
45
DEFAULT 'Live'::lifecycle_status NOT NULL,
46
visible_name character varying(256),
47
username character varying(256) UNIQUE,
48
subscription_status lifecycle_status
49
DEFAULT 'Live'::lifecycle_status NOT NULL,
50
shard_id Text DEFAULT 'storage',
55
COMMENT ON TABLE StorageUser IS 'StorageUsers that the \
56
storage system is aware of.';
59
COMMENT ON COLUMN StorageUser.id IS 'A unique identifier for this record.';
62
COMMENT ON COLUMN StorageUser.status IS
63
'Whether the record represents a live, valid StorageUser.';
66
COMMENT ON COLUMN storageuser.visible_name IS
67
'The visible name of the user, the more human friendly of his/her \
71
COMMENT ON COLUMN storageuser.username IS
72
'The username of the user, its human friendly unique id.';
75
COMMENT ON COLUMN storageuser.subscription_status IS
76
'The status of the user subscription.';
79
COMMENT ON COLUMN StorageUser.shard_id IS
80
'The internal identifier for the database shard the user''s data is on'
83
COMMENT ON COLUMN StorageUser.root_volume_id IS
84
'The root UserVolume id for this user';
87
CREATE TYPE access_level AS ENUM('View', 'Modify');
91
shared_by INT NOT NULL REFERENCES StorageUser (id),
92
subtree UUID NOT NULL,
93
shared_to INT REFERENCES StorageUser (id),
95
accepted boolean NOT NULL,
96
access access_level NOT NULL,
97
when_shared timestamp without time zone
98
DEFAULT timezone('UTC'::text, now()) NOT NULL,
99
when_last_changed timestamp without time zone
100
DEFAULT timezone('UTC'::text, now()) NOT NULL,
101
status lifecycle_status
102
DEFAULT 'Live'::lifecycle_status NOT NULL,
103
id uuid NOT NULL PRIMARY KEY,
108
COMMENT ON TABLE share IS 'Shares encompass both access control and \
109
inter-user_id visbility of objects. An object from a StorageUser''s \
110
volume is visible to another StorageUser iff there is a Share entry \
111
for it or for its ancestors. Access granted is in terms of the highest p\
112
ermission granted the StorageUser on the object or any of its \
113
ancestors. Owners always have full access to their own objects. Note \
114
that, given visibility, people can copy objects and do whatever they \
115
like with the copies.'
118
COMMENT ON COLUMN share.id IS 'A unique identifier for the share';
121
COMMENT ON COLUMN share.shared_by IS 'The StorageUser who initiated the \
122
share (can be different from the owner, though the owner must explicitly \
123
approve all shares of subtrees she owns).';
126
COMMENT ON COLUMN Share.subtree IS
127
'The root of the subtree being offered.';
130
COMMENT ON COLUMN Share.shared_to IS
131
'The StorageUser to whom the share is being offered.';
134
COMMENT ON COLUMN share.name IS 'The name the StorageUser sees the share \
135
as in his list of shares, once it has been accepted.';
138
COMMENT ON COLUMN Share.accepted IS
139
'Whether the offered share has been accepted yet or not.';
142
COMMENT ON COLUMN Share.access IS
143
'The access level granted by this share.';
146
COMMENT ON COLUMN Share.when_shared IS
147
'Timestamp when the share was originally offered.';
150
COMMENT ON COLUMN share.when_last_changed IS 'Timestamp when details \
151
of the share were changed (e.g. if it was accepted, or the granted \
152
access was changed).';
155
COMMENT ON COLUMN Share.status IS
156
'Whether this share is active/alive.';
159
COMMENT ON COLUMN share.email IS 'Email address that \
160
this share was offered to.';
163
CREATE INDEX share_subtree_idx ON share USING btree (subtree);
166
CREATE INDEX share_shared_by_fkey ON Share(shared_by)
169
CREATE UNIQUE INDEX share_shared_to_key
170
ON Share(shared_to, name) WHERE status='Live'
173
CREATE UNIQUE INDEX share_shared_to_shared_by_subtree
174
ON Share(shared_to, shared_by, subtree)
175
WHERE status='Live' AND shared_to IS NOT NULL
178
CREATE TABLE PublicFile (
179
id SERIAL PRIMARY KEY,
180
owner_id integer NOT NULL REFERENCES StorageUser (id),
181
node_id uuid NOT NULL UNIQUE,
182
public_uuid UUID UNIQUE);
185
COMMENT ON TABLE PublicFile IS
186
'A mapping of public file IDs to storage objects.';
189
COMMENT ON COLUMN PublicFile.id IS
190
'Unique identifier for this public file.';
193
COMMENT ON COLUMN PublicFile.owner_id IS 'The owner of this file.';
196
COMMENT ON COLUMN PublicFile.node_id IS 'The file to be made public.';
199
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE share TO storage, webapp;
202
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE storageuser TO storage, webapp;
205
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE PublicFile to storage, webapp;
208
GRANT SELECT,USAGE ON TABLE publicfile_id_seq to storage, webapp;
216
"DELETE FROM PublicFile",
217
"DELETE FROM StorageUser",
218
"ALTER SEQUENCE PublicFile_id_seq RESTART WITH 1",