47 lines
2.7 KiB
MySQL
47 lines
2.7 KiB
MySQL
--MARS-846
|
|
|
|
SET serveroutput ON
|
|
|
|
|
|
-- ====================================================================
|
|
-- A_PACKAGE_VERSION_TRACKING Table
|
|
-- ====================================================================
|
|
-- Purpose: Track package versions, source code hashes, and deployment history
|
|
-- This table enables automatic detection of package changes even when
|
|
-- version numbers are not updated by developers
|
|
-- ====================================================================
|
|
|
|
CREATE TABLE CT_MRDS.A_PACKAGE_VERSION_TRACKING (
|
|
A_PACKAGE_VERSION_TRACKING_KEY NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
PACKAGE_OWNER VARCHAR2(128) NOT NULL,
|
|
PACKAGE_NAME VARCHAR2(128) NOT NULL,
|
|
PACKAGE_TYPE VARCHAR2(20) NOT NULL CHECK (PACKAGE_TYPE IN ('SPEC', 'BODY', 'BOTH')),
|
|
PACKAGE_VERSION VARCHAR2(10),
|
|
PACKAGE_BUILD_DATE VARCHAR2(20),
|
|
PACKAGE_AUTHOR VARCHAR2(100),
|
|
SOURCE_CODE_HASH_SPEC VARCHAR2(64), -- SHA256 hash of specification source
|
|
SOURCE_CODE_HASH_BODY VARCHAR2(64), -- SHA256 hash of body source
|
|
LINE_COUNT_SPEC NUMBER,
|
|
LINE_COUNT_BODY NUMBER,
|
|
DETECTED_CHANGE_WITHOUT_VERSION CHAR(1) DEFAULT 'N' CHECK (DETECTED_CHANGE_WITHOUT_VERSION IN ('Y', 'N')),
|
|
CHANGE_DETECTION_MESSAGE VARCHAR2(4000),
|
|
TRACKING_DATE TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
|
|
TRACKED_BY_USER VARCHAR2(128) DEFAULT USER NOT NULL,
|
|
TRACKED_BY_MODULE VARCHAR2(64) DEFAULT SYS_CONTEXT('USERENV','MODULE'),
|
|
CONSTRAINT UK_PKG_VERSION_TRACK UNIQUE (PACKAGE_OWNER, PACKAGE_NAME, TRACKING_DATE)
|
|
);
|
|
|
|
-- Index for quick lookups
|
|
CREATE INDEX IDX_PKG_TRACK_NAME ON CT_MRDS.A_PACKAGE_VERSION_TRACKING(PACKAGE_OWNER, PACKAGE_NAME);
|
|
CREATE INDEX IDX_PKG_TRACK_DATE ON CT_MRDS.A_PACKAGE_VERSION_TRACKING(TRACKING_DATE);
|
|
|
|
-- Comments
|
|
COMMENT ON TABLE CT_MRDS.A_PACKAGE_VERSION_TRACKING IS 'Tracks package version history and source code hashes for change detection';
|
|
COMMENT ON COLUMN CT_MRDS.A_PACKAGE_VERSION_TRACKING.SOURCE_CODE_HASH_SPEC IS 'SHA256 hash of package specification source code';
|
|
COMMENT ON COLUMN CT_MRDS.A_PACKAGE_VERSION_TRACKING.SOURCE_CODE_HASH_BODY IS 'SHA256 hash of package body source code';
|
|
COMMENT ON COLUMN CT_MRDS.A_PACKAGE_VERSION_TRACKING.DETECTED_CHANGE_WITHOUT_VERSION IS 'Y if source hash changed but version did not';
|
|
COMMENT ON COLUMN CT_MRDS.A_PACKAGE_VERSION_TRACKING.CHANGE_DETECTION_MESSAGE IS 'Details about detected changes without version update';
|
|
|
|
-- Grant access to other schemas
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON CT_MRDS.A_PACKAGE_VERSION_TRACKING TO MRDS_LOADER;
|