[Discussion] Changes to db-scripts and condition system

[Auntie Mangos]

Hi guys.

I have a few changes for the db-script system and for the condition system in mind.

I would appreciate any input as well as some implementation work!

Db-script system

* First Step (Generic cleanup and rewrite, in Rev 11940)

* Second Step

Improve how db-scripts are called:

While looking into them I noticed that iE with the transport-scripts (yes, such exist!) they are only called for the transports and not for a passenger (if unique this might have been an idea)

Similar problems might exist

Maybe add a few new scripts features:

<Brainstorming>

areatrigger_scripts

gameobject_template_scripts, which are linked by entry and no more guid

creature_just_died_scripts (actually, for them it would be cool to have a sub-index to be able to script aggro, evade, death) - but at least to be able to script events on npc-death would be likely a very nice tool to do some fance things that would require some SD2 handling otherwise.

< / Brainstorming>

* Third Step

Add possibility for conditions to db-scripts. - see also http://getmangos.eu/community/topic/17262/feature-request-check-conditions-in-db-script/

This would improve their abilities enourmously!

However this can only be done if the conditions system would be changed that it was easier to get conditions assigned to other places than now.

Conditions System

See http://getmangos.eu/community/topic/18031/new-conditions-system/

Below is some alternative system, that uses a more list like approach

Advantages:

* Less space in the database

* More powerfull concept

* Easier to add conditions to other places

Disadvantes:

* Harder to read (always require an additional join - or a set of joins)

* Harder to port entries from tables with conditions between different DB-projects

* To actually convert current condition entries to `conditions` format might not be easy

(And depending on how well this can be done, the actual way of pushing the change finally depends)

Changes in code would be in many places, but should be no very big deal.

[VladimirMangos]

nice idea)

[tyrael]

good luck my dear friend... can't wait to see this step by step in repo

[Schmoozerd]

And some more Updates:

Condition system

http://paste2.org/p/1963271

This system allows to add conditions to a `conditions` table.

These conditions can link to each other.

Negative link-entry is interpreted as "OR", positive link-entry is interpreted as "AND"

The actual list of conditions and its links is treated in the form of a http://en.wikipedia.org/wiki/Disjunctive_normal_form

Currently added support for gossip_menu_option - the rest is rather time consuming.

Troublesome Good ideas for converting current conditions to the new condition system. If possible MySQL only solutions, but it might be that this is impossible.

Edit: Might be an idea to create a small GUI tool for this system (display/ modify/ create conditions) for DB guys

[lillecarl]

The suggested layout for `conditions` would be

(entry, type, val1, val2, linkedEntry)

Negative link-entry is interpreted as "OR", positive link-entry is interpreted as "AND"

Very good idea! Myself would have created another column for that (doh), i didnt pick everything up, but i think the idea is great! But yes indeed conversion from the current system would be "hard" id say it would be easier to "redo" everything

How will it work with multiple and's and or's? As example

If 1=1 || 2=2 && 3=3

Would that be possible etc..?

[Schmoozerd]

" The actual list of conditions and its links is treated in the form of a http://en.wikipedia.org/wiki/Disjunctive_normal_form "

so your example 1=1 || 2=2 && 3=3

would be treated as (1=1) || (2=2 && 3=3)

Edit: After some thinking, he use (adding new conditions) of this system for people is really difficult.

A program interface (probably best GUI) would be really helpfull.

I would help with the required algorithms to check if a "conditon-list" is already in the conditions table, and also with optimezed ways to insert a new "conditon-list"

[lillecarl]

Seems good enough:) Keep it up, but take your time! We dont wanna burn you out! Tell us if you need any help:)

Edit: Did i get this correct? In the conditions table all conditions are stored and linked? So all actual condition data is stored in the conditions table? And then you "link" To the actual conditions via lets say gossip-options? For a start, just creste a wiki article(if im right about this)

[Schmoozerd]

Yes - I need somebody to write a GUI tool for this system

Atm I estime that the time for checking if a conditions-list is already in the conditions table, will be n^(m!), where n=lenght of conditions, and m=length of list.

And this is nearly impossible to do manually for longer conditions-lists.

Very complex matter, but most likely also interesting for ppl interested in computer science

[lillecarl]

Im sorry to tell, but then you might have to wait for awhile, i do not know anything about graphics etc... So i cannot support with the applicatio there. Anyways, im going to check if i can figure some smart mysql way out^^, It might be doable with temporary tables?

[Schmoozerd]

Sure, but you might require a finite amount of them, whereas the number is determined by the input data

[lillecarl]

Could you tell me all places in the DB where the conditions are stored at the moment? Would be quite useful (if im not out sailing atm )

EDIT: If we just look at loot_template and gossip_menu_option, we see that their "condition lists" are not equal. If we could merge those lists together to one contition list, and extend the conditions table to 3 rows as gossip_menu_option has it. I think its doable in SQL by someone a lil smarter then me.

