Sqitch migrations, drawn as a database diagram

-- Deploy petclinic:vets

CREATE TABLE vets
(
    id         INT         NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name  VARCHAR(50) NOT NULL,
    CONSTRAINT pk_vets PRIMARY KEY (id)
);

CREATE TABLE specialties
(
    id   INT         NOT NULL,
    name VARCHAR(80) NOT NULL,
    CONSTRAINT pk_specialties PRIMARY KEY (id)
);

CREATE TABLE vet_specialties
(
    vet_id       INT NOT NULL,
    specialty_id INT NOT NULL,
    CONSTRAINT pk_vet_specialties PRIMARY KEY (vet_id, specialty_id),
    CONSTRAINT fk_vet_specialties_vets FOREIGN KEY (vet_id) REFERENCES vets (id),
    CONSTRAINT fk_vet_specialties_specialties FOREIGN KEY (specialty_id) REFERENCES specialties (id)
);

-- Deploy petclinic:visits

CREATE TABLE visits
(
    id          INT NOT NULL,
    pet_id      INT NOT NULL,
    visit_date  DATE,
    description VARCHAR(255),
    CONSTRAINT pk_visits PRIMARY KEY (id),
    CONSTRAINT fk_visits_pets FOREIGN KEY (pet_id) REFERENCES pets (id)
);

-- Revert petclinic:owners_and_pets

DROP TABLE pets;
DROP TABLE types;
DROP TABLE owners;

-- Revert petclinic:vets

DROP TABLE vet_specialties;
DROP TABLE specialties;
DROP TABLE vets;

-- Revert petclinic:visits

DROP TABLE visits;

[core]
	engine = pg
	# plan_file = sqitch.plan
	# top_dir = .
# [engine "pg"]
	# target = db:pg:
	# registry = sqitch
	# client = psql

%syntax-version=1.0.0
%project=petclinic
%uri=https://schemadrawer.com/petclinic

owners_and_pets 2024-01-01T00:00:00Z schemadrawer <[email protected]> # Create owners, types and pets.
vets [owners_and_pets] 2024-01-01T00:01:00Z schemadrawer <[email protected]> # Create vets, specialties and vet_specialties.
visits [vets] 2024-01-01T00:02:00Z schemadrawer <[email protected]> # Create visits.

-- Verify petclinic:owners_and_pets

SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE 0 = 1;
SELECT id, name FROM types WHERE 0 = 1;
SELECT id, name, birth_date, type_id, owner_id FROM pets WHERE 0 = 1;

-- Verify petclinic:vets

SELECT id, first_name, last_name FROM vets WHERE 0 = 1;
SELECT id, name FROM specialties WHERE 0 = 1;
SELECT vet_id, specialty_id FROM vet_specialties WHERE 0 = 1;

-- Verify petclinic:visits

SELECT id, pet_id, visit_date, description FROM visits WHERE 0 = 1;