Web Interface MySQL table explanation for website developers

[Stefan1200]

Situation: You have an own website and like to create bots and user accounts for the bot web interface with your own website. This document should help you.

************* Adding a new user account *************
You want to create web interface user accounts on your own? No problem, you just have to fill the table jts3servermodwebui_users.
To add a simple user account without any admin permissions, just do (password has to be hashed, see below):

Code Select Expand

INSERT INTO `jts3servermodwebui_users` (`name`, `password`, `salt`, `email`) VALUES ('abc', 'Password123', 'ThisIsARandomSalt', 'my@mail.net');

*** Table jts3servermodwebui_users ***
Required table columns of jts3servermodwebui_users:
name = loginname of the user account, has to be unique.
password = hashed password using hash("sha512", "ThisIsARandomSalt"."Password123", false), example in PHP.
salt = the used salt for the password.
email = E-Mail address of the user, used of the "Reset Password" function (and some more pages), has to be unique.

Optional table columns of jts3servermodwebui_users:
registertime = Unix timestamp of the time when this user account was created.
maxbotcount = Amount of maximum allowed bots (for this user) or -1 for no limit. Will be checked only while the user create a new bot.
adminlevel = 0 - Normal user account without any admin permissions
                    1 - Restricted Administrator, limited admin functions
                    2 - Full Administrator, all admin functions
