search

haneefmubarak / Aixen

A partially distributed online multiplayer service
MIT License
13 stars 0 forks source link

Database structure #3

Open XePeleato opened 10 years ago

XePeleato commented 10 years ago

Sorry, I was busy and i have created the Db as i said in #1 I think that for a basic system at the moment, it's fine. We need to work with the Xenia author because he will tell us what functions are and are not going to be implemented. I have created the database like this:

Table 1:GamerData: ID - AN ID it's always useful Gamertag - The gamertag, i don't know if it's prohibited to use this name Password - For the gamertag Gamescore - "The "G" Points Email - The email of the user Rep - 1 to 5 stars Banned - 1 or 0 Reports - Number of times reported Admin - I think this can be useful with the panel

Second Table: LiveSystem // At the moment it's for statistics more than for configuration. Online - 1 or 0 if it is under maintenance Registered - Number of registered gamertags

Please feel free to tell if you think that something needs to be added or deleted. I have uploaded the .sql to my server for downloading it. http://82.165.170.28/Aixen.sql

substanc3-dev commented 10 years ago

Password will be hashed with SHA-512. Gamertag will be just ok. Server MySQL ok?

XePeleato commented 10 years ago

Yes, server OK

haneefmubarak commented 10 years ago

Actually, I'd prefer if we were to use the 256 bit version of BLAKE2 for 64-bit processors.

On Wednesday, March 12, 2014, XePeleato notifications@github.com wrote:

Yes, server OK

Reply to this email directly or view it on GitHubhttps://github.com/haneefmubarak/Aixen/issues/3#issuecomment-37444928 .

NOTICE: This e-mail (and any attachments) may contain PRIVILEGED OR CONFIDENTIAL information and is intended only for the use of the specific individual(s) to whom it is addressed. It may contain information that is privileged and confidential under state and federal law. This information may be used or disclosed only in accordance with law, and you may be subject to penalties under law for improper use or further disclosure of the information in this e-mail and its attachments. If you have received this e-mail in error, please immediately notify the person named above by reply e-mail, and then delete the original e-mail. Thank you.

substanc3-dev commented 10 years ago

But the password will need to be hashed also on client.

BTW: @haneefmubarak Did you see the servers i sent you by email?

haneefmubarak commented 10 years ago

Also, @XePeleato, we should implement it however we like, seeing as that we should be able to add new fields dynamically as we wish. I'm leaning heavily towards using a NoSQL solution, I just need to see which one has the best client driver for C.

I think it would be great I we could make the service generic so it can be extended to support other games and or platforms in the future as clients.

On Wednesday, March 12, 2014, Haneef Mubarak haneef503@gmail.com<javascript:_e(%7B%7D,'cvml','haneef503@gmail.com');> wrote:

Actually, I'd prefer if we were to use the 256 bit version of BLAKE2 for 64-bit processors.

On Wednesday, March 12, 2014, XePeleato notifications@github.com wrote:

Yes, server OK

Reply to this email directly or view it on GitHubhttps://github.com/haneefmubarak/Aixen/issues/3#issuecomment-37444928 .

NOTICE: This e-mail (and any attachments) may contain PRIVILEGED OR CONFIDENTIAL information and is intended only for the use of the specific individual(s) to whom it is addressed. It may contain information that is privileged and confidential under state and federal law. This information may be used or disclosed only in accordance with law, and you may be subject to penalties under law for improper use or further disclosure of the information in this e-mail and its attachments. If you have received this e-mail in error, please immediately notify the person named above by reply e-mail, and then delete the original e-mail. Thank you.

NOTICE: This e-mail (and any attachments) may contain PRIVILEGED OR CONFIDENTIAL information and is intended only for the use of the specific individual(s) to whom it is addressed. It may contain information that is privileged and confidential under state and federal law. This information may be used or disclosed only in accordance with law, and you may be subject to penalties under law for improper use or further disclosure of the information in this e-mail and its attachments. If you have received this e-mail in error, please immediately notify the person named above by reply e-mail, and then delete the original e-mail. Thank you.

substanc3-dev commented 10 years ago

Ok so use nosql?

haneefmubarak commented 10 years ago

BLAKE2 libs are available fairly universally, so this shouldn't be all that much of an issue. I can implement this pretty quickly.

Jano, we shouldn't settle on anything particular since it's so early in the project. Yes, I received your email on the keys, thx for that.

On Wednesday, March 12, 2014, Jano Varga notifications@github.com<javascript:_e(%7B%7D,'cvml','notifications@github.com');> wrote:

But the password will need to be hashed also on client.

BTW: @haneefmubarak https://github.com/haneefmubarak Did you see the servers i sent you by email?

