ACL Database Tables

The following tables are those used in or in some way referenced by the current W3C ACL System. The first column specifies the name of a row, the second column specifies the MySQL datatype, and the final column provides a brief description of the row.

The tables directly used by the ACL system are:

Related to these tables are the following auxiliary tables:

Note: Apache auth module get called twice. Query to userDetails to verify username/passwd. Second query to join between idInclusions, ids, uris, and acls.

superGroups

Maps subgroups to supergroups. This table is compiled from hierarchy. It tells which groups are member of other groups. In this table a "sub" is not necessarily directly contained in "super"; rather, the generation number specifies how subgroups the super had to resolve to eventually come to the specified subgroup.

sub int(10) unsigned Group ID.
super int(10) unsigned Group ID.
g int(11) "generation"; one of {0,1,2,3,4,5}. The distance this subgroup is from the specified supergroup. There is no limit on g.

idUpdates

Contains a list of all the records that have been modified. This is used in the scripts that replicate new GDBM data to the mirrors.

id int(10) unsigned User ID or IP address ID
type char(1) binary {A,I,T,U} (What is T???)
groupID int(10) unsigned ???
g int(11) "generation"; one of {0,1,2,3,4,5}. The distance this subgroup is from the specified supergroup. There is no limit on g.
seqNo int(10) unsigned ???
action enum('add','del','cpl','sum','mrk') ???
agent char(20) ???

ids

Contains a record of all the users, groups, and IPs.

id int(10) unsigned The user, group, and IP ID numbers???
stops int(10) unsigned One of {0,1,2,6,8,16,64,128}. ??? deprecated: stops generally apply to an ID's role, not a ID.
type char(1) binary One of { ,A,G,I,M,U,W}. 'G' (784 rows) specifies a group, 'I' (13864 rows) an IP, 'U' (8381 rows) a user. ' ' (2 rows), 'A' (1 row), 'M' (4 rows), and 'W' (14 rows) are unknown???
value varchar(40) binary ???
pubKey text ???
expire date ???
info varchar(80) ???
last timestamp(14) ???
orgId int(10) unsigned ???
sponsor varchar(32) ???
fmp int(1) ???

idInclusions

Maps users/IPs to the groups of which they are members. This table is generated from the hierarchy table.

id int(10) unsigned If the type (below) is 'U', then this maps to a User ID in the userDetails table. There will be as many entries for a user or IP with a given ID as there are groups to which that user or IP belong.
type char(1) binary One of {A,I,U}. 'I' specifies that the record describes an IP address and 'U' specifies a user. 'A' is a special unique type (id=1, type=A, groupId=1, g=0) that indicates world access.
groupId int(10) unsigned Group ID to which this user belongs.
g int(11) The generation number, range {0-6}; i.e. the number of subgroups that had to be resolved in order to find this user/IP.

hierarchy

The hierarchy table is the base table from which most derived tables are compiled.

super int(10) unsigned A group ID.
type char(1) binary One of {G,I,U,W,'}. 'G' specifes group, 'I' specifes IP, 'U' specifies user, and 'W' specifies world access (?).
sub int(10) unsigned An ID of a user, IP, or group.
sponsor int(10) unsigned The group that put this person in this group.
stops int(10) unsigned One of {0,1,2,6,65}. The number of times that the person has been dropped from the group???
why varchar(255) binary The reason this person/group/IP was put in this group.
newType char(1) NULL or 'U', (4419 U, 18839 NULL) ???
last timestamp(14) The last time this was ???

acls

acl int(10) unsigned 82 rows; integers look like {4,5,6,7,20,21,...,170,171}
type char(1) binary One of {A,G,I,U}. 'G' specifes group, 'I' specifes IP, 'U' specifies user, and 'A' specifies world access (?). (Is the single ' entry a mistake???)
id int(10) unsigned 43 rows; Numbers start with {0,1,4,100,101,102,...,14341,21483}
access int(10) unsigned One of {1-7,16,17,18,37,93}. Should represent GET, PUT, etc.; not sure what the "big" numbers do.

uris

A table specifiying properties of the URIs both on the site and elsewhere.

id int(10) unsigned URI ID
uri varchar(255) binary The W3C URI for the resource (e.g. http://www.w3.org/path/to/resource)
acl int(10) unsigned 45 rows; integers look with {0,5,6,7,21,22,...,170,171}. Probably maps to acls/acl ???
last timestamp(14) ???
idsId int(10) unsigned ???
valid char(1) Either 'y' or 'n'. Only 15 rows are 'y'. Tells whether a resource was HTML valid or not. Defaults to 'n'. (NOTE: Not all resources have been checked.)

groupDetails

794 rows.

id int(10) unsigned Group ID
type char(1) One of {G,M,O,W}. ???
acRep int(10) unsigned One of {0,3,9729}. ???
entityEncoded varchar(255) Looks like a long descriptive name.
alphaname varchar(255) Looks like entityEncoded...
category varchar(100) Looks like an arbitrary categorization scheme. Is this used???
name varchar(255) Looks like alphaname...
notes text Unused
last timestamp(14) Timestamp for ???

userDetails

8370 rows.

id int(10) unsigned The User ID.
family varchar(255) Family name
given varchar(255) Given name
prefix varchar(255) Prefix (Mr., Mrs., etc.)
status int(10) unsigned One of {0,32,36}. ???
passwd varchar(40) Encrypted password???
mailback varchar(40) All NULL. ???
email varchar(255) Email address
emailUrisId int(10) unsigned ???
phone varchar(32) Phone number
position varchar(255) User's position in their company.
ac varchar(255) 618 rows; the company that sponsors them?
acEric int(10) unsigned ???
url varchar(255) A URL that is associated with the user.
last timestamp(14) Timstamp for ???
cleartxt varchar(40) Cleartext password???
notes text Notes about the user.
phonesId int(10) unsigned ???