Author Topic: UCL to SQL Discussion  (Read 5487 times)

0 Members and 1 Guest are viewing this topic.

Offline roastchicken

  • Respected Community Member
  • Sr. Member
  • *****
  • Posts: 476
  • Karma: 84
  • I write code
UCL to SQL Discussion
« 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.
Give a man some code and you help him for a day; teach a man to code and you help him for a lifetime.

Offline roastchicken

  • Respected Community Member
  • Sr. Member
  • *****
  • Posts: 476
  • Karma: 84
  • I write code
Technical Stuff
« Reply #1 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.
« Last Edit: February 10, 2016, 12:45:46 PM by roastchicken »
Give a man some code and you help him for a day; teach a man to code and you help him for a lifetime.

Offline roastchicken

  • Respected Community Member
  • Sr. Member
  • *****
  • Posts: 476
  • Karma: 84
  • I write code
Progress
« Reply #2 on: February 08, 2016, 09:07:02 PM »
Progress


I'll get it finished one day, I promise!
« Last Edit: February 10, 2016, 12:47:30 PM by roastchicken »
Give a man some code and you help him for a day; teach a man to code and you help him for a lifetime.

Offline feldma

  • Newbie
  • *
  • Posts: 47
  • Karma: 5
  • 5696 hours in Gmod and counting!
    • Mega-Strike Network
Re: UCL to SQL Discussion
« Reply #3 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.

« Last Edit: February 08, 2016, 09:22:45 PM by feldma »
Solving 50% of people's questions by searching it up on google.

Trying to think of a cool idea to code, so I can inspire myself to code. If you want something done, please message me!

Offline roastchicken

  • Respected Community Member
  • Sr. Member
  • *****
  • Posts: 476
  • Karma: 84
  • I write code
Re: UCL to SQL Discussion
« Reply #4 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.
Give a man some code and you help him for a day; teach a man to code and you help him for a lifetime.

Offline Megiddo

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 6213
  • Karma: 394
  • Project Lead
Re: UCL to SQL Discussion
« Reply #5 on: February 09, 2016, 04:26:20 AM »
Roastchicken, look into BCNF -- it will help you come up with a workable schema.
Experiencing God's grace one day at a time.

Offline roastchicken

  • Respected Community Member
  • Sr. Member
  • *****
  • Posts: 476
  • Karma: 84
  • I write code
Re: UCL to SQL Discussion
« Reply #6 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?
Give a man some code and you help him for a day; teach a man to code and you help him for a lifetime.

Offline Megiddo

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 6213
  • Karma: 394
  • Project Lead
Re: UCL to SQL Discussion
« Reply #7 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.

Experiencing God's grace one day at a time.

Offline roastchicken

  • Respected Community Member
  • Sr. Member
  • *****
  • Posts: 476
  • Karma: 84
  • I write code
Re: UCL to SQL Discussion
« Reply #8 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.
Give a man some code and you help him for a day; teach a man to code and you help him for a lifetime.

Offline Megiddo

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 6213
  • Karma: 394
  • Project Lead
Re: UCL to SQL Discussion
« Reply #9 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.
Experiencing God's grace one day at a time.

Offline roastchicken

  • Respected Community Member
  • Sr. Member
  • *****
  • Posts: 476
  • Karma: 84
  • I write code
Re: UCL to SQL Discussion
« Reply #10 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
« Last Edit: February 09, 2016, 04:31:08 PM by roastchicken »
Give a man some code and you help him for a day; teach a man to code and you help him for a lifetime.

Offline roastchicken

  • Respected Community Member
  • Sr. Member
  • *****
  • Posts: 476
  • Karma: 84
  • I write code
Re: UCL to SQL Discussion
« Reply #11 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
Give a man some code and you help him for a day; teach a man to code and you help him for a lifetime.

Offline Megiddo

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 6213
  • Karma: 394
  • Project Lead
Re: UCL to SQL Discussion
« Reply #12 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.
Experiencing God's grace one day at a time.

Offline roastchicken

  • Respected Community Member
  • Sr. Member
  • *****
  • Posts: 476
  • Karma: 84
  • I write code
Re: UCL to SQL Discussion
« Reply #13 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?
Give a man some code and you help him for a day; teach a man to code and you help him for a lifetime.

Offline Megiddo

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 6213
  • Karma: 394
  • Project Lead
Re: UCL to SQL Discussion
« Reply #14 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.
Experiencing God's grace one day at a time.