1-- $NetBSD$
2
3-- Copyright (c)2010,2011 YAMAMOTO Takashi,
4-- All rights reserved.
5--
6-- Redistribution and use in source and binary forms, with or without
7-- modification, are permitted provided that the following conditions
8-- are met:
9-- 1. Redistributions of source code must retain the above copyright
10--    notice, this list of conditions and the following disclaimer.
11-- 2. Redistributions in binary form must reproduce the above copyright
12--    notice, this list of conditions and the following disclaimer in the
13--    documentation and/or other materials provided with the distribution.
14--
15-- THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
16-- ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
17-- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
18-- ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
19-- FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
20-- DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
21-- OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
22-- HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
23-- LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
24-- OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
25-- SUCH DAMAGE.
26
27-- create a new pgfs filesystem.
28-- usage: psql -f newfs.sql [dbname]
29
30-- remove pgfs schema if already exists.
31-- XXX this leaves large objects
32BEGIN;
33DROP SCHEMA pgfs CASCADE;
34COMMIT;
35
36BEGIN;
37-- we put everything in the pgfs schema.  (except large objects)
38CREATE SCHEMA pgfs;
39SET search_path TO pgfs;
40
41-- define various types
42CREATE DOMAIN fileid AS int8 NOT NULL CHECK(VALUE > 0);
43CREATE DOMAIN uid AS int8 NOT NULL CHECK(VALUE >= 0);
44CREATE DOMAIN gid AS int8 NOT NULL CHECK(VALUE >= 0);
45CREATE DOMAIN mode AS int8 NOT NULL CHECK(VALUE >= 0 AND VALUE <= 7*8*8+7*8+7);
46CREATE DOMAIN nlink AS int8 NOT NULL CHECK(VALUE >= 0);
47CREATE SEQUENCE fileid_seq START WITH 1;
48CREATE SEQUENCE dircookie_seq START WITH 3;
49CREATE TYPE filetype AS ENUM (
50	'regular',
51	'directory',
52	'link');
53
54-- a row in the file table describes a file.
55-- having nlink here is somehow redundant as it could be calculated from
56-- filetype and the dirent table.  however, users expect that getattr is
57-- executed in a nearly constant time.
58CREATE TABLE file (
59	fileid fileid PRIMARY KEY,
60	type filetype NOT NULL,
61	mode mode,
62	uid uid,
63	gid gid,
64	nlink nlink,
65	rev int8 NOT NULL,
66	atime timestamptz NOT NULL,
67	ctime timestamptz NOT NULL,
68	mtime timestamptz NOT NULL,
69	btime timestamptz NOT NULL);
70
71-- datafork table maintains the association between our files and its backing
72-- large objects.
73CREATE TABLE datafork (
74	fileid fileid PRIMARY KEY REFERENCES file,
75	loid Oid NOT NULL UNIQUE);
76-- we want the following but lo lives in system catalogs.
77--	loid REFERENCES pg_largeobject_metadata(oid);
78
79-- a row in the dirent table describes a directory entry.
80-- the ".." and "." entries are handled differently and never appear here.
81CREATE TABLE dirent (
82	parent_fileid fileid NOT NULL REFERENCES file,
83	name text NOT NULL,
84	cookie int8 NOT NULL UNIQUE DEFAULT nextval('dircookie_seq'),
85	child_fileid fileid NOT NULL REFERENCES file,
86	CONSTRAINT dirent_pkey PRIMARY KEY(parent_fileid, name),
87	CONSTRAINT dirent_notdot CHECK(name <> '.'),
88	CONSTRAINT dirent_notdotdot CHECK(name <> '..'),
89	CONSTRAINT dirent_noself CHECK(parent_fileid <> child_fileid));
90CREATE INDEX dirent_child ON dirent (child_fileid);
91
92-- create the root directory.
93INSERT INTO file (fileid, type, mode, uid, gid, nlink, rev,
94		atime, ctime, mtime, btime)
95	VALUES (nextval('fileid_seq'), 'directory', 7*8*8 + 5*8 + 5, 0, 0, 1, 0,
96		current_timestamp,
97		current_timestamp,
98		current_timestamp,
99		current_timestamp);
100
101-- create a dummy sequence.  see pgfs_node_fsync().
102CREATE SEQUENCE dummyseq;
103
104RESET search_path;
105COMMIT;
106