OpenACS

jader
committed
oacs-5-1:jader:20040707173452
on 07 Jul 04
Added a link to a webpage that describes how nested sets works. Otherwise,
it is difficult to figure out how to write queries against catego… Show more
Added a link to a webpage that describes how nested sets works. Otherwise,

it is difficult to figure out how to write queries against categories that

sort correctly. Thanks to Jeroen for this.

Show less

openacs-4/.../www/doc/design.html (+5 -1)
106 106 </pre>
107 107
108 108 <p>
109 109 Here the tree's name and description is stored in different translations.
110 110 <pre>
111 111 create table category_tree_translations (
112 112        tree_id                  integer
113 113                                 constraint cat_tree_trans_tree_id_fk
114 114                                 references category_trees on delete cascade,
115 115        locale                   varchar2(5) not null
116 116                                 constraint cat_tree_trans_locale_fk
117 117                                 references ad_locales,
118 118        name                     varchar2(50) not null,
119 119        description              varchar2(1000),
120 120        primary key (tree_id, locale)
121 121 );
122 122 </pre>
123 123
124 124 <p>
125 125 This table stores the tree hierarchy by holding the information about
126   the parent category. The tree is ordered by a nested index (left_ind, right_ind).
  126 the parent category. The tree is ordered by a nested index (left_ind,
  127 right_ind). Sorting is thus accomplished by means of a nested set. You
  128 can read a <a href="http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=49180">description of how nested sets work</a>. This also <i>describes how to write queries that sort correctly when using categories</i>.
  129
  130
127 131 <pre>
128 132 create table categories (
129 133        category_id                  integer primary key
130 134                                     constraint cat_category_id_fk
131 135                                     references acs_objects on delete cascade,
132 136        tree_id                      integer
133 137                                     constraint cat_tree_id_fk
134 138                                     references category_trees on delete cascade,
135 139        parent_id                    integer
136 140                                     constraint cat_parent_id_fk
137 141                                     references categories,
138 142        deprecated_p                 char(1) default 'f'
139 143                                     constraint cat_deprecated_p_ck
140 144                                     check (deprecated_p in ('t','f')),
141 145        left_ind                     integer,
142 146        right_ind                    integer
143 147 );
144 148 </pre>
145 149
146 150 <p>