Procedural and security objects

A model target can describe more than tables and views:

• routines: functions and procedures stored in a schema;
• triggers: on a table, calling a routine (PostgreSQL) or carrying an inline body (MySQL, SQL Server, Oracle);
• materialized views (indexed views on SQL Server), with their own indexes;
• partitioning of a table by range, list or hash, with named partitions and verbatim bounds;
• roles at the target level, with login, membership and options;
• grants of privileges on a schema, on all tables / sequences / routines of a schema, or on one object.

All of them are physical: no logical object is derived for them, and bluep pull applies them to the target only.

Text format

target_decl    = "target" , name , ":" , dialect , block_open , { ( role_decl | schema_decl ) , nl } , block_close ;
role_decl      = "role" , name , block_open , { role_item , nl } , block_close ;
role_item      = "login" | "member_of" , name | "option" , name , string ;
schema_item    += routine_decl | matview_decl | grant_decl ;
routine_decl   = ( "function" | "procedure" ) , name , "(" , [ arg , { "," , arg } ] , ")" ,
                 block_open , { routine_item , nl } , block_close ;
arg            = [ "in" | "out" | "inout" ] , [ name ] , phys_type , [ "default" , string ] ;
routine_item   = "returns" , string | "language" , name | "option" , name , string | "comment" , string
               | prop_item | "body" , raw_string ;
matview_decl   = "materialized_view" , name , block_open , { matview_item , nl } , block_close ;
matview_item   = "comment" , string | prop_item | "no" , "data" | "sql" , raw_string
               | "index" , name , [ "unique" ] , "(" , index_col , { "," , index_col } , ")" , [ "using" , name ] , [ "where" , string ] ;
grant_decl     = "grant" , privilege , { "," , privilege } , "on" , grant_object , "to" , name , [ "with" , "grant" , "option" ] ;
privilege      = "select" | "insert" | "update" | "delete" | "truncate" | "references" | "trigger" | "usage"
               | "execute" | "create" | "all" ;
grant_object   = "schema" | "all" , "tables" | "all" , "sequences" | "all" , "routines"
               | "table" , name | "view" , name | "sequence" , name | "routine" , name ;
table_item     += trigger_decl | partition_decl ;
trigger_decl   = "trigger" , name , block_open , { trigger_item , nl } , block_close ;
trigger_item   = "timing" , ( "before" | "after" | "instead_of" ) | "events" , event , { "," , event }
               | "update_columns" , "(" , name_list , ")" | "for" , "each" , ( "row" | "statement" )
               | "when" , string | "function" , name | "comment" , string | "body" , raw_string ;
event          = "insert" | "update" | "delete" | "truncate" ;
partition_decl = "partition" , "by" , ( "range" | "list" | "hash" ) , "(" , index_col , { "," , index_col } , ")" ,
                 block_open , { "partition" , name , string , nl } , block_close ;

An argument is read as [mode] [name] type: when two words (or strings) precede ,, ) or default, the first is the name and the second the type; with one word it is the type. A mode word with no type after it is the error expected argument type.

Canonical order

Privileges are written in the order select insert update delete truncate references trigger usage execute create all; the grantee public is written bare.

Generated SQL by dialect

PostgreSQL (dollar quoting picks $$, then $bluep$, $bluep1$, ... so that the tag never occurs in the body):

CREATE FUNCTION public.set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RETURN NEW;
END;
$$;
CREATE TRIGGER trg_order_touch BEFORE UPDATE ON public."order" FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

MySQL (routines and triggers are written verbatim inside a DELIMITER block):

DELIMITER $$
CREATE FUNCTION customer_count(min_id BIGINT) RETURNS BIGINT DETERMINISTIC
BEGIN
  RETURN (SELECT COUNT(*) FROM customer WHERE id >= min_id);
END$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER trg_customer_touch BEFORE UPDATE ON customer FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW(6);
END$$
DELIMITER ;

SQL Server (each routine and trigger is its own batch between GO lines):

GO
CREATE FUNCTION app.customer_count(@min_id BIGINT) RETURNS bigint AS
BEGIN
  RETURN (SELECT COUNT(*) FROM app.customer WHERE id >= @min_id);
END
GO
CREATE TRIGGER app.trg_customer_touch ON app.customer AFTER UPDATE AS
BEGIN
  UPDATE c SET updated_at = SYSUTCDATETIME() FROM app.customer c INNER JOIN inserted i ON i.id = c.id;
END
GO

Oracle (PL/SQL blocks end with a / line; routine names are quoted to keep their case):

CREATE OR REPLACE FUNCTION "app"."customer_count"(min_id IN NUMBER DEFAULT 0) RETURN NUMBER AS
n NUMBER;
BEGIN
  SELECT COUNT(*) INTO n FROM app.customer WHERE id >= min_id;
  RETURN n;
END;
/
CREATE OR REPLACE TRIGGER trg_customer_touch BEFORE UPDATE OF name ON app.customer FOR EACH ROW WHEN (OLD.name <> NEW.name)
BEGIN
  :NEW.updated_at := SYSTIMESTAMP;
END;
/

SQLite and the cloud warehouse dialects skip every procedural object with a note such as function 'f' skipped: not supported by SQLite.

Compare and migrate

bluep diff reports + function s.f, ~ function s.f: replaced, + trigger s.t.trg, ~ partitioning s.t: range (a) -> none, + role r, ~ role r: altered, + grant s: <grant> and the matching drops. A comment-only change is a comment change. bluep migrate orders the statements so that triggers and grants are dropped first and grants are created last.

A change the dialect cannot express is listed at the top of the script as -- skipped unsupported: <change>.

Import

bluep import ddl reads routines, triggers, materialized views, PARTITION BY / PARTITION OF / ATTACH PARTITION, roles, role memberships, GRANT and REVOKE for PostgreSQL, MySQL (including DELIMITER blocks), SQL Server (GO batches) and Oracle (/ lines).

bluep import db reads the same objects from live catalogs:

A failing optional query yields no rows and the note <client> query '<id>' failed: <first line of stderr>.