AdsQL Server Advertisements System - Short Description
Improved MySQL-based advertisements system with advanced game type and server selection features. Set up and manage your server ads with a web interface.
HEADS UP:
This plugin is no longer being maintained by its author/maintainer (me). It is a good plugin that works quite well for people who can follow directions, however as some have noted, there are some potential security risks with the Web UI. Unfortunately I am no longer participating in Valve gaming, either as a player or a server operator, as I do not have the time or interest for it any longer.
Until someone who knows what they're doing is willing to take up maintenance of the plugin and web UI -- since Obsidian apparently found security problems and blabbed them out in public to everyone -- it is recommended that steps be taken to restrict access to the web interface to trusted IP addresses, such as firewall rules, .htaccess files, etc.
100% of the web UI and plugin source code is attached below, so if you have the time and the inclination, and know what you're doing, knock yourself out.
Intro/Overview
The AdsQL advertisements system is a replacement for DJ Tsunami's standalone advertisements plugin, aimed at
experienced server operators who wish to store the advertisements used on all of their gameservers running this plugin in a single MySQL database, and use a web interface to create, edit, and manage display of the ads on your servers.
If you only operate a single gameserver, you do not need to use this plugin and can continue using DJ Tsunami's standalone version, unless you'd just rather use a web interface to manage your ads.
The AdsQL Advertisements System is a fork of the original MySQL Advertisements plugin and web interface. MySQL Advertisements has been unmaintained for a long time now with multiple bugs, problems and limitations. While a number of posts were made describing fixes, improvements, and even updated/modified versions somebody was working on, nobody ever actually posted one. Since I used this plugin/webui on our setup, and know a lot of other people also use it, I decided it was time to "fork" from what looks rather like an abandoned project and release what I have been working on to the community rather than just keep it for myself.
Credits
Original Work
Strontium Dog - for the original MySQL Advertisements plugin and web interface this fork project originated from.
DJ Tsunami - for the original standalone Advertisements plugin
I do not claim that the code of the plugin or the web interface is 100% mine. The original work by Strontium Dog and DJ Tsunami are still reflected to varying degrees in this plugin and web interface today. A cursory review of the PHP and plugin code will easily confirm this is definitely NOT just a "cheap knockoff" of either, however.
Other Credits
Psychonic - for help with the UTF8 charset problems
Features
The AdsQL Advertisements System retains all of the functionality of the original MySQL ads plugin and includes a multitude of fixes and improvements -- while doing as much as possible to make this system easy for existing sm_adsmysql users to switch to.
ALL known bugs and HTML coding standards compliance problems with the original web interface have been fixed. The web interface is now fully compatible with Internet Explorer, and the entire updated web interface has been W3C validated to be HTML 4.01 Transitional compliant, except one feature we need for the Server ID's text input box which is not strictly HTML standards-compliant, but is tested to work properly on ALL browsers.
The web interface, SQL database (as configured by the provided adsql.sql script), and plugin should all be fully UTF8-charset compliant now (as of 1.7.7 release).
Ad text is defined in essentially the same manner as DJ Tsunami's standalone advertisements plugin, supporting the same syntax, color tags, etc. (dependent upon which colors the games themselves support). But you use a web interface to write/edit the ads instead. And with AdsQL, you can choose multiple game types you want an ad to run on, define Server ID's to limit which servers of the specified type(s) run the ad, and even combine game types with Server ID's if you like.
Each server running the AdsQL plugin will ONLY load the ads it is supposed to be running from the SQL database, based on how the ads are defined.
Supported Games
The AdsQL Advertisements System currently supports defining ads for the following game types:
Age of Chivalry
Counterstrike Source
Counterstrike Source BETA
Day of Defeat Source
Half Life 2 Deathmatch (hl2mp)
Insurgency
Left 4 Dead 1
Left 4 Dead 2
Pirates Vikings & Knights II
Team Fortress 2
Team Fortress 2 Beta
Zombie Panic Source
Another feature I have added to the web interface is "hover over" names for each game type icon - if you hover over a game icon in the Game Type(s) column of the ads list page, the full name of the game will be displayed in a small title box beside it.
Requirements
-
Patience and ability to follow instructions. This is an advanced plugin and web interface system that needs to be set up correctly, and it will take you at least a few minutes to do it. It is intended to be used by responsible, experienced gameserver operators who pay attention to their server logs, take the time to "RTFM", and know how to maintain their gameserver and sourcemod configurations. Take your time and read the instructions carefully, so you can do it right the first time!
It is assumed that you are experienced/comfortable with creating and maintaining MySQL databases, installing web content on a webserver host, etc. These base needs are beyond the scope of my ability to support the plugin.
phpMyAdmin is a nice web interface to your MySQL server and the installation instructions assume you are using it. The included .sql file to set up the tables for the database you create can also be used from the mysql command line interface for those who are comfortable with that.
-
Sourcemod 1.1.x or newer
The web compiler that builds the adsql.smx file for the Get Plugin link at the bottom of the post will build for a 1.1 series, it should work fine on newer sourcemod versions too (tested!) You can also compile the .sp yourself if you are so inclined, no special extensions or includes are needed.
-
MySQL Server available
You will need to be able to create a MySQL ads database for the plugin. MySQL v5.0.7 or newer is recommended, but it should work with any MySQL version sourcemod itself supports.
-
Web server with PHP 5.x.x or newer installed on it
PHP version 5.2.3 or newer is recommended, but any 5.x.x release should work. Make sure it has PHP MySQL support also.
Installation
SECTION A - Web Interface
For EXISTING users of the unapproved sm_adsmysql plugin who are switching to AdsQL:
1. My best recommendation is that you "start over" with a fresh database and my adsql.sql script, especially if UTF8 support is important to you. You could copy-paste the *AD TEXT* (only) from the old DB into the new web interface, and take full advantage of the updated character set settings set up for tables, columns, etc. in the adsql.sql file included in the distribution .zip file.
If you ARE going to start over with a fresh database, just follow the "For New Users" section below.
If you don't want to start a fresh database, then you will at least need to add a new column to the adsmysql table in your existing ads database - the one defined in the "admintools" section of your sourcemod configs/databases.cfg file. This is required for the server ID feature to work with the plugin and the web interface. Back up your database first if you are concerned about it.
The column/field name you need to add is 'gamesrvid'. It needs to be a text type field, not NULL, with no default value or extra attributes. You can either point/click your way through this or run this SQL query (making sure you have the correct database selected first):
Code:
ALTER TABLE `adsmysql` ADD `gamesrvid` TEXT NOT NULL AFTER `game` ;
2. Unzip the adsql-(version).zip file and browse into the unpacked web/adsql folder.
3. If you will be using your own top logo banners, open the
style.css.default file and edit the background "fill" color according to the Top Logo Customization section in the
FAQ Post, and save the file in the same adsql/ directory as filename
style.css. If you are NOT going to make your own top logos right now and just want to use the sample ones, just copy the style.css.default file to
style.css.
4. If you will be using your own top logo banners, copy them into the unpacked adsql/images/ directory as filenames
logo02.jpg and
logo03.jpg. See the Top Logo Customization section of the
FAQ Post for more details. If you are just going to use the sample ones for now, copy logo02.jpg and logo03.jpg from adsql/images-sample/ into adsql/images/.
5. Referring to your existing
include/config.php file from the old adsmysql web interface on the web host, browse to the unpacked web/adsql/include folder and open up
config.php.default with a text editor. Edit the values in config.php.default to match what you have in your old config.php, or if you are starting with a fresh database, make sure you set the right info for the new database in config.php.default. However MAKE SURE you do not mess with the lines related to defining HAVE_ADSQL_CONFIG at the top and bottom of the file. When you are satisfied everything is correct, save the config.php.default file into the same unpacked web/adsql/include directory as filename
config.php.
6. Upload the ENTIRE unpacked adsql folder structure with all subfolders to your web host in the desired location. Since the folder name (adsql) is different, your old adsmysql folder will be left completely intact. You can remove it yourself later after step 5 and you realize you will never need it again.
7. Try out the web interface wherever you put it on your web host, for example
http://www.example.com/adsql
For NEW users of the AdsQL plugin:
1. Create a new database on your MySQL server, noting the database name, database username and password, and the database hostname.
2. Unpack the adsql-(version).zip file, and then browse to the unpacked web/adsql/ folder.
3. If you will be using your own top logo banners, open the
style.css.default file and edit the background "fill" color according to the Top Logo Customization section in the
FAQ Post, and save the file in the same adsql/ directory as filename
style.css. If you are NOT going to make your own top logos right now and just want to use the sample ones, just copy the style.css.default file to filename
style.css.
4. If you will be using your own top logo banners, copy them into the unpacked adsql/images/ directory as filenames
logo02.jpg and
logo03.jpg. See the Top Logo Customization section of the
FAQ Post for more details. If you are just going to use the sample ones for now, copy logo02.jpg and logo03.jpg from adsql/images-sample/ into adsql/images/.
5. Browse to the unpacked adsql/include/ folder and open the
config.php.default file with a text editor.
6. Edit
config.php.default so that the correct database info you noted in #1 above is set in the file, and also set the email and community name you want to use. MAKE SURE you do not mess with the lines related to defining HAVE_ADSQL_CONFIG at the top and bottom of the file. When you are satisfied everything is correct, save the config.php.default file as filename
config.php in the same unpacked adsql/include/ directory.
7. Upload the entire unpacked adsql folder structure to your web host in the directory you want to access the web interface from.
8. Using phpMyAdmin, log into your database server and choose the database you created in step 1, then click the
Import tab up top, and upload the
adsql.sql file from the top-level directory of the unpacked zip. It will set up your database tables and columns for you and includes a few sample ads.
9. Verify the web interface works - in your web browser type in the URL for your website, adding /adsql to the end (or whatever you set up for this web host) - for example:
http://www.example.com/adsql. Log in with user Administrator, password Administrator.
10. Click the User Management link at the bottom, and create yourself a new user account. The username and password both have to be at least 6 characters.
11. Once you've created your new user account, you'll see it added to the Users Table Contents. Now use the Update User Level field, typing in the username you just created, and change the access level to 9 and click the Update Level button.
12. Log out as the Administrator user, then verify you can log in with your new level 9 account.
13.
Once you have verified you can log in with your new level 9 account, go back into the User Management page and delete the Administrator account. This is for security reasons.
14. Check out the Advertisements page!
SECTION B - Gameserver Configuration
1. Add the Database Info to Your databases.cfg file
Users of the old sm_adsmysql plugin should be able to skip this step unless you elected to set up a fresh database
In your sourcemod base's configs/ directory (usually [gameserver path]/addons/sourcemod/configs), open your databases.cfg file with a text editor and insert a new section before the final closing curly brace (the "}" character) at the bottom of the file:
Code:
"admintools"
{
"driver" "mysql"
"host" "your-db-hostname"
"database" "your-db-name"
"user" "your-db-username"
"pass" "your-db-password"
//"timeout" "0"
//"port" "0"
}
Editing each section in the example above starting with
your to the correct information for your setup.
You MUST name the section admintools -- if you don't, you will get database SQL_TConnect / invalid handle errors. If your database server is NOT the same host this game is running on, you will need to type the correct database server hostname or numeric IP in the "host" line. Also if your MySQL server is not on localhost and especially if it is not running on the default port (3306), you may need to uncomment the port field and type in the correct port number as well. If the MySQL server *IS* the gameserver host you should be able to use "localhost" in the host field and should not have to uncomment the port field.
2. Upload/copy the adsql.cfg "autoexec" config file into your cfg/sourcemod/ directory
There is a copy attached separately at the bottom of this post, and also a copy in the distribution .zip's cfg/sourcemod/ directory. If you want to use a different ads display interval than the default (45 seconds) you'll probably want to edit the adsql.cfg file. CVAR's are described in detail below.
NOTE: This file will *NOT* be automatically created by the plugin if it doesn't exist, so you have to put one there manually (this is on purpose). The two CVAR's I support being defined in this file are adsql_debug and adsql_interval.
Define adsql_serverid in this file at your own risk and peril - I won't support it if you have problems.
3. OPTIONAL: Set the Server ID
Setting a server ID is not required for the plugin to function. However if a server does not have a server ID defined, it will
ONLY load and display ads that are set to run on All game types, or that are ads defined for this server's game type and have 'All' set (by itself) in the server ID field. If you wish to be able to run certain ads only on SOME servers of a game type, but not have to run the ad on ALL servers of that type, you will need to define a server ID on each server that should run these "special" ads.
Step A: Decide on a unique server ID string to assign for this server which no part (substring) of the ID is a match for the complete server ID of another server.
Do not use spaces, commas, or other non-alphanumeric characters in the server ID you choose. KEEP IT SIMPLE, using alphanumeric (letters and numbers) characters ONLY.
Good examples, given two CS:S servers:
css013 and
css001
Bad examples: css1 and
css13 (because "css1" is a substring match for "css13")
Step B: Create a new directory under the sourcemod base directory's configs/ directory named adsql. For most folks this will be addons/sourcemod/configs/adsql. Then you need to create a file in the adsql directory named
serverid.txt with a text editor.
Step C: Inside this file, type the unique server ID you wish this server to use as the FIRST and ONLY uncommented line in the file.
The plugin will ONLY read the first line from this file which does not start with // - so you don't want a blank line on top of the line you type your server ID in. You don't have to have any comments in the file at all, just make sure that only one line in the file isn't "commented out", and that this line contains the server ID string you want to use.
A console message will display as the plugin/server starts up to confirm the Server ID the plugin read from the file.
NOTE: The serverid.txt file is the ONLY place I will support defining the server ID. See the FAQ Post for more details.
3. Install the plugin
Place the adsql.smx file in your sourcemod base directory's plugins/ folder. For most folks this will be addons/sourcemod/plugins/. Then either load the plugin from the server console ("sm plugins load adsql.smx"), change the map, or start/restart the server.
Console/Server Commands
sm_reloadads
Ads are automatically reloaded on every map change. If you want to manually reload them before the next map change, you can use this command to do so in any ways described below.
sm_reloadads can be typed directly into the server console. Also, if you have the Server CVAR's admin flag ("h" by default), you can run this command via rcon from your game client and it will reload the ads from the database. You should also have a Reload Ads item in the Server Commands section of the admin menu, and assuming you have the
/ key working as a game chat substitute for "sm_", you should also be able to say
/reloadads in-game to reload the ads.
Plugin CVAR's
adsql_interval - Time interval between ads displayed, in seconds. This is meant to be set in cfg/sourcemod/adsql.cfg for initial plugin load/start configuration. It could also be set/changed in sourcemod.cfg or elsewhere.
adsql_debug - Default = 0 (off)
Enable debug-level logging to sourcemod/server logs. 0=off, 1=on
You don't want to turn this on unless you are having a problem and more detailed logs are needed.
This can/should be set in cfg/sourcemod/adsql.cfg, where having it on (set to 1) when you need it should make the plugin load and start in debug mode. You can also change its value at any time (temporarily turn it on or off) which will last at least until the next time the plugin is reloaded or the server is restarted.
adsql_version - public CVAR which shows the plugin version.
You should be able to see this in HLSW etc. (although possibly only after a map change)
adsql_serverid - public CVAR showing the Server ID assigned to this server.
You should be able to see this in HLSW etc. (although possibly only after a map change)
adsql_serverid defaults to undefined ("") but at plugin startup, it looks for the configs/adsql/serverid.txt file and if the file is found and a server ID can be read from the file, your server ID will be stored in this CVAR.
If you change the value of adsql_serverid after plugin startup, the plugin will automatically reload the ads 2 seconds later, since changing the ID will likely change which ads the server should be running.
CAUTION ABOUT CHANGING THE SERVER ID AFTER PLUGIN STARTUP
Note that if you change adsql_serverid via rcon, console, etc., it will NOT automaticially update the serverid.txt file -- so if you want the serverid change to "stick" after a plugin reload or server restart, you will need to update the serverid.txt file as well. Think of the ability to change the cvar while the server is running as a convenient way to change the ID and reload the ads (based on that new ID) without having to restart the server.
Also be aware that the last modified timestamp on serverid.txt is checked at each map start - if it doesn't change (ie you don't edit the file to set a new server ID in it and save the file), the plugin keeps the last server ID value set as long as the server runs. If you DO update serverid.txt, the plugin will detect this on the next map change and re-read the file, and the ads loaded will be based on the server ID saved in serverid.txt.
Using The Web Interface
Admin Levels
Level 9 admins are the highest access level and have full access to all features. Level 3 admins can create new ads or edit existing ads. Level 4 admins can change the order ads display using the small green up and down buttons on the right end of each row. Only Level 6 and higher have the access to delete ads.
Creating Ads
The Ad Text box lets you type in the text of the ad, and you can specify color tags in curly braces such as {LIGHTGREEN} etc. to use one or more game-supported colors in the ad. Tags you can use like these are listed to the right of the text box. You can also use various server CVAR's by typing the CVAR in all caps and enclosing the CVAR in curly braces -- such as {BOT_QUOTA}, {NEXTMAP}, etc. See the demo web interface for examples and a better look.
Ad Visibility
Under the Ad Visibility section you can choose to make the ad viewable by All players (default), admins only, or non-admins only. Anyone who has the "a" admin flag (reserved slot) is considered an admin by the plugin.
Selecting Which Game(s) an Ad Should Run On
In the
Game Type section at the bottom left of the add/edit screen, click the game type you want the ad to run for. If you want the ad to run on ALL game types, select
All Games.
To select multiple game types, Ctrl-click multiple games in the select box.
NEVER select "All Games" in combination with specific games.
Server ID's - Choosing Which Server(s) an Ad Should Run On
If you wish an ad to run on ALL servers of the selected game type(s), type
All in the Server ID's text input box. This is the default value for new ads.
Do not remove "All" from the Server ID's text field unless you are going to replace it with specific Server ID's (see below)
If you wish the ad to only run on SOME servers of a certain type or types, then instead of typing/leaving
All in the Server ID's text input box, you will type the Server ID('s) of the specific servers you want to run this ad instead.
When setting multiple server ID's, separate multiple ID's with commas using no spaces. This is CRITICAL. Also, DO NOT leave All in this field if you are setting specific server ID's.
Example
Let's say you have 5 CS:S servers, but you only want three of them to run this ad, and that the server ID's you set on these three servers are
css01,
iceworld, and
gungame1. You would select Counterstrike Source as the Game Type, then in the Server ID's text box, you would type this:
Code:
css01,iceworld,gungame1
Changelog
This post is long enough already.. so the changelog has been moved to
The Changelog Post
Support
Support info has been moved to
The Support Post. Make sure you read/follow it before posting any support or feature requests.
There is also a
Known Issues / To-Do List Post and a
Troubleshooting, FAQ, and Web Banner Customization Post.
Conclusion
I hope everyone enjoys this plugin. It has been fun to work on, and it's nice to have one that pretty much works like most of us want it to now.
Cheers,
PharaohsPaw
PS: There will always be a copy of the latest adsql.sp plugin sourcecode as well as a compiled adsql.smx under the addons/sourcemod/ directory inside the .zip file. I do this on purpose so in the event that the Sourcemod forums are unreachable, as long as you have the .zip there is an .smx available. The .smx in the .zip is currently built on a stock 1.3.6 release. I am also trying to keep a mirror of this on our game community forums so there is somewhere else it can be found.
PPS: The adsql.sp file is posted separately here for review purposes and for the convenient web-based plugin compiler. The "get plugin" link below will compile for sourcemod 1.1 series and the adsql.smx it produces should work on newer releases as well.
If you plan to use this plugin, make sure you grab the .ZIP file -- you'll need it for the web interface.
Hybrid Mode
