Pooling gameobjects

Malcrom · September 9, 2015, 5:47pm

I’m currently doing some pooling work for chest, fishing schools, herbs, and ore. The massive amount of lines of sql, finding guids to use, finding two different pool ids is tiresome.

I’m assuming the current system was designed with minimal coding in mind rather than ease of use.

Also the current design does not make it easy to go back at a later date and add more spawns. Below I have provided sql examples for current system and, what i think would be a better design for adding in future:

– ### SQL for Current pooling system tables with set guids ###

– Feralas (Zone 357) Mithril Deposit spawns
SET @LASTGUID := 7;
SET @OGUID1 := xxxxxx;
SET @OGUID2 := @OGUID1 + (@LASTGUID + 1);
SET @POOL := 357xxx; – xxx pooled
SET @MOTHER := 357xx;
SET @ACTIVE := 2; – Max number of active spawns
SET @TIMER := 30; – Respawn timer in seconds

DELETE FROM gameobject WHERE guid BETWEEN @OGUID1 AND @OGUID1+@LASTGUID;
DELETE FROM gameobject WHERE guid BETWEEN @OGUID2 AND @OGUID2+@LASTGUID;
INSERT INTO gameobject (guid,id,map,zoneId,spawnMask,phaseMask,phaseId,PhaseGroup,position_x,position_y,position_z,orientation,rotation0,rotation1,rotation2,rotation3,spawntimesecs,animprogress,state,VerifiedBuild) VALUES
– Mithril Deposit (2) 10% Truesilver Deposit 2047
(@OGUID1+0,2040,1,357,0,1,1,0,0,-4535.05,1617.48,114.628,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID1+1,2040,1,357,0,1,1,0,0,-4483.9,1216.35,129.027,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID1+2,2040,1,357,0,1,1,0,0,-4478.17,1110.82,131.305,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID1+3,2040,1,357,0,1,1,0,0,-4567.82,1241.38,136.818,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID1+4,2040,1,357,0,1,1,0,0,-4510.09,1583.08,126.23,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID1+5,2040,1,357,0,1,1,0,0,-4461.69,1127.75,134.806,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID1+6,2040,1,357,0,1,1,0,0,-4470.21,1454.98,135.588,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID2+7,2040,1,357,0,1,1,0,0,-4495.98,1536.9,135.482,0,0,0,0,1, @TIMER,255,1,20444),
– Truesilver Deposit
(@OGUID2+0,2047,1,357,0,1,1,0,0,-4535.05,1617.48,114.628,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID2+1,2047,1,357,0,1,1,0,0,-4483.9,1216.35,129.027,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID2+2,2047,1,357,0,1,1,0,0,-4478.17,1110.82,131.305,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID2+3,2047,1,357,0,1,1,0,0,-4567.82,1241.38,136.818,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID2+4,2047,1,357,0,1,1,0,0,-4510.09,1583.08,126.23,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID2+5,2047,1,357,0,1,1,0,0,-4461.69,1127.75,134.806,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID2+6,2047,1,357,0,1,1,0,0,-4470.21,1454.98,135.588,0,0,0,0,1, @TIMER,255,1,20444),
(@OGUID2+7,2047,1,357,0,1,1,0,0,-4495.98,1536.9,135.482,0,0,0,0,1, @TIMER,255,1,20444);

– Add mother pool to pool template
DELETE FROM pool_template WHERE entry=@MOTHER;
INSERT INTO pool_template (entry,max_limit,description) VALUES
(@MOTHER,@ACTIVE, ‘Feralas Mithril Deposit Mother pool’);

– Pool Ore Spawnpoints
DELETE FROM pool_gameobject WHERE guid BETWEEN @OGUID1 AND @OGUID1+@LASTGUID;
DELETE FROM pool_gameobject WHERE guid BETWEEN @OGUID2 AND @OGUID2+@LASTGUID;
DELETE FROM pool_gameobject WHERE guid BETWEEN @OGUID3 AND @OGUID3+@LASTGUID;
INSERT INTO pool_gameobject (guid,pool_entry,chance,description) VALUES
(@OGUID1+0,@POOL+0,80, ‘Mithril Deposit’),
(@OGUID2+0,@POOL+0,10, ‘Truesilver Deposit’),
(@OGUID3+0,@POOL+0,10, ‘Gold Vein’),
(@OGUID1+1,@POOL+1,80, ‘Mithril Deposit’),
(@OGUID2+1,@POOL+1,10, ‘Truesilver Deposit’),
(@OGUID3+1,@POOL+1,10, ‘Gold Vein’),
(@OGUID1+2,@POOL+2,80, ‘Mithril Deposit’),
(@OGUID2+2,@POOL+2,10, ‘Truesilver Deposit’),
(@OGUID3+2,@POOL+2,10, ‘Gold Vein’),
(@OGUID1+3,@POOL+3,80, ‘Mithril Deposit’),
(@OGUID2+3,@POOL+3,10, ‘Truesilver Deposit’),
(@OGUID3+3,@POOL+3,10, ‘Gold Vein’),
(@OGUID1+4,@POOL+4,80, ‘Mithril Deposit’),
(@OGUID2+4,@POOL+4,10, ‘Truesilver Deposit’),
(@OGUID3+4,@POOL+4,10, ‘Gold Vein’),
(@OGUID1+5,@POOL+5,80, ‘Mithril Deposit’),
(@OGUID2+5,@POOL+5,10, ‘Truesilver Deposit’),
(@OGUID3+5,@POOL+5,10, ‘Gold Vein’),
(@OGUID1+6,@POOL+6,80, ‘Mithril Deposit’),
(@OGUID2+6,@POOL+6,10, ‘Truesilver Deposit’),
(@OGUID3+6,@POOL+6,10, ‘Gold Vein’),
(@OGUID1+7,@POOL+7,80, ‘Mithril Deposit’),
(@OGUID2+7,@POOL+7,10, ‘Truesilver Deposit’),
(@OGUID3+7,@POOL+7,10, ‘Gold Vein’);

