156 lines
3.9 KiB
Markdown
156 lines
3.9 KiB
Markdown
# ADR 2025-03-23: Database Design
|
|
|
|
## Table of Contents
|
|
|
|
- [Context](#context)
|
|
- [Decision](#decision)
|
|
- [Table: Registered Projects](#table-registered-project)
|
|
- [Table: Scala Version](#table-scala-version)
|
|
- [Table: SBT Version](#table-sbt-version)
|
|
- [Table: Project Dependency](#table-project-dependency)
|
|
- [Table: Version Parser](#table-version-parser)
|
|
- [Table: Project Dependency Version Parser](#table-project-dependency-version-parser)
|
|
- [Table: Dependency Version](#table-dependency-version)
|
|
- [Out of Scope](#out-of-scope)
|
|
|
|
## Context
|
|
|
|
The `gs-maintainer` project is intended to manage dependency version updates,
|
|
Scala language version updates, and SBT version updates for Scala projects. It
|
|
is also intended to help provide tracking information over time along with
|
|
dashboards to help inform and expedite development.
|
|
|
|
## Decision
|
|
|
|
Establish a small core database that can be expanded upon over time. This
|
|
database must support:
|
|
|
|
- The concept of _registering_ a project for maintenance.
|
|
- The concept of _tracking critical tool versions_ over time.
|
|
- The concept of _tracking a dependency_ over time.
|
|
|
|
This will be accomplished using 7 tables:
|
|
|
|
- [Table: Registered Projects](#table-registered-project)
|
|
- [Table: Scala Version](#table-scala-version)
|
|
- [Table: SBT Version](#table-sbt-version)
|
|
- [Table: Project Dependency](#table-project-dependency)
|
|
- [Table: Version Parser](#table-version-parser)
|
|
- [Table: Project Dependency Version Parser](#table-project-dependency-version-parser)
|
|
- [Table: Dependency Version](#table-dependency-version)
|
|
|
|
### Table: Registered Project
|
|
|
|
```sql
|
|
CREATE TABLE registered_project(
|
|
project_id UUID NOT NULL PRIMARY KEY,
|
|
group TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
registered_at TIMESTAMPTZ NOT NULL,
|
|
git_url TIMESTAMPTZ NOT NULL
|
|
);
|
|
```
|
|
|
|
```sql
|
|
CREATE UNIQUE INDEX idx_registered_project_group_name
|
|
ON registered_project (group, name);
|
|
```
|
|
|
|
### Table: Scala Version
|
|
|
|
```sql
|
|
CREATE TABLE scala_version(
|
|
version TEXT NOT NULL PRIMARY KEY,
|
|
major INT NOT NULL,
|
|
minor INT NOT NULL,
|
|
patch INT NOT NULL,
|
|
added_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
```
|
|
|
|
```sql
|
|
CREATE UNIQUE INDEX idx_scala_version_major_minor_patch
|
|
ON scala_version (major, minor, patch);
|
|
```
|
|
|
|
### Table: SBT Version
|
|
|
|
```sql
|
|
CREATE TABLE sbt_version(
|
|
version TEXT NOT NULL PRIMARY KEY,
|
|
major INT NOT NULL,
|
|
minor INT NOT NULL,
|
|
patch INT NOT NULL,
|
|
added_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
```
|
|
|
|
```sql
|
|
CREATE UNIQUE INDEX idx_sbt_version_major_minor_patch
|
|
ON sbt_version (major, minor, patch);
|
|
```
|
|
|
|
### Table: Project Dependency
|
|
|
|
```sql
|
|
CREATE TABLE project_dependency(
|
|
dependency_id UUID NOT NULL PRIMARY KEY,
|
|
group TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
added_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
```
|
|
|
|
```sql
|
|
CREATE UNIQUE INDEX idx_project_dependency_group_name
|
|
ON project_dependency (group, name);
|
|
```
|
|
|
|
### Table: Version Parser
|
|
|
|
```sql
|
|
CREATE TABLE version_parser(
|
|
parser_id UUID NOT NULL PRIMARY KEY,
|
|
parser_name TEXT NOT NULL UNIQUE,
|
|
implementation TEXT NOT NULL,
|
|
added_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
```
|
|
|
|
### Table: Project Dependency Version Parser
|
|
|
|
```sql
|
|
CREATE TABLE project_dependency_version_parser(
|
|
dependency_id UUID NOT NULL REFERENCES project_dependency,
|
|
parser_id UUID NOT NULL REFERENCES version_parser,
|
|
PRIMARY KEY (dependency_id, parser_id)
|
|
);
|
|
```
|
|
|
|
### Table: Dependency Version
|
|
|
|
```sql
|
|
CREATE TABLE dependency_version(
|
|
dependency_id UUID NOT NULL REFERENCES project_dependency,
|
|
version TEXT NOT NULL,
|
|
first INT NULL,
|
|
second INT NULL,
|
|
third INT NULL,
|
|
fourth INT NULL,
|
|
added_at TIMESTAMPTZ NOT NULL,
|
|
PRIMARY KEY (dependency_id, version)
|
|
);
|
|
```
|
|
|
|
```sql
|
|
CREATE INDEX idx_dependency_version_order
|
|
ON dependency_version (first, second, third, fourth, added_at)
|
|
NULLS LAST;
|
|
```
|
|
|
|
## Out of Scope
|
|
|
|
The following items are considered out of scope:
|
|
|
|
- Project level upgrade policies.
|
|
- Per-dependency upgrade policies.
|