blob: 7612151bb9ecb37393ef6bce856a11c87be175e5 [file] [log] [blame]
-- Copyright 2020 The Go Authors. All rights reserved.
-- Use of this source code is governed by a BSD-style
-- license that can be found in the LICENSE file.
BEGIN;
CREATE TABLE packages (
path text NOT NULL,
module_path text NOT NULL,
version text NOT NULL,
commit_time timestamp with time zone NOT NULL,
name text NOT NULL,
synopsis text,
license_types text[],
license_paths text[],
v1_path text NOT NULL,
goos text NOT NULL,
goarch text NOT NULL,
redistributable boolean DEFAULT false NOT NULL,
documentation text,
tsv_parent_directories tsvector,
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (path, module_path, version),
FOREIGN KEY (module_path, version) REFERENCES modules(module_path, version) ON DELETE CASCADE
);
COMMENT ON TABLE packages IS
'TABLE packages contains packages in a specific module version.';
COMMENT ON COLUMN packages.commit_time IS
'commit_time is the same as verions.commit_time. It is added here so that we can reduce the number of joins in our queries.';
COMMENT ON COLUMN packages.tsv_parent_directories IS
'tsv_parent_directories should always be NOT NULL, but it is populated by a trigger, so it will be initially NULL on insert.';
CREATE INDEX idx_packages_v1_path ON packages (v1_path);
COMMENT ON INDEX idx_packages_v1_path IS
'INDEX idx_packages_v1_path is used to get all of the packages in a series.';
CREATE INDEX idx_packages_module_path_text_pattern_ops ON packages (module_path text_pattern_ops);
COMMENT ON INDEX idx_packages_module_path_text_pattern_ops IS
'INDEX idx_packages_module_path_text_pattern_ops is used to improve performance of LIKE statements for module_path. It is used to fetch directories matching a given module_path prefix.';
CREATE INDEX idx_packages_path_text_pattern_ops ON packages (path text_pattern_ops);
END;