Another data model you can use in the SQL Repository is an ordered one-to-many relationship. Suppose you have an author item descriptor and you want to model each author’s books in the order they were published. Your SQL repository definition file could define two item descriptors that look something like this:
<item-descriptor name="author"> <table name="author" type="primary" id-column-name="author_id"> </table> <table name="book" type="multi" id-column-name="author_id" multi-column-name="sequence_num"> <property name="books" data-type="list" component-item-type="book" column-name="book_id"/> </table> </item-descriptor> <item-descriptor name="book"> <table name="book" type="primary" id-column-name="book_id"> <property name="author" item-type="author" column-name="author_id"/> <property name="seq" data-type="int" column-name="sequence_num"/> </table> </item-descriptor>
Note some limitations for this data model:
You need to use the
Listdata type to represent the ordered “many” side of the relationship.The
sequence_numandauthor_idcolumns in thebooktable can’t be specified as not null, since the SQL Repository will try to set these fields to null when items in the List are removed.The
bookitem descriptor needs to define a property to point to thesequence_numfield, like this:<property name="seq" data-type="int" column-name="sequence_num"/>
SQL Statements for Example G
CREATE TABLE author ( author_id VARCHAR(32) not null, primary key(author_id) ); CREATE TABLE book ( book_id VARCHAR(32) not null, sequence_num INTEGER, author_id VARCHAR(32) references author(author_id), primary key(book_id) );
