AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Snippets and Tutorials (https://forums.alliedmods.net/forumdisplay.php?f=112)
-   -   [DB] Database Manager (https://forums.alliedmods.net/showthread.php?t=60036)

Nican 08-25-2007 17:18

[DB] Database Manager
 
19 Attachment(s)
Database manager

Description:
Different plugins may require different tables to get user informations, like XP, level, money, kills, deaths and bla bla bla... This plugin/stock automatically select info from table, create columns where necessary and makes it easy for people that want to use the database

How it works
Database manager detects all columns from the sm_users. Plugins can enable,disable the selecting of each column, or create a new column if needed.

Cvars
  • sm_db_config (deafult: "default") - The connection DB manager will use
  • sm_db_persit (deafult: "1") - Set to true to use persistant connection
  • sm_db_autolite (deafult: "1") - If the DB does not exist, SQLite will be used

Installation:
  • MySQL
    First, go to the file addons\sourcemod\configs\databases.cfg , and edit the "default" connection to your needs, it shouldn't be complicated, It should end up looking something like this:
    PHP Code:

    "default"
        
    {
            
    "driver"        "default"
            "host"            "localhost"
            "database"        "sm"
            "user"            "root"
            "pass"            ""
            
    //"timeout"            "0"
            //"port"            "0"
        


    And the leave the plugin to do the rest
    -----------------------------------
  • SQLite
    Change the cvar sm_db_config to "storage-local"
    and make sure that in addons\sourcemod\configs\databases.cfg this exists:
    PHP Code:

        "storage-local"
        
    {
            
    "driver"            "sqlite"
            "database"            "sourcemod-local"
        


  • Put a_mysql.smx file into your /addons/sourcemod/plugins folder, you can easily get the a_mysql.smx file by clicking at "Get Plugin" at the end of this message


FAQ:
  • I am getting an error "Can't connect to local MySQL server through socket '/tmp/mysql.sock'"
    • If you have acess to root in your linux computer, execute this in shell "ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock"



Example
See next post

ChangeLog:
  • August 25, 2007
    • Initial Release
    • Fixed Float Adding
    • Started Lock tables
    • Removed all FastQueries
  • August 26, 2007
    • Removed all the connecting/disconecting
    • Added cvars
    • Made it a lot user to use
  • August 27, 2007
    • Added SQLite support
    • Started using atd_array, there is no more limits now
  • August 29, 2007
    • MAJOR UPDATE!
  • August 30, 2007
    • Fixed adt_size array bug
  • September 06, 2007
    • Added new cvar
  • September 15, 2007 (3.1)
    • Changed some "new" to "decl"
    • Removed some fail states
  • January 10, 2007 (4.0)
    • Now the plugin will story all data in 2 Adt arrays.
    • Fixed possible error of things
    • Changed the way the plugin saves data on the database.
    • Added some extra checks to prevent errors.
  • January 21, 2008
    • Data is now saved on every round start.

Notes:
Put the globaldb.inc in addons\sourcemod\scripting\include

Nican 08-26-2007 23:20

Re: [MySQL] Database Manager
 
Exemples:

Simple bank plugin:

PHP Code:

new db_money;

public 
OnPluginStart()
{
}

public 
DB_OnRowsUpdated(){
    
db_money DB_GetColumnId("my_money"truetrueDB_INT);
    if(
db_money == -1)
        
SetFailState("[SMbank] Could not create money column!");
}

stock GetUserMoney(client){
    return 
DB_GetColumnclientdb_money );
}

stock SetUserMoney(clientmoney){
    return 
DB_SetInfo(clientdb_money money);



This function is called right after the server receives information from the database.
DO NOT try to use DB_GetColumn on player OnClientConnect or anything of the type.
PHP Code:

public DB_OnClientUpdate(client){
     
//Do something...


Note: Information is saved on OnClientDisconnect_Post, so it is OK to do this:
PHP Code:

public OnClientDisconnect(client){
    
DB_SetInfoclientdb_money5000 );



Functions
PHP Code:

#define DB_INT 0
#define DB_STRING 1
#define DB_FLOAT 2

/**
 * Gets the ID of a row
 * @param RowName Name of the mysql Column you want to get
 * @param autoenable To enable this row on select query
 * @param autocreate If the row does not exist, create it
 * @param rowtype row type if it is being created
 * @param rowsize row size if it is being created, 0 = default, float has no size
*/

native DB_GetColumnId(const String:RowName[], bool:autoenable truebool:autocreate falserowtype DB_INTrowsize 0);

/**
 * Gets the ID of a row
 * @param Enable Row id from fetching when client connect
*/
native DB_EnableIdColumnId );

/**
 * Gets the ID of a row
 * @param Disable Row Id from fetching when client connect
*/
native DB_DisableIdColumnId );

/**
 * Gets the ID of a row
 * @param client Client ID for set info
 * @param ColumnId The column ID for set info
 * @param value The information to set it to
*  @param add Set to true if you want to add the value to the old amount
*/
native DB_SetInfoclientColumnIdany:value booladd false);
native DB_SetInfoStringclientColumnId, const String:name[] );


/**
 * Gets the ID of a row
 * @param client Client ID for get info
 * @param ColumnId The column ID for get info
*/
native any:DB_GetColumnclient ,  ColumnId );
native DB_GetColumnStringclientColumnIdString:value[], maxsize);