Reply to this email directly or view it on GitHubhttps://github.com/haneefmubarak/Aixen/issues/3#issuecomment-37445913 .

NOTICE: This e-mail (and any attachments) may contain PRIVILEGED OR CONFIDENTIAL information and is intended only for the use of the specific individual(s) to whom it is addressed. It may contain information that is privileged and confidential under state and federal law. This information may be used or disclosed only in accordance with law, and you may be subject to penalties under law for improper use or further disclosure of the information in this e-mail and its attachments. If you have received this e-mail in error, please immediately notify the person named above by reply e-mail, and then delete the original e-mail. Thank you.

substanc3-dev commented 10 years ago

ok

substanc3-dev commented 10 years ago

So just to tell everyone, We have one master server: bigger in EU and two slaves in US and ASIA And xepeleato's server.

haneefmubarak commented 10 years ago

You require multiple masters, actually, two minimum, recommended three.

I think that any VPS or other cloud hosted servers ought to be set as masters (since the masters run in peer to peer mode), slaves should be home servers and the like, I will connect a home server myself as a slave once we have enough basic code for the entire framework. After that, I encourage others to connect their home servers as slaves too.

Perhaps writing is not the best way of explaining this, I'll draw up a picture when I get home today.

On Wednesday, March 12, 2014, Jano Varga notifications@github.com wrote:

So just to tell everyone, We have one master server: bigger in EU and two slaves in US and ASIA And xepeleato's server.

Reply to this email directly or view it on GitHubhttps://github.com/haneefmubarak/Aixen/issues/3#issuecomment-37446991 .

NOTICE: This e-mail (and any attachments) may contain PRIVILEGED OR CONFIDENTIAL information and is intended only for the use of the specific individual(s) to whom it is addressed. It may contain information that is privileged and confidential under state and federal law. This information may be used or disclosed only in accordance with law, and you may be subject to penalties under law for improper use or further disclosure of the information in this e-mail and its attachments. If you have received this e-mail in error, please immediately notify the person named above by reply e-mail, and then delete the original e-mail. Thank you.

substanc3-dev commented 10 years ago

I also created a picture but about counting to which server connect and loadbalancing. I dont have home server nor public ip...

XePeleato commented 10 years ago

@haneefmubarak It seems that MongoDB works well with C

perezpaya commented 10 years ago

What about achivements? You should take in to account the achivements of the player to calculate the G points.

Imho MongoDB is not a choice for production, it makes so weird things, high memory usage usually. I prefer using cassandra or mariadb/posgress. Also we should take into account Redis if we want fast response times and to cache some data about sessions, users, queues, distributed events for all machines (pub/sub).

albertofem commented 10 years ago

Hi, new here. Project looks interesting. One thing I noticed after looking at the database schema, is that there is no salt field! By all means we should hash passwords using salting, as hashing alone is proved to be insecure. Also I noticed that you are using the MyISAM engine, which does not support foreign keys and contraints. Nevertheless, maybe we should discuss about schemas on theory for now without bothering about choosing a backend. At the end, once you have a solid database design, choosing it is a minor issue.

perezpaya commented 10 years ago

Lol, I didn't had time to read code, but seriusly we are not using Salts? That will be a big failure of security if someone steals data from databases, he could use rainbow tables and get lots of passwords.

XePeleato commented 10 years ago

@alexperezpaya The achievements are stored into the profile.

EDIT: The Gamescore shouldn't be on the server, sorry.

perezpaya commented 10 years ago

@XePeleato Do you know that there are some limitations at NoSQL databases e.g MongoDB where there are some limitations for each documents, each document can storage up to 16mb and that means that if a user has lots of data the document will be full and could start throwing exceptions. I had bad experiences with documents that exceed the limit and broked all the db, a friend debugging one app managed to write into a document the full Don Quixote and corrupted all the database.

haneefmubarak commented 10 years ago

KV stores might be a possibility too. As for the passwords thing, obviously we would use a salted hash. Worst case scenario, you could simply use the persons last time of login combined with their user as a salt, and recalculate the stored hash upon every login.

On Wednesday, March 12, 2014, Alejandro Perezpayá notifications@github.com wrote:

@XePeleato https://github.com/XePeleato Do you know that there are some limitations at NoSQL databases e.g MongoDB where there are some limitations for each documents, each document can storage up to 16mb and that means that if a user has lots of data the document will be full and could start throwing exceptions. I had bad experiences with documents that exceed the limit and broked all the db, a friend debugging one app managed to write into a document the full Don Quixote and corrupted all the database.

Reply to this email directly or view it on GitHubhttps://github.com/haneefmubarak/Aixen/issues/3#issuecomment-37472921 .

