ArcEmu: Proposal For The Calendar Events - ArcEmu

Jump to content

Toggle shoutbox Lastest Announcements

dfighter  : (07 December 2014 - 12:06 PM) Arcemu is in hibernation mode, please read http://arcemu.org/fo...showtopic=26903
dfighter  : (01 January 2013 - 05:56 PM) Arcemu wishes you all a happy new year!
Hasbro  : (12 September 2012 - 10:01 AM) Please excuse our outage from the web! Our web host had a major malfunction!
dfighter  : (01 September 2012 - 04:05 PM) Since the spam bots just don't want to stop, I've enabled admin verification when registering.
dfighter  : (23 January 2012 - 09:56 PM) Please note that from now on you will need to confirm your email on the wiki in order to edit it!
Hasbro  : (31 December 2011 - 12:50 PM) Happy New Years all!
Navid  : (26 December 2011 - 04:09 AM) Merry Christmas !!!!!! Happy holidays all :)
WAmadeus  : (24 December 2011 - 03:54 PM) Merry Christmas to all!
dfighter  : (24 December 2011 - 11:05 AM) The Arcemu team wishes y'all a Merry Christmukkah!
Hasbro  : (05 October 2011 - 12:53 PM) Looking for web designers for upcoming web related project. If you're interested in designing user interfaces contact me
dfighter  : (02 September 2011 - 03:47 PM) So who here wants vehicles in Arcemu? :P http://arcemu.org/fo...showtopic=25440
Hasbro  : (14 August 2011 - 03:25 PM) Join us on irc, grab an irc client and connect to irc.freenode.net join channel #arcemu /server irc.freenode.net:6667 /join #arcemu
jackpoz  : (03 August 2011 - 05:33 AM) to all Lua Engine (old one) users: please check http://arcemu.org/fo...showtopic=25274
Hasbro  : (20 May 2011 - 05:27 PM) Looking for people experienced with CMake configuration and setup! Contact me asap
Hasbro  : (15 May 2011 - 05:03 PM) ArcEmu is recruiting C++ programmers, contact Hasbro if interested.
paroxysm  : (03 May 2011 - 06:26 PM) Updated luabridge gossip example to describe the whole gossip creation process rather than just how to create menu. Gossip tutorial
paroxysm  : (23 April 2011 - 11:35 AM) Lua writers can refer to the Luabridge Tutorials section in the Wiki to learn how to write gossip code correctly.
Hasbro  : (20 April 2011 - 05:22 PM) Thank you for your continuous contribution of bug reports, we are working on them.
Hasbro  : (17 April 2011 - 03:20 AM) Please consider donating to support our bills. Donations can be sent using PayPal to donations@arcemu.org - Thank you for your support.
paroxysm  : (10 April 2011 - 12:43 AM) Refer to the Luabridge Tutorials section in the Wiki to learn the new syntax of luabridge.
Resize Shouts Area

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Proposal For The Calendar Events

#1 User is offline   Magnifikator 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 398
  • Joined: 05-October 11
  • Gender:Male
  • Location:Switzerland
  • Server OS:Windows

Posted 28 August 2012 - 12:03 PM

Hello

This is a proposal how to implement data for calendar events into the world db. It's a conceptual study and could be coded in different expanding steps.

Actual situation:
If you have calendar depended events like noblegarden or custom events you have to insert manually at the right time by SQL queries all the spawn entries of npcs and gameobejects. At the end of the event you need to delete all these spawn events by SQL queries. Every time you have to restart the core.
There are 3 main disadvantages of this system:
1. You need to manage a set of span ids for the SQL DELETE queries. Sometimes the spawn ids are in a block, f.ex. 300100 till 300200. Sometimes not. That makes it difficult to control which spawn id has to be deleted.
2. With every INSERT and DELETE your internal index counter is increasing. However the counter is very big and you may never will reach the limit in the rest of your life. But still it's not professional.
3. You also have to take care that you don't produce overlapping block of spawn ids.

I would propose as simple change in the database and a simple change in the core that would solve the problems above.

