SQlite and Observer Mode

[ffcmike]

On Classic I have it so that account data is added to an SQLite database on a player's first login, and if they have already logged in it is then updating the existing data.

For example:

PHP Code:

//Called within onActionPlayerOnline from Control-NPC
public function playerLoggedIn(temp.pl){
  if(temp.pl.communityname == NULL){
    temp.cn = temp.pl.account;
  }
  else{
    temp.cn = temp.pl.communityname;
  }
  //The player does not have a flag for being added to the database
  if(!temp.pl.stat_sqlsetup){
    temp.query = "INSERT INTO tPlayer (account, subscription, lastlogin, gelats, communityname) VALUES ('" @ temp.pl.account @ "', '" @ temp.pl.getSubcription() @ "', " @ temp.pl.getLoginTime() @ ", " @ temp.pl.getGelat() @ ", '" @ temp.cn @ "')";
    this.trigger("SQLCommit", temp.query, "PlayerStored", temp.pl);
  }
  else{
    //The player does have a flag for being added to the database
    temp.query = "UPDATE tPlayer SET subscription = '" @ temp.pl.getSubcription() @ "', lastlogin = " @ temp.pl.getLoginTime() @ ", gelats = " @ temp.pl.getGelat() @ ", communityname = '" @ temp.cn @ "' WHERE account = '" @ temp.pl.account @ "'";
    this.trigger("SQLCommit", temp.query);
  }
} 


The class script containing "SQLCommit" is as follows:

PHP Code:

function onSQLCommit(temp.query, temp.triggername, temp.param1, temp.param2, temp.param3){
  temp.request = requestsql(temp.query, true);
  if(!temp.request.completed){
    waitfor(temp.request, "onReceiveData", 5);
  }
  //If something goes wrong, such as a failed constraint, the query is logged and no function is triggered
  if(temp.request.error != NULL){
    savelog2("log_sql.txt", temp.request.error @ " : " @ temp.query);
    echo(temp.request.error);
    return;
  }
  if(temp.triggername != NULL){
    this.trigger(temp.triggername, temp.request, temp.param1, temp.param2, temp.param3);
  } 
} 


So in this case, assuming all goes well, this function would be triggered:

PHP Code:

function onPlayerStored(temp.r, temp.pl){
  temp.pl.stat_sqlsetup = true;
} 


The problem I am having is that a small portion of trial accounts are producing an error of "constraint failed", sometimes the problem is that they are added to the database but somehow the flag has not set. Other times the flag is set but they have not been added to the database, and so it fails whenever they are added to a different table containing their primary key of the first table.

As far as I can tell this seems to co-inside with Observer Mode.

[DustyPorViva]

Ah, there's your problem. Observer mode.

[Mark Sir Link]

what are the column constraints for each field? There have been issues on UN where null community names have had issues so you might be better off evaluating
if(temp.pl.communityname.length() > 0)
instead.

You say you have a log what entries are failing so can you post an example of that

EDIT:

posting your function for Player.getSubcription() and Player.getLoginTime() may help as well, if these are in a class than perhaps this is executing before the player joins the class? Or there is an issue with them joining it at all?

[ffcmike]

One example of an error caused by the account not being added to the database:

INSERT INTO tTask (pid, num, complete) VALUES ((SELECT tPlayer.pid FROM tPlayer WHERE tPlayer.account = 'Graal750778'), 0, 1)

The reason this is occurring is because I have a foreign key referring to tPlayer's primary key.
There is also a unique index on pid + num but this isn't the problem.

One example of an error caused by the account already existing within the database:

INSERT INTO tPlayer (account, subscription, lastlogin, gelats, communityname) VALUES ('pc:5306287', 'trial', 1311299072, 0, 'guest')

This problem definitely isn't being caused by any of those values being NULL, the other constraint is a unique index on account.

All of the classes are joined and working before this is executed (otherwise this would be producing a function not found error)

[Mark Sir Link]

the issue with the first one seems to be num being 0, I imagine that is already being used in an entry

not really sure what would cause issues with the flag not being set, although I wonder why you take that approach instead of just checking to see if SELECT * FROM tPlayer WHERE account='pl.account' and checking to see if there is actually an existing row.

UN has had a similar database for about 2 and a half weeks now that hasn't thrown any errors from trials going in and out of observer mode.