– Pool Mithril Deposit Spawnpoints
DELETE FROM pool_gameobject WHERE guid BETWEEN @OGUID AND @OGUID+@LASTGUID;
INSERT INTO pool_gameobject (guid,pool_entry,chance,description) VALUES
(@OGUID+0,@MOTHER,0, ‘Feralas Mithril Deposit Spawnpoint’),
(@OGUID+1,@MOTHER,0, ‘Feralas Mithril Deposit Spawnpoint’),
(@OGUID+2,@MOTHER,0, ‘Feralas Mithril Deposit Spawnpoint’),
(@OGUID+3,@MOTHER,0, ‘Feralas Mithril Deposit Spawnpoint’),
(@OGUID+4,@MOTHER,0, ‘Feralas Mithril Deposit Spawnpoint’),
(@OGUID+5,@MOTHER,0, ‘Feralas Mithril Deposit Spawnpoint’),
(@OGUID+6,@MOTHER,0, ‘Feralas Mithril Deposit Spawnpoint’),
(@OGUID+7,@MOTHER,0, ‘Feralas Mithril Deposit Spawnpoint’);

– Add each pool to pool template
DELETE FROM pool_template WHERE entry BETWEEN @POOL AND @POOL+@LASTGUID;
INSERT INTO pool_template (entry,max_limit,description) VALUES
(@POOL+0,1, ‘Feralas Mithril Deposit Spawnpoint’),
(@POOL+1,1, ‘Feralas Mithril Deposit Spawnpoint’),
(@POOL+2,1, ‘Feralas Mithril Deposit Spawnpoint’),
(@POOL+3,1, ‘Feralas Mithril Deposit Spawnpoint’),
(@POOL+4,1, ‘Feralas Mithril Deposit Spawnpoint’),
(@POOL+5,1, ‘Feralas Mithril Deposit Spawnpoint’),
(@POOL+6,1, ‘Feralas Mithril Deposit Spawnpoint’),
(@POOL+7,1, ‘Feralas Mithril Deposit Spawnpoint’);

– ### SQL for pooling system tables with dynamic guids ###

– Mithril Deposit 10% Truesilver Deposit 2047
SET @POOL := 357xxx; – xxx pooled
SET @ACTIVE := 2; – Max number of active spawns
SET @TIMER := 30; – Respawn timer in seconds
– Add pool to pool template
DELETE FROM gameobject_spawn_pool WHERE entry=@POOL;
INSERT INTO gameobject_spawn_pool (entry,max_limit,spawntimesecs,description) VALUES
(@POOL,@ACTIVE,@TIMER, ‘Feralas Mithril / Truesilver Deposit Mother pool’);
– Pool spawn points
DELETE FROM gameobject_pool WHERE pool=@POOL;
INSERT INTO gameobject_pool (pool,spawn,id1,id2,id3,chance1,chance2,chance2,map,zoneId,spawnMask,phaseMask,phaseId,PhaseGroup,position_x,position_y,position_z,orientation,rotation0,rotation1,rotation2,rotation3,animprogress,state,VerifiedBuild) VALUES
(@POOL,0,2040,2047,0,90,10,0,1,357,0,1,1,0,0,-4535.05,1617.48,114.628,0,0,0,0,1,255,1,20444),
(@POOL,1,2040,2047,0,90,10,0,1,357,0,1,1,0,0,-4483.9,1216.35,129.027,0,0,0,0,1,255,1,20444),
(@POOL,2,2040,2047,0,90,10,0,1,357,0,1,1,0,0,-4478.17,1110.82,131.305,0,0,0,0,1,255,1,20444),
(@POOL,3,2040,2047,0,90,10,0,1,357,0,1,1,0,0,-4567.82,1241.38,136.818,0,0,0,0,1,255,1,20444),
(@POOL,4,2040,2047,0,90,10,0,1,357,0,1,1,0,0,-4510.09,1583.08,126.23,0,0,0,0,1,255,1,20444),
(@POOL,5,2040,2047,0,90,10,0,1,357,0,1,1,0,0,-4461.69,1127.75,134.806,0,0,0,0,1,255,1,20444),
(@POOL,6,2040,2047,0,90,10,0,1,357,0,1,1,0,0,-4470.21,1454.98,135.588,0,0,0,0,1,255,1,20444),
(@POOL,7,2040,2047,0,90,10,0,1,357,0,1,1,0,0,-4495.98,1536.9,135.482,0,0,0,0,1,255,1,20444);In the proposed dynamic design you would only need to find a pool id and adding extra spawns in the future would be easy for any db dev.

I’m just throwing this out as an idea for anyone interested in looking into it.

Malcrom · September 9, 2015, 7:42pm

I’m currently gathering gameobject spawning for Feralas, Silithus, Tanaris, Thousand Needles, Un’Goro Crater, and Desolace for pooling. To get much many spawnpoints for ore, herb, fish, and chest, you need many sniffs. So if anyone is interested in submitting fly overs of any of these zones please email me a note if you submit any.

r00ty · September 10, 2015, 7:00am

Yep, game objects was always on the roadmap for the pooling changes I’ve been making my initial plan was to have a single spawns table and have game object/creature tables reference those.

Like I’ve said before. Some other input from other DB people that will actually need to use whatever solution is finalized needs to arrive. For gameobjects the current pooling seems overly strange. The way I see it, it should be quite simple…

For the average zone you have maybe 2-3 normal kinds of ore, and a special kind of ore. From my experience, the special (green) ore shares the spawn points of one of the regular ore types.