/**
 * Gets the ID of a row
 * @param RowName Name of the mysql Column you want to get
*/
native DB_ColumnTypeColumnId );

/**
 * Returns the DB handle, for your own personal use
*/
native Handle:DB_GetHandle( );


/**
 * Gets the ID of a row
 * @param RowName The name of the new row
 * @param RowType Use DB_* and define the new row type
 * @param Size The size of this now row, leave blank for default
 * @return RowId, -1 on failure
*/
stock DB_CreateRow( const String:Rowname[], RowtypeDB_INTsize ){
    return 
DB_GetColumnId(RowNametruetrueRowtype size);
}

/**
 * Called when info of a client has been updated
 * @param client client
 */
forward DB_OnClientUpdate(client); 


kipplitz 08-27-2007 00:52

Re: [MySQL] Database Manager
 
Ok, completely new here at this...so walk with me...do I need a mysql database to use this as well as the bank mod you have? Give me some insight here oh great one! :)

KippLitz

Rainbow 6 08-29-2007 18:34

Re: [DB] Database Manager
 
Which one do we need or do we need both?

Nican 08-29-2007 21:13

Re: [DB] Database Manager
 
Just the a_mysql.sp

tkaway69 08-29-2007 21:33

Re: [DB] Database Manager
 
I know this will be Noobish, but it would be nice if you had some kind of instalation instructions. We would like to use the Bank mod you fixed up. We are just not real Mysql savy. Thanks.

Rainbow 6 08-29-2007 22:18

Re: [DB] Database Manager
 
I will try to come up with one as soon as i get the bank mod + the mysql Manager plugin to work.

tkaway69 08-29-2007 23:06

Re: [DB] Database Manager
 
Thanks. I assume we can use the mysql database for our website? Waiting for some info.

Nican 08-29-2007 23:40

Re: [DB] Database Manager
 
Ok... I just majorly update it... there was a major error...


And what do you mean by "use the mysql database for our website"

tkaway69 08-30-2007 11:01

Re: [DB] Database Manager
 
Well I am not sure how this mysql stuff works, but we have a msql that holds all the info to our forums and such. I was assuming we could use that to create a database for the server as well.

Rainbow 6 08-30-2007 16:49

Re: [DB] Database Manager
 
you as in haveing a page that displays how much money you have on the server?
if so yes you can do that and if you pm me with your e-mail address i can send you a page but you will have to fill in the database info yourself
Rainbow 6

tkaway69 08-30-2007 19:44

Re: [DB] Database Manager
 
Ok. I sent you the pm. Thanks.

Rainbow 6 08-31-2007 23:17

Re: [DB] Database Manager
 
it will take me some time to get it all worked out but when i am done i will send it your way

kipplitz 09-01-2007 17:47

Re: [DB] Database Manager
 
Completely foreign to me at this point...I can't get the Database Manager to load and run without erroring. Here are snippets form logs and configs.

L 09/01/2007 - 16:39:28: [SM] Plugin encountered error 25: Call was aborted
L 09/01/2007 - 16:39:28: [SM] Native "SetFailState" reported: [MYSQLmanager] Fail state: Could not connect to mysql
L 09/01/2007 - 16:39:28: [SM] Debug mode is not enabled for "a_mysql.smx"
L 09/01/2007 - 16:39:28: [SM] To enable debug mode, edit plugin_settings.cfg, or type: sm plugins debug 5 on

L 09/01/2007 - 16:39:28: [a_mysql.smx] [MYSQLmanager] [2005]: Unknown MySQL server host 'db1107.perfora.net' (11004)