ALTER TABLE creature_spawns 
ADD event_id INT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Calendar event id',
ADD active BOOLEAN NOT NULL DEFAULT '1' COMMENT 'Is this spawn id active',
ADD INDEX ( event_id );

ALTER TABLE gameobject_spawns 
ADD event_id INT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Calendar event id',
ADD active BOOLEAN NOT NULL DEFAULT '1' COMMENT 'Is this spawn id active',
ADD INDEX ( event_id );


These SQL queries adds 2 fields to creature_spawns and gameobject_spawns:
event_id represents an integer for a specific Calendar Event, f.ex. 1=Noblegarden, 2=Brewfest, 3=Own custom event
active represents a flag if this spawn id should be spawned at the start of the core

From now on you can let all the records in the db.
You can add at any time a new spawn id and don't need to take care if the id is in a block of addresses or how to delete it later.
You can active event by a simple SQL query like:
UPDATE creature_spawns SET active=1 WHERE event_id = 2;   -- activate
UPDATE creature_spawns SET active=0 WHERE event_id = 2;   -- deactivate


Of course you still have to restart the core and take care for the event dates yourself.

A next step could be to add this table into the db:
CREATE TABLE IF NOT EXISTS `calendar_schedule` (
  `event` int(10) unsigned NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name of the event',
  `comment` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Personal comments',
  `start` datetime NOT NULL COMMENT 'Date when event is starting',
  `end` datetime NOT NULL COMMENT 'Date when event is finishing',
  KEY `event` (`event`),
  KEY `start` (`start`),
  KEY `end` (`end`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


This is a timetable like that:
event 	name   			comment        start              	end 
1 	Noblegarden Event 	Search all eggs 2012-04-01 00:00:00 	2012-04-10 23:59:59
2 	Attack Stormwind 	Custom Event 	2012-08-07 02:00:00 	2012-08-10 01:00:00
2 	Attack Stormwind 	Custom Event 	2012-09-16 02:00:00 	2012-09-19 01:00:00


Here the core could check at the start if the a set of spawn id should be loaded or not.

All these things don't need a lot of effort and are done by some simple SQL queries at the start of the core.

By implementing these change into the world db you also could reuse this data later. For example if somebody would implement a realtime scheduler for calendar events. But this is not really necessary and can be done later. However these changes would prevent a lot of headaches to manage inserting and deleting of spawn records.
Try out the LoE ArcEmu World database. Please visit dev.LandOfElves.net.

Posted Image


3

#2 User is offline   Ceterrec 

  • Member
  • Pip
  • Group: Members
  • Posts: 54
  • Joined: 16-March 12
  • Gender:Male
  • Location:Germany
  • Server OS:Windows

Posted 28 August 2012 - 12:59 PM

I think that this would be great. You won't have to deal with the database for something simple and essential as calender events. Even better, there would be a very easy way to implement a realtime scheduler if someone would take his time to work on a implementation. This would be a huge plus for ArcEmu.
0

#3 User is offline   Magnifikator 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 398
  • Joined: 05-October 11
  • Gender:Male
  • Location:Switzerland
  • Server OS:Windows

Posted 28 August 2012 - 01:17 PM

And it's easy to implement.

Just change the db structure and in the core I guess you have to change the SQL queries from "SELECT * FROM xxx_spawns" to "SELECT * FROM xxx_spawns WHERE active". That's all. :)

I recommend also to put the scheduler table into the next structure update. Even the core is not regarding it we could already add data in the LoE World database for this table like predefined ids for the official blizzlike events. And also start adding event related stuff (NPC spawns) to the database.

The schedule could be used than already by php or Lua applications, may just only informational.

And after that users can add their custom event stuff.

EDIT:
Theoretically there should be SQL queries you can even join the schedule table with the spawn table.

But I would loop through the schedule table and collect all ids that are valid (in time) into a table.
And than you can apply it to a SQL query like that:
SELECT * FROM xxx_spawns WHERE event_id=0 OR event_id IN (table)
Something like that.

BTW: If you use the schedule table you don't need the field 'active'. Or your let it, f.ex. for some testing purpose.

EDIT2:
Not only theoretically also practically (not tested):
SELECT * FROM xxx_spawns WHERE event_id=0 OR event_id IN ( SELECT event FROM calendar_schedule WHERE ( NOW()>= start AND NOW()<= end ) );
Try out the LoE ArcEmu World database. Please visit dev.LandOfElves.net.

Posted Image


0

#4 User is offline   dfighter 

  • Titles are overrated
  • PipPipPipPipPipPipPipPipPipPip
  • Group: Administrator
  • Posts: 5,189
  • Joined: 14-June 08
  • IRC:dfighter
  • Gender:Male
  • Server OS:Linux

Posted 01 September 2012 - 04:24 PM

The field `active` is redundant ( repeated information ), because from the `event_id` you can already deduce whether the spawns are active or not.
"The demand for free goods is infinite."
0

#5 User is offline   Magnifikator 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 398
  • Joined: 05-October 11
  • Gender:Male
  • Location:Switzerland
  • Server OS:Windows

Posted 02 September 2012 - 05:57 AM

Yes, I know, I thought we could implement it in 2 steps. But now I see it's quite easy to do everything with a single SQL query.

The idea was growing in my mind while writing it. :)
Try out the LoE ArcEmu World database. Please visit dev.LandOfElves.net.

Posted Image


0

#6 User is offline   Zuel 

  • Member
  • Pip
  • Group: Members
  • Posts: 91
  • Joined: 11-November 11
  • Gender:Male
  • Server OS:Windows

Posted 30 September 2012 - 05:00 AM

Yea, if we could get this successful, ArcEmu would get another step closer to victory. Without it doing this manually, we need like a Script to tell it when to do this on the start days and end days. The Start days would spawn them then the after the end days, it would despawn.
0

#7 User is offline   Satanail 

  • Enthusiast
  • PipPipPip
  • Group: Members
  • Posts: 150
  • Joined: 11-March 12
  • Gender:Male
  • Location:Absurdistan
  • Server OS:Windows

Posted 04 July 2013 - 03:57 AM

Hello. I decided to support this idea and give some suggestions. Hope they are of some use:
There are many more things than just spawning and despawning the events coreside. Some npc's are used in many different events, where they need to start different quests, display different gossip menu's and etc. Not to mention that many items (spells used by items) require specific "Holiday" active, so that they can be used. The holiday id's can be found in HolidayNames.dbc.
First the linking to each spawn to specific event:
ALTER TABLE creature_spawns 
ADD event_id INT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Calendar event id',
ADD INDEX ( event_id );

ALTER TABLE gameobject_spawns 
ADD event_id INT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Calendar event id',
ADD INDEX ( event_id );


We don't need the `active` column, because the core can check the if the event is active from the next table:

Event data:
CREATE TABLE `calendar_schedule` (
  `event` int(10) unsigned NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name of the event',
  `comment` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Personal comments',
  `start` datetime NOT NULL COMMENT 'Date when event is starting',
  `end` datetime NOT NULL COMMENT 'Date when event is finishing',
  `holiday_id` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `event` (`event`),
  KEY `start` (`start`),
  KEY `end` (`end`)
);

Here we can add another table - holiday, that will tell the core what holyday/s are currently active.

We also need to change gossip data for some creatures (like the human, orc, goblin and etc. commoners). This can be don with another table:
CREATE TABLE `gossip_event_npc` (
  `entry` int(10) unsigned NOT NULL, -- npc id of the creature
  `text` int(10) unsigned NOT NULL, -- text id that will be shown as gossip text
  `event_id` int(10) unsigned NOT NULL, -- event id
  PRIMARY KEY (`entry`,`event_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The data from this table should override the data from npc_gossip_textid.

And finally, we need to control the quests, added to the questgivers:

CREATE TABLE `event_quests` (
  `quest` int(10) unsigned NOT NULL, -- quest id
  `event_id` int(10) unsigned NOT NULL, -- event id
  PRIMARY KEY (`quest`,`event_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The quest id's here should only be added to quest givers and finishers if the event id is active.

If there is some progress in this direction, contact me and i will redo the events for permanent insert into the database. All i need is a finished database structure.

EDIT:
There are is also creature loot that should only drop during specific events, so there should also be a new table in loot_creatures, that contains the event id the loot can only drop in.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users