OpenACS

davidam
committed
MAIN:davidam:20070524085953
on 24 May 07
reorder the steps
openacs-4/.../postgresql/bookmarks-create.sql (+2 -2)
141 141         end if;
142 142         
143 143         if p_sep is not null then
144 144                 separator := p_sep;
145 145         end if;
146 146
147 147         for v_rec in select local_title
148 148                 from bm_bookmarks
149 149                 where tree_sortkey in
150 150                         (select tree_ancestor_keys( -- get all parent folders up to level 2
151 151                                         tree_ancestor_key(      -- start with the parent folder key
152 152                                                 tree_key, tree_level(tree_key) - 1), 2 ) )
153 153                 order by tree_sortkey
154 154         LOOP
155 155                 folder_names := folder_names || separator || v_rec.local_title;
156 156         end LOOP;
157 157
158 158         return trim(leading separator from folder_names);
159 159 end;' language 'plpgsql';
160 160
161   create function bm_bookmarks_insert_tr () returns opaque as '
  161 create function bm_bookmarks_insert_tr () returns trigger as '
162 162 declare
163 163         v_parent_sk     varbit default null;
164 164         v_max_value     integer;
165 165 begin
166 166         if new.parent_id is null then
167 167             select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value
168 168               from bm_bookmarks
169 169              where parent_id is null;
170 170         else
171 171             select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value
172 172               from bm_bookmarks
173 173              where parent_id = new.parent_id;
174 174
175 175             select tree_sortkey into v_parent_sk
176 176               from bm_bookmarks
177 177              where bookmark_id = new.parent_id;
178 178         end if;
179 179
180 180
181 181         new.tree_sortkey := tree_next_key(v_parent_sk, v_max_value);
182 182
183 183         return new;
184 184
185 185 end;' language 'plpgsql';
186 186
187 187
188 188 create trigger bm_bookmarks_insert_tr before insert
189 189 on bm_bookmarks for each row
190 190 execute procedure bm_bookmarks_insert_tr ();
191 191
192   create function bm_bookmarks_update_tr () returns opaque as '
  192 create function bm_bookmarks_update_tr () returns trigger as '
193 193 declare
194 194         v_parent_sk     varbit default null;
195 195         v_max_value     integer;
196 196         ctx_id          integer;
197 197         v_rec           record;
198 198         clr_keys_p      boolean default ''t'';
199 199 begin
200 200         if new.bookmark_id = old.bookmark_id and
201 201            ((new.parent_id = old.parent_id) or
202 202             (new.parent_id is null and old.parent_id is null)) then
203 203
204 204            return new;
205 205
206 206         end if;
207 207
208 208         for v_rec in select bookmark_id
209 209                        from bm_bookmarks
210 210                       where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey)
211 211                    order by tree_sortkey
212 212         LOOP