Persistence Reference

file ::=
    model <SEMI> (imp <SEMI>)* 
       (object|association|query)* <EOF>

model ::=
    <MODEL> <ID> (<DOT> <ID>)*

imp ::=
    <IMPORT> <ID> (<DOT> <ID>)* 
       (<DOT> <STAR>)?

identifier ::=
    <ID> (<DOT> <ID>)*

object ::=
    <OBJECTTYPE> <ID> [<EXTENDS> identifier]
    <LBRACE>
        (property <SEMI>)*
        [objectKey <SEMI>]
        (uniqueKey <SEMI>)*
      [optionBlock]
        [<REFERENCEKEY> <LPAREN> column <RPAREN> <SEMI>]
        [<AGGRESSIVE> <LPAREN> path (<COMMA> path)* 
         <RPAREN> <SEMI>]
        (joinPath <SEMI>)*
        [<FLEXFIELDS> <LBRACE> ( flexFields )+ <RBRACE>> ]
        (event)*
    <RBRACE>

flexFields ::=
    column <SEMI>

dbType ::=
    <ID> (<LPAREN> <INT> <RPAREN>)?

uniqueKey ::=
    <UNIQUE> <LPAREN> <ID> 
       (<COMMA> <ID>)* <RPAREN>

objectKey ::=
    <OBJECTKEY> <LPAREN> <ID> 
       (<COMMA> <ID>)* <RPAREN>

association ::=
    <ASSOCIATION> <LBRACE> 
      property <SEMI> 
      property <SEMI> 
      ( property <SEMI> )*
      [optionBlock]
      ( event )*
<RBRACE>

property ::=
    [<UNIQUE>] [<COMPOSITE> | <COMPONENT>] 
    identifier [multiplicity] <ID> [<EQ> ( column | joinPath )]

joinPath ::= 
    <JOIN> column <TO> column
    (<COMMA> <JOIN> column <TO> column)*

multiplicity ::=
    <LBRACKET> <INT> <DOT> <DOT> 
       (<ID> | <INT>) <RBRACKET>


query ::=
    (<QUERY> | <DML>) <ID> [ <RETURNS> 
    <INT> <DOT> <DOT>
      ( <ID> | <INT> )] <LBRACE> 
      (property <SEMI>)* [optionBlock] block <RBRACE>

foreignKey ::=
    <FOREIGNKEY> <LPAREN> column <RPAREN>

column ::= 
    <ID> <DOT> <ID> [dbType]

event ::=
    ( <RETRIEVE> [<ALL> | <ATTRIBUTES> | <ID>]) 
    | (<INSERT>
       | <UPDATE>
       | <DELETE>)
    | ((<ADD>
        | <REMOVE>
        | <CLEAR> ) <ID>)
    <LBRACE> (block | <SUPER> <SEMI>)* <RBRACE>


block ::=
<DO> [<CALL>] <SQL>
[ <MAP> <LBRACE> ( mapStatement <SEMI> )+ <RBRACE> ]

mapStatement ::=
    (binding | mapping)

binding ::=
    path <COLON> dbType

path ::=
    <ID> ( <DOT> <ID> )*

mapping ::=
    path <EQ> <ID> [<DOT> <ID>]

optionBlock ::=
    <OPTIONS> <LBRACE> ( option <SEMI> )+ <RBRACE>

option ::=
    <ID> <EQ> optionValue

optionValue ::=
    <TRUE> | <FALSE>

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