PDA

View Full Version : Good methods for user accounts? [SQLite help needed also]


Outbreak
17/11/2009, 01:17 AM
What's some of the best methods for creating user accounts and storing data.

I've been using a method I saw in a godfather gamemode, but it looks pretty crap and very inefficient.

I made another gamemode and for the user accounts I used dini.

Im just wondering are there any better methods?

Thanks.

(.Aztec);
17/11/2009, 02:04 AM
djson, use search.

Y_Less
17/11/2009, 09:22 AM
The YSI method is very different from all these - instead of being name based it's ID based and uses an index to look up your user file. This means you can have multiple nicknames associated with your account - if you change your name you don't need to reset your account, you just associate the new one with the old one. It also uses ID based files so there's no worries about invalid characters. And as I said it uses a set if index files for fast ID lookup.

Nameless303
17/11/2009, 02:21 PM
The YSI method is very different from all these - instead of being name based it's ID based and uses an index to look up your user file. This means you can have multiple nicknames associated with your account - if you change your name you don't need to reset your account, you just associate the new one with the old one. It also uses ID based files so there's no worries about invalid characters. And as I said it uses a set if index files for fast ID lookup.

But the YSI method is a include, isn't it?
Sow, is there a script that already uses the YSI method?

Thanks (:

Y_Less
17/11/2009, 09:44 PM
No, that's why it's an include.

Nameless303
18/11/2009, 12:13 AM
No, that's why it's an include.

No I mean a script that uses your include, so I can learn how to script with it.

Outbreak
18/11/2009, 12:22 AM
Which one would be best for quick ip lookups?

For example, I want to make a ban system, which will work with the account system.

So if a player is banned I want to make a command to check any other names banned on that ip address and range, and check the ip addresses used by a player.

Am I right in thinking I'd need a database for this rather than it all in sepperate files?

_Vortex
18/11/2009, 04:29 AM
No, that's why it's an include.

No I mean a script that uses your include, so I can learn how to script with it.


As far as I'm aware, it's built into the include.

Correct me if I'm wrong.

Y_Less
18/11/2009, 08:03 PM
All you need to do to use the YSI user system is include YSI - it will work with no additional code. To learn more about YSI check the wiki:

http://wiki.sa-mp.com/wiki/YSI:Main_Page

You couldn't really use an account system for checking banned IPs as they could change their name and you have no way (in any user system) to associate that name with a banned account to check the IPs.

Basically you need to do that separately to any other user system, but whether that's in a database or file is entirely up to you.

Outbreak
19/11/2009, 12:38 AM
You couldn't really use an account system for checking banned IPs as they could change their name and you have no way (in any user system) to associate that name with a banned account to check the IPs.


what I mean is I need to log players names and ip addresses, and I'll make a command to gather the data.

So if I need to search for names on one ip address or one ip range, or search for ip addresses associated with a certain user.

I'm not sure how to set that up.

For the account system I'll check out YSI or if that's complicated I may try dudb.


What about gathering something like hard drive serial number for banning a player, would make it near impossible to evade.

I've heard they do this on xbox360 with some games. Is it possible in pawno and samp?

BP13
19/11/2009, 02:02 AM
Its not possible for SA:MP. I herd storing files/IP's on a MYSQL database it very efficient.

cyber_punk
19/11/2009, 05:18 AM
The YSI method is very different from all these - instead of being name based it's ID based and uses an index to look up your user file. This means you can have multiple nicknames associated with your account - if you change your name you don't need to reset your account, you just associate the new one with the old one. It also uses ID based files so there's no worries about invalid characters. And as I said it uses a set if index files for fast ID lookup.



Sorry but as much as I love YSI I hated the way user accounts were handled, it was a pain in the ass to hit the index file when I could just goto the folder and find the file by users name.

Sergei
19/11/2009, 05:56 AM
The best way is definitely storing data in SQL database.

Y_Less
19/11/2009, 12:02 PM
The YSI method is very different from all these - instead of being name based it's ID based and uses an index to look up your user file. This means you can have multiple nicknames associated with your account - if you change your name you don't need to reset your account, you just associate the new one with the old one. It also uses ID based files so there's no worries about invalid characters. And as I said it uses a set if index files for fast ID lookup.



Sorry but as much as I love YSI I hated the way user accounts were handled, it was a pain in the ass to hit the index file when I could just goto the folder and find the file by users name.


But that way assumes that:

a) You only have one name per account, which YSI doesn't restrict, others do. I went for the IRC style users where you can have multiple names per account.

