Ulysses

General => Developers Corner => Topic started by: roastchicken on February 08, 2016, 09:02:38 PM

Title: UCL to SQL Discussion
Post 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.
Title: Technical Stuff
Post by: roastchicken on February 08, 2016, 09:05:12 PM
Technical Stuff

Database Schema

ulib_users:
Storage ClassINTEGERINTEGERTEXTTEXT
Columnsteamid64uniqueidnamegroup
Example Value765611980415442891164255280roastchickenoperator

ulib_groups:
Storage ClassTEXTTEXTTEXT
Columnnameinherit_fromcan_target
Example Valuevipuser!%operator

ulib_user_allowed_access:
Storage ClassINTEGERTEXTTEXT
Columnuser_steamid64access_stringaccess_tag
Example Value76561198041544289ulx ban

ulib_user_denied_access:
Storage ClassINTEGERTEXTTEXT
Columnuser_steamid64access_stringaccess_tag
Example Value76561198041544289ulx banid

ulib_group_allowed_access:
Storage ClassTEXTTEXTTEXT
Columngroup_nameaccess_stringaccess_tag
Example 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.
Title: Progress
Post by: roastchicken on February 08, 2016, 09:07:02 PM
Progress


I'll get it finished one day, I promise!
Title: Re: UCL to SQL Discussion
Post by: feldma on February 08, 2016, 09:20:08 PM
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.

Title: Re: UCL to SQL Discussion
Post by: roastchicken on February 08, 2016, 09:48:41 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.

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.
Title: Re: UCL to SQL Discussion
Post by: Megiddo on February 09, 2016, 04:26:20 AM
Roastchicken, look into BCNF -- it will help you come up with a workable schema.
Title: Re: UCL to SQL Discussion
Post by: roastchicken on February 09, 2016, 11:45:33 AM
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?
Title: Re: UCL to SQL Discussion
Post by: Megiddo on February 09, 2016, 01:55:01 PM
You can convert steamid formats in Lua. It's a somewhat arbitrary decision. No point in duplicating data.

Title: Re: UCL to SQL Discussion
Post by: roastchicken on February 09, 2016, 02:23:52 PM
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.
Title: Re: UCL to SQL Discussion
Post by: Megiddo on February 09, 2016, 02:40:09 PM
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.
Title: Re: UCL to SQL Discussion
Post by: roastchicken on February 09, 2016, 02:45:38 PM
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 ClassINTEGERINTEGERTEXTTEXT
Columnsteamid64uniqueidnamegroup
Example Value765611980415442891164255280roastchickenoperator

ulib_groups:
Storage ClassTEXTTEXTTEXT
Columnnameinherit_fromcan_target
Example Valuevipuser!%operator

ulib_user_allowed_access:
Storage ClassINTEGERTEXTTEXT
Columnuser_steamid64access_stringaccess_tag
Example Value76561198041544289ulx ban

ulib_user_denied_access:
Storage ClassINTEGERTEXTTEXT
Columnuser_steamid64access_stringaccess_tag
Example Value76561198041544289ulx banid

ulib_group_allowed_access:
Storage ClassTEXTTEXTTEXT
Columngroup_nameaccess_stringaccess_tag
Example Valuevipulx 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
Title: Re: UCL to SQL Discussion
Post by: roastchicken on February 10, 2016, 12:42:48 PM
(this post is just for reference, in case we want to revert to a previous database schema)

New Schema:

ulib_users:
Storage ClassINTEGERINTEGERTEXTTEXT
Columnsteamid64uniqueidnamegroup
Example Value765611980415442891164255280roastchickenoperator

ulib_groups:
Storage ClassTEXTTEXTTEXT
Columnnameinherit_fromcan_target
Example Valuevipuser!%operator

ulib_user_allowed_access:
Storage ClassINTEGERTEXTTEXT
Columnuser_steamid64access_stringaccess_tag
Example Value76561198041544289ulx ban

ulib_user_denied_access:
Storage ClassINTEGERTEXTTEXT
Columnuser_steamid64access_stringaccess_tag
Example Value76561198041544289ulx banid

ulib_group_allowed_access:
Storage ClassTEXTTEXTTEXT
Columngroup_nameaccess_stringaccess_tag
Example Valuevipulx votekick

Old Schema:

ulib_users:
Storage ClassINTEGERTEXTINTEGERTEXTTEXTTEXTTEXT
Columnsteamid64steamiduniqueidnamegroupallowdeny
Example Value76561198041544289STEAM_0:1:406392801164255280roastchickenoperator"ulx ban"
"ulx kick"
"ulx maul"
"ulx banid"

ulib_groups:
Storage ClassTEXTTEXTTEXTTEXT
Columnnameallowinherit_fromcan_target
Example Valuevip"ulx blind"
"ulx cloak"
user!%operator
Title: Re: UCL to SQL Discussion
Post by: Megiddo on February 10, 2016, 04:14:46 PM
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.
Title: Re: UCL to SQL Discussion
Post by: roastchicken on February 10, 2016, 06:40:10 PM
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?
Title: Re: UCL to SQL Discussion
Post by: Megiddo on February 11, 2016, 04:22:51 AM
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.
Title: Re: UCL to SQL Discussion
Post by: roastchicken on August 08, 2017, 02:39:00 PM
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.
Title: Re: UCL to SQL Discussion
Post by: Megiddo on August 11, 2017, 06:23:53 AM
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.