RE: Help Converting to Server Query

[RickoT]

Hey guys, so I was really excited when I finally figured out how to program all of my SQL triggers to update the Teamspeak database on the fly as information was changed on my PHPBB Board. What I did not count on was the necessity of restarting my TS3 server after every change. That being said I was hoping to get some help in converting my triggers to SQ or finding a way to force TS to refresh its data from the database. Either would be helpful

Here are my triggers

---

Thhis trigger updates the Unique ID of a user in the database when they update it on the forums (for better integration and not generating lots of stale ID's, also for maintaining their db ID on the server for receiving messages, ect)

CREATE TRIGGER UpdateTSIDForUser BEFORE UPDATE ON rickswor_MIGuildForums.phpbb_profile_fields_data
   FOR EACH ROW BEGIN
   DECLARE groupid INT;
   DECLARE clientid INT; 
      
   IF OLD.pf_ts_uid != NEW.pf_ts_uid THEN
	  
      SET clientid = (SELECT client_id FROM rickswor_MIGuildTeamSpeak.clients WHERE client_unique_id=OLD.pf_ts_uid);
      SET groupid = (SELECT group_ts_id from rickswor_MIGuildForums.phpbb_groups WHERE group_id=(SELECT group_id from rickswor_MIGUildForums.phpbb_users where user_id=NEW.user_id));
      
      IF NEW.pf_ts_uid='' THEN
         DELETE FROM rickswor_MIGuildTeamSpeak.group_server_to_client WHERE id1=clientid;
         DELETE FROM rickswor_MIGuildTeamSpeak.clients WHERE client_unique_id=OLD.pf_ts_uid;
      ELSE   
         UPDATE rickswor_MIGuildTeamSpeak.clients SET client_unique_id=NEW.pf_ts_uid WHERE client_unique_id=OLD.pf_ts_uid;
         IF (SELECT COUNT(*) FROM rickswor_MIGuildTeamSpeak.group_server_to_client WHERE id1=clientid) > 0 THEN
            UPDATE rickswor_MIGuildTeamSpeak.group_server_to_client 
            SET group_id=groupid;
         ELSE
            INSERT INTO rickswor_MIGuildTeamSpeak.group_server_to_client (group_id,server_id,id1,id2) VALUES
            (groupid , 1, clientid, 0);
         END IF;
      END IF;
   END IF;
END;

---

This updates their TS Group membership when their primary group membership changes on the forums. I have created a custom field in the groups table in PHPBB to hold the datbase ID of the group in teamspeak

CREATE TRIGGER UpdateTSMembershipOnGroupChange BEFORE UPDATE ON rickswor_MIGuildForums.phpbb_users
   FOR EACH ROW BEGIN

   DECLARE clientuid char(255);
   DECLARE clientid INT;
   DECLARE newtsid INT;
   DECLARE oldtsid INT;
   
   IF OLD.group_id != NEW.group_id THEN
      SET clientuid = (SELECT pf_ts_uid FROM rickswor_MIGuildForums.phpbb_profile_fields_data where user_id=NEW.user_id);
	  SET clientid = (SELECT client_id FROM rickswor_MIGuildTeamSpeak.clients where client_unique_id=clientuid);
	  SET newtsid = (SELECT group_ts_id from phpbb_groups WHERE group_id=NEW.group_id);
	  SET oldtsid = (SELECT group_ts_id from phpbb_groups WHERE group_id=OLD.group_id);

      IF clientuid != '' THEN
         IF newtsid=0 THEN
            DELETE FROM rickswor_MIGuildTeamSpeak.group_server_to_client WHERE id1=(SELECT client_id FROM rickswor_MIGuildTeamSpeak.clients WHERE client_unique_id=clientuid);
         ELSEIF oldtsid=0 THEN
            INSERT INTO rickswor_MIGuildTeamSpeak.group_server_to_client (group_id,server_id,id1,id2) VALUES
               (newtsid, 1, clientid, 0);
         ELSE    
            UPDATE rickswor_MIGuildTeamSpeak.group_server_to_client
            SET group_id=newtsid
            WHERE id1=clientid;
         END IF;
      END IF;
   END IF;
END;

---

This removes the user from the Teamspeak database and Teamspeak groups when the user is deleted from the forums.

CREATE TRIGGER DeleteUserAfterForumDelete BEFORE DELETE on rickswor_MIGuildForums.phpbb_users
   FOR EACH ROW BEGIN
      DECLARE clientid CHAR(255);
      SET clientid=(SELECT pf_ts_uid FROM rickswor_MIGuildForums.phpbb_profile_fields_data where user_id=OLD.user_id);

      DELETE FROM rickswor_MIGuildTeamSpeak.group_server_to_client WHERE id1=(SELECT client_id FROM rickswor_MIGuildTeamSpeak.clients WHERE client_unique_id=clientid);
      DELETE FROM rickswor_MIGuildTeamSpeak.clients WHERE client_unique_id=clientid;
   END;

[SilentStorm]

You do realize that you are (extremely likely) making a mess of your TS3 Database?
ServerGroup Memberships (group_server_to_client) is by far not the only thing stored in the Database...
Clients got Channelgroups, certain Informations (avatar etc.), might have banned people, might have client or channel client permissions etc. all of which gets stored into the database but not removed when your Triggers fire. Last time I checked TS3 did not use foreign keys which would be able to reduce the degrading effect your Triggers have on the TS3 Database Integrity.
Depending on how often your Triggers got fired, you will have lots of irrelevant / no longer valid entries in the database, which could at some point cause trouble, which might not reveal itself to be related to a database problem.


In any case, you won't be able to do the same without modifying your Forums Scripts (the .php Files of your Forum System), and some parts of which you were doing can not be done at all with serverquery.


To delete Clients from the Database:
clientdbdelete cldbid=<DatabaseID of Client>

To remove Clients from a Servergroup:
servergroupdelclient sgid=<Servergroup ID> cldbid=<Database ID of the Client>

To add Clients to a Servergroup:
servergroupaddclient sgid=<Servergroup ID> cldbid=<Database ID of the Client>

To get the Client Database ID from a Unique ID:
clientgetdbidfromuid cluid=<UniqueID of the Client>

Not possible:
Create "new" Clients.

To "create" new Clients you could however do the following:
Use the tokenadd Command to create a Privilege Key for a specific Servergroup, and then create a Link to your Server which includes the generated Privilege Key and display this Link to your Users. Now the only thing your User needs to do is click the Link to connect to the server and get the Servergroup you need him to have.

You will need to use any of the existing Frameworks that abstract the Query Interface. Like this one

[RickoT]

Thanks for your feedback, I understand the potential damage of doing what I had setup which is why I am here now. This is a new server so once i get all this set up properly and tested I plan on wiping the tables, starting over, and going forward with this.

As far as creating new clients, I already have something in place for that, basically I will be having people log into TS, then post their UID in part of their profile. The goal will be to fire off one of these scripts that will update their group permissions.

So now that I have this information, I found the documentation to be kind of lacking an explanation of how to implement this. do I put it in a PHP script and let the server fire it off? Do I make a bash script? I am kind of learning this on the fly so any and all input would be appreciated.

[RickoT]

Also how do I set a value? Do I say

$clientdbid = clientgetdbidfromuid cluid=<UniqueID of the Client>
servergroupaddclient sgid=8 cldbid=$clientdbid

Would something like that work?

[RickoT]

So here is a script I put together based on some stuff I found, is this accurate?

#!/usr/bin/php
<?PHP
        $cid = $_GET["cid"];
        $grpid = $_GET["grpid"];

        require("/home6/rickswor/ts/sq/libraries/TeamSpeak3/TeamSpeak3.php");
        TeamSpeak3::init();

        $ts3 = TeamSpeak3::factory("serverquery://serveradmin:C0nsum3d1031@69.89.20.168:10011/?server_port=9987");

        $user = $ts3->clientGetIdsByUid($cid);
        if ($user["clid"] != 0) {
                $ts3->serverGroupClientDel(6,$user["clid"]);
                $ts3->serverGroupClientDel(7,$user["clid"]);
                $ts3->serverGroupClientDel(9,$user["clid"]);
                $ts3->serverGroupClientAdd($grpid,$user["clid"]);
                $ts3->serverGroupClientDel(8,$user["clid"]);
        }
?>

The idea is to verify that this user is not in any groups, add them to the appropriate group and then make sure they are not in the guest group. This does not appear to be working... any suggestions would be great.

~Rick

[SilentStorm]

Script doesn't look to bad, but as I said it would be easier to put that Code into a function, then edit the PHP File of your Forum System that is responsible for updating the Profile and in that File include your own and call your Function with the appropriate Parameters.

Same goes for deleting Clients. Just edit the PHP File of your Forum System that is responsible for deleting Forum Accounts, and in there call the function to delete the Client on the TS3 Server at the appropriate place.