D.3. PDL and SQL Used in the Tutorial

This section provides two examples, one each of SQL and PDL usage. These can be used as quick references to the context of every SQL and PDL example throughout the WAF Developer Guide. The PDL usage is first in Example D-1, with the SQL usage second in the Example D-2.

model tutorial;

object type Publication {
    BigDecimal id = publications.publication_id INTEGER;
    String name = publications.name VARCHAR(400);

    object key (id);
}


object type Magazine extends Publication {
    // we need to specify the size of the String attribute so we know
    // whether it is actually a String or if it is really a Clob
    String issueNumber = magazines.issue_number VARCHAR(30);

    // notice that because it extends Publication, there is not an
    // explicitly "object key" declaration.  Rather, there is
    // a "reference key" declaration and "id" is not defined
    // as one of the attributes
    reference key (magazines.magazine_id);
}

object type Article {
    BigDecimal id = articles.title INTEGER;
    String title = articles.title VARCHAR(30);

    object key (articles.article_id);
}

// this is an "association block" associating "articles" and "magazines"
association {
   // note that the Attribute Type is an Object Type (Article)
   // and not a standard Java Type.  Also notice the order of the
   // join path and see the note below.
   Article[0..n] articles = join magazines.magazine_id
                              to magazine_article_map.magazine_id,
                            join magazine_article_map.article_id
                              to articles.article_id;
   Magazine[0..n] magazines = join articles.article_id
                                to magazine_article_map.article_id,
                              join magazine_article_map.magazine_id
                                to magazines.magazine_id;
   // the next line is the Link Attribute
   BigDecimal pageNumber = magazine_article_map.page_number INTEGER;
}


object type Paragraph {
    BigDecimal id = paragraphs.paragraph_id INTEGER;
    String text = paragraphs.text CLOB;

    object key (paragraphs.paragraph_id);
}

association {
   Article[1..1] articles = join paragraphs.article_id
                              to articles.article_id;
   // notice the composite keyword indicates that if the article does
   // not exist then the paragraph also does not exist
   composite Paragraph[0..n] paragraphs = join articles.article_id
                                            to paragraphs.article_id;
}


object type screenName {
   BigDecimal id = screen_names.name_id INTEGER;
   String screenName = screen_names.screen_name VARCHAR(700);
   Blob screenIcon = screen_names.screen_icon BLOB;

   object key (id);
}

object type Author {
    BigInteger[1..1] id = authors.author_id INTEGER;
    String[1..1] firstName = author.first_name VARCHAR(700);
    String[1..1] lastName = author.last_name VARCHAR(700);
    Blob[0..1] portrait = authors.portrait BLOB;
    // notice the use of a join path to allow the events for the
    // Role Reference to be automatically created.
    ScreenName[0..1] screenName =
         join authors.screen_name_id to screen_names.name_id;

   object key (id);
}


query paragraphMagazines {
   BigDecimal magazineID;
   BigDecimal paragraphID;
   Integer issueNumber;
   String text;
   do {
       select m.magazine_id, p.paragraph_id, issue_number, text
       from magazines m, a, magazine_article_map ma, paragraphs p
       where ma.magazine_id = m.magazine_id
       and p.article_id = ma.article_id
   } map {
       magazineID = m.magazine_id;
       paragraphID = p.paragraph_id;
       issueNumber = m.issue_number;
       text = p.text;
   }
}



query MagazineToAuthorMapping {
    // the next two lines are declaring that objects will be returned
    Magazine magazine;
    Author author;

    options {
        WRAP_QUERIES = false;
    }

    do {
        select publications.name, issue_number, publication_id,
               authors.first_name, authors.last_name, author_id
          from magazines, publications, articles, authors,
               magazine_article_map, article_author_map
         where publications.publication_id =
               magazines.magazine_id
           and magazine_article_map.magazine_id =
               magazines.magazine_id
           and magazine_article_map.article_id =
               article_author_map.article_id
           and article_author_map.author_id = authors.author_id
    } map {
        // here we map the attributes of the objects
        // to columns returned by the query.
        magazine.name = publications.name;
        magazine.issueNumber = magazines.issue_number;
        magazine.id = publications.publication_id;
        author.authorID = authors.author_id;
        author.firstName = authors.first_name;
        author.lastName = authors.last_name;
    }
}

query MagazineWithMaxID {
    BigDecimal magazineID;
    do {
       select max(magazine_id) as magazine_id from magazines
    } map {
       magazineID = magazines.magazine_id;
    }
}

data operation createMagazine {
   do {
      insert into magazine_article_map (magazine_id, article_id)
      select :magazineID, article_id from articles where not exists
      (select 1 from magazine_article_map
      where magazine_article_map.article_id = articles.article_id)
   }
}

data operation DataOperationWithPLSQLAndArgs {
    // the "call" keyword after the "do" indicates that the following
    // is actually a piece of PL/SQL.  The system then uses a
    // java.sql.CallableStatement to execute it instead of only a
    // java.sql.PreparedStatement.
    do call {
          myPLSQLProc(:title)
    }
}