b) Names have no special characters.

Edit: See my next post, there's nothing actually tying you to this file layout (or indeed files) in the front and of the YSI user system, so you could do it any other way.


The best way is definitely storing data in SQL database.


Do you have any sort of empirical evidence for that? A well structured file system can rival a generic database - bespoke code is always better than generic code because it can be highly tailored.

MenaceX^
19/11/2009, 12:03 PM
The best way is definitely storing data in SQL database.
MySQL / SQLite? ;)
I myself prefer SQLite, even the sa-mp package came up with an include..

Y_Less
19/11/2009, 12:15 PM
Sorry, forgot to reply to this one.


what I mean is I need to log players names and ip addresses, and I'll make a command to gather the data.

So if I need to search for names on one ip address or one ip range, or search for ip addresses associated with a certain user.

I'm not sure how to set that up.


As I said that would be separate to any account system as they could change their name, thus loosing all relation to the ban data. There are a number of ban systems already you may wish to look into.


For the account system I'll check out YSI or if that's complicated I may try dudb.



#include <YSI>

#define LOAD:%1(%2) \
forward LoginDat_%1(%2); \
public LoginDat_%1(%2)


main()
{
}

// Save data
// The player's playerid and their globally unique ID.
public OnPlayerLogout(playerid, yid)
{
// Set the unique identifier for the mode.
// This is so all your data is easily found and loaded
Player_SetTag("my_mode");

// Save the data for the player
Player_WriteInt("kills", gPlayerKills[playerid]);
Player_WriteInt("deaths", gPlayerDeaths[playerid]);

// Random other data
Player_WriteString("some_string", "hello");
Player_WriteFloat("some_float", 2.0);
}

// Load data
// Note that this macro doesn't actually exist in the current version of YSI,
// but I've included it above for simplicity.
LOAD:my_mode(playerid, identifier[], text[])
{
// Check which data is being loaded and store it.
// You can use some advanced string methods here such
// as a modified zcmd if you like.
if (!strcmp(identifier, "kills")) gPlayerKills[playerid] += strval(text);
else if (!strcmp(identifier, "deaths")) gPlayerDeaths[playerid] += strval(text);
else if (!strcmp(identifier, "some_string")) // Do something with "hello"
else if (!strcmp(identifier, "some_float")) // Do something with floatstr("2.0")
return 1;
}


IMHO this is vastly simpler than other methods, and it's abstracted to such a point where the underlying save system doesn't matter - it could be the YSI file system, a DUDB file system, a database or anything else. If that's not clear there's plenty more documentation here:

http://wiki.sa-mp.com/wiki/YSI:Players

Also it should be noted that this method of loading and saving data is vastly faster than DINI like methods which opens a file, searches for the data in the whole file, reads, writes or appends it (writing may involve copying the entire file) and closes the file to return, only to do the whole process again for the next piece of information. Writing in YSI buffers a load of data to be written at once and reading loads the entire file at once, passing you any data associated with your mode.


What about gathering something like hard drive serial number for banning a player, would make it near impossible to evade.

I've heard they do this on xbox360 with some games. Is it possible in pawno and samp?


Not without modifying the client.

MX_Master
19/11/2009, 12:38 PM
I think for the good and fast storing of players accounts needs at list INI files system.. Will be better to use INI plugin with C++ functionality instead of PAWN. imho

pagie1111
19/11/2009, 12:44 PM
Y_Less, it seems that whenever someone disagrees with you or doesn't like what you have created, you get offended. Then you try to reason with them to get them to use your creation.

Chill out man.

Outbreak
19/11/2009, 03:14 PM
; ]
Y_Less, it seems that whenever someone disagrees with you or doesn't like what you have created, you get offended. Then you try to reason with them to get them to use your creation.

Chill out man.



Or he was trying to explain the flexability of his creation, advising alternative ways of using it?

Don't jump down peoples throats, i created this topic for help and advice, not for nerds such as yourself to cry.

If you've got nothing to useful to add, just move on...



As I said that would be separate to any account system as they could change their name, thus loosing all relation to the ban data. There are a number of ban systems already you may wish to look into.


I was thinking of a database of some kind storing all accounts, then another storing all IP addresses and the names associated with them. Making it easy to search an IP address or IP range and get the list of names which have entered the server with the specific IP or IP range.