"Databases"
{
"driver_default" "mysql"

"default"
{
"driver" "mysql"
"host" "db1107.perfora.net"
"database" "db215923511"
"user" "*********"
"pass" "********"
//"timeout" "0"
//"port" "0"
}

"storage-local"
{
"driver" "sqlite"
"database" "sourcemod-local"
}
}


Any guidance appreciated.

Thanks,

KippLitz

tkaway69 09-01-2007 19:09

Re: [DB] Database Manager
 
Quote:

Originally Posted by kipplitz (Post 526105)
Completely foreign to me at this point...I can't get the Database Manager to load and run without erroring. Here are snippets form logs and configs.

L 09/01/2007 - 16:39:28: [SM] Plugin encountered error 25: Call was aborted
L 09/01/2007 - 16:39:28: [SM] Native "SetFailState" reported: [MYSQLmanager] Fail state: Could not connect to mysql
L 09/01/2007 - 16:39:28: [SM] Debug mode is not enabled for "a_mysql.smx"
L 09/01/2007 - 16:39:28: [SM] To enable debug mode, edit plugin_settings.cfg, or type: sm plugins debug 5 on

L 09/01/2007 - 16:39:28: [a_mysql.smx] [MYSQLmanager] [2005]: Unknown MySQL server host 'db1107.perfora.net' (11004)

"Databases"
{
"driver_default" "mysql"

"default"
{
"driver" "mysql"
"host" "db1107.perfora.net"
"database" "db215923511"
"user" "*********"
"pass" "********"
//"timeout" "0"
//"port" "0"
}

"storage-local"
{
"driver" "sqlite"
"database" "sourcemod-local"
}
}


Any guidance appreciated.

Thanks,

KippLitz

Note: We also tried leaving..

"driver" "mysql" as.... "driver" "default"

still errors.

Nican 09-01-2007 22:41

Re: [DB] Database Manager
 
Did you read the part of:
Unknown MySQL server host 'db1107.perfora.net'

Source mod could not connect to db1107.perfora.net

tkaway69 09-02-2007 01:32

Re: [DB] Database Manager
 
Yea turns out the host we were using did not allow this type of usage.

kipplitz 09-02-2007 02:39

Re: [DB] Database Manager
 
ok, setup a free mysql database and I think it is working. It appears to connect to the data base (no more errors) and it appears to keep track of money. When myself and two others got in the server to try it, we immediately had these HUGE amounts of money in our accounts when we ran bank status. Then we deposited 1000 in to our accounts and then it said we had 50,000. Any ideas?

kipplitz 09-02-2007 15:31

Re: [DB] Database Manager
 
ok...weird.

Last night after I setup the mysql database and the database manager along with bank mod, it acted all weird and in the database there was no data. Now today it appers fine and the database is populated. All new joining players start with 0. Maybe it is like a good bread and just needed time for the yeast to rise? lol.

Nican 09-02-2007 17:48

Re: [DB] Database Manager
 
I am confused... D:

Stinkyfax 09-11-2007 11:25

Re: [DB] Database Manager
 
nice plugin, i'm using it on my server now in case it's required by bank.
I hope you write more awesome plugins )

Shaman 09-11-2007 11:45

Re: [DB] Database Manager
 
Questions
1-How does this work? How does this save the information?
2-Can you give some examples:
-Creating a column if it doesn't exist
-Reading and writing to columns named "xp"(integer) "name"(string)

Suggestions
1-Can you remove 'add'(bool) and add 'todo'(string) to DB_SetInfo? ('todo' can be 'set', 'inc' or 'dec')
2-Saving and reading to custom ID's:
PHP Code:

public bool:BuildDB()
    {
    if(!
DB_CheckIDTable("shamans_plugin_players")) //Checks the table for existance (This means there will be multi-table support.)
        
{
        
DB_CreateIDTable("shamans_plugin_players"); //Creates a table
        
DB_SelectIDTable("shamans_plugin_players"); //Selects the table
        
DB_CreateIDTableColumn("xp"); //Creates new column named "xp"
        
DB_CreateIDTableColumn("money"); //Creates new column named "money"
        
DB_CreateID("default"); //Creates a new id named "default"
        
DB_SetIDValue("default""xp""100"); //Sets "xp" to "100" for id named "default"
        
DB_SetIDValue("default""money""200"); //Sets "money" to "200" for id named "default"
        
return true;
        }
    return 
false;
    }

