Ulysses
General => Developers Corner => Topic started by: roastchicken on February 08, 2016, 09:02:38 PM
-
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):
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.
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.
-
Technical Stuff
Database Schema
ulib_users:
Storage Class | INTEGER | INTEGER | TEXT | TEXT |
Column | steamid64 | uniqueid | name | group |
Example Value | 76561198041544289 | 1164255280 | roastchicken | operator |
ulib_groups:
Storage Class | TEXT | TEXT | TEXT |
Column | name | inherit_from | can_target |
Example Value | vip | user | !%operator |
ulib_user_allowed_access:
Storage Class | INTEGER | TEXT | TEXT |
Column | user_steamid64 | access_string | access_tag |
Example Value | 76561198041544289 | ulx ban | |
ulib_user_denied_access:
Storage Class | INTEGER | TEXT | TEXT |
Column | user_steamid64 | access_string | access_tag |
Example Value | 76561198041544289 | ulx banid | |
ulib_group_allowed_access:
Storage Class | TEXT | TEXT | TEXT |
Column | group_name | access_string | access_tag |
Example Value | vip | ulx 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.
-
Progress
- 01/23/2016 - Brainstorm Database Schema
- 02/10/2016 - Normalize Database Schema (https://forums.ulyssesmod.net/index.php/topic,9309.msg47615.html#msg47615)
I'll get it finished one day, I promise!
-
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.
-
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.
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.
-
Roastchicken, look into BCNF -- it will help you come up with a workable schema.
-
Roastchicken, look into BCNF -- it will help you come up with a workable schema.
I think I looked into something like this previously, where you would have (for example) one table for users and then a separate table for users' allowed commands. I'm not sure why I didn't think of this.
Another thing I'm thinking about in terms of the database schema is removing the steamid column. Originally I had it there so people could add someone by SteamID without knowing their SteamID64, but I'm not sure if it's a good idea to have that redundant column just so that people can manually edit the database file to add players. If they really wanted to they could just use an online SteamID finder and get their SteamID64 that way. Thoughts?
-
You can convert steamid formats in Lua. It's a somewhat arbitrary decision. No point in duplicating data.
-
You can convert steamid formats in Lua. It's a somewhat arbitrary decision. No point in duplicating data.
Yeah, I know of the functions to convert to/from the two SteamID formats. I was just thinking of the edge case where someone wants to manually edit their sv.db file and add a user by SteamID, but that's really specific and they can just convert it to SteamID64 if they absolutely must add the user by editing the database (not a good idea, if anyone is wondering).
I'm currently in the process of normalizing the schema, but I've come a cross a bit of a snag. The only way I can think of storing access tags is using NULL values, which technically goes against normalization. If anyone has any ideas on how to store access tags without the use of NULL values, please let me know. Then again this is a really nitpicky side of database normalization and as far as I can tell there isn't much reason to follow it.
-
As with all "best software/database practices", you have to draw the line somewhere. Sometimes utility trumps sexy.
I personally have no problem with appropriate use of nulls. I believe this is an appropriate use.
-
As with all "best software/database practices", you have to draw the line somewhere. Sometimes utility trumps sexy.
I personally have no problem with appropriate use of nulls. I believe this is an appropriate use.
One possible solution that I just thought of is to have two tables, one for access strings and one for access tags, but this is pretty clumsy and it seems like a lot of work just to follow some normalization rules. Plus the names of the tables get pretty out of hand. "ulib_user_allowed_access_strings" and "ulib_user_allowed_access_tags" :o
edit:
I've been working more on normalizing the database schema, but I think I'm going to stick with this schema that is normal form one (lowest level of normalization):
ulib_users:
Storage Class | INTEGER | INTEGER | TEXT | TEXT |
Column | steamid64 | uniqueid | name | group |
Example Value | 76561198041544289 | 1164255280 | roastchicken | operator |
ulib_groups:
Storage Class | TEXT | TEXT | TEXT |
Column | name | inherit_from | can_target |
Example Value | vip | user | !%operator |
ulib_user_allowed_access:
Storage Class | INTEGER | TEXT | TEXT |
Column | user_steamid64 | access_string | access_tag |
Example Value | 76561198041544289 | ulx ban | |
ulib_user_denied_access:
Storage Class | INTEGER | TEXT | TEXT |
Column | user_steamid64 | access_string | access_tag |
Example Value | 76561198041544289 | ulx banid | |
ulib_group_allowed_access:
Storage Class | TEXT | TEXT | TEXT |
Column | group_name | access_string | access_tag |
Example Value | vip | ulx votekick | |
The table names are a bit clunky but I think it's best that they are this clunky so they stay descriptive.
In order to normalize it to any higher forms (such as BCNF that Megiddo suggested), all the access strings and tags would need to go in their own tables that only contained access strings/tags. That seems extremely wasteful to me, and I don't see any possible benefits either.
Oh, and the reason there aren't any access tags is because I forgot how they work :P
-
(this post is just for reference, in case we want to revert to a previous database schema)
New Schema:
ulib_users:
Storage Class | INTEGER | INTEGER | TEXT | TEXT |
Column | steamid64 | uniqueid | name | group |
Example Value | 76561198041544289 | 1164255280 | roastchicken | operator |
ulib_groups:
Storage Class | TEXT | TEXT | TEXT |
Column | name | inherit_from | can_target |
Example Value | vip | user | !%operator |
ulib_user_allowed_access:
Storage Class | INTEGER | TEXT | TEXT |
Column | user_steamid64 | access_string | access_tag |
Example Value | 76561198041544289 | ulx ban | |
ulib_user_denied_access:
Storage Class | INTEGER | TEXT | TEXT |
Column | user_steamid64 | access_string | access_tag |
Example Value | 76561198041544289 | ulx banid | |
ulib_group_allowed_access:
Storage Class | TEXT | TEXT | TEXT |
Column | group_name | access_string | access_tag |
Example Value | vip | ulx votekick | |
Old Schema:
ulib_users:
Storage Class | INTEGER | TEXT | INTEGER | TEXT | TEXT | TEXT | TEXT |
Column | steamid64 | steamid | uniqueid | name | group | allow | deny |
Example Value | 76561198041544289 | STEAM_0:1:40639280 | 1164255280 | roastchicken | operator | "ulx ban" "ulx kick" | "ulx maul" "ulx banid" |
ulib_groups:
Storage Class | TEXT | TEXT | TEXT | TEXT |
Column | name | allow | inherit_from | can_target |
Example Value | vip | "ulx blind" "ulx cloak" | user | !%operator |
-
Access tags are for specific permissions on a per-argument basis (who you can target, what you can specify, etc). As such, there's no such thing as an access tag for denied accesses.
Thinking about it, since access tags should be relatively rare, it may be worth splitting it into it's own table, but it's six of one, half a dozen of the other to me. It would make a much larger difference with MySQL instead of SQLite.
-
Access tags are for specific permissions on a per-argument basis (who you can target, what you can specify, etc). As such, there's no such thing as an access tag for denied accesses.
Thinking about it, since access tags should be relatively rare, it may be worth splitting it into it's own table, but it's six of one, half a dozen of the other to me. It would make a much larger difference with MySQL instead of SQLite.
Yeah, I wasn't quite sure if access tags would exist for denied accesses so I just threw it in there just in case. I'm not sure if I'll split the access tags off to a separate table because I don't really see any benefit doing so. However you said it would make a larger difference with MySQL... What exactly would be the advantage of putting access tags into their own table?
-
With MySQL you can use fixed-width rows to improve lookup performance, but SQLite does not support that: http://www.sqlite.org/different.html
With fixed-width, you'd be wasting space with the mostly unused access tag column. The point is moot with SQLite.
-
It's been a while...
I was bored today and I wanted to work on a Garry's Mod project involving SQL, so I came back to this topic to look at how I structured the tables. I was inspired to take up this project again, which brings me to the purpose of this post.
I've been digging around in ucl.lua and noticed that ULib allows for users to be added by IP or UniqueID, and that it also uses UniqueID internally in at least one place to distinguish between users. Is this something that should be preserved by the SQL storage system? I'm not sure of the usefulness of storing either, apart from making the transition from flatfile to SQLite cleaner. I could just add columns for IP and UniqueID and populate them when there's no other way to identify a user, and then swap them out when the player comes online. However that would mean an extra column that is going to be very empty for most servers.
Alternatively I could drop these two records and just leave any users that are specified by these in users.txt and swap them over to SQLite when they come online. But that has the disadvantage of mixing flatfile and database for user storage, which I don't think is a very appealing idea. I guess a third option would be to just notify the server operator during the transition that they have users specified by these 'outdated' identifiers and give them the option to manually convert them if they remember who it belongs to, and after they confirm to just drop any records that don't specify a SteamID.
Does anyone actually add users by IP address? I'm definitely for removing UniqueID because it can be completely replaced by SteamID64, but IP addresses could be useful for some people I guess. I'm not sure I should remove that functionality if it is relied on by some server operators.
-
The other IDs exist for historical reasons. ULX4 will be dropping support for those. From what I've seen, no one used anything other than SteamIDs. Fun fact, we used to support adding users by name and "clan tags" with an authentication password.