How To Query A Single Setting In the Various Options / Permissions Fields

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Jake Bunce
    Senior Member
    • Dec 2000
    • 46598
    • 3.6.x

    How To Query A Single Setting In the Various Options / Permissions Fields

    In vB2, the various "on / off" settings were stored in their own fields in the database, where "1" was On and "0" was Off. For example, look at how the vB2 usergroup settings were stored:



    Under the old system you could use these queries to toggle a setting:

    Turn ON a setting:
    UPDATE tablename SET fieldname = 1

    Turn OFF a setting:
    UPDATE tablename SET fieldname = 0

    vB3 stores these settings differently. Instead of an individual field for each setting, there is a single field that contains all settings:



    These permissions are stored using a binary scheme. Each permission is given a number value, like this:

    permission 1 = 2^0 = 1
    permission 2 = 2^1 = 2
    permission 3 = 2^2 = 4
    permission 4 = 2^3 = 8
    permission 5 = 2^4 = 16
    permission 6 = 2^5 = 32
    permission 7 = 2^6 = 64
    permission 8 = 2^7 = 128
    etc...

    Notice how each number is an integer power of 2. If you were to look at the stored value in natural binary format, then each bit would represent one of the permissions. For examlpe, if permissions 1 and 4 are turned on and the rest are turned off, then the stored value would look like this:

    Code:
    		2^7	2^6	2^5	2^4	2^3	2^2	2^1	2^0
    binary #	0	0	0	0	1	0	0	1
    In this case, the stored value is 9 (the sum of the "on" bits).

    To change a single setting (a single bit) in the stored value, you can use these queries:

    Turn ON a bit:
    UPDATE tablename SET fieldname = fieldname + # WHERE NOT(fieldname & #)

    Turn OFF a bit:
    UPDATE tablename SET fieldname = fieldname - # WHERE fieldname & #

    tablename is the name of the table being updated. In the previous example about group permissions, the permissions are stored in the usergroup table. fieldname is the name of the field which is genericpermissions in the previous example.

    # is the decimal number that represents the permission, like the numbers above (1, 2, 4, 8, 16, 32, 64, 128). You can find out which number corresponds to which permission by referring to the includes/init.php file. In this file you will find several sections of code like this:

    Code:
    // field names for general permissions
    $_BITFIELD['usergroup']['genericpermissions'] = array(
    	'canviewmembers'           => 1,
    	'canmodifyprofile'         => 2,
    	'caninvisible'             => 4,
    	'canviewothersusernotes'   => 8,
    	'canmanageownusernotes'    => 16,
    	'canseehidden'             => 32,
    	'canbeusernoted'           => 64,
    	'canprofilepic'            => 128,
    	'canuseavatar'             => 512,
    	'canusesignature'          => 1024,
    	'canusecustomtitle'        => 2048,
    	'canseeprofilepic'         => 4096,
    	'canviewownusernotes'	   => 8192,
    	'canmanageothersusernotes' => 16384,
    	'canpostownusernotes'      => 32768,
    	'canpostothersusernotes'   => 65536,
    	'caneditownusernotes'      => 131072,
    	'canseehiddencustomfields' => 262144,
    
    	// Reputation
    
    	'canseeownrep'             => 256,
    	'canuserep'                => 524288,
    	'canhiderep'               => 1048576,
    	'cannegativerep'           => 2097152,
    	'canseeothersrep'          => 4194304,
    	'canhaverepleft'           => 8388608,
    );
    Here you can clearly see which number corresponds to which permission, and so you can construct the complete query. Let's say we want all groups to be able to use avatars. The number that corresponds to the canuseavatar permission is 512, so this would be the query:

    UPDATE usergroup SET genericpermissions = genericpermissions + 512 WHERE NOT(genericpermissions & 512)

    You can see which other tables / fields use this binary scheme by looking in that same file, includes/init.php. Take the previous code for example, you can see the table and field names in green and blue:

    Code:
    // field names for general permissions
    $_BITFIELD['[color=green]usergroup[/color]']['[color=blue]genericpermissions[/color]'] = array(
    	'canviewmembers'           => 1,
    	'canmodifyprofile'         => 2,
    	'caninvisible'             => 4,
    	'canviewothersusernotes'   => 8,
    	'canmanageownusernotes'    => 16,
    	'canseehidden'             => 32,
    	'canbeusernoted'           => 64,
    	'canprofilepic'            => 128,
    	'canuseavatar'             => 512,
    	'canusesignature'          => 1024,
    	'canusecustomtitle'        => 2048,
    	'canseeprofilepic'         => 4096,
    	'canviewownusernotes'	   => 8192,
    	'canmanageothersusernotes' => 16384,
    	'canpostownusernotes'      => 32768,
    	'canpostothersusernotes'   => 65536,
    	'caneditownusernotes'      => 131072,
    	'canseehiddencustomfields' => 262144,
    
    	// Reputation
    
    	'canseeownrep'             => 256,
    	'canuserep'                => 524288,
    	'canhiderep'               => 1048576,
    	'cannegativerep'           => 2097152,
    	'canseeothersrep'          => 4194304,
    	'canhaverepleft'           => 8388608,
    );
    Note:
    After you change a setting using this method, you may need to update this counter for the changes to take effect (group permissions and forum settings in particular):

    Admin CP -> Import & Maintenance -> Update Counters -> Rebuild Forum Information
    Attached Files
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...