Create a MySQL DocDB Database

1. Start by creating a test database to make sure you can work with MySQL.
2. Create the DocDB database and accounts:
   

   mysql -u root -p
   mysql> show databases;
   mysql> use mysql;
   mysql> select * from user;
   mysql> delete from user where user="";
   mysql> create database SomeDocDB;
   mysql> grant select on SomeDocDB.* to docdbro@localhost identified by "read only password";
   mysql> grant select on SomeDocDB.* to docdbro@mydocs.fnal.gov identified by "read only password";
   mysql> grant select,insert,update,delete on SomeDocDB.* to docdbrw@mydocs.fnal.gov identified by "read write password";
   mysql> grant select,insert,update,delete on SomeDocDB.* to docdbrw@localhost identified by "read write password";
   mysql> grant ALL on SomeDocDB.* to docdbadm@localhost identified by "database owner password";
   mysql> grant ALL on SomeDocDB.* to docdbadm@mydocs.fnal.gov identified by "database owner password";
   mysql> quit
   

3. MySQL accounts. You may choose your own names or leave these as defaults. Only docdbadm (in our example) is visible to users, but if you have more than one DocDB sharing the same MySQL server, you may want different names.
   • docdbadm - administrative account with full MySQL access
   • docdbrw - for trusted user read/write access to MySQL
   • docdbro - for public or other non-trusted read-only access to MySQL
4. Definition of MySQL access restrictions:
   • localhost - only access from the MySQL server machine.
   • mydocs.fnal.gov - only access from this machine
   • %.fnal.gov - access restricted to machines in the fnal.gov domain. (not recommended)
   • % - access allowed from any domain. (don't even consider doing this)
5. Test access: Login to the host where DocDB will run and as the user (usually the user that runs Apache) that the scripts will run under. Connect to DocDB with mysql -h hostname -u docdbro -p SomeDocDB. Make sure you can connect. If not, see if you are running SELinux extensions. There are reports of these access rules being configured to disallow access from the Apache user to MySQL
6. Create the DocDB tables:
   mysql -u docdbadm -p SomeDocDB < CreateDatabase.SQL
   (CreateDatabase.SQL is in DocDB/sql)
7. You will need to declare the database administrator in the SecurityGroup table.
   

   mysql -u docdbadm -p SomeDocDB
   mysql> insert into SecurityGroup set Name='docdbadm',Description='DocDB Administrators',CanCreate=1,CanAdminister=1;
   

8. Populating the database
   • If you just want to see how DocDB works, you can install some test data with the command:
     mysql -u docdbadm -p SomeDocDB < TestData.sql
     (TestData.sql is also in DocDB/sql)
     If you decide to adopt DocDB, you should issue the command "drop database SomeDocDB" and begin again with Step 3.
   • The DocDB administration interface allows the administrator to perform all administration tasks from the web pages.
   • If you have a lot of information to enter, you may find it useful to populate the Author, Institution, Topic, and SecurityGroup tables directly.
     

       mysql -u docdbadm -p SomeDocDB
       mysql> insert into SecurityGroup set Name='SomeGroup',Description='Some Group of Priviledged People',CanCreate=1;
       mysql> insert into Institution set ShortName='Fermilab',LongName='Fermi National Accelerator Laboratory';
       mysql> insert into Author (FirstName,MiddleInitials,LastName,InstitutionID) 
           ->   values('John','J.','Doe',1),
           -> ('W.','Bill','Jones',1);
       mysql> insert into Author (FirstName,LastName,InstitutionID) 
           ->   values('Amy','Smith',1);
       mysql> INSERT INTO Topic (TopicID,ShortDescription,LongDescription)         
           -> values(1,'Main Topic 1','Some high-level topic');
       mysql> INSERT INTO Topic (TopicID,ShortDescription,LongDescription)         
           -> values(2,'Main Topic 2','Another high-level topic');
       mysql> INSERT INTO Topic (TopicID,ShortDescription,LongDescription)         
           -> values(3,'Lower-level','A child topic of topic 2');
     ## And insert into the hierarchy too
       mysql> INSERT INTO TopicHierarchy (TopicID,ParentTopicID)         
           -> values(3,2);
           
     

9. The web interface allows the database administrator to perform administrative tasks, such as adding Major and Minor Topics. Access to the administrative links is restricted to database administrators. You will not see the links unless you use the database administrator username to login to the database pages.
10. Authors can be added by anyone in a trusted group.
11. All relevant tables will be populated from the web interface when a document is entered into the database.

DocDB License