Then if someone is suspected of ban evading, its easier to check and catch them.

I get a lot of players unregistered, get banned, come back with the same name, different IP address. So i'd want to put the banned name in a database, along with the IP address. This would be part of the ban sytem.

Is it possible? Whats the best method for this?

Y_Less
19/11/2009, 04:21 PM
; ]
Y_Less, it seems that whenever someone disagrees with you or doesn't like what you have created, you get offended. Then you try to reason with them to get them to use your creation.

Chill out man.



I get offended by this statement, not by people disagreeing with me.

The point of this topic was to discuss the best ways of doing a user system, which is what I believe my method is. In line with this view I have presented my argument and backed it up with evidence, although admittedly most of it depends on your point of view - YSI is faster than DINI, you can't deny that as I can prove it, but wether you prefer the syntax is entirely up to you, I do so I come at it from that direction, you're free to disagree.

If someone doesn't agree, as they're free to do, then I will attempt to persuade them, yes, I'm not going to deny this. The easiest way is by addressing concerns raised and showing why they're non issues. In the case of cyber_punk's concern about the directory layout, there is no real solution - that is how it is, and I do see his point (I considered it when writing the system), in that case all I could (and did) do was explain WHY it was like that.


I think for the good and fast storing of players accounts needs at list INI files system.. Will be better to use INI plugin with C++ functionality instead of PAWN. imho


This I agree with entirely, and have considered before. I have never said were I stand on the DB/file issue as although custom code is almost always (if not always when written properly) faster, PAWN is slower than C so timings need to be done. But yes, if the file reading was mostly done in a plugin, I'm almost certain (but again, I have no empirical evidence) that it would be faster.

