Update templates

[martindsouza]

Need to update the templates to show fully working TAPIs. We may need to also update the the Handlebars lib file to include common, repetitive procedures. (see #7 )

[jeffreykemp]

Here's one I've been working on. It uses a different syntax but hopefully the intent is discernible. What I've found useful is to define some record types and then use them almost everywhere for parameter passing - this leads to less repetition in the generated packages.

Package spec:

CREATE OR REPLACE PACKAGE #TAPI# AS
/*******************************************************************************
 Table API for #table#
 #SYSDATE# - Generated by #USER#
*******************************************************************************/

TYPE rowtype IS RECORD
  (<%COLUMNS INCLUDING ROWID,VIRTUAL>
   #col#... #datatype#~
   #col#... VARCHAR2(20){ROWID}~
  ,<%END>
  );
TYPE arraytype IS TABLE OF rowtype INDEX BY BINARY_INTEGER;

TYPE rvtype IS RECORD
  (<%COLUMNS EXCLUDING AUDIT INCLUDING ROWID>
   #col#... VARCHAR2(4000)~
   #col#... #table#.#col#%TYPE{ID}~
   #col#... #table#.#col#%TYPE{BLOB}~
   #col#... #table#.#col#%TYPE{CLOB}~
   #col#... #table#.#col#%TYPE{XMLTYPE}~
   #col#... VARCHAR2(20){ROWID}~
  ,<%END>
  );
TYPE rvarraytype IS TABLE OF rvtype INDEX BY BINARY_INTEGER;

-- return a rowtype with the given values
FUNCTION rec
  (<%COLUMNS INCLUDING ROWID EXCLUDING GENERATED>
   #col#... IN #table#.#col#%TYPE... := NULL~
   #col#... IN VARCHAR2 := NULL{ROWID}~
  ,<%END>
  ) RETURN rowtype;

-- return an rvtype with the given values
FUNCTION rv
  (<%COLUMNS EXCLUDING AUDIT INCLUDING ROWID>
   #col#... IN VARCHAR2 := NULL~
   #col#... IN #table#.#col#%TYPE... := NULL{ID}~
   #col#... IN #table#.#col#%TYPE... := NULL{LOB}~
  ,<%END>
  ) RETURN rvtype;

-- validate the row (returns an error message if invalid)
FUNCTION val (rv IN rvtype) RETURN VARCHAR2;

-- insert a row
FUNCTION ins (rv IN rvtype) RETURN rowtype;

-- insert multiple rows, array may be sparse; returns no. records inserted
FUNCTION bulk_ins (arr IN rvarraytype) RETURN NUMBER;

-- update a row
FUNCTION upd (rv IN rvtype) RETURN rowtype;

-- update multiple rows, array may be sparse; returns no. records updated
FUNCTION bulk_upd (arr IN rvarraytype) RETURN NUMBER;

-- delete a row
PROCEDURE del (rv IN rvtype);

-- delete multiple rows; array may be sparse; returns no. records deleted
FUNCTION bulk_del (arr IN rvarraytype) RETURN NUMBER;

-- get a row (raise NO_DATA_FOUND if not found)
FUNCTION get (<%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID>
              #col#... IN #table#.#col#%TYPE~
              #col#... IN VARCHAR2{ROWID}~
              #col#... IN #table#.#col#%TYPE := NULL{VERSION_ID}~
             ,<%END>
             ,as_at   IN TIMESTAMP := NULL) RETURN rowtype;

END #TAPI#;

[jeffreykemp]

Package body:

CREATE OR REPLACE PACKAGE BODY #TAPI# AS
/*******************************************************************************
 Table API for #table#
 #SYSDATE# - Generated by #USER#
*******************************************************************************/

PROCEDURE lost_upd (rv IN rvtype) IS
  db_last_updated_by #table#.last_updated_by%TYPE;
  db_last_updated_dt #table#.last_updated_dt%TYPE;
BEGIN

  SELECT x.last_updated_by
        ,x.last_updated_dt
  INTO   db_last_updated_by
        ,db_last_updated_dt
  FROM   #table# x
  WHERE  <%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID>
         x.#col#... = rv.#col#~
         x.#COL#... = rv.#col#{ROWID}~
  AND    <%END>;

  UTIL.raise_lost_update
    (updated_by => db_last_updated_by
    ,updated_dt => db_last_updated_dt);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    UTIL.raise_error('LOST_UPDATE_DEL');
END lost_upd;

/*******************************************************************************
                               PUBLIC INTERFACE
*******************************************************************************/

FUNCTION rec
  (<%COLUMNS INCLUDING ROWID EXCLUDING GENERATED>
   #col#... IN #table#.#col#%TYPE... := NULL~
   #col#... IN VARCHAR2 := NULL{ROWID}~
  ,<%END>
  ) RETURN rowtype IS
  r      rowtype;
BEGIN

  <%COLUMNS INCLUDING ROWID EXCLUDING GENERATED>
  r.#col#... := #col#;~
  <%END>

  RETURN r;
END rec;

-- return an rvtype with the given values
FUNCTION rv
  (<%COLUMNS EXCLUDING AUDIT INCLUDING ROWID>
   #col#... IN VARCHAR2 := NULL~
   #col#... IN #table#.#col#%TYPE... := NULL{ID}~
   #col#... IN #table#.#col#%TYPE... := NULL{LOB}~
   #col#... IN VARCHAR2 := NULL{ROWID}~
  ,<%END>
  ) RETURN rvtype IS
  rv     rvtype;
BEGIN

  <%COLUMNS EXCLUDING AUDIT INCLUDING ROWID>
  rv.#col#... := #col#;~
  <%END>

  RETURN rv;
END rv;

FUNCTION val (rv IN rvtype) RETURN VARCHAR2 IS
  -- Validates the record but without reference to any other rows or tables
  -- (i.e. avoid any queries in here).
  -- Unique and referential integrity should be validated via suitable db
  -- constraints (violations will be raised when the ins/upd/del is attempted).
  -- Complex cross-record validations should usually be performed by a XAPI
  -- prior to the call to the TAPI.
BEGIN

  <%COLUMNS EXCLUDING GENERATED,SURROGATE_KEY,NULLABLE>
  UTIL.val_not_null (val => rv.#col#, column_name => #COL#);~
  <%END>
  <%COLUMNS EXCLUDING GENERATED,SURROGATE_KEY,LOBS>
  UTIL.val_ind (val => rv.#col#, column_name => #COL#);{IND}~
  UTIL.val_yn (val => rv.#col#, column_name => #COL#);{YN}~
  UTIL.val_max_len (val => rv.#col#, len => #MAXLEN#, column_name => #COL#);{VARCHAR2}~
  UTIL.val_numeric (val => rv.#col#, column_name => #COL#);{NUMBER}~
  UTIL.val_date (val => rv.#col#, column_name => #COL#);{DATE}~
  UTIL.val_datetime (val => rv.#col#, column_name => #COL#);{DATETIME}~
  UTIL.val_timestamp (val => rv.#col#, column_name => #COL#);{TIMESTAMP}~
  UTIL.val_timestamp_tz (val => rv.#col#, column_name => #COL#);{TIMESTAMP_TZ}~
  ~
  <%END>

  RETURN UTIL.first_error;
END val;

PROCEDURE bulk_val (arr IN rvarraytype) IS
  i         BINARY_INTEGER;
  error_msg VARCHAR2(32767);
BEGIN

  i := arr.FIRST;
  LOOP
    EXIT WHEN i IS NULL;

    error_msg := val (rv => arr(i));

    -- raise the error on the first record with any error (stop validating
    -- subsequent records)
    IF error_msg IS NOT NULL THEN
      UTIL.raise_error(error_msg || ' (row ' || i || ')');
    END IF;

    i := arr.NEXT(i);
  END LOOP;

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UTIL.raise_dup_val_on_index (scope, params);
END bulk_val;

FUNCTION ins (rv IN rvtype) RETURN rowtype IS
  r         rowtype;
  error_msg VARCHAR2(32767);
BEGIN

  error_msg := val (rv => rv);

  IF error_msg IS NOT NULL THEN
    UTIL.raise_error(error_msg);
  END IF;

  INSERT INTO #table#
        (<%COLUMNS EXCLUDING GENERATED>
        #col#~
        ,<%END>)
  VALUES(<%COLUMNS EXCLUDING GENERATED>
         #seq#.NEXTVAL{SURROGATE_KEY}~
         rv.#col#~
         UTIL.num_val(rv.#col#){NUMBER}~
         UTIL.date_val(rv.#col#){DATE}~
         UTIL.datetime_val(rv.#col#){DATETIME}~
         UTIL.timestamp_val(rv.#col#){TIMESTAMP}~
         UTIL.timestamp_tz_val(rv.#col#){TIMESTAMP_TZ}~
        ,<%END>)
  RETURNING
         <%COLUMNS INCLUDING VIRTUAL,ROWID>
         #col#~
         #COL#{ROWID}~
        ,<%END>
  INTO   <%COLUMNS INCLUDING VIRTUAL,ROWID>
         r.#col#~
        ,<%END>;

  RETURN r;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UTIL.raise_dup_val_on_index (scope, params);
END ins;

FUNCTION bulk_ins (arr IN rvarraytype) RETURN NUMBER IS
  rowcount NUMBER;
BEGIN

  bulk_val(arr);

  FORALL i IN INDICES OF arr
    INSERT INTO #table#
           (<%COLUMNS EXCLUDING GENERATED>
            #col#~
           ,<%END>)
    VALUES (<%COLUMNS EXCLUDING GENERATED>
            #seq#.NEXTVAL{SURROGATE_KEY}~
            arr(i).#col#~
            UTIL.num_val(arr(i).#col#){NUMBER}~
            UTIL.date_val(arr(i).#col#){DATE}~
            UTIL.datetime_val(arr(i).#col#){DATETIME}~
            UTIL.timestamp_val(arr(i).#col#){TIMESTAMP}~
            UTIL.timestamp_tz_val(arr(i).#col#){TIMESTAMP_TZ}~
           ,<%END>);

  rowcount := SQL%ROWCOUNT;

  RETURN rowcount;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UTIL.raise_dup_val_on_index (scope, params);
END bulk_ins;

FUNCTION upd (rv IN rvtype) RETURN rowtype IS
  r         rowtype;
  error_msg VARCHAR2(32767);
BEGIN

  <%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID>
  assert(rv.#col# IS NOT NULL, '#col# cannot be null', scope);~
  <%END>

  error_msg := val (rv => rv);

  IF error_msg IS NOT NULL THEN
    UTIL.raise_error(error_msg);
  END IF;

  UPDATE #table# x
  SET    <%COLUMNS EXCLUDING GENERATED,SURROGATE_KEY>
         x.#col#... = rv.#col#~
         x.#col#... = UTIL.num_val(rv.#col#){NUMBER}~
         x.#col#... = UTIL.date_val(rv.#col#){DATE}~
         x.#col#... = UTIL.datetime_val(rv.#col#){DATETIME}~
         x.#col#... = UTIL.timestamp_val(rv.#col#){TIMESTAMP}~
         x.#col#... = UTIL.timestamp_tz_val(rv.#col#){TIMESTAMP_TZ}}~
        ,<%END>
  WHERE  <%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID>
         x.#col#... = rv.#col#~
         x.#COL#... = rv.#col#{ROWID}~
  AND    <%END>
  RETURNING
         <%COLUMNS INCLUDING VIRTUAL,ROWID>
         #col#~
         #COL#{ROWID}~
        ,<%END>
  INTO   <%COLUMNS INCLUDING VIRTUAL,ROWID>
         r.#col#~
        ,<%END>;

  IF SQL%NOTFOUND THEN
    RAISE UTIL.lost_update;
  END IF;

  RETURN r;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UTIL.raise_dup_val_on_index (scope, params);
  WHEN UTIL.ref_constraint_violation THEN
    UTIL.raise_ref_con_violation (scope, params);
  WHEN UTIL.lost_update THEN
    lost_upd (rv => rv);
END upd;

FUNCTION bulk_upd (arr IN rvarraytype) RETURN NUMBER IS
  rowcount NUMBER;
BEGIN

  bulk_val(arr);

  FORALL i IN INDICES OF arr
    UPDATE #table# x
    SET    <%COLUMNS EXCLUDING GENERATED,SURROGATE_KEY>
           x.#col#... = arr(i).#col#~
           x.#col#... = UTIL.num_val(arr(i).#col#){NUMBER}~
           x.#col#... = UTIL.date_val(arr(i).#col#){DATE}~
           x.#col#... = UTIL.datetime_val(arr(i).#col#){DATETIME}~
           x.#col#... = UTIL.timestamp_val(arr(i).#col#){TIMESTAMP}~
           x.#col#... = UTIL.timestamp_tz_val(arr(i).#col#){TIMESTAMP_TZ}~
          ,<%END>
    WHERE  <%COLUMNS ONLY SURROGATE_KEY INCLUDING ROWID>
           x.#col#... = arr(i).#col#~
           x.#COL#... = arr(i).#col#{ROWID}~
    AND    <%END>;

  rowcount := SQL%ROWCOUNT;

  RETURN rowcount;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UTIL.raise_dup_val_on_index (scope, params);
  WHEN UTIL.ref_constraint_violation THEN
    UTIL.raise_ref_con_violation (scope, params);
END bulk_upd;

PROCEDURE del (rv IN rvtype) IS
BEGIN

  <%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID>
  assert(rv.#col# IS NOT NULL, '#col# cannot be null', scope);~
  <%END>

  DELETE #table# x
  WHERE  <%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID>
         x.#col#... = rv.#col#~
         x.#COL#... = rv.#col#{ROWID}~
  AND    <%END>;

  IF SQL%NOTFOUND THEN
    RAISE UTIL.lost_update;
  END IF;

EXCEPTION
  WHEN UTIL.ref_constraint_violation THEN
    UTIL.raise_del_ref_con_violation (scope, params);
  WHEN UTIL.lost_update THEN
    lost_upd (rv => rv);
END del;

FUNCTION bulk_del (arr IN rvarraytype) RETURN NUMBER IS
  rowcount NUMBER;
BEGIN

  FORALL i IN INDICES OF arr
    DELETE #table# x
    WHERE  <%COLUMNS ONLY SURROGATE_KEY INCLUDING ROWID>
           x.#col#... = arr(i).#col#~
           x.#COL#... = arr(i).#col#{ROWID}
    AND    <%END>;

  rowcount := SQL%ROWCOUNT;

  RETURN rowcount;
EXCEPTION
  WHEN UTIL.ref_constraint_violation THEN
    UTIL.raise_del_ref_con_violation (scope, params);
END bulk_del;

FUNCTION get (<%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID>
              #col#... IN #table#.#col#%TYPE~
              #col#... IN #table#.#col#%TYPE := NULL{VERSION_ID}~
              #col#... IN VARCHAR2{ROWID}~
             ,<%END>
             ,as_at   IN TIMESTAMP := NULL) RETURN rowtype IS
  r       rowtype;
BEGIN

  IF <%COLUMNS ONLY SURROGATE_KEY INCLUDING ROWID>#col# IS NOT NULL~
  OR <%END> THEN

    IF as_at IS NOT NULL THEN

      SELECT <%COLUMNS INCLUDING VIRTUAL>
             x.#col#~
            ,<%END>
      INTO   <%COLUMNS INCLUDING VIRTUAL>
             r.#col#~
            ,<%END>
      FROM   #table# AS OF TIMESTAMP as_at x
      WHERE  <%COLUMNS ONLY SURROGATE_KEY INCLUDING ROWID>
             x.#col#... = get.#col#~
             x.#COL#... = get.#col#{ROWID}~
      AND    <%END>;

    ELSIF version_id IS NOT NULL THEN

      SELECT <%COLUMNS INCLUDING VIRTUAL>
             x.#col#~
            ,<%END>
      INTO   <%COLUMNS INCLUDING VIRTUAL>
             r.#col#~
            ,<%END>
      FROM   #journal# x
      WHERE  <%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID>
             x.#col#... = get.#col#~
             x.#COL#... = get.#col#{ROWID}~
      AND    <%END>;

    ELSE

      SELECT <%COLUMNS INCLUDING VIRTUAL,ROWID>
             x.#col#~
             x.#COL#{ROWID}~
            ,<%END>
      INTO   <%COLUMNS INCLUDING VIRTUAL,ROWID>
             r.#col#~
            ,<%END>
      FROM   #table# x
      WHERE  <%COLUMNS ONLY SURROGATE_KEY INCLUDING ROWID>
             x.#col#... = get.#col#~
             x.#COL#... = get.#col#{ROWID}~
      AND    <%END>;

    END IF;

  ELSE

    -- set up default record
    <%COLUMNS ONLY DEFAULT_VALUE EXCLUDING GENERATED,SURROGATE_KEY>
    r.#col#... := #DATA_DEFAULT#;~
    NULL;{NONE}~
    <%END>

  END IF;

  RETURN r;
END get;

END #TAPI#;

[martindsouza]

Thanks @jeffreykemp For the template that we include right now I'd like to keep it to the basic CRUD functions to help highlight how to use Handlebars in a PL/SQL code.

Where do you think we should put other's template files? I.e. this works for you and others may want to view/use it for their system. Should it be in the templates folder or should we have an area where people can post what they're doing? What should the files be called?

[jeffreykemp]

Hi Martin,

I think a "templates" folder is a good idea.

In addition, a "draft" github branch might be a good way to keep new/proposed templates separate from the main area, and when they have been converted to Handlebars they can be promoted to the master branch.

I don't know what a useful naming standard would look like. We may need to just name them template01, template02, etc. for now and come back later when we have a better idea. Or name them after their creator - kemp01, dsouza01, etc.

[martindsouza]

Ok let's put them in the templates folder but create subfolders for each user based on their username. This way we'd know who was supporting them. So if people had questions / suggestions for the jeffreykemp they could contact @jeffreykemp. I'll update the README.md file accordingly.

Can you convert your template that you include above to use Handlebars and add to the project.