public 
CheckXP(client)
    {
    if(!
DB_SelectIDTable("shamans_plugin_players")) //Tries to select the table
        
{
        
PrintToChatAll("Cannot select "Players" table!");
        }
    new 
xp;
    new 
String:name[MAX_NAME_LENGHT];
    
GetClientName(clientnameMAX_NAME_LENGHT);
    if(!
DB_CheckID(name)) //Checks id for existance
        
{
        
DB_CreateIDFromOtherID(name"default"); //Creates a new id using values from id "default"
        
DB_GetIDValue(name"xp"s_xpsizeof(s_xp)); //Gets the value of "xp"
        
xpStringToInt(s_xp);
        
PrintToChat(client"Account not found. Created new account with %i xp!"xp);
        }
        else
        {
        new 
String:s_xp[32];
        
DB_GetIDValue(name"xp"s_xpsizeof(s_xp)); //Gets the value of "xp"
        
xpStringToInt(s_xp);
        
PrintToChat(client"Account found. Your xp is %i!"xp);
        if(
xp<100)
            {
            new 
bool:luckbool:GetRandomInt(01);
            if(!
luck)
                {
                
PrintToChat(client"Your XP is too low. Account deleted!");
                
DB_DeleteID(name); //Deletes the id
                
xp= -1;
                }
                else
                {
                
PrintToChat(client"Your XP is too low; but you are lucky!");
                
DB_EmptyID(name); //Sets all values for the id to "0"
                
DB_SetIDValueFromOtherID(name"xp""default"); //Copies "xp" value from "default"
                
xp100;
                }
            }
        }
    return 
xp;
    } 

The second suggestion will be better for everybody; because it's more flexible. ID's can be a player's name, ip or steam id. Maybe it can be a setting for a plugin like "DB_CreateID("simple_plugin_enabled");".

Nican 09-11-2007 14:42

Re: [DB] Database Manager
 
Well, the whole idea of the plugin is people don't have to select tables, create users and bla bla bla...

SM Bank: MySQL is the only published plugin I finished that is based on this:
http://forums.alliedmods.net/showthread.php?t=60110

I tried to make it as easy as possible... for example, for get player XP, you can just:

PHP Code:

new row_xp;
public 
DB_OnRowsUpdated(){
       
//This will automatically create a int row called PluginXP
    
row_xp DB_GetColumnId("PluginXP"truetrueDB_INT);
    if(
row_xp == -1)
        
SetFailState("Fail state: Could not create row!");
}

stock GetPlayerXP(client){
   return 
DB_GetColumnclientrow_xp );
}

//The 4 argument, "add", if set to true it will add to the old value
//For exemple, if the player XP is 100, and you use SetPlayerXP(client, 50, true);, now player XP will be 150
stock SetPlayerXP(clientamountbool:add=false){
   
DB_SetInfoclientrow_xpamount,add);
}