Lets say we create a table called gossip_menu_option_temporary with the same condition layout as gossip_option and nothing else then that. Then we use SQL update commands to "port" the old "condition check id's" to new ones just using something like ** update temptablewhatever set cond_1 = 1 where cond_1 = 2 ** For all the different types of conditions in the gossip_option table. I might just be completely wrong tho

[Schmoozerd]

ATM, conditions are stored in columns of the style

(condtion-type, value-1, value-2)

for *loot_template this is one such 3-column, for gossip_menu it two such 3-column, and for gossip_menu_option it is three such 3-column.

They all will be replaced by one column named "condition_id"

This new condition_id points to an condition-entry of the (new) `conditions` table.

The conditions table has the format

(condition-entry, condition-type, value-1, value-2, link-entry)

And it is possible to link multiple lines of `conditions` together by using the link-entry (depending on sign AND or OR link)

From now on I will write big letters (A, B, C) instead of (condition-type, value-1, value-2).

then a logical line in conditions would be entry, A, link-entry.

So for example, assume you had

(1, A, 0)

(2, B, 1)

(3, C, -2)

(4, D, 3)

as entry in conditions.

Then if gossip_menu_option.condition_id was 4, this menu would point to conditions 4, which evaluates as

(D && C) || (B && A)

Btw - if you want to do some work related to this patch (which would be very welcome).

In the code I only added actual support for the gossip_menu_option table.

You could add for other tables as well (should be relatively straight forward to do so)

I can push the stuff with some logical commits to my repo over the day, so it should be easier to understand which part of what is required for what.

Edit: https://github.com/Schmoozerd/mangos/commits/condition_system

[Schmoozerd]

About Your Edit:

No, you are right, but this would be relatively inefficient, because many conditions would be in `conditions` table multiple-times.

The goal was to do it efficient, and this is the difficulty

[lillecarl]

Oh yeah sorry, i just woke up.. Hmm never mind lets not talk about mysql queries solving this problem really... But if you want this tool to ever be written, you should indeed "prepare" the unknown writer for it as much as possible. Anyways, thank you for explaining so well! Hmm.... If you know exactly how the conversion can happen, then you can write a tiny CLI c++ tool only? Because i guess you have more knowledge about how it works that way =) But yeah i see now, each of the gossip_menu_option columns act as one row in the conditions table =) I cannot say more then good luck then So good luck!

[lillecarl]

Ah, i get this now, lets say we create a C++ array and map it so it would look like the conditions table.

then we fetch each and every current condition into the map, and with some einstein solution check for duplicates, remove all duplicates except one. Then we redirect all other entries to the only one left? and then when we have ran this loop for each and every row until there are no duplicates left, we make a loop that writes SQL queries into a text file? Hmmrmmm that might "not be as hard" as i trought (Far out of my knowledge but not that hard for someone with "real" programming skills, i guess)

[Schmoozerd]

pushed the stuff in a rebased version to my github repo:

https://github.com/Schmoozerd/mangos/commits/condition_system

Should be easy to ie add other tables, clean a bit stuff, etc

For the initial fill I have already started to write a python script, see http://paste2.org/p/1964566

But I have never used python before, so this is not running yet, and only very basic.

But the initial fill is not as complicated as the later check for already existing condition-lists. see:

if you want to know if (A && B) || C is included in the conditions system, you must check for

(A && B) || C or (B && A) || C or C || (A && B) or C || (B && A)

rougly the number of required possible chains is m! (faculty of m, where m is the length of the conditions list)

Even more complicated it gets if you want to insert a new conditions list, because you might want to check, if already parts of this list are inside the system (and optimize for this)

[lillecarl]

Oh yeah i forgot about this. Maby if the "after part" should be processed by mangosd on each startup? It would indeed take alot of time, so maby putting in a configuration called "optimizedb" or something, because i pretty much dislike the idea of needing a external tool. Wouldnt that be the best solution you think? Then we might be able to put other optimizations to the mangos startup (in this config aswell) such as GUID reordering etc.. (When someone feels for doing that) I can only see pros about this, since the code wont really depend on anything else it will be easily maintained, and since it is a config if you want to database optimize on startup, you wont loose any speed to it really. Im just trying to help you brainstorm out a good idea, im sadly not good enough in programming to help you really, but i think writing it into mangos would be kick-***

[VladimirMangos]

Compress condition table by remove duplicates can be contra-productive infact. This make very hard fix condition because will be unclear is it shared or not with other condition as part its. At condition loading it registered and lookup by condition data so it auto merge with same data to single record.

[Schmoozerd]

Updated the helper tool for conversion.

Seems to be fully working.

http://paste2.org/p/1965468

A whole dump for all conditions (for both udb and ytdb) here: http://paste2.org/p/1965470

@Vladimir yes indeed, it can be expected that over time this table will be filled with bad data - but I don't expect this to happen very fast.

But only if there will be a neat tool for optimized inserting..

Edit: Well, simple conditions are easy to insert, so there should be for most case no real requirement.

But I think that it is well worth the additional effort when changing/ inserting new conditions because of more power and more flexibility.

[faramir118]

If people don't mind C#(or mono), I can do the tooling for this... probably a bit different from what you expect, but it will work. Can push something tomorrow maybe.

