USE internationaliser;
/**
 * roles of people as: translators, proofreaders, programmers and administrators.
 */
DROP TABLE IF EXISTS roles;
CREATE TABLE roles /* aka userRoletable to Tomcat JDBCRealm */ (
   /**
    * aka userNameCol to Tomcat JDBCRealm
    * this is the User
    * people are identified by unique initials.  If there are duplicates,
    * break the tie with a number or extra letter.
    * The field is case-insensitive.
    * The initials (acual a user id) must pass muster by a system wide
    * configurable regex to determine what is considered acceptable.
    * To allow only letters, underscore and dot use [a-zA-Z\\\_\\.]++
    * in the initialsValidRegex field in the configuration file.
    */
   initials CHAR( 4 ) NOT NULL,
   /**
    * ake roles to Tomcat JDBCRealm
    * a person could be any combination of translator, proofreader, programmer, administrator
    * The combination is encoded as a SET in the people record.
    */
   capability ENUM( 'translator', 'proofreader', 'programmer','administrator' ),
   /**
    * We look up people by their initials.
    * We also use initials to link in other tables
    * to link to this information.
    */
   PRIMARY KEY( initials, capability )
   );