//Called when clients connect and his data is prefetched from the table
public DB_OnClientUpdate(client){
   if(
GetPlayerXP(client) == 0)
       
SetPlayerXP(client100);


I do like the idea of setting a default value... I think I will add that later
and maybe a cvar changing what player id will be

Shaman 09-11-2007 16:39

Re: [DB] Database Manager
 
But you use "client". So it's saved with Name, IP or SteamID and it's not enough. Players' change names, some players have dynamic IP's and this can't be used to save plugin settings.

Nican 09-11-2007 17:10

Re: [DB] Database Manager
 
if SteamID is not enough, I wonder how you are keeping data :O

and player data are saved when they disconnected, with their Steam_id as id

tkaway69 09-12-2007 23:23

Re: [DB] Database Manager
 
Yea even I am lost on that comment.

mooshotty 09-15-2007 02:56

Re: [DB] Database Manager
 
i'm sorry. i'm a complete noobie to SourceMod. A mani transfer over.

So I'm trying to set up the bank.

I have no idea how to setup mysql. So i'm not sure if I have a database made somewhere on my server, nor do I know if I dont have a db, what I do to set one up. If somebody could give me damn near a step by step instruction to installing both bank and the database manager I would really appreciate it

Nican 09-15-2007 02:58

Re: [DB] Database Manager
 
The same way you setup a plugin...
Just put both .smx files into your plugins folder

tkaway69 10-01-2007 13:04

Re: [DB] Database Manager
 
Hey "Nican" how can I get rid of this error. Thanks for your help. We are still using a "free" Mysql Database. I was thinking maybe the database was being slow or something.

L 10/01/2007 - 00:19:45: [SM] Native "SQL_FetchRow" reported: Invalid query Handle 0 (error: 4)
L 10/01/2007 - 00:19:45: [SM] Debug mode is not enabled for "a_mysql.smx"
L 10/01/2007 - 00:19:45: [SM] To enable debug mode, edit plugin_settings.cfg, or type: sm plugins debug 6 on

Nican 10-01-2007 20:30

Re: [DB] Database Manager
 
Change to SQLite
Apparently your MySQL is not being able to make complete requests, and failing in gathering player data

Scope 10-13-2007 00:59

Re: [DB] Database Manager
 
Where does the sql lite make the database at when you do the local database thing ?

Ive looked through all my files and cant find the database it has created

Nican 10-13-2007 10:52

Re: [DB] Database Manager
 
/sourcemod/data/sqlite/sourcemod-local.sq3

tkaway69 10-21-2007 23:06

Re: [DB] Database Manager
 
Ok.. What exactly does it take to get this to store locally. When we try to transfer money we get "no one connected". and this error is showing up.

Code:

L 10/21/2007 - 21:42:03: [SM] Native "SQL_FetchRow" reported: Invalid query Handle 0 (error: 4)
L 10/21/2007 - 21:42:03: [SM] Debug mode is not enabled for "a_mysql.smx"

We were using a data base but until we upgrade it is no good. So I am trying to store bank info locally. I just want to make sure I have it correct. We have this at start of log but obviously something is not right.
Code:

L 10/21/2007 - 20:11:01: [a_mysql.smx] DB MANAGER SAYS:
L 10/21/2007 - 20:11:01: [a_mysql.smx] Unable to connect to default configuration
L 10/21/2007 - 20:11:01: [a_mysql.smx] Using SQLite instead...

Added
Code:

sm_db_config storage-local
to server.cfg. Hopefully this fixes it.

Well I am lost. Still getting errors even after adding the storage local line. Still cannot transfer money between players.

Code:

L 10/22/2007 - 18:39:16: Info (map "de_dust2") (file "errors_20071022.log")
L 10/22/2007 - 18:39:16: [SM] Native "SQL_FetchRow" reported: Invalid query Handle 0 (error: 4)
L 10/22/2007 - 18:39:16: [SM] Displaying call stack trace for plugin "a_mysql.smx":
L 10/22/2007 - 18:39:16: [SM]  [0]  Line 270, a_mysql.sp::GetClientConnectInfo()
L 10/22/2007 - 18:39:17: [SM] Native "SQL_FetchRow" reported: Invalid query Handle 0 (error: 4)


Nican 10-22-2007 21:50

Re: [DB] Database Manager
 
Can you tell me what Version of this plugin are you using (type "sm plugin list" in console) and can you attach the DB you were using? (/sourcemod/data/sqlite/sourcemod-local.sq3)


I am sorry for the late response, I am having a horrible time lately

tkaway69 10-22-2007 22:10

Re: [DB] Database Manager
 
1 Attachment(s)
Sorry to hear that. Hopefully things will loosen up for you.
Code:

metamod_version "1.4.2.414"
sourcemod_version "1.0.0.1644"
07 "Database manager" (3.1) by Nican132
08 "SM Bank" (2.2) by MaTTe, edit by Nican

Hope this helps.

sessus 10-25-2007 00:00

Re: [DB] Database Manager
 
hey Nican,

any chance you'd be able to implement an option to creat all the tables based on the date? For example: when no new information has been recorded for a cetrain steam_ID that then all those table entries are deleted. This would help to keep the database small.

tkaway69 10-26-2007 23:51

Re: [DB] Database Manager
 
What exactly does this "There is not one connected" message mean? That may be a start in finding out what is going on. With the bank and transfering money.
This error is filling my logs.

Code:

L 10/26/2007 - 22:37:05: [SM] Native "SQL_FetchRow" reported: Invalid query Handle 0 (error: 4)
L 10/26/2007 - 22:37:05: [SM] Debug mode is not enabled for "a_mysql.smx"


sessus 10-27-2007 03:22

Re: [DB] Database Manager
 
just realised sm_db_mapload is not even defined in the .sp file. Is it still working?

Nican 10-27-2007 10:21

Re: [DB] Database Manager
 
Ops... my bad

I removed it after I discovered that all players are disconnected and reconnected when map changes, so client_disconnect is taking care of it

Nican 01-10-2008 21:47

Re: [DB] Database Manager
 
I am back on-line.

I updated this plugin, and tested on saving string, float and integer values, and they are all working.

I am now working to add:
- Key / Value table
- Allow plugins to force the MySQL to update / select information from the database.
- Added cvars to allow more frequent UPDATE queries.


All times are GMT -4. The time now is 18:51.

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