[lillecarl]

faramir, i think C#/Mono would do fine, and if you could write it working well with mono. There would be no problem at all! =)

[stfx]

How about making a php page? This way it doesn't depend on the OS

[TuX]

Guys maybe you should talk to xfurry before start writing.

He mentioned yesterday that he wants to extend the EventAI Tool to support those stuff

GreeZ

ZuSe

PS: stfx if he writes it in C#/Mono it should also work on every OS, though Java might be the better choice.. for PHP you would need a wwwserver

[Schmoozerd]

The converting tool for initial fill is now quite powerfull, even if it still won't give an optimal result.

Version: http://paste2.org/p/1967856

I compared the different databases (zero-db, tbc-db, udb, ytdb).

The results you can see below.

Processing database zero_db (1 vector conditions)

Processing database tbcdb (1 vector conditions)

Processing database udb_clean (1 vector conditions)

Processing database ytdb (1 vector conditions)

Processing database zero_db (2 vector conditions)

Processing database tbcdb (2 vector conditions)

Processing database udb_clean (2 vector conditions)

Processing database ytdb (2 vector conditions)

Processing database zero_db (3 vector conditions)

Processing database tbcdb (3 vector conditions)

Processing database udb_clean (3 vector conditions)

Processing database ytdb (3 vector conditions)

Inserted rows from start 1406

Processed database(s): [['zero_db', 0], ['tbcdb', 1], ['udb_clean', 2], ['ytdb', 2]]

>>>

Processing database zero_db (1 vector conditions)

Processing database zero_db (2 vector conditions)

Processing database zero_db (3 vector conditions)

Inserted 32 rows for database zero_db

Processing database tbcdb (1 vector conditions)

Processing database tbcdb (2 vector conditions)

Processing database tbcdb (3 vector conditions)

Inserted 163 rows for database tbcdb

Processing database udb_clean (1 vector conditions)

Processing database udb_clean (2 vector conditions)

Processing database udb_clean (3 vector conditions)

Inserted 365 rows for database udb_clean

Processing database ytdb (1 vector conditions)

Processing database ytdb (2 vector conditions)

Processing database ytdb (3 vector conditions)

Inserted 846 rows for database ytdb

Processed database(s): [['zero_db', 0], ['tbcdb', 1], ['udb_clean', 2], ['ytdb', 2]]

=> Sum of entries is 32+163+365+846 = 1406 (so no real disadvantage in doing the databases in such an incremental work)

But:

Inserted 1237 rows from start

Processed database(s): [['ytdb', 2], ['udb_clean', 2], ['zero_db', 0], ['tbcdb', 1]]

So depending on order of processing, and how they are processed the size of the initial fill can become very small.

On a large scale it will be impossible to use the udb or ytdb condtitions for tbc-db or zero-db, because some conditions will throw errors (like for non existing items)

So it will become very difficult to create a merged version of the conditions, but there is no big disadvantage to at least built tbcdb's conditions table on the table of zero, and the ones for udb and ytdb on the on tbcdb.

But - actually this is mostly up to the db-guys to decide how they want to approach this, and how they want to work with it.

(For contributors the best clearly would be a conditions table shared between the projects! - but this is much more effort to maintain!)

Up to you guys to decide how to proceed

[faramir118]

Basically, there are 4 parts:

1. Framework which represents conditions.
   These come in the form of methods like HasAura, HasItem, etc
   
2. An expression tree representation that we can store in the database.
   In our case, we probably want SQL insert statements
   
3. Conversion from the former to the latter
   
4. Mangos Core implementation to rebuild and execute the expression tree
   

[h]Database Structure[/h]

Conditions will go in a table, similar to Schmoo's design but with a small difference (my approach sacrifices space for simplicity):

(id, Type, Val1, Val2)

eg:

(0, 1, 1234, 1)

(1, 6, 67, 0)

(2, -2, 0, 1)

(3, 1, 1235, 1)

(4, 6, 469, 0)

(5, -2, 3, 4)

(6, -1, 2, 5)

Where

Type = -2 := AND

Type = -1 := OR

From the rows above, we can build condition 6 by repeated expansion of each expression:

6

2 OR 5

(0 AND 1) OR (3 AND 5)

((Aura 1234 idx 1) AND (Team Horde)) OR ((Aura 1235 idx 1) AND (Team Alliance))

[h]Code Format[/h]

A very expressive, extendible way to represent the above condition expression in C#:

(c.HasAura(1234, 1) && c.IsHorde()) || (c.HasAura(1235, 1) && c.IsAlliance())

[h]Conversion[/h]

Basically, the database developer will just need to write some C# code, and the framework will convert it into a suitable format.

I have pushed the code to http://github.com/faramir118/MangosUtil/ - it works with MonoDevelop and Visual Studio 2010. It demonstrates that complex logic is easily parsed and formatted (currently not the format we need)

For an example, just look at https://github.com/faramir118/MangosUtil/blob/master/MangosUtil/Runner.cs. You can step through with the debugger if you wish to learn how it works.