AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   SQL Help\code (https://forums.alliedmods.net/showthread.php?t=87283)

Bugsy 03-09-2009 11:43

SQL Help\code
 
I am trying to convert my VAC ban status plugin (see sig) from using nVault to SQLx. I have no experience in SQL and do not want to experiment with it on peoples servers. Is the AMX-X SQL protocol identical to that used by Windows? If so I could research it myself. The usage I need is fairly basic:

For each player, I need the following data, using the players STEAMID or IP as the lookup key:
0/1 [ban status] 12353543 [time-stamp]

What I need help with:
  • Setting up the db for fresh plugin installs
  • How to look up an entry using steamid\ip and retrieve the respective ban status and timestamp.
  • How to create an entry using items noted above
  • How to delete an entry
  • How to update an item (ie, suppose someone connects and their time-stamp has expired, instead of deleting the entry and creating a new one, just update the ban status and timestamp)

+karma to anyone who can help

Note: Yes, I have read the tutorial by hawk but that looks more geared towards people that already know SQL.

AntiBots 03-09-2009 12:06

Re: SQL Help\code
 
Well, now i see your plugin and I can help you.

Was info you need to save into db?

Bugsy 03-10-2009 01:00

Re: SQL Help\code
 
I need to find a ban value (0 or 1) and a timestamp for when the entry was written for a player. I want to use the players steamid or IP as the look-up key

Bugsy 03-31-2009 23:08

Re: SQL Help\code
 
Bump

|PJ| Shorty 04-01-2009 04:45

Re: SQL Help\code
 
Quote:

Originally Posted by Bugsy (Post 777109)

For each player, I need the following data, using the players STEAMID or IP as the lookup key:
0/1 [ban status] 12353543 [time-stamp]




What I need help with:
  • Setting up the db for fresh plugin installs
  • How to look up an entry using steamid\ip and retrieve the respective ban status and timestamp.
  • How to create an entry using items noted above
  • How to delete an entry
  • How to update an item (ie, suppose someone connects and their time-stamp has expired, instead of deleting the entry and creating a new one, just update the ban status and timestamp)



the query commands are the same like php, but the handle is different. response calls a handle function not just in time (depends on db connection quality, ping)
  • send a CREATE TABLE IF NOT EXISTS query at plugin start
  • simple select query with WHERE, get response in the handle function
  • INSERT query
  • DELETE query
  • SELECT query, check timestamp in the handle function, if necessary make a UPDATE query
my english is very "well", so feel free to ask :wink:
i know, this is a very short explaination... but i´m currently @ work.

Bugsy 04-01-2009 11:23

Re: SQL Help\code
 
Quote:

Originally Posted by |PJ| Shorty (Post 794603)
the query commands are the same like php, but the handle is different. response calls a handle function not just in time (depends on db connection quality, ping)
  • send a CREATE TABLE IF NOT EXISTS query at plugin start
  • simple select query with WHERE, get response in the handle function
  • INSERT query
  • DELETE query
  • SELECT query, check timestamp in the handle function, if necessary make a UPDATE query
my english is very "well", so feel free to ask :wink:
i know, this is a very short explaination... but i´m currently @ work.

I do not know php or SQL and I do not want to experiment with this plugin that will be running on peoples servers. I'm hoping someone can contribute code.

|PJ| Shorty 04-02-2009 02:28

Re: SQL Help\code
 
some questions:

- steamid allowed more than once in the db?
- timestamp is the info when the ban expired? if expired delete the db entry?

Bugsy 04-03-2009 00:23

Re: SQL Help\code
 
Just one steam id in the db per player.

timestamp is set when the player connects for the first time (there is no entry in the db for this steamid) I want to retrieve this value on this players future connections and verify if the timestamp is expired, if so, delete entry.

My goal is to store a players VAC ban status so that a player will not keep getting re-checked every time he connects. If a VAC ban is found, the timestamp can be set to 0 or some other non-expireable value. If no VAC ban found then set a current timestamp so it can be later compared at future connections. The logic is once a player is banned then there is no possibility that he will be unbanned as VAC2 bans are permanent so no need to expire any db entries. However, if a player connects and has no VAC ban on record, there is a possibility that he can still be banned so we want to check this occasionally. To prevent the server from constantly checking the steam site for ban status I would rather store the ban status locally via the db and only check every 2 weeks or so, this is where the timestamp comes into play.

|PJ| Shorty 04-03-2009 15:46

Re: SQL Help\code
 
2 Attachment(s)
here is an example code, not tested but should work and you can see how this can be done.

select_user_db(id) search a player in the db. if exists store status and time-stamp from db to global vars, if not add the player to the db ( see line 98 )

update_user_db(id,status,timestamp) to update status and timestamp from a player in the db

delete_user_db(id) to delete a player from the db


if you need help feel free to ask :wink:

Bugsy 04-05-2009 09:27

Re: SQL Help\code
 
I added a few server_prints to the source so the error lineage may be 1 or 2 off in the source.

Code:

L 04/05/2009 - 09:25:35: [AMXX] Plugin ("sqlx_example.amxx") is setting itself as failed.
L 04/05/2009 - 09:25:35: [AMXX] Plugin says: SQL Query failed.
L 04/05/2009 - 09:25:35: [AMXX] Displaying debug trace (plugin "sqlx_example.amxx")
L 04/05/2009 - 09:25:35: [AMXX] Run time error 1: forced exit
L 04/05/2009 - 09:25:35: [AMXX]    [0] sqlx_example.sma::SelectHandle (line 115)



All times are GMT -4. The time now is 08:53.

Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.