From Code to Community: Sponsoring The Perl and Raku Conference 2025 Learn more

DWHEELER / App-Sqitch-v1.5.1 / lib / App / Sqitch / Engine / oracle.sql 

            

              
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142

              
CREATE TABLE &registry..releases (
    version           FLOAT                    PRIMARY KEY,
    installed_at      TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
    installer_name    VARCHAR2(512 CHAR)       NOT NULL,
    installer_email   VARCHAR2(512 CHAR)       NOT NULL
);
COMMENT ON TABLE  &registry..releases                 IS 'Sqitch registry releases.';
COMMENT ON COLUMN &registry..releases.version         IS 'Version of the Sqitch registry.';
COMMENT ON COLUMN &registry..releases.installed_at    IS 'Date the registry release was installed.';
COMMENT ON COLUMN &registry..releases.installer_name  IS 'Name of the user who installed the registry release.';
COMMENT ON COLUMN &registry..releases.installer_email IS 'Email address of the user who installed the registry release.';
CREATE TABLE &registry..projects (
    project         VARCHAR2(512 CHAR)       PRIMARY KEY,
    uri             VARCHAR2(512 CHAR)       NULL UNIQUE,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
    creator_name    VARCHAR2(512 CHAR)       NOT NULL,
    creator_email   VARCHAR2(512 CHAR)       NOT NULL
);
COMMENT ON TABLE  &registry..projects                IS 'Sqitch projects deployed to this database.';
COMMENT ON COLUMN &registry..projects.project        IS 'Unique Name of a project.';
COMMENT ON COLUMN &registry..projects.uri            IS 'Optional project URI';
COMMENT ON COLUMN &registry..projects.created_at     IS 'Date the project was added to the database.';
COMMENT ON COLUMN &registry..projects.creator_name   IS 'Name of the user who added the project.';
COMMENT ON COLUMN &registry..projects.creator_email  IS 'Email address of the user who added the project.';
CREATE TABLE &registry..changes (
    change_id       CHAR(40)                 PRIMARY KEY,
    script_hash     CHAR(40)                     NULL,
    change          VARCHAR2(512 CHAR)       NOT NULL,
    project         VARCHAR2(512 CHAR)       NOT NULL REFERENCES &registry..projects(project),
    note            VARCHAR2(4000 CHAR)      DEFAULT '',
    committed_at    TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
    committer_name  VARCHAR2(512 CHAR)       NOT NULL,
    committer_email VARCHAR2(512 CHAR)       NOT NULL,
    planned_at      TIMESTAMP WITH TIME ZONE NOT NULL,
    planner_name    VARCHAR2(512 CHAR)       NOT NULL,
    planner_email   VARCHAR2(512 CHAR)       NOT NULL,
    UNIQUE(project, script_hash)
);
COMMENT ON TABLE  &registry..changes                 IS 'Tracks the changes currently deployed to the database.';
COMMENT ON COLUMN &registry..changes.change_id       IS 'Change primary key.';
COMMENT ON COLUMN &registry..changes.script_hash     IS 'Deploy script SHA-1 hash.';
COMMENT ON COLUMN &registry..changes.change          IS 'Name of a deployed change.';
COMMENT ON COLUMN &registry..changes.project         IS 'Name of the Sqitch project to which the change belongs.';
COMMENT ON COLUMN &registry..changes.note            IS 'Description of the change.';
COMMENT ON COLUMN &registry..changes.committed_at    IS 'Date the change was deployed.';
COMMENT ON COLUMN &registry..changes.committer_name  IS 'Name of the user who deployed the change.';
COMMENT ON COLUMN &registry..changes.committer_email IS 'Email address of the user who deployed the change.';
COMMENT ON COLUMN &registry..changes.planned_at      IS 'Date the change was added to the plan.';
COMMENT ON COLUMN &registry..changes.planner_name    IS 'Name of the user who planed the change.';
COMMENT ON COLUMN &registry..changes.planner_email   IS 'Email address of the user who planned the change.';
CREATE TABLE &registry..tags (
    tag_id          CHAR(40)                 PRIMARY KEY,
    tag             VARCHAR2(512 CHAR)       NOT NULL,
    project         VARCHAR2(512 CHAR)       NOT NULL REFERENCES &registry..projects(project),
    change_id       CHAR(40)                 NOT NULL REFERENCES &registry..changes(change_id),
    note            VARCHAR2(4000 CHAR)      DEFAULT '',
    committed_at    TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
    committer_name  VARCHAR2(512 CHAR)       NOT NULL,
    committer_email VARCHAR2(512 CHAR)       NOT NULL,
    planned_at      TIMESTAMP WITH TIME ZONE NOT NULL,
    planner_name    VARCHAR2(512 CHAR)       NOT NULL,
    planner_email   VARCHAR2(512 CHAR)       NOT NULL,
    UNIQUE(project, tag)
);
COMMENT ON TABLE  &registry..tags                 IS 'Tracks the tags currently applied to the database.';
COMMENT ON COLUMN &registry..tags.tag_id          IS 'Tag primary key.';
COMMENT ON COLUMN &registry..tags.tag             IS 'Project-unique tag name.';
COMMENT ON COLUMN &registry..tags.project         IS 'Name of the Sqitch project to which the tag belongs.';
COMMENT ON COLUMN &registry..tags.change_id       IS 'ID of last change deployed before the tag was applied.';
COMMENT ON COLUMN &registry..tags.note            IS 'Description of the tag.';
COMMENT ON COLUMN &registry..tags.committed_at    IS 'Date the tag was applied to the database.';
COMMENT ON COLUMN &registry..tags.committer_name  IS 'Name of the user who applied the tag.';
COMMENT ON COLUMN &registry..tags.committer_email IS 'Email address of the user who applied the tag.';
COMMENT ON COLUMN &registry..tags.planned_at      IS 'Date the tag was added to the plan.';
COMMENT ON COLUMN &registry..tags.planner_name    IS 'Name of the user who planed the tag.';
COMMENT ON COLUMN &registry..tags.planner_email   IS 'Email address of the user who planned the tag.';
CREATE TABLE &registry..dependencies (
    change_id       CHAR(40)                 NOT NULL REFERENCES &registry..changes(change_id) ON DELETE CASCADE,
    type            VARCHAR2(8)              NOT NULL,
    dependency      VARCHAR2(1024 CHAR)      NOT NULL,
    dependency_id   CHAR(40)                     NULL REFERENCES &registry..changes(change_id),
    CONSTRAINT dependencies_check CHECK (
            (type = 'require'  AND dependency_id IS NOT NULL)
         OR (type = 'conflict' AND dependency_id IS NULL)
    ),
    PRIMARY KEY (change_id, dependency)
);
COMMENT ON TABLE  &registry..dependencies               IS 'Tracks the currently satisfied dependencies.';
COMMENT ON COLUMN &registry..dependencies.change_id     IS 'ID of the depending change.';
COMMENT ON COLUMN &registry..dependencies.type          IS 'Type of dependency.';
COMMENT ON COLUMN &registry..dependencies.dependency    IS 'Dependency name.';
COMMENT ON COLUMN &registry..dependencies.dependency_id IS 'Change ID the dependency resolves to.';
CREATE TYPE &registry..sqitch_array AS varray(1024) OF VARCHAR2(512);
/
CREATE TABLE &registry..events (
    event           VARCHAR2(6)                   NOT NULL
    CONSTRAINT events_event_check CHECK (
        event IN ('deploy', 'revert', 'fail', 'merge')
    ),
    change_id       CHAR(40)                      NOT NULL,
    change          VARCHAR2(512 CHAR)            NOT NULL,
    project         VARCHAR2(512 CHAR)            NOT NULL REFERENCES &registry..projects(project),
    note            VARCHAR2(4000 CHAR)           DEFAULT '',
    requires        &registry..SQITCH_ARRAY       DEFAULT &registry..SQITCH_ARRAY() NOT NULL,
    conflicts       &registry..SQITCH_ARRAY       DEFAULT &registry..SQITCH_ARRAY() NOT NULL,
    tags            &registry..SQITCH_ARRAY       DEFAULT &registry..SQITCH_ARRAY() NOT NULL,
    committed_at    TIMESTAMP WITH TIME ZONE      DEFAULT current_timestamp NOT NULL,
    committer_name  VARCHAR2(512 CHAR)            NOT NULL,
    committer_email VARCHAR2(512 CHAR)            NOT NULL,
    planned_at      TIMESTAMP WITH TIME ZONE      NOT NULL,
    planner_name    VARCHAR2(512 CHAR)            NOT NULL,
    planner_email   VARCHAR2(512 CHAR)            NOT NULL
);
CREATE UNIQUE INDEX &registry..events_pkey ON &registry..events(change_id, committed_at);
COMMENT ON TABLE  &registry..events                 IS 'Contains full history of all deployment events.';
COMMENT ON COLUMN &registry..events.event           IS 'Type of event.';
COMMENT ON COLUMN &registry..events.change_id       IS 'Change ID.';
COMMENT ON COLUMN &registry..events.change          IS 'Change name.';
COMMENT ON COLUMN &registry..events.project         IS 'Name of the Sqitch project to which the change belongs.';
COMMENT ON COLUMN &registry..events.note            IS 'Description of the change.';
COMMENT ON COLUMN &registry..events.requires        IS 'Array of the names of required changes.';
COMMENT ON COLUMN &registry..events.conflicts       IS 'Array of the names of conflicting changes.';
COMMENT ON COLUMN &registry..events.tags            IS 'Tags associated with the change.';
COMMENT ON COLUMN &registry..events.committed_at    IS 'Date the event was committed.';
COMMENT ON COLUMN &registry..events.committer_name  IS 'Name of the user who committed the event.';
COMMENT ON COLUMN &registry..events.committer_email IS 'Email address of the user who committed the event.';
COMMENT ON COLUMN &registry..events.planned_at      IS 'Date the event was added to the plan.';
COMMENT ON COLUMN &registry..events.planner_name    IS 'Name of the user who planed the change.';
COMMENT ON COLUMN &registry..events.planner_email   IS 'Email address of the user who plan planned the change.';