swg-source-1.2/src-1.2
1
0
Fork 0
mirror of https://bitbucket.org/theswgsource/src-1.2.git synced 2026-07-14 00:01:30 -04:00
Code Issues Projects Releases Wiki Activity
Files
master
src-1.2/game/server/database/packages/purge_methods2.plsql
T
Anonymous cf2e16dba2 Added database/sql
2014-01-17 07:03:16 -07:00

336 lines
15 KiB
Plaintext
Raw Permalink Blame History

CREATE OR REPLACE PACKAGE BODY PURGE_METHODS2 is
/**********************************************************************/
/*** Purge Objects ***/
/*** ***/
/*** Deletes objects marked for deletion in object tables and ***/
/*** then the master objects table based on date value ***/
/**********************************************************************/
FUNCTION Purge_objects
(date_in in objects.deleted_date%type)
RETURN PLS_INTEGER
IS
v_count PLS_INTEGER := 0;
Obj_list Obj_list_t; -- VARRAY object to hold ids to bulk delete for child objects
Obj_list_index PLS_INTEGER; -- index into VARRAY object
BEGIN
Obj_list := Obj_list_t(); -- initialize VARRAY object
Obj_list.extend(chunk_size); -- allocate VARRAY object memory to chunk size
-- LOGGING.LOG_STATUS(1,'Starting object deletions.');
--open the cursor for the object ids we want to purge.
--we will use these ids to delete records from other tables
FOR x IN (SELECT /*+ INDEX (OBJECTS DELETED_OBJECT_IDX) */ object_id
FROM objects
WHERE deleted_date < date_in) LOOP
v_count := v_count + 1;
Obj_list_index := MOD(v_count, chunk_size); -- what obj index for this batch
If Obj_list_index = 0 then
Obj_list(chunk_size) := x.object_id; -- make sure we fill last VARRAY element
Purge_objects_work(Obj_list); -- bulk delete object ids in VARRAY list
COMMIT;
-- LOGGING.LOG_STATUS(v_count,'batch purged');
Else
Obj_list(Obj_list_index) := x.object_id; -- fill VARRAY with ids
End If;
END LOOP;
-- LOGGING.LOG_STATUS(v_count,'Child object main loop exited.');
--Now delete remaining partially full object list for objects
If Obj_list_index != 0 then
Obj_list.trim(chunk_size - Obj_list_index);
Purge_objects_work(Obj_list);
COMMIT;
-- LOGGING.LOG_STATUS(v_count,'Deleting chunk remainder.');
End If;
Obj_list.delete; -- cleanup - delete VARRAY list
-- LOGGING.LOG_STATUS(v_count,'Finished deleting '|| to_char(v_count) ||' references to objects.');
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
IF v_count = 0 THEN
dbms_output.put_line ('never deleted');
-- LOGGING.LOG_ERROR(SQLCODE, SQLERRM);
END IF;
dbms_output.put_line (v_count||': iterations');
-- LOGGING.LOG_ERROR(SQLCODE, SQLERRM);
RETURN SQLCODE;
END Purge_objects;
/**********************************************************************/
/*** Purge Objects Work ***/
/*** ***/
/*** Bulk delete objects marked for deletion in child object tables ***/
/*** given a list of object ids passed in from a VARRAY. Called ***/
/*** purge objects function. ***/
/**********************************************************************/
PROCEDURE Purge_objects_work
(Obj_list IN Obj_list_t) IS
BEGIN
--delete all children
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM armor WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM battlefield_marker_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM battlefield_participants WHERE region_object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM battlefield_participants WHERE character_object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM biographies WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM building_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM cell_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM creature_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM experience_points WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM factory_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM guild_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM harvester_installation_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM installation_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM intangible_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM location_lists WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM manf_schematic_attributes WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM manf_schematic_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM manufacture_inst_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM mission_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM object_variables WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM planet_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM player_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM property_lists WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM resource_container_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM scripts WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM static_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM tangible_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM token_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM universe_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM vehicle_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM waypoints WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM weapon_objects WHERE object_id = Obj_list(i);
--delete messages target(object_id)
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM messages WHERE target = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM objects WHERE object_id = Obj_list(i);
END Purge_objects_work;
--
--
--
FUNCTION time_purge
(date_in IN VARCHAR2)
RETURN PLS_INTEGER IS
v_retval PLS_INTEGER := 0;
v_date VARCHAR2(20) := date_in;
v_time NUMBER := 1;
BEGIN
FOR i IN 1..24 LOOP
dbms_output.put_line(to_char(to_date(v_date, 'dd-mon-yy hh24:mi:ss') + v_time/24, 'DD-MON-YY hh24:mi:ss'));
v_retval := purge_objects(to_date(v_date, 'dd-mon-yy hh24:mi:ss') + v_time/24);
EXIT WHEN v_retval <> 0;
IF v_time = 23 THEN
v_time := v_time + (59/60);
ELSE
v_time := v_time + 1;
END IF;
END LOOP;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line('Purge return values: '||v_retval);
dbms_output.put_line('Time it failed at: '||v_time);
RETURN SQLCODE;
END time_purge;
/**********************************************************************/
/*** Purge Consumables ***/
/*** ***/
/*** Deletes consumable objects marked for deletion in child object ***/
/*** tables and then the master objects table based on date value ***/
/**********************************************************************/
FUNCTION Purge_consumables
(start_date_in in objects.deleted_date%type,
end_date_in in objects.deleted_date%type)
RETURN PLS_INTEGER
IS
v_count PLS_INTEGER := 0;
Obj_list Obj_list_t; -- VARRAY object to hold ids to bulk delete for objects
Obj_list_index PLS_INTEGER; -- index into VARRAY object
BEGIN
Obj_list := Obj_list_t(); -- initialize VARRAY object
Obj_list.extend(chunk_size); -- allocate VARRAY object memory to chunk size
-- LOGGING.LOG_STATUS(1,'Starting child object deletions for consumables.');
--open the cursor for the object ids we want to purge.
--we will use these ids to delete records from other tables
FOR x IN (SELECT object_id
FROM objects
WHERE deleted_date is not NULL
AND deleted_date BETWEEN start_date_in AND end_date_in
and object_template_id in (
300281711,
352903310,
1006954225,
9343642,
35614291,
339194814,
1001781097,
1632911215,
1139268412,
2059421636,
1349101341,
-1657672270,
1931602976,
-1440714292,
-1436615854,
-1614624973,
-1556205486,
-679440489,
-1397391672,
-541746271,
-718648728,
-789429729,
-786663538,
-610128810,
-132487792,
-1129945606,
-104858180,
-82570045)) LOOP
v_count := v_count + 1;
Obj_list_index := MOD(v_count, chunk_size); -- what obj index for this batch
If Obj_list_index = 0 then
Obj_list(chunk_size) := x.object_id; -- make sure we fill last VARRAY element
Purge_consumables_work(Obj_list); -- bulk delete object ids in VARRAY list
COMMIT;
-- LOGGING.LOG_STATUS(v_count,'batch purged');
Else
Obj_list(Obj_list_index) := x.object_id; -- fill VARRAY with ids
End If;
END LOOP;
-- LOGGING.LOG_STATUS(v_count,'Consumables object main loop exited.');
--Now delete remaining partially full object list for objects
If Obj_list_index != 0 then
Obj_list.trim(chunk_size - Obj_list_index);
Purge_consumables_work(Obj_list);
COMMIT;
-- LOGGING.LOG_STATUS(v_count,'Deleting chunk remainder.');
End If;
Obj_list.delete; -- cleanup - delete VARRAY list
-- LOGGING.LOG_STATUS(v_count,'Finished deleting '|| to_char(v_count) ||' consumable objects.');
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
IF v_count = 0 THEN
dbms_output.put_line ('never deleted');
-- LOGGING.LOG_ERROR(SQLCODE, SQLERRM);
END IF;
dbms_output.put_line (v_count||': iterations');
-- LOGGING.LOG_ERROR(SQLCODE, SQLERRM);
RETURN SQLCODE;
END Purge_consumables;
/**********************************************************************/
/*** Purge Consumables Work ***/
/*** ***/
/*** Bulk delete consumable objects marked for deletion in child ***/
/*** object tables given a list of object ids passed in from a ***/
/*** VARRAY. Called purge consumables function. ***/
/**********************************************************************/
PROCEDURE Purge_consumables_work
(Obj_list IN Obj_list_t)
IS
BEGIN
--delete all children
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM armor WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM battlefield_marker_objects WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM biographies nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM creature_objects nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM experience_points nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM factory_objects nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM intangible_objects nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM location_lists nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM mission_objects nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM object_variables nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM property_lists nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM resource_container_objects nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM scripts nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM tangible_objects nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM token_objects nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM vehicle_objects nologging WHERE object_id = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM weapon_objects nologging WHERE object_id = Obj_list(i);
--delete messages target(object_id)
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM messages nologging WHERE target = Obj_list(i);
FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM objects nologging WHERE object_id = Obj_list(i);
END Purge_consumables_work;
--
--
--
FUNCTION time_purge_consumables
(start_date_in in objects.deleted_date%type,
end_date_in in objects.deleted_date%type)
RETURN PLS_INTEGER IS
v_retval PLS_INTEGER := 0;
v_start_date DATE := start_date_in;
v_end_date DATE := end_date_in;
v_hours NUMBER := (v_end_date - v_start_date) * 24;
v_time NUMBER := 1;
BEGIN
dbms_output.put_line('Hours between dates: '||v_hours);
FOR i IN 1..v_hours LOOP
v_end_date := v_start_date + v_time/24;
dbms_output.put_line ('Calling proc with Start Date: '||to_char(v_start_date, 'dd-mon-yy hh24:mi:ss')||'-'||'End Date: '||to_char(v_end_date, 'dd-mon-yy hh24:mi:ss'));
v_retval := purge_consumables(v_start_date, v_end_date);
v_start_date := v_start_date + v_time/24;
EXIT WHEN v_retval <> 0;
END LOOP;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line('Purge return value: '||v_retval);
RETURN SQLCODE;
END time_purge_consumables;
end purge_methods2;
/
Reference in New Issue View Git Blame Copy Permalink