Skip to main content

Include custom scripts

Custom scripts are hand-written SQL files that do something DataStar's data and object components can't express: a one-off DML fix, a schema migration that doesn't model cleanly as a component, a bulk back-fill. Unlike static scripts (which wrap a component with constraint disable / re-enable), custom scripts replace a component for a particular deployment.

When (and when not) to use a custom script​

Prefer a data or object component whenever the change fits. Components give you versioning, conflict detection, and automatic reversal generation. Custom scripts bypass those benefits and put the reversal logic back on you.

Reach for a custom script when:

  • The change is truly a one-off (a DML migration, a deprecated column drop, a data fix).
  • The shape doesn't fit a template (bulk operations across many tables, vendor-specific DDL).
  • You need full control of the SQL rather than a generated MERGE.

Avoid them for data changes a data component could cover; you'll lose versioning and own the rollback yourself.

Rules of the road​

  • Re-runnable. If the script has already been applied, re-running it must be a no-op. Use an idempotency guard (examples below).
  • Tagged as custom. Add the metadata comment at the top so DataStar treats the script as custom and doesn't try to reverse it as a component.
  • Part of a deployment manifest. Custom scripts sit in the deployment manifest directly, not inside a template's <Deployment> block. Add them from the deployment editor like any other item.

The metadata tag​

-- Metadata: {"custom":"true"}
-- ...your SQL here...

The tag tells the reversal engine not to try to produce a reverse MERGE for this file. Instead it processes any --SWITCH comments inside and emits the result as the reversal script.

Reversal via --SWITCH comments​

A --SWITCH marker flips position between the forward and reverse passes:

Marker position in the sourceForward behaviourReversal behaviour
--SWITCH <statement> (marker at the start)Whole line is a comment; statement is skippedMarker moves to the end; statement runs
<statement> --SWITCH (marker at the end)Statement runs; marker is a trailing commentMarker moves to the start; statement is skipped

One file describes both directions. Example:

Forward script
-- Metadata: {"custom":"true"}
--SWITCH INSERT INTO dbo.T1 (col1, col2) VALUES ('reverse', 'value');
INSERT INTO dbo.T1 (col1, col2) VALUES ('forward', 'value'); --SWITCH
Generated reversal
-- Metadata: {"custom":"true"}
INSERT INTO dbo.T1 (col1, col2) VALUES ('reverse', 'value'); --SWITCH
--SWITCH INSERT INTO dbo.T1 (col1, col2) VALUES ('forward', 'value');

Alternative: a reversal-mode flag​

For longer scripts, flipping a single --SWITCH to toggle a variable reads more cleanly than peppering markers through every statement:

-- Metadata: {"custom":"true"}
DECLARE @ReversalMode BIT = 0;
--SWITCH SET @ReversalMode = 1;

IF @ReversalMode = 0
BEGIN
    -- forward work
    PRINT 'applying change';
END
ELSE
BEGIN
    -- reversal work
    PRINT 'reverting change';
END

The --SWITCH on the SET line is the only thing that flips; everything else is plain SQL that reads the flag.

Idempotency guards​

Custom scripts must tolerate being re-run on the same database. Guard DML the same way as DDL: check before inserting, UPDATE with a predicate that makes a second run a no-op.

SQL Server:

IF NOT EXISTS (SELECT 1 FROM sys.columns
               WHERE object_id = OBJECT_ID('ORDERS') AND name = 'LEGACY_FLAG')
BEGIN
    ALTER TABLE ORDERS ADD LEGACY_FLAG BIT NULL;
END

Oracle:

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM user_tab_columns
    WHERE table_name = 'ORDERS' AND column_name = 'LEGACY_FLAG';

    IF v_count = 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE ORDERS ADD (LEGACY_FLAG NUMBER(1))';
    END IF;
END;
/

Oracle DDL and transactions​

Oracle issues an implicit COMMIT before and after every DDL statement. A custom script that mixes DDL with DML will break the transactional atomicity DataStar otherwise gives the deployment: if a later step fails, the DDL (and any data already committed by it) won't roll back.

Isolate DDL into its own deployment, or keep custom-script changes data-only.

See also​

Last updated on