NOTICE: This e-mail (and any attachments) may contain PRIVILEGED OR CONFIDENTIAL information and is intended only for the use of the specific individual(s) to whom it is addressed. It may contain information that is privileged and confidential under state and federal law. This information may be used or disclosed only in accordance with law, and you may be subject to penalties under law for improper use or further disclosure of the information in this e-mail and its attachments. If you have received this e-mail in error, please immediately notify the person named above by reply e-mail, and then delete the original e-mail. Thank you.

XePeleato commented 10 years ago

@alexperezpaya Ok, but we need to get the DB working with C Code, so a NoSQL DB seems to be the best option. ¿Any idea?

perezpaya commented 10 years ago

@haneefmubarak that seems weird but so secure :)

haneefmubarak commented 10 years ago

I specialize in WTF solutions :)

On Wednesday, March 12, 2014, Alejandro Perezpayá notifications@github.com wrote:

@haneefmubarak https://github.com/haneefmubarak that seems weird but so secure :)

Reply to this email directly or view it on GitHubhttps://github.com/haneefmubarak/Aixen/issues/3#issuecomment-37473408 .

NOTICE: This e-mail (and any attachments) may contain PRIVILEGED OR CONFIDENTIAL information and is intended only for the use of the specific individual(s) to whom it is addressed. It may contain information that is privileged and confidential under state and federal law. This information may be used or disclosed only in accordance with law, and you may be subject to penalties under law for improper use or further disclosure of the information in this e-mail and its attachments. If you have received this e-mail in error, please immediately notify the person named above by reply e-mail, and then delete the original e-mail. Thank you.

haneefmubarak commented 10 years ago

we're starting to clog up the issue - so for aixen architectural concerns see #5

haneefmubarak commented 10 years ago

@XePeleato how's progress coming along?

XePeleato commented 10 years ago

I am testing some NoSQL DB's , for Key-Value DB's i have tried Cassandra, MariaDB and Bigtable. Cassandra: seems to be a good option but i couldn't find any good C Driver. MariaDB: The structure is similar to MySQL (same founder), it has a C Driver, it seems that is a very good option but i don't know if is a trouble the similarities with MySQL. Bigtable: Cassandra and MariaDB seem to be better than this.

substanc3-dev commented 10 years ago

I can work with MySQL/MariaDB so for me no problem.

substanc3-dev commented 10 years ago

@haneefmubarak @XePeleato BTW why dont you want to use MySQL/MariaDB

haneefmubarak commented 10 years ago

@XePeleato You know, KV DBs was just a suggestion - it's up to you, and as long as the DB is NoSQL and has a good C driver, we can use it:

Perhaps you should also look into the following:

rethinkDB
mongoDB
GT.M
FoundationDB
Couchbase
Aerospike 2
Aerospike 3
Riak
OrientDB
substanc3-dev commented 10 years ago

Why NoSQL? It is no problem to write a SELECT or INSERT :D

haneefmubarak commented 10 years ago

@Jan4V SQL presents tons of issues, just to give you a mild taster:

xkcd

More importantly, sending strings here and there will cause large code dirt issues in the long run; it is better to call functions that do specific and unique tasks, this way, you can guarantee that no text input can alter your functionality, increase performance drastically, and most importantly, you can have clear and concise code that is extremely readable.

substanc3-dev commented 10 years ago

Ok, but i dont know what is unreadable in "SELECT row_name FROM table_name WHERE some_row=something" ...

haneefmubarak commented 10 years ago

It would end up being a string wrapped inside a function... And when you have many of those strings, it can become a problem.

MayeulC commented 10 years ago

The main problem problem with SQL, in my opinion, is that it isn't designed for distributed databases. There's some projects for a distributed SQL database, such as facebook/presto, more info here, but this one is using java.

haneefmubarak commented 10 years ago

That too. That paper by FB was nice, but unlike them, we don't have the luxury of loads of servers nor do we have the necessity to use SQL. The issue with something like facebook/presto is that one bad query can screw up the entire system, and that makes it even more devastating. On the other hand, most NoSQL databases don't really support massive singular queries anyways, so that risk is lessened greatly.

MayeulC commented 10 years ago

We could also come up with our own solution : I know it sounds like reinventing the wheel, but on the other side, we could end up with a solution more taylored with our needs.

Just some thoughts on this : We need a local representation of the data, I think we can afford (at least as a first approach) to store a complete copy of the data on each master server. Then, we need a propagation mechanism, as well as a sync one, in case of failed propagation (as a first approach, we could re-download the entire database, but we can later implement a snapshot system, or incremental copy as git does). Once this is setup, the rest is quite trivial (answering client requests, etc...). The downside of this custom approach is that it would require some downtime for each upgrade to the system. We need to implement at least a sceduled maintenance protocol, to stop listening from clients, synchonize a last time, and then quit.

