UCL to SQL Discussion

[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):

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 ) 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 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
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]

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.

[Megiddo]

Roastchicken, look into BCNF -- it will help you come up with a workable schema.

[roastchicken]

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?

[Megiddo]

You can convert steamid formats in Lua. It's a somewhat arbitrary decision. No point in duplicating data.

[roastchicken]

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.

[Megiddo]

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.

[roastchicken]

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" 

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

[roastchicken]

(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

[Megiddo]

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.

[roastchicken]

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?

[Megiddo]

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.