The first screenshot has a constraint on account being unique, the 2nd has no constraints on any column

[xXziroXx]

Maybe you have communityname set to UNIQUE? Would explain why the person with "guest" as community name didn't work.

[cbk1994]

I'm not sure this will fix your problem, but I'd recommend just doing this instead of using a flag:

PHP Code:

INSERT OR REPLACE INTO tPlayer (account, subscription, lastlogin, gelats, communityname) VALUES ('pc:5306287', 'trial', 1311299072, 0, 'guest') 


If inserting the row would cause a constraint conflict, it deletes the existing row causing the conflict and inserts the row. If there is no conflict, it just inserts the row.

It should get rid of any issues resulting from out-of-sync database/player attributes (e.g. if a player is reset or rolled back).

Note that this will change the rowid for the player's entry, but you should never rely on the rowid. If you specify a PRIMARY KEY column, it probably won't change the rowid.

[ffcmike]

Originally Posted by xXziroXx

Maybe you have communityname set to UNIQUE? Would explain why the person with "guest" as community name didn't work.

If this was the case this would be occurring for every single guest that logs onto the server, but it is only a small minority.

Originally Posted by Mark Sir Link

the issue with the first one seems to be num being 0, I imagine that is already being used in an entry

The unique index is on both num and pid joined together, as in to prevent the same player from having a task entry added twice.
It's definitely not the issue with this, it can just as easily happen with values above 0.

Originally Posted by cbk1994

I'm not sure this will fix your problem, but I'd recommend just doing this instead of using a flag:

PHP Code:

INSERT OR REPLACE INTO tPlayer (account, subscription, lastlogin, gelats, communityname) VALUES ('pc:5306287', 'trial', 1311299072, 0, 'guest') 


If inserting the row would cause a constraint conflict, it deletes the existing row causing the conflict and inserts the row. If there is no conflict, it just inserts the row.

It should get rid of any issues resulting from out-of-sync database/player attributes (e.g. if a player is reset or rolled back).

Note that this will change the rowid for the player's entry, but you should never rely on the rowid. If you specify a PRIMARY KEY column, it probably won't change the rowid.

I guess that would fix that half of the issue, though that would have the slight inconvenience that you can't see each player in the order they were created when exploring the data.

What I believe might be the problem is that I have the query executed after a trigger, and so by the time onPlayerStored occurs observer mode is already preventing the ability to write flags to the player.
The reason I have it like this is so not to delay any subsequent processes in the Control-NPC's login event where this SQL function is invoked from.
As in:
Control-NPC - SQL.playerLoggedIn();
Control-NPC - Someothersystem.playerLoggedIn();
SQL.onSQLCommit();

As for the issue of players somehow not being stored in the database though I have no idea, no errors are occurring at the time of logging on, I'm only noticing these accounts aren't there due to later tasks failing to add.

[xXziroXx]

Originally Posted by ffcmike

What I believe might be the problem is that I have the query executed after a trigger, and so by the time onPlayerStored occurs observer mode is already preventing the ability to write flags to the player.

I can confirm that, had the same issue on Maloria.

[ffcmike]

Originally Posted by xXziroXx

I can confirm that, had the same issue on Maloria.