haneefmubarak commented 10 years ago

I've been down that road before, and you will end up being seriously screwed, losing scalability, running into consistency issues, and so on. We really just need a decent DB with a decent C interface and scalability and consistency. At the moment, I'm familiar with FoundationDB, and I'm leaning towards them because they have some neat stuff while still being absurdly flexible.

You get up to six total threads of fdb for free, after that you have to get a license. Their pricing model is cool here though, because they offer pricing that is scaled closer to whether you actually make money or not. In the long run, Aixen will probably end up being ad-supported (once there are quite a few games and many players), so that is a possibility.

Either way, I have asked @XePeleato to look through the list of DBs that I posted above, and I'd like to know how he feels about each of the DBs before making a final call.

XePeleato commented 10 years ago

Ok, i have tried all the DBs and i am going to make a review of all of them:

RethinkDB:

MongoDB: We already discarded this, thanks, @alexperezpaya. *Valid?: NO

GT.M: When i was looking for info, the only info that google shows is some cars and weird Samsung stuff, doesn't seem to be a good option.

FoundationDB:

Couchbase:

Aerospike3:

Riak:

OrientDB:

3/8 Valid Ok this is the end of my review, if i need to choose one i would choose Riak, Foundation DB or AeroSpike. Any oppinion?

haneefmubarak commented 10 years ago

Alright, so at the end, from your research, we end up with these:

Couchbase
Aerospike3
Riak

For the FoundationDB, the graphs on their site seem to show pretty good performance, and ACID functionality too (which will make our work much, much, easier). So I'm curious as to which limitations you found disturbing and what makes you believe that FoundationDB would have low performance.

XePeleato commented 10 years ago

https://foundationdb.com/documentation/beta1/known-limitations.html

haneefmubarak commented 10 years ago

@XePeleato So I'd like you to do further research into the following:

Aerospike3
    What licensing & pricing options do they offer (beyond their free edition)?
    What are the consistency properties of the DB?
    Are queries blocking or non-blocking?

Couchbase
    What are the performance characteristics of the DB?
    What language is the DB itself written in?
    Is the DB multithreaded?
    Are queries blocking or non-blocking?

Riak
    How is the consistency of the DB?
    What language is the DB written in?
    Are queries blocking or non-blocking?

FoundationDB
    Just write up, point by point, what you dislike here.
XePeleato commented 10 years ago

Okay, as soon as i can i will do it

haneefmubarak commented 10 years ago

I'm familiar with the known limitations (latest: https://foundationdb.com/documentation/known-limitations.html), but what specifically do you object to? Most of the limitations are common to most DBs (others just won't tell you), and the remaining ones seem rather reasonable.

So just list off what you dislike with the research from the rest of the above.

But before you do that, please read the guide on markdown @ https://github.com/adam-p/markdown-here/wiki/Markdown-Cheatsheet (you can use markdown when writing in issues)

haneefmubarak commented 10 years ago

@XePeleato how's progress on the research coming along?

XePeleato commented 10 years ago

Sorry @haneefmubarak and all the Team I was very busy. I am currently researching about this and i wil edit the comment with the info

Aerospike 3

CouchBase

Riak

FoundationDB For me, FoundationDB is fine, now i have researched more data and in my oppinion it is valid.

If you or any member of the team have any Question please ask. Have a nice day.

haneefmubarak commented 10 years ago

@XePeleato?

XePeleato commented 10 years ago

@haneefmubarak Updated.

haneefmubarak commented 10 years ago

Excellent work, @XePeleato.

When you have time, could you please read the guide on markdown @ https://github.com/adam-p/markdown-here/wiki/Markdown-Cheatsheet (you can use markdown when writing in issues) - it'll help a lot in improving clarity if you used formatting (for example, in the above, bullet points would have been nice :smiley_cat:)? Thanks!

XePeleato commented 10 years ago

Ok, thanks @haneefmubarak i will take a look.

EDIT: Edited above

haneefmubarak commented 10 years ago

@XePeleato - if it's not too much trouble, could you also edit your earlier posts in this thread - whenever you get the time of course?

Thanks!

XePeleato commented 10 years ago

Edited. @haneefmubarak it's big but clear, in my oppinion

haneefmubarak commented 10 years ago

Cool @XePeleato, thanks again. I'd like your opinion here: if it was entirely up to you, which DB would _you_ pick out of the four we have narrowed it down to and why would you pick that particular DB?

EDIT: added to question