So the spawning would be. 1 entry per spawn point.

Then, one entry for say, copper ore linking to the pool ID on all the copper spawn points. No minimum/maximum. 100% chance

One entry for tin ore, linking to the pool ID on all tin spawn points. No minimum/maximum. 99% chance

One entry for Silver ore, linking to the pool ID for all tin spawnpoints. 1 minimum, 3 maximum for example, 1% chance.

Then one entry per pool above, for pool within a pool pointing to both of the above pool IDs. With minimum 15, maximum 40. Weighting those. So say 70% copper, 30% tin/silver (they were one pool ID).

The result would be that it would upon server startup, it would choose 40 points at random. 70% from the copper group and 30% from the tin/silver group and spawn the ores. If somehow 1% would spawn more than 3 silvers, it would stop at 3.

As people mine, respawns would happen at the normal rate. If the number of spawned ored dropped below 15 (or for silver below 1) then, enough ore would be spawned to meet those minimums at the next expedited spawn update.

That way, it’s a lot less work to enter the data first of all. It also allows fine tuning of the quotas, by just changing the numbers in the parent pool records.

Anyway I understand this multi layer approach is more difficult and your way is also fine, but I think it could do with adding some of the features I include above. That is, the pool in a pool to cope with the situation in most zones where multiple minerals are present but there should be an overall control of the spawn count in the whole zone. Plus the minimum spawn limit, to handle busy zones.

Malcrom · September 10, 2015, 10:03am

Ore can be up to three ids per spawnpoint. Herbs seem to have their own for each type. Some fish schools share spawn points.

Something needs to be implemented for proper pooling. This has been ignored for far too long.

r00ty · September 10, 2015, 12:45pm

Yes, but something was already done, for creatures and go’s were also planned. However, the main issue with dynamic guids is, that without the changes to localise them to the map object, they will run out too soon.

Thus, this all hinges on when that is accepted into 3.3.5. It’s already in 6.x. Once it’s in 3.3.5 I’ll work on pooling for 3.3.5, and it can be ported into 6.x once complete and accepted.

What matters now though is what format people in general want the database. Because your way, is easier for you. But it has limitations. Hence I’d like to hear from some other database people that may or may not have their own views.

Malcrom · September 10, 2015, 2:20pm

Well it would be easier for anyone /emoticons/default_smile.png

Like I’ve said before I make an sql with 1400 lines for 172 ore spawnpoints in Felwood. I have 289 so far in feralas That would be a lot more sql /emoticons/default_sad.png

r00ty · September 10, 2015, 2:25pm

I’m not describing the current method. I’m describing the method used in my previous threads about pooling.

At any rate there might be some solutions to keep the flexibility but make this layout available to you. But I’ll think more about it when in a position to get back to the coding on this.

Malcrom · September 10, 2015, 2:31pm

Would something like this be more your liking?

Noting that this doubles table entries and also requires user to uses two lines of sql to update chance. Also adding an extra entry to the spawn location would require adding a record to the db rather than updating one.

[CODE]-- ### SQL for pooling system tables with dynamic guids ###

– Mithril Deposit 10% Truesilver Deposit 2047
SET @POOL := 357xxx; – xxx pooled
SET @ACTIVE := 2; – Max number of active spawns
SET @TIMER := 30; – Respawn timer in seconds
– Add pool to pool template
DELETE FROM gameobject_spawn_pool WHERE entry=@POOL;
INSERT INTO gameobject_spawn_pool (entry,max_limit,spawntimesecs,description) VALUES
(@POOL,@ACTIVE,@TIMER, ‘Feralas Mithril / Truesilver Deposit Mother pool’);
– Pool spawn points
DELETE FROM gameobject_pool WHERE pool=@POOL;
INSERT INTO gameobject_pool (pool,spawn,id1,chance1,map,zoneId,spawnMask,phaseMask,phaseId,PhaseGroup,position_x,position_y,position_z,orientation,rotation0,rotation1,rotation2,rotation3,animprogress,state,VerifiedBuild) VALUES
– Mithril
(@POOL,0,2040,90,1,357,0,1,1,0,0,-4535.05,1617.48,114.628,0,0,0,0,1,255,1,20444),
(@POOL,1,2040,90,1,357,0,1,1,0,0,-4483.9,1216.35,129.027,0,0,0,0,1,255,1,20444),
(@POOL,2,2040,90,1,357,0,1,1,0,0,-4478.17,1110.82,131.305,0,0,0,0,1,255,1,20444),
(@POOL,3,2040,90,1,357,0,1,1,0,0,-4567.82,1241.38,136.818,0,0,0,0,1,255,1,20444),
(@POOL,4,2040,90,1,357,0,1,1,0,0,-4510.09,1583.08,126.23,0,0,0,0,1,255,1,20444),
(@POOL,5,2040,90,1,357,0,1,1,0,0,-4461.69,1127.75,134.806,0,0,0,0,1,255,1,20444),
(@POOL,6,2040,90,1,357,0,1,1,0,0,-4470.21,1454.98,135.588,0,0,0,0,1,255,1,20444),
(@POOL,7,2040,90,1,357,0,1,1,0,0,-4495.98,1536.9,135.482,0,0,0,0,1,255,1,20444),
– Truesilver
(@POOL,0,2047,10,1,357,0,1,1,0,0,-4535.05,1617.48,114.628,0,0,0,0,1,255,1,20444),
(@POOL,1,2047,10,1,357,0,1,1,0,0,-4483.9,1216.35,129.027,0,0,0,0,1,255,1,20444),
(@POOL,2,2047,10,1,357,0,1,1,0,0,-4478.17,1110.82,131.305,0,0,0,0,1,255,1,20444),
(@POOL,3,2047,10,1,357,0,1,1,0,0,-4567.82,1241.38,136.818,0,0,0,0,1,255,1,20444),
(@POOL,4,2047,10,1,357,0,1,1,0,0,-4510.09,1583.08,126.23,0,0,0,0,1,255,1,20444),
(@POOL,5,2047,10,1,357,0,1,1,0,0,-4461.69,1127.75,134.806,0,0,0,0,1,255,1,20444),
(@POOL,6,2047,10,1,357,0,1,1,0,0,-4470.21,1454.98,135.588,0,0,0,0,1,255,1,20444),
(@POOL,7,2047,10,1,357,0,1,1,0,0,-4495.98,1536.9,135.482,0,0,0,0,1,255,1,20444);[/CODE]

