General > Developers Corner

UCL to SQL Discussion

(1/4) > >>

roastchicken:
General Description

A frequent problem people come to these forums to ask for help with is malfunctioning/laggy UCL (users and groups). For those not aware, users and groups are stored in text files (garrysmod/data/ulib/ users.txt and groups.txt). This works just fine for most servers. However, as the amount of users (or groups for that matter, but usually users are the ones that get out of control) increases problems start to arise. There doesn't seem to be a distinct cut off, but after a certain point the server starts to lag when trying to change permissions or change a user's group. Sometimes there are even more serious problems like users being randomly dropped from groups.

It's been mentioned a few times that switching to SQL (SQLite specifically, the only SQL solution supported natively by Garry's Mod) would likely mitigate many, if not all, of these problems. In addition to playing nicely with large amounts of users, switching to SQL will (I'm pretty sure) speed up reading/writing users and groups in addition to making them easier to work with (no more json with no particular order! yay!).

Moving UCL to SQLite hasn't been a priority for the Ulysses developers for a few reasons (according to Megiddo):


--- Quote from: Megiddo on January 23, 2016, 01:22:43 PM ---1. When we designed UCL, we never intended people to add every single user to join. I still don't see why people are doing this, though we definitely empathize that if you want to do so, ULib is not your friend. However, since a minority of users are doing this, it is not a priority change for us.
2. Time, though secondary to #1.
3. We are trying to shift efforts to ULX4. Any time spent doing this is time not spent working on ULX4 -- and because of #1, ULX4 is more important to us.

--- End quote ---

I recently (a little over two weeks ago ;D) offered to make the switch from flatfile (text files) to SQL. I decided it might be useful to make this topic to discuss the change, since it is pretty major and I don't want to make any glaring errors. Feel free to ask questions, express concerns, share ideas, or just discuss the idea in general.

Side note: don't get your hopes up if you have a ton of users or just want UCL to switch to SQL. I'm a chronic procrastinator and it is very likely quite possible that I will never finish this project. Fingers crossed.

roastchicken:
Technical Stuff

Database Schema

ulib_users:
Storage ClassINTEGERINTEGERTEXTTEXTColumnsteamid64uniqueidnamegroupExample Value765611980415442891164255280roastchickenoperator
ulib_groups:
Storage ClassTEXTTEXTTEXTColumnnameinherit_fromcan_targetExample Valuevipuser!%operator
ulib_user_allowed_access:
Storage ClassINTEGERTEXTTEXTColumnuser_steamid64access_stringaccess_tagExample Value76561198041544289ulx ban
ulib_user_denied_access:
Storage ClassINTEGERTEXTTEXTColumnuser_steamid64access_stringaccess_tagExample Value76561198041544289ulx banid
ulib_group_allowed_access:
Storage ClassTEXTTEXTTEXTColumngroup_nameaccess_stringaccess_tagExample Valuevipulx votekick
PS: The reason there aren't any access tags is because I forgot how they work :P
Feel free to post/PM me some examples so I can add them.

Off-Topic: Is there any way to have a table with lines around the cells? I find it a bit difficult to read them when they're all cluttered like this, though I might be the only one. Oh well.

roastchicken:
Progress


* 01/23/2016 - Brainstorm Database Schema
* 02/10/2016 - Normalize Database Schema
I'll get it finished one day, I promise!

feldma:
You know what be awesome? Having is set up with MySQL! I should totally do that!
Except I don't have a clue how to xD.

This looks like a pretty neat idea. If you wanted help (not that my coding is good whatsoever), I'd be glad to help out, somehow.

Regarding the schema, in theory, even if it were to 'bog it down', wouldn't using a SQLite reduce / eliminate the chance of users randomly dropping out of groups? I've once had it that I created a rank and a whole bunch of people were randomly put into it. If SQLite stopped that from happening, that would be a significant feature that I would be more then willing to help create / test.

roastchicken:

--- Quote from: feldma on February 08, 2016, 09:20:08 PM ---Regarding the schema, in theory, even if it were to 'bog it down', wouldn't using a SQLite reduce / eliminate the chance of users randomly dropping out of groups? I've once had it that I created a rank and a whole bunch of people were randomly put into it. If SQLite stopped that from happening, that would be a significant feature that I would be more then willing to help create / test.
--- End quote ---

I'm no expert, but I'm pretty sure moving to SQL will only fix the group-dropping bug if it is caused by a large users.txt/groups.txt. I don't fully know why the bug occurs, my guess is that Garry's Mod's file writing breaks down after a certain threshold and just starts dropping off data.

Navigation

[0] Message Index

[#] Next page