D.3. PDL and SQL Used in the Tutorial

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;
  }
}

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