1 PACKAGE BODY IES_META_DATA_UPDATER AS
2 /* $Header: iesmdupb.pls 115.3 2003/06/06 20:16:26 prkotha noship $ */
3
4
5
6 -- **********************************************************************
7 -- API name : getPropertiesElement
8 -- Type : Private
9 -- Function : This Function returns the xml element starting after
10 -- <Properties.. tag
11 -- **********************************************************************
12
13 FUNCTION getPropertiesElement(element IN xmldom.DOMElement) RETURN xmldom.DOMElement IS
14 nl xmldom.DOMNodeList;
15 len NUMBER;
16 n xmldom.DOMNode;
17 e xmldom.DOMElement;
18 retElement xmldom.DOMElement;
19 BEGIN
20 n := xmldom.getFirstChild(xmldom.makeNode(element));
21 e := xmldom.makeElement(n);
22 if (xmldom.getTagName(e) <> 'Properties') then
23 retElement := getPropertiesElement(e);
24 else
25 retElement := e;
26 end if;
27
28 return retElement;
29 END getPropertiesElement;
30
31 -- **********************************************************************
32 -- API name : childExists
33 -- Type : Private
34 -- Function : This Function returns true if an object (child) exists
35 -- in the XML tree for a given object_uid and type
36 -- **********************************************************************
37
38 FUNCTION childExists(element IN xmldom.DOMElement, object_uid IN VARCHAR2, typeName IN VARCHAR2) RETURN BOOLEAN IS
39 nl xmldom.DOMNodeList;
40 len number;
41 n xmldom.DOMNode;
42 e xmldom.DOMElement;
43 result boolean := false;
44 relUID VARCHAR2(256);
45 retTypeName VARCHAR2(256);
46 BEGIN
47 nl := xmldom.getChildNodes(xmldom.makeNode(element));
48 len := xmldom.getLength(nl);
49
50 for i in 0..len-1 loop
51 n := xmldom.item(nl, i);
52 e := xmldom.makeElement(n);
53
54 if (xmldom.getTagName(e) = 'Properties') then
55 result := childExists(e, object_uid, typeName);
56 end if;
57
58 retTypeName := xmldom.getAttribute(e, 'NAME');
59
60 if (xmldom.getTagName(e) = 'CCTPropertyList' OR xmldom.getTagName(e) = 'CCTPropertyMapList')
61 AND (typeName = retTypeName) then
62 e := getPropertiesElement(e);
63 relUID := IES_META_DATA_UTIL.getProperty(e, 'UID');
64
65 if (relUID IS NOT NULL AND relUID = object_uid) then
66 result := true;
67 end if;
68 elsif (xmldom.getTagName(e) = 'ChildObject') AND (typeName = retTypeName) then
69 relUID := IES_META_DATA_UTIL.getProperty(e, 'UID');
70
71 if (relUID IS NOT NULL AND relUID = object_uid) then
72 result := true;
73 end if;
74 end if;
75 end loop;
76
77 return result;
78 END childExists;
79
80
81 -- **********************************************************************
82 -- API name : deleteOldRelationships
83 -- Type : private
84 -- Function : This procedure deletes all obsolete relationship records.
85 -- childExists function first checks if the record exists,
86 -- if false, it deletes the record.
87 -- **********************************************************************
88
89 PROCEDURE deleteOldRelationships(element IN xmldom.DOMElement, objUID IN VARCHAR2) IS
90 CURSOR getRelationships IS
91 SELECT b.objrel_id, b.secondary_obj_id, a.object_uid, c.type_name
92 FROM ies_meta_objects a,
93 ies_meta_obj_relationships b,
94 ies_meta_objects p,
95 ies_meta_relationship_types c
96 WHERE p.object_uid = objUID
97 AND a.object_id = b.secondary_obj_id
98 AND p.object_id = b.primary_obj_id
99 AND c.type_id = b.type_id
100 AND c.type_name <> 'self';
101
102 sqlStmt VARCHAR2(256);
103 BEGIN
104 for i in getRelationships loop
105 if NOT childExists(element, i.object_uid, i.type_name) then
106 sqlStmt := 'DELETE FROM ies_meta_obj_relationships
107 WHERE objrel_id = :objectrel_id';
108 EXECUTE IMMEDIATE sqlStmt USING i.objrel_id;
109 sqlStmt := 'DELETE FROM ies_meta_obj_relationships
110 WHERE primary_obj_id = :objId
111 AND secondary_obj_id = :secObjId';
112 EXECUTE IMMEDIATE sqlStmt USING i.secondary_obj_id, i.secondary_obj_id;
113 end if;
114 end loop;
115 END deleteOldRelationships;
116
117 -- **********************************************************************
118 -- API name : updatePropertyValues
119 -- Type : private
120 -- Function : This procedure updates the meta object prop values
121 -- **********************************************************************
122
123 PROCEDURE updatePropertyValues(element IN xmldom.DOMElement, uid IN varchar2) IS
124 CURSOR getPropertiesForObject IS
125 SELECT b.name, c.propval_id , b.property_id
126 FROM ies_meta_objects o,
127 ies_meta_object_propvals a,
128 ies_meta_properties b,
129 ies_meta_property_values c
130 WHERE o.object_uid = uid
131 AND a.propval_id = c.propval_id
132 AND b.property_id = c.property_id
133 AND o.object_id = a.object_id;
134
135 val VARCHAR2(256);
136 propId NUMBER;
137 lookupId NUMBER;
138 sqlStmt VARCHAR2(256);
139 BEGIN
140 for i in getPropertiesForObject loop
141 val := IES_META_DATA_UTIL.getProperty(element, i.name);
142
143 lookupId := IES_META_DATA_UTIL.getLookupId(i.property_id, val);
144 if (lookupId = -1) then
145 sqlStmt := 'UPDATE ies_meta_property_values
146 SET string_val = :value,
147 last_update_date = sysdate
148 WHERE propval_id = :propvalue_id';
149 EXECUTE IMMEDIATE sqlStmt USING val, i.propval_id;
150 else
151 sqlStmt := 'UPDATE ies_meta_property_values
152 SET lookup_id = :lookupId,
153 last_update_date = sysdate
154 WHERE propval_id = :propvalue_id';
155 EXECUTE IMMEDIATE sqlStmt USING lookupId, i.propval_id;
156 end if;
157
158 end loop;
159 END updatePropertyValues;
160
161 -- **********************************************************************
162 -- API name : insertObjPropertyValues
163 -- Type : Private
164 -- Function : This procedure inserts the property values of the object
165 -- into ies_meta_object_propvals table.
166 -- **********************************************************************
167
168 PROCEDURE insertObjPropertyValues(propval_tab IN propval_table, objUID IN VARCHAR2) IS
169 propValId number;
170
171 objPropValId number;
172 sqlstmt varchar2(2000);
173 seqval number;
174 BEGIN
175 for i in 0..propval_tab.last loop
176 propValId := propval_tab(i);
177
178 execute immediate 'select ies_meta_object_propvals_s.nextval from dual' into seqval;
179 sqlStmt :=
180 'INSERT INTO ies_meta_object_propvals
181 (objpropval_id,
182 object_id,
183 propval_id,
184 created_by) SELECT :seq,
185 object_id,
186 :propValId,
187 1 from ies_meta_objects where object_uid = :objuid';
188
189 execute immediate sqlStmt using seqval, propValId, objUID;
190 end loop;
191 END insertObjPropertyValues;
192
193 -- **********************************************************************
194 -- API name : insertNewProperties
195 -- Type : Private
196 -- Function : This procedure inserts the properties into
197 -- ies_meta_property_values table. The inserted propval_ids
198 -- are stored in a pl/sql table and all the propval_ids are
199 -- later inserted into ies_meta_object_propvals table. Filter
200 -- records which already have been inserted.
201 -- **********************************************************************
202
203
204 PROCEDURE insertNewProperties(element IN xmldom.DOMElement, objUID IN VARCHAR2) IS
205 type props_type IS REF CURSOR;
206 props props_type;
207
208 val VARCHAR2(256);
209 propId NUMBER;
210 propName VARCHAR2(256);
211 lookupId NUMBER;
212
213 propval_tab propval_table;
214 propvalue_id number;
215 counter NUMBER := 0;
216 sqlStmt varchar2(2000);
217 BEGIN
218
219 OPEN props FOR
220 'SELECT b.name, a.property_id
221 FROM ies_meta_obj_type_properties a,
222 ies_meta_properties b
223 WHERE a.property_id = b.property_id
224 AND a.objtype_id IN (SELECT type_id
225 FROM ies_meta_object_types
226 CONNECT BY PRIOR parent_id = type_id
227 START WITH type_id IN (SELECT type_id
228 FROM ies_meta_objects
229 WHERE object_uid = :aUID))
230 AND b.property_id NOT IN (SELECT b.property_id
231 FROM ies_meta_objects o,
232 ies_meta_object_propvals a,
233 ies_meta_properties b,
234 ies_meta_property_values c
235 WHERE o.object_uid = :bUID
236 AND a.propval_id = c.propval_id
237 AND b.property_id = c.property_id
238 AND o.object_id = a.object_id)' USING objUID, objUID;
239
240 LOOP
241 FETCH props INTO propName, propId;
242 EXIT WHEN props%NOTFOUND;
243
244 val := IES_META_DATA_UTIL.getProperty(element, propName);
245
246 lookupId := IES_META_DATA_UTIL.getLookupId(propId, val);
247 select ies_meta_property_values_s.nextval into propValue_id from dual;
248
249 if (lookupId = -1) then
250 sqlStmt := 'INSERT INTO ies_meta_property_values (propval_id,
251 property_id,
252 string_val,
253 created_by)
254 VALUES (:id,
255 :property_id,
256 :val,
257 1 )';
258
259 execute immediate sqlStmt using propvalue_id, propId, val;
260 propval_tab(counter) := propvalue_id;
261 else
262 sqlStmt := 'INSERT INTO ies_meta_property_values (propval_id,
263 property_id,
264 lookup_id,
265 created_by)
266 VALUES (:id,
267 :property_id,
268 :lookupId,
269 1 )';
270
271 execute immediate sqlStmt using propvalue_id, propId, lookupId;
272 propval_tab(counter) := propvalue_id;
273 end if;
274 counter := counter + 1;
275 end loop;
276 if (counter > 0) then
277 insertObjPropertyValues(propval_tab, objUID);
278 end if;
279 END insertNewProperties;
280
281 /************************ Public methods *******************************/
282
283 -- **********************************************************************
284 -- API name : updateMetaObject
285 -- Type : public
286 -- Function : This function updates the meta object and its properties,
287 -- returns object id.
288 -- **********************************************************************
289
290 FUNCTION updateMetaObject(element IN xmldom.DOMElement) return NUMBER IS
291 objId NUMBER := -1;
292 objname VARCHAR2(256);
293 uid VARCHAR2(256);
294 objTypeId NUMBER;
295 BEGIN
296 objname := IES_META_DATA_UTIL.getProperty(element, 'name');
297
298 if (objname is null) then
299 objname := 'nullname';
300 end if;
301
302 uid := IES_META_DATA_UTIL.getProperty(element, 'UID');
303 objtypeId := IES_META_DATA_UTIL.getObjectTypeId(xmldom.getAttribute(element, 'CLASS'));
304 objId := to_number(IES_META_DATA_UTIL.getProperty(element, 'objectId'));
305
306
307 execute immediate' UPDATE ies_meta_objects
308 SET name = :1,
309 last_update_date = :2,
310 type_id = :3
311 WHERE object_uid = :4 returning object_id INTO :5' using objname, sysdate, objTypeId, uid returning INTO objId;
312
313
314 updatePropertyValues(element, uid);
315 insertNewProperties(element, uid);
316 deleteOldRelationships(element, uid);
317
318 return objId;
319 END updateMetaObject;
320
321
322
323
324
325
326
327 END IES_META_DATA_UPDATER;
[Home] [Help]