r00ty · September 10, 2015, 2:43pm

But that’s pretty much my proposal. From the other thread (for creatures):

[ol][li]INSERT INTO pool_template[/li](entry, min_limit, max_limit, description)
VALUES
(100007,3,7,‘Scarlet convert/initiates (statics) - 7’),
(100003,8,12,‘Scarlet convert/initiates (roaming) - 26’)

INSERT INTO pool_creature_info
(pool_id, creature_id, creature_qualifier, chance, spawnMask, phaseMask, modelid, equipment_id, spawntimesecs, corpsetimesecsloot, corpsetimesecsnoloot, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags)
VALUES
(100003,1506,0,80,1,1,2403,1,180,0,0,10,0,71,0,1,0,0,0),
(100003,1507,0,20,1,1,2478,1,180,0,0,10,0,67,174,1,0,0,0),
(100007,1506,0,50,1,1,2403,1,180,0,0,0,0,71,0,0,0,0,0),
(100007,1507,0,50,1,1,2467,1,180,0,0,0,0,67,174,0,0,0,0)

INSERT INTO pool_spawns
(pool_id, guid, map, zoneId, areaId, position_x, position_y, position_z, orientation)
VALUES
(100003,1,0,0,0,1875.67,1303.86,90.4382,1.06303),
(100003,2,0,0,0,1719.75,1294.1,127.224,3.61536),
(100003,3,0,0,0,1862.37,1327.33,76.739,3.44423),
(100003,4,0,0,0,1804.3,1333.56,88.0722,4.86114),
(100003,5,0,0,0,1678.79,1330.88,130.01,3.76314),
(100007,6,0,0,0,1790.39,1345.21,89.3329,0.891939),
(100003,7,0,0,0,1835.88,1340.23,81.4634,4.07856),
(100003,8,0,0,0,1775.13,1303.42,104.451,4.84573),
(100003,9,0,0,0,1702.63,1363.19,118.611,3.81337),
(100003,10,0,0,0,1781.26,1293.31,110.75,5.42494),
(100003,11,0,0,0,1877.66,1285.81,97.7998,2.32532),
(100003,12,0,0,0,1798.14,1299.33,103.75,5.12213),
(100007,13,0,0,0,1795.91,1342.81,89.0869,2.22754),
(100003,14,0,0,0,1814.61,1284.95,97.8504,0.336726),
(100007,15,0,0,0,1844.64,1290.78,102.226,3.62851),
(100003,16,0,0,0,1829.73,1327.49,86.4345,3.30384),
(100003,17,0,0,0,1747.31,1282.72,112.219,0.209708),
(100007,18,0,0,0,1789.48,1335.2,89.7295,1.36136),
(100003,19,0,0,0,1861.87,1379.92,75.753,5.0045),
(100003,20,0,0,0,1873.37,1361.32,70.4063,1.29634),
(100003,21,0,0,0,1836.48,1365.1,74.2134,4.4619),
(100003,22,0,0,0,1810.6,1360.75,83.6972,3.52339),
(100003,23,0,0,0,1939.19,1366.7,68.0464,3.61504),
(100003,24,0,0,0,1730.29,1367.5,104.448,1.2161),
(100003,25,0,0,0,1741.45,1342.81,104.04,4.259),
(100003,26,0,0,0,1752.96,1325,99.9812,4.63906),
(100003,27,0,0,0,1728.35,1327.06,108.116,1.90598),
(100003,28,0,0,0,1768.75,1336.7,90.3585,3.8317),
(100007,29,0,0,0,1770.16,1376.83,90.0053,1.41803),
(100007,30,0,0,0,1777.73,1385.49,91.8299,1.7109),
(100003,31,0,0,0,1814.35,1395.29,78.5285,1.24603),
(100007,32,0,0,0,1771.12,1409.43,95.1356,1.09421),
(100003,33,0,0,0,1745.93,1385.38,98.6645,0.979422
[/ol]
You can see, it’s one table to define pools, with minimum/maximum quotas. One table to store the creatures that will be spawned (with weight) and one to store the spawn points.

Now, some refinements are planned, such as a tier model that will allow pools to be pooled themselves (mainly just so you can define say for gameobjects, a minimum/maximum quotes for all spawns in the zone, while keeping the spawn points separate for say copper and tin ore). Also as you mentioned, probably the waypoint field can be moved to spawnpoint.

Also, there’s no reason we can’t provide your interface while keeping things internally in the way I suggested originally. By using views to show the data in the way you’d like, and stored procedures to store, revert or commit any changes you make to staging tables (in the format you suggest).

So I think then we have the flexibility of the original idea, with the ease of reading/designing that you’d like… Might be the win/win situation.

Malcrom · September 10, 2015, 3:20pm

Reguarding the fish pool we were talking about in irc this is how my proposal would look:

-- Feralas Oily Blackmouth & Firefin Snapper School spawns SET @POOL := 357xxx; -- xxx pooled SET @MINACTIVE := 9; -- Min number of active spawns SET @MAXACTIVE := 9; -- Max number of active spawns SET @TIMER := 30; -- Respawn timer in seconds -- Add pool to pool template DELETE FROM `gameobject_pools` WHERE `entry`=@POOL; INSERT INTO `gameobject_pools` (`entry`,`min_limit`,`max_limit`,`spawntimesecs`,`description`) VALUES (@POOL,@MINACTIVE,@MAXACTIVE,@TIMER, 'Feralas Oily Blackmouth & Firefin Snapper School pool'); -- Pool spawn points DELETE FROM `gameobject_pool_spawns` WHERE `pool`=@POOL; INSERT INTO `gameobject_pool_spawns` (`pool`,`spawn`,`id1`,`id2`,`id3`,`chance1`,`chance2`,`chance2`,`map`,`zoneId`,`spawnMask`,`phaseMask`,`phaseId`,`PhaseGroup`,`position_x`,`position_y`,`position_z`,`orientation`,`rotation0`,`rotation1`,`rotation2`,`rotation3`,`animprogress`,`state`) VALUES (@POOL,0,180682,0,0,100,0,0,1,357,0,1,1,0,0,-4357,2360.859,0,3.141593,0,0,-1,0,255,1), (@POOL,1,180682,0,0,100,0,0,1,357,0,1,1,0,0,-4074.941,2720.83,0,3.141593,0,0,-1,0,255,1), (@POOL,2,180682,0,0,100,0,0,1,357,0,1,1,0,0,-5020.641,2238.609,0,3.141593,0,0,-1,0,255,1), (@POOL,3,180682,0,0,100,0,0,1,357,0,1,1,0,0,-5238.16,2207.62,0,3.141593,0,0,-1,0,255,1), (@POOL,4,180682,0,0,100,0,0,1,357,0,1,1,0,0,-4402.471,-659.9271,0,3.141593,0,0,0,1,255,1), (@POOL,5,180682,0,0,100,0,0,1,357,0,1,1,0,0,-4130.021,2643.98,0,3.141593,0,0,0,1,255,1), (@POOL,6,180682,0,0,100,0,0,1,357,0,1,1,0,0,-5055.609,2239.561,0,3.141593,0,0,0,1,255,1), (@POOL,7,180682,0,0,100,0,0,1,357,0,1,1,0,0,-5366.319,2293.99,0,3.141593,0,0,0,1,255,1), (@POOL,8,180682,0,0,100,0,0,1,357,0,1,1,0,0,-4384.059,-624.8802,0,3.141593,0,0,0,1,255,1), (@POOL,9,180682,0,0,100,0,0,1,357,0,1,1,0,0,-4163.471,2580.9,0,3.141593,0,0,-1,0,255,1), (@POOL,10,180682,0,0,100,0,0,1,357,0,1,1,0,0,-4909.471,2220.13,0,3.141593,0,0,-1,0,255,1), (@POOL,11,180682,0,0,100,0,0,1,357,0,1,1,0,0,-5319.78,2257.351,0,3.141593,0,0,-1,0,255,1), (@POOL,12,180682,0,0,100,0,0,1,357,0,1,1,0,0,-4042.95,2946.21,0,3.141593,0,0,-1,0,255,1), (@POOL,13,180682,180683,0,50,50,0,1,357,0,1,1,0,0,-4187.559,2547.74,0,3.141593,0,0,0,1,255,1), (@POOL,14,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4043.78,2915.47,0,3.141593,0,0,-1,0,255,1), (@POOL,15,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4225.641,2452.359,0,3.141593,0,0,-1,0,255,1), (@POOL,16,180683,0,0,100,0,0,1,357,0,1,1,0,0,-5402.41,2329.42,0,3.141593,0,0,-1,0,255,1), (@POOL,17,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4224.76,2412.609,0,3.141593,0,0,0,1,255,1), (@POOL,18,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4466.03,-709.7257,0,3.141593,0,0,0,1,255,1), (@POOL,19,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4531.76,2336.04,0,3.141593,0,0,0,1,255,1), (@POOL,20,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4743.471,2353.109,0,3.141593,0,0,0,1,255,1), (@POOL,21,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4011.441,2773.93,0,3.141593,0,0,0,1,255,1), (@POOL,22,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4871.28,2154.109,0,3.141593,0,0,0,1,255,1), (@POOL,23,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4438.04,-709.7847,0,3.141593,0,0,0,1,255,1), (@POOL,24,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4110.021,2675.07,0,3.141593,0,0,-1,0,255,1), (@POOL,25,180683,0,0,100,0,0,1,357,0,1,1,0,0,-5273.721,2249.891,0,3.141593,0,0,-1,0,255,1), (@POOL,26,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4561.441,2324.67,0,3.141593,0,0,-1,0,255,1), (@POOL,27,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4405.42,2366.851,0,3.141593,0,0,-1,0,255,1), (@POOL+28,180683,0,0,100,0,0,1,357,0,1,1,0,0,-4187.559,2547.74,0,3.141593,0,0,-1,0,255,1);

Malcrom · September 10, 2015, 3:50pm

Updated with moved fields:

-- Feralas Oily Blackmouth & Firefin Snapper School spawns SET @POOL := 1; -- pool id SET @MINACTIVE := 9; -- Min number of active spawns SET @MAXACTIVE := 9; -- Max number of active spawns SET @TIMER := 30; -- Respawn timer in seconds -- Add pool to pool template DELETE FROM `gameobject_pools` WHERE `id`=@POOL; INSERT INTO `gameobject_pools` (`map`,`zoneId`,`id`,`spawnMask`,`phaseId`,`PhaseGroup`,`min_limit`,`max_limit`,`spawntimesecs`,`description`) VALUES (1,357,@POOL,0,1,0,0,@MINACTIVE,@MAXACTIVE,@TIMER, 'Feralas Oily Blackmouth & Firefin Snapper School pool'); -- Pool spawn points DELETE FROM `gameobject_pool_spawns` WHERE `pool`=@POOL; INSERT INTO `gameobject_pool_spawns` (`pool`,`spawn`,`id1`,`id2`,`id3`,`chance1`,`chance2`,`chance2`,`position_x`,`position_y`,`position_z`,`orientation`,`rotation0`,`rotation1`,`rotation2`,`rotation3`,`animprogress`,`state`) VALUES (@POOL,0,180682,0,0,100,0,0,-4357,2360.859,0,3.141593,0,0,-1,0,255,1), (@POOL,1,180682,0,0,100,0,0,-4074.941,2720.83,0,3.141593,0,0,-1,0,255,1), (@POOL,2,180682,0,0,100,0,0,-5020.641,2238.609,0,3.141593,0,0,-1,0,255,1), (@POOL,3,180682,0,0,100,0,0,-5238.16,2207.62,0,3.141593,0,0,-1,0,255,1), (@POOL,4,180682,0,0,100,0,0,-4402.471,-659.9271,0,3.141593,0,0,0,1,255,1), (@POOL,5,180682,0,0,100,0,0,-4130.021,2643.98,0,3.141593,0,0,0,1,255,1), (@POOL,6,180682,0,0,100,0,0,-5055.609,2239.561,0,3.141593,0,0,0,1,255,1), (@POOL,7,180682,0,0,100,0,0,-5366.319,2293.99,0,3.141593,0,0,0,1,255,1), (@POOL,8,180682,0,0,100,0,0,-4384.059,-624.8802,0,3.141593,0,0,0,1,255,1), (@POOL,9,180682,0,0,100,0,0,-4163.471,2580.9,0,3.141593,0,0,-1,0,255,1), (@POOL,10,180682,0,0,100,0,0,-4909.471,2220.13,0,3.141593,0,0,-1,0,255,1), (@POOL,11,180682,0,0,100,0,0,-5319.78,2257.351,0,3.141593,0,0,-1,0,255,1), (@POOL,12,180682,0,0,100,0,0,-4042.95,2946.21,0,3.141593,0,0,-1,0,255,1), (@POOL,13,180682,180683,0,50,50,0,-4187.559,2547.74,0,3.141593,0,0,0,1,255,1), (@POOL,14,180683,0,0,100,0,0,-4043.78,2915.47,0,3.141593,0,0,-1,0,255,1), (@POOL,15,180683,0,0,100,0,0,-4225.641,2452.359,0,3.141593,0,0,-1,0,255,1), (@POOL,16,180683,0,0,100,0,0,-5402.41,2329.42,0,3.141593,0,0,-1,0,255,1), (@POOL,17,180683,0,0,100,0,0,-4224.76,2412.609,0,3.141593,0,0,0,1,255,1), (@POOL,18,180683,0,0,100,0,0,-4466.03,-709.7257,0,3.141593,0,0,0,1,255,1), (@POOL,19,180683,0,0,100,0,0,-4531.76,2336.04,0,3.141593,0,0,0,1,255,1), (@POOL,20,180683,0,0,100,0,0,-4743.471,2353.109,0,3.141593,0,0,0,1,255,1), (@POOL,21,180683,0,0,100,0,0,-4011.441,2773.93,0,3.141593,0,0,0,1,255,1), (@POOL,22,180683,0,0,100,0,0,-4871.28,2154.109,0,3.141593,0,0,0,1,255,1), (@POOL,23,180683,0,0,100,0,0,-4438.04,-709.7847,0,3.141593,0,0,0,1,255,1), (@POOL,24,180683,0,0,100,0,0,-4110.021,2675.07,0,3.141593,0,0,-1,0,255,1), (@POOL,25,180683,0,0,100,0,0,-5273.721,2249.891,0,3.141593,0,0,-1,0,255,1), (@POOL,26,180683,0,0,100,0,0,-4561.441,2324.67,0,3.141593,0,0,-1,0,255,1), (@POOL,27,180683,0,0,100,0,0,-4405.42,2366.851,0,3.141593,0,0,-1,0,255,1), (@POOL+28,180683,0,0,100,0,0,-4187.559,2547.74,0,3.141593,0,0,-1,0,255,1);

Malcrom · September 10, 2015, 9:07pm

Final Suggestion for table formats for gameobject and creature. please check to see if I left anything out and cement some table and filednames.

[CODE]-- Felwood Deadwood Village Spawning template
SET @POOLID := 1; – pool id
SET @MINACTIVE := 5; – Min number of active spawns
SET @MAXACTIVE := 34; – Max number of active spawns
SET @SPAWNTIMER := 300; – Respawn timer in seconds
SET @CORPSETIMER := 120; – Corpse remove timer in seconds
– Spawn template
DELETE FROM creature_pools WHERE entry = @POOLID;
INSERT INTO creature_pools (mapId,zoneId,poolId,spawnMask,phaseId,PhaseGroup,minLimit,maxLimit,spawnTimer,corpseTimer,description) VALUES
(1,361,@POOLID,1,0,0,@MINACTIVE,@MAXACTIVE,@SPAWNTIMER,@CORPSETIMER,‘Felwood Deadwood Village Spawning’);
– Spawnpoint pool
DELETE FROM creature_pool_spawns WHERE poolId=@POOLID;
INSERT INTO creature_pool_spawns (poolId,spawnPoint,creatureId1,creatureId2,creatureId3,chance1,chance2,chance3,positionX,positionY,positionZ,orientation,setupId,scriptId) VALUES
– No movement
(@POOLID,0,7153,7154,7155,34,33,33,3597.07,-1026.9,226.09,3.05,1,0),
(@POOLID,1,7153,7154,7155,34,33,33,3597.07,-1026.9,226.09,3.05,1,0),
(@POOLID,2,7153,7154,7155,34,33,33,3622.21,-1130.46,210.79,5.25,1,0),
(@POOLID,3,7153,7154,7155,34,33,33,3737.23,-1149.34,209.85,3.94,1,0),
(@POOLID,4,7153,7154,7155,34,33,33,3743.92,-1169.23,209.84,2.46,1,0),
(@POOLID,5,7153,7154,7155,34,33,33,3618.7,-1188.61,213.86,1.12,1,0),
(@POOLID,6,7153,7154,7155,34,33,33,3704.07,-1135.35,207.95,5.62,1,0),
(@POOLID,7,7153,7154,7155,34,33,33,3752.59,-993.49,241.83,3.58,1,0),
– Random movement
(@POOLID,8,7153,7154,7155,34,33,33,3533.1,-1108.63,225.34,5.6,1,1),
(@POOLID,9,7153,7154,7155,34,33,33,3534.61,-1150.68,225.42,5.1,1,1),
(@POOLID,10,7153,7154,7155,34,33,33,3543.74,-1128.56,222.26,0.27,1,1),
(@POOLID,11,7153,7154,7155,34,33,33,3609.81,-1164.02,214.18,3.63,1,1),
(@POOLID,12,7153,7154,7155,34,33,33,3611.9,-1042.81,220.58,1.72,1,1),
(@POOLID,13,7153,7154,7155,34,33,33,3619.8,-1209.82,217.42,2.36,1,1),
(@POOLID,14,7153,7154,7155,34,33,33,3636.09,-1168.11,210.07,0.44,1,1),
(@POOLID,15,7153,7154,7155,34,33,33,3649.05,-1138.72,209.13,3.68,1,1),
(@POOLID,16,7153,7154,7155,34,33,33,3649.92,-1179.41,211.8,6.28,1,1),
(@POOLID,17,7153,7154,7155,34,33,33,3653.85,-1028.21,221.65,0.52,1,1),
(@POOLID,18,7153,7154,7155,34,33,33,3673.6,-1157.89,207.91,2.41,1,1),
(@POOLID,19,7153,7154,7155,34,33,33,3712.1,-1198.12,207.7,2.06,1,1),
(@POOLID,20,7153,7154,7155,34,33,33,3716.14,-1015.36,228.82,5.97,1,1),
(@POOLID,21,7153,7154,7155,34,33,33,3721.86,-1062.5,221.8,3.14,1,1),
(@POOLID,22,7153,7154,7155,34,33,33,3727.77,-1103.57,213.43,5.04,1,1),
(@POOLID,23,7153,7154,7155,34,33,33,3773.95,-1196.66,217.44,1.04,1,1),
(@POOLID,24,7153,7154,7155,34,33,33,3811.52,-1148.61,237.25,6.26,1,1),
(@POOLID,25,7153,7154,7155,34,33,33,3566.68,-1148.24,220.05,0.65,1,1),
(@POOLID,26,7153,7154,7155,34,33,33,3580.56,-1118.58,219.95,4.01,1,1),
(@POOLID,27,7153,7154,7155,34,33,33,3583.93,-1076.81,221.25,4.66,1,1),
(@POOLID,28,7153,7154,7155,34,33,33,3590.91,-1169.25,219.69,3.02,1,1),
(@POOLID,29,7153,7154,7155,34,33,33,3679.75,-1019.22,224.18,3.54,1,1),
– Pathed movement
(@POOLID,30,7153,7154,7155,34,33,33,2,3577.37,-1073.75,221.76,3.99,1,2),
(@POOLID,31,7153,7154,7155,34,33,33,3,3651.72,-1166.22,209,6.22,1,3),
(@POOLID,32,7153,7154,7155,34,33,33,4,3719.67,-1133.48,209.19,4.15,1,4),
(@POOLID,33,7153,7154,7155,34,33,33,5,3709.65,-1156.61,207.9,4.72,1,5);

DELETE FROM creature_pool_setup WHERE poolId=@POOLID;
INSERT INTO creature_pool_setup (poolId,setupId,scriptId,mountId,bytes1,bytes2,emoteState,auras,modelId,equipmentId,npcFlags,unitFlags,dynamicFlags) VALUES
(@POOLID,1,0,0,0,257,0,‘12345’,0,0,0,0,0);

– Feralas Oily Blackmouth & Firefin Snapper School spawns
SET @POOLID := 1; – pool id
SET @MINACTIVE := 1; – Min number of active spawns
SET @MAXACTIVE := 9; – Max number of active spawns
SET @SPAWNTIMER := 300; – Respawn timer in seconds
– Spawning template
DELETE FROM gameobject_pools WHERE id=@POOLID;
INSERT INTO gameobject_pools (mapId,zoneId,poolId,spawnMask,phaseId,PhaseGroup,minLimit,maxLimit,spawnTimer,description) VALUES
(1,357,@POOLID,1,0,0,@MINACTIVE,@MAXACTIVE,@SPAWNTIMER, ‘Feralas Oily Blackmouth & Firefin Snapper School pool’);
– Spawnpoint pool
DELETE FROM gameobject_pool_spawns WHERE pool=@POOLID;
INSERT INTO gameobject_pool_spawns (poolId,spawnPoint,objectId1,objectId2,objectId3,chance1,chance2,chance3,positionX,positionY,positionZ,orientation,rotation0,rotation1,rotation2,rotation3,animprogress,state) VALUES
(@POOLID,0,180682,0,0,100,0,0,-4357,2360.859,0,3.141593,0,0,-1,0,255,1),
(@POOLID,1,180682,0,0,100,0,0,-4074.941,2720.83,0,3.141593,0,0,-1,0,255,1),
(@POOLID,2,180682,0,0,100,0,0,-5020.641,2238.609,0,3.141593,0,0,-1,0,255,1),
(@POOLID,3,180682,0,0,100,0,0,-5238.16,2207.62,0,3.141593,0,0,-1,0,255,1),
(@POOLID,4,180682,0,0,100,0,0,-4402.471,-659.9271,0,3.141593,0,0,0,1,255,1),
(@POOLID,5,180682,0,0,100,0,0,-4130.021,2643.98,0,3.141593,0,0,0,1,255,1),
(@POOLID,6,180682,0,0,100,0,0,-5055.609,2239.561,0,3.141593,0,0,0,1,255,1),
(@POOLID,7,180682,0,0,100,0,0,-5366.319,2293.99,0,3.141593,0,0,0,1,255,1),
(@POOLID,8,180682,0,0,100,0,0,-4384.059,-624.8802,0,3.141593,0,0,0,1,255,1),
(@POOLID,9,180682,0,0,100,0,0,-4163.471,2580.9,0,3.141593,0,0,-1,0,255,1),
(@POOLID,10,180682,0,0,100,0,0,-4909.471,2220.13,0,3.141593,0,0,-1,0,255,1),
(@POOLID,11,180682,0,0,100,0,0,-5319.78,2257.351,0,3.141593,0,0,-1,0,255,1),
(@POOLID,12,180682,0,0,100,0,0,-4042.95,2946.21,0,3.141593,0,0,-1,0,255,1),
(@POOLID,13,180682,0,0,100,0,0,-4187.559,2547.74,0,3.141593,0,0,0,1,255,1),
(@POOLID,14,180683,0,0,100,0,0,-4043.78,2915.47,0,3.141593,0,0,-1,0,255,1),
(@POOLID,15,180683,0,0,100,0,0,-4225.641,2452.359,0,3.141593,0,0,-1,0,255,1),
(@POOLID,16,180683,0,0,100,0,0,-5402.41,2329.42,0,3.141593,0,0,-1,0,255,1),
(@POOLID,17,180683,0,0,100,0,0,-4224.76,2412.609,0,3.141593,0,0,0,1,255,1),
(@POOLID,18,180683,0,0,100,0,0,-4466.03,-709.7257,0,3.141593,0,0,0,1,255,1),
(@POOLID,19,180683,0,0,100,0,0,-4531.76,2336.04,0,3.141593,0,0,0,1,255,1),
(@POOLID,20,180683,0,0,100,0,0,-4743.471,2353.109,0,3.141593,0,0,0,1,255,1),
(@POOLID,21,180683,0,0,100,0,0,-4011.441,2773.93,0,3.141593,0,0,0,1,255,1),
(@POOLID,22,180683,0,0,100,0,0,-4871.28,2154.109,0,3.141593,0,0,0,1,255,1),
(@POOLID,23,180683,0,0,100,0,0,-4438.04,-709.7847,0,3.141593,0,0,0,1,255,1),
(@POOLID,24,180683,0,0,100,0,0,-4110.021,2675.07,0,3.141593,0,0,-1,0,255,1),
(@POOLID,25,180683,0,0,100,0,0,-5273.721,2249.891,0,3.141593,0,0,-1,0,255,1),
(@POOLID,26,180683,0,0,100,0,0,-4561.441,2324.67,0,3.141593,0,0,-1,0,255,1),
(@POOLID,27,180683,0,0,100,0,0,-4405.42,2366.851,0,3.141593,0,0,-1,0,255,1),
(@POOLID+28,180683,0,0,100,0,0,-4187.559,2547.74,0,3.141593,0,0,-1,0,255,1);[/CODE]

Malcrom · September 16, 2015, 3:27pm

Final from me:

[CODE]-- Creature Spawning template
DELETE FROM creature_pool WHERE poolId BETWEEN XXXXX AND XXXXX;
INSERT INTO creature_pool (poolId,zoneId,mapId,spawnMask,phaseId,PhaseGroup,minLimit,maxLimit,spawnTimer,corpseTimer,description) VALUES
– Creature Spawn pool
DELETE FROM creature_pool_spawn WHERE poolId BETWEEN XXXXX AND XXXXX;
INSERT INTO creature_pool_spawn (poolId,zoneId,spawnId,creatureId,chance,description) VALUES
– Creature spawnpoints
DELETE FROM creature_pool_spawnpoint WHERE poolId= XXXXX AND spawnPoint BETWEEN XX AND XX;
INSERT INTO creature_pool_spawnpoint (poolId,zoneId,spawnPoint,spawnId,setupId,positionX,positionY,positionZ,orientation) VALUES
– Creature spawnpoint setup
DELETE FROM creature_spawnpoint_setup WHERE poolId=XXXXX AND setupId=XX;
INSERT INTO creature_spawnpoint_setup (poolId,zoneId,setupId,scriptId,mountId,bytes1,bytes2,emoteState,auras,modelId,equipmentId,npcFlags,unitFlags,dynamicFlags,description) VALUES

– Gameobject Spawning template
DELETE FROM gameobject_pool WHERE poolId BETWEEN XXXXX AND XXXXX;
INSERT INTO gameobject_pool (poolId,zoneId,mapId,spawnMask,phaseId,PhaseGroup,minLimit,maxLimit,spawnTimer,description) VALUES
– Gameobject Spawn pool
DELETE FROM gameobject_pool_spawn WHERE poolId BETWEEN XXXXX AND XXXXX;
INSERT INTO gameobject_pool_spawn (poolId,zoneId,spawnId,objectId,chance,description) VALUES
– Gameobject spawnpoints pool
DELETE FROM gameobject_pool_spawnpoint WHERE poolId= XXXXX AND spawnPoint BETWEEN XX AND XX;
INSERT INTO gameobject_pool_spawnpoint (poolId,zoneId,spawnPoint,spawnId,positionX,positionY,positionZ,orientation,rotation0,rotation1,rotation2,rotation3,animprogress,state) VALUES[/CODE]