Guess I'll have to make SQL the last process within Control-NPC login without a trigger event.
Ideally observer mode would be waiting on such possible triggered events within the login process before kicking in (more ideally would just be removed as it's a pain in the arse to players and developers alike).

[xXziroXx]

Originally Posted by ffcmike

Guess I'll have to make SQL the last process within Control-NPC login without a trigger event.
Ideally observer mode would be waiting on such possible triggered events within the login process before kicking in (more ideally would just be removed as it's a pain in the arse to players and developers alike).

You could just process the SQL part when the player is sent back from observer mode, no? That's what I do at least.

[ffcmike]

Originally Posted by xXziroXx

You could just process the SQL part when the player is sent back from observer mode, no? That's what I do at least.

Good idea, are you checking player.isobserver as a condition to process SQL normally?

[xXziroXx]

Originally Posted by ffcmike

Good idea, are you checking player.isobserver as a condition to process SQL normally?

I do it like this:

PHP Code:

function onActionPlayerOnline()
{
  // What type is the account?
  client.upgradeStatus = player.upgradestatus;
  client.communityName = (player.communityname == "" ? player.account : player.communityname);
  
  // Okay, if it's a trial, let's give some special treatment.
  if (player.upgradestatus == "trial") {
    // Just now logging on.
    if (!player.isLoggedOn)
      return initializePlayer();
  }
  else   
    initializePlayer();
}

function onSwitchPlayerToObserver(pl)
  pl.isLoggedOn = true;

function onSwitchObserverToPlayer(pl)
  scheduleevent(1, "HandleTrialAccount", pl);

function onHandleTrialAccount(pl)
{
  // Sometimes the player logs off during observer mode but still triggers onSwitchObserverToPlayer
  if (pl.level.name == "")
    return;
  
  with (pl)
    initializePlayer();
} 


EDIT: There's a short delay after onSwitchObserverToPlayer is triggered and when the player isn't observer anymore, hence why I use a scheduleevent.

[ffcmike]

Originally Posted by xXziroXx

I do it like this:

PHP Code:

function onActionPlayerOnline()
{
  // What type is the account?
  client.upgradeStatus = player.upgradestatus;
  client.communityName = (player.communityname == "" ? player.account : player.communityname);
  
  // Okay, if it's a trial, let's give some special treatment.
  if (player.upgradestatus == "trial") {
    // Just now logging on.
    if (!player.isLoggedOn)
      return initializePlayer();
  }
  else   
    initializePlayer();
}

function onSwitchPlayerToObserver(pl)
  pl.isLoggedOn = true;

function onSwitchObserverToPlayer(pl)
  scheduleevent(1, "HandleTrialAccount", pl);

function onHandleTrialAccount(pl)
{
  // Sometimes the player logs off during observer mode but still triggers onSwitchObserverToPlayer
  if (pl.level.name == "")
    return;
  
  with (pl)
    initializePlayer();
} 


EDIT: There's a short delay after onSwitchObserverToPlayer is triggered and when the player isn't observer anymore, hence why I use a scheduleevent.

I think I'd prefer this if there was a built-in simple way of knowing if somebody was logging on as an observer, for now I'll see how it goes just having SQL occurring within the original event rather than a subsequent trigger.

[Crow]

Originally Posted by ffcmike

I think I'd prefer this if there was a built-in simple way of knowing if somebody was logging on as an observer, for now I'll see how it goes just having SQL occurring within the original event rather than a subsequent trigger.

onSwitchPlayerToObserver() and onSwitchObserverToPlayer() are built-in and should do the trick just fine.

[ffcmike]

Originally Posted by Crow

onSwitchPlayerToObserver() and onSwitchObserverToPlayer() are built-in and should do the trick just fine.

I was referring to something simple/easy from within the onActionPlayerOnline function.

I suppose I could try something such as:

PHP Code:

function onInitialized(){
  this.observers = new TStaticVar();
}

function onSwitchPlayerToObserver(temp.pl){
  this.observers.(@ temp.pl.account) = true;
}

function onSwitchObserverToPlayer(temp.pl){
  this.observers.(@ temp.pl.account) = false;
  this.doSQLStuff(temp.pl);
}

function onActionPlayerOnline(){
  if(!this.observers.(@ player.account)){
    this.doSQLStuff(player);
  }
} 


Under the assumption that onSwitchPlayerToObserver() is occurring before onActionPlayerOnline.
Edit: Tested and it doesn't so scratch that.

[ffcmike]

This issue hasn't been occurring with players logging on for the first time over the last several days so presumably moving the query into the original chain of functions as opposed to a subsequent trigger seems to have fixed it.

[ffcmike]

Originally Posted by ffcmike

This issue hasn't been occurring with players logging on for the first time over the last several days so presumably moving the query into the original chain of functions as opposed to a subsequent trigger seems to have fixed it.

Moving the query into the original function as opposed to a subsequent trigger certainly helped, yet somehow the problem of an observer on their first login not being added to the database, with no error returned from the query, is still happening on the rare occasion.

Whenever I've witnessed it happen at the precise moment, it's always appeared within the playerlist as if the trial player logs in, logs out, and then back in again. This last time however I asked the player if they had logged out in the start level or experienced any problem, they claimed they were logged in the entire time and it ran smoothly.