userstatus =  0 - Active user account
                   -1 - Disabled user account (can't login)
Other columns are only used by the web interface to track last login time or password fail count.


**************** Adding a new bot ****************
You can also add a bot directly to the MySQL table, but this is a little bit more complex. Two tables have to be touched. After you added the bot to the database, you have to send the telnet command instancelistreload to the bot (more details below).

Code Select Expand

INSERT INTO `jts3servermod_instances` (`name`, `ts3serverip`, `ts3portid`) VALUES ('abc', '192.168.1.100', '9987');
INSERT INTO `jts3servermod_instanceconfig` (`instance_id`, `configkey`, `configvalue`) VALUES(1, 'ts3_server_address', '192.168.1.100');
INSERT INTO `jts3servermod_instanceconfig` (`instance_id`, `configkey`, `configvalue`) VALUES(1, 'ts3_server_query_port', '10011');
INSERT INTO `jts3servermod_instanceconfig` (`instance_id`, `configkey`, `configvalue`) VALUES(1, 'ts3_server_query_login', 'serveradmin');
INSERT INTO `jts3servermod_instanceconfig` (`instance_id`, `configkey`, `configvalue`) VALUES(1, 'ts3_server_query_password', 'abc123');
INSERT INTO `jts3servermod_instanceconfig` (`instance_id`, `configkey`, `configvalue`) VALUES(1, 'ts3_virtualserver_id', '-1');
INSERT INTO `jts3servermod_instanceconfig` (`instance_id`, `configkey`, `configvalue`) VALUES(1, 'ts3_virtualserver_port', '9987');
INSERT INTO `jts3servermod_instanceconfig` (`instance_id`, `configkey`, `configvalue`) VALUES(1, 'bot_slowmode', '0');


*** Table jts3servermod_instances ***
Required table columns of jts3servermod_instances:
name = The bot name, has to be unique. Don't use spaces in the bot name, only use letters, numbers, minus and underscore!
ts3serverip = Just the TS3 server address which the bot is using, should be the config value of ts3_server_address.
ts3portid = The TS3 server port or virtual server id, or more exactly: ts3_virtualserver_id == -1 ? ts3_virtualserver_port : ts3_virtualserver_id

Optional table columns of jts3servermod_instances:
enabled = Auto start bot, 1 or 0. Should be 0 to allow bot being configured first. Will be changed to 1 from bot web interface on first start.
logmode = 0 - No log for this bot will be created.
                1 - Log file will be created to file system, table column logpath is required only in this case.
                2 - Write log into the MySQL database, table jts3servermod_log will be used as target (recommended).
csvlogmode = This logs connecting clients, including time, name, ip address. Possible values like at logmode, if set to 1, table column csvlogpath is required.
debug = Special debug mode, 1 or 0. Should be 0. Creates a full communication log file into the bot directory. Don't use this for a long time, some hours could create very big log files already.
Other columns are only for internal web interface timestamp stuff (last start time, etc.).


*** Table jts3servermod_instanceconfig ***
Required table columns of jts3servermod_instanceconfig:
instance_id = The id of the table jts3servermod_instances (so request last insert id from database after inserting a new bot to jts3servermod_instances).
configkey = A config key for the bot, they are the same like from the file based configuration.
                  Required are: ts3_server_address, ts3_server_query_port, ts3_server_query_login, ts3_server_query_password, ts3_virtualserver_id, ts3_virtualserver_port and bot_slowmode
                  For a full list of config keys, just look into the normal bot configuration help file.
configvalue = The value for the config key, they are the same like from the file based configuration.
                     For a more detailed explanation, just look into the normal bot configuration help file.



************ Make bots visible for a user ************
You have to connect a user account to a bot. This allows that one user can manage many bots. But it is also possible that many users can manage one bot. For every user or every bot you have to insert the bot / user connection to the database.

Code Select Expand

INSERT INTO `jts3servermodwebui_usersinstances` (`user_id`, `instance_id`, `fullaccess`) VALUES(1, 1, true);


*** Table jts3servermodwebui_usersinstances ***
Required table columns of jts3servermodwebui_usersinstances:
users_id = The user id of the table jts3servermodwebui_users.
instance_id = The bot id of the table jts3servermod_instances.

Optional table columns of jts3servermodwebui_usersinstances:
fullaccess = TRUE or 1 if the user should have full bot management permissions, FALSE or 0 if just the normal permissions.



************** Remove a user account **************
Just delete the user id from the tables jts3servermodwebui_users and jts3servermodwebui_usersinstances.

Code Select Expand

DELETE FROM `jts3servermodwebui_users` WHERE id = 1;
DELETE FROM `jts3servermodwebui_usersinstances` WHERE users_id = 1;



****************** Remove a bot ******************
To remove a bot, just delete the bot id from the tables jts3servermod_instances, jts3servermod_instanceconfig, jts3servermodwebui_usersinstances and jts3servermod_log. After you removed the bot from the database, you have to send the telnet command instancelistreload to the bot (more details below). Important: Make sure that you stopped the bot BEFORE you remove the bot from the database! Use the telnet command stop <instance name> for this.

Code Select Expand

DELETE FROM `jts3servermod_instances` WHERE id = 1;
DELETE FROM `jts3servermod_instanceconfig` WHERE instance_id = 1;
DELETE FROM `jts3servermodwebui_usersinstances` WHERE instance_id = 1;
DELETE FROM `jts3servermod_log` WHERE instance_id = 1;



******** Bot telnet command instancelistreload ********
If you added or removed a bot in the MySQL database, you have to trigger an instance list reload at the bot. To do this, you can connect to the bot telnet port (default: 5873), login with your telnet password and send the command instancelistreload. If you work with the class/dbconnect.php and class/BotTelnet.php of the official web interface, you can just use the official $botTelnet->instancelistReload(); function.

Important: If you want to remove a bot, make sure that you stopped the bot BEFORE you remove the bot from the database! Use the telnet command stop <instance name> for this.

A list of all bot telnet commands: TelnetCommandHelp.html



**************** Important Settings ****************
If you create user accounts and bots on your own, don't forget to disable both functions for normal web interface users, look at the screenshot below.

[Hedrauta]

Missing the "Like"-Button 

[Stefan1200]

Updated first post (Important Settings).

[Stefan1200]

Updated first post, but one stuff is missing at adding and removing bots. Will be added in the next days.

[TSCoach]

Thanks,

Is it because of me, that you created the thread?

regards Thomas

[Stefan1200]

Is it because of me, that you created the thread?

For everyone who needs this information. This information was requested some day ago, so I started writing it down.

[Stefan1200]

Updated first post again, documentation should be complete now.

[Stefan1200]

Small update of the first post (added link to the bot telnet document with all commands).

[Stefan1200]

Updated the first post for the new version 3.0 of the web interface. Changes made at the tables jts3servermodwebui_usersinstances and jts3servermod_instances

[Stefan1200]

Updated above post, section "Adding a new user account". Adding missing optional columns for the table jts3servermodwebui_users.

Also updated the settings screenshot to match with the current design of the web interface.

[Stefan1200]

Updated first post, section "Adding a new user account" for the changes of web interface build 3033 of today.