data operation DataOperationProcWithInOut {
    do call {
        DataOperationProcWithInOut(:newID, :copiedID)
    } map {
        newID : INTEGER;
        copiedID : INTEGER;
    }
}

query DataOperationWithPLSQLAndArgsAndReturnInPDL {
    do call {
       :title = DataQueryPLSQLFunction(:articleID)
    } map {
        title : VARCHAR(700);
        articleID : Integer;
    }
}

query myDataQuery {
    BigDecimal articleID;
    options {
        WRAP_QUERIES = false;
    }
    do {
       select max(article_id) from articles
    } map {
       articleID = articles.article_id;
    }
}

query UsersGroups {
      String firstName;
      String lastName;
      String groupName;
      do{
        select *
           from users, groups, membership
          where users.user_id = membership.member_id
            and membership.group_id = groups.group_id
      } map {
        firstName=users.first_name;
        lastName=users.last_name;
        groupName=groups.group_name;
      }
}


query retrieveArticlesBasedOnAuthor {
      BigDecimal authorID;
      do {
          select article_id
            from authors, author_article_map
           where authors.author_id = author_article_map.author_id
             and lower(last_name) like :lastName || '%'
      } map {
          authorID = authors.author_id;
      }
}

query retrieveSelectedArticles {
      BigDecimal articleID;
      String title;
      do {
          select article_id, title from articles
      } map {
          articleID = articles.article_id;
          title = articles.title;
      }
}

query CategoryFamily {
  Integer level;
  BigDecimal categoryID;
  String name;
  String description;
  Boolean isEnabled;
  do {
     select l, c.category_id, c.name, c.description, c.enabled_p
        from (select level l, related_category_id
                 from (select related_category_id, category_id
                          from cat_category_category_map
                          where relation_type = :relationType)
                 connect by prior related_category_id = category_id
                 start with category_id = :categoryID) m,
            cat_categories c
        where c.category_id = m.related_category_id
  } map {
     level = m.l;
     categoryID = c.category_id;
     name = c.name;
     description = c.description;
     isEnabled = c.enabled_p;
  }
}

Example D-1. Usage Reference for PDL

create table publications (
    publication_id     integer
                       constraint publications_pub_id_nn
                       not null
                       constraint publications_pub_id_pk
                       primary key,
    name               varchar(400)
                       constraint publications_pub_id_nn
                       not null
);


create table magazines (
    magazine_id       integer
                      constraint magazines_magazine_id_fk
                      references publications
                      constraint magazines_magazine_id_pk
                      primary key,
    issue_number      varchar(30)
);

create table articles (
    article_id        integer
                      constraint articles_article_id_pk
                      primary key
                      constraint articles_article_id_nn
                      not null
    title             varchar(700)
                      constraint articles_title
);

create table magazine_article_map (
    magazine_id       integer
                      constraint mag_article_map_mag_id_nn
                      not null
                      constraint mag_article_map_mag_id_fk
                      references magazines,
    article_id        integer
                      constraint mag_article_map_article_id_fk
                      references articles
                      constraint mag_article_map_article_id_nn
                      not null,
    page_number       integer
);

create table paragraphs (
    paragraph_id      integer
                      constraint paragraphs_paragraph_id_pk
                      primary key
                      constraint paragraphs_paragraph_id_nn
                      not null,
    text              clob,
    article_id        integer
                      constraint paragraphs_article_id_fk
                      references articles
                      constraint paragraphs_article_id_nn
                      not null
);

create table authors (
    author_id         integer
                      constraint authors_author_id_nn
                      not null
                      constraint authors_author_id_pk
                      primary key,
    last_name              varchar(700)
                      constraint authors_name_nn
                      not null,
    first_name              varchar(700)
                      constraint authors_name_nn
                      not null,
    portrait          blob,
    screen_name_id    integer references screen_names
);

create table screen_names (
    name_id           integer primary key,
    screen_name       varchar(700) not null,
    screen_icon       blob
);

create or replace function myPLSQLProc(v_priority in integer)
as
begin
   insert into magazines (magazine_id, title)
   select nvl(max(magazine_id), 0) + 1, :title from magazine_id;
end;
/
show errors

create or replace procedure DataOperationProcWithInOut(
       v_new_id IN Integer,
       v_copied_id OUT Integer)
as
begin
   select max(article_id) into v_copied_id from articles;
   insert into articles (article_id, title)
       select v_new_id, title from articles
       where article_id = v_copied_id;
   insert into article_author_map (article_id, author_id)
       select v_new_id, author_id from article_author_map
       where article_id = v_copied_id;
end;
/
show errors

create or replace function
   DataQueryPLSQLFunction(v_article_id in integer)
return number
is
   v_title varchar(700);
begin
   select title into v_title from articles
    where article_id = v_article_id;
   return v_title;
end;
/
show errors

Example D-2. Usage Reference for SQL