USE internationaliser;
/**
 * translators, proofreaders, programmers and administrators.
 */
DROP TABLE IF EXISTS people;
CREATE TABLE people (
   /**
    * 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,
   /**
    * First name then surname
    */
   fullName VARCHAR( 30 ) NOT NULL,
   /**
    * email address
    */
   email VARCHAR( 50 ),
    /**
    * telephone, complete with international dialing prefixes.
    */
   telephone VARCHAR( 50 ),
   /**
    * a person could be any combination of translator, proofreader, programmer, administrator
    */
   capabilities SET( 'translator', 'proofreader', 'programmer','administrator' ),
   /**
    * password or passphrase, case sensitive. Administrator or the person themselves may modify it.
    * SHA-1 digest 40 hex digits. Because this is a digest, it is impossible to work backwards
    * from this to the original password.
    * Most likely authentication will be handled via RFC2617 HTTP digest, which is more secure
    * than basic plain text passwords, but not as secure as SSL-https.  The messages back and forth
    * are not encrypted.
    */
   encryptedPassword CHAR( 40 ),
   /**
    *  preferred language for receiving email and running the editor, 2-char abbreviation, lower case.
    */
   language CHAR ( 2 ) NOT NULL,
   /**
    *  preferred country for receiving email and running the editor, 2-char abbreviation, all caps, possibly null.
    */
   country CHAR ( 2 ),
   /**
    *  preferred locale variant for receiving email and running the editor, 3-char abbreviation, all caps, possibly null.
    */
   variant CHAR ( 2 ),
   /**
    * Preferred way this user wants to have his emails encoded.  The encoding must be supported both by Java,
    * see http://mindprod.com/jgloss/encodings.html on the server, and by the recipient's email program.
    * It is crucial that this be a valid supported name.
    * Default is UTF-8.  In desperation, you could use US-ASCII which supports no accented letters.
    */
   emailEncodingCharset VARCHAR (20),
   /**
    * People are optionally associated with an icon, but those icons are not stored in the database.
    * They live in resource files, under names like people/mary.png or people/tallblond.png  or DRF.png.
    * You might wonder why we don't just insist that the correponding icon be named to match the initials. This way
    * you can reduce the number of icons in the resource file if several people share the same icon.
    * WHen the icon is displayed, tooltip hoverhelp will tell you the initials and
    * full name of the person it represents.
    */
    iconName VARCHAR( 30 ),
   /**
    * the preferred look and feel name, the long name, the  class name.
    * Metal : javax.swing.plaf.metal.MetalLookAndFeel
    * CDE/Motif : com.sun.java.swing.plaf.motif.MotifLookAndFeel
    * Windows : com.sun.java.swing.plaf.windows.WindowsLookAndFeel
    * MacOS : it.unitn.ing.swing.plaf.macos.MacOSLookAndFeel
    * Watch out! when an administrator configures this, he will be selecting
    * from a list of short names of L&Fs supported on HIS machine.
    * He must be careful that any L&F he picks is actually
    * supported by the machines this person will use.
    * If that L&F is not supported, it will revert to the locale default.
    */
    lookAndFeel VARCHAR( 100 ),
   /**
    * We look up people by their initials.
    * We also use initials to link in other tables
    * to link to this information.
    */
   PRIMARY KEY( initials )
   );