How would I do this?

I am making an admin mod.
I am using SQL to store the players privileges.
There are 50 different privileges.
How should it work?

Option 1

I have a couple column, atrib1, atrib2 …
I will have one row for each group
Each column stores a letter (a-z) to represent a privileges.
So attib1, a would be ban command, atrib2, b would be kick command.
One data entry may look like abcefhjikl for atrib1 and lamskej for atrib2.
I will use string.gfind to see if they have that permission or not.

Option 2

I have 2 columns, group and atrib
I will have multiple rows for each group
Each group will have multiple entries such as (group=admin;atrib=ban)
Each group will need a new entry for each atrib

I hope you see what I mean.
So how should I slice it up; what option shall I use?

Use bitflags?

I don’t know what bitflags is but odds are it dosent use MySql.

Wouldn’t it be better to store the column name as the command, and then when your looking up you column you can use the command as the search. Then for your row you could have to groups in there.

Yea, but it would take up lots of columns. I was trying to compress those by doing one column with letters to represent those other columns. Also at the game start it would be loaded onto a table, so there would be minimal sql transactions.

Speaking of Sql Transactions should I load bans, logs etc onto a sql database then clear the txt file?

If you want to store 50 perks, you get a 50bit bitflag.
An example would be this:

You got one byte (8 bits): 00000000
With that byte, you can store 8 perks: 00000001 <- This would enable perk 1 and leave others disabled
So lets say bit 1 is noclip, bit 2 is slap, 3 is slay, 4 is goto, 5 is bring, 6 is rocket, 7 is kick and 8 is ban.
To give a user all perks it would look like 11111111, but if you just want to give this player noclip, goto and bring it would be 10011000. If you want more perks, you simply add more bits to it! And when you got all your numbers, you just store it as a string on the MySQL database.

[editline]25th January 2011[/editline]

You would just need a column to identify the user and one to store the bitflag string.
If you want i can give you a lua snippet to explain it better.

[editline]25th January 2011[/editline]

And if you want even more compact storage, you convert your bitflag to hex, and convert it back when you want to read/write to it

:what:

If I used bitflags when gathering data from the database would I need to chop it once I got it from lua?

Like if I wanted to see if the person could noclip how would I tell? I do SELECT perks FROm blah would return 11111110111 or whatever.

Because, the string limit is 256 chars, if he has more perks he would have to use hex. (Or bigger)

And zzaacckk, i would say use a table with all the perks in the correct order, then use strfind or w/e to find your perk and use left or right

Or he could store it as binary?
Or does SQL freak out on non ascii or something?
I don’t really know much about it:saddowns:

If he stores the binary as a string, there is a limit of 256. Converting the binary to hex saves space thus allowing more perks without adding columns, or having the problem of reading two columns, myself i find it easier to convert it than to read two columns.

But if he stores it binary 8 bits would translate to 1 character in the string thus allowing for 2048 bits to be stored before exceeding the string limit.
It’s as compact as it gets!

I never knew SQL could store binary.

Me neither, that’s why I was asking.

Hmm so if I had the following…

1101001
Noclip, kick, ban, slay, add admins, remove admins, ignite

If I wanted to see if they could ban I do string.Left(string.Right(binary,3),1)

Because ban is the 3rd value and to isolate that value UPI chop all excess to the right of it then chop off all excess to the left correct?

Yea, altough it would be better to make a function to which you pass a string to, and it just returns the value.

They can, as a Binary Large OBject (BLOB in SQL).

Are you sure? That might work if you converted it to a string of 1 and 0 characters but that would be a bit silly for the bit flag stuff.

If you have your byte of privileges, 01101001, and wanted to see if the second bit from the left was set, you’d use the bitwise operators to do 01101001 & 01000000. That performs a logical AND on each corresponding bit in the two numbers.

01101001
01000000

01000000

Then you just see if the resulting number is non-zero.

Yea but what I have 50 different bit operators it would be easier to isolate it with string.Left and string.Right instead

No it wouldn’t.

[lua]FLAG_KICK = 1
FLAG_BAN = 2
FLAG_SLAY = 4
FLAG_WHATEVER = 8

if pl:GetFlags() & FLAG_BAN == FLAG_BAN then
MsgN(pl:Nick…" can ban.")
end[/lua]