101 lines
2.4 KiB
SQL
101 lines
2.4 KiB
SQL
DROP DATABASE IF EXISTS cheese_shop;
|
|
CREATE DATABASE cheese_shop;
|
|
USE cheese_shop;
|
|
|
|
CREATE TABLE contacts (
|
|
email varchar(255) PRIMARY KEY,
|
|
name varchar(255));
|
|
|
|
CREATE TABLE releases (
|
|
id smallint AUTO_INCREMENT PRIMARY KEY,
|
|
project varchar(32),
|
|
version varchar(32),
|
|
summary varchar(255),
|
|
homepage varchar(2083),
|
|
email varchar(255),
|
|
CONSTRAINT integrity UNIQUE (project, version),
|
|
FOREIGN KEY (email) REFERENCES contacts(email));
|
|
|
|
CREATE TABLE troves (
|
|
id smallint AUTO_INCREMENT PRIMARY KEY,
|
|
classifier varchar(255) UNIQUE);
|
|
|
|
CREATE TABLE classifiers (
|
|
release_id smallint,
|
|
trove_id smallint,
|
|
PRIMARY KEY (release_id, trove_id),
|
|
FOREIGN KEY (release_id) REFERENCES releases(id),
|
|
FOREIGN KEY (trove_id) REFERENCES troves(id));
|
|
|
|
CREATE TABLE keywords (
|
|
release_id smallint,
|
|
term varchar(32),
|
|
PRIMARY KEY (release_id, term),
|
|
FOREIGN KEY (release_id) REFERENCES releases(id));
|
|
|
|
CREATE TABLE dependencies (
|
|
release_id smallint,
|
|
dependency varchar(64),
|
|
PRIMARY KEY (release_id, dependency),
|
|
FOREIGN KEY (release_id) REFERENCES releases(id));
|
|
|
|
CREATE TABLE distributions (
|
|
release_id smallint,
|
|
filename varchar(255),
|
|
size int,
|
|
url varchar(255),
|
|
dist_type varchar(16),
|
|
python_version varchar(8),
|
|
requires_python varchar(32),
|
|
sha256 char(64),
|
|
md5 char(32),
|
|
PRIMARY KEY (release_id, filename),
|
|
FOREIGN KEY (release_id) REFERENCES releases(id));
|
|
|
|
CREATE INDEX contacts_name_idx
|
|
ON contacts (name);
|
|
|
|
CREATE INDEX releases_summary_idx
|
|
ON releases (summary);
|
|
|
|
CREATE VIEW authorships
|
|
AS SELECT name as author, project
|
|
FROM contacts NATURAL JOIN releases
|
|
GROUP BY author, project;
|
|
|
|
DELIMITER //
|
|
CREATE PROCEDURE browse(class varchar(255))
|
|
BEGIN
|
|
SELECT project, version
|
|
FROM releases, classifiers
|
|
WHERE id = release_id AND trove_id = (
|
|
SELECT id
|
|
FROM troves
|
|
WHERE classifier = class);
|
|
END//
|
|
|
|
CREATE PROCEDURE release_data(project varchar(32), version varchar(32))
|
|
BEGIN
|
|
DECLARE i smallint;
|
|
SET i = (
|
|
SELECT id
|
|
FROM releases
|
|
WHERE releases.project = project AND releases.version = version);
|
|
SELECT project, version, homepage, name as author, email, summary
|
|
FROM releases NATURAL JOIN contacts
|
|
WHERE id = i;
|
|
|
|
SELECT term as keyword
|
|
FROM keywords
|
|
WHERE release_id = i;
|
|
|
|
SELECT classifier
|
|
FROM classifiers, troves
|
|
WHERE release_id = i AND trove_id = troves.id;
|
|
|
|
SELECT dependency
|
|
FROM dependencies
|
|
WHERE release_id = i;
|
|
END//
|
|
DELIMITER ;
|