MySQL Polls
 

Polls mod based on MySQL

[IMG]http://img341.**************/img341/6471/mysqlpolls.jpg[/IMG]

Description:

Mod for in-game voting on MySQL. Easily display the results of voting on your site (in real-time).

Cvars:

sm_mysql_polls_version - Plugin version
sm_mysql_polls_check_client_by "0" - Checking voted client by SteamID or IP. 0 - SteamID, 1 - IP. Default: "0"
sm_mysql_polls_dbconfig "mysql_polls" - Name of database config in addons/sourcemod/configs/database.cfg. Default: "mysql_polls"
sm_mysql_polls_enable "1" - Enable\Disable this plugin. Default: "1"
sm_mysql_polls_prefix "sm_mysql_p" - Table prefix in database. Default: "sm_mysql_p"
sm_mysql_polls_server_id "1" - Server ID for multiservers polls. Default: "1"


DB structure description:

Table prefix_polls:
`id` - poll ID
`name` poll name, for your site
`title` poll title, for in-game poll menu
`time` time creating a poll, for your site
`stat` poll status (0 - inactive\1 - active)
`server_id` server ID for multiservers polls ("1","2","public1" etc.) (see cvars in cfg/sourcemod/mysql_polls.cfg)

Table prefix_variants:
`id` - variant ID
`poll_id` - poll ID
`name` - variant name, for in-game menu

Table prefix_votes:
`id` - vote ID
`poll_id` - poll ID
`time` - voted time
`variant` - variant ID
`steam` - SteamID player voted
`ip` - IP player voted
`name` - nickname player voted

If there are no tables, they will be created automatically )

Version history:


v1.4 (11.19.2010) - Fixed problem with names containing a ' (single quotes)
and:
Installation:

1. Edit your addons/sourcemod/configs/databases.cfg file and add a new section (or declare existing in sm_mysql_polls_dbconfig):
2. Upload the plugin in the 'addons/sourcemod/plugins/' folder and refrash plugin list or change map or restart server.

Web-interface - http://forums.alliedmods.net/showpos...7&postcount=13 (Thanks To crazydog)

Re: MySQL Polls
 

Will this work in any game or is it specific to CSS for some reason?

Re: MySQL Polls
 

I quickly looked over the code, and from what I can tell it has nothing specific to CS:S, although the plugin author may only want to support it for CS:S. You could always give it a try on TF2.

Re: MySQL Polls
 

Sorry, i did not test for other. I think it will work on other gameservers. )

Re: MySQL Polls
 

I'll drop it on my server tonight, I was thinking about writing this exact plugin a few weeks ago :grrr: :up:

Re: MySQL Polls
 

@Cep}|{
Please use Sourcemod's MAXPLAYERS define rather than creating your own.

Re: MySQL Polls
 

I'm not the most experienced sourcemod programmer, so please take these criticisms with a grain of salt.

All of the queries are non-threaded. Normally a few SQL queries that take 10ms each to complete are not much of a problem. But when one of those queries winds up taking 500ms, it can result in a poor gaming experience. Whenever possible, you should use SQL_TQuery instead of SQL_Query or SQL_FastQuery, because it keeps the delays from slow queries out of the main server thread.

The plugin makes many calls to SQL_Query without ever calling CloseHandle on the results. This is a memory leak.

The plugin makes redundant queries. It would be better if the active polls were queried OnMapStart, then stored in a DataPack. But this isn't such a big deal if the queries are made threaded.

The tables are not designed well. The 'id' field in the votes table is unnecessary, and poor choices for primary key. were made in all the tables.

Here's what I recommend for the table structure:
CREATE TABLE IF NOT EXISTS `%s_polls` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128 ) NOT NULL,
`title` varchar(128 ) NOT NULL,
`time` int(11) NOT NULL,
`stat` int(1) NOT NULL,
`server_id` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
INDEX (`stat`, `time` DESC)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;", sm_mysql_polls_prefix);

I added an index on the (`stat`, `time`) tuple to make it faster to find active questions, ordered by time.

CREATE TABLE IF NOT EXISTS `%s_variants` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`poll_id` int(11) NOT NULL,
`name` varchar(128 ) NOT NULL,
PRIMARY KEY (`poll_id`, `id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
I made the primary key a joint one between poll_id and id. This way queries for variants will be efficient.

CREATE TABLE IF NOT EXISTS `%s_votes` (
`poll_id` int(11) NOT NULL,
`time` int(11) NOT NULL,
`variant` int(11) NOT NULL,
`steam` varchar(32) NOT NULL,
`ip` varchar(15) NOT NULL,
`name` varchar(128 ) NOT NULL,
INDEX (`steam`, `poll_id`),
INDEX (`ip`, `poll_id`),
INDEX (`poll_id`, `variant`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I removed the id field and the primary key. The (`steam`, `poll_id`) and (`ip`, `poll_id`) indexes will speed the queries that test the existence of votes. The (`poll_id`, `variant`) will make counting votes fast.

The plugin uses a series of several queries to find the first poll that a user has not answered. This could instead be done with a single query.

This one assumes you are checking identity by steam id. Checking by ip is very similar. This query returns the id of the first poll that has not been answered by a user, or it returns no rows when a user has answered all the poll questions.
SELECT %s_polls.id FROM %s_polls LEFT JOIN (SELECT poll_id, variant FROM %s_votes WHERE `steam` = '%s') AS votes ON %s_polls.id = votes.poll_id WHERE stat = 1 AND variant IS NULL ORDER BY %s_polls.time DESC LIMIT 1

Hope this helps.

Re: MySQL Polls
 

Done.

Re: MySQL Polls
 

Did you not upload the edited version?

Re: MySQL Polls
 

Sorry. I upload a new file on 02.05.2010, but accidentally uploaded the old.
I am upload new version now.