Edit: I do not pretend that YSI is the answer to everything, I know of quite a few problems, but I designed it from experience of the most common problems people had. The reason I keep mentioning it is because people still have these common problems (that's what makes them common).


Or he was trying to explain the flexability of his creation, advising alternative ways of using it?

Don't jump down peoples throats, i created this topic for help and advice, not for nerds such as yourself to cry.

If you've got nothing to useful to add, just move on...


Thank you.



I was thinking of a database of some kind storing all accounts, then another storing all IP addresses and the names associated with them. Making it easy to search an IP address or IP range and get the list of names which have entered the server with the specific IP or IP range.

Then if someone is suspected of ban evading, its easier to check and catch them.

I get a lot of players unregistered, get banned, come back with the same name, different IP address. So i'd want to put the banned name in a database, along with the IP address. This would be part of the ban sytem.

Is it possible? Whats the best method for this?


I'd agree with the database for this (or that's how I'd do it), they're designed specifically for relations like this.

Outbreak
19/11/2009, 08:52 PM
So how would i setup a Mysql database?

I've downloaded the sa-mp plugin 0.15

Not sure what i need to do from there. From what i gather, i'll need a mysql hosting server. I signed up to two free ones, but they're giving me more problems than is time worthy.

Trying to setup a database, the site kept crashing, the same happened with the second one i tried.

So, if i can get mysql database hosting from my server providor, how would i go about setting up the databases, then creating the login and registering code?

If someone could provide a link to a site where i could learn this stuff I'd appreiciate it.

Y_Less
20/11/2009, 08:53 AM
If you're having these problems I'd just use SQLite, it's built into the server so you don't need to do anything.

MX_Master
20/11/2009, 01:56 PM
Earlier I also used a SQLite database to store the accounts of players. However, after I occured some problems in recording data, I decided to return to the old but proven method of storage - INI files. The problem of incorrect recording of data was only on the operating system LINUX. No such problem on WINDOWS.

Y_Less
20/11/2009, 02:32 PM
Out of interest do you have any information on this problem? Such as cases where the problem occurred?

Outbreak
20/11/2009, 04:56 PM
After around 10 minutes trying to learn how to create a database in SQLite, its already doing my head in.

I found a few tutorials on YouTube and on google.

I type the commands exactly as it says in the tutorial, only to get an error message.

All i want to do is create a database and then create the rows and colums for my data to be stored..

SQLite itself was quite scare for windows. I dont use Linux, but my samp server does. So will the database work if i create it in Windows, and use it in Linux?

Y_Less
20/11/2009, 06:06 PM
SQLite is built into the server as I said, you don't need to find it for Windows. The commands should be the same for MySQL and SQLite, they're both based on SQL, but it's up to you how you do it, that was just a suggestion based on the fact that you couldn't get MySQL working.

Outbreak
20/11/2009, 06:24 PM
Yeah i noticed its on wiki, how to use the functions. But how do i create the database?

The only thing i found on wiki was how to open an existing one.

Y_Less
20/11/2009, 06:36 PM
http://www.google.co.uk/search?q=sql+create+database&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-GB:official&client=firefox-a

Not trying to tell you to "search", I just honestly don't know which is the best resource for you. And as I said it's the same syntax for SQLite and MySQL (that's what the "SQL" part is).

Outbreak
20/11/2009, 06:42 PM
I did around 30mins reading and searching, until i found a download called SQLite3.

I downlaoded that, found some tutorials on it, one being on youtube.

It explained how to create a simple table.

so i tried the same method. Using sqlite3 test.db

But i got no luck with that just a like that looked like " ...> "



EDIT:

If i was to open a blank gamemode. Then write db_open("text.db"); under OnGameModeInit

Would that automatically create "test.db" in my scriptfiles folder, but with no content?

Is it that easy?

Y_Less
20/11/2009, 07:34 PM
All I could suggest is try it (also try without the .db), I'm not an expert on the SQL functions.

Dabombber
21/11/2009, 02:10 AM
There's a surprising lack of examples for sqlite in samp considering how easy it is to use. A simple example:


#define GAMEMODE_DATABASE "Gamemode.sqlite"

public OnGameModeInit()
{
new DB:db = db_open(GAMEMODE_DATABASE);
if(db) {
db_free_result(db_query(db, "CREATE TABLE IF NOT EXISTS `Users` ( `name` TEXT NOT NULL PRIMARY KEY , `skin` INTEGER NOT NULL DEFAULT 0 )"));
db_close(db);
}
}

public OnPlayerConnect(playerid)
{
if(!IsPlayerNPC(playerid)) {
new DB:db = db_open(GAMEMODE_DATABASE);
if(db) {
new string[128];
format(string, sizeof(string), "SELECT * FROM `Users` WHERE ( `name` = '%s' ) LIMIT 1", ReturnPlayerName(playerid));
new DBResult:result = db_query(db, string);
if(db_num_rows(result)) {
db_get_field_assoc(result, "skin", string, sizeof(string));
gPlayerSkin[playerid] = strval(string);
}
db_free_result(result);
db_close(db);
}
}
return 1;
}

public OnPlayerRequestSpawn(playerid)
{
if(!IsPlayerNPC(playerid)) {
new DB:db = db_open(GAMEMODE_DATABASE);
if(db) {
new string[128];
format(string, sizeof(string), "SELECT * FROM `Users` WHERE ( `name` = '%s' ) LIMIT 1", ReturnPlayerName(playerid));
new DBResult:result = db_query(db, string);
if(db_num_rows(result)) {
format(string, sizeof(string), "UPDATE `Users` SET `skin` = %i WHERE ( `name` = '%s' )", gPlayerSkin[playerid], ReturnPlayerName(playerid));
} else {
format(string, sizeof(string), "INSERT INTO `Users` ( `name` , `skin` ) VALUES ( '%s' , %i )", ReturnPlayerName(playerid), gPlayerSkin[playerid]);
}
db_free_result(result);
db_free_result(db_query(db, string));
db_close(db);
}
}
return 1;
}


The database can have be any valid filename I think. It's created if it doesn't exist or db_open returns 0 if it can't be opened. Pretty much everything you need to know about sqlite can be found here (http://www.sqlite.org/lang.html).

One thing to be careful of is NULL values, which crash db_get_field_assoc and db_get_field. You can either put a NOT NULL constraint on your columns or do select statements like SELECT ifnull(`skin`,'') AS `skin` FROM `Users` WHERE ( `name` = '%s' ) LIMIT 1

For testing you might want to put something like

stock DBResult:debug_db_query(DB:db, query[])
{
print(query);
return db_query(db, query);
}
#define db_query debug_db_query

just after including a_samp, then if a query doesn't work you can try executing it in something like the firefox addon (http://code.google.com/p/sqlite-manager/) which will tell you if there's an error.

Also, if you're putting text from the user into the database (ban reasons or whatever), you need to escape it strreplace(string "'", "''");

Outbreak
21/11/2009, 02:29 AM
Ah thanks man, this is awesome!

Its had my head in bits trying to work it out.

Is there a tutorial available also, where it is relevent to using SQLite in SAMP?

Thanks again :)