MaNGOS database class and leftJoins ?

[Auntie Mangos]

Hey,

I was wondering if it's possible to see an example of how to handle a one-to-many leftjoin with the MaNGOS database implementation. I plan on rewriting the -External-Mail-from-SQL"]external mail (Atleast partially) to support up to 15 items (The max I believe) per mail. However I don't want to create 30 columns (One for ItemID and one for Count) instead I want to use a 2nd table containing the itemId and ItemCount.

So does anyone have an example of this ?

Thanks for your time, Xeross

[darkstalker]

something like this:

DROP TABLE IF EXISTS `mail_external`;
CREATE TABLE `mail_external` (
 `id` int(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `receiver` bigint(20) UNSIGNED NOT NULL,
 `subject` varchar(200) DEFAULT 'Support Message',
 `message` varchar(500) DEFAULT 'Support Message',
 `money` int(20) UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mail_external_items`;
CREATE TABLE `mail_external_items` (
 `id` int(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `item` int(20) UNSIGNED NOT NULL DEFAULT '0',
 `item_count` int(20) UNSIGNED NOT NULL DEFAULT '1',
 `mail_id` int(20) UNSIGNED NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

after you read the "id" from mail_external:

SELECT id, item, item_count WHERE mail_id = <id_here>

if you do a left-join it will return one row for each item, that means N mails with one item inside

[xeross155]

The query ain't the problem, how will I parse the result inside C++ all leftjoin examples I found in the code do leftjoin SOMETHING as SOMETHING and then refer to it as a field, but that way I'd have 30 additional fields, I'd rather leftjoin everything related to one message and loop over it. Only way I can think of now is using a seperate query for the items.

[balrok]

in BattleGroundMgr::LoadBattleEventIndexes i used a full outer join this is, cause mysql doesn't support it, a full left outer and a full right outer join..

maybe that can help you..

but i don't realy understand what you're asking for..

you want to know what happens with those variables, which aren't in the db?

i did checks like:SELECT bla.a, bla2.a FROM bla LEFT OUTER JOIN bla2 ON bla.a=bla2.a

and then

check in the code if bla.a equals bla2.a.. if not, one of those must be NULL.. but maybe in this implementation we also can check directly for NULL.. i'm not very sure..

ah maybe i understand you now..

so you are asking how to do the 1->* relation

your result will look like this:

mail | item

1 | 123

1 | 456

2 | NULL

then just use and std::map<uint32 (mailid),std::vector<uint32(itemid)> > mailmap

add your mails to this map and then push all items with the same mailid to the mailmap-vector

and your select might look like this

and checking if an item exists, you select the mailid once from "mails" table and once from "mail_items" table.. and if mail_items is NULL the mails.mailid != mail_items.mailid

i hope i wasn't to confusing and could answer your problem somehow..

[xeross155]

Basically if I do a normal left join I'd get something like

Mail - ID: 1 - Mail Item 1
            - Mail Item 2
            - Mail Item 3
Mail - ID: 2 - Mail Item 4
            - Mail Item 5
            - Mail Item 6

However I wonder how I could loop over the mail items then, adding them to the mail template ain't a problem.

[DasBlub]

if you want that, you can get a table like 'mail_id, mail_item_id' with a query like this (just pseudo code, you need to adopt it to the mangos db):

SELECT m.id
     ,mi.id
FROM   mail      m
     ,mail_item mi
WHERE  mi.mail_id = m.id
;

[balrok]

Basically if I do a normal left join I'd get something like

Mail - ID: 1 - Mail Item 1
            - Mail Item 2
            - Mail Item 3
Mail - ID: 2 - Mail Item 4
            - Mail Item 5
            - Mail Item 6

However I wonder how I could loop over the mail items then, adding them to the mail template ain't a problem.

no you will get something like

Mail - ID: 1 - Mail Item 1
Mail - ID: 1 - Mail Item 2
Mail - ID: 1 - Mail Item 3
Mail - ID: 2 - Mail Item 4
Mail - ID: 2 - Mail Item 5
Mail - ID: 2 - Mail Item 6

and it looks more like a semijoin for me.. or why you want to select mailitems, which don't have a mailid assigned to it?

and the post from dasblub is a semijoin..

[FragFrog]

Basically if I do a normal left join I'd get something like

Mail - ID: 1 - Mail Item 1
            - Mail Item 2
            - Mail Item 3
Mail - ID: 2 - Mail Item 4
            - Mail Item 5
            - Mail Item 6

However I wonder how I could loop over the mail items then, adding them to the mail template ain't a problem.

You could use a subquery to select the mail items seperately - something like

SELECT    `mail_item`.`id`
FROM      `mail_item`
WHERE     `mail_item`.`mail_id`
 IN (    SELECT    `id` 
         FROM      `mail` 
         WHERE     `mail`.`account` = %s)

And link them to the mail's themselfs manually in code. A lot faster than performing a seperate query for each mail, but probably still slower than just doing an outer left join as balrok suggested (not to mention subqueries are generally best avoided).

What you must realise is that MySQL (to my knowledge anyway) cannot return multidimensional resultsets, at least not with more than two dimensions. You will never be able to get a list of mail's with a seperate array of mail-items' as part of the resultset.

[DasBlub]

SELECT    `mail_item`.`id`
FROM      `mail_item`
WHERE     `mail_item`.`mail_id`
 IN (    SELECT    `id` 
         FROM      `mail` 
         WHERE     `mail`.`account` = %s)

sub-selects are pretty bad for the performance

SELECT mi.mail_id
     ,mi.id
FROM   mail_item mi
     ,mail      m
WHERE  mi.mail_id   = m.id
 AND  mail.account = "<whatever account>"
;

[FragFrog]

sub-selects are pretty bad for the performance

I totally agree, hence my remark they are best avoided. TS seemed to want to select just the mail-items without doing a join on the mail table though, at least if I understood him correctly, only way to do that is with a subquery (or with a seperate query for each mail).

[DasBlub]

uhm... no o_O

just take my above query and remove the mail_id from the select:

SELECT mi.id
FROM   mail_item mi
     ,mail      m
WHERE  mi.mail_id   = m.id
 AND  mail.account = "<whatever account>"
;

[FragFrog]

uhm... no o_O

just take my above query and remove the mail_id from the select:

That's still a join Just do a describe, you'll see it still refs both tables.

[freghar]

That's still a join Just do a describe, you'll see it still refs both tables.

AFAIK nested SELECT and a JOIN is something a bit different - looks like the nested SELECT is done each time for each column selected via FROM - at least from the time estimate.

And the JOIN performance issue isn't as obvious as it might look like. Today's storage engines are more optimized for using JOINs, they cache references. Think about the fragmentation - rows aren't stored on the disk as they appear in your DB browser, reading one superlong row (which is done each time, even if you SELECT only one column) from (possibly) many fragments and wasting many pages of cache can be slower than using one or two JOINs, especially on tables using proper indexes.

Moreover, the fragmentation occur both on file level and on DB level (ie. longtext dynamic size columns).

See http://getmangos.eu/wiki/Development_ideas - database section ... doing too many JOINs isn't good, keeping all in one table is not good as well.

[xeross155]

I now have the following SQL query

Code sql:

SELECT e.id, e.receiver, e.subject, e.message, e.money, i.item, i.count FROM mail_external e LEFT JOIN mail_external_items i ON e.id = i.mail_id;

Which yields the following result

Now I was wondering how I should iterate over it, should I check if the id is still the same and if so add the item to the current mail and if not send the mail and create a new one ?

[balrok]

i would do it with this (warning mostly pseudocode):

m_mailItemMap.clear();

QueryResult *result = WorldDatabase.PQuery(

"SELECT e.id,i.mail_id, e.receiver, e.subject, e.message, e.money, i.item, i.count FROM mail_external e LEFT JOIN mail_external_items i ON e.id = i.mail_id order by e.id");

do

{

Field *fields = result->Fetch();

uint32 mailid = fields[0].GetUInt32();

uint32 mailid_items = fields[1].getuit32();

if (mailid == mailid_items)

{

// this mail has items add it to the map

// in real code you need to first check if m_mailItemMap[mailid] exists, if not create a new vector..

m_mailItemMap[mailid].push_back(your_var_containing_item_information);

}

...

the m_mailItemMap must be defined like this

std::map<uint32, std::vector<uint32> > MailItemMapType;

..

and about the nested select from dasblub.. i think in mysqldocs it's also explained, that this is totaly aequivalent to semi-join..

semi join will be good if you just want those mails, which actually have at least mailitem, but would be bad if you want mails with or without items.. for the later case a left-join is realy what he needs..

and still i'm not totaly sure, if i understand your question.. just look at the code how to handle mysqlresults.. or?

ok after this code-example i now see the problem and this post is mostly useless..

[xeross155]

Well I kind of wrote code that just checks if we're still at the same mail by comparing the ID with the last ID I think the code should work but I got a few errors I can't seem to figure out.

void WorldSession::SendExternalMails()
{
   sLog.outString("EXTERNAL MAIL> Sending mails in queue...");
   QueryResult *result = CharacterDatabase.Query("SELECT e.id, e.receiver, e.subject, e.message, e.money, i.item, i.count FROM mail_external e LEFT JOIN mail_external_items i ON e.id = i.mail_id;");
   if(!result)
   {
       sLog.outString("EXTERNAL MAIL> No mails in queue...");
       delete result;
       return;
   }
   else
   {
       uint32 last_id = 0;
       do
       {
           Field *fields = result->Fetch();
           uint32 id = fields[0].GetUInt32();
           uint64 receiver_guid = fields[1].GetUInt64();
           std::string subject = fields[2].GetString();
           std::string message = fields[3].GetString();
           uint32 money = fields[4].GetUInt32();
           uint32 itemId = fields[5].GetUInt32();
           uint32 itemCount = fields[6].GetUInt32();
           MailDraft mail;

           Player *receiver = sObjectMgr.GetPlayer( receiver_guid );

           if(receiver)
           {
               sLog.outString("EXTERNAL MAIL> Sending mail to character with guid %s", receiver_guid, itemId);
               if(id != last_id)
               {
                   if(mail)
                   {
                       mail.SendMailTo( MailReceiver(receiver_guid), MailSender(MAIL_NORMAL, 0, MAIL_STATIONERY_GM), MAIL_CHECK_MASK_RETURNED);
                       delete mail;
                       CharacterDatabase.PExecute("DELETE mail_external AS e, mail_external_items AS i FROM mail_external AS e, mail_external_items AS i WHERE i.mail_id = e.id AND e.id = %u;", id);
                       sLog.outString("EXTERNAL MAIL> Mail sent");
                   }

                   uint32 itemTextId = !message.empty() ? sObjectMgr.CreateItemText( message ) : 0;
                   mail = MailDraft( subject, itemTextId );
                   if(money)
                   {
                       sLog.outString("EXTERNAL MAIL> Adding money");
                       mail.AddMoney(money);
                   }
               }

               if (itemId)
               {
                   sLog.outString("EXTERNAL MAIL> Adding %u of item with id %u", itemCount, itemId);
                   Item* mailItem = Item::CreateItem( itemId, itemCount, receiver );
                   mailItem->SaveToDB();                    
                   mail.AddItem(mailItem);
               }

               last_id = id;
           }
           else
               sLog.outString("EXTERNAL MAIL> Character with guid %u doesn't exist, skipping mail!", receiver_guid);

       }
       while( result->NextRow() );
   }

   delete result;
   sLog.outString("EXTERNAL MAIL> All Mails Sent...");    
}

I can't seem to declare mail as an uninitialized MailDraft so I got the following errors.

'MailDraft' : no appropriate default constructor available f:\\Developing\\LecoWoW\\Server\\Source\\mangos\\src\\game\\Mail.cpp

conditional expression of type 'MailDraft' is illegal f:\\Developing\\LecoWoW\\Server\\Source\\mangos\\src\\game\\Mail.cpp

'delete' : cannot convert from 'MailDraft' to 'void *' f:\\Developing\\LecoWoW\\Server\\Source\\mangos\\src\\game\\Mail.cpp

[Revils]

Well I kind of wrote code that just checks if we're still at the same mail by comparing the ID with the last ID

you're assuming that the DB will return the resulset already ordered while it depends by how the index on the fileds are defined and I'm not positively sure that mysql on a query join always orders the result using the primary keys.

On other hand adding a sort, on a large resulset, will impact on performance negatively.

[xeross155]

Ye I forgot the sort stuff added it now, Just have the problem that the shit crashes once I add the item to the mail.

void WorldSession::SendExternalMails()
{
   sLog.outString("EXTERNAL MAIL> Sending mails in queue...");
   QueryResult *result = CharacterDatabase.Query("SELECT e.id, e.receiver, e.subject, e.message, e.money, i.item, i.count FROM mail_external e LEFT JOIN mail_external_items i ON e.id = i.mail_id ORDER BY e.id;");
   if(!result)
   {
       sLog.outString("EXTERNAL MAIL> No mails in queue...");
       delete result;
       return;
   }
   else
   {
       uint32 last_id = 0;
       MailDraft* mail = NULL;
       uint32 last_receiver_guid;
       do
       {
           Field *fields = result->Fetch();
           uint32 id = fields[0].GetUInt32();
           uint64 receiver_guid = fields[1].GetUInt64();
           std::string subject = fields[2].GetString();
           std::string message = fields[3].GetString();
           uint32 money = fields[4].GetUInt32();
           uint32 itemId = fields[5].GetUInt32();
           uint32 itemCount = fields[6].GetUInt32();

           Player *receiver = sObjectMgr.GetPlayer( receiver_guid );

           if (id != last_id)
           {
               // send last mail
               if (last_id != 0)
               {
                   sLog.outString("EXTERNAL MAIL> Sending mail to character with guid %d", last_receiver_guid);
                   mail->SendMailTo( MailReceiver(last_receiver_guid), MailSender(MAIL_NORMAL, 0, MAIL_STATIONERY_GM), MAIL_CHECK_MASK_RETURNED);
                   delete mail;
                   CharacterDatabase.PExecute("DELETE mail_external AS e, mail_external_items AS i FROM mail_external AS e, mail_external_items AS i WHERE i.mail_id = e.id AND e.id = %u;", last_id);
                   sLog.outString("EXTERNAL MAIL> Mail sent");
               }

               // create new mail
               uint32 itemTextId = !message.empty() ? sObjectMgr.CreateItemText( message ) : 0;
               mail = &MailDraft( subject, itemTextId );
               if(money)
               {
                   sLog.outString("EXTERNAL MAIL> Adding money");
                   mail->AddMoney(money);
               }
           }

           if (itemId)
           {
               sLog.outString("EXTERNAL MAIL> Adding %u of item with id %u", itemCount, itemId);
               Item* mailItem = Item::CreateItem( itemId, itemCount, receiver );
               mailItem->SaveToDB();
               sLog.outDebug("EXTERNAL MAIL> Item lowGuid = %u", mailItem->GetGUIDLow());
               mail->AddItem(mailItem);
           }

           last_id = id;
           last_receiver_guid = receiver_guid;

       }
       while( result->NextRow() );
       // we only send a mail when mail_id!=last_mail_id, so we need to send the very last mail here:

       if (last_id != 0)
       {
           // send last mail
           sLog.outString("EXTERNAL MAIL> Sending mail to character with guid %d", last_receiver_guid);

           mail->SendMailTo( MailReceiver(last_receiver_guid), MailSender(MAIL_NORMAL, 0, MAIL_STATIONERY_GM), MAIL_CHECK_MASK_RETURNED);
           delete mail;
           CharacterDatabase.PExecute("DELETE mail_external AS e, mail_external_items AS i FROM mail_external AS e, mail_external_items AS i WHERE i.mail_id = e.id AND e.id = %u;", last_id);
           sLog.outString("EXTERNAL MAIL> Mail sent");
       }
   }

   delete result;
   sLog.outString("EXTERNAL MAIL> All Mails Sent...");    
}

Stack overflow on mail->AddItem(mailItem);

[darkstalker]

i think its better to just make 2 separate queries, decoding a join result is too hacky imho

maybe just adding a "items" longtext field would be easier, decode it using a std::istringstream later

[xeross155]

It's properly working this way just the mail->AddItem is crashing (Stack overflow).

Or in other words it crashes after the query

Code sql:

[16 ms] SQL: INSERT INTO item_instance (guid,owner_guid,DATA) VALUES (41,0,'41 1073741824 3 35 1065353216 0 0 0 0 0 0 0

0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 25 25 0 ' )

Weird, it thinks owner_guid = 0; Lemme try and fix that.

[xeross155]

I did it, it's working: http://github.com/xeross/mangos/commit/5e4351fcb07dd35fb0cebafe6377704dfed59a99 thanks Lynx3d