Some extra Emails_Get flags

[kbar]

It would be nice if the Emails_Get Lobby2Message had the following input parameters.

// Input parameters
bool unreadEmailsOnly;  //If this is true then it will only return emails that have not been read by the user.
bool emailIdsOnly; //When this is set only the emailIds are filled out and no data is returned.
DataStructures::List<int> emailsToRetrieve; //If this has entries then it will only get the emails in the list, otherwise it will get all emails.

These flags could then be used after receiving a Notification_Emails_Received to just get the email that was received rather than all the emails.

With these flags it allows you to cache email data on disk, request a list of email id's on the server and only download those that the user wants.

At the moment it looks like Emails_Get will download all of the emails, including all binary data, every time it is called.

[Rak'kar]

This change should do it for you
http://raknet.svn.sourceforge.net/viewvc/raknet?view=revision&revision=140

[kbar]

Absolutely incredible! Thanks for adding that so quickly. I can now go full stream ahead with my email system plans for my application. Thanks again!

[kbar]

I am actually thinking of caching emails to an SQLite database on each client and keeping it in sync when a client connects. I thought I would mention it just in case you are thinking of implementing such a system as well. I think it would be a good addition to the lobby.

[Rak'kar]

I wasn't planning to add that. It's a good idea though.

[kbar]

I found a problem with this new method.

   RakNet::Lobby2Message *m = m_pMessageFactory->Alloc(RakNet::L2MID_Emails_Get);
   RakNet::Emails_Get *arg = (RakNet::Emails_Get *) m;
   arg->unreadEmailsOnly = true;
   arg->emailIdsOnly = true;
   m_pLobby->SendMsgAndDealloc(m);

This causes an assertion in Lobby2Server_PGSQL

[Rak'kar]

Sorry, just need some spaces at the end of each line

if (emailIdsOnly)
      {
         result = pgsql->QueryVariadic(
         "SELECT tbl2.emailid_fk from lobby2.users, ( "
         "SELECT tbl1.*, lobby2.emails.creationDate FROM "
         "(SELECT emailId_fk, userMe_fk, userOther_fk, isDeleted FROM lobby2.emailTargets) as tbl1, lobby2.emails "
         "WHERE tbl1.emailId_fk=lobby2.emails.emailId_pk AND tbl1.userMe_fk=%i AND tbl1.isDeleted=FALSE AND tbl1.wasRead=FALSE "
         ") as tbl2 "
         "WHERE userId_pk=tbl2.userother_fk ORDER BY creationDate ASC;"
         , command->callerUserId);
      }
      else
      {
         result = pgsql->QueryVariadic(
         "SELECT handle, tbl2.* from lobby2.users, ("
         "SELECT tbl1.*, lobby2.emails.subject, lobby2.emails.body, lobby2.emails.binaryData, lobby2.emails.creationDate FROM"
         "(SELECT emailId_fk, emailTarget_pk, userMe_fk, userOther_fk, status, wasRead, ISentThisEmail, isDeleted FROM lobby2.emailTargets) as tbl1, lobby2.emails "
         "WHERE tbl1.emailId_fk=lobby2.emails.emailId_pk AND tbl1.userMe_fk=%i AND tbl1.isDeleted=FALSE AND tbl1.wasRead=FALSE"
         ") as tbl2 "
         "WHERE userId_pk=tbl2.userother_fk ORDER BY creationDate ASC;"
         , command->callerUserId);
      }
   }
   else
   {
      if (emailIdsOnly)
      {
         result = pgsql->QueryVariadic(
            "SELECT tbl2.emailid_fk from lobby2.users, ( "
            "SELECT tbl1.*, lobby2.emails.creationDate FROM "
            "(SELECT emailId_fk, userMe_fk, userOther_fk, isDeleted FROM lobby2.emailTargets) as tbl1, lobby2.emails "
            "WHERE tbl1.emailId_fk=lobby2.emails.emailId_pk AND tbl1.userMe_fk=%i AND tbl1.isDeleted=FALSE "
            ") as tbl2 "
            "WHERE userId_pk=tbl2.userother_fk ORDER BY creationDate ASC;"
            , command->callerUserId);
      }
      else
      {
         result = pgsql->QueryVariadic(
            "SELECT handle, tbl2.* from lobby2.users, ("
            "SELECT tbl1.*, lobby2.emails.subject, lobby2.emails.body, lobby2.emails.binaryData, lobby2.emails.creationDate FROM"
            "(SELECT emailId_fk, emailTarget_pk, userMe_fk, userOther_fk, status, wasRead, ISentThisEmail, isDeleted FROM lobby2.emailTargets) as tbl1, lobby2.emails "
            "WHERE tbl1.emailId_fk=lobby2.emails.emailId_pk AND tbl1.userMe_fk=%i AND tbl1.isDeleted=FALSE"
            ") as tbl2 "
            "WHERE userId_pk=tbl2.userother_fk ORDER BY creationDate ASC;"
            , command->callerUserId);
      }

[kbar]

I added that new code and tried it again but now I get the following error:

ERROR:  column tbl1.wasread does not exist
LINE 1: ...l1.userMe_fk=$1::int AND tbl1.isDeleted=FALSE AND tbl1.wasRe...
                                                             ^

This is interesting because when I have that flag set to false (arg->emailIdsOnly = false); )  then this error does not appear, however I don't seem to be able to set the wasread flag or deleted flag. I will explain these a bit...

I am calling the following to set the read flag

Code:

RakNet::Lobby2Message *m = m_pMessageFactory->Alloc(RakNet::L2MID_Emails_SetStatus);
RakNet::Emails_SetStatus *arg = (RakNet::Emails_SetStatus *) m;
arg->emailId = mailID;
arg->isNowMarkedRead = true;
arg->updateMarkedRead = true;
m_pLobby->SendMsgAndDealloc(m);

This always returns SUCCESS. Even if I set this flag more than once. Is that the correct behavior?  But it doesn't seem to respect the flag, when I get the emails again the flag wasReadByMe is always false.


Now for delete I call this code

Code:

RakNet::Lobby2Message *m = m_pMessageFactory->Alloc(RakNet::L2MID_Emails_Delete);
RakNet::Emails_Delete *arg = (RakNet::Emails_Delete *) m;
arg->emailId = emailID;
m_pLobby->SendMsgAndDealloc(m);

This succeeds and if I call it again it says that the email has already been deleted. But when I get the emails again all the deleted emails still come through  (And as a feature request I would like to be able to get a list of the emails that have been deleted so users can undelete them if they want to).

And one last quirk that I am seeing is that I see duplicates of all the emails. I get them twice, right after each other in the

Code:

DataStructures::List<EmailResult> emailResults;

list. EmailA, EmailA, EmailB, EmailB, EmailC, EmailC etc...

[Rak'kar]

I added that new code and tried it again but now I get the following error:

ERROR:  column tbl1.wasread does not exist
LINE 1: ...l1.userMe_fk=$1::int AND tbl1.isDeleted=FALSE AND tbl1.wasRe...

Change the first block to this:

Code:

if (unreadEmailsOnly==true)
{
if (emailIdsOnly)
{
result = pgsql->QueryVariadic(
"SELECT tbl2.emailid_fk from lobby2.users, ( "
"SELECT tbl1.*, lobby2.emails.creationDate FROM "
"(SELECT emailId_fk, userMe_fk, userOther_fk, isDeleted, wasRead FROM lobby2.emailTargets) as tbl1, lobby2.emails "
"WHERE tbl1.emailId_fk=lobby2.emails.emailId_pk AND tbl1.userMe_fk=%i AND tbl1.isDeleted=FALSE AND tbl1.wasRead=FALSE "
") as tbl2 "
"WHERE userId_pk=tbl2.userother_fk ORDER BY creationDate ASC;"
, command->callerUserId);

[Rak'kar]

Regarding L2MID_Emails_SetStatus and L2MID_Emails_Delete, I think this is a bug in Emails_Get_PGSQL. I'm returning emailId_fk when I should be returning emailTarget_pk. In fact I even commented out the right code because I didn't think through why I did that

// 11/4/2010 - I think this was a copy/paste error
      // PostgreSQLInterface::PQGetValueFromBinary(&emailResult.emailID, result, i, "emailTarget_pk");
      PostgreSQLInterface::PQGetValueFromBinary(&emailResult.emailID, result, i, "emailId_fk");

So change it back to emailTarget_pk and let me know if that fixes it for you.

[kbar]

Thanks. Your two fixes plus this one here has solved all my problems so far.

Code:

emailResults.Insert(emailResult, __FILE__, __LINE__ );
}
//emailResults.Insert(emailResult, _FILE_AND_LINE_ );   //THIS WAS THE CAUSE OF THE DOUBLE EMAILS

Cheers,